dailygrubs_console/IMPLEMENTATION_PLAN.md

# Weekly Order Intake — Implementation Plan

**Status:** Draft, awaiting decisions in §7
**Date:** 2026-09-04
**Scope:** `dailygrubs_console` (+ one new Jupiter endpoint from phase 2)
**Goal:** A weekly spreadsheet of orders is turned into created orders automatically, with agent assistance at the ambiguous steps.

---

## 1. Summary

The requested outcome is achievable, but **not from the file currently being used as the example.**
`Orders_Detail_2026-09-02_073552.xlsx` is a *report of orders that already happened*, not an
order intake sheet. Phase 0 therefore defines a real intake template; everything else builds on it.

The build is six phases. **Phases 0–2 write nothing.** The first real order is created in phase 3,
behind an operator commit button. Full autonomy is phase 5, after idempotency is proven in phase 4.

---

## 2. Why the attached file cannot be the input

### 2.1 It records outcomes, not intent

Every distinguishing column is a result: `orderstatus=delivered`, `deliverytime`, `rider`,
`assigntime`, `kms`, `deliverycharge`. Re-submitting it would re-create twelve deliveries that
were already ridden and already charged.

### 2.2 It fails the console's existing upload contract

`src/pages/nearle/orders/multipleOrders.js` maps incoming headers through `headerMap` and marks
six columns required (suffix `*`). Measured against the 12 data rows of the export:

| Upload column | Required | Report field | Filled | Status |
|---|---|---|---|---|
| `sendername*` | yes | `locationname` | 12/12 | Mappable |
| `senderphone*` | yes | `locationcontactno` | 12/12 | Mappable |
| `senderaddress*` | yes | `Pickupaddress` | **0/12** | Present but empty |
| `receivername*` | yes | `deliverycustomer` | 12/12 | Mappable |
| `receiveralternatephone*` | yes | — | — | **Absent** |
| `itemdescription*` | yes | — | 1/12 | **Absent** |
| `receiverphone` | no | `deliverycontactno` | 12/12 | Mappable |
| `receiverfulladdress` | no | `deliveryaddress` | 12/12 | Mappable |
| `receiverlatitude` | no | `deliverylat` | 12/12 | Mappable |
| `receiverlongitude` | no | `deliverylong` | 12/12 | Mappable |
| `pickupdate(yyyy-mmm-dd)` | no | `deliverydate` | 12/12 | Timezone-suspect — see §6 |
| `Quantity` | no | — | — | Absent |

Two required columns are absent outright and one is blank on every row. The *underlying customer
data* is all present, so a mapping is possible — it just needs a purpose-built template.

### 2.3 Orders attach to customer records, not to addresses

This is the constraint that decides the architecture. `POST /orders/createorders` takes an array
in which every element carries `customerid` **and** `deliveryid`. A row of free text is not enough:
each line must resolve to an existing customer, or one must be created first. This is why the
current UI says *"Press Continue to add as drop customers"* between upload and creation.

### 2.4 Three values never come from any sheet

| Value | Source |
|---|---|
| `applocationid`, `partnerid`, `locationid`, `moduleid`, pickup lat/long | The selected pickup location record |
| `deliverytime` | The pickup slot chosen for the batch |
| `deliverycharge`, `orderamount`, `ordervalue` | Computed from tenant pricing (`gettenantpricing`) and distance |

On the sample data the pricing rule fits **₹60 up to 4 km, then ₹12/km** (6 km → ₹84, 5 km → ₹72),
but that is *inferred from one day and one tenant*, not read from configuration. Never copy a
charge from the sheet.

> **Consequence:** the weekly file supplies *who and what*. The pickup point, the slot and the
> money are supplied by the run. Automation must bind them explicitly rather than inherit them.

---

## 3. Where an agent earns its place

Most of this pipeline is not an AI problem. Parsing, validating, pricing and posting are
deterministic and must stay that way — they are the steps where a confident wrong answer costs
money.

| Deterministic — no model | Agent — proposes, never commits |
|---|---|
| Parse XLSX/CSV, drop `TOTAL` rows | **Customer resolution** — match name + phone + address to an existing record, or propose a new one |
| Validate required columns, reject early | **Address normalisation** — split free text into locality / landmark / pincode, including Tamil-script entries |
| Compute charge from tenant pricing | **Anomaly triage** — explain in operator language why a row looks wrong |
| Duplicate detection by run key | |
| Build and POST the `createorders` payload | |
| Reuse `scanDataQuality` as a pre-commit gate | |

