Finding PII in Test & Development Data

The Prod-Data-in-Staging Problem

Nobody plans to store customer data in a development environment. It happens anyway, for reasons that are individually reasonable: a bug only reproduces with real records, so someone restores last night's backup into staging. Load tests need realistic volume, so the performance environment gets a full production copy. A data scientist needs "a quick sample" and exports 50,000 rows to a laptop. Each shortcut solves today's problem and creates a permanent copy of personal data in an environment that was never designed to protect it.

The asymmetry of controls is what makes this dangerous. Production databases sit behind network segmentation, encryption, audited access, and on-call security monitoring. Staging and dev typically have shared passwords that predate the current team, broad access for every engineer and often external contractors, no audit logging, snapshots lying around in unencrypted buckets, and copies synced to personal machines. When attackers or auditors go looking, they go where the data is soft — a long list of public breach post-mortems trace back to a forgotten staging server, an exposed test database, or a repository containing a "sample" export.

Regulators are unambiguous on this point: personal data does not become less personal because the hostname contains the word staging. GDPR applies to every copy of every record; using customer data for testing is a processing purpose that needs its own lawful basis, which "it was convenient" is not. HIPAA's Security Rule follows PHI into any environment it touches. PCI DSS is bluntest of all — Requirement 6.5 category thinking and testing guidance explicitly prohibit live PANs in test systems, and a staging box holding card numbers silently expands your entire audit scope to include it.

The fix has two halves, and this guide covers both: detection — continuously scanning fixtures, dumps, and staging content with a PII detection API so leaks are found in hours instead of years — and prevention — masking production copies before they ever reach a lower environment.

Masking Before Every Staging Refresh

Teams refresh staging from production because tests need realistic data — realistic distributions, realistic edge cases, realistic volumes. The mistake is refreshing with identifiable data when realism is what was actually needed. A masking stage inserted into the refresh pipeline delivers the realism without the identity, and once automated it costs nothing per refresh.

The pipeline shape that works: snapshot → restore into a quarantine environment → scan & mask → promote to staging. The quarantine instance is locked down like production, because until masking completes it is production data. Structured columns you already know about (from your data inventory) get masked directly. Free-text columns — the ticket notes and comment fields where PII hides unpredictably — are run through the detection API, which returns both the entity list and a ready-made anonymized_text you write back in place. Only after the masked copy passes a verification scan does it get promoted where developers can reach it.

Mask-mode choice determines how useful the result is for testing. replace substitutes typed placeholders like [NAME] — clearly synthetic, ideal for fixtures and documentation. redact removes matches entirely, best when downstream parsing must not see placeholder tokens. hash replaces each value with a consistent hash — and consistency is the killer feature for databases: the same email hashes to the same token in every table and every row, so joins still join, foreign keys still resolve, uniqueness constraints still hold, and "count distinct customers" still returns the right shape. For relational test data, hash mode is almost always the right answer.

import fs from "node:fs";
import path from "node:path";

const API = "https://piidetectionapi.com/api/moderate.php";

async function maskText(text) {
  const resp = await fetch(API, {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify({
      api_key: process.env.PII_API_KEY,
      api_type: "pii_detection",
      text,
      mask_mode: "hash",   // consistent tokens keep joins working
      threshold: 0.6,
    }),
  });
  const data = await resp.json();
  return { masked: data.anonymized_text, found: data.entities_detected };
}

// Pre-test hook: refuse to run the suite over unmasked seed files
const seedDir = "./db/seeds";
let dirty = 0;
for (const file of fs.readdirSync(seedDir)) {
  if (![".sql", ".json", ".csv"].includes(path.extname(file))) continue;
  const text = fs.readFileSync(path.join(seedDir, file), "utf8").slice(0, 50000);
  const { found } = await maskText(text);
  if (found > 0) {
    console.error(`✗ ${file}: ${found} PII entities — regenerate this seed`);
    dirty++;
  }
}
if (dirty > 0) process.exit(1);
console.log("✓ seed files clean — starting tests");