How it should work, technically

1Project Overview

Skycrest Stock Control is a private web app for the Skycrest / Annie Makes Cakes kitchen in Marrickville, NSW. It tracks finished-goods stock by product code, plans production against airline orders, and manages tartlet-shell orders. The next step is an Orders & Dispatch module that takes every customer order — airline and non-airline — from intake through allocation to dispatch, and automatically removes dispatched goods from stock.

2Technical Goals

• One place for stock, orders and the production plan, replacing the shared Excel files.
• Stock figures must always be explainable: every change is a transaction with user, time and note.
• Office, kitchen and packing staff can use it on a desktop or a tablet in the kitchen.
• New modules must not put existing data at risk: no destructive migrations, no change to users or passwords on deploy.
• Low-maintenance hosting: shared PHP host, no build step, no external services required to run.

3Tech Stack (existing — keep)

Decision for new work: keep the same stack. To control file size, new module code goes in includes/orders.php (and similar), required from stock_control.php. The URL scheme (?page=…, ?api=…, POST action=…) stays the same.

4Existing System — Functional Summary

4.1 Request routing

1. ?api=<name> → JSON endpoint (auth required). Returns {ok:true,…} or {error:"…"}.
2. POST action=<name> → form handler → flash message → redirect (Post/Redirect/Get).
3. Otherwise → renderLogin() if not signed in, else renderDashboard() which switches on ?page= (stock default, products, plan, tartlets, users).

4.2 Stock model

• products.base_stock = opening balance.
• transactions rows of type in / out with quantity in units.
• Live stock = base_stock + Σ in − Σ out (currentStock()).
• Boxes = floor(live / pack_size); partial = remainder.
• Stock OUT cannot exceed live stock (manual form). Admin "set stock" and XLSM sync create an adjustment transaction for the difference.

4.3 Existing data model

4.4 Existing JSON APIs

search, product, set_stock, apply_xlsm_sync, discard_xlsm_preview; plan: add_plan_date, delete_plan_date, update_plan_dest, add_plan_week, save_plan_order, update_plan_date, list_plans, save_plan, load_plan, delete_saved_plan; tartlets: save_tartlet_line, save_tartlet_color, save_tartlet_order, add_tartlet_order, delete_tartlet_order, save_tartlet_asat, save_tartlet_plan, load_tartlet_plan, delete_tartlet_plan.

4.5 Existing form actions

login, logout, transaction, export, add_user, edit_user, delete_user, change_password, add_product, edit_product, delete_product, upload_xlsm.

5New Module — Orders & Dispatch: Functional Requirements

Source: workflow.xlsx (two swim-lanes: Airline and Non-airline).

5.1 Customers

• Seed list from the dispatch history: dnata Catering Sydney, Sydney – Halal, Melbourne, Brisbane South, Brisbane North, Perth West, Perth East, Adelaide Auscold Logistics, Adelaide Airport, Canberra Airport, Darwin; Gate Gourmet Sydney, Gate Gourmet Riverside.
• Admin can add / edit customers with: name, business entity (SKYCREST or ANNIE), channel (AIRLINE / NON_AIRLINE), default port/region (SYD, MEL, BNE, PER, ADL, CBR, DRW, …), contact email, phone, delivery address, notes.
• Entity defaults from channel (airline → SKYCREST, non-airline → ANNIE) but can be overridden.

5.2 Order intake

• User can create an order with: customer, PO number, source (EMAIL_PO, PDF_PO, HTML_PO, EMAIL_TEXT, PHONE, ENQUIRY), order date, required dispatch date, delivery method (DELIVERY / PICK_UP), port/region (prefilled from customer), notes.
• User can attach the original PO (PDF / image / .eml / .html, max 10 MB).
• User adds lines: product (search by code/name), quantity in units or cartons (converted with pack_size).
• Non-airline only: before saving, the screen shows an availability check per line (live stock − already allocated) and suggests the earliest delivery date. User confirms the delivery date.
• Bespoke enquiry: line can be a free-text item with no product code; such orders cannot be allocated until a product is linked.
• Duplicate PO number for the same customer shows a warning.

5.3 Invoicing (manual in V1)

• User records the Xero invoice number against the order. The UI labels which Xero to use (SkyCrest Xero / Annie Makes Cakes Xero) based on entity.
• Status cannot move past INVOICED without an invoice number (admin can override with a reason).

5.4 Production planner link

• Airline order lines automatically feed the Production Planning matrix: a column per (port, dispatch date); cell = Σ units ordered for that product.
• Manual cell edits on the planner remain possible for "forecast" quantities, but cells driven by orders are shown as linked and not directly editable.

5.5 Kitchen ticket and confirmation

• User can print the order (kitchen ticket: customer, PO, dispatch date, method, lines with units + cartons, notes, barcode/QR of order no. optional).
• User can generate the customer confirmation email text (copy to clipboard / mailto:). Marks CONFIRMED_SENT with timestamp. (Sending from the app = V2.)

5.6 Packing check and allocation

