Storing Customer VAT IDs in PostgreSQL

Most teams get this wrong in the same way: they add a single vat_id TEXT column to the customers table, write whatever the user typed, and move on. It works until an auditor asks "what VAT status did this customer have on the day you reverse-charged invoice 4471?" — and the answer, stored in one mutable column that has been overwritten twice since, is gone.

Storing customer VAT IDs well is not hard, but it does require separating two things that look like one thing. This post is the schema I would actually ship: normalization rules that survive Greece and Northern Ireland, one mutable column for the current identifier, and an append-only log for the evidence. Scope here is EU-27 plus XI (Northern Ireland) — the identifiers VIES validates.

Two different things you're storing: the identifier vs the evidence

A customer's VAT ID is a current fact about the customer. It changes: they re-register, they move entity, they fix a typo. You want the latest value, and you want it to be mutable.

A validation check is a historical event. It happened at a moment, against a source, and it either produced a consultation number or it did not. You never want to change it after the fact — the whole point is that it records what was true when you relied on it.

Conflating these is the root mistake. If the VAT ID column and the "was it valid" column live on the same mutable row, then updating the customer's VAT ID silently invalidates the evidence behind every invoice you already issued. Split them: one table holds the current identifier, another holds the immutable trail of checks. This is the same separation as the caching vs storing — the difference split — Redis holds the fast current answer, PostgreSQL holds the durable evidence.

Normalizing before you store (uppercase, strip; EL not GR; XI)

EU VAT IDs are case-insensitive alphanumeric strings with a leading two-letter country prefix. de 123 456 789, DE123456789, and DE-123-456-789 are the same identifier. If you store them verbatim in your lookup column, WHERE vat_id = $1 becomes a coin flip.

The canonical form is: uppercase everything, strip all whitespace and punctuation, keep the alphanumerics. Do that once, before you persist and before you compare.

Two prefixes trip people up:

• Greece is EL, not GR. The VAT system uses EL for Greece deliberately — it predates the ISO-3166 GR code and VIES expects EL. Do not "helpfully" rewrite EL to GR; you will send GR123… to VIES and get nothing back. Leave it alone.
• Northern Ireland is XI. After Brexit, Northern Irish businesses use the XI prefix for intra-EU goods and are validatable through VIES. It is a real prefix, not a typo for IE — treat it as in-scope.

Resist the urge to enforce a fixed length or one universal regex. Length and composition vary by country — there is no single width and no one pattern that matches every member state. Normalize the shape (case, whitespace, punctuation, prefix), and leave format-correctness to the validator that actually knows each country's rules. Storing a malformed-looking string is fine; it is data. Rejecting a valid ID because your regex was too strict is a bug.

// Canonical storage form: uppercase, alphanumerics only.
// Does NOT validate country-specific length/format — that's the
// validator's job. Does NOT rewrite EL->GR. Preserves XI.
function normalizeVatId(raw: string): string {
  return raw.toUpperCase().replace(/[^A-Z0-9]/g, '')
}

// normalizeVatId('el 094 259 216') === 'EL094259216'
// normalizeVatId('XI 432 525 179') === 'XI432525179'
// normalizeVatId('de-123-456-789') === 'DE123456789'

The country prefix is just the first two characters of the normalized string. You can derive country_code from it rather than storing the user's separate country selection, which is one less field to keep in sync.

Store raw-as-entered AND normalized

Keep both forms. The normalized value is what you index and compare on. The raw value — exactly what the user typed — is provenance: it tells you what you were given, which matters when someone disputes "I never entered that number." Storage is cheap; the raw string costs you nothing and answers questions the normalized form cannot.

So the customer-facing column is really two columns: vat_id_raw (as entered) and vat_id (normalized, indexed). Everything downstream — lookups, joins, cache keys, VIES calls — uses the normalized one.

Schema part 1: the customer VAT column (mutable, current value)

This lives on (or next to) your customers table. It holds the current identifier and the latest known status, and it is allowed to change.

