Consent Ledger Service — Data Model

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

Schema: consent. Owned exclusively by consent-ledger-service. No other service reads or writes these tables directly — integration is over gRPC (CheckConsent, RecordConsent, RevokeConsent), HTTP REST (admin, citizen, tenant), or NATS events.

1. Tables

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

CREATE SCHEMA IF NOT EXISTS consent;

CREATE TYPE consent.scope AS ENUM (
  'TRANSACTIONAL','MARKETING','OTP','EMERGENCY'
);

CREATE TYPE consent.status AS ENUM (
  'OPT_IN','OPT_OUT','EXPIRED'
);

CREATE TYPE consent.verification_method AS ENUM (
  'DOUBLE_OPT_IN','KYC_AT_PURCHASE','WET_SIGNATURE_SCAN',
  'BULK_IMPORT_ATTESTATION','TENANT_API','CITIZEN_PORTAL','STOP_MO'
);

CREATE TYPE consent.revoked_reason AS ENUM (
  'STOP_KEYWORD','CITIZEN_PORTAL','TENANT_API',
  'DOUBLE_OPT_IN_EXPIRED','ERASURE_REQUEST','NATIONAL_DND_OVERRIDE','EXPIRED'
);

CREATE TYPE consent.dnd_category AS ENUM ('FULL_BLOCK','MARKETING_ONLY');

CREATE TYPE consent.audit_event_type AS ENUM (
  'RECORD_CREATED','RECORD_REVOKED','RECORD_EXPIRED',
  'DOUBLE_OPTIN_INITIATED','DOUBLE_OPTIN_CONFIRMED','DOUBLE_OPTIN_EXPIRED',
  'STOP_MO_RECEIVED','ACK_BACK_SENT','DND_SYNC_APPLIED',
  'ERASURE_REQUESTED','ERASURE_COMPLETED',
  'STOP_KEYWORD_FALSE_POSITIVE_REPORTED',
  'POLICY_CHANGED','KEYWORD_CATALOG_CHANGED',
  'BULK_IMPORT_COMPLETED','CITIZEN_INSPECTION_VIEW',
  'AUDIT_INTEGRITY_BROKEN','AUDIT_INTEGRITY_VERIFIED'
);

CREATE TYPE consent.language AS ENUM ('EN','DR','PS','AR');

CREATE TYPE consent.stop_keyword_action AS ENUM (
  'REVOKE_TENANT_SCOPE','REVOKE_GLOBAL'
);

CREATE TYPE consent.erasure_status AS ENUM (
  'PENDING','IN_PROGRESS','COMPLETED','REJECTED'
);

CREATE TYPE consent.double_optin_status AS ENUM (
  'PENDING','CONFIRMED','EXPIRED'
);

-- =====================================================================
--                         CONSENT RECORDS
-- =====================================================================

CREATE TABLE consent.records (
  consent_id           TEXT PRIMARY KEY,            -- 'cn_<ULID>'
  tenant_id            UUID NOT NULL,
  msisdn               TEXT NOT NULL,               -- E.164 (or tombstone token after erasure)
  msisdn_hash          BYTEA NOT NULL,              -- sha256(msisdn || pepper)
  msisdn_encrypted     BYTEA,                       -- AES-256-GCM, key from Vault Transit; null after erasure
  scope                consent.scope NOT NULL,
  status               consent.status NOT NULL,
  source               JSONB NOT NULL,              -- ConsentSource VO
  verification_method  consent.verification_method NOT NULL,
  valid_from           TIMESTAMPTZ NOT NULL DEFAULT now(),
  valid_until          TIMESTAMPTZ,
  revoked_at           TIMESTAMPTZ,
  revoked_reason       consent.revoked_reason,
  replaced_by          TEXT REFERENCES consent.records(consent_id),
  created_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT chk_revoked_consistent CHECK (
    (status = 'OPT_OUT' AND revoked_at IS NOT NULL AND revoked_reason IS NOT NULL)
    OR (status <> 'OPT_OUT' AND revoked_at IS NULL AND revoked_reason IS NULL)
  )
);

