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.

Per-branch previews — own database only when the PR adds a migration

With MONGODB_PREVIEW_PER_BRANCH=1 set on both sides — the repository variable in GitHub and the Vercel Preview target — a pull request’s preview gets a database of its own, preview_<branch>, only when the PR adds a migration file: a file under packages/services/src/db/migrations/ that is on the branch and not on its merge-base with main. Unset both to go back to one shared preview database for everything.

• Model-only and index-only changes do not qualify. They run against the shared database, which the push-to-main run keeps at main’s shape. That is the accepted trade-off for not copying a database per pull request — a copy per PR filled the M0 staging cluster.
• How the app decides. The name is still derived by databaseName() in .icm/scripts/lib/db-name.mjs; packages/services/src/db/database-choice.ts opens that name only when the database exists on the cluster, and otherwise falls back to MONGODB_DATABASE_NAME. The check runs once per cold start.
• The one race. A request that lands before CI’s first copy finishes reads the shared database until that serverless instance recycles. Start a persona walk or a hand check only after the migrate job has finished, and redeploy the preview if one ran inside that window.
• Cleanup. mongodb-cleanup.yaml drops preview_<branch> when the PR closes. Stale databases left by earlier runs: bash .icm/scripts/db-env.sh status, then db-env.sh prune --apply, with MONGODB_URI pointing at the staging cluster.

Where the configuration lives

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

No workflow reads Clerk keys from the preview environment any more: Preview smoke, which did, was retired (D43, 2026-09-24). The persona walk now runs by hand, with the Clerk pair in the operator’s own shell (below).

The Clerk pair belongs to the preview deployment, not to you

The operator’s persona walk (E2E_BASE_URL=<web preview URL> pnpm e2e, or an agent’s .icm/scripts/e2e.sh walk <base-url> on request — see the persona walk) mints sign-in tokens for the six demo seats and redeems them in a real browser against the preview URL. The CLERK_SECRET_KEY and NEXT_PUBLIC_CLERK_PUBLISHABLE_KEY exported in the shell for it must be the Clerk instance the preview deployment itself authenticates with — the same values Vercel holds for the web project’s preview scope. Anything else (production’s keys, a developer’s own) fails every seat at once with “not in this Clerk instance”, which looks like a missing roster rather than a key mismatch.

It must also be a development instance: clerkSetup refuses a production secret key by design, and production instances issue no Testing Tokens, so Clerk’s bot detection would stop the walk before the first sign-in.

Preparing a walk — the operator steps

1. Find the Clerk instance the web preview authenticates with. Vercel → the web project → Settings → Environment Variables → the Preview scope → NEXT_PUBLIC_CLERK_PUBLISHABLE_KEY. It is a pk_test_… key: the instance name is base64 inside it, and the same key is embedded in any web preview’s sign-in page, which is the quickest cross-check. If that scope holds a pk_live_… key, stop — the preview is authenticating against a production instance, and no walk can run against it (see the previous section). Fix the deployment first.
2. Take both keys from that instance. Clerk Dashboard → the application → the Development instance (never Production) → Configure → API keys: the publishable key (pk_test_…) must be the one Vercel holds; the secret key is the matching sk_test_…. Export both in the shell the walk runs from, with the demo organisation’s org_… ID in that same instance as DEMO_TENANT_CLERK_ORG_ID (Clerk Dashboard → Organizations → the demo organisation; see Standing it up). apps/e2e/README.md → Running it shows the full invocation; e2e.sh checks the keys by name before any browser opens.
3. Check the roster is provisioned in that instance. The walk signs in as the six demo seats and creates nobody, so pnpm --filter @sustentus/services demo:roster --check against the preview instance’s CLERK_SECRET_KEY must report every seat present (whose faces you see). A missing seat fails the walk at once, naming the persona — not with a navigation timeout.
4. Run the walk against a ready PR’s web preview, once migrate-preview has finished, and read its summary (apps/e2e/README.md → The walk summary).

A failure that names a persona and a route is a real result: either the walk drifted (a renamed route, a moved sidebar entry — repair apps/e2e with the browser-smoke skill) or the page is broken (park a triage stub).

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, and draft pushes skip the job entirely (the ready-for-review flip is what migrates a draft-written schema change), 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.

One writer at a time — the migration lease

Two runs migrating the shared preview database at once break each other: one PR’s pending migration is re-inserted between another’s prune and up, and that up crashes importing a file only the sibling branch has. Production must never run two migrations at once either. So every run that writes a long-lived database — the shared preview database (a PR with per-branch previews off, a push to main or a manual run with them on) and production — takes the migration lease first:

• db:lease acquire writes one document to that database’s migration_lease collection, holding it under the run’s own URL. If another run holds it, the run waits and says whose it is waiting on.
• db:lease release gives it back after the migrate (and the seed, on preview), and runs even when the job fails or is cancelled.
• A run that waits 20 minutes without getting the lease fails red, naming the run that holds it. A lease whose holder died without releasing it expires after 30 minutes, and the next run takes it over.

The workflow does not use a shared GitHub concurrency group for this. A group holds one pending run, and a third run evicts the waiting one as cancelled — a red check on a PR, or a cancelled run on a main commit, for nothing. A PR’s runs share a group of their own (a newer push replaces its own older pending run, which is intended — only the head’s check counts). Pushes and manual runs share none. A production run waiting for its reviewers’ approval holds nothing: the lease is taken inside the job, after the approval.

A per-branch database takes no lease: one PR’s runs already go one at a time.

The logic and its timings are in packages/services/src/db/migration-lease.ts, and the jobs’ timeout-minutes (30) is sized to the wait plus the work.

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 without a database of its own will do at steps 1 and 4: those previews all read the one shared preview database, so where you seed from does not matter. A preview of a pull request that adds a migration reads its own copy, and seeding there changes only that copy.

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.