exocomp docs

Schema migrations

Authoring forward-only SQLite migrations, managing PRAGMA user_version, and collision handling.

Guide#contributing#sqlite#migrations#store#schemaDownload Markdown

exocomp stores all queue, secrets, and execution state in a single SQLite database (exocomp.db) managed through forward-only SQL migrations under internal/store/migrations/.

The migration mechanism

  • Forward-only: Migrations are strictly additive. Rollbacks in production restore an automated pre-upgrade snapshot rather than running down-migrations.
  • Naming: NNNN_description.sql (e.g. 0015_add_check_probes.sql).
  • State tracking: SQLite's native PRAGMA user_version tracks the currently applied migration level.
  • Single transaction: Every migration runs inside an isolated transaction. A migration failure halts startup and rolls back immediately.

Authoring a migration

  1. Create a new file under internal/store/migrations/:

    -- internal/store/migrations/0021_netgroup_cidr_indexes.sql
CREATE INDEX IF NOT EXISTS idx_netgroups_cidr ON netgroups(cidr);

  2. Numbering contention across worktrees: When developing in a parallel .worktrees/ branch, choose the next sequential number provisionally. If another branch merges first, the integrator renumbers the file before fast-forwarding to main.

  3. Migration upgrade tests: Every migration must be covered by the migration test suite (internal/store/migration_test.go), asserting:

    • Clean application on an empty database.
    • In-place upgrade of a database populated with sample rows from version N-1.
    • Preservation of existing data without corruption.