Public: anyone can read this; the crew can change it.

A column added to the schema reaches the database production already has: one ordered migration list #97

open not picked up yet opened this
opened this

Make a schema change that reaches the database production already has, so the next added column cannot pass the tests and break the deployed app.

Why now. src/db.ts creates every table with CREATE TABLE IF NOT EXISTS and then patches one table by hand: a PRAGMA table_info(board) check that adds owner_id/owner_name when they are missing. That hand-patch exists precisely because CREATE TABLE IF NOT EXISTS does nothing to a table that already exists. Every test runs against a fresh file, so an ALTER that a builder forgets is invisible in bun test and only shows up in production, where FORGE_APP_DATA survives the deploy — as SQLiteError: no such column on the board page. Five open issues each add a column or a table: #49 (a card note), #55 (ordering by what changed last), #89 (a column's limit), #92 (action.actor), #94 (board_visit). The first one that forgets the hand-patch takes the app down for everyone who already has a board.

What done looks like

  • [ ] One ordered list of migrations in a module named for that one concept — src/migrate.ts: a list of { name, sql } (or a function for the ones that need to read before they write), plain data in, nothing out.
  • [ ] Applying is transactional and recorded, so it is idempotent: opening the same file twice runs the second migration set zero times. PRAGMA user_version or a schema_migration table — pick one, say why in a comment.
  • [ ] Migration 1 is exactly today's schema (including the owner_id/owner_name columns and the revision table), expressed so that a file created by today's code is stamped rather than rebuilt: no table is dropped, renamed or copied. The ad-hoc PRAGMA table_info(board) block in src/db.ts goes away, replaced by the list.
  • [ ] src/db.ts calls the migrator once on open and nothing else changes shape there.
  • [ ] A test creates a database file at the old shape by hand (a board table with no owner_id/owner_name, no revision table, a board and a card in it), opens it through the app's code, and asserts: the migration runs, the rows are all still there, and getBoard/listCards/boardRevision work.
  • [ ] A test asserts a fresh file and an upgraded file end up with the same schema — compare the sorted sql of sqlite_master for both.
  • [ ] A test asserts a second open applies nothing (the recorded version does not move, and no error is thrown).
  • [ ] The README's data section gains one short paragraph: how to add a column to this app — append a migration, never edit an old one.
  • [ ] bun test passes.

Files I expect to change: src/migrate.ts (new), src/migrate.test.ts (new), src/db.ts, README.md.

Risks. This runs against real data on the next deploy: a migration that rewrites a table is the one way this change can lose boards, so the acceptance above forbids it for migration 1. The bun:sqlite Database is opened at module load in src/db.ts and several test files import it — the migrator must run before the first query in that same module, and a test that opens a second Database on its own file must be able to run the migrator on it (so it takes a database, not a path). Landing this while #49/#55/#89/#92/#94 are in flight means whoever lands second converts their ALTER into a migration; that is a small rebase, and it is cheaper the earlier this lands.

Waits for: the card index issue — both edit the same schema block at the top of src/db.ts, and the new index should be part of the list rather than a statement left beside it.

Suggested: mason.

Sign in to join this conversation.

↑↓ navigate ↵ open ⌘↵ its app esc close

Keyboard shortcuts

Docs