# Pisces Checklists v2 — Technical Documentation

Mobile-first, **offline-first** Progressive Web App for nightclub staff operations: opening/closing checklists, order entries with signatures, activity logs, and team management. Designed to work seamlessly on phones with or without an internet connection.

---

## 1. System Goals

| Goal | Why it matters |
|------|----------------|
| **Works offline** | Bar/club Wi-Fi is unreliable. Staff must be able to check off items, sign sheets, and enter counts without a connection. |
| **Cache-first shell** | App opens in <1s on every visit, even before the network responds. |
| **Conflict-safe sync** | Two staff editing the same item from different phones must not overwrite each other. |
| **Single PWA, no app store** | "Add to Home Screen" gives an app icon. No App Store review, no install fees. |
| **Authoritative server** | The server is always the source of truth. Local cache is a mirror, never a fork. |

---

## 2. Tech Stack

| Layer | Choice | Notes |
|-------|--------|-------|
| Runtime | Node.js 22.5+ | Required for `node:sqlite` (`DatabaseSync`) |
| Server framework | Express (CommonJS) | Same pattern as v1 |
| Database (server) | `node:sqlite` (`DatabaseSync`) | No native deps; built into Node |
| Database (client) | IndexedDB (via `idb` helper) | Native browser persistent storage |
| Frontend | Vanilla HTML/CSS/JS, ES modules | No framework — keeps the bundle small |
| Service Worker | Vanilla `sw.js` | Cache-first shell, network-first data |
| Auth | JWT (12h) + scrypt-hashed PINs | Server-side. Offline path uses SHA-256 `pin_check`. |
| Hosting | Render | Persistent disk at `/data` |

**Why no framework?** Checklists is small (~5 main screens). Vanilla keeps the JS bundle under 50KB and the service worker simple.

---

## 3. Folder Structure

```
PiscesChecklists/DevV2-Lite/
├── server.js                  Express app, routes, sync endpoints
├── package.json
├── data/                      Local SQLite (gitignored)
│   └── pisces.db
├── public/
│   ├── index.html             Single-page shell
│   ├── manifest.webmanifest   PWA manifest
│   ├── sw.js                  Service worker
│   ├── css/
│   │   └── app.css
│   ├── js/
│   │   ├── app.js             Main app logic + UI
│   │   ├── db.js              IndexedDB wrapper (idb)
│   │   ├── sync.js            Sync engine (pull + drain)
│   │   ├── auth.js            PIN auth (online + offline)
│   │   └── ui/
│   │       ├── checklist.js
│   │       ├── orders.js
│   │       ├── signatures.js
│   │       └── manager.js
│   ├── images/
│   │   └── apple-touch-icon.png
│   └── lib/
│       └── idb.min.js         Vendored IndexedDB helper
├── docs/
│   ├── TECHNICAL.md           (this file)
│   └── USER_GUIDE.md
└── Sync_Dev_to_Prod.command
```

---

## 4. Architecture Overview

```
┌──────────────────────────────────────────────────────────────────┐
│  PHONE (PWA)                                                      │
│                                                                    │
│   UI ─────► IndexedDB cache  ◄─────────┐                          │
│            ├─ templates/items                                      │
│            ├─ today_state    (per-item progress)    Sync Engine   │
│            ├─ order_entries  (counts + col_values)  ├─ pull       │
│            ├─ activity_log                          │  every 5min │
│            ├─ staff (with pin_check hash)           ├─ drain      │
│            └─ pending_queue  (offline writes)       │  every 1min │
│                                                     └─ on focus   │
│                                                                    │
│   Service Worker:                                                  │
│   ├─ Shell cache (HTML/CSS/JS/icons) — cache-first                 │
│   └─ /api/* — network-only (sync engine handles offline)           │
└──────────────────────────────────────────────────────────────────┘
                              │  HTTPS
                              ▼
┌──────────────────────────────────────────────────────────────────┐
│  RENDER (server)                                                   │
│   Express + node:sqlite                                            │
│   /data/pisces.db (persistent disk)                                │
│                                                                    │
│   Endpoints:                                                       │
│   ├─ POST /api/auth/login            PIN → JWT                     │
│   ├─ GET  /api/sync/snapshot         Full state pull               │
│   ├─ POST /api/sync/drain            Bulk apply pending writes     │
│   ├─ GET/POST/PATCH /api/orders/...  Per-item endpoints            │
│   └─ GET  /api/summary               HQ server-to-server feed      │
└──────────────────────────────────────────────────────────────────┘
```