CREATE UNIQUE INDEX ux_records_current
  ON consent.records (tenant_id, msisdn_hash, scope)
  WHERE replaced_by IS NULL;

CREATE INDEX ix_records_tenant
  ON consent.records (tenant_id, msisdn_hash);

CREATE INDEX ix_records_msisdn_hash
  ON consent.records (msisdn_hash);          -- citizen-portal cross-tenant view

CREATE INDEX ix_records_valid_until
  ON consent.records (valid_until)
  WHERE replaced_by IS NULL AND valid_until IS NOT NULL;

-- =====================================================================
--                         AUDIT LOG (hash-chained, partitioned monthly)
-- =====================================================================

CREATE TABLE consent.audit (
  audit_id          TEXT NOT NULL,                  -- 'cna_<ULID>'
  partition_name    TEXT NOT NULL,                  -- e.g., 'consent_audit_2026_04'
  seq               BIGINT NOT NULL,                -- monotonic per partition
  event_type        consent.audit_event_type NOT NULL,
  tenant_id         UUID,
  msisdn_hash       BYTEA,
  msisdn_encrypted  BYTEA,                          -- AES-256-GCM, key per signing_key_id; null after erasure
  payload           JSONB NOT NULL,
  prev_hash         BYTEA NOT NULL,                 -- 32 bytes
  payload_hash      BYTEA NOT NULL,                 -- 32 bytes
  record_hash       BYTEA NOT NULL,                 -- 32 bytes (sha256(payload_hash || prev_hash))
  signing_key_id    TEXT NOT NULL,
  redacted_fields   TEXT[] DEFAULT '{}',            -- e.g., ['payload.msisdn','msisdn_encrypted'] after erasure
  actor_user_id     UUID,
  trace_id          TEXT,
  occurred_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (occurred_at, partition_name, seq)
) PARTITION BY RANGE (occurred_at);

-- Initial partition example (provisioned monthly by AuditPartitionMaintainer)
-- CREATE TABLE consent.audit_2026_04
--   PARTITION OF consent.audit
--   FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

CREATE INDEX ix_audit_msisdn_time
  ON consent.audit (msisdn_hash, occurred_at DESC);
CREATE INDEX ix_audit_tenant_time
  ON consent.audit (tenant_id, occurred_at DESC);
CREATE INDEX ix_audit_event_time
  ON consent.audit (event_type, occurred_at DESC);
CREATE UNIQUE INDEX ux_audit_partition_seq
  ON consent.audit (partition_name, seq);

-- =====================================================================
--                       NATIONAL DND REGISTRY (mirror)
-- =====================================================================

CREATE TABLE consent.dnd_registry (
  dnd_id              TEXT PRIMARY KEY,             -- 'dnd_<ULID>'
  msisdn              TEXT NOT NULL,                -- E.164
  msisdn_hash         BYTEA NOT NULL,
  msisdn_encrypted    BYTEA,
  registered_at       TIMESTAMPTZ NOT NULL,
  category            consent.dnd_category NOT NULL DEFAULT 'FULL_BLOCK',
  source_feed_run_id  TEXT NOT NULL REFERENCES consent.dnd_sync_runs(run_id),
  last_seen_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  removed_at          TIMESTAMPTZ
);

CREATE UNIQUE INDEX ux_dnd_msisdn_hash
  ON consent.dnd_registry (msisdn_hash) WHERE removed_at IS NULL;

CREATE INDEX ix_dnd_last_seen
  ON consent.dnd_registry (last_seen_at);

