The client wanted production data in preprod. I said no, then gave them something better.

“Can preprod just use the production database? Testing with fake data doesn’t show us the real problems.”

The client was right about the second sentence. Preprod had a dozen hand-made records, every campaign was called “Test campaign 3”, and any bug that depended on volume, on odd real-world inputs, or on the shape of actual customer activity was invisible until it hit production. They had noticed. It was a fair complaint, and the proposed fix was the obvious one.

It was also the fix that would have turned a preprod environment into a second copy of every customer’s personal data, on a less protected machine, with test code pointed at it.

This is part 10 of a series on hardening a solo-built SaaS in production. The setup and the full list of weak spots are in the pillar article. It is the last one, and the only one where the incident is a conversation rather than an outage.

Why “just copy prod” is the worst option

I wrote the reasons down before the call, because “it’s a bad idea” is not an argument and the client deserved better. Four of them held up.

Preprod is less protected, by design. Production sits behind CrowdSec, hardened nginx and now Cloudflare. Preprod is a separate, smaller VPS with a looser nginx config, no CrowdSec, and credentials that half the agency knows because that is what preprod is for. Copying the production database there does not extend production’s protection to the copy. It extends preprod’s weakness to the data.

It creates a second copy of personal data with no reason to exist. The product is a marketplace connecting brands with creators: names, emails, phone numbers, payout details, contracts. Under GDPR every copy of that needs a purpose, a retention period, and a place in the deletion process. The retention article is about how hard erasure already is with one database and its backups. A preprod copy that gets refreshed “sometimes” is a copy that never gets erased, and “we needed realistic test data” is not a purpose a regulator recognises.

Test code does real things. This was the argument that landed. Preprod runs the same application with the same integrations configured through environment variables: the SMTP server, the Stripe keys, the SMS provider. Point that at real customer records and the first person to test “send campaign reminder” emails every real creator in the database. The first test of a refund flow touches a real payment. We would not be testing on a copy of production; we would be operating on production’s customers from a machine we did not trust.

The bugs they wanted to find are about shape, not identity. Volume, odd inputs, real-world distributions of statuses and dates: none of that requires the real name attached to the row. A dataset that has production’s shape and nobody’s identity finds the same bugs and cannot leak anything that matters.

That last point is what turned the conversation from a refusal into a proposal.

How the conversation actually went

I did not say no. I said “yes to realistic data, no to real data, and here is the difference”, and then I showed the email-to-every-creator scenario. That one sentence did more than the GDPR paragraph. Compliance is abstract until the day it is not; a test button that messages every customer is concrete immediately, and everyone on the call had a story about a mass email sent by mistake.

Then I offered a timeline: two days to build an anonymised seed pipeline, after which preprod would have the full volume and shape of production, refreshed weekly, with every personal field replaced. They agreed in about a minute. The thing clients actually want is rarely the thing they ask for; they wanted preprod to find real bugs, and any path to that was fine.

The pipeline

The repository already had a seeding system: an umzug-driven bun run seed that generates records with @faker-js/faker. It was written for empty local databases and produced a few dozen rows. Volume and realism were the gap, and the answer was to keep faker for identity and take everything else from production.

The shape of it:

prod db  --pg_dump-->  throwaway container  --anonymise.sql-->  pg_dump  -->  preprod

Four steps, run weekly from the production box, never from preprod, so preprod credentials never touch production.

Step 1: dump, and restore into a box that lives for ten minutes

#!/usr/bin/env bash
set -euo pipefail

STAMP=$(date +%F)
pg_dump -Fc -h 127.0.0.1 -U app app > /tmp/prod-$STAMP.dump

docker run -d --name anon-db -e POSTGRES_PASSWORD=x postgres:15
sleep 5
pg_restore -h localhost -U postgres -d postgres --create --no-owner /tmp/prod-$STAMP.dump

The anonymisation never runs against production itself. It runs against a copy in a container that is deleted at the end of the script, so a bug in the anonymisation SQL can at worst produce a bad preprod, never a damaged prod.

Step 2: replace every personal field

This is the part that has to be complete, and “complete” means every column, not just the obvious ones. I went through the schema table by table with the PII masking article open next to it. Trimmed and anonymised:

-- anonymise.sql, run inside the throwaway container
BEGIN;

-- deterministic per row: the same user gets the same fake identity
-- on every refresh, so preprod bugs stay reproducible week to week
UPDATE users SET
  first_name = 'User',
  last_name  = 'Number ' || id,
  email      = 'user-' || id || '@preprod.example.com',
  phone      = '+33 6 00 00 ' || lpad((id % 10000)::text, 4, '0'),
  avatar_url = NULL;

