billing — Phase 8

Schema locked 2026-06-10. 8 tables, 113 cols: ar_account (12), ar_charge (19), ar_payment (17), ar_payment_application (8), ar_statement (19), vendor_payable (14), ap_payment (16), ap_payment_application (8).

Merchant-side A/R + A/P control layer — not GL/accounting. Customer receivables sourced from POS and Orders charge-account tenders; vendor payables referencing Purchasing invoices. See PROJECT_DECISIONS "Billing Module Boundary (Clarified 2026-06-10)" for scope, seams, and deferrals.

Design rules:

• Maintained balance + immutable trail: ar_account.current_balance_cents and vendor_payable.open_amount_cents are maintained caches. Charges / payments / applications are the source of truth (same pattern as pos.gift_card / pos.store_credit).
• Idempotency: ar_charge is unique on source identifiers — event retries do not produce duplicate receivables. vendor_payable is unique on vendor_invoice_id — one Purchasing invoice produces exactly one payable.
• Event-driven seam: POS/Orders emit AccountChargeCreated; Billing creates ar_charge asynchronously. Checkout does not block on Billing. Idempotency makes retries safe.
• Billing write-backs to Purchasing: when a vendor_payable is paid, BillingService writes billing_ap_ref, payment_status_ref, paid_at on purchasing.vendor_invoice. PurchasingService writes no value to those columns.
• Credit terms / credit limit: referenced from crm.customer — NOT duplicated on ar_account. Vendor payment terms: referenced from purchasing.vendor — NOT duplicated on vendor_payable.

Cross-Phase FK seams:

Column	References	Status
*.tenant_id	platform.tenant	Locked — enforced
ar_account.customer_id, ar_charge.customer_id, ar_payment.customer_id, ar_statement.customer_id	crm.customer	Locked — enforced
vendor_payable.vendor_id, ap_payment.vendor_id	purchasing.vendor	Locked — enforced
vendor_payable.vendor_invoice_id	purchasing.vendor_invoice	Locked — enforced
ar_charge.source_ref	pos.sale.id or orders.order_header.id	Locked — enforced; CLOSES pos.sale_payment.charge_account_ref and orders.order_payment.charge_account_ref seams
ar_charge.source_payment_ref	pos.sale_payment.id or orders.order_payment.id	Locked — enforced
ar_payment.stripe_payment_intent_id, ap_payment.stripe_payment_intent_id	Payments module	FORWARD-REF — plain text seam; FK added when Payments module locks
*.created_by_user_id, *.received_by_user_id, *.paid_by_user_id, *.applied_by_user_id	identity.identity_user	Locked — enforced

Deferred items (explicit triggers):

• ar_adjustment table (write-offs / disputes / manual corrections): v1 uses a manual ar_charge with source_type = 'adjustment' and a negative amount. Add ar_adjustment when write-offs are a product requirement.
• Formal customer_invoice, customer_credit_memo, AP payment batches/runs, dunning/collections, payment plans, GL posting, multi-currency: deferred per Billing Module Boundary. Trigger: concrete product requirement names one.
• ar_charge (charged_at) index: deferred — add when AR aging date-range report query is written.
• ar_statement (period_start, period_end) index: deferred — add when statement generation query is written.
• ap_payment partial status index for ('applied','partially_applied'): deferred — add when AP payment reconciliation query is written.

---

billing.ar_account — 12 cols

One row per customer with an active charge account. Holds current balance and account state.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
customer_id	UUID	NOT NULL	—	FK → crm.customer
current_balance_cents	bigint	NOT NULL	0	Maintained cache; positive = customer owes tenant. Reconciliation formula: SUM(ar_charge.charge_amount_cents WHERE status != 'void') − SUM(ar_payment.amount_cents WHERE status != 'void') for this account. Source of truth = charge / payment / application rows.
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
status	text	NOT NULL	'active'	CHECK IN ('active','on_hold','closed')
last_statement_at	timestamptz	nullable	—	When last statement was generated
last_activity_at	timestamptz	nullable	—	Updated on any charge or payment
note	text	nullable	—	Internal account note

Indexes:

• PK on id
• on (tenant_id)
• UNIQUE on (tenant_id, customer_id) WHERE deleted_at IS NULL — one A/R account per customer

---

billing.ar_charge — 19 cols

