Files · Supplier Order Optimizer for Small Restaurant Groups

# Supplier Order Optimizer for Small Restaurant Groups
 
Auto-calculate order quantities from current pars and rolling usage history.
 
The chef or kitchen manager manually compares a spreadsheet of past supplier orders against current inventory pars every week. This takes 2-3 hours and often leads to over-ordering (waste) or under-ordering (emergency runs). This project automates that workflow: upload a spreadsheet of inventory pars, usage history, and past orders — the system uses LLM reasoning to generate optimised order quantities, reducing food waste and emergency runs.
 
## How it works
 
1. **Upload** — Upload an XLSX spreadsheet with three sheets: `par_levels`, `usage_history`, `past_orders`
2. **Parse** — The spreadsheet service reads and validates each sheet with Zod schemas
3. **Analyse** — The LLM service (Vercel AI SDK + OpenAI) generates optimised order suggestions with explanations
4. **Cache** — Responses are cached in Redis via `@reaatech/llm-cache` to reduce costs on repeated runs
5. **Persist** — Order history is stored in memory via `@reaatech/agent-memory-*` for future reference
6. **Export** — Results can be exported as a new XLSX spreadsheet
 
## API Reference
 
### `POST /api/optimize-order`
 
Upload an XLSX file (multipart/form-data) or send a JSON body:
 
**Multipart:**
```
Content-Type: multipart/form-data
file: <xlsx buffer>
supplierId: "supplier-1"
```
 
**JSON:**
```json
{
  "supplierId": "supplier-1",
  "parLevels": [{ "itemId": "item-1", "name": "Tomatoes", "targetQuantity": 50, "reorderPoint": 10 }],
  "usageHistory": [{ "itemId": "item-1", "date": "2025-01-01", "quantityUsed": 15 }],
  "pastOrders": [{ "itemId": "item-1", "supplierId": "supplier-1", "quantity": 50, "orderDate": "2025-01-01", "unitCost": 0.5 }]
}
```
 
Returns 200 with `OptimizationResult`:
```json
{
  "supplierId": "supplier-1",
  "supplierName": "supplier-1",
  "suggestions": [{ "itemId": "item-1", "itemName": "Tomatoes", "currentStock": 20, "parLevel": 50, "suggestedOrderQuantity": 35, "reason": "Expected demand based on past usage", "urgency": "high" }],
  "totalCost": 17.5,
  "orderByDate": "2025-01-08",
  "generatedAt": "2025-01-01T12:00:00.000Z"
}
```
 
### `GET /api/par-levels?tenant=default`
 
Returns stored par levels for the given tenant.
 
### `POST /api/par-levels?tenant=default`
 
```json
[{ "itemId": "item-1", "name": "Tomatoes", "targetQuantity": 50, "reorderPoint": 10 }]
```
Returns 201 with `{ "stored": 1 }`.
 
### `GET /api/suppliers?tenant=default`
 
Returns stored suppliers.
 
### `POST /api/suppliers?tenant=default`
 
```json
[{ "id": "supplier-1", "name": "Produce Co.", "leadTimeDays": 3, "minimumOrder": 20 }]
```
Returns 201 with `{ "stored": 1 }`.
 
## Environment variables
 
| Variable | Description | Default |
|---|---|---|
| `OPENAI_API_KEY` | OpenAI API key for LLM | (required) |
| `LANGFUSE_PUBLIC_KEY` | Langfuse public key for tracing | (required) |
| `LANGFUSE_SECRET_KEY` | Langfuse secret key | (required) |
| `LANGFUSE_BASE_URL` | Langfuse base URL | `https://cloud.langfuse.com` |
| `REDIS_URL` | Redis connection URL | `redis://localhost:6379` |
| `LLM_MODEL` | OpenAI model ID | `gpt-5.2-mini` |
| `MAX_RETRY_ATTEMPTS` | Max retries for LLM calls | `3` |
| `CACHE_TTL_SECONDS` | Cache TTL for LLM responses | `3600` |
 
## Tech stack
 
- **Framework:** Next.js 16+ (App Router)
- **LLM:** Vercel AI SDK (`ai`) + OpenAI (`@ai-sdk/openai`)
- **Persistence:** `@reaatech/agent-memory-core`, `@reaatech/agent-memory-storage`, `@reaatech/agent-memory-retrieval`
- **Task tracking:** `@reaatech/a2a-reference-persistence`
- **Caching:** `@reaatech/llm-cache`, `@reaatech/llm-cache-adapters-redis`
- **Spreadsheets:** `xlsx` (SheetJS)
- **Validation:** `zod`
- **Observability:** `langfuse`
 
## Development
 
```bash
pnpm dev        # Start the dev server
pnpm test       # Run tests with coverage
pnpm typecheck  # TypeScript type checking
pnpm lint       # ESLint checks
```