### Two-tier read/write model

- **Optimistic UI**: Every action updates IndexedDB first. The UI re-renders from the cache. The user never waits for the network.
- **Sync engine**: Background pulls keep the cache fresh. Pending writes drain when online.
- **Server is authoritative**: When the snapshot pulls down, it overwrites local state for unsynced rows the user hasn't touched. The pending queue protects local edits until they're acknowledged.

---

## 5. Database Schema (Server)

All tables live in `/data/pisces.db`. Use `IF NOT EXISTS` so the same code creates the schema on first boot and on Render's persistent disk.

### `staff`
```sql
CREATE TABLE staff (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name   TEXT NOT NULL,
  role         TEXT NOT NULL CHECK(role IN ('DOOR','MANAGER','OWNER')),
  pin_hash     TEXT NOT NULL,                  -- scrypt
  pin_check    TEXT NOT NULL,                  -- SHA-256(salt || pin), for offline verify
  created_at   TEXT NOT NULL DEFAULT (datetime('now')),
  active       INTEGER NOT NULL DEFAULT 1
);
```

The hardcoded admin lives in the app code as `id=0`, never inserted into this table. Login flow checks `id=0` first, then falls back to the table.

### `templates`
```sql
CREATE TABLE templates (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  name         TEXT NOT NULL,
  shift        TEXT NOT NULL CHECK(shift IN ('open','close','maintenance','order')),
  active       INTEGER NOT NULL DEFAULT 1,
  display_order INTEGER NOT NULL DEFAULT 0
);
```

### `template_items`
```sql
CREATE TABLE template_items (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  template_id  INTEGER NOT NULL REFERENCES templates(id),
  label        TEXT NOT NULL,
  item_type    TEXT NOT NULL DEFAULT 'check',  -- check | count | text | photo
  display_order INTEGER NOT NULL DEFAULT 0,
  cols_json    TEXT                            -- columns config for order tables
);
```

### `progress`
One row per (template_item, business_date). Tracks check state and who did it.
```sql
CREATE TABLE progress (
  item_id       INTEGER NOT NULL REFERENCES template_items(id),
  business_date TEXT NOT NULL,                 -- YYYY-MM-DD, business day
  checked       INTEGER NOT NULL DEFAULT 0,
  checked_by    INTEGER REFERENCES staff(id),
  text_value    TEXT,
  count_value   INTEGER,
  updated_at    TEXT NOT NULL DEFAULT (datetime('now')),
  PRIMARY KEY (item_id, business_date)
);
```

### `order_entries`
For order/inventory templates with multi-column rows. **Insert-or-ignore + selective update** (never UPSERT all fields with nulls — see [feedback memory](../../../.claude/projects/-Users-mwboot-AI-Pisces/memory/feedback_checklists_order_saves.md)).
```sql
CREATE TABLE order_entries (
  item_id       INTEGER NOT NULL REFERENCES template_items(id),
  business_date TEXT NOT NULL,
  on_hand       TEXT,
  to_order      TEXT,
  checked       INTEGER NOT NULL DEFAULT 0,
  col_values    TEXT NOT NULL DEFAULT '{}',    -- JSON {colId: value}
  staff_id      INTEGER REFERENCES staff(id),
  updated_at    TEXT NOT NULL DEFAULT (datetime('now')),
  PRIMARY KEY (item_id, business_date)
);
```