CREATE TABLE consent.dnd_sync_runs (
  run_id                TEXT PRIMARY KEY,           -- 'ddr_<ULID>'
  source_feed_hash      TEXT NOT NULL,              -- sha256 of fetched file
  source_signature_valid BOOLEAN NOT NULL,
  added_count           INT NOT NULL,
  removed_count         INT NOT NULL,
  updated_count         INT NOT NULL,
  total_count           INT NOT NULL,
  duration_ms           INT NOT NULL,
  started_at            TIMESTAMPTZ NOT NULL,
  completed_at          TIMESTAMPTZ NOT NULL,
  error_message         TEXT
);

-- =====================================================================
--                       STOP-KEYWORD CATALOG
-- =====================================================================

CREATE TABLE consent.stop_keywords (
  keyword_id            TEXT PRIMARY KEY,           -- 'kw_<ULID>'
  language              consent.language NOT NULL,
  keyword               TEXT NOT NULL,              -- NFKC-normalised, lowercase
  is_platform_default   BOOLEAN NOT NULL DEFAULT FALSE,
  tenant_override_of    UUID,                       -- tenant_id when tenant-added; null for platform default
  revoke_action         consent.stop_keyword_action NOT NULL DEFAULT 'REVOKE_TENANT_SCOPE',
  added_by              UUID NOT NULL,
  added_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at            TIMESTAMPTZ
);

CREATE UNIQUE INDEX ux_stop_keywords_unique
  ON consent.stop_keywords (language, keyword, COALESCE(tenant_override_of, '00000000-0000-0000-0000-000000000000'::uuid))
  WHERE deleted_at IS NULL;

CREATE INDEX ix_stop_keywords_language
  ON consent.stop_keywords (language) WHERE deleted_at IS NULL;

-- Trigger: defaults cannot be soft-deleted
CREATE OR REPLACE FUNCTION consent.fn_protect_default_keywords() RETURNS TRIGGER AS $$
BEGIN
  IF OLD.is_platform_default = TRUE AND NEW.deleted_at IS NOT NULL THEN
    RAISE EXCEPTION 'Cannot delete platform-default STOP keyword %', OLD.keyword_id
      USING ERRCODE = 'check_violation';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_protect_default_keywords
  BEFORE UPDATE ON consent.stop_keywords
  FOR EACH ROW EXECUTE FUNCTION consent.fn_protect_default_keywords();

-- =====================================================================
--                       ACK-BACK TEMPLATES
-- =====================================================================

CREATE TABLE consent.ack_back_templates (
  template_id   TEXT PRIMARY KEY,                   -- 'tpl_<slug>'
  language      consent.language NOT NULL,
  body          TEXT NOT NULL,                      -- with {senderId} placeholder
  active        BOOLEAN NOT NULL DEFAULT TRUE,
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX ux_ack_back_active_per_language
  ON consent.ack_back_templates (language) WHERE active = TRUE;

-- =====================================================================
--                       TENANT SCOPE CONFIG
-- =====================================================================

CREATE TABLE consent.tenant_scope_config (
  tenant_id        UUID NOT NULL,
  custom_scope     TEXT NOT NULL,                   -- tenant-defined
  resolves_to      consent.scope NOT NULL,          -- canonical scope used by CheckConsent
  description      TEXT,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, custom_scope)
);

-- =====================================================================
--                       DOUBLE-OPT-IN
-- =====================================================================

