Stock availability across branches: showing what the nearest store actually has

One row per product, per branch

Inventory is a table with a unique pairing of product and branch. Each row holds quantity on hand, quantity reserved, an optional price override, an in-store-only flag and a low-stock threshold.

Reserved quantity is what stops the classic double-sell: stock committed to an order in progress is not offered again as available. The low-stock threshold is what drives "only 2 left at Boksburg" messaging without hard-coding a number into the front end.

Who is allowed to change it

Stock is publicly readable, shoppers must see it, but writes are constrained. Administrators can update anything; branch staff can update only the branches they are attached to, enforced through a security-definer helper used inside the row-level security policies on inventory and orders.

That means a franchisee correcting their own stock count cannot touch another branch's numbers, and you do not have to police it with process.

• Public read so availability can be shown pre-checkout
• Branch staff writes scoped to their own branch by policy
• The same policy pattern governs orders and order lines

Finding the nearest branch two ways

The store locator asks the browser for the visitor's location, or takes a manual search, then sorts branches by haversine distance from their coordinates and maps them with hours, contact details and directions.

Separately, the server resolves an approximate visitor location and ranks active branches by distance from it, falling back to matching city and region names against branch details when coordinates are unavailable. That server-side ranking is what allows a sensible default branch on the first page view, before anyone has clicked anything.

Being precise about "real time"

Stock is read live from the database on every request, so a change made in the admin is reflected on the next page load or search. There is no cached nightly snapshot.

What is not currently implemented is a live socket subscription that repaints a stock number on an open page without a request. We would rather state that plainly than let "real-time stock" imply something the code does not do. For retail decision-making, is it worth driving to this branch, request-time accuracy is what actually matters.

Where it shows up for the shopper

Branch stock is not a hidden admin field. It filters photo-search and text-search results when a branch is selected, it drives collection availability at checkout, it powers the branch locator, and it feeds low-stock messaging on the product page.

For multi-branch retailers this is usually the single feature that moves online revenue, because it converts browsing into a store visit with confidence attached.