DEV Community: Poojan

I Have Idempotent Search Endpoints Doing POST. Should They Be QUERY?

Poojan — Fri, 03 Jul 2026 00:00:00 +0000

TL;DR — In June 2026, HTTP got a new method: QUERY (RFC 10008). It's safe, idempotent, and cacheable like GET, but carries a request body like POST — exactly the shape of every "search with a big filter payload" endpoint you've ever been forced to build as a POST. It's real and standards-track. It is also barely deployable in a browser today (no fetch support, needs a CORS preflight, and plenty of proxies still reject unknown methods). My verdict: model your API around it now, keep POST on the wire until the ecosystem catches up.

The endpoint I've apologized for a hundred times

Every backend I've built has one endpoint that lies about what it is. On my multi-tenant food-tech platform it's the order search:

POST /orders/search
{
  "outletIds": [12, 44, 91, 156],
  "status": ["preparing", "out_for_delivery"],
  "dateRange": { "from": "2026-06-01", "to": "2026-06-30" },
  "customer": { "phoneSuffix": "8821" },
  "sort": [{ "field": "placedAt", "dir": "desc" }],
  "page": 3, "pageSize": 50
}

That is a read. It changes nothing. Call it a hundred times, get the same answer a hundred times. Semantically it is a GET. But it's a POST, and it's a POST for one dumb, unavoidable reason: the filter doesn't fit in a URL. Try to encode that nesting into a query string and you get an unreadable ?outletIds[]=12&outletIds[]=44&status[]=...&dateRange[from]=... mess that flirts with URL length limits, has no agreed-upon syntax for arrays and nested objects, and leaks a customer's phone digits into every proxy log, browser history entry, and Referer header along the way.

So we all reach for POST. And the moment we do, we lie to the entire HTTP stack:

We tell caches "this is dangerous, never cache it" — even though it's the single most cacheable thing in the system. Our CDN and reverse proxy shrug and pass every identical search straight through to the database.
We tell every retry layer "do not automatically retry this" — because POST is defined as non-idempotent, meant for creating things. So a dropped response on a pure read can't be safely re-sent by middleware, even though re-sending is completely harmless.

This is the same theme I keep hitting: HTTP semantics are load-bearing, and when you pick the wrong verb, you silently switch off the infrastructure built to help you. (It's the mirror image of the payment webhook post — there the problem was making a write idempotent; here it's that a read has been mislabeled as a write.)

What QUERY actually is

RFC 10008, "The HTTP QUERY Method," was published as a Proposed Standard in June 2026. It defines exactly the method that gap has needed since the beginning of HTTP. In the spec's own words, it's for cases where the "data conveyed is too voluminous to be encoded in the request's URI."

Put my three verbs side by side and the hole QUERY fills is obvious:

Property	`GET`	`POST`	`QUERY`
Safe (no state change)	✅	❌	✅
Idempotent (retry-safe)	✅	❌	✅
Cacheable	✅	✗ (effectively no)	✅
Carries a request body	❌	✅	✅

