# Offline-First Architecture

## Overview

SupplierPulse uses a progressive offline-first architecture. The primary use case is BMW factory floor assessments on iPads where WiFi coverage is unreliable. The architecture is layered:

1. **Phase 1** — Foundation: IndexedDB schema (Dexie), connection detection, basic local read/write helpers
2. **Phase 2** — Write queue + sync engine: FIFO queue → Supabase push, pull from Supabase, LWW conflict resolution
3. **Phase 3** — Repository layer: generic `createRepository<T>` factory, module-specific repos, migrate assessment + planning modules
4. **Phase 4** — Remaining modules + PWA: projects module, account cache, analytics offline banner, service worker, sync indicator

---

## Stack

| Layer | Technology |
|---|---|
| Local database | [Dexie](https://dexie.org) v4 (IndexedDB wrapper) |
| React integration | `dexie-react-hooks` — `useLiveQuery` for reactive local reads |
| Connection state | Custom React context (`OnlineStatusProvider`) using `window online/offline` events |
| Remote database | Supabase (PostgreSQL + Realtime) |
| PWA | Service Worker (`/sw.js`) — network-first for APIs, cache-first for static assets |

---

## File Structure

```
lib/offline/
  types.ts          — Shared types (SyncStatus, SyncQueueEntry, SyncResult, SyncConflict, …)
  db.ts             — Dexie schema: all IndexedDB stores + getDB() singleton + getStoreByName()
  registry.ts       — Entity registry: maps table names → offline config (TTL, queueWrites, …)
  connection.tsx    — OnlineStatusProvider context + useOnlineStatus() / useIsOnline() hooks
  local-store.ts    — Cache metadata helpers: markCacheFetched, isCacheStale, getLastFetchedAt
  queue.ts          — Write queue API: enqueue, markCompleted, markFailed, retryFailed, …
  sync-engine.ts    — Push (queue → Supabase) + pull (Supabase → local), fullSync(), LWW conflict
  sync-hooks.ts     — useSyncStatus, useSyncOnReconnect, useEntitySync React hooks
  repository.ts     — createRepository<T> generic factory (getAll, getById, create, update, remove, …)

lib/repositories/
  assessment-repo.ts — assessmentRepo, assessmentResponseRepo (custom upsert), assessmentQuestionRepo
  planning-repo.ts   — assignmentRepo, consultantRepo, appointmentTypeRepo
  project-repo.ts    — projectRepo, processStepRepo, measurementRepo, shiftRecordRepo, actionRepo
  account-repo.ts    — userProfileRepo (display fields only — never tokens/passwords)

components/offline/
  offline-provider.tsx    — Registers all entity configs + starts auto-sync on reconnect
  online-status-banner.tsx — Full-width banner: red (offline), green "restored" for 3 s
  sync-status-badge.tsx   — Compact badge: pending/error count with Dexie live query
  sync-status-bar.tsx     — Wider bar with retry button (used in module headers)
  sync-indicator.tsx      — Header icon: green ✓ / amber ⏳ / blue 🔄 / red ! + popover
  sw-registrar.tsx        — Registers /sw.js on client mount

public/
  manifest.json  — PWA manifest (name, icons, theme_color #0066B1, start_url /dashboard)
  sw.js          — Service worker (network-first APIs, cache-first static assets)
  icons/         — icon-192.png, icon-512.png (must be created manually)
```

---

## Entity Registry

The registry (`lib/offline/registry.ts`) defines which Supabase tables participate in offline sync.

| Table | Store | TTL | Queue Writes | Notes |
|---|---|---|---|---|
| `assessment_responses` | `assessmentResponses` | 30 min | ✅ | Core offline use case |
| `assessments` | `assessments` | 1 h | ✅ | Assessment metadata |
| `assessment_questions` | `assessmentQuestions` | 24 h | ❌ | Read-only catalog; joined select |
| `assignments` | `assignments` | 15 min | ✅ | Planning board cards |
| `consultants` | `consultants` | 1 h | ❌ | Admin-managed roster |
| `appointment_types` | `appointmentTypes` | 24 h | ❌ | Dropdown catalog |
| `projects` | `projects` | 1 h | ✅ | Visit projects |
| `process_steps` | `processSteps` | 30 min | ✅ | Steps within a project |
| `cycle_measurements` | `cycleMeasurements` | 30 min | ✅ | Per-step measurements |
| `shift_outputs` | `shiftOutputs` | 30 min | ✅ | Shift-level output records |
| `workshop_actions` | `workshopActions` | 15 min | ✅ | Action items |
| `qaf_uploads` | `qafUploads` | 1 h | ❌ | File upload metadata |
| `profiles` | `userProfiles` | 24 h | ❌ | Display fields only — never tokens |

---

## Database Versions (Dexie)

```
v1  Base schema: syncQueue, cacheMetadata, assessmentResponses, assessments, assignments
v2  Added: syncConflicts
v3  Added: assessmentQuestions, consultants, appointmentTypes
v4  Added: projects, processSteps, cycleMeasurements, shiftOutputs, workshopActions, qafUploads, userProfiles
```

**Rule:** Never modify an existing version block. Always add a new `this.version(N+1).stores({...})`.

---

## Sync Flow

```
User writes → createRepository.create/update/remove
                    ↓
            Optimistic local write (IndexedDB)
                    ↓
            enqueue({ table, operation, payload, recordId })
                    ↓  (fire-and-forget if online)
            startSync() — drains the queue in FIFO order
                    ↓
            Supabase RPC (insert / update / upsert / delete)
                    ↓ success
            markCompleted(id)  +  updateLocalRecordStatus → 'synced'
                    ↓ failure
            markFailed(id, error)  [retried up to MAX_RETRY_COUNT=5]
                    ↓ permanently failed
            permanentlyFailed = true  (shown in sync indicator)
```

### Pull flow

```
fullSync() / useSyncOnReconnect / manual "Jetzt synchronisieren"
    ↓
pullByTableName(table, since?)
    ↓
supabase.from(table).select(config.pullSelect ?? '*').gte('updated_at', since?)
    ↓
store.bulkPut(rows)  — marks all as _syncStatus: 'synced'
    ↓
markCacheFetched(key, ttlMs)
```

---

## Conflict Resolution (LWW)

On `upsert`, the sync engine compares `updated_at` timestamps:

- **Server wins** → server row is written to local store, conflict recorded in `syncConflicts`
- **Local wins** → local record status updated to `'synced'`, no record in `syncConflicts`

Conflicts are stored in the `syncConflicts` Dexie table and can be retrieved with `getSyncConflicts()`.

---

## PWA Strategy

| Request type | Cache strategy | Cache name |
|---|---|---|
| `/_next/static/**` + static assets | Cache-first | `sp-v1-static` |
| Supabase API + `/api/**` + `/_next/data/**` | Network-first | `sp-v1-api` |
| Navigation (HTML) | Network-first → `/offline` fallback | `sp-v1-api` |

Cache version is `sp-v1`. Bump to `sp-v2` (etc.) in `sw.js` when a breaking cache change is needed. Old caches are cleaned up in the `activate` event.

---

## Adding a New Offline Entity

1. **DB** (`lib/offline/db.ts`): Add a new `version(N+1).stores({...})` block. Add the `Table<LocalFoo, string>` declaration and a `LocalFoo` interface.
2. **Registry** (`lib/offline/registry.ts`): Call `registerOfflineStore({ table, storeName, ttlMs, queueWrites, primaryKey })`.
3. **Repository** (`lib/repositories/your-module-repo.ts`): `createRepository<LocalFoo>({ module, entityType, tableName })`. Extend with custom query methods as needed.
4. **OfflineProvider** (`components/offline/offline-provider.tsx`): Import the new repo file so its registrations run at startup.
5. **Sync engine** (`lib/offline/sync-engine.ts`): Add the new entity to `MODULE_ENTITY_TABLE` if you want `pullRemoteData(module, entityType)` to work.

---

## Per-Record Sync Badges

Each writable entity's card shows a colored dot based on `_syncStatus`:

| Status | Color | Symbol | Meaning |
|---|---|---|---|
| `synced` | — | (hidden) | Up to date |
| `pending` | `#D97706` amber | ↑ | Queued, not yet pushed |
| `error` | `#DC2626` red | ! | Push failed |
| `conflict` | `#7C3AED` purple | ⚠ | LWW conflict — server version was kept |

---

## Testing Checklist

- [ ] Toggle device to airplane mode, create/edit a record — verify `_syncStatus: 'pending'` in DevTools → Application → IndexedDB
- [ ] Restore connectivity — verify record appears in Supabase within seconds
- [ ] Open two browser tabs, edit the same record in both while offline, reconnect — verify conflict recorded in `syncConflicts`
- [ ] Force `permanentlyFailed` by using an invalid payload — verify red badge in sync indicator, "Fehlgeschlagene wiederholen" button appears
- [ ] Verify PWA install prompt on mobile (Chrome/Safari iOS)
- [ ] Verify `/offline` page is served from service worker cache while offline