Skip to main content

DATA_MODEL — billing-service

Conforms to 06 Data Models, ADR-0002 Multi-Tenancy, standards/NAMING.md. Storage is Cloud SQL Postgres 16 with two physical schema patterns:

  • Per-tenant schema tenant_<uuid>_billing for all guest folio data (folios, charges, payments, refunds, invoices, credit notes, settlements, cash drawers, sessions, daily reconciliations, outbox).
  • Central schema billing_central for platform subscription billing (subscriptions, subscription invoices, usage records, plans, dunning history, central outbox).

Per-tenant schemas are created by the tenant-provisioning-job (Cloud Run Job) on melmastoon.tenant.created.v1 from a templated DDL bundle. PgBouncer sets search_path per connection based on the X-Tenant-Id header propagated through the request context. RLS is also enabled inside each per-tenant schema as defense-in-depth (mistakes that leak a connection across tenants still fail at the row level).

1. ID prefixes (additions to NAMING.md)​

PrefixAggregate / table
fol_folios
chg_folio_charges
fpm_folio_payments
frd_folio_refunds
inv_doc_invoices (existing)
ln_invoice_lines
cnt_credit_notes
cnl_credit_note_lines
set_settlements
cdr_cash_drawers
cds_cash_drawer_sessions
dcr_daily_cash_reconciliations
sub_subscriptions
sin_subscription_invoices
sil_subscription_invoice_lines
usg_usage_records
pln_plans

All ULIDs; bigint money columns are suffixed _micro per NAMING §6.

2. Per-tenant schema template (tenant_<uuid>_billing)​

Provisioned by db/tenants/billing.tmpl.sql substituting :tenant_id. Migrations bumped via the per-tenant migration job; every tenant runs the same forward DDL.

2.1 folios​

CREATE TABLE folios (
  id              text PRIMARY KEY CHECK (id LIKE 'fol_%'),
  tenant_id       text NOT NULL CHECK (tenant_id LIKE 't_%'),
  property_id     text NOT NULL CHECK (property_id LIKE 'prop_%'),
  reservation_id  text NOT NULL CHECK (reservation_id LIKE 'res_%'),
  currency        char(3) NOT NULL,
  fx_snapshot     jsonb NOT NULL,           -- { baseCurrency, rates, takenAt, source }
  status          text NOT NULL CHECK (status IN ('pending','open','balance_due','settled','closed','re_opened')),
  opened_at       timestamptz NOT NULL,
  closed_at       timestamptz,
  reopened_count  int NOT NULL DEFAULT 0,
  version         int NOT NULL DEFAULT 1,
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, reservation_id)
);
CREATE INDEX folios_tenant_property_idx ON folios (tenant_id, property_id);
CREATE INDEX folios_status_idx ON folios (tenant_id, status) WHERE status IN ('open','balance_due');
ALTER TABLE folios ENABLE ROW LEVEL SECURITY;
CREATE POLICY folios_tenant_isolation ON folios
  USING (tenant_id = current_setting('app.tenant_id', true));

2.2 folio_charges​

