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.
case study · Internal tool · 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.
RoleSole full-stack developer: data model, SQL core, backend, frontend and deploymentClientGrupo CPS, construction and aluminum joineryYear2026
Let's talk about the projectThe code is private since it is an internal company tool.
Next.jsTypeScriptTailwind 4shadcn/uiSupabasePostgreSQLRLSCoolify
100% traceable
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.
The 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.
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.
the solution
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.
UI
Next.js + shadcn/ui
Operation and query screens: customizable dashboard, movements, ledger views and catalogs.
API
Self-hosted Supabase
PostgreSQL + Auth + API running on the company's own server, not on a third-party cloud.
RPC
Transactional RPCs
Every operation is a function that validates role and business rules inside the database, in a transaction.
LED
Immutable ledger
Movements are insert-only; correcting means posting a reversing entry. A trigger maintains the balances per warehouse.
SEC
RLS on every table
The movement tables have no write policies at all: the only door in is the RPCs.
OPS
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.
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. There is no such thing as a silent correction.
quality & engineering
The core is tested where it lives: in SQL.
- SQL core test suite
- 1,500+ lines · ~160 assertions
- 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
- 19 migrations, applied automatically by CI
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.
why this project represents me
- A stock system is a trust system: if people do not believe the number, they go back to the spreadsheet. Total traceability is what sustains that trust.
- 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.
Does your operation also live in spreadsheets and messages?
This is the kind of system I enjoy building most. The code is internal to the company, but we can talk about the problem.