Storing Customer VAT IDs in MongoDB and NoSQL Schemas

The relational answer to ‘how do I store VAT check evidence’ is two tables and a foreign key. MongoDB gives you the same two-collection shape, but three things that PostgreSQL gets for free – atomicity across the split, a hard row-immutability grant, and a size-agnostic table – are all decisions you have to make yourself in a document database. The rest is only about where the NoSQL paradigm actually changes the schema, not the parts that are identical everywhere.

Two things you store: the identifier vs the evidence

Same split as in any database: a customer’s current VAT ID is a mutable fact that changes when they re-register or fix a typo, and a validation check is an immutable historical event that happened at a moment against a source. Conflating them means updating the VAT ID silently invalidates the evidence behind invoices you already issued – see storing VAT IDs in PostgreSQL for the full rationale behind splitting current-value from evidence, and for the normalization rules (uppercase, strip punctuation, EL not GR, XI in scope) that apply identically in MongoDB.

Embed vs a separate collection

The document-database-native instinct is to embed the check history as an array on the customer document – one document, one round trip, no joins. Don’t. A VAT check log grows without bound: every renewal, every scheduled revalidation, every reverse-charge invoice adds a row, for the lifetime of the customer relationship. MongoDB caps a single BSON document – including every nested array and subdocument – at 16MB. An embedded vatChecks array will eventually hit that ceiling, and it will do it silently until an insertOne or updateOne starts failing with a document-too-large error in production.

Use a separate vatChecks collection, referenced by customerId:

// customers collection — current identifier + denormalized latest status
{
  _id: ObjectId('...'),
  vatId: 'DE123456789',       // normalized
  vatIdRaw: 'de 123 456 789', // as entered, for provenance
  countryCode: 'DE',
  lastValid: true,
  lastCheckedAt: ISODate('2026-09-10T08:12:00Z'),
  lastCheckId: '019d2a89-a5d9-7b97-b710-57b84604de2b',
  updatedAt: ISODate('2026-09-10T08:12:00Z'),
}

// vatChecks collection — one document per check, append-only
{
  _id: ObjectId('...'),
  checkId: '019d2a89-a5d9-7b97-b710-57b84604de2b', // the API's checkId
  customerId: ObjectId('...'),
  vatId: 'DE123456789',
  countryCode: 'DE',
  valid: true,
  source: 'VIES',             // 'VIES' | 'CACHE' | national fallback code
  consultationNumber: 'WAPPKS8090...',
  companyName: 'Example GmbH',
  companyAddress: 'Musterstraße 1, 10115 Berlin',
  verifiedAt: ISODate('2026-09-10T08:12:00Z'),
  createdAt: ISODate('2026-09-10T08:12:01Z'),
}

This is not just taste. It’s the same reasoning that pushes large blobs and unbounded arrays into GridFS or a child collection in every MongoDB schema – the document-size ceiling makes ‘keep growing an array forever’ structurally wrong for a table that is, by design, append-only forever.

No cross-collection atomicity by default

This is the sharpest paradigm difference from the relational version. In PostgreSQL, writing a new check row and updating the customer’s last_valid pointer is ‘two statements, one direction’ inside a transaction – trivial, because the database gives you atomicity across tables for free. In MongoDB, a write to vatChecks and a write to customers are two separate operations against two separate collections, and by default nothing ties them together. If your process crashes between them, you get a check row with no updated pointer, or – much worse if you write in the other order – a pointer that claims a status you never actually recorded.

MongoDB does have multi-document transactions: available since 4.0 on a replica set, and since 4.2 across a sharded cluster. They’re real ACID transactions and they would solve this cleanly. They also cost you session overhead, retry-on-transient-error handling, and a set of operational edge cases most single-region deployments don’t need to take on for a two-write sequence where one side is a cache.

The practical pattern that doesn’t need transactions:

// 1. Write the check first — this IS the evidence. If nothing else
//    happens, you still have a correct, queryable audit trail.
await db.collection('vatChecks').insertOne({
  checkId: data.checkId,
  customerId,
  vatId: data.vatId,
  countryCode: data.countryCode,
  valid: data.valid,
  source: data.source,
  consultationNumber: data.consultationNumber,
  companyName: data.companyName,
  companyAddress: data.companyAddress,
  verifiedAt: new Date(data.verifiedAt),
  createdAt: new Date(),
})

// 2. Best-effort denormalized pointer update. If this fails or is
//    never reached, the customer doc is briefly stale — not wrong,
//    just behind. It's rebuildable from vatChecks at any time.
await db.collection('customers').updateOne(
  { _id: customerId },
  {
    $set: {
      lastValid: data.valid,
      lastCheckedAt: new Date(data.verifiedAt),
      lastCheckId: data.checkId,
      updatedAt: new Date(),
    },
  }
)

