Storing Customer VAT IDs in PostgreSQL

When handling Customer VAT IDs in PostgreSQL, teams often make a critical mistake by treating the VAT ID as just another mutable field in a single column. This approach fails when auditors question the VAT status of a customer for specific invoices, as the information becomes unreliable due to multiple updates over time.

To store VAT IDs correctly, you need to separate the current identifier from the historical evidence. Store the current VAT ID in a mutable column and an append-only log for the validation evidence. This ensures the mutable column always holds the latest value, while the immutable log records the validation checks.

The VAT ID itself is a current fact about the customer that changes over time, such as re-registration or entity movement. Validation checks, on the other hand, are historical events that happened at specific moments and should never be altered after the fact. Conflating these two aspects leads to incorrect results when updating a customer's VAT ID.

To normalize VAT IDs, convert them to uppercase, strip whitespace and punctuation, and keep only alphanumeric characters. Greece uses the prefix "EL" and Northern Ireland uses "XI" for intra-EU transactions. Do not attempt to standardize these prefixes, as VIES validates them as-is.

When storing VAT IDs, keep both the raw-entered value and the normalized, indexed version. The normalized value is what you use for indexing and comparisons, while the raw value serves as provenance, showing exactly what was given by the user. This separation allows you to maintain both current and historical information about VAT IDs without sacrificing data integrity.