--- title: "Keforo App" description: "The order desk for MENA e-commerce teams: orders, stock, customers and a Google Sheet that stays in sync." date: 2024 language: en canonical: https://aissamirhir.com/work/keforo-app source: aissamirhir.com --- # Keforo App The order desk for MENA e-commerce teams: orders, stock, customers and a Google Sheet that stays in sync. - Kind: product - Status: live - Year: 2024 - Built with: Next.js, Hono, Bun, MongoDB, Redis, BullMQ, Google Sheets, OpenRouter - Site: https://app.keforo.com ## Summary Every order in one board with a strict lifecycle, stock that moves in the same transaction as the status, a Google Sheets connector that needs no Google app review, and an AI assistant that can only do what the person asking is allowed to do. In Arabic, French and English. - An order is a state machine: one endpoint per transition, its own scope for the money-sensitive moves, stock side-effects in the same MongoDB transaction - Google Sheets sync with three ways to connect, column mapping with drift detection, a conflict policy per sheet and one job per run on a queue - An AI assistant made of an orchestrator and five specialists whose every tool checks the caller's scopes before it runs - Bun and Hono behind a Next.js front, 386 routes, 68 scopes across 18 modules, 585 backend tests ## How it works An order as a state machine, one Google Sheets sync replayed, an assistant that can only do what you may, billing by bank transfer, and why the stack is what it is. ## What it runs Only what the code runs today. Every line below is live in the product, in Arabic, French and English, with a real right-to-left layout. - **Orders.** One board for every order with filters that live in the URL, a strict lifecycle, line items that can be cancelled or marked out of stock one by one, partial returns that restock, and an activity log with 18 kinds of entry. - **Stock.** Warehouses and inventory in two buckets, on hand and committed. Every change writes a stock movement that points back at the order, import or return that caused it. - **Customers, countries and cities.** Who ordered, and the delivery zones a shop serves. An order carries its payment method, cash on delivery, card, wire or other, and its payment status. - **Google Sheets.** A sheet linked to the app is read on a schedule or on demand, mapped column by column, and upserted without touching rows that did not change. - **Imports.** CSV and Excel files through the same processors, with progress checkpoints so a browser refresh does not lose the run. - **Team.** Members, roles and a scope matrix: 68 scopes across 18 modules, edited per person. Team chat rooms over the same WebSocket. - **Assistant.** A chat that reads orders, products and KPIs and can take a few actions, with a confirmation step and a permission check on every tool. - **Billing.** Plans, paid by bank transfer with an uploaded proof and an admin review. Renewals, grace periods and suspensions run on cron. ### How a request moves through the system The assistant has its own diagram further down; this is the path an order or a sheet takes. ```flow { "direction": "LR", "height": 420, "nodes": [ { "id": "browser", "label": "Browser", "sub": "Next.js 16 · 72 pages", "kind": "client" }, { "id": "api", "label": "API", "sub": "Hono on Bun · 386 routes", "kind": "api" }, { "id": "mongo", "label": "MongoDB", "sub": "50 models · transactions", "kind": "store" }, { "id": "redis", "label": "Redis", "sub": "BullMQ · limits · pub/sub", "kind": "queue" }, { "id": "workers", "label": "Workers", "sub": "sync · cron · imports · billing", "kind": "worker" }, { "id": "google", "label": "Google APIs", "sub": "Drive · Sheets", "kind": "provider" }, { "id": "ws", "label": "WebSocket", "sub": "rooms · same port", "kind": "api" } ], "edges": [ { "from": "browser", "to": "api", "label": "REST · SSE" }, { "from": "api", "to": "mongo", "label": "read · write" }, { "from": "api", "to": "redis", "label": "queue.add" }, { "from": "redis", "to": "workers", "label": "claim" }, { "from": "workers", "to": "google", "label": "values.get" }, { "from": "workers", "to": "mongo", "label": "bulkWrite" }, { "from": "workers", "to": "ws", "label": "emit" }, { "from": "ws", "to": "browser", "label": "syncRun:finished", "dashed": true } ] } ``` ## An order is a state machine Every status change in the system goes through one service. It checks the move against a table, applies what the move does to stock, writes the new status and appends to the activity log, all inside one MongoDB transaction. If any part fails, none of it happened. ```flow { "direction": "LR", "height": 420, "nodes": [ { "id": "draft", "label": "draft", "sub": "being built, no stock held", "kind": "note" }, { "id": "pending", "label": "pending", "sub": "placed · units committed", "kind": "queue" }, { "id": "confirmed", "label": "confirmed", "sub": "status only", "kind": "queue" }, { "id": "packed", "label": "packed", "sub": "status only", "kind": "worker" }, { "id": "shipped", "label": "shipped", "sub": "held until delivered", "kind": "worker" }, { "id": "delivered", "label": "delivered", "sub": "sold · on hand −", "kind": "store" }, { "id": "refunded", "label": "refunded", "sub": "on hand +", "kind": "note" }, { "id": "cancelled", "label": "cancelled", "sub": "committed released", "kind": "note" } ], "edges": [ { "from": "draft", "to": "pending", "label": "POST /place" }, { "from": "pending", "to": "confirmed", "label": "POST /confirm" }, { "from": "confirmed", "to": "packed", "label": "POST /pack" }, { "from": "packed", "to": "shipped", "label": "POST /ship" }, { "from": "shipped", "to": "delivered", "label": "POST /deliver" }, { "from": "delivered", "to": "refunded", "label": "POST /refund" }, { "from": "draft", "to": "cancelled", "dashed": true }, { "from": "pending", "to": "cancelled", "label": "POST /cancel", "dashed": true }, { "from": "confirmed", "to": "cancelled", "dashed": true }, { "from": "packed", "to": "cancelled", "dashed": true } ] } ``` Eight statuses, two of them terminal. A shipped order cannot be cancelled from the app, because the parcel is already with the courier; it is delivered and then refunded. ### Try it Pick a token, then press an endpoint. The route refuses a missing scope before the handler runs, the service refuses a move that is not in the table, and a legal move changes the status, moves stock and writes the log. ```demo order-transitions ``` ### The rules the code enforces - **One endpoint per transition.** There is no `PATCH status`. Place, confirm, pack, ship, deliver, cancel and refund are seven routes, each with its own validation, its own scope and its own line in the activity log. - **Money-sensitive moves have their own scope.** Confirm, pack, ship and deliver share `orders:transition`. Cancel is `orders:cancel` and refund is `orders:refund`, so an operations agent can run the day without being able to undo a sale. - **Stock and status move together.** Cancelling releases the committed units, delivering removes them from on hand and committed, refunding puts them back on hand. The stock write comes first, inside the same transaction, so a failed stock update aborts the transition. - **Every stock change is a row.** A stock movement records the bucket, the before and after, the reason and the order it belongs to. Inventory can always be explained. - **Atomic guards, not checks.** The update that sells a unit requires on hand and committed to still cover the quantity; if a concurrent write got there first, the update matches nothing and the move fails with 409. - **Line items have their own life.** A line can be cancelled, marked out of stock or restored while the order is still in the shop; each releases or re-reserves exactly its units. If every line is cancelled, the order follows. - **Returns restock.** From shipped or delivered, a partial return of one or more lines credits stock back and writes its own movement, under the refund scope. - **Re-applying a status does nothing.** The same request twice is a no-op, not a second stock operation. ## One sheet, one run Most shops in the region run on a Google Sheet before they run on anything else. The connector treats the sheet as the source and the app as the mirror: each run reads the sheet and pushes every row through the same import processor a CSV upload uses, and nothing is written back to the sheet. ```replay { "chrome": "Keforo App · worker · google-sheet-sync", "request": { "title": "Commandes · sheet “Ventes 2026”", "meta": "orders · daily 06:00 · sheet wins", "quote": "6,214 rows, 13 mapped columns, sheet wins, last synced yesterday at 06:00" }, "status": { "idle": "idle", "pending": "queued", "processing": "running", "completed": "success", "failed": "failed" }, "labels": { "replay": "Replay", "toFail": "Replay with a moved column", "toOk": "Replay the happy path" }, "variants": { "ok": { "steps": [ { "call": "queue.add google-sheet-sync", "note": "one job per sheet, from the cron or the Sync now button", "ms": 4, "play": 420, "status": "pending" }, { "call": "SyncRun.status = running", "note": "written before the adapter starts, so the browser shows it without polling", "ms": 3, "play": 380, "status": "processing" }, { "call": "drive.files.get version", "note": "watermark 118 → 121: the sheet changed since the last run", "ms": 142, "play": 520 }, { "call": "mapping.checkDrift", "note": "13 headers still where the mapping expects them", "ms": 61, "play": 440 }, { "call": "sheets.values.get ×2", "note": "5,000 rows per page, two pages, under the per-org limit of 60 calls a minute", "ms": 1184, "play": 1800 }, { "call": "orders.import ×6214", "note": "212 new orders, 5,987 existing ones replaced from the sheet, 15 left alone because they are already packed or later", "ms": 638, "play": 1200 }, { "call": "LinkedSheet.lastDriveVersion = 121", "note": "the next run compares against this and skips an unchanged sheet after one call", "ms": 5, "play": 380 }, { "call": "SyncRun.counts + errorSample", "note": "0 failed; the error sample is capped at 1,000 rows", "ms": 8, "play": 380 }, { "call": "ws.emit syncRun:finished", "note": "the organisation’s room hears it at once", "ms": 2, "play": 420, "status": "completed" } ], "done": "Done in {seconds} s. Tomorrow at 06:00 the worker checks the version first; an unchanged sheet costs one Drive call and is marked skipped_no_change.", "status": "completed" }, "fail": { "steps": [ { "call": "queue.add google-sheet-sync", "note": "one job per sheet, from the cron or the Sync now button", "ms": 4, "play": 420, "status": "pending" }, { "call": "SyncRun.status = running", "note": "written before the adapter starts", "ms": 3, "play": 380, "status": "processing" }, { "call": "drive.files.get version", "note": "watermark 121 → 122: someone edited the sheet", "ms": 139, "play": 520 }, { "call": "mapping.checkDrift", "note": "the column mapped to city is gone: someone renamed “Ville”", "ms": 58, "play": 900, "fail": true }, { "call": "LinkedSheet.status = mapping_drift", "note": "the sheet is paused, not deleted; its mapping is kept", "ms": 6, "play": 420 }, { "call": "escalation.onMappingDrift", "note": "one email to the owner, not one per failed run", "ms": 210, "play": 520 }, { "call": "SyncRun.status = failed", "note": "with the missing column named; a run is never left running", "ms": 5, "play": 420, "status": "failed" } ], "done": "Failed after {seconds} s. Nothing was written. The wizard proposes a new mapping for the renamed column; until someone confirms it, the sheet stays paused. There is no automatic retry, because it would fail the same way.", "status": "failed" } } } ``` ### Three ways to connect Google reviews every app that asks for Drive access, and the review takes time. So the connector has three doors, and two of them need no review at all. ```flow { "direction": "LR", "height": 420, "nodes": [ { "id": "sheet", "label": "Your sheet", "sub": "Google Sheets", "kind": "provider" }, { "id": "oauth", "label": "Google sign-in", "sub": "file picker · drive.file scope", "kind": "client" }, { "id": "csv", "label": "Public CSV link", "sub": "no Google API at all", "kind": "client" }, { "id": "account", "label": "Service account", "sub": "share the sheet with an email", "kind": "client" }, { "id": "linked", "label": "Linked sheet", "sub": "mapping · conflict policy · cadence", "kind": "store" }, { "id": "run", "label": "Sync run", "sub": "one job, one worker at a time", "kind": "worker" } ], "edges": [ { "from": "sheet", "to": "oauth" }, { "from": "sheet", "to": "csv" }, { "from": "sheet", "to": "account" }, { "from": "oauth", "to": "linked", "label": "token, encrypted" }, { "from": "csv", "to": "linked", "label": "content hash" }, { "from": "account", "to": "linked", "label": "key, encrypted" }, { "from": "linked", "to": "run", "label": "cron · Sync now" } ] } ``` ### The rules inside the connector - **Mapping is proposed, then confirmed.** Headers are matched to fields with a confidence of exact, normalized, fuzzy or unmapped. You fix what the matcher got wrong, then a dry run shows what would be written before anything is. - **A moved column pauses the sheet.** Each run checks that every mapped header is still there. If one is gone, the sheet becomes `mapping_drift`, the person who connected it gets one email, and the wizard proposes a re-map. Nothing is guessed at run time. - **One conflict policy per sheet.** Database wins leaves an existing order alone; sheet wins and latest-updated replace it from the row. An order that is packed or later is never touched by a sheet, whatever the policy. Chosen once, applied to every row. - **Rows are matched by reference, not by guess.** An order keeps the sheet’s reference in its source, and a partial unique index on organisation, platform and reference makes a re-sync, a stalled job re-run or a duplicate worker harmless: the second insert is refused and the row counts as skipped. - **Cadence is a cron or an interval.** Every 4 hours to yearly, anchored at 06:00 Casablanca time for the daily and longer ones; every 15 minutes and hourly exist in the code, hidden until a paid tier. - **One job at a time.** The sync worker runs one job per process with a two-minute lock. Two runs upserting the same collection tripped MongoDB write conflicts in a cascade, so concurrency is one, on purpose. - **No automatic retry.** A failed run is a run someone has to look at: a gone sheet, a lost connection, a moved column. Retrying would fail the same way and hide the cause. Three failures in a row mark the sheet `failing` and switch it back to manual sync until someone re-arms it. - **Sixty calls a minute per organisation.** A fixed window in Redis mirrors Google’s own per-user ceiling. A job that hits it is re-queued with the wait time the limiter reports. - **Cancel is cooperative.** A flag is checked between pages. A cancelled run writes the counts it reached and stops. - **A run always ends.** Queued, running, then success, partial success, failed, cancelled or skipped because nothing changed. The worker marks a crash as failed rather than leave a run running. ## The assistant only does what you may The chat in the sidebar is an orchestrator that owns no domain knowledge and one tool: `delegate`. It hands a sub-task to a specialist, in parallel when a question spans domains, and writes the answer from what comes back. ```flow { "direction": "LR", "height": 420, "nodes": [ { "id": "you", "label": "You", "sub": "chat in the sidebar", "kind": "client" }, { "id": "chat", "label": "POST /ai/chat", "sub": "SSE stream", "kind": "api" }, { "id": "orchestrator", "label": "Orchestrator", "sub": "sees one tool", "kind": "worker" }, { "id": "delegate", "label": "delegate", "sub": "one specialist per domain", "kind": "queue" }, { "id": "specialist", "label": "Specialist", "sub": "order · product · customer · analytics · helper", "kind": "worker" }, { "id": "tool", "label": "Tool", "sub": "11, each declares a scope", "kind": "api" }, { "id": "scope", "label": "requireScope", "sub": "your JWT, never the model’s word", "kind": "note" }, { "id": "data", "label": "MongoDB", "sub": "scoped to your organisation", "kind": "store" } ], "edges": [ { "from": "you", "to": "chat", "label": "message" }, { "from": "chat", "to": "orchestrator", "label": "turn" }, { "from": "orchestrator", "to": "delegate", "label": "sub-task" }, { "from": "delegate", "to": "specialist", "label": "in parallel" }, { "from": "specialist", "to": "tool", "label": "call" }, { "from": "tool", "to": "scope", "label": "first line" }, { "from": "scope", "to": "data", "label": "then the service" }, { "from": "specialist", "to": "chat", "label": "tokens", "dashed": true } ] } ``` - **Permission is checked on every tool call.** The first line of every tool is `requireScope`. The scopes come from the caller’s token; a model saying “the user is an admin” changes nothing. Wildcards work the way they do in the API, and the system bypass never crosses an organisation boundary. - **Specialists you cannot use do not exist.** A specialist whose tools need scopes the caller lacks is left out of the orchestrator’s prompt, so the model cannot even try. - **Writes wait for a person.** Cancelling an order or changing a price creates a pending action that the person confirms in the chat. The scope is checked again at confirmation. - **Models are an allow-list.** Four tiers from premium to economy, each entry with its vendor, context window and cost per million tokens. Provider errors are classified and fall back along a chain instead of failing the turn. - **Every turn leaves a trace.** Which specialist ran, which tools, how long, how many tokens. Admins read traces, a turn can be replayed, and a long conversation is compacted by a prompt rather than truncated. - **Three languages in the prompt too.** Locale overlays for Arabic, French and English, so the assistant answers in the language the shop works in. ## Billing by bank transfer Most businesses here do not pay by card. A subscription is a bank transfer with a photo of the receipt, reviewed by an admin, then advanced month by month by a cron. ```flow { "direction": "LR", "height": 420, "nodes": [ { "id": "request", "label": "Request", "sub": "plan · months · proof", "kind": "client" }, { "id": "review", "label": "pending_review", "sub": "an admin checks the transfer", "kind": "worker" }, { "id": "approved", "label": "approved", "sub": "subscription active", "kind": "store" }, { "id": "declined", "label": "declined", "sub": "with a reason", "kind": "note" }, { "id": "expired", "label": "expired", "sub": "7 days without review", "kind": "note" }, { "id": "period", "label": "Monthly period", "sub": "the cron advances it", "kind": "queue" }, { "id": "grace", "label": "Grace", "sub": "3 days · reminder email", "kind": "note" }, { "id": "suspended", "label": "suspended", "sub": "back to the free plan", "kind": "note" } ], "edges": [ { "from": "request", "to": "review", "label": "upload" }, { "from": "review", "to": "approved", "label": "approve" }, { "from": "review", "to": "declined", "label": "decline", "dashed": true }, { "from": "review", "to": "expired", "label": "stale", "dashed": true }, { "from": "approved", "to": "period", "label": "each month" }, { "from": "period", "to": "grace", "label": "months run out" }, { "from": "grace", "to": "suspended", "label": "after 3 days" }, { "from": "grace", "to": "request", "label": "renew", "dashed": true } ] } ``` - **A request is reviewed, not trusted.** The proof is uploaded, an admin approves or declines with a reason, and the organisation’s plan changes only on approval. - **The cron does the calendar.** One lifecycle job advances the billing period each month, warns when the paid months run out, gives three days of grace with a reminder email that carries the bank details, and suspends after that by dropping the plan to free. - **Stale requests expire.** A request nobody reviewed in seven days becomes `expired`, so the queue an admin sees is always current. - **Card payment is written, not switched on.** A Stripe service with idempotency keys per operation and a retry helper exists in the code; the card tab in the app is disabled. Bank transfer is the live path. ## Under the hood - **Tokens.** The access token lives in memory; the refresh token never reaches the browser. It sits in an encrypted, HTTP-only cookie that only a server action reads. Protected routes check a signed role cookie before rendering. - **Tenancy.** Every document carries its organisation. A middleware loads and tenant-guards the order before a handler sees it, and background jobs do the same lookup with their own guard. - **Routes as data.** Each route declares its method, path, scopes and schema; one declaration feeds the router, the permission check and the request validation. There are 386 of them. - **Realtime.** A WebSocket server native to Bun, on the same port as HTTP, with rooms for everyone, one person, one organisation and one chat room. Ten thousand connections, fifty per address, ten per person, 64 KB per message, a circuit breaker that resets after 30 seconds, Redis pub/sub across instances. - **Queues.** Four BullMQ workers: sheet sync, sheet cron, imports, billing lifecycle. Sync and imports write progress as they go, so the browser follows a run without polling. - **Errors in three languages.** Every module carries its own messages in Arabic, French and English; a 409 says why in the language of the shop. - **Tests.** 585 backend tests in 52 files against MongoDB and Redis, including scenario tests for the assistant; 65 tests on the front. ## Why this stack - **Bun and Hono.** TypeScript end to end, no build step, one runtime for the API, the workers and the scripts. Hono stays out of the way and lets routes be declared as data. - **MongoDB with transactions.** An order, its lines, its payment summary and its metadata are one document, and the stock update that goes with a status change is in the same transaction. Fifty models, each carrying its organisation. - **Redis and BullMQ.** A sync run reads thousands of rows and cannot live inside an HTTP request. BullMQ gives a durable job, a lock only one worker holds, a concurrency cap and a repeat schedule that is either a cron or an interval. Redis also holds the per-organisation rate limit and the WebSocket fan-out. - **A WebSocket, not polling.** The worker reports itself when a run ends, the chat streams, notifications arrive. One socket, rooms per audience. - **Next.js and TanStack.** Filters, sort and page live in the URL so a view is a link. Tables are virtualised because an order list is long, and queries are cached and invalidated by the events the socket delivers. - **One gateway for models.** Every assistant call goes through OpenRouter, so the model behind a specialist is a configuration row with a cost, not a dependency. - **Google Sheets first.** It is where the orders already are. Reading a sheet well, with mapping, drift detection and hashing, is worth more to a shop here than a store connector it does not use yet. ## Not there yet - Shopify, WooCommerce, YouCan and Salla are listed as coming soon. A WooCommerce OAuth controller is scaffolded; nothing syncs from a store today. - The sheet is read, never written. Changes made in the app do not flow back to the spreadsheet. - No per-row change detection yet. Under sheet wins, every editable order in the sheet is rewritten on each run; only the whole-sheet version check saves an unchanged sheet from a full pass. - Card payment through Stripe is coded but switched off; bank transfer is the only live way to pay. - No shipping-carrier integration. Delivery zones are countries and cities; the courier is outside the app. - No exports, and no mobile app in this code base.