RFC: Golden Seed Database
A shared, scrubbed, FK-consistent Postgres baseline — built once, restored everywhere — rolled out crawl → walk → run.
💬 Feedback wanted — this is a draft. Weigh in on the GitHub Discussion before we commit. Open questions are flagged in Risks & open questions.
TL;DR
Reproducible seed in CI
A seed from existing dev data. No scrub. Unblocks E2E in days. Shipped — see the Crawl panel for what landed.
Scrubbed prod subset
FK-safe subset of production, PII-masked via Greenmask. Feeds dev DBs + previews.
Copy-on-write branches
Per-PR ephemeral databases off the golden baseline. Powers preview envs.
Keystone: if you follow expand-and-contract migrations, you get a free regeneration cadence. Expand migrations are additive and backward-compatible, so an old seed dump always replays cleanly up to head — the dump may lag many migrations at no cost. Only contract phases (drops/renames/type changes) force a re-baseline, and those are rare and already heavily reviewed. That single rule kills the seed-drift trap that defeats most golden-seed efforts.
Two prerequisites make this tractable — what any team would need to replicate it:
- Machine-readable per-column data classification — lets the scrub config generate itself (details).
- Disciplined expand-and-contract migrations — gives the regeneration cadence.
Without both, the walk stage is far more manual.
Motivation
Local DBs are stood up ad-hoc and drift per developer; the checked-in compose file is stale; the legacy bootstrap replays the entire migration history from a years-old dump and prompts interactively. No seeded DB sits behind E2E in CI at all.
The E2E suite creates → uses → deletes its own data; only an immutable login identity is shared; assertions are on own data, not global counts. This narrows the seed for E2E to a skeleton — but E2E is one of several consumers.
Local dev DBs, PR preview envs, and static envs all benefit from realistic content. The blockers were always the same two: shrinking prod without breaking FKs, and scrubbing PII. Both are solvable now.
Proposal
Consumers and requirements
The golden seed is a shared platform artifact, not just test infra. Four consumers, different needs:
| Consumer | Needs from the seed | Data richness | Reset / refresh model |
|---|---|---|---|
| E2E tests | login identity + full lookups + bootable baseline; tests create/delete own data | minimal skeleton | full reset per run/branch |
| Local dev DBs | realistic, identical across devs | rich | full reset on demand |
| PR preview envs | realistic, isolated per PR, demo/QA-able | rich | branch/restore per PR, destroy on close |
| Static / long-lived envs (stretch) | refreshable ephemeral seed data without clobbering persistent user data | rich | partial refresh — see Future |
E2E alone would justify only the crawl-stage minimal seed. Local dev DBs, PR previews, and static envs are what pay for the rich walk + run stages. The stretch consumer also imposes one design constraint to honor from day one even though it's built last — seed-row provenance (see Future).
Core model: restore a baseline, then migrate to head
The seed is a dump pinned to a baseline migration revision N, not a perpetually-current snapshot. Every use does:
pg_restore -j4 -O -d "$DB_URL" golden-baseline.dump # baseline @ rev N
# … then run the migration tool to head # replay rev N+1 .. headExpand-and-contract makes this safe: expand migrations are additive and backward-compatible (nullable columns with defaults, new tables, constraints added NOT VALID then validated separately), so replaying them on an older seed always succeeds and never rewrites data destructively. The DB is always at head regardless of how stale the dump is, and the replay window equals "migrations since the last baseline" — kept short by re-baselining at each contract.
flowchart LR
A[Prod copy] --> B[Subset and scrub]
B --> C[Golden baseline dump]
C --> D[Restore in CI]
D --> E[Migrate to head]
E --> F[Tests and envs]
C --> G[CoW branch per PR]
G --> E
style C fill:#e6f3ff,color:#000
style E fill:#fff4e6,color:#000What the seed must contain — the floor
Because tests own their full data lifecycle, the seed is not a fixture library. This is the floor every consumer needs; E2E needs only the floor, while dev / preview / static layer rich data on top.
- The immutable login identity — the exact user + tenant + role the auth flow expects. A hard contract: every suite assumes it exists and never mutates it. Scrub/regen must preserve it verbatim.
- Complete lookup / reference tables — type lookups, roles, statuses, permission matrix, feature flags. Rows tests create must FK-resolve against a full set of these. Always full-copy, regardless of subsetting.
- A bootable, migrated baseline — enough structural data that the app renders without empty-state crashes.
Parallelism: because tests self-isolate (unique prefixes + cleanup) on one shared stack, per-worker DB isolation isn't required for correctness — one seeded DB serves all workers. Per-PR/per-worker copies (Postgres TEMPLATE databases, branches) are an optional clean-slate convenience.
Regeneration cadence keyed to the contract phase
The core maintenance discipline, straight out of expand-and-contract:
| Migration phase | Regenerate the baseline? | Why |
|---|---|---|
| Expand (add column/table, additive) | No | Restore + migrate-to-head absorbs it; the seed may lag. |
| Migrate / backfill | No | Data-only; replays fine. |
| Contract (drop/rename/type-change) | Yes | Old shape is gone for good — cut a fresh baseline so the seed stops carrying dropped columns and the replay window stays short. |
Contract changes are already rare and gated by a multi-point review checklist. Add one line — "golden seed baseline regenerated" — and regeneration rides along at near-zero cost. The opposite of regenerating on every migration.
The rollout
Crawl — reproducible seed in CI off dev data
Goal: E2E green in CI, fast, no scrub.
- A checked-in
docker composePostgres service (pinned minor version) as the canonical local + CI database — the shared replacement for today's ad-hoc per-developer setups. The repo file is the source of truth. - CI: start Postgres → restore the baseline → migrate to head → apply the seed. (A cached
pg_dump -Fckeyed on baseline-rev + seed-hash is the eventual speed-up; the seed is fast enough that crawl shipped without it.) - Source = current dev seed (no scrub). Include the full lookup/reference tables and the login identity.
Ships a reproducible CI database immediately, zero scrubbing/subsetting infrastructure.
✅ Implemented. What landed (operational details live in the living docs, not here):
- Artifacts in
devtools/db/:schema-baseline.sql(schema-only dump of dev @ head —awsdms/pglogicalreplication objects stripped, role-bootstrapped),seed_data.sql(secret-free, data-only: lookups + login identities + per-tenant fixtures),build_seed.sh/build_schema_baseline.sh(regenerators with a secret self-gate),docker-compose.yml. - Targets:
make db.seed/db.reset/db.regen. Loginadmin@example.com(vendor 0) /customer@example.com(vendor 1). - CI:
.github/workflows/e2e.yml— per-suite matrix + path-filters (PR runscore+ touched domains; shared/seed change or push-to-devruns all). Serves a built bundle (PREVIEW_MODE) withCIunset for the app (CI=true ⇒ backendqa_modehides login). - Maintenance between stages: the
/golden-seed-updaterouting skill. - Where the how-to lives: repo
README.md(local DB bootstrap) anddocs/dev-setup/writing-e2e-tests(running/authoring E2E against the seed). This panel keeps the decision; those keep the instructions.
Keeping the seed current between stages — the /golden-seed-update skill
Until the walk stage automates rich data from prod, features still add data the seed should reflect (a new lookup, or an E2E fixture a spec depends on). The trap is hand-editing seed_data.sql — that slides back into a drifting, hand-maintained seed (the rejected status quo). The discipline that avoids it is a routing rule, encapsulated in the /golden-seed-update skill (.claude/skills/golden-seed-update/).
The deciding question: does prod need this row? flask db upgrade runs in every environment including prod; the seed does not. So each new piece of data has exactly one correct home:
| Data a feature adds | Goes in | Result for the seed |
|---|---|---|
Product reference prod needs (task type, feature-flag default, or_object_type, integration action, status code) | a data migration (op.bulk_insert) | free — migrate-to-head replays it; no seed edit |
Dev/test-only fixture (sample project, a cached HubSpot or_integration an E2E spec reads, demo content) | the seed generator (build_seed.sh), then make db.regen | regenerated + secret-gated + diffable |
| Transactional data a test creates | nothing | the test owns its lifecycle |
Two hard guards. (1) No fixtures in migrations — a fake integration or demo project in a migration ships to prod permanently (worse than the dummy-data-in-migrations anti-pattern, because it's global). (2) No secrets in either — the generator scrubs; keep it generated, never hand-append raw INSERTs.
So "update the golden seed" usually resolves to "put real reference rows in your migration" (which prod needs anyway) — and only genuine dev/test fixtures flow through the generator. The skill performs the routing, enforces the guards, regenerates via build_seed.sh, and validates by replaying the migration + running the affected E2E suite against the regenerated seed. Migration SQL craft is delegated to the backend-development skill; column sensitivity to sql-tool-exposure. This is the interim, per-feature bridge — the walk stage's classification-driven scrub (next section) is the eventual automation.
The scrub manifest is generated from data classification
This is the highest-leverage piece, and the main prerequisite for copying this approach. Every column carries a machine-readable sensitivity classification, and a CI gate fails the build if any column is unclassified — so coverage is effectively 100% with no manual catalog to maintain.
A small codegen step walks the classified columns and emits the masking config — classification → scrub action:
| Classification | Action |
|---|---|
| Confidential — name / email / phone / free-text PII | fake — type-appropriate generator |
| Internal / secret — passwords, tokens, financial | null / redact |
Public + audit columns (id, uuid, *_at, *_by) | passthrough |
| Owner / tenant FKs | deterministic pseudonymize — preserve referential integrity, never random |
- Virtuous loop with expand-and-contract: every new column is classified when it's added (CI enforces it), so the next contract-triggered regeneration picks it up automatically — new confidential columns get scrubbed with no manual config edit.
- Snapshot the generated config and diff it in code review, so any classification change is visible.
A row-masking extension (e.g.
anon) can declare the same rules as in-DBSECURITY LABELs, which map 1:1 to per-column classification. We default to the external (Greenmask) path — no extension/superuser, portable dump — but the in-DB path is a viable alternative driven by the same codegen.
CI wiring — measured: local db.seed is the floor
A cached pg_dump fast path (the "build once, restore everywhere" idea) was built and measured, then reverted — it didn't pay for itself:
- Seed wasn't the bottleneck.
db.seedis ~21s of a ~250s e2ecorejob (~8%); the job is dominated by test execution (~88s), boot (~32s), and the FE build on a cache miss (~37s). - The cache saved ~1s.
db.restore(pg_restore + the sameflask db upgradereconcile) lands within ~1s ofdb.seed— both pay the Flask-import reconcile and the baseline load was already fast. Net ~0.4% of the job, and it caused a main-CI outage (cluster-global roles aren't captured by a DB-scopedpg_dump). - Neon (copy-on-write branch) is net-slower, even co-located. Spiked from a GH runner: US-West Neon = 610 ms/connection → +9 to +86 s/suite; US-East (co-located) Neon = 160 ms/connection → still +17 to +52 s/suite. Two region-robust killers: a ~+20 s boot penalty (the app's startup DB connections over TLS/pooler, identical W and E) and per-connection TLS/pooler cost on chatty suites. Concurrency was fine (one branch took the whole matrix with no pooler errors) — latency, not connections, is the wall.
Conclusion: local db.seed (local socket, no TLS) is the fastest DB option; the cache and Neon are both ruled out by measurement. The per-suite seed redundancy can't be cheaply removed (matrix jobs are separate runners; only a shared DB collapses it, and the only shared option — Neon — is slower). The real lever is boot/init time (serve mode, lazy imports, deferred init — which also helps prod Lambda cold start), pursued in a separate PR. Phase 0 (the shared composite action) stays — a pure de-dup win.
Future: static environments with refreshable seed data
Out of near-term scope, but it constrains one decision now, so it's worth stating.
Goal: a long-lived shared environment whose seed data can be refreshed (so it never goes stale against head) while user-created data persists. One DB, two data classes:
- Ephemeral (seed-origin) — everything the golden seed inserts. Regeneratable.
- Persistent (user-origin) — everything users create in the env. Must survive a refresh.
A refresh is then an idempotent upsert + prune scoped to seed-origin rows: re-apply the current seed set by stable key, delete seed rows no longer in the manifest, never touch user rows.
The one decision to make now (cheap insurance): give every seed-origin row durable provenance so a future refresh can target it — a stable, deterministic UUID set for seed rows, plus either an is_seed flag or a reserved-tenant convention. Keep the seed FK-self-contained so the upsert respects insert order.
The hard part — user data referencing seed data
If a user row FK-points at a seed row, pruning that seed row orphans it. Two containment rules make it tractable:
- Lookup/reference tables refresh upsert-only, never delete — append-mostly and stable, so user references always survive.
- User activity stays out of seed tenants — seed-provided content is read-mostly; real user work happens in user-created tenants. If that convention can't hold, the split degrades to tear-down + reseed (losing user data) — the status quo.
Alternatives considered
- Object-factory generation. Generate synthetic data with per-table factories (prototyped). Heavyweight to build and maintain, and largely obviated by the dump/restore + Greenmask path — a subsetter already solves FK insert-ordering, a factory framework's hardest part. Shelve; revisit only if we ever need fully synthetic data.
- Regenerate the baseline on every migration. Simpler trigger, but expensive and unnecessary given expand migrations replay safely. Rejected for the contract-keyed cadence.
- Never regenerate / hand-maintain a seed. The status quo and the source of the drift trap. Rejected.
- In-DB masking extension instead of an external tool. Elegant — rules as
SECURITY LABELs map 1:1 to classification — but needs an extension + superuser. Greenmask needs neither and emits a portable dump, so it's primary; the extension is the alternative, same codegen. - Dead / avoid subsetters: Neosync (archived 2025), Replibyte (stagnant since 2022), standalone Condenser (2019), rdbms-subsetter (archived 2020).
pg_sample/pgcopydbare maintained but lack polymorphic support.
Risks & open questions
The whole suite depends on an exact immutable login identity in the seed. If scrub/regen changes it, all E2E breaks at once.
Mitigation: pin the identity in an allowlist the codegen preserves verbatim; assert it in a pre-flight check.
Who runs the prod → scrub pipeline, against which replica, and where does the pre-scrub copy live transiently?
Mitigation: security sign-off gating the walk stage; scrub on an isolated box, never persist the raw copy.
Retrofitting seed-origin provenance across an existing seed + live env later is painful.
Mitigation: emit the marker from the first seed, even though refresh tooling comes last.
New polymorphic column pairs need a manual virtual_references entry.
Mitigation: a CI check that lists polymorphic pairs and fails if one is unmapped.
git-lfs (small) vs registry image (medium) vs object storage keyed by revision (large).
Mitigation: size-dependent; decide at the walk stage.
The restore-then-migrate model never downgrades the baseline.
Mitigation: a bad migration is fixed forward; document the invariant.
Open questions
Are preview + static envs committed enough to fund run? E2E alone needs only the crawl seed; the rich stages are paid for by dev / preview / static. Do those uses justify the run-stage build and hosting cost, or do we ship crawl + a rich local dump first and defer run?
Codegen output — one format or both? Should the scrub-manifest codegen emit external (Greenmask) config, in-DB security labels, or both from day one?