# Calc Server Incremental Change Detection

## Overview

Calculations return ~300,000 rows. Full reload on every browser refresh is too slow. This project adds incremental loading: only changed rows are queried from DB, tracked, and sent to browser as a diff.

## `changeIdx` Format

The unified change tracking format used everywhere — server internal state, server-to-browser wire, browser consumption.

```lua
changeIdx = {
    add_count = 5,          -- total across all tables
    modify_count = 3,
    delete_count = 1,
    [tableName] = {
        add_count = 3,
        modify_count = 1,
        delete_count = 0,
        add    = {[id] = row},     -- hash map: id -> full row, O(1) lookup
        modify = {[id] = row},     -- hash map: id -> full row, O(1) lookup
        delete = [id1, id2, ...]   -- array: just ids, no row data
    }
}
```

- `id` = value of `gridInfo.id_field` per table (`product_id` for product, `record_id` for others)
- `row` = full row data, no extra keys (`change`, `__change`, `calc` — NONE)
- Browser applies by type:
  - `add[id]` -> push to `state.data`
  - `modify[id]` -> find by id, update in place
  - `delete[i]` -> find by id, mark deleted

## Architecture

### Data flow

```mermaid
graph TD
    A[Browser requests data] --> B{grid cached?}
    B -->|no| C[Full load: query all rows]
    B -->|yes| D[Incremental load: query rows where modify_id > last]
    C --> E[Save grid + last_modify_id to loc]
    D --> F[Detect deletes via binary search]
    F --> G[Track changes in changeIdx]
    G --> H{which calc module?}
    H -->|work-center| I[changeIdx = direct row refs from load]
    H -->|product-need| J[changeIdx = setIncrementalChange cache diff]
    I --> K[Send changeIdx to browser]
    J --> K
    K --> L[change-data.js applies add/modify/delete]
    E --> H
```

### Two incremental strategies

| Module | Strategy | Why |
|--------|----------|-----|
| `work-center.lua` | `changeIdx` direct row references from load | Output rows ARE input grid rows — no recalculation |
| `product-need.lua` | `changeData.setIncrementalChange()` cache comparison | Output rows are CALCULATED (balance, need_down) — must compare new result vs cached result |

### Full load vs incremental load

| Condition | Behavior |
|-----------|----------|
| `loc.grid[orgId]` is nil | First load after restart — full load, `param.full_load = true` |
| `loc.grid[orgId].loaded` is empty | Grid initialized but no data — full load, `param.full_load = true` |
| `param.rec.reload_data = true` | User forced reload — full load |
| `param.full_load = true` | Guarantees: no `changeIdx` tracking, no `last_modify_id` filter, no delete detection |
| Grid cached + `reload_data = false` | Incremental load — query only changed rows |

## Bugs Found and Fixed

### Bug 1: Incremental duplicate discard (CRITICAL)

**File**: `nc-calc-server-load-data.lua` `queryData()` row merge logic

Modified rows from primary DB fell through to `duplicate = duplicate + 1` branch — silently discarded instead of updated. The `elseif queryDbId` branch only handled secondary local DB, not primary DB incremental.

**Fix**: Added `elseif isIncremental then` branch that calls `util.recToRec(gridIdx[val], item)` for primary DB incremental updates.

### Bug 2: `modifyRow` nil access in delete detection

Delete detection ran when `changeIdx` was nil (direct `queryData` calls). Crashed on `changeIdx.delete[tableName]` access.

**Fix**: Added `if changeIdx then` guard around delete tracking.

### Bug 3: `findDeletedByBinarySearch` not exposed for testing

Function is local in load-data, tests expected it on `calc.test`.

**Fix**: Added `loadData.test = {findDeletedByBinarySearch = findDeletedByBinarySearch}`, exposed through `calc.test`.

### Bug 4: product-need output vs input mismatch (design)

Initially used `changeIdx` (input row refs) for product-need. WRONG — product-need OUTPUT tables are calculated results, not the same objects as input rows.

**Fix**: Product-need keeps `setIncrementalChange()` cache comparison. Only work-center uses direct `changeIdx` refs.

### Bug 5: `id_field` vs `record_id` confusion

Product table uses `product_id` as `id_field`, not `record_id`. Tests assumed `record_id` everywhere.

**Fix**: All code uses `gridInfo.id_field` which resolves correctly per table.

### Bug 6: nc-production.js stale indexes after incremental

`update()` called `nc.incrementalUpdate()` but returned early without rebuilding local indexes (`productIdx`, `gridData`, etc.).

**Fix**: Added `rebuildAfterIncremental()` that rebuilds all local indexes from updated `state.data`.

### Bug 7: Inconsistent change type values

`change-data.lua` used `"modify"`, `"new"`, `"deleted"`. `change-data.js` used `"new"`, `"change"`, `"delete"`. `nats-grid.lua` used `__change` field.

**Fix**: Unified globally: `"add"`, `"modify"`, `"delete"`, field name `change` (no `__` prefix).

### Bug 8: No cache reset in change-data.lua

Module-level cache persisted between test runs. No way to reset.

**Fix**: Added `changeData.clearCache()`.

### Bug 9: Flat vs nested `lastModifyId` structure

