Run: db-ci-gates

• branch: claude/gifted-thompson-ry8nmw
• pr: #489

Spec: Honest database audit + gated preview/production migrations

• slug: db-ci-gates
• personas: admin
• touches: .github/workflows/db-audit.yaml, .github/workflows/db-migrate.yaml, packages/services/src/db/audit/index.ts, packages/services/scripts/db-audit.ts
• complexity: standard

Problem

The database CI is dishonest in two ways, and both undermine the admin-facing data-quality guardrails that the "Refine the bridge" initiative leans on to keep the platform lean and trustworthy as it scales.

1. The Database audit check is a false green. db-audit.yaml runs pnpm db:audit on every PR, but auditDatabase() (packages/services/src/db/audit/index.ts) catches each collection's error, console.warns, and skips it; scripts/db-audit.ts then exits 0 regardless. In CI the DB user lacks the indexStats privilege, so every collection is skipped — the live job log shows MongoServerError: user is not allowed to do action [indexStats] per collection — yet the job prints "no findings across N collections" and passes. The check proves nothing while looking green.
2. Migrations run only after merge, ungated, against a single target. db-migrate.yaml triggers solely on push to main against secrets.MONGODB_URI / secrets.MONGODB_DATABASE_NAME. There is no preview-DB migration on PRs (so schema changes are never exercised before merge) and no human approval gate before production migrations run.

Proposed change

Make the audit honest and split migrations into two gated runs. This is a CI + audit-tooling pass only — no app/runtime changes beyond the audit script.

A) Honest audit. Teach the audit to distinguish "audited and clean" from "could not audit":

• auditDatabase() records, per collection, whether it was successfully inspected or skipped, and why (capturing permission/privilege errors such as indexStats not allowed). The return shape carries enough for the script to tell the two states apart (e.g. counts of inspected vs skipped collections, plus skip reasons).
• scripts/db-audit.ts exits non-zero with a clear message when any collection is skipped due to a permission/privilege error, or when zero collections were actually inspected — so the CI check fails loudly instead of false-greening.
• The existing graceful skip when MONGODB_URI is absent (forked PRs / no secret) still exits 0 and passes.
• The audit stays strictly read-only.

B) Gated migrations via GitHub Environments. Split db-migrate.yaml into two jobs:

• A PR job that runs pnpm db:migrate up against preview, using environment: preview, triggered on pull_request for same-repo PRs only (github.event.pull_request.head.repo.full_name == github.repository — never forks).
• The existing merge-to-main job, pointed at production via environment: production (whose required-reviewer protection is the human approval gate).
• Both read MONGODB_URI + MONGODB_DATABASE_NAME from their environment's secrets — DB names are never hardcoded (production is sustentus-prod; the preview name is set in the preview env).
• Existing safeguards are kept: concurrency with cancel-in-progress: false (never kill an in-flight migration) and the if: github.repository == 'sustentus/sustentus' canonical-repo guard.

db-audit.yaml keeps workflow_dispatch (on-demand) and gains a nightly schedule so the honest audit runs on a cadence, not only per PR.

Acceptance criteria

• auditDatabase() returns a report that distinguishes successfully inspected collections from skipped ones and captures each skip's reason (permission/privilege errors included), while remaining strictly read-only.
• scripts/db-audit.ts exits non-zero with a clear message when any collection is skipped due to a permission/privilege error.
• scripts/db-audit.ts exits non-zero with a clear message when zero collections were actually inspected.
• scripts/db-audit.ts still exits 0 (graceful skip) when MONGODB_URI is absent.
• When all collections are inspected with no findings, the audit exits 0 and reports a clean database (the only legitimate green).
• db-audit.yaml retains workflow_dispatch, retains its same-repo guard, and adds a nightly schedule trigger.
• db-migrate.yaml has a PR job running pnpm db:migrate up against environment: preview, triggered on pull_request for same-repo PRs only (the head.repo.full_name == github.repository guard).
• db-migrate.yaml has a merge-to-main job running migrations against environment: production.
• Both migration jobs read MONGODB_URI + MONGODB_DATABASE_NAME from their environment's secrets — no DB name is hardcoded.
• Both migration jobs keep concurrency with cancel-in-progress: false and the if: github.repository == 'sustentus/sustentus' canonical-repo guard.
• Any TS changes to the audit script follow CONVENTIONS.md (arrow functions, type over interface, async/await, named imports).
• The PR notes the human-owned infra prerequisites (Atlas users + GitHub Environments/secrets/ required reviewers) so the workflows can actually pass in CI once that is in place.

Out of scope