CREATE TABLE folio_charges (
  id                 text PRIMARY KEY CHECK (id LIKE 'chg_%'),
  folio_id           text NOT NULL REFERENCES folios(id) ON DELETE RESTRICT,
  tenant_id          text NOT NULL,
  kind               text NOT NULL CHECK (kind IN ('room_night','tax','fee','mini_bar','restaurant','laundry','service','adjustment','late_fee')),
  description        jsonb NOT NULL,         -- { default, locales: { ps,ar,en,fr,fa,tg } }
  quantity           int NOT NULL CHECK (quantity > 0),
  unit_price_micro   bigint NOT NULL CHECK (unit_price_micro >= 0),
  gross_micro        bigint NOT NULL CHECK (gross_micro >= 0),
  currency           char(3) NOT NULL,
  tax_code           text NOT NULL,
  tax_rate_num       bigint NOT NULL,
  tax_rate_den       bigint NOT NULL,
  tax_amount_micro   bigint NOT NULL,
  tax_jurisdiction   text NOT NULL,
  posted_at          timestamptz NOT NULL,
  posted_by          text NOT NULL,
  source_kind        text NOT NULL CHECK (source_kind IN ('rate_plan','pos','manual','event')),
  source_ref         text,
  voided_at          timestamptz,
  voided_by          text,
  void_reason        text,
  created_at         timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX folio_charges_folio_idx ON folio_charges (folio_id);
CREATE INDEX folio_charges_tenant_posted_idx ON folio_charges (tenant_id, posted_at);
ALTER TABLE folio_charges ENABLE ROW LEVEL SECURITY;
CREATE POLICY folio_charges_tenant_isolation ON folio_charges
  USING (tenant_id = current_setting('app.tenant_id', true));

2.3 folio_payments​

CREATE TABLE folio_payments (
  id                   text PRIMARY KEY CHECK (id LIKE 'fpm_%'),
  folio_id             text NOT NULL REFERENCES folios(id) ON DELETE RESTRICT,
  tenant_id            text NOT NULL,
  method               text NOT NULL CHECK (method IN ('cash','card','paypal','mfs','bank_transfer','on_account')),
  amount_micro         bigint NOT NULL CHECK (amount_micro > 0),
  currency             char(3) NOT NULL,
  external_payment_id  text,                 -- payment-gateway-service paymentId
  cash_session_id      text REFERENCES cash_drawer_sessions(id),
  recorded_at          timestamptz NOT NULL,
  recorded_by          text NOT NULL,
  metadata             jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at           timestamptz NOT NULL DEFAULT now(),
  CHECK ( (method = 'cash' AND cash_session_id IS NOT NULL AND external_payment_id IS NULL)
       OR (method = 'on_account')
       OR (method NOT IN ('cash','on_account') AND external_payment_id IS NOT NULL AND cash_session_id IS NULL) )
);
CREATE INDEX folio_payments_folio_idx ON folio_payments (folio_id);
CREATE INDEX folio_payments_external_idx ON folio_payments (external_payment_id) WHERE external_payment_id IS NOT NULL;
CREATE INDEX folio_payments_session_idx ON folio_payments (cash_session_id) WHERE cash_session_id IS NOT NULL;
CREATE INDEX folio_payments_recorded_idx ON folio_payments (tenant_id, recorded_at);
ALTER TABLE folio_payments ENABLE ROW LEVEL SECURITY;
CREATE POLICY folio_payments_tenant_isolation ON folio_payments
  USING (tenant_id = current_setting('app.tenant_id', true));

2.4 folio_refunds​

CREATE TABLE folio_refunds (
  id                 text PRIMARY KEY CHECK (id LIKE 'frd_%'),
  folio_id           text NOT NULL REFERENCES folios(id) ON DELETE RESTRICT,
  tenant_id          text NOT NULL,
  amount_micro       bigint NOT NULL CHECK (amount_micro > 0),
  currency           char(3) NOT NULL,
  reason             text NOT NULL,
  external_refund_id text,
  cash_session_id    text REFERENCES cash_drawer_sessions(id),
  refunded_at        timestamptz NOT NULL,
  refunded_by        text NOT NULL,
  created_at         timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX folio_refunds_folio_idx ON folio_refunds (folio_id);
CREATE INDEX folio_refunds_external_idx ON folio_refunds (external_refund_id) WHERE external_refund_id IS NOT NULL;
ALTER TABLE folio_refunds ENABLE ROW LEVEL SECURITY;
CREATE POLICY folio_refunds_tenant_isolation ON folio_refunds
  USING (tenant_id = current_setting('app.tenant_id', true));

2.5 invoices, invoice_lines, credit_notes, credit_note_lines​

CREATE TABLE invoices (
  id                text PRIMARY KEY CHECK (id LIKE 'inv_doc_%'),
  tenant_id         text NOT NULL,
  folio_id          text NOT NULL REFERENCES folios(id),
  number            text NOT NULL,
  customer          jsonb NOT NULL,         -- { class, name, email, phone, vatNumber, taxRegistration, address, preferredLocale }
  currency          char(3) NOT NULL,
  subtotal_micro    bigint NOT NULL,
  tax_total_micro   bigint NOT NULL,
  grand_total_micro bigint NOT NULL,
  locale            text NOT NULL,
  template          text NOT NULL CHECK (template IN ('standard','government','corporate','agent','sharia')),
  pdf_uri           text,
  issued_at         timestamptz NOT NULL,
  voided_at         timestamptz,
  void_reason       text,
  version           int NOT NULL DEFAULT 1,
  UNIQUE (tenant_id, number)
);
CREATE INDEX invoices_folio_idx ON invoices (folio_id);
CREATE INDEX invoices_tenant_issued_idx ON invoices (tenant_id, issued_at);
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY invoices_tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id', true));

CREATE TABLE invoice_lines (
  id                 text PRIMARY KEY CHECK (id LIKE 'ln_%'),
  invoice_id         text NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
  charge_id          text REFERENCES folio_charges(id),
  description        jsonb NOT NULL,
  quantity           int NOT NULL,
  unit_price_micro   bigint NOT NULL,
  gross_micro        bigint NOT NULL,
  tax_code           text NOT NULL,
  tax_amount_micro   bigint NOT NULL,
  ordering           int NOT NULL
);
CREATE INDEX invoice_lines_invoice_idx ON invoice_lines (invoice_id, ordering);

CREATE TABLE credit_notes (
  id          text PRIMARY KEY CHECK (id LIKE 'cnt_%'),
  tenant_id   text NOT NULL,
  invoice_id  text NOT NULL REFERENCES invoices(id),
  number      text NOT NULL,
  total_micro bigint NOT NULL,
  currency    char(3) NOT NULL,
  reason      text NOT NULL,
  pdf_uri     text,
  issued_at   timestamptz NOT NULL,
  UNIQUE (tenant_id, number)
);
ALTER TABLE credit_notes ENABLE ROW LEVEL SECURITY;
CREATE POLICY credit_notes_tenant_isolation ON credit_notes
  USING (tenant_id = current_setting('app.tenant_id', true));

CREATE TABLE credit_note_lines (
  id                  text PRIMARY KEY CHECK (id LIKE 'cnl_%'),
  credit_note_id      text NOT NULL REFERENCES credit_notes(id) ON DELETE CASCADE,
  original_line_id    text NOT NULL REFERENCES invoice_lines(id),
  amount_micro        bigint NOT NULL,
  reason              text
);

2.6 settlements​

CREATE TABLE settlements (
  id                  text PRIMARY KEY CHECK (id LIKE 'set_%'),
  tenant_id           text NOT NULL,
  folio_id            text NOT NULL REFERENCES folios(id) UNIQUE,
  per_currency_totals jsonb NOT NULL,    -- [{ currency, charges_micro, payments_micro, refunds_micro }]
  residual_micro      bigint NOT NULL,
  residual_currency   char(3) NOT NULL,
  closed_at           timestamptz NOT NULL
);
ALTER TABLE settlements ENABLE ROW LEVEL SECURITY;
CREATE POLICY settlements_tenant_isolation ON settlements
  USING (tenant_id = current_setting('app.tenant_id', true));

2.7 cash_drawers & cash_drawer_sessions & daily_cash_reconciliations​

CREATE TABLE cash_drawers (
  id          text PRIMARY KEY CHECK (id LIKE 'cdr_%'),
  tenant_id   text NOT NULL,
  property_id text NOT NULL,
  label       text NOT NULL,
  currency    char(3) NOT NULL,
  active      boolean NOT NULL DEFAULT true,
  created_at  timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, property_id, label)
);

CREATE TABLE cash_drawer_sessions (
  id                       text PRIMARY KEY CHECK (id LIKE 'cds_%'),
  drawer_id                text NOT NULL REFERENCES cash_drawers(id),
  tenant_id                text NOT NULL,
  status                   text NOT NULL CHECK (status IN ('open','pending_close','closed','reconciliation_blocked')),
  opening_float_micro      bigint NOT NULL,
  expected_closing_micro   bigint,
  counted_closing_micro    bigint,
  variance_micro           bigint,
  variance_threshold_micro bigint NOT NULL,
  currency                 char(3) NOT NULL,
  opened_by                text NOT NULL,
  opened_at                timestamptz NOT NULL,
  initiated_close_by       text,
  initiated_close_at       timestamptz,
  closed_by                text,
  co_signer                text,
  closed_at                timestamptz,
  step_up_token_id         text,
  shift_label              text,
  version                  int NOT NULL DEFAULT 1
);
CREATE UNIQUE INDEX cash_session_one_open_per_drawer
  ON cash_drawer_sessions (drawer_id) WHERE status IN ('open','pending_close','reconciliation_blocked');
ALTER TABLE cash_drawer_sessions ENABLE ROW LEVEL SECURITY;
CREATE POLICY cash_session_tenant_isolation ON cash_drawer_sessions
  USING (tenant_id = current_setting('app.tenant_id', true));

CREATE TABLE daily_cash_reconciliations (
  id                 text PRIMARY KEY CHECK (id LIKE 'dcr_%'),
  tenant_id          text NOT NULL,
  property_id        text NOT NULL,
  business_date      date NOT NULL,
  folio_cash_micro   bigint NOT NULL,
  session_cash_micro bigint NOT NULL,
  variance_micro     bigint NOT NULL,
  status             text NOT NULL CHECK (status IN ('match','variance','mismatch')),
  created_at         timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, property_id, business_date)
);

