Sole Full-Stack Engineer Jan — Jun 2026 roasteryhub.com

RoasteryHub

The operating system for a wholesale coffee roaster — and a study in getting multi-tenant correctness right at the database, not in the app.

Next.js 15 PostgreSQL · RLS TypeScript Server Actions Supabase Realtime Claude API Zod next-intl

← All projects

Visit roasteryhub.com ↗

Small roasters run on WhatsApp, spreadsheets and paper labels. They roast coffee, split it into lots, and sell it in bulk to cafés on credit terms. RoasteryHub turns that into software: roast batches, FIFO inventory, credit-limited ordering, recurring standing orders, and a café-facing portal — with every roaster fully isolated from every other one.

18.4KLines of TypeScript

1,200Lines of hand-written SQL

10Core tables

2Languages · EN & TR

Two kinds of user, one database

A roaster’s staff see everything in their roastery. A café logs into the same system and must see exactly one row — its own. Both surfaces run against the same tables, so the boundary can’t live in application code where one forgotten WHERE clause leaks a competitor’s customer list.

It lives in Postgres row-level security. Policies are deny-by-default; the tenant key is resolved from the session, never passed in by the client.

Two separate policies on the same table rather than one combined rule — each access path stays readable, and extending one can’t silently widen the other.

The bug that taught the most

Auth worked end to end. Users logged in, sessions refreshed, roles resolved. And every single database query returned nothing, silently, with no error worth reading.

Postgres has two independent access layers. GRANT decides whether a role may touch a table at all; RLS decides which rows it sees — and RLS only runs after GRANT lets you in. Without the grants, the policies never even execute. It became its own migration, and its own entry in the decision log.

-- migration 006: the layer everyone forgets GRANT SELECT, INSERT, UPDATE, DELETE ON public.customers TO authenticated; -- only now do the RLS policies get a say

Money and stock in one transaction

Confirming an order does four things at once: resolve the customer’s tier price, allocate coffee from the oldest lots first, check the order against the credit limit, and move the balance. Do those as four separate steps and a concurrent order slips between them — two orders each pass a credit check they’d jointly fail, or both claim the same five kilos.

An application mutex can’t fix it, because serverless runs many instances. So the pipeline runs as a single Postgres transaction with row locks: it commits together or not at all.

FIFO allocation under lock

Coffee is perishable, so the oldest roast has to leave first. The allocator walks candidate lots in roast-date order, locking each as it goes, and splits a single line item across several lots when no one lot can cover it. If it can’t fill the order it raises and the whole transaction unwinds — no partial shipments, no negative inventory.

for lot in select il.id, il.weight_kg_remaining from inventory_lots il join roast_batches rb on ... where il.status = 'available' order by rb.roast_date asc for update -- lock as we go loop exit when remaining <= 0; ...

Credit as a business state, not an error

The credit check locks the customer row before it reads the balance, so two concurrent orders queue instead of racing. When the limit would be crossed it returns false rather than raising — the caller marks the order credit_blocked and the roaster sees it in the queue. An exceeded limit is a thing that happens in business, not a crash.

Prepay customers skip the balance entirely. And the balance column is signed, because real customers overpay and get refunds — a “must be positive” constraint would break on the first credit note.

Every one of these calls is recorded in docs/DECISIONS.md — an architecture decision log in ADR format, eleven entries, each with the reasoning and the alternatives that were rejected.

New order Filter Café · 12 kg Ethiopia Yirgacheffe

Net 30

Lot BR-ETH-0412-1
roasted 12 Apr · 5.0 kg on hand DrawnHeld 5.0 kg

Lot BR-ETH-0412-2
roasted 12 Apr · 5.0 kg on hand DrawnHeld 5.0 kg

Lot BR-ETH-0419-1
roasted 19 Apr · 5.0 kg on hand PartialHeld 2.0 kg

Lot BR-ETH-0426-1
roasted 26 Apr · 5.0 kg on hand DrawnHeld —

Balance: €2,400
Credit limit: €3,000
Order total: €348.00

Reject Approve order

