Billing Domain Design (Catalog + Invoices + Single Ledger + Lots + Xero-Friendly Exports)

info

Billing Spec Notes

Fundamental concepts to understand first

1. Entitlement (units) vs Money (accounting)

   • Entitlements are what the customer can still use:

     • placement_credit (Placement/Visibility credits)
     • gig_credit_cents (stored value credits measured in cents)

   • Money is what finance cares about:

     • Deferred revenue (you owe service delivery)
     • Principal liability (refundable stored value)
     • Recognized revenue (earned)

2. Ledger vs Projection

   • Ledger: an append-only journal of all balance-affecting actions (grant/reserve/release/consume/adjust).
   • Projection: cached “current state” tables for fast reads (balances, active holds). Projections are rebuildable from ledger.

3. Reservation is not consumption

   • Reserve moves units from available → reserved.
   • Consume reduces units (usually from reserved, sometimes directly from available).

4. Why Gig is special

   • Gig credits require FIFO lots and per-lot platform fee rates → you must track consumption against purchase batches.
   • Placement credits do not tag units to purchase price → no lots required.

5. Xero-friendly exports

   • Your finance process is lump-sum journaling (daily/monthly).
   • Your internal system still needs customer-level statements and auditability.
   • Design should support both without refactors.

---

Overview

We will implement ::Billing as a bounded context that manages:

• customer entitlements (units)
• deferred revenue / liability movements (money)
• reservations and consumption
• statements of account (SOA)
• finance exports compatible with your current journaling workflows and future Xero integration

Products / instruments supported

• Placement Credits (marketed as “Visibility Credits”, pooled deferred revenue):

  • Ads (display placements)
  • Job Boost (featured placement for a duration)
  • Careers Job Posting (per post / per application later)

• Gig Credits (stored value + FIFO lots for platform fee):

  • credits represent wage value
  • platform fee deferred and recognized by FIFO lots

Future:

• Subscriptions (Workforce) will be introduced as another entitlement type (seat-days, seat-months) and/or as contract schedules—this design accommodates it.

---

Glossary

• Entitlement Type: defines unit type and accounting policy (e.g. placement_credit, gig_credit_cents)

• Ledger Entry: one atomic balance-changing record (append-only)

• Balance: current available/reserved units and deferred money for a given entitlement type

• Hold: an “active reservation” projection keyed by a reference (e.g. Gig::Shift #123)

• Lot: gig purchase batch with its own platform fee rate; used for FIFO allocation

• Allocation: mapping from a ledger entry to one or more lots (gig only)

• Reference: the external domain object that caused the billing event (Ads campaign placement, Careers job, Gig shift)

• Product: global definition of what is being sold (stable); used to derive entitlements granted

• Offer: market-specific price list row for a product (currency, taxes, seller legal entity, gig fee terms)

• Legal Entity: your seller-of-record for a jurisdiction (drives invoice numbering + tax treatment)

• Bill-To Profile: customer billing recipient details (address/contact), owned by the customer company

• Invoice: customer-facing commercial document generated from an offer snapshot

• Payment: offline bank transfer record + verification status

• Posting: the idempotent internal action that grants entitlements into the ledger once an invoice is fully paid

---

---

0) Billing System Layers (so engineers and finance can both navigate)

Billing gets confusing because it mixes commercial flows (invoices, bank transfers) and entitlement accounting (credits, reservations, revenue recognition). To keep the system understandable, we design it in layers:

Layer A — Catalog & Pricing (what we sell)

Tables: billing_products, billing_product_prices (plus billing_legal_entities)

• A Product defines what is being sold (e.g. “Gig Credits”, “Placement Credits” (Visibility Credits in UI)) and what it grants.
• An Offer defines how we sell that product in a specific market:
  • currency (SGD, IDR, KRW…)
  • price, tax rules
  • seller-of-record (Legal Entity)
  • gig platform fee rate terms (if applicable)

Analogy: Product is the menu item (“Latte”), Offer is the outlet-specific price (“$6.50 at Orchard, $7.20 at Marina Bay”).

Layer B — Commercial Documents (how customers buy)

Tables: billing_invoices, billing_invoice_items, billing_payments, billing_invoice_postings

• Invoices are customer-facing documents.
• Payments capture offline bank transfers (with proof).
• Posting is the internal step that grants entitlements only after payment is verified.

Analogy: invoice = “bill”, payment = “bank transfer receipt”, posting = “kitchen confirms payment then starts preparing food”.

Layer C — Entitlements Engine (what customers can spend)

Tables: billing_entitlement_types, billing_ledger_entries, billing_entitlement_balances, billing_entitlement_holds, and gig-only billing_entitlement_lots, billing_entitlement_lot_allocations

• Single ledger is the source of truth for all movements.
• Balances / holds are projections for fast reads.
• Lots exist only for instruments that require FIFO & per-purchase terms (Gig credits).

Layer D — Reporting & Finance Exports (how we reconcile)

Outputs: SOA per company, finance export aggregates, Xero-friendly journals/invoice exports

• Statements of Account are derived from ledger entries (grouped by reference).
• Finance exports are produced from the same ledger, but summarized to match your operational workflow (daily lumps / month-end).

Why layering matters: As you add Workforce subscriptions later, you’ll add a new instrument/policy in Layer C and new products/offers in Layer A, but the invoice/payment posting flow remains the same.

1) High-Level Ramp-Up (Grok)

1.1 Why a single ledger table

What we need long-term

• Customer statements for Gig credits (like your legacy system)
• Similar statements for Placement Credits (Visibility Credits in UI)
• Easy to build: filtering, pagination, date-range statements
• Clean audit trail for finance and ops
• Consistent idempotency and concurrency behavior across all billing actions

Why multiple action tables is a tax

If you split into topups, reservations, usages, you’ll constantly:

• union or stitch streams for statements
• duplicate filters and pagination logic
• add more tables as you add subscriptions/refunds/rebates

Single ledger = one chronological stream per entitlement type. Statements become one query: filter by account + entitlement type + date range; order by time.

---

1.2 The canonical “financial primitives” we model

Inside Billing, we name things by stable finance primitives (not Ads/Gig/etc.):

• Grant: increase available entitlements (usually after payment verification)
• Reserve (Hold): move available to reserved (to prevent overspend)
• Release: move reserved back to available (campaign canceled, shift canceled)
• Consume: reduce reserved or available (service delivered)
• Adjust: manual corrections

These primitives apply to all instruments; only the policies differ per entitlement type.

---

1.3 Policies per entitlement type

Placement Credits (placement_credit)

• Units are pooled; no per-purchase unit price tracking.

• Money tracking: only

  • deferred_revenue_cents (remaining)

  • revenue recognized proportionally at consumption time:

    • recognized = units_consumed * (deferred_revenue_before / units_before)

