PII, Data Privacy and Audit Trails

In one line

Classify data (public, internal, confidential, PII, sensitive PII), minimise what you collect, and protect PII with masking, tokenisation and field-level encryption. Support retention limits and the right to deletion across every copy. Keep append-only audit trails recording actor, action, time, before and after values, and request and trace IDs.

0/3 · 0%

Think of it like this

A clinic's reception. Staff see a patient's name and appointment time, not the diagnosis; the full record is locked; every access to it is logged with who and why; and when a patient leaves, their records are destroyed once the legal period ends.

Key ideas

01
Classification drives controls: tag columns in a data catalogue (and with COMMENT ON COLUMN), and apply rules per class: who can read, encryption, masking in non-production, retention.
02
Masking: show partial values (XXXX-XXXX-1234) in UIs and to support roles via views; production data copied to test must be masked or synthesised, never raw.
03
Tokenisation: replace sensitive values with random tokens and keep the mapping in a separate, tightly controlled vault (standard for card numbers; limits PCI scope). Hashing with a secret salt (HMAC) allows equality lookups without storing the raw value.
04
Right to deletion (GDPR Article 17, India's DPDP Act): find every copy (primary, replicas, caches, search, warehouse, logs, backups); delete or anonymise; use crypto-shredding for backups; keep a deletion log without the deleted PII. Some records must be retained by law (invoices), so separate identity from them.
05
Audit record fields: actor (user or service identity), action, target (table and key), timestamp, before and after values (excluding secrets), request ID and trace ID for correlation, source IP or client. Store append-only (no UPDATE/DELETE grants), ideally shipped to a separate system (SIEM or WORM storage).

Code & diagrams

privacy.sqlsql

COMMENT ON COLUMN customer.phone IS 'classification=PII';
COMMENT ON COLUMN customer.pan   IS 'classification=SENSITIVE_PII;encrypted=app';

-- masked view for support
CREATE VIEW support_customer AS
SELECT id, full_name,
       left(phone, 2) || repeat('*', length(phone) - 4) || right(phone, 2) AS phone_masked,
       city
FROM customer WHERE deleted_at IS NULL;
GRANT SELECT ON support_customer TO support;

-- equality lookup without storing raw email for a suppression list
-- (HMAC key held by the application / KMS, not in the database)
CREATE TABLE email_suppression (email_hmac bytea PRIMARY KEY, reason text, added_at timestamptz);

-- pgaudit for sensitive tables
-- postgresql.conf: shared_preload_libraries = 'pgaudit'; pgaudit.log = 'write, ddl'

Interview problem

The problem

Implement account deletion for a consumer app

A user requests deletion. Their data is in PostgreSQL (profile, orders, messages), Redis (session, cart), Elasticsearch (profile search), the warehouse, application logs and backups. Invoices must be kept 8 years for tax. Design the process.

When it breaks

Production database copied to staging for debugging

What you see

Real customer PII sits in a less-protected environment, accessible to contractors; a staging leak becomes a reportable breach.

Fix & prevent

Masked or synthetic data for non-production, automated masking pipelines, and no direct copies of production.

Explain it without notes

What's the difference between tokenisation and encryption?

Practice

List the fields an audit record needs to reconstruct who changed an order's status and why.

Trade-offs

↔
Privacy controls add engineering and slower access workflows; they reduce breach impact and legal exposure dramatically.

Done when you can

I can classify data, mask and tokenise PII, implement deletion across copies, and design audit trails.

Command Palette