OIKORA
Business, in sync
- Role
- Solo build
- Type
- Personal · Live
- Systems
- Next.js · Prisma · PostgreSQL
- Scale
- Multi-tenant, org-isolated, 12 phases shipped
Objective
Products, Purchases, and Sales are three connected views onto one thing: a movement ledger that's the single source of truth for every quantity in the system. That's what the name is about — one system, three sides of it, always in sync.
Every business table carries an organizationId, and every company ("Organization") gets fully isolated Products, Purchases, and Sales on the same deployment — a user can belong to more than one organization and switch between them from the nav without signing out, with a per-membership role (OWNER / ADMIN / STOREKEEPER / STAFF) so the same person can own one company and be plain staff at another.
Around that core it has grown into a full operations suite: stock counts with discrepancy adjustments, product bundles, partial purchase-order receiving, sales and purchase returns, draft-order stock reservation, an inventory valuation report, supplier on-time-delivery tracking, reorder and inter-location transfer suggestions, global search, audit-log and failed-login trails, optional TOTP two-factor, read-only API keys, in-app notifications with per-event preferences, and daily or weekly digest emails.
Why this is hard: multi-tenancy isn't a feature you bolt on — it's a property every single query has to uphold, forever. One route that forgets a where clause is a data breach between two paying customers, not a bug ticket. The isolation guarantee has to be enforced structurally, not by developer discipline alone.
Decisions
Multi-tenancy enforced at the query layer, not by convention
A Prisma Client Extension (scopedDb()) auto-injects the current organizationId into every read/update/delete issued through it, so one organization's data can't leak into another's just because a route forgot a where clause. A scripted audit (npm run audit:org-scoping, also run in CI) checks that every API route touching a tenant-scoped model uses the scoped client rather than the raw one — and a dedicated integration suite fires many concurrent cross-org reads/writes at it to prove the isolation holds under real load, not just in a single request.
Race-safe stock mutations
Receive/issue/transfer flows are atomic and race-safe: concurrent stock-out requests can't oversell, enforced via a conditional updateMany (UPDATE ... WHERE quantity >= ? RETURNING) rather than a read-then-write check that two simultaneous requests could both pass. The same claim/guard idiom is reused for the background-job queue's claim query.
A background job queue with no Redis
src/lib/job-worker.ts implements a minimal, Postgres-backed job queue (a Job table with PENDING/RUNNING/DONE/FAILED status) instead of standing up Redis or a queue service. CSV import/export enqueue a Job and return immediately; the UI polls GET /api/jobs/[id] instead of blocking on a request a large file could time out. A worker endpoint claims pending jobs with the same race-safe conditional updateMany used for stock, so concurrent invocations never double-process a job, and runs them in batches of 20.
Order and stock changes lock first, then apply relative deltas
A production-scale pass found the read-then-write shape was still racy in places. Fulfilling, receiving, returning, status changes and stock counts now lock the order row and apply relative changes (quantity = quantity + n) instead of writing back a value read earlier, so two requests on the same order can't both succeed and drift stock. A concurrency-safety integration suite races them against each other to prove it.
Membership and role re-checked on every request
A JWT alone would keep working after someone was removed from an organization or demoted. Every request now re-checks the membership and its role, and a password reset ends existing sessions, so access changes take effect immediately instead of whenever the token expires. A session-revocation test covers it.
Pre-aggregated analytics
A daily rollup job pre-aggregates each day's stock and sales activity into summary tables, so dashboard analytics reads a bounded rollup for historical data and only falls back to a live query for today's not-yet-rolled-up activity — the query cost doesn't grow with how much transaction history accumulates. Lists, exports and deep product pages use keyset paging and SQL-side aggregation rather than offset scans or loading rows into memory.
Architecture
Request path
Client → Auth.js session → App Router route → scopedDb() extension → PostgreSQL (org-scoped)
- Every tenant-scoped model is registered in ORG_SCOPED_MODELS so the extension auto-scopes it — a route can't opt out by accident.
- requireAdmin and a canManage flag enforce the same OWNER/ADMIN-only boundary on both the API route and the UI, so STOREKEEPER/STAFF see a read-only view instead of a form that would just fail server-side.
Background job pipeline
Route enqueues Job (Postgres) → Queue runs right after the response → Worker claims it (race-safe) → UI polls for DONE/FAILED
- Vercel's Hobby plan only allows daily crons, so the route that enqueues a job runs that organization's queue as soon as its response is sent, and the UI can also trigger it on demand. The daily sweep is the fallback: it re-queues jobs whose worker died mid-run, fails them after three attempts, and deletes finished jobs older than seven days.
- Imports run in batches and exports in chunks, so a large file is many short invocations instead of one that can time out.
Screens
Location and employee names anonymized — this is live production data.
Result
A live, deployed multi-tenant inventory system covering the full Products / Purchases / Sales cycle — org switching, role-scoped permissions, purchase and sales order lifecycles, CSV import/export, stock counts, returns, bundles, valuation, two-factor, API keys, digests, and a real-time-feeling dashboard backed by pre-aggregated rollups. Org isolation and stock correctness are proven under concurrent load by dedicated integration suites, not just asserted by code review. Twelve roadmap phases are done; the remaining plan (packaging hierarchy, sales and purchasing documents, warehouse operations, light manufacturing, integrations) is scoped but not built.
By the numbers
Stack
Next.js, TypeScript, Tailwind, Prisma, PostgreSQL, Auth.js, Vitest, Playwright
Lessons learned
- The org-scoping guarantee needed more than a code review to trust — a scripted audit plus a dedicated integration suite firing concurrent cross-org reads/writes was necessary to actually prove isolation holds under real load, not just in a single request.
- An early CSV import ran synchronously on the request, risking timeouts on large files. Moving it to a Postgres-backed job queue with UI polling avoided pulling in Redis just to make one import path non-blocking.
- The primary hosted Postgres instance was repeatedly unreachable mid-build — the fallback became a documented local-Postgres workaround (fresh scratch database, replay every migration, reseed, run the integration suite twice back to back to confirm zero residue) rather than skipping verification when the database was down.
- Features-first building left real races behind: several flows that looked atomic read a value, checked it, then wrote it back. A deliberate hardening pass, locking rows and moving to relative updates with integration tests that race the requests, caught what unit tests never would. I now treat "passes sequentially" as the weaker half of correctness for anything that touches stock.
Click to zoom · Esc to close