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 (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, behind the production environment’s required-reviewer gate. Both read MONGODB_URI / MONGODB_DATABASE_NAME from their environment’s secrets, 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. Until an environment’s secret is configured the job skips gracefully; a failed migration surfaces as a red status check.

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.

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.

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). 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 pipeline/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 pipeline/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.