How We Fixed a Silent GDPR Data Integrity Risk in Our Erasure Pipeline

Release v1.0.404 · ERR-18 · Data Integrity

This post explains the root cause, the risk, and how we resolved a database transaction bug (ERR-18) that affected our GDPR user erasure function and Open Banking account import pipeline.

The Problem

GDPR Erasure Was Not Atomic

When a user exercises their right to erasure under GDPR Article 17, our platform must delete or anonymise all personal data associated with their account. The performUserErasure() function in src/lib/routers/user.ts carried out more than ten sequential database operations to do this:

Anonymise audit log entries
Nullify foreign-key references across related tables
Delete HMRC credentials
Delete sessions
Delete memberships
Delete organisations
Delete the user record itself

These operations were executed one after another as independent database calls — with no enclosing transaction.

The consequence: if anything went wrong partway through — a transient network blip, a database timeout, an unexpected constraint violation — the function would halt, leaving the database in a partially erased state. For example, if the process failed after deleting hmrcCredentials but before deleting the user record, personal data would still exist in the system even though the erasure had technically been "attempted". This is both a GDPR compliance failure and a referential integrity problem.

Bank Account Imports Were Also Unprotected

A second, related issue existed in bank/callback/route.ts. After a successful Open Banking OAuth callback, the handler loops through the returned accounts and writes each one to the bankAccounts table. This loop also had no transaction wrapper. A failure on iteration three of a five-account import would leave the user with a partial set of connected accounts, with no clean way to detect which records were actually persisted.

Why This Matters

Database transactions exist precisely to handle these scenarios. Without them, multi-step write operations are not atomic — they can be interrupted at any point, leaving data in an inconsistent state that is difficult or impossible to recover from programmatically.

For a GDPR erasure workflow, partial state is not just a data quality issue — it is a legal compliance failure. Article 17 requires that erasure be complete. A system that silently half-erases a user, with no rollback and no retry, cannot satisfy that requirement.

The Fix

Both code paths have been updated to use Drizzle ORM's db.transaction() API, which wraps all operations in a single database transaction. If any step throws an error, the entire transaction is automatically rolled back — leaving the database exactly as it was before the operation began.

// Before: independent calls, no atomicity
await db.update(auditLogs).set({ userId: null });
await db.delete(hmrcCredentials).where(eq(hmrcCredentials.userId, userId));
await db.delete(users).where(eq(users.id, userId));
// ^ if this line throws, hmrcCredentials is already deleted

// After: all-or-nothing transaction
await db.transaction(async (tx) => {
  await tx.update(auditLogs).set({ userId: null });
  await tx.delete(hmrcCredentials).where(eq(hmrcCredentials.userId, userId));
  await tx.delete(users).where(eq(users.id, userId));
  // ^ if this throws, ALL prior steps are rolled back automatically
});

The key change is that all db.* calls inside the wrapped functions are replaced with tx.* calls, so they share the same database connection and participate in the same transaction boundary.

The same pattern is applied to the bankAccounts upsert loop in the Open Banking callback handler.

What Changed in Practice

Scenario	Before v1.0.404	From v1.0.404
Network error mid-erasure	Partial deletion, no rollback	Full rollback, no data left in inconsistent state
DB timeout during bank import	Partial accounts saved	No accounts saved; client can safely retry
HMRC credential deletion fails	User record may still be deleted (orphaned credentials)	Entire erasure aborted and rolled back

Recommendations for Self-Hosted Deployments

If you are running a self-hosted instance, we recommend:

Upgrading to v1.0.404 immediately if your deployment handles GDPR erasure requests.
Auditing your database for users in a partially-erased state (i.e. rows in hmrcCredentials, sessions, or memberships with no corresponding users record). These represent incomplete erasure attempts from before this fix.
Reviewing any downstream jobs that rely on the erasure function completing successfully — they should now handle the case where erasure either fully succeeds or fully fails, with no partial states.

Summary

ERR-18 is a silent but serious data integrity bug. No data was corrupted in the sense of being altered incorrectly, but the absence of transaction wrappers meant that failure scenarios could leave the database in states that violated both GDPR obligations and relational integrity constraints. Wrapping both performUserErasure() and the bank account upsert loop in db.transaction() blocks resolves this fully, with all-or-nothing guarantees on every execution.

This fix is included in v1.0.404 and is available now.