extract authd's SQLite migration system into a standalone npm package #16

New Issue

brendan · 2026-05-13T19:02:01Z

brendan commented

2026-05-13 19:02:01 +00:00

Originally filed: 2026-05-11 in ~/features.md, block #2.
Cross-project companion issues: brendan/sqlite-migrate, brendan/authd, brendan/buchinese, brendan/inventory, brendan/dashcam, brendan/movement

2026-05-11 — cross-project: extract authd's SQLite migration system into a standalone npm package installable via direct Gitea URL, make the repo public, then adopt it in every project that uses SQLite (buchinese, inventory, nanodrop, movement, dashcam, and authd itself dogfoods). Replaces six independent hand-rolled applySchema(db) blobs with one versioned, checksum-verified, runner-tested implementation.

Motivation. authd PR #14 (merge_commit ced21ab4d16c28a6b5fafa9072b0ae8207ba4bfd, merged 2026-05-11) landed a real migration system: numbered .sql files in src/db/migrations/, a schema_migrations tracking table, sha256 checksum verification, idempotent re-apply, genesis-stamping for pre-existing prod DBs, and three CLI scripts (db:migrate / db:status / db:stamp). The runner lives in src/db/migrate.ts (178 LOC, exports applyMigrations, listMigrations, readAppliedRows, stampMigration) and is exercised by tests/db/migrate.test.ts (270 LOC, 9 specs covering fresh-apply / idempotent / checksum-mismatch / genesis-stamp / partway-failure rollback).

Meanwhile, every other SQLite-using project still ships a single applySchema(db) function that runs CREATE TABLE IF NOT EXISTS for every table on every boot and does ad-hoc ALTER patches when schema needs to evolve (e.g. src/db/schema.ts:111-122 in buchinese for the sessions.expires_at retrofit). This is fine for a fresh DB but rots fast: there's no way to add a new column to an existing table without writing the same idempotent-probe pattern by hand each time, and there's no record of which schema state any given prod DB is at. The next migration in any of these projects (e.g. the buchinese article user-scoping feature filed below) would re-invent the same probe-and-ALTER pattern again.

One package, six consumers — much better than six divergent copies.

Inventory of consumers.

project	schema entry point	tables (approx)	first table for genesis probe
authd	`src/db/schema.ts` `applySchema` → already replaced by `applyMigrations` in PR #14, but currently inlines the runner under `src/db/migrate.ts`. After this feature, authd imports the runner from the package and deletes its inline copy.	users, login_attempts, oauth_, mfa_	`users`
buchinese	`src/db/schema.ts:109-123` `applySchema`	sessions, oauth_pending, articles, article_tags, article_tokens, article_sentences, flashcards, flashcard_review_history	`sessions`
inventory	`src/db/schema.ts` `applySchema`	(audit during implementation)	TBD
nanodrop	`src/db/schema.ts:3` `initDb`	users, files, login_attempts	`users`
dashcam	`src/db/schema.ts:6` `initDb`	sessions, oauth_pending, uploads, processing_queue, clips	`sessions`
movement	`src/server/db/client.ts` (no central `applySchema`; DDL is split across `users.ts`, `login-attempts.ts`, etc.)	users, login_attempts, …	`users`

Each project's genesis migration (0001_init.sql) just captures whatever the current applySchema block emits, byte-for-byte; the runner then stamps it as applied on the first migrate-run against an existing prod DB and applies zero rows.

Package shape.