• Dropping or re-prefixing indexes (tenantId-prefixed compounds, redundant {tenantId:1}) — that needs the now-honest audit's live explain() / $indexStats evidence first; a separate later pass.
• The global autoIndex:false + syncIndexes() migration — separate later pass.
• Rollback logic — migrations are forward-only and idempotent by existing convention; no down orchestration is added.
• Any app/runtime code changes beyond the audit script.
• Infra setup itself (done by a human, not in this PR; only documented here):
  • Atlas: a read-only claude_audit user granted indexStats / collStats / dbStats (or clusterMonitor) so the audit returns real findings; a migrator user with readWrite on both DBs. Atlas IP allowlist is already open (0.0.0.0/0).
  • GitHub: Environments preview and production, each with MONGODB_URI + MONGODB_DATABASE_NAME secrets (preview → preview DB, production → sustentus-prod), and required reviewers added to production. The db-audit job's secrets point at the claude_audit user / preview DB.

Open questions

• none. (The sandbox cannot reach MongoDB — outbound TCP 27017 is blocked, only HTTP/443 — so the audit script and workflows are verified by reading the YAML and the audit logic, not by running db:audit / db:migrate locally. This is a known constraint, not a blocker for Build.)

Build notes: db-ci-gates

• commits: feat: db-ci-gates — honest audit exit codes, feat: db-ci-gates — gated preview/production migrations + nightly audit

What changed

A) Honest audit (packages/services/src/db/audit/index.ts)

• Replaced the silent console.warn-and-skip with a recorded outcome. AuditReport now carries collectionsInspected (count actually audited), collectionsSkipped (a new AuditSkip[] with collection, reason, and a permissionError flag), and findings — so the caller can tell "audited and clean" from "could not audit".
• Added isPermissionError() to classify skips: MongoServerError code === 13 / codeName === "Unauthorized", or a message matching not allowed to do action (the exact $indexStats failure seen in CI). Added errorMessage() to stringify the reason safely.
• Still strictly read-only — only indexes(), $indexStats, $collStats, and find().limit() reads; no writes.

A) Honest exit codes (packages/services/scripts/db-audit.ts)

• MONGODB_URI absent → unchanged graceful skip, exit 0 (forked PRs / no secret).
• Any collection skipped, or zero collections inspected → exit non-zero (process.exitCode = 1) with a clear message; permission/privilege skips get an extra hint to grant the audit user indexStats/collStats/dbStats (or clusterMonitor). This is the false-green the old script produced in CI.
• All collections inspected, no findings → exit 0, "the database is lean" (the only legitimate green).
• All inspected, with findings → exit 0, findings surfaced as a non-blocking report (acting on findings is out of scope — it needs the now-honest live evidence).
• The top-level .catch no longer swallows errors to exit 0; an audit that errors out before finishing now fails loudly too (the MONGODB_URI-absent skip returns earlier, before any DB work, so it is unaffected).

B) Gated migrations (.github/workflows/db-migrate.yaml)

• Split into two environment-scoped jobs:
  • migrate-preview — environment: preview, runs pnpm db:migrate up on pull_request for same-repo PRs only (github.event.pull_request.head.repo.full_name == github.repository).
  • migrate-production — environment: production (required-reviewer protection = the human approval gate), runs on push to main (+ workflow_dispatch).
• Both read MONGODB_URI + MONGODB_DATABASE_NAME from their environment's secrets — no DB name hardcoded. Both keep the github.repository == 'sustentus/sustentus' canonical-repo guard.
• concurrency keeps cancel-in-progress: false; the group keys on github.ref, so a PR run and the main run never share a group (an in-flight migration is never killed).
• No rollback logic added — migrations stay forward-only and idempotent.
• Non-interactive migrations (scripts/db-migrate.ts, autosync: true, added after CI hung): ts-migrate-mongoose's default up prompts (interactive checkbox) when the migrations folder holds files not yet tracked in the target DB — which deadlocks a TTY-less CI runner. autosync imports those files in the down (pending) state and up then runs them, so every migration is applied unattended. Safe because migrations are forward-only and idempotent; already-tracked migrations are untouched.
• Production-target guard on migrate-preview (added after CI exposed the hazard): a fail-fast step aborts the PR job if the resolved DB is sustentus-prod — either MONGODB_DATABASE_NAME equals it or the URI contains it. This closes the transitional window where the preview environment has no secrets yet and secrets.MONGODB_URI falls back to the repo-level (production-pointing) value, so a PR can never migrate production. The production DB name is referenced only as a denylist; the migration target itself still comes from the environment's secrets, never hardcoded.

A) Audit workflow (.github/workflows/db-audit.yaml)

• Kept workflow_dispatch and the same-repo PR guard; added a nightly schedule (cron: "17 3 * * *") so leanness is tracked on a cadence, not only per PR.

Acceptance criteria status

