routing-engine — Data Model

Status: populated | Last updated: 2026-04-18

1. PostgreSQL Schema: ops_routing

routing-engine has read-only access to this schema. Writes are performed by operator-management-service.

1.1 ops_routing.destination_prefixes

CREATE TABLE ops_routing.destination_prefixes (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    prefix      VARCHAR(20)  NOT NULL UNIQUE,   -- E.164 prefix, e.g. '+1', '+447', '+9375'
    country     CHAR(2)      NOT NULL,           -- ISO 3166-1 alpha-2
    description TEXT,
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ  NOT NULL DEFAULT now()
);

CREATE INDEX idx_dest_prefix_prefix ON ops_routing.destination_prefixes (prefix text_pattern_ops);

1.2 ops_routing.operators

Read-replica projection of operator connectivity data. Authoritative source is operator-management-service.

CREATE TABLE ops_routing.operators (
    id             UUID PRIMARY KEY,
    name           VARCHAR(100)  NOT NULL,
    host           VARCHAR(255)  NOT NULL,
    port           INTEGER       NOT NULL CHECK (port BETWEEN 1 AND 65535),
    system_id      VARCHAR(64)   NOT NULL,
    tps_limit      INTEGER       NOT NULL CHECK (tps_limit > 0),
    message_types  TEXT[]        NOT NULL DEFAULT ARRAY['SMS'],
    is_active      BOOLEAN       NOT NULL DEFAULT TRUE,
    created_at     TIMESTAMPTZ   NOT NULL DEFAULT now(),
    updated_at     TIMESTAMPTZ   NOT NULL DEFAULT now()
);

1.3 ops_routing.routing_rules

CREATE TABLE ops_routing.routing_rules (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id  UUID,                                   -- NULL = global rule
    prefix_id   UUID         NOT NULL REFERENCES ops_routing.destination_prefixes(id),
    strategy    VARCHAR(20)  NOT NULL CHECK (strategy IN ('COST','PRIORITY','FAILOVER')),
    is_active   BOOLEAN      NOT NULL DEFAULT TRUE,
    priority    INTEGER      NOT NULL DEFAULT 100,       -- lower = evaluated first when multiple rules match
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ  NOT NULL DEFAULT now(),
    UNIQUE (account_id, prefix_id)
);

CREATE INDEX idx_routing_rules_prefix     ON ops_routing.routing_rules (prefix_id);
CREATE INDEX idx_routing_rules_account    ON ops_routing.routing_rules (account_id);
CREATE INDEX idx_routing_rules_active     ON ops_routing.routing_rules (is_active) WHERE is_active = TRUE;

1.4 ops_routing.routing_rule_operators

CREATE TABLE ops_routing.routing_rule_operators (
    rule_id      UUID            NOT NULL REFERENCES ops_routing.routing_rules(id) ON DELETE CASCADE,
    operator_id  UUID            NOT NULL REFERENCES ops_routing.operators(id),
    cost         NUMERIC(10, 6)  NOT NULL DEFAULT 0,    -- cost per message (COST strategy)
    priority     INTEGER         NOT NULL DEFAULT 1,    -- rank (PRIORITY + FAILOVER strategies)
    PRIMARY KEY (rule_id, operator_id)
);

CREATE INDEX idx_rro_operator ON ops_routing.routing_rule_operators (operator_id);

---

2. Redis Key Schemas

2.1 Routing Decision Cache

Key:   route:decision:{prefix}:{accountId}:{messageType}
Type:  String (JSON)
TTL:   300 seconds

Value (JSON):
{
  "operatorId":  "550e8400-e29b-41d4-a716-446655440001",
  "host":        "smpp1.roshan.af",
  "port":        2775,
  "systemId":    "smsgtw01",
  "tpsLimit":    100,
  "strategy":    "PRIORITY",
  "resolvedAt":  "2026-04-18T10:00:00.000Z"
}

Examples:

route:decision:+93:00000000-0000-0000-0000-000000000001:SMS
route:decision:+9375:00000000-0000-0000-0000-000000000001:FLASH

2.2 Operator Health Cache

Key:   operator:health:{operatorId}
Type:  String (JSON)
TTL:   60 seconds

Value (JSON):
{
  "status":    "BOUND",
  "updatedAt": "2026-04-18T10:30:00.000Z"
}

---

3. In-Memory Caches (Process-Level)

Cache	Contents	Refresh interval
prefixCache	All destination_prefixes rows	Every 60 s via background job

The prefix table is small (typically < 5,000 rows) and read-heavy, making in-process caching cost-effective. It is never written by this service.