Tests used flat `lastModifyId.PRODUCT = "value"` but code uses nested `lastModifyId[tableName][from_db] = value`.

**Fix**: Updated all tests to nested format.

### Bug 10: No cache setup for modify tests

Modify detection tests expected cached items to exist without populating cache first.

**Fix**: Added `clearCache()` at test start, pre-populate cache for modify tests.

**Fix (3 files)**:

| File | Change |
|------|--------|
| `calc.lua:451` | Grid reuse: `next(loc.grid[orgId].loaded)` check — empty grid never treated as reusable |
| `calc.lua:454` | Set `param.full_load = true` on fresh grid |
| `load-data.lua:233` | `isIncremental` guard: `param.full_load ~= true` |
| `load-data.lua:253-254` | Clear `last_modify_id`/`modify_id_field` from `queryParameter` before each call |
| `load-data.lua:255` | Only set them when `param.full_load ~= true` |
| `load-data.lua:292` | Delete detection guard: `param.full_load ~= true` |

### Bug 12: json_data false positive changes

`getFieldDiffArray()` compared table values with `~=` (reference equality). `json_data` parsed fresh on every query -> always different reference -> always reported as "modify".

**Fix**: Added `tableEqual()` recursive deep comparison for table values.

### Bug 13: Delete entries lacked `product_id`

**File**: `nc-calc-server-load-data.lua:307`

Delete entry was `{record_id = id}` — no `product_id`. For `production_order`, `order_row` tables, `markChangedProduct` could not find which product to recalculate when rows were deleted.

**Fix**: Include `product_id = item.product_id` in delete entries when available.

### Bug 14: `markChangedProduct` did not iterate deletes for production_order, order_row

**File**: `product-need.lua:134-148`

Only `product` table had delete iteration. `production_order`, `order_row-sales`, `order_row-purchase` only iterated `add` and `modify`. Deleting a production_order or sales order did NOT mark the product for recalculation.

**Fix**: Added delete iteration for all three tables.

### Bug 15: `setIncrementalChange` did not detect disappeared calculated rows

**File**: `change-data.lua:233-273`

`iterItems` processed only items IN the new data. Cached items that DISAPPEARED (e.g. production order deleted -> product_need row gone from output) were never detected as deletes. Old rows stayed in cache forever -> stale data on frontend.

**Fix**: After `iterItems`, track `seenRecordIds` and `seenProductIds`. Scan cache for items whose `product_id` was recalculated but whose `record_id` is not in the new data -> create delete entries and clean cache.

### Bug 16: `changedProductIdx` missed downstream material products

**File**: `product-need.lua:1278-1283`

`changedProductIdx` was rebuilt from the original `markChangedProduct` result, which only included products with direct input data changes. Downstream materials recalculated via `addLowerLevelNeed` cascade were NOT included -> their results not sent to frontend.

**Fix**: Save `prevBalancePassCountIdx` before calculation. After all loops, rebuild `changedProductIdx` from products with increased `balance_pass_count`.

## Key Design Decisions

| Decision | Rationale |
|----------|-----------|
| `changeIdx` as module-local | Shared state accessible from all calc modules via `getChangeIdx()` |
| Row references (not clones) in `changeIdx` | Zero memory overhead, direct pointer to grid data |
| No `__` fields on data rows | Clean separation of tracking from data |
| `param.full_load` flag | One boolean guards all incremental logic — clear intent, no ambiguity |
| `next(loaded)` check for grid reuse | Empty grid `{loaded = {}}` is not a valid cache |
| Keep `setIncrementalChange` for product-need | Output rows are calculated, not same as input rows |
| `id_field` per table | Different tables use different primary keys (`product_id` vs `record_id`) |

## Key Findings

### Finding 1: `id_field` varies per table

`gridInfo.id_field` is set by `dschema.primaryKeyField` and is NOT always `record_id`:

- Product table: `product_id`
- Other tables: may use `record_id`

All code must use `gridInfo.id_field` or `getIdField()` — never assume `record_id`.

### Finding 2: `queryParameter` is shared across `queryData` calls

`queryParameter` is the same object reused for all queries in a load cycle. Fields like `last_modify_id` and `modify_id_field` persist from one call to the next. Must be cleared before each call to prevent stale values from leaking between tables.

### Finding 3: `loc.tableStat` is shared state

`loc.tableStat` persists across `queryData` calls. Tests calling `queryData` directly must clear `loc.tableStat` between tests. The `loadData()` function in calc.lua handles this automatically.

### Finding 4: Delete detection requires `last_modify_id`

`findDeletedByBinarySearch` only runs when `last_modify_id ~= ""`. On first full load, `last_modify_id` is empty -> delete detection skipped. Correct: first load assumes all data is fresh.

### Finding 5: change-data.lua has two cache systems

1. `dataCache`/`dataCacheIdx` — per-table cache indexed by `record_id`
2. `dataCacheByFilter`/`dataCacheByFilterIdx` — per-table per-filter cache

Both are in-memory only, lost on server restart. Full load rebuilds them.

## Files Modified

