How We Fixed Silent Data Corruption in Multi-Step Database Writes

Version: v0.1.120 | Control: ERR-18 | Category: Data Integrity

The Problem

Some of the most dangerous bugs in production systems are the ones that don't immediately throw an error — they simply leave your database in a state you didn't intend.

We identified three code paths in the platform that performed multiple sequential database writes with no transaction wrapping. Each path was effectively betting that nothing would go wrong between the first write and the last. For most requests, that bet pays off. But in the cases where it doesn't — a network blip, an unexpected error, an application restart — the result is silent, partial data corruption.

The Three Affected Paths

1. GDPR Delete

When a user exercises their right to erasure, the GDPR delete route performs 8+ writes across several tables to anonymise their record. Without a transaction, a failure at step 4 of 8 leaves the user half-anonymised: some personal data removed, some still present. This is not a compliant erasure under GDPR — the right to erasure requires the operation to be complete.

2. Credit Top-Up (addCredits)

Adding credits to a user's account involved two statements: increment the balance, then insert a transaction record. Between those two statements there is a window — however small — where the balance has increased but no record of why exists. A crash in that window creates credits from nothing, with no audit trail. This breaks financial integrity.

3. Nightly Sanctions Sync

Every night, the platform syncs against the OFSI consolidated list: create a new version, insert 3,000+ entities in batches, mark the version as complete. If a batch fails halfway through, the previous batches are already committed. The result is a version record pointing to a partial dataset — entities that exist in the list but weren't imported, silently absent from screening results.

The Fix

All three paths are now wrapped in Drizzle ORM's db.transaction(). This is the standard approach for atomic multi-step writes: every statement inside the callback uses the transaction's connection, and if any error is thrown, the entire operation is rolled back automatically.

await db.transaction(async (tx) => {
  // Step 1
  await tx.update(users).set({ ... }).where(...);

  // Step 2
  await tx.insert(auditLog).values({ ... });

  // Step 3 ... N
  // If any step throws, all previous steps are rolled back.
});

The key change in each path is replacing db with the transaction's tx object for every write inside the block. The transaction is committed only after all steps complete successfully.

GDPR Delete

The full anonymisation sequence is now a single atomic operation. Either all 8+ writes succeed and the user's data is fully erased, or none of them are committed and the record remains intact for a retry.

Credit Top-Up

The balance update and transaction record insert now happen inside a single transaction. It is no longer possible for one to succeed without the other.

Nightly Sanctions Sync

The entity insertion loop and the version status update now share a single transaction. If any batch fails, the entire import is rolled back — no partial versions, no orphaned entities. The next sync run starts from a clean state.

Why This Matters for Compliance

The sanctions screening platform operates in a regulated environment where data integrity is not optional:

• GDPR right to erasure requires deletion to be complete. Partial anonymisation is non-compliant.
• Financial audit trails must be accurate. Credits that appear without a corresponding transaction record undermine the integrity of the audit log.
• Sanctions screening accuracy depends on the dataset being consistent. A partial import could result in a sanctioned entity not appearing in search results.

Database transactions are the correct technical primitive for enforcing these guarantees. This release closes the gap between what the system was intended to do and what it was actually guaranteed to do under failure conditions.