encryptedSupabaseV3: .contains() cannot match substrings — the query operand carries an include_original token

[coderdan]

Summary

.contains() on a v3 text_search / text_match column matches only when the search string equals the stored value, or is exactly 3 characters (the trigram length). Every other substring silently matches nothing.

await db.from("users").insert({ email: "alice@example.com" })

await db.from("users").select("*").contains("email", "alice@example.com")  // ✅ matches
await db.from("users").select("*").contains("email", "ali")                // ✅ matches (3 chars)
await db.from("users").select("*").contains("email", "alice")              // ❌ matches nothing

Silent, not an error. The v3 Drizzle contains has the same defect.

Root cause

Two things combine.

1. The operand is a storage payload. PostgREST has no syntax to cast a filter value, so EncryptedQueryBuilderV3Impl cannot reach the narrow eql_v3.query_text_search overload and sends the full envelope from encrypt() instead (query-builder-v3.ts:356). The reachable overload coerces it back to the storage domain:

eql_v3.contains(a public.eql_v3_text_search, b jsonb)
  → match_term(a) @> match_term(b::public.eql_v3_text_search)

2. include_original is forced on. schema/match-defaults.ts:39 sets include_original: true, and the comment there notes it is "the v2 builder default rather than the zod-schema default of false". types.TextSearch / EncryptedTextSearchColumn accept no match options, so it cannot be turned off.

Because the needle is tokenized as if it were a stored value, its bloom is trigrams("alice") + "alice". Containment requires every needle bit to be present in the haystack, whose bloom is trigrams("alice@example.com") + "alice@example.com". The trigrams are all there. The whole-needle token "alice" is not. So @> fails.

It works for a 3-character needle only because its include_original token coincides with a trigram the haystack already has.

Why v2 is unaffected: v2 query encryption runs in QueryMode, which does not emit the original token. v3 runs in StoreMode. See to_query_plaintext in protect-ffi: "QueryMode with Default under eqlVersion 2, StoreMode under eqlVersion 3."

This is a stack bug, not an EQL or protect-ffi bug

protect-ffi's own v3 integration test declares the column with match: {} (so include_original defaults to false), searches 'quick' against a stored 'the quick brown fox', and matches:

https://github.com/cipherstash/protect-ffi/blob/main/integration-tests/tests/postgres-v3.test.ts

Substring containment works correctly at the ffi layer. It is stack's include_original: true default, inherited from the v2 builder, that breaks it under v3's StoreMode query path.

Note on the unreleased protect-ffi work

protect-ffi main carries EQL v3 scalar query-term encryption (f7ad9d6, unreleased — latest tag is v0.28.0). It removes EQL_V3_QUERY_UNSUPPORTED and returns a term-only {v, i, <terms>} operand with no c.

That alone does not fix this, for two reasons:

• It still hoists StoreMode terms ("the terms are generated exactly as storage encryption generates them and query_output hoists them, dropping the ciphertext"), so the bf remains a storage bloom carrying the original token.
• The narrow operand is bindable as $1::jsonb::eql_v3.query_text_search, which Drizzle can emit but PostgREST cannot. It fixes Drizzle's path; the Supabase wrapper still cannot reach that overload.

Proposed fix

Generate a storage-shaped operand (carrying c, so it satisfies the storage domain's CHECK and survives the b::public.eql_v3_text_search coercion) whose bloom filter contains only the query's tokens, with include_original off. That keeps include_original: true on the storage side, where it usefully allows exact matching of values shorter than the trigram length, while making the needle's bloom a strict subset of the haystack's.

Two smaller variants, if a bloom-generation change in ffi is too large right now:

• Set include_original: false on the v3 match/text_search domains in match-defaults.ts. Symmetric on both sides, so containment works immediately. Cost: values shorter than 3 characters become unmatchable.
• Expose match options on types.TextSearch so callers can opt out. Leaves the default broken.

Impact

Free-text search is unusable on v3 text_search / text_match columns via both the Supabase wrapper and Drizzle. Equality, ordering, and range queries are unaffected.

The docs currently ship a warn callout describing the limitation (cipherstash/docs#39). Happy to remove it once this lands.