CREATE TABLE consent.double_optins (
  optin_id                   TEXT PRIMARY KEY,      -- 'do_<ULID>'
  tenant_id                  UUID NOT NULL,
  msisdn                     TEXT NOT NULL,
  msisdn_hash                BYTEA NOT NULL,
  scope                      consent.scope NOT NULL,
  status                     consent.double_optin_status NOT NULL DEFAULT 'PENDING',
  confirmation_token_hash    BYTEA NOT NULL,        -- HMAC-SHA256 of token (don't store raw token)
  expires_at                 TIMESTAMPTZ NOT NULL,
  initiated_at               TIMESTAMPTZ NOT NULL DEFAULT now(),
  confirmed_at               TIMESTAMPTZ,
  sender_used_for_optin_sms  TEXT NOT NULL,
  recorded_consent_id        TEXT REFERENCES consent.records(consent_id)
);

CREATE INDEX ix_optins_status_expires
  ON consent.double_optins (status, expires_at)
  WHERE status = 'PENDING';
CREATE INDEX ix_optins_tenant_msisdn
  ON consent.double_optins (tenant_id, msisdn_hash);

-- =====================================================================
--                       ERASURE REQUESTS
-- =====================================================================

CREATE TABLE consent.erasure_requests (
  erasure_id        TEXT PRIMARY KEY,               -- 'er_<ULID>'
  msisdn_hash       BYTEA NOT NULL,
  requested_via     TEXT NOT NULL,                  -- 'CITIZEN_PORTAL'|'REGULATOR_PORTAL'|'SUPPORT_TICKET'
  requested_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  sla_due_at        TIMESTAMPTZ NOT NULL,
  status            consent.erasure_status NOT NULL DEFAULT 'PENDING',
  rejected_reason   TEXT,
  completed_at      TIMESTAMPTZ,
  tombstone_token   TEXT NOT NULL,
  records_redacted  INT NOT NULL DEFAULT 0,
  audit_rows_redacted INT NOT NULL DEFAULT 0
);

CREATE INDEX ix_erasure_status
  ON consent.erasure_requests (status, sla_due_at);

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

CREATE TABLE consent.outbox (
  event_id     UUID PRIMARY KEY,
  subject      TEXT NOT NULL,                       -- e.g., 'consent.granted.v1'
  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 consent.outbox (created_at) WHERE published_at IS NULL;

-- =====================================================================
--                       IDEMPOTENCY KEYS
-- =====================================================================

CREATE TABLE consent.idempotency_keys (
  tenant_id        UUID NOT NULL,
  idempotency_key  TEXT NOT NULL,
  request_hash     BYTEA NOT NULL,                  -- sha256(canonicalised request body)
  response         JSONB NOT NULL,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at       TIMESTAMPTZ NOT NULL DEFAULT (now() + INTERVAL '24 hours'),
  PRIMARY KEY (tenant_id, idempotency_key)
);
CREATE INDEX ix_idempotency_expires
  ON consent.idempotency_keys (expires_at);

-- =====================================================================
--                       FALSE-POSITIVE FEEDBACK (CONS-US-011)
-- =====================================================================

CREATE TABLE consent.false_positive_feedback (
  feedback_id      TEXT PRIMARY KEY,                -- 'fp_<ULID>'
  tenant_id        UUID NOT NULL,
  msisdn_hash      BYTEA NOT NULL,
  mo_encrypted     BYTEA NOT NULL,                  -- encrypted MO body
  recorded_time    TIMESTAMPTZ NOT NULL,
  justification    TEXT,
  triage_status    TEXT NOT NULL DEFAULT 'PENDING',
  resolution       TEXT,
  reviewed_by      UUID,
  reviewed_at      TIMESTAMPTZ,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);

2. Redis keys

All keys namespaced consent:. Tenant-scoped keys encode {tenantId} so per-tenant invalidation is cheap.

Key	TTL	Purpose
`consent:state:{tenantId}:{msisdnHash}:{scope}`	300 s	Hot cache for `CheckConsent`. Value: `{ status, validUntil, recordId, computedAt }` JSON.
`consent:dnd:{msisdnHash}`	86,400 s (24 h)	National DND fast-lookup. Value: `{ category }`.
`consent:keywords:{language}`	no TTL (catalog mirror)	In-process catalog reload signal; value is `updatedAt` timestamp
`consent:lock:dnd_sync`	1,800 s	Distributed lock for DND sync worker
`consent:lock:audit_verifier`	3,600 s	Distributed lock for chain verifier
`consent:lock:erasure_processor`	1,800 s	Lock for erasure processor
`consent:lock:double_optin_expiry`	600 s	Lock for double-opt-in expiry cron
`consent:rate:citizen_otp:{msisdnHash}`	3,600 s	Citizen OTP rate-limit counter
`consent:rate:citizen_revoke:{citizenJwt}`	3,600 s	Citizen revoke rate-limit counter
`consent:warm:tenants`	21,600 s	List of tenants whose top-N MSISDNs to pre-warm
`consent:tenant:{tenantId}:lifecycle`	600 s	Mirror of tenant lifecycle (SUSPENDED → reject writes)

Redis cluster mode is used; {tenantId} is the slot tag where applicable so per-tenant operations stay on a single shard.

3. Row-Level Security & append-only protection

3.1 Append-only on `consent.audit`

CREATE RULE consent_audit_no_update AS
  ON UPDATE TO consent.audit DO INSTEAD NOTHING;
CREATE RULE consent_audit_no_delete AS
  ON DELETE TO consent.audit DO INSTEAD NOTHING;

Erasure performs UPDATE on msisdn_encrypted and payload via a dedicated SECURITY DEFINER function consent.fn_redact_audit_for_erasure(erasureId) that:

Acquires an advisory lock on the partition.
Sets the redacted fields explicitly without touching prev_hash, payload_hash, record_hash.
Updates redacted_fields array.
Writes a fresh ERASURE_COMPLETED audit row at the chain tail.

Postgres rules above apply to ordinary roles; the redact function is owned by a privileged DB role granted only to the ErasureProcessor. The function emits a RAISE NOTICE for audit, and grants EXECUTE only to that role.

Retention is enforced by dropping old partitions after they have been verified-archived to S3, never via DELETE.

3.2 Row-level security on `consent.records`

ALTER TABLE consent.records ENABLE ROW LEVEL SECURITY;

CREATE POLICY records_tenant_isolation ON consent.records
  FOR ALL
  USING (
    tenant_id = current_setting('app.current_tenant_id', true)::uuid
    OR current_setting('app.caller_role', true) IN (
      'platform.consent.admin','platform.regulator','platform.support'
    )
  );

CREATE POLICY records_citizen_read ON consent.records
  FOR SELECT
  USING (
    current_setting('app.caller_role', true) = 'citizen'
    AND msisdn_hash = decode(current_setting('app.citizen_msisdn_hash', true), 'hex')
  );

Tenant-portal handlers set SET LOCAL app.current_tenant_id = '…' from the Kong-injected X-Tenant-Id header. Citizen handlers set SET LOCAL app.caller_role = 'citizen' and SET LOCAL app.citizen_msisdn_hash = '…' from the citizen JWT.

3.3 RLS on `consent.dnd_registry`

ALTER TABLE consent.dnd_registry ENABLE ROW LEVEL SECURITY;

CREATE POLICY dnd_admin_read ON consent.dnd_registry
  USING (current_setting('app.caller_role', true) IN (
    'platform.consent.admin','platform.regulator'
  ));

Tenants do not read the DND registry; they receive verdicts via CheckConsent. The platform-level decision to publish the DND list externally is the regulator's, not the platform's.

4. ID prefixes

Prefix	Entity
`cn_`	ConsentRecord
`cna_`	ConsentAuditEntry
`dnd_`	NationalDndEntry
`ddr_`	DndSyncRun
`kw_`	StopKeyword
`tpl_`	AckBackTemplate
`do_`	DoubleOptin
`er_`	ErasureRequest
`fp_`	FalsePositiveFeedback
`ts_`	Tombstone token (for erasure)
`cvr_`	ChainVerifierRun

ULIDs are used for all *_id values; prefixes apply only to externally-exposed identifiers on REST and gRPC responses.

5. PII, encryption & retention

Field	Class	Protection
`consent.records.msisdn`	CONFIDENTIAL (PII)	Stored in plaintext for SQL `WHERE msisdn = $1` joinability; access controlled via RLS; replaced by tombstone token after erasure
`consent.records.msisdn_encrypted`	RESTRICTED	AES-256-GCM ciphertext; key wrapped by per-tenant KEK in Vault Transit (`transit/ghasi-consent-<tenantId>`); decrypted only inside admin handlers and by the citizen-portal MSISDN-validation flow
`consent.records.msisdn_hash`	INTERNAL	sha256 with a platform-wide pepper held in Vault KV (`secret/ghasi/consent/msisdn_pepper`); rotated quarterly with envelope re-keying
`consent.audit.payload.msisdn`	CONFIDENTIAL	Same as records.msisdn; redacted on erasure with a redacted-fields marker preserving chain integrity
`consent.audit.payload.body` (STOP MO)	NOT STORED	Only the matched keyword span and language are persisted; never the full MO body
`consent.false_positive_feedback.mo_encrypted`	RESTRICTED	AES-256-GCM; per-tenant KEK; viewable only by `platform.consent.admin` reviewers via a role-gated handler
`consent.dnd_registry.msisdn`	CONFIDENTIAL	Same as records; never replicated outside the Kabul region per ADR-0004 §3

Retention

Table	Hot retention	Cold retention	Rule
`consent.records`	Indefinite (active records); 5 years (revoked/expired)	—	Citizen erasure tombstones rows ahead of natural expiry
`consent.audit`	13 months hot (Postgres)	7 years (S3 immutable, Object Lock)	Cron archives partitions > 13 m to `s3://ghasi-consent-audit-cold/` then drops
`consent.dnd_registry`	Indefinite	—	ATRA feed authoritative
`consent.dnd_sync_runs`	13 months	—
`consent.stop_keywords`	Indefinite	—	Soft-delete only; defaults sealed
`consent.double_optins`	90 days post-terminal	—
`consent.erasure_requests`	7 years	—	Regulatory requirement
`consent.outbox`	7 days post-publish	—
`consent.idempotency_keys`	24 h	—
`consent.false_positive_feedback`	365 days	—

Cold archive flow (per CONS-US-013):

AuditPartitionMaintainer (daily 02:30 Asia/Kabul) identifies partitions older than 13 months.
Per partition: pg_dump --schema-only --table=consent.audit_2025_01 to S3 + COPY consent.audit_2025_01 TO PROGRAM 'gzip > /tmp/audit_2025_01.csv.gz', uploaded with SSE-KMS to s3://ghasi-consent-audit-cold/year=2025/month=01/. Object Lock retention: 7 years governance mode.
Verification: re-read first/last/middle rows from S3, recompute hash chain across the boundary, confirm identical.
Drop the partition from Postgres.

Restore (per CONS-US-014 §4): admin POST /v1/admin/consent/audit/restore triggers RestoreJob to pull objects, load into consent.audit_restore_<jobId> (off-tree), and return a signed presigned URL within 1 h SLA.

6. Migration strategy

Partitions provisioned 3 months ahead by AuditPartitionMaintainer daily run. Missing future partitions fire ConsentAuditPartitionMissing (HIGH).
Schema changes follow the expand/contract pattern; all migrations are forward-only and reviewed by Security for any column that holds PII.
ENUM evolution: new values added with ALTER TYPE consent.<enum> ADD VALUE 'NEW' BEFORE 'OTHER'; only — existing enum values are never renamed or removed within v1.
Index changes: created CONCURRENTLY to avoid table locks; pg_repack used for any rewrites > 100 GB.
consent.records PK is the consent_id (string) so the ULID-based lex ordering is naturally time-clustered, reducing index bloat on bulk inserts.
Redis schema versioning: when the cache value shape changes, the key prefix changes (consent:state:v2:…) and the old keys are allowed to expire naturally. Co-existence is supported by feature flag during the rollover.

1. Tables​

2. Redis keys​

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

3.1 Append-only on consent.audit​

3.2 Row-level security on consent.records​

3.3 RLS on consent.dnd_registry​

4. ID prefixes​

5. PII, encryption & retention​

Retention​

6. Migration strategy​