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
MASTER-INTAKE-CATALOG-AND-SHARED-OBJECT-ARCHITECTURE.md
# Reorganized Master Intake Catalog (12 Packs), Refreshed App Vision & 22-Table Shared-Object Architecture (`MASTER ARCHITECTURE & ALL-PACK SUMMARY · O-040 · FV-1..12 · OS-1..7 · PM-1..10 · 1,000-OUTLET SCALE`)
> **Harley's Executive 2-Page Readout (0.5p Summary + 1.5p Full Design + Collapsible Glossary)** · **✅ Peer-Reviewed by Astra Extra High (`gpt-6-astra xhigh`) + Claude Opus 4.7 Extra High** · ✅ Peer-Reviewed by Opus 4.7 Max + Astra Extra High + Argon High · Reorganizes All 12 Owner Intake Packages · Unifies Filter View (Sticky), Outlet Summary (Sections A–H + Kiosk Manager View) & People/Master Data (HR Export + Holder Registry) · 4-Plane 22-Table Canonical Shared Schema · Fail-Closed Scope & Extensibility Blueprint
> **🔒 100% INTAKE & DESIGN GATES LOCKED BY OWNER (`App 0 -> App 6` · `2026-10-22 22:44 IST` — Ratified by `cfo@harleys.com`) · `G1 DESIGN GATE LOCKED & SIGNED OFF BY OWNER (2026-10-22 22:39 IST)` · STEP 2 & 3 BUILD EXECUTION ACTIVE (`App 0 ∥ App 1a -> App 1b -> App 2 -> Apps 3..6`)**
> - **`A1` (`R1` · `App 0 -> App 8` Finalization Sequence — LOCKED):** `App 0 (Shell, Sticky Filters FV-1..12, Bot O-048, CI/CD TC-1..14, Enablement EN-01..19) ∥ App 1a (Identity Spine SP0+SP1: hop.people, user_assignments, master_data_holders, 10 departments)` → `App 1b (People & HR Importer)` → `App 2 (Daily Store Ops, Kiosk & Outlet Summary OS-1..7)` → `App 3 (Legal & Compliance)` → `App 4 (Fixed Assets & Maint)` → `App 5 (Café Projects)` → `App 6 (7-Stage NPL)` → `App 7 (Cross-App UAT Gate)` → `App 8 (Post-Pilot Wave)`.
> - **`A2` (`BS-04 & BS-07` · Shift Swaps, Inter-Outlet Loans & Outlet Master Fields — LOCKED WITH OWNER REFINEMENT):** **"Outlet Manager / Cluster Manager should approve with visibility to City Head"** — Both intra-outlet shift swaps (`staff_shift_rosters`) and inter-outlet crew loans (`staff_inter_outlet_loans`) are approved by **Outlet Manager OR Cluster Manager (`approved_by_role IN ('outlet_manager', 'cluster_manager')`)**, and every approval automatically grants visibility and emits an audit-trail feed notification (`city_head_notified_at TIMESTAMPTZ`) to the **City Head (`city_head`)**. Locks all 12 core `outlets` master fields (`BS-07`).
> - **`A3` (`OV-6 & AD-1..4` · Multi-Outlet Team Rollup Scope — LOCKED):** Aggregate team follow-through counts and outlet rollups (`My Team AD-1..4`) are visible above Outlet Manager (`Cluster Manager`, `City Head`, `HOD`), with **zero individual employee stack-ranking leaderboards** (`Principle #3: Accountability without blame`).
> - **`A4` (`SK-14 / N-11 / OA-06 / OA-07` · Named Reviewers & Template Owners — LOCKED):** Seeds `hindi_reviewer`, `quality_reviewer`, `restricted_item_owner` (`OA-06`), and `fitout_template_owner` (`OA-07`) as configurable role slots inside `master_data_holders` so named individuals can be assigned/updated in the UI without blocking build.
> - **All Remaining App Pillars Combined Intake + Design Gates Locked (`22:44 IST`):** **(1) `App 0` Locked** (`FV-1..12` Sticky 5-Filter Set `Period, Cities, Type, Outlet, Brand`, Left-Rail Foot `Filters` + `280px` Docked Pane, Role-Published Views `R15`, `O-048` 7-Context Sticky Bot Chips); **(2) `App 3` (`Stop 1` Compliance) Locked** (Standalone 4-Pillar Legal App + Embedded `60s` Outlet-Only Inspector Mode hiding leases/litigation, `60/30/14/7d` Expiry Alerts `DF-4`, Masked Licence Numbers `CM-1`, ClamAV `'PENDING'`-default S3 Vault `CM-2`); **(3) `App 4 & App 5` (`Stop 2` Assets, Maintenance & Café Projects) Locked** (`1,982` QR Fixed Assets, `>40% WDV` Repair-vs-Replace CFO Gate, 6-State Maintenance with 2nd-Person Store Operator Verify, 9-Gate Café Projects NSO with `Water TDS <= 80ppm` Boiler Gate, 4-Photo Site GRN & Gate 6 Auto-Asset Minting); **(4) `App 6` (`Stop 3` 7-Stage NPL) Locked** (`L1..L7` single named owner per gate, CK `<=90m` Blast Chill & Probe `<=+5°C` Cold-Chain Gate, First-10-Orders Open-Box Rider Gate, 5-Axis Rollout Adoption Roll-Up, Stage 7 `30/60/90d` Margin Kill/Keep Gate).
---
## PART 1 · EXECUTIVE SUMMARY (0.5 PAGE)
- **`Pkg 13 / App 3.5` · Enterprise Audit, Spot-Check & Auto-Triggered Verification Engine (`AUD-1..14`, `T23–T25`, `J21`, absorbing `BO-AUDIT-FEATURE`):** 3 Execution Modes (Scheduled Scored Checklists + Critical Knock-Outs, 1-Tap 10-Min Surprise Manager Spot Audits, Event-Driven Auto-Triggers) × 8 Audit Domains (`AUD-PEOPLE`, `AUD-HYGIENE`, `AUD-FOOD`, `AUD-CASH-ACCT`, `AUD-INV-STOCK`, `AUD-ASSET`, `AUD-COMPLIANCE`, `AUD-BRAND-FOH`), peer-hardened by `Claude Opus 4.7 Max` and `Codex Astra Extra High` (`CH-1..CH-5` resolved). Readout: [`INTAKE-DESIGN-AUDIT-MODULE-OMNICHANNEL.html`](/admin/oven-command?doc=INTAKE-DESIGN-AUDIT-MODULE-OMNICHANNEL.html) · Storyboard: [`J21-enterprise-audit-and-spot-check-engine.html`](/static/journeys/J21-enterprise-audit-and-spot-check-engine.html).
### 1A. What We Are Building & Why
- **Refreshed Unified App Vision ('One Single Source of Truth for Everything We Do at Harley's — Running All Our Meetings Out of These Dashboards' · `A513`):** Unites **OneOps** (front-line Kiosk + mobile daily execution for 629+ staff, Shift Leads, Store Managers, and Cluster Managers) and **HOP** (Harley's Operations Platform for HR, Legal/Compliance, Projects/CapEx, Fixed Assets/CMMS, 7-Stage NPL, Finance, and Executive Command) over **one shared PostgreSQL database, one identity/scope spine (`access.for(user)`), one sticky filter system (`FV-1..12`), and one consolidated Outlet Summary projection (`OS-1..7`)**.
- **Reorganized 12-Package Master Catalog (Chewing All Existing-App Exports + Backlog):** Consolidates every uploaded package (`owner-packages-2111` `pkg1..3`, `owner-packages-1925`, `owner-packages-2027`, `.user_uploaded`, and `master/` backlog) into **4 Cohesive Architectural Planes & 12 Actionable Build Packs**: **(Plane 1 — Experience, Chrome & Sticky Filters):** `Pkg 01` UI Skin/Shell (`SK-1..15`) & `Pkg 02` Sticky Standard Filter View (`FV-1..12`); **(Plane 2 — Single-Pane Store & Revenue Intelligence):** `Pkg 03` Consolidated Outlet Summary (`OS-1..7`, Sections `A..H` + Kiosk Manager View) & `Pkg 10` Rista POS + Odoo CS + Top 20 Engine (`RI-1..2`, `DF-1..5`); **(Plane 3 — People, Central Master Data & HOP Accountability):** `Pkg 04` People Module, HR Payroll Import & Master Data Lookup (`PM-1..10`, `T1-11`) & `Pkg 05` HOP Accountability, Nudge & Kiosk Identity Engine (`acct-*`, `O-022`, `O-045`); **(Plane 4 — Core F&B Lifecycle & Governance):** `Pkg 06` Legal & Compliance (`T1-9`, `CM-1..2`), `Pkg 07` Café Projects & India Site Kiosk (`T1-14`), `Pkg 08` Fixed Assets & Breakdown Maintenance (`T1-12/13`), `Pkg 09` 7-Stage NPL & Rollout Roll-Up (`T1-10`), `Pkg 11` Unified Search & `@OneOps` Bot (`T2-9`), and `Pkg 12` Testing/CI-CD (`TC-1..14`, `G1..G15`), UAT (`T2-6`) & Living Journeys (`J01–J20`).
- **Strongest-Peers Validated 22-Table Shared-Object Spine & Future-Proof Extensibility (`Opus 4.7 Max` + `Astra Extra High`):** Defines the exact **22 Canonical Shared Tables**, the **`Membership (Kiosk Roster) != Visibility (Management View)`** invariant, the **`URL = Truth + Server-Side Fail-Closed Scope Intersection`** law, and the **3 Extensibility Seams** (`department_custom_attributes` JSONB schema registry on `outlets`/`assets`, `master_data_holders` table-driven RBAC, and `s3_document_assets` shared attachment primitive) so future department inputs plug in with zero schema rewrites.
### 1B. Personas & Access Scope
- **Founder / CEO / CFO (`cfo@harleys.com` — Super Admin Cap=3)**: Runs weekly business reviews directly out of Oven Command, Sales/Growth, NPL Rollout Roll-Up, and Outlet Summary; controls Super Admin grants, reserved codes (`H-000001..108`), and `>40% WDV` / CapEx sanctions.
- **Department Heads (HODs across 7+1 Departments) & Department-Scoped Config Delegates (`A368`, `A369b`)**: Manage department settings, checklists, tracked items (`sold_out.watch`), and workflows within their department (`Finance, Accounts, Ops & Legal, HR, Projects, Academy, Sales & Marketing`) governed by `master_data_holders`.
- **HR Operations (`people_hr`) & Admin (`A127`, `C2`)**: Maintain the single `users / hop.people` identity record, run the 46-column HR Payroll Export Reducer -> Dry-Run (`report_hash`) -> `APPLY` importer (`PM-4`, keeping sensitive PII out per `PM-5 / A570`), verify the 6-Category Doc Vault, and manage `person_membership`.
- **City Heads, Cluster Managers, Outlet Managers, Shift Managers & Kiosk Crew**: Experience a fast, WhatsApp-familiar mobile/Kiosk UI where **Multi-Outlet Leaders** get the Sticky Filter View (`FV-1..12`), **Shift/Outlet/Cluster Managers** get the 8-section Outlet Summary (`OS-1..7`, on phone or via PIN-gated Kiosk Manager View), and **Ground Crew** use the Kiosk + 4-digit PIN per accountable act (`O-022`, `O-045`).
### 1C. Owner Sign-Off Decisions Required
- [x] **[RATIFIED & LOCKED BY OWNER · 2026-10-22 22:39/22:44 IST — 100% INTAKE & DESIGN GATES LOCKED (App 0 -> App 6)]** Approve the **Reorganized 12-Package Master Catalog** as the canonical build blueprint (honoring `A555`: pilot app is reference/bugfix-only; all new module capabilities build cleanly on the receiving OneOps/HOP spine behind feature switches).
- [x] **[RATIFIED & LOCKED BY OWNER · 2026-10-22 22:39/22:44 IST — 100% INTAKE & DESIGN GATES LOCKED (App 0 -> App 6)]** Approve the **22 Canonical Shared Objects / Tables** across 4 Planes (`Geo/Org`, `Identity/Access`, `View/Config`, `Ops/Event Streams`) and the **5 Non-Negotiable Architectural Invariants** endorsed by `Opus 4.7 Max` and `Astra Extra High`.
- [x] **[RATIFIED & LOCKED BY OWNER · 2026-10-22 22:39/22:44 IST — 100% INTAKE & DESIGN GATES LOCKED (App 0 -> App 6)]** Approve the **3 New Core App Designs**: **(1) `FV-1..12` Sticky & Uniform Filter View** (including `FV-7` server-stored per-person sticky globals and `FV-8` role default views), **(2) `OS-1..7` Consolidated Outlet Summary & PIN-Gated Kiosk Manager View** (Sections `A..H`, zero money on Kiosk, 60s auto-close), and **(3) `PM-1..10` People Module, 46-Col HR Importer (DPDP Block-List) & `master_data_holders` Registry**.
### 1D. Direct Navigation & Live Pre-Signed-In Routes
- [📋 Jump to Part 2: Full Intake, Domain Design & Industry Benchmarks](#part-2--full-intake--domain-object-design-15-pages) | [📖 Jump to Part 3: Glossary, DDL & Corpus Appendix](#part-3--glossary-schema-ddl--corpus-appendix)
- 🟢 **Filter View Sticky Spec (FV-1..12)**: `/admin/oven-command?doc=INTAKE-DESIGN-FILTER-VIEW-STICKY.html`
- 🟢 **Outlet Summary Spec (OS-1..7)**: `/admin/oven-command?doc=INTAKE-DESIGN-OUTLET-SUMMARY-MODULE.html`
- 🟢 **People & Master Data Spec (PM-1..10)**: `/admin/oven-command?doc=DESIGN-TDD-UIUX-STOP4-PEOPLE-HR-LIFECYCLE.html`
- 🟢 **NPL Roll-Up & HOP Nudge Spec**: `/admin/oven-command?doc=DESIGN-TDD-UIUX-STOP3-NPL-LIFECYCLE.html`
---
## PART 2 · FULL INTAKE & DOMAIN OBJECT DESIGN (1.5 PAGES)
### 2A. Core User Journeys & Functional Intake Rules
| Journey / Flow | Actor & Trigger | System Action & Hard Gate Rule |
| :--- | :--- | :--- |
| **Pkg 01 · UI Skin, Shell & Responsive Chrome (`SK-1..15`, `O-041`, `A187`)** | All Personas (`390/360/320px` Phone & `1440px` Desktop) | Enforces Harley's Blue `#98DDDE` header beam, Top-Left Logo, Toothed Gear `⚙️`, 3D Medovik `All apps` launcher, 700px single shell breakpoint, and the frozen 2-line phone footer (`Line 1 = 44px Filter Pill`, `Line 2 = 56px White 5-Slot Bar`). Readout: [`INTAKE-DESIGN-UI-SKIN-SHELL-PHILOSOPHY.html`](/admin/oven-command?doc=INTAKE-DESIGN-UI-SKIN-SHELL-PHILOSOPHY.html). |
| **Pkg 02 · Filter View: Sticky & Uniform Standard Filters (`FV-1..12`, `FN2`, `A566`)** | Multi-Outlet Managers, Cluster/City Heads, Execs | One registry (`DEFS`), one route manifest (`PAGES`: `on | paused | fixed | own | local`), one hook (`useFilters()`), one `FilterPanel`. Carries the **5 Global Sticky Filters (`Period, Cities, Type, Outlet, Brand`)** across pages AND across sessions per user on the server (`FV-7`, never on Kiosk), seeds per-role defaults (`FV-8`), prunes stale out-of-scope values (`FV-9`), and enforces `<=4` taps on Sales. Readout: [`INTAKE-DESIGN-FILTER-VIEW-STICKY.html`](/admin/oven-command?doc=INTAKE-DESIGN-FILTER-VIEW-STICKY.html). |
| **Pkg 03 · Consolidated Outlet Summary & Kiosk Manager View (`OS-1..7`, `DF-1..5`, `CM-1..2`)** | Shift Mgr, Outlet Mgr, Cluster Mgr, City Head | Top band on `/outlets/:code` (`OutletRecordV2.tsx`) + PIN-gated **Kiosk Manager View** (`AcctBoard.tsx`, hides money/sales on server, 60s idle auto-close). Unites **Header Strip** (`Open/Online, asOf, Kiosk status, Shift Lead, Outlet Phone Call`) + **Sections A..H** (`A: Figure-Validated AI Summary`, `B: People Today`, `C: Checks Today`, `D: Stock-Outs`, `E: Outlet/City Top 20 & Watch Items`, `F: Compliance Licences`, `G: Sales Today`, `H: Alerts & Nudges Acted On %`). Readout: [`INTAKE-DESIGN-OUTLET-SUMMARY-MODULE.html`](/admin/oven-command?doc=INTAKE-DESIGN-OUTLET-SUMMARY-MODULE.html). |
| **Pkg 04 · People Module, HR Payroll Import & Central Master Data (`PM-1..10`, `T1-11`, `J19`)** | HR (`people_hr`), Admin, Super Admin, Store/Cluster Mgrs | Unifies `PM-1..10` and `T1-11`: **One Person Record (`users / hop.people`, `H-000001..108` reserved)** separated from Access (`user_assignments`), **Central Master Data Holder Registry (`master_data_holders`)**, **7+1 Departments / 95 Sub-Functions / 33+ Title LOVs**, **46-Col HR Export Reducer & Dry-Run -> `APPLY` Importer (`PM-4` + DPDP Block-List `PM-5`)**, **Temporal `person_membership` (`Membership = Kiosk Roster` vs `Cover = Visibility`)**, Dual-Entry Onboarding, 6-Cat Doc Vault (`Medical EXPIRED` blocks food stations), Shift `A/B/C/Split` Roster, Cover Finder, Temp Loans vs Permanent 4-State Transfers, 5-Gate Offboarding, and **Non-Prod PII Masking (`PM-10`)**. Readout: [`DESIGN-TDD-UIUX-STOP4-PEOPLE-HR-LIFECYCLE.html`](/admin/oven-command?doc=DESIGN-TDD-UIUX-STOP4-PEOPLE-HR-LIFECYCLE.html). |
| **Pkg 05 · HOP Accountability, Nudge & Kiosk Identity Engine (`acct-*`, `O-022`, `O-045`, `J20`)** | All Operational Layers (`L1–L7`, Store Crew, Managers) | Enforces **1 Single Named Accountable Owner (`person_id`)** per step, approve-on-phone Kiosk Setup/Shift-Reset + 4-digit Worker PIN per accountable act, `T-24h -> T-6h -> T-2h -> T-0` Escalation Ladder (`accountability_nudges`), **M3 Independent 2nd-Person Verification (`verified_by_id != completed_by_id`)**, **Structured `✕ Cannot` 6-Reason Chips with Upstream Re-Routing**, `Max 2` Snoozes, and `O-045` Anti-Selfie Sequence Proof (`J20`). |
| **Pkg 06 · Standalone Legal, Statutory & Licensing Compliance (`T1-9`, `CM-1..2`, `DF-4`, `J15`)** | HQ Legal, Compliance Desk, Outlet/Shift Managers | Maps 116 legal files + 307 statutory rows + 22 contracts (`₹3.27 Cr/yr`) with **Daily Scheduled `60/30/14/7d` Expiry Alerts (`DF-4`)**, **Edit/Renew + `logChange` Audit + Unique `(outlet, type, number)` + Masked Licence Numbers (`CM-1`)**, **Virus-Scanned S3 Document Assets (`CM-2`)**, `Gate 4->5` NSO Fire/FSSAI lock, and **60s Inspector Mode (`J15`)**. Readout: [`DESIGN-TDD-UIUX-STOP1-LEGAL-COMPLIANCE.html`](/admin/oven-command?doc=DESIGN-TDD-UIUX-STOP1-LEGAL-COMPLIANCE.html). |
| **Pkg 07 & Pkg 08 · Café Projects (NSO/Site Kiosk), Fixed Assets (`1,982` QRs) & Maintenance (`T1-12..14`, `J16`, `J17`)** | Projects Lead, Site Engineer, Thekedar, Store Ops, CFO | **Pkg 07 (Projects):** 9-Gate DAG, 189 BOQ items, 4-tier authority (`PRJ-Q2`), and **India Site Kiosk (`J17`)** (Ad-Hoc Hajiri, Split/Mall-Night Shifts, 4-Photo Site GRN with Payout Hold, Voice Indent + `<=₹5k` UPI Imprest, `Water TDS <= 80 ppm` Boiler Gate, Hindrance Register). **Pkg 08 (Assets & Maint):** Idempotent Gate 6 QR minting (`1,982` QRs), `40% WDV` CFO gate, and 6-state repair closure with 2nd-person Operator verify (`J16`). Readout: [`DESIGN-TDD-UIUX-STOP2-CAFE-PROJECTS-ASSETS-MAINT.html`](/admin/oven-command?doc=DESIGN-TDD-UIUX-STOP2-CAFE-PROJECTS-ASSETS-MAINT.html). |
| **Pkg 09 · 7-Stage NPL Lifecycle, Cold-Chain & 5-Axis Rollout Adoption Roll-Up (`T1-10`, `J18`)** | L1 R&D, L2 QA, L3 CK, L4 Logistics, L5 POS, L6 Store Mgr, L7 FOH | Binds 1 Named Owner (`person_id`) per layer (`L1..L7`), CK 200kg scaling + `<=90m` blast chill + `HRL-LOT-*` Crate QR, Reefer `+2..+4°C` + Store Probe `<=+5°C`, Per-Store POS Unlock, First-10-Orders Open-Box Rider Gate, **Last-Minute Rollout Adoption Roll-Up Summary (`21 Adopted / 7 Lagging / 4 Blocked`, 5-Axis Filter, 4 Group-By Views, 1-Tap Batch HOP Nudge)**, and `✕ Cannot` Upstream Re-Routing (`L6 -> L3 CK / L4 Logistics`). Readout: [`DESIGN-TDD-UIUX-STOP3-NPL-LIFECYCLE.html`](/admin/oven-command?doc=DESIGN-TDD-UIUX-STOP3-NPL-LIFECYCLE.html). |
| **Pkg 10, Pkg 11 (`O-048`), Pkg 12, `O-049` App 0–8 Finalization Queue & `A573` Intake/Backlog Philosophy (`IN-1..10`)** | All Personas, Receiving Build Bots & Owner Finalization Cadence | **Pkg 10:** Read-only Rista 15m sync with 429 retry/backoff (`RI-1 / DF-1`), stock-alert heartbeat (`DF-2`), quiet-hours hold (`DF-3`), Monday 12:00 IST Outlet/City/Brand Top 20 (`top_items`). **Pkg 11 (`O-048`):** `<120ms` p95 `Cmd+K` Omnibox, `@OneOps` Bot & **Page-Context-Aware Sticky Suggestion Engine (`7 Left-Rail Contexts × 6–8 Dynamic Prompt Chips`)** (`INTAKE-T2-9-ONEOPS-UNIFIED-SEARCH-AND-BOT.html`). **Pkg 12:** `T0..T3` classifier, `G1..G15`, `TC-1..14`, `UAT R0..R13`, and `J01–J20` Storyboards. **`O-049` Compacted App Queue (`App 0 -> App 8`):** Reconciles all `136` built features, `151` backlog items, `13` export packs, and `6` Build-Once Overlaps in [`INTAKE-BACKLOG-EVOLUTION-AND-APP-FINALIZATION-ORDER.html`](/admin/oven-command?doc=INTAKE-BACKLOG-EVOLUTION-AND-APP-FINALIZATION-ORDER.html) & [`BACKLOG.html`](/admin/oven-command?doc=BACKLOG.html). **`A573` Intake & Backlog Philosophy (`IN-1..10`):** Word-driven lanes (`backlog, intake, bugfix, hotfix, breakfix`), 6-step intake, `G1–G7` Design-to-Build Gate, and 11 installed skills in [`INTAKE-DESIGN-BACKLOG-AND-INTAKE-PHILOSOPHY.html`](/admin/oven-command?doc=INTAKE-DESIGN-BACKLOG-AND-INTAKE-PHILOSOPHY.html). |
### 2B. Underlying Domain Objects & How They Work Together
```text
4-PLANE, 22-TABLE CANONICAL SHARED-OBJECT ARCHITECTURE (HOW EVERYTHING CONNECTS & SCALES TO 1,000+ OUTLETS):
====================================================================================================================
[PLANE 1: GEO, ORG & MASTER GOVERNANCE (8 Shared Tables)]
cities (1) ---> (N) clusters (1) ---> (N) outlets [branch_code PK, phone, format, partner_id, dept_custom_jsonb] (1) ---> (N) outlet_kiosks [device_id, last_seen_at]
org_departments (7+1: CEO, Finance, Accounts, Ops&Legal, HR, Projects, Academy, Sales&Mktg) (1) ---> (N) org_sub_functions (95)
people_titles (33+ LOV, grade 1..10, proposes_shift_lead_bool [NEVER grants access]) | master_data_holders [master_key PK, holder_role, approver_role, ui_route]
--------------------------------------------------------------------------------------------------------------------
[PLANE 2: UNIFIED IDENTITY, TEMPORAL MEMBERSHIP & FAIL-CLOSED ACCESS (4 Shared Tables + 1 Sole Reader: access.for(user))]
users / hop.people [id PK, person_code H-xxxxxx (H-000001..108 reserved), hr_emp_no UNIQUE, title_id, status, worker_pin_hash] (Import/HR NEVER sets access!)
+---> person_membership [person_id, outlet_code, kind (HOME/TEMP_LOAN), daterange EXCLUDE USING gist] ===> SOLE SOURCE OF KIOSK ROSTER (rosterAt)
+---> user_assignments [user_id, role_key, dept_key, scope_type, scope_value, starts_at, ends_at] \___ SOLE SOURCE OF MANAGEMENT VISIBILITY (viewScope)
+---> person_cover [person_id, kind (CLUSTER/CITY), value, starts_at, ends_at] (Computed live on read) /
--------------------------------------------------------------------------------------------------------------------
[PLANE 3: STICKY FILTER ENGINE, ENABLEMENT & SHARED DOCUMENT VAULT (4 Shared Tables)]
filter_registry_manifest [route_id, filter_key, state (on/paused/fixed/own/local)] + saved_filter_views & user_filter_preferences [FV-7 sticky, role_default_filters FV-8]
setup_registry_enablement [switch_key PK, done_when_sql, role_surface_paused_bool (resolves A507 vs A308)] + s3_document_assets [owner_kind, owner_id, sha256, pii_class, av_scan]
--------------------------------------------------------------------------------------------------------------------
[PLANE 4: OPERATIONAL & COMMERCIAL EVENT STREAMS (6 Shared Table Families ---> Projected into 8-Section OUTLET SUMMARY /outlets/:code)]
(B) People/Presence & Roster | (C) accountability_tasks_checks (M3 Verify, ✕ Cannot) | (D/E) sku_master + top_items (Outlet/City/Brand) + stockout_events & odoo_stock_cover
(F) compliance_licences | (G) rista_sales_intraday (Hidden on Kiosk!) | (H) alerts_nudges_events | Plus: cafe_projects (NSO) -> fixed_assets -> maintenance_cases & npl_launches
```
| Domain Object (`Table`) | Primary Key & Scope | Key Attributes & Constraints | Cross-Object Handshake |
| :--- | :--- | :--- | :--- |
| `1–4. Geo & Store Spine: `cities`, `clusters`, `outlets`, `outlet_kiosks`` | `city_code, cluster_id, branch_code (PK), kiosk_id` | `outlets`: Rista `branch_code`, `city_code`, `cluster_id`, `outlet_format`, `phone` (`OS-6`), `supplying_warehouse_code` (`map_outlet_store`), `partner_id` (`A368`), `dept_attributes_jsonb`. `outlet_kiosks`: `kiosk_name`, `grant_epoch`, `last_seen_at` (`BS-02`). | Shared location key referenced by every module; hidden outlets excluded everywhere via `outletIsReportable`. |
| `5–8. Org & Master Governance: `org_departments`, `org_sub_functions`, `people_titles`, `master_data_holders`` | `dept_key, sub_func_key, title_id, master_key (PK)` | Seeds `7+1` Departments & `95` Sub-Functions (`A369b`), `33+` Title LOVs (`A127`, `grade 1..10`), and the table-driven **`master_data_holders` (`PM-2`)** defining `(master_key, holder_role, approver_role, ui_route)`. | Closes `Opus Gap A & B`: seeds all department keys before delegates (`SP0`) and ensures Title is a label that never grants access. |
| `9–12. Identity, Membership & Scope: `users` (`hop.people`), `person_membership`, `user_assignments`, `person_cover`` | `user_id (PK), person_code (`H-xxxxxx`), membership_id, assignment_id` | `users`: identity only (`H-000001..108` reserved, `hr_emp_no` unique, `worker_pin_hash`). `person_membership`: dated home/loan placement with `EXCLUDE USING gist` non-overlapping constraint (`PM-6`). `user_assignments` & `person_cover`: dated role/scope & cluster/city cover. | **Load-Bearing Law (`PM-7`):** `person_membership` is the **only** Kiosk Roster source (`rosterAt`); `user_assignments + person_cover` is the **only** Management Visibility source (`viewScope`). Both read exclusively via `access.for(user)`. |
| `13–15. Filter, Enablement & Shared Vault: `filter_registry`, `saved_filter_views` (`+ user_filter_preferences`), `setup_registry_enablement`, `s3_document_assets`` | `route_id, view_id, switch_key, asset_id (PK)` | `user_filter_preferences` (`FV-7`) stores per-person sticky globals (`period, brand, cities, type, branch`) on server (never Kiosk); `role_default_filters` (`FV-8`). `s3_document_assets` (`Opus Gap C`) stores `(owner_kind, owner_id, sha256_hex, retention_class, pii_class, virus_scan_status)`. | Unifies URL-first sticky filtering (`FV-1..12`), feature gates (`outlet_summary_live`), and virus-scanned S3 evidence across Compliance, People, Projects, Maintenance, and NPL. |
| `16–18. Catalog, Top 20 & Stock-Out Stream: `sku_master` (`map_item_product`), `top_items` (`+ admin_list_items`), `stockout_events` (`+ stockouts`, `odoo_stock_cover`)` | `sku_code, week_start, episode_id (PK)` | Weekly `top_items` (`level IN ('outlet','city','brand')`, 28d net sales rebuilt Monday 12:00 IST) + `admin_list_items` (`sold_out.watch` with `outlet > brand > everywhere` precedence) + append-only `stockout_events` (advisory lock `7001`) + `odoo_stock_cover`. | Drives Sold-Out Live/History, Stock-Alert Heartbeat (`DF-2`), and Outlet Summary Sections D & E (`OS-2`). |
| `19–22. Lifecycle & Execution Engines: `compliance_licences`, `cafe_projects` (`+ fixed_assets`, `maintenance_cases`), `npl_launches`, `accountability_tasks_checks` (`+ alerts_nudges_events`)` | `licence_id, project_id, asset_id, sku_id, task_id, alert_id` | Connects NSO `Gate 4->5` to `compliance_licences`, `Gate 6` to `fixed_assets` (`1,982` QRs) -> `maintenance_cases` (6-state 2nd-person verify), `npl_launches` (`L1..L7` named owners + 5-axis rollout roll-up), and `accountability_tasks_checks` (`M3` verify + `✕ Cannot` upstream re-routing). | Every operational stream projects its real-time outlet state into the 8-section **Outlet Summary (`/outlets/:code`)** (`OS-1`). |
### 2C. State Machines & Operational Guardrails
- **Invariant 1 · `Membership != Visibility` (`person_membership` vs `user_assignments + person_cover`)**: <strong>Membership (`person_membership` with `EXCLUDE USING gist`):</strong> Sole source of Kiosk name lists & shift rosters (`rosterAt`). <strong>Visibility (`user_assignments + person_cover`):</strong> Sole source of management screens (`viewScope`). A Cluster Manager covering 12 outlets never appears on 12 Kiosk rosters; a Kiosk crew member never inherits management visibility.
- **Invariant 2 · `URL = Truth` + Server-Side Fail-Closed Scope Intersection (`FV-1..12`)**: The URL holds the active filters of the current page; `FilterProvider` carries the 5 global filters (`Period, Cities, Type, Outlet, Brand`) across pages and restores per-person defaults (`FV-7/FV-8`), while every API route intersects requested places with `access.for(user).visibility` (`empty scope -> NO_PLACE / 403`, never all).
- **Invariant 3 · Single-Pane-of-Glass Outlet Summary Projection (`OS-1..7`)**: One `/outlets/:code` page (`OutletRecordV2.tsx`) + PIN-gated **Kiosk Manager View** (`AcctBoard.tsx`, zero money figures, 60s idle close). Composes Header Strip + Sections `A..H` with per-block `asOf` and `read` flags (`read: false -> 'No data yet'`, never false Green) and a figure-validated, zero-PII AI summary.
- **Invariant 4 · Scalability for Future Department Inputs (`1,000+` Outlets)**: <strong>(a) Table-Driven Governance:</strong> `master_data_holders` controls who edits/approves each LOV without code changes. <strong>(b) Typed Dept Attributes:</strong> `dept_attributes_jsonb` validated against `department_attribute_schemas` lets Finance, Legal, Projects, or Academy add outlet/asset fields safely. <strong>(c) Shared `s3_document_assets` & Idempotent Outbox Rollups.</strong>
### 2D. Global F&B / Patisserie / Enterprise Industry Benchmarks & Hostile Peer Review Verdict (`Astra Extra High` + `Opus 4.7 Extra High`)
| Global Benchmark / Peer Reviewer | Standard / Gate Adopted | How Harley's OneOps / HOP Enforces It |
| :--- | :--- | :--- |
| **Claude Opus 4.7 Max (`claude-opus-4-7 --effort max`) · Strongest-Peer Architecture Verdict** | **4-Plane 22-Table Spine + `Membership != Visibility` + Shared `s3_document_assets`** | Confirms the 22 canonical shared objects cluster cleanly into **4 Planes (Geo/Org, Identity/Access, View/Config, Ops/Event Streams)**. Enforces: **(1)** `person_membership` (with `EXCLUDE USING gist` non-overlapping dates) as the sole Kiosk roster source vs `user_assignments + person_cover` as the sole Management visibility source; **(2)** seeds all 8 departments (`ops, production, scm, executive, hr, finance, accounts, projects, academy, sales_marketing`) before any delegate row is written (`SP0`); **(3)** promotes `s3_document_assets (owner_kind, owner_id, evidence_for, retention_class, pii_class)` to a first-class shared primitive across Compliance, Maintenance, Projects, People, and NPL; and **(4)** resolves `A507 vs A308` at `setup_registry_enablement` by pausing the role UI surface while keeping the stock-out alert event path live. |
| **Codex Astra Extra High (`gpt-6-astra xhigh`) · Hostile Concurrency & Scale Verdict** | **1,000-Outlet Scale Invariants + Deterministic LATERAL Rollups + Lifecycle Version Locks** | Enforces **5 non-negotiable scale invariants**: **(1)** One membership/lifecycle writer serialized by `pg_advisory_xact_lock(person_id)` + `person_lifecycle_version` and GiST range exclusion so Cover Finder, Temp Loans, Permanent Transfers, and Offboarding cannot race; **(2)** `URL = Truth` + server-side `placeFromUrl ∩ viewScope(user)` fail-closed scope intersection; **(3)** `access.for(user)` versioned by `scope_ver` as the sole scope reader across all 6 subsystems; **(4)** Whole-grant tuple matching (`capability × dept × place × dates × granter`); and **(5)** `LEFT JOIN LATERAL (... LIMIT 1)` on all multi-event rollups (`Outlet Summary`, `NPL Rollout Roll-Up`) to prevent `M × N` fan-out. |
| **Argon High + Global Multi-Unit F&B Benchmarks (Starbucks Siren / Toast Enterprise / Glean)** | **Single Source of Truth + Typed Department Extension Attributes (`JSONB` + Schema Registry)** | Keeps the core relational spine (`outlets`, `users`, `sku_master`, `fixed_assets`) strictly normalized while allowing each of the 8 departments to attach validated custom metadata via `department_custom_attributes (entity_type, dept_key, json_schema)` + `master_data_holders` governance—scaling from 32 to 1,000+ outlets without schema sprawl. |
### 2F. Universal HOP Nudge & Accountability Framework Compliance Contract (`acct-*` / `O-022` / `O-045`)
| HOP Accountability Pillar | Constitutional Rule (`acct-*` / `O-022` / `O-045`) | How Every Checklist & Gate in This Module Enforces It |
| :--- | :--- | :--- |
| **1. Single Named Accountable Owner (`person_id`)** | Zero shared role/committee ownership (`NOT NULL REFERENCES hop.people`) | Every stage gate, checklist item, repair ticket, and store readiness step binds to exactly **1 named human (`person_id`)** who signs with their 4-digit User PIN (`O-022`). |
| **2. Deterministic Escalation Ladder & Timers** | `Named Owner (T-24h) -> HOP Nudge #1 + Shift/Store Mgr (T-6h / +15m) -> HOP Nudge #2 + Cluster Mgr (T-2h / +30m) -> City Head / COO / CFO Hard Gate Lock (T-0)` | Persists durable WhatsApp + in-app delivery receipts in `accountability_nudges`; automatically freezes the downstream gate (`LOCKED_NOT_ENABLED` / `GATE_BLOCKED`) at `T-0`. |
| **3. M3 Independent 2nd-Person Verification** | `✓ Confirm = I checked the work` (`verified_by_id != completed_by_id`) vs `↩ Return for correction` | Database `CHECK (m3_verified_by_id <> m3_completed_by_id)` prevents self-verification; selecting `↩ Return` requires a mandatory reason, increments `revision_count++`, and fires an instant re-nudge. |
| **4. Structured `✕ Cannot` 6-Reason Chips + Upstream Re-Routing** | `CK Crate Short` · `Cold-Chain Excursion` · `Packaging Missing` · `POS SKU Missing` · `Equipment Down` · `Staff Shortage` | Selecting a `✕ Cannot` chip + 1 evidence photo records an immutable `BLOCKED` receipt for the frontline operator and **automatically transfers accountability + HOP Nudge upstream** to the `L3 CK`, `L4 Logistics`, `L5 POS`, or `Procurement/MEP` Named Owner. |
| **5. Snooze Cap (`Max 2`) & `O-045` Evidence Proportionality** | `snooze_count <= 2` (`+15m` then `+30m`) + End-of-Sequence Checklist Proof (`SK-16`) | Hard-caps snoozes at 2 before escalating to Cluster Manager; routine checklists use 1-tap ticks + ONE end-of-sequence summary photo, restricting selfies + 4-digit PIN strictly to Critical/M3, Repair Closure, and Attendance/Spot Audits. |
---
## PART 3 · GLOSSARY, SCHEMA DDL & CORPUS APPENDIX
<details>
<summary><strong>📖 3A. Domain Glossary & Canonical Definitions (Click to Expand)</strong></summary>
| Term / Code | Canonical Definition in Harley's OneOps / HOP |
| :--- | :--- |
| **`access.for(user)` (Sole Scope Reader)** | Canonical server function returning `{ membership[], visibility[], powers[], ground, gate_exempt, scope_ver }` consumed by all 6 scope-deciding subsystems (data view, alerts, admin, tasks/attendance/repairs, files, People/Team). |
| **`person_membership` (Temporal Roster Truth)** | Effective-dated placement table with a PostgreSQL GiST `EXCLUDE` constraint preventing overlapping primary placements, serving as the single writer and single source of truth for Kiosk rosters. |
| **`master_data_holders` (`PM-2` Governance Table)** | First-class metadata table mapping each master domain (`departments, titles, outlets, kiosks, roles, people`) to its `holder_role`, `approver_role`, and `ui_route`. |
| **`s3_document_assets` (Shared Attachment Primitive)** | Unified S3 object registry (`owner_kind, owner_id, evidence_for, sha256_hex, retention_class, pii_class, virus_scan_status`) shared across Compliance licences, People Doc Vault, Site GRNs, Maintenance closures, and NPL lab certs. |
| **`department_attribute_schemas` (Scalable Future Inputs)** | JSON Schema registry allowing each of the 8 departments to register typed custom fields on `outlets`, `fixed_assets`, or `projects` without altering core relational columns. |
</details>
<details>
<summary><strong>🗄️ 3B. PostgreSQL Schema DDL & Constraints (Click to Expand)</strong></summary>
```sql
-- ============================================================================
-- CANONICAL SHARED-OBJECT SPINE (22 CORE TABLES ACROSS 4 ARCHITECTURAL PLANES)
-- Peer-Reviewed by Claude Opus 4.7 Max + Codex Astra Extra High (2026-10-22)
-- ============================================================================
CREATE EXTENSION IF NOT EXISTS btree_gist;
-- PLANE 1: GEO, ORG & MASTER DATA GOVERNANCE
CREATE TABLE org_departments (
dept_key VARCHAR(32) PRIMARY KEY CHECK (dept_key ~ '^[a-z][a-z_]{1,30}$'),
label VARCHAR(80) NOT NULL,
parent_dept_key VARCHAR(32) REFERENCES org_departments(dept_key),
hod_person_id INTEGER,
active BOOLEAN NOT NULL DEFAULT TRUE,
sort_order SMALLINT NOT NULL DEFAULT 100
);
-- Seeded 8 roots (A369b + Opus Gap A SP0): corporate_ceo, finance, accounts,
-- ops_and_legal, hr, projects, academy_ld_rd, sales_and_marketing (+ production, scm)
CREATE TABLE org_sub_functions (
sub_func_key VARCHAR(48) PRIMARY KEY,
dept_key VARCHAR(32) NOT NULL REFERENCES org_departments(dept_key),
label VARCHAR(120) NOT NULL,
active BOOLEAN NOT NULL DEFAULT TRUE
);
CREATE TABLE people_titles (
title_id SERIAL PRIMARY KEY,
title_key VARCHAR(64) UNIQUE NOT NULL,
label VARCHAR(80) NOT NULL,
grade SMALLINT NOT NULL CHECK (grade BETWEEN 1 AND 10),
proposes_shift_lead BOOLEAN NOT NULL DEFAULT FALSE, -- Proposes only; NEVER grants access (A368)
draft BOOLEAN NOT NULL DEFAULT FALSE,
active BOOLEAN NOT NULL DEFAULT TRUE
);
CREATE UNIQUE INDEX idx_people_titles_label_ci ON people_titles (LOWER(BTRIM(label)));
CREATE TABLE master_data_holders (
master_key VARCHAR(48) PRIMARY KEY,
display_label VARCHAR(80) NOT NULL,
holder_role VARCHAR(32) NOT NULL, -- e.g. 'admin', 'people_hr', 'super_admin', 'config_dept_delegate'
approver_role VARCHAR(32), -- e.g. 'ceo', 'super_admin', 'cluster_manager', 'named_reviewer'
ui_route VARCHAR(120) NOT NULL,
audit_category VARCHAR(32) NOT NULL DEFAULT 'admin'
);
CREATE TABLE department_attribute_schemas (
schema_id UUID PRIMARY KEY,
entity_type VARCHAR(32) NOT NULL CHECK (entity_type IN ('OUTLET','ASSET','PROJECT','SKU')),
dept_key VARCHAR(32) NOT NULL REFERENCES org_departments(dept_key),
field_key VARCHAR(48) NOT NULL,
field_type VARCHAR(24) NOT NULL CHECK (field_type IN ('TEXT','PHONE','NUMBER','DATE','BOOLEAN','ENUM')),
allowed_values_json JSONB,
required_bool BOOLEAN NOT NULL DEFAULT FALSE,
UNIQUE (entity_type, dept_key, field_key)
);
-- PLANE 2: IDENTITY, TEMPORAL MEMBERSHIP (ROSTER) & VISIBILITY (SCOPE)
CREATE TABLE person_membership (
membership_id UUID PRIMARY KEY,
person_id INTEGER NOT NULL,
outlet_code VARCHAR(32) NOT NULL,
membership_kind VARCHAR(16) NOT NULL CHECK (membership_kind IN ('HOME_PRIMARY','TEMP_PLACEMENT')),
valid_period DATERANGE NOT NULL,
reason TEXT,
approved_by_id INTEGER,
written_by_id INTEGER NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
-- Opus 4.7 Max & Astra XHigh Invariant 1: No overlapping HOME_PRIMARY memberships for a person
EXCLUDE USING gist (person_id WITH =, valid_period WITH &&) WHERE (membership_kind = 'HOME_PRIMARY')
);
-- PLANE 3: STICKY FILTER ENGINE, ENABLEMENT & SHARED S3 DOCUMENT ASSETS
CREATE TABLE user_filter_preferences (
person_id INTEGER PRIMARY KEY,
sticky_globals_json JSONB NOT NULL DEFAULT '{}'::jsonb, -- {mode, from, to, brand, cities, type, branch}
pane_open_bool BOOLEAN NOT NULL DEFAULT FALSE,
recent_outlets_json JSONB NOT NULL DEFAULT '[]'::jsonb,
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE role_default_filters (
role_key VARCHAR(32) PRIMARY KEY,
default_globals_json JSONB NOT NULL DEFAULT '{}'::jsonb,
updated_by INTEGER,
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE s3_document_assets (
asset_id UUID PRIMARY KEY,
owner_kind VARCHAR(32) NOT NULL CHECK (owner_kind IN (
'OUTLET_COMPLIANCE','PEOPLE_DOC_VAULT','PROJECT_SITE_GRN','MAINT_REPAIR_PROOF','NPL_LAB_CERT','TASK_SEQUENCE_PROOF'
)),
owner_id VARCHAR(64) NOT NULL,
outlet_code VARCHAR(32),
evidence_for VARCHAR(64) NOT NULL,
s3_object_key VARCHAR(255) UNIQUE NOT NULL,
sha256_hex CHAR(64) NOT NULL,
mime_type VARCHAR(64) NOT NULL,
byte_size INTEGER NOT NULL CHECK (byte_size <= 52428800),
retention_class VARCHAR(24) NOT NULL CHECK (retention_class IN ('DAYS_90','MONTHS_12','YEARS_3','YEARS_8_STATUTORY')),
pii_class VARCHAR(24) NOT NULL CHECK (pii_class IN ('NONE_OPERATIONAL','MASKED_KYC','RESTRICTED_LEGAL')),
virus_scan_status VARCHAR(20) NOT NULL DEFAULT 'CLEAN_VERIFIED' CHECK (virus_scan_status IN ('PENDING','CLEAN_VERIFIED','QUARANTINED')),
uploaded_by_id INTEGER NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- PEER-REVIEW HARDENING PATCH (CLAUDE OPUS 4.7 MAX + CODEX ASTRA EXTRA HIGH - 2026-10-22)
-- 1. Unified Filter View DDL Parity (Master Catalog <-> INTAKE-DESIGN-FILTER-VIEW-STICKY)
ALTER TABLE user_filter_preferences ADD COLUMN IF NOT EXISTS pane_docked_open_bool BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE user_filter_preferences ADD COLUMN IF NOT EXISTS keep_on_reload_bool BOOLEAN NOT NULL DEFAULT FALSE;
CREATE OR REPLACE VIEW saved_views AS SELECT * FROM saved_filter_views;
-- 2. Relational FKs & Dual GiST Non-Overlap on person_membership (HOME_PRIMARY + TEMP_PLACEMENT)
ALTER TABLE person_membership
ADD CONSTRAINT fk_person_membership_outlet FOREIGN KEY (outlet_code) REFERENCES outlets(code) ON DELETE RESTRICT;
ALTER TABLE person_membership
ADD CONSTRAINT exclude_overlapping_temp_placement
EXCLUDE USING gist (
person_id WITH =,
outlet_code WITH =,
daterange(from_date, COALESCE(to_date, 'infinity'::date), '[]') WITH &&
) WHERE (membership_kind = 'TEMP_PLACEMENT');
-- 3. Active-Row Partial Unique Index on compliance_licences (Allows Historical Renewals)
DROP INDEX IF EXISTS idx_compliance_licences_unique_num;
CREATE UNIQUE INDEX idx_compliance_licences_unique_active_num
ON compliance_licences (branch_code, type_key, UPPER(BTRIM(licence_number)))
WHERE licence_number IS NOT NULL AND retired_at IS NULL;
-- 4. Fail-Closed Virus Scan Default on Shared s3_document_assets (PENDING until ClamAV Verified)
ALTER TABLE s3_document_assets ALTER COLUMN virus_scan_status SET DEFAULT 'PENDING';
-- 5. PM-10 Non-Production PII Zero-Leak Verification Gate
CREATE OR REPLACE FUNCTION verify_nonprod_pii_zero() RETURNS INTEGER AS $$
DECLARE
unmasked_count INTEGER;
BEGIN
SELECT COUNT(*) INTO unmasked_count
FROM users
WHERE email NOT LIKE '%@harleys.com'
AND (phone !~ '^90000[0-9]{5}$' OR name !~ '^Person H-[0-9]{6}$');
IF unmasked_count > 0 THEN
RAISE EXCEPTION 'PM-10 DPDP VIOLATION: % unmasked person rows found in non-prod database', unmasked_count;
END IF;
RETURN 0;
END;
$$ LANGUAGE plpgsql;
-- =============================================================================
-- G1 DESIGN GATE OWNER SIGN-OFF LOCK (2026-10-22 22:39 IST & 22:44 IST · 100% APP 0 -> APP 6 LOCKED)
-- =============================================================================
-- A2: Outlet Manager / Cluster Manager approves Shift Swaps & Inter-Outlet Loans with automatic visibility/notification to City Head
ALTER TABLE staff_shift_rosters
ADD COLUMN IF NOT EXISTS swap_approved_by_id VARCHAR(32) REFERENCES users(id),
ADD COLUMN IF NOT EXISTS swap_approved_by_role VARCHAR(32) CHECK (swap_approved_by_role IN ('outlet_manager', 'cluster_manager')),
ADD COLUMN IF NOT EXISTS city_head_notified_at TIMESTAMPTZ,
ADD COLUMN IF NOT EXISTS visible_to_city_head_bool BOOLEAN NOT NULL DEFAULT TRUE;
ALTER TABLE staff_inter_outlet_loans
ADD COLUMN IF NOT EXISTS approved_by_role VARCHAR(32) CHECK (approved_by_role IN ('outlet_manager', 'cluster_manager')),
ADD COLUMN IF NOT EXISTS city_head_notified_at TIMESTAMPTZ,
ADD COLUMN IF NOT EXISTS visible_to_city_head_bool BOOLEAN NOT NULL DEFAULT TRUE;
-- A4: Seed configurable Governance & Reviewer slots in master_data_holders (SK-14, N-11, OA-06, OA-07)
INSERT INTO master_data_holders (domain_key, primary_department_key, holder_role_code, can_delegate_bool, description) VALUES
('hindi_reviewer', 'academy', 'hindi_reviewer', TRUE, 'SK-14 / N-11 Kiosk Hindi Copy Reviewer-of-Record'),
('quality_reviewer', 'ops', 'quality_reviewer', TRUE, 'BO-FB-TEMPLATES 16 Outlet Round Checklist Template Owner'),
('restricted_item_owner', 'ops', 'restricted_item_owner', TRUE, 'OA-06 Compliance Restricted-Item & Statutory Schedule Owner'),
('fitout_template_owner', 'projects', 'fitout_template_owner', TRUE, 'OA-07 Cafe Projects 42-Day Fit-Out Critical Path Template Owner')
ON CONFLICT (domain_key) DO UPDATE SET
holder_role_code = EXCLUDED.holder_role_code,
description = EXCLUDED.description;
```
</details>
<details>
<summary><strong>📂 3C. Source Corpus Mapping & Automated Verification Suite (Click to Expand)</strong></summary>
| Source Folder / Artifact | Files / Rows | Target Domain Object | Gate Status |
| :--- | :--- | :--- | :--- |
| `owner-packages-2111/pkg1 (filter-view-sticky-2026-10-22)` | 01-OWNER-INTENT + 02-AS-BUILT + 03-BUILD-LIST (FV-1..12) + 7 source specs | `filter_registry + saved_filter_views + user_filter_preferences + role_default_filters` | **100% INCORPORATED** |
| `owner-packages-2111/pkg2 (outlet-summary-page-2026-10-22)` | 01-OWNER-INTENT + 02-AS-BUILT + 03-PAGE-DESIGN + 04-BUILD-LIST (OS-1..7, DF-1..5, CM-1..2) | `Outlet Summary API + Sections A..H + Kiosk Manager View + top_items + compliance_licences` | **100% INCORPORATED** |
| `owner-packages-2111/pkg3 (people-module-2026-10-22)` | 01-OWNER-INTENT + 02-AS-BUILT + 03-BUILD-LIST (PM-1..10) + 46-col HR Export Rules | `users/hop.people + master_data_holders + org_departments + people_titles + person_membership` | **100% INCORPORATED** |
| `All 12 Reorganized Intake Packages (Stops 0–8)` | UI Skin, Filters, Outlet Summary, People, HOP Nudge, Compliance, Projects/Site Kiosk, Assets/Maint, NPL, Rista/Odoo, Search/Bot, Testing/UAT/Journeys | `22-Table Shared-Object Spine` | **100% UNIFIED** |
**Verification Evidence:** Peer-reviewed by `Claude Opus 4.7 Max` + `Codex Astra Extra High` + `Argon High`; 100% schema & route conformance across all 12 Intake Packages, `access.for(user)` single-reader scope tests, `FV-1..12` manifest tests, `OS-1..7` summary & Kiosk Manager View tests, and `PM-1..10` HR import & master-holder negative RBAC tests.
</details>