### `signatures`
```sql
CREATE TABLE signatures (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  template_id   INTEGER NOT NULL REFERENCES templates(id),
  business_date TEXT NOT NULL,
  staff_id      INTEGER NOT NULL REFERENCES staff(id),
  png_blob      BLOB NOT NULL,                 -- lazy-fetched, never in snapshot
  signed_at     TEXT NOT NULL DEFAULT (datetime('now'))
);
```

### `activity_log`
```sql
CREATE TABLE activity_log (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  staff_id     INTEGER REFERENCES staff(id),
  action       TEXT NOT NULL,
  detail_json  TEXT,
  created_at   TEXT NOT NULL DEFAULT (datetime('now'))
);
```

### `settings`
Key-value config (admin PIN hash, business-day cutover hour, etc.).
```sql
CREATE TABLE settings (
  key   TEXT PRIMARY KEY,
  value TEXT NOT NULL
);
```

### `client_changes` (idempotency log)
Every successful drain is logged here. Used to deduplicate retried writes.
```sql
CREATE TABLE client_changes (
  client_change_id TEXT PRIMARY KEY,             -- UUID generated on phone
  staff_id         INTEGER,
  action           TEXT NOT NULL,
  payload_json     TEXT NOT NULL,
  applied_at       TEXT NOT NULL DEFAULT (datetime('now'))
);
```

---

## 6. Database Schema (Client / IndexedDB)

Database name: `pisces-checklists-v2`, version: 1.

| Object Store | Key | Purpose |
|--------------|-----|---------|
| `meta`              | string key       | Token, user, last sync timestamp, pin_check by staff |
| `templates`         | id (number)      | Cached templates |
| `template_items`    | id (number)      | Cached items |
| `progress`          | `[item_id, business_date]` | Today's checklist state |
| `order_entries`     | `[item_id, business_date]` | Order entry rows |
| `staff`             | id (number)      | For offline PIN auth (id, name, role, pin_check) |
| `activity_log`      | id (number)      | Last 50 entries |
| `pending_queue`     | client_change_id | Writes made offline, awaiting drain |

Pending queue row shape:
```js
{
  client_change_id: "uuid-v4",
  action: "save_progress" | "save_order_entry" | "sign" | "...",
  payload: { ... },
  created_at: "2026-04-29T22:15:30Z",
  attempts: 0,
  last_error: null,
}
```

---

## 7. API Reference

All endpoints except `/api/auth/login`, `/api/version`, `/api/manifest`, and `/api/summary` (HQ key) require `Authorization: Bearer <jwt>`.

### Auth

#### `POST /api/auth/login`
Body: `{ first_name, pin }`
Response: `{ token, staff: { id, first_name, role }, settings }`

For offline auth, the response also includes the staff list with `pin_check` for every active staff member. The phone caches these so future logins work offline.

```js
// Server
const pinCheck = sha256(STAFF_SALT + pin);
return res.json({
  token,
  staff: { id, first_name, role },
  settings: { ... },
  staff_directory: db.prepare(
    "SELECT id, first_name, role, pin_check FROM staff WHERE active=1"
  ).all(),
});
```

### Sync

#### `GET /api/sync/snapshot?since=<iso>`
Returns full state since the timestamp. If `since` is omitted, returns everything.

Response:
```json
{
  "now": "2026-04-29T22:00:00Z",
  "business_date": "2026-04-29",
  "templates":      [...],
  "template_items": [...],
  "progress":       [...],
  "order_entries":  [...],
  "activity_log":   [...],   // last 50
  "staff":          [...]    // active staff w/ pin_check
}
```

#### `POST /api/sync/drain`
Body:
```json
{
  "actions": [
    {
      "client_change_id": "uuid",
      "action": "save_progress",
      "payload": { "itemId": 12, "checked": true },
      "created_at": "2026-04-29T22:14:00Z"
    },
    ...
  ]
}
```