**The rule:** the agent returns a proposal with a confidence score and its reasoning.
Deterministic code applies a threshold. Anything below it goes to the operator queue rather than
into the batch. **A model never writes an order.**

---

## 4. Files

| File | Action |
|---|---|
| `src/pages/nearle/ai/intakeTemplate.js` | new — column contract + downloadable template |
| `src/pages/nearle/ai/intakeParser.js` | new — parse, validate, classify rows |
| `src/pages/nearle/ai/intakeParser.test.js` | new |
| `src/pages/nearle/ai/customerResolver.js` | new — phone match, agent seam |
| `src/pages/nearle/ai/customerResolver.test.js` | new |
| `src/pages/nearle/ai/runKey.js` | new — idempotency |
| `src/pages/nearle/ai/runKey.test.js` | new |
| `src/pages/nearle/ai/buildOrderPayload.js` | new — the `createorders` array |
| `src/pages/nearle/ai/buildOrderPayload.test.js` | new |
| `src/pages/nearle/orders/WeeklyIntake.js` | new — review & commit screen |
| `src/pages/nearle/api/api.js` | modify — `fetchTenantCustomers`, `createOrdersBatch`, `proposeCustomerMatches` |
| `src/routes/MainRoutes.js` | modify — add `nearle/orders/weekly-intake` |
| `src/menu-items/nearle.js` | modify — menu entry under Orders |

Existing modules reused: `opsAnalysis.js` (`readField`, `onlyOrders`, `scanDataQuality`),
`xlsx` and `papaparse` (already dependencies).

---

## 5. Phases

Each phase is useful on its own. Nothing writes an order until phase 3.

### Phase 0 — Define the intake template

`intakeTemplate.js` holds one contract; everything else derives from it.

```js
export const INTAKE_COLUMNS = [
  { key: 'receivername',    label: 'Receiver Name*',     required: true },
  { key: 'receiverphone',   label: 'Receiver Phone*',    required: true },
  { key: 'receiveraddress', label: 'Receiver Address*',  required: true },
  { key: 'itemdescription', label: 'Item Description*',  required: true },
  { key: 'quantity',        label: 'Quantity',           required: false, default: 1 },
  { key: 'collectcash',     label: 'Collect Cash',       required: false, default: 0 },
  { key: 'receiverlat',     label: 'Receiver Latitude',  required: false },
  { key: 'receiverlong',    label: 'Receiver Longitude', required: false },
  { key: 'notes',           label: 'Notes',              required: false }
];

export const downloadTemplate = () => { /* xlsx, already a dependency */ };
```

