Tokenising and pseudonymising personal data in app databases

TL;DR — If your app needs to keep working with user records but most services do not need raw personal data, split identity data from business data and replace direct storage of PII with tokens or deterministic pseudonyms. The most common good design is: store sensitive values in a small, tightly-controlled vault table or service; store opaque tokens in the main application tables; use keyed hashes only for lookup fields where you need equality matching but not reversal. Reading time: ~7 min

What it is and where it sits

Tokenising and pseudonymising are database design patterns for reducing how much raw personal data is exposed to the application, analytics jobs, support tooling, and backups.

In a typical app, the anti-pattern is simple: users.email, users.phone, orders.shipping_address, and support_tickets.customer_name all live in the same primary database, readable by the same app role, copied into staging, and replicated into BI pipelines. That is what you are replacing.

The practical replacement is usually one of these:

• Tokenisation: the main application database stores an opaque token like tok_2f4f...; the real value lives in a separate table or service with stricter access controls.
• Pseudonymisation: the main database stores a transformed value that still lets you correlate records, typically a deterministic keyed hash like HMAC(email_normalized), but not recover the original value from the database alone.

Architecture-wise, this sits between your write path and your persistence layer. Your app or a dedicated privacy service transforms incoming PII before it hits the broad-access tables.

[Client] ---- 1. POST /signup email,phone ----> [App API]
[App API] ---- 2. tokenize/rewrite PII ----> [PII Vault]
[App API] ---- 3. store token + pseudonyms ----> [App DB]
[Worker] ---- 4. read tokenized records ----> [App DB]
[Worker] ---- 5. detokenize only if allowed ----> [PII Vault]
[BI/Analytics] ---- 6. read pseudonyms only ----> [Replica/Warehouse]

What talks to it:

• Write path: signup, checkout, profile update, support intake.
• Read path: customer support tools, email/SMS senders, fraud systems, analytics jobs.
• Admin/ops path: backups, replicas, staging refreshes, ad-hoc SQL access.

What it replaces:

• direct PII columns in broad-use tables
• raw PII copied into logs, events, and warehouses
• “encrypt the whole disk and call it done” thinking

Where it lives:

• Minimum viable pattern: same Postgres cluster, separate schema/table, separate DB role, column-level encryption handled by app or extension.
• Stronger pattern: separate database or service for PII vault, separate credentials, separate network policy, separate audit trail.

If your current app role can SELECT email FROM users in production, and your BI role can too, you have not meaningfully reduced exposure.

How it actually works

Walk one realistic example: a SaaS app stores customer profiles and sends billing emails. Requirements:

• app needs to display a customer record to support staff
• billing worker needs the real email address
• analytics only needs to count distinct users and join events to accounts
• most engineers and jobs should not read raw email or phone

Step-by-step flow

1. Client submits signup payload:

{"email":"Alice.Example+trial@Example.com","phone":"+1 415 555 0101","name":"Alice Example"}

1. App normalizes fields before any token/hash operation.

   • email: lowercase domain and local part if your product treats email case-insensitively; strip surrounding whitespace; decide whether to preserve +tag based on your login semantics.
   • phone: parse to E.164.
   • name: usually do not create deterministic pseudonyms for names unless you have a very specific use case; names have low entropy and are easy to brute-force.

2. App creates:

   • an opaque token for reversible fields: email_token = tok_7b6c..., phone_token = tok_91aa...
   • a deterministic pseudonym for lookup/join fields: email_hmac = HMAC_SHA256(k_lookup, normalized_email)

3. App writes the real values into the vault keyed by token.

4. App writes business record into main DB:

   • users.email_token
   • users.email_hmac
   • users.phone_token
   • no raw email or phone

5. Billing worker reads users.email_token, calls vault or joins a restricted vault table, gets the real email, sends the message.

6. Analytics pipeline receives only user_id and email_hmac if it truly needs cross-table deduplication. It never sees the raw email.

What the tables usually look like

Main DB table:

