Database & Migrations

This is a condensed version of orchestration/include/db/docs/MIGRATION_GUIDE.md (full detail there) plus a summary of the MIGRATION_004_AUDIT_SUMMARY.md findings. Schema is defined by the SQLModel classes in orchestration/include/db/src/db/models/ — see Packages → db.

Infrastructure

Airflow Orchestration Layer
├── db_migrations (safe, incremental)
└── db_rollback   (DESTRUCTIVE — can drop all data)

Azure PostgreSQL Flexible Server (TimescaleDB)
├── Hypertables (time-series): purchase, sale, inventory, import, export
├── Reference tables: permit, report_period
└── Application tables: report_job, companies, dashboard_users, ...

Migrations live in orchestration/include/db/src/db/alembic/versions/, run via orchestration/include/db/src/db/migrate.py.

Running a migration

Trigger the db_migrations DAG (manual trigger only) from the Airflow UI with a config, or accept the default:

Config	Effect
`{}`	Upgrade by +1 revision (safe default)
`{"target": "head"}`	Upgrade to the latest revision
`{"target": "+3"}`	Upgrade by 3 revisions
`{"direction": "downgrade", "target": "-1"}`	Downgrade by 1 revision
`{"target": "<revision-id>"}`	Go to a specific revision

The DAG connects via PostgresHook(postgres_conn_id=PG_CONN_ID) (environment-dependent connection ID) and calls db.run_migrations(url, revision=direction, target=target).

Pre-flight checklist: review the migration’s SQL, check SELECT version_num FROM alembic_version; for current state, check for active connections, take a snapshot, and notify the team before running against production.

Rollback strategy

Prefer db_migrations with direction: downgrade, target: -1 for a safe, single-step rollback — each migration’s downgrade() only touches the objects its own upgrade() created, leaving all other tables and data untouched. Verify afterward:

SELECT version_num FROM alembic_version;  -- back to the prior revision
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public' AND table_name = '<dropped_table>';  -- 0 rows

The separate db_rollback DAG is destructive — its default (target: "-1") is a safe single-step downgrade, but {"target": "base"} drops all tables and data (all migrations’ downgrade() run in reverse). Only use target: base for local/staging resets or disaster recovery with a fresh backup — never for a production bug fix or schema adjustment. db_rollback can optionally re-upgrade to head afterward via skip_upgrade: false, but defaults to skipping that step for safety.

Migration 004 audit notes

A 2026-07-09 audit of migration 004 (branch feat/xml_reporting_view) standardized several conventions going forward, now reflected in the current models:

Plural table names (companies, dashboard_users, company_permits, company_users) instead of singular
UUID7 (time-sortable) primary keys via uuid_utils.uuid7, using PostgreSQL native postgresql.UUID(as_uuid=True) columns
Consistent field naming (user_id) across dashboard_users and report_job
Fixed a revision-ID header typo in migration 003

See MIGRATION_004_AUDIT_SUMMARY.md for the full field-by-field audit if working on a new migration that touches these tables.