| File | Change | Status |
|------|--------|--------|
| `nc-calc-server-load-data.lua` | `changeIdx` tracking, incremental duplicate fix, `full_load` guard, parameter cleanup, expose test API | [x] done |
| `nc-calc-server-calc.lua` | Pass `changeIdx` via `gridIdx`, `next(loaded)` grid reuse check, `param.full_load` flag, expose test API | [x] done |
| `product-need.lua` | `changeArr` -> `changeIdx`, `from_row` as `"productId:productRow"` string, no flatten, `renumberData` sets `product_row`, keeps `setIncrementalChange()` | [x] done |
| `nc-calc-server-work-center.lua` | Uses `changeIdx` for `change_idx` building | [x] done |
| `change-data.js` | Full row update on "modify", unified change types | [x] done |
| `change-data.lua` | Unified change types, `clearCache()`, `tableEqual()` deep comparison | [x] done |
| `change-data-test.lua` | Nested `lastModifyId`, unified change types, `clearCache()`, json_data tests | [x] done |
| `nc-production.js` | `rebuildAfterIncremental()` for index rebuild, flatten+sort+resolve `from_row` string+set flags | [x] done |
| `nc-calc-server-load-data-test.lua` | 13 tests, clean names, delete detection, full load guards | [x] done |
| `nc-calc-server-test.lua` | Expose `findDeletedByBinarySearch` for binary search tests | [x] done |
| `nc-production-incremental.test.js` | 15 new tests for production incremental | [x] done |
| `nats-grid.lua` | `__change` -> `change`, `"new"` -> `"add"`, `"change"` -> `"modify"` | [x] done |
| `nats-grid.js` | Updated to new change type names | [x] done |
| `nats-grid.md` | Updated examples | [x] done |
| `nats-grid-test.lua` | Updated all change types | [x] done |
| `nats-test.lua` | Updated all change types | [x] done |
| `nats-grid.test.js` | Updated all change types | [x] done |
| `calc-server-plan.md` | This file | [x] done |
| `constant.json` | Added `reload_default: 0` | [x] done |
| `nc-work-center.js` | Added `rec.constant?.reload_default === 2`, `reload_default` checkbox init | [x] done |
| `nc-work-center.json` | Added Reload checkbox, update button uses `rec.constant?.reload_default === 2` | [x] done |
| `nc-production.js` | Added `rec.constant?.reload_default === 2`, `reload_default` checkbox init | [x] done |
| `nc-production.json` | Update button uses `rec.constant?.reload_default === 2` | [x] done |
| `nc-production/rec.json` | Added `constant`, `reload_data` fields | [x] done |
| `nc-production/init.json` | Added constant include | [x] done |
| `nc-load.js` | Added `rec.constant?.reload_default === 2`, `reload_default` checkbox init | [x] done |
| `nc-load.json` | Update button uses `rec.constant?.reload_default === 2` | [x] done |
| `nc-load/rec.json` | Added `constant`, `reload_data`, `incremental_update`, `old_work_date` fields | [x] done |
| `nc-load/init.json` | Added constant include | [x] done |
| `nc-load/hdr.json` | Added `reload_data` label | [x] done |

---

## Phase 1b: Update Button Reload Behavior

### `reload_default` constant

Controls how the Update button and Reload checkbox behave across all calc-server screens. Value in `plugin/nc-calc-server/constant.json`:

| Value | Reload checkbox default | Update button behavior |
|-------|------------------------|----------------------|
| `0` | OFF | Respects checkbox |
| `1` | ON | Respects checkbox |
| `2` | OFF | Always forces reload regardless of checkbox |

### Per-screen behavior

All three screens (work-center, production, nc-load) use the same pattern:

1. `onMounted`: if `reload_default === 1`, set `rec.reload_data = true`
2. `rec.constant?.reload_default === 2`: if `reload_default === 2`, force reload; otherwise use `rec.reload_data`
3. Update button calls `rec.constant?.reload_default === 2` instead of forcing reload directly
4. Reload checkbox bound to `rec.reload_data`
5. Other actions (create_schedule, virtual_production_order, tab change) still force reload directly

### Data flow

```mermaid
flowchart TD
    A["User clicks Update button"] --> B["rec.constant?.reload_default === 2"]
    B --> C{"reload_default == 2?"}
    C -- "yes" --> D["force reload_data = true"]
    C -- "no" --> E["use rec.reload_data<br/>(checkbox value)"]
    D --> F["server call"]
    E --> F
    F --> G{"was reload forced?"}
    G -- "yes" --> H["restore rec.reload_data"]
    G -- "no" --> I["checkbox unchanged"]
```

## Test Results

All tests pass:

| Test Suite | Pass | Fail |
|------------|------|------|
| nc-calc-server-load-data-test.lua | 13 | 0 |
| nc-calc-server-calc-test.lua | 13 | 0 |
| change-data.test.js | 41 | 0 |
| change-data-test.lua | 33 | 0 |
| nc-work-center-incremental.test.js | 30 | 0 |
| nc-work-center-incremental-integration.test.js | 5 | 0 |
| nc-production-incremental.test.js | 15 | 0 |
| nats-grid-test.lua | 30 | 0 |
| nats-test.lua | 74 | 0 |
| nats-grid.test.js | 38 | 0 |
| All other JS tests | 194 | 0 |
| **Total** | **478** | **0** |

### How to run all tests

