Compliance Engine — Data Model

Status: populated Owner: Platform Engineering / Trust & Safety Last updated: 2026-04-19 Companion: DOMAIN_MODEL · SECURITY_MODEL

Schema: compliance. Owned exclusively by compliance-engine. No other service reads or writes these tables directly — integration is over gRPC (EvaluateCompliance), HTTP REST (admin), or NATS events.

1. Tables

-- =====================================================================
--                              RULES
-- =====================================================================

CREATE TYPE compliance.rule_type AS ENUM (
  'KEYWORD','REGEX','SENDER_ID','RECIPIENT','RATE_VOLUME',
  'GEO_RESTRICTION','TEMPORAL','DLR_ABUSE','AI_CLASSIFICATION','COMPOSITE'
);

CREATE TYPE compliance.verdict AS ENUM ('ALLOW','FLAG','HOLD','BLOCK');

CREATE TABLE compliance.rules (
  rule_id       UUID PRIMARY KEY,
  name          TEXT NOT NULL,
  description   TEXT,
  type          compliance.rule_type NOT NULL,
  action        compliance.verdict NOT NULL,
  priority      INT  NOT NULL DEFAULT 1000,
  config        JSONB NOT NULL,                 -- type-specific; validated on write
  is_active     BOOLEAN NOT NULL DEFAULT TRUE,
  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(),
  deleted_at    TIMESTAMPTZ
);
CREATE INDEX ix_rules_active_priority
  ON compliance.rules (is_active, priority)
  WHERE deleted_at IS NULL;
CREATE INDEX ix_rules_type ON compliance.rules (type) WHERE deleted_at IS NULL;

CREATE TABLE compliance.rule_versions (
  rule_id        UUID NOT NULL,
  version        INT  NOT NULL,
  snapshot       JSONB NOT NULL,
  changed_by     UUID NOT NULL,
  change_reason  TEXT,
  changed_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (rule_id, version)
);

-- =====================================================================
--                        RULE SETS & ASSIGNMENTS
-- =====================================================================

CREATE TYPE compliance.rule_set_status AS ENUM ('draft','active','retired');

CREATE TABLE compliance.rule_sets (
  rule_set_id  UUID PRIMARY KEY,
  name         TEXT NOT NULL UNIQUE,
  description  TEXT,
  is_default   BOOLEAN NOT NULL DEFAULT FALSE,
  rule_ids     UUID[] NOT NULL DEFAULT '{}',    -- ordered
  status       compliance.rule_set_status NOT NULL DEFAULT 'draft',
  version      INT  NOT NULL DEFAULT 1,
  activated_at TIMESTAMPTZ,
  retired_at   TIMESTAMPTZ,
  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 UNIQUE INDEX ux_rule_sets_one_default
  ON compliance.rule_sets (is_default) WHERE is_default = TRUE;

CREATE TABLE compliance.tenant_rule_set_assignments (
  tenant_id    UUID NOT NULL,
  account_id   UUID,
  rule_set_id  UUID NOT NULL REFERENCES compliance.rule_sets(rule_set_id),
  priority     INT  NOT NULL DEFAULT 100,
  assigned_by  UUID NOT NULL,
  assigned_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, account_id, rule_set_id)
);
CREATE INDEX ix_tra_tenant ON compliance.tenant_rule_set_assignments (tenant_id);

-- =====================================================================
--                           HOLD QUEUE
-- =====================================================================

CREATE TYPE compliance.hold_status AS ENUM (
  'PENDING','REVIEWING','REVIEWED_RELEASED','REVIEWED_REJECTED','AUTO_EXPIRED'
);

CREATE TABLE compliance.hold_queue (
  hold_id           UUID PRIMARY KEY,
  message_id        UUID NOT NULL,
  tenant_id         UUID NOT NULL,
  account_id        UUID NOT NULL,
  evaluation_id     UUID NOT NULL,
  payload           JSONB NOT NULL,           -- full MessageContext (encrypted at rest; see SECURITY_MODEL §3)
  trigger_findings  JSONB NOT NULL,
  review_priority   INT  NOT NULL,
  status            compliance.hold_status NOT NULL DEFAULT 'PENDING',
  reviewer_user_id  UUID,
  review_notes      TEXT,
  held_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  reviewed_at       TIMESTAMPTZ,
  auto_expires_at   TIMESTAMPTZ NOT NULL
);
CREATE INDEX ix_hold_status_priority
  ON compliance.hold_queue (status, review_priority DESC, held_at ASC)
  WHERE status IN ('PENDING','REVIEWING');
