Optimistic Locking Implementation Plan (CONC-001)

Created: 2024-12-27 Status: Planning Complete Priority: P2 (Data Integrity) Target: V1.1

---

1. Problem Statement

Current Behavior

The system uses Last Write Wins - no concurrency control. When two users edit the same record simultaneously, the second save silently overwrites the first.

Failure Scenario

Time	User A (Preparer)	User B (Reviewer)
0:00	Opens client "David Thompson"	Opens client "David Thompson"
0:30	Changes phone to (555) 111-1111, saves
0:45		Changes email to new@email.com, saves
Result	Phone change LOST - no warning	Email saved, but A's change gone

User A has no indication their change was overwritten. This creates: - Data integrity issues (lost updates) - Audit trail confusion (who made what change?) - User frustration and distrust

Affected Operations

Entity	Operations	Risk Level
Clients	Update name, contact, address	High - multiple staff may edit
Tax Returns	Status changes, assignments	High - preparer/reviewer handoffs
Documents	Reclassify, move, status	Medium - typically single user
Chat Sessions	Status updates	Low - usually single owner

---

2. Solution: Optimistic Locking

Approach

Add a version field to each record. On update: 1. Client sends current version with update request 2. Server checks if version matches database 3. If match: update succeeds, version increments 4. If mismatch: return 409 Conflict with current data

Why Optimistic vs Pessimistic Locking

Approach	Pros	Cons
Optimistic (chosen)	Simple, no lock management, scales well	Conflict resolution needed
Pessimistic	Prevents conflicts entirely	Lock management, deadlocks, stale locks

For a small tax practice (2-5 concurrent users), conflicts are rare. Optimistic locking handles the occasional conflict gracefully without the complexity of lock management.

---

3. Database Changes

Schema Updates

Add version column to all mutable entities:

-- Clients table
ALTER TABLE clients ADD COLUMN version INTEGER NOT NULL DEFAULT 1;

-- Tax returns table
ALTER TABLE tax_returns ADD COLUMN version INTEGER NOT NULL DEFAULT 1;

-- Documents table
ALTER TABLE documents ADD COLUMN version INTEGER NOT NULL DEFAULT 1;

-- Users table (staff)
ALTER TABLE users ADD COLUMN version INTEGER NOT NULL DEFAULT 1;

Migration Script

-- Migration: add_optimistic_locking_version
-- Date: 2024-12-27

BEGIN;

-- Add version columns with default 1 for existing records
ALTER TABLE clients ADD COLUMN IF NOT EXISTS version INTEGER NOT NULL DEFAULT 1;
ALTER TABLE tax_returns ADD COLUMN IF NOT EXISTS version INTEGER NOT NULL DEFAULT 1;
ALTER TABLE documents ADD COLUMN IF NOT EXISTS version INTEGER NOT NULL DEFAULT 1;
ALTER TABLE users ADD COLUMN IF NOT EXISTS version INTEGER NOT NULL DEFAULT 1;

-- Create index for version checks (optional, small performance gain)
CREATE INDEX IF NOT EXISTS idx_clients_version ON clients(id, version);
CREATE INDEX IF NOT EXISTS idx_tax_returns_version ON tax_returns(id, version);
CREATE INDEX IF NOT EXISTS idx_documents_version ON documents(id, version);

COMMIT;

---

4. API Changes

Request/Response Updates

All GET endpoints - Include version in response:

// GET /v1/clients/123
{
  "id": "123",
  "name": "David Thompson",
  "email": "david@email.com",
  "version": 3,
  ...
}

All PATCH/PUT endpoints - Require version in request:

// PATCH /v1/clients/123
{
  "name": "David T.",
  "version": 3
}

Conflict Response (409)

When version mismatch detected:

// HTTP 409 Conflict
{
  "error": "conflict",
  "message": "This record was modified by another user",
  "detail": {
    "entity": "client",
    "entity_id": "123",
    "your_version": 3,
    "current_version": 4,
    "current_data": {
      "id": "123",
      "name": "David Thompson",
      "email": "new@email.com",
      "phone": "(555) 111-1111",
      "version": 4,
      "updated_at": "2024-12-27T15:30:00Z",
      "updated_by": "reviewer@firm.com"
    }
  }
}

Affected Endpoints

Endpoint	Method	Change Required
/v1/clients/{id}	GET	Add version to response
/v1/clients/{id}	PATCH	Require version, check, increment
/v1/clients/{id}/returns	GET	Add version to each return
/v1/returns/{id}	GET	Add version to response
/v1/returns/{id}	PATCH	Require version, check, increment
/v1/returns/{id}/status	PUT	Require version, check, increment
/v1/documents/{id}	GET	Add version to response
/v1/documents/{id}/reclassify	POST	Require version, check, increment
/v1/documents/{id}/move	POST	Require version, check, increment
/v1/documents/{id}	DELETE	Require version, check (soft delete)

Implementation Pattern

