docs: add CREATE INDEX guide for encrypted columns (self-hosted vs Supabase paths differ) #4

Summary

The new docs site doesn't currently cover how users should create PostgreSQL indexes on encrypted columns. CREATE INDEX appears zero times across content/ — grep-confirmed. This is a regression vs. the previous docs and a real user-facing gap because the correct index-creation pattern differs between self-hosted PostgreSQL and Supabase, and the wrong path silently degrades to sequential scans even when EQL is correctly installed.

Context

EQL ships in two build variants:

• Full (cipherstash-encrypt.sql) — installs custom btree and hash operator classes (encrypted_operator_class, encrypted_hash_operator_class) on the eql_v2_encrypted type. This lets users index encrypted columns directly: CREATE INDEX ... USING btree (encrypted_col).

• Supabase (cipherstash-encrypt-supabase.sql) — omits all CREATE OPERATOR CLASS / CREATE OPERATOR FAMILY statements because they require superuser. On Supabase, the only working index path is functional indexes that wrap the column with an EQL extraction function:

  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));

  Queries must then be written in the matching wrapped form:

  -- Hits the hash index above
  SELECT * FROM users
   WHERE eql_v2.hmac_256(email) = eql_v2.hmac_256('alice@example.com'::jsonb::eql_v2_encrypted);
  
  -- Bare equality on the column does NOT hit the functional index.
  -- On Supabase this falls back to a sequential scan.
  SELECT * FROM users WHERE email = 'alice@example.com'::jsonb::eql_v2_encrypted;

What's currently in the docs

There's nothing telling a user:

• Which functional indexes to create for which query type
• The query-form requirement (must use wrapped eql_v2.hmac_256(col) form on Supabase)
• That self-hosted users have a simpler CREATE INDEX ... USING btree (col) option that doesn't exist on Supabase
• The performance consequences of getting it wrong (silently slow queries)

Suggested page structure

A new page /stack/cipherstash/encryption/indexes (or similar), cross-linked from supabase.mdx and searchable-encryption.mdx. Core table:

Plus a Supabase-specific Callout with the query-form requirement — bare WHERE col = … doesn't engage functional indexes. Queries must wrap the column with the same extraction function used in the index.

Why this matters now

• A recent EQL change (perf: register cross-type btree/hash operators with eql_v2 opfamilies encrypt-query-language#186) makes bare-jsonb equality on encrypted columns work via direct btree indexes — but only on self-hosted, because the cross-type opfamily registration is in the same Supabase-excluded file. This widens the behavioural gap between the two deployments.
• The OPE-index work in flight will introduce a second Supabase-specific guidance area (range/ORDER BY) that needs the same treatment.

Reference material to draw from

• tests/sqlx/fixtures/bench_setup.sql in the EQL repo — currently the only canonical example of all five index types in one place.
• tests/sqlx/fixtures/drop_operator_classes.sql — the fixture that simulates the Supabase environment (drops the opclasses to match what the supabase build doesn't install).

Out of scope

• The OPE-index path for range queries on Supabase — separate work; once it lands, it'll need its own row in the table above.
• Migration / upgrade docs from the previous index-creation pages — likely a clean rewrite is faster than porting.