Busflow Docs

Internal documentation portal

Busflow Docs

Appearance

Tenant Credentials Encryption — Migration Plan ​

Status: 📋 Ready to apply once the backoffice schema exists and a hasura_user role is provisioned. Scope: Concrete DDL for pgsodium-encrypted tenant API credentials + masked Hasura-facing view, implementing ADR-029 Layer B. Why this file exists: This plan was originally drafted as a Hasura migration (apps/hasura/migrations/default/20260420120100_tenant_credentials_encryption/). The migration was removed because the backoffice database is still empty. The encryption primitives and masking pattern are non-obvious enough that losing them to a git rm would cost a non-trivial redesign later; this document preserves the concrete SQL.

Preconditions ​

  1. Postgres 15+ with shared_preload_libraries including pgsodium. On Ubicloud standard-2 this is pre-enabled (ADR-022 §Consequences).
  2. A dedicated hasura_user role exists. Raw table access will be REVOKEd from PUBLIC, and only the masked view will be GRANTed to hasura_user.
  3. A dedicated tenant_admin_raw role (to be created in a follow-up migration; see ADR-029 Layer B) is the only role that may SELECT the underlying ciphertext columns. It is used by a NestJS service account, never by a Hasura user role.
  4. backoffice schema exists.

Key Hierarchy (ADR-029 Layer B) ​

  • One KEK (key-encryption-key) per environment, stored in the pgsodium.key table.
  • The DEK (data-encryption-key) for each ciphertext column is derived from the KEK using the pgsodium deterministic context busflow.tenant_credentials.
  • Determinism is scoped to key_fingerprint only. api_secret and webhook_secret use standard (randomized) AEAD and are not searchable. Legal sign-off on determinism is recorded against the key_fingerprint column in ADR-029 §Decision.

1. Extension ​

sql
CREATE EXTENSION IF NOT EXISTS pgsodium;

2. Create the Per-Environment KEK ​

sql
DO $$
BEGIN
  PERFORM pgsodium.create_key(
    name     => 'busflow_tenant_credentials_kek',
    key_type => 'aead-det'
  );
EXCEPTION WHEN duplicate_object THEN
  -- idempotent — migration may have partially applied
  NULL;
END $$;

Important: create_key is environment-scoped. Do not drop this key in the down migration — other environments may still reference it. The down section below deliberately leaves the KEK alone.

3. Credentials Table ​

All PII-bearing columns are TEXT ciphertext. The column comments below are load-bearing — they record the AEAD mode so future reviewers don't have to re-derive it from the masked view.

sql
CREATE TABLE IF NOT EXISTS backoffice.tenant_credentials (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id        UUID NOT NULL,
  provider         TEXT NOT NULL,
  label            TEXT NOT NULL,
  key_fingerprint  TEXT NOT NULL,           -- deterministic AEAD ciphertext of the credential's stable fingerprint
  api_secret       TEXT NOT NULL,           -- standard AEAD ciphertext
  webhook_secret   TEXT,                    -- standard AEAD ciphertext (nullable)
  last_rotated_at  TIMESTAMPTZ NOT NULL DEFAULT now() AT TIME ZONE 'UTC',
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now() AT TIME ZONE 'UTC'
);

-- Unique fingerprint per tenant+provider (deterministic AEAD makes this safe).
CREATE UNIQUE INDEX IF NOT EXISTS idx_tenant_creds_fp
  ON backoffice.tenant_credentials (tenant_id, provider, key_fingerprint);

COMMENT ON TABLE backoffice.tenant_credentials IS
  'Tenant API credentials. Columns are pgsodium AEAD ciphertext. Never expose via Hasura directly; use v_tenant_credentials_masked.';

Why deterministic on key_fingerprint: the fingerprint is a stable identifier derived from the credential (e.g. the last 4 chars of the API key). The product requires equality lookups like "does this tenant already have a Mollie key with fingerprint X?" — that's impossible with randomized AEAD. The fingerprint deliberately leaks no plaintext information on its own; the ****$SUFFIX masking in §4 is what is shown to operators.

Why randomized on api_secret / webhook_secret: these must never be searchable. Randomized AEAD is the correct default; accept the loss of equality lookups.

4. Masked View (Hasura-Facing) ​

Hasura must never see the ciphertext or the plaintext. It only ever sees ****XXXX — enough for an operator to disambiguate credentials in the UI, nothing more.

sql
CREATE OR REPLACE VIEW backoffice.v_tenant_credentials_masked AS
SELECT
  id,
  tenant_id,
  provider,
  label,
  -- ****$SUFFIX — last 4 characters of the base64-encoded ciphertext are
  -- sufficient to disambiguate in the UI and reveal nothing about the plaintext.
  '****' || right(key_fingerprint, 4) AS key_fingerprint_mask,
  last_rotated_at,
  created_at
FROM backoffice.tenant_credentials;

5. Permissions Lockdown ​

sql
-- Strip all default public access to the raw table.
REVOKE ALL ON backoffice.tenant_credentials FROM PUBLIC;

-- Hasura reads ONLY the masked view.
GRANT SELECT ON backoffice.v_tenant_credentials_masked TO hasura_user;

Operator rule (enforced by code review, not SQL): if you need to surface another field to Hasura, extend the masked view. Do not GRANT on backoffice.tenant_credentials. The single REVOKE ALL above is the only guard — once hasura_user gets a column-level grant on the raw table, the masking contract is permanently broken for that deployment.

Writes (credential creation / rotation) flow through a NestJS service using the tenant_admin_raw role — never through Hasura. The write path must call pgsodium's AEAD encrypt functions before insertion; the DDL above deliberately does not add triggers that would silently re-encrypt already-encrypted data.

6. pii_redacted_at Parity (Future Work) ​

When the wider GDPR retention plan (gdpr-migration-plan.md) lands, tenant_credentials should be added to the scrub list with a distinct retention window — credentials deleted by the tenant manager should be purged within the 30-day GDPR window, not the 3-year passenger window. This is deferred because:

  1. There is no tenant UI yet to distinguish "deleted" from "active" credentials.
  2. Rotation (which is in scope today) is not a deletion — last_rotated_at bumps but the row stays.

Track in a separate ADR once the credential lifecycle (draft → active → revoked → purged) is specified.

Down Migration ​

sql
DROP VIEW IF EXISTS backoffice.v_tenant_credentials_masked;
DROP INDEX IF EXISTS backoffice.idx_tenant_creds_fp;
DROP TABLE IF EXISTS backoffice.tenant_credentials;
-- Do NOT drop the KEK — other tenants/envs may still reference it.

Warning: the down migration destroys tenant credentials. It exists for migration development only. Any real post-drop recovery must come from a PITR restore per postgres-cutover.md §PITR.

When to Land This Migration ​

  1. After backoffice schema exists and hasura_user is provisioned.
  2. After the tenant_admin_raw role exists (add it in a preceding migration).
  3. Before the first tenant wires up a real payment provider or WhatsApp channel — credentials must land into the encrypted table from day one; there is no supported "plaintext → encrypted" migration path.

Internal documentation — Busflow

© 2026 MLP Software Development