INTAKE-RISTA-CENTRAL-ODS-2026-10-22.md

# G1 Intake & Architectural Synthesis: Rista POS Integration via Central ODS (`EPIC-RISTA-CENTRAL-ODS`)

- **Epic ID:** `T3-0` / `EPIC-RISTA-CENTRAL-ODS`
- **Source Pack:** `rista-integration-2026-10-22` (`00-HANDOFF.md`, `01-OWNER-INTENT.md`, `02-AS-BUILT.md`, `03-BUILD-LIST.md`)
- **Owner Gate Status:** **G1 INTAKE SYNTHESIZED — HOLD BUILD (`NO BUILD YET`)**
- **Owner Architectural Directive (`2026-10-22 10:24 IST`):**
  > *"Attached a full Rista integration pack! Just synthesize it into the requirements.. nothing to build yet.. we are working on a central ODS model to make the build. and we will pull in the data from there!"*

---

## 1. Executive Summary & Architectural Shift (Direct Polling $\rightarrow$ Central ODS)

1. **No Direct Rista Polling in OneOps/HOP Yet:**
   - In the legacy pilot (`harleys-revenue-dashboard` at `1b03086a`), the app polled 7 Rista POS endpoints directly every 15 minutes using `src/ristaa-client.js`.
   - Under the Owner's **Central ODS Architecture (`2026-10-22`)**, Rista POS and Odoo ERP ingestion, retry/backoff (`RI-1`), rate-limit governance, raw payload archiving, and schema normalization move into a **Central Operational Data Store (Central ODS)**.
   - OneOps/HOP will **not** call Rista directly from app pages or duplicate Rista polling loops; instead, OneOps/HOP will read canonical, pre-reconciled views/tables from the **Central ODS**.

2. **Non-Negotiable Invariants Inherited by Central ODS + OneOps Consumer Layer:**
   - **Strictly Read-Only Against Source POS/ERP:** Nothing ever writes back to Rista or Odoo (`ristaaGet` is `GET`-only, `20s` timeout, HS256 JWT per call from `RISTAA_API_KEY` + `RISTAA_SECRET_KEY`).
   - **Secret Isolation:** Credentials never leave the server; Admin > Credentials displays only `Set` or `Missing` (and at most the last 4 characters) with encrypted storage and value-free audit logs (`credential_audit`).
   - **Time & Business-Day Law:** All timestamps are pinned to `Asia/Kolkata` (`IST`). Sync pulls query **calendar days** (today + yesterday before `06:00 IST`) so post-midnight sales before the **`07:00 IST` business-day rollover** (`BUSINESS_DAY_ROLLOVER_HOUR = 7`) are never missed. Every consumer payload carries `asOf` from the actual sync timestamp, never the wall clock.
   - **Idempotent Upserts & Zero Destructive Deletes:** Every upsert uses `WHERE (cols) IS DISTINCT FROM (EXCLUDED.cols)` so unchanged rows do not churn timestamps. Vanished bills are marked `status = 'removed'` (never hard-deleted), and an empty response removes nothing.
   - **Staging Read-Only & Quiet Rule:** Staging reads production Rista/ODS read-only and keeps `OUTBOUND_MESSAGES = off` so copied/synced alerts never page real staff.

---

## 2. The 7 Canonical Rista Data Streams & Field-Level Contract (Central ODS Schema)

When the Central ODS model is finalized, it must ingest these 7 Rista feeds and expose the following normalized tables/views to OneOps/HOP:

| # | Rista Endpoint & Cadence | Central ODS Target Tables | Key Field Transformations & Business Rules (`02-AS-BUILT.md` §19.9) |
|---|---|---|---|
| **1** | **`/branch/list`**<br>*(Every 15m sync)* | `ods.outlets`<br>`ods.city_code_rules` (`RI-5`) | • `branchCode` $\rightarrow$ `outlets.branch_code` (PK, text). Only `status = 'Active'` and `isRealOutlet` (not `"Head Office"`) sync.<br>• **City Resolution (`RI-5`):** Derive city via data-driven `city_code_rules` table (seeded with `BLR`, `HYD`, `MUM`, `PUNE`; `GUN`/`VIJAYAWADA` $\rightarrow$ `Vijayawada`; `GUR`/`GURGAON`/`GURUGRAM`/`DELHI`/`NOIDA`/`NCR` $\rightarrow$ `NCR`; `Secunderabad` $\rightarrow$ `Hyderabad`).<br>• **Locations Master (`PD03`):** Preserve Config-owned non-Rista locations (`CK-` Central Kitchen, `HO-` Corporate) so Rista sync never overwrites them.<br>• Preserve Config-owned columns (`outlet_type`, `opened_on`, `closed_on`, `hidden`, `cluster`). |
| **2** | **`/analytics/sales/summary`**<br>*(Every 15m; `branch`, `period`)* | `ods.sales_daily`<br>`ods.sales_channel_daily`<br>`ods.sales_payment_daily`<br>`ods.sales_item_daily`<br>`ods.sales_discount_daily`<br>`ods.sales_tax_daily`<br>`ods.sales_intraday` | • **Net Sales Law (`2026-10-01`):** `sales_daily.net_sales = netAmount + chargeTotal` (`netSalesOf`), while `ristaa_net_sales = netAmount` and `charges = chargeTotal` are stored separately for reconciliation. `directChargeTotal` $\rightarrow$ `packaging_revenue` (not added to net sales).<br>• **Channel Grouping:** `channelSummary[].name` mapped to `online` (`swiggy`, `zomato`, `ownly`, `dotpe`, `delivery`) vs `offline`.<br>• **SKU Merging:** `items[]` split by `itemNature` (`Goods` vs `Service`) are summed by `skuCode` (`mergeItemRows`) before upserting `qty`, `net_amount`, `discount_amount`, `gross_amount` (`itemTotalgrossAmount`), `tax_amount` (`itemTotaltaxAmount`).<br>• **Discount Normalization:** Strip trailing `"(...)"` from `discounts[].code` so percentage/flat variants roll up cleanly.<br>• **Intraday 15m Slots:** Capture `net_sales` and `orders` per 15-min slot for current business day (retained 10 days). |
| **3** | **`/sales/page`**<br>*(Every 15m; paged via `lastKey` up to 500 pages)* | `ods.sales_bills`<br>`ods.sales_bill_lines`<br>`ods.sales_brand_item_daily`<br>`ods.sales_brand_payment_daily`<br>`ods.sales_brand_discount_daily`<br>`ods.sales_brand_tax_daily`<br>`ods.sales_brand_sync_daily` | • **Owner Brand Decision (`A560` / `RI-2`):** Derive `brand` (`harleys`, `tresley`, `cacao`) from Rista's **`brandName`** across **ALL** tables, including `sales_bills.brand` (fixing the legacy bug where `sales_bills` used `channel` while `sales_brand_*` used `brandName`).<br>• **Closed vs All Bills:** Only `status = 'Closed'` bills aggregate into `sales_brand_*`; all bills store in `sales_bills` + `sales_bill_lines` with `content_hash` change detection and `pg_advisory_xact_lock`.<br>• **Complimentary Filter:** Skip `"Non Chargeable"` discounts in `sales_brand_discount_daily` to match Rista summary. |
| **4** | **`/analytics/custom/sales/summary`**<br>*(Daily after `03:00 IST` for finished days)* | `ods.sales_item_flags_daily`<br>`ods.sales_item_flags_sync` | • Captures exception items where `canceled_after_dispatch > 0` or `complimentary > 0` (summed by `item_key` = `sku_code` or `item_name`), plus `non_complimentary` and `rate`. |
| **5** | **`/items/soldout/history`**<br>*(Every 15m; paged via `lastKey` up to 50 pages)* | `ods.stockout_events`<br>`ods.stockouts`<br>`ods.stockout_sync_daily`<br>`ods.stockout_sync_runs` | • **Event Ingestion:** Stores `(branch_code, sku_code, event_at, event_type)` for valid `OUT`/`IN` events (`ON CONFLICT DO NOTHING`); cleans trailing `"undefined"`/`"null"` from `userName`.<br>• **Episode State Machine (`stockouts`):** `OUT` opens an episode (`out_at`, `out_by`) if none is open for `(branch_code, sku_code)`; `IN` (`>= out_at`) closes it (`back_in_at`, `back_in_by`) and calculates `trading_minutes` inside trading hours (`08:00–23:00 IST`). Enriched with `is_hero` (weekly Top-20 / 14-day rank), `family`, `top_city`, `top_brand`. |
| **6** | **`/items/soldout`**<br>*(Live list after each sold-out sync; up to 20 pages)* | `ods.soldout_reconciliation`<br>`ods.soldout_recon_pending` | • Compares Rista live sold-out SKUs against open `stockouts` episodes (`live_count`, `ours_open`, `only_ours`, `only_rista`). Tracks consecutive misses in `soldout_recon_pending` (confirms stale after 3 misses & 30m open; ignores empty live list when `>3` items are open). |
| **7** | **`/outlet/status`**<br>*(After each sold-out sync, throttled per outlet & platform)* | `ods.outlet_status_now`<br>`ods.outlet_status_events` | • Polls online/offline state per outlet for `swiggy`, `zomato`, and `ownly`. Writes `outlet_status_events` only on state transition and tracks `since` timestamp in `outlet_status_now` to compute trading-hour offline minutes. |

