Staging data pipeline — scrubbed copy of production

:::note Contenido en inglés Esta página del wiki se sincroniza desde la base de conocimiento en inglés y todavía no está traducida. :::

canonical · devops · updated 2026-09-06 · source

Design only (effort #1650 sub #1654). Building it is a follow-up. Companion of environments.md § Databases.

Principle

Staging holds production's shape, never production's people. Real structure, realistic row counts, real referential integrity; every personal or payment identifier replaced. A raw copy of tenant data into a non-production database is forbidden (same principle as the training-trace rule in .claude/rules/effort-model.md). A refresh is blocked while any column lacks an explicit scrub classification (see § Scrub vocabulary — unlisted is NOT "assumed safe").

Flow (manual, José's machine)

Fly prod (tuempresa-pg) ─pg_dump -Fc─▶ raw dump ─scrub─▶ masked dump ─reset + pg_restore─▶ Render staging DB

1. Dump — from the prod connection string, on José's machine only (prod credentials never live on Render or in a repo file): pg_dump -Fc --schema=drizzle --schema=plan --schema=product --schema='client_*' (drizzle = the api's Drizzle migration journal, drizzle.__drizzle_migrations per apps/api/drizzle.config.ts; the plan and product journals live inside their own schemas and travel with them) (exact commands: §1 of render-postgres-migration-plan.md). Exclude pgboss (job queue state is never copied). Handling the raw dump (it IS production PII): write it to an encrypted, access-restricted location (an encrypted APFS volume or an age-encrypted file), never inside the repo, a synced folder (iCloud/Drive/Dropbox) or a Time Machine-included path; retention = the duration of the refresh, max 24 h; rm -P (secure delete) the raw dump AND drop the unsanitized local database as soon as the masked dump exists.
2. Scrub — restore the raw dump into a throwaway local Postgres (docker-compose, bound to localhost only), run the masking tool, dump again. Tool: Greenmask (declarative YAML rules, Postgres-native, keeps FK consistency across tables) — first choice; pg_anon as the fallback if a rule Greenmask cannot express is needed. Decision recorded when the build sub picks one.
3. Reset + restore — do not rely on pg_restore --clean (it only drops objects present in the dump; destination-only objects and stale rows survive). Reset the staging database first, then restore:

   -- on the staging DB, before restore
   DROP SCHEMA IF EXISTS drizzle, plan, product, pgboss CASCADE;  -- drizzle = api migration journal
   DROP SCHEMA IF EXISTS public CASCADE; CREATE SCHEMA public;  -- public holds the extensions; rebuild it too
   DO $$ DECLARE s text; BEGIN
     FOR s IN SELECT nspname FROM pg_namespace WHERE nspname LIKE 'client\_%' LOOP
       EXECUTE format('DROP SCHEMA %I CASCADE', s);
     END LOOP; END $$;
   CREATE EXTENSION IF NOT EXISTS vector;   -- idempotent; after the public rebuild, before any table uses the type

   then pg_restore --no-owner --if-exists the masked dump. Read-only journal check (do NOT run db:migrate:all as a check — it applies migrations and backfills): for each of the three journals (api drizzle.__drizzle_migrations, the plan journal of @tedos/db, the product journal of @tedos/domain) SELECT hash, created_at FROM <journal> ORDER BY created_at and compare the ordered list of migration identities + hashes against the repo's drizzle/meta/_journal.json entries (tag + the sha256 of each .sql file, which is what Drizzle stores). All three lists identical = staging matches prod's schema version; a differing id or hash is a hard stop even if the row counts agree — counts are only the quick secondary check. If a journal is BEHIND (prod has not yet taken a migration develop already carries) run db:migrate:all deliberately, then re-check all three. pg-boss recreates its own schema on boot. Recreating the Render database itself is deliberately NOT the reset mechanism — that is a paid resource action and a human-confirmed step; schema reset gives the same clean slate for free.
4. Verify — per-table counts match the expected post-scrub counts, not prod: equal for structural tables; memory_chunk = 0; OTP/session/auth-token tables = 0; pgboss absent (recreated empty on boot). A SELECT on email/phone columns returns no real value (spot-check against a known prod row's pseudonym); the api boots on api-staging and one authenticated read succeeds.

Prod is ~18 MB with one tenant (2026-09-06): the whole loop is minutes on a laptop.

Scrub vocabulary (from the domain schema)

Pseudonymization is keyed and deterministic: HMAC-SHA256(key, value) truncated, never an unkeyed hash (an unkeyed digest of an email is reversible by dictionary). The key lives in José's password manager as a versioned secret (staging-hmac-v1), is reused across refreshes (so the same prod person maps to the same staging pseudonym and joins survive), and is never committed or set on Render. The HMAC key is not rotated between refreshes — rotating it changes every pseudonym at once. Rotate only in a coordinated full re-scrub (new version, staging fully re-loaded from a fresh masked dump, old version retired); this is separate from rotating any runner or database credential, which can happen any time. Canonicalize before hashing: email → trim, Unicode NFC, lowercase the whole address; phone → E.164 digits only (no +, spaces or dashes); ids → their string form. Input is UTF-8; output is lowercase hex, truncated to 16 hex chars (64 bits) — collision odds are negligible below ~10^7 rows and the result fits every varchar we store these in. The Greenmask rules use exactly this spec.

Column / object	Rule
email (29 columns)	user-<hmac>@scrubbed.invalid — .invalid is reserved (RFC 2606) and can never deliver; staging email sending stays on Resend's test mode
phone (27 columns)	keyed-deterministic E.164 that keeps the 10-digit MX shape (+52 + 10 digits from the HMAC). Mexico has no reserved fictional range, so the number MAY be real — the guarantee is delivery, not the digits: staging runs with outbound SMS/WhatsApp disabled (provider sandbox or the MESSAGING_SINK=log kill-switch; the build sub wires whichever exists)
rfc (9), curp if present	synthetic but format-valid, keyed-deterministic
full_name, first/last name columns	faker names seeded from the HMAC of the person id
address, birth_date	fake street/city; date shifted ±N days (N from the HMAC), preserving age band
memory_chunk (text + embedding, pgvector)	drop rows — embeddings leak the text they encode; excluded from exact-count verification; staging re-embeds if needed
Stripe ids (cus_*, pi_*, pm_*, sub_*)	replace with cus_test_<hmac> etc.; amounts and currencies kept
Tenant display name	keep only if verified public (the tenant's live marketing site shows it); otherwise faker company name
Tenant logo, custom domains, contact fields	strip (logo URL → placeholder, domains → NULL, tenant contact email/phone → scrubbed like any email/phone)
email_event, notification payload bodies	blank the body, keep type/status/timestamps
OTP / session / auth tokens	truncate the tables
Any column NOT in this table	blocked — the refresh stops until it is classified here as copy (structural: ids, statuses, timestamps, prices, course content) or given a rule

The build sub turns this table into the Greenmask rules file; the rules file MUST fail on an unclassified column (Greenmask's strict mode / a pre-flight column diff against this table). New PII columns must be added here in the same PR that adds them to the schema.

Cadence and ownership

• On demand, run by José. Typical triggers: before a QA pass on staging, after a schema change, when staging data is too stale to be useful.
• Not automated in this effort, and NOT on Render. Automating it would put a prod credential and the HMAC key on a third-party runner, which § Flow forbids. If it is ever automated, the runner is a machine we control (José's Mac via launchd, or a self-managed runner) using a read-only prod role scoped to the dumped schemas whose credential rotates on a schedule; a Render cron service is explicitly excluded unless a written least-privilege exception (owner, rotation, revocation) is added here first. Cost direction of the manual loop: zero.

Follow-up

• Build issue: to be filed when this design is reviewed (Greenmask rules file under apps/api/scripts/staging-scrub/, a pnpm staging:refresh script, a runbook section here).
• Pointers: environments.md · .claude/rules/cost-decisions.md · .claude/rules/render-conventions.md.