2.8 _outbox (per-tenant)​

CREATE TABLE _outbox (
  id           bigserial PRIMARY KEY,
  tenant_id    text NOT NULL,
  event_type   text NOT NULL,
  subject      text NOT NULL,
  payload      jsonb NOT NULL,
  trace_id     text,
  created_at   timestamptz NOT NULL DEFAULT now(),
  published_at timestamptz
);
CREATE INDEX outbox_unpublished_idx ON _outbox (created_at) WHERE published_at IS NULL;

2.9 _inbox (per-tenant)​

CREATE TABLE _inbox (
  key         text PRIMARY KEY,             -- 'inbox:billing:<event-type>:<id>' or 'req:<route>:<idem-key>'
  tenant_id   text NOT NULL,
  result      jsonb,
  created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX inbox_tenant_created_idx ON _inbox (tenant_id, created_at);

3. Central schema billing_central (subscription billing)​

CREATE SCHEMA IF NOT EXISTS billing_central;
SET search_path = billing_central, public;

CREATE TABLE plans (
  code        text PRIMARY KEY,             -- 'STARTER_PER_ROOM','PRO_PER_ROOM','ENTERPRISE_FLAT'
  base        jsonb NOT NULL,               -- { kind, amount?, perRoomAmount? }
  meters      jsonb NOT NULL DEFAULT '[]'::jsonb,
  active      boolean NOT NULL DEFAULT true,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE subscriptions (
  id                  text PRIMARY KEY CHECK (id LIKE 'sub_%'),
  tenant_id           text NOT NULL UNIQUE,
  plan_code           text NOT NULL REFERENCES plans(code),
  state               text NOT NULL CHECK (state IN ('current','grace','past_due','suspended','cancelled')),
  currency            char(3) NOT NULL,
  cycle_anchor        smallint NOT NULL CHECK (cycle_anchor BETWEEN 1 AND 28),
  grace_period_days   int NOT NULL DEFAULT 7,
  hard_cap_micro      bigint,
  payment_method_token text,                -- opaque token to payment-gateway-service
  created_at          timestamptz NOT NULL DEFAULT now(),
  suspended_at        timestamptz,
  cancelled_at        timestamptz,
  version             int NOT NULL DEFAULT 1
);
CREATE INDEX subscriptions_state_idx ON subscriptions (state);

CREATE TABLE subscription_invoices (
  id                  text PRIMARY KEY CHECK (id LIKE 'sin_%'),
  subscription_id     text NOT NULL REFERENCES subscriptions(id),
  tenant_id           text NOT NULL,
  period              text NOT NULL,        -- 'YYYY-MM'
  currency            char(3) NOT NULL,
  subtotal_micro      bigint NOT NULL,
  tax_total_micro     bigint NOT NULL DEFAULT 0,
  grand_total_micro   bigint NOT NULL,
  status              text NOT NULL CHECK (status IN ('open','paid','failed','void')),
  pdf_uri             text,
  issued_at           timestamptz NOT NULL,
  due_at              timestamptz NOT NULL,
  paid_at             timestamptz,
  attempt_count       int NOT NULL DEFAULT 0,
  next_retry_at       timestamptz,
  dunning_history     jsonb NOT NULL DEFAULT '[]'::jsonb,
  UNIQUE (subscription_id, period)
);
CREATE INDEX subscription_invoices_status_idx ON subscription_invoices (status, due_at);
CREATE INDEX subscription_invoices_retry_idx ON subscription_invoices (next_retry_at) WHERE next_retry_at IS NOT NULL;

CREATE TABLE subscription_invoice_lines (
  id            text PRIMARY KEY CHECK (id LIKE 'sil_%'),
  invoice_id    text NOT NULL REFERENCES subscription_invoices(id) ON DELETE CASCADE,
  kind          text NOT NULL,              -- 'base_flat','base_per_room','ai_overage','storage_overage'
  units         numeric(20,4) NOT NULL DEFAULT 0,
  unit_price_micro bigint NOT NULL,
  amount_micro  bigint NOT NULL,
  ordering      int NOT NULL
);

-- Usage records partitioned by period (YYYY-MM) for affordable retention pruning.
CREATE TABLE usage_records (
  id            text NOT NULL CHECK (id LIKE 'usg_%'),
  tenant_id     text NOT NULL,
  period        text NOT NULL,              -- 'YYYY-MM'
  meter         text NOT NULL CHECK (meter IN ('rooms','ai_tokens','storage_bytes')),
  units         numeric(20,4) NOT NULL,
  measured_at   timestamptz NOT NULL,
  source_event_id text,
  PRIMARY KEY (tenant_id, period, meter, id)
) PARTITION BY LIST (period);

CREATE INDEX usage_records_lookup_idx
  ON usage_records (tenant_id, period, meter);
-- Concrete partition example:
CREATE TABLE usage_records_2026_04 PARTITION OF usage_records FOR VALUES IN ('2026-04');

-- Central outbox & inbox mirror the per-tenant ones.
CREATE TABLE _outbox (LIKE billing_central.usage_records EXCLUDING ALL); -- placeholder; actual columns mirror per-tenant outbox

4. Tenant lifecycle hooks​

EventSchema action
tenant.created.v1tenant-provisioning-job runs db/tenants/billing.tmpl.sql substituting :tenant_id; sets app.tenant_id GUC default; seeds default cash_drawers from property bootstrap event later
tenant.suspended.v1application-level read-only flag in subscriptions.state (no DDL)
tenant.deleted.v1per ADR-0002: schema is archived (renamed tenant_<uuid>_billing__archived_<yyyymmdd>) for 90 days then dropped; subscription rows marked cancelled

5. Money & FX columns rules​

  • All money is bigint _micro (1e6). Tax, gross, payment, refund, settlement totals all use the same scale.
  • Each money-bearing row carries its own currency char(3); CHECK constraints enforce alignment with the parent (e.g., folio.currency) only for payment / refund computations at the application layer; multi-currency payments to a single folio are allowed and reconciled via FXSnapshot.
  • The FX snapshot is stored once on the folio (and embedded in events). Subsequent rate changes do not retroactively alter the folio.

6. Indexing strategy​

GoalIndex
Hot read: in-house folios per propertyfolios (tenant_id, property_id, status) WHERE status='open'
Folio detail viewfolio_charges (folio_id), folio_payments (folio_id), folio_refunds (folio_id)
Daily reconciliation joinfolio_payments (tenant_id, recorded_at), cash_drawer_sessions (drawer_id, status)
Dunning sweepsubscription_invoices (status, due_at) and (next_retry_at)
Usage aggregationpartitioned usage_records (tenant_id, period, meter)
Audit queries_outbox (created_at) WHERE published_at IS NULL

7. Constraints encoding domain invariants​

InvariantDB-level enforcement
Closed folio rejects mutationsapplication layer + trigger folio_charges_no_insert_when_closed/payments/refunds (reads folios.status)
One open cash session per drawerunique partial index cash_session_one_open_per_drawer
Cash payment requires session, non-cash requires external idrow-level CHECK on folio_payments
Invoice number unique per tenantUNIQUE (tenant_id, number) on invoices and credit_notes
Settlement 1:1 with closed folioUNIQUE (folio_id)
Refund > 0, charge unit_price ≥ 0CHECK (amount_micro > 0) etc.

8. Retention & archival​

ClassHot retentionArchival
Folios + charges + payments + refunds7 years (regulatory)After 7 years → moved to billing-archive-<tenantId> GCS bucket as parquet, then dropped from Postgres
Invoices + credit notes (PDFs)7 years in file-storage-serviceCold-tier GCS after 1 year
_outbox30 days hotdropped (audit-service is the long-term tail)
_inbox7 daysrolling cleanup job
daily_cash_reconciliations7 yearsas folios
usage_records25 months hot (24 cycles + buffer)older partitions detached and parquet-archived
subscription_invoices7 yearsas folios

9. Backups & PITR​

  • Cloud SQL automatic daily backups + transaction log retention 7 days for PITR ≤ 1 minute.
  • Cross-region replica (asia-south1 primary → europe-west3 replica) for disaster recovery; failover RTO ≤ 15 min, RPO ≤ 1 min.
  • Per-tenant logical exports nightly to per-tenant bucket for offboarding readiness (DSAR).

10. Migration tooling​

  • Per-tenant DDL bundle in db/tenants/billing.tmpl.sql; the billing-tenant-migrator Cloud Run Job iterates tenants.active=true, applies forward migrations under advisory lock per tenant.
  • Central DDL in db/central/billing-central.sql; standard drizzle-kit migrate.
  • See MIGRATION_PLAN for the legacy folio importer.

11. Cross-references​