numbering-service — Data Model

Version: 1.0 Status: Draft Owner: Commerce Engineering + Platform Engineering Last Updated: 2026-04-21 Companion: DOMAIN_MODEL · SECURITY_MODEL · SYNC_CONTRACT

Schema: numbering. Owned exclusively by numbering-service. No other service reads or writes these tables directly — integration is over gRPC (ValidateLease, Lookup, Reserve, Assign, Release, Recall), HTTP REST (admin + portal), or NATS events.

1. Tables

CREATE SCHEMA IF NOT EXISTS numbering;

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

CREATE TYPE numbering.number_type AS ENUM ('MSISDN','SHORT_CODE','ALPHA_ID');

CREATE TYPE numbering.number_subtype AS ENUM (
  'STANDARD','VANITY','TOLL_FREE','PREMIUM_RATE','MNO_INTERNAL'
);

CREATE TYPE numbering.number_state AS ENUM (
  'AVAILABLE','RESERVED','HELD','LEASED','SUSPENDED','RECALLED','QUARANTINE'
);

CREATE TYPE numbering.reservation_kind AS ENUM ('RESERVE','HOLD');

CREATE TYPE numbering.release_reason AS ENUM (
  'TTL_EXPIRED','TENANT_RELEASE','PROMOTED_TO_LEASE','PROMOTED_TO_HOLD',
  'QUARANTINE_COMPLETED','ADMIN_OVERRIDE'
);

CREATE TYPE numbering.recall_reason AS ENUM (
  'REGULATOR_ORDER','ABUSE','NON_PAYMENT','TENANT_RELEASE','EXPIRED','PLATFORM_RECALL'
);

CREATE TYPE numbering.lease_term AS ENUM ('P7D','P30D','P90D','P1Y','P3Y');

CREATE TYPE numbering.contract_status AS ENUM (
  'DRAFT','ACTIVE','EXPIRING','EXPIRED','SUSPENDED'
);

CREATE TYPE numbering.export_status AS ENUM (
  'PENDING','GENERATED','SIGNED','SUBMITTED','ACCEPTED','REJECTED'
);

-- =====================================================================
--                          MOBILE OPERATORS
-- =====================================================================

CREATE TABLE numbering.mobile_operators (
  operator_id   UUID PRIMARY KEY,
  code          TEXT NOT NULL UNIQUE,      -- 'ROSHAN', 'ETISALAT_AF', 'MTN_AF', 'AWCC', 'SALAAM'
  display_name  TEXT NOT NULL,
  mcc           TEXT NOT NULL,              -- '412' (Afghanistan per ITU-T E.212)
  mnc           TEXT NOT NULL,              -- '40' Roshan, '50' Etisalat-AF, '01/20' MTN-AF, '03' AWCC, '88' Salaam
  allowed_prefixes TEXT[] NOT NULL,         -- e.g. ['+9370','+9371','+9372']
  is_active     BOOLEAN NOT NULL DEFAULT TRUE,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (mcc, mnc)
);

-- =====================================================================
--                         LEASE CONTRACTS (MNO)
-- =====================================================================

CREATE TABLE numbering.lease_contracts (
  lease_contract_id UUID PRIMARY KEY,
  operator_id       UUID NOT NULL REFERENCES numbering.mobile_operators(operator_id),
  prefix            TEXT NOT NULL,             -- e.g. '+9370'
  suffix_from       TEXT NOT NULL,             -- e.g. '0000000'
  suffix_to         TEXT NOT NULL,             -- e.g. '9999999'
  block_size        INT  NOT NULL,
  effective_from    TIMESTAMPTZ NOT NULL,
  effective_until   TIMESTAMPTZ NOT NULL,
  auto_renew        BOOLEAN NOT NULL DEFAULT FALSE,
  signature_ref     TEXT NOT NULL,
  status            numbering.contract_status NOT NULL DEFAULT 'DRAFT',
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (effective_until > effective_from)
);
CREATE INDEX ix_contracts_operator ON numbering.lease_contracts (operator_id, status);
-- Guard against overlapping prefix ranges per operator
CREATE UNIQUE INDEX ux_contracts_prefix_range
  ON numbering.lease_contracts (operator_id, prefix, suffix_from, suffix_to)
  WHERE status IN ('ACTIVE','EXPIRING');