Gig Credits (gig_credit_cents)

• Units represent stored value (in cents).

• Requires FIFO lots because platform fee rate can differ per purchase.

• Money tracking:

  • principal liability (you owe stored value back / service)
  • platform fee deferred and recognized based on FIFO lot allocations

• Reservation may span multiple lots.

---

1.4 Projections (fast reads) vs ledger (truth)

We will store two main projections:

• billing_entitlement_balances: fast “what’s available/reserved/deferred?”
• billing_entitlement_holds: fast “what active holds exist for a given reference?”

Both are updated in the same DB transaction as ledger entry insertion. Both are rebuildable from ledger (and lots for gig).

---

1.5 Don’t paint into a corner for Xero integration

We design for two export modes:

1. Journal mode (matches your current process)

   • Aggregate daily totals across the platform:

     • movement in deferred revenue
     • recognized revenue
     • gig credits consumed (liability reduction)
     • insurance deductions/sponsored amounts

   • Export as journal lines (CSV now; API later)

2. Invoice mode (future)

   • If you later want per-customer sales invoices in Xero:

     • keep billing_invoices and billing_payments as optional modules
     • the ledger remains the “delivery and recognition” system of record

Key design point: The ledger stores recognition snapshots at the moment of consumption. That ensures you can export accounting entries later without recomputing history and risking drift.

---

1.6 Catalog + Invoicing (Products, Offers, Invoices, Payments)

Why this is a separate concern from the ledger

Your entitlements ledger answers: “What does the customer still have, and what happened over time?”

Your invoicing answers: “What did we sell, under which legal entity, in what currency, and did we get paid?”

They must be separate because:

• Pricing changes over time (offers change), but old invoices must remain accurate and auditable.
• Multi-country expansion (SG → KR) introduces:
  • different currencies
  • different seller legal entities
  • different tax rules
  • different invoice numbering sequences
• Offline bank transfers require a “human verification” step before entitlements are granted.

Product vs Offer (so multi-country doesn’t explode your schema)

• Billing::Product = global definition (e.g. “Placement Credits” (Visibility Credits in UI), “Gig Credits”)
• Billing::ProductPrice = market-specific sellable variant (e.g. “Placement Credits — Singapore price list”, “Gig Credits — Korea enterprise rate”)

An invoice item should store a snapshot of the offer terms at the time of invoice issue (price, currency, tax, fee rate terms) so later offer edits don’t mutate history.

Invoice lifecycle (minimal but robust)

Keep the invoice state machine small but correct for partial payments:

• draft → prepared internally (not sent)
• issued → customer can pay (bank transfer initiated)
• partially_paid → at least one verified payment, but sum(verified) < invoice total
• paid → sum(verified payments) >= invoice total
• void → cancelled/invalidated (before paid)
• credited → (future) credit note / refund workflow

Posting (granting entitlements) is separate from “paid” to guarantee idempotency:

• When invoice becomes paid, we create billing_invoice_postings (1:1, unique).
• Posting writes to the ledger and creates lots (gig) in the same DB transaction.
• Unique constraint on postings prevents double-grants if finance clicks “verify” twice. g ensures invoice cannot be posted twice.

Payment lifecycle (offline bank transfer)

Keep payment state machine minimal too:

• submitted → someone recorded a bank transfer reference + proof
• verified → business/finance confirms money received
• rejected → proof invalid / not received

Allow multiple payments per invoice (partial payments) even if you don’t use it on day one — it prevents corner cases later (e.g., finance accidentally records 2 transfers).

Why we call this “Placement Credits” (Visibility Credits in UI) today (and “Actions” later)

Right now you’re selling “buy visibility”:

• sponsored placements (Ads inventory)
• job posting visibility (Careers)
• boosted listings (Job Boost)

So the UI / sales packaging can safely be called “Visibility Credits”.

Internally, we name the instrument by what it actually buys across domains:

• Entitlement Type code: placement_credit
• Meaning: a count of “visibility units” you can spend on placements/posts/boost days

Later, when you introduce a smarter ad system (targeting, relevance, performance), you may add a second instrument:

• action_credit (or performance_action_credit)

Both instruments can share the same accounting model:

• pooled units (no per-lot pricing)
• pooled “deferred revenue” money balance
• proportional revenue recognition at consumption time
• same commercial flow (offer snapshot → invoice → payments → posting → ledger)

The difference would be only in how other domains consume it (days/placements vs measurable actions).

2) High-Level Schema Design

2.1 Entity relationship overview (Billing)

Org::Company
   |
   v
billing_accounts
   |
   +-- billing_entitlement_balances (1 row per entitlement type)
   |
   +-- billing_ledger_entries (append-only; statements come from here)
   |
   +-- billing_entitlement_holds (active holds; projection)
   |
   +-- billing_entitlement_lots (gig only; FIFO batches)
          |
          +-- billing_entitlement_lot_allocations (gig only; map ledger entry -> lots)

2.2 Naming strategy (finance primitives first)

We will not name tables by Ads/Gig. We name them by primitives and type policies.

• billing_entitlement_types
• billing_ledger_entries
• billing_entitlement_balances
• billing_entitlement_holds
• billing_entitlement_lots (gig-only, but still finance language)
• billing_entitlement_lot_allocations

This keeps Billing stable even if your Ads/Boost/Careers naming evolves.

---

2.3 Commercial layer schema (Catalog + Invoices)

This sits “above” the entitlements engine and stays stable even if you add new instruments later.

geo_countries
   ▲
   │ (seller jurisdiction)
billing_legal_entities 1 ──── N billing_product_prices N ──── 1 billing_products
        │                         │
        │ (seller on invoice)     │ (chosen by buyer)
        ▼                         ▼
billing_invoices 1 ──── N billing_invoice_items
        │
        │ 1 ──── N
        ▼
billing_payments

billing_invoices 1 ──── 1 billing_invoice_postings   (idempotent “grant” step)
                                  │
                                  ▼
                           billing_ledger_entries (+ gig lots)

Key rule: Entitlements are granted only via posting (not by invoice creation).

3) Model and Table Deep Dive

3.0 Commercial layer tables (Catalog + Invoices)

These tables exist to support:

• automated invoice generation (customer-facing)
• manual/offline bank transfer verification (ops/finance-facing)
• stable multi-country price lists (offers)
• a clean, idempotent trigger that grants entitlements into the ledger only after payment is verified and the invoice is fully paid

billing_legal_entities

Purpose: Represents us as the seller-of-record for a jurisdiction (e.g., Singapore entity vs Indonesia entity vs future Korea entity).

Minimal attributes

