Billing ledger schema that reconciles: invoices and payments

Why billing data stops reconciling

For finance, “reconcile” is a simple promise: the totals in reports match the source records, and every number can be traced. If the month shows $12,430 collected, you should be able to point to the exact payments (and any refunds), see which invoices they apply to, and explain every difference with a dated record.

Billing data usually stops reconciling when the database stores results instead of facts. Columns like paid_amount, balance, or amount_due get updated over time by application logic. One bug, one retry, or one manual “fix” can quietly change history. Weeks later, the invoice table says an invoice is “paid,” but the payment rows don’t add up, or a refund exists without a matching credit.

Another common cause is mixing different document types together. An invoice is not a payment. A credit memo is not a refund. An adjustment is not the same thing as a discount. When those are squeezed into one “transactions” row with lots of optional fields, reporting turns into guesswork and audits turn into arguments.

The underlying mismatch is simple: apps often care about the current state (“is access active?”), while finance cares about the trail (“what happened, when, and why?”). A billing ledger schema has to support both, but traceability must win.

Design for this outcome:

• Clear totals per customer, per invoice, and per accounting period
• Every change recorded as a new row (not overwritten)
• A complete chain from invoice to payments, credits, refunds, and adjustments
• The ability to re-calculate totals from raw entries and get the same answer

Example: if a customer pays $100, then gets a $20 credit, your reports should show $100 collected, $20 credited, and $80 net, without editing the original invoice amount.

Separate invoices, payments, credits, and adjustments

If you want a billing ledger schema that reconciles, treat each document type as a different kind of event. Mixing them into one “transactions” table looks tidy, but it blurs meaning.

An invoice is a claim: “the customer owes us money.” Store it as a document with a header (customer, invoice number, issue date, due date, currency, totals) and separate line items (what was sold, quantity, unit price, tax category). It’s fine to store header totals for speed, but you should always be able to explain them from the lines.

A payment is money movement: “cash went from the customer to us.” In card flows, you often see authorization (bank approves) and capture (money actually taken). Many systems keep authorizations as operational records and only put captured payments into the ledger, so you don’t inflate cash reporting.

A credit memo reduces what the customer owes without necessarily sending money back. A refund is cash going out. They often happen together, but they are not the same thing.

• Invoice: increases receivable and revenue (or deferred revenue)
• Payment: increases cash and reduces receivable
• Credit memo: reduces receivable
• Refund: reduces cash

An adjustment is a correction made by your team when reality doesn’t match the records. Adjustments need context so finance can trust them. Store who created it, when it was posted, a reason code, and a short note. Examples: “write off 0.03 due to rounding,” or “migrate legacy balance.”

A practical rule: ask, “Would this exist if no one made a mistake?” Invoices, payments, credit memos, and refunds still exist. Adjustments should be rare, clearly labeled, and easy to review.

Pick a ledger model finance can audit

A reconciling billing ledger schema starts with one idea: documents describe what happened, and ledger postings prove the totals. An invoice, payment, or credit memo is a document. The ledger is the set of entries that add up, period.

Documents vs postings (store both)

Keep the documents (invoice header and lines, payment receipt, credit memo) because people need to read them. But don’t rely on document totals alone as the source of truth for reconciliation.

Instead, post each document into a ledger table as one or more immutable entries. Then finance can sum entries by account, customer, currency, and posting date and get the same answer every time.

A simple audit-friendly model follows a few rules:

• Immutable entries: never edit posted amounts; changes are new entries.
• Clear posting event: each document creates a posting batch with a unique reference.
• Balanced logic: entries net correctly (often debit equals credit at the company level).
• Separate dates: keep document date (what the customer sees) and posting date (what hits reporting).
• Stable references: store the external reference (invoice number, payment processor ID) alongside internal IDs.

Natural keys vs surrogate IDs

Use surrogate IDs for joins and performance, but also store a stable natural key that survives migrations and re-imports. Finance will ask for “Invoice INV-10483” long after database IDs change. Treat invoice numbers and provider IDs (like a payment processor charge ID) as first-class fields.

Reversals without deleting history

When something must be undone, don’t delete or overwrite. Post a reversal: new entries that mirror the original amounts with opposite signs, linked back to the original posting.

Example: a $100 payment applied to the wrong invoice becomes two steps: reverse the misapplied posting, then post a new application to the correct invoice.

Step-by-step schema blueprint (tables and keys)

A billing ledger schema reconciles more reliably when each document type has its own table and you connect them with explicit allocation records (instead of guessing relationships later).

Start with a small set of core tables, each with a clear primary key (UUID or bigserial) and required foreign keys:

• customers: customer_id (PK), plus stable identifiers like external_ref (unique)
• invoices: invoice_id (PK), customer_id (FK), invoice_number (unique), issue_date, due_date, currency
• invoice_lines: invoice_line_id (PK), invoice_id (FK), line_type, description, qty, unit_price, tax_code, amount
• payments: payment_id (PK), customer_id (FK), payment_date, method, currency, gross_amount
• credits: credit_id (PK), customer_id (FK), credit_number (unique), credit_date, currency, amount

Then add the tables that make totals auditable: allocations. A payment or credit can cover multiple invoices, and an invoice can be paid by multiple payments.

Use join tables with their own keys (not just composite keys):

• payment_allocations: payment_allocation_id (PK), payment_id (FK), invoice_id (FK), allocated_amount, posted_at
• credit_allocations: credit_allocation_id (PK), credit_id (FK), invoice_id (FK), allocated_amount, posted_at

Finally, keep adjustments separate so finance can see what changed and why. An adjustments table can reference the target record with invoice_id (nullable) and store the delta amount, without rewriting history.

Add audit fields everywhere you post money:

• created_at, created_by
• reason_code (write-off, rounding, goodwill, chargeback)
• source_system (manual, import, Stripe, support tool)

Credits, refunds, and write-offs without broken totals

Most reconciliation problems start when credits and refunds are recorded as “negative payments,” or when write-offs are mixed into invoice lines. A clean billing ledger schema keeps each document type as its own record, and the only place they interact is through explicit allocations.

A credit should show why you reduced what the customer owes. If it applies to one invoice, record a single credit memo and allocate it to that invoice. If it applies across several invoices, allocate the same credit memo to multiple invoices. The credit stays one document with many allocations.

Refunds are payment-like events, not negative payments. A refund is cash leaving you, so treat it as its own record (often tied to the original payment for reference), then allocate it just like a payment. This keeps the audit trail clear when the bank statement shows both the incoming payment and the outgoing refund.

Partial payments and partial credits work the same way: keep the payment or credit total on its own row, and use allocation rows for how much was applied to each invoice.

Posting rules that prevent double counting

These rules remove most “mystery differences”:

• Never store a negative payment. Use a refund record.
• Never reduce an invoice total after posting. Use a credit memo or adjustment.
• Post documents once (with a posted_at timestamp) and don’t edit amounts after posting.
• The only thing that changes invoice balance is the sum of posted allocations.
• A write-off is an adjustment with a reason code, allocated to the invoice like a credit.

Taxes, fees, currency, and rounding choices

Most reconciliation problems start with totals you can’t re-create. The safest rule is simple: store the raw lines that created the bill, and also store the totals you showed to the customer.

Taxes and fees: keep them at line level

Store tax and fee amounts per line item, not only as invoice-level summary fields. Different products can have different tax rates, fees may be taxable or not, and exemptions often apply to only part of the invoice. If you only store a single tax_total, you will eventually hit a case you can’t explain.

Keep:

• Raw lines (what was sold, qty, unit price, discount)
• Calculated line totals (line_subtotal, line_tax, line_total)
• Invoice summary totals (subtotal, tax_total, total)
• The tax rate and tax type used
• Fees as their own line items (for example, “Payment processing fee”)

This lets finance rebuild totals and confirm that tax was computed the same way every time.

Multi-currency: store what happened and how you report it

If you support multiple currencies, record both the transaction currency and the reporting currency values. A practical minimum is: currency_code on every monetary document, an fx_rate used at posting time, and separate reporting amounts (for example, amount_reporting) if your books close in one currency.

Example: a customer is billed 100.00 EUR plus 20.00 EUR VAT. Store those EUR lines and totals, plus the fx_rate used when posting the invoice, and the converted totals for reporting.

Rounding deserves its own treatment. Pick one rounding rule (per line or per invoice) and stick to it. When rounding creates a difference, record it explicitly as a rounding adjustment line (or a small adjustment entry) instead of silently changing totals.

Statuses, posting dates, and what not to store

Reconciliation gets messy when a “status” is used as a shortcut for accounting truth. Treat status as a workflow label, and treat posted ledger entries as the source of truth.

Make statuses strict and boring. Each one should answer: can this document affect totals yet?

• Draft: internal only, not posted, should not hit reports
• Issued: finalized and sent, ready to be posted (or already posted)
• Void: canceled; if it was posted, it must be reversed
• Paid: fully settled by posted payments and credits
• Refunded: money went back out via a posted refund

Dates matter more than most teams expect. Finance will ask, “Which month did this belong to?” and your answer shouldn’t depend on UI activity logs.

• issued_at: when the invoice became final
• posted_at: when it counts in accounting reports
• settled_at: when funds cleared or the payment was confirmed
• voided_at / refunded_at: when the reversal became effective

What not to store as truth: derived numbers you can’t rebuild from the ledger. Fields like balance_due, is_overdue, and customer_lifetime_value are fine as cached views only if you can always recompute them from invoices, payments, credits, allocations, and adjustments.

