Data migration

Add nullable encrypted mirrors

Add one encrypted column beside each plaintext column:

ALTER TABLE patients
    ADD COLUMN name_encrypted          public.eql_v3_text_eq,
    ADD COLUMN date_of_birth_encrypted public.eql_v3_date_ord;

The new columns must be nullable because existing rows do not have ciphertext yet. The _encrypted suffix is the convention used by stash encrypt backfill; use --encrypted-column if your names differ.

Ship this schema change together with the dual-write code in the next step. Do not rename or remove the plaintext columns.

Dual-write every mutation

Update every insert, update, and upsert path to write the plaintext column and its encrypted mirror in the same statement:

import { db } from "./db"

export async function createPatient(input: {
  email: string
  name: string
  dateOfBirth: string
  plan: string
}) {
  return db.from("patients").insert({
    email: input.email,
    name: input.name,
    name_encrypted: input.name,
    date_of_birth: input.dateOfBirth,
    date_of_birth_encrypted: input.dateOfBirth,
    plan: input.plan,
  })
}

export async function updatePatientName(id: number, name: string) {
  return db
    .from("patients")
    .update({ name, name_encrypted: name })
    .eq("id", id)
}

The encrypted client encrypts the mirror values while passing the original values through unchanged. Keeping both values in one database statement prevents one half of the write from succeeding without the other.

Search the codebase for every writer before deploying. A missed importer, background job, conflict handler, or administrative path can create uncovered rows.

Deploy and verify dual-writes

Deploy the schema and application changes to the environment that owns the database. The backfill is not safe while dual-writes exist only locally or in CI.

After the deployment, create and update known rows through each important write path. Confirm that both the plaintext and encrypted columns are populated, then inspect the migration state:

npx stash encrypt status

The application is now in a safe steady state: new writes are mirrored, old rows remain plaintext-only, and reads still use the original columns.

Backfill historical rows

Run one backfill per plaintext column:

npx stash encrypt backfill --table patients --column name
npx stash encrypt backfill --table patients --column date_of_birth

The command pages by primary key, encrypts rows in chunks, and records checkpoints. It is resumable and idempotent. For a non-interactive job, acknowledge the deployment gate with --confirm-dual-writes-deployed.

Verify complete coverage

Check that every populated plaintext value has an encrypted mirror:

SELECT
    count(*) FILTER (
      WHERE name IS NOT NULL AND name_encrypted IS NULL
    ) AS name_uncovered,
    count(*) FILTER (
      WHERE date_of_birth IS NOT NULL
        AND date_of_birth_encrypted IS NULL
    ) AS dob_uncovered
FROM patients;

Both counts must be zero. A non-zero result usually means a writer is not dual-writing. Fix and deploy that path, then run the backfill again before continuing.

Also sample known records through the encrypted application client and verify their decrypted values against the plaintext originals.

Build encrypted indexes

Build the functional indexes required by the queries you will move to the encrypted columns. Do this after the bulk backfill and before changing reads.

Index definitions, supported query shapes, EXPLAIN verification, and guidance for large tables are maintained in EQL indexes. Use concurrent index creation where that guide recommends it for a live table.

Cut reads over

Deploy an application-only change that reads and filters on the encrypted mirror columns:

const { data } = await db
  .from("patients")
  .select("id, email, name_encrypted, date_of_birth_encrypted")
  .eq("name_encrypted", "Alice Chen")
  .gt("date_of_birth_encrypted", "1980-01-01")
  .order("date_of_birth_encrypted", { ascending: false })

Keep dual-writes enabled. If the new read path misbehaves, reverting this deployment restores plaintext reads while both sets of columns remain current.

Verify correctness rather than checking only for non-empty results. Test known equality matches, range boundaries, ordering, nulls, and authorization behavior.

Soak, then remove plaintext

Let production traffic exercise the encrypted read path. Monitor query errors, latency, decryption failures, and uncovered-row counts for an appropriate period.

Once you no longer need a plaintext rollback, remove the dual-write code and generate the destructive migration:

npx stash encrypt drop --table patients --column name

Review and apply the generated migration through your normal deployment process. Repeat per column rather than combining unrelated migrations into one irreversible change.