# Incremental Update Data Flow

## Overview

The incremental update system detects and propagates changes from external databases (4D) to the local PostgreSQL database, then to the browser UI. It supports three types of changes: **new**, **modify**, and **delete**.

## Complete Data Flow

```text
┌─────────────────────────────────────────────────────────────────────────────┐
│ STEP 1: Detect Changes (nc-calc-server-calc.lua:queryData)                │
├─────────────────────────────────────────────────────────────────────────────┤
│ Location: /Volumes/nc/nc-plugin/plugin/nc-calc-server/nc-calc-server-calc.lua│
│ Function: queryData() (lines 90-372)                                       │
│                                                                             │
│ Process:                                                                    │
│ 1a. Query external DB with last_modify_id filter → returns changed rows    │
│      - Uses queryParam.last_modify_id from param.rec.last_modify_id        │
│      - Only fetches records modified since last sync                       │
│                                                                             │
│ 1b. Build syncRec from gridInfo + param.schema (lines 200-217):            │
│      - tblNameRecType: tableName (e.g., "product_work")                     │
│      - recordType: from connection query                                   │
│      - schema: param.schema                                                │
│      - sourceId: param.schema (external DB)                                │
│      - targetId: "" (local DB - auto-detected)                             │
│      - queryName: query JSON file path                                     │
│      - recordIdFieldLocal: primary key field in local schema               │
│      - recordIdFieldLocalCompare: primary key field in external schema     │
│      - modifyIdFieldLocal: modify_time field in local schema               │
│      - modifyIdFieldLocalCompare: modify_time field in external schema    │
│      - primaryKeyFieldLocal: primary key field                             │
│                                                                             │
│ 1c. Call binarySearch.tableChange(syncRec, option) (line 219):            │
│      Location: /Volumes/nc/nc-backend/plugin/db-sync/db-sync-binary-search.lua│
│      Function: tableChange(syncRec, option) (lines 1065-1103)              │
│                                                                             │
│      Internal process:                                                       │
│      ├─ syncUtil.readPref() → syncPrf (loads db-sync.json preferences)     │
│      ├─ Set sourceCountAll=0, targetCountAll=0 (forces count queries)      │
│      ├─ Set prevSyncModifyId="" (skip modify_id filter for full compare)   │
│      └─ binarySearch.binarySearch(syncRec, syncPrf, binaryStat, bsOption) ││
│         └─ Compares source vs target using binary search                   │
│            ├─ Uses range-bounded queries for efficiency                    │
│            ├─ Processes batches of records                                 │
│            └─ Returns {add={id1,id2}, modify={id3}, delete={id4}}         │
│                                                                             │
│ 1d. Build gridIdx.deleted from changes.delete (lines 221-231):             │
│      - Format: [{table="product_work", record_id="xxx"}, ...]              │
│      - Only populated if #changes.delete > 0                              │
│                                                                             │
│ 1e. Return query results + gridIdx.deleted to caller                      │
└─────────────────────────────────────────────────────────────────────────────┘
                                    ↓
┌─────────────────────────────────────────────────────────────────────────────┐
│ STEP 2: Build Change Array (work-center.lua:workCenter)                   │
├─────────────────────────────────────────────────────────────────────────────┤
│ Location: /Volumes/nc/nc-backend/preference/form/nc/nc-work-center/        │
│          nc-calc-server-work-center.lua                                   │
│ Function: workCenter() (lines 700-850)                                     │
│                                                                             │
│ Process:                                                                    │
│ 2a. Receive data + gridIdx.deleted from queryData()                        │
│                                                                             │
│ 2b. Call changeData.setIncrementalChange() (lines 783-794):                │
│      Location: /Volumes/nc/nc-server/lib/db/change-data.lua                 │
│      Function: setIncrementalChange(data, lastModifyId, deletedRecordArr)  │
│      (lines 37-92)                                                          │
│                                                                             │
│      Returns changeArr with __change markers:                              │
│      ├─ For deleted records:                                               │
│      │   {record_id="deleted_id", __change="delete"}                       │
│      ├─ For new records:                                                   │
│      │   {full_record_data, __change="new"}                               │
│      └─ For modified records:                                             │
│          {full_record_data, __change="change"}                            │
│                                                                             │
│ 2c. Set param.change_arr = changeArr                                      │
│ 2d. Update rec.last_modify_id                                              │
└─────────────────────────────────────────────────────────────────────────────┘
                                    ↓
┌─────────────────────────────────────────────────────────────────────────────┐
│ STEP 3: Apply Changes (change-data.js:incrementalUpdate)                  │
├─────────────────────────────────────────────────────────────────────────────┤
│ Location: /Volumes/nc/nc-backend/preference/form/nc/nc-work-center/        │
│          change-data.js                                                     │
│ Function: incrementalUpdate(state, response, option) (lines 116-164)      │
│                                                                             │
│ Process:                                                                    │
│ 3a. Receive response.change_arr from server                                │
│                                                                             │
│ 3b. Call applyChangeArr(state, changeArr) (lines 34-109):                  │
│                                                                             │
│      For each table in changeArr:                                          │
│      For each change record in table:                                      │
│                                                                             │
│      ├─ "new" case (lines 65-77):                                          │
│      │   - Delete __change field                                           │
│      │   - Add calc.change = "new"                                         │
│      │   - Add calc.change_time = ISO timestamp                           │
│      │   - Push to dataArray (or replace if exists)                       │
│      │                                                                     │
│      ├─ "change" case (lines 78-89):                                       │
│      │   - Delete __change field                                           │
│      │   - Add calc.change = "modify"                                     │
│      │   - Add calc.change_time = ISO timestamp                           │
│      │   - Replace existing record (full row replace)                      │
│      │                                                                     │
│      └─ "delete" case (lines 91-102):                                      │
│          - MARK with calc.change = "delete"                               │
│          - Add calc.change_time = ISO timestamp                           │
│          - DO NOT remove from array!                                       │
│                                                                             │
│ 3c. Update grid data to match state.data (updateGridData)                 │
│ 3d. Update state.rec.last_modify_id                                         │
│ 3e. UI filters/hides rows where calc.change === "delete"                   │
└─────────────────────────────────────────────────────────────────────────────┘
```

