---
name: evolution-float-money-columns
description: "invoiceitems money columns are single-precision FLOAT and lose cents above ~$8.4M, drifting from Xero"
metadata: 
  node_type: memory
  type: project
  originSessionId: 21ffbe0f-c4ed-4679-a200-abd10121944f
---

The money columns across invoicing are **single-precision** FLOAT (4-byte): item tables (`invoiceitems.total` float(10,2), `adjPrice` float(11,4); same on `quoteitems`/`billitems`/`purchaseitems`) AND header tables (`invoices.total` float(10,2), `invoices.tax` float(9,2), `invoices.balance`/`paid`; `quotes.total`; `bills.total`/`tax`). Above ~$8,388,608 a single float's spacing is ≥1.0, so cents can't be stored: `11,615,125.90` is physically stored as `11,615,126.00`, and a header subtotal of `11,869,333.21` stores as `11,869,333.00` (lost 21c). The Xero payment-sync reconciliation reads `invoices.total + invoices.tax` and false-flagged a matching invoice as a mismatch (evo 13,056,266.38 vs xero 13,056,266.55) purely from this truncation — the migration must cover the HEADER tables too, not just the item tables.

**Why:** root cause of the "invoice GST/total out by ~10c vs Xero" report (2026-06) was the column TYPE, not rounding logic.

**How to apply:** to match Xero, sum line amounts as `SUM(ROUND(CAST(adjPrice AS DECIMAL(15,4))*qty, 2))` — cast the float to exact decimal first, round each line to 2dp, then sum. Applied in `library/invoice.php` `updateTotals()`+`saveInvoiceTotal()` and `invoiceaddsave.php:668`. NOTE: the on-screen line sub-total still renders the truncated float `total` column, so a single huge line can display e.g. 126.00 while contributing 125.90 to the (now-correct) header — a proper fix is migrating the money columns to DECIMAL. GL breakdown queries in `accounting.php` (sum(total) grouped by account/tax) still read the float column. Relates to [[evolution_two_accounting_files]].

**DECIMAL migration (2026-06-12) `migrations/money_columns_float_to_decimal.sql`** converted the invoicing tables (invoiceitems/quoteitems/billitems/purchaseitems/invoices/quotes/bills) to decimal(15,2)/(15,4) — but **MISSED `payment` and `gledger`**, which stayed float(10,2). So Xero payment-sync still rounded payments to whole dollars above ~$8.4M: a $13,056,266.55 payment echoed correctly by the cron but stored as 13,056,267.00 (float ULP = $1 in [8.39M,16.78M)), and the GL journal (gledger.credit/debit) lost cents on every large posting. **Fixed + hotfixed/tested in prod 2026-06-14 by `migrations/payment_gledger_decimal.sql`** (payment.total/tax + gledger.credit/debit → decimal(15,2); mirrors the money_columns structure). Verified working: $13M Xero payment now stores cents. Already-stored mangled rows are unrecoverable by the ALTER — re-pull affected payments from Xero (delete row+gledger postings, let xero_payment_cron re-insert). See [[gst_precision_project]].