• id
• code (e.g. staffany_sg, staffany_id)
• display_name (printed on invoices)
• registered_address (or structured fields)
• tax_regime (enum/string: sg_gst, id_vat, kr_vat…)
• default_currency (e.g. SGD)
• country_id (FK → geo_countries)
• xero_organisation_id (optional, if you eventually manage multiple Xero orgs)
• invoice_number_prefix (e.g. SG-INV-)
• invoice_number_sequence (integer, locked/atomic increment)
• timestamps

Invariants

• Invoice numbering must be unique per legal entity.
• A legal entity’s country/tax regime is stable over time; don’t mutate historical invoices.

Gotcha

• Multi-country usually implies multiple legal entities (for tax + compliance). Don’t encode “country” only on the invoice and ignore legal entity — you’ll regret this at audit time.

---

billing_bill_to_profiles

Purpose: Represents the customer billing recipient details for an Org::Company (address, attention line, email). This is not the same as billing_legal_entities.

• billing_legal_entities = who is selling
• billing_bill_to_profiles = who we are billing

Minimal attributes

• id
• billing_account_id (FK → billing_accounts)
• label (e.g. HQ, Finance Dept, Outlet Group A)
• company_name
• attention (e.g. “Attn: Finance Team”)
• billing_email
• billing_address (or structured fields)
• country_id (FK → geo_countries, optional if address implies it)
• timestamps

Invariants

• A company can have multiple bill-to profiles, but an invoice must snapshot bill-to fields at issuance time.

Gotcha

• Never “join back” to bill-to profile to render historical invoices. Copy the bill-to fields into the invoice record.

---

billing_products

Purpose: Global “thing we sell” definition (stable). Think: what entitlement instrument is granted, not the price.

Examples:

• placement_credits (used for ads placements, job posting, job boost)
• gig_credits (stored value credits for wages)

Minimal attributes

• id
• code (unique, stable; e.g. placement_credits, gig_credits)
• name (human friendly)
• description
• entitlement_type_id (FK → billing_entitlement_types)
• unit_name (e.g. credit, coin, cent)
• grants_units_per_quantity (integer; see below)
• is_lot_based (boolean, true for gig)
• timestamps

How grants_units_per_quantity works

• For package-based products: quantity=1 grants 1,000 credits.
• For per-unit top-ups: quantity=500 grants 500 credits.

This keeps invoices flexible without creating infinite product rows.

---

billing_product_prices

Purpose: Market-specific sellable variant (your “price list row”). This is where we encode currency, price, taxes, and gig-specific platform fee terms.

Minimal attributes

• id
• billing_product_id (FK → billing_products)
• billing_legal_entity_id (FK → billing_legal_entities) (seller-of-record)
• country_id (FK → geo_countries) (market selector)
  • can be same as legal entity country, but keep it explicit
• currency (e.g. SGD, IDR, KRW)
• pricing_model (enum: package, per_unit)
• unit_price_cents (integer; interpreted as cents for the currency minor unit)
• tax_code (string; maps to your finance system)
• tax_rate (decimal, optional; country rules vary)
• active_from, active_until (optional)
• metadata (jsonb; gig: platform_fee_rate_bps, promos, notes)
• timestamps

Gig-specific (split pricing + split tax)

For Gig offers in Singapore you need to support:

• principal pricing (the stored value portion)
• platform fee rate (bps)
• tax policy: GST applies only to the platform fee

Pragmatically:

• store platform_fee_rate_bps in metadata
• store tax codes/rates either:
  • as tax_code for taxable lines (platform fee), and treat principal as tax-exempt (0%), or
  • in metadata as principal_tax_code + fee_tax_code if your finance system requires explicit codes.

Either way, the invoice items must snapshot the final tax rate/code per line.

Invariants

• Offers are append-only over time: if pricing changes, create a new offer row instead of mutating historical terms.

Gotcha

• Even if your Org::Company has country_id, you still want the offer to be explicit — it’s the contract of sale.

---

billing_invoices

Purpose: Customer-facing invoice (commercial document). Generated when company requests to top-up entitlements.

Minimal attributes

• id
• billing_account_id (FK)
• billing_legal_entity_id (FK) seller
• billing_bill_to_profile_id (FK, optional) source profile
• invoice_no (unique per legal entity)
• status (draft, issued, partially_paid, paid, void, credited)
• currency
• Snapshot bill-to fields
  • bill_to_company_name
  • bill_to_attention
  • bill_to_email
  • bill_to_address
• issued_at, due_at, settled_at
• subtotal_cents, tax_cents, total_cents
• timestamps

Invariants

• Once issued, invoice amounts and items are immutable.
• If you need corrections after issuance: use void + reissue (if unpaid) or credit note (future enhancement).

---

billing_invoice_items

Purpose: Line item snapshots. This is the bridge between commercial sale and entitlement grant.

Minimal attributes

• id
• billing_invoice_id (FK)
• billing_product_id (FK, optional but useful)
• billing_product_price_id (FK, optional but useful)
• description (snapshot)
• quantity (integer)
• unit_price_cents (integer snapshot)
• amount_cents (integer)
• tax_cents (integer)
• entitlement_type_id (FK → billing_entitlement_types)
• units_to_grant (integer) (computed at invoice creation time)
• metadata (jsonb snapshot)
  • gig: platform_fee_rate_bps, principal_amount_cents, platform_fee_amount_cents
  • visibility: optional promo flags
• timestamps

Tax rules (Singapore MVP)

You confirmed the tax base differs by instrument:

A) Placement Credits (placement_credit)

• GST applies to the full value of the placement credits purchase.
• If buying 100 placement credits for $200:
  • subtotal = 200
  • tax = 200 × 0.09 = 18
  • total = 218

So the single placement-credit invoice item will usually carry:

• tax_rate = 9%
• tax_cents = amount_cents × 0.09

B) Gig Credits (gig_credit_cents)

• GST applies only to the platform fee line item (see Gig section above).

Critical design choice (Gig): Two invoice items are mandatory

For Gig credits in Singapore, you explicitly confirmed:

• The customer invoice must show two line items:
  1. Gig Credits (stored value / principal) — grants gig credit units
  2. Gig Platform Fee — grants no units (money-only line)
• GST applies only to the platform fee (not to principal).

That means we do not support the “single invoice item with split metadata” approach for Gig. It looks simpler in UI, but it makes tax handling and accounting exports fragile.

Invoice Item Tax Policy (SG, MVP)

• Gig Credits (principal): tax rate = 0, tax_cents = 0
• Gig Platform Fee: tax rate = 9% (or from LegalEntity tax regime), tax_cents = fee_amount × 0.09

The posting step uses these item snapshots to create the correct ledger entries and gig lots safely.

---

billing_payments

Purpose: Records offline bank transfers and proof.

Minimal attributes

