Skip to main content

Operator Management Service — Data Model

Status: populated Owner: Platform Engineering Last updated: 2026-04-18 Companion: DOMAIN_MODEL

Schema: ops. Owned exclusively by operator-management-service. No cross-service writes.

1. Tables​

ops.operators​

CREATE TABLE ops.operators (
  operator_id       UUID PRIMARY KEY,
  name              TEXT NOT NULL UNIQUE,
  host              TEXT NOT NULL,
  port              INTEGER NOT NULL CHECK (port BETWEEN 1 AND 65535),
  system_id         TEXT NOT NULL,
  system_type       TEXT NOT NULL DEFAULT '',
  bind_type         TEXT NOT NULL,             -- 'TRANSCEIVER'|'TRANSMITTER'|'RECEIVER'
  status            TEXT NOT NULL DEFAULT 'INACTIVE',
  health_state      TEXT NOT NULL DEFAULT 'UNKNOWN',
  max_tps           INTEGER NOT NULL,
  burst_multiplier  NUMERIC(4,2) NOT NULL DEFAULT 1.5,
  enforcement_mode  TEXT NOT NULL DEFAULT 'HARD_REJECT',
  notes             TEXT,
  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,
  CONSTRAINT uq_operators_host_port_system_id UNIQUE (host, port, system_id)
);
CREATE INDEX ix_ops_operators_status ON ops.operators(status) WHERE deleted_at IS NULL;

Note: No password column. SMPP credentials live exclusively in Vault at secret/ops/operators/{operator_id}/credentials.

ops.routing_rules​

CREATE TABLE ops.routing_rules (
  rule_id           UUID PRIMARY KEY,
  operator_id       UUID NOT NULL REFERENCES ops.operators(operator_id),
  prefix            TEXT NOT NULL,             -- E.164 prefix e.g. '+44'
  mcc               TEXT,
  mnc               TEXT,
  priority          INTEGER NOT NULL DEFAULT 10,
  weight            INTEGER NOT NULL DEFAULT 100,
  cost_per_segment  NUMERIC(18,6) NOT NULL,
  currency          CHAR(3) NOT NULL DEFAULT 'USD',
  active            BOOLEAN NOT NULL DEFAULT true,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ix_routing_rules_operator_active ON ops.routing_rules(operator_id, active);
CREATE INDEX ix_routing_rules_prefix ON ops.routing_rules(prefix) WHERE active = true;

ops.destination_prefixes​

Normalized prefix table for fast range-scan routing lookups. Populated/maintained by service whenever routing_rules change.

CREATE TABLE ops.destination_prefixes (
  prefix            TEXT NOT NULL,
  rule_id           UUID NOT NULL REFERENCES ops.routing_rules(rule_id) ON DELETE CASCADE,
  operator_id       UUID NOT NULL,
  priority          INTEGER NOT NULL,
  weight            INTEGER NOT NULL,
  PRIMARY KEY (prefix, rule_id)
);
CREATE INDEX ix_dest_prefixes_prefix_op ON ops.destination_prefixes(prefix, priority);

ops.tps_limits​

Standalone table for per-operator TPS windows (allows historical audit on limit changes).

CREATE TABLE ops.tps_limits (
  id                UUID PRIMARY KEY,
  operator_id       UUID NOT NULL REFERENCES ops.operators(operator_id),
  max_tps           INTEGER NOT NULL,
  burst_multiplier  NUMERIC(4,2) NOT NULL,
  enforcement_mode  TEXT NOT NULL,
  effective_from    TIMESTAMPTZ NOT NULL DEFAULT now(),
  set_by            UUID NOT NULL,
  notes             TEXT
);

ops.operator_health_log​

CREATE TABLE ops.operator_health_log (
  id                BIGSERIAL PRIMARY KEY,
  operator_id       UUID NOT NULL REFERENCES ops.operators(operator_id),
  previous_state    TEXT NOT NULL,
  new_state         TEXT NOT NULL,
  error_rate        NUMERIC(6,4),
  bind_error        TEXT,
  recorded_at       TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (recorded_at);
-- Monthly partitions; retain 90 days

2. Redis Keys​

KeyTTLValueNotes
ops:health:{operatorId}60 s{ state: "HEALTHY|DEGRADED|UNHEALTHY|UNKNOWN", errorRate: 0.012, updatedAt: "..." }Authoritative health state for routing-engine

Routing-engine reads this key directly. On Redis miss, routing-engine falls back to calling GET /v1/internal/operators from this service.

3. Vault Paths​

PathFieldsWho reads
secret/ops/operators/{operatorId}/credentials{ systemId, password, updatedAt }operator-management-service (internal API), smpp-connector (via internal API)

No service reads Vault directly except this service. smpp-connector calls the internal HTTP endpoint, which fetches from Vault.

4. TypeScript Interfaces​

export interface OperatorRow {
  operatorId: string;
  name: string;
  host: string;
  port: number;
  systemId: string;
  systemType: string;
  bindType: 'TRANSCEIVER' | 'TRANSMITTER' | 'RECEIVER';
  status: 'ACTIVE' | 'INACTIVE' | 'SUSPENDED';
  healthState: 'HEALTHY' | 'DEGRADED' | 'UNHEALTHY' | 'UNKNOWN';
  maxTps: number;
  burstMultiplier: number;
  enforcementMode: 'HARD_REJECT' | 'SOFT_THROTTLE';
  createdBy: string;
  updatedBy: string;
  createdAt: Date;
  updatedAt: Date;
  deletedAt: Date | null;
}

export interface OperatorCredentials {
  operatorId: string;
  systemId: string;
  password: string; // from Vault only; never persisted to PG
}

5. Retention​

  • ops.operators: indefinite (soft-delete; never purge — audit requirement).
  • ops.operator_health_log: 90 days hot; archive to cold storage.
  • ops.tps_limits: indefinite (compliance audit trail for rate limit changes).