This topic pulls everything from the module together into one continuous workflow: a feature requirement lands, you inspect the current schema with an agent, plan a change, generate a reviewed migration, apply it, and verify the resulting query plans actually improved before calling it done. The example is a real pattern — adding a status-history table to an orders system that currently only tracks current status — chosen because it touches schema design, migration safety, and index verification in one coherent piece of work.
Workflow Overview: From Feature Requirement to Deployed Schema Change
The requirement: product wants an order timeline view showing every status transition with a timestamp, currently impossible because orders.status is a single mutable column with no history. The end-to-end workflow, with the agent doing the drafting at each step and a human reviewing before anything touches a real database:
1. Inspect current schema + confirm the gap (no history table exists)
2. Design the new table + decide backfill strategy for existing orders
3. Generate the forward + down migration, human reviews the SQL
4. Apply to staging, verify with EXPLAIN ANALYZE that new queries perform
5. Apply to production during a low-traffic window, re-verify
Every step below uses the same postgres-mcp connection, switched between a --access-mode=restricted read-only role for inspection/verification and a scoped, temporary write-capable role only for the actual migration apply step — never the same role for both, per the guardrails covered in this module's first topic.
Tips
- Write the five-step plan down before starting, even for a change that feels small — it's the checklist that keeps "quick backfill" from turning into an unplanned production incident.
- Keep read-only inspection and write-capable migration application as genuinely separate roles/sessions, not just a mental note to "be careful" with one shared role.
- Budget for a staging verification pass as a non-optional step, not a nice-to-have — the whole point of step 4 below is catching problems before they're a production incident.
Step 1: Inspecting the Current Schema and Planning the Change with AI
Prompt:
Using postgres MCP, show me the current orders table structure and
confirm there's no existing status-history tracking anywhere in the
schema. Then propose a design for an order_status_history table that
would let us show a full timeline of status changes, including how
we'd backfill history for the ~2.1M existing orders that only have
a current status today.
-- what the agent checks
SELECT column_name, data_type FROM information_schema.columns
WHERE table_name = 'orders';
SELECT table_name FROM information_schema.tables
WHERE table_name ILIKE '%history%' OR table_name ILIKE '%audit%' OR table_name ILIKE '%event%';
Confirmed: orders has a bare status text column and updated_at, no separate history mechanism, and an events table exists but is scoped to analytics tracking, not domain state changes — worth ruling out explicitly rather than assuming based on the table name alone. Proposed design:
CREATE TABLE order_status_history (
id bigserial PRIMARY KEY,
order_id integer NOT NULL REFERENCES orders(id),
status text NOT NULL,
changed_at timestamptz NOT NULL DEFAULT now(),
changed_by integer REFERENCES users(id), -- nullable: system-driven transitions have no user
previous_status text
);
CREATE INDEX idx_order_status_history_order_id
ON order_status_history (order_id, changed_at);
The honest answer on backfill: there is no real history to backfill. The existing 2.1M orders only ever recorded their current status — there's no audit trail to reconstruct past transitions from. The agent flagged this directly rather than fabricating plausible-looking history rows, and proposed the only defensible option: insert one synthetic "known as of" row per existing order using its current status and updated_at as a floor, explicitly marked as such.
-- backfill: one synthetic row per existing order, clearly marked
INSERT INTO order_status_history (order_id, status, changed_at, previous_status)
SELECT id, status, updated_at, NULL
FROM orders;
-- Note: these rows represent "known status as of updated_at", not a real
-- transition event. The timeline UI must handle the first row per order
-- differently (label it "status as of" rather than "changed to").
That distinction — real event history versus a synthetic starting point — has to be surfaced to whoever builds the timeline UI, or the product ships a feature that implies more historical accuracy than the data actually supports.
Tips
- Explicitly ask the agent to check for existing history/audit mechanisms under multiple plausible names before designing a new table — duplicating an existing but differently-named mechanism is a common and avoidable mistake.
- When backfilling history that doesn't actually exist, insist on the agent stating that limitation plainly rather than generating plausible-looking synthetic data that reads as real — this is a place where a fluent, confident-sounding wrong answer is a genuine risk.
- Flag any "synthetic starting point" data distinctly in the schema or in accompanying documentation so downstream consumers (UI, reporting) don't treat it as equivalent to a real event.
Step 2: Generating a Reversible Migration and Reviewing It Before Apply
Prompt:
Turn the order_status_history design into an Alembic migration.
Make the backfill safe for a 2.1M-row orders table — batch it, don't
lock orders for the duration. Include a working downgrade.
"""add order_status_history table
Revision ID: 0083_add_order_status_history
Revises: 0082_previous_migration
Create Date: 2026-08-21
"""
from alembic import op
import sqlalchemy as sa
revision = "0083_add_order_status_history"
down_revision = "0082_previous_migration"
def upgrade():
op.create_table(
"order_status_history",
sa.Column("id", sa.BigInteger, primary_key=True),
sa.Column("order_id", sa.Integer, sa.ForeignKey("orders.id"), nullable=False),
sa.Column("status", sa.Text, nullable=False),
sa.Column("changed_at", sa.DateTime(timezone=True), nullable=False,
server_default=sa.text("now()")),
sa.Column("changed_by", sa.Integer, sa.ForeignKey("users.id"), nullable=True),
sa.Column("previous_status", sa.Text, nullable=True),
)
op.create_index(
"idx_order_status_history_order_id",
"order_status_history",
["order_id", "changed_at"],
)
# Table creation only here. Backfill runs as a SEPARATE data
# migration script (backfill_order_status_history.py), not inline
# in this DDL migration — keeps schema change and data change
# independently reviewable and independently re-runnable.
def downgrade():
op.drop_index("idx_order_status_history_order_id", table_name="order_status_history")
op.drop_table("order_status_history")
import time
from sqlalchemy import text
BATCH_SIZE = 10_000
def backfill(engine):
with engine.connect() as conn:
last_id = 0
while True:
result = conn.execute(text("""
INSERT INTO order_status_history (order_id, status, changed_at, previous_status)
SELECT id, status, updated_at, NULL
FROM orders
WHERE id > :last_id
ORDER BY id
LIMIT :batch_size
RETURNING order_id
"""), {"last_id": last_id, "batch_size": BATCH_SIZE})
rows = result.fetchall()
if not rows:
break
last_id = max(r.order_id for r in rows)
conn.commit()
print(f"backfilled through order_id={last_id}")
time.sleep(0.2)
Splitting the DDL migration from the data backfill script is the review-critical decision here: the schema change is small, fast, and safe to apply during a normal deploy; the 2.1M-row backfill is slower and better run as a monitored, resumable, manually-triggered job rather than blocking a deploy pipeline. An agent asked for "a migration" without that framing will often generate one file doing both, which is exactly what you want to catch in review before merging, not after a deploy hangs waiting on a multi-minute backfill.
Human review checklist actually used here:
[x] DDL migration is fast and doesn't lock orders
[x] Backfill is separate, batched, resumable, and idempotent (RETURNING
lets us track progress; re-running from last_id is safe)
[x] Downgrade actually drops what upgrade created, in reverse order
[x] FK to users.id is nullable — confirmed system transitions won't
violate a NOT NULL constraint
Tips
- Insist on schema DDL and data backfill living in separate migration artifacts — one fast and deploy-safe, one slow and independently run/monitored/resumable.
- Review the downgrade with the same scrutiny as the upgrade — a downgrade that doesn't fully reverse the upgrade (wrong index name, missed constraint) is a common, easy-to-miss review gap.
- Confirm nullable FK columns are genuinely intended to be nullable (here,changed_byfor system-driven transitions) rather than an oversight — ask the agent to state its reasoning for every nullable column explicitly.
Step 3: Verifying Query Plans and Index Coverage After Migration
Applying the migration and backfill isn't the finish line — the whole point of the new table is powering a timeline query efficiently, and that needs verifying against real data volume, on staging, before production.
Prompt:
The order_status_history migration and backfill are applied on
staging. Using postgres MCP, run EXPLAIN ANALYZE on the query the
timeline UI will actually use — fetching all history rows for one
order, most recent first — and confirm the index is being used, not
a sequential scan.
EXPLAIN ANALYZE
SELECT * FROM order_status_history
WHERE order_id = 481022
ORDER BY changed_at DESC;
Index Scan Backward using idx_order_status_history_order_id on order_status_history
(cost=0.42..8.51 rows=3 width=64) (actual time=0.031..0.034 rows=3 loops=1)
Index Cond: (order_id = 481022)
Planning Time: 0.112 ms
Execution Time: 0.058 ms
Index Scan Backward confirms the composite (order_id, changed_at) index is being used both for the equality filter and to satisfy the ORDER BY ... DESC without a separate sort step — exactly the design intent. Execution time of 0.058ms on staging with the full 2.1M-row backfill applied is the number that matters, not the theoretical index design; verifying it against a populated table, not an empty one right after CREATE TABLE, is what actually confirms the index earns its keep.
A second check worth doing before calling this done — the dashboard/reporting side, which queries across all orders rather than one:
EXPLAIN ANALYZE
SELECT status, count(*) FROM order_status_history
WHERE changed_at > now() - interval '7 days'
GROUP BY status;
Seq Scan on order_status_history (cost=0.00..48211.00 rows=812004 width=12) (actual time=0.021..289.442 rows=6 loops=1)
Filter: (changed_at > (now() - '7 days'::interval))
Rows Removed by Filter: 2093996
Planning Time: 0.098 ms
Execution Time: 289.511 ms
This one seq-scans because the existing index leads with order_id, not changed_at — it doesn't help a query filtering by time across all orders. 289ms isn't catastrophic for an internal reporting query run occasionally, but it's worth a deliberate decision rather than an oversight: either accept it as acceptable for a low-frequency dashboard query, or add a second index (changed_at) if this query runs often enough to matter. That's exactly the kind of trade-off to have the agent state explicitly rather than silently "fixing" by adding an index you didn't ask for and now have to maintain.
Prompt:
Should we add a second index on changed_at alone for the reporting
query, or is 289ms acceptable given how often it actually runs? Check
if there's a way to tell from pg_stat_statements how frequently a
similar query pattern executes today.
SELECT calls, mean_exec_time, query
FROM pg_stat_statements
WHERE query ILIKE '%order_status_history%'
ORDER BY calls DESC;
If pg_stat_statements shows this pattern running dozens of times a day rather than once a week, that tips the decision toward adding the index; if it's a monthly report, 289ms isn't worth another index's write-amplification and maintenance cost. Either way, that's a decision made from data the agent pulled live, not a default "add an index" reflex.
Tips
- Verify index usage against a fully populated table (post-backfill), not immediately afterCREATE TABLEon an empty one — plan choice and actual timing both depend on real row counts and statistics.
- Check both the primary access pattern (single order lookup) and any secondary/reporting access patterns before declaring the migration done — a new table often serves more than one query shape, and only one of them was the original design target.
- Usepg_stat_statements(enable it if it isn't already:CREATE EXTENSION pg_stat_statements;) to base index trade-off decisions on actual query frequency rather than guessing whether an extra index is worth its maintenance cost.
- Re-run this same verification pass in production after deploy, not just on staging — data distribution, autovacuum state, and cache warmth differ enough between environments that a staging-only check isn't sufficient sign-off for anything performance-sensitive.
Tips
Tips
- Treat AI-assisted schema work as a five-step pipeline — inspect, design, migrate-with-review, apply, verify — not a single prompt that outputs a finished migration; each step is where a different class of mistake gets caught.
- Insist the agent state honestly when historical data can't be reconstructed rather than generating plausible-looking synthetic history — this module's example (order status backfill) is a common real-world case where that honesty matters.
- Split DDL migrations from data backfills as separate artifacts, and verify resulting query plans withEXPLAIN ANALYZEagainst a fully populated table for every access pattern the new schema is meant to serve, not just the one that motivated the change.