sender-id-registry-service — Data Model

Version: 1.0 Status: Draft Owner: Trust & Safety + Regulator-facing Last Updated: 2026-04-21 Companion: DOMAIN_MODEL · SECURITY_MODEL

Schema: sender_id_registry. Owned exclusively by sender-id-registry-service. No other service reads or writes these tables directly — integration is over gRPC (Verify, GetReputation), HTTPS REST (admin, tenant), or NATS events.

Per ADR-0004 §14, this schema is part of the multi-master control-plane data set with logical replication between kbl and mzr. Per-row LWW with HLC clocks resolves cross-region conflicts; audit and reputation_history are append-only and use append-only conflict semantics.

1. Tables

-- =====================================================================
--                          ENUMS
-- =====================================================================

CREATE TYPE sender_id_registry.sender_id_type AS ENUM (
  'ALPHA','SHORT','LONG'
);

CREATE TYPE sender_id_registry.sender_id_category AS ENUM (
  'BANKING','GOVERNMENT','HEALTHCARE','UTILITIES','MNO_INTERNAL',
  'RETAIL','TRANSPORT','EDUCATION','OTHER'
);

CREATE TYPE sender_id_registry.registry_state AS ENUM (
  'SUBMITTED','KYC_REVIEW','KYC_APPROVED','KYC_REJECTED',
  'INFO_REQUESTED','VERIFIED','ACTIVE','SUSPENDED','REVOKED'
);

CREATE TYPE sender_id_registry.verification_level AS ENUM (
  'NONE','OTP','DOCUMENT','NOTARISED'
);

CREATE TYPE sender_id_registry.verification_method AS ENUM (
  'OTP','DOCUMENT','NOTARISED','DOMAIN_DNS'
);

CREATE TYPE sender_id_registry.verification_state AS ENUM (
  'PENDING','IN_PROGRESS','SUCCEEDED','FAILED','EXPIRED'
);

CREATE TYPE sender_id_registry.kyc_doc_type AS ENUM (
  'COMMERCIAL_LICENCE','NATIONAL_ID','REGULATOR_LETTER',
  'NOTARISED_AUTHORITY','BOARD_RESOLUTION','DOMAIN_OWNERSHIP_PROOF','OTHER'
);

CREATE TYPE sender_id_registry.kyc_outcome AS ENUM (
  'PENDING','ACCEPTED','REJECTED'
);

CREATE TYPE sender_id_registry.restricted_category AS ENUM (
  'BANK','GOV','MNO','JUDICIAL','HEALTH','EMERGENCY','OTHER_RESERVED'
);

CREATE TYPE sender_id_registry.audit_entity AS ENUM (
  'SENDER_ID','KYC_DOC','KYC_DOC_VIEW','VERIFICATION',
  'RESTRICTED_PATTERN','REGULATOR_EXPORT','REPUTATION'
);

CREATE TYPE sender_id_registry.audit_action AS ENUM (
  'CREATE','UPDATE','APPROVE','REJECT','REQUEST_INFO',
  'SUSPEND','REACTIVATE','REVOKE','VIEW','EXPORT','RESET'
);

-- =====================================================================
--                         SENDER_IDS  (root aggregate)
-- =====================================================================

