Offline Sync Queue
Data Entity
Description
Persistent queue of local mutations (creates, updates, deletes) captured while the device is offline or connectivity is degraded. Each entry represents a single API call that must be replayed against the server once connectivity is restored, preserving causal ordering and enabling idempotent retry with exponential backoff.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key for the queue entry | PKrequiredunique |
user_id |
uuid |
FK to users — the user whose action produced this mutation | required |
operation |
enum |
HTTP method / CRUD verb this entry represents | required |
entity_type |
string |
Domain object type being mutated (e.g. 'activity', 'contact', 'expense'). Used to route the replay to the correct API endpoint. | required |
local_id |
uuid |
Client-generated ID assigned to the entity at creation time (before a server ID exists). Tracked in id_mappings once server responds. | - |
server_id |
uuid |
Server-assigned ID for the entity. Null until the entry has been successfully synced and the id_mapping resolved. | - |
endpoint |
string |
Relative API endpoint path to replay against (e.g. '/api/v1/activities'). May contain local_id placeholders resolved at replay time. | required |
payload |
json |
Serialized request body. Encrypted at rest using SQLCipher. May contain unresolved local_id references that are substituted for server IDs at replay time. | - |
headers |
json |
Additional HTTP headers required for the request (e.g. content-type, idempotency key) | - |
idempotency_key |
string |
UUID v4 sent as a header on every replay attempt to prevent duplicate server-side writes if the same request is received more than once | requiredunique |
status |
enum |
Current processing state of the queue entry | required |
attempt_count |
integer |
Number of sync attempts made so far | required |
max_attempts |
integer |
Maximum retries before marking the entry as permanently failed | required |
next_retry_at |
datetime |
Earliest timestamp at which the next retry is eligible (used for exponential backoff). Null if not yet retried. | - |
last_error |
text |
Error message or server response body from the most recent failed attempt, for debugging and conflict resolution | - |
last_error_code |
integer |
HTTP status code returned by the last failed attempt (e.g. 409 for conflict, 422 for validation failure) | - |
sequence_number |
integer |
Monotonically increasing integer per user ensuring causal ordering of mutations at replay time. Lower numbers are replayed first. | required |
dependency_ids |
json |
Array of queue entry IDs (local_ids) that must be successfully synced before this entry is eligible for replay. Used when a mutation references an entity that was also created offline. | - |
created_at |
datetime |
Device-local timestamp when the mutation was captured. Used for ordering and staleness detection. | required |
synced_at |
datetime |
Timestamp when the entry was successfully acknowledged by the server. Null until synced. | - |
conflict_resolution |
enum |
Strategy applied when a 409 Conflict is returned by the server | - |
priority |
integer |
Replay priority within the same sequence position. Higher values drain first. Activities default 10, expenses 8, contacts 5. | required |
Database Indexes
idx_offline_sync_queue_user_status
Columns: user_id, status
idx_offline_sync_queue_status_next_retry
Columns: status, next_retry_at
idx_offline_sync_queue_user_sequence
Columns: user_id, sequence_number
idx_offline_sync_queue_idempotency_key
Columns: idempotency_key
idx_offline_sync_queue_entity_type
Columns: entity_type, status
idx_offline_sync_queue_local_id
Columns: local_id
Validation Rules
operation_must_be_valid_verb
error
Validation failed
endpoint_must_be_relative_path
error
Validation failed
payload_must_be_valid_json
error
Validation failed
sequence_number_must_be_monotonic
error
Validation failed
user_id_must_reference_existing_user
error
Validation failed
dependency_ids_must_exist_in_queue
error
Validation failed
status_transition_must_be_valid
error
Validation failed
Business Rules
causal_ordering_enforced
Entries for the same user must be replayed in sequence_number order. An entry with unresolved dependency_ids (referencing pending entries) is blocked until all dependencies reach 'succeeded' status.
idempotency_key_unique_per_mutation
Each queue entry is assigned a UUID v4 idempotency key at creation time. The same key is sent on every retry. The server must treat repeated requests with the same key as no-ops after the first success.
exponential_backoff_on_failure
After each failed attempt, next_retry_at is set to now + (2^attempt_count * base_delay_seconds), capped at 30 minutes. This prevents thunderstorm reconnect patterns.
max_attempts_hard_stop
When attempt_count reaches max_attempts and the entry has not succeeded, status is set to 'failed'. Failed entries are surfaced to the user for manual resolution or discard. They are never retried automatically.
conflict_requires_resolution_strategy
When the server returns HTTP 409, status transitions to 'conflict' and processing halts. ConflictResolverService evaluates the conflict_resolution strategy. 'server_wins' discards the local payload; 'client_wins' forces a PUT with If-Match disabled; 'manual' notifies the user.
succeeded_entries_pruned_after_sync
Entries in 'succeeded' status are eligible for deletion after synced_at + 7 days. This keeps the local Drift DB from growing unboundedly while preserving a short audit window for debugging.
local_id_resolution_before_replay
Before sending a queued request, the background sync worker resolves all local_id references in the payload and endpoint against the id_mappings table. If a referenced local_id has no server_id yet, the entry is blocked.
in_flight_lock_prevents_duplicate_dispatch
When a worker picks up an entry, status is set to 'in_flight' atomically. No other worker process may pick up an in_flight entry. If the worker crashes, a watchdog resets in_flight entries older than 2 minutes back to 'pending'.