OneOps🏖 Silent Day Active (SILENT-DAY-7 Store Bypass): Manager notified in Alerts · All store alerts bypassing off-duty staff directly to Manager.
🔔 2-Stage CX Escalation Chain (SILENT-DAY-8): CX Head (Silent Day) → Routes to CX Delegate (Config EA) → If Delegate Does Not Act Within SLA (15m/30m) → Auto-Escalates Directly to CEO (
/ceo).← Back to Oven Command
INTAKE-DESIGN-DAILY-OPS-KIOSK-ADHERENCE-AND-DATA-TRUST.md
# HARLEY'S FINE BAKING — OWNER EXECUTIVE READOUT (PACK B2)
> **🔒 ✅ RATIFIED & LOCKED BY OWNER (2026-10-22 23:58 IST) — Ratified by `cfo@harleys.com` (Gates A1–A4 100% Locked)**
## DAILY OPERATIONS: KIOSK FLEET, SHIFT ADHERENCE, DATA TRUST & BILINGUAL VOICE UX
- **Document ID:** `INTAKE-DESIGN-DAILY-OPS-KIOSK-ADHERENCE-AND-DATA-TRUST`
- **Synthesized Backlog Cluster:** **Pack B2 — Daily Operations, Store Kiosk Fleet, Shift & Checklist Adherence, Data Trust & Bilingual Voice UX**
- **Format Standard:** `O-040` Two-Page Royal Operational Readout (`Part 1: 0.5p Executive Summary + Right-Side Stage-Aware Sign-Off Box` · `Part 2: 1.5p Full Groomed Backlog, Industry Research, User Journeys, Domain Objects & Peer-Review Verdict` · `Part 3: Collapsible Glossary & PostgreSQL DDL`)
- **Build-Once Overlaps Owned Here:** **Overlap #1** (`BS-01/BS-02` Kiosk Name & `last_seen_at` Heartbeat), **Overlap #2** (`AD-1..AD-4` Per-Person Follow-Through Counts without Individual Ranking Leaderboards)
- **Locked Owner Refinements Incorporated:** **A2** (Shift swaps & inter-outlet crew loans approved by Outlet Manager OR Cluster Manager with automatic visibility/notification to City Head; 12 core `outlets` fields locked to Master-Data Holders), **A3** (Multi-outlet team follow-through rollups visible above Outlet Manager with zero individual employee stack-ranking), **A4** (`hindi_reviewer` seeded in `master_data_holders`).
---
## PART 1 — EXECUTIVE SUMMARY (HALF-PAGE) & OWNER SIGN-OFF BOX
### 1.1 Executive Summary (0.5 Page)
At **44 operating outlets scaling toward 100+ stores**, daily execution breaks down in four predictable places on the café and kitchen floor:
1. **Unmonitored Store Kiosks & Spoofed Attendance (`BS-01..BS-06`, `BO-PIN-RESET`, `BO-CHECKIN-EXCEPTION`, `BO-ATTEND-B-OFFLINE`):** Shared Android tablets at store entrances go offline silently or have their URLs copied onto personal phones to punch attendance from outside the store. **Pack B2** binds every kiosk session to a registered `kiosks` hardware token (`BS-03`), streams a 60-second device heartbeat (`BS-01`, Build-Once Owner for `last_seen_at` & device name), enforces GPS geofencing (`100m` default) or signed kiosk QR tokens (`BS-06`), supports an offline IndexedDB cryptographic punch queue (`BO-ATTEND-B-OFFLINE`), and provides a green/amber/red `/admin/kiosks` fleet console with 60-second revocation (`BS-02`).
2. **Rigid Rostering & Unstructured Shift Checklists (`BS-04..BS-11`):** Outlets lack structured operating hours (`open_time`, `close_time`, `temp_closure_until`), shift headcount targets (`Morning 06:00–14:00`, `Afternoon 14:00–22:00`, `Closing 18:00–02:00`), and time-windowed opening/closing checklists. Under **Refinement A2**, inter-outlet crew loans and same-outlet shift swaps (`BS-09`) can be approved by **either the Outlet Manager or Cluster Manager**, while **automatically notifying and surfacing visibility to the City Head (`city_head_notified_at`)**, and **12 core outlet master fields** (`BS-07`) are hard-locked to `master_data_holders`.
3. **Coaching-First Team Follow-Through (`AD-1..AD-4`, Overlap #2):** Instead of toxic cross-store individual stack-ranking leaderboards that cause crew to game checklists, **My Team Adherence** tracks per-person `completed_on_time`, `completed_late`, `missed`, and `top_missed_domain` (`AD-1..AD-2`) with an append-only 1-line manager coaching note log (`AD-3`). Per **Refinement A3**, **Cluster Managers, City Heads, and HODs** see a multi-outlet follow-through rollup (`AD-4`) to coach struggling stores without ever ranking individual employees across outlets.
4. **Data Trust Rails (`DQ-1..DQ-5`) & Bilingual Hands-Free Floor UX (`HI-1..HI-3`, `N-11 / T2-8`):** Every dashboard renders a live data freshness indicator (`DQ-1`), quarantines `>3x` sync anomalies before they corrupt executive cards (`DQ-3`), logs every manual metric override (`DQ-4`), runs a `02:00 IST` nightly reconciliation job (`DQ-5`), and delivers **100% Hindi/Telugu translation parity (`HI-1..HI-3`)** signed off by a seeded `hindi_reviewer` (`A4`) alongside a **Push-to-Talk Bilingual Voice Pilot (`N-11 / T2-8`)** for pastry chefs and baristas whose hands are covered in flour or sugar syrup.
### 1.2 Right-Side Stage-Aware Owner Sign-Off Box
| Sign-Off Attribute | Current Governance State |
| :--- | :--- |
| **Module / Pack** | `Pack B2` — Daily Operations, Store Kiosk Fleet, Shift Adherence, Data Trust & Bilingual Voice UX |
| **Groomed Backlog Scope** | **40 Backlog IDs (`100%` of Cluster B2)**: `BS-01..BS-11`, `AD-1..AD-4`, `DQ-1..DQ-5`, `HI-1..HI-3`, `N-11 (T2-8)`, `BO-PIN-RESET`, `BO-CHECKIN-EXCEPTION`, `BO-ATTEND-B-OFFLINE`, `BO-PACK-GROUND`, `BO-AUD-OPS-MGR`, `BO-AUD-CLUSTER`, `BO-AUD-GROUND`, `A133`, `A164`, `A261`, `A324`, `A393`, `A447`, `A449`, `A511`, `A512`, `A550`, `A551`, `S180`, `S181`, `S212`, `H039`, `H048` |
| **Stage Gate** | **Stage 1: Design & Backlog Sign-Off** -> Ready for Stage 2 Build Authorization |
| **Peer-Review Verdict** | **PASS WITH HARDENED INVARIANTS** (`Claude Opus 4.7 Max` + `Codex Astra Extra High` — `/tmp/peer_review_pack_b1_b6.txt`) |
| **Owner Decision (`Srinivas`)** | `[ ] APPROVE FOR BUILD` · `[ ] APPROVE WITH NOTES` · `[ ] HOLD` |
---
## PART 2 — FULL GROOMED BACKLOG LEDGER, INDUSTRY RESEARCH, USER JOURNEYS & PEER REVIEW (1.5 PAGES)
### 2.1 Complete Groomed Backlog Ledger (Zero Orphans — 40 Item IDs)
| Item ID | Source Pack / Ref | Priority & Effort | Build-Once / Owner Rule | Production Engineering Specification & Acceptance Criteria |
| :--- | :--- | :--- | :--- | :--- |
| **`BS-01`** | `pkg2` (`02-BS`) | `P0` · `0.5d` | **Overlap #1 Owner** (`last_seen_at`, `device_name`) | `POST /api/v1/kiosks/heartbeat` called every 60s by registered store kiosks; updates `kiosks.last_seen_at`, `app_version`, `battery_pct`, `network_type`, `ip_address`. Consumed by `OS-6` Outlet Drawer without duplicate tables. |
| **`BS-02`** | `pkg2` (`02-BS`) | `P0` · `1.0d` | **Overlap #1 Owner** | `/admin/kiosks` Fleet Console showing outlet, device name, `last_seen_at` status badge (**Green** `<5m`, **Amber** `5–30m`, **Red** `>30m`), battery, app version, and 1-click **Revoke Device** button. |
| **`BS-03`** | `pkg2` (`02-BS`) | `P0` · `1.0d` | Security Gate | Bind `/kiosk` session to `kiosks.kiosk_token_hash` (SHA-256 of provisioning token stored in `HttpOnly` cookie + IndexedDB). Unregistered browsers get `403 KIOSK_UNREGISTERED`; revocation kills active sessions within `<60s`. |
| **`BS-04`** | `pkg2` (`02-BS`) | `P0` · `0.5d` | Store Hours | Adds `open_time`, `close_time`, `is_24_hour`, `temp_closure_until`, `temp_closure_reason` on `outlets`. Feeds checklist window enforcement (`BS-11`) and Rista sync expectations. |
| **`BS-05`** | `pkg2` (`02-BS`) | `P0` · `1.0d` | Shift Templates | Creates `shift_templates` (`Morning`, `Afternoon`, `Closing`, `Night`) per outlet with `start_time`, `end_time`, `grace_minutes` (default `10`), and `target_headcount`. |
| **`BS-06`** | `pkg2` (`02-BS`) | `P1` · `1.0d` | Geofence / Kiosk | Adds `attendance_geofence_meters` (default `100m`) on `outlets`. Check-in requires either valid registered kiosk token (`BS-03`) OR mobile GPS Haversine distance `<= attendance_geofence_meters`. |
| **`BS-07`** | `pkg2` (`02-BS`) + `A2` | `P1` · `0.5d` | **Refinement A2** (`12 Locked Fields`) | Locks **12 core fields** on `outlets` (`code`, `name`, `city_id`, `cluster_id`, `outlet_type`, `rista_branch_code`, `odoo_analytic_account_id`, `gstin`, `fssai_license_no`, `latitude`, `longitude`, `attendance_geofence_meters`) to `admin` / `master_data_holders`. Outlet Managers get `403` on direct edit and submit `outlet_change_requests` instead. |
| **`BS-08`** | `pkg2` (`02-BS`) | `P1` · `1.0d` | Staffing Target | Compares active shift `checked_in_count` against `shift_templates.target_headcount`; renders **Under-Staffed** Amber/Red pill on Outlet Summary (`OS-4`) and Cluster Manager view (`+15 min` after shift start). |
| **`BS-09`** | `pkg2` (`02-BS`) + `A2` | `P2` · `1.5d` | **Refinement A2** (`Crew Loan & Swap`) | `crew_loans` (inter-outlet temporary assignment with `from_outlet_id`, `to_outlet_id`, `shift_date`, `start_time`, `end_time`) and `shift_swaps` (same-outlet peer swap). **Approved by Outlet Manager OR Cluster Manager**, with mandatory automatic notification & visibility (`city_head_notified_at`) to **City Head**. |
| **`BS-10`** | `pkg2` (`02-BS`) | `P0` · `1.5d` | Checklist Engine | `checklist_templates` & `checklist_runs` supporting `OPENING`, `MID_SHIFT`, `CLOSING`, `HANDOVER` types with item-level required photo (`photo_url`), chiller temperature (`numeric_value`), or boolean sign-off. |
| **`BS-11`** | `pkg2` (`02-BS`) | `P1` · `1.0d` | Window Escalation | Enforces `window_minutes` (e.g., Opening checklist due within `open_time + 45m`). Runs submitted after deadline are tagged `LATE`; unsubmitted runs at `+60m` trigger Cluster Manager alert. |
| **`AD-1`** | `pkg6` (`05-AD`) | `P1` · `1.0d` | **Overlap #2 Owner** | Per-person 7d/30d follow-through row on `/my-team`: `completed_on_time`, `completed_late`, `missed`, and `follow_through_pct` across assigned checklists, tasks, and SOP acknowledgements. |
| **`AD-2`** | `pkg6` (`05-AD`) | `P1` · `1.0d` | **Overlap #2 Owner** | Computes each team member's single highest-frequency missed category (`top_missed_domain`, e.g., `"Closing Chiller Temp Log — 3 misses in 14d"`) for targeted 1-on-1 coaching. |
| **`AD-3`** | `pkg6` (`05-AD`) | `P2` · `0.5d` | Coaching Log | `team_coaching_notes` append-only table allowing Outlet/Cluster Manager to record a 1-line coaching action against a team member (`note_text`, `domain_tag`, `created_by`). |
| **`AD-4`** | `pkg6` (`05-AD`) + `A3` | `P2` · `1.0d` | **Refinement A3** (`Above-Store Rollup`) | Multi-outlet follow-through rollup for **Cluster Managers, City Heads, and HODs** showing per-outlet `on_time_pct`, `late_pct`, `missed_count`, and `coaching_notes_logged_7d`. **Hard negative rule:** zero cross-outlet individual employee stack-ranking leaderboards. |
| **`DQ-1`** | `pkg6` (`03-DQ`) | `P0` · `0.5d` | Freshness Banner | Universal `<DataFreshnessBanner>` on Revenue, Command Center, Outlet Summary, and Compliance pages: Green `<30m`, Amber `30m–2h`, Red `>2h` with `last_synced_at` and source name. |
| **`DQ-2`** | `pkg6` (`03-DQ`) | `P1` · `1.0d` | **Overlap #5 Consumer** | Consumes `OV-1` `metric_dictionary` (built once in `Pack B1`) to render `?` popover tooltips on every operational and adherence KPI card. |
| **`DQ-3`** | `pkg6` (`03-DQ`) | `P1` · `1.0d` | Sync Quarantine | `sync_quarantine` table intercepts connector payloads where outlet daily revenue `< 0`, `> 3.0x` trailing 30-day max, or item count `> 500` in a single bill; holds row in `QUARANTINED` status until Admin/Finance review. |
| **`DQ-4`** | `pkg6` (`03-DQ`) | `P1` · `0.5d` | Manual Edit Audit | `metric_edit_audit` append-only log capturing `table_name`, `record_id`, `field_name`, `old_value`, `new_value`, `reason`, `edited_by`, `edited_at` on any manual override. |
| **`DQ-5`** | `pkg6` (`03-DQ`) | `P2` · `1.5d` | Nightly Recon | `02:00 IST` cron comparing HOP `outlet_daily_revenue` against Rista branch summary API for `T-1`; creates `recon_discrepancy_alerts` whenever variance exceeds `0.5%` or `₹500`. |
| **`HI-1`** | `pkg6` (`04-HI`) | `P1` · `1.0d` | Translation Parity | Completes 100% Hindi (`hi`) and Telugu (`te`) UI string coverage across `/kiosk`, `/ground`, `/checklists`, `/sops`, `/my-team`, and `/tasks` with automated CI parity gate (0 missing keys). |
| **`HI-2`** | `pkg6` (`04-HI`) | `P1` · `1.5d` | Bilingual DB Content | Adds `title_hi`, `description_hi`, `title_te`, `description_te` columns to `checklist_templates`, `checklist_items`, `shift_templates`, and `sops` with automatic fallback to English if null. |
| **`HI-3`** | `pkg6` (`04-HI`) + `A4` | `P1` · `0.5d` | **Refinement A4** (`hindi_reviewer`) | Seeds `hindi_reviewer` role in `master_data_holders` (`A4`); adds `hi_review_status` (`DRAFT`, `VERIFIED_BY_HINDI_REVIEWER`) so machine-translated strings cannot go live on `/kiosk` without native kitchen-floor sign-off. |
| **`N-11` (`T2-8`)** | `CONTRACT-T2-8` | `P1` · `2.0d` | Bilingual Voice Pilot | Push-to-talk `<VoiceChecklistButton>` on `/kiosk` and `/ground` supporting **English, Hindi, and Hinglish/Telugu** speech-to-intent (`"Chiller 1 temp 3.2 degree"`, `"Opening checklist step 4 done"`) with TTS confirmation and mandatory tap/voice confirm before write. |
| **`BO-PIN-RESET`** | `02-BUILT-BACKLOG` | `P1` · `0.5d` | Kiosk Self-Service | Manager-assisted or OTP-verified 4-digit Kiosk PIN reset on `/kiosk` and `/my-team` with cooldown rate-limiting (`5 attempts / 15m`) and audit log. |
| **`BO-CHECKIN-EXCEPTION`** | `02-BUILT-BACKLOG` | `P1` · `1.0d` | Missed Punch Flow | Structured `attendance_exceptions` workflow (`FORGOT_PUNCH_OUT`, `KIOSK_POWER_OUTAGE`, `FIELD_ERRAND`) submitted by crew, approved by Outlet/Cluster Manager with mandatory reason, reflected separately from raw biometric/kiosk punches. |
| **`BO-ATTEND-B-OFFLINE`** | `02-BUILT-BACKLOG` | `P1` · `1.0d` | Offline Kiosk Queue | IndexedDB offline punch queue on `/kiosk` using HMAC-signed device monotonic counter + wall-clock timestamp; replays idempotently (`client_event_uuid`) when internet returns and flags clock-skew `>120s`. |
| **`BO-PACK-GROUND` & Historical IDs** | `BACKLOG-BY-DEPT` | `P1` · Consolidated | Full Traceability | Consolidates `BO-PACK-GROUND`, `BO-AUD-OPS-MGR`, `BO-AUD-CLUSTER`, `BO-AUD-GROUND`, `A133`, `A164`, `A261`, `A324`, `A393`, `A447`, `A449`, `A511`, `A512`, `A550`, `A551`, `S180`, `S181`, `S212`, `H039`, `H048` into the unified Kiosk, Shift, Checklist, Adherence & Voice UX architecture above. |
### 2.2 F&B Multi-Outlet Industry Research (300–1,000+ Stores)
- **Crunchtime! Ops Execution & Zenput (now Crunchtime) at Domino's / Sweetgreen (500–1,000+ stores):** High-performing QSR chains never trust browser-only URLs for attendance or opening line checks. They bind tablet hardware via device-enrollment tokens, enforce time-windowed line checks (`open_time + 45m`), and require live camera capture (blocking gallery uploads) + Bluetooth/manual thermometer bounds (`+1°C to +4°C` for pastry chillers) before an opening checklist can close.
- **Toast Sling / 7shifts / Square Team Management (Inter-Outlet Labor Sharing):** In multi-outlet dense urban clusters (like Harley's in Hyderabad, Bengaluru, and Mumbai), a barista calling in sick at `Jubilee Hills` is solved by loaning a barista from `Banjara Hills` for a single 8-hour shift. Industry-standard labor platforms treat a **Crew Loan (`BS-09`)** as a time-bounded overlay (`from_outlet_id`, `to_outlet_id`, `shift_date`, `approved_by`) rather than mutating the employee's permanent home `outlet_id` in HR master data—preventing payroll cost-center corruption while notifying the regional **City Head (`A2`)**.
- **Starbucks Digital Flywheel & Pret A Manger Operational Coaching (Anti-Stack-Ranking Principle):** Retail store operations research consistently demonstrates that cross-store individual employee leaderboards induce **Goodhart's Law** (pencil-whipping checklists in 8 seconds just to stay #1 on the leaderboard). Pret and Starbucks expose individual completion counts (`AD-1`) and top repeated gaps (`AD-2`) **only to the immediate coaching manager**, while regional leaders (**Cluster Managers, City Heads, HODs — Refinement A3**) view **outlet-level follow-through rollups (`AD-4`)** and coaching activity rates.
- **Hands-Free Kitchen Voice UX (`N-11 / T2-8`):** In commercial bakeries and cafés, touchscreen failure rates spike because crew touch tablets with wet, flour-dusted, or buttercream-gloved hands. Push-to-talk bilingual speech-to-intent with a deterministic grammar parser (`<item_number> <temperature/done>`) eliminates screen smudging while preserving an auditable transcript.
### 2.3 Core Operational User Journeys
1. **06:00 IST Store Opening & Kiosk Check-In (`BS-01..06`, `BS-10..11`, `BO-ATTEND-B-OFFLINE`):** Shift Supervisor arrives at `Banjara Hills` café. The wall-mounted tablet sends its 60s heartbeat (`BS-01`). Even if broadband is down at 06:00 IST, the supervisor punches in via PIN + selfie on `/kiosk`; the event is stored in IndexedDB with a signed device token (`BO-ATTEND-B-OFFLINE`) and syncs automatically at 06:08 IST. The Opening Checklist (`BS-10`) must be completed by `06:45 IST` (`BS-11`).
2. **07:15 IST Emergency Inter-Outlet Crew Loan (`BS-08`, `BS-09`, `Refinement A2`):** `Madhapur` café is short 1 barista (`checked_in_count = 3 < target_headcount = 4`, triggering an Amber Under-Staffed badge `BS-08`). Either the Outlet Manager or the Cluster Manager creates a 1-day **Crew Loan (`BS-09`)** borrowing a barista from `Gachibowli`. The moment it is approved, the barista can punch in on the `Madhapur` kiosk for that shift, and the **City Head receives an automatic notification and live visibility row (`city_head_notified_at`)** per **Refinement A2**.
3. **16:00 IST Coaching-First Weekly Review (`AD-1..AD-4`, `Refinement A3`):** Outlet Manager opens `/my-team` -> **Follow-Through (`AD-1`)**, sees a team member has `3 late closing checklists` with `top_missed_domain = "Display Case Expiry Check"` (`AD-2`), and logs a 1-line coaching note (`AD-3`). Above the store, the **City Head** opens the **Multi-Outlet Adherence Rollup (`AD-4`)** to compare store-level on-time rates across clusters—with zero individual employee stack-ranking.
### 2.4 Adversarial Peer-Review Verdict (`Claude Opus 4.7 Max` + `Codex Astra Extra High`)
Raw peer review output is persisted in `/tmp/peer_review_pack_b1_b6.txt`. Key architectural catches folded into **Pack B2**:
1. **Pencil-Whipping / Speed-Tap Guard on Checklists (`Claude Opus 4.7 Max`):** Crew often tap 15 checklist checkboxes in 4 seconds at the end of a shift. **Fix Folded In:** Added `started_at`, `completed_at`, `duration_seconds`, and `is_rapid_tap_flagged BOOLEAN` (flagged if `< 2 seconds` per item or gallery photo EXIF timestamp older than `15 minutes`) on `checklist_runs`.
2. **Offline Kiosk Clock-Tampering Attack (`Codex Astra Extra High`):** If a store tablet is offline (`BO-ATTEND-B-OFFLINE`), a user could manually rewind the Android system clock to `05:55 IST` to hide a late arrival, punch in, and restore the clock before reconnecting. **Fix Folded In:** Kiosk heartbeat stores `last_server_monotonic_offset_ms` and boot-elapsed `performance.now()`; offline punches record both `device_wall_clock_at` and `monotonic_delta_ms`, flagging any punch where wall-clock drift vs monotonic elapsed time exceeds `120 seconds` as `CLOCK_TAMPER_SUSPECTED`.
3. **Overlap Guard on `crew_loans` (`Codex Astra Extra High`):** Prevent an employee from being loaned to two outlets simultaneously or loaned during an approved leave. **Fix Folded In:** Added `EXCLUDE` / unique shift-date overlap check and automatic cross-check against `leave_requests` before approving `crew_loans`.
---
## PART 3 — COLLAPSIBLE GLOSSARY & POSTGRESQL DDL (BELOW THE FOLD)
<details>
<summary><strong>3.1 Plain-English Operational Glossary (Click to Expand)</strong></summary>
- **Kiosk Heartbeat (`BS-01`):** A lightweight 60-second ping sent by every registered store tablet to HOP so Operations knows immediately if a store tablet lost power or Wi-Fi.
- **Crew Loan (`BS-09`, `A2`):** A temporary, single-shift or multi-day assignment of a team member from their home outlet to a sister outlet without changing their permanent HR home store. Approved by Outlet Manager or Cluster Manager, with automatic visibility to City Head.
- **Follow-Through Count (`AD-1`, `A3`):** The count of assigned checklists, tasks, and SOP reads completed on time, completed late, or missed—used for coaching, never for cross-store individual stack-ranking.
- **Sync Quarantine (`DQ-3`):** A safety holding table where impossible POS numbers (e.g., negative daily revenue or `>3x` historical peak) are paused for human inspection before they distort executive dashboards.
</details>
<details>
<summary><strong>3.2 Executable PostgreSQL Migration DDL — Pack B2 (Click to Expand)</strong></summary>
```sql
-- ============================================================================
-- PACK B2 DDL: KIOSK FLEET, SHIFTS, CREW LOANS, ADHERENCE, DATA TRUST & VOICE
-- ============================================================================
-- 1. Registered Store Kiosks (BS-01..BS-03, Overlap #1 Owner)
CREATE TABLE IF NOT EXISTS kiosks (
id BIGSERIAL PRIMARY KEY,
outlet_id BIGINT NOT NULL REFERENCES outlets(id),
device_name VARCHAR(120) NOT NULL,
kiosk_token_hash CHAR(64) NOT NULL UNIQUE,
app_version VARCHAR(32),
battery_pct SMALLINT CHECK (battery_pct BETWEEN 0 AND 100),
network_type VARCHAR(24),
ip_address INET,
last_seen_at TIMESTAMPTZ,
revoked_at TIMESTAMPTZ,
revoked_by BIGINT REFERENCES users(id),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_kiosks_outlet_seen ON kiosks (outlet_id, last_seen_at DESC);
-- 2. Outlet Operating Window & Geofence Columns (BS-04, BS-06, BS-07 Locked Fields)
ALTER TABLE outlets
ADD COLUMN IF NOT EXISTS open_time TIME NOT NULL DEFAULT '07:00:00',
ADD COLUMN IF NOT EXISTS close_time TIME NOT NULL DEFAULT '23:00:00',
ADD COLUMN IF NOT EXISTS is_24_hour BOOLEAN NOT NULL DEFAULT FALSE,
ADD COLUMN IF NOT EXISTS temp_closure_until TIMESTAMPTZ,
ADD COLUMN IF NOT EXISTS temp_closure_reason TEXT,
ADD COLUMN IF NOT EXISTS attendance_geofence_meters INTEGER NOT NULL DEFAULT 100;
-- 3. Standard Shift Templates per Outlet (BS-05, BS-08, HI-2)
CREATE TABLE IF NOT EXISTS shift_templates (
id BIGSERIAL PRIMARY KEY,
outlet_id BIGINT NOT NULL REFERENCES outlets(id),
shift_code VARCHAR(32) NOT NULL, -- 'MORNING', 'AFTERNOON', 'CLOSING', 'NIGHT'
title_en VARCHAR(80) NOT NULL,
title_hi VARCHAR(80),
title_te VARCHAR(80),
start_time TIME NOT NULL,
end_time TIME NOT NULL,
grace_minutes SMALLINT NOT NULL DEFAULT 10,
target_headcount SMALLINT NOT NULL DEFAULT 3,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
UNIQUE (outlet_id, shift_code)
);
-- 4. Inter-Outlet Crew Loans & Shift Swaps (BS-09 + Locked Refinement A2)
CREATE TABLE IF NOT EXISTS crew_loans (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
from_outlet_id BIGINT NOT NULL REFERENCES outlets(id),
to_outlet_id BIGINT NOT NULL REFERENCES outlets(id),
shift_date DATE NOT NULL,
start_time TIME NOT NULL,
end_time TIME NOT NULL,
reason TEXT NOT NULL,
status VARCHAR(24) NOT NULL DEFAULT 'PENDING'
CHECK (status IN ('PENDING', 'APPROVED', 'REJECTED', 'CANCELLED')),
requested_by BIGINT NOT NULL REFERENCES users(id),
approved_by BIGINT REFERENCES users(id),
approver_role VARCHAR(32) CHECK (approver_role IN ('outlet_manager', 'cluster_manager', 'city_head', 'admin')),
approved_at TIMESTAMPTZ,
city_head_notified_at TIMESTAMPTZ, -- Mandatory A2 automatic visibility/notification timestamp
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CHECK (from_outlet_id <> to_outlet_id)
);
CREATE UNIQUE INDEX IF NOT EXISTS uq_crew_loans_active_shift
ON crew_loans (user_id, shift_date, start_time)
WHERE status = 'APPROVED';
-- 5. Bilingual Opening/Closing Checklists & Rapid-Tap Guard (BS-10, BS-11, HI-2)
CREATE TABLE IF NOT EXISTS checklist_templates (
id BIGSERIAL PRIMARY KEY,
code VARCHAR(64) NOT NULL UNIQUE,
checklist_type VARCHAR(24) NOT NULL CHECK (checklist_type IN ('OPENING', 'MID_SHIFT', 'CLOSING', 'HANDOVER')),
title_en VARCHAR(160) NOT NULL,
title_hi VARCHAR(160),
title_te VARCHAR(160),
window_minutes SMALLINT NOT NULL DEFAULT 45,
hi_review_status VARCHAR(32) NOT NULL DEFAULT 'DRAFT'
CHECK (hi_review_status IN ('DRAFT', 'VERIFIED_BY_HINDI_REVIEWER')),
hi_reviewed_by BIGINT REFERENCES users(id),
is_active BOOLEAN NOT NULL DEFAULT TRUE
);
CREATE TABLE IF NOT EXISTS checklist_runs (
id BIGSERIAL PRIMARY KEY,
template_id BIGINT NOT NULL REFERENCES checklist_templates(id),
outlet_id BIGINT NOT NULL REFERENCES outlets(id),
business_date DATE NOT NULL,
submitted_by BIGINT NOT NULL REFERENCES users(id),
kiosk_id BIGINT REFERENCES kiosks(id),
started_at TIMESTAMPTZ NOT NULL,
completed_at TIMESTAMPTZ,
duration_seconds INTEGER GENERATED ALWAYS AS (EXTRACT(EPOCH FROM (completed_at - started_at))::INTEGER) STORED,
timeliness_status VARCHAR(16) NOT NULL DEFAULT 'ON_TIME' CHECK (timeliness_status IN ('ON_TIME', 'LATE', 'MISSED')),
is_rapid_tap_flagged BOOLEAN NOT NULL DEFAULT FALSE,
voice_assisted BOOLEAN NOT NULL DEFAULT FALSE,
UNIQUE (template_id, outlet_id, business_date)
);
-- 6. Coaching-First Team Follow-Through Notes (AD-1..AD-4, Overlap #2)
CREATE TABLE IF NOT EXISTS team_coaching_notes (
id BIGSERIAL PRIMARY KEY,
outlet_id BIGINT NOT NULL REFERENCES outlets(id),
team_member_id BIGINT NOT NULL REFERENCES users(id),
manager_id BIGINT NOT NULL REFERENCES users(id),
domain_tag VARCHAR(64) NOT NULL, -- 'OPENING_CHECKLIST', 'CLOSING_CHECKLIST', 'SOP_READ', 'ATTENDANCE'
note_text VARCHAR(280) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- 7. Data Trust: Sync Quarantine & Manual Metric Edit Audit (DQ-3, DQ-4)
CREATE TABLE IF NOT EXISTS sync_quarantine (
id BIGSERIAL PRIMARY KEY,
connector_name VARCHAR(32) NOT NULL,
outlet_id BIGINT REFERENCES outlets(id),
business_date DATE NOT NULL,
quarantine_reason VARCHAR(120) NOT NULL,
raw_payload JSONB NOT NULL,
status VARCHAR(24) NOT NULL DEFAULT 'QUARANTINED'
CHECK (status IN ('QUARANTINED', 'RELEASED', 'DISCARDED')),
reviewed_by BIGINT REFERENCES users(id),
reviewed_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS metric_edit_audit (
id BIGSERIAL PRIMARY KEY,
table_name VARCHAR(64) NOT NULL,
record_id VARCHAR(64) NOT NULL,
field_name VARCHAR(64) NOT NULL,
old_value TEXT,
new_value TEXT,
reason TEXT NOT NULL,
edited_by BIGINT NOT NULL REFERENCES users(id),
edited_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
```
</details>