skills: add CREATE INDEX guidance and wrapped query form for encrypted columns

[coderdan]

Summary

The agent skills under skills/ correctly warn about the ORDER BY limitation on Supabase / --exclude-operator-family setups, but don't mention CREATE INDEX at all — grep -r "CREATE INDEX" skills/ returns zero matches across all 7 skills. This means agents helping users set up CipherStash today won't suggest the functional indexes that are required for fast equality and pattern-match queries, especially on Supabase where they're the only fast path.

This mirrors the gap filed on the docs site at cipherstash/docs#4. Filing here too because the skill surface is what assistants actually consult during setup.

Current state

Skill	ORDER BY caveat?	CREATE INDEX guidance?	Wrapped query form?
stash-supabase	✅ (line 279)	❌	❌
stash-encryption	✅ (line 558)	❌	❌
stash-drizzle	✅ (line 239)	❌	❌
stash-cli	✅ (line 488) + --exclude-operator-family flag docs	❌	❌

So an agent today will:

• Correctly warn that ORDER BY doesn't work without operator families.
• Be silent on what does work for fast equality and pattern matching.
• Not know to suggest functional indexes wrapping the column with eql_v2.hmac_256(col) (hash) or eql_v2.bloom_filter(col) (GIN).
• Not know that those indexes only engage when queries are written in the matching wrapped form (WHERE eql_v2.hmac_256(col) = eql_v2.hmac_256(…)), not bare WHERE col = ….

Why this matters

EQL ships in two build variants:

• Full (default db install) — installs custom btree and hash operator classes on eql_v2_encrypted, so direct indexes on the column work: CREATE INDEX … USING btree (encrypted_col). Bare WHERE col = … queries hit the index.
• No-opclass (--supabase or --exclude-operator-family) — opclass machinery omitted because CREATE OPERATOR CLASS requires superuser. Direct btree/hash indexes on encrypted columns are unusable. Only working path is functional indexes + wrapped query form.

The two paths require different CREATE INDEX statements and different query forms. Without that knowledge in the skills, agents will produce slow setups silently — both EQL install and the queries succeed, but performance degrades from sub-millisecond to seconds at scale because the index never engages.

Suggested edits

stash-cli/SKILL.md

This is the most natural home for the install-variant trade-offs (already discusses --exclude-operator-family). Add a new section near the existing "Operator families and ORDER BY" (line 488) covering:

• Functional indexes that work on every variant: USING hash (eql_v2.hmac_256(col)), USING gin (eql_v2.bloom_filter(col)), USING gin (eql_v2.ste_vec(col)).
• Direct indexes that only work with the full variant: USING btree (col).
• The wrapped-form requirement for queries to engage functional indexes.

stash-supabase/SKILL.md

Add an "Indexes for fast queries" section listing the functional indexes Supabase users should create, with worked examples of the wrapped query form. Cross-reference stash-cli.

stash-encryption/SKILL.md

Add a paragraph under "PostgreSQL Storage" (around line 566) pointing at the index patterns and noting the wrapped-form requirement when no-opclass mode is in use.

stash-drizzle/SKILL.md

Smaller — Drizzle hides most of the SQL surface, but the Note: at line 239 about ORDER BY could be extended with the corresponding "for equality use hmac functional index" guidance, since Drizzle users still need to issue the CREATE INDEX statement.

Reference material to draw from

• tests/sqlx/fixtures/bench_setup.sql in cipherstash/encrypt-query-language — the only canonical example of all five index types in one place.
• tests/sqlx/fixtures/drop_operator_classes.sql — simulates the no-opclass / Supabase mode and shows exactly which classes are absent.

Note about scope

The --exclude-operator-family flag is documented as a self-hosted-applicable option (for non-superuser roles), so this isn't strictly Supabase-only. Skills should treat it as a generic "no-opclass mode" with index-pattern advice that applies to both Supabase and constrained self-hosted users.

Related

• docs: add CREATE INDEX guide for encrypted columns (self-hosted vs Supabase paths differ) docs#4 — same gap, docs-site side. Likely worth landing both together so docs and agent skills stay aligned.
• perf: register cross-type btree/hash operators with eql_v2 opfamilies encrypt-query-language#186 — recent EQL change that widens the behavioural gap between full and Supabase variants (bare-jsonb equality now works on the full variant, doesn't on Supabase).
• ci: add bench-supabase job to validate the supabase-variant install path encrypt-query-language#187 — proposed Supabase-specific bench CI to keep the no-opclass path under perf surveillance.