Data Model

:::info Source Sourced from services/billing-service/DATA_MODEL.md in the documentation repo. :::

1. Database

Postgres 16 schema billing. 7-year retention for tax compliance.

2. Tables

2.1 customers

CREATE TABLE billing.customers (
  id ULID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  processor TEXT NOT NULL,             -- 'stripe'
  processor_customer_id TEXT NOT NULL, -- Stripe customer ID
  email TEXT,
  name TEXT,
  tax_ids JSONB,
  default_payment_method_id TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);
CREATE UNIQUE INDEX cust_processor ON billing.customers (processor, processor_customer_id);

2.2 subscriptions

CREATE TABLE billing.subscriptions (
  id ULID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  customer_id ULID NOT NULL,
  plan_id TEXT NOT NULL,
  state TEXT NOT NULL CHECK (state IN ('trialing','active','past_due','canceled','paused')),
  current_period_start TIMESTAMPTZ NOT NULL,
  current_period_end TIMESTAMPTZ NOT NULL,
  trial_end TIMESTAMPTZ,
  cancel_at TIMESTAMPTZ,
  item_quantities JSONB NOT NULL,
  processor_sub_id TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);
CREATE INDEX subs_by_tenant ON billing.subscriptions (tenant_id, state);
CREATE INDEX subs_period_end ON billing.subscriptions (current_period_end) WHERE state = 'active';

2.3 invoices

CREATE TABLE billing.invoices (
  id ULID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  customer_id ULID NOT NULL,
  subscription_id ULID,
  number TEXT UNIQUE,                  -- human-readable
  lines JSONB NOT NULL,
  subtotals_micro BIGINT NOT NULL,
  taxes JSONB NOT NULL,
  total_micro BIGINT NOT NULL,
  currency CHAR(3) NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('draft','open','paid','void','uncollectible')),
  due_at TIMESTAMPTZ,
  pdf_url TEXT,
  processor_invoice_id TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  paid_at TIMESTAMPTZ
);
CREATE INDEX inv_by_tenant ON billing.invoices (tenant_id, status, created_at DESC);

2.4 payments

CREATE TABLE billing.payments (
  id ULID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  invoice_id ULID,
  order_id ULID,
  amount_micro BIGINT NOT NULL,
  currency CHAR(3) NOT NULL,
  processor TEXT NOT NULL,
  processor_ref TEXT UNIQUE,
  status TEXT NOT NULL CHECK (status IN ('requires_action','pending','succeeded','failed','refunded')),
  failure_reason TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  succeeded_at TIMESTAMPTZ,
  refunded_at TIMESTAMPTZ
);
CREATE INDEX pay_by_order ON billing.payments (order_id);
CREATE INDEX pay_by_invoice ON billing.payments (invoice_id);
CREATE INDEX pay_by_tenant ON billing.payments (tenant_id, created_at DESC);

2.5 refunds

CREATE TABLE billing.refunds (
  id ULID PRIMARY KEY,
  payment_id ULID NOT NULL REFERENCES billing.payments(id),
  tenant_id UUID NOT NULL,
  amount_micro BIGINT NOT NULL,
  reason TEXT NOT NULL,
  processor_ref TEXT UNIQUE,
  refunded_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

2.6 dunning_processes

CREATE TABLE billing.dunning_processes (
  id ULID PRIMARY KEY,
  subscription_id ULID NOT NULL,
  tenant_id UUID NOT NULL,
  started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  stage TEXT NOT NULL,
  next_attempt_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,
  resolution TEXT
);
CREATE INDEX dunning_due ON billing.dunning_processes (next_attempt_at) WHERE completed_at IS NULL;

2.7 payouts / payout_batches

CREATE TABLE billing.payouts (
  id ULID PRIMARY KEY,
  provider_tenant_id UUID NOT NULL,
  amount_micro BIGINT NOT NULL,
  currency CHAR(3) NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  status TEXT NOT NULL,
  processor TEXT,
  processor_ref TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  completed_at TIMESTAMPTZ
);
CREATE TABLE billing.payout_batches (id ULID PRIMARY KEY, run_at TIMESTAMPTZ NOT NULL, status TEXT NOT NULL);
CREATE TABLE billing.payout_batch_items (batch_id ULID, payout_id ULID, PRIMARY KEY (batch_id, payout_id));

2.8 tax_lines

Embedded in invoices JSONB. Per-jurisdiction with rate, name, amount.

2.9 payment_methods

CREATE TABLE billing.payment_methods (
  id ULID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  customer_id ULID NOT NULL,
  processor TEXT NOT NULL,
  processor_pm_id TEXT NOT NULL,
  type TEXT NOT NULL,                  -- 'card', 'bank_account', 'sepa_debit'
  last4 TEXT,
  brand TEXT,
  exp_month INT,
  exp_year INT,
  is_default BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now()
);

2.10 webhook_events (for replay + dedup)

CREATE TABLE billing.webhook_events (
  event_id TEXT PRIMARY KEY,          -- Stripe event ID
  processor TEXT NOT NULL,
  received_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  payload JSONB NOT NULL,
  processed_at TIMESTAMPTZ,
  result TEXT
);

3. RLS

All tables except webhook_events (tenant inferred from event content):

ALTER TABLE billing.subscriptions ENABLE ROW LEVEL SECURITY;
CREATE POLICY subs_tenant_iso ON billing.subscriptions USING (tenant_id = current_setting('app.tenant_id')::uuid);
-- similar for all

4. Indexing

Covered in table defs. Additional:

• Partial indexes on state='active' for subscriptions.
• (tenant_id, created_at DESC) on invoices/payments for admin queries.

5. Retention

• 7 years hot; 10 years cold (some jurisdictions require longer).
• webhook_events 90 days (replay window).
• Legal hold overrides GDPR erasure.

6. Migration

Additive only for minors. Currency migration (new currencies) additive. Tax jurisdiction additions additive.

7. Performance

• Nightly subscription renewal batch: 100k subscriptions in 10 min.
• Invoice generation: batched by currency + jurisdiction.
• Indexes on current_period_end for fast due-now queries.