v3: no safe patch/minor upgrade path — uninstall is destructive for domain columns; ship versioned upgrade scripts

[coderdan]

Problem

There is no supported way to move an installed EQL v3 database from one release to the next. The only documented path is uninstall + reinstall, and for v3 that is data-destructive: the column types are DOMAINs living in the eql_v3 schema, so the uninstaller's schema drop cascades through user tables' encrypted columns. (v2 deliberately avoided this by placing eql_v2_encrypted in public so it survives uninstall — v3 lost that property by placement. Even v2's path destroys user functional indexes on every reinstall: 44 s to rebuild at 1M rows, hours at 10M.)

This is not hypothetical: applying the #353/#354 fixes to a populated benchmark database required hand-writing a surgical patch (9 statements) because reinstall would have destroyed 6 tables × 1M rows of encrypted data. Real consumers can't do that.

The object taxonomy that should drive the design

• Data-bearing (domains): user columns depend on them → must never be dropped in patch/minor; constraint changes go through ALTER DOMAIN DROP/ADD CONSTRAINT (non-destructive — exactly what fix(v3): inline the jsonb_entry domain CHECK; plpgsql jsonb_query validator (+19% field_eq regression) #358 needed).
• Index-bearing (opclasses; any function referenced by functional indexes): CREATE OR REPLACE preserves dependent indexes only when semantics are unchanged — a comparator whose ordering changes silently corrupts existing indexes, so releases must know when a change requires REINDEX and say so.
• Pure (wrappers, extractors, blockers, aggregates): freely CREATE OR REPLACE-able.

Both #353 and #354 were category 2/3 + one ALTER DOMAIN — the common patch-release case, upgradeable with a small script. That script should be a release artifact, not something consumers improvise.

Proposal

1. Before 3.0 GA (cheap):
   • Uninstaller guard: refuse (or loudly warn) when any user table has an eql_v3.*-typed column — one pg_depend query.
   • Document the constraint (docs/upgrading/): domains are frozen within a major version; never run the uninstaller against a database holding encrypted columns.
2. Versioned upgrade scripts as release artifacts: cipherstash-encrypt-upgrade-<from>-to-<to>.sql, opening with a guard on eql_v3.version() and closing by bumping it. The codegen architecture makes this cheap: the build can diff the previous release's bundle object-by-object and auto-emit the script (CREATE OR REPLACE for changed functions, ALTER DOMAIN constraint swaps, additive CREATEs), and refuse to auto-generate on changes it can't do safely (domain shape, opclass) — forcing a hand-written migration with an explicit REINDEX advisory.
3. CI equivalence gate: upgrade path ≡ fresh install. Install N−1 + upgrade script into one database, fresh-install N into another, diff the catalogs (function bodies, languages, proconfig/search_path pins, constraint defs, comments). This catches real bugs: validating the v3: opclass-path helper functions are LANGUAGE sql — cannot inline, regresses ORE ordered scans 43% vs v2 (validated fix: plpgsql) #353/v3: jsonb_entry domain CHECK calls a non-inlinable SQL function (+19% on field_eq queries) — and the pattern is systemic #354 surgical patch this way surfaced that the pin pass leaves the opclass helpers unpinned but pins the new plpgsql jsonb_query validator — hand-written upgrade SQL that missed the SET search_path would silently diverge from a fresh install.
4. Longer term: spike Trusted Language Extension (pg_tle) packaging where platforms allow (RDS, Supabase) — ALTER EXTENSION eql UPDATE TO '<v>' is Postgres's native answer, with version bookkeeping and pg_dump hygiene built in; the plain-SQL bundle remains the portable fallback. Relevant given the Supabase launch track.

Evidence

The surgical patch + fresh-install equivalence verification that motivated this: cipherstash/benches#23 (post-fix re-run commit). Sibling issues from the same analysis: #353, #354, #355, #356.