Database Seeding skill for Codex

Done well means: one command rebuilds a working dataset from nothing, running it twice changes nothing, the data looks like production in shape (volumes, skew, edge cases) without containing a single real person, and seeds never run against production by accident.

Classify what is being seeded

Different data has different homes. Mixing them is the root of most seed bugs.

Kind	Example	Where it lives	Runs in prod?
Reference data	Countries, plan tiers, permission names	Migration or idempotent reference seed	Yes
Dev/demo data	Users, orgs, orders for local work and screenshots	Seed script	Never
Test fixtures	Rows a single test needs	Factories inside the test suite ($test-data-factories)	Never
Production-like copy	Masked subset for staging or performance work	Masking pipeline, separate job	Never (source only)

If application code breaks without a row (a default plan, a system user), it is reference data: put it in a migration or a reference seed that production runs, not in dev seeds.

Workflow

- [ ] 1. Read AGENTS.md, the ORM config and existing seed files; find the seed command
- [ ] 2. List entities in dependency order (parents before children)
- [ ] 3. Write an environment guard that refuses production
- [ ] 4. Seed reference data with upserts on natural keys
- [ ] 5. Generate demo data deterministically (fixed Faker seed)
- [ ] 6. Run twice on an empty DB; second run must be a no-op or a clean reset
- [ ] 7. Verify counts, constraints and app smoke test

Hard guard first

Every seed entry point starts with a check that cannot be skipped by a missing env var:

const url = new URL(process.env.DATABASE_URL ?? "");
const allowed = ["localhost", "127.0.0.1", "db"]; // docker compose service name
if (process.env.NODE_ENV === "production" || !allowed.includes(url.hostname)) {
  throw new Error(`Refusing to seed ${url.hostname}; set SEED_ALLOW_HOST explicitly`);
}

Destructive resets (TRUNCATE ... CASCADE, prisma migrate reset, rails db:reset, manage.py flush) wipe data. Run them only on a local or disposable database, and ask the user before running one against anything shared such as staging.

Idempotent seeds by framework

Upsert on a natural or fixed key, never on auto-increment ids.

// Prisma: prisma/seed.ts (seed command set in package.json "prisma.seed" or prisma.config.ts)
import { PrismaClient } from "@prisma/client";
import { faker } from "@faker-js/faker";
const prisma = new PrismaClient();
faker.seed(20251028);

for (const tier of ["free", "pro", "enterprise"]) {
  await prisma.plan.upsert({ where: { code: tier }, update: {}, create: { code: tier } });
}
const users = Array.from({ length: 500 }, (_, i) => ({
  email: `user${i}@example.test`,          // reserved TLD, never a real inbox
  name: faker.person.fullName(),
  createdAt: faker.date.past({ years: 2 }),
}));
await prisma.user.createMany({ data: users, skipDuplicates: true });

// Drizzle (Postgres)
await db.insert(plans).values(rows).onConflictDoNothing({ target: plans.code });

# Rails: db/seeds.rb, run with bin/rails db:seed
Faker::Config.random = Random.new(42)
%w[free pro enterprise].each { |code| Plan.find_or_create_by!(code:) }
User.upsert_all(users, unique_by: :email)   # one statement; skips validations and callbacks

# Django: a management command, e.g. app/management/commands/seed.py
from faker import Faker
Faker.seed(42)
Plan.objects.update_or_create(code="pro", defaults={"price_cents": 2900})
User.objects.bulk_create(users, batch_size=1000, ignore_conflicts=True)

Laravel: php artisan db:seed --class=DemoSeeder, using model factories with fake()->seed(42) set in the seeder, and upsert() for reference rows.

Prefer bulk calls (createMany, insert_all/upsert_all, bulk_create) over per-row inserts: 50k rows one by one through an ORM takes minutes; in bulk it takes seconds.

Make it realistic

Uniform random data hides the bugs real data triggers. Encode the shape:

