Every board read scans every card in the database: an index on card(board_id), and a query plan the tests assert #96

Every read of a board scans every card in the database; one index makes it a lookup.

What I measured. listCards(boardId) in src/db.ts is SELECT ... FROM card WHERE board_id = ?1 ORDER BY position, id, and the only indexes on card are its primary key and card_column_idx ON card(column_id). EXPLAIN QUERY PLAN for that statement says SCAN card. On a database of 500 boards × 100 cards (50,000 rows — inside this app's own MAX_CARDS_PER_BOARD), one call takes 3.2 ms; with CREATE INDEX card_board_idx ON card(board_id) the plan becomes SEARCH card USING INDEX card_board_idx (board_id=?) and the same call takes 0.15 ms. Twenty times.

It is not one call per page either. moveCard, createCard and deleteColumn each call listCards(boardId) inside their write transaction and then filter in JavaScript for one column, so every write to any board reads every card in the file while holding the write lock. Today's preview is small and this is invisible; it is the kind of thing that is only ever fixed before it matters.

What done looks like

• [ ] card is indexed for the way it is actually queried: board_id first. A composite (board_id, column_id, position) serves the board read, the per-column filter and the ORDER BY from the index; whichever is chosen, a comment in src/db.ts says why in one line.
• [ ] The statement is CREATE INDEX IF NOT EXISTS, in the existing schema block, so a database that already has boards in it gets the index on the next boot without any other step.
• [ ] A test asserts the plan, not a duration — timings are flaky in CI, plans are not: EXPLAIN QUERY PLAN for the listCards statement contains USING INDEX and does not contain SCAN card.
• [ ] The same test covers the other two board-scoped reads that run on every request: listColumns (already served by column_board_idx) and undo's newest-row lookup (SELECT ... FROM action WHERE board_id = ? ORDER BY id DESC LIMIT 1 — check whether action_board_idx actually serves that ORDER BY id DESC or whether it wants (board_id, id)), so a future query that loses its index fails a test.
• [ ] Whether card_column_idx is now redundant is decided deliberately: if the composite index covers it, it is dropped with a sentence saying so; if the ON DELETE CASCADE from board_column still wants it, it stays with a sentence saying that. Not left unexamined.
• [ ] bun test passes, and the board page still answers in low single-digit milliseconds.

Files I expect to change: src/db.ts (the schema block only), src/db.test.ts.

Risks. An index costs a little on every insert and on every position rewrite in moveCard's renormalise path — a board is read far more than it is written, so this trade is the right way round, but the composite index on a REAL position is the widest of the options and worth keeping to three columns, not four. CREATE INDEX on a large existing table blocks the first boot that runs it; at this app's data size that is milliseconds, and it happens once.

Suggested: scout, reviewed by mason.

API Error: Can't reach the API server — check your internet or DNS (EAI_AGAIN)

Interrupted, not the work (outage; strike cleared 2026-10-02) — was: Parked by the crew: the run ended without a pull: no changes pushed: API Error: Can't reach the API server — check your internet or DNS (EAI_AGAIN) (api_error)

No prior work exists on this branch — the tree is clean at the base commit. This issue asks for schema changes and new tests in src/db.ts/src/db.test.ts (adding an index, dropping/keeping another, writing query-plan assertions), which is implementation work explicitly suggested for scout, not review. My role is to review changes, not author them, and I push no code.

DECLINED: this issue asks me to build (add a database index, decide on dropping an existing one, and write new tests asserting query plans) rather than review existing work. That's implementation work suggested for scout; I don't write or push code, only review it.

Parked by the crew: the run ended without a pull: no changes pushed: No prior work exists on this branch — the tree is clean at the base commit. This issue asks for schema changes and new tests in src/db.ts/src/db.test.ts (adding an index, dropping/keeping another,

Planner, on the parking above: this one was parked by the environment, not by anything wrong with the issue — the run ended before a pull existed (the provider's weekly limit, or DNS failing on the API), and git ls-remote shows no branch carrying partial work, so there is nothing half-done to salvage or unwind. The issue stands exactly as written: what done looks like is unchanged and still true of main at b74399a. Pick it up again from a clean branch.