Closing the Tax Gap with a Data Model: Invoice Traceability in SQL

The U.S. tax gap, the discrepancy between taxes owed and taxes paid, amounts to approximately $696 billion annually. This issue largely stems from underreported business income. From a policy standpoint, this represents an enforcement problem; however, for data professionals, it manifests as a join that fails to connect—invoice issuance, payment receipt, and tax reporting.

Each of these elements resides within its own system, resulting in discrepancies that only become apparent during audits, which typically require manual reconstruction of the fiscal year.

The proposed solution is straightforward yet powerful: assign every invoice a unique identifier, establish links between invoices, payments, and reports, and then conduct three key queries to monitor the data. The core concept is to assign each invoice a Unique Transaction Identifier (UTI) upon creation. Following this, three types of checks can be performed on the data: a three-way match, completeness verification, and integrity assessment.

The three-way match involves comparing the invoiced amount, the amount paid, and the amount reported. If these figures do not align, it indicates an exception that requires human review. The completeness check identifies gaps in the invoice sequence for each issuer, which may suggest invoices that were issued but not recorded. The integrity check examines the audit trail to ensure that any modifications to the data have been detected.

Countries that mandate electronic invoicing, such as Brazil's NF-e system and the European Union's ViDA rules, already implement some form of this approach on a national scale. The advantage of this method does not necessitate regulatory mandates; it can be implemented within individual businesses. To facilitate this, a normalized data model keyed on the UTI is sufficient.

A notable aspect of the design is the payment_allocation table, which addresses the many-to-many relationship between payments and invoices—where a single payment can settle multiple invoices, and multiple payments can settle the same invoice.

The necessary SQL code to implement this system includes the following tables: invoice, payment_allocation, reporting_record, and audit_event. The invoice table serves as the primary entity, serving as the anchor for linking all other data. The payment_allocation table reconciles the relationship between payments and invoices, while the reporting_record table captures the actual reporting of invoice amounts.

The audit_event table establishes an append-only, hash-chained record of every change made to the data, effectively creating a tamper-evident trail. This structure enables the systematic verification of the data through three key queries:

1. The three-way match query aggregates invoiced, paid, and reported amounts for each invoice. It categorizes any discrepancies as either unpaid, unreported, or overreported, directing human review to resolve these exceptions.

2. The completeness check identifies any missing invoices by analyzing the sequence of issued invoices for each issuer. A gap in the sequence signals an invoice that exists but was not recorded within the system.

3. The integrity check reviews the audit trail to ensure that any alterations to the data are flagged, thereby preventing unauthorized modifications.

A practical example illustrates the effectiveness of this system. Consider two invoices in a given quarter: INV-1001, with an invoiced amount of $4,000, fully paid but only $2,500 reported due to a miscalculation in the second deposit; and INV-1002, with a complete match between invoiced, paid, and reported amounts. The three-way match query would flag INV-1001 as an exception for underreported income ($1,500) that would otherwise go unnoticed in a lump-sum 1099 filing.

Conversely, INV-1002 would be classified as 'matched,' indicating proper reporting. This process enables businesses to rectify discrepancies before formal filings, significantly reducing the likelihood of undetected underreporting.

This approach is particularly valuable because it leverages existing data and tools, requiring no legislative changes and can be adopted incrementally by individual companies. While it does not address deliberate fraud or cash-only transactions, which remain significant compliance challenges, it effectively mitigates the majority of inadvertent underreporting by small businesses. In essence, the missing join between invoices, payments, and reports is a common, solvable problem that this data model addresses.