## Key Data Structures

### syncRec (built in nc-calc-server-calc.lua:queryData)

```lua
local syncRec = {
    tblNameRecType = "product_work",           -- Table name
    recordType = "",                            -- From connection
    schema = "4d",                              -- External DB schema
    sourceId = "4d",                            -- External DB ID
    targetId = "",                              -- Local DB (auto-detected)
    queryName = "plugin/nc-calc-server/query/product_work-actual.json",
    recordIdFieldLocal = "pr.record_id",        -- Local field name
    recordIdFieldLocalCompare = "record_id",    -- External field name
    modifyIdFieldLocal = "pr.modify_time",      -- Local modify time
    modifyIdFieldLocalCompare = "modify_time",   -- External modify time
    primaryKeyFieldLocal = "pr.record_id"       -- Primary key
}
```

### gridIdx.deleted (passed to changeData.setIncrementalChange)

```lua
-- Array of deleted record identifiers
gridIdx.deleted = {
    {table = "product_work", record_id = "xxx"},
    {table = "product_work", record_id = "yyy"},
    ...
}
```

### changeArr (returned by changeData.setIncrementalChange)

```lua
-- Change array with __change markers
changeArr = {
    product_work = {
        {record_id = "deleted_id", __change = "delete"},
        {record_id = "new_id", field1="val1", ..., __change = "new"},
        {record_id = "mod_id", field1="newval", ..., __change = "change"}
    }
}
```

### state.data (after applyChangeArr in change-data.js)

```javascript
// Actual data arrays in browser state with calc.change markers
state.data.product_work = [
    {
        record_id: "id1",
        field1: "val1",
        calc: {
            change: "modify",
            change_time: "2026-01-25T12:00:00Z"
        }
    },
    {
        record_id: "id2",
        field1: "val2",
        calc: {
            change: "delete",    // MARKED as deleted
            change_time: "2026-01-25T12:00:00Z"
        }
    },
    {
        record_id: "id3",
        field1: "val3",
        calc: {
            change: "new",
            change_time: "2026-01-25T12:00:00Z"
        }
    }
]
// UI can filter/hide rows where calc.change === "delete"
```

## Binary Search Options

The `binarySearch.binarySearch()` function accepts an `option` parameter to skip heavy checks:

```lua
option = {
    skip_reporting = true    -- Skip syncReport calls
}
```

### When to use options

- **skip_reporting=true**: Used when statistics/debug output is not needed

## Reused Infrastructure