Repo: gitea.bchen.dev/brendan/sqlite-migrate (must be public so npm install from a non-authenticated context — e.g. a Dockerfile RUN npm ci inside a public CI runner — can fetch the tarball without injecting credentials). The repo is created fresh, not forked; it doesn't carry any of authd's history.
Package name: @bchen/sqlite-migrate (scoped name) or unscoped bchen-sqlite-migrate — implementer picks based on whichever installs cleanly across consumers; scoped names with custom registries get fussy when there's no .npmrc, so unscoped is the safer default for direct-URL installs. Document the chosen name in the package README.
License: MIT, to keep the public-repo decision frictionless.
Install form (consumers' package.json dependency):
```
"bchen-sqlite-migrate": "git+https://gitea.bchen.dev/brendan/sqlite-migrate.git#v0.1.0"
```
Use HTTPS + a version tag (not SSH, because Docker builds inside CI don't have SSH keys). Public repo means no token needed in the URL. Pin to a tag (#v0.1.0), not a branch, so consumers don't silently pick up breaking changes on the next npm install. Tags follow semver.
Exports (package.json "exports" map; ESM-only, matching the existing TS code):
- "." → the runner API: applyMigrations, listMigrations, readAppliedRows, stampMigration, plus the public types (MigrationFile, MigrationRow, MigrationSummary, ApplyOptions).
- "./cli" → optional helper that consumers can wire from their own tsx src/scripts/db-migrate.ts shim — a single runMigrateCli({ openDb, migrationsDir }) function so each consumer just writes a 5-line script that injects its config-loading function. The package does NOT include a default bin/ entry because the DB path / config loader differs per project (authd's loadConfig() ≠ buchinese's, etc.).
Build: ship pre-compiled .js + .d.ts so consumers don't need to wire tsx for the package itself; their consumer code can still be .ts. Use the existing tsc --emitDeclarationOnly + a tiny esbuild step to bundle to dist/. The "files" field in package.json limits the published surface to dist/, README.md, LICENSE.

Parameterization. The runner today hard-codes one thing that has to become configurable for cross-project use:

GENESIS_PROBE_TABLE = 'users' (src/db/migrate.ts:34 in authd). Replace with an ApplyOptions.genesisProbeTable?: string parameter (default 'users', to preserve authd's behavior when authd consumes the package). Each consumer passes its own first-table name from the table above. Without this, the runner would refuse to stamp a buchinese DB (no users table) and would try to re-run 0001_init.sql on top of an existing populated DB → instant failure on CREATE TABLE for tables that already exist.

The rest of the runner stays as-is — version regex, checksum behavior, transaction-per-migration, the rollback-on-failure semantics, and the WARN log line on genesis-stamp all remain identical.

Scope.

New repo + package
- Create gitea.bchen.dev/brendan/sqlite-migrate (public). Use tea repo create --name sqlite-migrate --owner brendan --init.
- Lift src/db/migrate.ts + tests/db/migrate.test.ts from authd; add genesisProbeTable option; preserve the 9 existing test specs and add three new ones:
  - probe-table-not-present + stampGenesis=false → first migration runs normally (no stamp).
  - genesisProbeTable=undefined defaults to 'users' (authd back-compat).
  - genesisProbeTable='sessions' correctly stamps a buchinese-shaped DB.
- Wire runMigrateCli({ openDb, migrationsDir, stampGenesis }) thin wrapper extracted from authd's src/scripts/db-migrate.ts (so consumers don't re-copy the 25-line CLI shim).
- Build pipeline: npm run build → dist/ with .js + .d.ts; npm test runs vitest.
- package.json with the chosen name, "type": "module", "main": "./dist/index.js", "types": "./dist/index.d.ts", peer-dep on better-sqlite3 (do NOT bundle it; consumers already have it pinned).
- Tag v0.1.0 after CI is green.
Adopt in authd (dogfood — first consumer, lowest risk because it already has the runner inlined)
- npm i bchen-sqlite-migrate@git+https://gitea.bchen.dev/brendan/sqlite-migrate.git#v0.1.0
- Delete src/db/migrate.ts (now in the package).
- Update src/scripts/_db-cli.ts + the three db-*.ts scripts to import from the package.
- Update tests/db/migrate.test.ts to import from the package OR delete it (package owns those tests now). Recommended: keep a thin authd-specific test that asserts 0001_init.sql is byte-stable, since checksum drift would break prod migrations.
- Verify npm run db:migrate against a fresh tempdir and against a pre-existing dev DB → both behave identically to pre-extraction.
Adopt in buchinese, inventory, nanodrop, dashcam, movement (one PR per project)
- Add the package dependency.
- Create src/db/migrations/0001_init.sql containing exactly the current applySchema body (or, for movement, the union of the split DDLs).
  - For buchinese: also bake in the sessions.expires_at ALTER + backfill (lines 111-122 of src/db/schema.ts) into 0001_init.sql — by the time this migration system lands, prod has already been through that one-shot, so the genesis stamp will skip executing 0001_init.sql on existing DBs anyway.
- Replace applySchema(db) (or the per-project equivalent) with a call to applyMigrations(db, migrationsDir, { stampGenesis: process.env.DB_MIGRATIONS_STAMP_GENESIS === '1', genesisProbeTable: '<project first table>' }).
- Add db:migrate, db:status, db:stamp npm scripts (copy from authd's package.json).
- Per-project sanity test: spin up an empty tempdir DB → applyMigrations → assert every expected table exists. And: pre-seed a DB with the current tables + no schema_migrations → applyMigrations → assert it stamps 0001 and applies nothing.
Per-deploy bootstrap doc (one-line in each project's README): "first prod boot after this PR should show WARN genesis-stamp: marked 0001_init as applied without executing followed by migrations: 1 applied, 0 pending in the container log. If you see CREATE TABLE … already exists instead, the genesis probe table is wrong — file a bug."
Cross-cut with the buchinese article-scoping feature filed below. Once this package is adopted in buchinese, the article user-scoping feature filed in this same features.md block can ship its schema delta as 0002_articles_authd_sub.sql instead of inventing a one-off ad-hoc ALTER pattern. The implementer should pick up this package adoption before the article-scoping migration so the new convention is in place.

Out of scope.

Publishing to the public npm registry. The user explicitly asked for direct Gitea URL installs; npm publish is a separate decision and adds release-key management.
Down-migrations / rollbacks. The authd implementation is forward-only on purpose (MigrationSummary has no rolled_back field). Adding rollback support adds operational complexity (which down do you run, how do you handle data loss) and isn't needed for any current project.
Multi-statement transactional safety beyond what better-sqlite3's db.exec() already provides. The runner wraps each migration in db.transaction(...) — if a migration mixes DDL and DML and the DML fails, the whole migration rolls back. That's enough.
Schema diffing or auto-generation. Each migration is hand-authored SQL. No ORM, no diff tool.
Postgres / MySQL support. SQLite-only, matching every consumer's current reality. If a future project lands on Postgres it'll use its own ecosystem (node-pg-migrate, drizzle-kit, etc.) — no reason to retrofit one runner across dialects.
A web UI for migration status. db:status is a CLI that prints a table.

Acceptance.

gitea.bchen.dev/brendan/sqlite-migrate is public, tagged v0.1.0, with a green CI run on the tag.
npm i git+https://gitea.bchen.dev/brendan/sqlite-migrate.git#v0.1.0 works from a fresh shell with no Gitea credentials (this is the public-repo proof).
authd's main branch consumes the package; its inline src/db/migrate.ts is deleted; all existing authd tests still pass; npm run db:migrate against a pre-existing prod-shaped DB produces the same WARN-stamp output as before extraction.
All five other SQLite projects have adopted the package, each with their own 0001_init.sql. For each project, a fresh DB and a pre-existing prod-shaped DB both reach the same final schema state under npm run db:migrate.
Document the install command + the DB_MIGRATIONS_STAMP_GENESIS env var in the package README, with a section per consumer project showing the exact applyMigrations call signature (including the right genesisProbeTable).

> **Originally filed:** 2026-05-11 in ~/features.md, block #2. > **Cross-project companion issues:** brendan/sqlite-migrate, brendan/authd, brendan/buchinese, brendan/inventory, brendan/dashcam, brendan/movement **2026-05-11 — cross-project: extract authd's SQLite migration system into a standalone npm package installable via direct Gitea URL, make the repo public, then adopt it in every project that uses SQLite (buchinese, inventory, nanodrop, movement, dashcam, and authd itself dogfoods). Replaces six independent hand-rolled `applySchema(db)` blobs with one versioned, checksum-verified, runner-tested implementation.**         **Motivation.** authd PR #14 (merge_commit `ced21ab4d16c28a6b5fafa9072b0ae8207ba4bfd`, merged 2026-05-11) landed a real migration system: numbered `.sql` files in `src/db/migrations/`, a `schema_migrations` tracking table, sha256 checksum verification, idempotent re-apply, genesis-stamping for pre-existing prod DBs, and three CLI scripts (`db:migrate` / `db:status` / `db:stamp`). The runner lives in `src/db/migrate.ts` (178 LOC, exports `applyMigrations`, `listMigrations`, `readAppliedRows`, `stampMigration`) and is exercised by `tests/db/migrate.test.ts` (270 LOC, 9 specs covering fresh-apply / idempotent / checksum-mismatch / genesis-stamp / partway-failure rollback). Meanwhile, every other SQLite-using project still ships a single `applySchema(db)` function that runs `CREATE TABLE IF NOT EXISTS` for every table on every boot and does ad-hoc ALTER patches when schema needs to evolve (e.g. `src/db/schema.ts:111-122` in buchinese for the `sessions.expires_at` retrofit). This is fine for a fresh DB but rots fast: there's no way to add a new column to an existing table without writing the same idempotent-probe pattern by hand each time, and there's no record of which schema state any given prod DB is at. The next migration in any of these projects (e.g. the buchinese article user-scoping feature filed below) would re-invent the same probe-and-ALTER pattern again. One package, six consumers — much better than six divergent copies. **Inventory of consumers.** | project | schema entry point | tables (approx) | first table for genesis probe | |---|---|---|---| | authd | `src/db/schema.ts` `applySchema` → already replaced by `applyMigrations` in PR #14, but currently *inlines* the runner under `src/db/migrate.ts`. After this feature, authd imports the runner from the package and deletes its inline copy. | users, login_attempts, oauth_*, mfa_* | `users` | | buchinese | `src/db/schema.ts:109-123` `applySchema` | sessions, oauth_pending, articles, article_tags, article_tokens, article_sentences, flashcards, flashcard_review_history | `sessions` | | inventory | `src/db/schema.ts` `applySchema` | (audit during implementation) | TBD | | nanodrop | `src/db/schema.ts:3` `initDb` | users, files, login_attempts | `users` | | dashcam | `src/db/schema.ts:6` `initDb` | sessions, oauth_pending, uploads, processing_queue, clips | `sessions` | | movement | `src/server/db/client.ts` (no central `applySchema`; DDL is split across `users.ts`, `login-attempts.ts`, etc.) | users, login_attempts, … | `users` | Each project's genesis migration (`0001_init.sql`) just captures whatever the current `applySchema` block emits, byte-for-byte; the runner then stamps it as applied on the first migrate-run against an existing prod DB and applies zero rows. **Package shape.** - **Repo:** `gitea.bchen.dev/brendan/sqlite-migrate` (must be **public** so `npm install` from a non-authenticated context — e.g. a `Dockerfile RUN npm ci` inside a public CI runner — can fetch the tarball without injecting credentials). The repo is created fresh, not forked; it doesn't carry any of authd's history. - **Package name:** `@bchen/sqlite-migrate` (scoped name) or unscoped `bchen-sqlite-migrate` — implementer picks based on whichever installs cleanly across consumers; scoped names with custom registries get fussy when there's no `.npmrc`, so unscoped is the safer default for direct-URL installs. Document the chosen name in the package README. - **License:** MIT, to keep the public-repo decision frictionless. - **Install form** (consumers' `package.json` dependency): ```json "bchen-sqlite-migrate": "git+https://gitea.bchen.dev/brendan/sqlite-migrate.git#v0.1.0" ``` Use HTTPS + a version tag (not SSH, because Docker builds inside CI don't have SSH keys). Public repo means no token needed in the URL. **Pin to a tag** (`#v0.1.0`), not a branch, so consumers don't silently pick up breaking changes on the next `npm install`. Tags follow semver. - **Exports** (`package.json` `"exports"` map; ESM-only, matching the existing TS code): - `"."` → the runner API: `applyMigrations`, `listMigrations`, `readAppliedRows`, `stampMigration`, plus the public types (`MigrationFile`, `MigrationRow`, `MigrationSummary`, `ApplyOptions`). - `"./cli"` → optional helper that consumers can wire from their own `tsx src/scripts/db-migrate.ts` shim — a single `runMigrateCli({ openDb, migrationsDir })` function so each consumer just writes a 5-line script that injects its config-loading function. The package does NOT include a default `bin/` entry because the DB path / config loader differs per project (authd's `loadConfig()` ≠ buchinese's, etc.). - **Build:** ship pre-compiled `.js` + `.d.ts` so consumers don't need to wire `tsx` for the package itself; their consumer code can still be `.ts`. Use the existing `tsc --emitDeclarationOnly` + a tiny `esbuild` step to bundle to `dist/`. The `"files"` field in `package.json` limits the published surface to `dist/`, `README.md`, `LICENSE`. **Parameterization.** The runner today hard-codes one thing that has to become configurable for cross-project use: - `GENESIS_PROBE_TABLE = 'users'` (`src/db/migrate.ts:34` in authd). Replace with an `ApplyOptions.genesisProbeTable?: string` parameter (default `'users'`, to preserve authd's behavior when authd consumes the package). Each consumer passes its own first-table name from the table above. Without this, the runner would refuse to stamp a buchinese DB (no `users` table) and would try to *re-run* `0001_init.sql` on top of an existing populated DB → instant failure on `CREATE TABLE` for tables that already exist. The rest of the runner stays as-is — version regex, checksum behavior, transaction-per-migration, the rollback-on-failure semantics, and the WARN log line on genesis-stamp all remain identical. **Scope.** 1. **New repo + package** - Create `gitea.bchen.dev/brendan/sqlite-migrate` (public). Use `tea repo create --name sqlite-migrate --owner brendan --init`. - Lift `src/db/migrate.ts` + `tests/db/migrate.test.ts` from authd; add `genesisProbeTable` option; preserve the 9 existing test specs and add three new ones: - probe-table-not-present + stampGenesis=false → first migration runs normally (no stamp). - genesisProbeTable=undefined defaults to `'users'` (authd back-compat). - genesisProbeTable=`'sessions'` correctly stamps a buchinese-shaped DB. - Wire `runMigrateCli({ openDb, migrationsDir, stampGenesis })` thin wrapper extracted from authd's `src/scripts/db-migrate.ts` (so consumers don't re-copy the 25-line CLI shim). - Build pipeline: `npm run build` → `dist/` with `.js` + `.d.ts`; `npm test` runs vitest. - `package.json` with the chosen name, `"type": "module"`, `"main": "./dist/index.js"`, `"types": "./dist/index.d.ts"`, peer-dep on `better-sqlite3` (do NOT bundle it; consumers already have it pinned). - Tag `v0.1.0` after CI is green. 2. **Adopt in authd (dogfood — first consumer, lowest risk because it already has the runner inlined)** - `npm i bchen-sqlite-migrate@git+https://gitea.bchen.dev/brendan/sqlite-migrate.git#v0.1.0` - Delete `src/db/migrate.ts` (now in the package). - Update `src/scripts/_db-cli.ts` + the three `db-*.ts` scripts to import from the package. - Update `tests/db/migrate.test.ts` to import from the package OR delete it (package owns those tests now). Recommended: keep a thin authd-specific test that asserts `0001_init.sql` is byte-stable, since checksum drift would break prod migrations. - Verify `npm run db:migrate` against a fresh tempdir and against a pre-existing dev DB → both behave identically to pre-extraction. 3. **Adopt in buchinese, inventory, nanodrop, dashcam, movement (one PR per project)** - Add the package dependency. - Create `src/db/migrations/0001_init.sql` containing exactly the current `applySchema` body (or, for movement, the union of the split DDLs). - For buchinese: also bake in the `sessions.expires_at` ALTER + backfill (lines 111-122 of `src/db/schema.ts`) into `0001_init.sql` — by the time this migration system lands, prod has already been through that one-shot, so the genesis stamp will skip executing `0001_init.sql` on existing DBs anyway. - Replace `applySchema(db)` (or the per-project equivalent) with a call to `applyMigrations(db, migrationsDir, { stampGenesis: process.env.DB_MIGRATIONS_STAMP_GENESIS === '1', genesisProbeTable: '<project first table>' })`. - Add `db:migrate`, `db:status`, `db:stamp` npm scripts (copy from authd's package.json). - Per-project sanity test: spin up an empty tempdir DB → `applyMigrations` → assert every expected table exists. And: pre-seed a DB with the current tables + no `schema_migrations` → `applyMigrations` → assert it stamps `0001` and applies nothing. 4. **Per-deploy bootstrap doc** (one-line in each project's README): "first prod boot after this PR should show `WARN genesis-stamp: marked 0001_init as applied without executing` followed by `migrations: 1 applied, 0 pending` in the container log. If you see `CREATE TABLE … already exists` instead, the genesis probe table is wrong — file a bug." 5. **Cross-cut with the buchinese article-scoping feature filed below.** Once this package is adopted in buchinese, the article user-scoping feature filed in this same `features.md` block can ship its schema delta as `0002_articles_authd_sub.sql` instead of inventing a one-off ad-hoc ALTER pattern. The implementer should pick up this package adoption *before* the article-scoping migration so the new convention is in place. **Out of scope.** - Publishing to the public npm registry. The user explicitly asked for direct Gitea URL installs; npm publish is a separate decision and adds release-key management. - Down-migrations / rollbacks. The authd implementation is forward-only on purpose (`MigrationSummary` has no `rolled_back` field). Adding rollback support adds operational complexity (which down do you run, how do you handle data loss) and isn't needed for any current project. - Multi-statement transactional safety beyond what better-sqlite3's `db.exec()` already provides. The runner wraps each migration in `db.transaction(...)` — if a migration mixes DDL and DML and the DML fails, the whole migration rolls back. That's enough. - Schema diffing or auto-generation. Each migration is hand-authored SQL. No ORM, no diff tool. - Postgres / MySQL support. SQLite-only, matching every consumer's current reality. If a future project lands on Postgres it'll use its own ecosystem (`node-pg-migrate`, `drizzle-kit`, etc.) — no reason to retrofit one runner across dialects. - A web UI for migration status. `db:status` is a CLI that prints a table. **Acceptance.** - `gitea.bchen.dev/brendan/sqlite-migrate` is public, tagged `v0.1.0`, with a green CI run on the tag. - `npm i git+https://gitea.bchen.dev/brendan/sqlite-migrate.git#v0.1.0` works from a fresh shell with no Gitea credentials (this is the public-repo proof). - authd's main branch consumes the package; its inline `src/db/migrate.ts` is deleted; all existing authd tests still pass; `npm run db:migrate` against a pre-existing prod-shaped DB produces the same WARN-stamp output as before extraction. - All five other SQLite projects have adopted the package, each with their own `0001_init.sql`. For each project, a fresh DB and a pre-existing prod-shaped DB both reach the same final schema state under `npm run db:migrate`. - Document the install command + the `DB_MIGRATIONS_STAMP_GENESIS` env var in the package README, with a section per consumer project showing the exact `applyMigrations` call signature (including the right `genesisProbeTable`).

brendan added the feature label 2026-05-13 19:02:01 +00:00

brendan closed this issue

2026-05-13 19:02:01 +00:00

brendan referenced this issue

2026-05-13 19:02:16 +00:00

persistent "stay signed in" session cookies across every bchen.dev app (both #18

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: brendan/nanodrop#16