A staging environment per branch, database included

Preview deploys are table stakes. Open a pull request, get a URL, share it with design. Every hosting platform does it and it's genuinely great.

Then look at what that preview connects to. In most teams: the one shared staging database. Which means the preview isn't an isolated environment at all — it's a copy of the app pointed at shared state. And that's why two branches with conflicting migrations still break each other, why someone's test data still shows up in your review, and why 'it worked in preview' still doesn't mean much.

What the shared staging database costs you

• Migration conflicts. Branch A adds a column, branch B renames it. Both previews now run against a schema that matches neither, and someone spends a morning working out whose migration ran last.
• Data pollution. Your reviewer clicks through and sees three test orders from a load test somebody ran on Tuesday.
• Fear of destructive changes. Nobody tests a data migration properly, because the shared database is the only one with realistic data and breaking it blocks the team.
• Sequencing. Branch B's preview can't be reviewed until branch A's migration is reverted, and now your review process has a dependency graph.
• Nobody dares reset it. The shared database accumulates hand-edits until it's a fragile artifact everyone works around.

The fix is the same one that fixed preview apps: stop sharing. Each branch gets its own database as well as its own app instance.

Seed or clone: the choice that decides everything else

Two ways to populate a per-branch database, with different properties. Pick deliberately.

Seeded from fixtures

Run migrations from scratch, load a fixture set. Fast, small, no privacy concerns whatsoever, and reproducible. It's the right default for most branches, and it's the only sane option if untrusted people — contractors, an open-source contributor, a design agency — will see the environment.

The limitation is the one every seed script has: it contains the data the author imagined. Reviewing a UI change against 12 tidy rows tells you nothing about how it handles a customer with 4,000 orders and a company name containing an emoji.

Cloned from production

Clone the real database. Realistic volume, realistic weirdness, real query plans, and the ability to verify that a migration completes in a sane amount of time on actual data.

The cost is that it contains customer data. A clone must be anonymised before anyone gets the connection string, outbound endpoints and email queues must be neutralised, and access must not be broader than production's. This is a real obligation, not a checkbox — a preview environment with production data and team-wide access is a bad idea wearing a helpful costume.

Wiring it up

The shape is a CI job on pull-request open that creates the pair, deploys, comments the URL, and — the part people skip — a job on close that destroys both.

#!/usr/bin/env bash
# .github/workflows/preview.sh -- runs on pull_request opened/synchronize
set -euo pipefail

API=https://api.pandastack.ai/v1
AUTH="Authorization: Bearer $PANDASTACK_API_KEY"
SLUG="pr-${PR_NUMBER}"

# 1. A database for this branch. Cloning an already-migrated template
#    beats rebuilding: no migration run, no fixture load per preview.
DB=$(curl -sS -X POST "$API/databases/$TEMPLATE_DB_ID/clone" \
  -H "$AUTH" -H 'Content-Type: application/json' \
  -d "{\"label\":\"$SLUG\"}" | jq -r .id)

# create/clone is async and takes 30-90s: the API waits until Postgres
# genuinely accepts connections rather than returning 'provisioning'.
until [ "$(curl -sS -H "$AUTH" "$API/databases/$DB" | jq -r .status)" = running ]; do
  sleep 5
done
DB_URL=$(curl -sS -H "$AUTH" "$API/databases/$DB" | jq -r .connection_url)

# 2. An app for this branch, pinned to the PR head commit.
APP=$(curl -sS -X POST "$API/apps" -H "$AUTH" -H 'Content-Type: application/json' \
  -d "{\"name\":\"$SLUG\",\"git_url\":\"$REPO_URL\",
       \"git_branch\":\"$PR_BRANCH\",\"auto_deploy\":true,
       \"env\":{\"DATABASE_URL\":\"$DB_URL\",\"APP_ENV\":\"preview\"}}" | jq -r .id)

curl -sS -X POST "$API/apps/$APP/deploys" -H "$AUTH" \
  -H 'Content-Type: application/json' -d "{\"git_ref\":\"$PR_SHA\"}"

# 3. Record the ids on the PR so teardown does not have to guess.
gh pr comment "$PR_NUMBER" --body \
  "Preview: https://$APP.pandastack.app/"$'\n'"app=\`$APP\` db=\`$DB\`"

And the half everyone forgets, which is the difference between a nice feature and a growing bill.

#!/usr/bin/env bash
# runs on pull_request closed -- merged or not
set -euo pipefail

API=https://api.pandastack.ai/v1
AUTH="Authorization: Bearer $PANDASTACK_API_KEY"

# Delete both, tolerating already-gone. Teardown must be idempotent:
# it will be re-run, and it must not fail the workflow when it is.
curl -sS -X DELETE "$API/apps/$APP_ID"      -H "$AUTH" || true
curl -sS -X DELETE "$API/databases/$DB_ID"  -H "$AUTH" || true

# Belt and braces: a scheduled sweep that deletes anything labelled pr-*
# whose pull request is closed. Teardown jobs do not run when a workflow
# is cancelled, a repo is archived, or someone force-deletes a branch.

Keeping the cost honest

Two environments per open pull request sounds expensive and is, if they all run continuously. Three things keep it reasonable.

• Sleep them. A preview environment is used for a few minutes during review and idle for the rest of its life — the textbook case for scale-to-zero. Watch for the failure mode where automated traffic keeps them awake: a preview URL posted in a chat channel gets crawled by link-preview bots, and if that resets the idle timer nothing ever sleeps.
• Delete on close, plus a sweep. Cleanup jobs fail to run more often than you'd think — cancelled workflows, force-deleted branches, archived repos — so a scheduled sweep over anything labelled by pull request is the real safety net.
• Cap concurrency. Twenty open pull requests is normal; two hundred stale ones on a large repo is also normal. A cap with a clear error beats an unbounded bill, and it surfaces the stale-PR problem you already had.

The best part: migrations become safe to review

The clearest payoff isn't the preview URL, it's what happens to schema changes. With a per-branch database, a migration runs in an environment where breaking it costs nothing, against data that resembles production, before anyone merges it.

That turns a whole category of incident into a build check. Time the migration and fail the pull request when it exceeds a budget. Run the app's read path against the migrated schema to catch the case where a column rename compiles and then breaks a query. Test the rollback, which is otherwise a thing nobody ever runs until the night they urgently need it to work.

# In the preview job, after the branch database exists:
START=$(date +%s)
psql "$DB_URL" -v ON_ERROR_STOP=1 -f migrations/latest.sql
ELAPSED=$(( $(date +%s) - START ))

echo "migration: ${ELAPSED}s against production-shaped data"
[ "$ELAPSED" -lt 30 ] || {
  echo "::error::migration exceeds the 30s budget -- needs CONCURRENTLY"
  echo "or an online backfill strategy before this can merge"
  exit 1
}

# Then prove the rollback works, in the only environment where it is safe
# to find out that it does not.
psql "$DB_URL" -v ON_ERROR_STOP=1 -f migrations/latest.down.sql

Is it worth the setup?

If your team is two people on one branch at a time, no — a shared staging environment is fine and this is complexity you don't need. Be suspicious of anyone selling you infrastructure for a coordination problem you don't have.

It pays for itself with more than about four engineers working in parallel, or any team where migrations are frequent enough to collide. The tell is qualitative: when 'wait, don't merge yet, my migration is on staging' has been said out loud in your team chat more than twice, you're paying for the shared environment in coordination overhead, and that cost doesn't appear on any invoice — which is exactly why it goes unfixed for years.