Start with what you already have: query logs
Most teams try to document PII flows by interviewing people and reading code. That works, but it’s slow, incomplete, and hard to keep current. Database query logs are a faster source of truth because they show what actually happened: which tables were touched, by which roles or service accounts, from which apps, and how often.
This approach is especially effective when you need two deliverables quickly: (1) a PII data-flow diagram and (2) a least-privilege access map you can hand to engineering and security. With a focused log sample and a simple extraction workflow, you can usually produce an initial map in under an hour—good enough to review, correct, and iterate.
What you can produce in under an hour
A PII data-flow diagram
A practical PII diagram answers: Where is PII stored, which systems touch it, and where does it go next? You’re not aiming for an enterprise architecture masterpiece. You’re aiming for a readable diagram that captures the dominant paths: app → database → downstream consumers (analytics, support tooling, exports, email providers, data warehouse).
A least-privilege access map
This map answers: Which identities (roles, users, service accounts) access which PII tables or fields, from which source, and whether that access is justified. Done well, it becomes the backlog for privilege reduction: remove unused access, split broad roles, add views, or gate sensitive joins behind approved workflows.
Choose a log window and make the scope explicit
Speed depends on scoping. Pick a window that captures normal behavior without exploding the dataset:
- Time window: start with 24 hours for busy systems, or 7 days for lower-traffic environments.
- Environments: prioritize production. If you include staging, label it clearly because access patterns differ.
- Datastores: pick one primary database first (e.g., Postgres). You can replicate the method for the warehouse later.
- PII definition: write a short list you will use consistently (email, phone, name, address, IP, device IDs, payment tokens, government IDs). Keep it internal and pragmatic.
If your org already has a taxonomy, use it. If not, define “PII” for this exercise as “identifiers that can directly identify a person or become identifying when combined.” That keeps the review focused on what matters operationally.
Extract the minimum useful fields from logs
You do not need full SQL text for every query to get started, and you often shouldn’t store it in a new place without careful handling. For a fast first pass, extract only what you need to map access and flows:
- Timestamp
- Actor identity: database user, role, service account, or IAM principal
- Client/app identifier: application name, hostname, connection string tag
- Operation: SELECT / INSERT / UPDATE / DELETE
- Objects touched: schema.table (and ideally columns if available)
- Row counts or bytes: if available, helps prioritize high-impact access
- Query fingerprint: normalized hash to group similar queries
If column-level access isn’t available in your logging, table-level mapping is still valuable. You can later refine by inspecting representative query samples for the highest-risk tables.
Identify PII tables and “PII-adjacent” join points
The fastest way to find PII in logs is to start with a dictionary of common column names and table patterns. Examples include email, phone, first_name, last_name, address, dob, ip, customer, user_profile, payments, contacts. Then:
- Mark obvious PII tables (accounts, customers, contacts).
- Mark join keys that link PII to activity data (user_id, account_id, external_id).
- Mark export surfaces (reporting schemas, materialized views, “analytics_*” tables).
“PII-adjacent” tables matter because they enable re-identification when combined with PII. A least-privilege plan should treat broad access to join keys as a risk multiplier.
Turn logs into two artifacts: an access matrix and a flow graph
1) Build an access matrix (least privilege)
Create a simple table where each row is:
- Actor (role/service account)
- Source (app, BI tool, ETL job)
- PII object (table/column group)
- Operation (read/write)
- Frequency (queries/day or week)
- Justification (unknown at first; filled during review)
This matrix is your working backlog. Sort by “read access to PII” and then by frequency. The top rows are usually where privilege tightening pays off fastest.
2) Build a flow graph (data movement)
For the diagram, model systems as nodes and accesses as edges:
- Nodes: app services, databases, warehouses, BI tools, support tooling, exports/buckets.
- Edges: “reads PII from,” “writes PII to,” “exports,” “syncs.”
- Edge labels: identity (role) and object group (which PII domain).
Keep the first diagram readable. It’s better to have one clear page than five dense diagrams nobody maintains.
Generate a clear visual quickly with napkin.ai
Once you have the node/edge list, convert it into a structured text prompt and hand it to napkin.ai. This is where the “under an hour” promise becomes realistic: you avoid fiddling with diagram layouts and focus on correctness.
A practical input format is:
- Systems: API, Worker, Postgres, Warehouse, BI, Support Tool
- PII domains: Identity (name/email), Contact (phone/address), Security (IP/device)
- Flows: API → Postgres (read/write Identity), Worker → Warehouse (write Identity), BI → Warehouse (read Identity)
- Access: role_api (SELECT on customers), role_bi (SELECT on customer_views)
Then customize the diagram to highlight risk: color PII stores, mark external sinks, and visually separate human tools (BI, support consoles) from service accounts.
Least-privilege decisions you can make from the first pass
Even a coarse access map supports immediate improvements:
- Split “god roles” into narrower roles by app or job function.
- Replace raw table access with views that omit sensitive fields by default.
- Restrict joins that bridge PII to behavioral data unless explicitly needed.
- Time-box elevated access for support and incident response.
- Remove dead access where logs show zero usage over the chosen window (verify before removal).
If you need a parallel mental model for “who should receive what,” the same matrix thinking used in a feedback routing matrix applies well: explicit rules beat tribal knowledge when you’re assigning access by role and responsibility.
Common pitfalls and how to avoid them
Over-trusting a short log window
A 24-hour window can miss monthly jobs and rare incident workflows. If you’re cutting privileges, validate with a longer lookback for the specific identities you plan to change.
Ignoring indirect PII leakage paths
Exports, CSV downloads, “temporary” tables, and ad-hoc analyst queries often create the real risk. Treat BI tools and scheduled reports as first-class nodes in the flow diagram.
Confusing “can access” with “did access”
Logs tell you what happened, not what is possible. Use the log-driven map to prioritize, then cross-check with grants/IAM policy to find excessive permissions that weren’t exercised during the window.
Make it reviewable: the 15-minute stakeholder check
Before you call it done, run a short review with engineering and security:
- Confirm the top 5 PII tables and whether any are missing.
- Confirm which apps own each access path.
- Mark any access that is “surprising but valid” versus “surprising and wrong.”
- Create a small action list: remove, narrow, gate, or monitor.
The goal isn’t perfection. It’s a shared, visual baseline you can keep updating as systems evolve.
