Bespoke Stock Manager
A high-integrity logistics tool designed to eliminate human error. We architected a database-centric system with an immutable ledger that automates the complex relationship between physical components, digital sales bundles, and end-to-end financial tracking.
PostgreSQL / Supabase
The Entropy Problem
Before intervention, the client operated a high-volume logistics business relying solely on manual synchronization between warehouse spreadsheets and their e-commerce frontend.
This fractured state resulted in:
- Phantom Inventory: Sales made on items physically out of stock due to sync delays.
- Replenishment Guesswork: Ordering static amounts rather than calculating dynamic deficits.
- Data Silos: Financial liabilities regarding suppliers were calculated manually, leading to drift.
System Fragility
Previous reliance on "avoiding human error" was unsustainable at scale. A single missed spreadsheet update caused cascading failures in fulfillment.
Architecture
Component Normalization
We decoupled the concept of "what we sell" from "what we have". By treating inventory items as atomic units and sales products as molecular recipes, we achieved true data integrity.
Logic Engine
Available for immediate fulfillment
The Source
Physical entities (components) are tracked via an immutable ledger. Every damage report, arrival, or manual correction is a signed transaction.
The Recipe
Logic layers that define relationships. A single digital product may require multiple physical components. The system calculates availability based on the scarcest component.
The Sync
When component stock changes, a background worker recalculates all dependent recipes and pushes the new "Buildable Quantity" to the sales channel API.
Engineering Highlights
Immutable Audit Ledger
Stock levels are never simply "overwritten". Every change is recorded via a transactional RPC that creates an entry in an immutable log table, complete with a reason code, user ID, and a before/after snapshot. This creates a forensic audit trail for every item.
Algorithmic Replenishment
A core database function analyzes target stock levels against real-time component deficits to automatically generate draft procurement requests. The system manages the entire multi-stage procurement lifecycle, updating stock and financial ledgers only upon final receipt validation.
Automated Financial Ledgers
The system links physical operations to financial data. Upon receiving goods, it automatically calculates and accrues the corresponding financial liabilities into a dedicated ledger. This provides a real-time, auditable view of outstanding debts, which can be settled through a secure, transactional reconciliation process.
Database-Level Security (RLS)
Leveraging PostgreSQL's native Row Level Security, access control is enforced at the data layer itself, not just in the API. A custom permissions system allows granular control, ensuring warehouse staff can update stock counts but are physically prevented by the database from viewing sensitive financial data like cost prices or liability ledgers.