The quote that must not expire

If you've built a normal store, you know the shape: cart, checkout, payment, stock goes down. This isn't that.

Pharmacies don't check out. They submit a quote — ใบเสนอราคา — and wait. Staff read it, fix the quantities against what's actually on the shelf, and approve. Only then does an order exist.

That one change moves where stock leaves. Not at payment, but hours later, on a different screen, by a different person, after someone has corrected the numbers against reality. Basically all the interesting problems live in that gap.

Approval is one transaction, or it's a bug

When staff hit approve, four things have to happen together: check the requested quantities against real stock, decrement it, write the approved prices, create the order.

If any of those lands without the others, the shop loses money and nobody notices for weeks. Approve without decrementing and you oversell. Decrement without creating the order and stock is stuck against something that doesn't exist.

So it's one transaction, and it opens by locking rows in a fixed order:

SELECT … FROM branch_product_units
WHERE product_unit_id IN (…)
ORDER BY product_unit_id ASC
FOR UPDATE

The ORDER BY isn't decoration. Two staff approving two quotes that share products will reach for the same rows. Without a fixed lock order they each end up holding half of what the other needs, and you get a deadlock. Sort by product_unit_id and every transaction grabs them in the same sequence, so one just waits.

Inside the lock: check, decrement, write the approved numbers, set status, create the order. The decrement is the reservation. There's no separate reserved column, because that's a second source of truth for stock, and a second source of truth is a thing that will eventually disagree with the first one.

The status isn't the state

Here's the bit that bit me in my own codebase.

A quote that's become an order still says approved_quote. It never flips to converted. What actually marks it is a foreign key:

quotes.order_id IS NULL      → not an order yet
quotes.order_id IS NOT NULL  → an order exists, and it owns the stock

Felt wrong when I wrote it. I think it's right now. The status is what staff decided; the foreign key is what exists. If you collapse them, then the day you add a fifth status you're re-auditing every query that ever asked "is this converted?"

But it leaves a sharp edge lying around, and I found it the hard way.

The bug: stock going up on its own

Quotes expire. An approved quote nobody touches for 48 hours should release its reservation and give the stock back. It's lazy — checked on read, no cron — so an abandoned quote costs nothing until someone opens it.

First version was the obvious one:

if (quote.status === "approved_quote" && quote.expiresAt < now) {
  await releaseQuoteReservation(tx, quote.id, branchId);
}

Now read that against the rule above. A converted quote is still approved_quote.

So 48 hours after approval, the first person to open the quote behind a picked, packed, shipped, paid order would quietly hand its stock back to the branch.

We caught it in review as stock climbing by itself: 146, then 154, with nothing delivered in between. No error. Queue looked normal. The number was just wrong — and it would have kept overselling against inventory the warehouse didn't have, deleting promotion usages a live order still pointed at, and firing restock notifications for things that never came back.

Fix is one line:

if (
  quote.status === "approved_quote" &&
  quote.orderId === null &&          // ← the entire bug
  quote.expiresAt < now
) { … }

It goes in twice: once in the cheap pre-check, once more inside the FOR UPDATE re-check. Between reading the row and locking it, another transaction can convert the quote. A guard you only check outside the lock is a guard you check at the wrong time.

The general version, in case you have one of these: when one column means intent and another means existence, every rule written against intent alone is a bug waiting for the right timing. Worth grepping for.

Letting staff rewrite the quote

The feature that makes the whole model worth it came from one sentence from the shop: สต็อกจริงไม่ตรงกับระบบ — the real stock doesn't match the system.

So staff can rewrite a quote before approving. Change quantities, adjust a price, add lines, drop lines. Dropping is by omission — the schema says requestedQuantity >= 1, so there's no "set it to 0" that could mean two different things.

Every edit re-prices the whole thing from scratch and re-runs the promotion engine. Patching just the changed line would be cheaper, and that's exactly how totals drift: enough partial updates and the sum stops matching its parts. Recomputing is boring and stays right.

Two guards:

• order_id IS NOT NULL → 409, hard stop. Editing a converted quote desyncs the order that's already pointing at the original quantities. The UI hides the button too, but the server doesn't trust the UI.
• expectedUpdatedAt has to match, else 409. Two staff on the same quote is a normal Tuesday in a shop. Last-write-wins silently throws away someone's corrections.

There's also a price cap: approved unit price can't exceed 2× the requested one. Blunt, and aimed at typos rather than fraud. ฿70 becoming ฿700 should be caught by the system, not by the customer.

What I'd keep

One transaction around approval, a fixed lock order, recompute instead of patch, and enforce it server-side even when the UI already does. All of those earned their place.

The one I'd think harder about is splitting status from the foreign key. It's correct and it's still there — but it cost me a real bug because nothing in the type system said approved_quote was ambiguous. Same model next time, just make the ambiguity impossible to miss. A narrower type, or a helper that flat-out refuses to answer "is this expired?" unless you also hand it the order id.