The U.S. tax gap—the difference between taxes owed and taxes paid on time—runs about $696 billion a year, and most of it is underreported business income. To a policy analyst that is an enforcement problem. To anyone who works with data, it looks like something more familiar: a join that never happens.
There is no common key connecting the invoice a business issues, the payment it receives, and the amount it eventually reports. Each lives in its own silo—invoicing software, the bank feed, the tax workpapers—so when the three numbers disagree, nobody sees it until an audit reconstructs the year by hand.
This post sketches a small, boring, and surprisingly powerful fix: give every invoice one stable identifier, link everything to it, and let three queries do the watching.
The idea in one line
Assign each invoice a Unique Transaction Identifier (UTI) at issuance, then run three deterministic checks over the data:
Three-way match — does invoiced = paid = reported?
Completeness — are there gaps in the per-issuer invoice sequence?
Integrity — has the audit trail been altered?
Countries with mandatory e-invoicing (Brazil's NF-e, the EU's incoming ViDA rules) already do a version of this at national scale. You don't need a government mandate to get the benefit inside a single business.
The schema (condensed)
A normalized model, keyed on invoice_uti. The one non-obvious piece is payment_allocation, which resolves the many-to-many between payments and invoices—one payment can settle several invoices, and one invoice can be settled by several payments.
sql
CREATE TABLE invoice (
invoice_uti VARCHAR(64) PRIMARY KEY,
issuer_id VARCHAR(40) NOT NULL,
issuer_sequence BIGINT NOT NULL, -- per-issuer counter, enables gap detection
gross_amount NUMERIC(15,2) NOT NULL CHECK (gross_amount >= 0),
status VARCHAR(20) NOT NULL DEFAULT 'issued',
UNIQUE (issuer_id, issuer_sequence)
);
CREATE TABLE payment_allocation ( -- bridge: one row per (payment, invoice)
allocation_id VARCHAR(40) PRIMARY KEY,
payment_id VARCHAR(40) NOT NULL,
invoice_uti VARCHAR(64) NOT NULL REFERENCES invoice(invoice_uti),
allocated_amount NUMERIC(15,2) NOT NULL CHECK (allocated_amount > 0)
);
CREATE TABLE reporting_record ( -- what actually reached a return / 1099
report_id VARCHAR(40) PRIMARY KEY,
invoice_uti VARCHAR(64) NOT NULL REFERENCES invoice(invoice_uti),
reported_amount NUMERIC(15,2) NOT NULL CHECK (reported_amount >= 0)
);
CREATE TABLE audit_event ( -- append-only, hash-chained trail
event_id VARCHAR(40) PRIMARY KEY,
invoice_uti VARCHAR(64) NOT NULL REFERENCES invoice(invoice_uti),
prev_hash CHAR(64),
record_hash CHAR(64) NOT NULL UNIQUE,
FOREIGN KEY (prev_hash) REFERENCES audit_event(record_hash)
);
Control 1 — the three-way match
This is the whole point. Aggregate invoiced, paid, and reported amounts per invoice and classify the result. Anything that isn't matched is an exception a human reviews.
sql
CREATE VIEW v_three_way_match AS
SELECT
i.invoice_uti,
i.gross_amount AS invoiced,
COALESCE(p.paid, 0) AS paid,
COALESCE(r.reported, 0) AS reported,
CASE
WHEN COALESCE(p.paid,0) + 0.01 < i.gross_amount THEN 'exception_unpaid'
WHEN COALESCE(r.reported,0) + 0.01 < i.gross_amount THEN 'exception_unreported'
WHEN COALESCE(r.reported,0) > i.gross_amount + 0.01 THEN 'exception_overreported'
ELSE 'matched'
END AS status
FROM invoice i
LEFT JOIN (SELECT invoice_uti, SUM(allocated_amount) paid
FROM payment_allocation GROUP BY invoice_uti) p ON p.invoice_uti = i.invoice_uti
LEFT JOIN (SELECT invoice_uti, SUM(reported_amount) reported
FROM reporting_record GROUP BY invoice_uti) r ON r.invoice_uti = i.invoice_uti
WHERE i.status <> 'cancelled';
Control 2 — completeness (gap detection)
Because identifiers are issued in sequence per issuer, a break in the sequence means an invoice that exists but never made it into the books.
sql
CREATE VIEW v_sequence_gaps AS
SELECT issuer_id, issuer_sequence AS prev, next_seq,
next_seq - issuer_sequence - 1 AS missing
FROM (
SELECT issuer_id, issuer_sequence,
LEAD(issuer_sequence) OVER (PARTITION BY issuer_id ORDER BY issuer_sequence) AS next_seq
FROM invoice
) s
WHERE next_seq - issuer_sequence > 1;
Control 3 — a tamper-evident trail
Every state change is written as a new row whose prev_hash points at the previous row's record_hash. Nothing is ever overwritten, so altering history breaks the chain—and a single query finds the break.
Worked example
Two invoices in a quarter:
INV-1001: invoiced $4,000, paid $4,000, but only $2,500 reported (a second deposit was booked to the wrong account).
INV-1002: invoiced $1,800, paid $1,800, reported $1,800.
v_three_way_match returns matched for INV-1002 and exception_unreported for INV-1001, flagging exactly $1,500 of income that a lump-sum 1099 match would never catch. The practitioner fixes one flagged row before filing instead of discovering it in an audit.
Why this is worth building
The data to catch most inadvertent underreporting already exists—it just isn't linked. A UTI plus three queries turns a pile of disconnected records into something continuously checkable. It sits on top of the tools a business already runs, needs no change in the law, and can be adopted one company at a time.
It won't stop deliberate fraud or pure-cash activity—those are the hard cases, and no voluntary tool reaches them. But the large, ordinary, mostly-accidental underreporting of small-business income? That's just a missing join. And missing joins are the kind of problem we know how to fix.
I'm an accountant (Contador, CRC-SC IN BRAZIL) and an AI master's student writing about where tax compliance meets data and code. Feedback and war stories welcome in the comments.
Top comments (0)