UPDATE creators SET
  bio            = 'Bio for creator ' || id,
  instagram_id   = 'creator_' || id,
  iban           = NULL,
  payout_address = NULL;

UPDATE brands SET
  legal_name     = 'Brand ' || id || ' SAS',
  billing_email  = 'billing-' || id || '@preprod.example.com',
  vat_number     = NULL;

-- free text is where PII hides: messages, notes, contract bodies
UPDATE messages  SET body  = 'Message ' || id || ' (' || length(body) || ' chars)';
UPDATE contracts SET terms = 'Contract terms for ' || id;

-- tokens and secrets have no business in preprod at all
UPDATE users SET password_hash = crypt('preprod', gen_salt('bf'));
TRUNCATE sessions, password_resets, webhook_events;

-- keep the shape: statuses, amounts, dates, relations are untouched
COMMIT;

Three things I want to draw attention to.

It is deterministic. user-42@preprod.example.com is the same person every week, which means a bug reported against preprod stays reproducible after the next refresh. Faker’s random names would have been prettier and worse.

Free-text columns are anonymised by replacement, not redaction. Messages, notes and contract bodies are where people type phone numbers and addresses, and a regex that removes what it recognises leaves what it does not. Replacing the whole field with a placeholder that preserves the length keeps the layout bugs and loses the content.

Everything that is a credential is destroyed, not disguised. Every password becomes preprod. Sessions, reset tokens and webhook payloads are truncated. Preprod logins are a known shared account, and nothing in that database can be replayed against production.

Step 3: verify before shipping it anywhere

The scan that decides whether the dump leaves the box:

-- must return zero rows, or the script aborts
SELECT 'users.email' AS col, count(*) FROM users
  WHERE email NOT LIKE '%@preprod.example.com'
UNION ALL
SELECT 'creators.iban', count(*) FROM creators WHERE iban IS NOT NULL
UNION ALL
SELECT 'messages.body', count(*) FROM messages
  WHERE body ~ '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}';

If a migration adds a column with personal data and nobody updates anonymise.sql, this is the line that stops it reaching preprod. It is not perfect (a new nickname column would sail through), so the schema review for every migration now includes one question: does this column need an anonymisation rule? It goes in the pull request template from part 4.

Step 4: ship, and clean up

pg_dump -Fc -h localhost -U postgres app > /tmp/preprod-$STAMP.dump
docker rm -f anon-db
shred -u /tmp/prod-$STAMP.dump

scp /tmp/preprod-$STAMP.dump preprod:/tmp/
ssh preprod "pg_restore --clean --if-exists -d app /tmp/preprod-$STAMP.dump && rm /tmp/preprod-$STAMP.dump"
rm /tmp/preprod-$STAMP.dump

The production dump is shredded, not deleted, the moment the anonymised copy exists. The only artefact that leaves the production box is one that has already passed the verification scan.

What preprod looks like now

Full production volume, refreshed every Monday morning. Every campaign, creator and brand relationship intact. Real distributions of statuses, real edge cases in dates and amounts, real “why is this one record shaped like that” oddities. And not one real name, email, phone number or bank account anywhere in it.

The client’s original complaint, that fake data did not show real problems, went away in the first week. The first refresh surfaced two bugs that only appeared with a realistic number of campaigns per creator, exactly the class of problem they had been describing. The data was realistic. It just was not anybody’s.

What is still not fixed

The verification scan checks the columns I thought of. A column I did not think of is a leak, and the pull request checklist is a process control, not a technical one. The technical fix is a schema-level allowlist, where every column is either explicitly marked “not personal” or explicitly anonymised, and the script refuses to run on any column that is neither. I have the list; I have not wired it up.

The refresh runs from production’s own box, which means production’s box has a cron that knows preprod’s SSH host. That is one more thing on that machine than I would like. Pushing to object storage and having preprod pull would be cleaner.

And the dump exists in /tmp on the production server for the ten minutes the script runs. Shredded after, encrypted never. For ten minutes a week, that is a risk I have decided to accept, and I am writing it down so that it stays a decision rather than becoming a habit.

The lesson

“Copy prod to staging” is one of those requests that sounds like an engineering task and is actually a data governance decision, made in a chat window, by people who would be horrified to see it phrased that way. The right response was never to refuse it. It was to notice that the client wanted realistic bugs, not real identities, and that those two things come apart cleanly with a Sunday’s worth of SQL.

Every article in this series has been about a thing that broke. This one is about a thing that did not, because for once the decision got made before the incident instead of after. I would like more of the series to have gone that way. It is the one lesson the other nine have in common.