Chapter 17 · Security, Privacy, Governance, and Auditability
Sensitive Data Classification and Minimization
Classify sensitive data, minimize unnecessary collection, separate secrets from identifiers, and design masking, tokenization, encryption, and purpose-limited access.
Learning outcomes
Privacy-aware database design starts with a simple principle: data you never collect cannot be leaked, misused, or retained too long. Sensitive information should be classified, minimized, protected, and separated according to its purpose.
Classify personal, confidential, credential, and regulated data.
Apply data minimization and purpose limitation.
Distinguish hashing, encryption, tokenization, and masking.
Design WorkshopHub so sensitive fields are not casually propagated.
Data classification
| Class | Examples |
|---|---|
| Public | Part catalog marketing description |
| Internal | Operational notes, internal codes |
| Confidential | Customer contacts, contracts |
| Highly sensitive | Credentials, government identifiers, payment secrets |
Classification belongs in metadata
Document field sensitivity:
Customer.billing_email -> confidentialCustomer.tax_identifier -> highly sensitiveWorkOrder.problem_description -> potentially sensitive free textThis helps drive masking, access, retention, and logging policies.
Data minimization
Before adding a column, ask:
- Do we need it to provide the service?
- Could a less-sensitive value achieve the same purpose?
- How long must it exist?
- Who needs to read it?
- Will it be copied downstream?
Free-text fields are privacy hazards
Users can place arbitrary personal data into:
notesdescriptioncommentsattachmentsThese fields are harder to classify, search for deletion requests, and mask. Do not overuse unconstrained free text.
Hashing
Hashing is one-way and useful for values such as passwords when combined with a modern password-hashing algorithm and proper parameters. Ordinary fast hashes are not appropriate password storage.
Encryption
Encryption is reversible with a key. Use it when the application must recover the original sensitive value.
Encryption at rest is not field-level authorization
Disk/database encryption protects stolen storage media, but a privileged SQL query can still read decrypted values. Sensitive fields may need application/column-level encryption and narrow key access.
Encryption at rest, transport encryption, field encryption, masking, and authorization solve different threats.
Tokenization
Tokenization replaces a sensitive value with a surrogate token while the original is held in a protected vault or separate system:
payment_token = tok_8f3...The operational database can reference the token without storing the original payment credential.
Masking
ab***@example.com**** **** **** 1234Masking is for display/logging reduction. It is not encryption and does not replace access control.
Separate identity from activity when useful
Analytical/event systems may use:
customer_surrogate_idinstead of copying names/emails into every event. This reduces duplication of personal data.
Pseudonymization
Replacing direct identifiers with stable pseudonymous IDs can reduce exposure while preserving joins. If the mapping exists, the data may still be personal and must be governed accordingly.
Avoid sensitive data in keys
Do not use:
email as primary keygovernment ID in URLsphone number as tenant keyStable surrogate identifiers reduce propagation of sensitive values into logs and foreign keys.
Sensitive data in logs
Database design influences logs because application queries and events often serialize rows. Mark fields that must never appear in plaintext logs.
Backups and replicas contain sensitive data too
Security classification applies to:
- primary database;
- read replicas;
- backups;
- exports;
- developer snapshots;
- warehouse copies;
- search indexes.
Development/test data
Production personal data should not be casually copied into developer environments. Use synthetic data or controlled masking/anonymization pipelines.
WorkshopHub sensitive-data map
| Field | Approach |
|---|---|
| Customer name/contact | Confidential, role-limited |
| Tax identifier | Separate/highly restricted, encrypt/tokenize as needed |
| Technician password | Do not store plaintext; password hash only |
| External API secret | Secret manager, not business table |
| WorkOrder free text | Potentially sensitive, retention/search controls |
Practice: do you need the field?
Government identifier
A developer proposes storing every customer's government ID “in case billing needs it later.” What is the correct modeling response?
Review answer
Do not collect it without a defined purpose and requirement. If later required, define the legal/business purpose, access group, retention period, protection method, and whether tokenization or a separate protected store is more appropriate.
Summary and next lesson
Privacy-aware schemas minimize sensitive data, classify what remains, and protect it with layered controls. The next lesson applies ownership and least privilege to multi-tenant systems, where one of the most important invariants is preventing data from one customer from becoming visible to another.
References
- NIST privacy and security guidance.
- OWASP Cryptographic Storage and Secrets Management guidance.
- Applicable privacy/regulatory requirements for the deployment jurisdiction and domain.