```bash
# Lua tests
lj nc-plugin/plugin/nc-calc-server/nc-calc-server-load-data-test.lua
lj nc-server/test/plugin/nc-calc-server-calc-test.lua
lj nc-server/test/lib/change-data-test.lua        # run from nc-server/
lj nc-server/test/lib/nats-grid-test.lua
lj nc-server/test/lib/nats-test.lua

# JS tests
cd nc-frontend && bun vitest run
```

---

## Phase 2: SQLite Zstd Cache for Initial Data Load

### Goal

Speed up initial data load by saving fixed+sorted grid data to a local SQLite database file, compressed with zstd. On next startup, load from SQLite cache instead of querying PostgreSQL from scratch, then apply only incremental changes.

### Constraints

- **SQLite file only** — one file per org, encrypted on disk, easy to copy, safe
- **NO new PostgreSQL tables** — use only existing tables
- **Controlled by `use_calc_server_cache`** constant in `plugin/nc-calc-server/constant.json` — `0` = disabled, `> 0` = enabled
- **Must check** `if loc.constant.use_calc_server_cache > 0` before any cache operation
- **First version (V1) = synchronous** — coroutine optimization planned for V2 (when `> 1`)
- **All cache operations** must include timing and print markdown performance table using `markdown.printArrayAsTable()`

### Architecture

#### Storage

Uses existing `result_cache` PostgreSQL table — same pattern as `result_cache.lua`:

- Compressed data stored as base64 in `json_data` field
- One record per table per org: key = `calc-server-cache:{orgId}:data:{tableName}`
- Metadata records: key = `calc-server-cache:{orgId}:meta:{key}`

No new tables, no SQLite. Uses `dqry`/`dload`/`dsave` — same functions as normal code.

#### Compression

Zstd level 3 via `compress/zstd`. Each table's data array is:

1. Serialized to JSON via `json.toJsonRaw()`
2. Compressed with `zstd:compress()`
3. Base64 encoded via `base64.encode()`
4. Stored in `result_cache.json_data`

#### Data flow

```mermaid
graph TD
    A[loadData called] --> B{cache exists for orgId?}
    B -->|no| C[Full PostgreSQL query - normal flow]
    C --> D[fix + sort + calculate]
    D --> E{use_calc_server_cache > 0?}
    E -->|yes| F[Serialize grid tables to JSON]
    F --> G[Compress each with zstd]
    G --> H[Save blobs + metadata to SQLite]
    E -->|no| I[Done - no cache save]

    B -->|yes| J[Read metadata from SQLite - fast, no decompress]
    J --> K[Load + decompress all grid_data blobs]
    K --> L[Parse JSON -> grid tables]
    L --> M[Set last_modify_id from cached metadata]
    M --> N[Query PostgreSQL for incremental changes only]
    N --> O[Merge incremental into grid]
    O --> D
```

#### What gets cached

Entire grid after `fixData()` and sorting — all tables listed in `setTableArray()`:

- `product`, `product_work-schedule`, `product_work-estimated`, `product_work-actual`
- `product_material-estimated`, `product_material-actual`
- `order-sales`, `order-purchase`, `order_row-sales`, `order_row-purchase`
- `production_order`, `stock-product`, `person-employee`
- `company-customer`, `company-supplier`, `resource-load_group`
- `resource_efficiency`, `capacity`, `attachment`

NOT cached (rebuilt from grid data):

- `grid.info` — rebuilt from stat
- `gridIdx` — rebuilt by `calculateData()` and indexing code in `loadData()`
- Calculated capacity data — rebuilt by `capacity.calculate()` and `load.calculate()`

#### Save timing

Save happens AFTER `calculateData()` completes, at the end of `loadData()` in `nc-calc-server-calc.lua`. The cached data includes:

- Raw grid tables (post-fix, post-sort)
- `grid.info.start_time`, `grid.info.end_time` (set by calculateData)
- `last_modify_id` per table per db
- `tableStat` per table
- `stat` totals

#### Load timing

At the START of `loadData()`, BEFORE the query loop. If cache exists:

1. Open SQLite, read `grid_meta` for metadata
2. Read + decompress all `grid_data` blobs
3. Populate `grid` directly — skip SQL queries for all cached tables
4. Set `param.last_modify_id` from cached metadata
5. Set `loc.tableStat` from cached metadata
6. Continue with incremental SQL queries (only changed rows)

### Implementation Plan

#### Files to create

| File | Purpose |
|------|---------|
| `nc-plugin/plugin/nc-calc-server/nc-calc-server-cache.lua` | SQLite cache module: open, save, load, close |

#### Files to modify

| File | Change |
|------|--------|
| `nc-plugin/plugin/nc-calc-server/constant.json` | Add `"use_calc_server_cache": 0` |
| `nc-plugin/plugin/nc-calc-server/nc-calc-server-calc.lua` | Call cache load at start, cache save at end of `loadData()` |

#### New module: `nc-calc-server-cache.lua`

```lua
-- API:
cache.saveGrid(orgId, grid, lastModifyId, tableStat, stat, param)  -- save all tables to result_cache
cache.loadGrid(orgId, param)       -- load all tables from result_cache
cache.cacheExists(orgId)           -- check if cache exists
cache.close()                      -- no-op (no connection to close)
```

Key implementation details:

