""" auth/migrations.py — One-time migration to add UAT audit enrichment columns. Run once after deploying this change: python auth/migrations.py Safe to re-run — uses IF NOT EXISTS for PostgreSQL. For SQLite (local dev), falls back to a try/except approach. """ import os import sys from pathlib import Path # Ensure project root is on path so dotenv and auth package resolve correctly project_root = Path(__file__).resolve().parent.parent sys.path.insert(0, str(project_root)) from dotenv import load_dotenv load_dotenv() from auth.database import engine def run_migrations(): """Add extra_data and session_duration_seconds columns to audit_logs if not present.""" with engine.connect() as conn: dialect = engine.dialect.name print(f"[Migration] Connected to database (dialect: {dialect})") if dialect == "postgresql": # PostgreSQL supports IF NOT EXISTS statements = [ "ALTER TABLE audit_logs ADD COLUMN IF NOT EXISTS extra_data TEXT;", "ALTER TABLE audit_logs ADD COLUMN IF NOT EXISTS session_duration_seconds INTEGER;", ] for sql in statements: try: conn.execute(__import__("sqlalchemy").text(sql)) conn.commit() print(f"[Migration] OK: {sql.strip()}") except Exception as e: print(f"[Migration] WARN (may already exist): {e}") elif dialect == "sqlite": # SQLite does not support IF NOT EXISTS on ALTER TABLE for col_name, col_def in [ ("extra_data", "TEXT"), ("session_duration_seconds", "INTEGER"), ]: try: from sqlalchemy import text, inspect inspector = inspect(engine) existing = [c["name"] for c in inspector.get_columns("audit_logs")] if col_name not in existing: conn.execute(text(f"ALTER TABLE audit_logs ADD COLUMN {col_name} {col_def}")) conn.commit() print(f"[Migration] Added column: {col_name} ({col_def})") else: print(f"[Migration] Column already exists, skipping: {col_name}") except Exception as e: print(f"[Migration] WARN: {e}") else: print(f"[Migration] Unsupported dialect '{dialect}'. Please add columns manually:") print(" ALTER TABLE audit_logs ADD COLUMN extra_data TEXT;") print(" ALTER TABLE audit_logs ADD COLUMN session_duration_seconds INTEGER;") return print("[Migration] Done. All UAT audit columns are in place.") if __name__ == "__main__": run_migrations()