A small example: a payment retry hits your gateway twice. Without an idempotency key, you store two payments, mark the invoice “paid,” then finance sees an extra $100 in cash. Store a unique idempotency_key per external charge attempt and reject duplicates at the database level.

Reports finance expects from day one

A billing ledger schema proves itself when finance can answer basic questions quickly and get the same totals every time.

Most teams start with:

• Accounts receivable aging: amounts still open by customer and age bucket (0-30, 31-60, etc.)
• Cash received: money collected by day, week, and month, based on payment posting dates
• Revenue vs cash: invoices posted vs payments posted
• Audit trail for exports: a drill-back path from a GL export line to the exact document and allocation rows that created it

Aging is where allocations matter most. Aging is not “invoice total minus payments total.” It’s “what is left open on each invoice as of a date.” That requires storing how each payment, credit, or adjustment was applied to specific invoices, and when those allocations were posted.

Cash received should be driven by the payments table, not by invoice status. Customers can pay early, late, or partially.

Revenue vs cash is why invoices and payments must stay separate. Example: you issue a $1,000 invoice on March 30, receive $600 on April 5, and issue a $100 credit on April 20. Revenue belongs to March (invoice posting), cash belongs to April (payment posting), and the credit reduces receivables when posted. Allocations are what tie it all together.

Example scenario: one customer, four document types

One customer, one month, four document types. Each document is stored once, and money moves through an allocation table (sometimes called “applications”). That makes the final balance easy to recompute and easy to audit.

Assume customer C-1001 (Acme Co.).

The records you create

invoices

payments

credits (credit memo, goodwill credit, etc.)

adjustments (correction after the fact, not a new sale)

allocations (this is what actually reconciles the balance)

How the invoice balance is computed

For INV-10, an auditor can recompute the open balance from source rows:

open_balance = invoice.total + sum(adjustments) - sum(allocations)

So: 120.00 + 5.00 - (70.00 + 20.00) = 35.00 due.

To trace the “35.00” back:

• Start at the invoice total (INV-10)
• Add posted adjustments tied to the same invoice (ADJ-3)
• Subtract each posted allocation applied to the invoice (AL-900, AL-901)
• Confirm each allocation points to a real source document (PAY-77, CR-5)
• Verify dates and posted_at to explain the timeline

Common mistakes that break reconciliation

Most reconciliation problems aren’t “math bugs.” They’re missing rules, so the same real-world event gets recorded two different ways depending on who touched it.

A common trap is using negative rows as a shortcut. A negative invoice line, a negative payment, and a negative tax line can all mean different things. If you allow negatives, define one clear reversal policy (for example: only use a reversal row that references the original row, and don’t mix reversal semantics with discounts).

Another frequent cause is changing history. If an invoice was issued, don’t edit it later to match a new price or a corrected address. Keep the original document and post an adjustment or credit document that explains the change.

Patterns that usually break totals:

• Using negative rows without a strict reversal rule and a reference back to the original row
• Editing old invoices after issuing instead of posting adjustments or credit notes
• Mixing gateway transaction IDs with internal IDs without a mapping table and clear source of truth
• Letting application code compute totals while supporting rows (tax, fee, rounding, allocations) are missing
• Not separating “money moved” (cash movement) from “money allocated” (which invoice the cash pays)

That last point causes the most confusion. Example: a customer pays $100, then you apply $60 to Invoice A and $40 to Invoice B. The payment is one cash movement, but it creates two allocations. If you only store “payment = invoice,” you can’t support partial payments, overpayments, or reallocation.

Checklist and next steps

Before adding more features, make sure the basics hold up. A billing ledger schema reconciles when every total can be traced to specific rows, and every change has an audit trail.

Quick reconciliation checks

Run these checks on a small sample (one customer, one month) and then on the full dataset:

• Every posted number on a report traces to source rows (invoice line, payment, credit memo, adjustment) with a posting date and currency.
• Allocations never exceed the document they apply to (payment allocations total is less than or equal to the payment total; same for credits).
• Nothing is deleted. Wrong entries are reversed with a reason, then corrected with a new posted row.
• Open balance is derivable, not stored (invoice open amount = invoice total minus posted allocations and credits).
• Document totals match their lines (invoice header total equals sum of lines, taxes, and fees after your rounding rule).

Next steps to ship something usable

Once your schema is solid, build the operational workflow around it:

• Admin screens to create, post, and reverse invoices, payments, credits, and adjustments with required notes
• A reconciliation view that shows documents and allocations side by side, including who posted what and when
• Exports finance expects (by posting date, by customer, by GL mapping if you have one)
• A period close workflow: lock posting dates for closed months, and require reversal entries for late fixes
• Test scenarios (refunds, partial payments, write-offs) that must match expected totals

If you want a faster path to a working internal finance portal, AppMaster (appmaster.io) can help you model the PostgreSQL schema, generate APIs, and build the admin screens from the same source, so posting and allocation rules stay consistent as the app evolves.