TRCKR-219 - trckr

← TRCKR-219

bug sync

Created: 2025-12-09 • Updated: 2025-12-16

Relationships Loading...

## Problem

When a client tries to create an issue with an identifier that matches a soft-deleted issue on the server, the sync fails with a UNIQUE constraint violation.

### Reproduction Steps
1. Create issue TRCKR-157 on client, sync to server
2. Delete TRCKR-157 on server (soft-delete sets deleted_at)
3. Create new issue TRCKR-157 on client (gets new UUID)
4. Try to sync → HTTP 500: `sqlite3.IntegrityError: UNIQUE constraint failed: issues.identifier`

### Root Cause
- Server soft-deletes keep the identifier in the database
- Client counter doesn't know about soft-deleted issues
- When client creates a new issue, it may reuse an identifier that exists (but is deleted) on server
- Server's create_issue doesn't check for soft-deleted identifier collisions

### Observed Data
```
Server: TRCKR-157: id=5a8f3dd2..., deleted_at=2025-12-09T05:52:18
Client: TRCKR-157: id=53a9a729... (different UUID, pending create)
```

## Proposed Solutions

### Option 1: Server assigns canonical identifiers (Recommended)
- Client sends TEMP-* identifiers for new issues
- Server assigns the real identifier (TRCKR-N) during sync
- Server returns canonical_identifier in sync response
- Already partially implemented but not enforced

### Option 2: Server handles identifier collision
- On create, check if identifier exists (including soft-deleted)
- If soft-deleted with same ID: restore and update
- If soft-deleted with different ID: assign new identifier
- If active: reject as conflict

### Option 3: Hard delete or identifier recycling
- Either hard-delete issues (lose history)
- Or mark identifiers as "available" after N days

## E2E Tests Needed
- [ ] Create issue, sync, delete on server, recreate locally, sync again
- [ ] Two clients create issues simultaneously (identifier race)
- [ ] Client creates issue with identifier matching soft-deleted server issue
- [ ] Verify canonical_identifier assignment works correctly

## Files to Modify
- `server/routes/sync.py` - Handle identifier collisions in _apply_create
- `client/sync.py` - Use TEMP-* identifiers for new issues
- `tests/test_sync_e2e.py` - Add collision tests

Pull Requests (comma-separated URLs)

Attachments

Comments (2)

qa-agent · 2025-12-16

## QA PASSED See QA report document: 45f5ce4c-feac-485a-bcf3-83c53b16aa77 **All hypotheses tested, no issues found:** - identifier_exists() correctly detects soft-deleted issues - Sync collision detection triggers and assigns new identifiers - E2E tests validate the exact reproduction scenario - No regressions in sync test suite (53 tests pass) Fix verified working.

agent · 2025-12-09

## Fix Implemented ### Problem Found The root cause was in `server/routes/sync.py:390` where `store.get_issue_by_identifier(identifier)` only checks for active issues (`deleted_at IS NULL`). This allowed the check to pass even when a soft-deleted issue with the same identifier existed, leading to a UNIQUE constraint violation when trying to INSERT. ### Changes Made **1. Added `identifier_exists()` method to LocalStore** - File: `client/store.py:434-456` - Checks for identifier existence including soft-deleted issues - Parameters: `identifier` (string), `include_deleted` (bool, default True) - Returns: `True` if identifier exists (in any state), `False` otherwise **2. Updated server sync collision detection** - File: `server/routes/sync.py:388-401` - Changed from `get_issue_by_identifier()` to `identifier_exists(include_deleted=True)` - Now properly detects collisions with soft-deleted issues - Logs collision detection with INFO level for debugging - Assigns next available identifier using `_next_issue_identifier()` **3. Added comprehensive E2E tests** - File: `tests/test_sync_e2e.py:750-915` - `test_identifier_collision_with_soft_deleted_server_entity`: Tests the exact scenario from the issue - `test_two_clients_create_same_identifier_simultaneously`: Tests race condition between clients - Both tests verify proper identifier reassignment and successful sync ### Test Results ``` tests/test_sync_e2e.py::TestIdentifierCollisionE2E::test_identifier_collision_with_soft_deleted_server_entity PASSED tests/test_sync_e2e.py::TestIdentifierCollisionE2E::test_two_clients_create_same_identifier_simultaneously PASSED tests/test_counter_sync.py - All PASSED (no regression) ``` ### How It Works 1. Client creates issue with identifier `TRCKR-157` 2. Server checks `identifier_exists("TRCKR-157", include_deleted=True)` 3. Finds soft-deleted issue with that identifier 4. Logs: "Identifier collision detected: TRCKR-157 exists (possibly soft-deleted). Assigning new identifier." 5. Calls `_next_issue_identifier()` which finds the lowest available number (TRCKR-158+) 6. Creates issue with new identifier 7. Returns `canonical_identifier` in sync response 8. Client updates local identifier to match canonical ### Edge Cases Handled - Multiple soft-deleted issues with same prefix - Client counter reset scenarios - Two clients creating same identifier simultaneously - Project-specific identifiers (PROJECT-N) vs global (TRCKR-N)