CREATE TABLE sender_id_registry.sender_ids (
  sender_id_internal_id        UUID PRIMARY KEY,
  value                        TEXT NOT NULL,
  value_normalised             TEXT NOT NULL,                   -- upper(alpha) / canonical short / E.164 long
  type                         sender_id_registry.sender_id_type NOT NULL,
  category                     sender_id_registry.sender_id_category NOT NULL,
  tenant_id                    UUID NOT NULL,
  registrant_org_name          TEXT NOT NULL,
  registrant_contact_email     TEXT NOT NULL,
  registrant_contact_msisdn    TEXT NOT NULL,                   -- E.164
  restricted_pattern_id        UUID,
  required_verification_level  sender_id_registry.verification_level NOT NULL DEFAULT 'DOCUMENT',
  current_verification_level   sender_id_registry.verification_level NOT NULL DEFAULT 'NONE',
  has_domain_dns               BOOLEAN NOT NULL DEFAULT FALSE,
  state                        sender_id_registry.registry_state NOT NULL DEFAULT 'SUBMITTED',
  reserved_until               TIMESTAMPTZ,
  probation_until              TIMESTAMPTZ,
  first_submitted_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  kyc_approved_at              TIMESTAMPTZ,
  verified_at                  TIMESTAMPTZ,
  activated_at                 TIMESTAMPTZ,
  suspended_at                 TIMESTAMPTZ,
  revoked_at                   TIMESTAMPTZ,
  last_reviewed_at             TIMESTAMPTZ,
  last_suspend_reason          TEXT,
  last_revoke_reason           TEXT,
  version                      INT  NOT NULL DEFAULT 1,
  hlc_timestamp                TEXT NOT NULL,                  -- hybrid logical clock for LWW
  created_at                   TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at                   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- One active value per (value_normalised, type) — partial unique excluding REVOKED rows
-- (REVOKED rows are kept for the 12-month reservation lookup)
CREATE UNIQUE INDEX ux_sender_ids_active_value
  ON sender_id_registry.sender_ids (value_normalised, type)
  WHERE state IN ('SUBMITTED','KYC_REVIEW','KYC_APPROVED','INFO_REQUESTED','VERIFIED','ACTIVE','SUSPENDED');

CREATE INDEX ix_sender_ids_tenant
  ON sender_id_registry.sender_ids (tenant_id, state);
CREATE INDEX ix_sender_ids_state
  ON sender_id_registry.sender_ids (state, first_submitted_at);
CREATE INDEX ix_sender_ids_pattern
  ON sender_id_registry.sender_ids (restricted_pattern_id) WHERE restricted_pattern_id IS NOT NULL;
CREATE INDEX ix_sender_ids_reserved
  ON sender_id_registry.sender_ids (reserved_until) WHERE state = 'REVOKED';

-- Fast public-search & verify hot paths
CREATE INDEX ix_sender_ids_value_norm
  ON sender_id_registry.sender_ids (value_normalised text_pattern_ops, type)
  WHERE state IN ('ACTIVE','SUSPENDED');

ALTER TABLE sender_id_registry.sender_ids ENABLE ROW LEVEL SECURITY;

-- =====================================================================
--                          KYC DOCUMENTS
-- =====================================================================

CREATE TABLE sender_id_registry.kyc_documents (
  kyc_doc_id              UUID PRIMARY KEY,
  sender_id_internal_id   UUID NOT NULL REFERENCES sender_id_registry.sender_ids(sender_id_internal_id) ON DELETE RESTRICT,
  doc_type                sender_id_registry.kyc_doc_type NOT NULL,
  s3_ref                  TEXT NOT NULL,
  mime_type               TEXT NOT NULL,
  size_bytes              INT  NOT NULL CHECK (size_bytes > 0 AND size_bytes <= 26214400),  -- 25 MB
  sha256_hex              TEXT NOT NULL,
  encryption_key_id       TEXT NOT NULL,                     -- Vault Transit ref
  uploaded_by             UUID NOT NULL,
  uploaded_at             TIMESTAMPTZ NOT NULL DEFAULT now(),
  verification_outcome    sender_id_registry.kyc_outcome NOT NULL DEFAULT 'PENDING',
  verification_notes      TEXT,
  tombstoned_at           TIMESTAMPTZ                          -- set on GDPR erasure
);

CREATE INDEX ix_kyc_docs_sender   ON sender_id_registry.kyc_documents (sender_id_internal_id);
CREATE INDEX ix_kyc_docs_doctype  ON sender_id_registry.kyc_documents (sender_id_internal_id, doc_type);
CREATE INDEX ix_kyc_docs_pending  ON sender_id_registry.kyc_documents (verification_outcome) WHERE verification_outcome = 'PENDING';

ALTER TABLE sender_id_registry.kyc_documents ENABLE ROW LEVEL SECURITY;

-- =====================================================================
--                          VERIFICATIONS
-- =====================================================================

CREATE TABLE sender_id_registry.verifications (
  verification_id         UUID PRIMARY KEY,
  sender_id_internal_id   UUID NOT NULL REFERENCES sender_id_registry.sender_ids(sender_id_internal_id) ON DELETE RESTRICT,
  method                  sender_id_registry.verification_method NOT NULL,
  challenge               JSONB NOT NULL,                     -- method-specific payload
  attempts                INT  NOT NULL DEFAULT 0,
  max_attempts            INT  NOT NULL,
  state                   sender_id_registry.verification_state NOT NULL DEFAULT 'PENDING',
  expires_at              TIMESTAMPTZ NOT NULL,
  level_on_success        sender_id_registry.verification_level NOT NULL,
  attempted_by            UUID NOT NULL,
  primary_reviewer_id     UUID,                               -- for NOTARISED dual-control
  secondary_reviewer_id   UUID,
  succeeded_at            TIMESTAMPTZ,
  failed_at               TIMESTAMPTZ,
  failure_reason          TEXT,
  created_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT verifications_attempts_le_max CHECK (attempts <= max_attempts),
  CONSTRAINT verifications_dual_control_distinct
    CHECK (method != 'NOTARISED' OR primary_reviewer_id IS NULL OR secondary_reviewer_id IS NULL OR primary_reviewer_id <> secondary_reviewer_id)
);

CREATE INDEX ix_verif_sender    ON sender_id_registry.verifications (sender_id_internal_id, state);
CREATE INDEX ix_verif_state_exp ON sender_id_registry.verifications (state, expires_at) WHERE state IN ('PENDING','IN_PROGRESS');

ALTER TABLE sender_id_registry.verifications ENABLE ROW LEVEL SECURITY;

-- =====================================================================
--                       RESTRICTED PATTERNS
-- =====================================================================

CREATE TABLE sender_id_registry.restricted_patterns (
  pattern_id                    UUID PRIMARY KEY,
  pattern                       TEXT NOT NULL,                -- re2-compatible
  category                      sender_id_registry.restricted_category NOT NULL,
  required_verification_level   sender_id_registry.verification_level NOT NULL DEFAULT 'NOTARISED',
  required_doc_types            sender_id_registry.kyc_doc_type[] NOT NULL DEFAULT ARRAY['REGULATOR_LETTER','NOTARISED_AUTHORITY']::sender_id_registry.kyc_doc_type[],
  regulator_ref                 TEXT,
  is_active                     BOOLEAN NOT NULL DEFAULT TRUE,
  notes                         TEXT,
  version                       INT  NOT NULL DEFAULT 1,
  created_by                    UUID NOT NULL,
  updated_by                    UUID NOT NULL,
  created_at                    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at                    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX ix_restricted_active ON sender_id_registry.restricted_patterns (is_active);

-- =====================================================================
--                  REPUTATION HISTORY  (append-only, partitioned)
-- =====================================================================

CREATE TABLE sender_id_registry.reputation_history (
  sender_id_internal_id   UUID NOT NULL,
  snapshot_at             TIMESTAMPTZ NOT NULL,
  score                   SMALLINT NOT NULL CHECK (score >= 0 AND score <= 100),
  band                    TEXT NOT NULL,                       -- AUTO_SUSPEND/POOR/NEUTRAL/GOOD/EXCELLENT (denormalised for fast group-by)
  compliance_hits_7d      INT  NOT NULL DEFAULT 0,
  complaints_7d           INT  NOT NULL DEFAULT 0,
  fraud_hits_7d           INT  NOT NULL DEFAULT 0,
  delivery_rate_7d        NUMERIC(5,4) NOT NULL DEFAULT 1.0000,
  delta                   SMALLINT NOT NULL,
  trigger                 TEXT NOT NULL,                       -- DAILY_CRON / FRAUD_EVENT / COMPLIANCE_BLOCK / MANUAL_RESET
  trigger_event_id        UUID,
  PRIMARY KEY (snapshot_at, sender_id_internal_id)
) PARTITION BY RANGE (snapshot_at);

-- Provision next 3 monthly partitions (e.g. 2026-04, 2026-05, 2026-06):
-- CREATE TABLE sender_id_registry.reputation_history_2026_04
--   PARTITION OF sender_id_registry.reputation_history
--   FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

CREATE INDEX ix_rep_sender_time
  ON sender_id_registry.reputation_history (sender_id_internal_id, snapshot_at DESC);
CREATE INDEX ix_rep_band_time
  ON sender_id_registry.reputation_history (band, snapshot_at DESC);

-- =====================================================================
--                       REGULATOR EXPORTS
-- =====================================================================

CREATE TABLE sender_id_registry.regulator_exports (
  export_id           UUID PRIMARY KEY,
  exported_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  window_from         TIMESTAMPTZ NOT NULL,
  window_to           TIMESTAMPTZ NOT NULL,
  format              TEXT NOT NULL CHECK (format IN ('JSONL','CSV','XML_ATRA_v1')),
  s3_ref              TEXT NOT NULL,
  s3_ref_signature    TEXT NOT NULL,
  row_count           INT NOT NULL,
  transmitted_to      TEXT,
  transmitted_at      TIMESTAMPTZ,
  acknowledgment_ref  TEXT,
  triggered_by        TEXT NOT NULL CHECK (triggered_by IN ('CRON','ON_DEMAND','REGULATOR_REQUEST')),
  triggered_by_user   UUID
);

CREATE INDEX ix_exports_at ON sender_id_registry.regulator_exports (exported_at DESC);

-- =====================================================================
--                    AUDIT LOG  (append-only, partitioned)
-- =====================================================================

CREATE TABLE sender_id_registry.audit (
  audit_id        UUID NOT NULL,
  entity_type     sender_id_registry.audit_entity NOT NULL,
  entity_id       UUID NOT NULL,
  action          sender_id_registry.audit_action NOT NULL,
  actor_user_id   UUID NOT NULL,
  actor_role      TEXT NOT NULL,
  before          JSONB,
  after           JSONB,
  reason          TEXT,
  ip              INET,
  user_agent      TEXT,
  trace_id        TEXT,
  occurred_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (occurred_at, audit_id)
) PARTITION BY RANGE (occurred_at);

CREATE INDEX ix_audit_entity ON sender_id_registry.audit (entity_type, entity_id, occurred_at DESC);
CREATE INDEX ix_audit_actor  ON sender_id_registry.audit (actor_user_id, occurred_at DESC);

-- =====================================================================
--                            OUTBOX
-- =====================================================================

CREATE TABLE sender_id_registry.outbox (
  event_id     UUID PRIMARY KEY,
  subject      TEXT NOT NULL,
  payload      JSONB NOT NULL,
  published_at TIMESTAMPTZ,
  attempts     INT  NOT NULL DEFAULT 0,
  last_error   TEXT,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX ix_outbox_unpublished
  ON sender_id_registry.outbox (created_at) WHERE published_at IS NULL;

2. Redis keys

All keys namespaced sid:. Tenant-scoped keys encode {tenantId} to support per-tenant cache invalidation.

Key	TTL	Purpose
`sid:verify:{value}:{type}:{tenantId}`	300 s	`Verify` gRPC hot cache (UC-08)
`sid:verify:{value}:{type}:any`	300 s	Fallback when caller has not provided tenant context
`sid:reputation:{senderIdInternalId}`	86 400 s	`GetReputation` hot cache (UC-09)
`sid:public_search:{normalisedQuery}`	300 s	Public search hot cache (UC-13)
`sid:public_search:rl:{ip}:{minute}`	60 s	Per-IP rate-limit counter
`sid:otp:{verificationId}`	300 s	OTP plaintext for comparison (UC-04)
`sid:otp:rl:{registrantMsisdn}:{hour}`	3600 s	OTP issuance rate-limit (3/hour)
`sid:idem:{tenantId}:{idempotencyKey}`	86 400 s	Submission idempotency
`sid:reputation:lock:daily`	7200 s	Cron distributed lock
`sid:export:lock`	1800 s	Regulator export distributed lock
`sid:dns:resolver:cache:{domain}`	300 s	DoT resolver answer cache
`sid:notary:whitelist:v`	300 s	In-process notary-whitelist version marker
`sid:restricted:patterns:v`	300 s	In-process restricted-pattern version marker

3. Row-Level Security & append-only protection

3.1 Append-only on `audit` and `reputation_history`

CREATE RULE audit_no_update AS
  ON UPDATE TO sender_id_registry.audit DO INSTEAD NOTHING;
CREATE RULE audit_no_delete AS
  ON DELETE TO sender_id_registry.audit DO INSTEAD NOTHING;

CREATE RULE rep_no_update AS
  ON UPDATE TO sender_id_registry.reputation_history DO INSTEAD NOTHING;
CREATE RULE rep_no_delete AS
  ON DELETE TO sender_id_registry.reputation_history DO INSTEAD NOTHING;

Retention is managed by dropping old partitions, never DELETE.

3.2 Row-level security

-- Tenants can only see their own sender-IDs and KYC documents
CREATE POLICY sender_ids_tenant_read ON sender_id_registry.sender_ids
  FOR SELECT
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

CREATE POLICY sender_ids_admin_all ON sender_id_registry.sender_ids
  USING (current_setting('app.caller_role') IN (
    'platform.sid.admin','platform.sid.reviewer','platform.auditor','platform.regulator'
  ));

CREATE POLICY kyc_docs_tenant_read ON sender_id_registry.kyc_documents
  FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM sender_id_registry.sender_ids s
      WHERE s.sender_id_internal_id = kyc_documents.sender_id_internal_id
        AND s.tenant_id = current_setting('app.current_tenant_id')::uuid
    )
  );

CREATE POLICY kyc_docs_admin_all ON sender_id_registry.kyc_documents
  USING (current_setting('app.caller_role') IN (
    'platform.sid.admin','platform.sid.reviewer'
  ));

-- Verifications follow same pattern (kyc-doc style join via sender_id_internal_id)

The tenant portal handler sets SET LOCAL app.current_tenant_id = '…' from the Kong-injected X-Tenant-Id; admin handlers set app.caller_role. Regulator export caller sets app.caller_role = 'platform.regulator' to read the projected non-PII columns only.

4. ID prefixes

Prefix	Entity
`sid_`	SenderId
`kyc_`	KycDocument
`vrf_`	Verification
`rp_`	RestrictedPattern
`rep_`	ReputationSnapshot id (when emitted externally)
`exp_`	RegulatorExport
`aud_`	AuditEntry

Entities use UUIDv4 internally; prefixes apply only to externally-exposed identifiers on REST responses.

5. PII, encryption & retention

Field	Class	Protection
`kyc_documents.s3_ref` (object content)	RESTRICTED (KYC PII / financial / national-ID)	Encrypted at rest in object storage with per-tenant KEK (Vault Transit envelope, AES-256-GCM); 15-min signed URLs with watermarking sidecar
`kyc_documents.sha256_hex`	INTERNAL (integrity hash)	Plain
`sender_ids.registrant_contact_email`	CONFIDENTIAL	TLS in transit; not exported in regulator file; not in events
`sender_ids.registrant_contact_msisdn`	CONFIDENTIAL	Same; masked in admin UI list view (`+9370****`); full visible on detail to admin only
`sender_ids.registrant_org_name`	PUBLIC	This is the public face of the registry (US-SID-015)
`verifications.challenge.otp_hash`	CONFIDENTIAL	SHA-256 only — plaintext OTP exists 5 min in Redis
`audit.before/after`	CONFIDENTIAL (may contain PII excerpts)	Same masking rules as live data on read
`reputation_history.*`	INTERNAL (aggregates)	Plain

Retention.

audit: ≥ 13 months hot (regulatory), then cold archive in object storage WORM bucket for ≥ 7 years.
reputation_history: 25 months hot (covers 2-year trend analysis); cold-archive thereafter.
kyc_documents: lifetime of SenderId + 7 years post-revoke (regulatory). Tombstoned on GDPR erasure but blob overwritten with sealed redaction marker.
regulator_exports: 7 years (regulator obligation).
outbox: rows deleted 7 days after published_at.

6. Migration strategy

Partitions for audit and reputation_history are provisioned 3 months ahead by PartitionMaintenanceWorker (daily 03:00 UTC). Missing future partitions trigger a HIGH alert.
Retention enforced by dropping old partitions, never DELETE.
Schema changes follow the expand/contract pattern; all migrations forward-only and reviewed by Security for any column holding PII.
Default seed (per Phase-0 of MIGRATION_PLAN) inserts the restricted_patterns set at deploy time via a dedicated migration (seed_restricted_patterns_v1.sql) signed off by Legal.

7. Cross-region replication contract

Per ADR-0004 §14:

Table	Replication	Conflict policy
`sender_ids`	Logical replication `kbl ↔ mzr`	LWW on `(value_normalised, type)` keyed by `hlc_timestamp`
`kyc_documents`	Logical replication `kbl ↔ mzr`	Append-only — both regions can insert; conflict resolved by `kyc_doc_id` UUID uniqueness
`verifications`	Logical replication	LWW on `verification_id`
`restricted_patterns`	Logical replication	LWW on `pattern_id`
`reputation_history`	Logical replication, append-only	Both regions can compute; deduplication on `(snapshot_at, sender_id_internal_id, trigger_event_id)`
`regulator_exports`	Logical replication	One region owns export at a time (region-pinned via Redis lock); other region records receipt only
`audit`	Append-only logical replication	Deduplication on `audit_id`
`outbox`	NOT replicated	Outbox is per-region; events themselves replicate via NATS stream mirror

8. Performance & sizing

Estimated steady-state at national scale (5 MNOs × ~5 M MSISDNs each):

Metric	Estimate
Active sender-IDs	50 000 – 500 000
New submissions per day	100 – 500
`Verify` gRPC RPS (peak)	5 000 – 15 000 (mirrors message throughput)
`GetReputation` gRPC RPS (peak)	1 000 – 3 000
KYC documents per sender-ID	2 – 5
KYC document avg size	1 – 3 MB; max 25 MB
`audit` rows / day	~10 000 (state changes + KYC views)
`reputation_history` rows / day	~ N(active) + delta-event count ≈ 100 000

Postgres sizing: 2 vCPU / 8 GB / NVMe per region for the first 18 months; horizontal read replicas for Verify cold-miss path if needed (most reads served from Redis).

1. Tables​

2. Redis keys​

3. Row-Level Security & append-only protection​

3.1 Append-only on audit and reputation_history​

3.2 Row-level security​

4. ID prefixes​

5. PII, encryption & retention​

6. Migration strategy​

7. Cross-region replication contract​

8. Performance & sizing​