TransitPin - Data Model¶

Source of truth: the live SQLite database at /opt/transitpin-api/data/transitpin.db, dumped via sqlite3 ... ".schema" on 2026-08-15. There are 15 tables.

0. Conventions¶

SQLite 3, single file, no WAL mode, no foreign-key enforcement enabled. All relationships are by convention only (documented below), not enforced by the engine.
Primary keys are TEXT (UUID hex strings or slugs), generated in application code with uuid.uuid4().hex or supplied as slugs.
Timestamps are ISO-like strings via SQLite datetime('now') (local time) in created_at / paid_at, or Python-generated ISO strings in last_update, exp, policy_acknowledged_at.
Most tenant-scoped tables carry a tenant_id TEXT DEFAULT 'demo' column. users is the exception (single-tenant legacy org_id).
ensure_schema functions add columns idempotently with PRAGMA table_info-guarded ALTER TABLE. Columns added this way appear after the original CREATE TABLE columns in the live schema.

1. tenants¶

Platform customers. One row per transportation provider.

Column	Type	Notes
id	TEXT PK	slug, e.g. `demo`, `villageexpress`
name	TEXT	display name
tier	TEXT DEFAULT 'basic'	`basic` / `pro` / `enterprise`
theme	TEXT	JSON string of CSS theme tokens
domain	TEXT	optional custom domain
created_at	TEXT	`datetime('now')`

Seeded rows: demo (TransitPin Demo, enterprise) and villageexpress (Village Express Transportation, enterprise). theme is a JSON object such as {"primary":"#2ac0a6","accent":"#2ac0a6","bg":"#ffffff","font":"'Nunito',..."}.

2. households¶

A billing unit: one contact (typically a parent) and their children.

Column	Type	Notes
id	TEXT PK	UUID
tenant_id	TEXT
name	TEXT	household/primary-contact name
email	TEXT
phone	TEXT
address	TEXT
billing_cycle	TEXT DEFAULT 'weekly'	reserved; weekly is the only cycle
created_at	TEXT
first_pickup_date	TEXT	`YYYY-MM-DD`; drives 5-day-before invoicing
policy_acknowledged	TEXT	comma-joined policy ids
policy_acknowledged_at	TEXT	ISO timestamp

3. payers¶

An adult who is invoiced. A household has one primary payer and zero or more co-payers (for example a second parent or a co-parent outside the household).

Column	Type	Notes
id	TEXT PK	UUID
tenant_id	TEXT
household_id	TEXT	references households.id
name	TEXT
email	TEXT
phone	TEXT
is_primary	INTEGER DEFAULT 0	1 for the primary payer
created_at	TEXT
relationship	TEXT	optional label (e.g. "mother", "father")

4. billing_allocations¶

How a household's charges are split across payers.

Column	Type	Notes
id	TEXT PK	UUID
tenant_id	TEXT
household_id	TEXT	references households.id
payer_id	TEXT	references payers.id
child_id	TEXT NULL	optional per-child allocation
split_type	TEXT	`percentage` / `fixed` / `per_child`
value	REAL	percent, dollars, or child count
created_at	TEXT

5. invoices¶

Billing documents. Two kinds: registration (one-time $50) and transportation (weekly).

Column	Type	Notes
id	TEXT PK	UUID
tenant_id	TEXT
household_id	TEXT	references households.id
payer_id	TEXT	references payers.id
cycle_start	TEXT	ISO date
cycle_end	TEXT	ISO date
amount	REAL
status	TEXT	`draft` / `sent` / `unpaid` / `paid` / `partial`
provider	TEXT	`square` / `stripe` / `none`
provider_invoice_id	TEXT NULL	provider-side id
created_at	TEXT
kind	TEXT DEFAULT 'transportation'	`registration` or `transportation`

6. payments¶

Payment records against invoices.

Column	Type	Notes
id	TEXT PK	UUID
tenant_id	TEXT
invoice_id	TEXT	references invoices.id
amount	REAL
method	TEXT	payment method label
provider_ref	TEXT NULL	provider reference
paid_at	TEXT	`datetime('now')`

7. children¶

Riders (students). Extended by billing with trip type, household link, weekly rate, and school id.

Column	Type	Notes
id	TEXT PK	UUID
name	TEXT NOT NULL
school	TEXT	school display name
grade	TEXT
route	TEXT	route short name, e.g. "Route 1"
parent_name	TEXT
parent_email	TEXT
parent_phone	TEXT
tier	INTEGER DEFAULT 1
status	TEXT DEFAULT 'active'	`active` / inactive
tenant_id	TEXT DEFAULT 'demo'
created_at	TEXT
trip_type	TEXT	`one-way` / `two-way` (billing)
household_id	TEXT	references households.id
rate_weekly	REAL	derived weekly rate
school_id	TEXT	references schools.id

