Phase B0 — Foundations Spec

Status: locked build target (architecture review closed).
Goal: pay the durability / PDPA tax up front so B1–B6 hang on a fixed schema.
Substrate (locked): Redis Streams = transport; Postgres = system of record.
Migrator (locked): node-pg-migrate (hand SQL fidelity). Typed query helpers separately.
Out of scope for B0 runtime: workers, 202 ingest, SSE pub/sub, delivery outbox consumer, corroboration decisioning.

---

1. Why this substrate (short)

Need	Choice	Why not the alternative
Append log + consumer groups	Redis Streams	Already required for multi-replica JTI/rate-limit; Kafka is exotic ops for a pilot.
Durable audit / query / purge	Postgres	Streams are not a queryable RMiT evidence store.
Fan-out to SSE replicas	Redis pub/sub channel waspada:sse (B4)	Keep Streams for work queues.

B0 introduces Postgres + migrator + schema + crypto helpers + tenant seed. Redis Streams wiring starts in B1.

---

2. Tenancy model

Every durable tenant-scoped row and every bus event carries tenant_id.

Concept	Value in pilot	Rule
tenant_id	Partner platform id (JWT iss / source_platform)	Immutable after write.
Analyst scope	Constant pilot-analyst until OIDC	Never store the raw API key.
Cross-tenant	Forbidden in B	Schema-enforced (see §2.1).

Exception: processed_events is a cross-tenant idempotency ledger keyed by (consumer_group, event_id) — no tenant_id by design (event_id is globally unique). Retention still applies via retention_expires_at.

JWT iss / source_platform  →  tenant_id
ANALYST_API_KEY            →  auth only; analyst_id = "pilot-analyst" (or sha256(key) later)

2.1 Cross-tenant corroboration (locked)

• mule_key stays global — sha256(account|bic) with no tenant salt, so Phase C can correlate the same mule across banks later.
• Co-mingling forbidden until legal clears it:
  CHECK (reporting_tenant_id = tenant_id) on corroboration.
  Phase C drops the CHECK with a data-sharing legal basis.
• PDPA note: a consistent hash of a low-entropy account is re-identifiable pseudonymous data. mule_key is in erasure scope; every query must be tenant-scoped (the key itself is cross-tenant-joinable).

---

3. Encryption & keying

3.1 Layers

1. Managed disk encryption (provider default).
2. Application-level AES-256-GCM for:
   • mule_account_* envelope columns
   • scammer_alias_* envelope columns (when present)
   • signed_alert_ciphertext — full signed FmsRiskAlert JSON (encrypted, not plaintext JSONB)

Plaintext signed_alert_json is forbidden — the signed alert embeds target_mule_account in the clear; storing it as JSONB would make column encryption theater.

3.2 Envelope scheme (B0)

• DEK: random 32 bytes per row (crypto-shred = delete row + DEK)
• KEK: ALERT_DATA_KEK (32-byte base64) identified by kek_key_id (default env:ALERT_DATA_KEK)
• AAD (required): tenant_id:alert_id:field where field ∈ {mule_account, scammer_alias, signed_alert}. Binds each ciphertext to its row/column so a DB-tamper swap of {ciphertext, nonce, wrapped_dek} fails auth (envelopes are otherwise relocatable under a shared KEK).
• KEK rotation (document now, implement rewrap in B1+): never swap the env var alone — keep old KEKs addressable by kek_key_id and rewrap DEKs. Orphaned wrapped_dek is a data-loss bug.
• Boot: when DATABASE_URL is set, require a valid ALERT_DATA_KEK in production and hardened runtimes (staging / Railway / REQUIRE_ANALYST_API_KEY) — not production-only.
• Cleartext never logged.

3.3 Plaintext (indexed / join keys)

Column	Why plaintext
mule_key	Correlation id (pseudonymous — still erasure-scoped)
tenant_id, event_id, alert_id	Routing / idempotency
Enums / scores / retention_expires_at	Non-account fields
bank_bic, malicious_urls, enrichment	Needed for query; account/alias stay encrypted

---

4. Retention & PDPA erasure

Knob	Default	Notes
RETENTION_DAYS	90	Overridable per tenants.retention_days
retention_expires_at	created_at + retention_days	On every durable table including processed_events
Erasure (B6)	By mule_key / event_id	Delete alert row (DEK dies) and purge mule_key from corroboration / indexes

Crypto-shred limits (honest):

1. Deleting row+DEK does not erase PITR / WAL backups. Bound backup retention ≤ product retention and document it for DPIA.
2. Leaving mule_key in corroboration after shred leaves a re-derivable re-identification token — erasure must purge those rows too.

Purge job: B0 creates columns + a no-op stub; B6 implements daily delete where retention_expires_at < now().

---

5. Event schema (Redis Streams) — B1+

Single stream waspada:events + tenant_id field (pilot).

Envelope

