Invoicing app data model: customers, invoices, totals that work

Why invoicing totals break in real apps

Invoice totals usually look fine on day one. They break on day ten, when real people start editing invoices, applying discounts, recording partial payments, and asking for refunds.

Most issues come from totals not having a clear source of truth. An AI-built screen might store total = 120.00 on the invoice while the line items add up to 119.99 after rounding. Later, someone edits a quantity, but the stored total doesn’t update. Now the PDF, the database, and the payment record disagree.

“Correct totals” is more than basic math. It means your app can reproduce the same numbers every time using the same rules, even months later. It also means totals match what the user saw when they sent the invoice, including taxes, discounts, and any manual adjustments.

Another cause is lost history. If you overwrite an invoice in place, you can’t answer basic questions later: what changed, who changed it, whether the change happened before or after a payment or refund, and which version was actually sent.

A small scenario shows how fast this breaks: you send an invoice for 10 hours of work. The client pays half. Then you correct the hours to 9.5. If your data model doesn’t separate invoice versions from payments, your app can show the client as overpaid, underpaid, or fully paid depending on which screen they open.

Saving a single “total” field feels fast early on, but it creates pain once you add edits, partial payments, credits, and refunds.

Core entities you need (and what each one means)

A reliable invoicing app data model separates who you bill, what you bill for, and what happened after the invoice was sent. If these get mixed together, totals and statuses drift.

Contacts: customer is not always the payer

A Customer is the account you do business with (a company or person). Keep contacts separate so you can change where invoices go without changing the customer record.

A common setup is a long-lived customer record (name, default currency, tax ID, notes) plus separate billing and shipping contacts. That way, “Send invoices to accounting, but ship to our warehouse” doesn’t turn into overwritten addresses.

Invoices and line items: what you sent vs what you’re editing

An Invoice is the document you intend to send, with a customer, issue date, due date, currency, and invoice number. Treat draft vs sent as phases of the same invoice. If you allow changes after sending, add an Invoice Version (or revision) so you have a clean history.

For charges, treat Line Items as the source of truth for amounts. A line item can reference a Product/Service from a catalog or be a custom free-text item. Don’t force everything into a product table, and don’t store extra totals on line items unless you have a clear reason.

Money events: payments are not refunds

A Payment records money received and how it was applied to one or more invoices. A Refund is money returned, usually tied to a payment. (A Payout is money you send out, and it’s often unrelated to invoices.)

Keep lifecycle statuses minimal and stable (draft, sent, void). Treat “paid/overdue/partially paid” as derived from payments and dates, not as a manually edited field.

Step-by-step: design the schema before you build screens

If you build the UI first, your tables tend to copy whatever the first screen needed. That’s how an invoicing app data model ends up with gaps: unclear status, missing history, or invoices that can be edited into nonsense.

Start with the invoice lifecycle because it drives your rules and your data. A simple lifecycle works for many apps: draft (not final), sent (customer sees it), paid (fully settled), void (canceled and should not be collectible). Decide what changes are allowed at each step before you name a single column.

A practical order:

• Define states and transitions (draft -> sent -> paid, and void from draft or sent).
• Use internal immutable IDs (like UUIDs) and keep invoice numbers separate for humans.
• Decide what becomes locked after sending (line items, tax rates, billing address).
• List the minimum required fields per table.
• Make the currency decision early (single currency vs true multi-currency).

For IDs, use an internal immutable key for each record. Then add an invoice_number that is unique, readable, and never changes once sent. Users will quote the invoice number, not your primary key.

Be explicit about editability. You might allow fixing a typo in the customer name after sending, but not changing quantities or prices. If you want to allow money changes, model them as a new version, a credit note, or an adjustment, not a silent rewrite.

Minimum viable fields can stay small:

• Customer: id, name, email, billing_address
• Invoice: id, customer_id, invoice_number, status, issued_at, currency
• Line item: id, invoice_id, description, quantity, unit_price
• Payment (if you track it): id, invoice_id, amount, received_at

Single-currency apps can store money as integer minor units (like cents). Multi-currency apps need currency on every money field and clear exchange-rate rules.

How to keep totals correct and consistent

Most “wrong total” bugs come from unclear ownership. If your UI edits a line item, the invoice total should change in one predictable place, every time. Decide early what is computed, what is stored, and when values become final.

Start at the line item. Store the raw inputs (unit_price and quantity) and store the computed line_total your app calculated at the time. Use integer minor units (like cents) for every money field, including discounts and tax amounts. This avoids floating point surprises like 19.999999 showing up as 20.00.

A useful rule is: compute from raw fields, store what you display, and be consistent about when you recompute.

