Billing Service — Data Model

Status: populated Owner: TBD Last updated: 2026-04-17 Companion: Service Template · NAMING

1. ID prefix registry

Prefix	Entity
acc_	Account
chr_	Charge
chb_	ChargeBatch
inv_	Invoice
ili_	InvoiceLineItem
pay_	Payment
pal_	PaymentAllocation
rfd_	Refund
adj_	Adjustment
led_	LedgerEntry
srun_	StatementRun
stm_	Statement
pl_	PriceList
ple_	PriceEntry
txr_	TaxRule
idp_	IdempotencyRecord

2. TypeScript interfaces

export type Currency = 'AFN' | 'AED' | 'USD' | 'EUR';

export interface Money {
  currency: Currency;
  minor_units: bigint; // banker's integer
}

export type AccountStatus = 'active' | 'suspended' | 'closed';

export interface Account {
  id: AccountId;
  tenantId: TenantId;
  patientId: PatientId;
  currency: Currency;
  status: AccountStatus;
  version: number;          // optimistic locking
  createdAt: string;
  updatedAt: string;
}

export interface LedgerEntry {
  id: LedgerEntryId;
  tenantId: TenantId;
  accountId: AccountId;
  type: 'CHARGE' | 'PAYMENT' | 'REFUND' | 'ADJUSTMENT' | 'REVERSAL';
  amount: Money;            // signed
  effectiveAt: string;      // service date for charges, posted date for cash
  postedAt: string;
  sourceType: 'charge' | 'payment' | 'refund' | 'adjustment';
  sourceId: string;
  reversalOf: LedgerEntryId | null;
  createdAt: string;
}

export interface Charge {
  id: ChargeId;
  tenantId: TenantId;
  accountId: AccountId;
  encounterId: EncounterId | null;
  facilityId: FacilityId;
  providerId: ProviderId | null;
  code: { system: string; code: string; display?: string };
  modifiers: { system: string; code: string }[];
  units: number;
  unitPrice: Money;
  taxAmount: Money;
  totalAmount: Money;
  serviceDate: string;
  status: 'draft' | 'posted' | 'reversed' | 'cancelled';
  ledgerEntryId: LedgerEntryId | null;
  createdAt: string;
}

export interface Invoice {
  id: InvoiceId;
  tenantId: TenantId;
  accountId: AccountId;
  encounterId: EncounterId | null;
  status: 'draft' | 'issued' | 'partially_paid' | 'paid' | 'voided' | 'cancelled';
  subtotal: Money;
  taxAmount: Money;
  total: Money;
  issuedAt: string | null;
  voidedAt: string | null;
  pdfObjectKey: string | null;
  version: number;
  createdAt: string;
  updatedAt: string;
}

export interface InvoiceLineItem {
  id: InvoiceLineItemId;
  invoiceId: InvoiceId;
  chargeId: ChargeId;
  code: { system: string; code: string };
  description: string | null;
  units: number;
  unitPrice: Money;
  subtotal: Money;
  taxAmount: Money;
  total: Money;
  position: number;
}

export interface Payment {
  id: PaymentId;
  tenantId: TenantId;
  accountId: AccountId;
  method: 'CASH' | 'CARD' | 'BANK_TRANSFER' | 'MOBILE_MONEY' | 'PAYER_REMITTANCE' | 'CHECK';
  amount: Money;
  reference: string | null;
  externalRef: string | null;     // gateway ref
  idempotencyKey: string;
  postedAt: string;
  reversedAt: string | null;
  createdAt: string;
}

export interface PaymentAllocation {
  id: PaymentAllocationId;
  paymentId: PaymentId;
  invoiceId: InvoiceId | null;
  lineItemId: InvoiceLineItemId | null;
  amount: Money;
}

export interface Refund {
  id: RefundId;
  tenantId: TenantId;
  originalPaymentId: PaymentId;
  accountId: AccountId;
  amount: Money;
  reason: string;
  status: 'requested' | 'pending_approval' | 'approved' | 'rejected' | 'posted' | 'reversed';
  requestedBy: UserId;
  approvedBy: UserId | null;
  rejectedReason: string | null;
  idempotencyKey: string;
  postedAt: string | null;
  createdAt: string;
}

