Database lifecycle — migrations, seeding & auditing

This page is the canonical reference for managing the Sustentus MongoDB across environments: versioned migrations (with their syntax and conventions), idempotent seeding of baseline data, and the DB auditor that keeps the database lean. Everything lives in @sustentus/services and runs from packages/services.

All three commands reuse the same connection env as the app’s connectDB — MONGODB_URI (required) and MONGODB_DATABASE_NAME (optional). Run them with the workspace filter:

pnpm --filter @sustentus/services db:migrate <command>
pnpm --filter @sustentus/services db:seed
pnpm --filter @sustentus/services db:audit

Migrations

Migrations are versioned, ordered, reversible changes to the database, tracked by ts-migrate-mongoose. Applied state is stored in the migrations collection, so each environment knows exactly which migrations have run — no more hand-run, untracked scripts.

Commands

pnpm --filter @sustentus/services db:migrate create <name>   # scaffold a new migration
pnpm --filter @sustentus/services db:migrate up [name]       # apply all pending (or up to <name>)
pnpm --filter @sustentus/services db:migrate down [name]     # roll back the last (or down to <name>)
pnpm --filter @sustentus/services db:migrate status          # list migrations and applied state

create copies src/db/migrations/template.ts to a timestamped file src/db/migrations/<unix-ms>-<name>.ts. The timestamp prefix fixes the run order.

Migration syntax & conventions

A migration exports two arrow functions, up and down, each receiving the raw mongoose Connection:

import type { Connection } from "mongoose";
 
export const up = async (connection: Connection): Promise<void> => {
  await connection
    .collection("leads")
    .updateMany({}, { $set: { isArchived: false } });
};
 
export const down = async (connection: Connection): Promise<void> => {
  await connection
    .collection("leads")
    .updateMany({}, { $unset: { isArchived: "" } });
};

Conventions:

• Operate through the raw connection.collection(...), not app models. Migrations must stay independent of the current schema, plugins (soft-delete, tenant) and validation — a model’s shape changes over time, but a migration is a fixed point in history.
• Keep up and down symmetric. down reverses exactly what up did, so a migration is reversible.
• Make migrations idempotent where practical (e.g. guard on a field’s presence) so a re-run is safe.
• One concern per migration. Small, focused, named for the change (add-lead-is-archived).
• Never edit a migration that has run in any shared environment — write a new one.
• Follow the repo code rules (arrow functions, async/await, type over interface) — migrations are linted and typechecked like any other source file.

Automatic migrations in CI

The Database migrations GitHub Action (.github/workflows/db-migrate.yaml) applies migrations through two environment-gated jobs: a preview job runs db:migrate up against the preview database on every same-repo pull request that touches packages/services/** (so a migration is exercised before merge, with a fail-fast guard that refuses to target production), and a production job runs it against production on push to main, asserting the resolved database name is sustentus-prod first. Both read MONGODB_URI (a secret) and MONGODB_DATABASE_NAME (a variable, falling back to a secret) from their environment, never kill an in-flight migration (cancel-in-progress: false), and are gated to the canonical repository so forked-PR code never receives a secret. Migrations apply non-interactively — migration files not yet tracked in the target database are imported and run unattended.

Missing configuration fails the job, in seconds, before checkout, with a message naming what is absent and where to set it. There is no skip path, so a green check means a migration really ran; the preview job additionally writes the database it migrated and the outcome to the job summary. A manual dispatch takes a target input (preview | production, default preview) and migrates exactly one database — the catch-up path when app-only pull requests have left preview behind. Operator steps — where the values live, Vercel parity, rotation, catch-up — are on the preview database runbook.

Seeding

db:seed idempotently upserts the baseline data every environment needs, so deployments don’t drift and operators don’t hand-enter datapoints:

• Global reference data (not tenant-scoped): activity action types. (The status workflow is not seeded — it lives in version-controlled JSON at packages/services/src/db/workflows/workflows.json.)
• Per-tenant defaults (create-if-missing): SLA definitions per stage and every default-bearing tenant setting from the canonical settings registry (packages/services/src/db/services/tenant-setting/registry.ts), so each tenant always has the full deterministic set.

pnpm --filter @sustentus/services db:seed

It seeds through the app’s models, so plugins, validation and indexes all apply, and it is create-if-missing — existing data is never overwritten, so a second run on a steady state makes no changes and creates no duplicates. The seed data lives in src/db/seed/. Add a new baseline datapoint by extending the relevant array there, not by hand-editing the database.

migrate-preview runs it automatically after every successful preview migration, so the preview database stays populated without anyone remembering to seed it; the job summary reports the created / already-present counts. Production is not seeded from CI — migrate-production migrates and stops. Details on the preview database runbook.

Auditing

db:audit is a read-only report that keeps the database lean. It scans every collection and flags unused or redundant indexes, missing tenant indexes, oversized documents, unbounded arrays, and orphaned tenant references.

For the tenant check, leading with tenantId is necessary but not sufficient: a partial index covers only the documents matching its filter, so the planner can use it only where the query provably implies that filter. It therefore does not count as tenant cover, and a collection whose only tenantId-leading index is partial is reported as missing one. leads hid behind exactly that gap — its sole tenant-leading index is partial on requestId, so the check passed while every dashboard read still scanned the collection.

pnpm --filter @sustentus/services db:audit               # print the report
pnpm --filter @sustentus/services db:audit --json <path> # also write the report as JSON

Findings on a fully-inspected database are advisory — they are written to the job summary, not failed on. But the check is honest: if it cannot actually inspect the collections (e.g. the DB user lacks indexStats) or inspects none, it fails loudly rather than report a false all-clear, and it exits 0 (skips) only when no MONGODB_URI is configured (.github/workflows/db-audit.yaml, same-repo PRs plus a nightly schedule). In CI it audits production — leanness is a property of the data that actually accumulates — via the repository variable MONGODB_DATABASE_NAME, and refuses to run if a URI is configured without one. The check logic and its thresholds live in src/db/audit/index.ts. Each finding carries a discrete target (the index name or field it is about) alongside its kind, collection, and message, so findings have a stable machine-readable identity.

From findings to action — the triage pass

db:audit detects; the db-auditor skill turns detections into trackable work. Its triage pass consumes the audit’s machine-readable JSON (db:audit --json <path>, so it runs even where the database is unreachable) and refreshes the .icm/intake/db-audit-findings/ intake epic — a breakdown.md backlog plus one stub per actionable finding, in the standard intake stub format. Each stub is matched by a stable finding-key (<kind>/<collection>/<target>), so re-running the triage refreshes a finding in place — never duplicating it — skips stubs already spun out (_done/), and prunes findings no longer reported. From there a finding is picked up exactly like a feature: /pipeline new .icm/intake/db-audit-findings/<finding-slug>.md carries its fix (a single migration) through Define → Build → Release. The triage is read-only against the database — its only writes are the local intake files. Apply any structural fix as a migration — never ad hoc; the db-auditor skill pairs with mongodb-query-optimizer and mongodb-schema-design for the deep work.