CREATE TABLE app_public.users (
  id uuid PRIMARY KEY,
  account_id uuid NOT NULL,
  email_token text NOT NULL UNIQUE,
  email_hmac bytea NOT NULL,
  phone_token text,
  display_name text,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX users_email_hmac_idx ON app_public.users (email_hmac);

Vault table:

CREATE TABLE pii_vault.secret_values (
  token text PRIMARY KEY,
  kind text NOT NULL CHECK (kind IN ('email','phone')),
  ciphertext bytea NOT NULL,
  key_version integer NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

Why both token and pseudonym?

Because they solve different problems.

• Token lets you recover the original value when a tightly-scoped workflow needs it.
• Deterministic pseudonym lets you do equality lookup without recovery.

Example: “does this signup email already exist?” should usually query email_hmac, not detokenize every row.

End-to-end example query path

User signs up twice with the same normalized email.

• App computes same email_hmac both times.
• App checks:

SELECT id FROM app_public.users WHERE email_hmac = decode($1, 'hex');

If found, reject duplicate or link account per your business rules.

Support agent opens customer profile.

• Support API reads users.display_name directly.
• If the support role is allowed to reveal email, API detokenizes email_token via vault.
• If not allowed, API returns masked data like a***@example.com, ideally generated in the privileged service, not in the browser.

When to use it (and when not to)

Use it when the problem is overexposure of PII inside your own systems, not just theft of a disk or snapshot.

You probably do not need this if your real issue is simply bad access control. If only one service should read PII, first stop granting broad SELECT rights and stop exporting raw columns to logs, events, and BI.

Trade-offs

Every benefit costs something.

• Less blast radius if app DB leaks → costs extra systems and key management. A tokenized users table is less useful to an attacker, but now you operate a vault, key rotation, and stricter auth paths.
• Safer analytics and lower accidental exposure → costs data model friction. Analysts lose direct access to email/phone; some ad-hoc workflows become impossible without approved joins or derived datasets.
• Supports erasure-by-severing-link → costs careful retention design. Deleting vault rows may break billing disputes, fraud investigations, or legal holds if you did not classify records first.
• Deterministic lookup on pseudonyms → costs linkability. Same input maps to same pseudonym, which is useful for dedupe but leaks equality. If email_hmac appears in multiple systems, they can correlate records.
• Reversible tokenisation for operational workflows → costs latency and failure modes. Every detokenization call adds a dependency. If the vault is down, email sending and support views may fail.
• Reduced insider access → costs operational overhead. You need separate DB roles, audit logging, break-glass access, and a migration plan.

Two edge cases experienced devs ask about:

Low-entropy fields

Do not store plain unsalted hashes of emails, phones, ZIP codes, countries, or dates of birth and call it pseudonymisation. Those are brute-forceable with dictionaries. Use a secret-keyed HMAC for equality matching. Even then, low-entropy fields remain sensitive because equality and frequency leak information.

Rotation

If you rotate the HMAC key, deterministic lookups break unless you rederive values. Plan for hmac_key_version or dual-read during migration. Token encryption key rotation is easier if the vault stores key_version and supports decrypt-reencrypt in batches.

In practice

Example 1: Postgres schemas and roles

⚠️ Changing privileges on production tables can break app traffic immediately. Run these statements first in staging with the exact same roles your app, workers, and BI jobs use.

CREATE SCHEMA IF NOT EXISTS app_public;
CREATE SCHEMA IF NOT EXISTS pii_vault;

REVOKE ALL ON SCHEMA pii_vault FROM PUBLIC;
REVOKE ALL ON ALL TABLES IN SCHEMA pii_vault FROM PUBLIC;

CREATE ROLE app_runtime NOINHERIT;
CREATE ROLE pii_reader NOINHERIT;
CREATE ROLE analytics_reader NOINHERIT;

GRANT USAGE ON SCHEMA app_public TO app_runtime, analytics_reader;
GRANT SELECT, INSERT, UPDATE ON app_public.users TO app_runtime;
GRANT SELECT ON app_public.users TO analytics_reader;

GRANT USAGE ON SCHEMA pii_vault TO pii_reader;
GRANT SELECT, INSERT, UPDATE, DELETE ON pii_vault.secret_values TO pii_reader;

REVOKE SELECT (email_token, phone_token) ON app_public.users FROM analytics_reader;

This creates a separate vault schema and distinct roles so analytics can read business rows without automatically reading vault contents. Gotcha: Postgres column-level REVOKE only helps if you also stop analysts from creating SECURITY DEFINER functions or using a more privileged role indirectly.

Example 2: Application write path in Node.js

import crypto from 'node:crypto';
import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const HMAC_KEY = Buffer.from(process.env.PII_LOOKUP_HMAC_KEY, 'hex');

function normalizeEmail(email) {
  return email.trim().toLowerCase();
}

function emailHmac(email) {
  return crypto.createHmac('sha256', HMAC_KEY).update(normalizeEmail(email)).digest();
}

function newToken() {
  return 'tok_' + crypto.randomBytes(16).toString('hex');
}

async function createUser({ id, accountId, email, displayName }) {
  const client = await pool.connect();
  const token = newToken();
  const hmac = emailHmac(email);
  const ciphertext = encryptForVault(normalizeEmail(email));

  try {
    await client.query('BEGIN');
    await client.query(
      `INSERT INTO pii_vault.secret_values (token, kind, ciphertext, key_version)
       VALUES ($1, 'email', $2, $3)`,
      [token, ciphertext, 1]
    );
    await client.query(
      `INSERT INTO app_public.users (id, account_id, email_token, email_hmac, display_name)
       VALUES ($1, $2, $3, $4, $5)`,
      [id, accountId, token, hmac, displayName]
    );
    await client.query('COMMIT');
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
}

This writes the vault row and app row in one transaction when both schemas are in the same database. Gotcha: if your vault is a separate service or database, you lose single-transaction atomicity; use an outbox or compensating cleanup so you do not leave orphaned tokens or business rows.

Example 3: Detect duplicate email by pseudonym

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<'SQL'
SELECT encode(email_hmac, 'hex') AS email_hmac_hex, id
FROM app_public.users
WHERE email_hmac = decode('5f8c8c...deadbeef', 'hex');
SQL

Typical output shape:

          email_hmac_hex           |                  id
-----------------------------------+--------------------------------------
 5f8c8c...deadbeef                 | 8c2a4d3d-9d4a-4d0c-bd1c-0fd8b2d6b901
(1 row)

This is the pattern you want for equality lookup without exposing the original email. Gotcha: if normalization rules change later, old and new HMACs will not match. Version your normalization logic the same way you version keys.

Further reading

• NIST Privacy Framework
• GDPR Recital 26 and Article 4 definitions
• OWASP Cryptographic Storage Cheat Sheet
• PostgreSQL documentation: GRANT and REVOKE
• RFC 2104: HMAC

This article was written by an AI system and published pending human review. Verify anything you intend to act on.