Server processes each action in order. For idempotency, server checks `client_changes` first — if `client_change_id` already exists, skip but return success.

Response:
```json
{
  "applied":    ["uuid-1", "uuid-2"],
  "skipped":    ["uuid-3"],     // already applied
  "failed":     [{ "id": "uuid-4", "error": "..." }],
  "snapshot":   { ... }         // post-drain state, optional
}
```

### Action types

| Action | Payload | Server effect |
|--------|---------|---------------|
| `save_progress`     | `{ itemId, checked, text_value?, count_value? }` | Upsert `progress` row (preserve any field not in payload) |
| `save_order_entry`  | `{ itemId, on_hand?, to_order?, checked?, col_values? }` | INSERT OR IGNORE + selective UPDATE; merge col_values |
| `sign`              | `{ templateId, png_base64 }` | Insert into `signatures`, log activity |
| `add_staff`         | `{ first_name, role, pin }` | Manager-only |
| `reset_pin`         | `{ staff_id, pin }` | Manager-only |
| `remove_staff`      | `{ staff_id }` | Manager-only |
| `set_admin_pin`     | `{ pin }` | Owner-only |

### HQ feed (server-to-server)

#### `GET /api/summary`
Header: `x-hq-key: <HQ_API_KEY>`
Returns today's status for the PiscesHQ dashboard. Same shape as v1.

---

## 8. Offline-First Sync Engine

### Pull cycle (snapshot)
```
on app start              → full snapshot
every 5 minutes (online)  → incremental snapshot (since last_sync_at)
on visibility change      → snapshot if last_sync > 30s ago
on /api/sync/drain success → apply returned snapshot if present
```

### Drain cycle (pending queue)
```
on app start (online)     → drain
every 1 minute (online)   → drain
on online event           → drain
on user action (online)   → optimistic write to IDB + immediate drain attempt
on user action (offline)  → write to IDB + enqueue for later drain
```

### Conflict resolution

The crux of multi-phone offline editing.

| Type | Strategy |
|------|----------|
| **Per-row scalar fields** (`progress.checked`, `progress.count_value`) | **Last-write-wins by `updated_at`**. Server compares incoming `created_at` against current `updated_at` and only applies if newer. Snapshot then propagates the winner. |
| **`col_values` (multi-field JSON)** | **Per-key merge**. Server reads existing JSON, merges incoming keys (incoming wins per-key), writes back. Two phones editing different columns of the same row both succeed. |
| **Signatures** | Append-only. Multiple sigs per template/date are allowed. |
| **Activity log** | Append-only. |

### Idempotency

Every queued action carries a UUID (`client_change_id`). Server's `client_changes` table makes drains safe to retry. If the phone gets back `applied`+`skipped` mixed, it removes both from the queue.

### Error handling

```js
// Phone-side drain
try {
  const result = await fetch("/api/sync/drain", { ... });
  for (const id of [...result.applied, ...result.skipped]) {
    await idb.delete("pending_queue", id);
  }
  for (const f of result.failed) {
    await idb.update("pending_queue", f.id, q => ({
      ...q,
      attempts: q.attempts + 1,
      last_error: f.error,
    }));
  }
} catch (networkErr) {
  // Leave queue intact, retry next cycle
}
```

After 5 failed attempts, the row is moved to a `dead_letter` flag and the manager UI shows a "Stuck syncs" badge.

---

## 9. Authentication

### Online flow
1. User enters first name + 4-digit PIN
2. `POST /api/auth/login` — server scrypt-verifies pin, issues JWT (12h)
3. JWT stored in `localStorage` and `meta` IDB store
4. Login response includes `staff_directory` with `pin_check` for every active staff
5. App caches `staff_directory` in IDB