QUERY is safe (the client isn't asking to change anything), idempotent (it "can be automatically repeated or restarted without concern for partial state changes" — so retry layers are free to retry it), and cacheable. The POST /orders/search above becomes an honest QUERY /orders, with the same JSON body, and every layer of the stack now understands what it's looking at.

The genuinely new trick: a cache key that includes the body

This is the part I find most interesting, and it's the reason QUERY isn't just "GET with a body" cosplay.

For a GET, the cache key is the URL. Simple. But a QUERY's meaningful input is in the body, so RFC 10008 says the cache key MUST incorporate the request content and its metadata. A reverse proxy or CDN looks at the Content-Type and the body bytes together and can serve a cached response for two identical queries.

The spec even anticipates the obvious objection — "but {"a":1,"b":2} and {"b":2,"a":1} are the same query with different bytes." Caches are permitted to normalize away "semantically insignificant differences" (formatting, key order, content encoding) when computing the key, without altering the request itself.

That's a real capability GET-with-body never had and POST fundamentally can't have. My identical dashboard searches — same filter, fired by twenty operators every morning at the lunch-rush ramp-up — could be served from the edge instead of hammering Postgres. (I've written before about what that read load does to a database under peak; pushing repeat reads to a cache is precisely the pressure valve.)

So why haven't I migrated?

Because "standardized" and "deployable" are two very different dates, and in July 2026 the gap between them is wide. Here's the honest state of the ecosystem, and it's the part the explainer posts skip:

Browsers can't send it. There is no QUERY support in fetch() or XMLHttpRequest today. MDN has no page for it, there's no caniuse entry. If your caller is a web frontend, you literally cannot issue a QUERY from the browser right now. That alone rules it out for most user-facing traffic.

It needs a CORS preflight. QUERY is not on the CORS-safelist, so any cross-origin call triggers an OPTIONS preflight round-trip. Not fatal, but it's latency and server config you don't pay for with a plain GET.

The middle of the network is hostile to unknown methods. Plenty of proxies, WAFs, API gateways, and legacy load balancers still reject or mangle any method they don't recognize. Your request has to survive every hop between client and origin, and some of those hops were configured in 2015.

Framework support is young and uneven. It exists — .NET 10 shipped client and server support, and various gateways that already allow custom methods can pass it through — but this is early-adopter territory, not "npm install and go."

You lose shareable URLs. A filtered view expressed as a QUERY body can't be bookmarked, pasted into Slack, or deep-linked the way ?status=preparing can. For some search UIs that's a real downgrade.

What I'm actually doing about it

Not a rewrite. A cheap bit of future-proofing that costs almost nothing and pays off the day the browser gap closes:

1. Model the endpoint as a query, whatever verb carries it. I'm making sure my search handler is a pure, side-effect-free function of its request body — no logging that mutates, no "record this search" write sneaking into the read path. If it's genuinely safe and idempotent in behavior, flipping the wire method later is a one-line routing change, not a redesign. Same discipline as isolating order creation into one pure service function: keep the semantics clean and the transport swappable.

2. Keep POST on the wire for now , but treat that as a deployment detail, not the design. Where I control both ends (server-to-server, internal services, a CLI, my own SDK) I can experiment with real QUERY today, because there's no browser and often no hostile proxy in the path — that's where adoption will actually start.

3. Set up cache keys to include the body deliberately , so that when I do switch, the caching semantics RFC 10008 describes are something I've already reasoned about rather than a surprise.

The Kreya team put the pragmatic rule best in their write-up: if there's no immediate need to change an endpoint, leave it be. QUERY isn't a migration you rush. It's a gap in HTTP that's finally being filled, and the right move is to stop fighting the mismatch — stop pretending your reads are writes — so that adopting the correct method later is trivial.

The one idea to take away

For years we've been shipping reads disguised as writes because HTTP gave us no honest option for "safe request, big body." QUERY is that option. You can't put it on browser traffic yet — but you can stop building your search endpoints in a way that assumes the lie is permanent. Keep the behavior genuinely safe and idempotent, keep the body-as-input clean, and the day fetch learns the verb, your migration is a routing table edit instead of a rewrite.

Reconciliation, Not Sync: Integrating a System That Also Webhooks You Back

Poojan — Fri, 03 Jul 2026 00:00:00 +0000

TL;DR — When you integrate with a system that also mutates the shared data and_also_ webhooks you when it changes, you cannot "keep them in sync." Both sides write, both sides notify, and you get echo loops, out-of-order updates, and fields that quietly diverge. The model that actually works is reconciliation : (1) assign a single owner to every field so a change has an authoritative direction, (2) apply every inbound event idempotently and out-of-order-safely, (3) tag your own writes so you can ignore the webhook they echo back, and (4) run a periodic full sweep that compares both sides and repairs the delta. Live events keep you fresh; the sweep keeps you correct.

The word "sync" is the bug

The ticket says "sync our catalog with the POS." It sounds symmetric and simple. It is neither, and the word itself is what leads teams into a swamp.

Picture the setup. Your platform has a menu — items, prices, availability. You integrate a point-of-sale system that the restaurant also edits directly. Now:

The restaurant marks an item out of stock in the POS → the POS webhooks you.
An operator marks the same item available in your dashboard → you push to the POS.
The POS applies your push → and webhooks you back that it changed.

Two systems, both authoritative-feeling, both mutating, both notifying. "Sync" implies there is one true state and you're keeping a mirror of it. But there isn't one true state — there are two writers racing, and every write generates a notification that can trigger another write. The moment you try to make A always equal B by reacting to change events, you've built a distributed feedback loop. Let me show you the three ways it bites, because naming them is most of the cure.

Failure 1: the echo loop

You receive a POS webhook: item 88 is now unavailable. You dutifully write available = false and, because your own writes are supposed to propagate, you push that change... back to the POS. The POS sees a write, and webhooks you: item 88 changed. You apply it, and push again.

  [POS] ──"item 88 unavailable"──▶ [you: write + push back]
     ▲ │
     └──────────"item 88 changed"───────────────┘ ← infinite echo

Nothing is wrong with either system individually. The loop is emergent — it exists only in the seam between them. In production this shows up as a column that flickers, a webhook log that never goes quiet, and rate-limit warnings from the POS at 2 a.m.

The fix is to make your writes attributable. When you apply an inbound change, tag it as originating from the POS and do not echo it back out. Only changes that originate locally get pushed:

async function applyInbound(event) {
  await db.items.update(event.itemId, {
    available: event.available,
    last_source: 'pos', // this change came FROM the pos
    pos_version: event.version, // remember what the pos told us (see Failure 2)
  });
  // note: NO push back to the POS. We do not propagate what the POS just told us.
}

async function applyLocal(itemId, patch) {
  await db.items.update(itemId, { ...patch, last_source: 'local' });
  await pos.push(itemId, patch); // only LOCAL-origin changes go outbound
}

That single "who caused this change" tag breaks the loop. It's the integration version of a rule that keeps showing up: know who owns the write.

Failure 2: out-of-order and duplicate events

Webhooks are delivered at-least-once and not necessarily in order. The POS sends you "available → false" then "available → true" a second apart, and the network hands them to you reversed. If you blindly apply the last one you processed, you end up with false — permanently wrong, and nothing looks broken.

You cannot fix this with timestamps you generate on receipt (they reflect arrival, not truth). You need a monotonic version from the source and the rule "never apply an event older than what I've already applied":

async function applyInbound(event) {
  const row = await db.items.findById(event.itemId);

  // Idempotent AND order-safe: only move forward.
  if (row.pos_version != null && event.version <= row.pos_version) {
    return; // stale or duplicate — we've already seen this or newer. No-op.
  }

  await db.items.update(event.itemId, {
    available: event.available,
    last_source: 'pos',
    pos_version: event.version,
  });
}

If the source doesn't give you a version or updated-at you can trust, you're reduced to reconciliation-only (below) for that field — which is a perfectly honest answer, and better than pretending the event stream is ordered when it isn't. This is the same shape as making payment webhooks idempotent: the event stream is unreliable by contract, so correctness has to live in how you apply, not in the events arriving perfectly.

Failure 3: the fields don't mean the same thing

This is the one that burns a week. Two systems rarely model the same concept identically, and the mismatches are silent:

Your platform has availability (is this sellable right now?). The POS has stock count and a separate tracking mode (is stock even being tracked?). An item with stock = 0 but tracking = off is available, not out of stock. Map "stock 0" straight to "unavailable" and you'll hide sellable items.
The POS "deletes" an item, then "revives" it a day later under the same external id. If your import treats delete as terminal, the revive silently does nothing.
A field you assumed was a boolean is a tri-state; a price you assumed was minor-units is major-units for one region; an id you keyed on gets reused.

There is no clever code for this. The fix is a written field ownership map — for every field, exactly one system is the authority, and you translate meanings explicitly at the boundary:

Field	Owner	Rule
Catalog (name, description, category)	Platform	POS never overrides; local is truth
Price	Platform	pushed to POS; inbound price events ignored
Stock / availability	POS	POS is truth; translate `stock`+`tracking`→`available`
Item existence (create/delete/revive)	POS	delete is soft; a later revive must re-activate

Once ownership is explicit, most "conflicts" evaporate — they were never conflicts, just two systems editing fields the other didn't own. A change to a platform-owned field ignores whatever the POS says about it, and vice versa. Write this table before the code; it is the actual design.

The part that makes it correct: the reconciliation sweep

Everything above keeps you fresh — reacting to live events. None of it keeps you correct, because events get dropped. At-least-once is a floor: a webhook can fail every retry and vanish, a deploy can drop an in-flight batch, a bug can skip an apply. Over weeks, the two systems drift, and no single event tells you they have.

So the load-bearing piece is a periodic full reconciliation — a scheduled job that ignores the event stream entirely, pulls the full state of both sides, and repairs the delta by ownership:

// Runs on a schedule (this is a perfect job for a Postgres-backed
// outbox/cron worker — see the durable-queue post).
async function reconcile() {
  const ours = await db.items.allByExternalId();
  const theirs = await pos.listAllItems(); // full snapshot, not events

  for (const [extId, posItem] of theirs) {
    const local = ours.get(extId);

    if (!local) { await createFromPos(posItem); continue; } // they have, we don't
    // Repair ONLY fields the other side owns:
    if (local.available !== translateAvailability(posItem)) {
      await db.items.update(local.id, { available: translateAvailability(posItem) });
    }
  }
  for (const [extId, local] of ours) {
    if (!theirs.has(extId) && local.last_source !== 'local') {
      await flagOrphan(local); // we have it, POS doesn't — human decision, don't guess
    }
  }
}

Run it nightly (or hourly for volatile data). The live webhooks give you low-latency updates; the sweep guarantees that even if every webhook for a day was lost, you converge to correct within one cycle. This is the reconciliation-not-a-lucky-webhook idea applied to a whole integration: don't trust the stream to be complete — periodically check reality and repair.

Two hard-won rules for the sweep: repair only fields the other side owns (or two sweeps will fight the same field forever), and never auto-delete on "the other side doesn't have it" — flag it for a human. A dropped event and a real deletion look identical from one snapshot; guessing wrong deletes a live menu item.

Audit everything at the boundary

One operational note that paid for itself ten times over: log every inbound and outbound event, raw, before you interpret it. When a price is wrong or an item vanished, the only way to answer "did they send us bad data, did we misapply good data, or did our push do it?" is to replay the boundary. A tiny append-only integration_events table (direction, external id, raw payload, applied-result, timestamp) turns "no idea what happened" into a five-minute query. Integrations fail in the seam; instrument the seam.

What actually broke in production

A column-meaning mismatch marked good items unavailable. We mapped the POS stock field to availability without accounting for tracking = off, so every untracked-but-sellable item went dark. Caught only because a restaurant called about missing menu items. Fix: the explicit translation function, plus a reconciliation pass that re-derived availability correctly for the whole catalog.
delete-then-revive did nothing. Our importer treated a POS delete as final and dropped the row's linkage; the next day's revive created no change because our code had stopped tracking the external id. Fix: soft-delete with the external id preserved, and treat a revive as reactivation.
The echo loop we didn't notice for days — because each hop was individually valid, only the POS's rate-limit emails revealed it. Fix: the last_source tag, and a boundary log that made the loop visible at a glance.

What I'd still improve

Reconciliation is O(catalog) every run. Fine at this scale, wasteful at 10×. I'd move to a hashed/versioned diff — compare a per-item fingerprint and only fetch full detail for mismatches — so the sweep cost tracks the delta, not the total.
Ownership is still partly in my head. It should be declarative config (a table of field → owner → translator) that both the live-apply path and the sweep read from, so they can never disagree about who owns what. Right now the two paths encode it separately, which is exactly the kind of drift this whole post is about.

The one idea to take away

You can never sync two systems that both mutate and both notify — the symmetry is a lie that produces echo loops and silent divergence. You can only reconcile : give every field one owner, apply inbound events idempotently and in-order-safely, tag your own writes so you don't echo them, and run a periodic sweep that treats reality — not the event stream — as the thing to converge to. Events keep you fast. Reconciliation keeps you right.

I Automated 90% of a Grungy Data-Entry Job Without an LLM — and Where an Agent Would Actually Earn Its Place

Poojan — Fri, 03 Jul 2026 00:00:00 +0000

TL;DR — Onboarding a restaurant meant hand-entering hundreds of menu items and finding an image for each — hours of mind-numbing work per client. The 2026 reflex is "point an AI agent at it." I didn't. I built a deterministic pipeline — bulk import, normalize, fuzzy-match images by filename, auto-apply the confident matches — that cleared ~90% of the work with code that is cheap, debuggable, and correct-by-construction. The remaining ~10% (genuinely ambiguous matches) goes to a human-in-the-loop review queue. Then I mark the two specific spots where a vision-language model does pay for itself. The lesson isn't "AI bad" — it's deterministic-first, AI where it's uniquely better, and never an agent for what a for loop does more reliably.

The grungy job

Every new restaurant on the platform arrives the same way: a spreadsheet (or a PDF, or a photo of a printed menu) with a few hundred items, and a folder of food photos with filenames like paneer_tikka_final_v2.jpg. Someone has to create each item, set its price and category, and attach the right image. It's hours of tedious, error-prone clicking per client, and it's the least fun part of onboarding.

This is exactly the kind of task the current discourse says to hand to an autonomous agent: "let the AI read the menu, find the images, and fill in the system." And you can build that. But before reaching for the most powerful, most expensive, least predictable tool available, it's worth asking what the job actually decomposes into. Most "AI-shaped" grunt work is 90% deterministic plumbing wearing a 10% judgment hat.

Step 1: bulk import beats a smart importer

The first instinct people automate away is data entry itself. But most of the input is already structured — a spreadsheet is a table. It doesn't need intelligence; it needs a parser and validation.

// Parse the sheet, normalize, validate. No model. This is 60% of the "AI" job.
function importMenu(rows) {
  return rows.map((row, i) => {
    const item = {
      name: normalizeName(row.name), // trim, collapse spaces, title-case
      price: parsePrice(row.price), // "₹120" | "120.00" | "120" → 12000 (minor units)
      category: canonicalCategory(row.category), // map free-text → known category set
      external_key: slugify(row.name), // stable key for matching (step 2)
    };
    const problems = validate(item); // missing price? unknown category?
    return { item, rowNumber: i + 2, problems }; // +2: header + 1-index, for human-readable errors
  });
}

Everything a model would "understand" here — price formats, category names, whitespace — is a finite, enumerable set of cases. Enumerate them. The payoff: this code is deterministic and testable. The same sheet produces the same output every run, a malformed price is a specific error on a specific row, and there's no token bill. An LLM doing this would be slower, non-reproducible, occasionally hallucinate a category, and cost money per run to do worse than a regex. Wrong tool.

Step 2: match images by filename — fuzzy, but still deterministic

Attaching the right photo sounds like it needs vision. Usually it doesn't, because the filenames already encode the answer: paneer_tikka_final_v2.jpg is obviously the Paneer Tikka. This is a string-matching problem, not a seeing problem.

// Score each image filename against each item name; auto-apply confident matches.
function matchImages(items, filenames) {
  const results = [];
  for (const item of items) {
    const scored = filenames
      .map(f => ({ file: f, score: similarity(slugify(item.name), slugify(stripExt(f))) }))
      .sort((a, b) => b.score - a.score);

    const best = scored[0];
    const gap = best.score - (scored[1]?.score ?? 0); // how clear was the winner?

    if (best.score >= 0.85 && gap >= 0.15) {
      results.push({ item, file: best.file, decision: 'auto' }); // confident → apply
    } else {
      results.push({ item, candidates: scored.slice(0, 4), decision: 'review' }); // ambiguous → human
    }
  }
  return results;
}

The important design choice is the second threshold : not just "is the best match good?" but "is it clearly better than the runner-up?" A high score with a tiny gap ("Chicken Biryani" vs "Chicken Biryani Special", both 0.9) is ambiguous, and the right move is to not guess. That gap check is what makes auto-apply safe — it only fires when the answer is genuinely unambiguous, and everything else falls through to a human instead of being confidently wrong.

Where filenames are missing or useless (IMG_2231.jpg), a web image search by item name fills the gap — fetch a few candidates, present them, still let a human confirm. Deterministic retrieval, human judgment on the final pick.

Step 3: a human-in-the-loop queue for the ambiguous 10%

Here's the part the "full autonomy" pitch gets wrong: the last 10% isn't a failure of automation, it's where the actual judgment lives, and a person resolves it faster and more accurately than a model guessing. So the pipeline's job is to shrink the human's work to only the decisions that need a human — not to eliminate the human.

  spreadsheet ─▶ [import + validate] ─▶ [fuzzy match] ─┬─ auto (90%) ─▶ applied
                                                            └─ review (10%) ─▶ [human queue]
                                                                                     │
                                                                            pick candidate ─▶ applied

The review queue shows the item, the top 3–4 candidate images, and the validation problems — one screen, a few clicks each. A job that was "enter 300 items by hand" becomes "confirm 30 ambiguous matches." That's the win, and it came from ordinary code plus a good UI, not from a model.

Two properties make this trustworthy, and they're the same ones I lean on everywhere: the pipeline is idempotent (re-running on the same input doesn't create duplicates — it matches on external_key, the same discipline behind idempotent order creation), and every auto-decision is logged with its score and gap so a wrong auto-match is auditable and the threshold is tunable from real data.

So where does an LLM actually earn its place?

I'm not arguing against AI here — I'm arguing against using the most expensive, least predictable tool for the parts a for loop does better. There are exactly two spots in this pipeline where a model is genuinely, uniquely better, and it's worth being precise about them:

Unstructured input the parser can't touch. When the menu arrives as a photo of a printed board or a messy PDF, there's no spreadsheet to parse — extracting "item, price, category" from an image is real multimodal understanding, and a vision-language model does it well. This is the honest home for AI in this workflow: turning genuinely unstructured input into the structured rows my deterministic pipeline then takes over. The model does the perception; the boring code does the logic.
Semantic image disambiguation on the hard matches. For the ambiguous 10%, a vision model could look at the candidate photos and the item name and rank them better than filename similarity — "which of these three images is actually Paneer Tikka." That genuinely beats string matching on the residual. But note: it's operating on the pre-filtered hard cases, as an assist to the human reviewer, not as an autonomous decider on all 300 items.

The pattern I'd stand behind: let a model do perception and semantics on the inputs that are truly unstructured or truly ambiguous, and let deterministic code own everything with a definite right answer. That's the opposite of "an agent does the whole job." It's a model as a component with a narrow, well-defined contract — the same way you'd add any other dependency.

A note on cost and reliability, since it's the part the demos skip: running a model over all 300 items, every re-import, is real latency and a real per-run bill for work a regex does for free and a hash does more reliably. Scoped to just the unstructured extraction and the ambiguous residual, the model runs on a tiny fraction of the volume — which is the difference between "AI feature that pays for itself" and "AI tax on a solved problem."

What I'd build next (and what I still wouldn't)

Would build: the vision-model extraction path for photo/PDF menus, feeding the same deterministic pipeline. That's a clean, high-value addition with a narrow contract — extract rows, hand off to code.
Would build: learn the thresholds from the human queue. Every time a reviewer overrides an auto-match or confirms an ambiguous one, that's labeled data to tune the 0.85/0.15 cutoffs — no model required, just feedback.
Still wouldn't build: a fully autonomous "agent" that owns the whole flow end-to-end. It would be less reliable, harder to debug, more expensive, and non-reproducible — trading a pipeline I can reason about for a black box I'd have to babysit. The 10% that needs judgment is cheaper and safer with a human than with an agent pretending to be one.

The one idea to take away