- Uses `dqry`/`dload`/`dsave` — same functions as normal code, same as `result_cache.lua`
- `json.toJsonRaw(tbl.data)` serializes each table's data array
- `zstd:compress()` compresses, `base64.encode()` for binary-safe storage in jsonb
- `dqry.query("", "rc.name_id", "=", key)` + `dsave.saveToDatabase()` for writes
- `dqry.query("", "rc.name_id", "=", key)` + `dload.selectionToRecordArray()` for reads
- Key format: `calc-server-cache:{orgId}:data:{tableName}` for data, `calc-server-cache:{orgId}:meta:{key}` for metadata
- Metadata keys: `table_names`, `last_modify_id`, `table_stat`, `stat`, `save_time`

#### Changes to `nc-calc-server-calc.lua`

In `loadData()` function:

**Load from cache** (before query loop, ~line 450):

```lua
local cachedGrid
if loc.constant.use_calc_server_cache > 0 and param.full_load then
    cachedGrid = cache.loadGrid(orgId)
end
if cachedGrid then
    grid = cachedGrid.grid
    param.last_modify_id = cachedGrid.last_modify_id
    loc.tableStat = cachedGrid.tableStat
    for tblName, _ in pairs(grid) do
        if type(grid[tblName]) == "table" and grid[tblName].data then
            grid.loaded[tblName] = #grid[tblName].data
        end
    end
    param.full_load = false
end
```

**Save to cache** (after calculateData, before return, ~line 598):

```lua
if loc.constant.use_calc_server_cache > 0 and param.full_load then
    cache.saveGrid(orgId, grid, param.rec.last_modify_id, loc.tableStat, stat)
end
```

#### Changes to `constant.json`

Add field:

```json
"use_calc_server_cache": 0
```

### Performance Estimates

Based on benchmark data from `compress-test-result.txt`:

| Metric | Estimate |
|--------|----------|
| Grid data size (300K rows) | ~50-150 MB JSON |
| Zstd level 3 compression | ~15-45 MB (28% ratio) |
| Compression speed | ~400 MB/s -> ~0.1-0.4 sec |
| Decompression speed | ~1300 MB/s -> ~0.01-0.03 sec |
| SQLite BLOB read | ~35% faster than filesystem |
| Full PostgreSQL query | ~5-15 sec |
| Cache load (decompress + parse) | ~0.5-2 sec |
| **Speedup** | **~3-10x faster initial load** |

### V2 Plan: Coroutine-based Parallel Load (NOT implemented in V1)

When `use_calc_server_cache > 1`:

1. Start coroutine to load + decompress SQLite cache in background
2. Main thread immediately queries PostgreSQL for metadata (last_modify_id per table) — fast, only reads modify_id values
3. Coroutine completes — grid populated from cache
4. Main thread queries only rows where `modify_id > cached_last_modify_id` — the incremental changes
5. Merge incremental changes into cached grid

This overlaps SQLite I/O + decompression with the PostgreSQL metadata query, saving ~30-50% of load time.

Implementation would use `coro.createSocketCoroutine()` or raw `coroutine.wrap()` with `coro.yieldFunction()` for yielding during decompression.

### Performance Reporting

All cache operations must include timing and print a markdown table using `markdown.printArrayAsTable()`. Raw values in data, format in header definition:

```lua
local header = {
    {tag = "metric", show = "Metric", align = "left"},
    {tag = "total", show = "Total", align = "right", format = "%.0f"},
    {tag = "rate", show = "Rate", align = "right"}
}
```

#### Save performance table

```lua
local data = {
    {metric = "Tables saved", total = tableCount},
    {metric = "Rows saved", total = rowCount},
    {metric = "JSON size", total = jsonSize, rate = jsonSize / rowCount .. " bytes/row"},
    {metric = "Compressed size", total = compressedSize, rate = compressedSize / jsonSize * 100 .. "%"},
    {metric = "Compression time", total = compressTime, rate = jsonSize / compressTime / 1048576},
    {metric = "SQLite write time", total = writeTime, rate = compressedSize / writeTime / 1048576},
    {metric = "Total save time", total = totalTime}
}
local header = {
    {tag = "metric", show = "Metric", align = "left"},
    {tag = "total", show = "Total", align = "right", format = "%.2by"},
    {tag = "rate", show = "Rate", align = "right"}
}
markdown.printArrayAsTable(data, header, "Calc Server Cache Save")
```

Byte size columns use format `"%.2by"` — the special byte format that auto-scales (KB, MB, GB).

#### Load performance table

```lua
local data = {
    {metric = "Tables loaded", total = tableCount},
    {metric = "Rows loaded", total = rowCount},
    {metric = "Compressed size", total = compressedSize},
    {metric = "SQLite read time", total = readTime},
    {metric = "Decompression time", total = decompressTime, rate = compressedSize / decompressTime / 1048576},
    {metric = "JSON parse time", total = parseTime},
    {metric = "Total load time", total = totalTime}
}
local header = {
    {tag = "metric", show = "Metric", align = "left"},
    {tag = "total", show = "Total", align = "right", format = "%.2by"},
    {tag = "rate", show = "Rate", align = "right"}
}
markdown.printArrayAsTable(data, header, "Calc Server Cache Load")
```

#### Incremental change after cache load