• Packing view lists orders due in the next N days with per-line status: Available, Short by X.
• Allocate reserves stock for the order: creates allocations rows; does not reduce live stock yet. Available-to-promise = live − allocated.
• Partial allocation allowed; remaining quantity shows as short.
• Short lines:
  • Airline → flagged on the Production Planning "to produce" figure (Alex checks planner).
  • Non-airline → "Inform Alex" creates a production request (visible on planner and a "Missing items" list).
• When product is made and stocked IN, user can re-run allocation for short lines.

5.7 Delivery schedule (day before dispatch)

• Schedule screen filtered by dispatch date and grouped:
  • Airline · Interstate (port ≠ SYD, e.g. MEL, BNE): filter by date & port → mark Roadmaster booked + booking ref.
  • Airline · Intrastate (SYD): filter by date → delivery run → mark Driver booked + driver name.
  • Non-airline (all customers): filter by date → delivery run / pick-up list.
• Printable schedule and .xlsx export.

5.8 Dispatch

• User marks order (or whole schedule group) Dispatched. This creates out transactions for every allocated line (note: Dispatch ORD-xxxx / PO …), removes the allocations and stamps dispatched_at / dispatched_by.
• Dispatch is blocked if any line is not fully allocated (admin can dispatch partial with reason → order becomes PART_DISPATCHED).
• Undo dispatch (admin only) reverses with in transactions; never deletes the original ledger rows.

5.8a Dispatch record (replaces the Excel "Dispatch Record" sheet)

• Per dispatched line: lot code, use-by date, shelf-life days, carton qty, temperature reading(s), PO, invoice no.
• Validation: use-by after dispatch date; temperature above the configured limit (default −18.0 °C) shows a warning and needs a note.

5.9 Order status lifecycle

NEW → INVOICED → IN_KITCHEN → CONFIRMED_SENT → ALLOCATED ─┐
                                    │                      ├→ SCHEDULED → DISPATCHED
                                    └→ SHORT → (made) ─────┘
Any state (before DISPATCHED) → CANCELLED (releases allocations)

5.11 Dashboard and staff reminders (spec: DASHBOARD.md)

• After login every user lands on a role-based dashboard (office, packing, production, admin) with KPI tiles, today's to-do list, bot inbox badge, insight widgets and an activity feed.
• The app records when each user has seen the dashboard each day (visible ≥ 10 s).
• Hermes Reminder Bot (Hermes Agent on a small VPS) checks every 15 min from 06:00: not seen by check-in time → WhatsApp/Telegram → SMS → phone call (Twilio, opt-in only) → manager. Stops on view or "OK" reply.
• New tables staff_profiles, dashboard_views, reminder_log; token-protected APIs agent_unseen, agent_reminder, agent_ack.

5.12 Airline analytics (basis: AIRLINE_ANALYSIS.md)

• Analytics page: cartons per week by port, top products with trend, kitchen order rhythm (typical gap, days since last PO), menu-change / slow-mover list.
• Data from the new orders / dispatch tables; the 2025–26 Excel dispatch history is imported once as a baseline.

5.10 Roles

App roles map to the managerial dashboards in DASHBOARD.md §2.3: savoury_prod (Alex), sweets_prod (Annie & sweets team), office_mgr (Ruth), gm (Mark), director (Tisna, Heri, Vivi — read-only), packing, plus admin. In the table below, user (office) = Ruth's team; production managers can allocate and mark short; GM has admin-level overrides; directors view only.

6Data Requirements (new tables — additive only)