Most "AI could do this" grunt work is a deterministic pipeline with a small core of real judgment. Automate the deterministic 90% with boring, testable, idempotent code; route the ambiguous 10% to a human; and spend an LLM only where it's uniquely better — perception on unstructured input, semantics on the genuinely hard residual. That's not AI-skepticism. It's using the right tool for each part of the job — which is the whole skill.

Never Trust the Client to Create the Order: Making Payment the Source of Truth

Poojan — Fri, 03 Jul 2026 00:00:00 +0000

TL;DR — If order creation happens on the client after payment, a crashed phone means money captured with no order. Move creation to the payment.capturedwebhook so the durable, retrying, server-side actor owns the write. That creates a client-vs-webhook race, which takes three layers to kill: a UNIQUE(txn_id)constraint (the DB has the final say), an "order already exists" guard, and treating duplicate webhooks as expected no-ops. Verify signatures, always 200what you won't act on, and ship it behind a flag.

The bug that doesn't show up in your tests

Here's the order flow almost every food app ships first, because it's the obvious one:

Customer taps Pay.
The payment gateway's SDK opens, they pay, it succeeds.
The SDK returns control to your app.
Your app calls POST /createOrder with the cart.
The order lands in the database. The kitchen sees it.

Every step works. The demo works. The staging test works. And then you go to production, and a handful of times a day, a customer's money leaves their account and no order is ever created.

Look at the flow again. The order is created in step 4 — by the client, after payment. Everything between "money captured" and "order written" is running on a phone you don't control:

The app crashes right after the gateway returns.
The OS kills the backgrounded app before step 4 fires.
The customer's train goes into a tunnel and the /createOrder request never lands.
Someone force-quits because the spinner "looked stuck."

The payment is real. The order is not. You've built a dual-write : two systems (the payment provider and your database) that must both commit, coordinated by the least reliable computer in the entire system — the customer's phone. There is no transaction spanning both. When the second write is skipped, you don't get an error. You get silence, and a support ticket that reads "I paid and nothing happened."

You cannot fix this by adding retries on the client. The client is the thing that's gone.

The fix: make the money the source of truth

The payment provider already knows something happened — that's what a webhook is for. When a payment is captured, the gateway makes a server-to-server call to a URL you own. That call doesn't depend on the customer's phone at all. It retries on its own if your server is briefly down.

BEFORE — client is responsible for the write that matters
                                          ✗ crash / tunnel / OS-kill
  [phone] ──pay──▶ [gateway] ──ok──▶ [phone] ──✗──▶ [POST /createOrder]
                                                          money taken, no order

AFTER — durable server-side actor owns the write
  [phone] ──pay──▶ [gateway] ──payment.captured──▶ [your webhook] ──▶ createOrder()
     ▲ (retries on its own) │
     └────────────── order_confirmed (push) ─────────────────────────────┘

So the order should be created there — on the payment.captured webhook, server-side — not on the client's say-so.

The one thing this requires: at the moment you initiate payment, you have to stash everything needed to build the order somewhere durable, so the webhook can reconstruct it later without the client. I persist the entire order payload onto the transaction row when payment starts:

// At payment initiation — BEFORE the customer pays.
// The order doesn't exist yet, but everything to build it is now durable.
await db.transactions.insert({
  txn_id: txnId,
  status: 'pending',
  payment_type: 'online',
  order_payload: orderBody, // the full cart, exactly as /createOrder expects it
  customer_id: customerId,
});

Now the webhook has everything. The client's job shrinks to "start the payment and then listen for a push telling you the order exists." It is no longer responsible for the order's existence.

// The webhook the payment provider calls. No phone involved.
router.post('/payment/order-webhook', async (req, res) => {
  // 1. Verify it's really from the gateway (see "the boring parts" below)
  if (!verifySignature(req.rawBody, req.headers['x-signature'])) {
    return res.status(400).json({ ok: false });
  }

  const { event, payload } = req.body;
  if (event !== 'payment.captured') {
    return res.status(200).json({ ok: true }); // ack, don't act
  }

  const txn = await db.transactions.findByTxnId(payload.txn_id);

  // 2. Build the order from the payload we stashed at initiation
  const order = await createOrder(txn.order_payload);

  // 3. Push to the customer's app — it's been waiting, not driving
  io.toCustomer(txn.customer_id).emit('order_confirmed', { orderId: order.id });

  return res.status(200).json({ ok: true });
});

That's the whole idea in five lines. The interesting part is everything that idea breaks.

One order, two callers, zero divergence