export interface Adjustment {
  id: AdjustmentId;
  tenantId: TenantId;
  accountId: AccountId;
  amount: Money;
  reason: 'WRITE_OFF' | 'COURTESY' | 'CONTRACTUAL' | 'CODING_CORRECTION' | 'BAD_DEBT' | 'OTHER';
  relatedInvoiceId: InvoiceId | null;
  claimId: string | null;
  postedAt: string;
  idempotencyKey: string;
  createdAt: string;
}

export interface PriceList {
  id: PriceListId;
  tenantId: TenantId;
  facilityId: FacilityId | null;
  name: string;
  currency: Currency;
  status: 'draft' | 'published' | 'retired';
  effectiveFrom: string;
  effectiveTo: string | null;
  publishedAt: string | null;
  createdAt: string;
}

export interface PriceEntry {
  id: PriceEntryId;
  priceListId: PriceListId;
  code: { system: string; code: string };
  amount: Money;
  effectiveFrom: string;
  effectiveTo: string | null;
}

export interface TaxRule {
  id: TaxRuleId;
  tenantId: TenantId;
  facilityId: FacilityId | null;
  country: string;          // ISO-3166
  rate: number;             // 0.05 for 5%
  appliesTo: string[];      // code systems or wildcard
  effectiveFrom: string;
  effectiveTo: string | null;
}

export interface StatementRun {
  id: StatementRunId;
  tenantId: TenantId;
  facilityId: FacilityId | null;
  asOfDate: string;
  language: string;
  delivery: ('PRINT' | 'SMS' | 'EMAIL')[];
  status: 'queued' | 'running' | 'completed' | 'failed';
  generated: number;
  failed: number;
  startedAt: string | null;
  completedAt: string | null;
  createdAt: string;
}

export interface IdempotencyRecord {
  key: string;
  tenantId: TenantId;
  requestHash: string;
  responseJson: object;
  statusCode: number;
  createdAt: string;
  expiresAt: string;
}

3. PostgreSQL schema (billing namespace)

-- schema
CREATE SCHEMA IF NOT EXISTS billing;

-- Accounts
CREATE TABLE billing.accounts (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  patient_id TEXT NOT NULL,
  currency TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('active','suspended','closed')),
  version INT NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (tenant_id, patient_id, currency)
);
CREATE INDEX ix_accounts_tenant_patient ON billing.accounts (tenant_id, patient_id);

