cbc-bridge-service — Data Model

Version: 1.0 Status: Draft Owner: Government / Emergency Last Updated: 2026-04-21

Companion: DOMAIN_MODEL · SECURITY_MODEL · EVENT_SCHEMAS

Schema: cbc. Owned exclusively by cbc-bridge-service. No other service reads or writes these tables directly — integration is over gRPC (BroadcastEmergency, GetBroadcastStatus, CancelBroadcast), HTTPS REST (admin surface), or NATS events.

1. Tables

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

CREATE TYPE cbc.severity AS ENUM ('P0_EXTREME','P1_MAJOR','P2_ADVISORY');

CREATE TYPE cbc.broadcast_state AS ENUM (
  'ACCEPTED','DISPATCHING','ACKED','PARTIAL','FAILED','CANCELLED'
);

CREATE TYPE cbc.dispatch_status AS ENUM (
  'PENDING','DISPATCHED','ACKED','FAILED','TIMEOUT','REJECTED','CANCELLED'
);

CREATE TYPE cbc.adapter_kind AS ENUM (
  'STANDARD_3GPP','ERICSSON_PROPRIETARY','HUAWEI_PROPRIETARY'
);

CREATE TYPE cbc.mno_id AS ENUM (
  'AWCC','Roshan','Etisalat','MTN_AF','Salaam'
);

CREATE TYPE cbc.verify_result AS ENUM (
  'VERIFIED','SIGNATURE_INVALID',
  'CERT_EXPIRED','CERT_REVOKED_CRL','CERT_REVOKED_OCSP',
  'CALLER_NOT_REGISTERED','CALLER_SCOPE_VIOLATION',
  'HSM_UNAVAILABLE'
);

CREATE TYPE cbc.audit_transition AS ENUM (
  'REQUESTED','DISPATCHED','ACKED','PARTIAL','FAILED','CANCELLED','DRILL_EXECUTED'
);

CREATE TYPE cbc.drill_state AS ENUM (
  'SCHEDULED','EXECUTED','SKIPPED_HOLIDAY','CANCELLED'
);

CREATE TYPE cbc.drill_cadence AS ENUM (
  'MANUAL','MONTHLY_FIRST_TUESDAY','QUARTERLY'
);

-- =====================================================================
--                     BROADCASTS  (partitioned monthly,
--                             hash-chained)
-- =====================================================================

CREATE TABLE cbc.broadcasts (
  broadcast_id            UUID NOT NULL,
  caller_id               UUID NOT NULL,
  headline                TEXT NOT NULL,
  body_variants           JSONB NOT NULL,          -- LanguageVariant[]
  geo_target              JSONB NOT NULL,          -- {kind, payload}
  severity                cbc.severity NOT NULL,
  cbs_message_identifier  INT  NOT NULL,
  is_drill                BOOLEAN NOT NULL DEFAULT FALSE,
  serial_number           INT  NOT NULL,
  expires_at              TIMESTAMPTZ NOT NULL,
  state                   cbc.broadcast_state NOT NULL DEFAULT 'ACCEPTED',
  signature_audit_id      UUID NOT NULL,
  prev_hash               TEXT NOT NULL,            -- sha256 hex of prior row in partition
  record_hash             TEXT NOT NULL,            -- sha256 hex (see DOMAIN §2.1)
  accepted_at             TIMESTAMPTZ NOT NULL DEFAULT now(),
  dispatched_at           TIMESTAMPTZ,
  finalised_at            TIMESTAMPTZ,
  PRIMARY KEY (accepted_at, broadcast_id),
  CONSTRAINT chk_is_drill_msg_id CHECK (
    (is_drill = TRUE  AND cbs_message_identifier BETWEEN 4373 AND 4379) OR
    (is_drill = FALSE AND cbs_message_identifier BETWEEN 4370 AND 4372)
  )
) PARTITION BY RANGE (accepted_at);