Note what is deliberately **absent**: no sender columns (that is the batch's pickup location),
no delivery date (that is the slot), no charge (computed). Those are the §2.4 values.

**Acceptance:** template downloads and round-trips through the phase 1 parser with zero findings.
**Gate:** nothing else starts until the column list is agreed and one real sample week exists.

### Phase 1 — Parser and validator, dry run only

```js
parseIntakeFile(sheetRows) -> {
  missingColumns: string[],
  unknownColumns: string[],   // fail loudly — see §6 "silent field drift"
  rows: [{ index, raw, normalised, status, reasons: string[] }],
  summary: { total, ok, needsReview, rejected }
}
```

`status` is `'ok' | 'needs-review' | 'rejected'`.

- **Rejected:** missing required field, unparseable phone, coordinates outside the tenant radius.
- **Needs review:** no coordinates, non-Latin locality, suspected duplicate within the same file.

Pure module, no React and no network, tested the way `opsAnalysis.js` is.

**Acceptance:** run a real week through it and read the report. Zero writes.

### Phase 2 — Customer resolution

```js
resolveCustomers(parsedRows, existingCustomers, { proposeFn, threshold = 0.85 })
  -> [{ index, match: { customerid, confidence, method }, proposedNew }]
```

`method` is `'phone-exact' | 'agent' | 'none'`.

1. Deterministic exact phone match against `customers/gettenantcustomers` — no model, no cost.
2. Only unmatched rows go to `proposeFn`, the injected agent call. Injection keeps tests offline.
3. Below `threshold` → `method: 'none'` → operator queue.

**Acceptance:** measure match accuracy against a week whose correct answers are already known,
before resolution is allowed to influence anything.

### Phase 3 — Review and commit screen

```js
buildCreateOrdersPayload({ resolvedRows, pickupLocation, pickupSlot, pricing }) -> object[]
```

Emits exactly the shape `multipleOrders.js` posts today: `configid: 9`, `paymenttype: 42`,
`paymentstatus: 1`, `orderstatus: 'created'`, `deliverytype: 'B'`, `itemcount: 1`, plus
`customerid`/`deliveryid` from phase 2 and the location fields from the selected pickup record.

`WeeklyIntake.js` renders the proposed batch (resolved customer, address, charge, flags), takes
the pickup location and slot, and commits via `POST /orders/createorders`.

**This is the first phase that creates real orders.** Ship behind a role check (§7.5).

### Phase 4 — Scheduling and idempotency

```js
runKeyFor(row, { tenantid, weekStart })
// sha256(tenantid | weekStart | normalisedPhone | normalisedAddress | itemdescription)
```

Persisted server-side. `createorders` performs **no deduplication of its own**, so this layer is
the only thing between a retry and double-billing a customer.

**Acceptance:** deliberately re-run the previous week's file and confirm zero new orders.
This is the single most important test in the build.

### Phase 5 — Supervised autonomy

Auto-commit only rows that are `ok`, resolved at or above threshold, and unflagged. Everything
else waits in the queue and the operator receives a digest rather than a surprise. Requires a
kill switch and an audit row per created order naming the run that produced it.

**Gate:** enable only after several consecutive clean weeks at phase 4.

---

## 6. Risks and guardrails

| Risk | Why it is real here | Guardrail |
|---|---|---|
| **Duplicate orders** | Same file re-sent, or a retry after timeout. `createorders` does not dedupe. | Persisted run key per row; replay is a no-op (phase 4). |
| **Wrong customer** | Fuzzy names (`HAMEEZ RAMEEZ ` has a trailing space); one locality appears as both `ராமவர்மபுரம்` and `Ramavarmapuram`. | Exact phone match first; agent proposals below threshold never auto-commit. |
| **Bad geocode** | Addresses carry plus-codes and free text; the pickup store itself resolves to two precisions (`8.1841265` and `8.184126`). | Reject rows whose coordinates fall outside the tenant's service radius. |
| **Wrong slot / date** | The `deliverydate` timezone defect is unresolved and reads 5½ hours early. | Operator sets the slot per batch; never inherit a date from a sheet until the backend is fixed. |
| **Silent field drift** | Already observed in this codebase: the API sends `ridername`/`deliverycharges` while the CSV export writes `rider`/`deliverycharge`. Both fail as blanks and zeros, not errors. | Validate the parsed shape; fail loudly on unknown or missing columns. |

---

## 7. Open decisions

Defaults below are what the plan currently assumes. Only #1 changes the design materially.

1. **What is actually in the weekly file** — recurring standing orders for the same customers, or
   a fresh list each week?
   *Assumed: fresh list.* Standing orders would make phase 2 nearly free.
2. **One pickup location or several** — this tenant has two (Bawa Medical / King Nagar, and
   Bawaa Medicals 2 / Weavers Colony). If one file mixes both, the template needs a per-row sender
   column instead of one choice per batch.
   *Assumed: one location per batch, operator-selected.*
3. **How the file arrives** — console upload is simplest and needs no new infrastructure; a
   watched folder or mailbox is more automatic and more to build and secure.
   *Assumed: console upload.*
4. **Where the agent runs** — inside `backend_jupiter`, or a separate DailyGrubs service it calls.
   Phases 0–1 do not depend on the answer.
   *Assumed: a new Jupiter endpoint.*
5. **Who may commit a batch** — auth in this console is localStorage, and `AuthGuard`/`GuestGuard`
   are currently commented out in both route files. A screen that creates real orders needs this
   settled first.
   *Assumed: a role check, to be specified.*

---

## 8. Sequencing note

Phases 0–2 write nothing and depend on none of the open decisions except #1, so they can begin
immediately. Phase 3 must not ship before decision #5.

---

## Appendix — sources

- Upload contract: `src/pages/nearle/orders/multipleOrders.js` (`headerMap`, `handleFileDirectUpload`)
- Payload shape: `createorders()` in the same file → `POST ${REACT_APP_URL}/orders/createorders`
- Customer lookup: `customers/gettenantcustomers`, `customers/search`
- Pricing: `tenants/gettenantpricing`
- Data-quality findings reused from: `src/pages/nearle/ai/opsAnalysis.js`
- Sample analysed: `Orders_Detail_2026-09-02_073552.xlsx` — 13 rows, 12 orders plus one `TOTAL` row