-- =====================================================================
--                              NUMBERS
-- =====================================================================

CREATE TABLE numbering.numbers (
  number_id           UUID PRIMARY KEY,
  value               TEXT NOT NULL,
  type                numbering.number_type NOT NULL,
  subtype             numbering.number_subtype NOT NULL DEFAULT 'STANDARD',
  state               numbering.number_state NOT NULL DEFAULT 'AVAILABLE',
  operator_id         UUID REFERENCES numbering.mobile_operators(operator_id),
  lease_contract_id   UUID REFERENCES numbering.lease_contracts(lease_contract_id),
  originating_block_id UUID,                   -- FK → lease_import_batches (below)
  assigned_tenant_id  UUID,
  assigned_lease_id   UUID,
  quarantine_until    TIMESTAMPTZ,
  valid_from          TIMESTAMPTZ NOT NULL DEFAULT now(),
  valid_until         TIMESTAMPTZ,
  version             INT  NOT NULL DEFAULT 1,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (
    (state IN ('RESERVED','HELD','LEASED','SUSPENDED') AND assigned_tenant_id IS NOT NULL)
    OR (state NOT IN ('RESERVED','HELD','LEASED','SUSPENDED') AND assigned_tenant_id IS NULL)
  ),
  CHECK ((state = 'QUARANTINE') = (quarantine_until IS NOT NULL)),
  CHECK (type = 'ALPHA_ID' OR value ~ '^[+0-9]+$'),
  CHECK (type <> 'MSISDN' OR value ~ '^\+[1-9][0-9]{6,14}$')
);

-- Fast hot-path lookup
CREATE UNIQUE INDEX ux_numbers_value_type ON numbering.numbers (value, type);
-- Partial unique: only one active claim per identifier at a time
CREATE UNIQUE INDEX ux_numbers_active_claim
  ON numbering.numbers (value, type)
  WHERE state IN ('RESERVED','HELD','LEASED','SUSPENDED');
CREATE INDEX ix_numbers_state ON numbering.numbers (state);
CREATE INDEX ix_numbers_tenant ON numbering.numbers (assigned_tenant_id) WHERE assigned_tenant_id IS NOT NULL;
CREATE INDEX ix_numbers_operator_state ON numbering.numbers (operator_id, state) WHERE operator_id IS NOT NULL;
CREATE INDEX ix_numbers_quarantine_until ON numbering.numbers (quarantine_until) WHERE state = 'QUARANTINE';

-- RLS — tenants see only their own rows via portal API
ALTER TABLE numbering.numbers ENABLE ROW LEVEL SECURITY;

CREATE POLICY numbers_tenant_read ON numbering.numbers
  FOR SELECT
  USING (assigned_tenant_id = current_setting('app.current_tenant_id', true)::uuid
         OR state = 'AVAILABLE');

CREATE POLICY numbers_admin_all ON numbering.numbers
  USING (current_setting('app.caller_role', true) IN (
    'platform.numbering.admin','platform.auditor','platform.numbering.ops'
  ));

-- =====================================================================
--                              LEASES
-- =====================================================================

