MEDIUM: Floating-point REAL type for monetary amounts in SQLite #75

New Issue

Closed

opened 2026-05-31 12:03:27 -05:00 by null · 1 comment

null commented

2026-05-31 12:03:27 -05:00

Owner

Bug Description

Multiple money-related columns in the database use REAL (IEEE 754 double-precision floating point) in SQLite:

bills.expected_amount, bills.current_balance, bills.minimum_payment
payments.amount
monthly_bill_state.actual_amount
monthly_starting_amounts.first_amount, fifteenth_amount, other_amount
monthly_income.amount

Floating-point arithmetic introduces rounding errors (e.g., 0.1 + 0.2 !== 0.3). Some code mitigates this with toFixed() and Math.round(), but its inconsistent across the codebase.

Notably, transactions.amount uses INTEGER (presumably cents) while payments.amount uses REAL, creating an inconsistency that could cause comparison failures.

Impact

Pennies-level rounding discrepancies in financial calculations over time.

Recommended Fix

Long-term: migrate all monetary columns to INTEGER (cents) and convert in the API layer. Short-term: ensure all monetary arithmetic uses Math.round(result * 100) / 100 consistently.

## Bug Description Multiple money-related columns in the database use REAL (IEEE 754 double-precision floating point) in SQLite: - bills.expected_amount, bills.current_balance, bills.minimum_payment - payments.amount - monthly_bill_state.actual_amount - monthly_starting_amounts.first_amount, fifteenth_amount, other_amount - monthly_income.amount Floating-point arithmetic introduces rounding errors (e.g., 0.1 + 0.2 !== 0.3). Some code mitigates this with toFixed() and Math.round(), but its inconsistent across the codebase. Notably, transactions.amount uses INTEGER (presumably cents) while payments.amount uses REAL, creating an inconsistency that could cause comparison failures. ## Impact Pennies-level rounding discrepancies in financial calculations over time. ## Recommended Fix Long-term: migrate all monetary columns to INTEGER (cents) and convert in the API layer. Short-term: ensure all monetary arithmetic uses Math.round(result * 100) / 100 consistently.