GeoTech

Engineering / Case study

Searching encrypted customer data without decrypting it: blind indexes in PostgreSQL

GeoTech Engineering · · 8 min read

EncryptionPostgreSQLPrivacyNode.js

Kauthar AI stores the names, phone numbers and email addresses of the people who chat with, book from and order from the businesses that use it. To meet the "protected customer data" requirements of a commerce platform we integrate with, and our own privacy promise, those fields had to be encrypted at rest. The catch is that the product is built on finding people: a returning customer is recognised by phone, a receptionist searches by the last digits of a number, a merge needs two profiles with the same phone. This is how we encrypted the data and kept those searches, what we gave up, and how we rolled it out on a live system without downtime.

6tables covered
3separate keys
60 ssweeper interval
0plaintext rows left

TL;DR

The problem

Disk encryption and database-level encryption protect against a stolen disk. They do not protect against a leaked database dump, a backup copied to the wrong place, or a query run by someone who should not see customer details. Field-level encryption does: the application encrypts before writing, so the database only ever holds ciphertext for those columns.

The fields in scope were the end-customer's name, phone and email across six tables: customer profiles, channel identities, conversations, bookings, support tickets and store orders (where the customer is a JSON object with name, phone, address and notes).

Why the usual approaches fail

Encrypt and search in memory. Decrypting every row to find one phone number works for a hundred rows and collapses at scale. It also means the search path handles plaintext for every customer of a business, which is exactly what we were trying to avoid.

Deterministic encryption. Using a fixed IV makes equal inputs produce equal ciphertexts, so you can search. With GCM, reusing an IV under the same key is a known break of the scheme. Deterministic encryption also mixes two jobs, confidentiality and lookup, into one key.

A plain hash of the phone. A phone number has few possible values. Anyone holding the table can hash every possible mobile number in a country on ordinary hardware and read the column back, so an unkeyed hash is just a slower plaintext.

The design

Sealed values

A sealed value is a string with a version prefix, a key id, and three base64url parts:

pii:v1:<kid>:<iv>.<tag>.<ciphertext>
  kid = first 8 hex of SHA-256(key)   // which key of the ring sealed it

The key id makes rotation possible: the current key seals, older keys listed separately can still open, and a resumable script re-seals every value under the new id. A value without the prefix is legacy plaintext and is returned as it is, which is what made the rollout possible. A prefixed value that no key opens is never returned as text: the read gives null and logs a counter, never the value. These keys are separate from the key that protects business secrets such as API keys, so a rotation or leak of one never touches the other.

Blind indexes

For every sealed field that needs lookup, a second column stores a keyed HMAC of a normalised form of the value, with its own key that must differ from the encryption key:

bidx(kind, v) = HMAC_SHA256(INDEX_KEY, kind + ":" + normalise(kind, v)).hex.slice(0, 32)

normalise('phone', v)  // E.164 digits: "+966 5…", "00966 5…", "05…" + business country,
                       // Arabic-Indic digits -> all the same index
normalise('email', v)  // trimmed, lower-cased
normalise('name',  v)  // lower case, no diacritics, unified Arabic letter forms

Normalisation is the part that decides whether search feels right. A customer who typed their number with Arabic digits on WhatsApp and with a country code on the web chat must land on the same profile. A local number without a country cannot be normalised reliably, so it is indexed using the business's country, or not at all.

The kind prefix keeps an email index from ever colliding with a phone index of the same text. Uniqueness moved with it: "one profile per phone per business" used to be a unique constraint on the phone column, which stopped working the moment phones were encrypted with random IVs. It is now a unique index on (tenant_id, phone_bidx).

Whole-word names and phone tails

Staff do not type full names. For names we store an array of 16-hex HMACs, one per normalised word (up to 12 words), in a text[] column with a GIN index. Searching for a name computes the tokens of the query and asks for rows containing all of them. "Ahmed" finds "Ahmed Ali"; "Ahm" does not.

Receptionists often search by the end of a phone number. For that we store one HMAC per trailing length, from the last 4 digits to the last 9, also in a GIN-indexed array:

-- search "1122": the app computes HMAC('t', '1122') and binds it
SELECT id FROM contacts
 WHERE tenant_id = $1
   AND (phone_bidx = $2                       -- full number, any format
        OR phone_tail @> ARRAY[$3]::text[]    -- last 4..9 digits
        OR name_tok   @> $4::text[]           -- every word of the query
        OR (phone NOT LIKE 'pii:v1:%' AND phone ILIKE $5));  -- legacy rows only

The last line keeps unconverted rows searchable during migration, and the NOT LIKE guard stops a fragment from ever matching characters inside the base64 of a sealed row.

What we gave up, deliberately

Keeping it true over time

The design is only as good as the least careful write path. Three mechanisms keep it honest:

  1. Transparent reads. The database helper runs every result through one function that opens any value with the prefix: a column, a JSON field up to three levels deep, or a sealed value embedded in a label built in SQL. No read path had to change.
  2. A sweeper. Every 60 seconds a job reads a partial index per table whose predicate is "a covered column holds plaintext, or its blind index is missing". After the backfill those indexes are nearly empty, so the job costs almost nothing, and anything a forgotten code path wrote in plaintext is sealed within a minute.
  3. Stale-index triggers. A BEFORE UPDATE trigger clears a blind index when its source column changes in a statement that did not also write a new index. A wrong index is worse than a missing one; the sweeper rebuilds missing ones. Transactions that write every index themselves set a local flag to skip this.

Rollout on a live system

The order was: deploy the code with no keys (everything stays plaintext, the migration only adds columns, indexes and triggers), set two different keys, restart, run a resumable backfill while the app serves traffic, then flip a "required" switch. After that switch the server refuses to start without valid keys, so a missing key can never silently put the system back into plaintext mode.

The one trap the backfill surfaced: two older profiles in the same business could have phones written differently that normalise to the same number. They cannot both get the unique index, so the backfill seals them without one and reports them as conflicts for a person to merge.

The result on production, recorded when the rollout finished: encryption enabled and required, every covered row sealed, zero rows left in plaintext, and nightly backups encrypted as well.

Checklist

  1. List every column that holds a person's data, and write down the ones you will not cover, with the reason.
  2. Use authenticated encryption with a random IV, a key id in the stored value, and a keyring for rotation.
  3. Use a separate HMAC key for indexes. Never a plain hash.
  4. Normalise before indexing: phone formats, digits, case, letter forms.
  5. Decide which searches survive (full value, suffix, whole words) and tell your users before you ship.
  6. Move unique constraints to the blind index.
  7. Fail closed: a value that cannot be decrypted is empty, never garbage, and never logged.
  8. Add a safety net that seals anything a write path left in plaintext.

Need this?

Kauthar AI encrypts the personal data of your customers by default, and its team still finds them by phone or name in seconds. If your own system stores customer data in plaintext and you need field-level encryption without losing search, we can design and roll it out with you. Talk to GeoTech.