/stash-postgres

name: stash-postgres
description: Query EQL v3 encrypted columns from hand-written Postgres SQL over `pg` (node-postgres) or `postgres` (postgres-js) — no ORM. Covers the column-domain-to-query-domain operator matrix (which of `=`, `<>`, `<`, `<=`, `>`, `>=`, `@@`, `@>` each encrypted domain accepts), minting search needles with `encryptQuery`, the per-driver parameter-binding rules for encrypted payloads, and the double-encoding failure that trips the domain CHECK with a message naming neither JSON nor encoding. Use when writing INSERT/SELECT against an encrypted column without an ORM, when a predicate returns zero rows or raises "operator does not exist", or when a domain CHECK constraint rejects an encrypted value on write. Assumes a direct Postgres connection with client-side encryption — CipherStash Proxy encrypts on the wire and needs none of this.

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](https://github.com/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