-- Monthly partitions provisioned 3 months ahead by PartitionMaintenanceWorker
-- CREATE TABLE cbc.broadcasts_2026_05 PARTITION OF cbc.broadcasts
--   FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

CREATE INDEX ix_broadcasts_state_accepted
  ON cbc.broadcasts (state, accepted_at DESC);

CREATE INDEX ix_broadcasts_caller_time
  ON cbc.broadcasts (caller_id, accepted_at DESC);

CREATE INDEX ix_broadcasts_severity_drill
  ON cbc.broadcasts (severity, is_drill, accepted_at DESC);

-- =====================================================================
--                         MNO DISPATCHES
-- =====================================================================

CREATE TABLE cbc.mno_dispatches (
  dispatch_id          UUID PRIMARY KEY,
  broadcast_id         UUID NOT NULL,
  broadcast_accepted_at TIMESTAMPTZ NOT NULL,       -- partition routing FK
  mno_id               cbc.mno_id NOT NULL,
  adapter_kind         cbc.adapter_kind NOT NULL,
  resolved_cell_ids    TEXT[] NOT NULL DEFAULT '{}',
  cbs_pdus             JSONB NOT NULL,              -- per-language CBS PDUs
  attempt              INT  NOT NULL DEFAULT 1,
  status               cbc.dispatch_status NOT NULL DEFAULT 'PENDING',
  cbe_ack_reference    TEXT,
  latency_ms           INT,
  error_code           TEXT,
  error_detail         TEXT,
  dispatched_at        TIMESTAMPTZ,
  acked_at             TIMESTAMPTZ,
  FOREIGN KEY (broadcast_accepted_at, broadcast_id)
    REFERENCES cbc.broadcasts (accepted_at, broadcast_id)
);
CREATE UNIQUE INDEX ux_mno_dispatch_per_attempt
  ON cbc.mno_dispatches (broadcast_id, mno_id, attempt);
CREATE INDEX ix_mno_dispatch_status
  ON cbc.mno_dispatches (status, dispatched_at DESC)
  WHERE status IN ('PENDING','DISPATCHED');

-- =====================================================================
--                    AUTHORISED CALLER REGISTRY
-- =====================================================================

CREATE TABLE cbc.authorised_callers (
  caller_id                UUID PRIMARY KEY,
  org_name                 TEXT NOT NULL,
  cert_subject             TEXT NOT NULL,
  cert_fingerprint_sha256  TEXT NOT NULL,
  allowed_severities       cbc.severity[] NOT NULL,
  allowed_regions          TEXT[] NOT NULL,
  mou_ref                  TEXT NOT NULL,
  not_before               TIMESTAMPTZ NOT NULL,
  not_after                TIMESTAMPTZ NOT NULL,
  dual_control_partners    UUID[] NOT NULL DEFAULT '{}',
  active                   BOOLEAN NOT NULL DEFAULT TRUE,
  created_by               UUID NOT NULL,
  created_at               TIMESTAMPTZ NOT NULL DEFAULT now(),
  deactivated_at           TIMESTAMPTZ,
  CONSTRAINT chk_not_before_before_after CHECK (not_before < not_after),
  CONSTRAINT chk_mou_ref_nonempty CHECK (length(mou_ref) > 0)
);
CREATE UNIQUE INDEX ux_callers_cert_subject_active
  ON cbc.authorised_callers (cert_subject) WHERE active = TRUE;
CREATE UNIQUE INDEX ux_callers_cert_fingerprint_active
  ON cbc.authorised_callers (cert_fingerprint_sha256) WHERE active = TRUE;

