{"id":"table-reconciliation-check","version":"1.0.0","description":"Check a claimed keyed reconciliation of two JSON tables and per-key statuses; report the first wrong key or count.","supported_operations":["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","בדיקת התאמה בין שתי טבלאות"],"unsupported_operations":["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)"],"semantics":["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)."],"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},"pricing":{"status":"unpriced","charge_usd":null},"input_schema":{"type":"object","additionalProperties":false,"required":["left","right","key_columns","compare_columns","claimed_counts"],"properties":{"left":{"type":"array","maxItems":10000,"items":{"type":"object","additionalProperties":{"description":"any JSON value"}}},"right":{"type":"array","maxItems":10000,"items":{"type":"object","additionalProperties":{"description":"any JSON value"}}},"key_columns":{"type":"array","minItems":1,"maxItems":8,"items":{"type":"string","minLength":1,"maxLength":256}},"compare_columns":{"type":"array","maxItems":64,"items":{"type":"string","minLength":1,"maxLength":256}},"claimed_counts":{"type":"object","additionalProperties":false,"required":["unchanged","changed","left_only","right_only","ambiguous_keys","left_null_key_rows","right_null_key_rows"],"properties":{"unchanged":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"},"changed":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"},"left_only":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"},"right_only":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"},"ambiguous_keys":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"},"left_null_key_rows":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"},"right_null_key_rows":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"}}},"key_status":{"type":"array","maxItems":20000,"items":{"type":"object","additionalProperties":false,"required":["key","status"],"properties":{"key":{"type":"array","minItems":1,"maxItems":8,"items":{"type":["string","integer","boolean"]}},"status":{"type":"string","enum":["unchanged","changed","left_only","right_only","ambiguous"]}}}}}},"output_schema":{"type":"object","additionalProperties":false,"required":["valid","reason","first_discrepancy","counts","reference_version"],"properties":{"valid":{"type":"boolean"},"reason":{"type":["string","null"],"enum":["key_not_in_tables","key_status_mismatch","key_missing_from_claim","count_mismatch",null]},"first_discrepancy":{"type":["object","null"],"additionalProperties":false,"required":["claim_index","key","field","claimed","actual","column"],"properties":{"claim_index":{"type":["integer","null"],"minimum":0},"key":{"type":["array","null"],"items":{"type":["string","integer","boolean"]}},"field":{"type":"string","enum":["presence","status","unchanged","changed","left_only","right_only","ambiguous_keys","left_null_key_rows","right_null_key_rows"]},"claimed":{"type":["string","null"]},"actual":{"type":["string","null"]},"column":{"type":["string","null"]}}},"counts":{"type":"object","additionalProperties":false,"required":["unchanged","changed","left_only","right_only","ambiguous_keys","left_null_key_rows","right_null_key_rows"],"properties":{"unchanged":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"},"changed":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"},"left_only":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"},"right_only":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"},"ambiguous_keys":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"},"left_null_key_rows":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"},"right_null_key_rows":{"type":"string","pattern":"^(0|[1-9][0-9]*)$"}}},"reference_version":{"type":"string","const":"r59-reconciliation-1.0.0 (ISO/IEC 9075-2:2023 null and distinct predicate semantics; JSON-row profile 1)"}}},"examples":[{"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"}},"output":{"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)"}},{"input":{"left":[{"id":2,"v":"b","w":1}],"right":[{"id":2,"v":"B","w":1}],"key_columns":["id"],"compare_columns":["w","v"],"claimed_counts":{"unchanged":"0","changed":"1","left_only":"0","right_only":"0","ambiguous_keys":"0","left_null_key_rows":"0","right_null_key_rows":"0"},"key_status":[{"key":[2],"status":"unchanged"}]},"output":{"valid":false,"reason":"key_status_mismatch","first_discrepancy":{"claim_index":0,"key":[2],"field":"status","claimed":"unchanged","actual":"changed","column":"v"},"counts":{"unchanged":"0","changed":"1","left_only":"0","right_only":"0","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)"}}],"execute_url":"/v1/tools/table-reconciliation-check/versions/1.0.0/execute"}