kanji-buddy

Database

Eleven tables on Neon Postgres 18. The split that runs through all of them, and how a change reaches production.

The split: static content vs your progress

Two kinds of table, treated differently on purpose.

	Static content	Per-user state
Tables	subjects, spaced_repetition_systems, voice_actors	assignments, reviews, review_statistics, study_materials, level_progressions, resets
Shape	A jsonb `data` column holding WaniKani's payload verbatim.	Fully typed columns.
Why	The API returns it unchanged, and nothing here ever queries inside it. Storing it whole means an import is a copy, not a mapping.	The SRS engine reads and writes individual fields — a stage, a due time — so they have to be real columns with real indexes.

One exception, and what it cost

subjects.mnemonic_image_url is a real column even though it is content. It has to be: import:subjects writes data: excluded.data, replacing the jsonb wholesale, so anything merged in there is destroyed on the next re-import. Those URLs came from scraping and cannot be re-derived from the API dumps. The serialiser folds the column back into data on the way out, so clients still read it beside the mnemonic it belongs to.

Every table

Table	Holds
`users`	Account, level, portal id, email, notification flags.
`api_tokens`	sha-256 of each bearer token. Never the token itself.
`subjects`	9,418 radicals, kanji and vocabulary.
`spaced_repetition_systems`	The two stage tables and their intervals.
`voice_actors`	Metadata for the pronunciation audio.
`assignments`	The heart of it: one row per user per subject, carrying srs_stage and available_at.
`reviews`	One row per completed review, with the stage it moved between.
`review_statistics`	Running correct/incorrect tallies and streaks per subject.
`study_materials`	Your notes and custom synonyms.
`level_progressions`	When each level unlocked, started, passed.
`resets`	Level resets. Present for API parity; nothing writes them.

Conventions that pay for themselves

Every table has updated_at. That single column is what makes ?updated_after= work across the whole API, which is how a client syncs incrementally instead of refetching.

Indexes lead with user_id. (user_id, id) for pagination, (user_id, updated_at) for sync, (user_id, available_at) for “what is due”. Every query is scoped to one person, so every index should be too.

camelCase in TypeScript, snake_case in Postgres. Drizzle is configured with casing: "snake_case" in both the runtime client and the config, so mnemonicImageUrl becomes mnemonic_image_url without either side being written twice.

Connecting

db/client.ts

postgres(connectionString, { prepare: false, max: 12, idle_timeout: 20 })

prepare: false is not optional — Neon’s pooler is PgBouncer in transaction mode, which cannot do prepared statements. max: 12 covers the dashboard’s largest batch of eleven parallel queries; at max: 1 they quietly serialised and the batching bought nothing.

The exported db is a lazy Proxy: importing it opens no connection, and tests can swap in an in-process Postgres before the first query. That indirection is also what made a 31-second dashboard possible — see Build & deploy.

Two branches, and why

Neon project ├── production ← Vercel; your real progress └── dev ← .env.local; a copy-on-write clone

Neon branches are instant, cost nothing until written to, and are not git branches — there is no merge. They are disposable environments, and what flows between them is migrations through git, not data. Data can be pulled down (“Reset from parent”), never pushed up.

Why this exists at all

Running npm run dev against production is destructive in a way that isn’t obvious: finishing a lesson quiz consumes those lessons, and answering a review advances the SRS stage. Clicking through to check a change silently eats items you then never get taught. That happened — five radicals were consumed by a test run — which is why db/client.ts now announces its target on every non-production connect:

[db] connected to ep-quiet-union-….neon.tech [dev branch]

Migrations

Schema lives in db/schema.ts; SQL is generated from it, not written by hand.

npm run db:generate      # diff schema.ts → db/migrations/NNNN_*.sql
npm run db:migrate       # apply to the dev branch
npm run db:migrate:prod  # apply to production (ENV_FILE=.env.production.local)

Read the generated SQL before applying it. Adding a nullable column is safe; anything that drops or rewrites is not, and Neon has no undo. Each database tracks what it has applied in its own drizzle.__drizzle_migrations table, which is why a branch created before a migration still needs it.

Ordering matters when deploying. Deploy code first only when it tolerates the old schema — a nullable column it simply won’t find. If the code requires the column, migrate first: a Drizzle select() names every column, so live requests fail in the gap.