Decimal vs Integer Money Columns — IDR Consideration

Written 2026-07-27 from a deep analysis of the schema, code, and docs (staff-engineer review agent, claims verified against db/structure.sql and the code). Ali confirmed the production context the same day.

The Question

Three older tables store money as decimal. The newer Billing and Gig tables store money as integer cents. Two questions:

1. Should we migrate the old decimal columns to integer cents?
2. How does Indonesia change the answer? IDR is a large-number currency: 1,400,000 IDR ≈ 100 SGD.

Production Context (confirmed by Ali, 2026-07-27)

• Production billing tables are empty.
• Production does not use billing yet. The only exception: billing_agreements rows are copied over from jodgig.
• It is safe to delete all billing_agreements and regenerate them from jodgig.

Consequence: schema changes to the billing tables cost nothing right now. No 3-step migration is needed. A single widening ALTER TABLE per table is enough, and it is instant on empty tables.

Answer in One Paragraph

Leave all three decimal column sets alone. Two belong to tables that are being replaced. One is display-only. The real Indonesia problem is in the NEW tables: every billing and gig _cents column is a Postgres integer, and a normal Indonesian invoice overflows integer. Widen those columns to bigint now, while the tables are empty.

IDR Facts That Change the Design

• ISO 4217 gives IDR exponent 2. The minor unit is the sen (1/100 rupiah). Sen coins are obsolete in daily use, but the standard still says 2.
• Stripe treats IDR as a two-decimal currency. Stripe's maximum single IDR charge is 999,999,999,999 minor units.
• Every currency on Jod's path — SGD, IDR, MYR, PHP, THB — has exponent 2. A "per-currency minor unit" scheme and an "always ×100" scheme store identical numbers for all of them.
• Do not build a per-currency exponent lookup today. It adds a failure mode (a wrong exponent is a silent 100× error) and buys nothing until a zero-exponent currency (VND, JPY, KRW) actually arrives.
• The only real decision is integer vs bigint.

The Overflow Math

Postgres integer (int4) holds up to 2,147,483,647. Exchange rate used: 14,000 IDR per SGD.

Scheme	SGD ceiling	IDR ceiling	Verdict
integer, ×100	SGD 21,474,836.47	21,474,836 IDR = SGD 1,534	Breaks on a mid-size invoice
bigint, ×100	SGD 9.2 × 10¹⁶	9.2 × 10¹⁶ IDR	Correct. 4 extra bytes per row.

Real values that break integer ×100 today:

Real value	IDR minor units	Over int4 max by
SGD 2,000 credit purchase (typical SME)	2,800,000,000	1.3×
SGD 21,313.83 (largest observed balance)	29,839,362,000	13.9×
SGD 79,927.50 (most overdrawn outlet)	111,898,500,000	52.1×
Stripe's own IDR max charge	999,999,999,999	465×

The Three Decimal Column Sets — Verdicts

1. gig_temp_job_slots.slot_salary / .slot_insurance — leave it (dying table)

This table is a read-only mirror of the legacy jodgig database. Only the sync writes it (app/domains/gig/temp_jobs/sync_service.rb:115,125). The replacement already exists and already stores money correctly: gig_payments.gross_wage_cents / deductions_cents / net_wage_cents plus a currency column. docs/db/gig.dbml:5 says the legacy tables will be migrated to the new schema. Migrating a dying table is wasted work.

The same verdict covers gig_temp_job_slots.hourly_rate numeric(10,2), which is in the same table.

2. listings_jobs.pay_from / .pay_to — leave the type (display only)

These columns are displayed and range-filtered. They are never summed and never invoiced.

• Postgres numeric is exact. The rounding danger people associate with decimal money is a float problem. It does not exist here.
• No overflow risk: numeric(12,2) holds up to 9,999,999,999.99 — an IDR annual salary fits with room to spare.
• The upstream source careers_jobs.pay_from / pay_to is already integer whole dollars, copied across by app/domains/careers/jobs/sync_service.rb:29-30. Converting the projection to cents would move it further from its own source.

If cents is ever wanted here, fold it into the Gig::TempJob → Gig::Job cutover. The table is a projection and rebuilds from source; the cost is in the read sites, not the data.

Data-quality note found on the way: 206 of 424 local rows have fractional pay values, including a pay_from of 0.09 on a public listing. Worth its own look.

3. org_outlets.available_credits / .consumed_credits / .min_credit_limit — leave it (transitional)

