@rydeu/db-crypto

Application-level field encryption (AES-256-GCM over AWS KMS envelope keys) for the Postgres columns that hold personal data.

It lives here because users, vendors, drivers, transfers and accounts are modelled independently in backend-mvp, marketing-microservice, packages-service, discounting-microservice and email-microservice. If only one service encrypts, every other service reads enc:v1:… at cutover. All of them must depend on this one package, version-pinned.

Design doc: docs/pii-encryption-approach.md

Install

npm install @aws-sdk/client-kms

Boot

init() must complete before any model is loaded. It unwraps the key material once so that encrypt/decrypt can be synchronous afterwards — which is what allows them to run inside Sequelize getters and setters.

const dbCrypto = require("@rydeu/db-crypto");
const { loadSecrets, getSecretValueSync } = require("./config/secretManager");

await loadSecrets();
await dbCrypto.init({ readSecret: (name) => getSecretValueSync(name) });

const { db } = require("./models"); // only now

A background timer re-reads the secret every 15 minutes so a key rotation is picked up without a redeploy. A failed refresh is logged, not fatal — the previously loaded keys stay in place, because a transient KMS blip must not take every read and write down with it.

Use in a model

const { encryptedField } = require("@rydeu/db-crypto");

module.exports = (sequelize, Sequelize) => {
  const drivers = sequelize.define("drivers", {
    id: { type: Sequelize.UUID, primaryKey: true },
    firstName: encryptedField(Sequelize),
    lastName:  encryptedField(Sequelize),
    phone:     encryptedField(Sequelize),
    email:     encryptedField(Sequelize),
    drivingLicense: encryptedField(Sequelize)
  });

  return drivers;
};

For a JSON column:

bankDetails: encryptedField(Sequelize, { json: true })

Optionally add the write guard, which turns a plaintext value that slipped past the setter into a loud failure instead of silent corruption:

const { attachGuards } = require("@rydeu/db-crypto");
attachGuards(drivers, "drivers");   // second arg is the Postgres table name

DAOs, controllers and services need no changes — the getter and setter handle it.

Rules that are not negotiable

The column must be TEXT. Ciphertext is roughly twice the plaintext length. A VARCHAR(255) truncates it and the value is then unrecoverable. Migrate the type before enabling encryption.

decrypt() returns anything without the enc: prefix unchanged. This is what lets encrypted and not-yet-backfilled rows share a column, which is what makes the backfill resumable and every deploy step independently reversible. Do not "tighten" it.

encrypt() is idempotent. Re-encrypting an existing envelope returns it unchanged, so the backfill is safe to re-run over rows it already processed.

An encrypted column can never appear in a WHERE. GCM uses a random IV, so the same plaintext produces different ciphertext every time — there is no value to compare against. A query that tries returns 0 rows rather than failing, which reads as "no such record". columns.encryptedAmong() exists so the admin query console can reject these up front.

What still bypasses this layer

The getter/setter pair covers the ORM path. These do not go through it:

| Path | Effect | |---|---| | sequelize.query() / raw SQL | reads return ciphertext; writes store plaintext | | findAll({ raw: true }) | returns ciphertext | | queryInterface.bulkUpdate / bulkInsert (migrations) | stores plaintext | | Model.update(…, { validate: false }) | skips the setter — currently unused in the codebase |

attachGuards() catches the write side of the last two. Raw SQL has to be handled by the caller — that is exactly what the admin query console's auto-encrypt rewrite is for.

AWS setup

One CMK per environment:

aws kms create-key --description "rydeu-pii-prod"
aws kms create-alias --alias-name alias/rydeu-pii-prod --target-key-id <key-id>

Generate the data key once, keep only the wrapped form:

const { KMSClient, GenerateDataKeyCommand } = require("@aws-sdk/client-kms");
const res = await new KMSClient({ region }).send(
  new GenerateDataKeyCommand({ KeyId: "alias/rydeu-pii-prod", KeySpec: "AES_256" })
);
// res.Plaintext      → discard; never persisted anywhere
// res.CiphertextBlob → store base64 as the PII_KEYS entry below

IAM for the app role — only these two actions, only on that key ARN:

{ "Effect": "Allow", "Action": ["kms:Decrypt", "kms:GenerateDataKey"], "Resource": "arn:aws:kms:…:key/…" }

Add a CloudWatch alarm on ScheduleKeyDeletion. Losing the key means the data is gone; KMS gives a 7–30 day window to notice.

Secret shape

Added to each service's existing secret (SECRET_HEADER):

{
  "PII_ACTIVE_KEY_ID": "v1-2026-08",
  "PII_KEYS": {
    "v1-2026-08": "<base64 KMS-wrapped data key>",
    "v1-2026-02": "<base64 KMS-wrapped data key>"
  }
}

New ciphertext is written with PII_ACTIVE_KEY_ID. Every other entry stays for decrypt.

Rotation

1. Generate a new data key, add it to PII_KEYS.
2. Point PII_ACTIVE_KEY_ID at it. Within 15 minutes all services write with the new key.
3. Run the re-encrypt job over rows whose keyIdOf() is the old key.
4. Only once that reports zero remaining, drop the old entry from PII_KEYS.

Removing a key before step 4 finishes makes those rows permanently unreadable.

Per-column migration runbook

1. Add the column to src/columns.js.
2. Migration: ALTER TABLE "<table>" ALTER COLUMN "<col>" TYPE TEXT; — no data change, reversible.
3. Deploy with the field swapped to encryptedField. Existing plaintext rows keep working via pass-through, so this deploy is a no-op on live data and safe to roll back.
4. Backfill in batches of ~1000 by primary key, skipping rows already prefixed enc:. Use paranoid: false — these models are soft-delete, and rows skipped here fail to decrypt later.
5. Verify: zero rows lacking the enc: prefix, soft-deleted rows included.

Run migrations through this repo's runner (npm run migrate), not from the service repos.

Tests

node --test test/

42 tests, no AWS required — the suite injects raw keys through allowPlaintextKeys, which init() refuses to honour when NODE_ENV=production.

Covered: round-trip incl. non-ASCII and 50k values, null/empty/plaintext pass-through, idempotent re-encryption, IV randomisation, tamper and swapped-tag rejection, malformed envelopes, rotation with a retired key, dropped-key and uninitialised errors, the JSON path, and the write guards.