CREATE TABLE cbc.authorised_callers_history (
  history_id    UUID PRIMARY KEY,
  caller_id     UUID NOT NULL,
  snapshot      JSONB NOT NULL,
  actor_user_id UUID NOT NULL,
  change_reason TEXT,
  changed_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_callers_history ON cbc.authorised_callers_history (caller_id, changed_at DESC);

-- =====================================================================
--                   PER-MNO CBE CREDENTIALS POINTER
--        (no secrets in Postgres; all in Vault - CBC-US-003)
-- =====================================================================

CREATE TABLE cbc.cbe_credentials_ref (
  mno_id          cbc.mno_id PRIMARY KEY,
  vault_path      TEXT NOT NULL,              -- e.g. 'secret/cbc/mno/awcc/cbe-credentials'
  endpoint_url    TEXT NOT NULL,              -- CBE endpoint hostname+port
  adapter_kind    cbc.adapter_kind NOT NULL,
  ipsec_tunnel_id TEXT,                        -- IPSec SA identifier if peered
  leased_link_id  TEXT,                        -- Dedicated link if applicable
  updated_by      UUID NOT NULL,
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- =====================================================================
--                      DRILLS  (scheduler state)
-- =====================================================================

CREATE TABLE cbc.drills (
  drill_id                 UUID PRIMARY KEY,
  broadcast_id             UUID,
  broadcast_accepted_at    TIMESTAMPTZ,
  cadence                  cbc.drill_cadence NOT NULL,
  scheduled_at             TIMESTAMPTZ NOT NULL,
  geo_target               JSONB NOT NULL,
  body_variants            JSONB NOT NULL,
  after_action_report_ref  TEXT,
  state                    cbc.drill_state NOT NULL DEFAULT 'SCHEDULED',
  created_by               UUID NOT NULL,
  created_at               TIMESTAMPTZ NOT NULL DEFAULT now(),
  executed_at              TIMESTAMPTZ
);
CREATE INDEX ix_drills_state_scheduled
  ON cbc.drills (state, scheduled_at)
  WHERE state = 'SCHEDULED';

-- =====================================================================
--                  SIGNATURE AUDIT (append-only)
-- =====================================================================

CREATE TABLE cbc.signature_audit (
  audit_id                         UUID NOT NULL,
  presented_cert_subject           TEXT NOT NULL,
  presented_cert_fingerprint_sha256 TEXT NOT NULL,
  signature_algorithm              TEXT NOT NULL,
  result                           cbc.verify_result NOT NULL,
  pkcs11_operation                 TEXT NOT NULL DEFAULT 'C_Verify',
  evidence                         JSONB NOT NULL DEFAULT '{}',
  source_ip                        INET,
  observed_at                      TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (observed_at, audit_id)
) PARTITION BY RANGE (observed_at);

CREATE INDEX ix_sig_audit_result_time
  ON cbc.signature_audit (result, observed_at DESC);
CREATE INDEX ix_sig_audit_subject_time
  ON cbc.signature_audit (presented_cert_subject, observed_at DESC);

-- =====================================================================
--          PER-MNO CELL DATABASE  (atomic-swap via snapshot_version)
-- =====================================================================

CREATE TABLE cbc.mno_cell_database (
  mno_id           cbc.mno_id NOT NULL,
  cell_id          TEXT NOT NULL,              -- MCC+MNC+LAC+CI
  lat              NUMERIC(9,6) NOT NULL,
  lng              NUMERIC(9,6) NOT NULL,
  accuracy_meters  INT NOT NULL DEFAULT 500,
  snapshot_version INT NOT NULL,
  last_updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (mno_id, cell_id, snapshot_version)
);
CREATE INDEX ix_cell_db_geom ON cbc.mno_cell_database (mno_id, snapshot_version);

-- A separate view exposes only the current snapshot_version per MNO,
-- maintained by RefreshMnoCellDatabase worker (UC-09).
CREATE TABLE cbc.mno_cell_db_current (
  mno_id           cbc.mno_id PRIMARY KEY,
  snapshot_version INT NOT NULL,
  refreshed_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  row_count        INT NOT NULL,
  coverage_pct     NUMERIC(5,2) NOT NULL   -- estimated national coverage
);

-- Optional PostGIS extension for polygon-within-cell queries (UC-03):
-- CREATE EXTENSION IF NOT EXISTS postgis;
-- ALTER TABLE cbc.mno_cell_database ADD COLUMN geom GEOGRAPHY(POINT,4326)
--   GENERATED ALWAYS AS (ST_MakePoint(lng, lat)::geography) STORED;
-- CREATE INDEX ix_cell_db_geom_gist ON cbc.mno_cell_database USING GIST (geom);

-- =====================================================================
--               REGIONS GEOMETRY (ISO-3166-2 provinces/districts)
-- =====================================================================

CREATE TABLE cbc.regions_geom (
  region_code TEXT PRIMARY KEY,             -- e.g. 'AF-KAB'
  name_en     TEXT NOT NULL,
  name_fa     TEXT,
  name_ps     TEXT,
  geom        JSONB NOT NULL                 -- GeoJSON (or GEOGRAPHY if PostGIS enabled)
);

-- =====================================================================
--            MNO BIND REGISTRY (which CBE endpoints are wired)
-- =====================================================================

CREATE TABLE cbc.mno_bind_registry (
  mno_bind_id      TEXT PRIMARY KEY,
  mno_id           cbc.mno_id NOT NULL,
  adapter_kind     cbc.adapter_kind NOT NULL,
  active           BOOLEAN NOT NULL DEFAULT TRUE,
  last_heartbeat_at TIMESTAMPTZ,
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_mno_bind_active ON cbc.mno_bind_registry (mno_id) WHERE active = TRUE;

-- =====================================================================
--            AUDIT (append-only, partitioned, hash-chained)
-- =====================================================================

CREATE TABLE cbc.audit (
  audit_id         UUID NOT NULL,
  broadcast_id     UUID NOT NULL,
  transition       cbc.audit_transition NOT NULL,
  prev_hash        TEXT NOT NULL,
  row_hash         TEXT NOT NULL,
  snapshot         JSONB NOT NULL,          -- broadcast + per-MNO state snapshot
  actor_caller_id  UUID,
  actor_approver_id UUID,
  occurred_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (occurred_at, audit_id)
) PARTITION BY RANGE (occurred_at);

CREATE INDEX ix_audit_broadcast_time
  ON cbc.audit (broadcast_id, occurred_at DESC);

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

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

-- =====================================================================
--                NONCE LOG (secondary to Redis; audit evidence)
-- =====================================================================

CREATE TABLE cbc.nonce_audit (
  cert_fingerprint_sha256 TEXT NOT NULL,
  nonce                   TEXT NOT NULL,
  observed_at             TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (cert_fingerprint_sha256, nonce)
);
-- Retention: drop older than 48h via cron (Redis is primary; this is forensic only)

2. Indexes

Indexes are defined inline above. Summary of hot-path indexes:

Index	Supports
`ix_broadcasts_state_accepted`	Dispatcher worker pickup (`state = ACCEPTED`)
`ix_broadcasts_caller_time`	Admin "broadcasts for caller X" queries
`ix_broadcasts_severity_drill`	Regulator workbench filter panel
`ux_mno_dispatch_per_attempt`	Exactly-once per-MNO dispatch row with retry attempts
`ix_mno_dispatch_status`	Ack-aggregator sweeper
`ux_callers_cert_subject_active`	UC-01 step 5 caller-registry lookup
`ix_sig_audit_subject_time`	Signature-audit dashboard (CBC-US-010)
`ix_audit_broadcast_time`	Hash-chain verifier; `/v1/cbc/broadcasts/{id}/audit`

3. Row-Level Security & append-only protection

3.1 Append-only on `cbc.broadcasts`, `cbc.audit`, `cbc.signature_audit`, `cbc.nonce_audit`

CREATE RULE broadcasts_no_update AS
  ON UPDATE TO cbc.broadcasts DO INSTEAD (
    -- Allow ONLY state + finalised_at + dispatched_at updates
    UPDATE cbc.broadcasts
    SET state         = COALESCE(NEW.state,         OLD.state),
        dispatched_at = COALESCE(NEW.dispatched_at, OLD.dispatched_at),
        finalised_at  = COALESCE(NEW.finalised_at,  OLD.finalised_at)
    WHERE broadcast_id = OLD.broadcast_id
      AND accepted_at  = OLD.accepted_at
  );
CREATE RULE broadcasts_no_delete AS
  ON DELETE TO cbc.broadcasts DO INSTEAD NOTHING;

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

CREATE RULE sigaud_no_update AS
  ON UPDATE TO cbc.signature_audit DO INSTEAD NOTHING;
CREATE RULE sigaud_no_delete AS
  ON DELETE TO cbc.signature_audit DO INSTEAD NOTHING;

CREATE RULE nonce_no_update AS
  ON UPDATE TO cbc.nonce_audit DO INSTEAD NOTHING;
CREATE RULE nonce_no_delete AS
  ON DELETE TO cbc.nonce_audit DO INSTEAD NOTHING;

Retention on append-only tables is managed by dropping old partitions, never by DELETE.

3.2 Row-level security on `cbc.broadcasts` (caller self-view)

ALTER TABLE cbc.broadcasts ENABLE ROW LEVEL SECURITY;

-- A caller can read only their own broadcasts via the gRPC GetBroadcastStatus
CREATE POLICY broadcasts_caller_read ON cbc.broadcasts
  FOR SELECT
  USING (caller_id = current_setting('app.current_caller_id', true)::uuid);

-- Admin / auditor / regulator roles see all
CREATE POLICY broadcasts_admin_all ON cbc.broadcasts
  USING (current_setting('app.caller_role', true) IN (
    'platform.cbc.admin','platform.auditor','platform.regulator.read'
  ));

ALTER TABLE cbc.signature_audit ENABLE ROW LEVEL SECURITY;
CREATE POLICY sigaud_admin_only ON cbc.signature_audit
  USING (current_setting('app.caller_role', true) IN (
    'platform.cbc.admin','platform.auditor'
  ));

The gRPC handler sets SET LOCAL app.current_caller_id = '...' from the resolved callerId per request; admin handlers set app.caller_role.

4. ID prefixes

Prefix	Entity
`bc_`	Broadcast
`disp_`	MnoDispatch
`caller_`	AuthorisedCaller
`drill_`	Drill
`audit_`	BroadcastAuditEntry
`sigaud_`	SignatureAudit
`snap_`	Cell-DB snapshot

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

5. Redis keys

All keys namespaced cbc:…. Per-caller keys encode {callerId} to support per-caller rate-limit invalidation.

Key	TTL	Purpose
`cbc:idem:{requestId}`	24 h	`BroadcastEmergency` idempotency — maps to `broadcastId`
`cbc:nonce:{certFp}:{nonce}`	24 h	Anti-replay window per UC-01
`cbc:rl:{callerId}:{minuteBucket}`	60 s	Sliding-window rate-limit sorted-set
`cbc:cancel:pending:{broadcastId}`	60 s	First-approver of dual-control cancel
`cbc:dispatch:inflight:{broadcastId}`	120 s	Dispatcher worker advisory lock (backup to Postgres advisory)
`cbc:adapter:cb:{mnoId}`	60 s	Adapter circuit-breaker state
`cbc:drill:sched:{yyyy-mm-dd}`	86 400 s	Drill cron idempotency
`cbc:celldb:{mnoId}:{yyyy-mm-dd}`	86 400 s	Cell-DB refresh cron idempotency
`cbc:audit:lock:verifier`	3 600 s	Audit-chain verifier advisory lock
`cbc:crl:{issuerSha}`	14 400 s	Cached CRL (4 h per CBC-US-008)
`cbc:ocsp:{certFp}`	600 s	Last successful OCSP-stapled response hash

6. PII, encryption & retention

Field	Class	Protection
`cbc.broadcasts.body_variants`	Public by design (will be broadcast to every targeted handset)	Plain at rest; TLS 1.3 in transit
`cbc.authorised_callers.cert_subject`	`CONFIDENTIAL-INTERNAL`	Role-gated via RLS and body-redaction interceptor
`cbc.authorised_callers.cert_fingerprint_sha256`	`CONFIDENTIAL-INTERNAL`	Same
`cbc.authorised_callers.mou_ref`	`CONFIDENTIAL-INTERNAL` (legal artifact reference)	Same
`cbc.signature_audit.*`	`CONFIDENTIAL-INTERNAL`	Role-gated — `platform.cbc.admin`, `platform.auditor` only
`cbc.mno_cell_database.lat/lng`	`INTERNAL` (national-infrastructure info)	Plain; access role-gated
`cbc.cbe_credentials_ref.vault_path`	`INTERNAL` — no secret stored here	Secrets live in Vault
`cbc.audit.snapshot`	May include body (public) + caller ref (confidential)	Caller ref masked in events; full snapshot role-gated via REST

Retention.

Table	Hot	Cold (object-store / archive)
`cbc.broadcasts`	13 months	7 years
`cbc.mno_dispatches`	13 months	7 years
`cbc.audit`	13 months	7 years
`cbc.signature_audit`	25 months	—
`cbc.drills`	25 months	7 years
`cbc.authorised_callers`	Current + active	Full history retained forever (legal)
`cbc.authorised_callers_history`	Forever	—
`cbc.nonce_audit`	48 hours	— (forensic only)
`cbc.mno_cell_database`	Current + last 90 d snapshots	—
`cbc.outbox`	Until `published_at` + 7 d	—

Retention is enforced by dropping partitions and by cold-archive promotion to the S3-compatible sovereign bucket (s3://ghasi-dxb-audit-cold/cbc/...) per ADR-0004 §5.

7. Migration strategy

Partitions provisioned 3 months ahead by PartitionMaintenanceWorker (daily 02:30 Asia/Kabul). Missing future partitions trigger a HIGH alert.
Retention enforced by dropping partitions older than the retention window + cold-archive promotion, never via DELETE.
Schema changes follow the expand/contract pattern; all migrations are forward-only and reviewed by Security for any column holding PKI material or MOU references.
PostGIS is optional; if enabled, a migration adds the generated geom column plus GIST index (UC-03 polygon resolution becomes O(log N) vs O(N)).

8. Storage sizing estimates

Table	Expected volume	Row size	Annual
`cbc.broadcasts`	≤ 5 000 /year (emergencies are rare)	4 KB	20 MB
`cbc.mno_dispatches`	5 × broadcasts = 25 000 /year	6 KB	150 MB
`cbc.signature_audit`	100 000 /year (probing + real)	1 KB	100 MB
`cbc.audit`	6 × broadcasts = 30 000 /year	3 KB	90 MB
`cbc.nonce_audit`	100 000 /year (48 h retention)	200 B	negligible
`cbc.mno_cell_database`	5 MNOs × ~20 000 cells × 2 snapshots kept	400 B	80 MB

Total hot: ~1 GB/year — easily fits inside Patroni primary.

9. Cross-service reads

Other services never read cbc.* directly. Consumers that need CBC state obtain it via:

NATS events (cbc.*.v1 subjects) for reactive processing (notification-service, analytics-service, regulator-portal-service).
REST GET /v1/cbc/broadcasts* with role-gated access for admin surfaces (admin-dashboard).
gRPC GetBroadcastStatus for authorised callers (government clients, regulator-portal-service).

This strict encapsulation keeps the audit chain hash-chained within one writer and prevents accidental direct reads bypassing RLS.

1. Tables​

2. Indexes​

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

3.1 Append-only on cbc.broadcasts, cbc.audit, cbc.signature_audit, cbc.nonce_audit​

3.2 Row-level security on cbc.broadcasts (caller self-view)​

4. ID prefixes​

5. Redis keys​

6. PII, encryption & retention​

7. Migration strategy​

8. Storage sizing estimates​

9. Cross-service reads​