Preview database runbook

CI migrates and seeds a real preview database on every pull request that can change the schema. This page is the operator’s reference for that wiring: where the configuration lives, why it must match Vercel, how to rotate it, what the seed does and does not cover, and how to catch a lagging preview database up on demand.

The mechanics of the migration engine itself are on database lifecycle; the workflow’s place in the pipeline is on CI/CD.

Where the configuration lives

Settings → Environments → preview for the first two; Settings → Environments → production and Settings → Secrets and variables → Actions → Variables for the rest.

The database name is a variable, not a secret

A connection URI carries a password and is a secret. A database name is not, and registering it as one actively breaks the feature: GitHub masks a secret’s value everywhere it appears in a job, job summaries included. While MONGODB_DATABASE_NAME was a secret, the migration summary — whose entire job is to report which database was touched — could only ever print ***.

Masking is by value, not by source. Reading the name from vars does not unmask it while any secret in the job’s scope still holds the same string. So converting it takes two steps, in order:

1. Add MONGODB_DATABASE_NAME as an environment variable on preview.
2. Delete every MONGODB_DATABASE_NAME secret in that job’s scope — the environment’s and the repository’s. Step 1 alone is not enough.

The failure mode this produces is worth recognising, because it looks nothing like a configuration problem: a green check, no error, and *** where the database name should be.

Both jobs resolve the name as ${{ vars.MONGODB_DATABASE_NAME || secrets.MONGODB_DATABASE_NAME }}, so the secret still works as a fallback and no migration goes red mid-conversion.

Precedence — repository values reach every job

GitHub environments do not inherit from one another, and vars merges repository-level and environment-level values with the environment winning for the same name. Because || takes the first non-empty value, a repository variable outranks an environment secret of the same name. That is why migrate-production asserts the resolved name is sustentus-prod before it runs, and why migrate-preview keeps its denylist guard: preview refuses to be production, production refuses to be anything else.

Vercel parity — the requirement CI cannot check

migrate-preview must target the same database that Vercel’s Preview scope serves. Nothing in CI can prove it: the two values live in different dashboards and are only comparable by eye.

Vercel → the project → Settings → Environment Variables → the Preview scope holds MONGODB_URI. The database it names must be the one the GitHub preview environment migrates. If they diverge, every preview URL runs new code against an unmigrated database — the “my changes aren’t there on the preview” symptom — while the migration check stays green.

Check parity whenever either side is edited. The migration job’s summary names the database it migrated, so the comparison is a glance at that line against the Vercel value.

Rotating the credentials

1. Rotate the Atlas user’s password and take the new connection string.
2. Update MONGODB_URI in the Vercel Preview scope and in the GitHub preview environment. Both, or CI and the preview deployment drift apart.
3. Re-run Migrate preview database on any open pull request (or dispatch the workflow — below) and read the summary: it names the database and reports the migration outcome.

If the name changes as well as the credentials, update the preview environment variable too, never as a secret.

Missing configuration now fails red with a message naming what is absent and where to set it. There is no silent skip: a green Migrate preview database check means a migration actually ran.

Catching a lagging preview database up

