Skip to Content

← All archived runs

Run: mongodb-seeding-migrations

run.md

Run: mongodb-seeding-migrations

  • branch: claude/mongodb-seeding-migrations-1uyr2c
  • pr: #484

01_define/output/spec.md

Spec: MongoDB seeding & migrations

  • slug: mongodb-seeding-migrations
  • personas: admin (platform operators); reliability affects all personas
  • touches: packages/services/src/db, packages/services/scripts, packages/services/package.json, .github/workflows, apps/docs/app/technical, packages/services/AGENTS.md, .claude/skills/db-auditor
  • complexity: complex

Problem

MongoDB is unmanaged: there is no migration framework, schema and reference data drift between environments, and config datapoints are entered by hand in each environment. The codebase shows it — packages/services/package.json declares five migrate:* / backfill:* / rename:* scripts, but packages/services/scripts/ holds only load-repo-env.ts; every migration reference is dangling and nothing records what ran where. There is also no automatic migration on merge (Vercel auto-deploys on push to main; CI does no DB work). This blocks Q2-2026 Objective 3 ("Validate Technical Infrastructure & Payout Flow" — 99%+ uptime, zero payout failures): reliable, reproducible DB state is its foundation.

Proposed change

Introduce a managed database lifecycle for @sustentus/services, in five parts:

  1. Migration framework (ts-migrate-mongoose). Versioned up/down migrations tracked in a migrations collection, wired to the existing connectDB singleton and its env. A real pnpm db:migrate CLI (create / up / down / status) replaces the five dangling npm scripts. Migrations live in packages/services/src/db/migrations/.
  2. Idempotent seeding. pnpm db:seed upserts the global reference/config data every environment needs — Status workflows and ActionType, plus per-tenant defaults (SLA definitions, tenant settings) — so new deployments are never out of sync and operators stop hand-entering datapoints. Safe to re-run (no duplicates, no changes on a steady state).
  3. Auto-migrate on merge. A new GitHub Action runs db:migrate up against production on push to main, gated so forked-PR runs never receive secrets, using a new MONGODB_URI GitHub secret. Migration failure is visible as a status check.
  4. DB auditor (skill + CI guardrail). A .claude/skills/db-auditor capability and a pnpm db:audit script report unused/redundant indexes, missing indexes (ESR), unbounded arrays, oversized documents, orphaned references and collection bloat; a lightweight CI job surfaces audit warnings on PRs. Leans on the existing mongodb-schema-design, mongodb-query-optimizer, and mongodb-connection skills.
  5. Documented conventions. A technical docs page (migration syntax, file naming, up/down rules, seeding, auditor usage) under apps/docs/app/technical/, referenced from packages/services/AGENTS.md and the skills catalog.

Acceptance criteria

  • pnpm --filter @sustentus/services db:migrate create <name> scaffolds a timestamped TS migration with up/down in packages/services/src/db/migrations/.
  • db:migrate up / down / status run against MongoDB, record applied state in a tracking collection, and reuse the connectDB env (MONGODB_URI, MONGODB_DATABASE_NAME).
  • No dangling migration references remain — the five migrate:*/backfill:*/rename:* scripts are removed or reimplemented as real migrations.
  • pnpm db:seed idempotently upserts global reference data (Status, ActionType) and per-tenant defaults; a second run makes no changes and creates no duplicates.
  • A GitHub Action runs migrations on merge to main against production, gated so forked-PR runs get no secrets, with failure surfaced as a check.
  • pnpm db:audit produces a report covering indexes, document size, unbounded arrays and orphans; a CI job surfaces audit warnings on PRs.
  • .claude/skills/db-auditor exists and is listed in .claude/SKILLS.md.
  • A technical docs page documents migration syntax/conventions, seeding, and the auditor; packages/services/AGENTS.md points to it.

Out of scope

  • Backfilling or repairing existing production data beyond the seed reference set.
  • A second preview/staging migration database — production-only auto-run this round.
  • Rewriting models/schemas or changing the runtime behaviour of the connection singleton.
  • Writing automated tests (no test infrastructure, per conventions).

Open questions

  • none. (Adding MONGODB_URI as a GitHub Actions secret is an ops step the user performs; Build documents it. Not blocking — it gates only the auto-run job at deploy time.)

02_build/output/notes.md

Build notes: mongodb-seeding-migrations

  • commits: feat: mongodb seeding, migrations & db auditor (single feature commit on the run branch)

What changed

  • packages/services/package.json: added ts-migrate-mongoose dep; replaced the 5 dangling migrate:*/backfill:*/rename:* scripts with db:migrate, db:seed, db:audit.
  • packages/services/scripts/{db-migrate,db-seed,db-audit}.ts: CLI runners (reuse loadRepoEnv).
  • packages/services/src/db/migrations/template.ts: arrow-function up/down migration template (raw Connection). Migrations land beside it as <unix-ms>-<name>.ts.
  • packages/services/src/db/seed/: idempotent, create-if-missing seeding via the app's models — global reference data (statuses, action types) + per-tenant defaults (SLA definitions, settings).
  • packages/services/src/db/audit/: read-only auditor (unused/redundant indexes, missing tenant index, large docs, unbounded arrays, orphaned tenant refs).
  • .github/workflows/db-migrate.yaml: runs db:migrate up on push to main, gated to the canonical repo (forks never get the secret); skips gracefully until MONGODB_URI secret is set.
  • .github/workflows/db-audit.yaml: non-blocking db:audit on same-repo PRs (exits 0; writes a job summary).
  • .claude/skills/db-auditor/SKILL.md (+ listed in .claude/SKILLS.md): triage skill over db:audit.
  • apps/docs/app/technical/development/database/page.mdx + packages/services/AGENTS.md: documented migration syntax/conventions, seeding, auditing.