Pick a clear source of truth

You have three workable options. Mixing them without a rule creates drift:

• Compute totals on the fly from line items whenever you load an invoice.
• Store invoice totals and update them on every edit.
• Do both: compute, store, then validate.

If you do both, treat computed values as the validator. When they disagree, flag it instead of silently picking one.

Handle edits without rewriting history

Decide when an invoice is editable. A common approach is: drafts are editable, sent invoices are locked, and changes happen via a new version, a credit note, or an adjustment line.

Example: you sent an invoice with a 7.5% tax rate. A month later the tax rate changes. If you recompute using today’s tax rate, the old invoice total changes and the customer sees a different amount than what was sent.

To prevent that, snapshot the inputs that matter when the invoice becomes final: tax rate used, discount rules, rounding mode, and currency. Recalculation then uses the snapshot, not today’s account settings.

Model payment status without painting yourself into a corner

Many invoicing bugs start when one field tries to do too much. “Status” often gets used for everything: draft vs sent, late vs paid, refunded vs disputed. That works for a demo, then breaks the moment you support partial payments.

Keep two ideas separate:

• Invoice lifecycle status: what the document is (draft, sent, void, written off)
• Payment status: what money has happened (unpaid, partial, paid, overpaid, refunded, disputed)

These can disagree, and that’s normal. An invoice can be “sent” but “unpaid”. It can be “void” but still have a payment that must be refunded.

Model money movements, not just a label

Instead of storing “paid: true”, record each money movement in tables like payments and refunds (or a single payment_events table). Also track payment attempts separately from successful payments. Cards fail, get retried, then succeed. If you only store successes, you lose the story and support gets harder.

A simple rule: one row per real event, never rewrite history. If a payment is reversed, add a refund or chargeback event linked to the original payment.

Define “paid” as a calculation

Decide what “paid” means and make it consistent everywhere (UI, emails, reports). A common definition:

• net_paid = sum(successful payments) - sum(refunds and chargebacks)
• amount_due = invoice_total - net_paid
• Payment status is based on amount_due (0 means paid, negative means overpaid)

Example: an invoice total is $100. The customer pays $60, then pays $50. Net paid is $110, so the invoice is “overpaid” by $10. If you later refund $10, add a refund event and it becomes “paid” again without editing the old payments.

Taxes, discounts, and rounding that won’t surprise users

Most “my totals don’t match” bugs happen because taxes, discounts, and rounding get added late. If your data model makes these rules explicit, your UI can stay simple and users can verify every number.

Where to store tax

Tax can live at the line level, the invoice level, or both. What matters is having one clear calculation source.

Line-level tax is easiest to explain: each item has a taxable flag, a tax rate snapshot, and a computed tax amount. This works well when different items have different rates, or some are exempt.

Invoice-level tax is fine when everything shares one rate, but it gets messy with mixed rates. If you do invoice-level, store the rate used and the taxable subtotal you applied it to so later edits don’t change history.

Discounts and rounding rules

Discounts should be modeled as either a percentage or a fixed amount. Decide what they apply to (per line, subtotal, or total after tax) and encode it.

A practical pattern is to treat adjustments as first-class line items: shipping or handling as a positive line, discounts and credits as negative lines, and coupons as their own explicit entries.

Rounding also needs a clear rule. If you round per line, the invoice total can differ by a cent from rounding once on the subtotal. Choose a policy, then store the rounded results you display.

To make totals feel trustworthy, show the math the same way you compute it: subtotal, each adjustment, tax breakdown, and grand total.

Edits, versions, and audit history

Invoices feel simple until someone asks, “What did we actually send?” If your app lets people freely edit old invoices, totals and tax can change after the fact, and trust disappears.

When an invoice is sent

Treat “sent” as a line in the sand. Keep an immutable snapshot of what the customer saw, even if your product later changes pricing rules, tax rates, or rounding.

You can do this with versioned rows or a frozen snapshot payload. At minimum, snapshot the customer display info at send time, the line items as sent (including tax rate), the totals as sent, the terms and due date, and sent metadata like sent_at and recipient email.

Then decide what can still change without affecting money. Notes and internal tags are usually safe to keep editable. Monetary fields like unit_price, quantity, discounts, tax, and currency should be locked once sent (or only change via a new version).

Handling corrections

Real businesses make mistakes. Don’t “edit history” to hide them. Use explicit correction actions: a credit note for the difference, voiding with a reason, or cancel and reissue with a new invoice number (keeping the old one for audit).

Avoid hard deletes. Deleted invoices break numbering, reports, and payment reconciliation. Voiding keeps the record, sets the amount due to zero, and preserves the trail.

