Repository navigation
stack-prisma: auto-derive EQL v3 functional indexes via onFieldEvent codec hook #896
Description
Goal
An encrypted column declared in the contract should get its eql_v3.* functional indexes created by prisma-next migration plan automatically — no hand-written rawSql recipes (today's story, see #895 for the interim doc fix).
Why it's now possible (Prisma Next 0.17)
CodecControlHooks.onFieldEvent(event, ctx)(family-sql) fires per added/dropped/altered field, dispatched by the field'scodecId, and returnsOpFactoryCall[]inlined into the app-space migration'sops.json(ADR 213).- target-postgres exposes a
createIndexop factory whose elements are{columns} | {expression}— the expression form plustype/where/unique/optionsextras, rendered asCREATE INDEX ... USING <type> (<expression>). - Everything needed to derive the right indexes already exists in
packages/stack-prisma/src/v3/catalog.ts:V3DomainMetacarriescapabilitiesandindexesper domain.
Sketch
Implement onFieldEvent in packages/stack-prisma/src/migration/cipherstash-codec-v3.ts (its header currently documents the deliberate absence — that rationale was about v2-style search config, not index DDL):
'added': emit onecreateIndexper capability the domain carries —eql_v3.eq_term(col)btree (eq-capable text domains),eql_v3.ord_term(col)btree (ord),eql_v3.match_term(col)gin (match),(eql_v3.to_ste_vec_query(col)::jsonb) jsonb_path_opsgin (Json containment).'dropped': matchingdropIndexops.- Deterministic names (
<table>_<col>_eq/_ord/_match/_json) — expression indexes require explicit names, and stable names keep re-plans no-op.
Domain rules (from skills/stash-indexing): numeric/date _ord domains get no eq_term index (no overload exists; eq inlines to ord_term = ord_term); TextOrd/TextSearch need both; _ord_ore opclass is superuser-gated → must be opt-in or skipped (Supabase breaks otherwise).
Known hazards to resolve
- Introspection round-trip churn: Postgres normalizes stored index expressions (
pg_get_indexdef), and the IR treatsexpressionas opaque/never-parsed. If schema-verify compares authored vs introspected strings, every re-plan diffs dirty. Needs a live round-trip test before anything ships. - Codec-swap gap: per ADR 213, a change where only
codecIddiffers fires no'altered'event — a domain swap (e.g. TextEq → TextSearch) won't re-derive indexes. Handle or document. ANALYZE: part of the recipe (expression indexes have no statistics until it runs) — emit as a companion op.- Existing pins:
test/v3/migration-v3.test.tsand the example e2e assert v3 columns contribute zero extra migration ops; they flip to asserting the derived index set. - Update
skills/stash-prisma+skills/stash-indexingagain once this lands (auto vs manual story), with astashchangeset;@cipherstash/stack-prismagets a minor changeset.
Activity
coderdan commentedon Aug 19, 2026
Two hazards missing from the list above, surfaced while reviewing the interim skill fix (#895). Both share one root:
onFieldEventfires only on field add/drop/alter, so any index state that changes out-of-band is never reconciled.6. EQL reinstall/upgrade cascade-drop. The EQL install SQL begins with
DROP SCHEMA IF EXISTS eql_v3 CASCADE, sostash eql upgrade(oreql install --force) drops every derived expression index. After that: no field event fires, the migration that created the ops is already applied, and lenient verification never notices — the indexes are silently gone forever and queries fall back to sequential scans without erroring. Thestash-indexingskill's current recovery recipe ("add a new migration re-issuing the CREATE INDEX statements") doesn't map onto this flow either.7. Brownfield/pre-existing columns. Encrypted columns that exist before this feature lands produce no
'added'event, so they get no index ops at all — the manual path (and the fact thatCREATE INDEX CONCURRENTLYcan't run through the runner's transaction) persists for exactly the deployments most likely to need indexes on populated tables.Both point at the same fix shape: a reconciliation pass, not (only) an event hook — e.g. a verify step in
migration plandiffing the expected derived index set againstpg_indexes, or astash-side check that emits the repair ops.Two design facts verified against the 0.17 dist that make the transition story easier than the sketch assumes:
createIndexfactory passesindexNamethrough literally (CreateIndexCall.toOp) — none of theformatWireName<name>_<8hex>hashing the PSL@@indexpath applies. Derived ops can use the exact<table>_<col>_eq/_ord/_match/_jsonnames the old skill recipe taught.postcheck_pre_satisfied, control apply loop). Combined with the point above: databases carrying old-recipe indexes under those names get adopted for free — the derivedcreateIndexop plans, sees the index, and skips. Keeping the sketch's proposed names identical to the old recipe's is therefore load-bearing, not cosmetic.docs(skills): correct the 0.17 index guidance — hashed physical names…
docs(skills): teach the PSL expression-index form Prisma Next 0.17 ad…