CREATE INDEX ix_hold_tenant_status
  ON compliance.hold_queue (tenant_id, status);
CREATE INDEX ix_hold_expiry
  ON compliance.hold_queue (auto_expires_at)
  WHERE status = 'PENDING';

-- Row-Level Security: tenants can only read their own holds via the tenant portal API
ALTER TABLE compliance.hold_queue ENABLE ROW LEVEL SECURITY;

-- =====================================================================
--                   EVALUATION LOG  (partitioned monthly)
-- =====================================================================

CREATE TABLE compliance.evaluation_log (
  evaluation_id          UUID NOT NULL,
  message_id             UUID NOT NULL,
  tenant_id              UUID NOT NULL,
  account_id             UUID NOT NULL,
  fingerprint            TEXT NOT NULL,         -- sha256 hex
  verdict                compliance.verdict NOT NULL,
  findings               JSONB NOT NULL DEFAULT '[]',
  rule_set_id            UUID,
  rule_set_version       INT,
  evaluation_latency_ms  INT  NOT NULL,
  budget_exceeded        BOOLEAN NOT NULL DEFAULT FALSE,
  ai_cached              BOOLEAN,
  trace_id               TEXT,
  evaluated_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (evaluated_at, evaluation_id)
) PARTITION BY RANGE (evaluated_at);

-- Example initial monthly partitions (migration provisions next 3 months ahead):
-- CREATE TABLE compliance.evaluation_log_2026_04
--   PARTITION OF compliance.evaluation_log
--   FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

CREATE INDEX ix_eval_tenant_time
  ON compliance.evaluation_log (tenant_id, evaluated_at DESC);
CREATE INDEX ix_eval_fingerprint
  ON compliance.evaluation_log (fingerprint, evaluated_at DESC);

-- =====================================================================
--          TENANT COMPLIANCE SCORES  +  SCORE HISTORY (partitioned)
-- =====================================================================

CREATE TYPE compliance.risk_tier AS ENUM ('CLEAR','MONITOR','RESTRICTED','SUSPENDED');

CREATE TABLE compliance.tenant_compliance_scores (
  tenant_id            UUID PRIMARY KEY,
  overall_score        INT  NOT NULL,
  content_score        INT  NOT NULL,
  volume_score         INT  NOT NULL,
  dlr_score            INT  NOT NULL,
  optout_score         INT  NOT NULL,
  complaint_score      INT  NOT NULL,
  tenure_score         INT  NOT NULL,
  risk_tier            compliance.risk_tier NOT NULL,
  override_tier        compliance.risk_tier,
  override_reason      TEXT,
  override_expires_at  TIMESTAMPTZ,
  override_set_by      UUID,
  messages_sent_7d     BIGINT NOT NULL DEFAULT 0,
  violations_7d        BIGINT NOT NULL DEFAULT 0,
  dlr_success_rate     NUMERIC(5,4) NOT NULL DEFAULT 1.0000,
  optout_rate          NUMERIC(5,4) NOT NULL DEFAULT 0.0000,
  complaint_rate       NUMERIC(5,4) NOT NULL DEFAULT 0.0000,
  last_computed_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_scores_tier ON compliance.tenant_compliance_scores (risk_tier);
CREATE INDEX ix_scores_overall ON compliance.tenant_compliance_scores (overall_score);

CREATE TABLE compliance.score_history (
  tenant_id        UUID NOT NULL,
  computed_at      TIMESTAMPTZ NOT NULL,
  overall_score    INT  NOT NULL,
  risk_tier        compliance.risk_tier NOT NULL,
  dimensions       JSONB NOT NULL,
  PRIMARY KEY (computed_at, tenant_id)
) PARTITION BY RANGE (computed_at);

-- =====================================================================
--                          DLR STATS (abuse rules)
-- =====================================================================

CREATE TYPE compliance.dlr_window AS ENUM ('W1H','W24H','W7D');

CREATE TABLE compliance.dlr_stats (
  tenant_id       UUID NOT NULL,
  account_id      UUID NOT NULL,
  window          compliance.dlr_window NOT NULL,
  total_sent      BIGINT NOT NULL DEFAULT 0,
  total_delivered BIGINT NOT NULL DEFAULT 0,
  total_failed    BIGINT NOT NULL DEFAULT 0,
  total_rejected  BIGINT NOT NULL DEFAULT 0,
  failure_rate    NUMERIC(5,4) NOT NULL DEFAULT 0.0000,
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, account_id, window)
);

