perf: queries and indexes break down at ~100k orders (1yr, 300 orders/day); customers page freezes the app for 40s+

[carvalab]

Problem or motivation

FloCafe keeps order history forever, but the queries and indexes are built for a demo-sized database. I simulated 1 year at 300 orders/day (109,500 orders, 328,500 order items, 93,805 bills) against the real schema and timed the hot paths, then repeated it at 3 years (328,500 orders). A few pages stop being usable well before that point, and one freezes the whole app.

The Customers page is the worst. GET /customers runs 4 correlated subqueries per customer (visits, total spent, wallet balance, last visit) against orders, which has no index on customer_id. With 1,500 customers that took 40.3 seconds at 1 year and 174 seconds at 3 years. better-sqlite3 runs synchronously in the main process, so every API call, IPC, and print job queues behind the query and the app just freezes. The page also re-runs it on every search keystroke.

Second is the date filtering. Every report wraps the column in date(), which makes the created_at index unusable, and the dashboard fires 5+ of these per load (daily-stats, summary, sales, topProducts, insights, payment method breakdown). At 1 year / 3 years: salesToday 11/41ms, topProducts 60/169ms, payment method breakdown 49/157ms, reports/tables 68/215ms. Tolerable at year one, all linear.

Third is the Orders page. It shows only the latest 50 orders, with no pagination and no date filter, so after a year there is no way to reach yesterday's orders from the UI. Every 10-second poll also runs a per-order N+1, roughly 300 queries for 50 orders (items, addons, table, customer, bill, loyalty sum), plus up to 50 extra print-history HTTP calls.

The KDS/kitchen query has the same shape: status != 'cancelled' OR EXISTS(...) cannot use an index, scans every order with a correlated subquery, and the KDS screen polls it every 5 seconds in REST fallback mode. 114-194ms per poll at 1 year.

Proposed solution

1. Index migration, biggest win with no code change: orders(customer_id), orders(table_id), bills(created_at), bills(payment_status, paid_at), print_logs(bill_id), loyalty_ledger(bill_id, type), order_items(product_id), customers(created_at). After: customers page 40.3s -> 0.6ms at 1 year, 174s -> 1.9ms at 3 years; the daily stats and single-row lookups drop to ~0ms.
2. Rewrite date(col) = date(?) predicates as range comparisons (col >= ? AND col < ?) so the indexes get used. Measured: 1-day queries 11-41ms -> ~0ms, topProducts 60ms -> 8ms.
3. Orders list: default limit on the API, cursor pagination, a date range filter, and batch the N+1 into one IN() query per relation so pagination stays cheap.
4. Kitchen/KDS query: replace the OR EXISTS scan with a union over the status index plus a live-items join.

Alternatives considered

Keeping the correlated subqueries on the customers page and only adding the index: 40s -> 0.6ms, fine at 1-2 years. A grouped join is a later refinement if customer counts grow much faster than orders.

Two follow-ups worth doing while touching the reports, separate PR if preferred: "today" is computed in UTC everywhere (date('now'), toISOString().slice(0,10)), so for Argentina/Brazil tenants the daily numbers flip a day at 21:00 local and bill numbers get the wrong date after that hour. The code already has a tenant timezone setting used by the insights bucketing; day boundaries should use it too. Related: orders.updated_at is written as CURRENT_TIMESTAMP on insert but new Date().toISOString() on update, so the column mixes two timestamp formats and any future ORDER BY updated_at or range query on it will sort wrong.

Additional context

All numbers are 3-run best times on synthetic databases seeded from the production schema and indexes, 1 and 3 years at 300 orders/day and ~3 items per order. Benchmark script and EXPLAIN QUERY PLAN output available on request.