Portfolio / Traceability and costing ERP
From green coffee to the order, at real cost.
Broo Café buys green coffee from a farm, sends it to a partner roaster, packs whole beans, drip bags and cold brew, and sells mostly through WooCommerce, plus WhatsApp. We designed and built an ERP that follows every lot from the farm to the shipped order and turns that path into real cost, margin per order and customer relationships.
01The challenge
A full coffee supply chain in a small brand.
Broo doesn't buy coffee ready to sell. It buys green coffee from a farm in Antioquia, has it roasted elsewhere, loses weight in the roast, buys naturally processed coffee already roasted, and packs several bag sizes, 15-sachet drip kits and 700 ml cold brew. Every step changes the cost of the coffee that ends up in a bag, and that cost lived across invoices, spreadsheets and the team's memory.
The business questions were specific: what each lot cost to produce, how much inventory is left, what each order leaves after coffee, packaging, the payment gateway fee and the shipping subsidy, and what customer acquisition budget is sustainable. All without mistaking revenue for profit, and with WooCommerce as the main channel.
What had to be coordinated
- 01Green coffee with farm, variety, process and origin
- 02Outsourced roasting and its weight loss
- 03Recipes and production orders
- 04Packaging per unit sold
- 05WooCommerce, WhatsApp and event orders
- 06Dispatch, tracking and labels
- 07Customers and consent (Law 1581)
- 08Order emails and campaigns
- 09Expenses, ad spend and profitability
Our role
Domain and product modelling, architecture, visual system and interface, frontend and backend development, integrations (WooCommerce, Resend, Gemini, private file storage and n8n), automated tests, in-app user guides and deployment.
02The rule
The decision that shaped the whole system.
Inventory is a sum. Cost gets frozen.
There is no stock counter anyone can edit. Each lot's balance is the sum of movements that are never modified: a correction is an audited reversal, not an overwritten number. When an order ships, the cost of every lot that left —coffee and packaging included— is frozen on that line and stays put even if green coffee goes up tomorrow.
The same idea assigns an owner to each piece of data: WooCommerce owns the web order, and the ERP is the only owner of publishable stock. Stock is never imported from the store, so there are no sync loops.
Traceable to the farmReal cost, not estimatedOne source of stockAI proposes, people approve
03The life of a lot
From the farm to dispatch.
Every stage writes movements to the same ledger and links the new lot to the lots it consumed. That's why a bag that was sold can be traced back to the green coffee it came from, with the cost each input contributed.
Farm
Green coffee with originPartner roaster
Outsourced roastingProduction
Approved recipe · real costLedger
Immutable movementsWooCommerce
Web ordersResend
Order emails
- 01
Buying from the farm
Receiving creates a green coffee lot with farm, variety, process and origin, and a landed cost: price times exchange rate plus prorated extras.
- 02
Outsourced roasting
Green coffee leaves for the roaster and comes back roasted. The system works out the weight loss and the cost per usable kilo: green coffee, transport, roasting and extras, divided by what can actually be packed.
- 03
Production
An order on an approved recipe consumes lots first in, first out —first expired, first out for perishables—, records waste and costed labour time, and closes at real cost with a snapshot of planned against actual.
- 04
Order
It comes in from WooCommerce, from WhatsApp, as a manual sale or from an event or fair. Once payment is confirmed, stock is reserved; if a product is made to order, like cold brew, its production runs in a single step.
- 05
Dispatch
Stock is deducted exactly once and the cost of each lot, with its packaging, is frozen on the line. Carrier, tracking number and real shipping cost are recorded, and the 4×6 label prints.
- 06
Emails and profitability
The customer receives the order journey emails, and the order enters finance with its contribution: net revenue minus cost, gateway fee and shipping subsidy.
The figures in the screenshots come from the repository's test scenario: 10 kg of green coffee, 8.5 kg usable, 15% roast loss. They are invented data, not Broo's costs.
04Operations
Where the cost is built.
Two steps: send green coffee, receive roasted coffee. The table shows what was sent, what was usable, the loss and the cost per roasted kilo for each order. In the test scenario, 10 kg go out and 8.5 kg come back: a 15% loss.
A six-bottle cold brew batch: measured yield, labour in minutes and pesos, materials consumed, and the real cost per unit next to the planned one. Every consumption is an immutable movement tied to its lot.
The record of a lot of 250 g bags keeps its frozen cost —materials, labour, external processes— and its genealogy: the roasted coffee, bag and label it consumed and, one level up, the Antioquia green coffee that roasted coffee came from.
Every change creates a new version that must be approved before production. The system ships with no invented recipes: ratio, extraction and yield are entered by the team.
The movement ledger: sales, production output and consumption, with lot, quantity and cost. Nothing is edited; what gets corrected is reversed, with an author.
05The data model
Six decisions you don't see.
89 tables across 10 versioned migrations, split by domain: identity, purchasing, inventory, production, sales, CRM, marketing, finance, AI and files. This is what makes the numbers add up.
Green coffee is just a lot.
A green coffee lot has no table of its own: it's a raw-material lot linked to farm, variety, process and origin. So there is a single movement ledger for everything, from green bean to bottle.
Balance = sum.
Every movement carries an idempotency key and, if it corrects another one, a link to the reversal. Inventory never goes negative without an explicit permission.
What came from what.
Each produced lot stores the lots it consumed, with quantity and contributed cost. Tracing a bag back to the farm means walking that relationship backwards.
A snapshot, not a live calculation.
Production stores planned against actual cost; dispatch allocates lots to each line with their cost and packaging. If green coffee goes up tomorrow, what sold yesterday doesn't change.
Exact pesos and grams.
Money, quantities and unit costs use fixed decimals and decimal arithmetic, never floats. Base units are grams, millilitres and units, and mass is never converted to volume.
Append only.
Every change in a customer's per-channel consent is a new row; the latest one is current. The history of what a customer agreed to, and when, is never lost.
06Sales and dispatch
What each order actually leaves.
The order shows which lot each item came from, its frozen cost and the full breakdown: net revenue minus cost, gateway fee and shipping subsidy, down to contribution and its margin. Statuses move from paid to shipped, with cancellations and returns that never deduct twice.
The thermal label follows the brand's template: logo, order number, items, sender and recipient. It prints one at a time or in batches from “labels to ship”, and the panel flags any missing field before printing.
Four service emails: order confirmed, shipped with tracking, “has it arrived?” with a short brewing guide for what they bought, and “how's your coffee?” to ask for feedback. Each step goes out once per order; the timing is a setting.
07Channels and relationships
Every integration has one job.
Orders without loops.
Incremental import of products, customers and orders, signed webhooks that are verified and deduplicated, and a per-order lock so a resent event never lands twice. Orders placed before go-live come in as history, without touching stock.
Direct sales, same costing.
Orders that don't go through the website are created in the panel with their channel, recipient and dispatch, and they deduct and cost exactly like a store order. WhatsApp opens with one click; nothing is sent automatically.
Consent per channel.
Customer profiles, dynamic segments recalculated daily, CSV exports limited to people who consented to that channel —protected against formula injection— and anonymisation for data-subject requests under Colombian law, keeping the orders.
Email within limits.
A batched queue with daily and monthly caps, priority for order emails, one-click unsubscribe and automatic suppression after bounces or complaints, driven by signed events.
Invoices and evidence.
PDF or image attachments, up to 4 MB, on purchases, production, lots and orders. They download only with read permission on the record they belong to.
Look, don't touch.
A read-only view of workflows and their runs inside Marketing, to check the automation without leaving the panel.
08Finance
Revenue is not profit.
Net sales, cost by lot, contribution and estimated operating profit, calculated from orders, lots and expenses. The waterfall on the right walks from gross revenue to profit line by line; historical orders are left out.
Discounts, raw material price increases, roast loss, shipping and repeat purchases against break-even CPA, target CPA, break-even ROAS and LTV. It prefills observed averages, leaves empty whatever has no data, and is labelled as an estimate, not a result.
09Artificial intelligence
An analyst that reads, not one that writes.
The assistant uses Gemini with 7 fixed tools: each one is a parameterised, read-only SQL query that we wrote. The model chooses which one to call and with which dates; it never writes SQL and never changes data. If a model errors out or takes too long, the server falls back to the next one in a chain.
It also proposes drafts —an email, a WhatsApp message, a creative brief, a segment suggestion, a reply to a customer and a product description—, 6 kinds in all. None of them goes out on its own: each waits for someone with approval permission and, once approved, becomes an inactive template that a person decides to use.
The tools
- 01Period KPIs
- 02Profitability by SKU
- 03Stock status
- 04Campaign performance
- 05Segment summary
- 06Reorder and coverage suggestions
- 07Recent production costs
No screenshot, on purpose
The screenshots in this case were taken with AI switched off. We don't show an assistant answer because we're not going to fake one.
10Roles and permissions
Five roles, checked on the server.
28 granular permissions —view, edit, approve recipes, authorise negative inventory, export, anonymise, approve AI drafts— spread across 5 roles. Every page and every server action requires its own: hiding a button is never the only barrier.
The whole system.
Users, settings, integrations and the audit log. The only role that can anonymise a customer.
From green coffee to dispatch.
Purchasing, roasting, inventory, production and order dispatch.
Customers and campaigns.
CRM, segments, templates, campaigns and consent-based exports.
Read everything, own the costs.
Read access to every module except the audit log, plus expenses, scenarios and editing the catalog and its prices.
Look without changing.
View-only access for anyone who needs to see how the operation is doing.
11Security and privacy
Every action has an author.
Authentication is custom and has no public sign-up: database sessions with an opaque token stored as a hash, an httpOnly cookie, lockout after failed attempts, one-time access links that expire after 48 hours, and a forced password change whenever an administrator sets a temporary one.
Raw database errors never reach the screen: they're mapped to a message and a code. Webhooks are verified with constant-time comparison, relevant actions land in an audit log, and the AI tools return aggregated data, not customer records.
The model
- 01Permissions on every page and action
- 02Session token stored as a hash
- 03Lockout by email and IP
- 04One-time access links
- 05Signed, deduplicated webhooks
- 06Strict security headers
- 07Formula-safe exports
- 08Audit log of actions
12Visual system
The store's brand, inside the panel.
Primary action · Active state · Focus
- HEX
- #FF6A00
- RGB
- 255 · 106 · 0
Orange text on light backgrounds
- HEX
- #C24F00
- RGB
- 194 · 79 · 0
Sidebar · Headings · Charts
- HEX
- #062149
- RGB
- 6 · 33 · 73
“ERP” tag on navy
- HEX
- #C8915F
- RGB
- 200 · 145 · 95
Application background
- HEX
- #FAF9F8
- RGB
- 250 · 249 · 248
Secondary surfaces · Hover
- HEX
- #F5F3F1
- RGB
- 245 · 243 · 241
13The way in
An internal panel with a brand voice.
An animated navy background built in CSS and SVG —drifting light, steam and grain—, with no JavaScript. All of the panel's motion switches off when the system asks for reduced motion.
14The principles
The language of the operation, not of software.
Mattone to headline, Kanit to read.
The panel uses broocafe.co's display face for headings and figures, and Kanit for text, tables and forms, with small-caps labels and tabular numbers. On this page the headlines are set in Unbounded: Mattone's licence isn't confirmed for use outside the panel.
No data? It says so.
Costs, recipes, taxes and shelf life are settings the team enters; the system invents none of them. A figure with no baseline reads “sin base”, and an empty simulator field stays empty.
A guide for this screen.
Every screen has its own guide, one button or the “?” key away, and a help centre collects the walkthroughs by module, from first setup to the weekly review.
15Architecture
Business rules you can test.
Domain services take the database as a parameter and run without Next.js. That way tests execute the same SQL as production against an embedded Postgres, with no Docker.
Next.js 16
App Router · Server ActionsServices
Rules · permissions · auditNeon Postgres
Drizzle · transactionsJob queue
Outbox · retriesGemini
Read-onlyVercel
Deployment · storage
- 01
The interface
Next.js 16 with the App Router, React 19, TypeScript and Tailwind CSS 4. Mutations go through Server Actions that check the permission first.
- 02
The domain
One service per module validates, calculates with exact decimals and writes in a transaction, locking the rows that could be overwritten.
- 03
The database
Drizzle on Neon, with connections that support interactive transactions. Locally and in tests, PGlite: the same Postgres, embedded.
- 04
Async work
A Postgres-backed queue with exponential backoff, recovery of stuck jobs and a final state for whatever couldn't be processed. Integrations and email go through it.
- 05
Quality
More than 160 automated tests against real Postgres and 4 end-to-end flows on a production build. On every change, CI checks migrations, lint, types, tests and the build, with no secrets.
Resend sends the email and Gemini answers the assistant; both are called from the server only. Keys live in the deployment environment, never in the browser.
16Evidence of depth
Technical scope, not a sales result.
Figures counted on the repository. They describe the size of what was built, not sales, hours saved or adoption, which will be measured once the system has been running for a while.
- 28
- permissions across 5 roles, checked on the server
- 89
- tables across 10 versioned migrations
- 160+
- automated tests against real Postgres
- 4
- end-to-end flows on a production build
- 7
- read-only AI tools
- ~8
- days from first commit to the complete system
17Where it stands
Built, tested and in initial operation.
The first commit is from 12 October 2026; by the 19th the system had every module, hardening for day-to-day operation, end-to-end tests and user guides. Purchasing, roasting, production, inventory, sales, labels, CRM, the customer journey, finance and the assistant are built and covered by tests.
What is still off is off on purpose. Pushing stock to WooCommerce waits until physical inventory is reconciled; the n8n trigger every ten minutes is inactive and, in the meantime, a daily fallback runs. Relationship automations —welcome, repeat purchase and win-back— ship inactive until the owner turns them on, and WhatsApp marketing is hidden.
Real costs, the cold brew recipe and the labour rate are still being entered, so profitability figures will fill in with operation. There is no point of sale and no electronic invoicing: they weren't in scope.
Planned or switched off
- 01Pushing stock to WooCommerce
- 02n8n trigger every 10 minutes
- 03Relationship automations
- 04WhatsApp marketing
- 05Loading real costs and recipes
- 06VAT and e-invoicing decision
18The result
What changes when cost becomes visible.
Knowing what each order leaves.
Broo now has a foundation for answering with data what used to be estimated: which farm the coffee in a bag came from, what it cost to roast and pack, and what was left of the order after coffee, packaging, fees and shipping.
Everything else is decided on top of that: how much to spend on ads, what to restock and which customers to write to, always with a person approving what goes out.
TraceableCostedAudited
→Related project
The store that feeds this system.
beleafdesign
Do you know what each order leaves you, or only what you bill?
We design custom systems that work with what you already use —your store, your email, your suppliers— and turn the operation into numbers you can check. Let's look at how your business runs today.
Let's talk about your project