For non-technical teams, keep a small change log: who changed what, when, and why.

Example: one customer, two invoices, partial payment, refund

Here’s a concrete scenario you can use to sanity-check your invoicing app data model.

Customer: Green Field Studio (one billing contact, one currency).

Invoice 1: one-time setup fee

Invoice INV-1001 has one line item: setup fee for $500.00. Assume 8% sales tax.

Subtotal: $500.00 | Tax: $40.00 | Total: $540.00

A single payment of $540.00 is recorded. The invoice moves from Sent to Paid, and the open balance becomes $0.00.

Invoice 2: monthly plan with line items, discount, tax

Invoice INV-1002 has three line items: monthly plan ($200.00), extra seats (3 x $20 = $60.00), and a priority support add-on ($50.00). Subtotal is $310.00.

Apply a 10% discount on the subtotal: -$31.00, so the discounted subtotal is $279.00. Tax at 8% is $22.32.

Total due: $279.00 + $22.32 = $301.32

Now the events you should see after each step:

Notice what does (and does not) change at Step 4: the invoice total stays $301.32, and the open balance stays $0.00 because you already collected the full amount. The refund is a separate money movement, so the customer now has $50.00 owed back (often tracked as customer credit).

Common mistakes AI-generated prototypes make

AI tools can get you to a working demo fast, but invoicing is full of small rules that fail quietly. If your data model is even slightly off, you end up with totals that change, payments that don’t match, and reports you can’t trust.

Money math that drifts over time

A common prototype mistake is using floating-point numbers for money (like 19.99) and summing them in different places. This creates tiny rounding differences that show up as a 1 cent mismatch between the invoice total, the payment total, and the PDF.

Use integer minor units (cents) for stored values, and round once at the edge (display, PDF, export). If you must store decimal, use a fixed-precision decimal type and be consistent.

Totals that stop matching edits

Many prototypes store subtotal, tax_total, and total on the invoice but never recalculate them when line items change. Or they recalculate on create, but not on edit, import, or API updates.

A safer pattern is: compute totals from line items and adjustments, persist the computed totals, and record a clear “last calculated” moment. If something changes, trigger recalculation in one place, not in every screen.

The mistakes behind most “why is this total wrong?” bugs are predictable: splitting calculation rules across UI and backend, allowing money edits after payments post without a clear record of change, updating totals without updating balance due, mixing line-level and invoice-level tax inconsistently, and rounding lines differently than the final total.

Status fields that paint you into a corner

Prototypes often mash “sent” and “paid” into one status, which breaks as soon as you have partial payments, failed payments, or refunds. Keep delivery state (draft/sent/void) separate from payment state (unpaid/partial/paid/overpaid).

Also, never delete payments to “undo” them. Record a reversal or refund so the history stays true.

Quick checklist before you ship

Before you ship, do one pass focused on money correctness, not UI polish.

• Store money as integer minor units (for example, cents), not floats. Keep the currency on the invoice and on every payment record so you never mix USD and EUR.
• Make invoices immutable once sent. If you must allow edits, create a new version (or a credit note) and keep snapshot fields (customer name, address, tax IDs, line item descriptions, prices) exactly as they were at send time.
• Model payments and refunds as separate records. A refund is not a negative payment hidden in the same table unless you’re explicit about reporting and “paid” behavior.
• Verify totals with a single source of truth. Stored invoice totals should match: sum(line totals) + taxes/fees - discounts, with rounding applied in one place.
• Define “paid” in writing and encode it. For example: an invoice is paid when (payments - refunds) is greater than or equal to the amount due, and it’s not voided. Decide how you handle overpayment.

After that, run a simple reconciliation report: for a date range, list each invoice’s amount due, total payments, total refunds, and remaining balance. If you can’t generate this cleanly from your model, something important is missing.

Next steps: validate, refactor, and get it production-ready

Once your tables and statuses look right on paper, prove your totals with test cases that match real behavior:

• Edit a line item after an invoice is sent (quantity change, price change, remove an item)
• Partial payment, then another partial payment, then a refund
• Apply a discount, then change tax rate, then remove the discount
• Void an invoice after a payment attempt
• Change customer details and confirm historical invoices don’t rewrite the past

Add one reconciliation check you can run anytime: compare invoice total (what you billed) vs payments net (sum of payments minus refunds). If those don’t match the expected balance, you know where to look.

If you inherited an AI-built invoicing app that “mostly works” but falls apart around totals, status changes, or refunds, teams often bring in FixMyMess (fixmymess.ai) for a quick codebase diagnosis and repair. A free audit can be enough to surface where totals are calculated, where state changes happen, and which fields are drifting so you can make it safe before production.