Offline Sync Engine Implementation

This document logs the implementation details, database schema mapping, and event listeners configured to achieve offline-first background synchronisation for the ERO PWA application.

Architectural Overview

The application utilizes a storage dual-layer strategy to keep operations instant and responsive even under congestion or total loss of network connectivity:

graph TD
    A[Client UI Action] -->|Instant Write| B[(Dexie.js IndexedDB)]
    B -->|Check Connection| C{navigator.onLine}
    C -->|No| D[Mark: sync_status = 'pending']
    C -->|Yes| E[POST /api/preferences]
    E -->|Success| F[Mark: sync_status = 'synced']
    E -->|Failure| D
    D -->|Wait for online event| G[Trigger pushLocalChanges]
    G --> E

---

1. Local Database Schema Setup

The client-side IndexedDB configuration was updated in db.ts to match the required schemas:

• festivals: stores festival master configuration.
• stages: stores festival stage metadata.
• bands: stores schedules and slot information.
• user_routes: local-first table recording user preferences and schedules, including:
  • id: unique string GUID
  • band_id: target band foreign key
  • priority: user score/rating
  • sync_status: 'synced' | 'pending' | 'failed'
  • updated_at: local timestamp for conflict resolution

---

2. Sync Logic (Client-Side)

Created the synchronization module at sync.ts:

• hydrateSchedule():
  • Fetches the current master data from /api/data.
  • Performs an atomic transaction to wipe and rewrite local tables (festivals, stages, bands) to guarantee consistency.
• pushLocalChanges():
  • Queries IndexedDB for all records in user_routes where sync_status is 'pending'.
  • Sequentially posts these changes to /api/preferences.
  • Marks them as 'synced' on 200 OK response or 'failed' to queue them for retry on next network state change.

---

3. UI Network State Listeners

Wired client triggers in index.astro:

• Event Listeners:
  • window.addEventListener('online'): Automatically triggers pushLocalChanges() and updates UI connectivity badge status.
  • window.addEventListener('offline'): Sets UI status indicator to local cache mode.
• Initial Hydration:
  • Checks if the IndexedDB is empty. If empty and network is available, it invokes hydrateSchedule() to retrieve data.

---

4. Edge API Preferences Support

Added preference syncing support to the Cloudflare Worker in index.ts:

• Endpoint: POST /api/preferences

  • Parses band_id and priority from JSON.

  • Inserts/upserts the row using an ON CONFLICT clause against the active festival:

    INSERT INTO user_preferences (festival_id, band_id, priority_level)
    VALUES (?, ?, ?)
    ON CONFLICT(festival_id, band_id) DO UPDATE SET priority_level = excluded.priority_level;

---

5. Verification Status

• Type Checking: Clean compiler checks on both workspace modules (api/ and app/).
• PWA Packaging: Successfully ran npx astro build, which output static assets and registered the workbox service worker.