All jobs

Review Postgrator Migrations in Fastify Pull Requests

An agent reviewed a fastify/demo port of its migrations to Postgres: 57 tests pass, but five foreign keys lose their index and old databases cannot upgrade.

Run this on RushOpens the console with this prompt ready to send, in a Project that has your repository.

What the agent does

The request expects new migration files on MySQL; pull request fastify/demo #135 adds none. It rewrites 001, 002, 004 and 005 under migrations/ in Postgres syntax, moves the driver to pg and changes docker-compose.yml from mysql:8.4 to postgres:18. The agent reads scripts/migrate.ts, scripts/seed-database.ts, the Knex repositories and the TypeBox schemas.

With no Docker, it ran MySQL from the vendor tarball and Postgres from @embedded-postgres/linux-x64. It applied each do file while a second connection held a transaction for 3 s, ran every undo and reapplied.

Start it with one API call

POST https://api.phoenix.so/o/{org}/sessions with this body:

{
  "agent": "claude",
  "input": "Review the Postgrator migrations added by the open pull request in resources. Read scripts/migrate.ts, scripts/seed-database.ts, src/plugins/external/knex.ts and env.ts, every file under migrations, src/plugins/app/tasks/tasks-repository.ts, src/plugins/app/users/users-repository.ts, src/schemas and .github/workflows/ci.yml first. Start the MySQL version docker-compose.yml names in the sandbox with a fresh database only, never a production connection; set the MYSQL_* variables from .env.example with CAN_CREATE_DATABASE=1 and CAN_SEED_DATABASE=1, run npm run db:create and apply the migrations up to the version before the pull request, seed, and insert representative rows into the tables the new files touch. Apply each new do file while a second connection holds an open transaction on the affected table and report any measured wait. Flag NOT NULL without a default on populated tables, dropped or narrowed columns, foreign keys without an index, statements that rewrite the table, and an undo file that does not restore the previous SHOW CREATE TABLE; run the undo and reapply to prove it. Check that the Knex repositories and TypeBox schemas agree with the new columns, then run npm run build -- --noEmit, npm run lint, npm run db:migrate and npm run test as ci.yml does. Comment on the changed lines with evidence and a safer staged alternative where one exists, and return migration-review.md with every command, its output and the limits of a fresh database. Do not edit the migrations or open another pull request.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app/pull/42"
    }
  ]
}
  1. Get an API key. In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
  2. Send the request. POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
  3. Read what came back. Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

What comes back

Tried on fastify/demo at b93cca2: pull request #135, a review with five findings, no code change, and the full report returned as the artifact migration-review.md. npm run build -- --noEmit and npm run lint exited 0, npm run db:migrate printed Migration completed!, and npm run test reported # pass 57 and # fail 0. Do files 002, 003 and 005 waited 3008, 3006 and 3007 ms behind the held transaction; each undo restored the prior schema. The review was not posted.

For the reviewer: whether the port is for new installs only. A database whose schemaversion holds the old hashes fails Postgrator.validateMigrations, and the pull request has no data-move step. The staged fixes the review suggests were not tested.

Run it on every pull request labelled database

An automation with this prompt and a github trigger on pull_request.labeled, labels database and branch_patterns main, reviews pull requests marked as schema changes; a label suits a change like #135, which swaps the engine and adds no migration file. Fork pull requests fire no trigger, and #135 is one, so this trial was started by hand on a clone checked out at pull/135/head.

The use case this job follows, with its automation and trigger: Review every pull request.

Why run it on Rush?

Build, lint, migrate and all 57 tests pass when run the way ci.yml does, so the findings come from comparing engines. MySQL indexed foreign keys and bumped updated_at on its own; Postgres does neither, and on 200k tasks a filter by author_id became Seq Scan ... Rows Removed by Filter: 200003. Also, scripts/migrate.ts exits 0 after a failed migration, so db:migrate && test would carry on.

Frequently asked questions

The request names MySQL. What did the run do?

It followed the pull request, running the base branch on MySQL 8.4.5 and the PR on Postgres 18.6. Neither was CI's image, Node was 22.23.4, and npm install replaced npm ci, which needs a lockfile the repository lacks.

What does seeding do to the migration table?

After db:seed, schemaversion has 0 rows and the next db:migrate fails with relation "users" already exists, yet exits 0. MySQL on the base branch did the same; CI migrates before seeding, so misses it.

More Fastify jobs

The same job on other stacks

Keep reading