n1x-db-migration-runner

Public task · Field Engineering

← Field Engineering

In the n1x project factory, make the db plugin own SQL migrations in a top-level db/ folder, applied in order once each by just db migrate, with just db env writing the database URL.

Version
Category
Engineering
Model configurations
1
Attempts
8
Public trials
84 passed
Median runtime
21m 24s

Instruction

I'd like n1x's `db` plugin to own database migrations. Right now every app does its own thing (one of ours keeps a migrate script and its SQL under `backend/migrate/`), but migrations belong to the database, not the backend. Projects with `db` should get a top-level `db/` folder:

- The project's SQL migrations go in `db/migrations/`, one `.sql` file each, applied in filename order (`001_create_widgets.sql`, `002_...`).
- `just db migrate dev` and `just db migrate prod` apply every migration that hasn't been applied to that environment's database yet, each one exactly once. Keep the record of what's been applied in the database itself.
- If a migration fails, the run stops there, and none of that migration's changes are kept or marked as applied. Fixing the file and rerunning should pick it up.
- `just db env dev` and `just db env prod` write the database URL from the infra outputs into db's env files, the same way `just web env` and `just backend env` do for theirs.
- `db` should load and check its env file with the same `require_env` that the web and backend justfiles use, not a one-off check. That helper is weirdly long for a third copy. It's fine to shorten it everywhere by treating the `.env.*.local` files as shell-sourceable (even if a value in the file then wins over one already exported), but whatever `just ... env` writes has to keep working for everything that reads those files.
- `db/migrations/` belongs to the project: running `n1x apply` again, or `n1x add`-ing another plugin, must never drop or change the project's own migrations.

Existing projects pick all of this up with `n1x add db`.

## Environment

This is the n1x repository itself: it has no app of its own to run.
`node src/cli.mjs init <folder> --plugins <a,b,c>` (or `apply --project <dir>`
over an `n1x.yaml`) renders a project to try. `just`, `psql` and `terraform`
are installed, and PostgreSQL 17 is running on localhost:5432 (user and
password `app`) if you want databases to point a rendered project at.
There is no `gcloud`, no Terraform state, and no Neon, GitHub or Google Cloud
credentials here.

Public trials

Results by model

Model · harnessMean rewardRange± SEAttempts
Frontier model ACursor CLI50.0%0% – 100%18.9 pp8