Stash Postgres

Raw Postgres SQL Against Encrypted Columns (EQL v3)

An EQL v3 encrypted column is a Postgres domain over jsonb (public.eql_v3_text_search, public.eql_v3_bigint_ord, …). Reading and writing it from raw SQL is two rules:

1. Writing — bind the Encrypted payload your client produced as a JSON object parameter. How you do that differs per driver, and getting it wrong trips a domain CHECK with an unhelpful message.
2. Querying — never send a plaintext. Mint a query term with encryptQuery and cast it to the column's matching eql_v3.query_* domain. That cast is what selects the right operator overload: leave the operand as bare jsonb and you get a different overload, one that expects a full storage envelope.

This covers the pg and postgres-js drivers with no ORM — plain Node services, Hono, edge functions. If you use Drizzle, Prisma Next, or the Supabase client, those integrations emit correct operands for you: see stash-drizzle, stash-prisma, stash-supabase instead.

Using CipherStash Proxy? None of this applies. This skill assumes the app connects to Postgres directly and encrypts client-side: Stack mints the payloads and the query terms, and your SQL carries them. Through CipherStash Proxy the split is the opposite — you write plaintext SQL and Proxy encrypts on write and decrypts on read, so there is no encryptQuery, no eql_v3.query_* cast, and no payload to bind.

Proxy's former schema lifecycle belongs to EQL v2 and is no longer installed or mutated by stash; legacy state remains visible through status diagnostics only. This skill is EQL v3, where a column's encryption config lives in its own domain and there is no configuration table to push. The stash CLI targets the direct-connection path this skill describes.

When to Use This Skill

• Writing INSERT / UPDATE / SELECT against an encrypted column by hand.
• A predicate returns zero rows, or errors with operator does not exist.
• A write fails with value for domain eql_v3_… violates check constraint.
• Choosing the right operator for a column's domain.
• Ordering, ranging, or searching inside an encrypted JSON document.

The Two Halves

Assume users.email is declared in the database as public.eql_v3_text_eq — a Postgres domain over jsonb. The domain is the authority: it is what the column actually is, what the CHECK constraint enforces, and what decides which operators the column admits.

Everything else is a mapping onto that domain:

• types.TextEq('email') — the schema factory from @cipherstash/stack/eql/v3 that declares the column. A TypeScript builder, not the column's type.
• TextEq / TextEqQuery — the wire shapes, exported as TypeScript types by @cipherstash/eql. These and the JSON Schemas are generated from the Rust eql-bindings crate, and the SQL bundle is built from the same commit, so the payload shape and the domain CHECK cannot drift apart. Each type's doc comment names the domain it maps to and the operators that domain admits.
• eql_v3.query_text_eq — the query domain, also a database type, that a search needle is cast to.

That TextEq / TextEqQuery pairing is precisely the split this section is about. The domain names below follow the column, so a public.eql_v3_text_search column would use eql_v3.query_text_search in exactly the same places.

// 1. Encrypt for storage — the payload includes the ciphertext (`c`).
const enc = await client.encrypt('alice@example.com', { table: users, column: users.email })
if (enc.failure) throw new Error(enc.failure.message)
await sql`INSERT INTO users (email) VALUES (${sql.json(enc.data)})`

// 2. Mint a search needle — CIPHERTEXT-FREE, terms only.
const term = await client.encryptQuery('alice@example.com', {
  table: users, column: users.email, queryType: 'equality',
})
if (term.failure) throw new Error(term.failure.message)
const rows = await sql`
  SELECT * FROM users WHERE email = ${sql.json(term.data)}::jsonb::eql_v3.query_text_eq`

Storage payloads and query terms are different shapes with different domains. A storage payload carries c (the ciphertext); a query term deliberately omits it and the eql_v3.query_* CHECKs require its absence. Binding a storage payload where a query term belongs fails the CHECK, and vice versa.

queryType is one of 'equality', 'freeTextSearch', 'orderAndRange', 'searchableJson'. Omit it only for single-index columns (types.TextEq); be explicit on multi-index domains like types.TextSearch.

Naming: Column Domain → Query Domain

Strip public., insert query_, move to the eql_v3 schema:

public.eql_v3_text_eq       →  eql_v3.query_text_eq
public.eql_v3_text_search   →  eql_v3.query_text_search
public.eql_v3_bigint_ord    →  eql_v3.query_bigint_ord
public.eql_v3_timestamp_ord →  eql_v3.query_timestamp_ord

One irregular case: types.Json builds public.eql_v3_json_search, but its query domain is eql_v3.query_json — not query_json_search.

<N> ranges over Integer, Smallint, Bigint, Numeric, Real, Double, Date, Timestamp. The storage-only domains carry no query terms by design — there is no query domain and nothing to search server-side.

