case study · Stock system · Full-stack
Depósitos CPS
The whole company’s stock in one place: what’s in stock, where it is and which site it went to.
Stock is never corrected “by hand”: every change is born from a numbered voucher, with user and date. The number you see is the sum of its history. And negative stock is impossible by design, even under concurrent operations.
Internal system for Grupo CPS to manage materials and tools across warehouses and construction sites: purchase requests and orders, transfers, consumption vouchers with reconciliation, repairs and adjustments. Every operation is recorded in an immutable stock ledger with weighted-average costing, and the team sees the stock valuation, alerts and reports live.
01 · business problem
Grupo CPS moves materials and tools across several warehouses and construction sites at once. That movement was tracked with spreadsheets, messages and memory: knowing how much of an item there was, and where, meant calling, counting and trusting.
The consequences were always the same: shortages discovered with the site already running, material “on the road” that showed up nowhere, rushed purchases with no record, and no reliable number for how much capital sat in stock or how much each site had consumed.
And there was a subtler problem: tools. An angle grinder is not “3 angle grinders”: it is this grinder, with its serial number, which is at that site, came back broken or got lost. And someone has to answer for it.
02 · constraints
What I had to work around.
Several warehouses and sites operating at once
Concurrent movements from different places. The balance cannot depend on two people not posting at the same time.
Non-technical users, with different roles
Logistics operates; site managers and management query and request. Each one had to see and do only their part, without that depending on goodwill.
Own server, no third-party cloud
The company’s data stays on its VPS. That fixed self-hosted Supabase, Docker and Coolify, and left me in charge of operations too.
Numbers that must hold up
Stock valuation and spending per site are used to decide purchases and close sites. An estimate is useless: every number must be explained by vouchers.
the key decision
Stock is never edited: it is recorded. Everything else derives from that.
The decision that organizes the whole system: nobody writes “40 bags left”. You record the movement that explains it (a received purchase, a transfer, a consumption voucher, an adjustment) as an entry in an immutable ledger: movements are insert-only, and correcting means posting the reversing entry, never deleting.
The balance per warehouse is maintained by the database itself, with a constraint that makes negative stock impossible even under concurrent operations. And what is on the road or at the repair shop is stock too: the virtual warehouses “In transit” and “Repair” guarantee that the sum of all balances always equals the company’s real stock.
On that foundation, costing uses a weighted average price frozen into each movement: transfers do not alter the average, and returns re-enter at the cost frozen in the original voucher. That is why the stock valuation on the dashboard is a defensible number, not an estimate.
03 · technical decisions and why
Why it is built this way.
Append-only ledger
Movements are insert-only; correcting means posting a reversing entry. That single decision made auditing, voiding, period ledgers and valuation free: they are all queries over the same table.
The balance is kept by the database, not the app
A trigger updates the balance per warehouse on every entry and a constraint stops it from going below zero. Two simultaneous operations on the same item cannot leave negative stock, with no locks in the application.
Weighted-average cost frozen into each movement
The unit cost is fixed at posting time: transfers do not alter the average and returns re-enter at the original voucher’s cost. So the valuation never changes retroactively.
Transactional RPCs as the only write path
Every operation is a SECURITY DEFINER function that validates role and business rules inside a transaction. The movement tables have no write policies: even talking straight to the API, nobody can skip the rules.
Virtual “In transit” and “Repair” warehouses
What travels or sits at the repair shop is stock too, in its own warehouse. So the sum of all balances always equals the real stock and nothing ends up “nowhere”.
Tools as units, not quantities
Each tool has a serial number and its own life cycle: available → on site → under repair → broken or lost. Broken or lost ones are charged to the site that had them.
04 · what was discarded
What I ruled out along the way.
Editing quantities directly
That is what the spreadsheets did. Without the history behind the number, there is no way to explain a shortage, void without deleting, or value the stock.
Business rules in the application layer
Any client talking to the API could skip them, and every rule would have to be repeated on every screen. In PostgreSQL there is one door and it has no shortcut.
Cloud-hosted Supabase
More convenient, but the company’s data would leave its server. Keeping control and not paying for a plan mattered more; in exchange, I run the operations myself.
A materials-only system
Without serial-numbered tools, half the problem (who has what, at which site, and who answers for it) would still live in messages.
what was built
The whole material circuit, from request to construction site.
- 01
Purchase requests and orders
Any user, site managers included, requests what they need with destination and deadline. Logistics builds the orders, which can mix suppliers per line and feed a price history per item and supplier.
- 02
Partial receptions
Purchases and transfers are received in batches. Closing with missing items is an explicit decision, with a reason: only then is what never arrived written off.
- 03
“In transit” transfers
Material moving between warehouses passes through a real intermediate state: while it travels it sits in neither warehouse, it is in transit, with a truck and people assigned to the trip.
- 04
Consumption vouchers with reconciliation
What goes out to a site gets reconciled: what was used is charged to the site’s spending, and what comes back re-enters stock at the voucher’s frozen cost.
- 05
Tools by serial number
Each physical unit has its own life cycle: available → on site → under repair → broken or lost. They are reconciled per unit, and broken or lost ones are charged to the site that had them.
- 06
Per-user dashboard and alerts
Each user builds their own home screen with the widgets they need, and notifications fire on their own: overdue or expiring requests, delayed transfers and stock below minimum.
The system also covers the boring-but-critical: a ledger view per warehouse or site for any period, one item queried across all warehouses at once, purchases in pesos or dollars with their currency recorded, and purchase orders as PDFs with signature lines, because the physical circuit with the supplier is part of the system too.
architecture
Business logic lives in the database, not in the interface.
Next.js + shadcn/ui
Operation and query screens: customizable dashboard, movements, ledger views and catalogs.
Self-hosted Supabase
PostgreSQL + Auth + API running on the company's own server, not on a third-party cloud.
Transactional RPCs
Every operation is a function that validates role and business rules inside the database, in a transaction.
Immutable ledger
Movements are insert-only; correcting means posting a reversing entry. A trigger maintains the balances per warehouse.
RLS on every table
The movement tables have no write policies at all: the only door in is the RPCs.
VPS + Coolify
Automatic deploy on every push, migrations applied by CI, and daily backups of both server and database.
Hiding buttons in the interface is ergonomics; the real barrier is in PostgreSQL. Even talking straight to the API, nobody can skip the rules: every write goes through a function that validates everything on the database side.
security & traceability
A stock system is a trust system: every number has to be able to defend itself.
Roles with the bare minimum
The admin role operates and administers; the viewer role (site managers, management) reads everything and can only create its own purchase requests and customize its dashboard. And nobody can promote their own role.
Users closed by default
Public sign-up is disabled and profiles are born inactive, with no access to data, until an admin activates them from the app.
Who did what, always
Issuing, receiving, canceling, reconciling: every action is recorded with user and date, visible on every screen for every user.
Voiding is not deleting
Voiding posts the reversing entry and the history stays complete. Nothing gets corrected quietly.
05 · measurable result
In numbers.
of balances traceable: every number is the sum of vouchers with user and date.
negative stock possible, enforced by a database constraint, even under concurrent operations.
used by logistics, site managers and management at Grupo CPS, on the company’s own server.
quality & engineering
The core is tested where it lives: in SQL.
- SQL core tests
- Integration suite that walks the full circuit, inside a transaction that rolls back
- What it covers
- Numbering, weighted-average costing, partial receptions, reconciliations, serial-numbered tools, RLS and roles
- Negative stock
- Impossible by design, even under concurrency
- Versioned schema
- Migrations applied by CI on a clean database before every deploy
The suite runs inside a transaction that rolls back at the end: it tests the real core against the real database without dirtying it. If the rule lives in PostgreSQL, so does the test.
screenshots
The system from the inside.
takeaways
- If people do not believe the number, they go back to the spreadsheet. That is why everything is built so every balance can be explained by its vouchers.
- Modeling movements instead of quantities simplified everything else: ledger views, auditing, voiding and valuation all fell out of the same design.
- Putting business rules in the database, and testing them there, means security does not depend on which buttons the interface shows.
- The physical circuit is software too: the purchase order PDF with signature lines matters as much as the table that generates it.
Want to talk about this project?
The code is internal to the company, but I can explain how it is designed. Write to me if this interests you or you want to talk about a role.