You have a users.email column full of plaintext and a production system reading and
writing it. This is how you get it encrypted without a maintenance window and without a
giant transaction.
The shape is the standard four-phase online migration: expand, backfill, cut over, contract. Two rules govern the whole thing:
- Schema migrations never need keys. Adding
encrypted_emailis an ordinary Ash/Ecto migration. - Data migrations always need keys. The backfill needs the app runtime and a reachable
key provider, and it fails fast with
ProviderUnavailablerather than writing half a table.
Before you start
- The key provider is running and reachable, and
mix ash_vault.key_infoanswers for the tenants you are about to migrate. - You have decided on your scope. Changing it later re-keys everything.
- You have a database backup taken before the migration, and you know it contains plaintext. Contracting (step 5) does not remove plaintext from backups you already took; only key destruction can reach those, and it cannot — those rows were never encrypted. If your goal is to make historical plaintext unreadable, the migration is necessary but not sufficient: you also have to age out the backups that predate it.
Step 1 — Expand
Rename the plaintext column out of the way and add the ciphertext column. One migration, no keys:
defmodule MyApp.Repo.Migrations.ExpandUserEmail do
use Ecto.Migration
def change do
rename table(:users), :email, to: :legacy_email
alter table(:users), do: add(:encrypted_email, :binary)
end
endThe rename is what makes the rest work. The AshVault transformer removes the :email
attribute and replaces it with a calculation, so :email and a plaintext email column
cannot coexist — and mix ash_vault.backfill refuses a source that is the ciphertext
attribute. The plaintext has to live under a different name until you drop it.
If you would rather not rename, the alternative is to encrypt under a new field name (
encrypt :email_v2, backfill_from: :email) and rename the field in your application code instead. Same tradeoffs, different place to do the renaming. Everything below assumes the rename.
Deploy the migration on its own first. Nothing reads or writes the new column yet.
Step 2 — Deploy the transitional resource
Now put the field under ash_vault, alongside the surviving plaintext attribute:
defmodule MyApp.Accounts.User do
use Ash.Resource,
domain: MyApp.Accounts,
data_layer: AshPostgres.DataLayer,
extensions: [AshVault]
ash_vault do
vault MyApp.Vault
scope :tenant
encrypt :email, backfill_from: :legacy_email
end
attributes do
uuid_primary_key :id
attribute :org_id, :uuid, allow_nil?: false, public?: true
attribute :legacy_email, :string, public?: true
attribute :email, :string, public?: true
end
actions do
default_accept :*
defaults [:read, :destroy, create: :*]
update :update do
primary? true
require_atomic? false
end
end
endbackfill_from: :legacy_email records the source in the resource, so
mix ash_vault.backfill and mix ash_vault.verify do not need --from. The verifier
checks it names an attribute that still exists.
This deploy changes write behaviour
From this moment, writes to :email go through the vault and land in
encrypted_email. legacy_email stops being updated. Rows written from here on are
already encrypted; the backfill is only for the rows that predate this deploy.
If your application still reads legacy_email anywhere, those reads now see stale
values for newly-written rows. Move reads onto :email in this deploy or the one after,
and remember :email has to be loaded — it is a calculation:
MyApp.Accounts.User |> Ash.Query.load([:email]) |> Ash.read!(tenant: org.id)decrypt_by_default [:email] makes that automatic if you prefer.
Step 3 — Backfill
mix ash_vault.backfill MyApp.Accounts.User email --tenant acmeEncrypt MyApp.Accounts.User.email for 1 tenant(s)? [Yn] y
ash_vault.backfill=start resource=MyApp.Accounts.User field=email source=legacy_email \
tenant=acme scope=acme vault=MyApp.Vault provider=AshVault.KeyProviders.OpenBao \
key=v1 rows=2000 batch_size=500 dry_run=false
batch=1 rows=500 done=500 skipped=0 remaining=1500 rate=812.3 eta_s=1.8 elapsed_s=0.62 last_pk=...
batch=2 rows=500 done=1000 skipped=0 remaining=1000 rate=830.1 eta_s=1.2 elapsed_s=1.20 last_pk=...
batch=3 rows=500 done=1500 skipped=0 remaining=500 rate=835.7 eta_s=0.6 elapsed_s=1.79 last_pk=...
batch=4 rows=500 done=2000 skipped=0 remaining=0 rate=838.2 eta_s=0.0 elapsed_s=2.38 last_pk=...
ash_vault.backfill=done rows=2000 done=2000 skipped=0 batches=4 elapsed_s=2.38
Backfilled 2000 row(s) of MyApp.Accounts.User.email for tenant acme in 4 batch(es) (2.38s).Every flag:
mix ash_vault.backfill RESOURCE FIELD [options]
--from ATTR plaintext source attribute (default: the field's `backfill_from`)
--batch-size N rows per committed transaction (default 500)
--tenant TENANT required when the key scope is `:tenant`
--all-tenants MFA zero-arity Module.function/0 listing tenants
--domain MODULE Ash domain (inferred from `:ash_domains` when omitted)
--action NAME update action to write with (default: the primary update action;
it must be `require_atomic? false`)
--lookup populate `<field>_lookup` tokens for rows that ALREADY hold
ciphertext (see "Searchable fields" below); not part of an
ordinary backfill
--verify decrypt a sample and compare to the plaintext column — and, for a
`searchable?` field, recompute and compare its `<field>_lookup`
token; write nothing
--dry-run report what would be done; write nothing
--resume-from ID start after this primary key (one tenant only)
--sample N rows to check in `--verify` mode (`0` checks every row)
--yes skip the confirmation promptAcross every tenant:
mix ash_vault.backfill MyApp.Accounts.User email \
--all-tenants MyApp.Accounts.list_tenant_ids/0 --batch-size 1000 --yesA failure in one tenant stops the run and names that tenant.
--all-tenants cannot be combined with --resume-from, and the task refuses the pair.
A primary key is a keyset offset into one tenant's rows; applied to the others it is an
arbitrary cut, and because the pending count skips the same prefix every tenant would
report done == total, remaining: 0 with rows left unencrypted. Resume the single tenant
that failed with --tenant, then re-run --all-tenants with no --resume-from — the
backfill is idempotent, so the tenants that already finished cost one scan and write
nothing.
What it guarantees
- Idempotent and resumable with no state file. Rows are selected with
encrypted_email IS NULL, ordered by primary key, paged with a keyset (pk > last_seen) rather thanOFFSET. An encrypted row no longer matches the filter, so a second run is a no-op — and asserted so in the test suite by comparing ciphertext bytes, not merely "it still decrypts" (re-encrypting would produce a different nonce). - Each batch is its own transaction. The table is never wrapped in one.
- Only the encrypted column is written — plus the
<field>_lookuptoken when the field issearchable?: true, because a row holding one without the other is a row that decrypts correctly and cannot be found. Both come fromAshVault.write_attributes/4, the same function an ordinary create or update goes through. OneAsh.bulk_update/4per batch, withauthorize?: falseandreturn_records?: false. - Backfilled rows decrypt through the ordinary read path. The context is built by the
same
AshVault.Context.Builder.from_changeset/3an ordinary write uses, from a real changeset over the real record, with the plaintext field name — so the AAD matches. A backfill that hand-rolled its context would be one field name away from writing rows that never decrypt again, which is why there is a test asserting exactly this. - The provider is checked before the first batch. An unreachable provider fails immediately rather than half-writing the table.
If the provider goes away mid-run, the task stops at a batch boundary and prints the exact command to resume:
Stopped at a batch boundary. 1000 row(s) are committed; the rest are untouched.
Resume with:
mix ash_vault.backfill MyApp.Accounts.User email --batch-size 500 --tenant acme --resume-from 4a1f...--resume-from is an optimisation that skips re-scanning the committed prefix, never a
correctness requirement: the IS NULL filter already makes a plain re-run correct.
Searchable fields
A field declared searchable?: true has a second column, <field>_lookup: a
deterministic HMAC of the normalized value, which is what
AshVault.Query.filter_by/4, the generated by_<field> read action and any
unique?: true identity actually match on.
Nothing extra to do. Back-filling such a field writes the ciphertext and the token in
the same batch, and mix ash_vault.verify checks both. A row with ciphertext and a NULL
token is not "partly migrated" — it is invisible: filter_by returns [], the
by_<field> action finds nothing, and a unique?: true constraint over the token column
is not enforced, so duplicates get in.
One consequence worth stating outright, because it is visible in the data:
normalize: is lossy on the ciphertext too
AshVault normalizes once and encrypts that value, so the stored plaintext and the
stored token always agree about what the row holds. Under
normalize: :downcase_trim, a legacy_email of " Jake@Example.COM " back-fills to a
ciphertext that decrypts to "jake@example.com". That is the same thing an ordinary
write does; the backfill is deliberately not a special case. If the original spelling
matters to you, keep it in its own column — do not rely on the encrypted one to
preserve it.
mix ash_vault.verify knows this: it normalizes the plaintext column before comparing,
so a correctly back-filled row is not reported as a value_mismatch.
--lookup
--lookup is for the two cases where the ciphertext is already there and only the token
is missing:
- you added
searchable?: trueto a field that was already encrypted, so every existing row has a NULL token; - you rotated the provider's lookup secret, so every existing token is stale. (
mix ash_vault.rotatedeliberately does not touch tokens — if it moved them, every existing row would silently stop being findable.)
mix ash_vault.backfill MyApp.Accounts.User email --tenant acme --lookupIt reads, decrypts, normalizes, hashes and writes only the token column; the
ciphertext is never rewritten. Like the ciphertext backfill it is idempotent and
resumable with no state file — it selects only rows whose token IS NULL and whose
ciphertext IS NOT NULL.
It cannot be combined with --verify (one writes, the other writes nothing), and does
not need to be: plain --verify checks the token column, reporting lookup_missing for
a NULL token and lookup_mismatch for one that is not what a lookup computes.
It also cannot repair a changed normalize:. Changing normalize: on a populated
column makes the ciphertext and the token disagree, and hashing the newly normalized
value would leave those rows findable under one spelling and readable as another. The
task detects that, names the row and aborts. Reconciling the two requires re-encrypting,
and after Step 6 there is no plaintext column left to re-encrypt from.
Nil sources
When the field is encrypt_nil?: false, a nil source legitimately stores SQL NULL — so
those rows would match the IS NULL filter forever. They are excluded with
source IS NOT NULL and counted separately:
Backfilled 1988 row(s) of MyApp.Accounts.User.email in 4 batch(es) (2.38s), 12 row(s) skipped (nil source, `encrypt_nil?: false`).Dry run first
mix ash_vault.backfill MyApp.Accounts.User email --tenant acme --dry-runWrites nothing, and specifically does not mint key material — an unused scope reports
key=not_minted rather than being created.
Step 4 — Verify
mix ash_vault.verify MyApp.Accounts.User email --tenant acmeash_vault.verify=result resource=MyApp.Accounts.User field=email rows=2000 checked=200 mismatches=0
Verified 200 of 2000 row(s) of MyApp.Accounts.User.email: no mismatches.It decrypts a sample and compares to the plaintext column. The default sample is 10% of
the table or 1000 rows, whichever is smaller, always including the first and the last row;
--sample 0 checks every row. It writes nothing.
For a searchable?: true field it also recomputes the <field>_lookup token from the
value it just decrypted — the same computation a query does — and compares it to the
stored one.
Exit status is non-zero on the first tenant with any mismatch, and the summary names the offending primary keys with a reason:
| reason | what it means |
|---|---|
not_backfilled | the encrypted column is still NULL |
value_mismatch | it decrypts, to something other than the (normalized) plaintext column |
decrypt_failed | it does not decrypt at all |
lookup_missing | searchable? field, ciphertext present, token NULL — the row is invisible to filter_by |
lookup_mismatch | the stored token is not the one a lookup computes for this value |
Run --sample 0 at least once per tenant before you contract. It is the last cheap check
you get.
Step 5 — Cut over
Move every reader and writer onto :email. Delete any code still touching
legacy_email. Make sure background jobs, exports, admin tooling and reports go through
Ash.Query.load([:email]) with a tenant.
This is the point of no return for that attribute
Once the attribute lives under ash_vault and nothing writes the plaintext column any
more, the ciphertext is the only copy of the data. There is no decrypt-back mode in
v1 and no mix ash_vault.decrypt. Do this only after a clean --verify, and only when
you are confident the key provider is production-ready — because from here on, losing
the key store means losing the data.
Until step 6, the plaintext column is still there. That is your rollback. It is the only one you get.
Watch for a while. Specifically watch for MissingScope from anything that reads or writes
without a tenant — background jobs and mix tasks are the usual offenders, and they will not
have shown up in your test suite.
Step 6 — Contract
When you are sure, drop the plaintext column:
defmodule MyApp.Repo.Migrations.ContractUserEmail do
use Ecto.Migration
def change do
alter table(:users), do: remove(:legacy_email, :string)
end
endRemove backfill_from: :legacy_email and the legacy_email attribute from the resource in
the same deploy.
Then remember: every database backup taken before this migration still contains the plaintext. Crypto-erasure cannot reach them, because those rows were never encrypted. If making historical plaintext unrecoverable is the goal, age those backups out on a schedule you actually enforce, and record the date after which backups contain only ciphertext.
If something goes wrong
The backfill says the field is not configured.
MyApp.Accounts.User.email is not configured under `ash_vault`. Add `encrypt :email` to
the resource's `ash_vault` section. Known encrypted fields: [:ssn]Step 2 has not been deployed, or the field name is wrong.
The backfill has no source.
no plaintext source for MyApp.Accounts.User.email. Pass `--from ATTR`, or declare
`encrypt :email, backfill_from: :legacy_email`.The provider is unreachable. The run stops before the first batch, with
ProviderUnavailable. Nothing was written. Fix the provider and re-run; the task is
idempotent.
Verification fails on some rows. Those rows' ciphertext does not match their plaintext. Do not contract. The plaintext is still authoritative — investigate which rows and why (a concurrent write during the backfill is the usual answer, and a re-run will not fix them because their encrypted column is no longer NULL). The blunt remedy is to null out the encrypted column for the affected primary keys and re-run the backfill for them.
A read after cut-over raises MissingScope. Something is reading without a tenant. See
Tenant-scoped encryption.
Related
- Operations — the full mix task reference and error taxonomy
- Getting started — the greenfield path
AshVault.Backfill— the engine, callable from a release without Mix