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 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:

{
  "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:

{
  "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), then call execute with:

{
  "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)

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.

CLI

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

Get the client at /clients/cli/.

Related 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: Check claimed per-key row counts and join contributions for an equi-join and report the first wrong key.
• 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: Check a claimed COUNT(DISTINCT) and optional distinct-value list over JSON rows; returns the first mismatch.
• Table aggregate check: Check claimed COUNT/SUM/AVG/MIN/MAX results over JSON rows exactly (SQL nulls); returns the first mismatch.
• 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.