Skip to content

Cloud live-orders and addons queries exceed SQLite variable bind limit #1223

Closed

Description

@khaira777

Summary

Follow-up to PR #1201 / Issue #1187 (Cloud live-order snapshot executes N+1 database queries across active orders).

Problem

In main/services/cloud-sync.ts and attachEffectiveAddons in main/db.ts, lookups for order_items, order_item_addons, and order_audit_log generate SQL IN (?, ?, ...) clauses spanning all retrieved IDs in a single query:

const ids = items.map((item) => item.id);
const placeholders = ids.map(() => '?').join(',');
const rows = dbInstance.prepare(`SELECT * FROM order_item_addons WHERE order_item_id IN (${placeholders}) ORDER BY id`).all(...ids);

When selected orders collectively contain many items (reproduced with 164 orders with 200 items each = 32,800 items), the query attempts to bind 32,800 placeholders in a single prepared statement, exceeding SQLite's variable bind limit (SQLITE_MAX_VARIABLE_NUMBER, 32766 or legacy 999) and throwing:
SqliteError: too many SQL variables

Acceptance Criteria

  1. attachEffectiveAddons in main/db.ts and bulk lookups in main/services/cloud-sync.ts chunk large ID lists into safe bounds (e.g. <= 400 or <= 500 items per query) strictly respecting SQLite's variable limits.
  2. Exact snapshot structure, addons attachment, audit versioning, and sanitization are preserved.
  3. Regression test reproduces 164 orders with 200 items each without exceeding SQLite variable count or throwing errors.

Activity

  1. khaira777 commented on Oct 20, 2026

    @khaira777
    ContributorAuthor

    Verified and delivered on main via PR #1224 (1bda4e7781a456ef5dbc1ffcfc6c78e2840b7537).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions