feat(infra): adopt node-pg-migrate + backfill initial schema migration #32

Merged

varutasu merged 2 commits from convoy/migration-tool into main

2026-05-27 00:01:59 -04:00

varutasu commented

2026-05-26 23:55:55 -04:00

(Migrated from github.com)

Summary

Closes P1 #11 of .convoys/ship-readiness.md (launch sequence step 7) — "No migration tool — scripts/add-*.js graveyard". Schema changes post-this-convoy ship as node-pg-migrate migrations under migrations/ at the repo root; the legacy 27 scripts/add-*.js / scripts/fix-*.js / scripts/seed-*.js jobs remain append-only history per the no-go-zones rule.

AGENTS.md Gotcha #6 flips from open → RESOLVED.

Decisions (full record in `.convoys/migration-tool.md` § Decisions)

D1 — Tool: node-pg-migrate@^8. JavaScript-native, raw-SQL-friendly via pgm.sql(), ESM-clean. Rejected drizzle-kit / prisma migrate / kysely because each forces broader TypeScript surface than AGENTS.md Gotcha #9 allows. Brings pg@^8.21.0 as a peer dep (dev-only).
D2 — Migrations directory: migrations/ at the repo root. Separates tool-wrapped artifacts from the legacy scripts/migrations/ placeholder (which housed 2026-05-24-rename-admin-email.js and remains preserved). Matches node-pg-migrate's default --migrations-dir migrations.
D3 — Tracking table: default pgmigrations (no name collision with the existing schema).
D4 — Backfill: hand-translate scripts/setup-neon-db.js's DDL verbatim. Each await sql\...`block becomes onepgm.sql(`...`)call. UsesCREATE TABLE IF NOT EXISTS, so the initial migration is idempotent against fresh AND pre-existing envs. Documented assumption: prod has drifted via 27 historical add-*.jsscripts; reconciling those is the queuedreconcile-historical-add-scripts` follow-up.
D5 — Bootstrap reconciliation: split. setup-neon-db.js now (1) validates env vars, (2) spawns npm run migrate up, (3) seeds the admin row with ON CONFLICT (email) DO NOTHING. DDL ownership moves to the migration.
D6 — CI integration: defer to wire-migrate-into-ci follow-up convoy. Real work (needs a test DB + secret OR a Postgres service container); convoy spec authorizes deferral. Risk acknowledged in convoy file § R3.
D7 — Down-migration on the initial backfill: hard stub. Rolling back the initial schema would drop every user / card / collection / deck row. The stub throws with a long-form error pointing at the recommended alternative (Neon branch + forward-apply). Future migrations should write their own down().

The change (per file)

File	Action	Purpose
`migrations/1779853647564_initial-schema.js`	new	7-table backfill migration, idempotent. `down()` hard-stub (D7).
`package.json`	modified	Adds `migrate` script + `node-pg-migrate@^8.0.4` + `pg@^8.21.0` to devDependencies.
`scripts/setup-neon-db.js`	modified	Splits DDL (now in migration) from seed (admin INSERT). Adds `POSTGRES_URL` pre-flight check. Adds `runMigrations()` spawn helper.
`README.md`	modified	Step-4 mentions the migration chain. New § "Schema changes" documents the create/edit/up/commit flow.
`AGENTS.md`	modified	§ 3 Conventions: new "Schema changes" bullet. § 4 Gotcha #6 flipped → RESOLVED.
`.cursor/rules/no-go-zones.mdc`	modified	"Schema changes" rule rewritten to describe the `node-pg-migrate` flow.
`.cursor/rules/db-and-schema.mdc`	modified	§ "Schema source of truth" rewritten.
`docs/SCHEMA_MAP.md`	modified	Preamble re-scopes the file.
`.convoys/migration-tool.md`	new	Full architect-decision record + operator runbook + follow-ups.
`package-lock.json`	modified	New transitive deps.

Verification (all gates green pre-PR)

npm run lint → exit 1 with 128 problems (baseline preserved, zero regression). New migration file is lint-clean; no new ignore patterns added to eslint.config.mjs.
npm run test:run → 21/21 pass in ~1.3s.
node --check migrations/1779853647564_initial-schema.js → exit 0.
node --check scripts/setup-neon-db.js → exit 0.

Module-load + down() throw verification:

node -e "import('./migrations/1779853647564_initial-schema.js').then(m => m.down())"
→ [migration:1779853647564_initial-schema] Refusing to drop the initial schema. ...

npm run migrate -- --help → returns standard node-pg-migrate help text.

Live test against a Neon branch: deferred (no throwaway branch available). Optional post-merge operator sequence documented in .convoys/migration-tool.md § Operator runbook.

Operator runbook (new flow)

Schema change:

npm run migrate create add-foo-column -- -j js
# edit migrations/<timestamp>_add-foo-column.js
npm run migrate up
# update docs/SCHEMA_MAP.md, commit together

Onboarding a new env:

npm install
# seed .env.local with POSTGRES_URL + JWT_SECRET + ADMIN_INITIAL_PASSWORD
npm run setup-db   # chains migrate up, then seeds admin user

Re-running setup against existing env: idempotent on both halves (CREATE TABLE IF NOT EXISTS + ON CONFLICT (email) DO NOTHING).

Follow-ups surfaced

wire-migrate-into-ci (P2 CI infra) — D6 deferral.
reconcile-historical-add-scripts (P1 quality) — fold the 27 historical add-*.js effects into migration history so fresh-env onboarding works without manual script replay. R1 in convoy file.
retire-graveyard-scripts-after-audit (P3 polish; blocked on reconcile) — once history captures all effects, delete the legacy scripts/add-*.js / scripts/fix-*.js / scripts/seed-*.js files.
audit-node-pg-migrate-transitive-deps (P3 hygiene) — 11 npm audit findings from node-pg-migrate's glob + yargs transitive deps (all dev-only paths; not exercised). R5 in convoy file.
add-migration-template (P3 DX) — custom template via --template-file-name if migration authoring proves inconsistent.

Made with Cursor

## Summary Closes P1 #11 of `.convoys/ship-readiness.md` (launch sequence step 7) — *"No migration tool — `scripts/add-*.js` graveyard"*. Schema changes post-this-convoy ship as `node-pg-migrate` migrations under `migrations/` at the repo root; the legacy 27 `scripts/add-*.js` / `scripts/fix-*.js` / `scripts/seed-*.js` jobs remain append-only history per the no-go-zones rule. `AGENTS.md` Gotcha #6 flips from open → RESOLVED. ## Decisions (full record in `.convoys/migration-tool.md` § Decisions) - **D1 — Tool: `node-pg-migrate@^8`.** JavaScript-native, raw-SQL-friendly via `pgm.sql()`, ESM-clean. Rejected `drizzle-kit` / `prisma migrate` / `kysely` because each forces broader TypeScript surface than `AGENTS.md` Gotcha #9 allows. Brings `pg@^8.21.0` as a peer dep (dev-only). - **D2 — Migrations directory: `migrations/` at the repo root.** Separates tool-wrapped artifacts from the legacy `scripts/migrations/` placeholder (which housed `2026-05-24-rename-admin-email.js` and remains preserved). Matches `node-pg-migrate`'s default `--migrations-dir migrations`. - **D3 — Tracking table: default `pgmigrations`** (no name collision with the existing schema). - **D4 — Backfill: hand-translate `scripts/setup-neon-db.js`'s DDL verbatim.** Each `await sql\`...\`` block becomes one `pgm.sql(\`...\`)` call. Uses `CREATE TABLE IF NOT EXISTS`, so the initial migration is idempotent against fresh AND pre-existing envs. Documented assumption: prod has drifted via 27 historical `add-*.js` scripts; reconciling those is the queued `reconcile-historical-add-scripts` follow-up. - **D5 — Bootstrap reconciliation: split.** `setup-neon-db.js` now (1) validates env vars, (2) spawns `npm run migrate up`, (3) seeds the admin row with `ON CONFLICT (email) DO NOTHING`. DDL ownership moves to the migration. - **D6 — CI integration: defer** to `wire-migrate-into-ci` follow-up convoy. Real work (needs a test DB + secret OR a Postgres service container); convoy spec authorizes deferral. Risk acknowledged in convoy file § R3. - **D7 — Down-migration on the initial backfill: hard stub.** Rolling back the initial schema would drop every user / card / collection / deck row. The stub throws with a long-form error pointing at the recommended alternative (Neon branch + forward-apply). Future migrations should write their own `down()`. ## The change (per file) | File | Action | Purpose | | --- | --- | --- | | `migrations/1779853647564_initial-schema.js` | new | 7-table backfill migration, idempotent. `down()` hard-stub (D7). | | `package.json` | modified | Adds `migrate` script + `node-pg-migrate@^8.0.4` + `pg@^8.21.0` to devDependencies. | | `scripts/setup-neon-db.js` | modified | Splits DDL (now in migration) from seed (admin INSERT). Adds `POSTGRES_URL` pre-flight check. Adds `runMigrations()` spawn helper. | | `README.md` | modified | Step-4 mentions the migration chain. New § "Schema changes" documents the create/edit/up/commit flow. | | `AGENTS.md` | modified | § 3 Conventions: new "Schema changes" bullet. § 4 Gotcha #6 flipped → RESOLVED. | | `.cursor/rules/no-go-zones.mdc` | modified | "Schema changes" rule rewritten to describe the `node-pg-migrate` flow. | | `.cursor/rules/db-and-schema.mdc` | modified | § "Schema source of truth" rewritten. | | `docs/SCHEMA_MAP.md` | modified | Preamble re-scopes the file. | | `.convoys/migration-tool.md` | new | Full architect-decision record + operator runbook + follow-ups. | | `package-lock.json` | modified | New transitive deps. | ## Verification (all gates green pre-PR) - `npm run lint` → exit 1 with **128 problems** (baseline preserved, zero regression). New migration file is lint-clean; no new ignore patterns added to `eslint.config.mjs`. - `npm run test:run` → **21/21 pass** in ~1.3s. - `node --check migrations/1779853647564_initial-schema.js` → exit 0. - `node --check scripts/setup-neon-db.js` → exit 0. - Module-load + `down()` throw verification: ``` node -e "import('./migrations/1779853647564_initial-schema.js').then(m => m.down())" → [migration:1779853647564_initial-schema] Refusing to drop the initial schema. ... ``` - `npm run migrate -- --help` → returns standard `node-pg-migrate` help text. **Live test against a Neon branch: deferred** (no throwaway branch available). Optional post-merge operator sequence documented in `.convoys/migration-tool.md` § Operator runbook. ## Operator runbook (new flow) **Schema change:** ``` npm run migrate create add-foo-column -- -j js # edit migrations/<timestamp>_add-foo-column.js npm run migrate up # update docs/SCHEMA_MAP.md, commit together ``` **Onboarding a new env:** ``` npm install # seed .env.local with POSTGRES_URL + JWT_SECRET + ADMIN_INITIAL_PASSWORD npm run setup-db # chains migrate up, then seeds admin user ``` **Re-running setup against existing env:** idempotent on both halves (`CREATE TABLE IF NOT EXISTS` + `ON CONFLICT (email) DO NOTHING`). ## Follow-ups surfaced - `wire-migrate-into-ci` (P2 CI infra) — D6 deferral. - `reconcile-historical-add-scripts` (P1 quality) — fold the 27 historical `add-*.js` effects into migration history so fresh-env onboarding works without manual script replay. R1 in convoy file. - `retire-graveyard-scripts-after-audit` (P3 polish; blocked on reconcile) — once history captures all effects, delete the legacy `scripts/add-*.js` / `scripts/fix-*.js` / `scripts/seed-*.js` files. - `audit-node-pg-migrate-transitive-deps` (P3 hygiene) — 11 `npm audit` findings from node-pg-migrate's glob + yargs transitive deps (all dev-only paths; not exercised). R5 in convoy file. - `add-migration-template` (P3 DX) — custom template via `--template-file-name` if migration authoring proves inconsistent. Made with [Cursor](https://cursor.com)

vercel[bot] commented

2026-05-26 23:56:00 -04:00

(Migrated from github.com)

The latest updates on your projects. Learn more about Vercel for GitHub.

Project	Deployment	Actions	Updated (UTC)
tcg-vault	Ready	Preview, Comment	May 27, 2026 4:00am

[vc]: #CWXylQiqP0I0iDYA7Ymy9/YzSMCiJGPl6uflfrSNhq4=:eyJpc01vbm9yZXBvIjp0cnVlLCJ0eXBlIjoiZ2l0aHViIiwicHJvamVjdHMiOlt7Im5hbWUiOiJ0Y2ctdmF1bHQiLCJwcm9qZWN0SWQiOiJwcmpfRjZXOEVvRkd3Y0g3aWVGcnRvRlNlOXdVVkFhNSIsImxpdmVGZWVkYmFjayI6eyJyZXNvbHZlZCI6MCwidW5yZXNvbHZlZCI6MCwidG90YWwiOjAsImxpbmsiOiJ0Y2ctdmF1bHQtZ2l0LWNvbnZveS1taWdyYXRpb24tdG9vbC1yYW5kYWxsLXN0aWxsd2VsbHMtcHJvamVjdHMudmVyY2VsLmFwcCJ9LCJpbnNwZWN0b3JVcmwiOiJodHRwczovL3ZlcmNlbC5jb20vcmFuZGFsbC1zdGlsbHdlbGxzLXByb2plY3RzL3RjZy12YXVsdC9CUzhpd0NrWkxxNkFTbzZLM1VrekZLZkZjUmk1IiwicHJldmlld1VybCI6InRjZy12YXVsdC1naXQtY29udm95LW1pZ3JhdGlvbi10b29sLXJhbmRhbGwtc3RpbGx3ZWxscy1wcm9qZWN0cy52ZXJjZWwuYXBwIiwibmV4dENvbW1pdFN0YXR1cyI6IkRFUExPWUVEIiwicm9vdERpcmVjdG9yeSI6bnVsbH1dLCJyZXF1ZXN0UmV2aWV3VXJsIjoiaHR0cHM6Ly92ZXJjZWwuY29tL3ZlcmNlbC1hZ2VudC9yZXF1ZXN0LXJldmlldz9vd25lcj12YXJ1dGFzdSZyZXBvPXRjZy12YXVsdCZwcj0zMiJ9 The latest updates on your projects. Learn more about [Vercel for GitHub](https://vercel.link/github-learn-more). | Project | Deployment | Actions | Updated (UTC) | | :--- | :----- | :------ | :------ | | [tcg-vault](https://vercel.com/randall-stillwells-projects/tcg-vault) | ![Ready](https://vercel.com/static/status/ready.svg) [Ready](https://vercel.com/randall-stillwells-projects/tcg-vault/BS8iwCkZLq6ASo6K3UkzFKfFcRi5) | [Preview](https://tcg-vault-git-convoy-migration-tool-randall-stillwells-projects.vercel.app), [Comment](https://vercel.live/open-feedback/tcg-vault-git-convoy-migration-tool-randall-stillwells-projects.vercel.app?via=pr-comment-feedback-link) | May 27, 2026 4:00am | <a href="https://vercel.com/vercel-agent/request-review?owner=varutasu&repo=tcg-vault&pr=32" rel="noreferrer"><picture><source media="(prefers-color-scheme: dark)" srcset="https://agents-vade-review.vercel.sh/request-review-dark.svg"><source media="(prefers-color-scheme: light)" srcset="https://agents-vade-review.vercel.sh/request-review-light.svg"><img src="https://agents-vade-review.vercel.sh/request-review-light.svg" alt="Request Review"></picture></a>

github-actions[bot] commented

2026-05-26 23:56:05 -04:00

(Migrated from github.com)

Pipeline Health

Build + CI gates

Gate	Status
Vercel build (Preview)	✅ pass
CI: Lint	✅ pass
CI: Schema map fresh	❌ skipped
Preview smoke	✅ pass
Visual diff	⏭ skipped or pending

Build runs on Vercel; this CI runs lint and schema-map drift only (no duplicate build).

Role reports

Role	Status
Reviewer report	⏳ pending
A11y audit	⏳ pending
Design system audit	⏳ pending

See individual comments above for details. This rollup updates automatically.

## Pipeline Health ### Build + CI gates | Gate | Status | | --- | --- | | Vercel build (Preview) | ✅ pass | | CI: Lint | ✅ pass | | CI: Schema map fresh | ❌ skipped | | Preview smoke | ✅ pass | | Visual diff | ⏭ skipped or pending | _Build runs on Vercel; this CI runs lint and schema-map drift only (no duplicate build)._ ### Role reports | Role | Status | | --- | --- | | Reviewer report | ⏳ pending | | A11y audit | ⏳ pending | | Design system audit | ⏳ pending | See individual comments above for details. This rollup updates automatically.