Baseleg Docs
Reference · Architecture / Database · Baseleg Docs

Database overview

Baseleg uses Cloudflare D1 as the initial relational database, with Drizzle as the query/migration toolkit.

This document describes how the repo is structured so the data owner can expand it safely without breaking architecture boundaries.

Entity-relationship diagram

Reflects the schema as of migration 0005_flights_and_usage_events. PK/FK badges mark primary/foreign keys; a → note under an FK field names its target. ? after a field name means nullable. auth_* tables are generated by the Better Auth library rather than hand-designed.

People context
people
PKid
name
email
phone?
status
FKauth_user_id?
→ auth_users.id
created_at
updated_at
people_type
lookup table
PKid
e.g. 'student', 'instructor'
label
person_type_assignments
junction table
PK/FKperson_id
→ people.id (cascade)
PK/FKpeople_type_id
→ people_type.id
Aircraft & Scheduling contexts
aircraft
PKid
registration
type
status
grounded_reason?
grounded_at?
FKgrounded_by?
→ people.id (set null)
usage_status
current_hobbs?
current_tach?
created_at
updated_at
bookings
PKid
FKperson_id
→ people.id
FKaircraft_id
→ aircraft.id
FKinstructor_id?
→ people.id
start_at
end_at
status
provisional | confirmed | checked_out | completed | cancelled
cancelled_at?
created_at
flights
PKid
FKbooking_id
→ bookings.id (unique)
FKaircraft_id
→ aircraft.id
FKpilot_person_id
→ people.id
FKinstructor_person_id?
→ people.id
FKdispatcher_person_id
→ people.id
status
active | completed | cancelled_no_flight
checked_out_at
returned_at?
FKchecked_in_by_person_id?
→ people.id
has_defect
defect_description?
start_hobbs?
start_tach?
created_at
updated_at
bookings also has indexes on (aircraft_id, start_at, end_at), (instructor_id, start_at, end_at), and (person_id) to support conflict checks and lookups. flights.booking_id has a unique index — zero or one Flight per Booking.
Billing context
usage_events
PKid
FKflight_id
→ flights.id
FKperson_id
→ people.id
FKaircraft_id
→ aircraft.id
hobbs_hours?
tach_hours?
occurred_at
created_at
Settings
operating_hours
singleton row
PKid
open_hour
close_hour
created_at
updated_at
Authentication (Better Auth — generated, not hand-designed)
auth_users
PKid
name
email
email_verified
image?
created_at
updated_at
auth_sessions
PKid
expires_at
token
ip_address?
user_agent?
FKuser_id
→ auth_users.id (cascade)
created_at
updated_at
auth_accounts
PKid
account_id
provider_id
FKuser_id
→ auth_users.id (cascade)
access_token?
refresh_token?
id_token?
access_token_expires_at?
refresh_token_expires_at?
scope?
password?
created_at
updated_at
auth_verifications
no foreign keys
PKid
identifier
free-form, typically an email — not a declared FK
value
expires_at
created_at?
updated_at?

Key rules

  • UI routes/pages must not import Drizzle schema or repository implementations.
  • Application packages must not depend on infrastructure implementations directly.
  • Infrastructure owns D1/Drizzle wiring and repository implementations.
  • Schema changes must ship with migrations.

Repo structure

  • db/schema/ — canonical schema “source of truth”, one file per bounded context
  • db/migrations/ — SQL migration files (generated/maintained as the schema evolves)
  • db/seeds/ — seed scripts/data for local/dev environments
  • packages/infrastructure/db/ — Drizzle + D1 wiring (implementation detail)
  • packages/infrastructure/repositories/ — repository implementations (DB-backed)

Conventions

  • One schema entrypoint per bounded context, e.g.:
    • db/schema/people.ts
    • db/schema/aircraft.ts
    • db/schema/scheduling.ts
  • Migrations are append-only and ordered.
  • Prefer explicit foreign keys for cross-table relationships.
  • Keep “reporting” queries out of domain/application; implement in infrastructure behind ports.

Beyond the v1 spine

The v1 spine (People, Aircraft, Scheduling) has real schema, migrations, and indexes as shown above. Billing has one real table (usage_events) — the raw hours-flown fact written on every Flight completion — but no Charge Item/Invoice schema yet. Training, Compliance, Notifications, and Reporting remain intentionally unimplemented at the data layer — see each context’s page under bounded contexts for what’s planned.