1What the app is today
A private web app that the Skycrest kitchen team uses to track finished-goods stock and plan production. It replaces parts of three Excel workbooks:
| Excel source | Replaced by app page |
|---|---|
Stock Control.xlsm (main data + QUICHE / PIES / OTHER / SWEETS / GEMA ORDERS sheets) | Stock + Products pages, and Sync from XLSM |
Airline Wholesale Stock Count / AIRLINE ORDERS sheet ("Alex's excel") | Production Planning page |
TARTLET ORDERS 2026.xlsx | Tartlet Orders page |
The app does not yet handle customer orders end to end (intake → invoice → allocate → dispatch). That is the new module described in workflow.xlsx and planned in IMPLEMENTATION_PLAN.md.
2Module status
| # | Module | Page / entry | Status | Notes |
|---|---|---|---|---|
| 1 | Login / session | ? (login form) | ✅ Live | Username + password, PHP session. No password reset, no lockout. |
| 2 | Stock (home) | ? | ✅ Live | Greeting card, product search with autocomplete, product card with live stock (units + boxes), Stock IN / Stock OUT form (units or boxes), last 25 transactions. |
| 3 | Products | ?page=products | ✅ Live | Table with Code, Name, Category, Subcategory, Pack, Base, Live stock. Search + category + subcategory filters. Admin: add / edit / delete, inline "set live stock". |
| 4 | Sync from XLSM | Hamburger → Sync, or Products toolbar | ✅ Live (just deployed) | Upload .xlsm/.xlsx → preview → apply. Upserts catalog from "main data", sets stock from category sheets via adjustment transactions. |
| 5 | Production Planning | ?page=plan | ✅ Live | Airline order matrix: products × (destination + date) columns. Live stock, boxes, total ordered, stock-after, to-produce (units + cartons). Cell colours, add column / add week, save / load / delete snapshots, print. |
| 6 | Tartlet Orders | ?page=tartlets | ✅ Live | Client orders × 16 tartlet sizes. Due date/time, Delivery / Pick-up, notes. Totals, in-stock, NEED TO MAKE rows. Editable "as at" date, date sort ↑↓, cell colour picker, save / load snapshots, print. |
| 7 | Users | ?page=users | ✅ Live (admin) | Add / edit / delete users, roles admin (Super Admin) and user. Change own password. Last admin cannot be deleted. |
| 8 | Export | Hamburger → Export | ✅ Live | Stock levels and full transaction log as .xlsx. |
| 9 | Orders & Dispatch (new) | — | ⬜ Not started | See workflow.xlsx, TRD.md §5, IMPLEMENTATION_PLAN.md. |
| 10 | Dashboard (new home page) | — | ⬜ Not started | Role-based daily dashboard + "seen today" tracking. DASHBOARD.md |
| 11 | Hermes Reminder Bot (new) | — | ⬜ Not started | Message → SMS → call ladder for staff who haven't seen the dashboard. DASHBOARD.md §4 |
| 12 | Airline analytics (new) | — | 🟡 Analysis done | Patterns from 216 POs (Jul 2025–Apr 2026). AIRLINE_ANALYSIS.md |
3Recent changes (this work session)
- Tartlets: "as at" date now updates after Apply (dedicated text span).
- Tartlets: orders auto-sorted by due date; clickable ↑↓ sort on DATE DUE OUT.
- Tartlets: per-cell colour picker restored; custom colours no longer overridden by default yellow.
- Products: separate Subcategory column and filter.
- XLSM sync: parser rewritten to match the real
Stock Control.xlsmlayout (sheet-name aware). - Hamburger menu: new Sync section opening an upload modal (drag-and-drop or browse).
- Location corrected to Marrickville, NSW (greeting card + weather) in the local copy — not yet uploaded (new domain).
4Known issues and risks
| Severity | Issue | Impact | Suggested fix |
|---|---|---|---|
| High | No CSRF tokens on any POST form or JSON API | A malicious page could trigger actions while a user is logged in | Add per-session token, check on every POST (Phase 0) |
| High | No automated DB backup | stock.db loss = loss of all history | Nightly copy of stock.db outside public_html (Phase 0) |
| Medium | session_regenerate_id() not called on login | Session fixation risk | One-line fix at login |
| Medium | If users table is ever empty, app seeds admin / admin | Weak default credentials | Keep table non-empty; remove seed or force password change |
| Medium | Single 5,950-line file | Every new module makes edits riskier and slower | Split new modules into includes/ while keeping one entry point (Phase 0) |
| Medium | Deploy is manual FTP over plain (non-TLS) FTP | Credentials sent in clear; no rollback | Switch to FTPS/SFTP if host supports; keep dated backups of the PHP file |
| Low | apply_xlsm_sync refuses to run if the file has catalog rows but no stock rows | Catalog-only sync is blocked | Check rows or catalog |
| Low | currentStock() runs 2 queries per product | Pages slow down as products/transactions grow | One aggregate query per page |
| Low | Plan items (plan_items) and Tartlet sizes are separate lists from products | Codes can drift out of sync | Link by product code (Phase 1 of new module) |
| Info | stock.db sits in public_html | Returned HTTP 403 on the old domain (checked 4 Oct 2026) — re-check on skycrest.id | Move outside web root when convenient |
| Info | Domain moved to skycrest.id | FTP path / deploy target in deployment.md needs updating | Confirm new FTP host & folder before next upload |
5Operating constraints (must not be broken)
- Never reset, recreate or overwrite
stock.db. All schema changes are additive (CREATE TABLE IF NOT EXISTS,ALTER TABLE ADD COLUMN). - Never change existing users or passwords during deployment.
- FTP credentials live only in the local
deployment.md— never in source, never uploaded to the server, never in these docs. - Deploy = upload
stock_control.phponly (plain FTP, passive mode).
6My understanding of the business (from workflow.xlsx)
Two businesses share the kitchen and stock:
- SkyCrest — airline customers. Invoiced in SkyCrest Xero.
- Annie Makes Cakes — all non-airline customers. Invoiced in Annie Makes Cakes Xero.
Both follow the same core loop — receive order → invoice → print & send to kitchen → confirm to customer → packing checks stock → allocate or make → dispatch → remove from stock — with differences at intake (airline orders arrive as POs and go straight onto the production planner; non-airline orders first need an availability and delivery-date check) and at dispatch (airline interstate goes via Roadmaster by port; Sydney is a driver run; non-airline is a single schedule for all customers).
Full flow in APP_FLOW.md §3.
7Open questions before building the new module
These decide scope. Defaults I've assumed in the TRD are in brackets.
Answered: hosting supports scheduled tasks (cron) ✅ · airline orders arrive by email as system-generated POs (dnata, Gate Gourmet) — see AIRLINE_ANALYSIS.md §1.
- Xero: connect via the Xero API, or keep invoicing in Xero manually and just record the invoice number? (Manual — record invoice no. only, for V1.)
- Email intake: should the app read orders from the mailbox, or does someone key them in? (Keyed in manually, with the PO/PDF attached.)
- Confirmation email: should the app send it, or does the team send from Outlook/Gmail? (App generates the text; team sends it, for V1.)
- Allocation: does "move stock & allocate" mean reserve (stock still on hand, but committed) and the final deduction happens at dispatch? (Yes — reserve on allocate, deduct on dispatch.)
- Production planner link: should airline orders entered in the new module automatically appear as quantities on the Production Planning page? (Yes — the new module becomes the source; planner reads from it.)
- Tartlet orders: are these non-airline orders that should eventually move into the new Orders module? (Leave Tartlets as-is in V1; merge later.)
- Hermes host: a small VPS or an always-on office PC for the Hermes gateway? Which channel do staff use — WhatsApp or Telegram? (VPS + WhatsApp.)
- Roadmaster / driver booking: in-app booking, or just a printable/exportable delivery schedule? (Printable schedule + "booked" tick box and reference.)
- Who does what: do packing staff need their own login with limited rights (e.g. can allocate and dispatch but not edit prices/customers)? (Add a
packingrole.) - Customer list: is there a master list of customers / airlines / ports to load, or build it up as orders arrive? (Build as we go; seed airlines from the planner destinations.)