```lua
local data = {
    {metric = "Cached tables", total = cachedTableCount},
    {metric = "Incremental tables changed", total = changedTableCount},
    {metric = "Incremental rows added", total = addCount},
    {metric = "Incremental rows modified", total = modifyCount},
    {metric = "Incremental rows deleted", total = deleteCount},
    {metric = "Incremental query time", total = incrementalTime},
    {metric = "Total load time (cache + incremental)", total = totalTime}
}
local header = {
    {tag = "metric", show = "Metric", align = "left"},
    {tag = "total", show = "Total", align = "right", format = "%.0f"},
    {tag = "rate", show = "Rate", align = "right"}
}
markdown.printArrayAsTable(data, header, "Calc Server Incremental Load")
```

### Test Plan

#### New test file: `nc-calc-server-cache-test.lua`

| Test | What it verifies |
|------|-----------------|
| `cacheSaveAndLoad` | Round-trip: save grid, load back, data matches |
| `cacheMetadata` | last_modify_id and tableStat preserved |
| `cacheMultipleTable` | All table types cached correctly |
| `cacheOverwrite` | Second save overwrites first |
| `cacheNoCrashOnMissing` | loadGrid returns nil if no cache exists |
| `cacheEmptyTable` | Tables with 0 rows are not cached |

#### Existing tests

All 478 existing tests must continue to pass. Cache is disabled when `use_calc_server_cache = 0` (default).

### Open Questions

- [x] Storage — result_cache table with dsave/dload/dqry (no SQLite, no new tables)
- [x] Encryption — PostgreSQL connection handles security
- [ ] V2 SQLite local cache for faster load (when `use_calc_server_cache > 1`)
- [ ] Zstd level 3 (current) vs level 1 (faster)?

### Test names (nc-calc-server-load-data-test.lua)

| Test | What it verifies |
|------|-----------------|
| `init` | Shared state setup |
| `fullLoad` | Full load returns grid data, sets `last_modify_id` |
| `incrementalLoad` | Incremental load adds new rows to existing grid |
| `setCreateSchedule` | Flag setter |
| `fullLoadThenIncremental` | Full load anchors `last_modify_id`, incremental uses it to filter |
| `changeIdxAdd` | `changeIdx.add` tracks new rows |
| `changeIdxModify` | `changeIdx.modify` tracks updated rows, grid data updated |
| `fullLoadNoChangeIdx` | `changeIdx` is nil on full load |
| `noUnderscoreFields` | No `__` fields on any grid row |
| `changeIdxDelete` | Binary search detects deleted row, `changeIdx.delete` tracks it |
| `changeIdxMultiDelete` | Multiple deletions detected and tracked correctly |
| `changeIdxMixed` | Add + modify + delete in same incremental load |
| `noChangesNoFalsePositive` | Incremental load with no DB changes produces empty `changeIdx` |

---

## Phase 1c: Full Code Path Trace

### Question 1: Does non-full reload query only changed/new/deleted?

**YES.** The query uses `last_modify_id` to filter.

```mermaid
sequenceDiagram
    participant FE as Frontend
    participant BE as Backend (calc-server)
    participant DB as PostgreSQL

    FE->>BE: Request with last_modify_id
    BE->>BE: loadData() checks reload_data
    alt reload_data = false AND grid cached
        BE->>BE: queryData() sets last_modify_id on query parameter
        BE->>DB: SELECT ... WHERE modify_id > last_modify_id
        DB-->>BE: Only rows changed since last_modify_id
        BE->>BE: New rows: gridIdx[id] == nil → changeIdx.add
        BE->>BE: Modified rows: gridIdx[id] exists → recToRec + changeIdx.modify
        BE->>DB: COUNT(record_id) WHERE record_id IN (cached_ids)
        DB-->>BE: Count mismatch → deleted rows
        BE->>BE: Deleted rows: changeIdx.delete
    else reload_data = true OR first load
        BE->>DB: SELECT ... (full query, no modify_id filter)
        DB-->>BE: All rows
        BE->>BE: changeIdx = nil (no tracking)
    end
```

**Key code locations:**

| Step | File | Lines | What |
|------|------|-------|------|
| Send `last_modify_id` | `nc-work-center.js` | 826 | `last_modify_id: rec.last_modify_id` in callParameter |
| Send `last_modify_id` | `nc-production.js` | 1262 | `incremental_update: rec.incremental_update` in parameter |
| Send `last_modify_id` | `nc-load.js` | 28 | `reload_data: action === 'update'` or `rec.reload_data` |
| Decide incremental | `nc-calc-server-calc.lua` | 452 | `grid = ... reload_data ~= true ... and loc.grid[orgId]` |
| Set query filter | `nc-calc-server-load-data.lua` | 255-262 | `queryParam.parameter.last_modify_id = ...` |
| Detect new rows | `nc-calc-server-load-data.lua` | 357-368 | `gridIdx[val] == nil` → add |
| Detect modified rows | `nc-calc-server-load-data.lua` | 370-377 | `isIncremental` → recToRec + modify |
| Detect deleted rows | `nc-calc-server-load-data.lua` | 292-319 | Binary search COUNT vs cached IDs |

### Question 2: How does it handle deleted in all calculations?

**Two strategies:**

#### Strategy A: work-center — direct `changeIdx` from load

