# Architecture and database design

## Business relationship

`Customer → Area → Route stop → Route weekday + cutoff → Next delivery date → Order + Items → Date-specific route run → Vehicle + Driver → Delivery`

Routes and areas belong to the business. Each area belongs to one route; stops have unique ordered positions. Changing a route affects new bookings. Each order snapshots area, route, date and product price so existing commitments remain visible. Manual changes are audited.

## Implemented V1 stack

| Component | Choice | Reason / boundary |
|---|---|---|
| Dashboard | Browser JavaScript + CSS | Runs immediately on the supplied Windows machine; responsive UI without package installation |
| Backend | Python standard library | Builds alongside the supplied Python source; no Selenium/Chrome dependency for orders |
| Database | SQLite WAL | Local relational persistence and transactional booking, consistent backups; single-instance V1 |
| Queue | Database webhook journal + messages + outbox | Incoming payload committed before acknowledgment, recoverable pending processing, inspectable failures |
| WhatsApp | Meta Cloud API | Signed official webhook, per-number backend token, status callbacks |
| Understanding | Conservative catalogue parser; optional local Ollama | Works without a key; structured AI when configured; uncertain results remain reviewable |
| Delivery exports | Printable HTML / CSV | Staff can print/save PDF and open CSV in Excel |
| Auth | Scrypt passwords + HttpOnly session cookies | Role boundaries, CSRF mutation token, local rate limiting |

The recommended production target is React/Next.js UI + a hardened Python ASGI service (or the existing Next.js/Node expertise from WA-AKG), PostgreSQL with migrations, and a shared leased queue such as Redis/BullMQ or PostgreSQL-backed jobs. Production migration is a separate deployable implementation, not a hidden dependency of the running V1.

## Message pipeline

1. Meta sends signed POST data to `/webhook/whatsapp`.
2. HMAC-SHA256 is verified over raw bytes. Invalid signatures never enter the queue.
3. Raw request bytes, SHA256 idempotency hash and timestamp are committed to `webhook_events`. Only then is HTTP 200 returned. A database exception returns an error so Meta can retry.
4. The single worker resolves `metadata.phone_number_id`. Unknown numbers are retained in a failed event; staff configure the number then retry. All configured numbers belong to the same V1 workspace.
5. Sender phone identifies/creates the customer. Conversation is unique per customer/business number. The message and complete message object are saved before classification. A unique external message ID protects retries with differently batched payloads too.
6. Takeover or paused automation moves inbound text to human handling. Media is preserved for review.
7. Intent classification separates order requests, changes/cancellations, product, price, delivery, payment, general and unknown queries. Product master and saved area/address validate the request.
8. Repeat requests retrieve the most recent eligible confirmed/scheduled/completed order. Missing details trigger follow-up; new addresses are saved.
9. A validated draft is stored on the conversation. The bot presents products, quantities and expected date, then waits for YES/HAAN. No draft is an order.
10. Confirmation recalculates scheduling against the current cutoff, checks duplicates, creates order/items/delivery and outgoing confirmation within one database transaction. Failure/uncertainty creates a review notification.
11. The outgoing worker sends through the receiving number's Cloud API credentials. Successful local messages are marked simulated. Actual API sends record the returned external ID, then status webhooks update the message.
12. Order tables, daily sheets, driver assignments and audit logs read the same committed records.

The V1 worker is single-process and performs one cycle at a time. Launching multiple server instances on one SQLite file is unsupported. A production queue needs leases, per-conversation ordering and idempotent processing; a crash after Meta accepts a send can still result in an uncertain delivery. Verify provider logs before manually retrying.

## Scheduling algorithm

V1 is explicitly Asia/Kolkata (UTC+05:30). A route stores weekday 0–6, cutoff offset 0–6 days, cutoff HH:MM and departure HH:MM.

- Start at the next occurrence of its weekday, including today.
- Build the cutoff instant as `(delivery date − cutoff_days) at cutoff_time`.
- Build departure instant on the delivery date.
- Accept only if current time is **strictly before both**, the date is not a holiday, and that route run has not departed.
- Otherwise move forward seven days and repeat, for up to one year.
- Disabled routes fail into staff handling instead of accepting a false delivery promise.
- Admin date/route override is explicit. Fleet assignment belongs to a particular route and date. Driver reschedule requires a new date.

Example: a Monday route with Sunday 20:00 cutoff accepts a Thursday order for the upcoming Monday. At exactly Sunday 20:00, the order moves to the following Monday. Route-day arrival after departure also moves forward. The weekly schedule contains six demo days but real weekday configuration is data-driven, including Sunday if needed.

## Database entities

The executable source of truth is `schema.sql`, not pseudocode.

| Entity | Relationship / constraint |
|---|---|
| businesses | V1 workspace 1; future tenancy root |
| users | Business, unique username, role; driver role links to driver profile |
| customers | Business + unique phone, optional saved area, address, shop and notes |
| whatsapp_numbers | Unique Meta phone-number ID, business and token environment name |
| conversations | Customer + receiving number unique; takeover and pending draft |
| webhook_events | Raw envelope, unique body hash, processing state/error |
| messages | Conversation, unique inbound provider ID, raw message JSON, intent/confidence/state |
| products | Business + unique SKU; unit, price, aliases, activation |
| routes | Business, weekday, cutoff/day offset, departure, active |
| areas | Business + case-insensitive unique name, city, aliases |
| route_stops | Area unique across routes; unique route/position; sorted delivery sequence |
| delivery_schedules | Unique route/date; vehicle, driver and departed flag |
| holidays | Unique business/date closure |
| orders | Customer, receiving number/message, snapshot area/route/date; fingerprint and status |
| order_items | Order + product; positive quantity and stored unit price |
| deliveries | One per order; outcome, notes, timestamp |
| vehicles / drivers | Business fleet and driver master |
| message_templates | Business + unique name, editable application confirmation |
| notifications | Review, daily summaries, source message, resolution |
| outbox | Unique outgoing message, payload, provider ID, retry state |
| audit_logs | Actor, action, entity, timestamp and detail |
| settings | Business/key unique operational settings |

Foreign keys prevent accidental deletion of referenced records. Orders are cancelled instead of silently removed. Order indexes cover business/date/route/status and customer history; queue indexes cover pending messages and next outgoing attempt. Passwords are hashed, tokens stay in process environment, and arbitrary workspace paths are never served over HTTP.

## Supplied sources

- **WA-AKG:** inspected package and README, including the `message.received` payload. Its multi-session/inbox/gateway concepts informed the centralized number/inbox design. `adapters.wa_akg` normalizes an exported direct-message event in a private simulator. Its Baileys runtime is not the official production transport and is not enabled.
- **Wechaty:** inspected package and README. Its message/event abstraction informed separation of transport from business logic. `adapters.wechaty` accepts a documented normalized export shape; it does not claim to be the full native Wechaty serialization or a running puppet connection.
- **main.py:** preserved unchanged. Its outgoing text/media automation is a migration reference; credentialed API outbox replaces browser DOM operations. Group broadcast targets cannot safely be treated as individual retailer phone numbers.
- **Project brief:** business requirements drove route/data/order design. Embedded role/assistant instructions are treated as document content; external setup actions are not automatically authorized by that text.

Original ZIP licenses and source files remain intact. New application files are written independently; this is not a claim that the upstream systems have been installed or merged.