• Skew. Most customers have a few orders; a few have thousands. Draw counts from a long-tailed distribution (random.paretovariate, or pick 2% of users as heavy users).
• Time. Spread created_at over months with weekday and business-hour bias so date filters, partitions and charts behave.
• Edge cases on purpose. Names with apostrophes and non-Latin scripts, emoji, 255-char strings, NULLs in optional columns, soft-deleted rows, zero-quantity orders, users in several time zones.
• Personas. A handful of fixed, documented accounts ([email protected], [email protected]) that developers and screenshots rely on.
• Volume tiers. SEED_SCALE=small|large: small for daily dev (seconds), large for pagination and performance work.

Use example.test, example.com or .invalid addresses and 555-01xx style phone numbers so nothing can email or text a real person.

Bulk volume in Postgres

For hundreds of thousands of rows, generate in SQL or stream with COPY:

INSERT INTO events (user_id, kind, created_at)
SELECT (random() * 49999)::int + 1,
       (ARRAY['view','click','purchase'])[1 + floor(random() * 3)::int],
       now() - random() * interval '180 days'
FROM generate_series(1, 2000000);

psql "$DATABASE_URL" -c "\copy users(email,name,created_at) FROM 'seed/users.csv' CSV HEADER"

Run ANALYZE afterwards so the planner sees the new volume.

Masked copies of production

Copying production data is a privacy decision, not just an engineering one. Before starting, confirm with the user that the copy is approved, where it will live, and who can access it. Read $gdpr-privacy-engineering when regulated data is involved.

Approach	Tooling	Use when
Static masking at dump time	Greenmask, PostgreSQL Anonymizer (anon extension)	Regular staging refresh
Subset then mask	Custom SQL that follows FKs from a sample of root rows	Prod is too big to copy
Synthetic only	Seed scripts above	Data must never leave prod

Rules for masking:

• Mask deterministically (keyed hash of the original) wherever a value is joined or deduplicated on, so the same email maps to the same fake email in every table.
• Null out or replace secrets entirely: password hashes, API keys, OAuth tokens, session ids, webhook secrets, TOTP seeds.
• Free-text columns (notes, support messages, addresses in JSON) leak the most PII; replace them wholesale rather than regex-scrubbing.
• Keep distributions and NULL rates; shift dates by a consistent random offset per entity.
• Run the masking inside the production boundary; only masked output leaves it.

PII scan before anything leaves

python3 scripts/scan_pii.py dump.sql
python3 scripts/scan_pii.py exports/ --allow-domain example.test --max-samples 5

It scans SQL dumps, CSV and text files for email addresses outside the allowed domains, phone-like numbers, card numbers that pass the Luhn check, US SSN patterns, IBAN-like strings and JWT or API-key-shaped tokens. It prints counts per file and category with masked samples and exits 1 when anything is found. Treat a clean scan as necessary, not sufficient: it cannot see names or free-text addresses.

Verification

• Run the seed twice on a fresh database; row counts identical after both runs.
• SELECT count(*) per table matches the intended scale tier.
• Constraint check: no orphan rows (LEFT JOIN parent ... WHERE parent.id IS NULL).
• App smoke test: log in as each persona, open the busiest pages.
• Masked copies: scan_pii.py exits 0 and a sample review finds no real names.

Gotchas

• Sequences: inserting explicit ids leaves the sequence behind; fix with SELECT setval(pg_get_serial_sequence('users','id'), max(id)) FROM users;.
• upsert_all, bulk_create and createMany skip model callbacks and validations; derived columns (slugs, counters, search vectors) must be set explicitly.
• faker.seed only fixes output while the call order is stable; adding a field shifts everything after it. Seed per entity (faker.seed(base + i)) for stable personas.
• Seeds that call external services (Stripe, email) must use stubs or test keys.
• Unscoped Faker unique generators exhaust on large volumes; build uniqueness from the loop index instead.

Output template

**Command:** pnpm db:seed (SEED_SCALE=small, about 6 s; large about 90 s)
**Guard:** refuses non-local hosts and NODE_ENV=production
**Data:** 3 plans (reference), 500 users, 12 orgs, 18k orders (Pareto skew), 6 personas
**Idempotency:** second run inserts 0 rows (verified)
**Masked copy:** n/a | Greenmask config at ops/greenmask.yml, scan_pii.py clean