| Helper | Source | Purpose |
|--------|--------|---------|
| `syncUtil.readPref()` | db-sync-util.lua:130 | Load sync preferences |
| `binarySearch.binarySearch()` | db-sync-binary-search.lua:410 | Main binary search |
| `changeData.setIncrementalChange()` | change-data.lua:37 | Build changeArr |
| `incrementalUpdate()` | change-data.js:116 | Apply changes in browser |

## Frontend Tests

Location: `/Volumes/nc/nc-frontend/test/change-data.test.js`

Tests the JavaScript change handling:

- **applyChangeArr()**: Tests new/modify/delete marking
- **incrementalUpdate()**: Tests response processing

Run with:

```bash
cd /Volumes/nc/nc-frontend
bunx vitest run change-data
```

Key test cases (48 tests):

- New records get `calc.change = "new"` + timestamp
- Modified records get `calc.change = "modify"` + timestamp, **preserves existing calc object**
- Deleted records get `calc.change = "delete"` + timestamp, **NOT removed**
- Mixed changes in single call
- Multiple tables
- Grid mapping (table_name → grid_name)
- Return value: `true` for incremental, `undefined` for full updates

## Backend Tests

Location: `/Volumes/nc/nc-server/test/lib/change-data-test.lua`

Tests the Lua change handling:

- **lastModifyId()**: Tests highest modify_id calculation
- **setIncrementalChange()**: Tests change array creation with deleted records

Run with:

```bash
cd /Volumes/nc/nc-server
lj test/lib/change-data-test.lua
```

Key test cases (21 tests):

- Basic new/modify/delete detection
- Multiple data sources (PRODUCT, PERSON)
- Deleted records merged with new/modified records
- Empty data handling
- Return values: `changeArr`, `changeCount`, `newLastModifyId`

## File Paths

| Component | Full Path |
|-----------|-----------|
| Binary search | `/Volumes/nc/nc-backend/plugin/db-sync/db-sync-binary-search.lua` |
| Calc server | `/Volumes/nc/nc-plugin/plugin/nc-calc-server/nc-calc-server-calc.lua` |
| Work center | `/Volumes/nc/nc-backend/preference/form/nc/nc-work-center/nc-calc-server-work-center.lua` |
| Change data (Lua) | `/Volumes/nc/nc-server/lib/db/change-data.lua` |
| Change data (JS) | `/Volumes/nc/nc-backend/preference/form/nc/nc-work-center/change-data.js` |
| Frontend tests | `/Volumes/nc/nc-frontend/test/change-data.test.js` |
| Backend tests | `/Volumes/nc/nc-server/test/lib/change-data-test.lua` |

## Important Implementation Notes

### Deleted Records Merge Fix (change-data.lua)

**Bug**: When `setIncrementalChange()` processed both deleted records AND new/modified records, the new/modified records would replace the deleted records in `changeArr[tableName]`.

**Fix** (lines 82-92): The function now checks if `changeArr[tableName]` already contains deleted records before assigning new/modified records. If both exist, they are merged by appending new/modified records after deleted records.

```lua
if #tableChangeArr > 0 then
    -- Merge with existing deleted records if any
    if changeArr[tableName] and #changeArr[tableName] > 0 then
        -- Append new/modified records after deleted records
        for _, record in ipairs(tableChangeArr) do
            changeArr[tableName][#changeArr[tableName] + 1] = record
        end
    else
        changeArr[tableName] = tableChangeArr
    end
end
```

### Calc Object Preservation (change-data.js)

**Feature**: The "change" case preserves existing `calc` object data when marking modifications.

```javascript
// Merge calc objects to preserve existing calc data
const existingCalc = existingRecord.calc || {}
changeRecord.calc = changeRecord.calc || {}
Object.assign(changeRecord.calc, existingCalc)
changeRecord.calc.change = "modify"
```

This ensures that any existing calculated fields in `calc` are not lost when a record is modified.

### Return Value Semantics (change-data.js)

**Feature**: `incrementalUpdate()` returns boolean to indicate update type, not the change_arr object.

```javascript
const changeArr = response.change_arr
if (!changeArr) {
    // Non-incremental response (full data or grid update)
    // ... process full update ...
    return undefined // Full update, not incremental
}
// ... process incremental ...
return true // Incremental update
```

This makes it easy to check if an update was incremental: `if (incrementalUpdate(state, response) === true)`