@app.patch("/v1/clients/{client_id}")
async def update_client(client_id: str, request: ClientUpdate):
    conn = get_db()
    cursor = conn.cursor()

    # Version is now required
    if request.version is None:
        raise HTTPException(status_code=400, detail="version is required")

    # Atomic check-and-update with version
    cursor.execute("""
        UPDATE clients
        SET name = COALESCE(%s, name),
            email = COALESCE(%s, email),
            phone = COALESCE(%s, phone),
            version = version + 1,
            updated_at = %s
        WHERE id = %s AND version = %s
        RETURNING *
    """, (request.name, request.email, request.phone, now_iso(),
          client_id, request.version))

    row = cursor.fetchone()

    if row is None:
        # Either not found OR version mismatch - check which
        cursor.execute("SELECT * FROM clients WHERE id = %s", (client_id,))
        current = cursor.fetchone()

        if current is None:
            raise HTTPException(status_code=404, detail="Client not found")
        else:
            # Version mismatch - return 409 with current data
            raise HTTPException(
                status_code=409,
                detail={
                    "error": "conflict",
                    "message": "This record was modified by another user",
                    "your_version": request.version,
                    "current_version": current["version"],
                    "current_data": format_client(current)
                }
            )

    conn.commit()
    return format_client(row)

---

5. Frontend Changes

Pydantic Model Updates

class ClientUpdate(BaseModel):
    name: Optional[str] = None
    email: Optional[str] = None
    phone: Optional[str] = None
    version: int  # Now required

State Management

Frontend must: 1. Store version when loading entity 2. Send version with every update 3. Handle 409 Conflict response

// In React Query mutation
const updateClient = useMutation({
  mutationFn: async (data: ClientUpdate) => {
    const response = await fetch(`/api/v1/clients/${clientId}`, {
      method: 'PATCH',
      body: JSON.stringify({
        ...data,
        version: currentVersion  // Always include
      })
    });

    if (response.status === 409) {
      const conflict = await response.json();
      throw new ConflictError(conflict);
    }

    return response.json();
  },
  onSuccess: (data) => {
    // Update local version
    setCurrentVersion(data.version);
  },
  onError: (error) => {
    if (error instanceof ConflictError) {
      showConflictDialog(error.detail);
    }
  }
});

Conflict Resolution Dialog

When 409 received, show modal:

┌─────────────────────────────────────────────────────────┐
│  ⚠️  This record was modified by another user           │
├─────────────────────────────────────────────────────────┤
│                                                         │
│  Your changes:                                          │
│    • Name: "David T."                                   │
│                                                         │
│  Current data (saved by reviewer@firm.com at 3:30 PM):  │
│    • Name: "David Thompson"                             │
│    • Email: "new@email.com"  ← changed                  │
│    • Phone: "(555) 111-1111" ← changed                  │
│                                                         │
│  ┌─────────────────┐  ┌─────────────────┐               │
│  │  Discard Mine   │  │  Retry My Save  │               │
│  └─────────────────┘  └─────────────────┘               │
│                                                         │
│  "Retry" will overwrite the other user's changes.       │
│                                                         │
└─────────────────────────────────────────────────────────┘

Button behaviors: - Discard Mine: Reload current data, user's changes lost - Retry My Save: Re-submit with new version (overwrites other's changes)

Files to Modify

File	Changes
frontend/packages/ui/src/lib/api.ts	Add ConflictError handling
frontend/apps/staff-app/src/pages/ClientDetailPage.tsx	Handle version, show conflict dialog
frontend/apps/staff-app/src/pages/ReviewPage.tsx	Handle version for document actions
frontend/apps/staff-app/src/components/ConflictDialog.tsx	New - Conflict resolution modal

---

6. Test Cases

Unit Tests

Test	Description
test_update_client_increments_version	Version goes from N to N+1 on successful update
test_update_client_wrong_version_returns_409	Stale version returns 409 Conflict
test_update_client_missing_version_returns_400	Missing version returns 400 Bad Request
test_conflict_response_includes_current_data	409 response includes current_data
test_concurrent_updates_one_fails	Simulate race condition, verify one succeeds

Integration Tests

Test	Description
test_concurrent_client_updates	Two parallel updates, verify correct behavior
test_version_persists_across_requests	Update, reload, verify version incremented
test_conflict_resolution_retry	409 → retry with new version → success

E2E Tests (Playwright)

Test	Description
test_conflict_dialog_appears	Simulate conflict, verify dialog shown
test_discard_reloads_current_data	Click Discard, verify fresh data loaded
test_retry_overwrites_and_succeeds	Click Retry, verify save succeeds

BDD Scenarios

Feature: Optimistic Locking

  Scenario: Successful update with correct version
    Given a client "David Thompson" exists with version 1
    When I update the client name to "David T." with version 1
    Then the update should succeed
    And the client version should be 2

  Scenario: Conflict detected with stale version
    Given a client "David Thompson" exists with version 1
    And another user updates the client to version 2
    When I update the client name with version 1
    Then I should receive a 409 Conflict response
    And the response should include the current version 2
    And the response should include the current client data

  Scenario: Missing version rejected
    Given a client "David Thompson" exists
    When I update the client without providing a version
    Then I should receive a 400 Bad Request response
    And the error should mention "version is required"