---

## 3. Requirements & Enhancements (`RI-1`–`RI-6`) Synthesized for the Central ODS & OneOps

Nothing below is to be built until the Central ODS schema is ready and passes Owner Gate approval:

| Requirement ID | Layer | Requirement Specification & Acceptance Criteria |
|---|---|---|
| **`RI-1`**<br>Retry & Backoff on Live Syncs | **Central ODS Ingest** | Add exponential backoff retry on HTTP `429` / `5xx` inside the Rista client, invoke `recordRetry` into `ingestion_runs` / `stockout_sync_runs`, and ensure a `5xx` storm never blocks the next 15-minute scheduler cycle. |
| **`RI-2`**<br>Pin 8 Unconfirmed Field Names + Single Brand Rule (`A560`) | **Central ODS Schema & Fixtures** | Capture 1 scrubbed sample JSON fixture per endpoint (zero keys/PII) to pin the 8 unconfirmed field names (`bill_id` key, `closeDate`/`closedDate`, `modifiedDate`/`revisedDate`, `unitPrice`/`itemPrice`, item-flags keys, `/items/soldout` wrapper shape, `/items/soldout` key access, `/outlet/status` offline strings) and derive `sales_bills.brand` from `brandName` so `sales_bills` and `sales_brand_item_daily` totals match 100%. |
| **`RI-3`**<br>Sync Cycle Duration Metric & Alert (`PL-22`) | **Central ODS + OneOps Telemetry (`T2-6`)** | Record `duration_ms` on every sync run row and trigger an Admin alert when `duration > 0.6 × interval` (e.g., `> 9 min` on a 15-min interval). Expose in Admin > Data Sync and `/admin/success`. |
| **`RI-4`**<br>Rista / ODS Connection Setup Card | **OneOps Config/Admin (`T2-6`)** | Add a `SETUP_REGISTRY` card `"Rista / ODS Connection"` with `done_when` = credentials set, last connection test OK, and a completed sales + sold-out sync in the last 60 minutes (auto-reopens after 2 consecutive failures). |
| **`RI-5`**<br>City Rules as Data, Not Code | **Central ODS + OneOps Outlets (`T2-2`)** | Replace hardcoded `CITY_CODE_PATTERNS` regexes with an additive `city_code_rules` table seeded with today's rules (`HYD`, `BLR`, `MUM`, `PUNE`, `NCR` fold, `Vijayawada` fold). Show resolved city + matching rule in Config > Outlets. |
| **`RI-6`**<br>Scheduled Resumable Bills Backfill | **Central ODS Scheduler** | Turn the manual `bills-backfill.js` script into a throttled, resumable nightly catch-up job for missing outlet-days in `sales_bill_backfill` with automatic `429` backoff. |

---

## 4. Downstream OneOps Modules Waiting on Central ODS

1. **`T3-1` (`EPIC-SALES-STOCKOUT-INTELLIGENCE` / Outlet Summary Page):** Reads `ods.sales_daily`, `ods.sales_intraday`, `ods.stockouts`, `ods.outlet_status_now`, and `ods.top_items`.
2. **`T3-5` (`EPIC-FINANCE-PROCUREMENT-AUDIT` / `A280` Cash Register Audit):** Random audit trigger requiring cash register scan + Rista snapshot photo + variance nudge (*"Nudge on variance from Rista, do not re-capture cash"*).
3. **`T3-2` (`A377(5)` Custom Cake Orders):** Unified custom cake order intake across all 4 routes (walk-in/phone, WhatsApp chat, Rista POS note, aggregator order notes).
4. **Vendor Dependency (`OW4` — Parked on Owner):** Request stock status (`/stockStatus` currently returns HTTP `403`), delivery timings, and sales ledger from Rista vendor to unlock `S-03` (per-channel sold-out split & void/leakage audit).