### Offline flow
1. User enters first name + 4-digit PIN
2. App reads `staff_directory` from IDB
3. `pin_check_local = sha256(STAFF_SALT_PUBLIC + pin)`
4. Match against `staff_directory[name].pin_check`
5. On match: issue a **local-only token** with 12h expiry, marked `offline=true`
6. Local token grants UI access; any sync attempts queue actions instead of calling auth

The salt (`STAFF_SALT_PUBLIC`) is hardcoded per app and shipped in the bundle. It's not secret — its only job is to make rainbow-table attacks against the cached `pin_check` values harder. The real PIN stays in `pin_hash` (scrypt) on the server and is never sent to the phone.

### Admin PIN
- Default `0000`
- Stored hashed in `settings` table (`admin_pin_hash`)
- Hardcoded bypass: if entered PIN matches, issue OWNER token regardless of staff table
- Online-only by default; manager can flip a setting to make it offline-capable too

---

## 10. PWA Setup

### `manifest.webmanifest`
```json
{
  "name": "Pisces Checklists",
  "short_name": "Checklists",
  "start_url": "/?source=pwa",
  "display": "standalone",
  "background_color": "#160B24",
  "theme_color": "#3b0764",
  "icons": [
    { "src": "/images/icon-192.png", "sizes": "192x192", "type": "image/png" },
    { "src": "/images/icon-512.png", "sizes": "512x512", "type": "image/png" },
    { "src": "/images/apple-touch-icon.png", "sizes": "180x180", "type": "image/png" }
  ]
}
```

### Service Worker (`sw.js`)
- **Shell cache** (`pisces-checklists-shell-v{version}`) — cache-first for HTML, CSS, JS, icons
- **API requests** — never cached. Network-only. Sync engine handles offline.
- **Cache busting** — bump `version` in `sw.js` on every deploy (script in `package.json`)

```js
const SHELL_VERSION = "v__APP_VERSION__";  // replaced at build time
const SHELL_CACHE   = `pisces-checklists-shell-${SHELL_VERSION}`;
const SHELL_FILES   = [
  "/", "/index.html", "/css/app.css",
  "/js/app.js", "/js/db.js", "/js/sync.js", "/js/auth.js",
  "/lib/idb.min.js", "/manifest.webmanifest",
  "/images/apple-touch-icon.png",
];

self.addEventListener("install", e => {
  e.waitUntil(caches.open(SHELL_CACHE).then(c => c.addAll(SHELL_FILES)));
  self.skipWaiting();
});

self.addEventListener("activate", e => {
  e.waitUntil(
    caches.keys().then(keys =>
      Promise.all(keys.filter(k => k !== SHELL_CACHE).map(k => caches.delete(k)))
    )
  );
  self.clients.claim();
});

self.addEventListener("fetch", e => {
  const { request } = e;
  if (request.method !== "GET") return;
  if (new URL(request.url).pathname.startsWith("/api/")) return;  // no caching
  e.respondWith(
    caches.match(request).then(cached => cached || fetch(request))
  );
});
```

### Add to Home Screen (iOS)
- Login screen shows a banner with "Save Our App" instructions on first iPhone visit
- Hidden once `display-mode: standalone` is detected
- Icon and home-screen name controlled by `manifest.webmanifest` + `<link rel="apple-touch-icon">`

---

## 11. Business Date Logic

The business day at Pisces runs late — closing checklists at 4 AM are still part of "yesterday's" shift.

```js
// Business date = current date, but rolls over at 6 AM ET
function businessDate(now = new Date()) {
  const et = new Date(now.toLocaleString("en-US", { timeZone: "America/New_York" }));
  if (et.getHours() < 6) et.setDate(et.getDate() - 1);
  return et.toLocaleDateString("en-CA");  // YYYY-MM-DD
}
```

Both the server and phone compute this. The phone uses its local clock when offline; the server is authoritative when online. On the next snapshot, the server's `business_date` overrides any local computation for unsynced rows.

