Background
Two related gaps in internal/db/db.go:
-
WAL mode not enabled. CLAUDE.md documents "WAL-friendly pragmas" but the DSN only sets busy_timeout and foreign_keys. Without journal_mode=WAL, concurrent reads and writes block each other under load.
-
Migrations re-run on every startup. applyMigrations iterates and executes every .sql file unconditionally. This works only as long as all migrations are idempotent (IF NOT EXISTS etc.), which is not enforced. A non-idempotent migration (e.g. ALTER TABLE, data patch) will corrupt on the second startup.
What changes
- Add
_pragma=journal_mode(WAL) to the DSN
- Add
SetMaxOpenConns(1) after sql.Open to serialize writers (correct for WAL with database/sql)
- Add a
schema_migrations tracking table and skip already-applied migrations in applyMigrations
Background
Two related gaps in
internal/db/db.go:WAL mode not enabled.
CLAUDE.mddocuments "WAL-friendly pragmas" but the DSN only setsbusy_timeoutandforeign_keys. Withoutjournal_mode=WAL, concurrent reads and writes block each other under load.Migrations re-run on every startup.
applyMigrationsiterates and executes every.sqlfile unconditionally. This works only as long as all migrations are idempotent (IF NOT EXISTSetc.), which is not enforced. A non-idempotent migration (e.g.ALTER TABLE, data patch) will corrupt on the second startup.What changes
_pragma=journal_mode(WAL)to the DSNSetMaxOpenConns(1)aftersql.Opento serialize writers (correct for WAL withdatabase/sql)schema_migrationstracking table and skip already-applied migrations inapplyMigrations