Imported from Ashish54/model_documentation_llm (.github/skills/db-migration-safety/SKILL.md). Install upstream with npx skills add Ashish54/model_documentation_llm --skill db-migration-safety. Copyright stays with the author.

Database Migration Safety

The data model is append-only by contract (ADR 0001). A migration that rewrites or drops historical evidence is a governance incident, not a bug.

Rules

1. Additive only. New tables and nullable columns are fine. Never drop or reshape artifact_version, source_locator, extraction_run, llm_interaction, *_evidence, or any existing version row.
2. Portable DDL. Use JSONBCompat (not raw JSON/JSONB), VARCHAR enums (native_enum=False), and the naming convention already on Base.metadata.
3. Break FK cycles with ForeignKey(..., use_alter=True, name=...) — PostgreSQL rejects mutually-dependent CREATE TABLEs (see SchemaVersion.proposal_run_id).

Steps

1. Edit the ORM in src/modelkb/db/models/ following the existing versioning patterns (identity+version tables, or append-only ref+is_current rows).
2. Autogenerate: .venv/bin/alembic -x dburl=sqlite:///var/_scratch.db revision --autogenerate -m "<change>". Done when the revision file exists.
3. Read the generated migration line by line — autogenerate misses intent. Verify: no drops of historical tables; modelkb.db.types is imported (the mako template adds it — keep it); indexes match the query patterns.
4. Round-trip: upgrade head → downgrade -1 → upgrade head on scratch SQLite. Done when all three succeed.
5. Extend tests/integration/test_migration.py for the new structure. Done when pytest, ruff, and mypy all pass.

Gotchas

• UUID primary keys need uuid.UUID(...) objects on SQLite (session.get with a raw string raises).
• Re-ingestion must stay idempotent: changing what feeds a deterministic ID (core/ids.py) orphans existing evidence rows — treat those inputs as frozen.