Check the Migration Landed Without Deploying Something to Check It

From the team behind aidrop.it — one workspace to build, host, and keep changing your code.

A migration ran at deploy time, the build went green, and the only evidence is one log line from the migration runner. Whether the column is actually there, with the right type and the index the query plan needs, is a question the log does not answer. It is a question for the database.

The check is four statements, and it needs a way to run them. A managed database on a private network has no published port, so until recently the honest answer was to deploy a throwaway endpoint that ran the query — a deploy to verify a deploy. An agent that can run one SQL statement against the database from the session removes that step, and it also needs rules.

What the log can and cannot tell you

The migration runner's log says a file was applied and how long it took. That is worth reading first, and it should say where it stopped when it did not finish. What it cannot say is whether the schema now matches what the code expects: a migration that adds a column with the wrong default, or one that ran against a different database than the one the application reads, both log success.

So the log is the first check and never the last. The last check reads the schema and a row back out of the database the application will use, with the same credentials, after the deploy. Everything else is inference.

Four statements that settle it

The check does not need a tool; it needs the four questions written down. Which migration the database thinks it is on. Whether the column, type and nullability match the code. Whether the index the hot query needs exists. And one row read through the new shape, because a schema that is right and a table that is empty are different findings.

Ask the shape questions from information_schema and pg_indexes rather than from the application, so the check is independent of the ORM's opinion. Keep every statement a SELECT with a LIMIT; a verification that changes something has stopped being one.

Rules for an agent running SQL against live data

An agent that can reach the production database can also change it, and the difference between a check and an incident is the statement. The rules are short. Say what a destructive statement will do before running it, in one sentence, and wait. Prefer reading the count to reading the rows. Add a LIMIT to anything that could return a table. And treat a timeout as a finding about the query, not a reason to retry it wider.

These are the same reasons a deploy loop needs stop conditions: the cheapest path to a green result is sometimes the one that changes what was being measured. A verification statement that becomes a fix has to be announced as one.

The post-migration check, to paste after every deploy

Run these against the database the application reads, in this order, and stop at the first surprise. Replace the names in angle brackets; the shape is standard PostgreSQL and works through any client, including a fresh session that has to prove the state before it changes anything.

-- 1. Which migration does the database think it is on?
SELECT version FROM schema_migrations ORDER BY version DESC LIMIT 3;

-- 2. Is the column there, with the type and nullability the code expects?
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = '<table>' AND column_name = '<column>';

-- 3. Does the index the hot query needs exist?
SELECT indexname, indexdef FROM pg_indexes
WHERE tablename = '<table>' AND indexdef ILIKE '%<column>%';

-- 4. Read one row through the new shape, and the count.
SELECT <column>, count(*) OVER () AS rows FROM <table> LIMIT 1;

RULES FOR THE AGENT
- Before any statement that is not a SELECT: state what it changes, wait for yes.
- Every SELECT that can return a table carries a LIMIT.
- A timeout means narrow the query, never retry it as-is or wider.
- Report: migration version, column check, index check, one row — each pass/fail.

FAQ

How do I verify a database migration ran in production? Read the migration runner's log for the stage it reached, then query the database the application uses: the migrations table for the version, information_schema.columns for the shape, pg_indexes for the index, and one row through the new column. The log confirms the attempt; the queries confirm the result.

Should an AI agent have SQL access to the production database? For a verification, yes, with the statement recorded and rules it follows: announce anything that is not a SELECT, always LIMIT, treat a timeout as a signal to narrow. The access is the same the application already has; the difference is that a person can read what the agent ran.

How can I query a managed database that has no public port? Either deploy something inside the private network that runs the query for you, or use a host that lets the agent run a statement through its own tools. On aidrop.it that is shared_database_query: one statement, answered as CSV, stopped after 30 seconds and cut at 64 KB, with the statement kept in the Project's action log so the check is auditable afterwards.

What if the migrations table has a different name? Every runner names it differently — schema_migrations, _prisma_migrations, alembic_version, django_migrations. Ask the runner's documentation once, then keep the right name in the check block in your repository.