Skip to content

Persistence Migrations

Opinionated guide for touching the persistence layer. Read before editing anything under src/synthorg/persistence/.

The Rule

src/synthorg/persistence/ is the only place in SynthOrg that may import aiosqlite, sqlite3, psycopg, or psycopg_pool, or emit raw SQL DDL/DML keywords in string literals.

Two files are sanctioned exceptions:

  • src/synthorg/tools/database/schema_inspect.py -- agent-facing introspection tool; returns arbitrary DB metadata the repository abstraction does not expose.
  • src/synthorg/tools/database/sql_query.py -- agent-facing arbitrary-SQL tool; the SQL string itself is the payload, so it cannot ride the repository pattern.

Enforced by scripts/check_persistence_boundary.py in pre-push and CI Lint. Opt-out on a single line with a trailing # lint-allow: persistence-boundary -- <reason> comment (the justification after -- is required).

Happy Path -- Adding a New Durable Table

  1. Define the repository Protocol in src/synthorg/persistence/<domain>_protocol.py. Inherit from typing.Protocol, decorate with @runtime_checkable, and keep the method surface thin -- CRUD only. Composite orchestration (search, manifest assembly) belongs in a service layer, not the repository.

  2. Implement both backends.

  3. src/synthorg/persistence/sqlite/<entity>_repo.py -- SQLite<Name>Repository(db: aiosqlite.Connection).
  4. src/synthorg/persistence/postgres/<entity>_repo.py -- Postgres<Name>Repository(pool: AsyncConnectionPool).

Use # noqa: TC001 on runtime-needed type imports; Pydantic v2 evaluates annotations at runtime.

  1. Edit BOTH schema files in lockstep.
  2. src/synthorg/persistence/sqlite/schema.sql
  3. src/synthorg/persistence/postgres/schema.sql

Keep types Postgres-native (JSONB, TIMESTAMPTZ, BIGINT, BOOLEAN) on the Postgres side and SQLite-native (TEXT, INTEGER, REAL) on the SQLite side. Monetary columns carry a sibling currency TEXT NOT NULL with a CHECK constraint.

  1. Generate migrations with Atlas.
atlas migrate diff --env sqlite  <name>
atlas migrate diff --env postgres <name>   # requires Docker

Never hand-edit the generated files. Never edit atlas.sum.

  1. Expose on PersistenceBackend in src/synthorg/persistence/protocol.py. Add a @property. Wire instantiation into both SQLitePersistenceBackend._create_repositories() and PostgresPersistenceBackend._create_repositories().

  2. Write conformance tests under tests/conformance/persistence/test_<entity>_repository.py. Use the existing backend fixture in conftest.py -- it parametrizes over ["sqlite", "postgres"] and applies migrations fresh per test. Postgres arm auto-skips when Docker is unavailable.

What You Must Not Do

  • Never import aiosqlite / sqlite3 / psycopg / psycopg_pool outside src/synthorg/persistence/.
  • Never hand-edit src/synthorg/persistence/{backend}/revisions/*.sql. The files are Atlas output; changes go via atlas migrate diff --env <env> <name>.
  • Never edit atlas.sum manually. It is regenerated by atlas migrate hash only during a sanctioned squash.
  • Never run atlas migrate hash on a released revisions directory. It rewrites checksums for committed files.
  • Never land more than one new migration file per PR per backend. Enforced by scripts/check_single_migration_per_pr.sh. If you find yourself wanting two, consolidate them: rm the in-progress .sql files and regenerate with atlas migrate diff --env <env> <name>.
  • Never reach for persistence._db or persistence._pool via getattr to smuggle driver primitives across the boundary. Expose a method on the backend instead (see build_escalations, build_lockouts for the pattern).

For AI Agents

If your change touches persistence and you feel an urge to import aiosqlite outside src/synthorg/persistence/, stop. That is the symptom the boundary is designed to catch. Instead:

  1. Ask whether this operation belongs in a repository. Almost always, yes.
  2. If the operation is genuinely outside the repository contract (an agent tool, a migration-time script, a diagnostic probe), propose adding the path to _ALLOWLIST in scripts/check_persistence_boundary.py with a justifying comment, and surface the proposal to the user before committing.
  3. For one-off lines (a test fixture, a legacy caller being incrementally migrated), use the inline marker:
import aiosqlite  # lint-allow: persistence-boundary -- fixture bootstrap

The justification text after -- is non-empty by design so the opt-out is auditable.

Squash Procedure

A full single-baseline squash collapses every historical revision into one 00000000000000_baseline.sql per backend, derived from the current schema.sql. Run rarely -- the revision history is the audit trail between squashes.

Per backend (sqlite first, then postgres):

# 1. Verify clean state before squashing.
atlas migrate validate --dir "file://src/synthorg/persistence/sqlite/revisions"
atlas schema diff --env sqlite   # expect "Schemas are synced"

# 2. Remove every revision except __init__.py, and atlas.sum.
rm src/synthorg/persistence/sqlite/revisions/*.sql
rm src/synthorg/persistence/sqlite/revisions/atlas.sum

# 3. Regenerate the single baseline from schema.sql.
atlas migrate diff --env sqlite baseline

# 4. Rename to sort before any future migration.
mv src/synthorg/persistence/sqlite/revisions/<timestamp>_baseline.sql \
   src/synthorg/persistence/sqlite/revisions/00000000000000_baseline.sql

# 5. Recompute atlas.sum under the new filename.
atlas migrate hash --dir "file://src/synthorg/persistence/sqlite/revisions"

# 6. Validate and drift-check.
atlas migrate validate --dir "file://src/synthorg/persistence/sqlite/revisions"
atlas schema diff --env sqlite

Repeat for postgres. Commit with the bypass env var so the single-migration-per-PR hook accepts the squash:

SYNTHORG_MIGRATION_SQUASH=1 git commit -m "chore(persistence): squash revisions to baseline"

After squashing, check-no-modify-migration treats 00000000000000_baseline.sql as the one protected file going forward.

Troubleshooting

atlas migrate validate reports checksum mismatch → Someone edited a revision file or atlas.sum by hand. Run git status to find the diff; revert to origin. If the change was intentional, regenerate with atlas migrate diff rather than hand-editing.

atlas schema diff reports drift → schema.sql and the applied migrations disagree. Either regenerate the last migration (git restore the stale one, run atlas migrate diff fresh) or backfill the missing DDL in a new migration.

atlas migrate diff --env postgres hangs → Postgres dev URL resolution needs Docker running. Start Docker Desktop, then retry. Override with POSTGRES_DEV_URL=docker://postgres/18/dev if the default needs a tweak.

atlas migrate hash fails on a missing atlas.sum → Expected during the squash procedure (step 2 deletes it). Run step 5 to regenerate.

Pre-push persistence-boundary hook fires → Legitimate agent tool: add the path to _ALLOWLIST in scripts/check_persistence_boundary.py with a justifying comment and surface the diff to a reviewer. One-off test fixture: prefer the inline # lint-allow: persistence-boundary -- <reason> marker (per-line auditable) over growing the allowlist silently.

Referenced from CLAUDE.md, docs/design/persistence.md, and the header comment of every revision file added post-squash.