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

[coderdan]

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

Page	What it says about indexes	Gap
supabase.mdx	Mentions CREATE OPERATOR FAMILY is omitted as a technical setup note.	Doesn't connect this to "here's how to create indexes that actually work."
searchable-encryption.mdx:134	One Callout: "If your PostgreSQL database does not support EQL Operator families, use the eql_v2.ore_block_u64_8_256() function for ORDER BY."	Single sentence, ORDER BY only — doesn't cover equality, pattern match, or the general principle.
eql-guide.mdx:112	"EQL leverages PostgreSQL's native indexing capabilities ... B-tree for exact/range, GIN for pattern matching."	Abstract — never shows the actual CREATE INDEX statements.

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:

Query type	Self-hosted (full EQL)	Supabase
Equality	USING btree (col) (uses opclass) or USING hash (eql_v2.hmac_256(col))	USING hash (eql_v2.hmac_256(col)) only
Range / ORDER BY	USING btree (col)	None today — being addressed by upcoming OPE-index work
Pattern match	USING gin (eql_v2.bloom_filter(col))	Same
JSONB containment	USING gin (eql_v2.ste_vec(col))	Same

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.

[coderdan]

Companion issue filed on the agent-skills surface in stack: cipherstash/stack#419. Worth landing together so docs and agent skills stay aligned.

[calvinbrewer]

Addressed. Created content/stack/cipherstash/encryption/indexes.mdx with the deployment matrix (self-hosted vs Supabase), per-index-type sections with correct CREATE INDEX SQL for both deployments, and a prominent Supabase callout explaining the functional-index query-form requirement (right vs wrong SQL side-by-side).

Cross-link callouts added to cipherstash/encryption/searchable-encryption.mdx and cipherstash/supabase.mdx so users land on the indexes page from where they're most likely to hit the sequential-scan trap.

Wired into encryption/meta.json under ---Concepts--- after searchable-encryption. The new queries.mdx page (#5) cross-links per query type into the matching indexes section.

One thing to verify before publish: the page references eql_v2.hmac_256 as the functional-index form. The EQL guide uses eql_v2.encrypted_get_hmac_256 for extraction. Need a quick sanity check against live EQL source to confirm the right name.

Pending review on a working branch — not yet pushed.

[coderdan]

The current page (/docs/stack/cipherstash/encryption/indexes) has some errors which need to be addressed before launch:

• Operator class examples should be removed as they will no longer be supported (they aren't correct in these docs anyway)
• Supabase (environments without Op-class support) can support range queries via OPE (reopening this issue so we can address as part of perf: flip eql_v2_encrypted infix operator implementations to inlinable SQL (#193) encrypt-query-language#196 and related
• Best practices mentions proper use of indexes but doesn't link to anything (see /docs/stack/cipherstash/encryption/searchable-encryption#best-practices)