• auditDatabase() distinguishes inspected vs skipped collections with reasons — AuditReport/AuditSkip, read-only.
• db-audit.ts exits non-zero with a clear message on a permission/privilege skip.
• db-audit.ts exits non-zero with a clear message when zero collections inspected.
• db-audit.ts still exits 0 when MONGODB_URI is absent.
• All inspected + no findings → exit 0, reports a clean database.
• db-audit.yaml retains workflow_dispatch + same-repo guard, adds a nightly schedule.
• db-migrate.yaml PR job runs db:migrate up against environment: preview, same-repo PRs only.
• db-migrate.yaml merge-to-main job runs against environment: production.
• Both jobs read MONGODB_URI + MONGODB_DATABASE_NAME from their environment's secrets — no DB name hardcoded.
• Both jobs keep concurrency cancel-in-progress: false + canonical-repo guard.
• TS changes follow CONVENTIONS.md (arrow functions, type over interface, async/await, named imports).
• PR notes the human-owned infra prerequisites — carried in the spec's Out of scope (Atlas claude_audit/migrator users; GitHub Environments preview/production with secrets + production required reviewers).

Verify result

• Mechanical checks (format · lint · typecheck · build) run in CI + the Vercel preview, not here.
• DB-touching behaviour (db:audit / db:migrate) is not runnable in the sandbox — outbound TCP 27017 is blocked (only HTTP/443). Verified by reading the audit logic and the workflow YAML, as the spec requires. The workflows only go green once the human-owned infra prerequisites are in place (Atlas audit/migrator users; GitHub Environments + secrets + production reviewers).

Notes for review

• The audit fails on any skipped collection, not only permission skips — a stricter, more honest reading than the literal acceptance criterion (which names permission/privilege skips). Any "could not audit" collection now blocks the green, which is the whole point of the pass.
• The production migration job also allows workflow_dispatch (manual re-run), still behind the production environment's required-reviewer gate — preserving the old workflow's manual trigger.

Database changes are now safe-by-default

Who it's for: Internal operations What shipped: Database schema changes now run gated — tested against a preview database on every PR, applied to production only behind a human approval gate — and the database health check fails loudly instead of passing silently. Why it matters: Hardens the technical infrastructure behind our 99%+ uptime objective; risky schema changes can't reach production unverified.

Dig deeper: <merged-PR URL>

Release: db-ci-gates

• pr: #489 — merged: no (pending — Ready to merge ticked, CI green)
• CI: green (Audit ✅, Migrate preview ✅, Migrate production skipped on PR, Format/Lint/Typecheck ✅, Vercel previews ✅)
• technical docs: updated in this PR — technical/development/ci-cd (Stage 4 + summary table) and technical/development/database (automatic migrations, auditing) now reflect the honest audit + gated preview/production migrations
• business docs: no business docs impact (internal infra; no user-facing behaviour change)
• release notes: investor-only — no end-user note (internal change); investor draft in this PR
• deploy: pending merge
• sent: pending green deploy

Review summary

• /code-review (high effort) on the full diff — no blocking findings. Audit branches (could-not-audit → exit 1 · clean → exit 0 · findings advisory → exit 0) are mutually exclusive and correct; the .catch fails loudly while the MONGODB_URI-absent skip returns first; the new AuditReport shape has one consumer (the script), updated. Migrate-preview guard fires only on a sustentus-prod target (no false-positive vs sustentus-preview); concurrency groups differ by ref.
• Non-blocking note: the prod-name denylist guard on migrate-preview is a transitional safety net (chosen with the user; documented inline) — accepted, not a follow-up.

Acceptance check (vs spec)

• auditDatabase() distinguishes inspected vs skipped collections with reasons — verified in src/db/audit/index.ts (AuditReport/AuditSkip) and proven green in CI on the provisioned claude_audit user.
• db-audit.ts exits non-zero on a permission/privilege skip — verified in the script's could-not-audit branch.
• db-audit.ts exits non-zero when zero collections inspected — same branch.
• db-audit.ts exits 0 when MONGODB_URI absent — verified (early return).
• All inspected + no findings → exit 0 — verified; CI audit went green.
• db-audit.yaml keeps workflow_dispatch + same-repo guard, adds nightly schedule — verified.
• db-migrate.yaml PR job runs against environment: preview, same-repo only — verified green in CI.
• db-migrate.yaml merge job runs against environment: production — verified (skipped on PR, runs on push to main).
• Both jobs read URI + DB name from environment secrets, no hardcoded DB name — verified.
• Both jobs keep cancel-in-progress: false + canonical-repo guard — verified.
• TS changes follow CONVENTIONS.md — Lint + Typecheck green.
• PR notes the human-owned infra prerequisites — present in PR body.