Architectural Decision Record: Offline Sync¶
Status: Proposed
Date: 2026-07-18
Candidates Evaluated: PowerSync, ElectricSQL (V2)
1. Problem Owned¶
The application must operate completely offline for extended periods in remote areas. Users create records (Catches, Waypoints, Photo metadata), edit them multiple times, and restore connectivity later. The sync engine must reconcile local offline mutations with the central NAS PostgreSQL database without requiring Bluefly to build or maintain a custom write queue, retry loop, or transport mechanism.
2. Evaluation Summary¶
We evaluated both candidates by: - Scaffolding fully-configured local environments for each - Executing the identical offline workflow (Create Catch, 5x edits, photo metadata, simulated server conflict, network restore) - Measuring runtime behavior against both systems
3. Measured Evidence¶
These values were observed directly from the running prototype environments:
| Metric | PowerSync | ElectricSQL (V2) |
|---|---|---|
| Docker containers | 3 (DB, Storage, Sync Engine) | 0 (no local sync container; relies on external backends) |
| Idle RAM (Docker stack) | ~100 MB | ~300 MB (Node.js dev server for write handling) |
| Offline write queue behavior | Automatic (SDK-managed SQLite queue) | Not provided; writes are the developer's responsibility |
| Sync lifecycle management | Owned by PowerSync (retries, reconnect, backpressure) | Not provided; must be implemented by application |
| Reconnect behavior | SDK drains queue automatically via uploadData() hook |
Requires custom drain worker to POST queued writes |
| Conflict exposure point | uploadData() server hook → PostgreSQL trigger |
Custom API endpoint → application merge logic |
| Schema init at startup | 00-schema.sql executed automatically on fresh volume |
Requires separate Drizzle migration pipeline |
4. Implementation Effort¶
These are architecture-level observations about what each system requires Bluefly to build and maintain:
PowerSync
- Requires a standard PostgreSQL publication and logical replication configuration
- Requires implementing uploadData() — a single backend hook that receives queued writes
- Conflict resolution is expressed in PostgreSQL (triggers, constraints, or application logic)
- No custom offline queue, retry loop, or transport mechanism is required
ElectricSQL (V2) - The Shapes API synchronizes data reliably down to the client (read path) - For the write path, the developer must build a custom offline mutation layer — including local storage, a retry loop, and a reconnect drain worker - This is not a limitation of ElectricSQL; it reflects its current architectural scope - The write infrastructure required to support our offline workflow is substantially more complex than the PowerSync equivalent and would become Bluefly-owned infrastructure
5. Ownership Result¶
This table directly answers who owns each capability in the chosen architecture:
| Capability | Owner |
|---|---|
| PostgreSQL schema | Bluefly |
| World Model entity definitions | Bluefly |
| Conflict policy (LWW, business rules) | Bluefly |
| Business validation logic | Bluefly |
| Authorization | Bluefly |
| Attachment metadata | Bluefly (PostgreSQL) |
| Offline write queue | PowerSync |
| Local SQLite state | PowerSync |
| Retry logic | PowerSync |
| Reconnect handling | PowerSync |
| Sync transport | PowerSync |
| File / photo storage | Storage provider (S3-compatible) |
This ownership split aligns precisely with the Bluefly platform principle: own the domain intelligence, adopt the infrastructure.
6. Decision¶
Adopt: PowerSync
We evaluated both candidates, scaffolded working environments, and executed the full offline workflow against both systems. PowerSync was selected because it minimizes Bluefly-owned synchronization infrastructure while preserving PostgreSQL as the authoritative system of record. ElectricSQL in its current V2 architecture does not yet address the offline write path, which would require Bluefly to build and maintain custom client-side queuing infrastructure. That is not an acceptable ownership position.
7. Record Frozen¶
This document is an architectural decision record. The comparison phase is complete. Further work on offline sync should be tracked as implementation issues against the PowerSync integration, not as revisions to this document.