A Billing-owned receivable created from a POS charge-account tender, an Orders charge-account payment, or a manual adjustment. Idempotent on source identifiers — event retries produce no duplicates.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
ar_account_id	UUID	NOT NULL	—	FK → billing.ar_account
customer_id	UUID	NOT NULL	—	FK → crm.customer — denormalised for query
source_module	text	NOT NULL	—	CHECK IN ('pos','orders','manual'); valid (module, type) pairs enforced by table-level CHECK
source_type	text	NOT NULL	—	CHECK IN ('sale','order','adjustment'); valid (module, type) pairs enforced by table-level CHECK
source_ref	UUID	nullable	—	FK → pos.sale.id or orders.order_header.id — source document; NOT NULL when source_module IN ('pos','orders'), NULL when 'manual' (table-level CHECK)
source_payment_ref	UUID	nullable	—	FK → pos.sale_payment.id or orders.order_payment.id — specific charge-account tender; NOT NULL when source_module IN ('pos','orders'), NULL when 'manual' (table-level CHECK)
charge_amount_cents	bigint	NOT NULL	—	Amount of the receivable
applied_amount_cents	bigint	NOT NULL	0	Cache: SUM of ar_payment_application.applied_amount_cents for this charge; CHECK (applied_amount_cents <= charge_amount_cents)
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
status	text	NOT NULL	'open'	CHECK IN ('open','partially_paid','paid','void')
charged_at	timestamptz	NOT NULL	—	When the charge was created (matches source event timestamp)
due_date	date	nullable	—
note	text	nullable	—
created_by_user_id	UUID	nullable	—	FK → identity.identity_user

Table-level CHECK constraints:

• CHECK ((source_module = 'pos' AND source_type = 'sale') OR (source_module = 'orders' AND source_type = 'order') OR (source_module = 'manual' AND source_type = 'adjustment')) — enforces valid (module, type) pairs; rejects cross-combinations like ('pos', 'order')
• CHECK ((source_module = 'manual' AND source_ref IS NULL AND source_payment_ref IS NULL) OR (source_module IN ('pos', 'orders') AND source_ref IS NOT NULL AND source_payment_ref IS NOT NULL)) — protects idempotency key: a POS/Orders charge with a null ref would bypass dedup; a manual charge needs no source refs

Indexes:

• PK on id
• on (tenant_id)
• on (ar_account_id)
• on (customer_id)
• on (status) WHERE status IN ('open','partially_paid') — open receivables
• on (due_date)
• UNIQUE on (tenant_id, source_module, source_type, source_ref, source_payment_ref) WHERE deleted_at IS NULL — idempotency dedup; prevents duplicate receivables on event retry

---

billing.ar_payment — 17 cols

A customer payment received against the charge account. May be partially or fully applied across one or more charges. Unapplied amount (amount_cents − applied_amount_cents) sits as an account credit.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
ar_account_id	UUID	NOT NULL	—	FK → billing.ar_account
customer_id	UUID	NOT NULL	—	FK → crm.customer — denormalised for query
payment_method	text	NOT NULL	—	CHECK IN ('cash','check','card','ach','store_credit','other')
amount_cents	bigint	NOT NULL	—	Total payment received
applied_amount_cents	bigint	NOT NULL	0	Cache: SUM of ar_payment_application.applied_amount_cents for this payment; CHECK (applied_amount_cents <= amount_cents)
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
received_at	timestamptz	NOT NULL	—	When payment was received
reference_number	text	nullable	—	Check number, ACH trace, etc.
stripe_payment_intent_id	text	nullable	—	FORWARD-REF / text seam — FK → payments schema added when Payments module locks
status	text	NOT NULL	'received'	CHECK IN ('received','applied','partially_applied','refunded','void')
note	text	nullable	—
received_by_user_id	UUID	nullable	—	FK → identity.identity_user

Indexes:

• PK on id
• on (tenant_id)
• on (ar_account_id)
• on (customer_id)
• on (status)
• on (received_at)

---

billing.ar_payment_application — 8 cols

N:M junction applying a portion of an ar_payment to a specific ar_charge. Append-only — a wrong application is reversed via a new offsetting row (negative applied_amount_cents), never deleted. No updated_at/deleted_at per the financial-trail standard (cf. pos.gift_card_transaction).

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
ar_payment_id	UUID	NOT NULL	—	FK → billing.ar_payment
ar_charge_id	UUID	NOT NULL	—	FK → billing.ar_charge
applied_amount_cents	bigint	NOT NULL	—	CHECK (applied_amount_cents != 0); negative for reversal rows
applied_at	timestamptz	NOT NULL	—	When application was recorded
applied_by_user_id	UUID	nullable	—	FK → identity.identity_user

Indexes:

• PK on id
• on (tenant_id)
• on (ar_payment_id)
• on (ar_charge_id)

---

billing.ar_statement — 19 cols

