ADR-0097: Anonymized prod-subset backfill for stage

Status: Accepted (2026-07-16) Owner: Gera Amends: the CLAUDE.md §"Stage Fixtures" policy ("stage is intentionally NOT a prod snapshot")

Context

propflow-stage is a curated no-PII bench (seeded by npm run seed:stage). That keeps it SOC2-clean but nearly empty: testing anything that needs realistic cross-channel data — an SMS thread with a related email, a work order with a real-shaped timeline, a renewal mid-flight — fails because the data simply isn't there. The May-2026 attempt to fix this (backfill-stage-from-prod.sh + anonymize-stage-pii.ts) had two flaws:

1. Raw PII landed on stage first, scrub came after. The clone was a full unfiltered table copy; the scrub was light-touch (names/contacts on 3 hardcoded property partitions) and never touched message bodies, agent traces, tenantName caches, or email records. Worse, the scrub's own BACKFILL_REVIEW# audit rows stored the real before-values on stage.
2. Not repeatable. No time-windowing, no overlap protection, no census of what was copied.

Decision

Stage MAY hold anonymized subsets of prod data, produced exclusively by the pipeline in scripts/anon-backfill/. The prior blanket rule ("stage is never a prod snapshot") is amended to: stage never holds prod PII; it may hold prod-shaped rows whose identifiers have been irreversibly rewritten by this pipeline, plus the curated synthetic bench.

The pipeline (5 stages plus an as-needed prune sub-step, each a separate dry-run-first CLI)

Key properties:

• Anonymize in-flight. Raw PII never touches stage — the transform runs locally on the export file; only anonymized rows are written. (This is the inversion of the May flow, and the load-bearing SOC2 improvement.)
• Selection policy, fail-safe by default. Every row is classified (lib.ts): the identity spine + property/unit/lease/org/vendor structure is roster (always copied, so everything renders); activity (conversations, WOs, turnovers, sagas, traces, emails, signals, logs) is windowed to the requested range — conversation/WO/turnover/saga partitions are kept or dropped whole, anchored on the main entity row's timestamp, so nothing renders half-copied; credentials (PMSCRED#), auth (USER#), config/arms (CONFIG#), marketing PII (WAITLIST#), and anything unclassified are never copied. The export prints a census so new partitions surface for explicit classification instead of leaking.
• Deterministic, initials-preserving pseudonyms. Fake identities derive from the personId via FNV-1a over the shared src/lib/anonymize/name-table.ts conventions (moved from scripts/stage-anonymized-name-table.ts, ADR-0110) — same person, same fake, on every run ("Bryce Young" → the same B./Y. fake each time). Emails map to @example.test — an RFC-6761 reserved TLD that genuinely cannot deliver, which is the hard "can't reach a human" guarantee. Phones map into the +1000<2M–10M> fake convention (FAKE_PHONE_NPA): NANP area codes can never begin with 0, so a +1 000 … number is structurally impossible to assign or dial — the phone-side twin of the reserved email TLD. (The original +1206<2M–10M> convention was retired 2026-07-24: 206 is a live Seattle area code, so old-convention fakes both LOOKED real to a human and — worse — made every real +1206 number in the range pass the contact verifier as a "sanctioned fake". Residual old-convention rows on stage are deliberately NOT grandfathered: the belt flags them, and a re-bootstrap rewrites them.)
• Reversibility = same IDs, no key material. All entity IDs (pers_, conv_, wo_, …) are copied verbatim. To un-anonymize a stage row, look up the same ID in prod — the "key" is prod access itself. No mapping ledger exists on stage or in the repo. (The transform's local dictionary.json is a run artifact on the operator's machine; delete it with the run dir.)
• Sweep everything, including keys. After targeted field rewrites, a global sweep replaces every dictionary identifier (names word-boundary; phone variants + emails literal) in every string of every row — message bodies, trace text, summaries, and the PK/SK/GSI values themselves (several partitions embed the contact in the key: CLAIM#<org>#<TYPE>#<value>, CONSENT#<phone>, SUPPRESS#PHONE#<e164>). The transform then re-scans its own output and fails if any dictionary literal survived; stage is scanned again post-load (stage 5).
• Overlap-safe re-runs. The loader records each ingested UTC day under ANON_BACKFILL#MANIFEST / DAY#<yyyy-mm-dd>; a future run over an overlapping range skips those days' windowed rows. Windows always span complete days (an explicit --end is a day boundary; the default end is floored to the current UTC midnight) so day-granular skipping is exact. Roster rows always rewrite (deterministic → convergent). Every written row is stamped anonBackfillRunId — the wipe stage skips any stamped row, so a later window's wipe can never delete an earlier window's loaded output (windows accumulate).
• Truncation-gated inputs. census.json is written only after the export scan completes and records the item total; transform and wipe refuse to run unless it exists AND their input's row count matches it. A mid-scan crash cut on a line boundary would otherwise read cleanly, under-build the dictionary, and let un-dictionaried identifiers through every verifier. Prune has the symmetric guard: it refuses (absent --confirm-mass-delete) when the current anon.ndjson is much smaller than the row count the loader recorded — a stale/truncated file must not classify a loaded run as "superseded" and delete it.
• Single-flight. The write stages (wipe / load / prune) take a conditional-put lock row (ANON_BACKFILL#LOCK) for the duration of their read-decide-write — the deciding read (stage scan, manifest read) happens under the lock, not just the mutation — since concurrent runs against one stage table would interleave provenance stamps and manifest rows. The lock is fenced by a per-acquisition token: stale takeover (after 2h, or explicitly via --steal-lock) is conditional on the observed holder, and release is conditional on the owner's own token, so racing takeovers can't both win and an over-stale holder can't free its successor's lock.

Accepted exceptions (documented residual risk)

• External media points at prod. elevenlabsConversationId, Twilio SIDs, S3 keys, and Anthropic file-ids are copied as-is: stage plays the real call audio / photos via prod-side services. The audio itself contains real spoken names and cannot be edited; access is the same team that has prod access. Deliberate trade-off for testability.
• Free-text stragglers. The sweep replaces every identifier the spine knows about. A name typo'd in a message body, or a third party mentioned in prose who has no Person row, can survive. Emergency-contact blocks are blanked outright as the known-worst case of this class. Likewise, name tokens that double as bench markers or infrastructure words (Test, Demo, Local, …) are deliberately kept out of the name sweep — such a person's contacts still anonymize and are scanner-checked, but their bare name can survive in prose.
• Non-spine contact fields. The dictionary is built from the Person spine (profiles + claims + legacy Tenant/Prospect rows). Human contacts stored as structured fields on non-spine roster entities — e.g. an Organization owner's name/email/phone — are not dictionary entries and copy verbatim. Property/VendorCompany contact fields are business information by design (no personId, per ADR-0033); review the Org rows if an operator's personal contacts ever land there.
• Verification scope is contact literals. The transform self-verify and scan-stage-pii.ts assert on phone/email literals; names are swept best-effort but not scanner-asserted (word-boundary name matching cannot distinguish a leak from prose).
• Deleted-in-prod May remnants. The wipe matches stage rows against today's prod key-set; a May-clone row whose prod original was since deleted survives (already scrubbed or orphaned bookkeeping).
• Temporal workflow pointers. Copied in-flight turnovers/sagas encode prod workflow identities that don't exist in stage's Temporal namespaces. Acting on one in the stage UI yields a visible "workflow not found" error — safe (fake contacts are unreachable anyway), but expect it. Rows are not state-mutated to "freeze" them; the provenance stamp marks them instead.

Runbook (repeat for any window)

cd <worktree>
# 1. Export prod (read-only). Review the census before continuing.
npx tsx scripts/anon-backfill/export.ts --start 2026-06-16 --end 2026-07-16
# 2. Transform offline. Fails loudly if any identifier survives its own sweep.
npx tsx scripts/anon-backfill/transform.ts --start 2026-06-16 --end 2026-07-16
# 3. Wipe prod remnants off stage (dry-run first, review wipe-report.json).
npx tsx scripts/anon-backfill/wipe-prod-remnants.ts --start 2026-06-16 --end 2026-07-16
npx tsx scripts/anon-backfill/wipe-prod-remnants.ts --start 2026-06-16 --end 2026-07-16 --write
# 4. Load stage (dry-run first).
npx tsx scripts/anon-backfill/load.ts --start 2026-06-16 --end 2026-07-16
npx tsx scripts/anon-backfill/load.ts --start 2026-06-16 --end 2026-07-16 --write
# 4b. ONLY if you re-ran transform after a fix and re-loaded with --force:
#     prune the prior load's stale-key orphans (dry-run first, review
#     prune-report.json, then --write).
npx tsx scripts/anon-backfill/prune-superseded-run-rows.ts --start 2026-06-16 --end 2026-07-16
# 5. Verify: zero real identifiers on stage (exit 1 on any hit).
npx tsx scripts/anon-backfill/scan-stage-pii.ts --start 2026-06-16 --end 2026-07-16
# 6. Delete the local run dir — it holds the raw export + dictionary (real PII).
rm -rf ~/code/tmp/anon-backfill/anon-2026-06-16-to-2026-07-16

Artifacts live in ~/code/tmp/anon-backfill/<runId>/ (chmod 700). The raw export and dictionary are PII — local to the operator's machine, deleted after verification, never committed, never uploaded.

Alternatives considered

• Keep synthetic-only stage (status quo): rejected — three months of practice shows the synthetic bench can't reproduce cross-channel shape (SMS↔email↔voice threads, classification distributions, saga states), which is exactly what needs testing.
• Copy-then-scrub (May-2026 flow): rejected — raw PII on stage during the window, and field-enumeration scrubbing provably missed surfaces.
• Cryptographic ledger for name reversibility: rejected as unnecessary — preserved IDs make prod itself the reverse index, with zero key material.

Consequences

• The raw-clone flow is deleted, not just deprecated: scripts/backfill-stage-from-prod.sh, its wrapper scripts/seed-stage.sh, and the post-hoc scripts/anonymize-stage-pii.ts are removed in the same PR that lands this ADR (one source of truth — two live paths for "populate stage from prod" would leave the SOC2 liability available). src/lib/anonymize/name-table.ts (moved from scripts/stage-anonymized-name-table.ts in ADR-0110) is this pipeline's name table.
• CLAUDE.md §"Stage Fixtures" gains a pointer to this ADR; the synthetic bench (seed:stage) and its fixtures remain the canonical deterministic test identities and are untouched by the wipe (stage-native keys don't match prod keys).
• The classification policy in scripts/anon-backfill/lib.ts must be extended when new partition kinds appear — unknown kinds are skipped and reported by the export census, never copied silently.
• Tests: scripts/__tests__/anon-backfill-lib.test.ts pins classification, windowing, identity determinism/initials, and the sweep (incl. PII-in-key rewrites).