docs/db/org.dbml:74-76 marks these columns transitional: they migrate into Billing::EntitlementBalance and Billing::LedgerEntry. The org-outlet model doc says remove them once that migration is complete. In Rails they are write-once-from-sync and read-only for display. All credit arithmetic still happens in legacy PHP. The replacement design already uses bigint unit counts (billing_outlet_budgets.units_available).

org_outlets.rate numeric(8,2) is the same story — transitional, wage rates live in Gig::PayRate.

What To Do Instead: Widen _cents Columns to bigint

The current state is inconsistent, and the wrong half is the transactional half:

Already bigint (correct)	Still integer (overflows on IDR)
billing_entitlement_balances.deferred_revenue_cents	billing_invoices.subtotal_cents / discount_cents / tax_cents / total_cents
billing_entitlement_balances.platform_fee_deferred_cents	billing_invoice_lines.unit_price_cents / amount_cents / tax_cents / discount_cents
billing_entitlement_balances.units_available / units_reserved	billing_payments.amount_cents
	billing_product_prices.unit_price_cents / compare_at_price_cents
	gig_payments.gross_wage_cents / deductions_cents / net_wage_cents

Because production billing is empty (see Production Context), each column needs only:

change_column :billing_invoices, :total_cents, :bigint

integer → bigint is a widening change. No value can fail the cast.

• No Ruby changes: Ruby Integer has no size limit, so every Sorbet sig and every .to_i stays valid. Regenerate the Sorbet RBIs (bundle exec tapioca).
• No frontend changes: the ×100 rule is unchanged, so every existing ÷100 display site keeps working.
• Cleanup worth doing separately: six places hardcode the ÷100 conversion (frontend billing-entitlement-balance-service.js:26, billing-product-price-list.jsx:11, billing-product-form.jsx:45, billing-payment-form.jsx:67; API send_mailer.rb:38, pdf_renderer.rb:249). Centralise into one helper so a future zero-exponent currency is a one-file change.

Verification artifact: before running the migration, a report showing SELECT COUNT(*) = 0 for every table being altered (or, for billing_agreements, confirmation it was regenerated after). After running, a schema diff showing only integer → bigint type changes.

If rows ever exist before this runs, use the 3-step form instead (add nullable bigint column → backfill → constrain and swap). The riskiest read site is app/domains/billing/payments/team_verify_manager.rb:62-70: it compares summed amount_cents against total_cents to decide whether an invoice is paid and credits are granted. If the two sides read different columns mid-migration, credits are granted or refused wrongly, with no error raised. Cut that site over last, in one transaction.

The Storage Rule (for the conventions page)

Proposed home: jod-app/docs → 90-99-engineering-meta/96-conventions/money-and-currency.md. Summary:

• Store money as a whole number of the currency's smallest unit. Always bigint. Never float. Never decimal for an amount the system adds, invoices, or pays out.
• Every currency Jod trades in today has 100 minor units, so the stored number is the amount ×100. SGD 25.50 → 2550. IDR 1,400,000 → 140000000.
• Keep the _cents column name. Rename only if a zero-minor-unit currency (VND, JPY) ever arrives — and add a minor-unit column to geo_countries at that point, not before.
• Every table with a money column carries a currency column (ISO 4217 code), copied at write time on snapshot tables. Never hardcode a currency in code.
• Not covered by this rule: internal rates (_bps integer basis points), external-facing rates (decimal), credit unit counts (bigint, no _cents suffix), and advertised pay ranges on listings (marketing numbers, not money the system moves).

Side Findings

1. Hardcoded SGD in listings code (violates the multi-country rule; fix or file an issue):
   • app/domains/listings/jobs/gig_temp_job_attributes_service.rb:97 — pay_currency: 'sgd' on every gig listing.
   • app/domains/listings/gig_temp_job_schema_builder.rb:7 — 'SGD' baked into the Google JSON-LD.
2. Credits backfill needs a rounding rule. Legacy outlet credits are SGD amounts with real cents (largest observed balance $21,313.83; most overdrawn −$79,927.50). The new model counts whole credits (1 credit = 100 cents). Whoever writes the outlet-credit backfill must ratify: floor, round, or carry the remainder as a cents adjustment. Invisible until backfill day.
3. Invoice PDF formats IDR wrongly (cosmetic): pdf_renderer.rb:249 always prints two decimals — an IDR invoice would read "IDR 1400000.00" where Indonesian convention is "Rp 1.400.000".

Not Verified

• Legacy PHP credit paths (jodgig-api) were not read; the fractional-credits evidence comes from the Jod-side docs.
• Actual IDR salary and invoice ranges for the Indonesia launch. The analysis used 14,000 IDR/SGD and scaled observed SGD values. Different Indonesian pricing changes the magnitudes, not the direction.