Automatic Schema Refactoring with LLMs

Refactoring application code is routine, but refactoring a database schema often strikes fear into engineering teams. A simple column rename can break reports, crash APIs, and lock tables for hours.

Traditionally, this required manual script writing and high-stress deployment windows. In this guide, we will explore how AI agents can act as autonomous schema architects, planning and executing complex migrations safely.

The Concept: "Schema-Aware" AI Agents

A standard LLM knows SQL syntax, but a "Schema-Aware" agent understands the consequences of SQL. It combines knowledge of the database structure (DDL) with an understanding of the application code that uses it.

This context allows the agent to predict downstream breakages. Instead of just writing a migration script, it generates a full "migration strategy" that includes backfilling data and updating application logic.

Architecture: The 4-Step Migration Engine

To automate refactoring safely, we need an agentic workflow that mimics a senior DBA's thought process.

1. Analysis (The Inspector)

The agent scans the current database schema and the application codebase (e.g., ORM models or SQL files). It builds a dependency graph mapping every table column to the API endpoints or functions that query it.

2. Planning (The Strategist)

Based on the user's intent (e.g., "Normalize the customer table"), the agent proposes a multi-step migration plan. It chooses the safest strategy, such as the "Expand and Contract" pattern, to ensure zero downtime.

3. Execution (The Builder)

The agent generates the specific SQL commands (DDL) and the data migration scripts (DML). It wraps these in a transaction block or a migration tool format like Flyway or Liquibase.

4. Verification (The Auditor)

Before applying changes to production, the agent spins up a shadow database (a clone of prod). It runs the migration and executes a suite of regression tests to verify that performance remains stable and data is not lost.

Workflow 1: The "Split Table" Normalization

Legacy databases often suffer from "god tables" that contain too many unrelated columns. This workflow breaks a monolithic table into smaller, normalized tables without stopping the app.

1. Intent: The developer asks the agent to "Move address fields from users to a new user_addresses table."
2. Analysis: The agent identifies columns street, city, zip in the users table and finds 15 queries referencing them.
3. Step 1 (Expand): The agent generates SQL to create the user_addresses table and sets up a database trigger (or dual-write logic) to copy new data to both tables.
4. Step 2 (Migrate): It generates a background script to backfill existing data from users to user_addresses in small batches to avoid locking.
5. Step 3 (Contract): Once verified, the agent generates a PR for the application code to read from the new table and drops the old columns in a subsequent deployment.

Workflow 2: The "Type Safety" Fix

Changing a column data type (e.g., INT to BIGINT or VARCHAR to JSONB) is risky on large tables. This workflow handles the transition gracefully.

1. Trigger: An overflow alert warns that the order_id (INT) is reaching its maximum value.
2. Strategy: The agent recognizes that a direct ALTER COLUMN will rewrite the table and lock it for hours.
3. Action: It proposes adding a new column order_id_new (BIGINT).
4. Synchronization: The agent creates a trigger to sync values from old to new.
5. Swap: After backfilling, the agent performs an atomic rename: renaming the old column to order_id_deprecated and the new one to order_id.

Workflow 3: Drift Detection and Reconciliation

Over time, the actual database schema often drifts from the definition in the codebase (e.g., manual hotfixes applied by DBAs).

1. Scan: The agent periodically snapshots the production information_schema.
2. Compare: It uses an LLM to semantically compare the live schema against the repository's migration history or ORM definitions.
3. Alert: It detects that an index idx_customer_email exists in Prod but is missing from Git.
4. Resolution: The agent automatically opens a Pull Request to add the missing index definition to the codebase, ensuring the "Infrastructure as Code" promise is kept.

Safety Guardrails

Automating DDL requires strict safety mechanisms.

• Dry Runs: The agent must always output a "plan" (like Terraform) explaining exactly what it will do before doing it.
• Lock Timeout Limits: All generated migration scripts should include SET lock_timeout to prevent bringing down the production database if a lock cannot be acquired instantly.
• Rollback Generation: For every migration script, the agent must generate a corresponding, tested rollback script.

Conclusion

Automatic schema refactoring shifts database evolution from a "scheduled maintenance event" to a continuous, fluid process. By leveraging AI to handle the complexity of dependencies and data movement, teams can pay down technical debt aggressively without risking availability.

The database is no longer a static monument; it is a flexible software component that evolves as fast as your code.