My Fruitopia

A customer puts a mango in their basket and taps through checkout. This is everything that happens between that tap and the van reaching their door.

Read from supabase_checkout_data_source.dart, place_customer_order in supabase/schema.sql, and order_tracking_cubit.dart. Every name below appears in the source exactly as written.

1. 01

   The list is the stock

   Each delivery day the seller publishes one list: what was picked, what it costs, how much is left, which regions it reaches, the delivery fee and the hour ordering closes. The customer’s home screen is that row and the items hanging off it — not a catalogue filtered down to whatever happens to be available.

   • daily_lists
   • daily_list_items
   • order_deadline
   • delivery_fee
2. 02

   Ordering closes on the clock

   The deadline and the delivery date are on screen from the first tap. A list whose deadline has passed is skipped when the storefront is fetched, so it stops being offered rather than failing at the end. Nobody fills a basket they will not be allowed to submit.

   • order_deadline
   • delivery_date
3. 03

   Checkout is one call, not an insert

   The app never writes to the orders table. It hands the whole basket to a single security-definer function and lets the database decide what happens to it — which is what makes the order, its lines and the stock either all done or all not done.

   • place_customer_order
   • security definer

   ▸What it says when it refuses

   “Not authenticated”. The function reads the customer from auth.uid() and refuses outright if there is nobody there, whatever the app sent with the request.

4. 04

   Two taps cannot split one order

   Before anything is read, the transaction takes an advisory lock keyed on the customer and the delivery date. Two checkouts racing for the same day would otherwise both see no open order and insert one each — the very split the function exists to prevent. The second now waits, then finds the first.

   • pg_advisory_xact_lock
5. 05

   An open order is topped up, never duplicated

   A pending, unpaid order for the same seller and the same day is the one to add to; anything delivered or already settled is closed business and starts a fresh one. The same product ordered twice tops up its existing line rather than repeating it, so the packing list stays one entry per item — and the address follows the latest checkout, because one merged order is one drop-off.

   • orders
   • order_items
   • fulfillment
   • payment
6. 06

   Stock comes off the list as it goes

   Every line decrements the day’s list, floored at zero so a race cannot drive it negative. The “only 8 left” the next customer reads is the same number this order has just moved.

   • decrement_daily_list_stock
   • daily_list_items
   • stock
7. 07

   The totals are recomputed, never patched

   Once the lines are in, the subtotal is summed back off the order’s own rows and the delivery fee added once. A merged order can never disagree with what it actually contains, and delivery is never charged twice.

   • subtotal
   • delivery_fee
   • total
8. 08

   The seller hears about it

   Inserting the order fires one trigger and topping it up fires another, each writing a notification — but only if the seller has that alert switched on. That row’s insert then calls an edge function with nothing but its id: a forged request cannot choose the recipient or the wording, because the function reads both from the table itself.

   • notify_seller_new_order
   • on_order_topped_up
   • notifications
   • push-on-notification
9. 09

   Then the customer watches it come

   The tracking screen subscribes to two Postgres change streams filtered to that seller — the van’s position and the route’s progress. The map moves as the van moves, and a completed stop shortens the estimate immediately rather than at its next significant move.

   • seller_locations
   • delivery_progress
   • onPostgresChanges