perf: encryptedSupabase emits bare-column query forms that don't engage Supabase functional indexes

[coderdan]

Summary

encryptedSupabase is the integration that explicitly targets Supabase, yet emits exactly the SQL form Supabase can't index. Every encrypted equality lookup, pattern match, and ordering on a Supabase deployment is a sequential scan today.

This is the highest-impact finding from a wider audit of query-form correctness across the stack integrations (see also #419 for the agent-skills docs gap and the related issue for Drizzle).

Background

EQL's Supabase variant omits the custom operator classes (they require superuser, which Supabase doesn't grant the app role). On Supabase, the only fast index path is functional indexes that wrap the column with an EQL extraction function:

```sql
CREATE INDEX users_email_hmac_idx
ON users USING hash (eql_v2.hmac_256(email));

CREATE INDEX users_email_bloom_idx
ON users USING gin (eql_v2.bloom_filter(email));
```

For these indexes to engage, queries must be in the matching wrapped form:

```sql
-- Engages bench_text_hmac_idx
SELECT * FROM users
WHERE eql_v2.hmac_256(email) = eql_v2.hmac_256('alice@x.com'::jsonb::eql_v2_encrypted);
```

Bare `col = value` falls back to seq scan silently.

What encryptedSupabase actually emits

PostgREST's filter syntax has no mechanism to wrap a column reference in a function call. So `.eq()`, `.in()`, `.match()`, `.like()`, `.ilike()`, `.order()`, etc. all compile to:

Builder method	Emitted SQL form	Engages functional index?
`.eq()` / `.neq()` / `.in()` / `.match()`	`col = <encrypted_jsonb>`	❌ no
`.like()` / `.ilike()`	`col LIKE …`	❌ no
`.gt()` / `.gte()` / `.lt()` / `.lte()`	`col > ` etc.	❌ no (also no Supabase index path today)
`.order(col)`	`ORDER BY col`	❌ no

Evidence:

• `packages/stack/src/supabase/query-builder.ts:778-808` — `switch (f.op) … q.eq(f.column, value)` etc., PostgREST passthrough with no wrapping.
• `packages/stack/src/supabase/query-builder.ts:673-674` — `case 'order': query = query.order(t.column, t.options)`.
• `packages/stack/src/supabase/query-builder.ts:796-801` — bare `.like` / `.ilike` passthrough.

Risk

This is the integration with the most Supabase exposure — its entire raison d'être is the Supabase JS SDK shape. Customers who pick it specifically because they're on Supabase get the worst Supabase performance, silently. The seq-scan fall-through doesn't error — query results are correct, just unboundedly slow at scale.

Realistic fix paths

PostgREST's lack of column-wrapping in filter syntax is structural — there is no `.eq(eql_v2.hmac_256(col), value)` form in PostgREST. So the fix isn't a small code change; it requires choosing one of:

1. PostgREST RPC wrapper — define PL/pgSQL functions `eql_v2_eq`, `eql_v2_ilike`, `eql_v2_order_by` etc. that internally execute the wrapped form, and route `encryptedSupabase`'s `.eq()` / `.ilike()` / `.order()` through `.rpc()` instead of `.from().select().eq(...)`. Loses some of the SDK's chainable ergonomics but gains index engagement. Probably the most realistic option.

2. Document the limitation prominently and recommend customers needing fast equality / pattern-match queries connect via direct Postgres (or Drizzle, modulo its own gaps tracked separately) rather than the Supabase JS SDK. Honest but a regression on the integration's value proposition.

3. Upstream / fork PostgREST to support computed-column filters in the filter syntax. Large lift; benefits flow to non-CipherStash users too. Probably worth raising with the PostgREST team regardless.

4. Generated views — for each encrypted column `email`, create a view `users_eql` exposing `hmac_256_email = eql_v2.hmac_256(email)` etc. as plain columns. `encryptedSupabase` rewrites `.from('users').eq('email', …)` to `.from('users_eql').eq('hmac_256_email', …)`. Loses RLS interaction quality and is a lot of orchestration to wire up.

Option 1 is probably the right starting point. Worth a design doc before committing.

Verification on a real fixture

The encrypt-query-language repo has `tests/sqlx/fixtures/bench_data.sql` (10K-row encrypted fixture) and `bench_setup.sql` (creates the canonical Supabase functional indexes). To reproduce the gap concretely:

1. Apply `drop_operator_classes.sql` to simulate Supabase mode.
2. Run any `encryptedSupabase` `.eq()` query against the fixture.
3. `EXPLAIN` shows seq scan; cost grows linearly with row count.

Related

• skills: add CREATE INDEX guidance and wrapped query form for encrypted columns #419 — agent-skills docs gap (skills don't currently surface the wrapped-form requirement, so even agents helping users won't catch this).
• docs: add CREATE INDEX guide for encrypted columns (self-hosted vs Supabase paths differ) docs#4 — public docs gap (same shape, docs site).
• ci: add bench-supabase job to validate the supabase-variant install path encrypt-query-language#187 — proposed Supabase bench CI to keep the no-opclass path under perf surveillance.
• perf: register cross-type btree/hash operators with eql_v2 opfamilies encrypt-query-language#186 — recently fixed bare-jsonb equality on self-hosted via cross-type opfamily registration; that path is excluded from the Supabase build by design, so it doesn't help here.