---

## 12. Environment Variables

| Var | Required | Default | Notes |
|-----|----------|---------|-------|
| `JWT_SECRET`        | yes | — | App refuses to start without it |
| `HQ_API_KEY`        | yes | — | Must match `CHECKLISTS_HQ_KEY` in PiscesHQ |
| `ADMIN_PIN`         | no  | `0000` | Initial admin PIN |
| `STAFF_SALT_PUBLIC` | yes | — | 32+ chars; ships in bundle, used for offline pin_check |
| `SQUARE_ACCESS_TOKEN` | no | — | Optional staff sync from Square |
| `DB_PATH`           | yes (Render) | `data/pisces.db` | `/data/pisces.db` on Render |
| `NODE_ENV`          | no  | `development` | `production` enables trust proxy + HTTPS redirect |
| `PORT`              | no  | `4500` | |

---

## 13. Render Deployment

- Persistent disk at `/data` (lowercase — Linux is case-sensitive)
- Auto-deploys on push to `main`
- Build: `npm install`
- Start: `node server.js`
- DNS: `checklists.pisc3s.com` (Squarespace Domains)
- Health check: `GET /api/version` → returns `{ version, deployedAt }`

### Disk layout on Render
```
/data/
  ├── pisces.db        # Main DB (created on first boot)
  ├── pisces.db-wal    # WAL — DO NOT touch from outside the app
  └── pisces.db-shm    # Shared memory — same
```

### Migration from v1
1. Export v1 DB to JSON via `node export-v1.js`
2. Run v2 server pointed at empty `/data/pisces.db` — schema auto-creates
3. `POST /api/admin/import-v1` with the JSON payload
4. Verify staff list, today's progress, and last 30 days of activity log
5. Cut DNS over

---

## 14. Local Development

```bash
cd PiscesChecklists/DevV2-Lite
npm install
cp .env.example .env       # fill JWT_SECRET, HQ_API_KEY, STAFF_SALT_PUBLIC
node server.js             # http://localhost:4500
```

Watch mode for the frontend bundle isn't needed — vanilla JS + service worker only require a hard reload (Cmd+Shift+R) when files change.

To clear the local PWA state on the phone during dev:
- Safari: Settings → Safari → Advanced → Website Data → remove `checklists.pisc3s.com`
- Chrome: chrome://serviceworker-internals → Unregister

---

## 15. Testing Checklist (manual)

Before each deploy:

- [ ] Login with first name + PIN works (online)
- [ ] Toggle airplane mode → relogin works (offline path uses pin_check)
- [ ] Check off an item offline → appears as pending → drains on reconnect
- [ ] Edit `col_values` from two phones offline (different columns) → both apply on sync, no overwrites
- [ ] Sign a checklist offline → signature uploads on reconnect
- [ ] Add staff (manager) → new staff can log in offline immediately after their first online sync
- [ ] PWA install on iPhone → opens to login screen, displays in standalone mode
- [ ] Service worker version bump → old cache cleared on next visit
- [ ] HQ summary endpoint returns today's data with correct `x-hq-key` header
- [ ] Admin PIN reset reflects in offline auth on next sync

---

## 16. Known Limitations

1. **Signatures don't sync offline** — they upload from the queue but the local UI shows "pending sig" until drain succeeds. Trade-off: blob payloads in IDB queue use too much storage if many sigs are queued.
2. **One business date per phone** — if a phone is offline across the 6 AM cutover, its queued writes use the date the user opened the app. Server respects this on drain.
3. **No real-time collaboration** — two phones see each other's edits only after the next pull cycle (max 5 min). Not a chat app.
4. **Storage limits** — iOS Safari evicts IndexedDB after ~30 days of inactivity. Standalone (PWA-installed) mode is exempt; web-tab mode is not. Tell users to install.
5. **No push notifications** — out of scope for v2. v3 candidate.