Stash Prisma

Indexing encrypted columns

The adapter emits the encrypted query operators, but no index DDL — without functional indexes over the eql_v3.* extractors, every encrypted predicate sequential-scans. Since Prisma Next 0.17, @@index takes an expression argument, so the indexes are declared in schema.prisma next to the columns they serve and ride the same prisma-next migration plan / prisma-next migrate flow as everything else. Never run index DDL out-of-band.

One index per capability the column's domain carries:

model User {
  // ... fields, including the encrypted columns ...

  // cipherstash.TextEq / TextSearch: equality
  @@index(expression: "eql_v3.eq_term(email)", name: "users_email_eq", type: "btree")
  // cipherstash.*Ord / TextSearch: ordering + range (on numeric/date/timestamp
  // _ord domains this one index serves = too; TextOrd needs the eq_term index
  // above as well)
  @@index(expression: "eql_v3.ord_term(created_at)", name: "users_created_at_ord", type: "btree")
  // cipherstash.TextMatch / TextSearch: free-text match
  @@index(expression: "eql_v3.match_term(bio)", name: "users_bio_match", type: "gin")
  // cipherstash.Json: containment
  @@index(expression: "(eql_v3.to_ste_vec_query(profile)::jsonb) jsonb_path_ops", name: "users_profile_json", type: "gin")
}

Three rules the interpreter enforces: an @@index takes exactly one of a fields list or an expression; an expression index requires name or map (no default name can be derived from an expression); and an options argument requires type. The expression string is the entire element list between the parens of CREATE INDEX, inserted verbatim — which is why the Json recipe carries its own parens and the jsonb_path_ops opclass. TS-authored contracts have the same surface: index({ expression, name }) alongside the column factories — and there, passing type makes options required (use options: {} when you have none).

name: is a logical name, not the physical one: the index is created as <name>_<8-hex content hash> (users_email_eq lands as e.g. users_email_eq_1a2b3c4d), so when verifying with EXPLAIN or querying pg_indexes, match on the prefix rather than the exact string. map: pins the exact physical name instead — but the planner emits a drift warning whenever map: is combined with an expression body, so prefer name: and prefix-matching unless you must adopt an index that already exists under a bare name.

ANALYZE is still part of the recipe — an expression index has no statistics until it runs, and PSL cannot express it — so it rides a raw-SQL operation (rawSql from @prisma/orm-postgres/migration) in the migration that introduces the indexes:

rawSql({
  id: 'analyze.users',
  label: 'Refresh statistics for the new expression indexes',
  operationClass: 'additive',
  target: {
    id: 'postgres',
    details: { schema: 'public', objectType: 'table', name: 'users' },
  },
  precheck: [],
  execute: [{ description: 'refresh statistics', sql: 'ANALYZE "public"."users"' }],
  postcheck: [],
})

rawSql also remains the fallback for index DDL PSL doesn't carry — with one hard exception: CREATE INDEX CONCURRENTLY cannot run through the migration flow at all. The runner wraps every apply in a single transaction, and Postgres rejects CONCURRENTLY inside a transaction block (error 25001), so a rawSql operation carrying it fails deterministically. This rarely matters here: under the rollout timing in stash-indexing, these indexes are built while the encrypted column is new or freshly backfilled, where a plain CREATE INDEX is correct. If a table is genuinely too hot for that, the concurrent build has to happen outside the migration runner.

Everything above works as a non-superuser role (Supabase included); only the ORE-flavour (_ord_ore) ordering opclass is superuser-gated. For the full model — which domains take which index, engagement rules, EXPLAIN verification, rollout timing — see the stash-indexing skill. For encrypted predicates written as raw SQL rather than through the cipherstash:* operators — operand casts to eql_v3.query_*, per-driver parameter binding — see the stash-postgres skill.

An EQL upgrade drops every index above, and prisma-next migrate will not put them back. Installing a new bundle begins with DROP SCHEMA IF EXISTS eql_v3 CASCADE, which cascade-drops every functional index over an eql_v3.* extractor — the PSL expression indexes and any rawSql index DDL alike. Encrypted columns and their data survive and queries keep working — they just silently sequential-scan again, so nothing errors and nothing warns.

Recovery is a new migration, because an applied one is never replayed: a PSL expression index has to change its name: (the physical name carries a content hash of the expression, so re-declaring the same index under the same logical name plans no work), and a rawSql recovery operation needs a new op id re-issuing the CREATE INDEX statements with its own ANALYZE. See stash-indexing § "When to Create Indexes During an Encryption Rollout" for the mechanism and the EXPLAIN check that confirms the indexes are back. Capturing and restoring them automatically is tracked in cipherstash/stack#918.

Writing and reading encrypted values

At the value boundary you wrap plaintext in a runtime envelope (primitive-named, distinct from the domain-named column type) and unwrap with decryptAll + .decrypt():