03 — Order engine

Approve once, everything moves together.

Approving walks the lots in roast-date order under a row lock, draws what each can give, checks credit against a locked customer row, and writes the ledger — inside one transaction.

Oldest roast leaves first, split across as many lots as needed
Credit limit checked under SELECT … FOR UPDATE
A blocked order never consumes stock
Balance derived from an append-only ledger

Approving runs one transaction: oldest roast first, split across as many lots as it takes, credit checked under a row lock, ledger written. Any block and the whole thing unwinds — there is no state where a rejected order has already eaten the stock.

Cron run materialize_due_standing_orders · 06:00

Europe/Istanbul

Filter Café — 5 kg Ethiopia
weekly · Mondays · next run 22 Jun Created

Blue Bottle — 20 kg Colombia
weekly · Mondays · next run 22 Jun Created

Sunset Café — 8 kg Kenya
cron fired twice — second insert rejected Skipped23505 unique_violation on orders_standing_run_uniq (standing_order_id, run_on)

Ritual Café — 3 kg Brazil
paused until 18 May Not due

Created: 2
Skipped: 1
Errors: 0

04 — Scheduling

Run it twice. Nothing doubles.

Cron fires twice more often than anyone expects — a retry, a cold start, a second worker waking up. So the guard is not in the application: a unique index on the standing order and its run date means the database refuses the duplicate outright.

The second insert comes back as a unique violation, the loop catches it, counts it as skipped and carries on. Every run writes what it created and what it refused into an audit table, so production behaviour is readable without opening the logs.

Rows taken with FOR UPDATE SKIP LOCKED — workers never collide
Duplicate rejected by the database, not by application logic
Order created and next_run_on advanced in the same transaction
Run date pinned to a fixed timezone, not the server’s

“Which cafés haven’t ordered in three weeks?” get_quiet_customers → one hard-coded SELECT, run as you

Assistant Quiet customers · 21+ days

read-only

Sunset Café
last order 4 Apr · 34 days · balance €0 34d

Ritual Café
last order 19 Apr · 26 days · balance €180 26d

Morning Press
last order 24 Apr · 22 days · balance €0 22d

Tools available: 6
SQL written by model: none
Tenant scope: implicit — RLS

Ask something else Draft re-engagement email

05 — Assistant

The model gets tools, not a database.

The obvious build is to let the model write SQL. That hands it the keys. Here it picks from six fixed read-only tools instead — inventory, freshness, customers and balances, pending orders, sales, standing orders — and each one runs a single hard-coded query through the roaster’s own session.

So tenant scope is not something the model can get wrong. It cannot pass a roastery id, cannot reach a table nobody exposed, and cannot write. Ask it about the weather and it declines rather than inventing.

Six tools, each one query — the model emits only a tool name
Runs under the caller’s row-level security, so isolation is automatic
Unknown tool name returns an error, never a guess
Hand-written loop — capped rounds, trimmed history, cached prompt

Stack

Web

Next.js 15 · App Router
React 19 · Server Components
Server Actions + useActionState
Zod validation
Tailwind · Radix UI
next-intl · EN / TR

Data

PostgreSQL · Supabase
Row-level security
9 append-only migrations
SECURITY DEFINER functions
Partial unique indexes
Generated TypeScript types

Realtime & jobs

Supabase Realtime
Explicit channel cleanup
Vercel Cron
Secret-gated cron endpoint
Run audit table

AI

Claude API
Natural language → SQL
Four-layer injection defense
Re-engagement copy generation
Cupping-log quality insight

Outcome

Live with paying roasters. Sales are closed on a call and invoiced directly — the product doesn’t need a checkout, so it doesn’t have one.

The domain is modelled properly rather than generically: green-to-roasted weight loss, freshness windows, lot codes, cupping scores, tiered pricing, net payment terms. The dashboard surfaces “batches leaving the freshness window tomorrow” and “customers who haven’t ordered in 21 days” — operational signals, not vanity metrics.

The guarantee moves from “every developer remembers the WHERE clause” to “the database won’t let you.”