{
  "v": 1,
  "event_id": "uuid-v4",
  "type": "ingested | enriched | alert_created | disposition_recorded | delivery_*",
  "tenant_id": "PARTNER_EWALLET_SDK",
  "occurred_at": "ISO-8601",
  "causation_id": "uuid | null",
  "idempotency_key": "stable string",
  "payload": {}
}

disposition_recorded analyst identity

{
  "alert_id": "…",
  "mule_key": "…",
  "disposition": "confirmed | dismissed | false_positive",
  "analyst_id": "pilot-analyst",
  "recorded_at": "…"
}

Never put the raw API key in events, logs, or columns.

Streams correctness (B1 flags — implement with Streams)

• Set MAXLEN / approximate trim (unbounded stream is a footgun).
• XACK only after durable Postgres write (else crash between ack and write → at-most-once).
• Plan XAUTOCLAIM for stuck PEL entries.
• Postgres down → workers stall → stream grows: need MAXLEN + alerting (observability).

---

6. Postgres DDL (B0)

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE tenants (
  tenant_id            TEXT PRIMARY KEY,
  display_name         TEXT NOT NULL,
  retention_days       INTEGER NOT NULL DEFAULT 90
                       CHECK (retention_days >= 7 AND retention_days <= 730),
  created_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  status               TEXT NOT NULL DEFAULT 'active'
                       CHECK (status IN ('active', 'suspended', 'offboarded'))
);

CREATE TABLE alerts (
  alert_id             UUID PRIMARY KEY,
  event_id             UUID NOT NULL,
  tenant_id            TEXT NOT NULL REFERENCES tenants(tenant_id),
  mule_key             CHAR(64) NOT NULL,
  disposition          TEXT NOT NULL
                       CHECK (disposition IN ('advisory', 'actionable')),
  action_requested     TEXT NOT NULL
                       CHECK (action_requested IN ('MONITOR', 'ELEVATE_RISK_SCORE')),
  urgency              TEXT NOT NULL
                       CHECK (urgency IN ('HIGH', 'CRITICAL')),
  client_reported_confidence DOUBLE PRECISION NOT NULL
                       CHECK (client_reported_confidence >= 0 AND client_reported_confidence <= 1),
  corroboration_count  INTEGER NOT NULL DEFAULT 1 CHECK (corroboration_count >= 0),
  advisory_notice      TEXT NOT NULL,
  -- app-encrypted sensitive fields (per-row DEK)
  mule_account_ciphertext   BYTEA,
  mule_account_nonce        BYTEA,
  mule_account_wrapped_dek  BYTEA,
  scammer_alias_ciphertext  BYTEA,
  scammer_alias_nonce       BYTEA,
  scammer_alias_wrapped_dek BYTEA,
  -- full signed FmsRiskAlert JSON — ENCRYPTED (embeds cleartext account)
  signed_alert_ciphertext   BYTEA NOT NULL,
  signed_alert_nonce        BYTEA NOT NULL,
  signed_alert_wrapped_dek  BYTEA NOT NULL,
  kek_key_id           TEXT NOT NULL DEFAULT 'env:ALERT_DATA_KEK',
  bank_bic             TEXT NOT NULL,
  malicious_urls       JSONB NOT NULL DEFAULT '[]'::jsonb,
  enrichment           JSONB NOT NULL DEFAULT '{}'::jsonb,
  gateway_signature    TEXT NOT NULL,
  original_payload_hash TEXT NOT NULL,
  created_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  retention_expires_at TIMESTAMPTZ NOT NULL,
  UNIQUE (tenant_id, event_id),
  -- All-or-none envelope triples (no half-written undecryptable rows)
  CHECK (
    (mule_account_ciphertext IS NULL) = (mule_account_nonce IS NULL)
    AND (mule_account_ciphertext IS NULL) = (mule_account_wrapped_dek IS NULL)
  ),
  CHECK (
    (scammer_alias_ciphertext IS NULL) = (scammer_alias_nonce IS NULL)
    AND (scammer_alias_ciphertext IS NULL) = (scammer_alias_wrapped_dek IS NULL)
  )
);

CREATE INDEX alerts_tenant_created_idx ON alerts (tenant_id, created_at DESC);
CREATE INDEX alerts_mule_key_idx ON alerts (tenant_id, mule_key);
CREATE INDEX alerts_retention_idx ON alerts (retention_expires_at);

CREATE TABLE corroboration (
  tenant_id            TEXT NOT NULL REFERENCES tenants(tenant_id),
  mule_key             CHAR(64) NOT NULL,
  reporting_tenant_id  TEXT NOT NULL,
  first_seen_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_seen_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  report_count         INTEGER NOT NULL DEFAULT 1 CHECK (report_count >= 1),
  PRIMARY KEY (mule_key, reporting_tenant_id),
  -- Locked: no cross-tenant co-mingling until Phase C + legal basis
  CHECK (reporting_tenant_id = tenant_id)
);