-- Ledger (APPEND-ONLY)
CREATE TABLE billing.ledger_entries (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  account_id TEXT NOT NULL REFERENCES billing.accounts(id),
  type TEXT NOT NULL CHECK (type IN ('CHARGE','PAYMENT','REFUND','ADJUSTMENT','REVERSAL')),
  amount_minor BIGINT NOT NULL,
  currency TEXT NOT NULL,
  effective_at TIMESTAMPTZ NOT NULL,
  posted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  source_type TEXT NOT NULL,
  source_id TEXT NOT NULL,
  reversal_of TEXT REFERENCES billing.ledger_entries(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX ix_ledger_account_posted ON billing.ledger_entries (tenant_id, account_id, posted_at DESC);
CREATE INDEX ix_ledger_source ON billing.ledger_entries (tenant_id, source_type, source_id);

-- Enforce append-only
CREATE OR REPLACE FUNCTION billing.block_ledger_mutation() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'ledger_entries is append-only';
END; $$;
CREATE TRIGGER ledger_no_update BEFORE UPDATE OR DELETE ON billing.ledger_entries
  FOR EACH ROW EXECUTE FUNCTION billing.block_ledger_mutation();

-- Charges
CREATE TABLE billing.charges (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  account_id TEXT NOT NULL REFERENCES billing.accounts(id),
  encounter_id TEXT,
  facility_id TEXT NOT NULL,
  provider_id TEXT,
  code_system TEXT NOT NULL,
  code TEXT NOT NULL,
  display TEXT,
  modifiers JSONB NOT NULL DEFAULT '[]'::jsonb,
  units NUMERIC(12,4) NOT NULL,
  unit_price_minor BIGINT NOT NULL,
  tax_amount_minor BIGINT NOT NULL,
  total_amount_minor BIGINT NOT NULL,
  currency TEXT NOT NULL,
  service_date DATE NOT NULL,
  status TEXT NOT NULL,
  ledger_entry_id TEXT REFERENCES billing.ledger_entries(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX ix_charges_tenant_encounter ON billing.charges (tenant_id, encounter_id);
CREATE INDEX ix_charges_tenant_facility_date ON billing.charges (tenant_id, facility_id, service_date);

-- Invoices
CREATE TABLE billing.invoices (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  account_id TEXT NOT NULL REFERENCES billing.accounts(id),
  encounter_id TEXT,
  status TEXT NOT NULL,
  subtotal_minor BIGINT NOT NULL,
  tax_minor BIGINT NOT NULL,
  total_minor BIGINT NOT NULL,
  currency TEXT NOT NULL,
  issued_at TIMESTAMPTZ,
  voided_at TIMESTAMPTZ,
  pdf_object_key TEXT,
  version INT NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX ix_invoices_tenant_account_status ON billing.invoices (tenant_id, account_id, status);
CREATE INDEX ix_invoices_tenant_issued ON billing.invoices (tenant_id, issued_at DESC);

CREATE TABLE billing.invoice_line_items (
  id TEXT PRIMARY KEY,
  invoice_id TEXT NOT NULL REFERENCES billing.invoices(id) ON DELETE CASCADE,
  tenant_id TEXT NOT NULL,
  charge_id TEXT NOT NULL REFERENCES billing.charges(id),
  code_system TEXT NOT NULL,
  code TEXT NOT NULL,
  description TEXT,
  units NUMERIC(12,4) NOT NULL,
  unit_price_minor BIGINT NOT NULL,
  subtotal_minor BIGINT NOT NULL,
  tax_minor BIGINT NOT NULL,
  total_minor BIGINT NOT NULL,
  position INT NOT NULL
);
CREATE INDEX ix_ili_invoice ON billing.invoice_line_items (invoice_id);

-- Payments
CREATE TABLE billing.payments (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  account_id TEXT NOT NULL REFERENCES billing.accounts(id),
  method TEXT NOT NULL,
  amount_minor BIGINT NOT NULL,
  currency TEXT NOT NULL,
  reference TEXT,
  external_ref TEXT,
  idempotency_key TEXT NOT NULL,
  posted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  reversed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (tenant_id, idempotency_key)
);
CREATE INDEX ix_payments_tenant_account_posted ON billing.payments (tenant_id, account_id, posted_at DESC);

CREATE TABLE billing.payment_allocations (
  id TEXT PRIMARY KEY,
  payment_id TEXT NOT NULL REFERENCES billing.payments(id) ON DELETE CASCADE,
  invoice_id TEXT REFERENCES billing.invoices(id),
  line_item_id TEXT REFERENCES billing.invoice_line_items(id),
  amount_minor BIGINT NOT NULL
);
CREATE INDEX ix_alloc_payment ON billing.payment_allocations (payment_id);
CREATE INDEX ix_alloc_invoice ON billing.payment_allocations (invoice_id);

-- Refunds
CREATE TABLE billing.refunds (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  original_payment_id TEXT NOT NULL REFERENCES billing.payments(id),
  account_id TEXT NOT NULL REFERENCES billing.accounts(id),
  amount_minor BIGINT NOT NULL,
  currency TEXT NOT NULL,
  reason TEXT NOT NULL,
  status TEXT NOT NULL,
  requested_by TEXT NOT NULL,
  approved_by TEXT,
  rejected_reason TEXT,
  idempotency_key TEXT NOT NULL,
  posted_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (tenant_id, idempotency_key)
);

-- Adjustments
CREATE TABLE billing.adjustments (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  account_id TEXT NOT NULL REFERENCES billing.accounts(id),
  amount_minor BIGINT NOT NULL,
  currency TEXT NOT NULL,
  reason TEXT NOT NULL,
  related_invoice_id TEXT REFERENCES billing.invoices(id),
  claim_id TEXT,
  posted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  idempotency_key TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (tenant_id, idempotency_key)
);

-- Price list + entries
CREATE TABLE billing.price_lists (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  facility_id TEXT,
  name TEXT NOT NULL,
  currency TEXT NOT NULL,
  status TEXT NOT NULL,
  effective_from DATE NOT NULL,
  effective_to DATE,
  published_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX ix_pl_tenant_status ON billing.price_lists (tenant_id, status);

CREATE TABLE billing.price_entries (
  id TEXT PRIMARY KEY,
  price_list_id TEXT NOT NULL REFERENCES billing.price_lists(id) ON DELETE CASCADE,
  code_system TEXT NOT NULL,
  code TEXT NOT NULL,
  amount_minor BIGINT NOT NULL,
  currency TEXT NOT NULL,
  effective_from DATE NOT NULL,
  effective_to DATE
);
CREATE INDEX ix_pe_list_code ON billing.price_entries (price_list_id, code_system, code);

-- Tax rules
CREATE TABLE billing.tax_rules (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  facility_id TEXT,
  country TEXT NOT NULL,
  rate NUMERIC(6,4) NOT NULL,
  applies_to JSONB NOT NULL DEFAULT '[]'::jsonb,
  effective_from DATE NOT NULL,
  effective_to DATE
);

-- Statement runs
CREATE TABLE billing.statement_runs (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  facility_id TEXT,
  as_of_date DATE NOT NULL,
  language TEXT NOT NULL,
  delivery JSONB NOT NULL,
  status TEXT NOT NULL,
  generated INT NOT NULL DEFAULT 0,
  failed INT NOT NULL DEFAULT 0,
  started_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Idempotency
CREATE TABLE billing.idempotency (
  key TEXT NOT NULL,
  tenant_id TEXT NOT NULL,
  request_hash TEXT NOT NULL,
  response_json JSONB NOT NULL,
  status_code INT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expires_at TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (tenant_id, key)
);
CREATE INDEX ix_idem_expires ON billing.idempotency (expires_at);

-- Outbox / Inbox
CREATE TABLE billing.outbox (
  id TEXT PRIMARY KEY,
  tenant_id TEXT NOT NULL,
  subject TEXT NOT NULL,
  payload JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  published_at TIMESTAMPTZ
);
CREATE INDEX ix_outbox_unpublished ON billing.outbox (published_at) WHERE published_at IS NULL;

CREATE TABLE billing.inbox (
  source TEXT NOT NULL,
  event_id TEXT NOT NULL,
  processed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (source, event_id)
);

4. Row-level security

-- Enable RLS on every tenant-scoped table
ALTER TABLE billing.accounts ENABLE ROW LEVEL SECURITY;
ALTER TABLE billing.ledger_entries ENABLE ROW LEVEL SECURITY;
ALTER TABLE billing.charges ENABLE ROW LEVEL SECURITY;
ALTER TABLE billing.invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE billing.invoice_line_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE billing.payments ENABLE ROW LEVEL SECURITY;
ALTER TABLE billing.payment_allocations ENABLE ROW LEVEL SECURITY;
ALTER TABLE billing.refunds ENABLE ROW LEVEL SECURITY;
ALTER TABLE billing.adjustments ENABLE ROW LEVEL SECURITY;
ALTER TABLE billing.price_lists ENABLE ROW LEVEL SECURITY;
ALTER TABLE billing.tax_rules ENABLE ROW LEVEL SECURITY;
ALTER TABLE billing.statement_runs ENABLE ROW LEVEL SECURITY;
ALTER TABLE billing.idempotency ENABLE ROW LEVEL SECURITY;
ALTER TABLE billing.outbox ENABLE ROW LEVEL SECURITY;

-- Uniform policy
CREATE POLICY accounts_tenant_isolation ON billing.accounts
  USING (tenant_id = current_setting('app.tenant_id', TRUE));
-- Repeat for each table (name: {table}_tenant_isolation)

5. Materialised views

CREATE MATERIALIZED VIEW billing.account_balances AS
SELECT
  a.tenant_id,
  a.id AS account_id,
  a.currency,
  COALESCE(SUM(l.amount_minor), 0) AS balance_minor
FROM billing.accounts a
LEFT JOIN billing.ledger_entries l ON l.account_id = a.id
GROUP BY a.tenant_id, a.id, a.currency;

CREATE UNIQUE INDEX ix_mv_balances ON billing.account_balances (tenant_id, account_id);

-- Refreshed incrementally via trigger on ledger_entries insert (debounced background job)

6. Retention & archival

Table	Hot retention	Cold / archive	Notes
ledger_entries	indefinite	—	Financial record, 10+ yr per jurisdiction
charges, invoices, invoice_line_items, payments, refunds, adjustments	indefinite	—	Financial record
outbox	30 d then purge published rows	—	Published rows deleted nightly
inbox	180 d	—	Dedup window
idempotency	TTL via expires_at (24 h default)	—	Purge nightly
statement_runs	2 y	Archive to object store	Statement PDFs in object storage