CREATE TABLE numbering.leases (
  lease_id           UUID PRIMARY KEY,
  number_id          UUID NOT NULL REFERENCES numbering.numbers(number_id),
  tenant_id          UUID NOT NULL,
  account_id         UUID,
  effective_from     TIMESTAMPTZ NOT NULL DEFAULT now(),
  effective_until    TIMESTAMPTZ NOT NULL,
  term               numbering.lease_term NOT NULL,
  auto_renew         BOOLEAN NOT NULL DEFAULT FALSE,
  vanity_flag        BOOLEAN NOT NULL DEFAULT FALSE,
  previous_lease_id  UUID REFERENCES numbering.leases(lease_id),
  created_by         UUID NOT NULL,
  created_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  terminated_at      TIMESTAMPTZ,
  termination_reason numbering.recall_reason,
  CHECK (effective_until > effective_from),
  CHECK ((terminated_at IS NULL) = (termination_reason IS NULL))
);

-- At most one active lease per number
CREATE UNIQUE INDEX ux_leases_active
  ON numbering.leases (number_id)
  WHERE terminated_at IS NULL;
CREATE INDEX ix_leases_tenant_active
  ON numbering.leases (tenant_id, terminated_at)
  WHERE terminated_at IS NULL;
CREATE INDEX ix_leases_renewal_due
  ON numbering.leases (effective_until)
  WHERE terminated_at IS NULL AND auto_renew = TRUE;

ALTER TABLE numbering.leases ENABLE ROW LEVEL SECURITY;
CREATE POLICY leases_tenant_read ON numbering.leases
  FOR SELECT
  USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
CREATE POLICY leases_admin_all ON numbering.leases
  USING (current_setting('app.caller_role', true) IN (
    'platform.numbering.admin','platform.auditor','platform.numbering.ops'
  ));

-- =====================================================================
--                           RESERVATIONS
-- =====================================================================

CREATE TABLE numbering.reservations (
  reservation_id  UUID PRIMARY KEY,
  number_id       UUID NOT NULL REFERENCES numbering.numbers(number_id),
  tenant_id       UUID NOT NULL,
  kind            numbering.reservation_kind NOT NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at      TIMESTAMPTZ NOT NULL,
  released_at     TIMESTAMPTZ,
  release_reason  numbering.release_reason,
  CHECK (expires_at > created_at),
  CHECK ((released_at IS NULL) = (release_reason IS NULL))
);

CREATE UNIQUE INDEX ux_reservations_active
  ON numbering.reservations (number_id)
  WHERE released_at IS NULL;
CREATE INDEX ix_reservations_expiry
  ON numbering.reservations (expires_at)
  WHERE released_at IS NULL;
CREATE INDEX ix_reservations_tenant_active
  ON numbering.reservations (tenant_id)
  WHERE released_at IS NULL;

-- =====================================================================
--                           TENANT POOLS
-- =====================================================================

CREATE TABLE numbering.tenant_pools (
  pool_id                  UUID PRIMARY KEY,
  tenant_id                UUID NOT NULL UNIQUE,
  name                     TEXT NOT NULL,
  max_leased_msisdn        INT  NOT NULL DEFAULT 10,
  max_leased_short_code    INT  NOT NULL DEFAULT 1,
  max_leased_alpha         INT  NOT NULL DEFAULT 3,
  max_active_reservations  INT  NOT NULL DEFAULT 20,
  allowed_operator_ids     UUID[] NOT NULL DEFAULT '{}',
  vanity_enabled           BOOLEAN NOT NULL DEFAULT FALSE,
  bypass_reservation       BOOLEAN NOT NULL DEFAULT FALSE,
  created_at               TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at               TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (max_leased_msisdn >= 0 AND max_leased_short_code >= 0 AND max_leased_alpha >= 0)
);

-- =====================================================================
--                         QUARANTINE RECORDS
-- =====================================================================

CREATE TABLE numbering.quarantine_records (
  quarantine_id        UUID PRIMARY KEY,
  number_id            UUID NOT NULL REFERENCES numbering.numbers(number_id),
  previous_tenant_id   UUID NOT NULL,
  recall_reason        numbering.recall_reason NOT NULL,
  quarantine_from      TIMESTAMPTZ NOT NULL DEFAULT now(),
  quarantine_until     TIMESTAMPTZ NOT NULL,
  override_by          UUID,
  override_at          TIMESTAMPTZ,
  override_justification TEXT,
  completed_at         TIMESTAMPTZ,
  CHECK (quarantine_until >= quarantine_from),
  CHECK (
    (override_by IS NULL AND override_at IS NULL AND override_justification IS NULL)
    OR (override_by IS NOT NULL AND override_at IS NOT NULL AND length(override_justification) >= 20)
  )
);
CREATE INDEX ix_quarantine_active
  ON numbering.quarantine_records (quarantine_until)
  WHERE completed_at IS NULL;

