Sync System

Overview

The sync system keeps store databases in sync with HQ. Master data flows down from HQ to stores (pull), while transactional data flows up from stores to HQ (push). A WebSocket connection provides real-time notifications so stores pull immediately when data changes.

HQ Server (source of truth for master data)
    |
    +-- WebSocket server (/api/v1/sync/ws)
    |       |
    |       +-- Store 1 WS client (receives notifications)
    |       +-- Store 2 WS client (receives notifications)
    |
    +-- GET /api/v1/sync/pull  (stores fetch master data changes)
    |
    +-- POST /api/v1/sync/push (stores send local data to HQ)

Pull: HQ to Store

What Gets Pulled

Stores pull these entity types from HQ, in dependency order (parent tables before children):

1. stores
2. departments
3. tax_rates
4. tax_groups
5. tax_group_rates
6. suppliers
7. tenders
8. products
9. users
10. roles
11. permissions
12. role_permissions
13. user_store_roles
14. customers
15. product_suppliers
16. product_specials
17. sales_reps
18. store_product_dynamic (filtered to this store)
19. purchase_orders (filtered to this store)
20. purchase_order_items (filtered to this store's POs)

How Pull Works

Each entity type has a sync_version column. When a row is updated at HQ, its sync_version is incremented. The store tracks the last version it pulled for each entity type in the sequences table.

Pull cycle:

1. Store reads its last sync version for entity type X from sequences table
2. Sends GET /api/v1/sync/pull?entity_type=X&since_version=N
3. HQ returns all rows where sync_version > N and the latest version number
4. Store upserts each row into its local database (insert or update on conflict)
5. Store updates its sequences entry with the new latest version

Store DB                    HQ Server                    HQ DB
   |                            |                           |
   |-- GET /pull?since=5 ------>|                           |
   |                            |-- SELECT * WHERE v > 5 -->|
   |                            |<-- rows with v=6,7,8 -----|
   |<-- { entities, v=8 } -----|                           |
   |                            |                           |
   |-- upsert rows locally     |                           |
   |-- sequences['X'] = 8      |                           |

Special cases during pull:

• When products are pulled, store_product_dynamic rows are auto-created for the pulling store (with default prices from the product)
• permissions and role_permissions do a full pull (no sync_version filtering) since they are small and don't have a sync_version column
• store_product_dynamic, purchase_orders, and purchase_order_items are filtered to the requesting store's ID

Upsert Logic

The store's pull service uses different upsert strategies:

• Standard tables (products, departments, etc.): INSERT ... ON CONFLICT (id) DO UPDATE SET ...
• user_store_roles: Upsert by id, updating user/store/role columns
• role_permissions: ON CONFLICT (role_id, permission_id) DO NOTHING
• store_product_dynamic: Upsert by (store_id, product_id), updates prices and reorder levels but preserves local quantity

Source: apps/store-server/src/sync/pull-service.ts

Push: Store to HQ

What Gets Pushed

Stores push their locally-created transactional data:

• sales (with sale_items and sale_payments)
• inventory_adjustments
• register_sessions
• transfers
• customers (store-created customers)
• purchase_orders and purchase_order_items

Outbox Pattern

The push system uses a transactional outbox to guarantee delivery:

1. When a sale is created, the store server inserts both the sale record and a sync_outbox entry in the same database transaction
2. A background sync service periodically queries unprocessed outbox entries
3. Entries are batched and sent to HQ via POST /api/v1/sync/push
4. HQ returns per-entity results (ok, error, or unknown_type)
5. Successful entries are deleted from the outbox and the source entity is marked as synced = true
6. Failed entries have their attempts counter incremented and lastError recorded

Store DB                     HQ Server                    HQ DB
   |                             |                           |
   |-- POST /push {envelopes} ->|                           |
   |                             |-- process each entity --->|
   |                             |<-- per-entity results ----|
   |<-- { results } ------------|                           |
   |                             |                           |
   |-- delete OK from outbox    |                           |
   |-- increment failed attempts|                           |

Push Envelope Format

{
  "envelopes": [
    {
      "store_id": "uuid",
      "entity_type": "sales",
      "entity_id": "uuid",
      "action": "create",
      "data": { ... full entity data ... },
      "timestamp": "2024-01-15T10:30:00.000Z"
    }
  ]
}

Maximum 1000 envelopes per push request.

Conflict Resolution

HQ uses last-write-wins conflict resolution. When upserting a pushed entity:

1. Check if the entity already exists in HQ DB
2. If it does not exist, insert it
3. If it exists, compare timestamps: if the remote timestamp >= existing createdAt, the remote wins and the row is updated

Source: apps/hq-server/src/modules/sync/routes.ts (shouldRemoteWin function)

Side Effects on Push

When HQ processes pushed entities, it also updates the store_product_dynamic table:

• Sale items: Decrements quantity, updates lastSoldAt
• Inventory adjustments: Adjusts quantity, updates lastReceivedAt for "received" reason
• Both: Increments syncVersion and updates lastSyncedAt

Dead Letter Queue

If an outbox entry fails to push after 10 attempts, it becomes "dead-lettered":

• It is no longer picked up by the regular push cycle (WHERE attempts < 10)
• It remains in the outbox table for inspection
• Console errors are logged: [SYNC DEAD LETTER] entity_type/entity_id failed N times: error

Dead-lettered entries can be resolved from the HQ Sync Dashboard.

WebSocket Connection

Connection Flow

1. Store server starts a WebSocket client on boot (startWsClient())
2. Connects to ws://hq-server:3000/api/v1/sync/ws?sync_token=JWT
3. HQ validates the sync token and registers the store connection
4. Store sends heartbeat every 30 seconds with storeCode and uptime
5. HQ monitors pong responses; disconnects stores that miss pongs for 40+ seconds

Authentication

Two methods:

1. Query parameter: ?sync_token=JWT in the WebSocket URL (preferred)
2. First message: If no query parameter, the store has 10 seconds to send { type: "auth", sync_token: "JWT" }

Heartbeat Protocol

Store to HQ (every 30s):

{ "type": "heartbeat", "storeCode": "ST01", "uptime": 3600, "timestamp": "..." }

HQ to Store (on data change):

{ "type": "sync:notify", "entityType": "products" }

When the store receives a sync:notify message, it immediately pulls that entity type from HQ, ensuring near-real-time updates.

Reconnection

If the WebSocket disconnects, the store client reconnects with exponential backoff with jitter:

• Base delay: 1 second
• Max delay: 60 seconds
• Formula: min(1000 * 2^attempts + random(0-1000), 60000)
• Attempt counter resets on successful connection

Source: apps/store-server/src/sync/ws-client.ts

Connection Monitoring

HQ tracks connected stores in an in-memory Map<string, ConnectedStore>. The sync dashboard queries this to show live connection status:

interface StoreConnectionInfo {
  storeId: string;
  connected: boolean;
  storeCode: string;
  uptime: number;
  lastHeartbeatAt: string | null;
  connectedAt: string | null;
  secondsSinceHeartbeat: number | null;
}

Source: apps/hq-server/src/websocket/server.ts

Sync Status Tracking

sync_status Table (HQ)

Aggregate counters per (store_id, entity_type, direction). Upserted on every sync operation:

Column	Description
last_success_at	Last successful sync timestamp
last_error_at	Last error timestamp
last_error	Last error message
pending_count	Number of pending/failed entries
total_pushed	Cumulative push count
total_pulled	Cumulative pull count

This table never grows unbounded since it upserts on the unique constraint (store_id, entity_type, direction).

sync_log Table (HQ)

Detailed per-entity log. Status values: success, failed, skipped, resolved, retried.

Failed entries remain until an HQ administrator resolves them from the Sync Dashboard (either retry or manual resolve).

Sync Dashboard (HQ)

The HQ web client includes a Sync Dashboard at /hq/sync that shows:

• All stores with their connection status (connected/disconnected)
• Per-store sync status (last success, error count)
• List of failed sync entries with error details
• Actions: Retry (re-process the payload) or Resolve (mark as handled)
• Retry All: Bulk retry all failed entries for a store

Dashboard API Endpoints

Endpoint	Description
GET /api/v1/sync/dashboard	All stores with status and connection info
GET /api/v1/sync/errors	Failed sync log entries (paginated, filterable by store)
POST /api/v1/sync/retry/:id	Re-process a failed entry's payload
POST /api/v1/sync/resolve/:id	Mark as resolved without retry
POST /api/v1/sync/retry-all	Retry all failed entries (optional store filter)

Notification Queue

HQ uses BullMQ (backed by Redis) to queue WebSocket notifications. When a push is processed successfully, notifications are queued and sent to connected stores. This decouples the HTTP response from WebSocket delivery.

If Redis is unavailable, the notification worker degrades gracefully (logs a warning, sync still works via periodic polling).

Sync Service (Store)

The store's sync service (apps/store-server/src/sync/sync-service.ts) runs a periodic loop:

1. Pull all entity types from HQ (pullAll())
2. Push outbox entries to HQ (pushOutbox())
3. Wait SYNC_INTERVAL_MS (default 30 seconds)
4. Repeat

This runs independently of the WebSocket notifications, which trigger immediate pulls for specific entity types.