CREATE TABLE customer_vat (
  customer_id       UUID PRIMARY KEY REFERENCES customers (id),
  vat_id            TEXT,                    -- normalized: DE123456789
  vat_id_raw        TEXT,                    -- as entered: "de 123 456 789"
  country_code      TEXT,                    -- derived: left(vat_id, 2)
  -- Denormalized "latest known" status for fast reads. This is a
  -- convenience cache of the newest row in vat_checks, NOT the
  -- evidence. Never treat these as the audit record.
  last_valid        BOOLEAN,
  last_checked_at   TIMESTAMPTZ,
  last_check_id     UUID,                    -- FK into vat_checks
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX customer_vat_vat_id_idx
  ON customer_vat (vat_id)
  WHERE vat_id IS NOT NULL;

The last_* columns are a read-side convenience so a customer page does not have to scan the check log on every render. They are derived, disposable, and rebuildable from the log. The moment you catch yourself citing customer_vat.last_valid as proof of anything, stop — the proof lives in the next table.

The partial unique index enforces one customer per VAT ID (drop it if two customers legitimately share one, e.g. group entities — that is a domain decision, not a technical one).

Schema part 2: the append-only check log (immutable evidence rows)

Every validation call writes exactly one new row here. Nothing in this table is ever UPDATEd or DELETEd in normal operation. This is the audit trail.

CREATE TABLE vat_checks (
  check_id            UUID PRIMARY KEY,        -- the API's checkId
  customer_id         UUID REFERENCES customers (id),
  vat_id              TEXT NOT NULL,           -- normalized at time of check
  country_code        TEXT NOT NULL,
  valid               BOOLEAN NOT NULL,
  source              TEXT NOT NULL,           -- 'VIES' | 'CACHE' | national code
  consultation_number TEXT,                    -- VIES-only, NULL on fallback
  company_name        TEXT,
  company_address     TEXT,
  registry_code       TEXT,                    -- national registry id, when returned
  registry_code_name  TEXT,                    -- which registry it came from
  verified_at         TIMESTAMPTZ NOT NULL,    -- when the check was performed
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

The columns map straight onto the vatnode response fields — check_id ← checkId, verified_at ← verifiedAt, source, consultationNumber, companyName, and so on. Persist what the API gives you; do not re-derive it later.

Three rules make this table trustworthy:

1. Append only. A new check is a new row. Never overwrite a prior check when a customer re-validates — you would be destroying the record of what you knew at invoice time.
2. source is part of the evidence. VIES and a national-fallback source are not interchangeable. A VIES row carries a consultation number that VIES itself issued; a national-registry row is a yes/no from that country's authority with consultation_number NULL. Both are legitimate rows — but for audit weight they are different, so record which one answered and never flatten them together. (CACHE means vatnode served a recent VIES result from its own cache; the original VIES timestamp still travels with it.)
3. consultation_number is nullable and that is correct. It is VIES-only. A fallback row with a NULL consultation number is not a defect — it is an honest record that this particular check did not go through VIES. To understand exactly what that reference is and why it matters, extract the consultation number.

Storing a check row is not a tax determination. It records that you asked and what the answer was — whether a given supply is reverse-charged, exempt, or standard-rated is a separate decision your tax logic makes, using this evidence as one input.

Indexing for lookups and scheduled revalidation

Two access patterns dominate, and they want different indexes.

"What is the latest check for this VAT ID?" — the audit query and the cache-warm path.

CREATE INDEX vat_checks_vat_id_verified_at_idx
  ON vat_checks (vat_id, verified_at DESC);

This turns "most recent check for DE123456789 before the invoice date" into an index scan. Include source in your WHERE when you specifically need the most recent VIES check rather than any check.

"Which customers are due for revalidation?" — the batch job that re-checks stale IDs before renewal.

CREATE INDEX customer_vat_stale_idx
  ON customer_vat (last_checked_at ASC NULLS FIRST)
  WHERE vat_id IS NOT NULL;

NULLS FIRST puts never-checked customers at the front of the queue. This is the index the scheduled sweep reads when it selects the next batch to re-verify — see revalidate stored IDs in bulk for the job that consumes it.

What to persist for audit

The minimum evidence set, per check, is:

• the normalized VAT ID that was checked (not just a customer FK — the ID can change);
• the result (valid);
• a check identifier (check_id / checkId) so a specific check is quotable;
• when it ran (verified_at);
• which source answered (source);
• the consultation number where one was issued.

Everything else — company name, address, registry code — is useful context and worth keeping, but those six fields are the ones an auditor reconstructs a transaction from. The audit evidence, end to end guide walks the full chain from check to invoice.

The join you will actually run at audit time looks like this: given an invoice, find the VIES check that supported it.

-- The strongest check that supports a given invoice: the most recent
-- VIES-sourced validation for that customer's VAT ID, at or before
-- the invoice date.
SELECT c.check_id,
       c.vat_id,
       c.valid,
       c.source,
       c.consultation_number,
       c.verified_at
FROM vat_checks c
WHERE c.customer_id = $1
  AND c.source = 'VIES'
  AND c.verified_at <= $2          -- invoice issue date
ORDER BY c.verified_at DESC
LIMIT 1;

Filtering on source = 'VIES' here is deliberate: for reverse-charge evidence you want the row that carries a consultation number, not a fallback row that happens to be more recent. If you drop the filter, add consultation_number to your reviewer's export so they can weigh each row's source themselves.

What not to store / retention considerations

A few things do not belong in these tables, or do not belong forever:

• Do not store your VIES requester VAT ID per row if it never changes. It is a constant of your integration, not per-check data. Keep it in config; add a column only if you genuinely rotate requesters.
• Do not store raw SOAP/XML blobs as your primary evidence. They are bulky and awkward to query. The structured fields above are what audits actually use. Keep a raw blob only if you have a specific reason to, and keep it out of the hot path.
• Retention is a real decision, not a default. VAT record-keeping obligations in most member states run several years (commonly around 6–10, jurisdiction-dependent) — long enough that you should plan for it rather than let the table grow unbounded. This is a legal question for your jurisdiction, not something to guess at; confirm the period that applies to you before writing a deletion policy.
• Do not delete evidence rows to "clean up." If you must prune, prune the mutable convenience data and the operational cache, never the append-only log inside its retention window.

Example DDL

The whole thing in one place, ready to adapt:

-- Current identifier + latest-known status (mutable)
CREATE TABLE customer_vat (
  customer_id       UUID PRIMARY KEY REFERENCES customers (id),
  vat_id            TEXT,
  vat_id_raw        TEXT,
  country_code      TEXT,
  last_valid        BOOLEAN,
  last_checked_at   TIMESTAMPTZ,
  last_check_id     UUID,
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX customer_vat_vat_id_idx
  ON customer_vat (vat_id)
  WHERE vat_id IS NOT NULL;

CREATE INDEX customer_vat_stale_idx
  ON customer_vat (last_checked_at ASC NULLS FIRST)
  WHERE vat_id IS NOT NULL;

-- Append-only evidence log (immutable)
CREATE TABLE vat_checks (
  check_id            UUID PRIMARY KEY,
  customer_id         UUID REFERENCES customers (id),
  vat_id              TEXT NOT NULL,
  country_code        TEXT NOT NULL,
  valid               BOOLEAN NOT NULL,
  source              TEXT NOT NULL,
  consultation_number TEXT,
  company_name        TEXT,
  company_address     TEXT,
  registry_code       TEXT,
  registry_code_name  TEXT,
  verified_at         TIMESTAMPTZ NOT NULL,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX vat_checks_vat_id_verified_at_idx
  ON vat_checks (vat_id, verified_at DESC);

Writing a check is then: INSERT a new vat_checks row from the API response, and UPDATE customer_vat to point last_* at it. Two statements, one direction — the log is never rewritten.

The response you insert from comes straight off the VAT validation API — the API reference lists every field you can persist:

const res = await fetch(
  `https://api.vatnode.dev/v1/vat/${encodeURIComponent(normalizeVatId(raw))}`,
  { headers: { Authorization: `Bearer ${process.env.VATNODE_API_KEY}` } }
)
const data = await res.json()
// { valid, vatId, countryCode, countryName, companyName, companyAddress,
//   source, consultationNumber, checkId, verifiedAt, ... }

// INSERT into vat_checks (check_id, vat_id, valid, source,
//   consultation_number, verified_at, ...) VALUES (data.checkId, ...)

The official VIES service on Your Europe is the reference for what VIES itself validates and returns.

FAQ

Should I store the VAT ID as entered or normalized?

Store both. Keep the raw value as entered for provenance, and a normalized form — uppercased, spaces and punctuation stripped — for lookups and comparisons.

How do I handle Greece and Northern Ireland?

Use the VIES prefixes: Greece is EL, not GR, and Northern Ireland is XI. Normalize consistently and do not "correct" EL to GR — EL is deliberate in the VAT system.

What columns do I need for audit evidence?

At minimum the VAT ID, the result, a check identifier, when it ran, which source answered, and the consultation number where one was issued. Make these rows append-only — never overwrite a prior check.