Acceptance criteria status

  • db:migrate create <name> scaffolds a timestamped up/down migration in src/db/migrations/ — verified e2e (created 1781522971161-add-status-index.ts).
  • db:migrate up/down/status run against MongoDB and track state in the migrations collection, reusing connectDB env — verified e2e (status flips [ ]→[x] after up).
  • No dangling migration references — the 5 old scripts are removed.
  • db:seed idempotently upserts reference data + per-tenant defaults — verified e2e (first run 29 created; second run 0 created / 29 already present).
  • GitHub Action runs migrations on merge to main, gated so forked-PR runs get no secrets, with failure surfaced as a check.
  • db:audit reports indexes/doc size/unbounded arrays/orphans; CI job surfaces it on PRs — verified e2e (flagged unused indexes).
  • .claude/skills/db-auditor exists and is listed in .claude/SKILLS.md.
  • Docs page documents migration syntax/conventions, seeding, auditor; AGENTS.md points to it.

Verify result

  • Local (this agent): tsc --noEmit clean; eslint . 0 errors / 21 pre-existing warnings (under the 45 ceiling), none in new files; full migrate→seed→audit e2e green against an isolated MongoDB container (production Atlas DB deliberately untouched).
  • Factory: format · lint · typecheck run in CI; Vercel preview builds the PR. The db:migrate / db:audit workflows need a MONGODB_URI repo secret to do real DB work — until then they skip.

Notes for review

  • Per-tenant defaults are create-if-missing (never overwrite), seeded only for tenants with no existing value for that stage/key. SLA/setting default values had no canonical source in code, so sensible defaults were chosen (SLA mirrors the admin dashboard's intended config); admins can still edit/delete them. Reference-data (status/action-type) names match what the services layer relies on.
  • Ops step (out of scope, non-blocking): add MONGODB_URI (and optionally MONGODB_DATABASE_NAME) as a GitHub Actions repository secret to activate auto-migrations and the CI audit. Without it both jobs no-op.

03_release/output/investor-update.md

MongoDB now managed end-to-end — migrations, seeding and auditing automated

Who it's for: Platform operations (database reliability underpins every persona) What shipped: Versioned database migrations that run automatically on each release, idempotent seeding of reference data, and an auditor that flags inefficiencies. Why it matters: Ends manual database setup and environment drift — advancing "Validate Technical Infrastructure & Payout Flow" (99%+ uptime).

Verified end-to-end against an isolated database; the five legacy ad-hoc scripts are retired.

Dig deeper: <merged-PR URL> · <database docs URL>

03_release/output/release.md

Release: mongodb-seeding-migrations

  • pr: #484 · merged: no — blocked at the Ready to merge gate (unticked)
  • CI: green (Format · Lint · Typecheck) on the build commit; Database audit job runs non-blocking
  • technical docs: updated apps/docs/app/technical/development/database (new, in Build) and …/development/ci-cd (added Stage 4 — database workflows)
  • business docs: no business docs impact — internal infrastructure change, no user-facing behaviour
  • release notes: investor-only — no end-user note (internal change). Investor draft in 03_release/output/investor-update.md
  • deploy: not reached — merge gate not yet authorised
  • sent: none — nothing sent; will send only after a ticked gate, green merge, and green production deploy

Review summary

/code-review (high effort) run on the diff. Two real findings, plus two accepted follow-ups:

  • Fixed on branch — db:audit could fail the PR. If auditDatabase() threw (e.g. $indexStats not permitted on a restricted Atlas tier), the runner set exitCode=1, contradicting the workflow's documented non-blocking guarantee. Now each collection is audited in its own try/catch and the runner reports any error as a warning and exits 0.
  • Accepted (follow-up) — soft-delete idempotency edge case. If an admin soft-deletes a baseline reference row and re-seeds, createIfMissing reports "already present" without restoring it (the E11000 swallow hides the still-present soft-deleted unique key; SLA avoids this via its partial index). Out of scope this run — restoring admin-deleted data is a product decision, especially for tenant settings. Normal/fresh environments are unaffected (verified e2e). Tracked for a follow-up.
  • Accepted (follow-up) — invoice-status duplication. The five invoice statuses are defined in both seed/reference-data.ts and the existing statusService.ensureInvoiceWorkflowStatuses(); values match today but could drift. A shared constant would fix it; deferred to avoid changing the status service's runtime this round.
  • Verifier confirmed several non-issues: ActionType UPPERCASE↔lowercase idempotency is safe (mongoose applies the lowercase setter to query filters), tenantPlugin does not interfere (tenantId passed explicitly), tenant-defaults required fields are all supplied, and the workflow if: gating + the db-migrate empty-secret guard are correct.

Acceptance check (vs spec)

  • db:migrate create <name> scaffolds a timestamped up/down migration — verified e2e
  • db:migrate up/down/status track state in the migrations collection, reuse connectDB env — verified e2e
  • No dangling migration references — the five legacy scripts removed
  • db:seed idempotent (29 created → 0 created / 29 present on re-run) — verified e2e
  • GitHub Action migrates on merge to main, forked-PR-safe, failure surfaced — workflow added
  • db:audit report + CI guardrail — verified e2e; db-audit.yaml runs on PRs
  • .claude/skills/db-auditor exists and is listed in .claude/SKILLS.md
  • Docs page + packages/services/AGENTS.md pointer

Gate status

Release is paused at step 6: the Ready to merge checkbox on PR #484 is unticked. Docs, the investor draft, and the review are prepared on the branch. Tick the box and re-run /pipeline release mongodb-seeding-migrations to merge → verify the production deploy → send the investor update.