CREATE TABLE IF NOT EXISTS customers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  entity TEXT NOT NULL DEFAULT 'ANNIE',        -- SKYCREST | ANNIE
  channel TEXT NOT NULL DEFAULT 'NON_AIRLINE', -- AIRLINE | NON_AIRLINE
  port TEXT DEFAULT '',                        -- SYD | MEL | BNE | ADL ...
  email TEXT DEFAULT '', phone TEXT DEFAULT '', address TEXT DEFAULT '',
  notes TEXT DEFAULT '', active INTEGER DEFAULT 1,
  created_at TEXT DEFAULT (datetime('now','localtime'))
);
CREATE TABLE IF NOT EXISTS orders (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  order_no TEXT UNIQUE,                        -- ORD-000123
  customer_id INTEGER NOT NULL,
  po_number TEXT DEFAULT '', source TEXT DEFAULT 'EMAIL_PO',
  order_date TEXT NOT NULL, dispatch_date TEXT NOT NULL,
  delivery_method TEXT DEFAULT 'DELIVERY', port TEXT DEFAULT '',
  status TEXT NOT NULL DEFAULT 'NEW',
  xero_invoice_no TEXT DEFAULT '',
  confirmed_at TEXT DEFAULT '', booking_type TEXT DEFAULT '',  -- ROADMASTER | DRIVER | PICKUP
  booking_ref TEXT DEFAULT '', booked_at TEXT DEFAULT '',
  dispatched_at TEXT DEFAULT '', dispatched_by TEXT DEFAULT '',
  notes TEXT DEFAULT '', created_by TEXT DEFAULT '',
  created_at TEXT DEFAULT (datetime('now','localtime'))
);
CREATE TABLE IF NOT EXISTS order_lines (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  order_id INTEGER NOT NULL,
  product_code TEXT DEFAULT '',               -- '' for bespoke free text
  description TEXT DEFAULT '',
  qty_units INTEGER NOT NULL DEFAULT 0,
  qty_dispatched INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS allocations (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  order_line_id INTEGER NOT NULL, product_code TEXT NOT NULL,
  qty_units INTEGER NOT NULL, username TEXT DEFAULT '',
  created_at TEXT DEFAULT (datetime('now','localtime'))
);
CREATE TABLE IF NOT EXISTS order_attachments (
  id INTEGER PRIMARY KEY AUTOINCREMENT, order_id INTEGER NOT NULL,
  filename TEXT NOT NULL, stored_path TEXT NOT NULL, mime TEXT DEFAULT '',
  uploaded_by TEXT DEFAULT '', created_at TEXT DEFAULT (datetime('now','localtime'))
);
CREATE TABLE IF NOT EXISTS order_events (       -- audit trail
  id INTEGER PRIMARY KEY AUTOINCREMENT, order_id INTEGER NOT NULL,
  event TEXT NOT NULL, detail TEXT DEFAULT '', username TEXT DEFAULT '',
  created_at TEXT DEFAULT (datetime('now','localtime'))
);
CREATE INDEX IF NOT EXISTS idx_orders_dispatch ON orders(dispatch_date, status);
CREATE INDEX IF NOT EXISTS idx_lines_order ON order_lines(order_id);
CREATE INDEX IF NOT EXISTS idx_alloc_code ON allocations(product_code);
CREATE INDEX IF NOT EXISTS idx_tx_code ON transactions(product_code);

• Optional column: transactions.order_id INTEGER DEFAULT NULL (added with ALTER TABLE … ADD COLUMN only if missing) to link dispatch movements to orders.
• Available to promise = currentStock(code) − Σ allocations.qty_units for code.
• Attachments stored outside the web root (or in a folder with deny-all rules); served only through an authenticated ?api=attachment&id=.

7Non-Functional Requirements

• Data safety: all migrations additive and idempotent; run inside initDB(); no DROP, no DELETE FROM users, no reseeding when tables already contain data.
• Ledger integrity: stock changes only via transactions; dispatch and allocation run inside a DB transaction; never edit or delete historic ledger rows.
• Security: CSRF token on every POST and API write; session_regenerate_id(true) on login; server-side role checks on every write; all output escaped with htmlspecialchars; uploads type- and size-checked and never executed.
• Performance: Orders list, packing view and schedule load in < 1 s with 2,000 orders / 50,000 transactions (use aggregate queries, not per-row currentStock()).
• Usability: works from 768 px (kitchen tablet) up; touch targets ≥ 40 px on packing screens; clear loading / saved / error states; autosave pattern consistent with Plan and Tartlet pages.
• Printing: kitchen ticket and delivery schedule print cleanly on A4 portrait.
• Timezone: Australia/Sydney for all dates and "day before dispatch" logic.

8Integrations

9Constraints

• Do not change existing users, passwords or the stock.db file during any deploy.
• Credentials (FTP, future Xero/SMTP keys) never in source code or docs; stored in a server-side config file outside public_html or in local deployment.md only.
• Keep one entry point and existing URLs working; existing pages must behave exactly as before.
• No Composer / Node build required on the server.
• Production Planning and Tartlet pages remain usable during and after rollout.

10Agentic Layer (summary — full spec in AGENTIC.md)

• Ten bots (Order Intake, Availability, Invoice Prep, Confirmation, Production, Allocation, Dispatch Scheduler, Stock Reconciler, Hermes Reminder, Order Pattern). Nine run as PHP CLI jobs from one cron entry (hosting cron confirmed); the Hermes Reminder Bot runs on Hermes Agent.
• They read an events stream and write to an agent_proposals approval queue; humans approve in a Bot inbox.
• Autonomy levels L1 (human only) / L2 (bot prepares, human approves) / L3 (bot acts and reports). Stock, money and customer promises are permanently L1.
• Only the Order Intake Bot (non-airline emails) and Hermes use an AI model (Claude API, server-side key outside the web root); all others are deterministic rules.
• Every bot has a kill switch and starts disabled.

11Definition of Done (Orders & Dispatch V1)

• An airline order and a non-airline order can each be taken from NEW → DISPATCHED in the app, matching the two lanes in workflow.xlsx.
• Dispatch reduces live stock by exactly the dispatched quantities, visible in the transaction log with the order number.
• Airline orders show on the Production Planning page without double entry.
• Short items are visible to Alex (planner / missing-items list).
• Delivery schedule for a date prints correctly for all three groups.
• All TESTING.md release blockers pass; existing pages pass regression checks.
• stock.db backed up before deploy; users and passwords unchanged after deploy.