Plan: Sales Order deposit missing from End Shift (v2)

Status: DRAFT. Cause not proven. Not yet re-grilled. Replaces deposit-sales-order-end-shift-plan.html (kept for history; its Steps 1 and 2 are withdrawn). Audit of the old plan: grill-review.md.

The issue in simple words

What a deposit is. A customer orders an item that is not in the shop yet. They pay part of the price now, for example RM 50 of RM 150. The cashier puts the RM 50 in the cash drawer. The customer pays the rest and takes the item later.

What the cashier saw. At the end of the day, the cashier counts the drawer. The count is RM 50 more than the screen says the drawer should hold. The screen looks like it forgot the RM 50 deposit.

What we first thought. The system ignores the deposit because the order is not finished yet.

What we found. That is wrong for a normal day. We checked the live test system. When the customer pays the deposit on the same day, in the same shift as the order, the system counts it. A RM 100 float plus a RM 50 deposit shows RM 150. That is correct.

What probably goes wrong. The system links each deposit to the shift in which the order was made, not to the shift in which the money came in. Look at this case:

Day	What happens	What the system does
Monday, shift 1	Customer orders. Pays RM 50 deposit.	Counts RM 50 in shift 1. Correct.
Tuesday, shift 2	Customer comes back. Pays RM 30 more.	Counts the RM 30 in shift 1, not shift 2.

The RM 30 is in the drawer of shift 2. Shift 2 does not expect it. The cashier of shift 2 sees RM 30 too much. Shift 1 is already closed, so its numbers are now wrong. Refunds work the same way.

What we do not know yet. We have not seen this happen. The test system has no such case. We must try it by hand first (Phase 0 below). If it does not go wrong, this is not the cause and we look elsewhere.

The fix, if the test shows the problem. Save the shift on each payment, not only on the order. Then every payment counts in the shift where the money came in.

1. What we proved on dev (2026-10-23, read-only)

Database sellable_dev_tenant_db, schema sellable_tenant.

Check	Result
Deployed `fn_api_dayshift_get_xreport_shift_summary` `payment_amounts` block	No `doc_type` or `status` filter. Same text as `functions_pg.sql:1911-1922`.
Open shift 2830: float 100.00 + one DRAFT SO cash deposit 50.00	Function returns CLOSE_FLOAT expected 150.00 and `XYZ_SALES_DEPOSIT` 50.00. The same-shift case works.
Closed shift 2741 (deposits 11.00 + 15.00, float 0)	Saved CLOSE_FLOAT expected = 26.00. Saved figures include the deposits.
SO payments taken outside the order's shift window	0 rows. Dev has no cross-shift deposit to look at.
DRAFT SOs with deposits	15 sampled, all one payment on the order's own day.

So the old plan's claim ("a $50 deposit is dropped because the order is DRAFT") is false for same-shift deposits.

2. Still open: what caused the +$50 on the real till

The code supports one mechanism. Dev data cannot confirm it.

Cross-shift top-up (main suspect). ft_sales_payment has no shift column (tables_pg.sql:3201-3265). Every drawer sum joins on the order header's dayshift_id. A top-up or refund taken in shift 2 on an order made in shift 1 keeps the shift-1 header (list_deposit_dialog_controller.dart:1373-1393). Result: shift 1 (maybe already closed) gets the cash. Shift 2 is short.

Other suspects, each cheap to test:

The order was not synced before End Shift (list_deposit_dialog_controller.dart:1432-1439).
The tenant with the problem runs an older function body or an older app build.

Phase 0 (do first, no code)

Ask the reporter: was the deposit taken in the same shift as the order? Which tenant? Which app version?
On dev, run this by hand in the app: open shift A, create SO, deposit 50 cash, close shift A. Open shift B, Edit Order, add deposit 30 cash, close shift B.
Run the check below. Expect: shift B CLOSE_FLOAT misses 30; shift A's saved figure is wrong.

-- read-only
SELECT h.doc_ref_no, h.dayshift_id AS header_shift, p.trx_date, p.local_amt, p.tender_category
FROM sellable_tenant.ft_sales_header h
JOIN sellable_tenant.ft_sales_payment p ON p.sales_id = h.sales_id
WHERE h.doc_ref_no = '<SO number>' ORDER BY p.trx_date;
SELECT payment_name, expected_amt FROM sellable_tenant.fn_api_dayshift_get_xreport_shift_summary(<shift B id>)
WHERE payment_name IN ('OPEN_FLOAT','CLOSE_FLOAT','XYZ_SALES_DEPOSIT');