The moment you have a webhook creating orders, you have two things that create orders: the webhook, and the old client route (which you can't just delete — cash orders, wallet orders, and manual/counter orders still come in through it, and you want a staged rollout, not a big-bang cutover).

Two code paths that build the same object will drift. One will validate stock and the other won't. One will apply the subsidy math slightly differently. Six weeks later they disagree about what a valid order even is, and you're debugging why webhook orders have a different total than client orders.

The fix is to make "create an order" exactly one function — pure business logic, no HTTP, no framework objects, callable from anywhere:

// One function. No req, no res. Throws typed errors so any caller
// can map them to its own transport (HTTP status, socket error, log).
async function createOrder(params) {
  if (!params.txnId) throw orderError('VALIDATION', 'txnId is required');
  if (params.total <= 0) throw orderError('VALIDATION', 'total must be positive');

  const items = await validateAndFetchItems(params.items); // stock, availability
  const outlet = await fetchOutlet(params.outletId);
  enforceMinimumOrderValue(params, outlet); // server-authoritative
  const pricing = applySubsidies(params, outlet); // same math, always

  return db.orders.insert(buildOrderRow(params, pricing));
}

function orderError(type, message) {
  // type ∈ {'VALIDATION','NOT_FOUND','INTERNAL'} → caller maps to 400/404/500
  const e = new Error(message); e.type = type; return e;
}

The HTTP route becomes a thin adapter — parse the request, call the service, map the typed error to a status code. The webhook is another adapter that does the same, minus the response body. The order semantics live in exactly one place , and both entry points are provably identical because they are literally the same function call.

A quiet but important detail: things like the minimum-order-value check and the pricing/subsidy math live inside this service, not in the client. The client can compute a total to show the user, but the number that hits the database is computed server-side, every time. Once the server owns order creation, it should own every rule that decides whether the order is valid — otherwise a modified client is a discount generator.

The race you just created

Here's the trap. You didn't replace the client flow — you added the webhook alongside it. So on a normal, healthy order, both fire :

The client's /createOrder lands (the happy path still works fine).
The payment.captured webhook also lands, a second or two later.

Two writers, same order, racing. Without a guard you get two orders for one payment — which is strictly worse than the bug you started with, because now the kitchen makes the dish twice and settlement is wrong.

The tempting fix is "check if the order exists, and if not, create it." That check-then-act is itself a race: both callers can check "no order" in the same instant and both proceed. You cannot fix a concurrency bug with an if statement in application code.

So I layered three guards, weakest to strongest.

Layer 1 — the database has the final say

Every order carries the payment's transaction id. A single unique constraint makes a second order for the same payment physically impossible, no matter how the code races:

ALTER TABLE orders ADD CONSTRAINT orders_txn_id_unique UNIQUE (txn_id);

Postgres allows multiple NULLs in a unique column, so orders that legitimately have no payment yet (pending, cash-on-delivery being assembled) are unaffected. Whoever inserts second gets a constraint violation instead of a duplicate. This is the backstop that does not depend on me being clever — the other two layers just make it rare and graceful instead of an exception in the logs.

Before you can add this constraint, you have to prove there are no existing duplicates. That one-line GROUP BY txn_id HAVING COUNT(*) > 1 is not a formality — run it, and you'll often find the historical duplicates the bug already created. Fix those first, then constrain.

Layer 2 — the webhook recognizes "the client already won"

Before creating anything, the webhook checks whether an order for this payment already exists. If it does, the client beat it here — so the webhook does not create a second order. It just reconciles the payment status and re-notifies the customer:

const existing = await db.orders.findByTxnId(txnId);
if (existing) {
  await db.transactions.markSuccess(txnId); // just reconcile the money
  io.toCustomer(customerId).emit('order_confirmed', { orderId: existing.id });
  return res.status(200).json({ ok: true });
}

Layer 3 — duplicate webhooks are expected, not exceptional

Payment providers guarantee at-least-once delivery. They will send you the same payment.captured twice, and if your endpoint is slow to 200, they'll retry it as a matter of policy. So "I've seen this transaction already" has to be a no-op, not a second order:

if (txn.status === 'success' && txn.payment_id) {
  // We already fully processed this one. Re-emit the confirmation
  // (the customer may have reconnected) and ack. Do not re-create.
  io.toCustomer(customerId).emit('order_confirmed', { orderId: existing.id });
  return res.status(200).json({ ok: true });
}

And every status write is conditional — UPDATE ... WHERE status = 'pending' — so replaying a webhook can never move a row backward. Conditional updates are the cheapest idempotency tool there is; use them everywhere a webhook writes.

The boring parts that aren't optional

Verify the signature. Your order-creation endpoint is now a public URL that creates orders. Anyone who finds it could POST a fake payment.captured and get free food. Every provider signs its webhooks — an HMAC of the raw body against a shared secret. Verify it against the raw bytes, before any parsing, and reject on mismatch. This is the one place I return 400.

Return 200 for everything you're not going to act on. Wrong event type? 200. Not a payment you care about? 200. Feature flag off? 200. A non-2xx tells the provider "retry me," and a webhook that returns 500 on messages it simply doesn't handle will get itself hammered by the retry policy it triggered. Acknowledge fast; act only on what's yours.

Ship it behind a flag. This changes how money becomes orders — the single most sensitive path in the system. I put the whole webhook flow behind an environment flag, defaulted off, so the existing client flow was untouched until I was ready. Even disabled, the endpoint returns 200 so the provider isn't building a retry backlog while I'm still testing. Flip it on for one outlet, watch, widen.

What actually broke in production

I'd be lying if I framed this as a clean win. Moving the source of truth to the server surfaced a class of bug that the client flow had been hiding: status reconciliation for the paths that don't go through the gateway at all.

Cash and counter/manual orders never produce a payment.captured webhook — there's no online payment to capture. Their transaction rows were being created as pending and then... nothing flipped them to success, because the thing that flips them is the webhook that never fires for cash. For a while, revenue reports undercounted because a slice of perfectly-good cash orders looked unpaid.

The fix was two-part, and it's the honest shape of most production work:

Forward fix — the code that creates a manual/cash order now writes status = 'success' at creation time, since for cash there is no later confirmation step to wait for.
Backfill — a migration to reconcile the rows already stuck in pending, run as preview first (a read-only SELECT of exactly what will change), then the UPDATE inside a transaction I could ROLLBACK. You do not run a bare UPDATE against a money table and hope.

The lesson I keep relearning: when you make one path authoritative, you inherit responsibility for every path that isn't on it. The webhook made online orders bulletproof and immediately made me care about the orders that have no webhook.

What I'd still improve

The client flow is still allowed to write orders. Long-term, I'd rather the client only ever initiates and the server is the sole creator for online payments too — collapsing three idempotency layers back toward one. The layers exist because I'm running both flows during migration; they're a bridge, not the destination.
Reconciliation should be a scheduled job, not a lucky webhook. At-least-once is a floor, not a ceiling — a provider can drop a webhook entirely. A periodic sweep that asks the gateway "what did you capture in the last hour that I have no order for?" turns "we lost an order" from an incident into a self-healing background task. That's the next thing I'm building.

The one idea to take away

If two systems must both commit and no transaction spans them, do not let the unreliable one go second. Put the durable, retrying, server-side actor in charge of the write that matters, stash everything it needs ahead of time, and assume it will be told to do the job more than once. Everything above is just the working-out of that one sentence.

You Probably Don't Need Kafka: A Durable Outbox and Job Queue in Plain Postgres

Poojan — Fri, 03 Jul 2026 00:00:00 +0000

TL;DR — The moment you "save the order, then publish an event / send an email / call a downstream service," you have a dual write : two systems that must both commit with no transaction spanning them. Kafka doesn't fix that — it just gives you a second system to keep in sync. The fix is a transactional outbox : write the job into the same Postgres transaction as your business data, then drain it with a worker that dequeues using SELECT ... FOR UPDATE SKIP LOCKED. You get durable, at-least-once delivery with ordered, concurrent workers — and because delivery is at-least-once, your handlers must be idempotent. For a huge class of apps that is the entire messaging stack, and it's a table you already know how to back up.

The email that sends twice, or never

Here's the feature: when an order is placed, send the customer a confirmation. The obvious code:

await db.orders.insert(order); // 1. commit the order
await email.sendConfirmation(order); // 2. call the email provider

Two writes to two systems, and no transaction wraps both. So it fails in two directions, and both happen in production:

Insert commits, then the process dies (deploy, OOM, the email API times out) → order exists, email never sends. Silent.
Email sends, then the insert rolls back (a constraint trips on commit) → customer gets "your order is confirmed" for an order that doesn't exist. Worse than silent.

This is the same dual-write problem I hit moving order creation behind the payment webhook — two systems that must both commit, coordinated by nothing. And the instinct a lot of teams reach for here is: "add a message broker." Put Kafka (or SQS, or RabbitMQ) between the order and the email, publish an event, let a consumer send the mail.

But look at what that actually does to the failure above:

await db.orders.insert(order); // commit to Postgres
await kafka.publish('order.placed', order); // commit to Kafka — STILL a second system

You didn't remove the dual write. You moved it. Now the two systems that can disagree are Postgres and Kafka, and you've taken on a broker to run, monitor, upgrade, secure, and reason about — to solve a problem it doesn't actually solve. If the process dies between those two lines, the event is lost exactly like the email was.

The outbox: make the message part of the transaction

The insight is small and it's the whole thing: if the message lived in the same database as the business data, one transaction could commit both — atomically. No coordination problem, because there's only one system.

So you don't publish to a broker inside your request. You insert a row into an outbox table, in the same transaction as the order:

CREATE TABLE outbox (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  job_type TEXT NOT NULL, -- 'send_confirmation', 'push_to_pos', ...
  payload JSONB NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending', -- pending | done | dead
  attempts INT NOT NULL DEFAULT 0,
  run_after TIMESTAMPTZ NOT NULL DEFAULT now(), -- for retry backoff
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- The index that makes the dequeue cheap: only ever scan runnable rows.
CREATE INDEX idx_outbox_runnable
  ON outbox (run_after)
  WHERE status = 'pending';


// The request handler. One transaction, two inserts, atomic.
await db.tx(async (t) => {
  const order = await t.orders.insert(orderRow);
  await t.outbox.insert({
    job_type: 'send_confirmation',
    payload: { orderId: order.id, customerId: order.customerId },
  });
});
// If this transaction commits, the job is durably queued.
// If it rolls back, there was no order AND no job. They can never disagree.

That's the trick. The message is now as durable and as consistent as the order itself, because it is the order's transaction. Nothing is published to anyone yet — a separate worker does that, on its own schedule, after the commit is real.

DUAL WRITE — two systems, no shared transaction, they drift
  [request] ──insert order──▶ [Postgres]
             └──publish event──▶ [Kafka] ✗ dies here → event lost

OUTBOX — one transaction owns both, a worker drains later
  [request] ──┐
                ├─(one tx)─▶ [Postgres: orders + outbox]
  order + job ──┘ │
                            [worker] ◀──┘ polls, sends, marks done
                                 │
                                 └──▶ email / POS / downstream

Draining the queue: `FOR UPDATE SKIP LOCKED`

Now the worker. You want several workers running for throughput, and you want them to never grab the same job. The naive "select pending, then update" has the classic race: two workers read the same row and both process it.

Postgres has a purpose-built answer, and it's the same primitive behind the atomic delivery dispatcher I wrote about: SELECT ... FOR UPDATE SKIP LOCKED. It says "lock the rows I'm taking, and skip any row another worker has already locked instead of blocking on it." Concurrent workers glide past each other and each pulls a disjoint batch:

async function drainBatch() {
  await db.tx(async (t) => {
    // Claim up to N runnable jobs; other workers skip these locked rows.
    const jobs = await t.query(`
      SELECT id, job_type, payload, attempts
      FROM outbox
      WHERE status = 'pending' AND run_after <= now()
      ORDER BY id
      FOR UPDATE SKIP LOCKED
      LIMIT 20
    `);

    for (const job of jobs) {
      try {
        await handlers[job.job_type](job.payload); // do the side effect
        await t.outbox.markDone(job.id); // status = 'done'
      } catch (err) {
        await backoffOrDie(t, job, err); // retry with delay, or park it
      }
    }
  });
}

Run drainBatch on an interval (or, better, LISTEN/NOTIFY to wake instantly on new rows and poll as a fallback). Run it in three processes and you have three concurrent, non-overlapping workers with zero extra infrastructure. The ORDER BY id gives you rough FIFO; the LIMIT bounds how much one worker holds at once.

Retry and backoff are just column math — no broker "redelivery" semantics to configure:

async function backoffOrDie(t, job, err) {
  const attempts = job.attempts + 1;
  if (attempts >= 8) {
    await t.outbox.update(job.id, { status: 'dead', attempts }); // dead-letter, alert on it
    return;
  }
  const delaySec = Math.min(2 ** attempts, 3600); // exponential, capped at 1h
  await t.outbox.update(job.id, {
    attempts,
    run_after: sql`now() + ${delaySec} * interval '1 second'`,
  });
}

The catch you must design for: at-least-once

Here's the honest part the "just use Postgres" posts gloss over. This queue is at-least-once , not exactly-once — and no queue, Kafka included, gives you true exactly-once delivery of a side effect. The reason is unavoidable: a worker can send the email, and then die before it writes status = 'done'. The row is still pending, so the next worker sends the email again.

You cannot close that window by being clever with ordering — "mark done, then send" just flips the failure to "marked done, never sent," which is worse. The window is fundamental. So you don't try to eliminate duplicates. You make duplicates harmless by requiring every handler to be idempotent:

Sending email / push: dedupe on a natural key (order_id + 'confirmation') so the second send is a no-op.
Calling a downstream API: pass an idempotency key so the provider collapses retries — the same discipline the payment webhook relies on.
Writing to your own DB: a UNIQUE constraint or a conditional UPDATE ... WHERE status = 'pending' so a replay can't double-apply.

"Assume every job runs more than once, and make that fine" is the price of admission for at-least-once delivery. It's also, not coincidentally, exactly the discipline that makes a system resilient to any retry — client retries, load-balancer retries, a human clicking twice.

The safety net: reconciliation, not just delivery

The outbox guarantees a job is durably recorded. It does not, on its own, guarantee the downstream truth matches yours forever — a handler can succeed on your side and the downstream can still lose it, or a bug can park jobs as dead and no one notices. So the pattern isn't complete without a periodic reconciliation sweep that checks reality against your records:

// A cron that runs every few minutes — the backstop behind the queue.
async function reconcile() {
  // 1. Jobs that have been 'pending' far too long → the worker is wedged. Alert.
  // 2. Anything in 'dead' → surface it; a human or a fixed handler must resolve it.
  // 3. For integrations you can query back: ask the downstream
  // "what do you have that I don't, and vice versa?" and repair the delta.
}

This is the "scheduled job, not a lucky webhook" idea I closed the payment-webhook post promising to build — and it generalizes. At-least-once is a floor, not a ceiling: a message can still be dropped entirely by something outside your control. A reconciliation loop turns "we silently lost one" from an incident your customers report into a background task that self-heals. (I go deeper on this in Reconciliation, Not Sync.)

What actually broke in production

Four things bit me, and they're the four every outbox owner eventually meets:

Poison jobs wedged a worker. One malformed payload threw every time, and because I retried forever, that job's row got picked, failed, and re-queued in a hot loop that starved the batch. The fix is the dead state above: a hard attempt cap that parks the job and alerts, instead of retrying into infinity. A dead-letter you don't monitor is just a slower silence.
done rows piled up and the index bloated. The outbox table grew to millions of done rows and the runnable-rows query slowly got heavier. Two fixes: the partial index (WHERE status = 'pending') so the index only tracks live work regardless of table size, and a nightly job that deletes or archives done rows older than a few days. Treat the outbox as a spool, not a permanent log.
I reached for LIMIT without SKIP LOCKED first and workers serialized — each blocked waiting for the other's locked row instead of skipping it. Throughput collapsed to single-worker under contention. SKIP LOCKED is not optional; it's the entire reason this scales past one worker.
A slow handler held its transaction open too long. Because the claim-and-process ran inside one transaction, a handler that made a 30-second external call held row locks (and a connection) for 30 seconds. For slow side effects, claim the job in a short transaction (flip it to processing with a lease/locked_until timestamp), commit, then do the slow work outside the lock, and mark done in a second short transaction. Keep transactions that hold locks short — a lesson that shows up everywhere, including scaling the database itself under load.

When you actually do need Kafka

I'm not anti-Kafka; I'm anti-premature-Kafka. Reach for a real broker when you genuinely outgrow a table:

Throughput in the hundreds-of-thousands of messages per second, sustained — past what one Postgres instance should be spooling.
Fan-out to many independent consumer groups that each need their own replayable offset into a durable log.
Event sourcing / stream processing where the log itself is the source of truth and you replay history to rebuild state.
Cross-team decoupling where dozens of services subscribe and you want the broker as the contract boundary.

If you're sending a confirmation email, pushing an order to a POS, or kicking off a report — none of those are on that list. You have one app and one database. The outbox is less code, one fewer system at 3 a.m., and it's backed up by the same pg_dump you already run.

The one idea to take away

The dual-write problem doesn't get solved by adding a second system — it gets solved by making the message part of a transaction you already commit. Write the job into Postgres with your data, drain it with FOR UPDATE SKIP LOCKED, make every handler idempotent because delivery is at-least-once, and put a reconciliation sweep behind it. That's a durable, concurrent, retrying job queue in about forty lines and one table — and for most products, it's the whole messaging stack you were about to go operate a broker for.

Scaling PostgreSQL in Production: Indexing, RPC Functions, Views, and Query Optimization

Poojan — Fri, 05 Jun 2026 00:00:00 +0000

As applications grow, database performance becomes one of the biggest factors affecting user experience. Features can be implemented quickly, but poorly optimized queries, inefficient data access patterns, and increasing data volume eventually create bottlenecks.

Recently, I worked on optimizing a multi-tenant PostgreSQL system built on Supabase. The system consisted of:

99 database tables
Nearly 1 million records in the largest table
More than 120,000 database requests per day
Complex reporting and dashboard requirements
Transactional workloads with strict consistency requirements

While the application continued to function correctly, several reporting and aggregation queries had started taking multiple seconds to execute. As the dataset grew, it became clear that improvements were needed to keep the system responsive and maintainable.

This article covers the techniques that produced the biggest impact.

Understanding the Bottlenecks

The application served multiple tenants from a shared database. Most operations were tenant-scoped and involved data spread across several related tables.

The primary performance challenges were:

Slow reporting queries
Repeated aggregation logic
Increasing dashboard generation time
Large result sets being fetched unnecessarily
Excessive application-side processing
Growing query complexity

Rather than attempting a complete redesign, the focus was on improving the existing architecture through targeted database optimizations.

Composite Indexes Instead of More Indexes

One of the first discoveries was that several frequently executed queries were filtering on multiple columns simultaneously.

A common mistake is creating separate indexes for every column and expecting PostgreSQL to combine them efficiently via index merging. In practice, composite indexes often provide significantly better results when they match actual query patterns because they allow Postgres to perform a single index scan to satisfy multiple filters.

For example, instead of indexing columns independently:

-- ❌ Suboptimal: Individual index on each column
CREATE INDEX idx_events_tenant_id ON events (tenant_id);
CREATE INDEX idx_events_user_id ON events (user_id);
CREATE INDEX idx_events_event_date ON events (event_date);

we created a single composite index tailored to our access patterns:

-- ✅ Optimal: Single composite index matching query filters
CREATE INDEX idx_events_tenant_user_date ON events (tenant_id, user_id, event_date);

Similarly, transaction-related queries benefited from composite indexes structured like:

CREATE INDEX idx_transactions_composite 
ON transactions (tenant_id, transaction_type, created_at, status_id);

and scheduling-related operations used patterns such as:

CREATE INDEX idx_schedules_composite 
ON schedules (tenant_id, category_id, week_number, day_of_week);

The most important lesson was that index order matters.

The leading columns in the index should closely match how the query filters data. Since most queries were tenant-scoped, the tenant identifier typically appeared first. If a query filters on tenant_id and user_id but not event_date, the composite index is still utilized. However, a query filtering only on event_date would not benefit from this index.

After reviewing query patterns and introducing composite indexes where appropriate, reporting workloads showed noticeable improvements and several slow queries were eliminated entirely.

Moving Business Logic into Supabase RPC Functions

Initially, many operations required multiple database round trips.

The application would:

Fetch data
Perform calculations
Execute additional queries
Aggregate results
Return the final response

This approach worked well during the early stages of development but became increasingly expensive as datasets grew and the latency of consecutive queries compounded.

To reduce overhead, complex operations were moved into Supabase RPC functions (PostgreSQL PL/pgSQL database functions).

Instead of multiple client-server interactions:

Application
 ├─ Query A (Fetch tenant orders)
 ├─ Query B (Fetch menu metrics)
 ├─ Query C (Insert audit log)
 └─ Processing (Aggregate in Node.js)

the flow became:

Application
 └─ RPC Function (get_tenant_dashboard_metrics)
      ├─ Validation
      ├─ Aggregation
      ├─ Calculations
      └─ Result

Here is a simplified example of how we defined the database function in Postgres:

CREATE OR REPLACE FUNCTION get_tenant_summary(p_tenant_id UUID)
RETURNS TABLE (
  total_revenue NUMERIC,
  completed_orders BIGINT
) LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
  RETURN QUERY
  SELECT 
    COALESCE(SUM(amount), 0) AS total_revenue,
    COUNT(id) FILTER (WHERE status = 'completed') AS completed_orders
  FROM transactions
  WHERE tenant_id = p_tenant_id;
END;
$$;

We then call this function in a single, fast client-side request:

const { data, error } = await supabase
  .rpc('get_tenant_summary', { p_tenant_id: tenantId });

This reduced network round trips, centralized business logic, and improved execution speed. The biggest performance gains were observed in reporting-related workloads where response times dropped from several seconds to millisecond-level execution.

Simplifying Data Access with Database Views

As the application evolved, certain joins and aggregations began appearing repeatedly throughout the codebase. The same datasets were being reconstructed in multiple places.

To solve this, database views were introduced.

For example, to build a consolidated daily activity summary, we defined a Postgres View:

CREATE OR REPLACE VIEW daily_activity_summary AS
SELECT 
  tenant_id,
  DATE_TRUNC('day', created_at) AS activity_date,
  COUNT(id) AS total_events,
  COUNT(DISTINCT user_id) AS active_users
FROM events
GROUP BY tenant_id, DATE_TRUNC('day', created_at);

Instead of repeatedly joining numerous tables across different services, the application can now query the view directly, using client-side filters:

const { data, error } = await supabase
  .from('daily_activity_summary')
  .select('activity_date, total_events, active_users')
  .eq('tenant_id', tenantId)
  .order('activity_date', { ascending: false });

Benefits included:

Reduced query duplication
Cleaner application code
Easier reporting development
Improved maintainability
Faster onboarding for developers

Views effectively became reusable building blocks for reporting and dashboard generation.

Eliminating Unnecessary Data Retrieval

During optimization, several endpoints were found to be retrieving significantly more data than required.

Avoiding `SELECT *`

Instead of:

SELECT *
FROM transactions;

queries were modified to fetch only the required columns:

SELECT
  id,
  status,
  created_at
FROM transactions;

While simple, this optimization reduced network transfer and improved query efficiency across multiple endpoints.

Introducing Pagination

Some endpoints attempted to return entire datasets at once. As records increased, response sizes grew unnecessarily.

Pagination was introduced across reporting and administrative interfaces to ensure only the required records were fetched.

Instead of returning thousands of rows:

SELECT *
FROM transactions;

queries were limited appropriately:

SELECT *
FROM transactions
LIMIT 50 OFFSET 0;

This significantly reduced response sizes and improved perceived application performance.

Removing Unnecessary Sorting

Several queries performed sorting operations (ORDER BY) that were not actually required by the business logic or layout. Removing unnecessary sorting clauses reduced CPU execution time and lowered database workload, especially on larger datasets.

Optimizing Aggregations

Reporting systems often become slow because aggregation logic is repeatedly recalculated.

By simplifying query logic, moving heavy aggregations to periodic background rollups (or materialized views), and querying via optimized RPC functions, we reduced database complexity and significantly improved dashboard loading speeds.

Adopting UUIDv7 for New Tables

As the system evolved, UUIDv7 was adopted for newly created tables.

Traditional UUIDv4 values are completely random. While they provide excellent uniqueness guarantees, they introduce index fragmentation in B-Tree structures. When a database inserts random values, it forces the B-Tree index to split pages in random locations to fit the keys, lowering write speeds and caching efficiency.

UUIDv7 solves this by introducing millisecond-level time ordering while maintaining globally unique identifiers.

-- Conceptual structure of UUIDv7:
-- [48-bit Timestamp] [4-bit Version] [12-bit Rand/Seq] [2-bit Variant] [62-bit Random]

Benefits include:

Better insertion locality: Since keys are sequential, Postgres inserts new rows at the end of the B-Tree index structure, minimizing page splits.
More predictable index growth: Better utilization of memory-mapped buffer cache.
Improved long-term scalability: Higher write throughput on index pages.
Retained uniqueness guarantees: No risk of collisions across tenants.

To implement this on new tables, we define the column default using a generator:

CREATE TABLE new_tenant_log (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
  tenant_id UUID NOT NULL,
  log_message TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

The migration strategy was intentionally conservative: existing tables remained unchanged while newly created tables adopted UUIDv7 moving forward. Since no formal benchmarking was performed on the UUID transition, no specific performance improvements are claimed. However, UUIDv7 aligns well with modern PostgreSQL best practices for large-scale systems.

Key Lessons Learned

Several lessons became clear throughout the optimization process.

1. Indexes Are Not a Magic Solution

Adding indexes blindly often increases write overhead while providing little benefit. Indexes should be designed around real query patterns.

2. Composite Indexes Usually Matter More Than Individual Indexes

Understanding how queries filter data is more important than increasing the total number of indexes.

3. Database-Side Processing Can Be Extremely Effective

For aggregation-heavy workloads, RPC functions reduced round trips and simplified application logic.

4. Views Improve Maintainability

Complex reporting logic becomes significantly easier to manage when reusable views are introduced.

5. Pagination Should Be Introduced Early

Large datasets eventually become expensive. Pagination prevents future performance problems.

6. Small Optimizations Compound

Removing unnecessary sorting, reducing selected columns, optimizing aggregations, and improving indexes may seem minor individually. Together, they can transform the performance characteristics of a system.

Final Thoughts

Database optimization is rarely about finding a single breakthrough improvement. In this case, the largest gains came from systematically addressing multiple bottlenecks:

Composite indexing
RPC-based processing
Database views
Query optimization
Aggregation improvements
Pagination strategies
UUIDv7 adoption

The result was a more maintainable system, faster reporting workflows, and significantly improved responsiveness under production workloads. Most importantly, these improvements were achieved without redesigning the entire architecture. Instead, they came from understanding how the database was being used and optimizing the paths that mattered most.

Building an Atomic Delivery Dispatcher with PostgreSQL Row-Level Locking

Poojan — Tue, 02 Jun 2026 00:00:00 +0000

Building a logistics and last-mile delivery dispatcher involves more than calculating routes or sending SMS updates. In the real world, you are managing scarce physical resources (riders) operating under strict constraints (weight/capacity limits) in real-time.

When multiple orders enter the system at peak hours, a logistics backend must guarantee that:

Riders are never assigned orders exceeding their physical carrying capacity.
A rider is never double-booked onto multiple concurrent delivery batches.
Orders move through a strict, deterministic state progression.

Below is an engineering walkthrough on how we built a concurrency-safe delivery dispatch engine in TypeScript using PostgreSQL row-level locking.

1. Concurrency Control: Locking with `SELECT ... FOR UPDATE`

When assigning a batch of orders to a rider, the system must check the rider's active load to ensure the additions do not exceed their maximum capacity limit.

In a standard Node.js environment, querying the database to calculate current load, checking the limits in application memory, and then issuing an UPDATE leaves a gap for race conditions. If two different order managers attempt to assign batches to the same rider simultaneously, both operations might read the rider's load as "available" before either write completes, resulting in over-allocation.

To solve this, we use PostgreSQL row-level locking (SELECT ... FOR UPDATE) inside an explicit transaction block.

import { PoolClient } from 'pg';

interface RiderAssignmentResult {
  success: boolean;
  message: string;
}

async function assignRiderToBatch(
  client: PoolClient,
  batchId: string,
  riderId: string
): Promise<RiderAssignmentResult> {
  try {
    // Start transaction boundary
    await client.query('BEGIN');

    // 1. Lock the rider row for modification
    const riderRes = await client.query(
      `SELECT rider_id, availability_status, max_capacity, type 
       FROM delivery_riders 
       WHERE rider_id = $1 
       FOR UPDATE`,
      [riderId]
    );

    if (riderRes.rowCount === 0) {
      throw new Error('RIDER_NOT_FOUND');
    }

    const rider = riderRes.rows[0];

    if (rider.availability_status !== 'AVAILABLE') {
      throw new Error('RIDER_UNAVAILABLE');
    }

    // 2. Fetch order count in the proposed batch
    const batchRes = await client.query(
      `SELECT count(*) as order_count, status FROM delivery_batches WHERE batch_id = $1`,
      [batchId]
    );

    const batch = batchRes.rows[0];
    const incomingOrderCount = parseInt(batch.order_count, 10);

    if (batch.status !== 'CREATED') {
      throw new Error('INVALID_BATCH_STATUS');
    }

    // 3. Calculate rider's current active order load
    const activeLoadRes = await client.query(
      `SELECT COALESCE(SUM(order_count), 0) as current_load 
       FROM delivery_batches 
       WHERE assigned_rider_id = $1 AND status IN ('ASSIGNED', 'PICKED', 'OUT_FOR_DELIVERY')`,
      [riderId]
    );

    const currentLoad = parseInt(activeLoadRes.rows[0].current_load, 10);

    // 4. Validate capacity constraints
    if (currentLoad + incomingOrderCount > rider.max_capacity) {
      throw new Error('CAPACITY_EXCEEDED');
    }

    // 5. Update batch and assign rider
    await client.query(
      `UPDATE delivery_batches 
       SET assigned_rider_id = $1, status = 'ASSIGNED', assigned_at = NOW() 
       WHERE batch_id = $2`,
      [riderId, batchId]
    );

    // 6. Log assignment audit log
    await client.query(
      `INSERT INTO rider_assignment_history (batch_id, new_rider_id, reason) 
       VALUES ($1, $2, $3)`,
      [batchId, riderId, 'System Auto-Dispatch']
    );

    await client.query('COMMIT');
    return { success: true, message: 'Rider assigned successfully' };

  } catch (error: any) {
    await client.query('ROLLBACK');
    return { success: false, message: error.message || 'Transaction failed' };
  }
}

The Locking Mechanics

When the transaction runs SELECT ... FOR UPDATE on the target rider, Postgres places an exclusive write lock on that specific row. Any other concurrent transaction attempting to read or update that same rider row is forced to queue until the first transaction calls COMMIT or ROLLBACK. This guarantees that capacity limits are computed against deterministic, sequential state.

2. Enforcing State Transitions via State Machines

A delivery batch must follow a strict path to prevent "orphaned" orders or invalid logic (like marked delivered before they are picked up).

The lifecycle follows this state map:

    [CREATED]
        │
        ▼ (Rider Assigned)
   [ASSIGNED]
        │
        ▼ (Rider Picked Up Order)
    [PICKED]
        │
        ▼ (Transit Started)
[OUT_FOR_DELIVERY]
   ┌────┴────────────────────────┐
   ▼ (Successful Delivery) ▼ (Delivery Failed)
[COMPLETED] [FAILED]

To enforce this, we build a transition map in TypeScript that acts as our guardrail:

type BatchStatus = 'CREATED' | 'ASSIGNED' | 'PICKED' | 'OUT_FOR_DELIVERY' | 'COMPLETED' | 'FAILED' | 'CANCELLED';

const VALID_TRANSITIONS: Record<BatchStatus, BatchStatus[]> = {
  CREATED: ['ASSIGNED', 'CANCELLED'],
  ASSIGNED: ['PICKED', 'CREATED', 'CANCELLED'], // Can reassign back to CREATED
  PICKED: ['OUT_FOR_DELIVERY', 'FAILED'],
  OUT_FOR_DELIVERY: ['COMPLETED', 'FAILED'],
  COMPLETED: [], // Terminal State
  FAILED: [], // Terminal State
  CANCELLED: [] // Terminal State
};

function validateStateTransition(current: BatchStatus, next: BatchStatus): boolean {
  const allowed = VALID_TRANSITIONS[current];
  return allowed ? allowed.includes(next) : false;
}

By verifying transitions before executing any database changes, the engine blocks invalid states at the API boundaries. For example, if a client tries to patch the status of a CREATED batch to OUT_FOR_DELIVERY, the application throws a 400 Bad Request error immediately.

3. Storing Rich Tracking Data with JSONB Proof of Delivery

When a driver completes a delivery, the app captures rich proof of delivery (PoD): photo URLs (uploaded to S3), coordinate telemetry, customer signatures, notes, and timestamps.

Instead of writing a rigid relational schema with child tables for tracking metadata, we opted for a Postgres JSONB column on our batch order mapping table.

CREATE TABLE delivery_batch_orders (
    batch_order_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    batch_id UUID REFERENCES delivery_batches(batch_id),
    order_id UUID REFERENCES orders(order_id),
    sequence INTEGER NOT NULL,
    status VARCHAR(50) DEFAULT 'PENDING',

    -- JSONB document holding unstructured proof of delivery
    proof_of_delivery JSONB NULL,

    completed_at TIMESTAMP WITH TIME ZONE
);

Why JSONB instead of structured relational columns?

Dynamic Metadata: Different delivery types require different proofs. A corporate drop-off might require a text note ("Left with receptionist"), while a residential drop-off requires an S3 photo link and GPS bounds. JSONB handles this schema variance without migrations.
Reduced Join Overhead: When fetching delivery details for the administration portal, the application reads a single row without executing expensive relational joins to retrieve metadata.
Indexing Support: Postgres allows indexing JSONB fields using GIN (Generalized Inverted Index) keys. If we need to search for deliveries verified within specific coordinates, we can index the JSONB path directly:

CREATE INDEX idx_pod_coordinates ON delivery_batch_orders USING GIN ((proof_of_delivery->'coordinates'));

4. Real-Time Telemetry Updates

Delivery is highly interactive. Customers, outlet merchants, and platform dispatchers need to see location changes and status updates instantly.

To achieve this, we link our database transactions directly with a real-time event broker (Socket.IO):

import { Server } from 'socket.io';

interface DeliveryEventPayload {
  batchId: string;
  status: BatchStatus;
  outletId: string;
  riderId?: string;
  updatedAt: string;
}

class DeliveryNotificationService {
  private io: Server;

  constructor(io: Server) {
    this.io = io;
  }

  public broadcastBatchStatus(payload: DeliveryEventPayload): void {
    // 1. Notify the merchant outlet dashboard
    this.io.to(`outlet:${payload.outletId}`).emit('delivery_batch_updated', payload);

    // 2. Notify the specific rider's device
    if (payload.riderId) {
      this.io.to(`rider:${payload.riderId}`).emit('my_batch_updated', payload);
    }

    // 3. Notify the customer room tracking this order
    this.io.to(`batch:${payload.batchId}`).emit('tracking_updated', {
      status: payload.status,
      timestamp: payload.updatedAt
    });
  }
}

This pub/sub routing model ensures that the instant database row updates commit successfully, the web socket layer broadcasts the changes to the respective clients in sub-100ms.

Summary of Dispatcher Core Patterns

Serialize Resource Allocation: Use row locks (FOR UPDATE) inside isolated database transactions to prevent capacity overloading.
Defensive State Logic: Define a clear, immutable transitions map to enforce linear lifecycle paths on orders.
Leverage Document Fields for Audits: Use JSONB for storing flexible metadata profiles, keeping reads fast and schema migrations minimal.
Link DB Events to Websockets: Keep clients synchronized by coupling transactional success hooks directly to your real-time notification brokers.

Designing a Multi-Tenant Subsidy Engine with Concurrency-Safe Budget Tracking

Poojan — Tue, 02 Jun 2026 00:00:00 +0000

Expanding a single-tenant business application to support B2B corporate models brings massive complexity to your data access layer. You are no longer just handling independent user checkouts; you are now enforcing complex corporate compliance rules, isolating merchant visibility, managing shared program budgets, and tracking subsidy ledgers.

As a backend engineer, I recently architected a B2B corporate allowance engine. Below is a deep dive into the core engineering decisions, database design, conflict resolution algorithms, and concurrency patterns required to build a high-integrity multi-tenant corporate wallet.

1. Multi-Tenant Relational Isolation

In a multi-tenant B2B platform, security starts at the database schema. Corporate organizations contract with the platform to allow their employees to buy meals from a selected pool of merchants.

To enforce this, we designed a two-tiered outlet mapping structure:

Platform Level: The platform admin assigns a master pool of merchants to the organization.
Office Level: The corporate admin maps specific merchants from that pool to individual physical offices.

This is enforced at the database level using composite foreign keys:

-- 1. Master Pool assigned to the organization
CREATE TABLE corporate_org_outlets (
    organization_id UUID NOT NULL,
    outlet_id UUID NOT NULL,
    assigned_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (organization_id, outlet_id)
);

-- 2. Office level assignment mapping to specific offices
CREATE TABLE corporate_office_outlets (
    office_outlet_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    office_id UUID NOT NULL,
    organization_id UUID NOT NULL,
    outlet_id UUID NOT NULL,
    is_visible_in_app BOOLEAN DEFAULT TRUE,

    -- Enforce that the office outlet MUST exist in the organization's master pool
    FOREIGN KEY (organization_id, outlet_id) 
        REFERENCES corporate_org_outlets (organization_id, outlet_id)
        ON DELETE CASCADE
);

The Architectural Benefit

By referencing a composite key (organization_id, outlet_id) rather than just a single outlet_id, PostgreSQL prevents a corporate admin from accidentally (or maliciously) displaying an unauthorized merchant to their employees. Data integrity is guaranteed at the database engine level, minimizing reliance on application-layer checks.

2. Resolving Subsidy Conflicts: The "Maximum Rule"

When an employee checks out, multiple subsidy programs might apply to their order. For example:

A Corporate Allowance (e.g., 50% subsidy up to $10, eligible for the entire office).
A Merchant-Specific Subsidy (e.g., a flat $5 discount sponsored for a specific team).

Allowing these programs to stack cumulatively (e.g., applying both) leads to extreme financial leakage where orders can become free or negative-cost.

To solve this, we implemented the Maximum Rule : evaluate all eligible programs, calculate the absolute discount for each independently, and apply only the program that grants the user the highest discount.

Here is a simplified version of the logic executing in the order validation lifecycle:

interface SubsidyProgram {
  programId: string;
  type: 'PERCENTAGE' | 'FIXED' | 'PERCENTAGE_CAPPED';
  value: number;
  maxCap?: number;
}

function calculateDiscount(orderAmount: number, program: SubsidyProgram): number {
  switch (program.type) {
    case 'PERCENTAGE':
      return orderAmount * (program.value / 100);

    case 'PERCENTAGE_CAPPED':
      const percentageDiscount = orderAmount * (program.value / 100);
      return Math.min(percentageDiscount, program.maxCap || 0);

    case 'FIXED':
      return Math.min(program.value, orderAmount);

    default:
      return 0;
  }
}

function getBestEligibleSubsidy(
  orderAmount: number, 
  eligiblePrograms: SubsidyProgram[]
): { programId: string; discountAmount: number } | null {
  if (eligiblePrograms.length === 0) return null;

  return eligiblePrograms.reduce((best, current) => {
    const currentDiscount = calculateDiscount(orderAmount, current);

    if (!best || currentDiscount > best.discountAmount) {
      return { programId: current.programId, discountAmount: currentDiscount };
    }
    return best;
  }, null as { programId: string; discountAmount: number } | null);
}

Why Reduce?

The Array.prototype.reduce pattern executes in linear O(N) time complexity. Because the list of eligible programs for a single employee is typically small (under 10), this keeps calculation overhead virtually non-existent while guaranteeing deterministic, leak-proof checkouts.

3. Concurrency-Safe Budget Exhaustion

In corporate subsidy programs, organizations set strict limits (e.g., a total monthly budget of $5,000). Once the budget is exhausted, the program must immediately disable itself, and employees must pay full price.

In a high-concurrency environment where dozens of users check out at the exact same second, a standard Select-then-Update application-layer loop is highly vulnerable to race conditions:

1. User A Reads: budget_used = 4995. Limit = 5000. (Eligible!)
2. User B Reads: budget_used = 4995. Limit = 5000. (Eligible!)
3. User A Updates: budget_used = 4995 + 10 = 5005. (Overdraft!)
4. User B Updates: budget_used = 5005 + 10 = 5015. (Double Overdraft!)

The Solution: Database-Level Atomic Updates

Instead of validating budget state inside Node.js, we offloaded the validation to an atomic transaction block with conditional update locks in PostgreSQL.

We write a single, atomic SQL statement that increments the budget and checks the boundary condition in one step:

-- Atomic Check-and-Apply Query
UPDATE corporate_subsidies 
SET budget_used = budget_used + $1
WHERE subsidy_id = $2 
  AND is_active = TRUE
  AND (budget_used + $1) <= total_budget
RETURNING subsidy_id, budget_used;

How the Application Handles It

If the update affects exactly 0 rows, the database tells our application that the budget limit has been reached or the program is inactive. Node.js can catch this state and fail the transaction gracefully, prompting the user that the subsidy is no longer available:

async function applySubsidyTransaction(client, subsidyId, discountAmount) {
  const query = `
    UPDATE corporate_subsidies 
    SET budget_used = budget_used + $1
    WHERE subsidy_id = $2 
      AND is_active = TRUE
      AND (budget_used + $1) <= total_budget
    RETURNING subsidy_id, budget_used;
  `;

  const result = await client.query(query, [discountAmount, subsidyId]);

  if (result.rowCount === 0) {
    throw new Error('SUBSIDY_BUDGET_EXHAUSTED');
  }

  return result.rows[0];
}

By leveraging PostgreSQL's row locks on update execution, database transactions are serialized cleanly. We completely eliminate race conditions and budget overruns without introducing the latency or complexity of distributed lock managers like Redis Redlock.

4. Audit Trail Integrity: The Subsidy Ledger

Subsidies are financial transactions. In corporate systems, admins can edit or delete subsidy programs at any time. If you only store references to program IDs, generating historical GST reports, settlement invoices, or user ledgers will yield incorrect values if program parameters have changed.

To solve this, we built a Subsidy Ledger containing full denormalized snapshots:

CREATE TABLE corporate_subsidy_ledger (
    ledger_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL REFERENCES orders(order_id),
    employee_id UUID NOT NULL,
    subsidy_program_id UUID NOT NULL,
    applied_amount NUMERIC(10, 2) NOT NULL,

    -- Snapshot data for historical reporting
    program_snapshot JSONB NOT NULL, 
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Whenever an order is processed:

We calculate the discount using the active program state.
We record the calculation details and save a stringified snapshot of the parameters (the percentage, cap, limits, and type) directly inside the JSONB column.
This creates a permanent, immutable ledger entry. Even if an admin updates the program's parameters or deletes the program entirely next month, historical reports will remain 100% accurate and audit-safe.

Summary of Architectural Best Practices

When building financial or B2B entitlement layers:

Push Integrity to the Database: Enforce relational pools via composite foreign keys and composite constraints.
De-stack Early: Implement a deterministic resolution algorithm like the Maximum Rule to avoid cumulative discounts.
Enforce Atomic Bounds: Never validate business thresholds in application code. Let SQL updates handle calculations and assertions simultaneously.
Log Immutable State: Store point-in-time configuration snapshots alongside transactional tables to maintain audit compliance.

How I Cut AWS Costs by 60% by Fixing Infrastructure Inefficiencies

Poojan — Sun, 01 Mar 2026 00:00:00 +0000

When I became the sole backend and infra owner at MealPe, the AWS bill was higher than it needed to be. Not because of over-provisioning - but because of accumulated inefficiencies nobody had cleaned up.

I didn't redesign the entire system. Instead, I analyzed the bill, audited the runtime patterns, and fixed what was actually broken. Here's exactly how we accomplished a 60% reduction in monthly cloud spend while actually improving LCP and request latencies.

The Audit: Where Was the Money Going?

Before making any changes, I analyzed our AWS Billing console line-by-line. The monthly distribution revealed that compute overhead and log retention were dramatically out of proportion with our active user base:

Service Category	Monthly Cost (Before)	Monthly Cost (After)	% Reduction	Primary Culprit
EC2 Compute	$620	$280	54%	Blocked event loop, static serving CPU overhead
S3 Storage & Transfer	$380	$90	76%	Indefinite log storage, massive image uploads
CloudWatch / I/O	$250	$110	56%	Verbose application request/response dumps
Total	$1,250	$480	61%	Overall Savings: $770/month

1. Fixing Excessive Logging Inefficiencies

Our application log files were growing at an unsustainable rate. The Node.js application was generating verbose logs for every inbound request—including full request and response bodies, heavy SQL trace logs, and absolute stack dumps on trivial input errors.

// ❌ Deprecated verbose pattern that flooded CloudWatch and S3
app.use((req, res, next) => {
  logger.info({
    url: req.url,
    headers: req.headers,
    body: req.body, // Large raw JSON buffers
    timestamp: new Date()
  });
  next();
});

These logs were being written to disk and continuously shipped to Amazon S3.

The Fix

I audited our log configuration and implemented Pino for structured JSON logging. I restricted verbose request/response logging only to non-production environments and defined clear log levels for our production cluster:

DEBUG/INFO: Used only for crucial system boot details and structured API transaction summaries (method, path, status, duration).
WARN/ERROR: Reserved for true exception states, database connection alerts, and 500-level codes.

This change alone decreased log volume from ~15GB/day to under 800MB/day , dramatically dropping both write I/O costs and file shipping overhead.

2. Setting Up Automated S3 Lifecycle Policies

S3 storage is inexpensive until files accumulate over months without rules. We were storing every daily server log, PDF bill copy, and redundant image backup indefinitely. There was no cleanup process in place.

The Fix

I configured strict S3 Lifecycle Rules on our logging and static resource buckets:

Daily Server Logs: Transition to S3 Standard-IA (Infrequent Access) after 14 days, move to S3 Glacier Flexible Retrieval after 30 days, and permanently expire after 90 days.
Temporary File Uploads: Automatically expire from the /tmp/ bucket prefix after 7 days.

Implementing these automated lifecycle states trimmed down our total active S3 storage footprint by over 70% in the first billing cycle.

3. Offloading Static Asset Serving from Node.js to NGINX

This was the most impactful architectural refinement. The Node.js backend process was serving static frontend assets directly. Whenever a client loaded our web app, the request for a Javascript chunk, CSS bundle, SVG logo, or favicon went straight through our Express application.

Because Node.js is single-threaded, serving static file assets blocks the event loop from doing actual API computations, leading to high CPU usage and queuing lag.

The NGINX Solution

I configured NGINX as a reverse proxy in front of Node.js. NGINX is built from the ground up to serve static files with extremely low memory and CPU overhead. By handling static requests at the NGINX layer, we bypass the Node.js event loop completely:

                         ┌──────────────────────┐
   Web Client ───────▶ │ NGINX Reverse Proxy │
                         └──────────┬───────────┘
                    /static/* │ /api/*
              (CSS, JS, images) │ (REST endpoints)
                    ┌───────────────┴───────────────┐
                    ▼ ▼
        ┌─────────────────────┐ ┌────────────────────────┐
        │ Local Static Files │ │ Node.js / PM2 Cluster │
        └─────────────────────┘ └────────────┬───────────┘
                                                      ▼
                                          ┌────────────────────────┐
                                          │ PostgreSQL Database │
                                          └────────────────────────┘

Configured NGINX Routing Block

# NGINX config: serve static files directly, proxy only API requests
server {
    listen 443 ssl http2;
    server_name app.mealpe.in;

    # Static assets served directly by NGINX - never blocks Node.js loop
    location /static/ {
        root /var/www/mealpe-frontend;
        expires 30d;
        add_header Cache-Control "public, no-transform, immutable";
        access_log off; # Turn off access logging for static assets to reduce disk I/O
    }

    # API requests proxied to the PM2 Node.js server cluster
    location /api/ {
        proxy_pass http://127.0.0.1:3000;
        proxy_http_version 1.1;
        proxy_set_header Connection "";
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
    }
}

Offloading this work to NGINX decreased our average EC2 instance memory consumption by 35% and CPU usage by 40%. This allowed us to safely downsize our EC2 compute tier, reducing our monthly server cost significantly.

The Verdict

Optimizing infrastructure isn't always about moving to serverless or re-architecting your entire system. Usually, the highest return on investment comes from cleaning up operational inefficiencies:

Logging: Keep production logs actionable, structured, and short.
Storage: Enforce S3 lifecycle rules from day one.
Separation of Concerns: Let NGINX serve your files, and let Node.js compute your API logic.

Scaling a Food-Tech SaaS from 1K to 20K Users

Poojan — Thu, 01 Jan 2026 00:00:00 +0000

When I took over the backend at MealPe, the platform had around 1,000 registered users across a handful of client sites. A year later, it's at 20,000+ users processing 50,000 meals per month. This is what that scaling journey actually looked like - not the clean version, but the real one.

The Early Cracks

At 1,000 users, most things "worked." But as we onboarded more institutional clients, cracks started showing.

API response times crept up. Database queries that ran fine with 10,000 rows started timing out at 200,000. Concurrent meal orders during our intense lunch rush (11:30 AM – 1:00 PM) caused request queuing and database locks that slowed the entire site to a crawl.

1. Database-First Optimization

Almost every scaling problem traced back to the database. When I audited the system, I focused on three immediate interventions:

Indexing Strategy Overhaul: I audited every query using EXPLAIN ANALYZE and added composite indexes for the most common access patterns (such as combining venue_id with order_status and delivery_time). This alone dropped average query time from 800ms to under 50ms for the order listing endpoints.
Schema Normalization: Some tables had been designed with flexibility in mind (utilizing JSON columns for dynamically defined fields). I migrated these to proper relational columns with appropriate types and foreign key constraints, which dramatically cut down CPU evaluation costs in query filters.
Connection Pooling: We were creating new database connections per request, creating heavy TCP handshake overhead. Implementing proper connection pooling using pg-pool reduced connection overhead and kept connection saturation safely below thresholds.

2. API Architecture Refinement

The original API had grown organically - lots of endpoints doing too many things. I refactored our core router with a focus on:

Pagination Everywhere: Enforced strict query pagination using limit/offset and cursors so that list views never try to render thousands of rows in a single DOM draw.
Response Shaping: Different clients needed different data structures. Instead of sending full database models, I implemented lightweight view DTOs to shrink our network payload size.
Caching Hot Data: Venue menus change once a week, not once a second. Adding a cache layer in front of dynamic menus eliminated thousands of database reads per hour.

3. The Lunch Rush Problem

Institutional cafeterias have extremely predictable and highly congested usage patterns. Unlike standard food delivery, 80% of all meal orders come in during a tightly bounded 90-minute lunch window. This meant our servers needed to handle peak loads that were 15x the daily average.

To survive these spikes without scaling our cloud costs infinitely:

Decoupling Non-Critical Work: I offloaded tasks like push notifications, admin email alerts, and analytic log compilations into a background queue processed after peak hours.
Pre-Computing Availability: Instead of scanning ordering records to determine inventory limits on every checkout, we pre-computed meal item quotas every hour, converting an O(N) scan into an O(1) cache read.

Monitoring That Actually Matters

I set up monitoring dashboards that tracked metrics predictive of failures rather than just CPU alerts:

API p95 Latency: Warns us when p95 responses exceed 200ms.
Connection Pool Saturation: Alerts us when available database pools drop below 15%.
Job Queue Depth: Helps us spot background processing lag.

What I'd Do Differently Next Time

Plan the Indexing Early: Invest in database indexes from day one rather than retrofitting them under pressure.
Bake in Caching First: Integrate cache and pagination middlewares into the router framework rather than adding them as custom interventions.
Realistic Load Testing: Build synthetic load tests modeling the concentrated lunch rush early in staging.

Node.js Performance Optimization: From Slow APIs to Scalable Systems

Poojan — Mon, 01 Dec 2025 00:00:00 +0000

Node.js is incredibly fast out of the box because of its non-blocking I/O model. However, as your transaction volume grows, minor engineering shortcuts turn into devastating performance bottlenecks.

At MealPe, as the sole backend engineer, I faced the challenge of optimizing our backend to scale from a few dozen concurrent transactions to thousands. Here is a practical, evidence-based guide to identifying Node.js performance bottlenecks and resolving them using production-proven patterns.

Performance Benchmarks: The Impact of Optimization

To demonstrate the concrete impact of these optimizations, I ran load tests using Autocannon on a single t3.medium EC2 instance. The test simulated 100 concurrent users making continuous request rounds to a dynamic database endpoint over 10 seconds:

Stage of Optimization	Avg Latency (p95)	Throughput (Req/Sec)	CPU Utilization	Error Rate
Baseline (Single thread, no cache)	420 ms	240 req/sec	95% (bottleneck)	4.2%
Step 1: PM2 Clustered (4 Workers)	110 ms	880 req/sec	42% (distributed)	0.0%
Step 2: Redis Caching Layer Active	18 ms	4,200 req/sec	15% (idle compute)	0.0%

1. Keep the Event Loop Clear (Non-Blocking IO)

Node.js executes JavaScript code in a single-threaded loop. If you block that thread with CPU-intensive tasks—such as image resizing, parsing massive JSON files, or executing synchronous cryptos—every other user request is forced to wait in a queue.

// ❌ Deprecated synchronous blocking pattern
app.post('/api/report', (req, res) => {
  const data = fs.readFileSync('./large-report.csv', 'utf8'); // Blocks event loop!
  const processed = heavySyncParsing(data); // Blocks event loop!
  res.json(processed);
});

The Fix

Always use asynchronous, non-blocking file and network operations. If you absolutely must perform CPU-bound tasks, offload them to a separate thread using Worker Threads or queue them in a background job system like BullMQ :

// ✅ Production-ready non-blocking worker pattern
const { Worker } = require('worker_threads');

app.post('/api/report', (req, res) => {
  const worker = new Worker('./report-worker.js', {
    workerData: { filePath: './large-report.csv' }
  });

  worker.on('message', (processedData) => {
    res.json(processedData);
  });

  worker.on('error', (err) => {
    res.status(500).json({ error: "Failed to compile report" });
  });
});

2. Leverage Multi-Core Scalability with Clustering

A standard Node.js process runs on a single CPU core. In production environments where your cloud instances have multiple cores, a single thread leaves substantial compute capacity completely idle.

The PM2 Cluster Approach

Instead of manually managing this with the native cluster module, use PM2 cluster mode in production. PM2 handles process lifecycle management and automatic load-balancing between child processes out of the box:

// ecosystem.config.json
{
  "apps" : [{
    "name" : "mealpe-api",
    "script" : "./dist/server.js",
    "instances" : "max", // Automatically scales to all available logical cores
    "exec_mode" : "cluster",
    "env_production": {
      "NODE_ENV": "production"
    }
  }]
}

By deploying in cluster mode, we instantly multiplied our request capacity by 4x on our quad-core database proxy server without changing a single line of API routing logic.

3. Implement Redis Caching for Read-Heavy Routes

The fastest API request is the one that never touches your primary relational database. Database I/O is almost always the p95 latency bottleneck.

At MealPe, venue menus and outlet statuses change infrequently but are queried on every single page load. Caching these responses in Redis (an ultra-fast, in-memory store) reduces response times down to sub-10 milliseconds.

// ✅ Polished API response caching helper
const redis = require('redis');
const client = redis.createClient({ url: process.env.REDIS_URL });

async function getCachedMenu(req, res, next) {
  const { venueId } = req.params;

  try {
    const cachedData = await client.get(`menu:${venueId}`);
    if (cachedData) {
      res.setHeader('X-Cache', 'HIT');
      return res.json(JSON.parse(cachedData));
    }

    // Cache miss - pass to database controller
    res.setHeader('X-Cache', 'MISS');
    next();
  } catch (err) {
    // Fallback gracefully on cache error - don't crash client
    next();
  }
}

4. Query Optimization & Indexing

If your database requests are slow, Node.js will be slow. I audited our queries using EXPLAIN ANALYZE in PostgreSQL and addressed the following:

Missing Indexes: We added indexes on foreign keys and columns used in filtering/sorting (e.g., venue_id, created_at).
N+1 Query Elimination: Replaced recursive database calls inside loops with clean SQL JOIN statements or Sequelize eager loading (include).
Strict Pagination: Never load whole tables into memory. Enforce strict LIMIT and OFFSET (or cursor-based keys) on all listing tables.

5. Enable Compression

Ensure Gzip or Brotli compression is active on all JSON and text payloads. This reduces payload transfer sizes by up to 70% , speeding up rendering times on mobile devices operating on congested cellular networks:

const compression = require('compression');
const express = require('express');
const app = express();

// Compresses all outgoing response payloads automatically
app.use(compression());

Summary of Action Items

Profile Continuously: Use clinic.js or Chrome DevTools to locate memory leaks and CPU blocks under test loads.
Cluster by Default: Scale to multiple cores using PM2 cluster mode.
Cache Strategically: Cache read-heavy API responses using Redis with realistic TTLs.
Tune the Database: Set index paths on Postgres and prevent large scans.

Zero-Downtime Deployments with PM2 and NGINX

Poojan — Sat, 01 Nov 2025 00:00:00 +0000

When your platform processes meal orders across busy institutional cafeterias, downtime isn't just inconvenient - people don't get fed. High availability is a hard operational requirement, not just a product target.

The original deployment process was: SSH into the server, git pull, npm install, and pm2 restart all. This caused a 10–30 second outage per deploy , during which our API dropped orders and hung terminals.

Here is exactly how I set up graceful, zero-downtime deployments for our Node.js server cluster using PM2's native clustering and NGINX upstream proxies.

1. PM2 Cluster Configuration

The foundation of zero-downtime is simple: never let the number of active app instances drop to zero. PM2's cluster mode makes it straightforward to scale your process across all cores:

// ecosystem.config.js
module.exports = {
  apps: [{
    name: 'mealpe-api',
    script: './dist/server.js',
    instances: 'max', // Scale to all available CPU cores
    exec_mode: 'cluster',
    max_memory_restart: '500M',
    listen_timeout: 10000, // Wait 10s for boot signal
    kill_timeout: 5000 // Wait 5s for clean close
  }]
};

instances: 'max' : Spawns a process on each CPU core.
listen_timeout : Instructs PM2 to wait for a database connection or socket handshake before marking a new process as "Online."
kill_timeout : Gives active processes 5 seconds to wrap up in-flight REST queries before forcing a close.

2. Transitioning to Graceful Reloads

Instead of calling pm2 restart (which kills all instances simultaneously), we transition to pm2 reload.

Reload initiates a rolling update : it spawns a new instance, waits for it to become online, then safely turns down an old instance. It repeats this pattern process-by-process, maintaining maximum API capacity:

# Production deploy script (deploy.sh)
#!/bin/bash
set -e

echo "Pulling latest branch code..."
git pull origin main

echo "Installing production-only dependencies..."
npm ci --production

echo "Executing rolling reload..."
pm2 reload ecosystem.config.js --update-env

echo "Deploy successfully completed!"

3. Implementing Application Graceful Shutdowns

PM2 sends a SIGINT trigger to your process before shutting it down. If your application doesn't handle this signal, it terminates instantly, dropping all connections mid-transaction.

You must catch the SIGINT event, close the HTTP port to block new inbound traffic, finish active queries, and release database pools:

// ✅ Professional graceful shutdown hook in server.js
process.on('SIGINT', () => {
  console.log('SIGINT signal received. Starting graceful shutdown sequence...');

  // Stop the HTTP server from accepting new socket sessions
  server.close(async () => {
    console.log('HTTP server successfully closed.');

    try {
      // Release database connection pools cleanly
      await db.end();
      console.log('Database pools released. Exiting cleanly.');
      process.exit(0);
    } catch (err) {
      console.error('Error during database teardown:', err);
      process.exit(1);
    }
  });

  // Force close after a 6-second timeout block if connections hang
  setTimeout(() => {
    console.warn('Forced shutdown active: connections did not close in time.');
    process.exit(1);
  }, 6000);
});

4. Configuring NGINX Load-Balancing

NGINX is our front-door gateway. The key configurations for zero-downtime routing include setting up an upstream pool and instructing NGINX to pass traffic to active processes on failures:

upstream mealpe_backend {
    server 127.0.0.1:3000;
    keepalive 64; # Keep connection channels open to reduce latency
}

server {
    listen 443 ssl http2;
    server_name api.mealpe.in;

    location / {
        proxy_pass http://mealpe_backend;
        proxy_http_version 1.1;
        proxy_set_header Connection "";
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;

        # 🚀 Crucial: if one instance is reloading, try next active worker
        proxy_next_upstream error timeout http_502 http_503;
        proxy_connect_timeout 3s;
        proxy_read_timeout 15s;
    }
}

By adding proxy_next_upstream, if an instance is in the middle of reloading and drops a connection, NGINX instantly retries the query on a sibling process. The client has zero awareness of the deploy event.

5. Webhook CI/CD Automation

To secure execution, we set up a lightweight deployment daemon on our EC2 instance that exposes a secured webhook route. When a PR merges into main on GitHub, our actions pipeline runs tests, builds the typescript bundle, and pings the webhook:

# GitHub Actions CI deployment step
- name: Trigger Server Deploy Webhook
  run: |
    curl -X POST \
      -H "Authorization: Bearer ${{ secrets.DEPLOY_SECRET }}" \
      https://api.mealpe.in/webhooks/deploy

The daemon checks the bearer token (a shared secret) in constant time before running anything. This triggers the automated deploy.sh script locally on the server.

The Outcome

0 seconds of user-facing downtime recorded.
45-second deployment pipeline from git merge to production availability.
Confidence to deploy minor changes and hotfixes safely during standard hours.

DEV Community: Poojan

I Have Idempotent Search Endpoints Doing POST. Should They Be QUERY?

The endpoint I've apologized for a hundred times

What QUERY actually is

The genuinely new trick: a cache key that includes the body

So why haven't I migrated?

What I'm actually doing about it

The one idea to take away

Reconciliation, Not Sync: Integrating a System That Also Webhooks You Back

The word "sync" is the bug

Failure 1: the echo loop

Failure 2: out-of-order and duplicate events

Failure 3: the fields don't mean the same thing

The part that makes it correct: the reconciliation sweep

Audit everything at the boundary

What actually broke in production

What I'd still improve

The one idea to take away

I Automated 90% of a Grungy Data-Entry Job Without an LLM — and Where an Agent Would Actually Earn Its Place

The grungy job

Step 1: bulk import beats a smart importer

Step 2: match images by filename — fuzzy, but still deterministic

Step 3: a human-in-the-loop queue for the ambiguous 10%

So where does an LLM actually earn its place?

What I'd build next (and what I still wouldn't)

The one idea to take away

Never Trust the Client to Create the Order: Making Payment the Source of Truth

The bug that doesn't show up in your tests

The fix: make the money the source of truth

One order, two callers, zero divergence

The race you just created

Layer 1 — the database has the final say

Layer 2 — the webhook recognizes "the client already won"

Layer 3 — duplicate webhooks are expected, not exceptional

The boring parts that aren't optional

What actually broke in production

What I'd still improve

The one idea to take away

You Probably Don't Need Kafka: A Durable Outbox and Job Queue in Plain Postgres

The email that sends twice, or never

The outbox: make the message part of the transaction

Draining the queue: FOR UPDATE SKIP LOCKED

The catch you must design for: at-least-once

The safety net: reconciliation, not just delivery

What actually broke in production

When you actually do need Kafka

The one idea to take away

Scaling PostgreSQL in Production: Indexing, RPC Functions, Views, and Query Optimization

Understanding the Bottlenecks

Composite Indexes Instead of More Indexes

Moving Business Logic into Supabase RPC Functions

Simplifying Data Access with Database Views

Eliminating Unnecessary Data Retrieval

Avoiding SELECT *

Introducing Pagination

Removing Unnecessary Sorting

Optimizing Aggregations

Adopting UUIDv7 for New Tables

Key Lessons Learned

1. Indexes Are Not a Magic Solution

2. Composite Indexes Usually Matter More Than Individual Indexes

3. Database-Side Processing Can Be Extremely Effective

4. Views Improve Maintainability

5. Pagination Should Be Introduced Early

6. Small Optimizations Compound

Final Thoughts

Building an Atomic Delivery Dispatcher with PostgreSQL Row-Level Locking

1. Concurrency Control: Locking with SELECT ... FOR UPDATE

The Locking Mechanics

2. Enforcing State Transitions via State Machines

3. Storing Rich Tracking Data with JSONB Proof of Delivery

Why JSONB instead of structured relational columns?

4. Real-Time Telemetry Updates

Summary of Dispatcher Core Patterns

Designing a Multi-Tenant Subsidy Engine with Concurrency-Safe Budget Tracking

1. Multi-Tenant Relational Isolation

The Architectural Benefit

2. Resolving Subsidy Conflicts: The "Maximum Rule"

Why Reduce?

3. Concurrency-Safe Budget Exhaustion

Draining the queue: `FOR UPDATE SKIP LOCKED`

Avoiding `SELECT *`

1. Concurrency Control: Locking with `SELECT ... FOR UPDATE`