ten31-database/docs/guides/migrations.md

paths

paths
backend/migrations/** backend/core_migrations.py backend/thesis_seed.py

Migrations & seeders

Read this before adding or editing a schema migration or a one-time seed/backfill.

How they run

• Migrations apply automatically at startup via backend/core_migrations.py, reading backend/migrations/NNNN_*.sql in order and tracking applied files in a schema_migrations ledger.
• One-time seeds/backfills live in backend/thesis_seed.py (the ensure_* functions), wired into server.init_db() and run on every boot.

Rules

• Additive + reversible only. Numbered NNNN_*.sql with a paired NNNN_*.down.sql — ship the .down.sql with every new migration. SQLite ALTER is add-column / rename only; no drop-column, no type change.
• Seeds/backfills must be idempotent via interaction_log sentinels (the ensure_* pattern) — safe to re-run on every boot.
• Make migrations/seeders deployment-state-invariant. Target rows structurally, not by transient text the same change mutates; capture prior state so a revert is exact.
  • Learned the hard way: matching old nodes by a body string the same changeset deleted broke fresh DBs. A migration must produce the same end state whether the box is empty, mid-version, or fully seeded.
• Soft-delete only — deleted_at and/or status='retired'; never hard-delete CRM records or thesis history.

Verify before shipping

• python3 -m py_compile the edited Python.
• For any DB logic, run the change against a copy of data/crm.db, never production. Confirm the paired .down.sql cleanly reverts.