Findings in SecurityTrackr are encrypted at rest, each organisation under its own key, and sealed with an authenticated cipher that binds them to that org. The database holds ciphertext and nothing else, which is exactly what you want the morning a backup leaks. It's also a problem, because you can't run a LIKE query or a full-text index over bytes you can't read. So how do you search a register you've deliberately made unreadable? Here's how we do it without storing a single plaintext word, and the pattern transfers to any encrypted store.

The problem

You can't search what you can't read

An observation's title, affected systems, and your written description are all encrypted columns. To the database they're opaque blobs. Ordinary search is off the table: there's no LIKE over ciphertext, and a full-text index would need the plaintext it doesn't have.

You could decrypt every finding on each search and scan the plaintext in memory, but that doesn't scale. One query would mean pulling and decrypting the entire register, and it gets slower with every observation you add.

The obvious shortcut is to keep one plaintext column on the side, a small bag of keywords, and search that. It works, and it quietly undoes the encryption: anyone who can read the database (a leaked backup, a curious DBA) gets a clean, word-level fingerprint of every finding you took the trouble to encrypt. That's the trap to avoid. The question is what to do instead.

The idea

A blind index

The answer is a blind index. Instead of storing words, store one-way hashes of words, and arrange the hashes so a search can match them without anyone ever turning them back into text. We keep a side table of per-org token hashes. No plaintext is persisted, yet a finding stays searchable by which hashes it shares with your query.

plaintext text / keywords
  -> canonicalTokens()           normalize: lowercase, split,
                                 drop noise, light stem
  -> hashSearchTokensForOrg()    per-org HMAC-SHA-256, 128-bit
  -> ObservationToken.tokenHash  store / match

The whole trick is in one word: deterministic. The same pipeline runs when we index a finding and when you type a query. Because both sides normalize the same way before hashing, the hash of a word in a title is byte-for-byte the hash of that word in your search box. Matching is then just set intersection over hashes. Equal tokens, equal hashes, no decryption involved.

Step one

Normalize before you hash

Hashing is unforgiving. Change one character and the hash is unrelated, so everything hinges on both sides agreeing on the exact token first. That's the normalizer's whole job.

Lowercase, then split. But keep - _ . : and / so IPs, CVE IDs, ports, and file paths survive as single tokens instead of shattering.
Expand compounds. offline-backup becomes offline-backup, offline, and backup, so either half finds it.
Drop the noise. Stop words and anything under three characters go, using the same stop list as the duplicate scorer.
Stem lightly. Just enough to collapse plurals and tenses (certificate and certificates, scan and scanning), so a singular query finds a plural title. Not so much that restriction and unrestricted blur together.

Every one of those rules has to be identical on both sides, which is why there's exactly one normalizer. The same function runs when a finding is filed and when you go looking for one, so the two sides can't drift apart.

Step two

One key per org, derived not borrowed

A plain hash of a word would be identical for everyone, and a dictionary of common security terms would unmask it in seconds. So we don't hash, we HMAC, under a key unique to your organisation.

That key isn't your encryption key reused. It's derived from your org's data key through HKDF under its own label, so the search key and the AES key are cryptographically separate even though they share a root. The result is deterministic per word and per org, and different across orgs: the word “certificate” hashes to one value in your tenant and a completely unrelated value in someone else's.

Why per-org keying matters

It isolates the index itself, not just the query filter. Even if two organisations both have a finding about expired certificates, the stored hashes don't match, so there's no way to correlate findings across tenants by lining up the index. Tenant isolation holds at the cryptography level, not only at the WHERE clause.

Ranking

Putting the best match first, blind

Matching is the easy half. Ranking what you can't read is harder, and it's where two small choices earn their keep.

Identifiers stay in the clear. A finding's reference like Obs003 is not a secret, so it isn't hashed. It's matched by plain substring and ranked first, so looking up a known finding by ID always just works.
Title hits beat body hits. A query word in the title means more than the same word buried in the notes. The title is encrypted, so we compute that at query time: decrypt only the few top candidates, re-tokenize their titles, and count the overlaps. No extra plaintext is stored to make ranking work.

A finding matched only in its body still surfaces, just below the ones matched in their title. Recall is preserved, precision is rewarded, and the whole ordering is computed from things we can re-derive on the fly rather than anything sitting in the clear.

The honest cost

Whole words only

A blind index isn't free, and the bill comes due as you type. Because hashing destroys structure, the hash of “unrestr” has nothing to do with the hash of “unrestricted”. So as-you-type prefix matching on words is gone: the dropdown finds nothing until you've typed a whole word (light stemming still covers plurals and tenses). You could win prefix matching back by storing a hash of every prefix, but those are low-entropy and would hand a database thief most of the plaintext anyway, which defeats the point.

A trade-off you can't see is a bug, so we don't hide this one. A small (i) next to every search box explains the whole-word rule, and the no-results state says “type a full word” rather than letting an empty result read as “nothing here”. A complete observation identifier query such as Obs003 is compared by exact equality and ranked first; fragments do not enter identifier lookup.

The threat model

What a thief actually gets

Line it up against the attacker the encryption is for: someone with the database but not the keys. A leaked backup, a malicious DBA. To them the HMAC is irreversible, so the token contents stay secret. That's strictly better than the obvious alternative, a plaintext keyword column, which would hand that same person the words directly.

It also holds against a subtler attacker: one who has the database and one org's key, and wants to reach into another's. The fields are sealed with AES-GCM, an authenticated cipher (AEAD), with the owning org bound into the authentication tag as additional data. So a tenant's ciphertext won't open under any key but its own, and only with that org's context: the wrong key fails, and a ciphertext lifted from another tenant fails its integrity check before a byte of plaintext appears. The search index is keyed the same per-org way. Cross-tenant isolation is enforced by the cryptography, not by a WHERE clause you have to trust.

There's one honest residual. A keyless reader can still see structure: which rows share a hash, and how common a given hash is, inside one org. That's inherent to any deterministic index, it's not reversible, and it's a fraction of what a plaintext keyword column would leak. Anyone who does hold your org key could decrypt the fields outright, so the hashes tell them nothing new. The index is a strict improvement at both ends.

Takeaways

The pattern, portable

Encrypt the sensitive columns, and refuse to keep a plaintext search proxy beside them.
Build a blind index: normalize text to tokens, then store one-way HMAC hashes, not words.
Run the exact same normalizer on both sides so query tokens and index tokens are byte-identical.
Key the HMAC per tenant, derived from the tenant key via HKDF, so the index is isolated too.
Rank with things you can re-derive at query time, and surface the whole-word trade-off instead of hiding it.

← All articles

Searching data you can't read.