Caching

Syntra ODBC uses an embedded local cache between your SQL clients and QuickBooks Desktop. This architecture delivers fast analytical query performance while keeping data fresh through incremental sync.

Architecture Overview

Client (Excel, Power BI, etc.)
    │
    ▼
Syntra Server (port 5433)
    │
    ├──▶ Local Cache (fast reads)
    │
    ├──▶ Live reads (QuickBooks Enterprise)
    │
    └──▶ Live QuickBooks fetch (for writes, and fallback reads)
         │
         ▼
    QuickBooks Desktop

When a SELECT query arrives, Syntra chooses the optimal read path:

1. Local cache: Default. Sub-millisecond reads from the local cache.
2. Live reads: QuickBooks Enterprise feature. Low-latency reads directly from QuickBooks data. See Live Data Reads (Enterprise).
3. Live QuickBooks fetch: Used when QB_MAX_STALENESS = 0 or the cache is stale. Requires QuickBooks Desktop to be running. Used for all write operations on every edition.

Incremental Sync

Rather than re-fetching all data on every poll, Syntra uses incremental sync:

1. On first startup, a full sync populates the local cache with all QuickBooks data.
2. On subsequent polls, only records modified since the last sync timestamp are fetched.
3. Modified records are upserted into the cache, and deleted records are removed.

This approach dramatically reduces the load on QuickBooks and keeps sync times short, typically under a second for small change sets.

Adaptive Polling Tiers

Tables are assigned to polling tiers based on how frequently they tend to change. Each tier has a different polling interval:

Tier	Interval	Typical Tables
Hot	60 seconds	Invoices, Sales Receipts, Payments, Bills
Warm	180 seconds	Customers, Vendors, Items, Employees
Cold	900 seconds	Accounts, Classes, Terms, Sales Tax Codes
Frozen	3600 seconds	Company Info, Preferences, Templates

How Tier Assignment Works

Syntra monitors change frequency for each table and can promote or demote tables between tiers:

• A table with frequent changes gets promoted to a faster tier.
• A table with no recent changes gets demoted to a slower tier.
• You can override tier assignments in config.toml.

Configuring Tiers

In config.toml:

[cache.tiers]
hot_interval = 60
warm_interval = 180
cold_interval = 900
frozen_interval = 3600

[cache.overrides]
# Force a table to a specific tier
customers = "hot"
company_info = "frozen"

Cache Storage

The cache is stored as a single file on disk, typically at:

C:\ProgramData\SyntraODBC\cache.db

Cache Size

The cache file grows as more QuickBooks data is synced. Typical sizes:

• Small company (< 1,000 transactions): 5–20 MB
• Medium company (10,000–50,000 transactions): 50–200 MB
• Large company (100,000+ transactions): 200 MB – 1 GB

Cache Persistence

The cache persists across Syntra restarts. On startup, Syntra validates the cache integrity and resumes incremental sync from where it left off.

Bypassing the Cache

To force a live query that skips the cache:

SET QB_MAX_STALENESS = 0;
SELECT * FROM customers;

To rebuild the entire cache from scratch:

CALL qb_rebuild_all();

See Special Commands for details.

Monitoring Cache Status

Check when each table was last synced:

SELECT table_name, last_sync, row_count, tier
FROM qb_cache_status
ORDER BY last_sync DESC;