1. `findDeletedByBinarySearch()` in `nc-calc-server-load-data.lua:90-149`:
   - Takes all cached `record_id`s for a table
   - Batches them (1000 per batch)
   - Queries `COUNT(*) WHERE record_id IN (batch)`
   - If count < batch size → binary search to find exactly which IDs are missing
   - Missing IDs → `changeIdx[tableName].delete[id]`
   - Also removes from `gridRec.data` and adds to `loc.deletedArray[orgId]`

2. Work-center receives `gridIdx.changeIdx` (which includes `.delete` entries)
3. Sends directly to frontend as `param.change_idx`

#### Strategy B: product-need — `setIncrementalChange()` cache diff

1. Same binary search detection during load → `loc.deletedArray[orgId]`
2. `gridIdx.deleted` receives the array
3. `changeData.setIncrementalChange()` receives `deletedRecordArr` parameter (`gridIdx.deleted`)
4. Creates `changeIdx[tableName].delete[record_id]` entries from `deletedRecordArr`

**Delete flow in detail:**

```mermaid
graph TD
    A[Binary search finds deleted record_ids] --> B[Remove from gridRec.data]
    B --> C[Add to loc.deletedArray]
    C --> D[Track in changeIdx.tableName.delete]
    D --> E{Which module?}
    E -->|work-center| F[Send changeIdx directly]
    E -->|product-need| G[Pass gridIdx.deleted to setIncrementalChange]
    G --> H[setIncrementalChange creates delete entries]
    F --> I[Frontend applyChangeArr marks calc.change='delete']
    H --> I
```

### Question 3: How does it send ONLY changed messages?

**Two strategies:**

#### Work-center: Sends `change_idx` instead of `data`

`nc-calc-server-work-center.lua:797-825`:

```lua
if rec.incremental_update and next(lastModifyId) and rec.reload_data ~= true then
    param.change_idx = serverChangeIdx   -- only changes
    param.rec = {last_modify_id = ...}   -- updated tracking
    -- param.data is NOT set
else
    param.data = data                    -- full data
    param.rec = rec
end
```

When incremental: `param.data` is nil → only `param.change_idx` sent.

#### Product-need: `setIncrementalChange()` diffs against cache

`product-need.lua:1738-1756`:

```lua
if rec.incremental_update and next(lastModifyId) and rec.reload_data ~= true then
    local productNeedForChange = need.changedProductIdx or calcProductIdx
    local data = {product_need = productNeedForChange, ...}
    changeIdx = changeData.setIncrementalChange(data, lastModifyId, gridIdx.deleted)
    resultCache[orgId].change_idx = changeIdx
    resultCache[orgId].grid = nil       -- no grid data
else
    rec.last_modify_id = changeData.lastModifyId(...)
end
```

When incremental: `resultCache[orgId].grid = nil` → no grid data, only `change_idx`.

#### Frontend applies changes

`change-data.js:applyChangeArr()`:

- **delete**: Marks `item.calc.change = 'delete'`
- **modify**: Updates fields in-place, marks `item.calc.change = 'modify'`
- **add**: Inserts at correct position, marks `item.calc.change = 'add'`

### Full end-to-end trace: work-center Update button

```mermaid
sequenceDiagram
    participant User
    participant WC as nc-work-center.js
    participant CD as change-data.js
    participant Srv as nc-calc-server-work-center.lua
    participant LD as nc-calc-server-load-data.lua
    participant PN as product-need.lua
    participant FE as change-data.js (apply)

    User->>WC: Click Update button
    WC->>WC: rec.constant?.reload_default === 2
    WC->>WC: if reload_default===2: force reload
    WC->>WC: callParameter(reload, rowRec)
    Note over WC: Sets last_modify_id, reload_data, incremental_update
    WC->>Srv: calc/work-center {rec: {last_modify_id, reload_data, ...}}

    alt reload_data = true (full reload)
        Srv->>LD: loadData() with full_load=true
        LD-->>Srv: grid (all rows), changeIdx=nil
        Srv->>PN: calc.productNeed() - full calculation
        PN-->>Srv: result with grid + data
        Srv-->>WC: {data: full data, rec: {last_modify_id}}
        WC->>CD: incrementalUpdate(state, ret)
        Note over CD: wasIncremental=false, updates full data
    else reload_data = false (incremental)
        Srv->>LD: loadData() with last_modify_id filter
        LD->>LD: Query only WHERE modify_id > last_modify_id
        LD->>LD: Detect deletes via binary search
        LD-->>Srv: grid (merged), changeIdx with add/modify/delete
        Srv->>PN: calc.productNeed() with changeIdx
        PN->>PN: resetAndMarkChanged() - only recalc changed products
        PN->>PN: setIncrementalChange() - diff result vs cache
        PN-->>Srv: result with change_idx, grid=nil
        Srv->>Srv: param.change_idx = serverChangeIdx
        Srv-->>WC: {change_idx: {add/modify/delete}, rec: {last_modify_id}}
        WC->>CD: incrementalUpdate(state, ret)
        CD->>FE: applyChangeArr(state, change_idx)
        Note over FE: Applies add/modify/delete to grid data
    end
```

### Full end-to-end trace: production Update button

