StockPilot
The backend that makes “in stock” actually mean in stock.
A multi-tenant inventory engine where “in stock” is a promise — real-time reservations, transaction-safe counts, and a checkout flow that heals itself across every store.
A unit of stock is reserved at checkout, then either sold — or, if the shopper vanishes, swept back to the shelf by the expiry job.
01The problem nobody sees
Picture a customer who checks your website, sees one unit left, and drives across town to buy it — only to find the shelf empty. Or two shoppers who tap “Buy” on that last unit at the very same second, and both get charged. To the customer it feels like a broken promise. To the business it's a refund, an apology, and a lost sale.
StockPilot is the backend that sits underneath all of that. It is the single source of truth for how much of everything exists, in which store, right now — and it's built so that the word “available” can be trusted, even when thousands of requests arrive at once.
02One platform, many companies
StockPilot is multi-tenant from the ground up. A single deployment serves many independent businesses — each one an Organization with its own stores, products, staff logins, and API keys. Every query is silently scoped to the organization making the request, so one company can never see or touch another's stock. That isolation isn't a setting you switch on; it's woven into every database call.
Inside an organization the model mirrors the real world: stores have a location, products have a SKU and a barcode, and inventory is the bridge between them — this many of that product, in that store.
03Holding stock, like holding a seat
The heart of StockPilot is its reservation system, and it works like booking a seat at the cinema. The moment a customer begins checkout, the stock is reserved — moved out of the available pool so nobody else can grab it, but not yet sold. If they finish paying, the reservation is confirmed and the stock leaves for good. If they change their mind, it's released back to the shelf.
And if they simply vanish? Every reservation carries a ten-minute expiry. A background job sweeps the database every minute and quietly returns any forgotten holds to the shelf. Stock can never get stuck in limbo — the system heals itself.
04Never sell the same item twice
The dangerous moment in any inventory system is concurrency — two requests touching the same stock at the same instant. StockPilot wraps every change to a number inside a database transaction: read the current count, check it, write the new count, and record why — all as one indivisible step. If anything fails midway, the whole thing rewinds.
It's the difference between a system that is usually right and one that is always right.
05A memory of everything
Stock never just changes — it changes for a reason. Every adjustment, reservation, sale, and auto-expiry writes a line to an append-only ledger: what changed, by how much, and why. When a number looks wrong at the end of the month, there's no guesswork — you can replay the entire history of a single product in a single store and see exactly what happened.
06Open to the outside world
Internal staff sign in with an email and password (hashed, never stored in the clear) and carry a signed token for the rest of their session. But StockPilot also speaks to partners — delivery apps, marketplaces, price-comparison sites — through a separate, key-based API. Each partner gets a unique key and can only ever read what belongs to the organization that issued it.
Partners can ask the question that matters most: where, near me, can I actually buy this right now? StockPilot answers with a geographic search that ranks stores by real distance and shows only those with stock on hand. And when stock moves, registered webhooks fire instantly — so connected systems hear about a sale the moment it happens, instead of polling and waiting.
How a reservation lives and dies
01 · RESERVE
Customer starts checkout
One unit moves from Available to Reserved and a 10-minute timer starts. Nobody else can buy it.
02 · CONFIRM
Payment succeeds
The reserved unit is sold and leaves inventory for good. The sale is written to the ledger.
03 · RELEASE
Cart abandoned
The hold is cancelled and the unit returns to the Available pool, ready for the next customer.
04 · EXPIRE
Customer disappears
If the timer runs out, a background job sweeps the reservation away — the shelf restocks itself.
The API surface
Around forty endpoints, split across three audiences — internal staff, external partners, and outbound webhooks — each with its own way of proving who it is.
Staff sign in once; every later request carries a signed token that the server re-verifies against the database before trusting it.
- POST
/api/auth/signupCreate a staff user inside an organization - POST
/api/auth/loginExchange email + password for a 7-day JWT - GET
/api/auth/meResolve a token back to the current user
The core engine. Adjust stock, run the reserve → confirm → release lifecycle, and read analytics — every write is organization-scoped and wrapped in a transaction.
- POST
/inventory/adjustAdd or remove stock, atomically and logged - POST
/inventory/reserveHold stock for checkout with a 10-minute expiry - POST
/inventory/confirmTurn a hold into a completed sale - POST
/inventory/releaseReturn a hold to the shelf - POST
/inventory/bulk-adjustMany adjustments in a single transaction - POST
/inventory/uploadStreamed CSV import, all-or-nothing - GET
/inventory/statsOrg-wide totals and low-stock count - GET
/inventory/store/:id/analyticsPer-store totals and recent changes
A separate, key-authenticated surface for third parties. Each key is bound to one organization, so a partner can only ever see that organization's stock.
- GET
/external/products/availabilityWhich stores have this product right now? - GET
/external/stores/nearbyNearest in-stock stores — geo search - GET
/external/products/searchSearch a partner's catalogue
Instead of partners polling us, we push to them. Register a URL and StockPilot fires a JSON event the instant stock moves — with a 3-second timeout so a slow listener can never block a sale.
- POST
/api/webhooksRegister a listener URL - GET
/api/webhooksList an organization's webhooks - PUSH
inventory.reserved · confirmed · released …Events pushed to every listener
The hard parts
Most endpoints are simple reads and writes. A few are not — these are the problems that took real engineering to get right.
“Where can I buy this near me?”
This is the hardest endpoint in the system. The naive version would load every store, measure distances in JavaScript, then sort — slow, and impossible to scale past a few hundred stores.
Instead, StockPilot pushes the maths down into PostgreSQL as a raw SQL query. It first draws a cheap bounding box (±0.1° of latitude and longitude) so the database can throw away far-away stores instantly. Only the survivors get the expensive Haversine calculation — the great-circle formula that turns two latitude/longitude pairs into a real distance in kilometres. The results are filtered to stores with stock on hand and sorted nearest-first.
The payoff: a map-style “in stock near you” answer that stays fast even with thousands of stores.
Two shoppers, one last unit
When stock changes, StockPilot never simply writes a new number. It reads the current count, checks the rules (you can't drop below zero; you can't confirm more than you reserved), writes the result, and records the reason — all inside a single database transaction.
A transaction is all-or-nothing: if any step fails, the database rewinds as though nothing happened. So when two checkout requests race for the last unit, one wins cleanly and the other is rejected — never a double-sell, never a negative balance.
The reservation that cleans up after itself
A reserved unit that's never confirmed would be lost forever — stock frozen in limbo because a shopper closed the tab. So every hold is stamped with a ten-minute expiry.
A background job (node-cron) wakes once a minute, finds every hold whose timer has passed, and returns that stock to the available pool inside its own transaction — writing an EXPIRE_RELEASE entry so the history stays honest. The shelf restocks itself, with zero human intervention.
Importing a thousand rows, safely
Bulk imports are where systems quietly corrupt data. StockPilot streams the uploaded CSV row by row — so even a huge file never has to sit in memory all at once — then applies every row inside one transaction.
If row 998 is malformed, rows 1–997 are not left half-committed. The entire import rolls back and the data sits exactly as it was. An import either fully succeeds or changes nothing at all.
What it does
Multi-tenant by design
Organizations, stores, and API keys keep every business's data fully isolated — enforced at the query layer, not bolted on.
Reserve → Confirm → Release
A full checkout lifecycle with automatic ten-minute expiry, so abandoned carts never strand inventory.
Transaction-safe counts
Every stock mutation runs inside a database transaction — no double-sells, no negative stock, ever.
Append-only audit log
Each change is recorded with an amount and a reason, giving a complete, replayable history per item.
Geo store-finder
A Haversine distance query in raw SQL finds the nearest stores that actually have the product in stock.
Webhooks & partner API
Key-authenticated external endpoints plus outbound webhooks let third parties integrate in real time.
Bulk & CSV operations
Update thousands of items at once through batch adjustments or a streamed CSV upload.
Live analytics
Per-store and organization-wide stats, plus low-stock alerts surfaced on demand.
Under the hood
Runtime
Database
Auth & Security
Infrastructure