1290 lines
25 KiB
Markdown
1290 lines
25 KiB
Markdown
## Spreadsheet analysis summary
|
|
|
|
The workbook is effectively a costing and pricing model with three core calculation layers:
|
|
|
|
### 1. Raw Materials
|
|
|
|
Sheet: **`C- Raw Products Costs`**
|
|
|
|
Table: **`StraightGrain`**
|
|
|
|
This is the base input table.
|
|
|
|
Main fields:
|
|
|
|
| Area | Purpose |
|
|
| --- | --- |
|
|
| Raw Materials | Ingredient / material name |
|
|
| Market Value Uncleaned Grain | Base cost |
|
|
| Unit of Measure | kg, per tonne, bag, etc. |
|
|
| Kg per unit of measure | Converts purchase unit into kg |
|
|
| Waste % of Loss | Loss / shrinkage assumption |
|
|
| Loss | Formula-driven cost loss |
|
|
| Cost per Unit of Measure | Base cost + loss |
|
|
| Cost per Kg | Final reusable cost input |
|
|
|
|
Core formula pattern:
|
|
|
|
```
|
|
Loss = Market Value * Waste %
|
|
Cost per Unit = Loss + Market Value
|
|
Cost per Kg = Cost per Unit / Kg per Unit
|
|
```
|
|
|
|
This should become the foundation table in the app, with price history and versioning.
|
|
|
|
---
|
|
|
|
### 2. Mix Master
|
|
|
|
Sheet: **`M - All`**
|
|
|
|
Table: **`AllMix`**
|
|
|
|
This is the recipe / bill-of-materials table.
|
|
|
|
Main purpose:
|
|
|
|
| Area | Purpose |
|
|
| --- | --- |
|
|
| Client | Product/customer grouping |
|
|
| Product | Mix name |
|
|
| Total KGs per mix | Formula sum of all ingredients |
|
|
| Ingredient columns | Quantity of each raw material in the mix |
|
|
| Mix cost section | Calculates cost per mix and cost per kg |
|
|
|
|
Core formula pattern:
|
|
|
|
```
|
|
Total KGs per mix = SUM(all ingredient quantities)
|
|
|
|
Ingredient Cost = Ingredient Quantity * Raw Material Cost per Kg
|
|
|
|
Total Mix Cost = SUM(all ingredient costs)
|
|
|
|
Mix Cost per Kg = Total Mix Cost / Total KGs per mix
|
|
```
|
|
|
|
This should become a structured recipe module, not a wide spreadsheet table. In the app, each mix should have many ingredient rows rather than 60+ ingredient columns.
|
|
|
|
Important workbook comments found here:
|
|
|
|
| Location | Comment |
|
|
| --- | --- |
|
|
| `M - All!B8` | Need to confirm recipe |
|
|
| `M - All!D10` | Mix is 280, but bulka fits 550, therefore 275 for 2 batches |
|
|
| `M - All!AB53` | 85 same as Mayreef, was 110 according to files |
|
|
|
|
These should become data-quality notes or review flags in the app.
|
|
|
|
---
|
|
|
|
### 3. Product / Unit Cost / Price
|
|
|
|
Sheet: **`Product Cost - Price`**
|
|
|
|
Table: **`ProductCost`**
|
|
|
|
This is the main output table.
|
|
|
|
Main fields:
|
|
|
|
| Area | Purpose |
|
|
| --- | --- |
|
|
| Client | Pricing group |
|
|
| Item ID | Xero/product identifier |
|
|
| Name | Product name |
|
|
| Mix / Product Name | Links back to Mix Master |
|
|
| Standard or Bulka | Packaging / sale type |
|
|
| Own Bag | Packaging logic |
|
|
| Std Unit of Measure | Usually kg, bag, tonne, etc. |
|
|
| Items Per Pallet | Used in freight calculations |
|
|
| Bagging Process | Determines process costs |
|
|
| Distributor / Wholesale | Target margin inputs or outputs |
|
|
| Cleaned Product | Mix cost pulled from `M - All` |
|
|
| Grading / Bagging / Cracking | Process costs |
|
|
| Bag Cost | Packaging cost logic |
|
|
| Freight Cost | Freight allocation |
|
|
| Finished Product Delivered | Final delivered product cost |
|
|
| Price columns | Distributor and wholesale selling price outputs |
|
|
|
|
Core calculation pattern:
|
|
|
|
```
|
|
Cleaned Product = Mix Cost per Kg
|
|
|
|
Process Cost = Grading + Bagging + Cracking
|
|
|
|
Packaging Cost = Based on Standard / Bulka / Own Bag / Bag Size
|
|
|
|
Freight Cost = Based on pallet, kg, product type, or special case
|
|
|
|
Finished Product Delivered =
|
|
Cleaned Product
|
|
+ Process Cost
|
|
+ Packaging Cost
|
|
+ Freight Cost
|
|
|
|
Selling Price =
|
|
Finished Product Delivered / (1 - Margin)
|
|
```
|
|
|
|
Important workbook comments found here:
|
|
|
|
| Location | Comment |
|
|
| --- | --- |
|
|
| `Product Cost - Price!R4` | 20kg bag or by KG if Ton |
|
|
| `Product Cost - Price!X14` | Includes 24% profit. Cost per tonne $68 |
|
|
| `Product Cost - Price!C50` | In OLD - Ian McKay Stock Mix 20kg |
|
|
|
|
These should also become structured notes / flags.
|
|
|
|
---
|
|
|
|
## Recommended app direction
|
|
|
|
Build the app around **controlled costing scenarios**, not just data entry.
|
|
|
|
The client should enter raw material prices, recipes, product settings, packaging assumptions, freight costs, and margin rules. The backend should calculate final unit cost and pricing outputs. Power BI should consume clean, flattened API/database views.
|
|
|
|
The most important design decision:
|
|
|
|
> The spreadsheet formulas should move into the FastAPI backend, not the Svelte frontend.
|
|
>
|
|
|
|
The frontend should display inputs, validation warnings, scenario comparisons, and results. The backend should own the calculation engine, audit trail, versioning, and API outputs.
|
|
|
|
---
|
|
|
|
# Proposed system architecture
|
|
|
|
```
|
|
Svelte App
|
|
|
|
|
|-- Client Portal
|
|
| |-- Manage raw materials
|
|
| |-- Manage mix recipes
|
|
| |-- Manage products
|
|
| |-- Run price simulations
|
|
| |-- Submit / approve changes
|
|
|
|
|
|-- Consultancy/Admin Portal
|
|
|-- Review client changes
|
|
|-- Manage assumptions
|
|
|-- Compare scenarios
|
|
|-- Export / publish to Power BI
|
|
|-- Audit history
|
|
|
|
FastAPI Backend
|
|
|
|
|
|-- Auth + tenant access
|
|
|-- Data validation
|
|
|-- Cost calculation engine
|
|
|-- Scenario/version control
|
|
|-- Xero item mapping
|
|
|-- Power BI API endpoints
|
|
|-- Audit logging
|
|
|
|
Database
|
|
|
|
|
|-- PostgreSQL recommended
|
|
|-- SQLite acceptable only for prototype
|
|
```
|
|
|
|
---
|
|
|
|
# Core modules
|
|
|
|
## 1. Authentication and tenancy
|
|
|
|
Because this is consultancy-built software for a client, the app should support separation between:
|
|
|
|
| Role | Access |
|
|
| --- | --- |
|
|
| Client User | Enter and update data |
|
|
| Client Manager | Approve changes and scenarios |
|
|
| Consultancy User | Review, correct, simulate, publish |
|
|
| Admin | Manage users, assumptions, integrations |
|
|
|
|
Even if there is only one client at launch, build with `tenant_id` from the start.
|
|
|
|
---
|
|
|
|
## 2. Raw Materials module
|
|
|
|
This replaces **`C- Raw Products Costs`**.
|
|
|
|
### Required features
|
|
|
|
The client should be able to manage:
|
|
|
|
| Field | Notes |
|
|
| --- | --- |
|
|
| Raw Material Name | e.g. Maize, Barley, Acid Buf |
|
|
| Supplier / Xero Item | Optional but useful |
|
|
| Market Value | Main editable price input |
|
|
| Unit of Measure | kg, tonne, 20kg bag, etc. |
|
|
| Kg per Unit | Used for conversion |
|
|
| Waste % / Loss % | Editable assumption |
|
|
| Effective Date | Required for price history |
|
|
| Status | Draft, Active, Archived |
|
|
| Notes | For exceptions |
|
|
|
|
### Backend calculation
|
|
|
|
```
|
|
loss_cost = market_value * waste_percentage
|
|
|
|
cost_per_unit = market_value + loss_cost
|
|
|
|
cost_per_kg = cost_per_unit / kg_per_unit
|
|
```
|
|
|
|
### Key requirement
|
|
|
|
Do not overwrite old prices. Store price versions.
|
|
|
|
Example tables:
|
|
|
|
```
|
|
raw_materials
|
|
raw_material_price_versions
|
|
```
|
|
|
|
This allows Power BI to show price movement over time.
|
|
|
|
---
|
|
|
|
## 3. Mix Master module
|
|
|
|
This replaces **`M - All`**.
|
|
|
|
The spreadsheet has a wide format, where each raw material is a column. The app should use a proper relational structure.
|
|
|
|
### Instead of this
|
|
|
|
```
|
|
Product | Maize | Wheat | Barley | Canola | ...
|
|
```
|
|
|
|
### Use this
|
|
|
|
```
|
|
Mix
|
|
- Mix Name
|
|
- Client
|
|
- Status
|
|
- Version
|
|
|
|
Mix Ingredients
|
|
- Mix ID
|
|
- Raw Material ID
|
|
- Quantity Kg
|
|
```
|
|
|
|
### Required features
|
|
|
|
| Feature | Purpose |
|
|
| --- | --- |
|
|
| Mix list | View all current mixes |
|
|
| Mix detail page | Edit ingredient quantities |
|
|
| Ingredient search | Add raw materials easily |
|
|
| Auto total kg | Sum all ingredient quantities |
|
|
| Cost per kg | Calculated by backend |
|
|
| Recipe validation | Flag missing costs or unusual quantities |
|
|
| Versioning | Compare current vs proposed recipe |
|
|
| Notes / review flags | Capture comments like “need to confirm recipe” |
|
|
|
|
### Backend calculation
|
|
|
|
```
|
|
ingredient_cost = ingredient_quantity_kg * raw_material_cost_per_kg
|
|
|
|
total_mix_cost = SUM(ingredient_cost)
|
|
|
|
total_mix_kg = SUM(ingredient_quantity_kg)
|
|
|
|
mix_cost_per_kg = total_mix_cost / total_mix_kg
|
|
```
|
|
|
|
---
|
|
|
|
## 4. Product Cost / Unit Cost module
|
|
|
|
This replaces **`Product Cost - Price`**.
|
|
|
|
This is the main commercial output of the app.
|
|
|
|
### Product setup fields
|
|
|
|
| Field | Source |
|
|
| --- | --- |
|
|
| Client | Existing spreadsheet |
|
|
| Item ID | Xero / product system |
|
|
| Name | Product name |
|
|
| Mix / Product Name | Links to Mix Master |
|
|
| Standard or Bulka | Pricing logic |
|
|
| Own Bag | Packaging logic |
|
|
| Unit of Measure | Pricing logic |
|
|
| Items Per Pallet | Freight logic |
|
|
| Bagging Process | Process cost lookup |
|
|
| Distributor Margin | Pricing rule |
|
|
| Wholesale Margin | Pricing rule |
|
|
|
|
### Cost output fields
|
|
|
|
| Output | Logic |
|
|
| --- | --- |
|
|
| Cleaned Product Cost | From Mix Master |
|
|
| Grading Cost | From process assumptions |
|
|
| Bagging Cost | From process assumptions |
|
|
| Cracking Cost | From process assumptions |
|
|
| Bag Cost | From packaging assumptions |
|
|
| Freight Cost | From freight assumptions |
|
|
| Finished Product Delivered | Sum of above |
|
|
| Distributor Price | Cost / margin |
|
|
| Wholesale Price | Cost / margin |
|
|
|
|
### Backend calculation engine
|
|
|
|
Create a dedicated backend service:
|
|
|
|
```
|
|
/app/services/costing_engine.py
|
|
```
|
|
|
|
Suggested calculation order:
|
|
|
|
```
|
|
1. Load product
|
|
2. Load linked mix
|
|
3. Calculate mix cost per kg
|
|
4. Apply process costs
|
|
5. Apply packaging costs
|
|
6. Apply freight costs
|
|
7. Calculate finished delivered cost
|
|
8. Apply distributor / wholesale margins
|
|
9. Return full calculation breakdown
|
|
```
|
|
|
|
Every calculated product should return a transparent cost build-up.
|
|
|
|
Example output:
|
|
|
|
```
|
|
{
|
|
"product_id":123,
|
|
"cleaned_product_cost":0.5444,
|
|
"grading_cost":0.055,
|
|
"bagging_cost":0.04,
|
|
"cracking_cost":0,
|
|
"bag_cost":0.63,
|
|
"freight_cost":1.45,
|
|
"finished_product_delivered":14.31,
|
|
"distributor_price":18.46,
|
|
"wholesale_price":null,
|
|
"warnings": []
|
|
}
|
|
```
|
|
|
|
---
|
|
|
|
## 5. Assumptions module
|
|
|
|
The spreadsheet has embedded assumption tables around process costs, packaging costs, freight costs, and margins. These should not remain hidden in formulas.
|
|
|
|
Create editable assumption tables for:
|
|
|
|
| Assumption type | Examples |
|
|
| --- | --- |
|
|
| Process costs | Grading, cracking, bagging |
|
|
| Packaging costs | 20kg bag, bulka bag, wrap, slip sheet |
|
|
| Freight costs | Freight per pallet, special freight rules |
|
|
| Margin rules | Distributor / wholesale margin by client/category |
|
|
| Special cases | Peckish, Hay & Straw, PHF Horse Mixes |
|
|
|
|
This is important because the current workbook has hardcoded special logic in formulas. In the app, these rules should be visible and maintainable.
|
|
|
|
---
|
|
|
|
## 6. Scenario and simulation module
|
|
|
|
This is one of the most important parts based on your notes.
|
|
|
|
The client needs to simulate and negotiate prices. That means the app should support scenarios.
|
|
|
|
### Scenario examples
|
|
|
|
| Scenario | Purpose |
|
|
| --- | --- |
|
|
| Current Standard | Approved live pricing |
|
|
| Proposed Raw Material Increase | Test new input costs |
|
|
| Supplier Price Change | Test new Xero prices |
|
|
| Recipe Change | Change mix ingredients |
|
|
| Margin Negotiation | Test distributor/wholesale changes |
|
|
| Freight Increase | Test operational cost changes |
|
|
|
|
### Scenario workflow
|
|
|
|
```
|
|
Create Scenario
|
|
↓
|
|
Choose baseline
|
|
↓
|
|
Modify raw costs / mix / margins / freight
|
|
↓
|
|
Run calculation
|
|
↓
|
|
Compare against current
|
|
↓
|
|
Review margin impact
|
|
↓
|
|
Approve or reject
|
|
↓
|
|
Publish to reporting layer
|
|
```
|
|
|
|
### Comparison outputs
|
|
|
|
The UI should show:
|
|
|
|
| Comparison | Example |
|
|
| --- | --- |
|
|
| Old cost vs new cost | `$14.31 → $15.02` |
|
|
| Old price vs new price | `$18.46 → $19.38` |
|
|
| Margin impact | `22.5% → 21.1%` |
|
|
| Product impact | Which SKUs changed most |
|
|
| Raw material impact | Which ingredient caused the increase |
|
|
|
|
---
|
|
|
|
# Suggested database model
|
|
|
|
## Core tables
|
|
|
|
```
|
|
tenants
|
|
users
|
|
roles
|
|
|
|
clients
|
|
xero_items
|
|
|
|
raw_materials
|
|
raw_material_price_versions
|
|
|
|
mixes
|
|
mix_versions
|
|
mix_ingredients
|
|
|
|
products
|
|
product_versions
|
|
|
|
process_cost_rules
|
|
packaging_cost_rules
|
|
freight_cost_rules
|
|
margin_rules
|
|
|
|
costing_scenarios
|
|
costing_scenario_items
|
|
costing_results
|
|
|
|
stock_take
|
|
sales_history
|
|
|
|
data_quality_notes
|
|
audit_log
|
|
```
|
|
|
|
---
|
|
|
|
## Key relationships
|
|
|
|
```
|
|
raw_materials
|
|
→ raw_material_price_versions
|
|
|
|
mixes
|
|
→ mix_versions
|
|
→ mix_ingredients
|
|
→ raw_materials
|
|
|
|
products
|
|
→ product_versions
|
|
→ mixes
|
|
|
|
costing_scenarios
|
|
→ costing_results
|
|
→ products
|
|
```
|
|
|
|
---
|
|
|
|
# Svelte app plan
|
|
|
|
## Main navigation
|
|
|
|
```
|
|
Dashboard
|
|
Raw Materials
|
|
Mix Master
|
|
Products / Unit Costs
|
|
Scenarios
|
|
Price Review
|
|
Xero Mapping
|
|
Power BI Outputs
|
|
Admin
|
|
```
|
|
|
|
---
|
|
|
|
## 1. Dashboard
|
|
|
|
Purpose: high-level control view.
|
|
|
|
Show:
|
|
|
|
| Card | Purpose |
|
|
| --- | --- |
|
|
| Raw materials missing cost | Data quality |
|
|
| Mixes needing review | Recipe quality |
|
|
| Products with calculation warnings | Pricing quality |
|
|
| Latest approved scenario | Reporting state |
|
|
| Products changed since last publish | Governance |
|
|
| Average cost movement | Commercial insight |
|
|
|
|
---
|
|
|
|
## 2. Raw Materials page
|
|
|
|
Views:
|
|
|
|
```
|
|
Raw Materials List
|
|
Raw Material Detail
|
|
Price History
|
|
Bulk Upload
|
|
```
|
|
|
|
Features:
|
|
|
|
| Feature | Notes |
|
|
| --- | --- |
|
|
| Editable cost inputs | Market value, unit, kg conversion, waste |
|
|
| Price history | Required |
|
|
| Import from CSV/Xero | Future or phase 2 |
|
|
| Validation | Missing unit, zero kg, missing cost |
|
|
| Notes | Capture exceptions |
|
|
|
|
---
|
|
|
|
## 3. Mix Master page
|
|
|
|
Views:
|
|
|
|
```
|
|
Mix List
|
|
Mix Detail
|
|
Recipe Editor
|
|
Mix Cost Breakdown
|
|
Version Comparison
|
|
```
|
|
|
|
Recipe editor should feel like a clean table:
|
|
|
|
| Ingredient | Qty KG | Cost/KG | Line Cost | Notes |
|
|
| --- | --- | --- | --- | --- |
|
|
|
|
Footer:
|
|
|
|
```
|
|
Total KG
|
|
Total Cost
|
|
Cost per KG
|
|
Warnings
|
|
```
|
|
|
|
Validation examples:
|
|
|
|
| Warning | Reason |
|
|
| --- | --- |
|
|
| Missing raw material cost | Cannot calculate |
|
|
| Total kg is zero | Invalid recipe |
|
|
| Ingredient has no active price | Stale input |
|
|
| Recipe changed from approved version | Needs review |
|
|
| Comment unresolved | User needs to confirm |
|
|
|
|
---
|
|
|
|
## 4. Product / Unit Cost page
|
|
|
|
Views:
|
|
|
|
```
|
|
Product List
|
|
Product Detail
|
|
Cost Build-Up
|
|
Price Output
|
|
Margin Review
|
|
```
|
|
|
|
Product detail should show a transparent cost stack:
|
|
|
|
```
|
|
Cleaned Product Cost
|
|
+ Grading
|
|
+ Bagging
|
|
+ Cracking
|
|
+ Bag Cost
|
|
+ Freight Cost
|
|
= Finished Product Delivered
|
|
|
|
Then:
|
|
|
|
Distributor Price
|
|
Wholesale Price
|
|
Retail Price, if required
|
|
```
|
|
|
|
This is where the app becomes more useful than the spreadsheet.
|
|
|
|
---
|
|
|
|
## 5. Scenario page
|
|
|
|
Views:
|
|
|
|
```
|
|
Scenario List
|
|
Create Scenario
|
|
Scenario Workspace
|
|
Scenario Comparison
|
|
Approval View
|
|
```
|
|
|
|
Scenario workspace should allow the user to adjust:
|
|
|
|
| Input | Example |
|
|
| --- | --- |
|
|
| Raw material prices | Maize increases by 8% |
|
|
| Waste/loss assumptions | Waste from 2% to 4% |
|
|
| Mix ingredients | Change kg in recipe |
|
|
| Process costs | Bagging cost increase |
|
|
| Freight | New freight per pallet |
|
|
| Margin | Distributor margin change |
|
|
|
|
Output:
|
|
|
|
```
|
|
Before / After
|
|
Cost impact
|
|
Price impact
|
|
Margin impact
|
|
Products affected
|
|
Exportable result set
|
|
```
|
|
|
|
---
|
|
|
|
## 6. Price Review page
|
|
|
|
This replaces **`Price Review 30082025`**.
|
|
|
|
Purpose:
|
|
|
|
Compare old prices, proposed prices, and review status.
|
|
|
|
Suggested columns:
|
|
|
|
| Product | Current Wholesale | Proposed Wholesale | Difference | Current Distributor | Proposed Distributor | Difference | Status |
|
|
| --- | --- | --- | --- | --- | --- | --- | --- |
|
|
|
|
Statuses:
|
|
|
|
```
|
|
Draft
|
|
Reviewed
|
|
Approved
|
|
Rejected
|
|
Manual Override
|
|
Needs Client Review
|
|
```
|
|
|
|
---
|
|
|
|
## 7. Xero Mapping page
|
|
|
|
Because the raw material and product costs need to be tracked against Xero/product data, create a mapping layer.
|
|
|
|
Fields:
|
|
|
|
| Field | Purpose |
|
|
| --- | --- |
|
|
| Xero Item ID | External system key |
|
|
| SKU | Product code |
|
|
| Product Name | User-facing |
|
|
| Local Product ID | Internal app product |
|
|
| Last synced date | Data freshness |
|
|
| Sync status | Matched / unmatched / conflict |
|
|
|
|
Phase 1 can use CSV import/export.
|
|
|
|
Phase 2 can use Xero API integration.
|
|
|
|
---
|
|
|
|
## 8. Power BI Outputs page
|
|
|
|
This should expose the data model in a clean reporting shape.
|
|
|
|
Power BI should not ingest messy operational tables directly. Create purpose-built API endpoints or database views.
|
|
|
|
Recommended reporting outputs:
|
|
|
|
```
|
|
vw_raw_material_costs_current
|
|
vw_raw_material_cost_history
|
|
vw_mix_costs_current
|
|
vw_mix_ingredients_current
|
|
vw_product_costs_current
|
|
vw_product_price_outputs
|
|
vw_scenario_comparison
|
|
vw_price_review_status
|
|
vw_data_quality_issues
|
|
```
|
|
|
|
Power BI can ingest either:
|
|
|
|
```
|
|
FastAPI JSON endpoints
|
|
```
|
|
|
|
or
|
|
|
|
```
|
|
PostgreSQL views
|
|
```
|
|
|
|
For reliability, PostgreSQL views are better if Power BI can connect to the database securely.
|
|
|
|
---
|
|
|
|
# FastAPI backend plan
|
|
|
|
## Suggested backend structure
|
|
|
|
```
|
|
/backend
|
|
/app
|
|
main.py
|
|
/api
|
|
raw_materials.py
|
|
mixes.py
|
|
products.py
|
|
scenarios.py
|
|
costing.py
|
|
imports.py
|
|
powerbi.py
|
|
admin.py
|
|
/models
|
|
raw_material.py
|
|
mix.py
|
|
product.py
|
|
scenario.py
|
|
costing_result.py
|
|
user.py
|
|
/schemas
|
|
raw_material.py
|
|
mix.py
|
|
product.py
|
|
scenario.py
|
|
costing.py
|
|
/services
|
|
costing_engine.py
|
|
scenario_engine.py
|
|
import_excel.py
|
|
xero_mapping.py
|
|
validation.py
|
|
audit.py
|
|
/db
|
|
session.py
|
|
migrations
|
|
```
|
|
|
|
---
|
|
|
|
## Key API endpoints
|
|
|
|
### Raw materials
|
|
|
|
```
|
|
GET /api/raw-materials
|
|
POST /api/raw-materials
|
|
GET /api/raw-materials/{id}
|
|
PATCH /api/raw-materials/{id}
|
|
POST /api/raw-materials/{id}/prices
|
|
GET /api/raw-materials/{id}/price-history
|
|
```
|
|
|
|
### Mixes
|
|
|
|
```
|
|
GET /api/mixes
|
|
POST /api/mixes
|
|
GET /api/mixes/{id}
|
|
PATCH /api/mixes/{id}
|
|
POST /api/mixes/{id}/ingredients
|
|
PATCH /api/mixes/{id}/ingredients/{ingredient_id}
|
|
DELETE /api/mixes/{id}/ingredients/{ingredient_id}
|
|
GET /api/mixes/{id}/cost-breakdown
|
|
```
|
|
|
|
### Products
|
|
|
|
```
|
|
GET /api/products
|
|
POST /api/products
|
|
GET /api/products/{id}
|
|
PATCH /api/products/{id}
|
|
GET /api/products/{id}/cost-breakdown
|
|
GET /api/products/{id}/price-output
|
|
```
|
|
|
|
### Scenarios
|
|
|
|
```
|
|
GET /api/scenarios
|
|
POST /api/scenarios
|
|
GET /api/scenarios/{id}
|
|
POST /api/scenarios/{id}/run
|
|
GET /api/scenarios/{id}/results
|
|
POST /api/scenarios/{id}/approve
|
|
POST /api/scenarios/{id}/reject
|
|
```
|
|
|
|
### Power BI
|
|
|
|
```
|
|
GET /api/powerbi/raw-material-costs
|
|
GET /api/powerbi/mix-costs
|
|
GET /api/powerbi/product-costs
|
|
GET /api/powerbi/scenario-results
|
|
GET /api/powerbi/data-quality-issues
|
|
```
|
|
|
|
---
|
|
|
|
# Calculation engine design
|
|
|
|
Use explicit calculation functions instead of trying to replicate Excel formulas blindly.
|
|
|
|
Example services:
|
|
|
|
```
|
|
calculate_raw_material_cost()
|
|
calculate_mix_cost()
|
|
calculate_product_cost()
|
|
calculate_selling_price()
|
|
calculate_scenario()
|
|
```
|
|
|
|
Each function should return:
|
|
|
|
```
|
|
calculated value
|
|
source inputs
|
|
warnings
|
|
formula version
|
|
timestamp
|
|
```
|
|
|
|
This gives you an audit trail and makes Power BI more trustworthy.
|
|
|
|
---
|
|
|
|
# Data validation rules
|
|
|
|
The app should actively prevent spreadsheet-style silent errors.
|
|
|
|
## Raw materials
|
|
|
|
| Rule | Severity |
|
|
| --- | --- |
|
|
| Missing market value | Error |
|
|
| Kg per unit is zero | Error |
|
|
| Missing unit of measure | Warning |
|
|
| Waste % over threshold | Warning |
|
|
| Duplicate raw material name | Warning |
|
|
|
|
## Mixes
|
|
|
|
| Rule | Severity |
|
|
| --- | --- |
|
|
| Mix has no ingredients | Error |
|
|
| Total kg is zero | Error |
|
|
| Ingredient has no active price | Error |
|
|
| Recipe has unresolved note | Warning |
|
|
| Ingredient quantity unusually high | Warning |
|
|
|
|
## Products
|
|
|
|
| Rule | Severity |
|
|
| --- | --- |
|
|
| Product has no linked mix | Error |
|
|
| Missing unit of measure | Error |
|
|
| Missing bagging process | Warning |
|
|
| Missing margin rule | Error |
|
|
| Freight rule not found | Warning |
|
|
| Manual override exists | Warning |
|
|
|
|
---
|
|
|
|
# Import and migration plan
|
|
|
|
## Phase 1: Workbook audit and import
|
|
|
|
Build an Excel import script that extracts:
|
|
|
|
```
|
|
StraightGrain table
|
|
AllMix table
|
|
ProductCost table
|
|
Price Review table
|
|
Stock Take data
|
|
Sales R12M data
|
|
Workbook comments
|
|
```
|
|
|
|
Do not import formulas as the production logic. Import formulas only as a reference for testing.
|
|
|
|
---
|
|
|
|
## Phase 2: Formula parity testing
|
|
|
|
Before replacing the spreadsheet, create test cases from known workbook rows.
|
|
|
|
For example:
|
|
|
|
```
|
|
Raw material:
|
|
Acid Buf cost per kg
|
|
|
|
Mix:
|
|
Pigeon Mix cost per kg
|
|
|
|
Product:
|
|
Specialty Pigeon Breeder 20kg finished delivered cost
|
|
Distributor price
|
|
Wholesale price
|
|
```
|
|
|
|
The backend should calculate the same or near-same results as the workbook before the client uses it.
|
|
|
|
---
|
|
|
|
## Phase 3: Build operational app
|
|
|
|
Start with these modules:
|
|
|
|
```
|
|
1. Raw Materials
|
|
2. Mix Master
|
|
3. Product Cost
|
|
4. Scenario calculator
|
|
5. Power BI endpoint
|
|
```
|
|
|
|
Leave Xero integration, advanced approvals, and full stock/sales analytics until after the costing logic is stable.
|
|
|
|
---
|
|
|
|
# Recommended delivery phases
|
|
|
|
## Phase 0 — Discovery and formula mapping
|
|
|
|
Outcome:
|
|
|
|
```
|
|
Confirmed calculation rules
|
|
Confirmed edge cases
|
|
Confirmed client workflow
|
|
Confirmed Power BI output requirements
|
|
```
|
|
|
|
Tasks:
|
|
|
|
```
|
|
Map every key formula
|
|
Identify hardcoded assumptions
|
|
Identify manual overrides
|
|
Confirm Xero source fields
|
|
Confirm which spreadsheet sheets are still required
|
|
```
|
|
|
|
---
|
|
|
|
## Phase 1 — Data model and import prototype
|
|
|
|
Outcome:
|
|
|
|
```
|
|
Workbook imported into database
|
|
Raw materials, mixes, and products visible in the app
|
|
```
|
|
|
|
Build:
|
|
|
|
```
|
|
PostgreSQL schema
|
|
Excel import script
|
|
Raw Materials page
|
|
Mix Master read-only page
|
|
Product Cost read-only page
|
|
```
|
|
|
|
---
|
|
|
|
## Phase 2 — Calculation engine
|
|
|
|
Outcome:
|
|
|
|
```
|
|
Backend calculates product unit costs without relying on Excel
|
|
```
|
|
|
|
Build:
|
|
|
|
```
|
|
Raw material calculation
|
|
Mix cost calculation
|
|
Product cost calculation
|
|
Margin calculation
|
|
Freight/packaging/process rule engine
|
|
Formula parity tests against spreadsheet
|
|
```
|
|
|
|
---
|
|
|
|
## Phase 3 — Client data entry
|
|
|
|
Outcome:
|
|
|
|
```
|
|
Client can maintain costing inputs safely
|
|
```
|
|
|
|
Build:
|
|
|
|
```
|
|
Editable raw materials
|
|
Editable mix recipes
|
|
Editable product settings
|
|
Validation warnings
|
|
Audit trail
|
|
Role-based permissions
|
|
```
|
|
|
|
---
|
|
|
|
## Phase 4 — Scenarios and price review
|
|
|
|
Outcome:
|
|
|
|
```
|
|
Client and consultancy can simulate price changes and approve outputs
|
|
```
|
|
|
|
Build:
|
|
|
|
```
|
|
Scenario creation
|
|
Scenario comparison
|
|
Price review workflow
|
|
Manual override tracking
|
|
Approval status
|
|
```
|
|
|
|
---
|
|
|
|
## Phase 5 — Power BI integration
|
|
|
|
Outcome:
|
|
|
|
```
|
|
Power BI consumes governed costing outputs
|
|
```
|
|
|
|
Build:
|
|
|
|
```
|
|
Power BI API endpoints
|
|
Database views
|
|
Refresh-friendly output tables
|
|
Data quality issue feed
|
|
Scenario comparison feed
|
|
```
|
|
|
|
---
|
|
|
|
## Phase 6 — Xero integration
|
|
|
|
Outcome:
|
|
|
|
```
|
|
Xero item data and possibly raw cost data are synchronised
|
|
```
|
|
|
|
Build later:
|
|
|
|
```
|
|
Xero item mapping
|
|
Xero item import
|
|
Sync status
|
|
Conflict detection
|
|
Optional write-back, only if approved
|
|
```
|
|
|
|
---
|
|
|
|
# Minimum viable product
|
|
|
|
For the first usable version, build only this:
|
|
|
|
```
|
|
Raw Materials
|
|
Mix Master
|
|
Product Cost
|
|
Scenario Calculator
|
|
Power BI Output
|
|
```
|
|
|
|
Avoid building a full ERP-style system too early.
|
|
|
|
The MVP should answer:
|
|
|
|
```
|
|
What does this product cost now?
|
|
Why does it cost that?
|
|
What changed?
|
|
What price should we charge?
|
|
What would happen if raw material prices changed?
|
|
Can Power BI report on it?
|
|
```
|
|
|
|
---
|
|
|
|
# Key technical recommendations
|
|
|
|
## Frontend
|
|
|
|
Use:
|
|
|
|
```
|
|
SvelteKit
|
|
TypeScript
|
|
TanStack Table or similar grid library
|
|
Form validation with Zod
|
|
Clean scenario comparison pages
|
|
Role-based navigation
|
|
```
|
|
|
|
## Backend
|
|
|
|
Use:
|
|
|
|
```
|
|
FastAPI
|
|
SQLAlchemy
|
|
Alembic migrations
|
|
PostgreSQL
|
|
Pydantic schemas
|
|
pytest for formula parity tests
|
|
```
|
|
|
|
## Power BI
|
|
|
|
Best option:
|
|
|
|
```
|
|
Power BI connects to PostgreSQL reporting views
|
|
```
|
|
|
|
Acceptable option:
|
|
|
|
```
|
|
Power BI connects to FastAPI JSON endpoints
|
|
```
|
|
|
|
The database view option is usually cleaner, faster, and easier to refresh.
|
|
|
|
---
|
|
|
|
# Main risks
|
|
|
|
| Risk | Mitigation |
|
|
| --- | --- |
|
|
| Spreadsheet formulas contain hidden business rules | Complete formula mapping before build |
|
|
| Client expects spreadsheet flexibility | Add scenario mode and manual override tracking |
|
|
| Raw material names do not match across sheets | Use internal IDs, not names, after import |
|
|
| Xero data is inconsistent | Create mapping and review workflow |
|
|
| Power BI consumes unstable draft data | Publish only approved scenario outputs |
|
|
| Manual overrides break trust | Make overrides visible and auditable |
|
|
|
|
---
|
|
|
|
# Recommended build principle
|
|
|
|
Do not build a “spreadsheet on the web”.
|
|
|
|
Build a controlled costing platform with:
|
|
|
|
```markdown
|
|
structured data
|
|
versioned assumptions
|
|
transparent calculations
|
|
scenario comparison
|
|
approval workflow
|
|
Power BI-ready outputs
|
|
```
|
|
|
|
That gives the client safer data entry, gives the consultancy control and visibility, and gives Power BI a clean source instead of fragile workbook logic.
|
|
|
|
---
|
|
|
|
# Frontend layout debugging notes
|
|
|
|
## Full-height layouts inside padded shells
|
|
|
|
When a child layout uses negative margins to cancel a parent container's padding, `height: 100%` is often not enough to visually fill the container.
|
|
|
|
Example pattern:
|
|
|
|
```css
|
|
.parent {
|
|
--content-padding: 1.34rem;
|
|
padding: var(--content-padding);
|
|
}
|
|
|
|
.child {
|
|
margin: calc(var(--content-padding) * -1);
|
|
height: 100%;
|
|
}
|
|
```
|
|
|
|
This can leave a visible gap at the bottom because the child is still only `100%` tall while being visually expanded outward by the negative margins.
|
|
|
|
Preferred fix:
|
|
|
|
```css
|
|
.child {
|
|
margin: calc(var(--content-padding) * -1);
|
|
height: calc(100% + (var(--content-padding) * 2));
|
|
min-height: calc(100% + (var(--content-padding) * 2));
|
|
}
|
|
```
|
|
|
|
Worker reasoning rule:
|
|
|
|
- If a panel "almost" fills the viewport but leaves a strip equal to parent padding, inspect negative margins and the nearest padded scroll container before changing inner child heights.
|
|
- In this app, `AppSecondaryRailLayout.svelte` sits inside `ClientShell.svelte`'s padded `.content` container, so full-height fixes should account for `--content-padding`.
|