Write the check row first because it’s the source of truth; the customer doc’s last* fields are a read-side cache of the newest row, nothing more. If step 2 fails, a scheduled job can rebuild every stale pointer with a single aggregation – find the most recent vatChecks document per customerId and $set it back onto customers. That’s the entire recovery story, and it doesn’t need a transaction to exist. Reach for a real multi-document transaction only if your write volume or consistency requirements genuinely can’t tolerate a briefly-stale pointer – for most integrations, ‘rebuildable from the log’ is enough, because the log is what an audit actually reads.

Validating documents: $jsonSchema

MongoDB has no NOT NULL or CHECK constraint at the column level, but collection-level $jsonSchema validators are the closest analog – enforced on every insert and update, at the database.

db.createCollection('vatChecks', {
  validator: {
    $jsonSchema: {
      bsonType: 'object',
      required: ['checkId', 'customerId', 'vatId', 'valid', 'source', 'verifiedAt'],
      properties: {
        checkId: { bsonType: 'string' },
        customerId: { bsonType: 'objectId' },
        vatId: { bsonType: 'string' },
        valid: { bsonType: 'bool' },
        source: { bsonType: 'string' }, // 'VIES' | 'CACHE' | a national-fallback code like 'MF_PL', 'ANAF_RO' or 'ARES_CZ' — see the API's source field
        consultationNumber: { bsonType: ['string', 'null'] }, // VIES-only, nullable
        verifiedAt: { bsonType: 'date' },
      },
    },
  },
  validationLevel: 'strict',
})

consultationNumber stays nullable by design: it’s VIES-only and NULL on any national-fallback row. Note it’s also nullable on a subset of VIES itself – a consultation number is only issued when the VIES call succeeds and you have a requester VAT ID configured in your vatnode dashboard Account details, so a live VIES success doesn’t by itself guarantee one. Don’t tighten the schema to require it.

Immutability has no database-level equivalent

PostgreSQL can enforce append-only at the database layer – revoke the UPDATE and DELETE grants on the table’s role and the guarantee holds even against a compromised or buggy application. MongoDB doesn’t have that lever in the same way; role-based access control governs collections, not row-level mutability, and there is no built-in ‘insert-only’ table mode.

So this has to be enforced at the application and role layer instead:

• Only ever call insertOne on vatChecks from application code – never updateOne, replaceOne, or deleteOne against it.
• Create a dedicated MongoDB role for the write path that has insert but not update or remove on that collection, and use it for nothing else.
• Treat any code review that touches this collection with the same scrutiny you’d give a migration that drops a constraint.

Be honest about this one: it’s a weaker guarantee than a relational grant revocation gives you, because it depends on discipline and access control staying correct rather than the database refusing the operation outright. If append-only evidence is a hard compliance requirement, budget for periodically auditing who holds write roles on this collection.

Indexing for the audit join and current-VAT-ID uniqueness

The query you actually run at audit time – ‘what was the most recent VIES check for this VAT ID, at or before the invoice date’ – wants a compound index:

db.vatChecks.createIndex({ vatId: 1, verifiedAt: -1 })

db.vatChecks
  .find({ vatId: 'DE123456789', source: 'VIES', verifiedAt: { $lte: invoiceDate } })
  .sort({ verifiedAt: -1 })
  .limit(1)

For ‘one current VAT ID per customer’ on the customers collection, partialFilterExpression is Mongo’s analog to a partial unique index:

db.customers.createIndex(
  { vatId: 1 },
  { unique: true, partialFilterExpression: { vatId: { $type: 'string' } } }
)

That excludes customers with no VAT ID yet from the uniqueness check, same as WHERE vat_id IS NOT NULL does on the PostgreSQL side.

TTL indexes are a trap on evidence

This is the NoSQL-native failure mode – there’s no relational equivalent to warn you about it. A TTL index on vatChecks looks like a tidy way to keep the collection from growing forever:

// DO NOT do this on the evidence collection.
db.vatChecks.createIndex({ verifiedAt: 1 }, { expireAfterSeconds: 63072000 }) // ~2 years

MongoDB’s TTL monitor will quietly delete every document past that window, on a background sweep, with no audit log of what it removed. VAT record-keeping obligations commonly run several years – often somewhere in the 6–10 year range, but this varies by jurisdiction, so confirm the period that actually applies to you rather than guessing. A TTL index set shorter than that obligation destroys the exact evidence you’d need to produce on request, and it does it silently, months or years before anyone notices.

If you need to prune something, prune denormalized convenience data – stale lastCheckedAt pointers, cached read-model fields – never the append-only log itself, not inside its retention window.

