Billing Service — Data Model

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

Schema: billing. Owned exclusively by billing-service. No cross-service direct writes.

1. Tables

billing.billing_events

CREATE TABLE billing.billing_events (
  billing_event_id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  message_id         UUID NOT NULL UNIQUE,          -- dedup key
  tenant_id          UUID NOT NULL,
  account_id         UUID NOT NULL,
  operator_id        TEXT NOT NULL,
  direction          TEXT NOT NULL DEFAULT 'MT',
  segment_count      SMALLINT NOT NULL,
  pricing_table_id   UUID NOT NULL REFERENCES billing.pricing_tables(pricing_table_id),
  customer_price     NUMERIC(12,6) NOT NULL,
  operator_cost      NUMERIC(12,6) NOT NULL,
  margin             NUMERIC(12,6) NOT NULL,
  currency           CHAR(3) NOT NULL DEFAULT 'USD',
  charged_at         TIMESTAMPTZ NOT NULL,
  created_at         TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (charged_at);
-- Monthly partitions, 13-month hot retention then archive

Indexes:

• ix_billing_events_account_id_charged_at on (account_id, charged_at DESC)
• ix_billing_events_tenant_id_charged_at on (tenant_id, charged_at DESC)
• ix_billing_events_operator_id on (operator_id)

RLS:

ALTER TABLE billing.billing_events ENABLE ROW LEVEL SECURITY;
CREATE POLICY billing_events_tenant ON billing.billing_events
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

billing.pricing_tables

CREATE TABLE billing.pricing_tables (
  pricing_table_id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  account_tier       TEXT NOT NULL,
  operator_id        TEXT NOT NULL,
  direction          TEXT NOT NULL DEFAULT 'MT',
  pricing_model      TEXT NOT NULL,                 -- 'PER_SEGMENT' | 'FLAT_PER_MESSAGE'
  unit_price         NUMERIC(12,6) NOT NULL,
  currency           CHAR(3) NOT NULL DEFAULT 'USD',
  effective_from     DATE NOT NULL,
  effective_to       DATE,                          -- NULL = currently active
  created_by         TEXT NOT NULL,
  created_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at         TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX ix_pricing_tables_active
  ON billing.pricing_tables (account_tier, operator_id, direction, currency)
  WHERE effective_to IS NULL;

billing.operator_costs

CREATE TABLE billing.operator_costs (
  operator_cost_id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  operator_id        TEXT NOT NULL,
  direction          TEXT NOT NULL DEFAULT 'MT',
  cost_per_segment   NUMERIC(12,6) NOT NULL,
  currency           CHAR(3) NOT NULL DEFAULT 'USD',
  effective_from     DATE NOT NULL,
  effective_to       DATE,
  created_by         TEXT NOT NULL,
  created_at         TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX ix_operator_costs_active
  ON billing.operator_costs (operator_id, direction, currency)
  WHERE effective_to IS NULL;

billing.invoices

CREATE TABLE billing.invoices (
  invoice_id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id          UUID NOT NULL,
  account_id         UUID NOT NULL,
  period_start       DATE NOT NULL,
  period_end         DATE NOT NULL,
  total_messages     BIGINT NOT NULL DEFAULT 0,
  total_segments     BIGINT NOT NULL DEFAULT 0,
  subtotal_amount    NUMERIC(14,2) NOT NULL DEFAULT 0,
  currency           CHAR(3) NOT NULL DEFAULT 'USD',
  status             TEXT NOT NULL DEFAULT 'DRAFT',
  s3_key             TEXT,
  generated_at       TIMESTAMPTZ,
  voided_at          TIMESTAMPTZ,
  voided_by          TEXT,
  void_reason        TEXT,
  created_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (account_id, period_start)
);

billing.usage_summaries

CREATE TABLE billing.usage_summaries (
  summary_id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id           UUID NOT NULL,
  account_id          UUID NOT NULL,
  operator_id         TEXT NOT NULL,
  bucket_hour         TIMESTAMPTZ NOT NULL,          -- date_trunc('hour', charged_at)
  message_count       BIGINT NOT NULL DEFAULT 0,
  segment_count       BIGINT NOT NULL DEFAULT 0,
  total_customer_price NUMERIC(14,4) NOT NULL DEFAULT 0,
  total_operator_cost  NUMERIC(14,4) NOT NULL DEFAULT 0,
  currency            CHAR(3) NOT NULL DEFAULT 'USD',
  UNIQUE (account_id, operator_id, bucket_hour, currency)
) PARTITION BY RANGE (bucket_hour);

Indexes:

• ix_usage_summaries_account_bucket on (account_id, bucket_hour DESC)
• ix_usage_summaries_tenant_bucket on (tenant_id, bucket_hour DESC)

2. Redis Keys

Key pattern	TTL	Purpose
billing:pricing:{accountTier}:{operatorId}:{direction}	60s	Pricing lookup cache

3. TypeScript Interfaces

export interface BillingEventRow {
  billingEventId: string;
  messageId: string;
  tenantId: string;
  accountId: string;
  operatorId: string;
  direction: 'MT';
  segmentCount: number;
  pricingTableId: string;
  customerPrice: string;  // NUMERIC as string for precision
  operatorCost: string;
  margin: string;
  currency: string;
  chargedAt: Date;
  createdAt: Date;
}

export interface PricingTableRow {
  pricingTableId: string;
  accountTier: string;
  operatorId: string;
  direction: string;
  pricingModel: 'PER_SEGMENT' | 'FLAT_PER_MESSAGE';
  unitPrice: string;
  currency: string;
  effectiveFrom: Date;
  effectiveTo: Date | null;
}

4. Partitioning & Retention

• billing_events: monthly range partitions on charged_at; 13-month hot retention, then archive to S3 Parquet.
• usage_summaries: monthly range partitions on bucket_hour; 25-month hot retention (2 full years for YoY comparison).
• Partition maintenance: pg_partman or monthly cron creating next month's partition.