Database (Drizzle ORM + SQLite)
Why SQLite
SQLite is a database engine that lives in a single file on disk, with no separate server process to run or manage. For an app with one deployment target and no need for concurrent multi-server writes, it removes an entire category of operational work (connection pooling, a separate DB server to provision, network latency between app and DB). The whole database is db/dashboard.db - back it up by copying that file (see the Deployment guide).
better-sqlite3 is the Node driver used to talk to it - it's synchronous (no await needed per query) and fast, because it binds directly to SQLite's C library rather than shelling out or using an async wrapper.
Why an ORM (Drizzle)
Drizzle ORM lets you define your schema once, in TypeScript, and get:
- Fully-typed query results (the compiler knows what columns
ordershas and what type each one is). - A single source of truth that migrations are generated from, instead of hand-writing SQL migrations and a separate TypeScript schema that can drift out of sync.
- SQL-like query building (
db.select().from(orders).where(eq(orders.id, id))) rather than a heavier abstraction - Drizzle deliberately stays close to SQL rather than hiding it.
The schema (src/lib/db/schema.ts)
Tables are defined with Drizzle's sqliteTable(). This file re-exports the auth tables from src/lib/db/auth-schema.ts (see below) and defines the app-specific ones:
| Table | Represents |
|---|---|
team | The club's FRC subteams (Arm, Autonomous, Chassis, etc.) |
stfQuarter / stfBucket | School-year funding periods and their budget buckets ("STF" = Student Tech Fund), each with a starting balance in cents |
giftFund / financeSettings | Singleton rows (fixed id) tracking the gift-fund balance and tax/shipping percentages |
orders | Part purchase requests - vendor, link, item, cost, status (pending/approved/denied/ordered), fund type (STF/Gift) |
giftFundLog, orderHistory | Audit trails for balance changes and order status transitions |
apiKey | One-way hashed service API keys for the external simulation-script API (distinct from the vault - see Security) |
vaultEntry / vaultEntryAccess | The shared credential vault and its per-user read grants |
minecraftWhitelist | Minecraft whitelist requests (username, status, admin review) |
networkJoinRequest | Tailscale/network device join requests |
userFeature | Per-user feature-flag grant requests (pending/granted/rejected) - what middleware.ts checks for non-admin routes |
Money is always stored as integer cents (unitCostCents, startingBalanceCents, etc.), never floats - this avoids floating-point rounding errors in financial arithmetic. Timestamps are stored as millisecond integers via a shared SQL fragment:
const now = sql`(cast(unixepoch('subsecond') * 1000 as integer))`;
The auth tables are auto-generated - don't edit them
src/lib/db/auth-schema.ts defines user, session, account, and verification and is regenerated by better-auth itself, not hand-written:
pnpm auth:generate
If you change something in the better-auth config (src/lib/auth/auth.ts) that affects the schema - like adding a field to the user table - run this command rather than editing auth-schema.ts directly; your edits will just be overwritten next time it runs.
The DB client (src/lib/db/index.ts)
const sqlite = new Database(dbPath);
sqlite.pragma("journal_mode = WAL");
sqlite.pragma("foreign_keys = ON");
export const db = drizzle(sqlite, { schema });
WAL (write-ahead logging) mode lets reads and writes happen concurrently without blocking each other - relevant even in a single-process app, since Next.js handles multiple requests concurrently. foreign_keys = ON is required because SQLite disables foreign-key enforcement by default, even when a schema declares foreign keys.
Migrations
Drizzle's migration model: you never hand-write ALTER TABLE statements. You change schema.ts, then generate a migration file that captures the diff:
pnpm db:generate # drizzle-kit generate - diffs schema.ts against drizzle/migrations, writes a new .sql file
pnpm db:migrate # drizzle-kit migrate - applies any .sql files not yet recorded as applied
Generated files live in drizzle/migrations/*.sql, with a meta/_journal.json tracking which have been applied and a meta/000N_snapshot.json per migration capturing the full schema at that point (used to compute the next diff). Never hand-edit files under drizzle/migrations/ - they're a generated, ordered history; editing one after it's been applied anywhere desyncs that environment's migration state from the file.
Seeding (scripts/seed.ts, run via pnpm db:seed)
Populates the 6 subteams, a gift fund/quarter/buckets row, an admin user (from SEED_ADMIN_EMAIL/SEED_ADMIN_PASSWORD/SEED_ADMIN_NAME - throws if unset), and ~13 sample orders spanning every status, all prefixed [seed] so they're easy to spot and distinguish from real data.
It's idempotent - every insert is either .onConflictDoNothing() (teams) or an explicit select-then-insert/update check keyed on a unique name (gift fund, buckets, admin user, seed orders), so running pnpm db:seed again never creates duplicates. This is what makes pnpm setup (db:migrate + db:seed) safe to run repeatedly, including as part of the production deploy path.
Studio - a GUI for the local database
pnpm db:studio
Opens Drizzle Studio - a local web UI for browsing and editing rows directly, useful for poking at data during development without writing throwaway queries. Dev-only; don't point this at a production database path.