• id
• billing_invoice_id (FK)
• method (bank_transfer now, card later)
• status (submitted, verified, rejected)
• amount_cents
• received_at
• bank_reference (string)
• proof_file_id (FK → your file storage table, or active_storage_attachments)
• verified_by_admin_id (FK → admins/users table)
• verified_at
• timestamps

Invariants

• Invoice becomes paid when sum(verified payments) >= total (or when a single payment matches total if you enforce that policy).

Gotcha

• You want to support multiple payments even if you don’t expose it in UI at first (finance reality beats product purity).

• Policy: Allow partial payments, but grant entitlements only when the invoice is fully paid (status = paid).

---

billing_invoice_postings

Purpose: Idempotent internal record that “this invoice was posted into entitlements”.

Minimal attributes

• id
• billing_invoice_id (FK, unique)
• posted_at
• posted_by_admin_id
• idempotency_key (optional)
• timestamps

Invariants

• Unique constraint on billing_invoice_id guarantees no double-granting if the “verify payment” action is clicked twice.
• Only allowed when invoice status = paid (sum of verified payments >= invoice total).

---

How these connect to the entitlement engine

When an invoice is posted:

• For placement credits:

  • one ledger entry granting units (available_delta)
  • one ledger entry adding deferred revenue (deferred_revenue_delta_cents) (can be merged into a single ledger entry with both deltas)

• For gig credits:

  • create a billing_entitlement_lot per purchase (FIFO)
  • grant units via ledger
  • record platform fee deferred on the lot + ledger snapshot for audit

3.1 billing_accounts

Purpose

One Billing account per Org::Company. This is the parent entity for all entitlements.

Minimal attributes

• id
• org_company_id (unique)
• currency (e.g., SGD)
• status (active, suspended)
• timestamps

Invariants

• exactly one account per company
• currency is stable per account (future: if you need multi-currency per company, create separate accounts per legal entity/currency)

---

3.2 billing_entitlement_types

Purpose

Defines each financial instrument:

• its unit type
• whether it uses lots (FIFO)
• which accounting policy applies

Minimal attributes

• id

• code (unique string):

  • placement_credit
  • gig_credit_cents
  • future: workforce_seat_day

• unit_name (e.g. credit, cent)

• allocation_policy enum:

  • pooled
  • fifo_lots

• recognition_policy enum:

  • proportional_average (placement)
  • lot_based (gig platform fee)

• is_reservable boolean

• timestamps

Gotcha

Do not bake pricing into this table. Pricing belongs to product/catalog (Promotions, Careers), not Billing.

---

3.3 billing_entitlement_balances (projection)

Purpose

Fast reads for UI/ops:

• “How many units available/reserved?”
• “How much deferred revenue remains?”
• “How much platform fee deferred remains?” (gig cache)

One row per (billing_account_id, entitlement_type_id).

Minimal attributes

• billing_account_id
• billing_entitlement_type_id
• units_available (BIGINT)
• units_reserved (BIGINT)

Money columns (nullable depending on entitlement type):

• deferred_revenue_cents (BIGINT) — used by placement_credit
• platform_fee_deferred_cents (BIGINT) — used by gig as cache
• timestamps

Invariants

• units never negative
• units_available + units_reserved must match underlying lots totals for gig (projection consistency)
• deferred revenue never negative

Indexes

• unique (billing_account_id, billing_entitlement_type_id)

---

3.4 billing_ledger_entries (single ledger)

Purpose

The statement-of-account source of truth. Every balance change inserts one or more ledger entries.

Key columns (minimal but complete)

Identity:

• id

• billing_account_id

• billing_entitlement_type_id

• entry_type enum:

  • grant, reserve, release, consume, adjust

• occurred_at (timestamp)

• idempotency_key (unique)

Balance deltas (BIGINT):

• available_delta
• reserved_delta

Money fields (BIGINT cents; nullable depending on type + entry):

• deferred_revenue_delta_cents (placement grants and consumes)
• recognized_revenue_cents (placement consumes)
• platform_fee_deferred_delta_cents (gig grants)
• platform_fee_recognized_cents (gig consumes)

References (for audit + grouping):

• reference_type (string)
• reference_id (bigint)

Optional snapshot fields (strongly recommended for placement consumes):

• pool_units_before (BIGINT)
• pool_deferred_revenue_before_cents (BIGINT)

Metadata:

• metadata jsonb (insurance amounts, notes, admin reason, etc.)

Timestamps:

• created_at

Why two deltas?

Because reservations must be auditable and SOA-friendly:

• Reserve: available -X, reserved +X
• Release: available +X, reserved -X
• Consume reserved: reserved -X
• Consume direct: available -X

Without two deltas, your “available vs reserved” state becomes ambiguous or requires hacks.

Critical invariants

• A ledger entry must change something:
  • available_delta != 0 OR reserved_delta != 0 OR deferred_revenue_delta_cents != 0 OR platform_fee_deferred_delta_cents != 0 OR recognized_revenue_cents != 0 OR platform_fee_recognized_cents != 0
• Entry types should follow sign rules (enforced via check constraints; see migrations section)

Indexes (statement-friendly)

• (billing_account_id, occurred_at DESC)
• (billing_account_id, billing_entitlement_type_id, occurred_at DESC)
• (reference_type, reference_id)
• unique (idempotency_key) (or scope it by account if preferred)

---

3.5 billing_entitlement_holds (projection)

Purpose

Operational convenience for “active reservations”:

• find hold by (reference_type, reference_id)
• quickly know remaining reserved amount
• enforce “only one active hold per reference” without expensive ledger scanning

This table is not a second ledger. It’s a cache like balances.

Minimal attributes

• id
• billing_account_id
• billing_entitlement_type_id
• reference_type, reference_id
• status enum: active, released, consumed, expired
• units_held (BIGINT)
• opened_at, closed_at
• opened_ledger_entry_id (FK to ledger entry that created the hold)
• timestamps

Invariants

• unique active hold per (account, type, reference)
• units_held >= 0

Indexes

• unique partial index on (billing_account_id, billing_entitlement_type_id, reference_type, reference_id) where status='active'

---

3.6 billing_entitlement_lots (gig only)

Purpose

Track FIFO purchase batches for gig credits with per-lot platform fee rate.

Minimal attributes

• id

• billing_account_id

• billing_entitlement_type_id (must be gig_credit_cents)

• purchased_at

• source_reference_type, source_reference_id (recommended: Billing::InvoiceItem + billing_invoice_items.id for the gig principal line item; avoid payment because payments can cover multiple invoice items)

• Units:

  • units_purchased (BIGINT cents)
  • units_available (BIGINT cents)
  • units_reserved (BIGINT cents)

• Fee:

  • platform_fee_rate_bps (integer, e.g. 2000 = 20.00%)
  • platform_fee_total_cents
  • platform_fee_remaining_cents

Invariants

