Architecture guide for PGlite + ElectricSQL + Drizzle ORM hybrid sync. Use when designing offline-first apps where ElectricSQL handles server-to-client sync and REST API handles client-to-server writes with Drizzle ORM shared across frontend and backend.
IMPORTANT: Read project documentation first
When this skill is activated, immediately read these documentation files from the project:
# Core architecture documentation (MUST READ)
read docs/offline/02-arquitectura.md # Stack overview and patterns
read docs/offline/03-flujo-sync.md # Data flow timelines and sequences
read docs/offline/04-decisiones.md # Architecture decision records (ADRs)
# Reference documentation (as needed)
read docs/offline/01-entidades.md # Entity inventory for offline support
read docs/offline/05-migracion.md # Migration guide from TanStack DB
read docs/offline/06-troubleshooting.md # Common issues and solutions
read docs/offline/07-testing.md # Testing strategies
These documents contain:
Always reference these docs when:
Architecture pattern: PGlite local-first, Custom Sync Queue for writes
IMPORTANT: Avileo uses a custom sync service with a PGlite table (
sync_operations) for the write queue - NOT IndexedDB. See REFERENCE.md for the actual implementation patterns.
CRITICAL: All IDs MUST be valid UUIDs. PostgreSQL will reject non-UUID strings.
┌─────────────────────────────────────────────┐
│ CLIENT (Browser) │
│ ├─ React/Vue/Svelte UI │
│ ├─ Drizzle ORM (queries) │
│ ├─ PGlite (local Postgres) │
│ ├─ ElectricSQL (read sync) │
│ └─ sync_operations table (write queue) │
└──────────────────┬──────────────────────────┘
│
Electric Sync (reads)
│
┌──────────────────┼──────────────────────────┐
│ SERVER │ │
│ ├─ REST API ←───┘ (writes) │
│ ├─ Drizzle ORM │
│ ├─ SyncEngine + Handlers (framework) │
│ └─ PostgreSQL ←── Electric captures changes│
└─────────────────────────────────────────────┘
The backend sync system uses a handler-based framework pattern:
packages/backend/src/services/sync/
├── sync.service.ts # Thin orchestrator (103 lines)
├── sync-logger.ts # Correlation tracking (400 lines)
├── schemas/
│ └── index.ts # Zod validation schemas (169 lines)
├── types.ts # Shared types
├── framework/
│ ├── SyncEngine.ts # Batch processing + SAVEPOINTs (254 lines)
│ ├── SyncPipeline.ts # 3-stage: validate structure → business → execute (71 lines)
│ ├── ConflictResolver.ts # Per-entity conflict checking (130 lines)
│ ├── HandlerRegistry.ts # Handler factory registration (35 lines)
│ └── types.ts # Framework types (75 lines)
└── handlers/
├── BaseSyncHandler.ts # Base handler with logging (139 lines)
├── SaleSyncHandler.ts # Sales: create/update/delete (236 lines)
├── SaleItemSyncHandler.ts # Sale items: create/update/delete (179 lines)
├── CustomerSyncHandler.ts # Customers (101 lines)
├── AbonoSyncHandler.ts # Payments (76 lines)
└── DistribucionSyncHandler.ts # Distributions (111 lines)
ISyncHandler interfaceoperation.entityId used as the actual entity ID on serverBEGIN TRANSACTION
SAVEPOINT sp_op_0
Process operation 0 (insert sale)
RELEASE SAVEPOINT sp_op_0 -- success
SAVEPOINT sp_op_1
Process operation 1 (insert sale_item)
ERROR! (e.g., duplicate)
ROLLBACK TO SAVEPOINT sp_op_1 -- recover, tx still usable
SAVEPOINT sp_op_2
Process operation 2 (insert sale_item)
RELEASE SAVEPOINT sp_op_2 -- success
COMMIT
Without SAVEPOINTs, PostgreSQL enters "aborted transaction" state on any error, killing all subsequent operations in the batch.
User Action → Service.create() → PGlite INSERT + queueSync()
↓
sync_operations table (PGlite)
↓
SyncService.processPending() (every 30s)
↓
Group operations by sync_group_id
↓
POST /api/sync/batch (per group)
↓
Backend SyncEngine.processBatch()
↓
Update local status to 'completed' / 'failed'
Operations sharing the same syncGroupId are sent together:
processPending() fetches by group, sends as one batchQuestion: How will you share Drizzle schema between frontend and backend?
Options:
packages/shared/schema.tsConsiderations:
Question: Which tables need to sync to the client?
Pattern:
pg.electric.syncShapeToTable({
shape: {
table: 'customers',
where: `business_id = '${businessId}'`
},
table: 'customers',
primaryKey: ['id']
})
Decisions:
Question: How do you handle writes when offline?
Avileo uses Optimistic Local + Queue + Batch Sync:
sync_status='pending')sync_operations table (same PGlite DB)sync_group_id/api/sync/batch with grouped operationsQuestion: What happens if server data changes while user was offline?
Backend ConflictResolver: Per-entity version checking before processing. Electric handles reads: Server changes sync automatically to PGlite. Last-write-wins: On individual fields by timestamp.
@electric-sql/pglite and @electric-sql/pglite-syncdrizzle-orm in frontend and backenduseLiveQueryshape-config or shape registration, local PGlite table creation, backend tenant filter/proxy logic, and REPLICA IDENTITY FULL on PostgresPOST /api/sync/batch)sync_operations table)syncGroupId for multi-operation atomicitysales table (sync_status='pending')insert operation in sync_operations table/api/sync/batchoperation.entityId as ID)sales table (sync_status='pending')insert operation in sync_operations tablesync_group_id/api/sync/batch with grouped operationsuseLiveQuery(db.select().from(sales))syncShapeToTable({table: 'customers'})where: business_id = 'xyz'primaryKey in shape configprimaryKey: ['id']onError callbacksyncShapeToTable config is enoughREPLICA IDENTITY FULLsyncGroupId for all operations in a logical unitoperation.entityId as the actual row ID (SaleSyncHandler line 65)For the full checklist and examples, see REFERENCE.md in this skill.
| Aspect | Full Custom Sync | Electric + API (This Pattern) |
|---|---|---|
| Read Sync | Manual polling/pullChanges | Electric handles automatically |
| Write Sync | Manual pushChanges | Your API + PGlite queue |
| Offline Reads | Cached in PGlite | Cached in PGlite |
| Offline Writes | Complex queue logic | sync_operations table + batch API |
| Conflict Resolution | You implement | ConflictResolver + Electric |
| Batch Processing | Custom | SyncEngine + SAVEPOINTs |
| Complexity | High | Medium |
| Control | Full | Reads: Electric, Writes: You |
For implementation details, consult REFERENCE.md and EXAMPLES.md in this skill.