If step 3 does not show the gap, stop. The cause is elsewhere. Do not build Phase 1.

3. Fix (only if Phase 0 confirms): payment-level shift

Decision for the owner: add a shift key to each payment (recommended), or stamp the shift another way. Header-level shift cannot be right for top-ups.

Phase 1 - database (Alembic, prepared only; user runs it)

Add nullable dayshift_id bigint to sellable_tenant.ft_sales_payment (partitioned by trx_date; check the partition DDL first).
Backfill: UPDATE ... SET dayshift_id = h.dayshift_id FROM ft_sales_header h WHERE ... where null.
Change the shift join in all four places together, using COALESCE(fsp.dayshift_id, fsh.dayshift_id):
- fn_api_dayshift_get_xreport_shift_summary (functions_pg.sql:1911)
- fn_api_get_shift_summary (functions_pg.sql:2708; used by compare_dayshift_summary, a mismatch blocks shift close)
- fn_api_set_dayshift_payment (functions_pg.sql:6301)
- vw_dayshift_getdayallshifts (views_pg.sql)
Do not add a status filter. Do not drop the EX_FORFEITED / EXCHANGE_EXCESS exclusion.
Update canonical functions_pg.sql and tables_pg.sql in the same PR. Write the down-migration.
XYZ_SALES_DEPOSIT (DRAFT-only, functions_pg.sql:2372, :6593): also move to the payment shift, so a reprint after Finalize keeps the line. Decide in Phase 0 whether this is in scope.

Phase 2 - API (`apps/v2-sellable-api`)

Accept dayshift_id on payment rows in the sales upload (modules/sales/sales_query.py). Today the header shift is taken from the client and overwritten on update (:1590, :1636-1643).

Phase 3 - POS client (`apps/v2-pos-client/client`)

Add the column to the payment table and sync model (drift; flag build_runner as a manual step).
Set the current open shift on every payment written by list_deposit_dialog_controller.dart (deposit, refund, cash-change rows).
Leave sale_payment_dao.getSalesReportByCash alone except to decide what the dashboard should show. getSalesReportByTenderType has no callers.
Optional, small: show the existing XYZ_SALES_DEPOSIT ("Sales Deposit") line on the End Shift screen and receipt. Needs i18n keys in all four locales (rules I1-I3).

Order of release

Database first (column is nullable, old clients keep working through the COALESCE fallback), then API, then client. Rollback: down-migration restores the old join; the extra column can stay.

4. Tests (must exist before merge)

#	Case	Expect
T1	Same shift: SO + 50 cash deposit	Expected = float + 50 (regression guard, passes today)
T2	Shift A SO deposit 50; shift B top-up 30	A expects 50. B expects 30.
T3	Refund of 20 in shift B for a shift-A deposit	A keeps 50. B shows -20.
T4	Void of a deposit in shift B	Same as T3
T5	Card deposit	Card total includes it in the payment's shift
T6	Foreign-currency deposit	Counted in its own currency; no 1-cent loss
T7	Finalize in shift B	B counts only the balance paid. No double count (verified in code: payments are not copied to the invoice).
T8	Unsynced SO at End Shift	Decide the rule; test it
T9	Close shift: `compare_dayshift_summary`	X-report and shift summary agree
T10	Old row with null `dayshift_id`	Falls back to the header shift

Automated home for DB cases: apps/v2-sellable-bo/backend/tests/unit/repositories/test_business_day_summary_footing_597.py and tests/scenarios/.

5. Not in scope

Prepayment Invoice (Option B). Heavier, more documents, no gain here.
BO Sales Collection showing DRAFT orders.
Fixing old shifts that are already closed. Phase 0 must say whether a one-off correction script is needed.

6. Open questions for the owner

Is the real till problem a cross-shift top-up? (Phase 0 answers it.)
Payment-level shift key (recommended) vs. another way to assign the shift?
Do closed shifts get corrected, or only new ones?