`stash eql upgrade` silently deletes search indexes — save and restore them

[coderdan]

Background (new to CipherStash? start here)

CipherStash encrypts individual database columns. To search encrypted data quickly, Postgres needs special indexes built over helper functions from EQL — the SQL library we install into the customer's database as the eql_v3 schema. Without those indexes, every search reads the entire table.

The problem

Upgrading EQL (stash eql upgrade, or eql install --force) works by deleting the whole eql_v3 schema and reinstalling it (DROP SCHEMA IF EXISTS eql_v3 CASCADE). Because of the CASCADE, Postgres also deletes everything built on top of that schema — including every search index the customer created, plus any constraints or row-level-security policies that call EQL functions.

Nothing warns and nothing fails:

• Postgres raises no error — the functions come right back, so queries stay valid; they just stop using indexes. Performance degrades from "instant" to "scan every row", silently, scaling with table size.
• No migration tool recreates the indexes — Prisma, Drizzle, Supabase, and plain SQL runners all skip migrations they've already applied, and the index-creating migration was applied long ago.
• Our own detection (stash eql validate's "No functional index" finding) is Info-level and only runs if the user happens to invoke it with a reachable database.

Encrypted data is safe — the column types live in public, outside the dropped schema. Only searchability (and potentially RLS, which is worse) disappears.

Every customer with search indexes hits this on every EQL version bump.

Proposal

Teach stash eql upgrade (in EQLInstaller) to save and restore what the upgrade is about to destroy:

1. Before running the install SQL: ask Postgres for every index that depends on eql_v3 / eql_v3_internal (pg_depend + pg_indexes; pg_get_indexdef() returns each one's complete CREATE INDEX statement).
2. Run the install as today.
3. After: re-run the saved CREATE INDEX statements, then ANALYZE the affected tables.
4. If a recreate fails (e.g. the new EQL version renamed a function): fail loudly, printing the statements that need attention. A loud failure beats a silent deletion.
5. Constraints and RLS policies can't always be blindly recreated (their meaning may have changed) — at minimum, count them and show them before proceeding.

Bonus: the CLI runs outside any migration transaction, so large tables can be rebuilt with CREATE INDEX CONCURRENTLY — something the migration-runner path structurally cannot do (single wrapping transaction; Postgres error 25001).

Cheap interim step (shippable first): print a pre-drop warning with the count of dependent objects, and run the eql validate index check automatically after the upgrade, elevated to Warning.

Relationship to other work

• The real long-term fix is upstream in EQL: minor/patch releases usually change one or two functions and shouldn't drop the schema at all — CREATE OR REPLACE preserves dependent indexes. That's v3: no safe patch/minor upgrade path — uninstall is destructive for domain columns; ship versioned upgrade scripts encrypt-query-language#360. This issue makes upgrades safe until that lands, and stays as the safety net for major versions.
• EQL migration artefact redesign: GA follow-ups (installer digest pins, upgrade-window docs, re-baseline cadence) #762 task 2 covers documenting the upgrade window (docs only — this issue is the mechanism).
• stack-prisma: auto-derive EQL v3 functional indexes via onFieldEvent codec hook #896's reconciliation discussion covers the Prisma adapter noticing missing derived indexes — complementary, Prisma-only.