regulator-portal-service — Data Model

Version: 1.0 Status: Draft Owner: Regulator-facing + Legal Last Updated: 2026-04-21 Companion: DOMAIN_MODEL · SECURITY_MODEL · EVENT_SCHEMAS

Schema: regulator. Owned exclusively by regulator-portal-service. No other service reads or writes these tables directly — integration is via REST, NATS events, or read-through queries to upstream services.

---

1. PostgreSQL DDL

CREATE SCHEMA IF NOT EXISTS regulator;

-- =====================================================================
--                           ENUM TYPES
-- =====================================================================

CREATE TYPE regulator.user_status  AS ENUM ('ACTIVE','SUSPENDED','REVOKED');
CREATE TYPE regulator.regulator_role AS ENUM (
  'regulator-read','regulator-li','regulator-auditor','external-auditor'
);
CREATE TYPE regulator.li_scope     AS ENUM ('IRI','CC','FULL');
CREATE TYPE regulator.li_state     AS ENUM (
  'RECEIVED','ACK','IN_PROGRESS','DELIVERED','CLOSED','REJECTED'
);
CREATE TYPE regulator.li_action    AS ENUM (
  'SUBMIT','ACK','START','DELIVER','CLOSE','REJECT','NOTE'
);
CREATE TYPE regulator.complaint_type AS ENUM (
  'UNSOLICITED_SMS','FRAUD','PHISHING','BILLING_DISPUTE','DATA_PRIVACY',
  'SENDER_ID_ABUSE','OTHER'
);
CREATE TYPE regulator.complaint_state AS ENUM (
  'RECEIVED','TRIAGED','ASSIGNED','RESOLVED','CLOSED','REJECTED'
);
CREATE TYPE regulator.report_type  AS ENUM (
  'DAILY_CDR_STATUS','MONTHLY_COMPLIANCE_SUMMARY',
  'QUARTERLY_PLATFORM_HEALTH','AD_HOC'
);
CREATE TYPE regulator.report_status AS ENUM ('PENDING','RUNNING','READY','FAILED');
CREATE TYPE regulator.siem_kind    AS ENUM ('SPLUNK_HEC','LOGSTASH_HTTP','QRADAR_SYSLOG');
CREATE TYPE regulator.siem_format  AS ENUM ('CEF','LEEF','RAW_JSON');
CREATE TYPE regulator.siem_delivery_status AS ENUM ('RETRYING','ACKED','DEADLETTERED');
CREATE TYPE regulator.framework    AS ENUM (
  'ISO_27001','ISO_27017','ISO_27018','SOC2_TYPE_II','GSMA_AA_18'
);
CREATE TYPE regulator.evidence_status AS ENUM ('CURRENT','STALE','MISSING');
CREATE TYPE regulator.evidence_type AS ENUM (
  'AUTO_SBOM','AUTO_CI_RESULT','AUTO_ACCESS_REVIEW','AUTO_IMAGE_SIGNATURE','MANUAL_UPLOAD'
);
CREATE TYPE regulator.auditor_access_state AS ENUM (
  'GRANTED','EXPIRED','REVOKED'
);

-- =====================================================================
--                              USERS
-- =====================================================================

CREATE TABLE regulator.users (
  user_id                 UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  cert_subject_dn         TEXT NOT NULL,
  cert_fingerprint_sha256 BYTEA NOT NULL,
  issuer_dn               TEXT NOT NULL,
  org_name                TEXT NOT NULL,
  role                    regulator.regulator_role NOT NULL,
  allowed_regions         TEXT[] NOT NULL DEFAULT '{}',
  status                  regulator.user_status NOT NULL DEFAULT 'ACTIVE',
  created_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_login_at           TIMESTAMPTZ,
  revoked_at              TIMESTAMPTZ,
  UNIQUE (cert_subject_dn, issuer_dn)
);
CREATE INDEX ix_users_fp ON regulator.users (cert_fingerprint_sha256);
CREATE INDEX ix_users_status ON regulator.users (status) WHERE status <> 'REVOKED';