-- =====================================================================
--                       LEASE IMPORT BATCHES
-- =====================================================================

CREATE TABLE numbering.lease_import_batches (
  batch_id          UUID PRIMARY KEY,
  operator_id       UUID NOT NULL REFERENCES numbering.mobile_operators(operator_id),
  lease_contract_id UUID NOT NULL REFERENCES numbering.lease_contracts(lease_contract_id),
  source_filename   TEXT NOT NULL,
  file_sha256       TEXT NOT NULL,
  signature_ref     TEXT NOT NULL,
  imported          INT  NOT NULL DEFAULT 0,
  duplicates        INT  NOT NULL DEFAULT 0,
  invalid           INT  NOT NULL DEFAULT 0,
  status            TEXT NOT NULL DEFAULT 'RUNNING',  -- RUNNING|COMPLETED|FAILED
  created_by        UUID NOT NULL,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  completed_at      TIMESTAMPTZ
);

CREATE TABLE numbering.lease_import_errors (
  error_id      UUID PRIMARY KEY,
  batch_id      UUID NOT NULL REFERENCES numbering.lease_import_batches(batch_id) ON DELETE CASCADE,
  line_no       INT  NOT NULL,
  raw_row       TEXT NOT NULL,
  reason        TEXT NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_import_errors_batch ON numbering.lease_import_errors (batch_id);

-- =====================================================================
--                         VANITY ELIGIBILITY
-- =====================================================================

CREATE TABLE numbering.vanity_eligible (
  short_code      TEXT PRIMARY KEY,
  approved_by     UUID NOT NULL,
  approved_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  atra_ref        TEXT NOT NULL,         -- regulator approval ref
  price_tier      TEXT NOT NULL          -- BRONZE | SILVER | GOLD | PLATINUM
);

-- =====================================================================
--                         MNO SIGNING KEYS
-- =====================================================================

CREATE TABLE numbering.mno_signing_keys (
  key_id        UUID PRIMARY KEY,
  operator_id   UUID NOT NULL REFERENCES numbering.mobile_operators(operator_id),
  public_key_pem TEXT NOT NULL,
  valid_from    TIMESTAMPTZ NOT NULL,
  valid_until   TIMESTAMPTZ NOT NULL,
  rotated_from  UUID REFERENCES numbering.mno_signing_keys(key_id),
  CHECK (valid_until > valid_from)
);
CREATE INDEX ix_mno_keys_operator_active
  ON numbering.mno_signing_keys (operator_id, valid_until)
  WHERE valid_until > now();

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

CREATE TABLE numbering.audit (
  audit_id            UUID NOT NULL,
  number_id           UUID NOT NULL,
  from_state          numbering.number_state,
  to_state            numbering.number_state NOT NULL,
  lease_id_ref        UUID,
  reservation_id_ref  UUID,
  quarantine_id_ref   UUID,
  actor_user_id       UUID,
  actor_service       TEXT,
  tenant_id           UUID,
  reason_code         TEXT NOT NULL,
  ip                  INET,
  user_agent          TEXT,
  trace_id            TEXT,
  prev_hash           BYTEA,
  row_hash            BYTEA NOT NULL,
  occurred_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (occurred_at, audit_id)
) PARTITION BY RANGE (occurred_at);

CREATE INDEX ix_audit_number
  ON numbering.audit (number_id, occurred_at DESC);
CREATE INDEX ix_audit_actor
  ON numbering.audit (actor_user_id, occurred_at DESC)
  WHERE actor_user_id IS NOT NULL;
CREATE INDEX ix_audit_tenant
  ON numbering.audit (tenant_id, occurred_at DESC)
  WHERE tenant_id IS NOT NULL;

-- Append-only (DB-level enforcement)
CREATE RULE numbering_audit_no_update AS
  ON UPDATE TO numbering.audit DO INSTEAD NOTHING;
CREATE RULE numbering_audit_no_delete AS
  ON DELETE TO numbering.audit DO INSTEAD NOTHING;

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

CREATE TABLE numbering.regulator_exports (
  export_id         UUID PRIMARY KEY,
  period_year_month TEXT NOT NULL UNIQUE,       -- 'YYYY-MM'
  s3_ref            TEXT NOT NULL,
  sha256_hex        TEXT NOT NULL,
  signature_ref     TEXT NOT NULL,
  row_count         INT  NOT NULL,
  status            numbering.export_status NOT NULL DEFAULT 'PENDING',
  generated_at      TIMESTAMPTZ,
  signed_at         TIMESTAMPTZ,
  submitted_at      TIMESTAMPTZ,
  accepted_at       TIMESTAMPTZ,
  notes             TEXT
);

-- =====================================================================
--                          IDEMPOTENCY
-- =====================================================================

CREATE TABLE numbering.idempotency_keys (
  caller_id       TEXT NOT NULL,          -- tenantId | service CN
  idempotency_key TEXT NOT NULL,
  request_hash    TEXT NOT NULL,
  response        JSONB NOT NULL,
  status_code     INT  NOT NULL,
  expires_at      TIMESTAMPTZ NOT NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (caller_id, idempotency_key)
);
CREATE INDEX ix_idempotency_expiry ON numbering.idempotency_keys (expires_at);

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

CREATE TABLE numbering.outbox (
  event_id      UUID PRIMARY KEY,
  subject       TEXT NOT NULL,
  aggregate_id  UUID,                     -- number_id for ordering by aggregate
  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 numbering.outbox (created_at)
  WHERE published_at IS NULL;
CREATE INDEX ix_outbox_aggregate_order
  ON numbering.outbox (aggregate_id, created_at)
  WHERE published_at IS NULL;

2. Initial Partitions for `audit`

CREATE TABLE numbering.audit_2026_04
  PARTITION OF numbering.audit
  FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE TABLE numbering.audit_2026_05
  PARTITION OF numbering.audit
  FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
-- Nightly partition-maintenance cron provisions +3 months ahead.

3. Redis Keys

All keys namespaced num:…. Logical DB 7.

Key	TTL	Purpose
`num:valid:{type}:{value}:{tenantId}`	60 s	`ValidateLease` hot-path cache
`num:meta:{type}:{value}`	300 s	Metadata cache for `Lookup`
`num:reserve:{numberId}`	900 s (RESERVE) / 86400 s (HOLD)	Mirror of reservations; drives keyspace-notification expiry
`num:quota:{tenantId}:{class}`	300 s	Cached quota counters (MSISDN / SHORT_CODE / ALPHA / RESERVATIONS)
`num:pool:{tenantId}`	300 s	Cached `TenantPool` config
`num:idem:{callerId}:{key}`	86400 s	Idempotency replay cache
`num:lock:reservation-cleanup`	60 s	Distributed lock for cleanup cron
`num:lock:quarantine-sweep`	60 s	Distributed lock for quarantine sweep
`num:lock:lease-renewal`	600 s	Distributed lock for daily renewal cron
`num:lock:regulator-export`	3600 s	Distributed lock for monthly export cron
`num:lock:reconciliation`	600 s	Distributed lock for nightly reconciliation
`num:rate:reserve:{tenantId}`	60 s sliding window	Per-tenant Reserve flood guard

4. ID Prefixes (externally exposed)

Prefix	Entity
`num_`	NumberResource
`lease_`	Lease
`res_`	Reservation
`pool_`	TenantPool
`ctr_`	LeaseContract
`quar_`	QuarantineRecord
`batch_`	LeaseImportBatch
`exp_`	RegulatorExport
`aud_`	AuditLog

UUIDv4 internally; prefixes applied at REST serialisation layer.

5. Data Classification & Retention

Table / Field	Class	Protection
`numbers.value`	INTERNAL (inventory asset; MSISDN not subscriber-linked at platform level)	TLS in transit; DB-at-rest encryption; not in analytics exports unless hashed
`numbers.assigned_tenant_id`	INTERNAL	TLS; RLS enforced
`leases.tenant_id`	INTERNAL	TLS; RLS enforced
`mno_signing_keys.public_key_pem`	INTERNAL (public keys)	Plain
`lease_contracts.signature_ref`	INTERNAL	Reference to Vault-managed blob
`audit.*`	CONFIDENTIAL (tamper-evident evidence)	Hash-chained; append-only; 13 m hot + 7 y cold
`regulator_exports.s3_ref`	CONFIDENTIAL	S3 object-lock WORM; 7 y retention

Retention:

audit: 13 months hot in PG partitions; older partitions archived to S3 with object lock (7 y) then dropped.
leases: permanent (lease history is business-record-critical).
reservations: permanent (low row count; useful for audit of TTL-expiry behaviour).
lease_import_batches / lease_import_errors: 7 years (regulatory).
idempotency_keys: 24 hours, then DELETE WHERE expires_at < now() cron.
outbox: rows deleted 24 h after published_at by retention cron.

6. Row-Level Security (RLS) Summary

Table	Tenant policy	Admin policy
`numbers`	SELECT own + AVAILABLE rows (for browse)	`platform.numbering.admin`, `platform.auditor`, `platform.numbering.ops` see all
`leases`	SELECT own only	Admin roles see all
`reservations`	Tenants see own (no portal SELECT in current API; kept for future)	Admin roles see all
`tenant_pools`	SELECT own only	Admin roles see all
`quarantine_records`	No tenant read (operational data)	Admin roles see all
`audit`	No tenant read	`platform.auditor`, `platform.numbering.admin`
`lease_contracts`, `lease_import_*`, `regulator_exports`, `mno_signing_keys`	No tenant read	Admin roles only

Handlers call SET LOCAL app.current_tenant_id = :tenant; SET LOCAL app.caller_role = :role; at the start of each transaction.

7. Migration Strategy

All DDL via Prisma migrations, forward-only, reviewed by Security for PII-adjacent columns.
Partition maintenance cron creates next 3 months of audit partitions nightly; missing future partitions → HIGH alert.
Retention enforced by dropping old partitions (never DELETE).
Multi-region DDL follows a two-phase pattern per ADR-0004 §14: new columns added region-by-region before being used; enum values added via ALTER TYPE … ADD VALUE (non-blocking).

8. Cardinality & Capacity Estimates

Table	Expected row count (5 y)	Notes
`numbers`	8–15 M	AF MSISDN space per ATRA plan is ~50 M; expected platform lease < 30 %
`leases`	25 M	Avg 3 lease rows per active number (renewals)
`reservations`	500 M	High churn; aggressive partitioning (monthly) considered for v2
`quarantine_records`	5 M	90-d cool-off churn
`audit`	300 M	Monthly partitions; 13 m hot retention
`lease_import_batches`	1 k	Low; monthly MNO imports
`lease_import_errors`	10 k	Per-batch error rows

Indexes sized accordingly; btree on (value, type) is the only high-cardinality required-unique index.

End of DATA_MODEL.md

1. Tables​

2. Initial Partitions for audit​

3. Redis Keys​

4. ID Prefixes (externally exposed)​

5. Data Classification & Retention​

6. Row-Level Security (RLS) Summary​

7. Migration Strategy​

8. Cardinality & Capacity Estimates​