The pull_request trigger filters on packages/services/** and the workflow file itself, so a run of app-only pull requests leaves the preview database behind. Rather than push a dummy commit:

Actions → Database migrations → Run workflow → target: preview (the default) → Run.

The target input takes preview or production, and each job gates on it, so one manual run migrates exactly one database. A bare dispatch goes to preview — the safe target is the default. Choosing production runs migrate-production instead, against the production environment.

A preview dispatch seeds as well as migrates: it is the same job, so the catch-up path is also the way to re-seed a preview database whose baseline was cleared.

The baseline seed

Migrating a database and populating it are different problems, and CI does both: after db:migrate up succeeds, the same job runs db:seed. A schema-correct but empty preview database renders half-broken preview URLs for reasons that have nothing to do with the pull request under test, which is the failure this step removes.

The seed is create-if-missing throughout — existing data is never overwritten — so running it on every schema-touching pull request converges on a baseline rather than accumulating duplicates. A failure fails the job red, exactly like a migration failure; and because the migration step exits non-zero on failure, nothing is ever seeded against a half-migrated schema.

What it seeds

Two things this table is easy to misread:

• Statuses are not seeded. They are declared in packages/services/src/db/workflows/workflows.json and read in-memory, never from the database. Lead lifecycle and every other status-driven surface works on a preview URL whether or not the seed has ever run — do not debug a status problem by reaching for the seed.
• Tenant defaults need a tenant. They are seeded for the tenants already in the database, so on a preview database with none, Tenant defaults: 0 created, 0 already present is the correct reading, not a fault. Once a tenant exists, the next preview migration fills its defaults.

Production is deliberately not seeded from CI. migrate-production migrates and stops; whether production should be seeded is a separate decision, not a side effect of this one.

The demo world on preview

The baseline seed makes a preview database schema-correct and non-empty. It does not give you anything to look at: no engagements, no invoices, no populated dashboards. That comes from the demo storyline, the same world the production demo tenant shows — and putting it on preview is a manual, repeatable sequence rather than something the per-pull-request job does. The seeder appends a batch of engagements per run, so seeding on every pull request would accumulate rather than converge.

Preview runs a separate Clerk instance from production, so none of production’s organisation or user identities exist there. Nothing is copied across; the preview world is stood up on its own.

Standing it up

1. Sign in to a preview URL with your own account, in the preview Clerk instance’s demo organisation. The tenant document is created lazily on that first sign-in — until somebody has signed in, there is no tenant to flag.
2. Set DEMO_TENANT_CLERK_ORG_ID as a variable (never a secret — the job summary has to be able to print it) on the GitHub preview environment, to that organisation’s org_… ID. This is the preview instance’s org; production’s lives on the production environment and the two must never be crossed.
3. Let the migration flag it. 1786492800000-flag-demo-tenant.ts sets isDemo: true on the tenant matching that variable, and migrate-preview now carries the variable, so the next schema-touching pull request flags the tenant. If the migration was already recorded as applied against the preview database before the variable existed, 1786579200000-reflag-demo-tenant-preview.ts re-asserts the same write — an applied migration never re-fires, which is the whole reason that second migration exists.
4. Seed the storyline on any preview URL, signed in as an admin: Configure → Demo data (/admin/demo-data) → Reset demo data. That path purges and re-seeds, so it converges on the approved world instead of stacking another batch on top — it is also the re-seed action from here on. It refuses any tenant not flagged isDemo: true, which is what step 3 is for.

Any preview URL will do at steps 1 and 4: every preview deployment reads the one shared preview database, so where you seed from does not matter.

The one sharp edge

isDemo is written only by migrations, and a migration runs once per database. If the preview database is ever rebuilt from scratch, both flag migrations replay from empty and the flag comes back on its own. But if a recorded migration ever no-ops against a database — the variable unset at the moment it ran — nothing re-fires it, and the remedy is another migration, which is exactly what 1786579200000-reflag-demo-tenant-preview.ts is.

That matters more than it used to: isDemo now gates the demo-data seeder as well as the reset, so an unflagged demo tenant means the Populate demo data button refuses rather than silently writing fixtures into a real tenant.

Whose faces you see

The storyline names twenty people, all on one convention — jamie.nisbet+<lastname>.<role>@sustentus.com — and pnpm demo:roster provisions every one of them as a real Clerk login against whichever instance CLERK_SECRET_KEY points at. Preview and production are separate Clerk instances, so the command is run once against each; the result is the same twenty seats either side, differing only in their Clerk user ids. That is the point of a fixed roster: a preview walkthrough and a production walkthrough show the same faces.

Provision before the first seed. The seeder attaches every seat to a real Clerk-backed user and creates nobody, so it refuses on a tenant whose roster is not in place — and because it writes each person’s storyline attributes on every run rather than only at creation, a row the roster made first is dressed by the next seed rather than left bare:

pnpm --filter @sustentus/services demo:roster --check   # report, write nothing
pnpm --filter @sustentus/services demo:roster           # provision

The full sequence, and the two Clerk-side prerequisites the roster needs in both instances, are in the demo environment runbook.

Reading the result

The job writes to its summary rather than burying the outcome in logs — one block for the migration and one for the seed:

• the resolved database name,
• whether the migration succeeded, and the migration output in a collapsed block,
• whether the seed succeeded, its created / already-present counts per group, and the seed output in a collapsed block.

Connection strings are scrubbed out of both blocks, so a driver error quoting the URI cannot leak a credential into the summary. If the name still renders as ***, a secret somewhere in the job’s scope holds that same value — delete it and re-run.

The counts are how idempotence is observed rather than assumed: a run that changes nothing reports 0 created, with the same number already present as the run before it.

A non-zero created count is not by itself a fault. Three routine things produce one:

• a new tenant exists in preview — it gets the full per-tenant set at once,
• a new action type was added to the seed’s reference data, or
• a new default-bearing tenant setting was added to the settings registry.

Action types also auto-create on first use, so a database that has been exercised but never seeded will already hold some of them and the first seed run fills only the gaps. What is worth investigating is a non-zero count with none of those explanations — that suggests rows were removed.