• units_available >= 0, units_reserved >= 0
• units_available + units_reserved <= units_purchased (consumed removed elsewhere via allocations; see below)
• platform_fee_remaining_cents >= 0

FIFO rule

Consume/reserve from lots ordered by purchased_at ASC, id ASC.

Gotcha

Do not store decimals for credits or money. Use BIGINT cents.

---

3.7 billing_entitlement_lot_allocations (gig only)

Purpose

Record exactly how a ledger entry (reserve/consume/release/adjust) maps to lots.

This is how you:

• prove FIFO
• compute platform fee recognized correctly
• generate detailed gig SOA lines (“which batch funded this shift?”) if needed

Minimal attributes

• id

• billing_ledger_entry_id (FK)

• billing_entitlement_lot_id (FK)

• units_allocated (BIGINT cents; positive number)

• allocation_type enum:

  • reserve, consume, release, adjust

• platform_fee_recognized_cents (nullable; typically only for consume)

• timestamps

Invariants

• units_allocated > 0

---

4) Concurrency, Idempotency, and Data Integrity

4.1 Transaction boundary (non-negotiable)

Every billing operation must run inside a single DB transaction:

1. Lock the relevant billing_entitlement_balances row FOR UPDATE
2. If gig: lock the relevant lots rows FOR UPDATE in FIFO order
3. Insert ledger entry (or entries)
4. Update balances and holds projections
5. Update lots and allocations (gig)

If any step fails, rollback everything.

4.2 Idempotency

Every external-triggered call must provide an idempotency_key:

• Ads daily consume job retries → no double consumption
• Gig completion retries → no duplicate charges
• Payment verification callbacks → no duplicate grants

Enforce with a unique index on billing_ledger_entries.idempotency_key.

4.3 Check constraints (DB-level safety)

Use Postgres check constraints to prevent invalid ledger entries from being inserted.

Examples:

• reserve must move equal units between available and reserved
• release must move equal units back
• consume must reduce either available or reserved
• recognized revenue must be present for placement consumes

(See migrations section for concrete SQL constraints.)

---

5) Use Cases and How Models Interact

Below, each use case shows:

• which models/tables are touched
• the ledger entries inserted
• how balances/holds/lots change

5.0 Purchase flow (Invoice → Bank Transfer → Verification → Posting → Entitlements Granted)

Scenario

An Org::Company (Singapore) wants to buy:

• 1,000 Placement Credits (sold as “Visibility Credits” in UI) to run sponsored placements / job posting / boosts, or
• 1,000 Gig Credits (stored value) with a negotiated platform fee rate.

Step-by-step (models interacting)

Step 1 — Choose an offer (pricing row)

Input: org_company.country_id (you already store this), desired product, desired quantity.

Billing query:

• Find billing_products by code (e.g. placement_credits)
• Find a matching billing_product_prices for:
  • country_id = org_company.country_id
  • active_from/active_until (if used)
  • optional: tier / negotiated pricing

Gotcha: Do not hard-code pricing into code. ProductPrice has the data.

Step 2 — Generate invoice

Create billing_invoices (draft → issued) and billing_invoice_items.

Invoice item must snapshot:

• unit price, tax, total
• entitlement type
• units_to_grant
• gig: platform fee rate terms (bps) and principal vs platform fee breakdown

Step 3 — Customer pays via bank transfer (offline)

Option A (recommended operationally): business team records a payment row when they receive proof:

• create billing_payments with status=submitted and proof.

Option B: create billing_payments immediately upon invoice issuance (pending), then update.

Either is fine as long as:

• invoice stays issued until payment is verified.

Step 4 — Business team verifies bank transfer(s)

When finance confirms money received (one or more transfers):

• update the relevant billing_payments.status = verified
• recompute verified_total = sum(billing_payments.amount_cents where status=verified)
  • if verified_total == 0 → invoice stays issued
  • if 0 < verified_total < invoice.total_cents → set billing_invoices.status = partially_paid
  • if verified_total >= invoice.total_cents → set billing_invoices.status = paid and set settled_at = now

Policy: Credits are granted only after the invoice is paid. Partial payments do not grant partial credits.

Step 5 — Post the invoice (grant entitlements; idempotent)

In the same DB transaction:

1. Lock the invoice row (SELECT ... FOR UPDATE)
2. Create billing_invoice_postings (unique by invoice id)
3. For each invoice item:
   • write one or more billing_ledger_entries to grant units + deferred revenue deltas
   • if entitlement policy is lot-based (gig): create billing_entitlement_lots using snapshot terms

Then update projections:

• billing_entitlement_balances
• (gig) lots remaining / fee deferred remaining

Result: the company can immediately spend entitlements.

Why the posting record is important

It gives you:

• hard idempotency (cannot double-grant if admin clicks verify twice)
• an audit hook (“who posted this invoice, when?”)
• a clean pivot for finance exports (“posted entitlements today”)

SOA impact

Because the posting writes to the ledger, the Statement of Account generation remains simple:

• “Top-ups” are just ledger entries with entry_kind=grant
• “Usage” is ledger entries with entry_kind=consume
• Reservations are ledger entries with entry_kind=reserve/release

5.1 Placement Credits: Grant (top-up) after payment verified

Scenario

Company buys a Placement Credits package (shown as “Visibility Credits” in UI):

• +100 placement_credit
• +$500 deferred revenue

Steps

1. Billing service called: GrantEntitlements

2. Lock billing_entitlement_balances for (account, visibility_credit)

3. Insert ledger entry:

   • entry_type = grant
   • available_delta = +100
   • deferred_revenue_delta_cents = +50000

4. Update balance projection:

   • units_available += 100
   • deferred_revenue_cents += 50000

Statement line

“Purchased Visibility Credits +100”

---

5.2 Placement Credits: Reserve for an Ads campaign placement (visibility promise)

Scenario

Ads books a homepage placement for 14 days. You reserve upfront so they can’t spend the credits elsewhere.

Steps

1. Ads creates campaign line item (e.g. ads_campaign_placements.id = 999)