For the same mapping written out one factory per row, with the predicates and index each one supports, see the capability matrix in the stash-encryption skill (### The types Namespace). That table is the canonical type→predicate→domain→index lookup; this one is the SQL-side view of it, expanded only far enough to build a cast.

The Predicate Matrix

Which operators each column domain accepts against its query domain. Anything not listed does not exist as an encrypted operator.

Confirm types against EQL before relying on them. This table — and every other domain/operator table in this skill — is a snapshot of a versioned surface that is defined elsewhere. Do not treat it as the last word. Consult EQL for the precise current types, in the order given under Where this surface is defined below; the first two checks need nothing but node_modules.

Note what is absent: there is no < on an _eq domain, and no @@ outside the match-capable text domains. Asking for one raises operator does not exist — which is the good failure. The bad failure is leaving the operand as bare jsonb (see Traps).

Every operator has a function twin, useful when an operator is awkward to emit: eql_v3.eq(col, term), eql_v3.matches(col, term), and the comparison functions. col = term and eql_v3.eq(col, term) are equivalent.

Where this surface is defined

Nothing in the matrix above is authored by the client library. The domains, operators, CHECKs, and extractor functions all come from the EQL SQL bundle that stash eql install applies — published as the @cipherstash/eql package, whose source lives in cipherstash/stack under packages/eql/.

A given stash release carries one resolved bundle, so the same CLI version always installs the same SQL. A database, though, is on whatever bundle was last applied to it, and different databases drift apart until each is upgraded — and the Prisma Next adapter never goes through the CLI at all, installing and upgrading the bundle pinned by @cipherstash/stack-prisma through its own migrations. Ask the database (3 below) rather than the client.

That makes every table in this skill a snapshot of a versioned surface. Go to EQL for the precise current types rather than trusting these tables alone — in this order:

1. The generated TypeScript types in @cipherstash/eql. Every per-domain type's doc comment names its domain and that domain's operators — the TextEqQuery type, for instance, is documented as the eql_v3.query_text_eq equality query operand admitting = and <>. They are generated from the same Rust eql-bindings commit as the SQL bundle, so the two cannot disagree.

2. The install SQL, shipped at @cipherstash/eql/dist/sql/cipherstash-encrypt.sql (under node_modules, wherever your package manager resolves it). Its CREATE OPERATOR statements are the last word on which overloads exist — each names its LEFTARG, RIGHTARG, and implementing FUNCTION.

3. The database itself, which is the runtime truth and the right check when a query is failing right now:

   SELECT eql_v3.version();   -- which bundle is actually installed

The stash CLI depends on @cipherstash/eql, so 1 and 2 are available in any project that has the CLI installed, with no database connection required.

If an operator is absent from all of these, that is an EQL question, not a client-library one — the operator set is defined by the SQL bundle in packages/eql/.

Binding Parameters: The Driver Rules

This differs between drivers. Both encrypted payloads and query terms are plain JS objects, and the two drivers disagree about how to put a JS object into a jsonb-backed domain.

postgres (postgres-js) — always sql.json(...)

Use sql.json(...) in both positions — it is the only form that works in both, so there is no reason to track which position you are in.

await sql`INSERT INTO users (email) VALUES (${sql.json(enc.data)})`
await sql`SELECT * FROM users
           WHERE email = ${sql.json(term.data)}::jsonb::eql_v3.query_text_eq`

pg (node-postgres) — pass the object

node-postgres serialises a JS object to JSON exactly once, so all three forms happen to work. Pass the object and let the driver do it:

await client.query('INSERT INTO users (email) VALUES ($1)', [enc.data])
await client.query(
  'SELECT * FROM users WHERE email = $1::eql_v3.query_text_eq', [term.data])

Do not pre-stringify even though pg tolerates it — it is the one habit that silently breaks if the project ever moves to postgres-js.

The double-encoding failure, precisely

${JSON.stringify(payload)}::jsonb on postgres-js produces:

value for domain eql_v3_text_search violates check constraint "eql_v3_text_search_check"

The message names neither JSON nor encoding, which is why this one costs an afternoon. What happened: the explicit ::jsonb makes postgres-js infer a jsonb parameter, so it JSON-encodes the value — which was already a JSON string. The result is a jsonb string scalar, not an object:

SELECT jsonb_typeof($1::jsonb)   -- 'string', not 'object'

Every EQL domain CHECK opens with jsonb_typeof(VALUE) = 'object', so it fails on the very first clause. Diagnose any CHECK-violation-on-write by running jsonb_typeof on the parameter; 'string' means double-encoded.

Query Recipes

Assume sql is a postgres-js tag; for pg use numbered placeholders as above.

The recipes below omit the Result guard for brevity — your code must not. encryptQuery returns { data } | { failure }, so reading .data without first checking .failure binds undefined into the query, which fails as a domain CHECK violation rather than as the encryption error it actually is. Every recipe should be read as though it were written:

const term = await client.encryptQuery(/* … */)
if (term.failure) throw new Error(term.failure.message)

Equality

const term = await client.encryptQuery(email, {
  table: users, column: users.email, queryType: 'equality',
})
await sql`SELECT * FROM users
           WHERE email = ${sql.json(term.data)}::jsonb::eql_v3.query_text_eq`

On a types.TextSearch column the cast is ::eql_v3.query_text_search — the query domain always matches the column's domain, not the query type.

Free-text match

// `bio` is a types.TextSearch column, so its query operand is
// eql_v3.query_text_search. A types.TextMatch column uses query_text_match.
const term = await client.encryptQuery('needle', {
  table: users, column: users.bio, queryType: 'freeTextSearch',
})
await sql`SELECT * FROM users
           WHERE bio @@ ${sql.json(term.data)}::jsonb::eql_v3.query_text_search`

Match is one-sided: a hit may be a false positive, a miss never is. Filter client-side after decryption if exactness matters — and never build a negated match (NOT (bio @@ …)), which would drop true rows. Needles must be at least 3 characters; shorter ones tokenize to nothing and are rejected.