```mermaid
sequenceDiagram
    participant User
    participant PR as nc-production.js
    participant CD as change-data.js
    participant Srv as nc-calc-server-calc.lua (defaultCalc)
    participant LD as nc-calc-server-load-data.lua
    participant PN as product-need.lua
    participant FE as change-data.js (apply)

    User->>PR: Click Update button
    PR->>PR: rec.constant?.reload_default === 2
    PR->>Srv: calc/product-need {parameter: {reload_data, incremental_update, ...}}

    alt reload_data = true (full reload)
        Srv->>LD: loadData() with full_load=true
        LD-->>Srv: grid (all rows), changeIdx=nil
        Srv->>PN: productNeed.calculate() - full calculation
        PN->>PN: All products do_calc=1
        PN-->>Srv: {grid: columns, data: {product_need: all}, rec: {last_modify_id}}
        PR->>CD: incrementalUpdate(state, ret)
        Note over CD: wasIncremental=false
        PR->>PR: updateData(ret) - flatten + display
    else reload_data = false (incremental)
        Srv->>LD: loadData() with last_modify_id filter
        LD-->>Srv: grid (merged), changeIdx tracked
        Srv->>PN: productNeed.calculate()
        PN->>PN: resetAndMarkChanged() - mark only changed products
        PN->>PN: runCallback(calculateBalance) - only do_calc=1 products
        PN->>PN: Need down: addLowerLevelNeed sets do_calc=1 on materials
        PN->>PN: setIncrementalChange() diff changedProductIdx vs cache
        PN-->>Srv: {change_idx: ..., grid: nil, data: nil}
        PR->>CD: incrementalUpdate(state, ret)
        CD->>FE: applyChangeArr(state, change_idx)
        PR->>PR: rebuildAfterIncremental()
    end
```

### Full end-to-end trace: nc-load Update button

```mermaid
sequenceDiagram
    participant User
    participant NL as nc-load.js
    participant Srv as capacity/load
    participant LD as nc-calc-server-load-data.lua
    participant FE as change-data.js (apply)

    User->>NL: Click Update button
    NL->>NL: rec.constant?.reload_default === 2
    NL->>NL: if reload_default===2: action='update' else action='reload'
    NL->>Srv: calc/loading {parameter: {reload_data, ...}}

    alt reload_data = true (action='update')
        Srv->>LD: loadData() with full_load=true
        LD-->>Srv: grid (all rows)
        Srv-->>NL: {grid: full data}
        Note over NL: Full data replaces gantt_data
    else reload_data = false (action='reload')
        Srv->>LD: loadData() with last_modify_id filter
        LD-->>Srv: grid (merged)
        Srv-->>NL: {grid: full grid (not change_idx)}
        Note over NL: nc-load does NOT use change_idx
    end
```

**Note**: nc-load does NOT have incremental `change_idx` support — it always receives full grid data from the server and replaces `rec.gantt_data`. The `reload_data` flag controls whether the server queries all rows or only changed rows, but the response is always full grid.

### BUG FOUND: `changedProductIdx` misses downstream material products

**Severity**: HIGH — material-level need changes silently lost on incremental update

**Root cause**: `markChangedProduct()` only marks products based on INPUT data changes (product, production_order, order_row changes). It does NOT mark downstream material products that get recalculated because of need_down propagation.

**Trace**:

1. `resetAndMarkChanged()` at `product-need.lua:152-157`:
   - Sets ALL products' `do_calc = 0`
   - Marks only input-changed products' `do_calc = 1`
   - Returns `changedIdx` with only input-changed products

2. `addLowerLevelNeed()` at `product-need.lua:489`:
   - When product X has need_down → material Y gets `do_calc = 1`
   - BUT material Y is NOT added to `changedIdx`

3. `changedProductIdx` at `product-need.lua:1278-1283`:
   - Rebuilt from original `changedIdx` — missing material products

4. `setIncrementalChange()` at `product-need.lua:1740`:
   - Uses `changedProductIdx` (not `calcProductIdx`)
   - Material products' results NOT diffed → NOT sent to frontend

**Example scenario**:

- Sales order for product A changes amount
- Product A is marked changed → recalculated correctly
- Product A has need_down → material B gets new rows
- Material B is recalculated (do_calc=1) but NOT in changedProductIdx
- Material B's updated balance/need_down NOT sent to browser
- Browser shows stale data for material B

**Fix**: After all calculation loops, rebuild `changedProductIdx` to include ALL products that were actually recalculated. Use `balance_pass_count` tracking:

```lua
-- After calcArrayStats(), before building changedProductIdx
if serverChangeIdx then
    local recalculatedIdx = {}
    for productId, productArray in pairs(calcProductIdx) do
        if productArray[1].balance_pass_count > (prevBalancePassCountIdx[productId] or 0) then
            recalculatedIdx[productId] = true
        end
    end
    changedProductIdx = recalculatedIdx
end
```

This requires saving `balance_pass_count` for each product BEFORE calculation starts, then comparing after.

### Plan: Fix the downstream material bug

1. Save `prevBalancePassCountIdx` before `resetAndMarkChanged()` call
2. After all calculation loops (after `calcArrayStats`), rebuild `changedProductIdx` from products with increased `balance_pass_count`
3. Add test: change sales order for product with materials, verify material products appear in change_idx
4. Run all existing tests to verify no regression
