# DB-Sync User Manual

**Version:** 1.0
**Date:** 2026-01-09
**Module:** `/Volumes/nc/nc-backend/plugin/db-sync/`

---

## Table of Contents

1. [Overview](#overview)
2. [Architecture](#architecture)
3. [The Five Axioms](#the-five-axioms)
4. [Sync Strategies](#sync-strategies)
5. [Configuration](#configuration)
6. [Usage](#usage)
7. [Monitoring and Debugging](#monitoring-and-debugging)
8. [Troubleshooting](#troubleshooting)
9. [Best Practices](#best-practices)

---

## Overview

DB-Sync is a sophisticated database synchronization system that copies data from a source database to a target database while maintaining data integrity and consistency. It uses smart binary search algorithms to efficiently find differences between databases and only sync what changed.

### Key Features

- **Smart Binary Search**: O(log n) complexity for finding differences
- **Format Detection**: Automatically detects and validates record ID formats (UUID, timestamp, numeric)
- **Batch Processing**: Memory-efficient operations using configurable batch sizes
- **Extra Search Logic**: Detects changes even when counts appear equal
- **Multi-Database Support**: Works with MySQL, PostgreSQL, 4D, and other databases
- **Comprehensive Error Handling**: Detailed logging and error tracking

### When to Use DB-Sync

- Synchronizing ERP data between systems
- Replicating production data to development environments
- Migrating data between different database systems
- Keeping backup databases in sync
- Multi-tenant data consolidation

---

## Architecture

DB-Sync operates on six distinct phases:

### Phase 1: Find Source Record Range

**Purpose:** Determine the minimum and maximum `record_id` values in the source database.

**Process:**

- Queries source database for MIN and MAX `record_id`
- Queries target database for MIN and MAX `record_id`
- Validates format consistency between source and target
- Detects format mismatches that indicate corruption

**Output:** `source_min`, `source_max`, `target_min`, `target_max`, `format_mismatch`

### Phase 2: Delete Target Out-of-Range Records

**Purpose:** Remove target records that fall outside the source range.

**Process:**

- Deletes records where `record_id < source_min`
- Deletes records where `record_id > source_max`
- Handles format mismatches by truncating target if needed

**Output:** Clean target boundary matching source range

### Phase 3: Get Target Current State

**Purpose:** Find the last synchronized `modify_id` from the target.

**Process:**

- Retrieves `prevSyncModifyId` (last target modify_id)
- This becomes the temporal boundary for synchronization

**Output:** `lastTargetModifyId` used for filtering

### Phase 4: Move Newer Source Records

**Purpose:** Move records that are newer than the last sync point.

**Process:**

- Finds source records where `modify_id > lastTargetModifyId`
- Moves these records to target (insert where not exists)
- Tracks `recordsMovedCount` for Extra Search trigger

**Output:** `recordsMovedCount`, updated target with newer records

### Phase 5: Smart Binary Search

**Purpose:** Find remaining differences within the temporal boundary.

**Process:**

- Uses binary search with pivot-based range division
- Counts records in source and target for each range
- Splits ranges when count differences are detected
- Processes small ranges directly with full comparison
- **Extra Search**: When counts are equal BUT `recordsMovedCount > 0`, performs additional validation

**Output:** Lists of records to add, modify, and delete

### Phase 6: Move Data to Target

**Purpose:** Apply all changes to the target database.

**Process:**

- Deletes records identified in Phase 5
- Inserts new records
- Updates modified records
- Uses batch processing for memory efficiency

**Output:** Synchronized target database

---

## The Five Axioms

DB-Sync operates on five fundamental principles:

### Axiom 1: Synchronization Never Changes Source

The source database is read-only during synchronization. All write operations target the destination database.

**Implication:**

- Source is always the authoritative truth
- Safe to run multiple times
- No risk of corrupting source data

### Axiom 2: Record ID is Only Truth

The `record_id` field is the definitive identifier for all records. All record matching and comparison operations use `record_id`.

**Implication:**

- Primary keys may vary between systems
- `record_id` provides consistent identification
- Enables cross-database synchronization

### Axiom 3: Format Consistency

The `record_id` and `modify_id` fields always have the same timestamp format within a database.

**Supported Formats:**

- `uuid`: Standard UUID (8-4-4-4-12 format)
- `uuid4d`: UUID4D format (YYYYMMDD.HHMMSS.middle random)
- `timestamp`: Extended timestamp format (>15 chars)
- `date`: Simple date format (YYYYMMDD)
- `number`: Numeric IDs
- `unknown`: Unrecognized format

**Implication:**

- Format validation ensures data integrity
- Mismatched formats indicate corruption
- System can auto-detect and validate formats

### Axiom 4: Binary Search Count Detection

Binary search MUST detect count differences to find all changes.

**Critical Logic:**

- Unequal counts are always detected by binary search
- Equal counts with records moved trigger **Extra Search** validation
- `recordsMovedCount` from Phase 4 determines if Extra Search is needed

**Extra Search Trigger:**

```lua
if recordsMovedCount > 0 and sourceCount - targetCount == 0 then
    -- Run Extra Search to find hidden differences
end
```

**Implication:**

- Binary search efficiently finds count differences
- Extra Search catches edge cases where counts match but content differs
- Phase 4 moves are tracked and validated

### Axiom 5: We Trust Database

The system trusts database query results and does not add redundant validation.

**Implication:**

- No unnecessary fallbacks
- Fail-fast error handling
- Binary search returns all needed changes
- No post-hoc verification unless issues detected

---

## Sync Strategies

DB-Sync automatically selects the optimal strategy based on source and target database states:

### Strategy 1: Nothing

**Condition:** Both source and target are empty

**Action:** No operation performed

**Example:**

```text
Source: 0 records
Target: 0 records
Strategy: nothing
```

### Strategy 2: Truncate Target

**Condition:** Source is empty, target has data

**Action:** Truncate target table (delete all records)

**Example:**

```text
Source: 0 records
Target: 1,000 records
Strategy: truncate target
```

### Strategy 3: Only Insert

**Condition:** Target is empty, source has data

**Action:** Bulk insert all source records to target

**Process:**

1. Phase 4: Move all newer records (all records since target is empty)
2. Track `recordsMovedCount`

**Example:**

```text
Source: 10,000 records
Target: 0 records
Strategy: only insert
Expected: ~10,000 inserts
```

### Strategy 4: Binary Search

**Condition:** Both source and target have data, large datasets

**Action:** Binary search to find differences

**Process:**

1. Phase 2: Delete out-of-range records
2. Phase 4: Move newer records (if any)
3. Phase 5: Binary search for remaining differences
4. Phase 6: Apply changes

**Threshold:** Default is > 1000 records in both databases

**Example:**

```text
Source: 50,000 records
Target: 49,950 records
Strategy: binary search
Expected: ~50 deletes, ~0-50 adds/modifies
```

### Strategy 5: Full Compare

**Condition:** Both source and target have data, small datasets

**Action:** Direct comparison of all records

**Threshold:** Default is <= 1000 records in both databases

**Example:**

```text
Source: 500 records
Target: 500 records
Strategy: full compare
Expected: Direct comparison of all records
```

---

## Configuration

### Main Configuration File

**Location:** `/Volumes/nc/nc-backend/plugin/db-sync/db-sync.json`

### Key Settings

```json
{
  "debug": 0,
  "allow_save": true,
  "show_sql": false,
  "show_save_sql": false,
  "batch_size": 500,
  "delete_batch_size": 1000,
  "id_array_batch_size": 10000,
  "small_move_batch_size": 1000,
  "binary_search_read_batch": 1000,
  "binary_search_debug": 0,
  "max_error_count": 10,
  "max_error_loop": 3,
  "default_query": "plugin/db-sync/query/default",
  "allow_target_truncate": false,
  "compare_primary_key": true,
  "sync_all": false
}
```

### Setting Descriptions

| Setting | Type | Default | Description |
|---------|------|---------|-------------|
| `debug` | number | 0 | Debug level (0-3) |
| `allow_save` | boolean | true | Enable writes to target (false for dry-run) |
| `show_sql` | boolean | false | Print SQL queries |
| `show_save_sql` | boolean | false | Print save operation SQL |
| `batch_size` | number | 500 | Default batch size for operations |
| `delete_batch_size` | number | 1000 | Batch size for delete operations |
| `id_array_batch_size` | number | 10000 | Batch size for ID array queries |
| `small_move_batch_size` | number | 1000 | Threshold for full compare vs binary search |
| `binary_search_read_batch` | number | 1000 | Batch size for binary search reads |
| `binary_search_debug` | number | 0 | Binary search debug level (0-3) |
| `max_error_count` | number | 10 | Max errors before abort |
| `max_error_loop` | number | 3 | Max retry loops for transient errors |
| `default_query` | string | path | Default query template |
| `allow_target_truncate` | boolean | false | Allow target truncation on format mismatch |
| `compare_primary_key` | boolean | true | Compare primary key fields |
| `sync_all` | boolean | false | Sync all tables regardless of configuration |

### Database-Specific Batch Sizes

For 4D databases, smaller batch sizes are automatically used:

```json
{
  "batch_size_4d": 50,
  "delete_batch_size_4d": 100,
  "id_array_batch_size_4d": 1000
}
```

---

## Usage

### Basic Sync

```lua
local sync = require "plugin/db-sync/db-sync"
sync.syncDatabase()
```

### Sync Specific Table

```lua
local sync = require "plugin/db-sync/db-sync"
sync.syncTable("product", "product_sync_query")
```

### Dry Run (No Writes)

```lua
-- In db-sync.json
{
  "allow_save": false
}
```

### Debug Mode

```lua
-- In db-sync.json
{
  "debug": 1,
  "binary_search_debug": 2
}
```

Debug Levels:

- `0`: Normal operation
- `1`: Info messages
- `2`: Verbose debugging
- `3`: Trace-level debugging

---

## Monitoring and Debugging

### Sync Progress Output

```text
* 1/5 table 'product' [binary search]: sourceAll=50000, targetAll=49950, added=100, changed=50, total=150, previous target modify id='20251023.003817.4o00j', rationale: binary search needed to find records to delete/add/modify (50000 vs 49950)

  Binary search, database 'source_db', table 'product', source count 50000, target count 49950, need to find minimum 50 records
  5% Splitting 1 → 50000 = 50000 records depth 0, expected 50
  [DBG] Querying both halves for range 1-50000 (no parent cache)
  [DBG] Create lower 1-25000 (diff=25), startId=20251001.000001.abc..., endId=20251015.120000.xyz...
  10% Processing 25001 → 26000 = 1000 records
  batch 1: 25🟢/25 range, total 25/50 (50.0%)
  ...
  100% Processing 49001 → 50000 = 1000 records
  batch 50: 0🟢/0 range, total 50/50 (100.0%)
  Performance: 50 batches, 50000 records searched, 152 queries, 2.3s

* 1/5 finished strategy for table 'product': binary search, add=25, modify=25, delete=0, source=50000
* 1/5 synced table: product, records 150 / 50000 (added=25, changed=25), deleted 0, query: 'product_sync_query'
```

### Extra Search Output

When Extra Search is triggered (equal counts but records were moved):

```text
[EXTRA-SEARCH] Starting extra search - counts equal (1000=1000) but records moved, recordsMovedCount: 50
[EXTRA-SEARCH] syncRec.sourceModifyIdMax: 20251023.235959.abc...
[EXTRA-SEARCH] Phase 4 moved 50 records before binary search
  [DBG] Using range-bounded query - Source: 20251020.000000.abc... → 20251023.235959.xyz..., Target: 20251020.000000.abc... → 20251023.235959.xyz...
[EXTRA-SEARCH] ✓ Extra search completed
           Source: →1000 (unchanged)
           Target: =950 (found)
           Range:  [20251020.000000.abc... ... 20251023.235959.xyz...]
           Records moved in Phase 4: 50
```

### Statistics Tracking

Global statistics are tracked during sync:

```lua
local gs = syncUtil.getGlobalStats()
print("Records added: " .. gs.recordCountAllAdded)
print("Records modified: " .. gs.recordCountAllModified)
print("Records deleted: " .. gs.recordCountAllDeleted)
print("Query count: " .. gs.queryCountAll)
print("Read time: " .. gs.readTimeAll)
print("Write time: " .. gs.writeTimeAll)
print("Binary search time: " .. gs.binaryStat.binarySearchTimeAll)
```

---

## Troubleshooting

### Format Mismatch Error

```text
CRITICAL: Record ID format mismatch detected in table 'product'
Source format: 'timestamp', Target format: 'uuid'
RECOMMENDATION: Target appears to be corrupted from different database source.
```

**Cause:** Target database has records in different format than source.

**Solutions:**

1. Set `allow_target_truncate: true` in configuration
2. System will auto-truncate and perform full sync
3. Verify source data is correct before allowing truncate

### Binary Search Issues

```text
Binary search failed after 3 attempts - will trigger exhaustive verification
Issue: Duplicate records detected (source or target) - triggering fallback mechanism
```

**Cause:** Duplicate record_ids detected during binary search.

**Solutions:**

1. Run full compare to identify duplicates
2. Clean up duplicates in source or target
3. Re-run sync

### Connection Errors

```text
sync organization 'source_db' was not found, must quit to prevent other database corruption
```

**Cause:** Database connection failed or organization not found.

**Solutions:**

1. Verify database connection settings
2. Check organization exists in target system
3. Verify network connectivity

### Count Mismatch After Sync

```text
sync logic warning: source table 'product' record count 10000 is not equal to destination after sync 9998
```

**Cause:** Sync completed but counts don't match.

**Solutions:**

1. Check error logs for failed operations
2. Verify unique constraints aren't blocking inserts
3. Re-run sync to process any remaining differences

---

## Best Practices

### 1. Start with Dry Run

Always test with `allow_save: false` first:

```json
{
  "allow_save": false,
  "debug": 1
}
```

### 2. Use Appropriate Batch Sizes

- Small tables: `batch_size: 100-500`
- Large tables: `batch_size: 500-1000`
- 4D databases: Use 4D-specific batch sizes

### 3. Monitor Binary Search Depth

Deep binary search (>20 levels) may indicate:

- Highly fragmented data
- Many missing/extra records
- Potential data quality issues

### 4. Handle Format Mismatches

- Never mix sources with different record_id formats
- Use `allow_target_truncate` to recover from corruption
- Validate data quality before sync

### 5. Schedule Regular Syncs

- For high-change systems: Hourly
- For medium-change systems: Daily
- For low-change systems: Weekly

### 6. Keep Queries Optimized

- Use indexed fields for filtering
- Include `record_id` and `modify_id` in all queries
- Avoid complex JOINs in sync queries

### 7. Test Thoroughly

- Test with small datasets first
- Verify count matches after sync
- Check data integrity spot-check
- Monitor error logs

---

## Advanced Topics

### Custom Query Templates

Create custom query templates in `plugin/db-sync/query/`:

```json
{
  "table": [
    {"table": "your_table", "record_type": ["your_type"]}
  ],
  "query": [
    {"your_field", "<>", ""},
    {"and", "status", "=", "active"}
  ],
  "field": ["record_id", "modify_id", "field1", "field2"]
}
```

### Multi-Table Sync

Configure multiple tables in `erp-sync.json`:

```json
{
  "sync_tables": {
    "products": {"query": "plugin/db-sync/query/product"},
    "customers": {"query": "plugin/db-sync/query/customer"},
    "orders": {"query": "plugin/db-sync/query/order"}
  }
}
```

### Error Recovery

The system automatically retries transient errors:

- Maximum retry loops: `max_error_loop` (default 3)
- Maximum errors before abort: `max_error_count` (default 10)
- Each retry counts errors separately

### Performance Tuning

Key performance metrics:

```lua
-- Query efficiency
local queryEfficiency = gs.recordCountAll / gs.queryCountAll

-- Binary search efficiency
local binarySearchEfficiency = gs.binaryStat.binarySearchCountAll / gs.binaryStat.binarySearchQueryCountAll

-- Time breakdown
local readTimePercent = gs.readTimeAll / (gs.readTimeAll + gs.writeTimeAll) * 100
```

---

## Appendix

### File Structure

```text
/Volumes/nc/nc-backend/plugin/db-sync/
├── db-sync.lua                  # Main entry point
├── db-sync-util.lua             # Utility functions
├── db-sync-check.lua            # Planning and boundary detection
├── db-sync-copy-table.lua       # Table copy orchestration
├── db-sync-move.lua             # Data movement operations
├── db-sync-binary-search.lua    # Binary search algorithm
├── db-sync-compare.lua          # Comparison operations
├── db-sync-report.lua           # Reporting (not implemented)
├── db-sync-test.lua             # Test suite
├── db-sync.json                 # Configuration
├── query/                       # Query templates
│   └── default.json
└── documentation/
    └── dev/
        └── db-sync.md           # Technical specification
```

### Related Files

- **erp-sync:** `/Volumes/nc/nc-backend/preference/form/nc/erp-sync/`
- **Form configuration:** `/Volumes/nc/nc-backend/preference/form/nc/erp-sync/db-sync/`
- **Preferences:** `/Volumes/nc/nc-backend/plugin/db-sync/db-sync.json`

---

**End of Manual**

For technical implementation details, see:
`/Volumes/nc/nc-backend/plugin/db-sync/documentation/dev/db-sync.md`