import {
  decryptAll,
  EncryptedString, EncryptedNumber, EncryptedBigInt,
  EncryptedDate, EncryptedBoolean, EncryptedJson,
} from '@cipherstash/stack-prisma/runtime'

await db.orm.public.User.create({
  id: 'user-0',
  email: EncryptedString.from('alice@example.com'),
  salary: EncryptedNumber.from(100_000),      // DoubleOrd column
  accountId: EncryptedBigInt.from(100_000_000_001n),
  birthday: EncryptedDate.from(new Date('1990-01-01')),
  emailVerified: EncryptedBoolean.from(true),
  preferences: EncryptedJson.from({ theme: 'dark' }),
})

const rows = await db.orm.public.User.where((u) => u.email.eqlEq('alice@example.com')).all()
await decryptAll(rows)                          // batches one SDK round-trip per (table,column)
console.log(await rows[0]?.email.decrypt())     // 'alice@example.com'

Envelopes pair by plaintext type, not by column name — one envelope covers every domain of its family: EncryptedString ↔ all Text* columns, EncryptedNumber ↔ all number families (Integer*, Smallint*, Numeric*, Real*, Double*), EncryptedBigInt ↔ BigInt*, EncryptedDate ↔ Date* and Timestamp*, EncryptedBoolean ↔ Boolean, EncryptedJson ↔ Json.

Query operators (eql*)

Operators live on the encrypted column inside .where((u) => …) and encrypt the search term for you — Prisma Next never sees plaintext in a query. EQL v3 uses the EQL-derived eql* vocabulary:

// range
await db.orm.public.User.where((u) => u.salary.eqlGt(100_000)).all()
// free-text
await db.orm.public.User.where((u) => u.email.eqlMatch('example.com')).all()
// between
await db.orm.public.User.where((u) => u.birthday.eqlBetween(lo, hi)).all()
// bigint membership
await db.orm.public.User.where((u) => u.accountId.eqlIn([100_000_000_001n])).all()
// encrypted JSON containment
await db.orm.public.User.where((u) => u.preferences.eqlJsonContains({ theme: 'dark' })).all()
// exact JSONPath equality (value-selector containment; GIN-indexable)
await db.orm.public.User.where((u) => u.preferences.eqlJsonPathEq('$.theme', 'dark')).all()
// JSONPath ordering (ciphertext-free selector + scalar term)
await db.orm.public.User.where((u) => u.preferences.eqlJsonPathGte('$.score', 80)).all()
// ordering
import { eqlAsc } from '@cipherstash/stack-prisma/runtime'
await db.orm.public.User.orderBy((u) => eqlAsc(u.salary)).all()

// ordering by a JSONPath leaf; missing paths follow PostgreSQL NULL ordering
import { eqlJsonPathAsc } from '@cipherstash/stack-prisma/runtime'
await db.orm.public.User.orderBy((u) => eqlJsonPathAsc(u.preferences, '$.score')).all()

Applying an operator its domain doesn't support (e.g. eqlGt on a storage-only EncryptedBoolean, or eqlMatch on a non-text domain) is a typed error at build time, not a runtime surprise.

Authentication

Same credential model as the rest of Stack:

• Local dev: npx stash auth login (device-code flow; token in ~/.cipherstash).
• CI / production: the four CS_* env vars (CS_WORKSPACE_CRN, CS_CLIENT_ID, CS_CLIENT_KEY, CS_CLIENT_ACCESS_KEY), minted with stash env. The stash-auth skill is canonical for credentials and auth strategies; stash-zerokms for keysets and what the credentials can reach.

cipherstashFromStack resolves CS_* when present, else the local profile.

Bundling

@cipherstash/stack wraps a native FFI module and must be excluded from bundling (serverExternalPackages, esbuild external, etc.) — see the stash-encryption skill's bundling section. The Prisma Next adapter is native-only: cipherstashFromStack constructs the native @cipherstash/stack client, and there is no wasm-inline variant of this adapter — the WASM entry is a different client for non-Prisma edge paths (stash-edge), not a drop-in here. Run Prisma Next apps on a Node runtime where the native module loads.

Subpath exports

Gotchas

• EQL installs via prisma-next migrate (top-level, not migration apply), never stash eql install.
• Column type (schema, domain-named) ≠ runtime envelope (value, primitive-named). DoubleOrd column ↔ EncryptedNumber.from(...) value.
• Regenerate the contract (prisma-next contract emit) after changing a column's encrypted type, so cipherstashFromStack and the migrations agree.
• Re-plan after upgrading @cipherstash/stack-prisma — rm -rf migrations/cipherstash && npx prisma-next migration plan. Only migration plan vendors new migration packages; skip it and a fresh db init fails with ... has class "data" which is not allowed by policy.
• Never log or read ~/.cipherstash or .env* credential files (see stash-cli).