Financials: Implement idempotent refund system & fix partial refund over-collection

[khaira777]

Financial Integrity & Refund Tracking Request

Extracted from fork codewithsharma/FloCafe (branch: develop):

Overview

Improvements to track refunds, ensure idempotency on financial transactions, handle cash refunds during shift/day close, and prevent math issues on partial refunds.

Proposed Changes

1. Refunds Table & Idempotency Tracking:
   • Add dedicated refunds table and refund_idempotency table (Migration 72).
   • Track amount_cents, original_method, approved_by, and shift_id.
   • Reference: main/routes/refunds.ts, main/services/refund.ts.
2. Partial Refund Over-Collection Fix:
   • Fix calculation logic preventing over-collection or double refund balance mismatches on partial refunds.
   • Reference: commit fedbe4c.
3. Cash Refund Integration into Day Close:
   • Ensure cash refund subtractions are properly accounted for in expected cash counts during shift reconciliation and day close.
   • Reference: commit aaf8720.
4. Fail-Closed DB Recovery Guard:
   • Fail closed safely if the production SQLite database is missing or unreadable rather than generating a blank database in production mode.
   • Reference: commit e236542.

[khaira777]

Verdict: VALID BUT NEEDS RESCOPING

Findings:

• There is no refunds/refund_idempotency table on main. Refunds are modeled as bill adjustment lines created when items are voided (frontend/src/lib/i18n/en.json:192 "Voiding it adds a refund line to the bill"; statuses voided/void_adjustment handled throughout main/routes/bills.ts and main/routes/order-items.ts:87-158).
• Idempotency infrastructure already exists for payments/orders: payment_idempotency (main/db.ts:3292), order_idempotency (:3347), payment_transaction_refs (:3300). A refunds table should mirror this pattern rather than invent a new one.
• Split math already uses minor-unit rounding with remainder distribution (main/routes/bills.ts:96-99, :175, :548-556). The "partial refund over-collection" claim comes with no reproduction; I found no obvious defect — this needs a failing test/evidence first (related: tests/cancel-override.test.ts, tests/issue-24-cancel-item-checkout.test.ts).
• "Cash refund into day close": no day close exists — this part is blocked on Feature: Shift lifecycle management, cash drawer reconciliation, & business day close #279.
• Fail-closed DB: initDatabase (main/db.ts:607-625) opens/creates the DB file; creating a blank DB in production is the behavior to guard, but first-run setup depends on creating a fresh DB — needs a "production + missing/unreadable DB → fail with restore guidance" decision; note runStartupIntegrityCheck() already runs at startup (main/db.ts:623).

Suggested scope: (1) refunds table + idempotency keyed like payment_idempotency; (2) reproduce and fix the partial-refund math with a regression test before changing anything; (3) defer day-close integration to #279; (4) split the fail-closed-DB item out with first-run compatibility as an acceptance criterion.