-- =====================================================================
--                 BLOCKLISTS + KEYWORD LISTS
-- =====================================================================

CREATE TYPE compliance.blocklist_entity AS ENUM (
  'SENDER_ID','RECIPIENT','KEYWORD','COUNTRY','IP'
);
CREATE TYPE compliance.pattern_type AS ENUM (
  'EXACT','REGEX','PREFIX','CONTAINS','SUFFIX'
);

CREATE TABLE compliance.blocklists (
  blocklist_id  UUID PRIMARY KEY,
  name          TEXT NOT NULL UNIQUE,
  entity        compliance.blocklist_entity NOT NULL,
  description   TEXT,
  is_active     BOOLEAN NOT NULL DEFAULT TRUE,
  created_by    UUID NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE compliance.blocklist_entries (
  entry_id      UUID PRIMARY KEY,
  blocklist_id  UUID NOT NULL REFERENCES compliance.blocklists(blocklist_id) ON DELETE CASCADE,
  value         TEXT NOT NULL,
  pattern_type  compliance.pattern_type NOT NULL DEFAULT 'EXACT',
  note          TEXT,
  expires_at    TIMESTAMPTZ,
  added_by      UUID NOT NULL,
  added_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_blentries_list ON compliance.blocklist_entries (blocklist_id);
CREATE INDEX ix_blentries_exp ON compliance.blocklist_entries (expires_at) WHERE expires_at IS NOT NULL;

CREATE TABLE compliance.keyword_lists (
  keyword_list_id  UUID PRIMARY KEY,
  name             TEXT NOT NULL UNIQUE,
  language         TEXT NOT NULL,             -- ISO 639-1
  category         TEXT,
  is_active        BOOLEAN NOT NULL DEFAULT TRUE,
  created_by       UUID NOT NULL,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE compliance.keyword_entries (
  entry_id         UUID PRIMARY KEY,
  keyword_list_id  UUID NOT NULL REFERENCES compliance.keyword_lists(keyword_list_id) ON DELETE CASCADE,
  keyword          TEXT NOT NULL,
  weight           INT  NOT NULL DEFAULT 1,
  case_sensitive   BOOLEAN NOT NULL DEFAULT FALSE,
  added_by         UUID NOT NULL,
  added_at         TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_kwentries_list ON compliance.keyword_entries (keyword_list_id);

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

CREATE TYPE compliance.audit_entity AS ENUM (
  'RULE','RULE_SET','HOLD','TENANT_TIER','BLOCKLIST','KEYWORD_LIST',
  'REPORT','ASSIGNMENT'
);
CREATE TYPE compliance.audit_action AS ENUM (
  'CREATE','UPDATE','DELETE',
  'REVIEW_RELEASE','REVIEW_REJECT','BULK_REVIEW','OVERRIDE'
);

CREATE TABLE compliance.audit_log (
  audit_id       UUID NOT NULL,
  entity_type    compliance.audit_entity NOT NULL,
  entity_id      UUID NOT NULL,
  action         compliance.audit_action NOT NULL,
  actor_user_id  UUID NOT NULL,
  before         JSONB,
  after          JSONB,
  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 compliance.audit_log (entity_type, entity_id, occurred_at DESC);
CREATE INDEX ix_audit_actor
  ON compliance.audit_log (actor_user_id, occurred_at DESC);

-- =====================================================================
--                          REPORTS (async)
-- =====================================================================

CREATE TYPE compliance.report_type AS ENUM (
  'TENANT_RANKING','VIOLATION_SUMMARY','HOLD_QUEUE_SUMMARY',
  'TIER_TRANSITIONS','TOP_TRIGGERED_RULES','TENANT_AUDIT'
);
CREATE TYPE compliance.report_status AS ENUM ('PENDING','RUNNING','READY','FAILED');

CREATE TABLE compliance.reports (
  report_id      UUID PRIMARY KEY,
  report_type    compliance.report_type NOT NULL,
  params         JSONB NOT NULL,
  status         compliance.report_status NOT NULL DEFAULT 'PENDING',
  output_format  TEXT NOT NULL DEFAULT 'json',
  output_ref     TEXT,                           -- object-store URI when ready
  error_message  TEXT,
  requested_by   UUID NOT NULL,
  requested_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  completed_at   TIMESTAMPTZ
);

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

CREATE TABLE compliance.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 compliance.outbox (created_at) WHERE published_at IS NULL;

2. Redis keys

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

Key	TTL	Purpose
`compliance:eval:cache:{fingerprint}`	300 s	`EvaluateCompliance` result cache keyed by `sha256(accountId:senderId:to:body)`
`compliance:ruleset:{tenantId}:{accountId}`	300 s	Resolved ordered rule list for a tenant/account
`compliance:tenant:risk:{tenantId}`	900 s	Current risk tier + overall score
`compliance:ai:cache:{modelVersion}:{bodyHash}`	24 h	LLM classification result
`compliance:rate:{scope}:{key}`	window	`RATE_VOLUME` sliding-window sorted sets (ZADD + ZCOUNT)
`compliance:hold:queue:{tenantId}:size`	no TTL	Current hold-queue depth per tenant (incremented/decremented)
`compliance:hold:lock:expiry`	60 s	Distributed lock for the hold-auto-expiry cron
`compliance:score:lock:recompute`	900 s	Distributed lock for the scoring cron
`compliance:keywords:{language}`	300 s	In-process keyword list mirror invalidation marker
`compliance:blocklist:{blocklistId}`	300 s	Blocklist entries mirror invalidation marker

3. Row-Level Security & append-only protection

3.1 Append-only on `evaluation_log` and `audit_log`

CREATE RULE eval_log_no_update AS
  ON UPDATE TO compliance.evaluation_log DO INSTEAD NOTHING;
CREATE RULE eval_log_no_delete AS
  ON DELETE TO compliance.evaluation_log DO INSTEAD NOTHING;

CREATE RULE audit_log_no_update AS
  ON UPDATE TO compliance.audit_log DO INSTEAD NOTHING;
CREATE RULE audit_log_no_delete AS
  ON DELETE TO compliance.audit_log DO INSTEAD NOTHING;

Retention is managed by dropping old partitions, not by DELETE.

3.2 Row-level security on hold queue (tenant self-service read)

CREATE POLICY hold_tenant_read ON compliance.hold_queue
  FOR SELECT
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

CREATE POLICY hold_admin_all ON compliance.hold_queue
  USING (current_setting('app.caller_role') IN (
    'platform.compliance.admin','platform.compliance.reviewer','platform.auditor'
  ));

The tenant portal handler sets SET LOCAL app.current_tenant_id = '…' from the Kong-injected X-Tenant-Id header; admin handlers set app.caller_role.

4. ID prefixes

Prefix	Entity
`rl_`	Rule
`rs_`	Rule set
`hq_`	HeldMessage
`ev_`	EvaluationLog
`al_`	AuditLog
`bl_`	Blocklist
`kw_`	KeywordList
`rp_`	Report

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

5. PII, encryption & retention

Field	Class	Protection
`hold_queue.payload.body`	Message content (PII)	Encrypted at rest via per-tenant KMS envelope; decrypted only inside reviewer handlers; role-gated (`platform.compliance.admin` / `platform.compliance.reviewer`)
`hold_queue.payload.to`	E.164 destination (PII)	Masked in API responses (`+CCNNN***`) unless caller role = `platform.compliance.admin`
`evaluation_log.findings[].evidence`	Redacted preview only	Never contains raw body; regex/keyword matches are truncated (first 16 chars masked)
`audit_log.before/after` for hold reviews	May contain excerpts	Same masking rules
`dlr_stats.*`	Aggregates only	No PII

Retention. audit_log: ≥ 13 months (regulatory evidence). evaluation_log: 90 days hot, then cold archive to object storage. score_history: 25 months. hold_queue: 180 days post-terminal. reports.output_ref: per report configuration, default 180 days.

6. Migration strategy

Partitions are provisioned 3 months ahead by a nightly job. Missing future partitions trigger a HIGH alert.
Retention is enforced by dropping partitions older than the retention window, never via DELETE.
Schema changes follow the expand/contract pattern; all migrations are forward-only and reviewed by Security for any column that holds PII.

1. Tables​

2. Redis keys​

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

3.1 Append-only on evaluation_log and audit_log​

3.2 Row-level security on hold queue (tenant self-service read)​

4. ID prefixes​

5. PII, encryption & retention​

6. Migration strategy​