# PWA SQLite WASM Offline-First Capabilities & Sync Engine Integration

> Branch: `feature/pwa-offline-sync` (worktree `.worktrees/feature-pwa-offline-sync/`)
> Owners: Architect / Frontend-Expert / Sync-Expert

## What landed

Comprehensive client-side offline capability, enabling persistent local database storage, caching of all critical offline assets (including the SQLite WASM binary), and automatic API fallback to local SQLite for all Zustand stores and pages when offline.

### PWA & Storage

- **`client/src/lib/db.worker.ts`** (new) — Created Web Worker that initializes `@sqlite.org/sqlite-wasm` inside a background thread. In the worker context, synchronous `OpfsDb` is fully supported, achieving persistent storage (data survives reloads/tab closures). Also handles schema creation and dynamic table migrations.
- **`client/src/lib/db.ts`** (modified) — Rewritten to spawn the Web Worker using the Vite-friendly `new URL('./db.worker.ts', import.meta.url)` syntax, communicating asynchronously via message passing. Exported `query` and `execute` APIs return Promises.
- **`client/vite.config.ts`** (modified) —
  - Added `.wasm` and `.webmanifest` file extensions to Workbox `globPatterns` so the offline database can initialize without network access.
  - Increased `maximumFileSizeToCacheInBytes` to 5MB to prevent the large compiled client-side JS bundle (2.45MB) from being excluded from pre-caching.
- **`client/serve.json`** (new) — Supplies `Cross-Origin-Opener-Policy: same-origin` and `Cross-Origin-Embedder-Policy: require-corp` headers to production deployment containers running `npx serve`.

### Sync Engine & API Fallback

- **`client/src/store/api.ts`** (modified) — Added an Axios response interceptor that catches network/offline errors (`ERR_NETWORK`). It routes queries and mutations to the SQLite WASM Web Worker dynamically based on request path and method, enabling transparent offline fallback for all Zustand stores and pages without any component modifications.
- **`client/src/lib/sync.ts`** (modified) —
  - Awaited all async local database operations (`query` / `execute`).
  - Sequentially processed incoming changes (`upsertRecord`) to prevent database transaction lock conflicts.
  - Expanded client-writable tables pushing queue to include `users`, `attendance`, `grades`, `submissions`, `payments`, `messages`, `events`, and `parent_students`.

### Pages

- **`client/src/pages/Attendance.tsx`** (modified) — Migrated local database operations (`query`, `execute`) to `await` the now asynchronous Promise-based database APIs.

---

## Verification & Build Results

- Executed `npm run build` inside the `client` folder.
- Output successfully compiled:
  - `dist/assets/db.worker-...js` (236.73 kB)
  - `dist/assets/sqlite3-...wasm` (864.75 kB) cached successfully.
  - Precache size: `19 entries (4858.25 KiB)` with **no warnings** (the 2.45MB JS bundle is successfully cached).
