Searching Encrypted Data with Blind Indexes
MapFlow encrypts PII at the application layer (AES-256-GCM) before it reaches Postgres, so the database only ever stores ciphertext. That means a normal WHERE or LIKE on an encrypted column returns nothing — there is no plaintext to match. The only searchable artifact that exists for an encrypted column is its blind index: a parallel HMAC-SHA256 token column written alongside each value.
The core principle
Because the data is encrypted, only blind indexes exist for searching it. A blind index is not the plaintext, not a reversible hash, and not a decryption — it is a keyed, one-way HMAC token. The database can match tokens against each other (so a search finds the right row) but cannot reverse a token back to the value it came from. The HMAC key lives only in the server runtime, derived from the out-of-band BLIND_INDEX_PEPPER secret.
Stored column
AES-256-GCM ciphertext (ENC:…) — unreadable to the database.
Blind index column
{field}_blind_idx — HMAC-SHA256 token, indexed for O(log n) lookup.
What the DB sees
Two opaque strings. Neither reveals the citizen's name, address, or postcode.
How it works, step by step
1 · Data is encrypted at the application layer
Every free-text PII column chosen in the Copy-to-Postgres dialog is encrypted with AES-256-GCM before it is written to Postgres. The value stored in the database is ciphertext — it starts with ENC: and is meaningless to anyone holding only the database.
2 · The database cannot search ciphertext
Because the column holds random-looking ciphertext, a normal WHERE name = 'Smith' or LIKE '%SW1%' returns nothing — there is no plaintext to match. The database engine, the Postgres service role, and even a cloud-provider disclosure order all see only ciphertext.
3 · A blind index is written alongside each value
For every blind-indexed field, MapFlow computes an HMAC-SHA256 token of the (trim + lowercased) plaintext and writes it into a parallel {field}_blind_idx column at insert time. The HMAC key is held in the server runtime only — derived from the out-of-band BLIND_INDEX_PEPPER secret — and never sent to the database.
4 · Searching = matching the token, not the text
To search, the admin types a term in the Postgres Table Viewer. MapFlow computes the same HMAC of the query term server-side and runs an exact-match SELECT on the blind_idx column. The database performs a fast O(log n) btree lookup on the token — it never learns the plaintext term or the plaintext value.
Blind indexes & the schema rebuild
The schema build that shows relationships and lookups is the same build that carries blind-index columns. The blind-index set is chosen once in the Copy-to-Postgres dialog, persisted on the schema copy record, and re-applied on every rebuild — whether you trigger it manually or it runs overnight.
The PII Protection selector in the Copy-to-Postgres dialog — the same schema build that shows relationships and lookup fields — also lists every free-text column and lets you toggle a blind index on/off per field. Defaults cover name- and location-like fields. The chosen fields are passed as blind_index_fields to copySchemaToSupabase, which creates each {field}_blind_idx TEXT column and a btree index on it.
The Weekly SF Schema Copy (Saturday) workflow spawns a rebuildSfSchemaForCopy job per catalogue. Each job reads the blind_index_fields map persisted on the SupabaseSchemaCopy record and re-applies ALTER TABLE ADD COLUMN IF NOT EXISTS {field}_blind_idx plus CREATE INDEX IF NOT EXISTS — so a rebuild that only adds new columns preserves existing blind indexes without dropping data.
The blind_index_fields map is stored on the SupabaseSchemaCopy entity at copy time, so the overnight rebuild does not need to be reconfigured — it inherits the same blind-index set chosen during the manual build. Change the set by re-running the Copy-to-Postgres dialog for that environment.
Searching encrypted columns
From the Postgres Table Viewer, admins use the blind-index search on any encrypted column that has a blind index. The search term is HMAC'd server-side (the raw term never reaches the database) and matched exactly against the {field}_blind_idx column. Matched rows return with encrypted columns still as ciphertext — admins can then use the existing Decrypt toggle to reveal them via an MFA-authorised session.
-- What the database stores (both opaque):
SELECT name, name_blind_idx FROM planning_applications LIMIT 1;
name | name_blind_idx
-------------------+------------------------------------------
ENC:vG9k…3aQ== | 7f3c2e1a9b8d4c6e5a0f1b2c3d4e5f6a7b8c9d0e
-- How an admin searches (token computed server-side, exact match):
SELECT * FROM planning_applications
WHERE name_blind_idx = '<HMAC of "smith">'; -- returns matching rows only
-- LIKE / range / ORDER BY on the encrypted column are NOT possible:
SELECT * FROM planning_applications WHERE name LIKE 'Sm%'; -- returns nothing (ciphertext)Blind indexes support exact match only (after trim + lowercase normalisation). They do not support prefix/wildcard (LIKE 'Sm%'), range, or ordering. That is the security trade-off: a deterministic token that supported partial matching would leak far more about the plaintext to a database attacker. For free-text search across encrypted content, decrypt the rows first via an MFA-authorised session and search in the application layer.
Crypto at a glance
- Value encryption: AES-256-GCM (random IV per value, prefixed ENC:).
- Blind index: HMAC-SHA256 of normalised plaintext.
- Pepper: dedicated BLIND_INDEX_PEPPER secret, independent of the AES key.
- Key custody: server runtime only — never sent to the browser or the database.
- Lookup cost: O(log n) btree on the token column.
What you can & can't do
- Can: exact-match search by name, postcode, email, etc. (case-insensitive).
- Can: count / group by the token (e.g. distinct postcode tokens).
- Can't: prefix / wildcard (
LIKE 'Sm%') search. - Can't: range (
> / < / BETWEEN) orORDER BYon the encrypted value. - Can't: join on an encrypted value (join on the blind-index token instead, or on a non-encrypted key).
Where this is configured
The blind-index set is chosen in the PII Protection section of the Copy-to-Postgres dialog — the same schema build that shows relationships and lookup fields. It defaults name- and location-like free-text fields on, and you can toggle any free-text field on or off per table. The choice is persisted on the schema copy record and inherited by every overnight rebuild.
Last reviewed 31 August 2026.