Lightweight period snapshot of an account's activity. No ar_statement_line child table in v1 — the charge IDs in included_charge_ids JSONB serve as the lightweight snapshot reference.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
ar_account_id	UUID	NOT NULL	—	FK → billing.ar_account
customer_id	UUID	NOT NULL	—	FK → crm.customer — denormalised for query
statement_number	text	NOT NULL	—	UNIQUE (tenant_id, statement_number) WHERE deleted_at IS NULL
period_start	date	NOT NULL	—
period_end	date	NOT NULL	—
opening_balance_cents	bigint	NOT NULL	—	Balance at start of period
charges_total_cents	bigint	NOT NULL	—	Sum of new charges in period
payments_total_cents	bigint	NOT NULL	—	Sum of payments received in period
closing_balance_cents	bigint	NOT NULL	—	opening_balance_cents + charges_total_cents − payments_total_cents
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
included_charge_ids	JSONB	nullable	—	Lightweight snapshot: ["uuid", ...] — charge IDs included in this statement period. Avoids a ar_statement_line child table in v1.
generated_at	timestamptz	NOT NULL	—	When statement was generated
sent_at	timestamptz	nullable	—	When delivered to customer
delivery_method	text	nullable	—	CHECK (delivery_method IS NULL OR delivery_method IN ('email','print','none'))

Indexes:

• PK on id
• on (tenant_id)
• on (ar_account_id)
• on (customer_id)
• UNIQUE on (tenant_id, statement_number) WHERE deleted_at IS NULL

---

billing.vendor_payable — 14 cols

A Billing-owned payable created from a purchasing.vendor_invoice. One invoice = one payable (idempotent). When paid, BillingService writes back billing_ap_ref, payment_status_ref, paid_at on the source invoice.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
vendor_id	UUID	NOT NULL	—	FK → purchasing.vendor
vendor_invoice_id	UUID	NOT NULL	—	FK → purchasing.vendor_invoice — source document; one invoice = one payable
payable_amount_cents	bigint	NOT NULL	—	From the vendor invoice total
paid_amount_cents	bigint	NOT NULL	0	Cache: SUM of ap_payment_application.applied_amount_cents for this payable
open_amount_cents	bigint	NOT NULL	—	Maintained: payable_amount_cents − paid_amount_cents; CHECK (open_amount_cents >= 0)
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
status	text	NOT NULL	'open'	CHECK IN ('open','partially_paid','paid','void')
due_date	date	nullable	—	Derived from vendor invoice due_date; carried here for AP aging queries without joining back to Purchasing
note	text	nullable	—

Indexes:

• PK on id
• on (tenant_id)
• on (vendor_id)
• on (status) WHERE status IN ('open','partially_paid') — open payables / AP aging
• on (due_date)
• UNIQUE on (tenant_id, vendor_invoice_id) WHERE deleted_at IS NULL — one Purchasing invoice = one payable (idempotency)

---

billing.ap_payment — 16 cols

A vendor payment issued by the tenant. May be applied across one or more payables.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()
deleted_at	timestamptz	nullable	—	Soft delete
vendor_id	UUID	NOT NULL	—	FK → purchasing.vendor
payment_method	text	NOT NULL	—	CHECK IN ('check','ach','card','cash','wire','other')
amount_cents	bigint	NOT NULL	—	Total payment issued
applied_amount_cents	bigint	NOT NULL	0	Cache: SUM of ap_payment_application.applied_amount_cents for this payment; CHECK (applied_amount_cents <= amount_cents)
currency_code	char(3)	NOT NULL	'USD'	ISO 4217
paid_at	timestamptz	NOT NULL	—	When payment was issued
reference_number	text	nullable	—	Check number, ACH trace, wire ref, etc.
stripe_payment_intent_id	text	nullable	—	FORWARD-REF / text seam — FK → payments schema added when Payments module locks
status	text	NOT NULL	'paid'	CHECK IN ('paid','applied','partially_applied','void')
note	text	nullable	—
paid_by_user_id	UUID	nullable	—	FK → identity.identity_user

Indexes:

• PK on id
• on (tenant_id)
• on (vendor_id)
• on (status)
• on (paid_at)

---

billing.ap_payment_application — 8 cols

N:M junction applying a portion of an ap_payment to a specific vendor_payable. Append-only — a wrong application is reversed via a new offsetting row (negative applied_amount_cents), never deleted. No updated_at/deleted_at per the financial-trail standard (cf. pos.gift_card_transaction). When application fully pays a payable, BillingService triggers the write-back to purchasing.vendor_invoice.

RLS: tenant-isolated on tenant_id.

Column	Type	Nullable	Default	Constraints / Notes
id	UUID	NOT NULL	uuid_generate_v4()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
ap_payment_id	UUID	NOT NULL	—	FK → billing.ap_payment
vendor_payable_id	UUID	NOT NULL	—	FK → billing.vendor_payable
applied_amount_cents	bigint	NOT NULL	—	CHECK (applied_amount_cents != 0); negative for reversal rows
applied_at	timestamptz	NOT NULL	—	When application was recorded
applied_by_user_id	UUID	nullable	—	FK → identity.identity_user

Indexes:

• PK on id
• on (tenant_id)
• on (ap_payment_id)
• on (vendor_payable_id)

---

Column counts: ar_account(12) + ar_charge(19) + ar_payment(17) + ar_payment_application(8) + ar_statement(19) + vendor_payable(14) + ap_payment(16) + ap_payment_application(8) = 113

---