Invoice Data as a Domain
Why an invoice import isn’t a CSV job but a domain with its own terms, invariants and time rules – and how explicit types and a rejection report turn silent data errors into explainable data.
Invoice Data as a Domain
From a domain perspective, invoice data isn’t columns of numbers. An invoice is a document of record: it has an issuer, a recipient, a document number, line items and tax rates – and a legal meaning that the German VAT Act describes quite precisely. A document has relatives: the credit note, which gives an amount back, and the cancellation, which voids a document and must reference the original. And a document carries rules that appear in no export file: the sum of the line items must match the document total, net plus tax must equal gross, and a payment term counts from the invoice date – not from the date of supply, and certainly not from the day the money actually moves.
If you read an invoice only as a row with an amount, you read it wrong. But that’s exactly what happens when you treat the task of “merging invoice data” as what it looks like at first glance: an import job.
A private data project as the trigger#
The occasion was unspectacular. Over several years, invoice and receipt data had piled up for me in three forms: PDFs from suppliers, CSV exports from an accounting tool and an account export from the bank. I wanted to merge them into a local database so I could finally answer questions none of the individual systems answers: which amounts were invoiced but never paid? How are expenses distributed across tax rates? Which credit notes belong to which originals? Everything anonymized and purely private – this is about the structure of the problem, not about concrete business figures.
The first version was an evening’s work: convert PDFs to text, read CSVs with Papa Parse, rename columns, into SQLite. The import ran through without a single error message. And that was exactly the problem.
Because the data was silently broken. One supplier exports gross amounts, the other net – both call the column amount. One export writes German decimal commas, the next uses periods. In one file the date column held the date of supply, in the other the invoice date. Two cancellations showed up as perfectly normal invoices with a negative amount, with no link to the original. None of this is a parser error. Every single row was syntactically flawless. What was wrong was the meaning – and no CSV parser checks meaning.
At this point I realized I had filed the task in the wrong drawer. This wasn’t data transport. This was domain modeling, just for data that already exists.
Taking the terms seriously#
The first step out of silent breakage wasn’t code but a list of terms. Which domain things actually appear in these files? In the end, surprisingly few terms ended up on the piece of paper, but each with a sharp meaning:
- Document – the document as a whole, with a unique document number and a document type
- Line item – a row on the document, with quantity, unit price and its own tax rate
- Tax rate – in my data 0, 7 or 19 percent, nothing else
- Credit note – a separate document with the sign reversed
- Cancellation – voids a document and references its document number
- Payment term – a date derived from the invoice date plus a period
This list looks trivial. But it immediately forced decisions that had previously been hidden invisibly in the column mapping. Is a negative amount a credit note or a cancellation? From a domain perspective, a huge difference: the credit note is a new transaction, the cancellation invalidates an old one. In my raw data, both were the same row with a minus sign. Without the list of terms, I would never have recognized this as a problem – after all, the number is right.
The relationships between the document types fit into a small drawing:
flowchart LR I["Invoice<br/>INV-2023-0117"] --> P1["Line item 1<br/>19 %"] I --> P2["Line item 2<br/>7 %"] C["Cancellation<br/>CAN-2023-0119"] -- "references" --> I G["Credit note<br/>CRN-2023-0121"] -- "refers to" --> I
In this picture, a cancellation without a reference simply can’t be drawn. That’s exactly how it should be: what can’t be drawn shouldn’t be storable either.
Invariants: what must always hold#
Terms alone aren’t enough. The real substance of a domain lies in its invariants – the statements that must hold for every valid record, no matter where it comes from. For invoice data, there are three main ones:
First: totals must add up. The line item totals per tax rate, the tax on top, the whole thing added up – that must equal the gross amount of the document, to the cent. Rounding happens per tax rate group, not per line item, otherwise it drifts.
Second: a cancellation references an original. A cancellation without a reference isn’t a cancellation but a data error with a minus sign.
Third – the classic: gross and net must never be confusable. A field that sometimes contains one and sometimes the other is worse than no field at all. With a 19 percent difference, the error doesn’t even show up right away in total analyses; it spreads like an even fog over all the numbers.
With TypeScript and Zod, all of this can be made explicit. I always store amounts as integer cent values – floating point and money don’t get along:
import { z } from "zod";
const TaxRate = z.union([z.literal(0), z.literal(7), z.literal(19)]);
const InvoiceLine = z.object({
description: z.string().min(1),
quantity: z.number().positive(),
unitPriceNetCents: z.number().int(), // money as integer cents, never floats
taxRate: TaxRate,
});
const Invoice = z.object({
invoiceNumber: z.string().regex(/^(INV|CRN|CAN)-\d{4}-\d{4}$/),
kind: z.enum(["invoice", "credit_note", "cancellation"]),
referencesInvoice: z.string().optional(),
issueDate: z.string().date(), // invoice date
serviceDate: z.string().date(), // date of supply
dueDate: z.string().date(), // payment target
lines: z.array(InvoiceLine).min(1),
totalGrossCents: z.number().int(),
});
That’s just the structure. The invariants come on top as a refinement, so that a violation takes the same path as a structural error:
const ValidInvoice = Invoice.superRefine((inv, ctx) => {
// sum per tax rate group, round per group, then add up
const grossByRate = new Map<number, number>();
for (const line of inv.lines) {
const net = line.quantity * line.unitPriceNetCents;
const gross = Math.round(net * (1 + line.taxRate / 100));
grossByRate.set(line.taxRate, (grossByRate.get(line.taxRate) ?? 0) + gross);
}
const computedGross = [...grossByRate.values()].reduce((a, b) => a + b, 0);
if (computedGross !== inv.totalGrossCents) {
ctx.addIssue({
code: z.ZodIssueCode.custom,
message: `gross mismatch: lines say ${computedGross}, document says ${inv.totalGrossCents}`,
});
}
if (inv.kind === "cancellation" && !inv.referencesInvoice) {
ctx.addIssue({
code: z.ZodIssueCode.custom,
message: "cancellation must reference an original invoice",
});
}
});
The difference from the first version of the project is fundamental. Before, the import accepted whatever could be parsed. Now it accepts what is correct from a domain perspective. That’s the same shift you know from Domain-Driven Design, just at the most inconspicuous level there is: reading files.
Three dates, three meanings#
The time rules deserve their own section, because they’re where analyses go wrong most quietly. A document carries up to three dates, and none of them is “the date”:
The date of supply says when something was delivered or performed. The invoice date says when the document was issued. The payment date – which isn’t on the document at all but comes from the account export – says when money moved. Weeks can pass between supply and invoice, and again between invoice and payment. An analysis of “expenses per month” delivers three different truths depending on the chosen date, and each of the three is the right one for some question: the date of supply belongs to the question “When was the cost incurred?”, the invoice date to VAT, the payment date to liquidity.
In my raw data, each source had exactly one date column, and no two sources meant the same thing by it. Since the three dates became separate, mandatory fields, every analysis has to say which one it means – the question can no longer be skipped by accident. The payment term is then just derived information: invoice date plus period gives dueDate, and a comparison with the payment date shows what was overdue.
The boundary: reject instead of waving through#
That leaves the question of what happens to rows that fail the check. The usual answer is a log line, and the usual consequence is that nobody reads it. For an import that runs once a month, that’s equivalent to silent data loss – except that this time the bad data stays out instead of in, which is better, but still invisible.
The answer that works for me: rejected rows are a result of the run, on equal footing with the accepted ones. The import produces two artifacts – the database and a rejection report as a Markdown file, with source, row number and the domain reason:
type RejectedRow = {
source: string; // e.g. "supplier-a-2023.csv"
rowNumber: number;
reason: string; // human-readable, domain language
};
const rejectedRows: RejectedRow[] = [];
for (const [rowNumber, candidate] of candidates.entries()) {
const result = ValidInvoice.safeParse(candidate);
if (!result.success) {
rejectedRows.push({
source: candidate.source,
rowNumber,
reason: result.error.issues.map((i) => i.message).join("; "),
});
continue;
}
accepted.push(result.data);
}
// afterwards: write rejected-rows report next to the database, fail if quota exceeded
The report is deliberately a file and not a log stream. Files can be diffed, archived, compared with the previous month next month. If the import runs twice over the same inputs, it must produce exactly the same report – I explained in more detail why I put so much weight on this property in Deterministic CLIs. And for values that come from PDFs, heightened distrust applies anyway: what text recognition delivers is a measurement with uncertainty, not a fact – more on that in OCR with a Cache and Distrust.
The complete flow then looks like this:
flowchart LR
A["PDF documents"] --> N["Normalize<br/>commas, encoding, columns"]
B["CSV exports<br/>accounting"] --> N
K["Account export<br/>bank"] --> N
N --> V{"Domain validation<br/>types + invariants"}
V -- "valid" --> DB[("SQLite<br/>documents + line items")]
V -- "rejected" --> R["Rejection report<br/>Markdown, diffable"]What matters in this picture is the one diamond. There is exactly one place where raw data becomes domain data. Behind this boundary, any code may trust the fields: totalGrossCents is gross, in cents, and the totals add up. Before the boundary, everything is suspect. This asymmetry is what makes the rest of the project so pleasant – the analyses no longer contain a single defensive line, no “in case the field is empty after all,” no second recalculation.
Analyzing once the data is clean#
For the analyses, I put DuckDB directly on top of the database. Once the domain is right, the interesting questions become surprisingly short SQL statements. Open items, for example – documents whose payment term has passed without a payment date existing:
SELECT i.invoice_number,
i.due_date,
i.total_gross_cents / 100.0 AS gross_eur
FROM invoices i
LEFT JOIN payments p ON p.invoice_number = i.invoice_number
WHERE i.kind = 'invoice'
AND p.paid_date IS NULL
AND i.due_date < current_date
ORDER BY i.due_date;
This query owes its correctness entirely to the boundary before it. With the raw data of the first version, the same query would have delivered plausible and wrong numbers – cancellations counted as open items, gross and net mixed, the date of supply interpreted as the due date. None of it would have stood out, because the result looks like money and amounts of money always seem somehow credible.
A side effect I hadn’t expected: the rejection report became the best tool for understanding the sources themselves. If rejections from one file pile up with the same reason, that isn’t noise but an insight about the exporter – for example, that a tool sets the sign differently for credit notes than for invoices. Patterns like that used to appear in no log; now they’re collected in a file I actually read.
And e-invoicing?#
You could object that the problem will solve itself: with the Growth Opportunities Act, Germany decided in March 2024 that electronic invoicing in German B2B business becomes mandatory step by step from 2025 – structured formats according to EN 16931 instead of PDF prose. That’s good news, because exactly the fields this was about are formally defined there: document type, tax rates per line item, date of supply and invoice date.
But a structured format only shifts the work; it doesn’t eliminate it. Whether an XML field or a CSV column claims to be a gross amount – the receiving side still has to check that the totals add up and that the cancellation finds its original. And the historical data of past years stays what it is anyway: PDFs and exports with quirks. So the domain boundary doesn’t become superfluous; it just gets better input material in the future.
What I take away from it#
The reflex to treat invoice data as a transport problem is understandable – the files are small, the formats known, the tools ready. But the difficulty isn’t in the transport. It lies in the fact that this data has a domain language, invariants no parser knows, and three time axes that can’t be squeezed into one date column. If you ignore that, you get an import that runs green and still produces wrong numbers. If you model it, you get something rarer than correct data: explainable data. Every number in the database has an origin, every missing row a documented reason.
All in all, this small private project said more about software architecture than many a service split: you don’t recognize a domain by its size, but by the fact that it has its own terms, its own rules and its own time. Invoice data has all three.
Further reading#
- Zod on GitHub – schema validation for TypeScript
- Zod documentation – reference for refinements and error handling
- DuckDB documentation – local analyses directly on files and databases
- § 14 UStG – mandatory information on an invoice under German VAT law
- Papa Parse – CSV parsing in the browser and in Node.js
Revised and translated from German with the help of AI.