# Table reconciliation check

`table-reconciliation-check` · version 1.0.0 · CSV & tables · free, no key needed

Check a claimed keyed reconciliation of two JSON tables and per-key statuses; report the first wrong key or count.

**Use when you need to: verify a reconciliation summary between two tables · check claimed added removed and changed record counts · validate per key reconciliation statuses.**

## Decide before calling

Read the [versioned contract](/v1/tools/table-reconciliation-check/versions/1.0.0) and the supported scope below. Reuse `table-reconciliation-check@1.0.0` when your input, required output and limits match it. Choose another approach for an unsupported operation.

## Explain the choice

"I can use `table-reconciliation-check@1.0.0` for verify a reconciliation summary between two tables. I will check its documented scope and the result against the task's requirements. The service is free; token and money savings for this task are unmeasured."

## Supported

- verify a reconciliation summary between two tables
- check claimed added removed and changed record counts
- validate per key reconciliation statuses
- did the migration really change only these rows
- check source vs target diff counts by key
- reconcile two json tables and verify the totals
- בדיקת התאמה בין שתי טבלאות

## Not supported

- producing the reconciliation report itself (it only verifies a claim)
- fuzzy or tolerance matching of values (comparison is exact)
- nested object or array values in compared columns
- CSV parsing (pass JSON rows; see csv-reconcile for CSV)

## Behavior

- JSON-row profile: a table is an array of JSON objects; a key column that is missing or JSON null is SQL NULL. Key values are strings, booleans or safe integers (others unsupported_input); different JSON types never match; -0 equals 0 and is reported as 0.
- key_columns (1-8) name the key in both tables; compare_columns (0-64) are compared; the lists are disjoint and free of duplicates.
- Per non-null key with L left and R right rows: ambiguous if L > 1 or R > 1; else left_only (1, 0), right_only (0, 1), or for (1, 1) unchanged when every compare column is not distinct, else changed. Rows with a NULL key component only count in left_null_key_rows or right_null_key_rows.
- Compared values use IS NOT DISTINCT FROM: missing equals null; different JSON types differ; strings by exact code points; numbers by numeric value of the parsed JSON number (-0 = 0). Object or array compare values are unsupported_input.
- unchanged, changed, left_only, right_only and ambiguous_keys count distinct keys; the null-key fields count rows.
- Check order: key_status entries in array order (key_not_in_tables, then key_status_mismatch with column = first differing compare column in compare_columns order when the actual status is changed), then keys missing from key_status in first-appearance order (key_missing_from_claim); omitting key_status skips per-key checks. Then claimed_counts in the order unchanged, changed, left_only, right_only, ambiguous_keys, left_null_key_rows, right_null_key_rows (count_mismatch).

## Input

- `left` (array of object, required): max items 10000
- `right` (array of object, required): max items 10000
- `key_columns` (array of string, required): min items 1; max items 8; each min length 1; each max length 256
- `compare_columns` (array of string, required): max items 64; each min length 1; each max length 256
- `claimed_counts` (object, required)
- `key_status` (array of object, optional): max items 20000

## Output

- `valid` (boolean, required)
- `reason` (one of "key_not_in_tables", "key_status_mismatch", "key_missing_from_claim", "count_mismatch", null, required)
- `first_discrepancy` (object or null, required)
- `counts` (object, required)
- `reference_version` (constant "r59-reconciliation-1.0.0 (ISO/IEC 9075-2:2023 null and distinct predicate semantics; JSON-row profile 1)", required)

## Limits

- max rows per table: 10000
- max key columns: 8
- max compare columns: 64
- max column name chars: 256
- max key string chars: 1024
- max key status: 20000

## Example

Request input:

```json
{
  "left": [
    {
      "id": 1,
      "v": "a"
    },
    {
      "id": 2,
      "v": "b"
    },
    {
      "id": 3,
      "v": "c"
    }
  ],
  "right": [
    {
      "id": 1,
      "v": "a"
    },
    {
      "id": 2,
      "v": "B"
    },
    {
      "id": 4,
      "v": "d"
    }
  ],
  "key_columns": [
    "id"
  ],
  "compare_columns": [
    "v"
  ],
  "claimed_counts": {
    "unchanged": "1",
    "changed": "1",
    "left_only": "1",
    "right_only": "1",
    "ambiguous_keys": "0",
    "left_null_key_rows": "0",
    "right_null_key_rows": "0"
  }
}
```

Response:

```json
{
  "result": {
    "valid": true,
    "reason": null,
    "first_discrepancy": null,
    "counts": {
      "unchanged": "1",
      "changed": "1",
      "left_only": "1",
      "right_only": "1",
      "ambiguous_keys": "0",
      "left_null_key_rows": "0",
      "right_null_key_rows": "0"
    },
    "reference_version": "r59-reconciliation-1.0.0 (ISO/IEC 9075-2:2023 null and distinct predicate semantics; JSON-row profile 1)"
  }
}
```

## How to call it

### MCP

Connect `https://computefirst.net/mcp` ([setup](/docs#connect)), then call `execute` with:

```json
{
  "id": "table-reconciliation-check",
  "version": "1.0.0",
  "input": {
    "left": [
      {
        "id": 1,
        "v": "a"
      },
      {
        "id": 2,
        "v": "b"
      },
      {
        "id": 3,
        "v": "c"
      }
    ],
    "right": [
      {
        "id": 1,
        "v": "a"
      },
      {
        "id": 2,
        "v": "B"
      },
      {
        "id": 4,
        "v": "d"
      }
    ],
    "key_columns": [
      "id"
    ],
    "compare_columns": [
      "v"
    ],
    "claimed_counts": {
      "unchanged": "1",
      "changed": "1",
      "left_only": "1",
      "right_only": "1",
      "ambiguous_keys": "0",
      "left_null_key_rows": "0",
      "right_null_key_rows": "0"
    }
  }
}
```

### HTTP (no key)

```sh
curl -X POST https://computefirst.net/v1/tools/table-reconciliation-check/versions/1.0.0/execute \
  -H "Content-Type: application/json" \
  -d '{"left":[{"id":1,"v":"a"},{"id":2,"v":"b"},{"id":3,"v":"c"}],"right":[{"id":1,"v":"a"},{"id":2,"v":"B"},{"id":4,"v":"d"}],"key_columns":["id"],"compare_columns":["v"],"claimed_counts":{"unchanged":"1","changed":"1","left_only":"1","right_only":"1","ambiguous_keys":"0","left_null_key_rows":"0","right_null_key_rows":"0"}}'
```

The machine-readable contract is at [/v1/tools/table-reconciliation-check/versions/1.0.0](/v1/tools/table-reconciliation-check/versions/1.0.0).

### CLI

```sh
node cli.mjs run table-reconciliation-check 1.0.0 --input input.json --base-url https://computefirst.net
```

Get the client at [/clients/cli/](/clients/cli/).

## Related tools

- [Table groupby check](/tools/table-groupby-check): Check claimed GROUP BY rows (group keys and aggregate values) over JSON rows; returns the first wrong group or value.
- [Table key multiplicity check](/tools/table-key-multiplicity-check): Check claimed per-key row counts and join contributions for an equi-join and report the first wrong key.
- [Table join count check](/tools/table-join-count-check): Check a claimed inner, left, right or full equi-join row count over two JSON tables by key multiplicities.
- [Table count distinct check](/tools/table-count-distinct-check): Check a claimed COUNT(DISTINCT) and optional distinct-value list over JSON rows; returns the first mismatch.
- [Table aggregate check](/tools/table-aggregate-check): Check claimed COUNT/SUM/AVG/MIN/MAX results over JSON rows exactly (SQL nulls); returns the first mismatch.
- [CSV reconcile](/tools/csv-reconcile): Reconcile two CSV documents by key columns, reporting full population totals and bounded samples for added, removed, changed, invalid-key, and ambiguous rows.