8. registrations¶

The admin approval queue. Real signups (via /api/registrations/full) now write a row here keyed by id = household_id, so they surface alongside any legacy seeded records. status drives approval: pending on signup, approved (or rejected) after staff review, which also promotes the household's pending registration invoice and emails the fee.

Column	Type	Notes
id	TEXT PK	UUID
name	TEXT NOT NULL
initials	TEXT
type	TEXT DEFAULT 'Rider'
route	TEXT
school	TEXT
time	TEXT
status	TEXT DEFAULT 'pending'	`pending` / `approved` / `rejected`
badge	TEXT
email	TEXT
phone	TEXT
created_at	TEXT
tenant_id	TEXT DEFAULT 'demo'

9. routes¶

Transportation routes.

Column	Type	Notes
id	TEXT PK	e.g. `r1`
name	TEXT NOT NULL	e.g. "Route 1 - East Savannah"
bus_id	TEXT	references buses.id
driver	TEXT
stops	INTEGER DEFAULT 0	denormalized stop count
miles	REAL DEFAULT 0
status	TEXT DEFAULT 'active'
mode	TEXT DEFAULT 'published'	`draft` / `published`
tenant_id	TEXT DEFAULT 'demo'
created_at	TEXT

10. route_stops¶

Normalized ordered stops for a route.

Column	Type	Notes
id	TEXT PK	UUID
route_id	TEXT	references routes.id
seq	INTEGER	1-based order
name	TEXT
address	TEXT
lat	REAL
lng	REAL
time	TEXT
riders	TEXT	JSON array of rider names
tenant_id	TEXT DEFAULT 'demo'

routes.stops is the denormalized count kept in sync with route_stops rows. riders is stored as a JSON string and decoded by _route_with_stops.

11. schools¶

District school roster, seeded per tenant.

Column	Type	Notes
id	TEXT PK	e.g. `ve-` prefixed per tenant
name	TEXT NOT NULL
start_time	TEXT	24h `HH:MM`
end_time	TEXT	24h `HH:MM`
address	TEXT
zone	TEXT
created_at	TEXT
tenant_id	TEXT DEFAULT 'demo'

There are no write endpoints for schools; rows are inserted directly via sqlite3. Live counts: 53 rows for demo and 53 for villageexpress.

12. buses¶

Vehicles.

Column	Type	Notes
id	TEXT PK
name	TEXT NOT NULL
plate	TEXT
capacity	INTEGER DEFAULT 54
driver_name	TEXT
driver_phone	TEXT
status	TEXT DEFAULT 'active'
lat	REAL
lng	REAL
heading	REAL
speed	REAL
last_update	TEXT
created_at	TEXT
tenant_id	TEXT DEFAULT 'demo'

13. users¶

Legacy single-tenant user table (not tenant-scoped).

Column	Type	Notes
id	TEXT PK
email	TEXT UNIQUE NOT NULL
name	TEXT
role	TEXT DEFAULT 'admin'
org_id	TEXT DEFAULT 'village-express'
created_at	TEXT

14. impersonation_sessions¶

Ops assume-identity sessions.

Column	Type	Notes
token	TEXT PK	`secrets.token_urlsafe(32)`
actor_id	TEXT	real actor
actor_email	TEXT
tenant_id	TEXT	impersonated tenant
target_role	TEXT
exp	TEXT	ISO expiry (15 min)
created_at	TEXT

15. audit_log¶

Ops audit trail.

Column	Type	Notes
id	TEXT PK	UUID
actor_id	TEXT	real actor
actor_email	TEXT
impersonated_id	TEXT
impersonated_email	TEXT
tenant_id	TEXT
action	TEXT	e.g. `assume_identity_start`
resource	TEXT	e.g. `tenant:villageexpress`
details	TEXT	JSON string
created_at	TEXT

Relationship map¶

tenants 1 --- * households
tenants 1 --- * payers, invoices, children, routes, route_stops, buses,
                schools, registrations, billing_allocations, payments

households 1 --- * payers        (payers.household_id)
households 1 --- * children      (children.household_id)
households 1 --- * invoices      (invoices.household_id)
households 1 --- * billing_allocations (billing_allocations.household_id)

payers 1 --- * billing_allocations (payer_id)
payers 1 --- * invoices            (invoices.payer_id)

routes 1 --- * route_stops        (route_stops.route_id)
routes * --- 1 buses              (routes.bus_id -> buses.id, optional)

schools 1 --- * children          (children.school_id -> schools.id, optional)

invoices 1 --- * payments         (payments.invoice_id)