Date type vs ISO string for verifiedAt

Store verifiedAt as a BSON Date, not as an ISO-8601 string. It’s tempting to persist the API response’s verifiedAt field verbatim as the string it arrives as, but the audit-join query above is a range query – $lte: invoiceDate – and string comparison on dates only produces correct range results if every string is the same length, same timezone offset, and zero-padded identically. One value that drops the timezone suffix or omits milliseconds breaks the range silently. Cast on write:

verifiedAt: new Date(data.verifiedAt)

and index and query on the Date field, not a string copy of it.

Read preference: don’t audit off a secondary

If your MongoDB deployment reads from secondaries for latency or load reasons, be deliberate about which reads that applies to. A secondary can lag the primary by anywhere from milliseconds to much longer under replication pressure, which means a read of customers.lastValid or lastCheckedAt from a secondary can return a status that’s already been superseded by a check the primary has but the secondary hasn’t replicated yet.

That’s an acceptable trade-off for a dashboard badge. It is not acceptable for the query that produces audit evidence. Read the audit path from the primary, or explicitly request readConcern: 'majority', so the check row you cite as evidence is guaranteed durable and current, not a snapshot that might still roll back.

db.vatChecks.find({ customerId, source: 'VIES' }).readConcern('majority')

Getting the country code right in a document you store verbatim

A NoSQL schema tends to embed the API response close to as-is, which makes one shortcut especially tempting: deriving countryCode yourself from the VAT ID’s first two characters instead of trusting the field the API already returns.

Beyond MongoDB: key-value and document stores

The two-collection shape holds up outside MongoDB too, expressed through whatever the store’s native indexing primitive is.

DynamoDB, single-table design: PK = CUSTOMER#<customerId>, SK = CHECK#<verifiedAt>#<checkId> for check rows, and a PK = CUSTOMER#<customerId>, SK = PROFILE item for the current-identifier record. The sort key’s verifiedAt prefix gives you time-sorted audit reads for free via a Query on the partition, no secondary index needed for the common ‘most recent checks for this customer’ access pattern.

Firestore, subcollections: customers/{customerId} holds the current identifier and denormalized status; customers/{customerId}/vatChecks/{checkId} holds each immutable check as its own document. Security rules can enforce create-only access on the subcollection in a way that’s closer to a database-level guarantee than MongoDB’s role-based approach – worth it if Firestore is already your stack.

Same principles apply regardless of engine: separate the mutable pointer from the immutable log, don’t let TTL or retention policies touch the log inside its retention window, and don’t trust a secondary/eventually-consistent read for anything you’d cite as evidence.

Fetching the data you store

The response you insert from comes straight off the EU VAT validation API – the API reference documents every field in the response:

const res = await fetch(`https://api.vatnode.dev/v1/vat/${encodeURIComponent(vatId)}`, {
  headers: { Authorization: `Bearer ${process.env.VATNODE_API_KEY}` },
})
if (!res.ok) {
  throw new Error(`vatnode API error ${res.status}: ${await res.text()}`)
}
const data = await res.json()
// { valid, vatId, countryCode, countryName, companyName, companyAddress,
//   checkId, verifiedAt, source, consultationNumber, ... }

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 is a separate decision your tax logic makes, using this evidence as one input.

If you’re deciding between caching a response in Redis and storing it in a collection like this, the difference between caching and storing covers why they answer different questions. Once check rows are landing in vatChecks, the natural next steps are revalidating stored VAT IDs in bulk on a schedule, understanding what the consultation number is and why it only shows up on some rows, and the full audit trail, end to end for how these check documents turn into evidence an auditor accepts.

FAQ

Should I embed the VAT check history in the customer document, or use a separate collection?

Use a separate collection. The check log grows without bound, and embedding it risks hitting MongoDB’s document size limit long before that becomes a practical problem. Store the current VAT ID and a denormalized ‘latest status’ on the customer document, and keep every check as its own row in a dedicated collection.

How do I enforce that VAT check records are never modified, since MongoDB has no immutable-row feature?

MongoDB has no database-level equivalent to revoking UPDATE grants. Enforce append-only behavior at the application layer – only ever insertOne, never updateOne or deleteOne, on the check collection – and restrict write roles so only that code path can touch it. It’s a weaker guarantee than a relational database gives you, so treat the access-control layer as load-bearing.

Can I use a TTL index to clean up old VAT check records?

Not on the evidence collection. A TTL index will silently delete rows once they age out, which is exactly what you don’t want on an audit trail – VAT record-keeping obligations commonly run years, and a forgotten TTL index quietly destroys the evidence before that window closes. If you need to prune anything, prune denormalized convenience fields, not the check log.