TL;DR
Our staging environments ran on anonymized production dumps: slow to refresh, scary from a privacy standpoint, and useless for edge cases that hadn't happened in prod yet. I used Claude Code to build a schema-aware seed data generator that produces deterministic, foreign-key-correct, realistically distributed data for 40+ PostgreSQL tables. Refreshing staging went from a 3-hour dump-and-scrub ritual to a 90-second script, and we deleted the anonymization pipeline entirely. Here's how I built it and what I'd do differently. π
The Problem
Like a lot of teams, we had quietly settled into the worst common answer to "where does test data come from": copy production, scrub it, restore it into staging.
It worked, in the way that a shopping cart with one broken wheel works. But the costs kept stacking up:
- The scrub list was a liability. Every new column with personal data in it had to be manually added to the anonymization script. Miss one, and real user data lands in an environment with weaker access controls. We caught two near-misses in code review in a single quarter. That's two too many.
- Refreshes were slow and rare. The dump-scrub-restore cycle took about 3 hours end to end, so people ran it maybe once a month. Staging data drifted, tests started depending on specific rows, and "works on staging" stopped meaning anything.
- Prod data only contains the past. We were building a new billing flow with a pricing model that didn't exist yet. Production had zero rows exercising it. A dump can't test the future.
- It was big for no reason. We restored millions of rows to test features that needed a few hundred well-chosen ones.
Hand-written factory functions were the obvious alternative, and we had some β rotting. Factories are written once per feature, drift out of sync with the schema, and nobody notices until a migration breaks 30 of them at once.
The interesting constraint: our schema was 40+ tables with a dense web of foreign keys, check constraints, and a few polymorphic relationships that only existed in application code. Any generator that didn't respect all of that would produce data the app immediately choked on.
How I Solved It
The core idea: treat the database schema as the source of truth, and generate the generator. I didn't hand-write seed logic for 40 tables. I had Claude Code (I was on v1.x at the time, with PostgreSQL 16 and Python 3.12) introspect the schema and write per-table generators, then I reviewed and corrected them like any other PR.
Step 1: Introspect the schema, don't trust your memory
First, dump everything the database actually knows about itself:
SELECT
tc.table_name,
kcu.column_name,
ccu.table_name AS references_table,
ccu.column_name AS references_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
ON tc.constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';
I fed this (plus column types, nullability, check constraints, and enum definitions) to Claude Code as a big structured context file. This step matters more than it looks: when I first asked for generators from just the table names, the output was plausible-looking fiction. With the real constraint dump in context, it got the details right β including a couple of check constraints I had personally forgotten existed.
Step 2: Generate in dependency order
Foreign keys define a directed graph. You have to insert parents before children, so the first thing the generated tool does is a topological sort:
def insertion_order(tables: dict[str, set[str]]) -> list[str]:
"""tables maps table_name -> set of tables it references."""
order, resolved = [], set()
while len(order) < len(tables):
progressed = False
for name, deps in tables.items():
if name not in resolved and deps <= resolved:
order.append(name)
resolved.add(name)
progressed = True
if not progressed:
raise CycleError(f"FK cycle among: {set(tables) - resolved}")
return order
That CycleError fired on day one. We had a genuine FK cycle (two tables referencing each other, populated in prod via deferred constraints) that nobody had thought about in years. The generator forced us to acknowledge it explicitly β one of several places where generating data taught us things about our own schema.
Step 3: Realistic distributions, not uniform noise
Uniform random data is worse than useless for anything performance-shaped. If every customer has exactly 3 orders, you will never see the query plan that falls over when one customer has 40,000.
So each generator takes a distribution spec, and the specs are deliberately skewed:
ORDERS_PER_CUSTOMER = Skewed(
p50=2, # most customers: a couple of orders
p95=40, # power users
p999=25_000, # the one enterprise account that breaks everything
)
Claude Code's first draft used uniform distributions everywhere. This was the single biggest thing I had to push back on, and it became a standing instruction in the project's context file: seed data must include the whale account, the empty account, and the account with pathological history β every run, not by luck.
Step 4: Edge-case rows are first-class, not random
Random generation gives you volume. It does not reliably give you the customer whose name is 3,000 characters of combining diacritics, the order created in a timezone that no longer exists, or the subscription that was cancelled and reactivated on the same day.
So alongside the random bulk, every table has a small hand-curated edge list that is always inserted:
EDGE_CUSTOMERS = [
make(name=""), # empty display name (legacy rows)
make(name="A" * 3000), # length-limit prober
make(email="tag+filter@sub.example.co.uk"),
make(created_at=EPOCH), # pre-migration ancient row
make(deleted_at=NOW, active=True), # contradictory state prod really contains
]
That last one deserves a confession: I found it in production while building this. The generator project kept turning into an archaeology project, in a good way.
Step 5: Determinism via a seeded RNG
Every run is seeded:
./seed --seed 42 --scale 0.1 # 90 seconds, laptop-sized
./seed --seed 42 --scale 1.0 # staging-sized, same "people", more of them
Same seed, same data, every time. Bug reports can say "seed 42, customer #1847" and everyone is looking at the same row. This sounds like a small ergonomic win; it changed how we communicate about bugs more than anything else in the project.
The whole pipeline ends up looking like this:
flowchart LR
A[PostgreSQL schema] --> B[Constraint dump]
B --> C[Claude Code generates per-table generators]
C --> D[Human review + distribution specs]
D --> E[Topological sort]
E --> F[Deterministic seeded insert]
F --> G[Staging / CI / laptops]Total build time: about four days, most of it spent on review and on encoding constraints that lived only in application code. The anonymization pipeline was deleted two weeks later, once staging had run on generated data without anyone noticing a difference.
Lessons Learned
Generate the generator, not the data. Asking an AI agent for "1,000 rows of customer data" gives you a pile of static rows that rot like any fixture. Asking it to write a schema-aware program gives you something reviewable, diffable, and regenerable after every migration. The code is the artifact, and code review is the quality gate.
The schema is the best prompt you have. Every hallucinated detail in early drafts traced back to context I hadn't provided. Once the real constraint dump was in context, correctness jumped dramatically. If your database knows it, put it in the prompt β don't summarize from memory.
Distributions are where realism lives. FK-correct uniform data passes the constraints and fails the point. The skew β whale accounts, empty accounts, ancient rows β is what makes staging behave like prod. It's also exactly what an AI (or a human) won't produce unless explicitly told.
Deterministic beats realistic when they conflict. We sacrificed some realism (real prod is not reproducible) for seeds. Worth it every single time. Reproducible data turns "I can't repro your bug" conversations into "run seed 42."
Fake data is a privacy feature, not just a testing tool. The strongest argument that landed with leadership wasn't developer velocity β it was deleting the anonymization script and shrinking the blast radius of a staging breach to zero real users. If you need buy-in, lead with that. β οΈ
What's Next
Two things are on the list. First, wiring schema migrations to the generator in CI, so a migration that breaks generation fails the build β turning the seed tool into a living test of the schema itself. Second, teaching the generator to propose new edge-case rows by reading recent bug reports, because every incident is a data shape we should have been seeding all along.
Wrap-up
If your staging environment still runs on scrubbed prod dumps, you're paying a monthly tax and carrying a privacy risk to get data that can't even test your next feature. A schema-aware generator is a few days of work with an AI coding agent doing the mechanical parts β and unlike a dump, it gets better every time you touch it.
If this was useful, follow me here on Dev.to β I write about practical AI-assisted engineering, war stories included. And if you've solved seed data differently (Copycat? Snaplet-style capture? pure factories?), I'd genuinely love to hear what worked and what rotted β tell me in the comments. π¬
Top comments (0)