"""Apply numbered SQL migrations once, atomically and under a DB lock.""" from pathlib import Path import sys import psycopg sys.path.insert(0, str(Path(__file__).resolve().parents[1])) from src.db import database_url def main() -> None: migration_dir = Path(__file__).resolve().parents[1] / "migrations" with psycopg.connect(database_url(), connect_timeout=5, options="-c statement_timeout=30000") as conn: with conn.transaction(): conn.execute("SELECT pg_advisory_xact_lock(2026, 2)") conn.execute( "CREATE TABLE IF NOT EXISTS schema_migrations (" "version integer PRIMARY KEY, applied_at timestamptz NOT NULL DEFAULT clock_timestamp())" ) for path in sorted(migration_dir.glob("[0-9][0-9][0-9]_*.sql")): version = int(path.name.split("_", 1)[0]) if conn.execute("SELECT 1 FROM schema_migrations WHERE version=%s", (version,)).fetchone(): continue conn.execute(path.read_text(encoding="utf-8")) conn.execute("INSERT INTO schema_migrations (version) VALUES (%s)", (version,)) if __name__ == "__main__": main()