What Happens When the Database Says Success but the User Gets an Error?

1. The Boundary Gap: Commit vs. Network Ack

One of the most confusing customer support tickets in software engineering goes like this:

'The user saw a red error banner saying "Failed to create order", but the money was deducted and the order actually appeared in the database.'

Engineers instinctively check the application logs, find no exceptions, and discover that the SQL `COMMIT` executed in under 2 milliseconds. What happened?

The explanation lies in the distinction between a local database boundary and a distributed network boundary. A database commit guarantees durability (the 'D' in ACID). Once the WAL (Write-Ahead Log) is flushed to disk, the database has completed its contract. But returning that fact to the user requires traversing the web server, reverse proxy, load balancer, cell tower, and browser. If any packet drops after the commit finishes, the database has succeeded, but the user experiences an error.

2. The Danger of the Dual-Write Problem

This vulnerability multiplies when application code attempts to write to two different systems sequentially:

// ANTI-PATTERN: The Unprotected Dual Write
async function registerUser(userData: UserInput) {
  // 1. Write to primary database
  const user = await db.users.create({ data: userData });

  // 2. Publish event to message queue or send welcome email
  // If the server crashes or network partitions here:
  // - The user exists in the database
  // - The event/email is NEVER sent
  await messageQueue.publish("user.registered", { userId: user.id });

  return user;
}

In the snippet above, there is no atomic guarantee spanning the database write and the queue publish. If step 1 succeeds and the process crashes before step 2, your system enters an inconsistent state. Reversing the order is equally dangerous: if the message queue publish succeeds but the database write fails on a duplicate email constraint, an email is sent for a user that never exists in the database.

3. The Solution: The Transactional Outbox Pattern

To guarantee that downstream events and database modifications always stay synchronized, software architects use the Transactional Outbox Pattern. Instead of publishing directly to an external queue in application code, the event is inserted into an `outbox` table within the same local database transaction.

BEGIN;

-- 1. Insert domain entity
INSERT INTO orders (id, customer_id, total_amount, status)
VALUES ('ord_104', 'cust_88', 120.00, 'PENDING');

-- 2. Insert event into outbox table in the SAME transaction
INSERT INTO outbox_events (id, aggregate_type, aggregate_id, event_type, payload)
VALUES (
  'evt_772',
  'Order',
  'ord_104',
  'ORDER_CREATED',
  '{"orderId": "ord_104", "total": 120.00}'
);

COMMIT; -- Either both are saved, or neither is saved.

4. Designing Resilient Client Architectures

When designing mobile apps (like our Fuel Pilot application) or frontend single-page applications, never assume that a failed network call means the data was not recorded on the server.

Clients should implement reconciliation queries or provide an idempotency identifier so that subsequent retries fetch the existing record rather than duplicating state.