The inventory backend — data model & API

The backend is the shop's source of truth: catalogue, stock, orders and payments. It's a small, dependency-light Fastify (v5) + better-sqlite3 service — no ORM, no codegen, no separate database container. Source under /root/apps/shopbot/inventory/src/.

Why SQLite + better-sqlite3 (not Postgres + an ORM)

For a single-box demo, a Postgres container + an ORM with a migration/codegen step is more moving parts than the job needs. better-sqlite3 is synchronous (no await noise around queries), ships a prebuilt binary (no native compile on install), and stores the whole shop in one file on a docker volume. The result is one fewer container, less RAM, and a backend you can read top to bottom.

The data model

Four tables (src/db.js), created on boot with CREATE TABLE IF NOT EXISTS:

• products — name, unique sku, description, price, currency, stock, image_url.
• orders — unique ref (e.g. SB-7QF2K), customer fields, status (awaiting_payment → paid), total, and paystack_ref / paystack_auth_url.
• order_items — a row per line item, with unit_price and qty captured at order time (so later price changes don't rewrite history).
• payments — a record per verified payment, with the raw Paystack event.

WAL journal mode and foreign_keys = ON are set at startup.

The kobo rule — money is integers

Every monetary amount is stored as an integer number of kobo (Naira's minor unit: ₦1 = 100 kobo). A ₦45,000 product is 4500000. This is the standard defence against floating-point money bugs — you never add 0.1 + 0.2 and get 0.30000000000000004 on someone's bill. Formatting to "₦45,000" happens only at the edges (the API's *_formatted fields and the dashboard), via Intl.NumberFormat.

Order creation is one transaction

createOrder runs inside a better-sqlite3 transaction: it validates every line (quantity is a whole number ≥ 1, the product exists, enough stock), sums the total, inserts the order + its items, and returns the hydrated order — or throws a typed error (no_items, bad_qty, product_not_found, insufficient_stock) that the API turns into a 422 with a human message. Stock is checked here but not decremented — that waits for a verified payment (see Payments).

The API surface

All under /api, JSON in/out:

Route	Purpose	Auth
GET /health	liveness + counts + paystack_configured	open
GET /products?search=	catalogue (with price_formatted, in_stock)	open
GET /products/:id	one product (by id or SKU)	open
GET /products/:id/availability?qty=	is N in stock?	open
POST /products/:id/restock	demo affordance (+N stock)	open
POST /orders	create an order	X-API-Key
GET /orders?customer=	list orders (filterable)	open
GET /orders/:ref	one order by reference	open
POST /orders/:ref/pay	start a Paystack payment	X-API-Key
POST /paystack/webhook	payment callback (signature-verified)	signed
GET /stream	the SSE live feed	open
POST /admin/reset	reset to a fresh seeded catalogue (demo)	open

Writes are guarded by an onRequest hook that checks the X-API-Key header against INVENTORY_API_KEY; reads and the signed webhook are open.

A Fastify detail: the raw body

Fastify normally parses JSON and discards the raw bytes. The Paystack webhook must be verified against the exact bytes Paystack sent (HMAC over the raw body), so a custom content-type parser keeps req.rawBody around while still parsing JSON for everything else. Without this, the signature would never match.

Seeding

On first boot seedIfEmpty() inserts 8 sample gadgets ("Naija Gadgets & More") with kobo prices and an emoji thumbnail — enough catalogue to make a convincing demo. POST /api/admin/reset (and the dashboard's Reset demo button) clears orders and re-seeds for a clean slate.