# Nearle "Fiesta" backend — what was built A Go monolith that serves a multi-tenant retail platform: neighbourhood shops (tenants) with one or more outlets, selling through a consumer app, a rider delivery fleet, and — the newest and largest piece of work — **physical point-of-sale terminals sitting on shop counters**. There are really five distinct systems in here. They are ordered below by how much original engineering they represent. --- ## 1. The POS terminal integration (the centrepiece) ### What it is Retail tills in shops run a Flutter app with its own local SQLite database. They keep selling with **no network at all** — a village shop's connection drops for hours. When connectivity returns, each till uploads the bills it rang while offline, pulls down an updated product catalogue, and reports its own health. This backend is the other end of that conversation. It has to solve the classic offline-first sync problem under a hard constraint: *a sale that has already been paid for in cash must never be lost, and must never be counted twice.* The problem it solves for the business: shops that were doing counter sales entirely off-platform now have those sales in the same database as their app orders, deducting from the same stock, appearing in the same revenue reports. ### How it's built ``` ┌─────────────────────────────────────────────────────────┐ │ Till (Flutter + SQLite) — not in this repo │ │ sale committed locally first, sync_status = 0 │ └───────────────┬──────────────────────────┬──────────────┘ │ │ (transport A) MQTT (transport B) HTTPS nearle/pos/{loc}/{term}/order POST /live/api/v1/pos/orders nearle/pos/{loc}/{term}/customer POST .../customers nearle/pos/{loc}/{term}/health POST .../health │ │ ▼ ▼ ┌──────────────────────┐ ┌────────────────────────┐ │ messaging/posmqtt.go │ │ controllers/ │ │ • leader election │ │ posController.go │ │ • bounded worker │ │ • PosAuth middleware │ │ pools (ingest, │ │ verifies token + │ │ health) │ │ outlet ownership │ └──────────┬───────────┘ └───────────┬────────────┘ └────────────┬──────────────┘ ▼ services.PosService ← one code path for both │ ┌─────────────────┼──────────────────┐ ▼ ▼ ▼ posRepository posSalesRepository posPresence (ingest, catalogue) (read-back) (Redis) │ │ │ ▼ ▼ ▼ ┌──────────────────────────────┐ ┌──────────────┐ │ POSTGRES (nearledb) │ │ REDIS │ │ pos_orders │ │ pos:terminal:│ │ pos_order_items │ │ {id} HASH │ │ productstocks ← shared │ │ TTL 90s │ │ customers, app_users │ │ pos:location:│ └──────────────────────────────┘ │ {id} SET │ └──────────────┘ │ ▼ ack → nearle/pos/{loc}/{term}/ack (or HTTP 200 body) │ ▼ Till marks bill synced, keeps its copy 7 more days ``` The storage split is deliberate and explained in the code: - **Postgres `pos_orders` / `pos_order_items`** — the permanent record of counter bills, kept *separate* from the app's `orders` table. A bill carries a cashier, a terminal id, a rounding adjustment, promo campaigns, loyalty movement and a payment split across several tenders; `orders` has nowhere to put any of that. The stated cost of the split is that every revenue query has to union both — which was done, in `orderRepository.posRevenue` and `posSalesTotals`. - **Postgres `productstocks`** — stock is deliberately *not* split. A counter sale writes the same "out" ledger rows an app order does, through the same helper, so the catalogue pushed down to a till reflects the till's own trading. - **Redis** — terminal presence only, with a 90-second TTL. Shared with a separate Express backend (the rider app reads the board), namespaced `pos:*` so it cannot collide with that service's `delivery:*` / `city:*` keys. ### How the problem was solved — the approach Six interlocking decisions. **(a) The acknowledgement protocol is the whole design.** Delivery is at-least-once. The rule, stated in `models.PosAck`: *a till marks a record synced if and only if its id appears in `accepted`.* Silence is not acceptance — an empty ack, a dropped connection, or a 200 with no body all leave the record pending, and it gets re-sent. `rejected` is a separate, deliberate verdict meaning "stop retrying this one, fetch a human" — used for a malformed bill, never for "the database is having a bad minute." That distinction is enforced right up in the HTTP layer: `posIngestError` decides 4xx (permanent — the till halts and shows a person) versus 5xx (unknown — the till keeps everything and backs off). **(b) Idempotency: a duplicate is a success, not a failure.** Each bill carries a UUID minted at the till, stored as `terminalorderid` with a unique index. On arrival, `importPosOrder` opens a transaction, takes `pg_advisory_xact_lock(hashtext('possale:' || id))`, then checks whether the bill is already held. If it is, the transaction rolls back and the bill is **accepted** — stock untouched. Calling a re-delivered bill a failure would strand a day's takings on the till forever. The advisory lock turns what would otherwise be a unique-constraint violation into an orderly "already held," and closes the race where two redeliveries arrive simultaneously. **(c) Ordering inside the transaction — locks, then availability, then writes.** `stockLedger.go` holds the shared machinery. `lockStockRows` takes `SELECT … FOR UPDATE` on every `(tenant, location, product)` the sale touches, **sorted by (productid, locationid)** so two concurrent sales sharing products always contend in the same sequence and block rather than deadlock. Only then does `assertStockAvailable` read balances, and only then are rows written. This is the same path an app order and a spreadsheet import take — the code was extracted specifically so there aren't three implementations of the anti-overselling rule drifting apart. **(d) Line-item reconciliation.** A subtle one. The till has already apportioned bill-level discounts across its lines to get tax right, but it sends each line at its *pre-apportionment* value. Left alone, summing line items gives the subtotal while the header carries the total, and two reports disagree. So the code computes `amountFactor = (total − roundoff) / Σ line_total` and `taxFactor = header_tax / Σ line_tax`, and scales each line onto what was actually collected. The till stays authoritative for the bill as a whole; this only decides attribution *within* it. **(e) The catalogue downlink is a delta protocol with a safety invariant.** `GET /pos/catalogue` answers either a full snapshot or a change set. The terminal treats `is_delta: false` as a snapshot and **withdraws every product the response does not mention** — so mislabelling a filtered result empties the shop's shelf. The code therefore derives both the filter and the flag from one value: `cutoff := posRevisionCutoff(...)`; zero cutoff ⇒ no filter ⇒ `is_delta: false`, non-zero ⇒ filtered ⇒ `is_delta: true`. There is no path that filters without setting the flag. Supporting details: - The revision is an opaque token `loc{id}-{YYYYMMDDTHHMMSSZ}` the till stores and hands back. If it is unreadable, malformed, or belongs to a *different outlet*, the cutoff is zero and you get a full snapshot — failing toward "send everything" is the only safe direction. - **The revision only advances on the final page.** Mid-pagination it echoes back whatever the till already had. A till that dies half way through a paginated pull must not end up holding a revision claiming it saw pages it never received — those products would be excluded from every future delta, silently, forever. - The revision stamp is taken **one second in the past**, so a product written during the same second the query ran cannot land on the wrong side of the next cutoff. Costs one redundant row; cannot lose one. - "Changed" is one predicate covering three things: the product row, its per-location row (price/availability), or its stock ledger. Stock is in there because a shop's count drifts on every sale rung at another counter. - Acknowledged limitation, in a comment: a product *deleted* from `productlocations` leaves no tombstone, so a delta cannot know to withdraw it. Only a full pull collects those — hence "pull without a revision every morning." **(f) Presence is a TTL, not a table.** A heartbeat is a fact with an expiry date. In Postgres it would be ~288k writes/day across a hundred tills plus a reaper job to mark them dead. A Redis hash with a 90s TTL (three missed 30s heartbeats — "two would make a GPRS hiccup look like a dead till; five would take 2½ minutes to notice a real one") ages out for free. The location→terminals SET has *no* TTL: it is an index of what exists, not a claim anything is alive. A till whose hash expired comes back as a stub marked `offline` with a reason, rather than being omitted — because the missing till is exactly what someone is looking for. The write also `HDEL`s fields the till stopped reporting, so a hash never lingers at a stale battery reading. #### Tricky cases the code explicitly handles | Case | Handling | |---|---| | Bill with no id | Rejected — nothing to dedupe on, and it would double on every retry | | Fractional quantity (1.5 kg onions) vs integer stock column | `roundStockQty` rounds **up** — conservative, never records more stock than is physically there. Flagged in-code as a workaround, not a fix | | Timestamps without a timezone offset (older terminal builds) | Accepted, with a comment stating the instant will be wrong by the offset and is unrecoverable — but the *business date* is right, which is what daily figures use | | `jsonb` columns left at Go's zero value | Forced through `posJSON`, which emits `"null"` — an empty string reaches Postgres as invalid JSON and takes the whole bill down | | Terminal id present on the batch but not the bill | `posTerminalFor` falls back, trimming first so `" "` is not mistaken for a real code | | Broker Last Will (`{"status":"offline"}`) | Arrives on the same handler and is recorded verbatim — exactly right for a till that lost power | | Customer registrations replayed | Insert-if-absent, **never update** — a profile corrected at head office must not be reverted by a till replaying months-old data | | Loyalty points on the uplink | Deliberately absent from the wire format. Points are derived from the bill stream (idempotent, sees every counter); accepting a till's local balance would make "last till to sync wins" | #### Trade-offs, and what they buy - Bills separate from `orders` → full fidelity, at the cost of every report needing a union. - Payment mode denormalised to "largest tender" for grouping, with the full split kept verbatim in `paymentsjson` → fast reports, no lost reconciliation data. - `businessdate` denormalised as a `YYYY-MM-DD` string → a day's takings is one indexed equality match instead of a range scan with timezone arithmetic. - Barcode generation: `products.productsku` cannot be trusted for the till's *unique* barcode index (in live data, thousands of products share a single SKU value), so a SKU is only used if it looks like a real EAN/UPC — 8–14 digits — and otherwise the product id stands in. Scanning physical barcodes will not work until real ones are populated; the comment says so plainly and notes it starts working with no code change. --- ## 2. Transport, concurrency and delivery guarantees ### What it is The same ingest, over two transports (MQTT and HTTP), running under multiple replicas, with backpressure that reaches all the way back to the till. ### How it's built `messaging/posmqtt.go` (broker client, topic routing, ack publishing) plus `messaging/posworkers.go` (a bounded worker pool). Both hand off to the same `PosService` the HTTP controller uses — the facade exposes `f.PosService()` specifically so a bill cannot behave differently depending on how it arrived. ### The approach **Leader election with no coordination service.** MQTT has no queue groups — every subscriber gets every message, so three replicas would each commit the same bill and publish three acks. The ingest is idempotent so nothing double-counts, but it is 3× the database work. The fix: a StatefulSet gives pods stable ordinal names, so **ordinal 0 is the elected consumer** — no lease, no lock, no extra dependency. Overridable via `POS_MQTT_CONSUMER=always|never`; a non-ordinal hostname (bare container, local dev) is elected, because "a single instance that refused to consume would be a far more confusing failure." **Backpressure by construction.** paho delivers on one goroutine, so naively every bill commits serially — a bill is a full transaction (advisory lock, dedup, row locks, availability, four inserts, commit) at ~10–30 ms, giving 30–100 bills/sec, and a shop-wide backlog after an outage takes minutes. paho *can* call handlers concurrently, but it spawns without limit — a storm would open a transaction per message, exhaust the connection pool, and stall everything at once. So: a fixed pool behind a bounded queue, and `submit` **blocks** when the queue is full. That is the point — paho stops acking, the broker's in-flight window fills, it stops sending, and the till holds its bills and retries. `SetOrderMatters(true)` is kept on precisely because single-goroutine delivery is what makes that chain work; concurrent delivery would let paho keep reading no matter how far behind the workers were. **Separate pools for bills and heartbeats.** A heartbeat is one Redis write; a bill is a transaction. Sharing a queue would delay presence behind a bill backlog, and every till would appear to go dark at the exact moment the system was busiest. **The pool's shutdown race is handled explicitly.** A plain `select` over a done-channel and the job channel is not enough — once both are ready Go picks at random, and picking the send panics on a closed channel. So there is an `RWMutex` held for *reading across the whole of `submit`*, and `stop` takes the write lock before closing. The comment works through why this cannot deadlock: workers only exit once the channel is closed, which happens under the write lock the in-flight send is holding off. Post-close submissions run **inline** rather than being dropped — discarding a bill that already reached you is worse than doing it slowly. **Identity comes from the topic, never the body.** `topicIdentity` parses store and terminal out of `nearle/pos/{store}/{terminal}/{kind}`. A till that could name its own store in a payload could post sales into another shop's books. There is a test named exactly that: `TestABodyCannotOverrideTheTopicIdentity`. **Shutdown order is load-bearing.** `Close()` drains the worker pools *before* disconnecting, so a bill mid-commit still gets its ack out. Disconnecting first would strand it — committed here, unacknowledged there, re-sent on the till's next attempt. Payloads are copied in `wrapHandler` because paho reuses its buffer once the handler returns, and the work now happens after that. The test suite here is genuinely good: bounded concurrency, blocking-not-dropping, drain-on-stop, idempotent stop, payload copying, ordinal election, and "a failed presence write does not stop the till." --- ## 3. Terminal authentication and shop-managed staff ### What it is Before this work, the POS surface was completely open: a till held a store id typed into a Settings screen and a password compiled into the app, so `store_id=1185` in a URL was enough to read another tenant's catalogue or post bills into their books. One leaked build opened every tenant on the platform. This subsystem replaces that with a real session, and adds shop-run staff management on top. ### How it's built ``` POST /pos/login (the only unguarded route — it's where tokens come from) │ authname|contactno + password [+ optional configid, location_id] ▼ posAuthRepository.PosLogin │ reads the SAME app_users rows the web console authenticates against │ resolves the outlet FROM the user's record — never from the wire ▼ posService.mint → utils.MintPosToken │ base64url(payload) "." base64url(HMAC-SHA256) │ claims: uid, tid, lid, rid, cid, trm, iat, exp TTL 30 days ▼ PosSession { token, expires_at, role, can_manage_staff, tenant + GSTIN + address (for the printed invoice), locations[] (picker for multi-outlet owners), staff[] (so the till can trade immediately) } │ ▼ every other /pos/* route → middleware.PosAuth 1. verify signature 2. is the named outlet owned by the token's tenant? ``` ### The approach **A signed, self-describing token rather than a session table.** Reasoning given in `utils/postoken.go`: a till is not a browser. It signs in when the shop opens and bills for a whole day on a connection that comes and goes, so the credential must survive reboot, network loss, and an hour in a drawer. A server-side session table fails that (a till that cannot reach you must still be able to prove who it is when it returns), and so does a short expiry. **Deliberately not JWT.** One issuer, one audience, one algorithm — the header JWT spends bytes negotiating is a constant. And `alg` is the source of JWT's worst-known footgun (`alg: none`); a format with no algorithm field cannot have that bug. The MAC is taken over the *encoded* payload so verification never re-serialises anything. **Verification order is deliberate:** signature first, *then* expiry. Reading `exp` out of an unverified payload would mean taking the attacker's word for when their own token runs out. Comparison is `hmac.Equal` (constant time). A token that verifies but names no outlet is refused, so it cannot be mistaken for one that authorises everything. **The middleware's second check is the one that matters.** A valid token is not a licence to name *any* outlet — it is a licence to name *your* outlets. `requestedLocation` reads all three spellings the routes use (`store_id`, `locationid`, `location_id`) rather than breaking terminals in the field by normalising, and — crucially — **searches the JSON body, not just the query string**, because the two routes that *write* carry `store_id` in the batch and never in the URL. It handles the id being sent quoted or bare, since accepting only one shape would silently skip the check, and "a skipped check reads exactly like a passed one." **A migration escape hatch, honestly labelled.** `POS_AUTH_REQUIRED` defaults to **off**, because terminals are already in shops billing real customers against unauthenticated endpoints and flipping enforcement at deploy would stop every one of them mid-trade. While off, a token that *is* sent is still fully verified and a wrong-tenant request is still refused — the flag only governs requests carrying none. **Two-tier sign-in: password opens the terminal, PIN switches the operator.** - The session token is the security boundary. A four-digit PIN is not: `POST /pos/login/pin` sits *behind* the guard, so guesses are confined to one already-opened outlet's own staff. - PIN login mints a **fresh** token rather than reusing the presented one, so a cashier taking over from a supervisor drops the supervisor's permissions instead of inheriting them. - PINs travel in the clear over TLS, and the model file argues the case rather than hiding it: four digits is brute-forceable in microseconds whatever it is wrapped in, so hashing here buys the appearance of strength; meanwhile the terminal salts every PIN with its own random salt, so a hash computed server-side could never be verified there without inventing and maintaining a shared scheme across two codebases. The honest framing: **a PIN is shift attribution, not a security boundary.** **Schema archaeology, handled rather than wished away:** - `authname` is not unique in `app_users` — live data has the same address twice under one config. Rather than `LIMIT 1` (which would let a stranger's account shadow the one a person meant, and on a POS means billing into the wrong tenant), multiple matches are **refused** with an actionable message. - Inactive accounts are excluded from the *match*, not matched-then-refused, so a deactivated leaver cannot make a live login ambiguous. - `configid` (which tenant portal an account belongs to) is asked for by the web console because the browser knows it — but a person at a counter has never seen the number. So it is honoured when sent, inferred when not, and an ambiguous inference is reported rather than guessed. `PosConfigidFor` infers it from whichever value the tenant's existing accounts most commonly carry. - Staff come from **two** sources unioned: the purpose-built `tenantstaffs` table (a dozen rows on the entire platform) and `app_users.locationid` (where staff actually ended up). Reading either alone returns the wrong answer. - Duplicate PINs are dropped from the response, because live data has one PIN shared across many accounts — a shared PIN would attribute a bill to whichever row was read first. - PINs are bounded 1000–9999 with **no leading zero**, because `app_users.pin` is a `bigint`: "0451" stores as 451, and the cashier types four digits and is refused forever. That costs 1000 of 10000 combinations and buys a PIN that means the same thing in both directions. Obvious PINs (1234, 1111, …) are rejected. - `userid` is left to Postgres's identity column, with a comment explaining the earlier mistake: `information_schema.column_default` is empty for identity columns, which reads like "no default," and a hand-rolled MAX+1 leaves two allocators racing. - Emails go through `NULLIF(?, '')` because a unique constraint means a second PIN-only cashier would collide on the empty string, whereas NULLs do not collide in Postgres. **The inversion, applied to people.** `PosUserRequest` has no tenant and no location field. A supervisor creating staff can only ever create them at their own outlet, and *no field in the struct can say otherwise* — the same inversion that stopped a till naming its own shop. Updates are scoped by tenant *and* location in the `WHERE` clause rather than checked first, so a wrong user id updates zero rows and is reported, instead of quietly editing another shop's staff. Deactivation is a status change, never a delete, because bills carry the cashier's name. You cannot deactivate the account you are signed in as, or the last supervisor could lock the whole shop out with one tap. The same service calls are exposed to the web console under `/web/tenants/*` and `/mob/tenants/*` — deliberately the same code, not a parallel implementation, "because two code paths writing one table is exactly how that stops being true." The route file itself flags that this half is weaker: the console *asserts* its outlet where a terminal *proves* it, and says these should move behind a session guard as soon as the console can hold one. --- ## 4. The shared order + stock engine ### What it is One transactional path that every sale in the platform goes through, regardless of channel: an app order, a spreadsheet import of historical counter sales, or a POS bill. ### How it's built ``` CreateOrder (app) UploadOfflineSales (spreadsheet) importPosOrder (till) │ │ │ │ per-bill tx + advisory lock per-bill tx + advisory lock │ + remarks-based dedup + terminalorderid dedup ▼ ▼ │ ┌──────────────────────── createOrderTx ──────────────────────┐ │ │ 0. lockStockRows (FOR UPDATE, sorted, deduped) │ │ │ 1. assertStockAvailable (ledger balance, all lines first) │◄───────┤ (uses the same │ 1b. priceOrderLines (fill unpriced lines from catalogue) │ │ stockLedger.go │ 2. nextSequenceNo (UPDATE…RETURNING inside the tx) │ │ helpers directly) │ 3. insert header, insert lines, recordStockOut per line │ │ │ → syncProductLocationStatus re-derives availability │ │ └─────────────────────────────────────────────────────────────┘ │ │ contract: on failure it has already rolled back; │ │ on success tx is left OPEN so the caller can │ │ include its own dedup guard in the same tx │ ▼ ▼ COMMIT pos_orders + productstocks ``` ### The approach **Stock is derived, never stored.** Availability is `SUM(in) − SUM(out)` over `productstocks`, computed under the row locks. `productlocations.status` is a *derived cache* re-synced from that balance after every movement, in the same transaction, by the same rule on both the sale side and the receiving side. A commit message captures the earlier bug this replaced: receiving stock used to overwrite `products.productstatus` — a per-product **lifecycle** column — with an **availability** value, destroying the lifecycle state of well over a hundred products. Availability is a per-outlet fact and a single column on `products` cannot express it, since the same product can be stocked at one outlet and empty at another. **Order-number allocation.** `nextSequenceNo` does read-and-increment in a single `UPDATE … RETURNING` inside the caller's transaction. The doc comment is a small forensic report on the previous implementation: two separate calls on `r.db` (not the transaction), so concurrent orders read the same value; a `NULL` counter made `COALESCE(MAX(x)+1, 1)` evaluate `NULL+1 = NULL` and fall through to a hardcoded `"-1"`, so a whole cohort of live orders share one id; tenants with multiple sequence rows hit a `GROUP BY` where the read kept the first row and the write updated all of them. The fix pins to `MIN(sequenceid)`, seeds a NULL from the tenant's existing order count (guaranteed ≥ any id already issued, so recovery never reissues), and creates the row on first use. **Two rounding conventions, kept apart on purpose.** `legacyOrderQty` (truncate, floor at 1) is preserved *exactly* for app orders — changing it would silently alter stock deduction for every order in production. `roundStockQty` (ceil) is used by the POS path. Both are named, tested, and flagged in a comment as something to reconcile once someone owns the decision. That is the right call: the divergence is documented rather than papered over. **Deduplication without a natural key.** The spreadsheet importer dedupes on `orders.remarks = "OFFLINE:"` under an advisory lock. When the sheet has no bill number, an **FNV-1a hash of the bill's own contents** (date, mobile, payment mode, and each line's product/qty/price) stands in — so re-uploading the same file is a no-op rather than a double stock deduction. The trade-off is stated: two genuinely separate identical baskets on the same day with no bill numbers will collide, and the result *names* the collision rather than hiding it. **Referential scaffolding.** The order-listing query INNER JOINs five tables, so an imported order with a zero `applocationid` or `customerid` would be written successfully and then be **invisible in every screen**. `resolveOfflineLocationContext` is the single place where "this location belongs to this tenant" is established (so editing a locationid in a spreadsheet reaches nothing), and it fills the scaffolding from `tenantlocations` plus the most recent real order at that outlet — because `tenantlocations` carries 0 for `moduleid`/`partnerid` at outlets whose live orders use non-zero values. It refuses outright rather than writing an order that will never be visible. **Batch semantics.** Each bill is its own transaction, so one bad row cannot undo the rest, and the response says exactly which landed, which were duplicates, and which failed with why. Branch context and catalogue are memoised per outlet so a workbook covering six branches does not re-run both queries per bill. Also here: `priceOrderLines` fills lines the client sent unpriced from the merchant's own catalogue, using arithmetic that *deliberately matches* the offline importer exactly — gross, minus discount, tax extracted from the landing amount because shelf prices are MRP (tax inside). One convention for both channels, so the same basket rings up the same either way. This fixed a real bug where catalogue-imported products had no per-store price, so real delivered orders recorded zero revenue. --- ## 5. Brand catalogue bridge and the rest of the platform ### What it is A **second, isolated Postgres database** holds a curated master catalogue of packaged goods, organised one table per brand, with rich metadata (title, description, nutrients, highlights, FSSAI licence, size, variant key, providers). Shop owners browse it and "import" products into their own store catalogue rather than typing them in. Product photography lives in S3-compatible object storage. ### How it's built ``` CatalogueDB (separate conn, may be nil) Object storage (S3-compatible) brand_ tables daily/brands/{brand}/{image_id}/* │ │ │ catalogueRepository │ db/imagestore.go │ (table name from a fixed allowlist) │ full LIST → in-memory map, │ │ swapped atomically, 30-min refresh └──────────────┬───────────────────────────────┘ ▼ productService.ImportCatalogueProduct │ snapshot into tenant `products` (keyed brand+catalogueid) │ + link via productlocations (price, stock, status) ▼ nearledb: products / productlocations / productstocks ▼ POS catalogue pull · customer app · order lines ``` ### The approach - **Isolation is enforced structurally.** `NewCatalogueRepository` takes the catalogue connection and *never* `db.DB`. If the catalogue env vars are absent, the connection is simply nil and catalogue endpoints return a normal error — it must never block startup of the main app. Same rule for Redis and the image store: optional dependencies degrade, they do not kill the process. The one dependency that *is* fatal on misconfiguration is the MQTT broker, and the comment says why: "coming up healthy while every till quietly queues is the worse failure." - **Table names cannot be parameterised in SQL**, so brand → table goes through a fixed allowlist map. That is the correct pattern. - The catalogue DSN is built as a URL and percent-encoded via `net/url` rather than a `keyword=value` DSN, because that password contains characters the keyword format would misparse as quoting/comment syntax. - GORM's raw scan silently drops slice-kind destination fields, so `text[]` columns are cast to text in SQL and parsed in Go. - Cross-brand browsing merges and sorts in Go, with an explicit note that this is fine at a few hundred rows and should become a `UNION ALL` if it grows. - The image store caches a full object listing so no GET ever calls out to S3, rebuilt from scratch every 30 minutes and swapped under a write lock. A nice deployment detail: the endpoint is bucket-qualified, and the SDK's virtual-hosted-style client re-prepends the bucket — so the client is pointed at the bare region host or requests get addressed to `bucket.bucket.…`. - Import is idempotent on `(tenant, brand, catalogueid)`: re-import updates pricing rather than creating a duplicate product. **The surrounding platform** — roughly two thirds of the file count — is a conventional layered Fiber/GORM app: orders, deliveries (rider dispatch, status lifecycle with mirrored timestamps, rider/report summaries), products and stock, tenants and outlets, customers, partners, users, and FCM push. Most of it is CRUD and reporting SQL. The parts worth noting are the *repairs*, which are documented in-place with the evidence that motivated them: - `UpdateDelivery` used to write the parent order with `WHERE orderheaderid = ?` from a client-supplied field. Clients often omitted it, making it `= 0`, matching nothing — and GORM reports no error for an update affecting zero rows, so the API answered success while the order silently kept its old status. Hundreds of deliveries were marked delivered against orders still reading pending. Fixed by deriving the link from `deliveryid` (the one field every caller must send) and failing loudly when the row is not there. - The rider push-notification route had been commented out since the initial commit — the handler, the model, the Firebase service account and the Dockerfile `COPY` were all in place; only the route registration was missing. So every rider push the admin console ever sent returned 404, and riders were assigned deliveries and never told. - New store outlets and their logins were being forced to `InActive`, which blocked the spawned manager login before it could ever reach the password-setup screen. - Tenant onboarding created admin users with `configid = 0`, which the web login (which queries `configid = 1`) could never find — permanently unfindable accounts. - Order line items were being silently dropped. --- ## The stack **Language / runtime** - Go 1.24 **Web / API** - Fiber v2 (`gofiber/fiber/v2`), CORS middleware, custom `PosAuth` middleware **Data** - PostgreSQL (primary, `nearledb`) via GORM 1.25 + `pgx/v5` driver — heavily raw SQL, GORM mostly as a connection/scan layer - A second PostgreSQL instance for the brand catalogue (described in code as pgvector) - Redis 7-family via `go-redis/v9` — TTL-based presence, shared with a separate Node/Express service - Postgres features used directly: advisory locks (`pg_advisory_xact_lock`), `SELECT … FOR UPDATE`, `UPDATE … RETURNING`, `jsonb`, identity columns **Messaging** - Eclipse Mosquitto over MQTT, `eclipse/paho.mqtt.golang` v1.5 — QoS 1, persistent sessions, retained messages, Last Will **Crypto / auth** - `crypto/hmac` + SHA-256, custom compact token format (not JWT) **Cloud / integrations** - AWS SDK Go v2 S3 client pointed at an S3-compatible object store - Firebase Cloud Messaging via `golang.org/x/oauth2` JWT service-account flow (`firebase.google.com/go` present) **Config / ops** - `godotenv`, `spf13/viper`, `time/tzdata` (Asia/Kolkata baked in) - Multi-stage Dockerfile → static binary on Alpine - Kubernetes StatefulSet *(inferred — from the `HOSTNAME` ordinal election logic and the `-0` convention, not from manifests in this repo)* **Testing** - Go stdlib `testing`, table-driven, with hand-rolled fakes for the MQTT client and the POS service — no mocking framework **Consumers of this API** *(not in this repo; described in docs and comments)*: a Flutter POS terminal app with local SQLite, a React/TypeScript merchant console, a consumer mobile app, a rider app, and a separate Node/Express backend sharing the Redis instance. --- ## What's genuinely hard here **1. The ack protocol and idempotency, together.** Anyone can write "insert if not exists." What is hard is the discipline that follows from at-least-once delivery when the payload is *money that has already changed hands*: a duplicate must be reported as success, silence must never be interpreted as acceptance, "reject" must be reserved for permanent faults, transport errors must produce *no* ack at all, and the whole thing has to hold under a shutdown. The code gets all five right and the reasoning is written down at each decision point. The advisory-lock-then-check pattern — turning a constraint violation into an orderly "already held" — is the specific move to point to. **2. The delta/snapshot invariant in the catalogue pull.** The failure mode — `is_delta: false` on a filtered response empties a real shop's shelf — is the kind of bug that only shows up in a store, at a counter, with a queue. Deriving the filter and the flag from a *single* value so no code path can set them inconsistently is the right structural answer, not a defensive check. The pagination detail (never advance the revision mid-pull) and the one-second cutoff overlap are both real distributed-systems reasoning: each is a choice about which direction to fail in, and each picks "redundant work" over "silent permanent data loss." **3. Backpressure that reaches the physical device.** The chain is: bounded queue blocks → paho's single delivery goroutine stalls → broker's in-flight window fills → broker stops sending → till holds its bills. Every link is a deliberate configuration choice (`SetOrderMatters(true)` exists solely to preserve link two). The alternative designs are both worse in ways that are only obvious once you have reasoned it through: unbounded goroutines exhaust the connection pool and stall everything at once; dropping work loses a sale you had already accepted. Plus the pool's close race — recognising that `select` over done-and-jobs picks randomly and can panic on a closed channel, and solving it with a read-lock held *across the send* — is a genuinely subtle piece of Go concurrency. **4. Retrofitting authorisation onto a live, unauthenticated fleet.** The intellectual move is small and correct: **invert the direction of the store id.** It was an input (typed into Settings, believed on the wire); it becomes an output (derived from the authenticated user's record, sealed under a signature). Everything else follows — `PosUserRequest` having no tenant field, the middleware's tenant-owns-outlet cross-check, the read-the-body-not-just-the-query detail that closes the hole on the exact routes that write. The rollout strategy (ship the endpoint, let the fleet adopt, then flip `POS_AUTH_REQUIRED`) is how you do this without stopping a hundred shops trading, and the code is honest that the flag is a temporary state and not a design. **5. Sharing one transactional path across three channels without forking it.** `createOrderTx`'s contract — *on failure it has already rolled back; on success the transaction is left open so the caller can put its own dedup guard inside the same transaction* — is unusual and slightly dangerous, but it is what allows an app order, a spreadsheet import and a POS bill to share row-locking, availability checks, ledger writes and sequence allocation. The alternative (three implementations of the anti-overselling rule) is exactly the drift that produces "empty in the database, full on the shelf." **6. Making a hostile schema work without a migration.** This is unglamorous and it is a lot of the actual difficulty. Non-unique `authname`; a `bigint` PIN column that eats leading zeros; a `roleid` of 0 that is not a role; `app_roles` with six rows for four roles and most accounts carrying an id that is not in it; a `registrationno` column that is really the GSTIN; `configid` varying per tenant with no way to look it up; a SKU column where thousands of products share one value; tax rates in live data that include 3, 7, 15 and −1 when Indian GST only has 0/5/12/18/28. Each of these gets a handler *and a written justification measured against the actual data*, rather than a schema change nobody has the appetite to run. The negative-GST floor is a good example of why this matters: a negative rate would put negative tax on a bill and a negative figure in a slab on a **filed tax return**. **7. The commenting itself.** Worth calling out explicitly. Nearly every non-obvious decision carries a comment that states the alternative considered, the failure it prevents, and often the count of live rows that motivated it. Several read as small post-mortems (the sequence-number one, the delivery-status one). That is a real engineering artefact, not decoration — it is what makes the codebase maintainable by someone who was not there. --- ## Flags before publishing any of this ### Security issues found while reading 1. **A Firebase service-account JSON key is committed to the repository** and `COPY`'d into the Docker image by the Dockerfile. That is a live private key in version control. Rotate it and move it to a secret/volume mount. 2. **`.env` was tracked until 2026-08-03.** The `.gitignore` says so itself and notes the database credentials are still in history and the password should be rotated. That does not appear to have happened. 3. **SQL injection in `tenantRepository.CheckTenantByNo`** — the contact number is string-concatenated into raw SQL. Everything else in the codebase parameterises; this one does not. 4. **Passwords are stored and compared in plaintext platform-wide.** There is an explicit `TODO` acknowledging it, correctly noting a POS token minted off a plaintext password is only as good as that column. The comparison is at least constant-time. 5. **The main web/mobile API has no authentication at all.** `SECURITY_HANDOFF.md` documents this: identity comes from client-supplied query params, so requesting another tenant's id returns their data. Eight IDOR endpoints were patched with controller-level scoping guards, but the doc is clear that the root fix has not started. 6. **`POS_AUTH_REQUIRED` defaults to false**, so the POS surface is open unless explicitly enabled — intentional and documented, but worth confirming whether it has been flipped in production. 7. **The `/web/tenants/*` and `/mob/tenants/*` staff routes mint till credentials on unauthenticated requests.** The route file flags this itself. 8. **CORS is `AllowOrigins: "*"` with `AllowCredentials: true`** — that combination is rejected by browsers and is a smell either way. ### Commercially sensitive — genericise before this goes public - **Named brand partners** in the catalogue allowlist (six FMCG brands, some of them major). That is a partnership roster. - **The production hostname** and the deployed API base path, which appear in the proof scripts under `scratch/`. - **Live customer/tenant identifiers** — the scratch scripts and doc examples contain real tenant ids, location ids, store names and at least one real email address. All of them have been kept out of this document. - **Data-quality statistics about the live estate** (row counts, how many products share a SKU, how many accounts use a given PIN, duplicate-order-id counts, desynced-delivery counts). These make the writeup much more convincing, but they are an unflattering audit of a client's production data. Keep the reasoning and drop or round the numbers — "thousands of products shared a single SKU value" carries the point without publishing the audit. Exact figures have been rounded or removed here already. - **The terminal sync contract itself** (topic structure, ack semantics, revision format). It is the interface between two of their products; the *techniques* are portfolio-safe, the exact wire protocol less so. ### Inferred rather than read directly Deployment as a Kubernetes StatefulSet (from the ordinal election logic, not from manifests); the existence and behaviour of the Flutter till app, the React console, the consumer/rider apps and the Express backend (from docs and comments — none are in this repo); and that the catalogue database uses pgvector (asserted in comments, but nothing in this repo issues a vector query).