Conflict detection in distributed systems

When an AI agent works on data for 30 seconds, a lot can change in production. Here's how we detect conflicts before they become problems.

The lost update problem

Classic scenario:

1. Agent reads user balance: $100
2. Human updates balance to $150 (deposit)
3. Agent writes balance: $50 (refund calculation based on $100)
4. Result: Customer lost $50

This is called a "lost update" — the agent's write clobbered the human's change because it was based on stale data.

Optimistic vs pessimistic concurrency

Pessimistic locking — Lock rows when you read them. No one else can modify until you're done.

Problem: Agents might hold locks for minutes while "thinking." You'd grind production to a halt.

Optimistic concurrency — Don't lock anything. Detect conflicts at write time.

This is what we use. The agent works freely, and we detect problems before applying changes.

Row hashing

When an agent reads data it will later modify, we compute a hash of the row:

SELECT md5(row_to_json(users.*)::text) as hash
FROM users WHERE id = 42
-- Returns: 'a1b2c3d4e5f6...'

We store this hash in a tracking table:

INSERT INTO session_abc._row_hashes (table_name, pk_value, captured_hash)
VALUES ('users', '42', 'a1b2c3d4e5f6...')

Conflict detection at approval

When a reviewer clicks "Approve," we don't immediately apply changes. First, we recompute hashes:

pub async fn check_conflicts(session: &Session) -> Vec<Conflict> {
    let mut conflicts = vec![];

    for (table, pk, captured_hash) in session.captured_hashes() {
        let current_hash = compute_row_hash(table, pk).await;

        if current_hash != captured_hash {
            conflicts.push(Conflict {
                table,
                pk,
                conflict_type: if current_hash.is_none() {
                    ConflictType::RowDeleted
                } else {
                    ConflictType::RowModified
                },
                captured_hash,
                current_hash,
            });
        }
    }

    conflicts
}

Types of conflicts

We detect three scenarios:

RowModified — The row exists but its hash changed. Someone else updated it.

{
  "table": "users",
  "pk_value": "42",
  "conflict_type": "row_modified",
  "captured_hash": "a1b2c3d4",
  "current_hash": "e5f6g7h8"
}

RowDeleted — The row is gone. Someone deleted it while the agent was working.

{
  "table": "users",
  "pk_value": "42",
  "conflict_type": "row_deleted",
  "captured_hash": "a1b2c3d4",
  "current_hash": null
}

InsertCollision — The agent tried to insert a row, but that primary key now exists (someone else inserted first).

Foreign key validation

Conflicts aren't just about the rows you touched. What about referential integrity?

Scenario:

1. Agent inserts order with user_id = 42
2. Human deletes user 42
3. Agent's insert would create an orphan

We validate foreign keys at approval time:

-- Find orders in shadow that reference non-existent users
SELECT o.id, o.user_id
FROM session_abc._shadow_orders o
LEFT JOIN public.users u ON o.user_id = u.id
WHERE u.id IS NULL

If we find violations, we surface them before applying:

{
  "constraint_name": "orders_user_id_fkey",
  "violation_type": "missing_reference",
  "source_table": "orders",
  "target_table": "users",
  "violating_values": ["42"],
  "message": "Order references user_id=42 which no longer exists"
}

What about deletions?

Deletes are trickier. If the agent deletes a user, what happens to their orders?

We check for "would orphan" violations:

-- Find orders that would become orphans
SELECT o.id
FROM public.orders o
INNER JOIN session_abc._deleted_users d ON o.user_id = d.id

The reviewer sees: "Deleting user 42 would orphan 3 orders. Proceed?"

Conflict resolution strategies

When conflicts are detected, the reviewer has options:

1. Reject — Discard all changes, start fresh.
2. Proceed anyway — Apply changes, accepting data loss risk.
3. Merge manually — Not implemented yet, but on roadmap.

For now, we default to cautious. If there's any conflict, we show it and let the human decide.

Performance: lazy hash capture

We don't hash every row the agent reads. That would be expensive.

Instead, we use INSTEAD OF triggers on views. When the agent updates or deletes a row, the trigger:

1. Checks if we already have a hash for this (table, pk)
2. If not, computes and stores it
3. Proceeds with the shadow table operation

This means we only hash rows that actually get modified.

Edge cases

Composite primary keys. We serialize them as JSON: {"user_id": 1, "product_id": 2}.

No primary key. Tables without PKs can't be safely merged. We block writes to them.

JSONB columns. Hash comparison might differ due to key ordering. We normalize JSON before hashing.

Floating point. IEEE floats can have representation issues. We round to reasonable precision before hashing.

Why not version columns?

Some systems use a version or updated_at column for optimistic locking. Why not that?

1. Requires schema changes. Not every table has these columns.
2. Clock skew. updated_at comparisons fail across servers with different clocks.
3. Version gaps. If someone increments version twice, you can't tell what changed.

Row hashes are content-based. If the data is the same, the hash is the same. No schema changes needed.

Try it

Create a session, make some changes, then modify the same rows in production directly. When you approve, you'll see the conflict detection in action:

curl -X POST localhost:8080/sessions/$SESSION_ID/approve
# Returns:
# {
#   "status": "conflicts",
#   "conflicts": [
#     {"table": "users", "pk_value": "42", "conflict_type": "row_modified", ...}
#   ]
# }

Conflict detection is one piece of the puzzle. See Database sandboxes for AI agents for the full architecture.