Database
Drizzle + PGlite locally, Postgres in prod
Overview
The schema lives at `apps/api/src/db/schema.ts` and defines every table — users, routes, stops, vehicles, assignments, reservations, offers, announcements, otpTokens, tripLogs, driverNotes. Each table is a Drizzle pgTable with typed columns, and the generated InferSelectModel types flow into the shared package so the mobile and admin apps see them too. Local dev uses PGlite so contributors don't need Docker.
How it works
`pnpm db:generate` runs drizzle-kit generate which produces a SQL migration in apps/api/drizzle/. We commit the migrations.
`pnpm db:migrate` runs the migrations against either PGlite (in dev) or the configured Postgres URL (in staging/prod). The same migration file works on both.
Dev DB: PGlite stores data in `apps/api/.pglite/` — a folder we gitignore. Resetting the dev DB is `rm -rf .pglite` and re-running migrate plus seed.
Indexes are explicit and named after the access pattern — users_email_unique, reservations_date_route — so query plans are predictable.
Soft deletes: most tables have a nullable deletedAt column. Queries `WHERE deleted_at IS NULL` by default, exposed through a Drizzle composable helper.
Connection: in dev, a singleton PGlite instance lives on globalThis to survive Next.js hot reloads. In prod, we use a pooled pg.Pool and let the driver manage connections.
Key decisions
PGlite for dev, real Postgres for prod
Running Postgres locally is fine but not free — Docker, ports, init scripts. PGlite gives us 'just clone and run' for new contributors. The same SQL, the same Drizzle, the same migrations. Production differences are limited to connection pooling and the migration runner.
Drizzle over Prisma
Drizzle's typed SQL feels closer to the database than Prisma's query builder, and the generated types are leaner. We also get migrations as SQL files we can read, which is the right level of abstraction for an operational system.
Schema in shared package, not API-only
The mobile and admin apps need the same row types — Offer, Route, Reservation. We export them through @vineroute/shared so a schema change propagates to every consumer in one TypeScript pass.