CREATE INDEX corroboration_mule_idx ON corroboration (mule_key);

-- Append-only action log (no fake uniqueness on recorded_at).
-- Soft ref on alert_id (no FK): intentional erasure-decoupling — do NOT add
-- REFERENCES alerts(alert_id); that would fight crypto-shred.
CREATE TABLE dispositions (
  id                   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id            TEXT NOT NULL REFERENCES tenants(tenant_id),
  alert_id             UUID NOT NULL,
  mule_key             CHAR(64) NOT NULL,
  disposition          TEXT NOT NULL
                       CHECK (disposition IN ('confirmed', 'dismissed', 'false_positive')),
  analyst_id           TEXT NOT NULL,  -- "pilot-analyst" until OIDC; never raw API key
  recorded_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  retention_expires_at TIMESTAMPTZ NOT NULL
);

CREATE INDEX dispositions_alert_idx ON dispositions (alert_id, recorded_at DESC);
CREATE INDEX dispositions_mule_idx ON dispositions (tenant_id, mule_key);
CREATE INDEX dispositions_retention_idx ON dispositions (retention_expires_at);

-- Soft ref on alert_id (no FK): same erasure-decoupling as dispositions.
CREATE TABLE delivery_outbox (
  id                   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id            TEXT NOT NULL REFERENCES tenants(tenant_id),
  alert_id             UUID NOT NULL,
  idempotency_key      TEXT NOT NULL,
  status               TEXT NOT NULL DEFAULT 'pending'
                       CHECK (status IN (
                         'pending', 'in_flight', 'succeeded', 'failed', 'dead_lettered'
                       )),
  attempts             INTEGER NOT NULL DEFAULT 0,
  next_retry_at        TIMESTAMPTZ,
  last_error           TEXT,
  last_status_code     INTEGER,
  created_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  retention_expires_at TIMESTAMPTZ NOT NULL,
  UNIQUE (tenant_id, idempotency_key)
);

CREATE INDEX delivery_outbox_poll_idx
  ON delivery_outbox (status, next_retry_at)
  WHERE status IN ('pending', 'failed');

-- Cross-tenant idempotency ledger (no tenant_id — event_id is global).
CREATE TABLE processed_events (
  consumer_group       TEXT NOT NULL,
  event_id             UUID NOT NULL,
  processed_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  retention_expires_at TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (consumer_group, event_id)
);

CREATE INDEX processed_events_retention_idx ON processed_events (retention_expires_at);

Race-safe corroboration upsert (B3):

INSERT INTO corroboration (tenant_id, mule_key, reporting_tenant_id)
VALUES ($1, $2, $1)
ON CONFLICT (mule_key, reporting_tenant_id)
DO UPDATE SET last_seen_at = now(),
              report_count = corroboration.report_count + 1;

---

7. Ingest wire contract (B1 — not B0)

Today	B1+
201 + alert_id	202 ACCEPTED + event_id + schema_version: 2

SDK already accepts any response.ok (including 202). Coordinate SDK if it reads alert_id.

---

8. Federation carry-forward (B3 gate)

if disposition === "advisory":
  do NOT emit machine-actionable indicator SDOs
  (or suppress confidence / omit pattern)
  attach object_marking_refs for human review

Pilot disposition is always advisory → automated federation stays off until this gate exists. Analyst-mediated export may keep labeled indicators (Phase A.1) until automated feed is wired.

---

9. B0 implementation checklist

1. DATABASE_URL + ALERT_DATA_KEK env; provision CLI generates KEK.
2. node-pg-migrate initial migration matching §6.
3. Encrypt/decrypt helpers (per-row DEK wrap/unwrap) + unit tests.
4. Seed tenants from PARTNER_KEYS_JSON on boot (idempotent).
5. Production and hardened boot: if DATABASE_URL set, require valid ALERT_DATA_KEK.
6. /health reports postgres: up|down|skipped without leaking DSN.
7. Disposition logs use analyst_id: "pilot-analyst".
8. Docs link from architecture.md.
9. Envelope crypto requires AAD (tenant_id:alert_id:field); all-or-none CHECKs on optional triples.

Non-goals for B0 PR: Streams producers, 202 ingest, workers, SSE changes, writing alerts rows (B3).

---

10. Rollout posture (B1+)

In-process consumers first; split processes when scaling. XACK-after-Postgres; Streams MAXLEN; KEK registry before claiming KMS-ready.

---

11. Decisions (locked)

#	Decision
Q1	Global mule_key + CHECK (reporting_tenant_id = tenant_id) until Phase C legal basis
Q2	Keep full signed alert blob — encrypted with per-row DEK (no plaintext JSONB)
Q3	node-pg-migrate for DDL; thin typed queries separately

---

Phase A closed. B0 is schema + crypto + seed. B1 is the first behavior change (ingest → log → 202).