CREATE TABLE regulator.users_audit (
  audit_id     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id      UUID,
  action       TEXT NOT NULL,          -- LOGIN / LOGIN_FAILED / REVOKED / ROLE_CHANGED
  sub_reason   TEXT,
  ip           INET,
  user_agent   TEXT,
  trace_id     TEXT,
  occurred_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_users_audit_user ON regulator.users_audit (user_id, occurred_at DESC);

-- =====================================================================
--                           LI REQUESTS
-- =====================================================================

CREATE TABLE regulator.li_requests (
  li_request_id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  regulator_user_id          UUID NOT NULL REFERENCES regulator.users(user_id),
  org_name                   TEXT NOT NULL,
  target_msisdn_ciphertext   BYTEA NOT NULL,       -- Vault Transit envelope
  target_msisdn_masked       TEXT NOT NULL,        -- +CCNNN***
  date_range_from            TIMESTAMPTZ NOT NULL,
  date_range_to              TIMESTAMPTZ NOT NULL,
  scope                      regulator.li_scope NOT NULL,
  legal_ref                  TEXT NOT NULL,
  signed_warrant_hash        BYTEA NOT NULL,
  warrant_object_ref         TEXT NOT NULL,
  state                      regulator.li_state NOT NULL DEFAULT 'RECEIVED',
  ack_by                     TIMESTAMPTZ NOT NULL,
  in_progress_by             TIMESTAMPTZ NOT NULL,
  deliver_by                 TIMESTAMPTZ NOT NULL,
  close_by                   TIMESTAMPTZ,
  initiator_user_id          UUID,
  approver_user_id           UUID,
  delivery_package_ref       TEXT,
  delivery_sftp_receipt_at   TIMESTAMPTZ,
  created_at                 TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at                 TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT chk_date_range CHECK (date_range_from < date_range_to),
  CONSTRAINT chk_dual_control CHECK (initiator_user_id IS NULL OR approver_user_id IS NULL OR initiator_user_id <> approver_user_id)
);
CREATE INDEX ix_li_state_deliver_by ON regulator.li_requests (state, deliver_by)
  WHERE state NOT IN ('CLOSED','REJECTED');
CREATE INDEX ix_li_regulator ON regulator.li_requests (regulator_user_id, created_at DESC);

-- Append-only hash-chained audit
CREATE TABLE regulator.li_audit (
  audit_id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  li_request_id     UUID NOT NULL REFERENCES regulator.li_requests(li_request_id),
  from_state        regulator.li_state,
  to_state          regulator.li_state,
  action            regulator.li_action NOT NULL,
  initiator_user_id UUID,
  approver_user_id  UUID,
  rationale         TEXT,
  hash_prev         BYTEA NOT NULL,
  hash_self         BYTEA NOT NULL,
  occurred_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_li_audit_request_time ON regulator.li_audit (li_request_id, occurred_at);

CREATE RULE li_audit_no_update AS ON UPDATE TO regulator.li_audit DO INSTEAD NOTHING;
CREATE RULE li_audit_no_delete AS ON DELETE TO regulator.li_audit DO INSTEAD NOTHING;

-- =====================================================================
--                            COMPLAINTS
-- =====================================================================

CREATE TABLE regulator.complaints (
  complaint_id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  regulator_ref             TEXT NOT NULL,
  org_name                  TEXT NOT NULL,
  citizen_msisdn_ciphertext BYTEA NOT NULL,
  citizen_msisdn_masked     TEXT NOT NULL,
  complaint_type            regulator.complaint_type NOT NULL,
  summary                   TEXT NOT NULL,
  received_at               TIMESTAMPTZ NOT NULL,
  state                     regulator.complaint_state NOT NULL DEFAULT 'RECEIVED',
  sla_due_at                TIMESTAMPTZ NOT NULL,
  resolver_tenant_id        UUID,
  resolution_summary        TEXT,
  triage_ai_category        TEXT,
  triage_ai_confidence      NUMERIC(4,3),
  created_at                TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at                TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT chk_resolution_on_closed CHECK (
    state NOT IN ('RESOLVED','CLOSED') OR resolution_summary IS NOT NULL
  )
);
CREATE INDEX ix_complaints_state_sla ON regulator.complaints (state, sla_due_at)
  WHERE state IN ('RECEIVED','TRIAGED','ASSIGNED');
CREATE INDEX ix_complaints_org ON regulator.complaints (org_name, received_at DESC);

CREATE TABLE regulator.complaint_triage (
  triage_id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  complaint_id   UUID NOT NULL REFERENCES regulator.complaints(complaint_id),
  triaged_by     UUID NOT NULL,
  notes          TEXT,
  assigned_to    UUID,
  triaged_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- =====================================================================
--                      SCHEDULED + GENERATED REPORTS
-- =====================================================================

CREATE TABLE regulator.scheduled_reports (
  scheduled_report_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name                TEXT NOT NULL UNIQUE,
  report_type         regulator.report_type NOT NULL,
  cron_expr           TEXT NOT NULL,
  timezone            TEXT NOT NULL DEFAULT 'Asia/Kabul',
  filters             JSONB NOT NULL DEFAULT '{}',
  recipient_emails    TEXT[] NOT NULL DEFAULT '{}',
  enabled             BOOLEAN NOT NULL DEFAULT TRUE,
  last_run_at         TIMESTAMPTZ,
  last_status         regulator.report_status,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE regulator.generated_reports (
  report_job_id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  scheduled_report_id   UUID REFERENCES regulator.scheduled_reports(scheduled_report_id),
  report_type           regulator.report_type NOT NULL,
  filters               JSONB NOT NULL DEFAULT '{}',
  status                regulator.report_status NOT NULL DEFAULT 'PENDING',
  output_ref            TEXT,
  signature_ref         TEXT,
  signing_key_ref       TEXT,
  error_message         TEXT,
  requested_by_user_id  UUID NOT NULL,
  requested_by_org      TEXT NOT NULL,
  requested_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  completed_at          TIMESTAMPTZ
);
CREATE INDEX ix_reports_status ON regulator.generated_reports (status, requested_at DESC);
CREATE INDEX ix_reports_org ON regulator.generated_reports (requested_by_org, requested_at DESC);

-- =====================================================================
--                      SIEM DESTINATIONS + DELIVERY LOG
-- =====================================================================

CREATE TABLE regulator.siem_destinations (
  destination_id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name              TEXT NOT NULL UNIQUE,
  kind              regulator.siem_kind NOT NULL,
  endpoint_url      TEXT NOT NULL,
  format            regulator.siem_format NOT NULL,
  auth_ref          TEXT NOT NULL,         -- Vault path
  filter            JSONB NOT NULL DEFAULT '{}',
  enabled           BOOLEAN NOT NULL DEFAULT TRUE,
  wal_threshold     INT NOT NULL DEFAULT 100000,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE regulator.siem_delivery_log (
  log_id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  destination_id UUID NOT NULL REFERENCES regulator.siem_destinations(destination_id),
  source_event_id UUID NOT NULL,
  source_subject TEXT NOT NULL,
  status         regulator.siem_delivery_status NOT NULL DEFAULT 'RETRYING',
  attempts       INT NOT NULL DEFAULT 0,
  last_err       TEXT,
  delivery_latency_ms INT,
  acked_at       TIMESTAMPTZ,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);
CREATE INDEX ix_siem_delivery_status ON regulator.siem_delivery_log (destination_id, status);
-- Monthly partitions provisioned 3 months ahead by cron.

-- =====================================================================
--                      ATTESTATIONS + EVIDENCE + BUNDLES
-- =====================================================================

CREATE TABLE regulator.attestations (
  attestation_id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  framework        regulator.framework NOT NULL,
  control_id       TEXT NOT NULL,
  title            TEXT NOT NULL,
  description      TEXT,
  owner_service    TEXT NOT NULL,
  evidence_type    regulator.evidence_type NOT NULL,
  UNIQUE (framework, control_id)
);

CREATE TABLE regulator.attestation_evidence (
  evidence_id       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  framework         regulator.framework NOT NULL,
  control_id        TEXT NOT NULL,
  evidence_ref      TEXT NOT NULL,
  manifest_hash     BYTEA NOT NULL,
  status            regulator.evidence_status NOT NULL DEFAULT 'CURRENT',
  last_review_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  next_review_due_at TIMESTAMPTZ NOT NULL,
  collected_by      TEXT NOT NULL,          -- service/user
  FOREIGN KEY (framework, control_id) REFERENCES regulator.attestations(framework, control_id)
);
CREATE INDEX ix_ev_status ON regulator.attestation_evidence (status, next_review_due_at);
CREATE INDEX ix_ev_fw_ctrl ON regulator.attestation_evidence (framework, control_id, last_review_at DESC);

CREATE TABLE regulator.attestation_bundles (
  bundle_id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  framework          regulator.framework NOT NULL,
  year               INT NOT NULL,
  bundle_ref         TEXT NOT NULL,
  signature_ref      TEXT NOT NULL,
  controls_included  INT NOT NULL,
  evidence_files     INT NOT NULL,
  generated_by       UUID NOT NULL,
  generated_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (framework, year)
);

-- =====================================================================
--                     AUDITOR ACCESS (time-boxed)
-- =====================================================================

CREATE TABLE regulator.auditor_access (
  auditor_id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  firm_name          TEXT NOT NULL,
  cert_subject_dn    TEXT NOT NULL,
  cert_fingerprint_sha256 BYTEA NOT NULL,
  issuer_dn          TEXT NOT NULL,
  granted_frameworks regulator.framework[] NOT NULL,
  state              regulator.auditor_access_state NOT NULL DEFAULT 'GRANTED',
  access_granted_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  access_expires_at  TIMESTAMPTZ NOT NULL,
  granted_by         UUID NOT NULL,
  revoked_at         TIMESTAMPTZ,
  UNIQUE (cert_subject_dn, issuer_dn)
);
CREATE INDEX ix_auditor_access_state_exp ON regulator.auditor_access (state, access_expires_at);

CREATE TABLE regulator.auditor_access_audit (
  audit_id     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  auditor_id   UUID NOT NULL REFERENCES regulator.auditor_access(auditor_id),
  action       TEXT NOT NULL,          -- READ / DOWNLOAD
  resource_ref TEXT,
  ip           INET,
  user_agent   TEXT,
  occurred_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_auditor_audit_auditor ON regulator.auditor_access_audit (auditor_id, occurred_at DESC);

CREATE RULE auditor_audit_no_update AS ON UPDATE TO regulator.auditor_access_audit DO INSTEAD NOTHING;
CREATE RULE auditor_audit_no_delete AS ON DELETE TO regulator.auditor_access_audit DO INSTEAD NOTHING;

-- =====================================================================
--                       SIGNING KEYS REFERENCE
-- =====================================================================

CREATE TABLE regulator.signing_keys_ref (
  key_id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  purpose        TEXT NOT NULL UNIQUE,   -- regulator-reports / regulator-li / attestation-bundle
  vault_path     TEXT NOT NULL,          -- Vault Transit or PKCS#11 slot:label
  algorithm      TEXT NOT NULL,          -- SHA256-RSA-PSS-4096
  cert_chain_pem TEXT NOT NULL,
  active         BOOLEAN NOT NULL DEFAULT TRUE,
  rotated_at     TIMESTAMPTZ,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

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

CREATE TABLE regulator.outbox (
  event_id      UUID PRIMARY KEY,
  subject       TEXT NOT NULL,
  payload       JSONB NOT NULL,
  published_at  TIMESTAMPTZ,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_outbox_unpublished ON regulator.outbox (created_at) WHERE published_at IS NULL;

---

2. Row-Level Security

ALTER TABLE regulator.li_requests       ENABLE ROW LEVEL SECURITY;
ALTER TABLE regulator.complaints        ENABLE ROW LEVEL SECURITY;
ALTER TABLE regulator.generated_reports ENABLE ROW LEVEL SECURITY;

-- Regulators see only their org's rows
CREATE POLICY li_regulator_scope ON regulator.li_requests
  FOR SELECT
  USING (
    org_name = current_setting('app.current_org_name', true)
    AND current_setting('app.caller_role', true) IN ('regulator-li','regulator-read')
  );

CREATE POLICY complaints_regulator_scope ON regulator.complaints
  FOR SELECT
  USING (
    org_name = current_setting('app.current_org_name', true)
  );

CREATE POLICY reports_regulator_scope ON regulator.generated_reports
  FOR SELECT
  USING (
    requested_by_org = current_setting('app.current_org_name', true)
  );

-- Platform admins bypass
CREATE POLICY all_admin ON regulator.li_requests
  USING (current_setting('app.caller_role', true) = 'platform.regulator.admin');

-- Identical admin policies added to complaints, reports

---

3. S3 Buckets + Object Lock

Bucket	Prefix	Retention	Lock Mode
ghasi-regulator	li/warrants/	7 years	COMPLIANCE
ghasi-regulator	li/deliveries/	7 years	COMPLIANCE
ghasi-regulator	reports/{yyyy}/{mm}/	7 years	COMPLIANCE
ghasi-regulator	evidence/{framework}/{controlId}/	7 years	COMPLIANCE
ghasi-regulator	bundles/{framework}/{year}/	Permanent	COMPLIANCE

Server-side encryption: SSE-KMS with bucket-scoped KMS key, per-object DEK. Bucket versioning enabled. Bucket policy forbids s3:DeleteObject from any identity (object-lock enforces this further).

---

4. Redis Key Namespaces

Key	TTL	Purpose
regulator:session:{token}	30 min sliding	Regulator mTLS session
regulator:auditor:session:{token}	30 min sliding	Auditor session
regulator:crl:{issuerDn}	15 min	CRL cache
regulator:ocsp:{certSha256}	10 min	OCSP response cache
regulator:report:lock:{reportJobId}	30 min	Dispatcher lock
regulator:evidence:lock:collect	60 min	Evidence cron lock
regulator:siem:lock:{destinationId}	5 min	Per-destination drainer lock
regulator:ratelimit:{userId}:{endpoint}	window	Envoy local rate-limit data

---

5. ID Prefixes

Prefix	Entity
li_	LiRequest
comp_	Complaint
rpt_	ReportJob
sched_	ScheduledReport
siem_	SiemDestination
ev_	AttestationEvidence
bndl_	AttestationBundle
aud_	AuditorAccess

Internal UUIDv4 persists in DB; prefixes only applied in externally-exposed REST/event IDs.

---

6. Retention

Table	Retention	Mechanism
users / users_audit	13 months active; 7y archive	partition drop + S3 archive
li_requests / li_audit	7 years (regulator mandate)	object-lock + no delete
complaints / complaint_triage	7 years	same
generated_reports	7 years	S3 object-lock
siem_delivery_log	30 days	monthly partition drop
attestation_evidence	7 years	S3 object-lock
attestation_bundles	permanent	S3 object-lock permanent
auditor_access_audit	13 months	append-only; partition drop
outbox	7 days post-publish	cron prune

---

7. Migration & Partitioning

• Partitions provisioned 3 months ahead for siem_delivery_log, users_audit, auditor_access_audit.
• Schema migrations via Prisma; forward-only; all PII-carrying column additions reviewed by Security.
• Retention enforced by dropping partitions and S3 lifecycle, never via DELETE.