Find the personal data hiding in every table, column, and comment field. Learn sampling strategies, API-driven classification, and how to turn scan results into a living data inventory and RoPA-ready data map.
Start DiscoveringMost organizations can tell you which table stores customer email addresses. Far fewer can tell you which of their 4,000 other columns also contain email addresses, phone numbers, or national identifiers that arrived there by accident. Personal data spreads through databases the way water spreads through a building: support agents paste full customer records into ticket notes, engineers copy production rows into analytics schemas, ETL jobs denormalize address fields into event payloads, and free-text comment columns quietly accumulate everything from dates of birth to credit card numbers typed in by users who were never supposed to share them.
This sprawl has direct regulatory consequences. GDPR Article 30 requires a Record of Processing Activities that describes categories of personal data and where they live. CCPA/CPRA obliges you to respond to deletion and access requests, which is impossible if you do not know every location where a person's data resides. PCI DSS Requirement 3 assumes you can prove cardholder data exists only inside your defined cardholder data environment. Every one of these obligations begins with the same technical prerequisite: an accurate, current map of PII across your databases.
Manual data mapping — interviewing engineers and reading schema documentation — captures perhaps the intended data flows. It systematically misses the unintended ones, and the unintended locations are precisely where breaches, audit findings, and subject-access failures originate. Automated discovery reverses the approach: instead of asking people where PII should be, you sample actual data values and let a detection engine tell you where PII actually is.
A modern PII detection API makes this practical. Rather than maintaining hundreds of regex patterns per database dialect, you extract sample values, send them as text to a single endpoint, and receive typed, scored entities back — the same engine that scans support tickets and log files can classify your columns across 150+ entity types and 60+ languages.
Database columns fall into two fundamentally different discovery problems, and treating them the same is the most common mistake in DIY scanners. Structured columns hold one value of one expected type: email, ssn, phone_number, date_of_birth. For these, discovery is a classification exercise — you need to determine what the column as a whole contains, and a modest sample of rows answers that question with high confidence. If 96 of 100 sampled values in users.contact are detected as EMAIL_ADDRESS, the column is an email column regardless of what its name suggests.
Free-text columns — support notes, order comments, CRM activity logs, JSON blobs, survey answers — are a fundamentally harder problem. Any individual row may contain zero entities or a dozen, of any type, in any language. Column-level classification is meaningless here; a comments column is not "a phone number column" just because 3% of rows contain phone numbers. Instead you need presence statistics: what fraction of rows contain each entity type, and at what confidence. That 3% may represent 40,000 rows of leaked phone numbers in a 1.3-million-row table.
The practical consequence: structured columns can be classified with small samples (50–200 values) and cheap heuristics as a pre-filter, while free-text columns deserve larger samples and context-aware NER, because regex alone cannot find "her maiden name is Rodriguez and she was born 4/12/85" in a ticket note. Names, addresses, and medical details in prose have no fixed format — this is exactly the gap between pattern matching and transformer-based detection covered in our NER vs regex comparison.
There is a third, sneaky category worth flagging: semi-structured columns such as JSON, XML, or key-value strings stored in text fields. Treat these as free text for scanning purposes, but record which JSON keys triggered detections — a metadata column where PII always appears under $.shipping.address can be remediated with a targeted migration rather than a full rewrite.
notes, description, payload, extra, and temp. Name-based classification is a useful prioritization signal, never a discovery method.Scanning every row of every table is rarely feasible — a mid-size warehouse holds billions of values. Sampling makes discovery tractable, but the sampling design determines whether your data map is trustworthy. Four strategies cover most situations.
Pull N random rows per column using TABLESAMPLE, ORDER BY RANDOM() on small tables, or random primary-key probing on large ones. With 100 random values, a data type present in 5% of rows will appear in your sample with ~99.4% probability. This is the default strategy for column classification: cheap, unbiased, and statistically defensible. Its weakness is rare contamination — a handful of leaked SSNs in a million-row comment column will usually be missed.
Split the table into strata — by tenant, region, source system, or creation year — and sample each stratum separately. Data quality and content vary enormously across segments: rows imported from an acquired company's CRM may be PII-dense while native rows are clean. Stratifying by time is especially valuable, since a column that was sanitized in 2023 may still carry raw PII in rows from 2019 that a uniform sample under-represents.
Sample heavily from the newest rows (e.g., 70% from the last 90 days) plus a thin uniform tail across history. New rows reflect what your application writes today, so recency weighting catches fresh leaks quickly — a logging change that started writing full card numbers last Tuesday shows up in this week's scan, not next year's audit. Ideal for scheduled re-scans that keep an existing data map current.
For columns that sampling has already flagged as contaminated, or for high-stakes questions ("does cardholder data exist anywhere outside the CDE?"), scan every row. Run exhaustive scans against a read replica or snapshot, batch rows into requests up to the API's 50,000-character limit, and record per-row offsets so remediation scripts can update exactly the affected records. Reserve this mode for confirmation and cleanup, not first-pass discovery.
A discovery run has four stages: enumerate, sample, detect, aggregate. First, enumerate candidate columns from the information schema, keeping textual types and skipping obvious non-candidates like booleans and foreign-key IDs. Second, pull a sample per column. Third, send the sampled values to the detection endpoint. Fourth, aggregate entity counts per column into classification decisions.
Enumerating and sampling in PostgreSQL looks like this — one query to list columns, one templated query per column to sample it:
-- 1. Enumerate candidate text columns
SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE data_type IN ('text', 'character varying', 'json', 'jsonb')
AND table_schema NOT IN ('pg_catalog', 'information_schema');
-- 2. Sample one column (fast random sample on a big table)
SELECT ticket_note
FROM support.tickets TABLESAMPLE SYSTEM (1)
WHERE ticket_note IS NOT NULL
LIMIT 100;Then concatenate the sampled values — separated by newlines so offsets stay attributable to individual rows — and post them to the API. A single request classifies an entire column sample:
curl -X POST https://piidetectionapi.com/api/moderate.php \
-H "Content-Type: application/json" \
-d '{
"api_key": "YOUR_API_KEY",
"api_type": "pii_detection",
"text": "Called Maria Gonzalez re: refund\nCust DOB 04/12/1985, card 4111 1111 1111 1111\nShip to 88 Elm St, Springfield IL 62704",
"threshold": 0.6,
"mask_mode": "replace"
}'The response returns each entity with type, text, start/end offsets, and a confidence score. Because you know the character offset where each sampled row begins, you can map every detection back to its source row — the foundation for both the per-column statistics in your inventory and any later remediation. The optional anonymized_text field even gives you a masked rendition you can paste into audit evidence without re-exposing the data. Try this loop interactively in the live demo before wiring it into a script.
The Python script below is a complete miniature scanner: it enumerates text columns, samples each, classifies the sample through the API, and prints a per-column summary you can write straight into your inventory. Adapt the connection and sampling query to your engine — the API side stays identical for MySQL, SQL Server, Oracle, Snowflake, or BigQuery.
import psycopg2, requests, collections
API_URL = "https://piidetectionapi.com/api/moderate.php"
API_KEY = "YOUR_API_KEY"
SAMPLE_SIZE = 100
conn = psycopg2.connect("dbname=crm host=replica.internal")
cur = conn.cursor()
cur.execute("""
SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE data_type IN ('text','character varying')
AND table_schema NOT IN ('pg_catalog','information_schema')
""")
for schema, table, column in cur.fetchall():
cur.execute(
f'SELECT "{column}" FROM "{schema}"."{table}" '
f'TABLESAMPLE SYSTEM (1) WHERE "{column}" IS NOT NULL LIMIT %s',
(SAMPLE_SIZE,),
)
values = [str(r[0])[:400] for r in cur.fetchall()]
if not values:
continue
resp = requests.post(API_URL, json={
"api_key": API_KEY,
"api_type": "pii_detection",
"text": "\n".join(values),
"threshold": 0.6,
}, timeout=30)
data = resp.json()
counts = collections.Counter(e["type"] for e in data["detected_entities"])
if counts:
top, n = counts.most_common(1)[0]
density = n / len(values)
print(f"{schema}.{table}.{column}: {dict(counts)} "
f"-> dominant={top} density={density:.0%}")The same pattern in Node.js, useful when your data platform tooling is TypeScript-based. Here the aggregation step decides between "classified column" (dominant entity in most sampled values) and "contaminated free-text" (low but non-zero density):
import mysql from "mysql2/promise";
const db = await mysql.createConnection({ host: "replica.internal", database: "crm" });
async function classifyColumn(table, column) {
const [rows] = await db.query(
`SELECT ?? AS v FROM ?? WHERE ?? IS NOT NULL ORDER BY RAND() LIMIT 100`,
[column, table, column]
);
const sample = rows.map(r => String(r.v).slice(0, 400)).join("\n");
const resp = await fetch("https://piidetectionapi.com/api/moderate.php", {
method: "POST",
headers: { "Content-Type": "application/json" },
body: JSON.stringify({
api_key: process.env.PII_API_KEY,
api_type: "pii_detection",
text: sample,
threshold: 0.6,
}),
});
const { detected_entities } = await resp.json();
const counts = {};
for (const e of detected_entities) counts[e.type] = (counts[e.type] || 0) + 1;
const density = detected_entities.length / rows.length;
return {
table, column, counts,
verdict: density > 0.8 ? "PII_COLUMN"
: density > 0.02 ? "CONTAMINATED_FREE_TEXT"
: "CLEAN",
};
}
console.log(await classifyColumn("tickets", "agent_notes"));Both scripts deliberately truncate individual values to a few hundred characters — for classification you need enough context for accurate detection, not the whole document. When a column is flagged, follow up with the targeted exhaustive scan described above. If your compliance posture forbids sending samples to a cloud endpoint, the same contract is available as an on-premise deployment; see the API documentation.
Raw detections are evidence, not an inventory. An inventory is a maintained record per column — what it contains, how sure you are, who owns it, and what should happen next. Three practices turn scan output into an asset your privacy and security teams actually use.
For every classified column, store the entity histogram, sample size, scan date, dominant entity density, and average confidence — not merely a "contains PII" flag. Evidence lets auditors verify conclusions, lets you re-baseline after schema changes, and lets you distinguish a 98%-density email column from a comments field with 4% incidental emails, which demand entirely different remediations.
mask_mode: "replace") as safe evidenceEvery flagged column needs a human owner (usually the owning service's team) and a sensitivity tier that drives policy. A pragmatic four-tier scheme: direct identifiers (SSN, passport, card numbers), strong quasi-identifiers (name, DOB, address), special-category data (health, religion, sexual orientation — GDPR Article 9), and technical identifiers (IP, device ID, cookies). Tiers map directly to encryption, access-control, and retention rules.
A data map decays the moment it is finished — new tables appear, application changes alter what columns receive. Schedule recurring scans: weekly recency-weighted samples of known free-text columns, monthly classification of any column added since the last run, quarterly full re-baselines. Diff each run against the previous one and alert on new entity types appearing in previously clean columns; that diff is your earliest leak detector.
The end product of discovery is documentation your legal and compliance teams can file: a Record of Processing Activities (GDPR Article 30), a data map for CCPA/CPRA request fulfillment, and scoping evidence for PCI DSS and HIPAA audits. Detection results supply the "categories of personal data" and "where stored" fields with evidence instead of guesswork.
A useful convention is to maintain the inventory at column granularity and roll it up to processing-activity granularity for the RoPA. The table below shows the rollup for a fictional support platform — note how detected entity types translate directly into RoPA data categories, and how discovery findings (the contaminated agent_notes column) surface obligations that a manual mapping exercise would have missed entirely.
| Location | Detected Entity Types (density) | RoPA Data Category | Lawful Basis / Purpose | Retention | Action From Discovery |
|---|---|---|---|---|---|
crm.users.email |
EMAIL_ADDRESS (99%) | Contact data | Contract — account administration | Life of account + 30 days | Confirmed as designed; encrypt at rest |
crm.users.dob |
DATE_OF_BIRTH (97%) | Identity data | Legal obligation — age verification | Life of account | Restrict access to verification service |
support.tickets.agent_notes |
PERSON_NAME (11%), PHONE_NUMBER (6%), CREDIT_CARD_NUMBER (0.4%) | Contact + financial data (unintended) | None for card data — remediate | 2 years | Exhaustive scan; mask card numbers in place; agent training |
analytics.events.payload (JSON) |
IP_ADDRESS (88%), DEVICE_ID (61%) | Online identifiers | Legitimate interest — product analytics | 13 months | Add to CCPA deletion pipeline; truncate IPs |
billing.invoices.notes |
IBAN_CODE (2%), PERSON_NAME (9%) | Financial data (unintended) | Contract — billing | 7 years (statutory) | Block free-text IBAN entry at application layer |
Two rollup rules keep this maintainable. First, the RoPA references processing activities ("customer support", "billing"), each pointing at the set of columns discovery attributes to it — so when a scan finds a new location, you update one linkage, not the whole document. Second, export the inventory in a machine-readable format (CSV or JSON) so DSAR tooling can consume it: when a deletion request arrives, the fulfillment job iterates exactly the columns mapped to that data subject's categories. Teams handling regulated verticals should pair this with the specific obligations in our GDPR detection guide and PCI DSS cardholder data discovery guide.
entities parameter (e.g., only PCI-relevant types) makes targeted audits faster and reports cleaner.Lessons that separate a discovery program that survives contact with production from a one-off scan that never gets repeated.
Sampling queries look innocent until ORDER BY RANDOM() forces a full table scan on a 500 GB table during peak traffic. Run discovery against read replicas, snapshots, or warehouse copies; use TABLESAMPLE or key-range probing instead of full-table sorts; set statement timeouts; and schedule off-peak. Discovery must be operationally invisible or the DBA team will (rightly) kill it.
A discovery pipeline concentrates samples of your most sensitive data, so treat it as sensitive infrastructure: use TLS to the API, never write raw samples to scanner logs or intermediate files, store only masked snippets as evidence, and give the scanner's database credentials read-only access scoped to what it scans. Threshold and custom_instruction tuning belong in config review, not ad-hoc edits.
Discovery favors recall: run first-pass scans at a lower threshold (0.4–0.5) so nothing sensitive escapes, then confirm flagged columns at a higher threshold before opening remediation tickets. Track false-positive patterns — order IDs detected as phone numbers, SKU codes as national IDs — and suppress them with exclude_entities or a custom_instruction rather than raising the global threshold. How to measure this rigorously is covered in our accuracy guide.
mask_mode options give you replace, redact, or consistent-hash behavior), and record the remediation in the inventory. Skipping step two guarantees the finding returns at the next scan.Point a sampling script at your replica, classify every column through one API, and walk away with an evidence-backed data inventory. Try the detection engine on your own data right now.
Try the Live Demo View Pricing