Biswajeet Arukha

Proposal: Architecture for AI-Assisted "Zero-Downtime" Migration Guardrails Implementing a "Pre-flight check" is critical for Supabase, especially as projects scale to large production tables where a single ALTER TABLE can lock out the entire application. Here is a proposed architectural framework for how this engine should work: 1. The "DDL Footgun" Detection Engine The core of the pre-flight check should focus on Postgres Lock Hierarchy. The engine must flag any operation that requests an ACCESS EXCLUSIVE lock on a table exceeding a certain size/row count. Mandatory Concurrent Checks: Flag any CREATE INDEX that lacks the CONCURRENTLY keyword. Phased Constraint Validation: Flag ADD CONSTRAINT (Foreign Keys/Check) that aren't marked NOT VALID. The engine should suggest a two-step migration: ADD CONSTRAINT ... NOT VALID (Fast lock) VALIDATE CONSTRAINT (Scan without blocking writes) 2. Automated "Safety Wrappers" The AI assistant shouldn't just "detect"; it should "remediate" by injecting safety headers into the migration files. Every migration analyzed by the pre-flight check should be suggested to include: sql -- AI Suggested Safety Header SET statement_timeout = '30s'; SET lock_timeout = '5s'; -- Prevents the migration from queuing and blocking the app 3. LLM "Semantic Linting" Strategy Traditional linters (like Squawk) are great for syntax, but an AI-assisted check can understand intent. The AI Agent should be prompted with a "Danger Checklist": "Is this migration rewriting a 10M+ row table?" "Is there a default value being added to an existing column that triggers a full table rewrite in this PG version?" "Are we dropping a column that is still being referenced in the edge function code?" (Requires cross-repo analysis). 4. CLI Integration (supabase migration check) The workflow should be baked into the CLI. Before a supabase db push, the user runs a check: bash supabase migration check --ai --target production The output would provide a Risk Score (1-10) and a Lock Analysis Table, showing exactly which tables will be locked and for how long based on metadata statistics from the production branch. 5. Proposed "Checklist" for the MVP Index Audit: Detect non-concurrent index creation. Type Mutation Audit: Flag column type changes that require table rewrites (e.g., varchar to int). Nullability Audit: Flag adding NOT NULL to columns with existing data without a default value. Lock Duration Estimation: Use pg_stat_user_tables to estimate rewrite time. 🏁 Why this approach works: By combining Static SQL Analysis with LLM-based context, Supabase can move from "Reactive" fixing to "Proactive" prevention. POC: Simple Migration Linter (Python) Here is a snippet of how the logic could be implemented to detect "Locking Footguns" before they hit production: python import re def pre_flight_migration_check(sql_content): issues = [] # Check for non-concurrent index creation if re.search(r"CREATE INDEX", sql_content, re.I) and not re.search(r"CONCURRENTLY", sql_content, re.I): issues.append({ "risk": "CRITICAL", "msg": "Found 'CREATE INDEX' without 'CONCURRENTLY'. This will lock the table for writes.", "fix": "Use 'CREATE INDEX CONCURRENTLY' instead." }) # Check for Access Exclusive lock triggers exclusive_triggers = ["ALTER TABLE", "DROP TABLE", "TRUNCATE"] for trigger in exclusive_triggers: if re.search(rf"{trigger}", sql_content, re.I): issues.append({ "risk": "HIGH", "msg": f"Detected {trigger} which requires an Access Exclusive lock.", "fix": "Ensure 'lock_timeout' is set before running this migration." }) return issues # Example Usage migration_sql = "CREATE INDEX idx_user_email ON users(email);" report = pre_flight_migration_check(migration_sql) print(report)