---

7. Delegation Strategy

Recommended Approach: Haiku for Implementation, Sonnet for Review

Task	Model	Rationale
Database migration script	Haiku	Simple DDL statements
API endpoint modifications	Haiku	Repetitive pattern across endpoints
Pydantic model updates	Haiku	Mechanical changes
Frontend state management	Sonnet	Requires understanding React Query patterns
Conflict dialog component	Sonnet	UX considerations, styling
Unit tests	Haiku	Straightforward test cases
Integration tests	Sonnet	Database setup, concurrency simulation
E2E/Playwright tests	Sonnet	Complex UI interactions
Code review	Sonnet	Pattern verification, edge cases

Parallel Work Streams

Stream 1 (Backend):
├── Database migration (Haiku, 30 min)
├── API endpoint updates (Haiku, 2 hrs)
└── Unit/Integration tests (Haiku+Sonnet, 2 hrs)

Stream 2 (Frontend):
├── API client updates (Haiku, 30 min)
├── State management (Sonnet, 1 hr)
├── ConflictDialog component (Sonnet, 1 hr)
└── E2E tests (Sonnet, 1 hr)

Total estimated effort: 8-10 hours

---

8. Implementation Phases

Phase 1: Database + API (Backend)

1. Create and run migration script
2. Update scripts/bootstrap.py to include version column
3. Update scripts/local_api.py endpoints:
4. Add version to all GET responses
5. Add version requirement to all PATCH/PUT/DELETE
6. Implement 409 Conflict handling
7. Update Pydantic models
8. Write unit tests
9. Write integration tests

Phase 2: Frontend

1. Update API client to handle 409
2. Add version tracking to state
3. Create ConflictDialog component
4. Integrate dialog into pages
5. Write E2E tests

Phase 3: Validation

1. Manual testing with two browser sessions
2. Run full test suite
3. Update API documentation
4. Update ARCHITECTURE.md

---

9. Rollback Plan

If issues discovered post-deployment:

1. Immediate: Frontend can ignore version field (backwards compatible)
2. Short-term: API can accept missing version with warning log
3. Full rollback: Remove version requirement from API, keep column

The version column itself is harmless - only the enforcement causes issues.

---

10. Future Enhancements

CONC-002: Field-Level Merging

Priority: P3 Depends on: CONC-001

Problem: Record-level locking forces all-or-nothing conflict resolution. If User A changes phone and User B changes email, they shouldn't conflict - both changes can coexist.

Solution: Track which fields were changed, auto-merge non-overlapping changes, only conflict on same-field edits.

Request Format:

{
  "name": "David T.",
  "version": 3,
  "_changed_fields": ["name"]
}

Server Logic: 1. Fetch current record and identify fields changed since client's version 2. Compare _changed_fields with server-changed fields 3. If no overlap: auto-merge all changes, return success 4. If overlap: return 409 with field-level conflict details

Conflict Response:

{
  "error": "field_conflict",
  "auto_merged": ["phone"],
  "conflicts": [
    {
      "field": "email",
      "your_value": "new@email.com",
      "current_value": "other@email.com",
      "changed_by": "reviewer@firm.com"
    }
  ]
}

UI Options: - Keep mine (overwrite their email) - Keep theirs (discard my email change) - Manual merge (edit the field)

Effort: 4-6 hours additional

---

CONC-003: Special Conflict Scenarios

Priority: P3 Depends on: CONC-001

Scenarios to Handle:

Delete vs Update: - User A deletes client while User B is editing - Solution: Soft-delete checks for pending edits - If active edit session, block delete with "Record is being edited by X" - If edit submitted after delete: "This record was deleted by X at Y"

Status Race Conditions: - Two users change return status simultaneously - Solution: State machine validation - Only allow valid transitions (intake → in_progress → review) - Invalid transition returns 422 with "Cannot change from X to Y"

Assignment Collision: - Two preparers claim same return - Solution: Atomic assignment with version check - "Already assigned to X" message if collision

Document Move Conflicts: - Two users move same document to different returns - Solution: Version on document entity - Standard optimistic locking applies

Bulk Operation Conflicts: - Bulk status update while individual edit in progress - Solution: Each record checked individually - Report: "Updated 45 of 50 records, 5 had conflicts"

Parent-Child Constraints: - Delete client with active returns - Delete return with unprocessed documents - Solution: Pre-delete validation - "Cannot delete: has N active returns" error

Effort: 6-8 hours

---

Other Future Enhancements

Real-time Presence (P4): Show "User X is editing this record" via WebSocket. Prevents conflicts before they happen.

Change History (P3): Audit log showing who changed what field, when. Useful for debugging and compliance.

WebSocket Notifications (P4): Push updates to other viewers when record changes. They see fresh data without manual refresh.

---

11. References

• Backlog item: CONC-001
• Related: DUP-001 (Duplicate Detection) - similar conflict handling UX
• Pattern: Similar to ETag/If-Match HTTP caching