Skill v1.0.1
currentAutomated scan100/1003 files
version: "1.0.1" name: stash-supabase description: Integrate CipherStash encryption with Supabase using @cipherstash/stack/supabase. Covers the encryptedSupabase wrapper, transparent encryption/decryption on insert/update/select, encrypted query filters (eq, like, ilike, gt/gte/lt/lte, in, or, match), identity-aware encryption, and the complete query builder API. Use when adding encryption to a Supabase project, querying encrypted columns, or building secure Supabase applications.
CipherStash Stack - Supabase Integration
Guide for integrating CipherStash field-level encryption with Supabase using the encryptedSupabase wrapper. The wrapper provides transparent encryption on mutations and decryption on selects, with full support for querying encrypted columns.
When to Use This Skill
- Adding field-level encryption to a Supabase project
- Querying encrypted data with Supabase's query builder (eq, like, gt, in, or, etc.)
- Inserting, updating, or upserting encrypted data
- Using identity-aware encryption (lock contexts) with Supabase
- Building applications where sensitive columns need encryption at rest and in transit
Installation
npm install @cipherstash/stack @supabase/supabase-js
Database Schema
Encrypted columns must be stored as JSONB in your Supabase database:
CREATE TABLE users (id SERIAL PRIMARY KEY,email jsonb NOT NULL, -- encrypted columnname jsonb NOT NULL, -- encrypted columnage jsonb, -- encrypted column (numeric)role VARCHAR(50), -- regular column (not encrypted)created_at TIMESTAMPTZ DEFAULT NOW());
For searchable encryption (equality, range, text search), install the EQL extension:
CREATE EXTENSION IF NOT EXISTS eql_v2;
Setup
1. Define Encrypted Schema
import { encryptedTable, encryptedColumn } from "@cipherstash/stack/schema"const users = encryptedTable("users", {email: encryptedColumn("email").equality() // eq, neq, in.freeTextSearch(), // like, ilikename: encryptedColumn("name").equality().freeTextSearch(),age: encryptedColumn("age").dataType("number").equality().orderAndRange(), // gt, gte, lt, lte})
2. Initialize Clients
import { createClient } from "@supabase/supabase-js"import { Encryption } from "@cipherstash/stack"import { encryptedSupabase } from "@cipherstash/stack/supabase"const supabase = createClient(process.env.SUPABASE_URL!,process.env.SUPABASE_ANON_KEY!,)const encryptionClient = await Encryption({ schemas: [users] })const eSupabase = encryptedSupabase({encryptionClient,supabaseClient: supabase,})
3. Use the Wrapper
All queries go through eSupabase.from(tableName, schema):
const { data, error } = await eSupabase.from("users", users).select("id, email, name").eq("email", "alice@example.com")
Insert (Encrypted Automatically)
// Single insertconst { data, error } = await eSupabase.from("users", users).insert({email: "alice@example.com", // encrypted automaticallyname: "Alice Smith", // encrypted automaticallyage: 30, // encrypted automaticallyrole: "admin", // not in schema, passed through}).select("id")// Bulk insertconst { data, error } = await eSupabase.from("users", users).insert([{ email: "alice@example.com", name: "Alice", age: 30, role: "admin" },{ email: "bob@example.com", name: "Bob", age: 25, role: "user" },]).select("id")
Update (Encrypted Automatically)
const { data, error } = await eSupabase.from("users", users).update({ name: "Alice Johnson" }) // encrypted automatically.eq("id", 1).select("id, name")
Upsert
const { data, error } = await eSupabase.from("users", users).upsert({ id: 1, email: "alice@example.com", name: "Alice", role: "admin" },{ onConflict: "id" },).select("id, email, name")
Select (Decrypted Automatically)
// List query - returns decrypted arrayconst { data, error } = await eSupabase.from("users", users).select("id, email, name, role")// data: [{ id: 1, email: "alice@example.com", name: "Alice Smith", role: "admin" }]// Single resultconst { data, error } = await eSupabase.from("users", users).select("id, email, name").eq("id", 1).single()// data: { id: 1, email: "alice@example.com", name: "Alice Smith" }// Maybe single (returns null if no match)const { data, error } = await eSupabase.from("users", users).select("id, email").eq("email", "nobody@example.com").maybeSingle()// data: null
Important: You must list columns explicitly in select() — using select('*') will throw an error. The wrapper automatically adds ::jsonb casts to encrypted columns so PostgreSQL parses them correctly.
select() also accepts an optional second parameter: select(columns, { head?: boolean, count?: 'exact' | 'planned' | 'estimated' }).
Query Filters
All filter values for encrypted columns are automatically encrypted before the query executes. Multiple filters are batch-encrypted in a single ZeroKMS call for efficiency.
Equality Filters
// Exact match (requires .equality() on column).eq("email", "alice@example.com")// Not equal.neq("email", "alice@example.com")// IN array (requires .equality()).in("email", ["alice@example.com", "bob@example.com"])// NULL check (no encryption needed).is("email", null)
Text Search Filters
// LIKE - case sensitive (requires .freeTextSearch()).like("name", "%alice%")// ILIKE - case insensitive (requires .freeTextSearch()).ilike("name", "%alice%")
Range/Comparison Filters
// Greater than (requires .orderAndRange()).gt("age", 21)// Greater than or equal.gte("age", 18)// Less than.lt("age", 65)// Less than or equal.lte("age", 100)
Match (Multi-Column Equality)
.match({ email: "alice@example.com", name: "Alice" })
OR Conditions
// String format.or("email.eq.alice@example.com,email.eq.bob@example.com")// Structured format (more type-safe).or([{ column: "email", op: "eq", value: "alice@example.com" },{ column: "email", op: "eq", value: "bob@example.com" },])
Both forms encrypt values for encrypted columns automatically.
NOT Filter
.not("email", "eq", "alice@example.com")
Raw Filter
.filter("email", "eq", "alice@example.com")
Delete
const { data, error } = await eSupabase.from("users", users).delete().eq("id", 1)
Transforms
These are passed through to Supabase directly:
.order("name", { ascending: true }).limit(10).range(0, 9).csv().abortSignal(signal).throwOnError().returns<U>()
Ordering by Encrypted Columns
`ORDER BY` on encrypted columns is not currently supported on databases without operator family support (including Supabase).
Without operator families installed in PostgreSQL, the database cannot sort on eql_v2_encrypted columns. This affects all clients — the Supabase JS SDK, Drizzle, raw SQL, and any other ORM.
Workaround: Sort application-side after decrypting the results.
Operator family support is currently being developed in collaboration with the Supabase and CipherStash teams and will be available in a future release.
.order() on non-encrypted columns works normally.
Identity-Aware Encryption
Chain .withLockContext() to tie encryption to a specific user's JWT:
import { LockContext } from "@cipherstash/stack/identity"const lc = new LockContext()const identified = await lc.identify(userJwt)if (identified.failure) throw new Error(identified.failure.message)const lockContext = identified.dataconst { data, error } = await eSupabase.from("users", users).insert({ email: "alice@example.com", name: "Alice" }).withLockContext(lockContext).select("id")
Audit Logging
Chain .audit() to attach metadata for ZeroKMS audit logging:
const { data, error } = await eSupabase.from("users", users).select("id, email, name").eq("email", "alice@example.com").audit({ metadata: { action: "user-lookup", requestId: "abc-123" } })
Complete Example
import { createClient } from "@supabase/supabase-js"import { Encryption } from "@cipherstash/stack"import { encryptedSupabase } from "@cipherstash/stack/supabase"import { encryptedTable, encryptedColumn } from "@cipherstash/stack/schema"// Schemaconst users = encryptedTable("users", {email: encryptedColumn("email").equality().freeTextSearch(),name: encryptedColumn("name").equality().freeTextSearch(),age: encryptedColumn("age").dataType("number").equality().orderAndRange(),})// Clientsconst supabase = createClient(process.env.SUPABASE_URL!, process.env.SUPABASE_ANON_KEY!)const encryptionClient = await Encryption({ schemas: [users] })const eSupabase = encryptedSupabase({ encryptionClient, supabaseClient: supabase })// Insertawait eSupabase.from("users", users).insert([{ email: "alice@example.com", name: "Alice", age: 30 },{ email: "bob@example.com", name: "Bob", age: 25 },])// Query with multiple filtersconst { data } = await eSupabase.from("users", users).select("id, email, name, age").gte("age", 18).lte("age", 35).ilike("name", "%ali%")// data is fully decrypted:// [{ id: 1, email: "alice@example.com", name: "Alice", age: 30 }]
Response Type
type EncryptedSupabaseResponse<T> = {data: T | null // Decrypted rowserror: EncryptedSupabaseError | nullcount: number | nullstatus: numberstatusText: string}
Errors can come from Supabase (API errors) or from encryption operations. Check error.encryptionError for encryption-specific failures.
The full EncryptedSupabaseError type:
type EncryptedSupabaseError = {message: stringdetails?: string // Supabase error detailshint?: string // Supabase error hintcode?: string // Supabase/PostgreSQL error codeencryptionError?: EncryptionError // CipherStash encryption-specific error}
Filter to Index Mapping
| Filter Method | Required Index | Query Type | |
|---|---|---|---|
eq, neq, in | .equality() | 'equality' | |
like, ilike | .freeTextSearch() | 'freeTextSearch' | |
gt, gte, lt, lte | .orderAndRange() | 'orderAndRange' | |
is | None | No encryption (NULL/boolean check) |
Exported Types
@cipherstash/stack/supabase also exports the following types:
EncryptedSupabaseConfigEncryptedSupabaseInstanceEncryptedQueryBuilderPendingOrConditionSupabaseClientLike
Migrating an Existing Column to Encrypted
The hard case: a Supabase table that already exists with live data in a plaintext column you want to encrypt. You can't just change the column type — that would drop the data.
CipherStash splits this into two named steps with a hard production-deploy gate between them: an encryption rollout (schema-add + dual-write code) and an encryption cutover (backfill + rename + drop). The stash-encryption skill is the canonical reference for the lifecycle; this section walks the Supabase-specific shape.
Using CipherStash Proxy? If you query encrypted data through CipherStash Proxy instead of the SDK, also runstash db pushafter schema-add and again before cutover to register the encrypted column shape with EQL.
Runner note.stash initaddsstashto the project as a dev dependency, sostash <command>runs through whichever package manager the project uses (Bun, pnpm, Yarn, or npm) — examples below show this bare form. Before init has run, prefix with your package manager's one-shot runner:bunx,pnpm dlx,yarn dlx, ornpx. The CLI's behaviour is identical across all of them.
Where am I? Runstash statusfirst (substitute the runner per the note above). It shows you which tables/columns are mid-rollout, which are post-deploy, and what the next move is. Re-run after every transition.
Starting state
You have:
-- supabase/migrations/<timestamp>_initial.sql (already applied)CREATE TABLE users (id uuid PRIMARY KEY DEFAULT gen_random_uuid(),email text NOT NULL, -- plaintext, populated, NOT NULLcreated_at timestamptz DEFAULT now());
…and an await supabase.from('users').insert({ email }) somewhere in your app code.
Step 1 — Encryption rollout (one PR, one deploy)
Everything below lands in one PR. The deploy of that PR is the gate.
Schema-add: declare the encrypted twin
Generate a Supabase migration:
supabase migration new add_users_email_encrypted
Edit the generated file to add an email_encrypted column alongside email. The encrypted column must be nullable at creation — never NOT NULL, because rows that already exist will have NULL in this column until backfill catches them.
-- supabase/migrations/<timestamp>_add_users_email_encrypted.sqlALTER TABLE usersADD COLUMN email_encrypted eql_v2_encrypted; -- nullable
Apply with supabase db reset locally or supabase migration up against the remote project.
Update the encryption schema to declare the new encrypted column:
// src/encryption/schema.tsimport { encryptedTable, encryptedColumn } from '@cipherstash/stack/schema'export const users = encryptedTable('users', {email_encrypted: encryptedColumn('email_encrypted').freeTextSearch().equality(),})// src/encryption/index.tsimport { Encryption } from '@cipherstash/stack'import { users } from './schema'export const encryptionClient = await Encryption({ schemas: [users] })
Using CipherStash Proxy? Register the new encryption config with EQL:```bashstash db push```If this is the project's first encrypted column,db pushwrites directly to the active EQL config. If an active config already exists, it writes the new config aspending— that's expected. Cutover (later) will promote it.SDK users: Skip this step. Your encryption config lives in app code.
Dual-writing: write to both columns from app code
Find every code path that writes to users.email and update it to encrypt and also write to email_encrypted. The cleanest pattern is to keep the raw supabase client for the plaintext write and use the encryptedSupabase wrapper for the encrypted write — wrapped in a single function so callers can't forget one half:
// src/db/users.tsimport { supabase, encrypted } from './clients'import { users } from '../encryption/schema'export async function insertUser(email: string) {// The encryptedSupabase wrapper handles the encryption call for you;// the plaintext write is a separate `supabase` call so the rollout// does not change read behaviour for `email` yet.const ciphertext = await encrypted.encryptValue(email, {table: users,column: 'email_encrypted',})if (ciphertext.failure) throw new Error(ciphertext.failure.message)return supabase.from('users').insert({email, // plaintext — keep writingemail_encrypted: ciphertext.data, // encrypted twin — new})}
Same shape for UPDATE: every site that updates email must also re-encrypt and update email_encrypted in the same statement.
The dual-write rule. Every persistence path that mutates this row writes both columns, in the same transaction, on every code branch. Insert sites, update sites, upserts, ON CONFLICT clauses, seeders, fixtures, edge functions, RPC functions, admin actions, background jobs, third-party webhooks — all of them. A single missed branch means rows inserted in production after deploy land in plaintext only, and backfill won't catch them. Grep for every site that touches users.email before declaring this step done.
After this phase, existing rows still have email_encrypted = NULL. Reads still come from email. Nothing has broken.
⛔ Deploy gate
Stop. Ship this PR to production. The deployed environment must be running the dual-write code before any cutover-step work is safe.
When the deploy is live:
stash status # verify the rollout is recordedstash plan # detects dual-writes are live; drafts the cutover plan
stash impl will refuse to run a cutover-step plan if cs_migrations has no dual_writing event for users.email. That refusal is the safety net for cases where someone runs cutover work locally before the code is actually live.
Step 2 — Encryption cutover
Once dual-writes are live in production and cs_migrations records dual_writing:
Backfill: encrypt the historical rows
stash encrypt backfill --table users --column email# (Interactive: answer 'yes' to the dual-write confirmation prompt.)# (CI: pass --confirm-dual-writes-deployed instead.)
Resumable, idempotent, chunked. The CLI walks the table in keyset-pagination order, encrypts each chunk via the encryption client, and writes the ciphertext into email_encrypted inside transactions that also checkpoint to cs_migrations. SIGINT-safe.
If something goes wrong (e.g. you discover the dual-write code wasn't actually live when backfill ran), re-run with --force to re-encrypt every row regardless of current state.
Cutover: rename swap and activate
First, update the encryption schema to the post-cutover shape — the encrypted column will live under the original column name:
// src/encryption/schema.ts (post-cutover)export const users = encryptedTable('users', {email: encryptedColumn('email').freeTextSearch().equality(),})
Known gap (SDK-only users):stash encrypt cutovercurrently requires a pending EQL configuration, which is set bystash db push. If you're using the SDK without Proxy, you'll hit a "No pending EQL configuration" error from cutover. Workaround: runstash db pushonce beforestash encrypt cutover. This will be decoupled in a future release — see issue #447.Using CipherStash Proxy? Re-push the encryption config so EQL has a pending row that points at_encryptedsuffix):```bashstash db push# → writes the new config aspending. Active config (still pointing at#email_encrypted) keeps serving while we complete the cutover.```
Now run the cutover:
stash encrypt cutover --table users --column email
Inside one transaction it: (1) renames email → email_plaintext and email_encrypted → email, (2) promotes the pending EQL config to active (and the prior active to inactive), (3) records a cut_over event in cs_migrations.
App code that does select('email') now returns ciphertext that must be decrypted via the encryptedSupabase wrapper. This is the moment that breaks read paths if they aren't going through the wrapper.
Update read paths to use encryptedSupabase:
// Beforeconst { data } = await supabase.from('users').select('email').eq('id', id).single()// After — encryptedSupabase decrypts transparentlyconst { data } = await encrypted.from('users').select('email').eq('id', id).single()
For queries that filter on email, the encryptedSupabase wrapper handles the encrypted operators internally — the call site is the same shape as before (.eq(), .like(), .ilike(), .gte(), etc.), but the values are encrypted before reaching the database. See ## Query Filters above.
Drop: remove the plaintext column
Once read paths are routing through encryptedSupabase and you're confident reads are decrypting correctly:
stash encrypt drop --table users --column email
The CLI emits a Supabase migration file with ALTER TABLE users DROP COLUMN email_plaintext;. Review and apply with supabase migration up (or supabase db reset locally). Then remove the dual-write code from app paths — email_plaintext is gone; only email (encrypted) is written now via encryptedSupabase.
Inspecting progress at any time
stash status # quest log: where each rollout is, what to do nextstash encrypt status # raw per-column phase, EQL state, backfill progressstash encrypt plan # diffs your migrations.json intent vs observed state
All three are read-only.