2. Ads calls Billing: ReserveEntitlements(reference: Ads::CampaignPlacement#999, units: 14)

3. Billing locks placement balance row

4. Ensure units_available >= 14

5. Insert ledger entry:

   • entry_type = reserve
   • available_delta = -14
   • reserved_delta = +14
   • reference_type='Ads::CampaignPlacement', reference_id=999

6. Upsert hold projection:

   • status active
   • units_held = 14
   • opened_ledger_entry_id = <reserve_entry_id>

7. Update balance:

   • available -= 14
   • reserved += 14

Statement lines

“Reserved 14 Visibility Credits for CampaignPlacement #999”

---

5.3 Placement Credits: Daily consumption (deliver service + recognize revenue)

Scenario

Each day the campaign runs, consume 1 credit from reserved. At the same time, recognize revenue proportionally.

Revenue recognition rule (pooled proportional)

At consume time:

• recognized = units_consumed × (deferred_revenue_before / pool_units_before)

Where:

• pool_units_before = balance.units_available + balance.units_reserved
• deferred_revenue_before = balance.deferred_revenue_cents

Steps (daily job)

1. Scheduler triggers: ConsumeEntitlements(reference: Ads::CampaignPlacement#999, units: 1)

2. Lock balance row

3. Confirm hold is active and has units held

4. Compute snapshots:

   • pool_units_before = available + reserved
   • deferred_revenue_before_cents = deferred_revenue

5. Compute recognized:

   • recognized = 1 * deferred_revenue_before / pool_units_before
   • use integer math with rounding rule you choose (recommend: round half up to cents)

6. Insert ledger entry:

   • entry_type = consume
   • reserved_delta = -1
   • recognized_revenue_cents = recognized
   • deferred_revenue_delta_cents = -recognized
   • snapshot fields: pool_units_before, pool_deferred_revenue_before_cents
   • reference to campaign placement

7. Update balance projection:

   • reserved -= 1
   • deferred_revenue -= recognized

8. Update hold projection:

   • units_held -= 1
   • if units_held becomes 0: mark hold consumed, closed_at

Statement line

“Consumed 1 Placement Credit for CampaignPlacement #999 (recognized $X.XX)”

---

5.4 Placement Credits: Cancel campaign and release remaining reservation

Scenario

Campaign canceled with 5 days remaining. Release held credits back to available.

Steps

1. Ads calls Billing: ReleaseHold(reference: Ads::CampaignPlacement#999)

2. Find active hold with units_held=5

3. Lock balance row

4. Insert ledger entry:

   • entry_type = release
   • available_delta = +5
   • reserved_delta = -5

5. Update balance

6. Mark hold as released

Statement line

“Released 5 Visibility Credits for CampaignPlacement #999”

---

5.5 Careers Job Posting: consume credits (two pricing modes)

Pricing mode A: per job posting

At job publish:

• reserve (optional) then consume immediately

Recommended: consume immediately (no need to reserve unless you have multi-step approvals).

Ledger entry:

• consume with available_delta=-X or reserved_delta=-X
• reference: Careers::Job

Pricing mode B: per application

At application creation:

• consume 1 credit per application
• reference: Careers::JobApplication

This is exactly why we keep the ledger generic: pricing changes don’t require schema changes.

---

5.6 Job Boost: reserve then daily consume (same as Ads)

Boost is a “visibility placement” product. Treat the boosted listing as a reference:

• reference_type='Listings::Boost', reference_id=<boost_id>

Reserve N days upfront, consume 1/day.

---

5.7 Gig: Grant credits (top-up) and create FIFO lot

Scenario

Company buys:

• $100.00 gig credits (10000 cents)
• platform fee rate 20% → $20.00 deferred platform fee

Steps

1. Billing locks gig balance

2. Insert ledger entry:

   • entry_type=grant
   • available_delta=+10000 (cents)
   • platform_fee_deferred_delta_cents=+2000

3. Create lot:

   • units_purchased=10000
   • units_available=10000
   • units_reserved=0
   • platform_fee_rate_bps=2000
   • platform_fee_remaining_cents=2000

4. Update balance projection:

   • available += 10000
   • platform_fee_deferred += 2000

Statement line

“Purchased Gig Credits $100.00 (+ platform fee deferred $20.00)”

---

5.8 Gig: Reserve credits when posting a shift (may span lots)

Scenario

Posting a gig shift reserves estimated wage value, e.g. 1800 cents ($18.00).

Steps

1. Gig calls Billing: ReserveEntitlements(reference: Gig::Shift#123, units: 1800)

2. Lock gig balance row

3. Lock lots with units_available > 0 FIFO order

4. Allocate 1800 across lots FIFO:

   • Lot A take 1000
   • Lot B take 800

5. Insert ledger entry:

   • entry_type=reserve
   • available_delta=-1800
   • reserved_delta=+1800
   • reference shift

6. Insert allocations:

   • (reserve, lot A, 1000)
   • (reserve, lot B, 800)

7. Update lots:

   • lot A units_available -=1000, units_reserved +=1000
   • lot B units_available -=800, units_reserved +=800

8. Update balance:

   • available -=1800, reserved +=1800

9. Create hold projection:

   • units_held=1800, status active

Statement line

“Reserved $18.00 Gig Credits for Shift #123”

---

5.9 Gig: Complete shift (consume actual wage, handle differences vs reserved)

Scenario

Reserved 1800 cents earlier. Actual wage payable becomes 1750 cents due to deductions/adjustments.

Recommended billing behavior

• Consume actual amount (1750) from reserved
• Release remainder (50) back to available This keeps statements clean and mirrors reality.

Steps

1. Gig calls Billing: CompleteShift(reference: Gig::Shift#123, actual_units: 1750, metadata: insurance...)

2. Find active hold units_held=1800

3. Lock gig balance + related lots (based on hold allocations)

4. Consume from lots that were reserved (proportionally to reserved allocations FIFO):

   • Lot A consume 1000
   • Lot B consume 750 (of its 800 reserved)

5. Compute platform fee recognized per allocation:

   • per lot: fee_recognized = (consumed_cents * rate_bps) / 10_000

6. Insert ledger entry (consume):

   • entry_type=consume
   • reserved_delta=-1750
   • platform_fee_recognized_cents=<sum fee recognized>

7. Insert allocation rows (consume):

   • lot A consume 1000, fee recognized
   • lot B consume 750, fee recognized

8. Update lots:

   • lot A units_reserved -=1000
   • lot B units_reserved -=750
   • reduce lot platform_fee_remaining_cents by recognized portion (based on consumed)

9. Update balance:

   • reserved -=1750
   • platform_fee_deferred -= fee_recognized (optional cache update)

10. Insert ledger entry (release remainder 50):

• entry_type=release
• available_delta=+50, reserved_delta=-50

11. Update lots for release remainder:

• release remaining 50 back to the lot it was reserved from (lot B):

  • lot B units_reserved -=50, units_available +=50

12. Close hold: consumed/released

Statement lines

• “Consumed $17.50 Gig Credits for Shift #123”
• “Released $0.50 Gig Credits for Shift #123”

---

5.10 Gig: Cancel shift before completion (release reservation)

Steps

1. Find active hold for shift

2. Insert ledger entry release:

   • available_delta=+held, reserved_delta=-held

3. Update allocations: release units back into lots (reverse of reserve allocations)

4. Update lots: reserved → available

5. Close hold

---

6) Statements of Account (SOA)

6.1 The SOA contract (what we want)

For a given company:

• Show a chronological list of ledger entries
• Group by reference when needed (e.g. shift/campaign)
• Provide running balances
• Provide totals within period

6.2 SOA query pattern (single ledger = simple)

Gig SOA

Filter by:

• account_id
• entitlement_type = gig_credit_cents
• date range

Order by occurred_at ASC, id ASC.

Placement Credits usage report

Same, with entitlement type = placement_credit.

Running balance calculation

Option A (fast): use projections + reconstruct running changes from ledger lines in memory. Option B (SQL window): use window sums if you really need it in SQL.

Recommendation for MVP: do it in Ruby:

• fetch rows ordered
• running_available += available_delta
• running_reserved += reserved_delta

6.3 SOA formatting guidance

Ledger entry should render as:

• timestamp
• action label (grant/reserve/consume/release/adjust)
• units change
• money change (recognized revenue / deferred changes)
• reference label (Shift #123, CampaignPlacement #999)
• metadata (insurance, notes)

This is why ledger is the canonical statement source.

---

7) Finance Exports (Xero-friendly, not cornered)

7.1 Design goals

• Match your current finance workflow (lump-sum daily entries)

• Maintain customer-level truth internally regardless of what Xero stores

• Support future enhancements:

  • per-invoice exports
  • per-customer deferred breakdown (if finance ever wants it)
  • multi-legal-entity expansion into Singapore / Indonesia

7.2 What the ledger must provide to exports

Exports need:

• recognized revenue amounts with timestamps
• deferred revenue movements (especially for placement credits)
• gig platform fee recognized (lot-based)
• gig credits consumed (principal liability reduction)
• insurance deductions/sponsorships (from metadata)

Key point: we store recognition snapshots at the moment of consume → exports don’t need to recompute or re-allocate later.

7.3 Suggested accounting mapping configuration (small but crucial)

Create a configuration layer (can be table-backed later) mapping:

• entitlement type → liability accounts
• revenue accounts
• tax codes if needed

Minimal mapping keys you will eventually need

• Placement credits:

  • Deferred revenue liability account
  • Placement revenue account

• Gig credits:

  • Stored value liability account
  • Platform fee deferred account
  • Platform fee revenue account
  • Insurance payable / insurance expense (depending on your treatment)

You can start with constants in code, but don’t hardcode everywhere—centralize it.

7.4 Export Mode A: Daily Journal Summary (recommended initial integration)

For each day:

• Sum recognized revenue for placement credits (from ledger.consume)
• Sum deferred revenue increases (from ledger.grant placement)
• Sum gig platform fee recognized (from gig consume)
• Sum gig credits consumed (from gig consume units)
• Sum insurance fields from metadata

Then create journal lines:

• Debit/Credit deferred liability and revenue accordingly

Why daily journal mode is best first

• matches current finance rhythm
• avoids building customer contact sync in Xero immediately
• keeps volume manageable

7.5 Export Mode B: Invoice-based (later)

If you generate billing_invoices:

• Export sales invoices + payments
• Still use ledger consumption to recognize revenue over time This is a bigger accounting integration step and doesn’t block you now.

7.6 Idempotent export runs (avoid double-posting)

Even if you start with CSV export, you should track export runs to avoid re-exporting.

Add (optional but recommended):

• billing_export_runs:

  • export_type (daily_journal)
  • date_from, date_to
  • status
  • unique constraint on (export_type, date_from, date_to)

• Later when you use Xero API, store returned journal IDs.

This prevents “oops we imported the same day twice”.

---

8) Rails Implementation Guidance (service layer patterns)

8.1 Public Billing API (application services)

Keep Billing mutations behind a small set of commands:

• Billing::GrantEntitlements.call(account:, entitlement_type:, units:, money:, reference:, idempotency_key:)
• Billing::ReserveEntitlements.call(account:, entitlement_type:, units:, reference:, idempotency_key:)
• Billing::ReleaseHold.call(account:, entitlement_type:, reference:, idempotency_key:)
• Billing::ConsumeEntitlements.call(account:, entitlement_type:, units:, reference:, idempotency_key:, metadata:)
• Billing::AdjustEntitlements.call(...)

Internally:

• lock balance row
• enforce invariants
• insert ledger entry
• update projections
• if gig: allocate and update lots

8.2 Locking strategy

• BillingEntitlementBalance.lock("FOR UPDATE")
• For gig: select lots FOR UPDATE ordered FIFO

8.3 Rounding policy (important)

For proportional recognition (placement credits):

• choose one policy and document it:

  • “round half up to cents”
  • or “banker’s rounding”

• store recognized cents on the ledger entry so the export matches statements exactly.

---

9) Migrations (Rails + Postgres constraints + indexes)

Below are migration-style examples. Treat these as templates (you can split into multiple migrations).

Notes:

• Use bigint for unit counts and cents.
• Use Postgres enums if you prefer; strings are fine if constrained.
• Add check constraints using execute.

9.1 billing_accounts

create_table :billing_accounts do |t|
  t.references :org_company, null: false, foreign_key: true, index: { unique: true }
  t.string :currency, null: false, default: "SGD"
  t.string :status, null: false, default: "active"
  t.timestamps
end

9.2 billing_entitlement_types

create_table :billing_entitlement_types do |t|
  t.string :code, null: false
  t.string :unit_name, null: false
  t.string :allocation_policy, null: false # pooled, fifo_lots
  t.string :recognition_policy, null: false # proportional_average, lot_based
  t.boolean :is_reservable, null: false, default: true
  t.timestamps
end

add_index :billing_entitlement_types, :code, unique: true

Seed examples:

• visibility_credit: pooled + proportional_average
• gig_credit_cents: fifo_lots + lot_based

9.3 billing_entitlement_balances

create_table :billing_entitlement_balances do |t|
  t.references :billing_account, null: false, foreign_key: true
  t.references :billing_entitlement_type, null: false, foreign_key: true

  t.bigint :units_available, null: false, default: 0
  t.bigint :units_reserved, null: false, default: 0

  t.bigint :deferred_revenue_cents, null: false, default: 0
  t.bigint :platform_fee_deferred_cents, null: false, default: 0

  t.timestamps
end

add_index :billing_entitlement_balances,
  [:billing_account_id, :billing_entitlement_type_id],
  unique: true,
  name: "idx_billing_balances_unique"

You can keep deferred_revenue_cents and platform_fee_deferred_cents both present (default 0). They’ll be “unused” depending on entitlement type—but safer than nulls for arithmetic.

9.4 billing_ledger_entries

create_table :billing_ledger_entries do |t|
  t.references :billing_account, null: false, foreign_key: true
  t.references :billing_entitlement_type, null: false, foreign_key: true

  t.string :entry_type, null: false # grant, reserve, release, consume, adjust
  t.datetime :occurred_at, null: false
  t.string :idempotency_key, null: false

  t.bigint :available_delta, null: false, default: 0
  t.bigint :reserved_delta, null: false, default: 0

  t.bigint :deferred_revenue_delta_cents, null: false, default: 0
  t.bigint :recognized_revenue_cents, null: false, default: 0

  t.bigint :platform_fee_deferred_delta_cents, null: false, default: 0
  t.bigint :platform_fee_recognized_cents, null: false, default: 0

  t.bigint :pool_units_before
  t.bigint :pool_deferred_revenue_before_cents

  t.string :reference_type
  t.bigint :reference_id

  t.jsonb :metadata, null: false, default: {}

  t.timestamps
end

add_index :billing_ledger_entries, :idempotency_key, unique: true
add_index :billing_ledger_entries, [:billing_account_id, :occurred_at], name: "idx_ledger_account_time"
add_index :billing_ledger_entries, [:billing_account_id, :billing_entitlement_type_id, :occurred_at], name: "idx_ledger_account_type_time"
add_index :billing_ledger_entries, [:reference_type, :reference_id], name: "idx_ledger_reference"

Check constraints (strongly recommended)

execute <<~SQL
  ALTER TABLE billing_ledger_entries
  ADD CONSTRAINT chk_ledger_nonzero_delta
  CHECK (
    available_delta <> 0
    OR reserved_delta <> 0
    OR deferred_revenue_delta_cents <> 0
    OR recognized_revenue_cents <> 0
    OR platform_fee_deferred_delta_cents <> 0
    OR platform_fee_recognized_cents <> 0
  );
SQL

execute <<~SQL
  ALTER TABLE billing_ledger_entries
  ADD CONSTRAINT chk_reserve_moves_equal
  CHECK (
    entry_type <> 'reserve'
    OR (available_delta < 0 AND reserved_delta > 0 AND available_delta = -reserved_delta)
  );
SQL

execute <<~SQL
  ALTER TABLE billing_ledger_entries
  ADD CONSTRAINT chk_release_moves_equal
  CHECK (
    entry_type <> 'release'
    OR (available_delta > 0 AND reserved_delta < 0 AND available_delta = -reserved_delta)
  );
SQL

execute <<~SQL
  ALTER TABLE billing_ledger_entries
  ADD CONSTRAINT chk_consume_reduces_units
  CHECK (
    entry_type <> 'consume'
    OR (available_delta <= 0 AND reserved_delta <= 0 AND (available_delta <> 0 OR reserved_delta <> 0))
  );
SQL

You can add a more specific constraint for placement consume requiring recognized revenue fields when entitlement type is placement. Cross-table checks are harder in pure SQL; enforce via application validations.

9.5 billing_entitlement_holds

create_table :billing_entitlement_holds do |t|
  t.references :billing_account, null: false, foreign_key: true
  t.references :billing_entitlement_type, null: false, foreign_key: true

  t.string :reference_type, null: false
  t.bigint :reference_id, null: false

  t.string :status, null: false, default: "active" # active, released, consumed, expired
  t.bigint :units_held, null: false, default: 0

  t.references :opened_ledger_entry, null: false, foreign_key: { to_table: :billing_ledger_entries }

  t.datetime :opened_at, null: false
  t.datetime :closed_at

  t.timestamps
end

add_index :billing_entitlement_holds,
  [:billing_account_id, :billing_entitlement_type_id, :reference_type, :reference_id],
  unique: true,
  where: "status = 'active'",
  name: "idx_holds_unique_active"

9.6 billing_entitlement_lots (gig)

create_table :billing_entitlement_lots do |t|
  t.references :billing_account, null: false, foreign_key: true
  t.references :billing_entitlement_type, null: false, foreign_key: true

  t.datetime :purchased_at, null: false
  t.string :source_reference_type
  t.bigint :source_reference_id

  t.bigint :units_purchased, null: false
  t.bigint :units_available, null: false
  t.bigint :units_reserved, null: false

  t.integer :platform_fee_rate_bps, null: false
  t.bigint :platform_fee_total_cents, null: false
  t.bigint :platform_fee_remaining_cents, null: false

  t.timestamps
end

add_index :billing_entitlement_lots, [:billing_account_id, :purchased_at, :id], name: "idx_lots_fifo"
add_index :billing_entitlement_lots, [:billing_account_id], name: "idx_lots_account"

9.7 billing_entitlement_lot_allocations (gig)

create_table :billing_entitlement_lot_allocations do |t|
  t.references :billing_ledger_entry, null: false, foreign_key: true
  t.references :billing_entitlement_lot, null: false, foreign_key: true

  t.string :allocation_type, null: false # reserve, consume, release, adjust
  t.bigint :units_allocated, null: false
  t.bigint :platform_fee_recognized_cents, null: false, default: 0

  t.timestamps
end

add_index :billing_entitlement_lot_allocations, [:billing_ledger_entry_id], name: "idx_alloc_entry"
add_index :billing_entitlement_lot_allocations, [:billing_entitlement_lot_id], name: "idx_alloc_lot"

execute <<~SQL
  ALTER TABLE billing_entitlement_lot_allocations
  ADD CONSTRAINT chk_alloc_positive_units
  CHECK (units_allocated > 0);
SQL

---

10) Recommended file layout in Rails (for mid-level engineers)

app/domains/billing/
  models/
    billing_account.rb
    billing_entitlement_type.rb
    billing_entitlement_balance.rb
    billing_ledger_entry.rb
    billing_entitlement_hold.rb
    billing_entitlement_lot.rb
    billing_lot_allocation.rb

  services/
    grant_entitlements.rb
    reserve_entitlements.rb
    release_hold.rb
    consume_entitlements.rb
    gig/
      fifo_allocator.rb
      consume_reserved.rb
      release_reserved.rb

  reporting/
    statement_of_account.rb
    daily_finance_summary.rb

  accounting/
    mappings.rb
    journal_builder.rb
    export_run.rb

Keep the integration points from Ads/Careers/Gig calling Billing services explicitly.

---

11) Final recommendations (so you don’t regret this later)

1. Single ledger is the right choice for you

   • It makes statements and reporting simpler forever.

2. Keep holds and balances as projections

   • They avoid expensive scans and enforce “one active hold per reference”.

3. Gig requires lots + allocations

   • Don’t fight it. This is the minimal complexity that preserves correctness and auditability.

4. Never store money as decimals

   • Use BIGINT cents + basis points for rates.

5. Store recognition amounts at consumption time

   • This is what prevents Xero exports from becoming a fragile “recompute history” nightmare.

---

If you want, in the next step I can also provide:

• the exact Ruby service object pseudocode for ReserveEntitlements / ConsumeEntitlements including row locking order (to avoid deadlocks),
• and a sample “Daily Journal CSV export” format that matches your current finance workflow and is straightforward to import into Xero later.