187 lines
7.9 KiB
Markdown
187 lines
7.9 KiB
Markdown
# Sync operator runbook
|
||
|
||
> **Audience:** systems administrators and on-site IT staff.
|
||
> **Scope:** the offline-first bidirectional sync between the local LAN server
|
||
> (better-sqlite3) and the Supabase cloud instance.
|
||
|
||
This document explains what a sync conflict is, when one happens, how to
|
||
resolve it from `/admin/sync-logs`, when to escalate, and what the diagnostic
|
||
chart tells you.
|
||
|
||
---
|
||
|
||
## 1. The data model
|
||
|
||
Every table that participates in sync uses four sync-control columns (see
|
||
`server/src/database/init.js` for the schema and `.harness/docs/conventions.md`
|
||
for the contract):
|
||
|
||
```sql
|
||
uid TEXT UNIQUE, -- stable cross-system identifier
|
||
last_synced_at DATETIME, -- last successful push/pull timestamp
|
||
sync_status TEXT CHECK IN -- 'synced' | 'pending' | 'conflict'
|
||
('synced','pending','conflict')
|
||
DEFAULT 'pending',
|
||
is_deleted INTEGER DEFAULT 0, -- soft-delete tombstone (never hard-delete)
|
||
```
|
||
|
||
Tables that participate in sync are listed in
|
||
`server/src/services/SyncEngine.js` under `tablesToSync`, ordered by foreign
|
||
key dependency (parents first).
|
||
|
||
## 2. The sync cycle
|
||
|
||
`server/src/services/SyncEngine.js` runs every 30 seconds (configurable via
|
||
`/admin/settings` → `sync_interval_seconds`, floor of 5s):
|
||
|
||
1. **Push** — `POST` rows where `sync_status='pending'` to Supabase.
|
||
`pending` is set by every write in every controller
|
||
(`sync_status='pending'` next to every `INSERT`/`UPDATE`).
|
||
2. **Pull** — `GET` rows from Supabase where Supabase's `updated_at > our
|
||
last_synced_at`. Apply locally.
|
||
3. **Conflict detection** — if the same `uid` changed on both sides between
|
||
pull cycles (Supabase's version newer than ours AND ours still
|
||
`sync_status='pending'`), mark the row `sync_status='conflict'` and stop
|
||
touching it until an admin resolves.
|
||
|
||
If `SUPABASE_URL` or `SUPABASE_KEY` env vars are empty/missing, both push and
|
||
pull become **no-ops** — the app keeps working fully offline. The sync engine
|
||
logs `online=false` in `audit_logs`.
|
||
|
||
## 3. What is a `sync_status='conflict'` row?
|
||
|
||
A conflict happens when:
|
||
|
||
- The same row was edited on the LAN (a teacher in the school), **and**
|
||
- The same row was edited on the cloud (an admin with Supabase Studio access),
|
||
**and**
|
||
- Both changes happened within the same sync window, **and**
|
||
- They differ.
|
||
|
||
The sync engine flags the LAN copy with `sync_status='conflict'` and stops
|
||
trying to push/pull until a human decides. The cloud copy stays at its last
|
||
known state.
|
||
|
||
The default policy is **server-wins**: when an admin clicks "Resolve
|
||
(server-wins)", the LAN row is overwritten with the cloud's version and
|
||
`sync_status` is cleared back to `'synced'`. In offline mode (`SUPABASE_KEY`
|
||
empty), the resolve endpoint still clears the conflict flag locally but does
|
||
not contact Supabase — the row is marked pending so it will be picked up
|
||
later when connectivity returns.
|
||
|
||
## 4. The `/admin/sync-logs` UI
|
||
|
||
Route: `/admin/sync-logs`. Gated to `systems_admin` only (other roles get
|
||
403 from the `<ProtectedRoute allowedRoles>`).
|
||
|
||
Four tabs:
|
||
|
||
### Tab 1 — Sync Log
|
||
|
||
The last 100 audit log entries from `sync_logs`: which table, which row,
|
||
which direction (push/pull), success/failed/retried, error message. Shows the
|
||
last 24h of sync activity.
|
||
|
||
### Tab 2 — Sync Conflicts
|
||
|
||
Live list of every row across every `tablesToSync` table where
|
||
`sync_status='conflict'`. Columns:
|
||
|
||
| Column | Source |
|
||
|---|---|
|
||
| Table | `name` field from each `tablesToSync` entry |
|
||
| Row uid | the offending row's `uid` |
|
||
| Conflict at | `updated_at` of the LAN row |
|
||
| Status | always `conflict` here |
|
||
|
||
Each row has a **Resolve (server-wins)** button. Clicking it:
|
||
|
||
1. Optimistically removes the row from the UI.
|
||
2. Calls `POST /api/sync/conflicts/resolve` with `{ tableName, uid }`.
|
||
3. Server delegates to `SyncEngine.resolveConflict(table, id)` which:
|
||
- Clears `sync_status='conflict'` and sets `last_synced_at=now`.
|
||
- Writes an audit log entry of type `SYNC_CONFLICT_RESOLVED`.
|
||
4. The UI re-fetches `/api/sync/conflicts` to verify the resolution.
|
||
5. On error, the optimistic removal is rolled back.
|
||
|
||
### Tab 3 — Latency
|
||
|
||
Polled every 5 seconds. Three KPI tiles + one line chart.
|
||
|
||
- **Push duration** — last `pushMs` from `SyncEngine.getSyncStatus()`.
|
||
- **Pull duration** — last `pullMs`.
|
||
- **Total pending** — sum of `sync_status='pending'` rows across the synced
|
||
tables.
|
||
|
||
The line chart (`Recharts`) shows the rolling last 60 samples of
|
||
`pushMs`, `pullMs`, and `totalPending` (each on its own y-axis scale, sharing
|
||
time on x). The implementation lives in `useSyncLogsStore.fetchStatus()` —
|
||
`statusHistory` is capped at 60 entries to bound memory.
|
||
|
||
A healthy school typically shows push/pull in the 100–500ms range and
|
||
`totalPending` returning to ~0 within a minute of activity. If
|
||
`totalPending` climbs steadily without falling, either the network is down
|
||
or Supabase is throwing 5xx — check the green/red KPI tile on top.
|
||
|
||
### Tab 4 — Pending by table
|
||
|
||
A bar chart of the **top 10** synced tables by count of `sync_status='pending'`
|
||
rows. When everything is green, this chart is empty. If one table has a tall
|
||
bar, that is usually the source of the latency — go to that table in the
|
||
admin section and confirm that the most recent writes succeeded. If they did,
|
||
`pending` count should drain within one cycle.
|
||
|
||
## 5. Manual diagnostic procedure
|
||
|
||
When a teacher reports that "their data didn't sync":
|
||
|
||
1. Log in as `systems_admin`. Open `/admin/sync-logs`.
|
||
2. Look at the **Latency** tab. Is `totalPending > 0`? Is `Sync Running` red?
|
||
3. If `Sync Running` is red and stays red, the sync engine has stalled.
|
||
Check `logs/sync.log` on the server (or `audit_logs` rows with `type` like
|
||
`'SYNC'`).
|
||
4. If `Latency` is fine, go to **Pending by table** — find the table the
|
||
teacher was editing.
|
||
5. If pending drains after one cycle, the issue was transient. Otherwise:
|
||
6. Switch to the **Conflicts** tab. Click **Resolve (server-wins)** on the
|
||
row if any. (This is the only manual action allowed by the UI; do not
|
||
edit the DB directly unless you've stopped the server.)
|
||
7. If conflicts keep re-appearing on the same row, escalate — there is
|
||
probably a duplicate-write somewhere in a controller that is not
|
||
properly setting `sync_status='pending'`. Open a bug against the table's
|
||
controller.
|
||
|
||
## 6. Manual sync run
|
||
|
||
If the dashboard is online but stalled, you can trigger a single sync cycle
|
||
without waiting for the 30s timer:
|
||
|
||
```bash
|
||
curl -X POST http://localhost:3001/api/sysadmin/sync/trace \
|
||
-H "Authorization: Bearer $ADMIN_JWT"
|
||
```
|
||
|
||
The response returns the captured stdout/stderr and a per-table count of
|
||
pushed/pulled rows. The endpoint returns 409 if a cycle is already running
|
||
— wait for the next cycle or kill the stalled process.
|
||
|
||
## 7. Escalation matrix
|
||
|
||
| Symptom | Action |
|
||
|---|---|
|
||
| One-off conflict that resolves | Self-clear via the Conflicts tab. |
|
||
| Same row keeps conflicting | Open a bug against the controller for that table. |
|
||
| `totalPending` climbing for >1h | Check Supabase status page; check `logs/sync.log`. |
|
||
| Offline mode (cloud unreachable) | The LAN app keeps working; `sync_status='pending'` rows accumulate. They auto-drain when cloud returns. |
|
||
| DB corruption suspected | Use `bash scripts/backup/verify-restore.sh latest` to confirm the latest backup boots. If that fails, see `docs/BACKUP.md`. |
|
||
|
||
## 8. Related files
|
||
|
||
- `server/src/services/SyncEngine.js` — the engine itself
|
||
- `server/src/controllers/sync.controller.js` — `/api/sync/conflicts`, `/api/sync/conflicts/resolve`, `/api/sync/status`, `/api/sync/logs`
|
||
- `client/src/store/syncLogs.ts` — Zustand slice (`fetchLogs`, `fetchConflicts`, `resolveConflict`, `fetchStatus`)
|
||
- `client/src/pages/admin/SyncLogs.tsx` — the admin UI with 4 tabs
|
||
- `client/src/store/syncLogs.test.ts` — 8 vitest specs covering all store actions
|
||
- `client/e2e/sync-logs.spec.ts` — 4 Playwright specs covering tab navigation and resolve flow
|
||
- `docs/BACKUP.md` — restore / disaster-recovery runbook
|