Reconciling Submitted and Returned Rows over REST with TypeScript

A run can report status: "complete" with total equal to finished while individual rows inside it have status: "failed" or hold an empty value. This example compares the ids it submitted with the rows that came back, one row at a time, and sorts each row into ok, failed, empty, missing, unexpected or duplicate.

What you will build

A script that submits six Company Employee Count rows, three of which are built to misbehave, reads every result row, and prints a per-row reconciliation. It also uploads a two-line batch file with one invalid line to show how the batch endpoint reports bad input.

POST /routines/{id}/run                    -> routine_run_id
    ↓
GET  /routines/run/{id}/results?limit=100  (follow cursor until absent)
    ↓
reconcile(submitted ids, returned rows)    -> ok | failed | empty | missing | unexpected | duplicate
    ↓
exit 0 when every row is ok, 2 when any row is not, 1 on a script error

batch input with a bad line:
POST .../run-batch/upload-url -> PUT JSONL -> POST .../run-batch/start -> GET /routines/run-batch/{id}/results
    -> status "validation_failed" with the line number

POST /routines/{id}/run                    -> routine_run_id
    ↓
GET  /routines/run/{id}/results?limit=100  (follow cursor until absent)
    ↓
reconcile(submitted ids, returned rows)    -> ok | failed | empty | missing | unexpected | duplicate
    ↓
exit 0 when every row is ok, 2 when any row is not, 1 on a script error

batch input with a bad line:
POST .../run-batch/upload-url -> PUT JSONL -> POST .../run-batch/start -> GET /routines/run-batch/{id}/results
    -> status "validation_failed" with the line number

AI Prompt

Implement a TypeScript script that reconciles the rows it submitted to a Clay routine with the rows it got back.

Requirements:
- Base URL: https://api.clay.com/public/v0, auth header "clay-api-key".
- Use Company Employee Count, function:t_0tjqm9ruMvztzu9wbbw. The input is "Company Domain", which must be a hostname such as "example.com"; "not a domain" is rejected with 400 'Must be a valid hostname (e.g. "example.com")'. The result key is "Employee Count" and its value is a string.
- Submit these rows with POST /routines/{id}/run: notion.so, stripe.com, mit.edu, jerrysmith.net, qzxv-wplk-9981-nonexistent.invalid and localhost.test. Give each an id.
- Read the results with GET /routines/run/{id}/results?limit=100 and follow the cursor. The endpoint returns 20 rows per page by default.
- A row that cannot be enriched has status "failed" and an error.message, for example 'Function column "Enrich Company": Invalid company identifier.', while the run's own status is still "complete". A row can also be "complete" with "Employee Count": "".
- Classify each submitted id as ok (status complete and a non-empty value for the key), failed, empty (complete but the key is missing or blank), or missing (no returned row). Classify returned ids that were not submitted as unexpected and repeated ids as duplicate.
- Inline requests reject bad input as a whole. A missing, null, blank or whitespace-only required input returns 400 'Invalid inputs for item <id>: <field>: Missing required field: <field>', a number where a string is expected returns "Expected type string", a repeated id returns 'Duplicate item ids: "<id>"', and an empty items array returns 400. In every case no run id is returned.
- Batch input is validated after the start call. POST run-batch/start returns 202 with status in_progress, and the first poll of GET /routines/run-batch/{id}/results returns status "validation_failed" with error.details[{ line_number, field, message }] and no result_url.
- Exit 0 when every row is ok, 2 when any row is not ok, and 1 when the script itself fails.
- Check the reconcile function against controls built in memory: a clean input, an input with one row removed, and an input with one extra row. Run the verification step below before finishing.

Implement a TypeScript script that reconciles the rows it submitted to a Clay routine with the rows it got back.

Prerequisites

Node.js 18+ and TypeScript
A Clay Public API key (clay_scoped_...)
About 5 credits for the six-row run

1. Create the project

mkdir clay-reconcile && cd clay-reconcile
npm init -y
npm install -D typescript @types/node

mkdir clay-reconcile && cd clay-reconcile
npm init -y
npm install -D typescript @types/node
mkdir src

Add this tsconfig.json, which lists the node types explicitly because TypeScript 6 and later does not pick up @types packages by itself:

{
  "compilerOptions": {
    "target": "ES2022",
    "module": "commonjs",
    "outDir": "dist",

{
  "compilerOptions": {
    "target": "ES2022",
    "module": "commonjs",
    "outDir": "dist",
    "rootDir": "src",
    "strict": true,
    "types": ["node"],
    "esModuleInterop": true,
    "skipLibCheck": true
  },
  "include": ["src"]
}

2. Discover the real schema

Each of these inline requests used function:t_0tjqm9rUjfGY4MunGDM (Company Domain) and was rejected with 400 before any run started:

inputs {"Company Name": ""}                   -> Invalid inputs for item a: Company Name: Missing required field: Company Name
inputs {"Company Name": null}                 -> Invalid inputs for item n: Company Name: Missing required field: Company Name
inputs {"Company Name": "   "}                -> Invalid inputs for item ws: Company Name: Missing required field: Company Name
inputs {"Company Name": 12345}                -> Invalid inputs for item n: Company Name: Expected type string
two items with the same id                    -> Duplicate item ids: "x"
items: []                                     -> Invalid request parameter(s): Field "items" - Too small: expected array to have >=1 items
an item without an id                         -> Invalid request parameter(s): Field "items.0.id" - Invalid input: expected string, received undefined

inputs {"Company Name": ""}                   -> Invalid inputs for item a: Company Name: Missing required field: Company Name
inputs {"Company Name": null}                 -> Invalid inputs for item n: Company Name: Missing required field: Company Name
inputs {"Company Name": "   "}                -> Invalid inputs for item ws: Company Name: Missing required field: Company Name
inputs {"Company Name": 12345}                -> Invalid inputs for item n: Company Name: Expected type string
two items with the same id                    -> Duplicate item ids: "x"
items: []                                     -> Invalid request parameter(s): Field "items" - Too small: expected array to have >=1 items
an item without an id                         -> Invalid request parameter(s): Field "items.0.id" - Invalid input: expected string, received undefined

Rows that pass validation can still fail inside the run. Two rows of the six-row run below came back like this:

{"id":"nonexistent","status":"failed","error":{"message":"Function column \"Enrich Company\": Invalid company identifier."}}
{

{"id":"nonexistent","status":"failed","error":{"message":"Function column \"Enrich Company\": Invalid company identifier."}}
{"id":"obscure","status":"complete","result":{"Employee Count":""}}

3. Configure credentials

Create .env:

CLAY_API_KEY=

Keep .env out of version control.

4. Implement the script

src/clay.ts is the shared fetch wrapper and the response types:

const BASE = "https://api.clay.com/public/v0";

export async function clay<T>(path:

const BASE = "https://api.clay.com/public/v0";

export async function clay<T>(path: string, init: { method?: string; body?: unknown } = {}): Promise<T> {
  const key = process.env.CLAY_API_KEY;
  if (!key) throw new Error("CLAY_API_KEY is not set");
  const res = await fetch(`${BASE}${path}`, {
    method: init.method ?? "GET",
    headers: { "clay-api-key": key, "content-type": "application/json" },
    body: init.body === undefined ? undefined : JSON.stringify(init.body),
  });
  const text = await res.text();
  if (!res.ok) throw new Error(`${res.status} ${path}: ${text}`);
  return JSON.parse(text) as T;
}

export interface RunRow {
  id: string;
  status: string;
  result?: Record<string, unknown>;
  error?: { message: string };
}

export interface RunPage {
  routine_run_id: string;
  status: string;
  total: number;
  finished: number;
  data?: RunRow[];
  cursor?: string;
}

export const sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));

src/reconcile.ts holds the comparison, which has no network calls and can be tested on its own:

import { RunRow } from "./clay";

export type Verdict = "ok" | "failed" |

import { RunRow } from "./clay";

export type Verdict = "ok" | "failed" | "empty" | "missing" | "unexpected" | "duplicate";

export interface Reconciliation {
  submitted: number;
  returned: number;
  counts: Record<Verdict, number>;
  detail: { id: string; verdict: Verdict; message?: string }[];
}

// Compares what was submitted with what came back, one row at a time.
// A row is "ok" only when its status is complete and the key we need holds a non-empty value.
export function reconcile(submittedIds: string[], rows: RunRow[], requiredKey: string): Reconciliation {
  const submitted = new Set(submittedIds);
  const seen = new Map<string, RunRow>();
  const detail: Reconciliation["detail"] = [];

  for (const row of rows) {
    if (!submitted.has(row.id)) {
      detail.push({ id: row.id, verdict: "unexpected" });
    } else if (seen.has(row.id)) {
      detail.push({ id: row.id, verdict: "duplicate" });
    } else {
      seen.set(row.id, row);
    }
  }

  for (const id of submittedIds) {
    const row = seen.get(id);
    if (!row) {
      detail.push({ id, verdict: "missing" });
    } else if (row.status !== "complete") {
      detail.push({ id, verdict: "failed", message: row.error?.message ?? `status ${row.status}` });
    } else {
      const value = row.result?.[requiredKey];
      detail.push({ id, verdict: typeof value === "string" ? (value.trim() ? "ok" : "empty") : value === undefined || value === null ? "empty" : "ok" });
    }
  }

  const counts: Record<Verdict, number> = { ok: 0, failed: 0, empty: 0, missing: 0, unexpected: 0, duplicate: 0 };
  detail.forEach((d) => counts[d.verdict]++);
  return { submitted: submittedIds.length, returned: rows.length, counts, detail };
}

export const isClean = (r: Reconciliation) => r.counts.ok === r.submitted && r.returned === r.submitted;

src/run-and-reconcile.ts runs both scenarios and the controls:

import { clay, sleep, RunPage, RunRow } from "./clay";
import { reconcile, isClean } from "./reconcile";

const EMPLOYEE_COUNT = "function:t_0tjqm9ruMvztzu9wbbw";

const people = [
  { id: "notion", domain: "notion.so" },
  { id: "stripe", domain: "stripe.com" },
  { id: "mit", domain: "mit.edu" },
  { id: "obscure", domain: "jerrysmith.net" },
  { id: "nonexistent", domain: "qzxv-wplk-9981-nonexistent.invalid" },
  { id: "reserved-tld", domain: "localhost.test" },
];

async function readAll(runId: string): Promise<RunPage & { data: RunRow
  let page = await clay<RunPage>(`/routines/run/${runId}/results?limit=100`);
  const rows = [...(page.data ?? [])];
  while (page.cursor) {
    page = await clay<RunPage>(`/routines/run/${runId}/results?limit=100&cursor=${encodeURIComponent(page.cursor)}`);
    rows.push(...(page.data ?? []));
  }
  return { ...page, data: rows };
}

async function inlineScenario() {
  const items = people.map((p) => ({ id: p.id, inputs: { "Company Domain": p.domain } }));
  const started = await clay<{ routine_run_id: string }>(`/routines/${EMPLOYEE_COUNT}/run`, { method: "POST", body: { items } });
  let results = await readAll(started.routine_run_id);
  for (let i = 0; results.status === "in_progress" && i < 100; i++) {
    await sleep(3000);
    results = await readAll(started.routine_run_id);
  }
  const report = reconcile(items.map((i) => i.id), results.data, "Employee Count");
  console.log(JSON.stringify({ scenario: "inline", runStatus: results.status, total: results.total, finished: results.finished, ...report.counts }));
  report.detail.filter((d) => d.verdict !== "ok").forEach((d) => console.log(JSON.stringify
  return { results, report };
}

async function batchValidationScenario() {
  const lines = [
    { id: "good", inputs: { "Company Domain": "notion.so" } },
    { id: "no-input", inputs: {} },
  ];
  const upload = await clay<{ upload_url: string; file_id: string }>(`/routines/${
  const put = await fetch(upload.upload_url, { method: "PUT", body: lines.map((l) => JSON.stringify
  if (!put.ok) throw new Error(`upload failed: ${put.status}`);
  const started = await clay<{ routine_run_id: string }>(`/routines/${EMPLOYEE_COUNT}/run-batch/start`, { method: "POST"
  let res: Record<string, unknown> = {};
  for (let i = 0; i < 20; i++) {
    res = await clay<Record<string, unknown>>(`/routines/run-batch/${started.routine_run_id}/results`);
    if (res.status !== "in_progress") break;
    await sleep(3000);
  }
  console.log(JSON.stringify({ scenario: "batch with an invalid line", status: res.status, hasResultUrl: "result_url" in res, error: res.error }));
  return res;
}

async function main() {
  const { results, report } = await inlineScenario();
  const batch = await batchValidationScenario();

  // Negative controls, run in-process on a copy of the real rows: drop one row, then add a stray row.
  const good = [{ id: "a", status: "complete", result: { "Employee Count": "5" } }, { id: "b", status: "complete", result: { "Employee Count": 
  const dropped = reconcile(["a", "b"], good.slice(0, 1), "Employee Count");
  const stray = reconcile(["a", "b"], [...good, { id: "z", status: "complete", result: { "Employee Count": "1"
  const control = { cleanInput: isClean(reconcile(["a", "b"], good, "Employee Count")), droppedRowFlaggedMissing: dropped.counts.missing === 1
  console.log(JSON.stringify({ control }));

  // Properties, not literals: provider results for real domains can change between runs.
  const failedIds = report.detail.filter((d) => d.verdict === "failed").map((d) =>
  const accounted = Object.values(report.counts).reduce((a, b) => a + b, 0) ===
  const expectations =
    results.status === "complete" && // the run reports success ...
    failedIds.includes("nonexistent") && failedIds.includes("reserved-tld") && // ... while these rows failed
    report.counts.missing === 0 && report.counts.unexpected === 0 && accounted &&
    !isClean(report) &&
    batch.status === "validation_failed" && !("result_url" in batch) &&
    control.cleanInput && control.droppedRowFlaggedMissing && control.strayRowFlaggedUnexpected;
  console.log(expectations ? "expectations met" : "expectations NOT met");
  // The exit code a scheduler sees: 1 when the example itself is broken, 2 when any row is not ok, else 0.
  process.exit(!expectations ? 1 : isClean(report) ? 0 : 2);
}

main().catch((err) => {
  console.error(err instanceof Error ? err.message : err);
  process.exit(1);
});

import { clay, sleep, RunPage, RunRow } from "./clay";
import { reconcile, isClean } from "./reconcile";

const EMPLOYEE_COUNT = "function:t_0tjqm9ruMvztzu9wbbw";

const people = [
  { id: "notion", domain: "notion.so" },
  { id: "stripe", domain: "stripe.com" },
  { id: "mit", domain: "mit.edu" },
  { id: "obscure", domain: "jerrysmith.net" },
  { id: "nonexistent", domain: "qzxv-wplk-9981-nonexistent.invalid" },
  { id: "reserved-tld", domain: "localhost.test" },
];

async function readAll(runId: string): Promise<RunPage & { data: RunRow[] }> {
  let page = await clay<RunPage>(`/routines/run/${runId}/results?limit=100`);
  const rows = [...(page.data ?? [])];
  while (page.cursor) {
    page = await clay<RunPage>(`/routines/run/${runId}/results?limit=100&cursor=${encodeURIComponent(page.cursor)}`);
    rows.push(...(page.data ?? []));
  }
  return { ...page, data: rows };
}

async function inlineScenario() {
  const items = people.map((p) => ({ id: p.id, inputs: { "Company Domain": p.domain } }));
  const started = await clay<{ routine_run_id: string }>(`/routines/${EMPLOYEE_COUNT}/run`, { method: "POST", body: { items } });
  let results = await readAll(started.routine_run_id);
  for (let i = 0; results.status === "in_progress" && i < 100; i++) {
    await sleep(3000);
    results = await readAll(started.routine_run_id);
  }
  const report = reconcile(items.map((i) => i.id), results.data, "Employee Count");
  console.log(JSON.stringify({ scenario: "inline", runStatus: results.status, total: results.total, finished: results.finished, ...report.counts }));
  report.detail.filter((d) => d.verdict !== "ok").forEach((d) => console.log(JSON.stringify(d)));
  return { results, report };
}

async function batchValidationScenario() {
  const lines = [
    { id: "good", inputs: { "Company Domain": "notion.so" } },
    { id: "no-input", inputs: {} },
  ];
  const upload = await clay<{ upload_url: string; file_id: string }>(`/routines/${EMPLOYEE_COUNT}/run-batch/upload-url`, { method: "POST", body: {} });
  const put = await fetch(upload.upload_url, { method: "PUT", body: lines.map((l) => JSON.stringify(l)).join("\n") + "\n" });
  if (!put.ok) throw new Error(`upload failed: ${put.status}`);
  const started = await clay<{ routine_run_id: string }>(`/routines/${EMPLOYEE_COUNT}/run-batch/start`, { method: "POST", body: { file_id: upload.file_id } });
  let res: Record<string, unknown> = {};
  for (let i = 0; i < 20; i++) {
    res = await clay<Record<string, unknown>>(`/routines/run-batch/${started.routine_run_id}/results`);
    if (res.status !== "in_progress") break;
    await sleep(3000);
  }
  console.log(JSON.stringify({ scenario: "batch with an invalid line", status: res.status, hasResultUrl: "result_url" in res, error: res.error }));
  return res;
}

async function main() {
  const { results, report } = await inlineScenario();
  const batch = await batchValidationScenario();

  // Negative controls, run in-process on a copy of the real rows: drop one row, then add a stray row.
  const good = [{ id: "a", status: "complete", result: { "Employee Count": "5" } }, { id: "b", status: "complete", result: { "Employee Count": "6" } }];
  const dropped = reconcile(["a", "b"], good.slice(0, 1), "Employee Count");
  const stray = reconcile(["a", "b"], [...good, { id: "z", status: "complete", result: { "Employee Count": "1" } }], "Employee Count");
  const control = { cleanInput: isClean(reconcile(["a", "b"], good, "Employee Count")), droppedRowFlaggedMissing: dropped.counts.missing === 1 && !isClean(dropped), strayRowFlaggedUnexpected: stray.counts.unexpected === 1 && !isClean(stray) };
  console.log(JSON.stringify({ control }));

  // Properties, not literals: provider results for real domains can change between runs.
  const failedIds = report.detail.filter((d) => d.verdict === "failed").map((d) => d.id);
  const accounted = Object.values(report.counts).reduce((a, b) => a + b, 0) === report.submitted;
  const expectations =
    results.status === "complete" && // the run reports success ...
    failedIds.includes("nonexistent") && failedIds.includes("reserved-tld") && // ... while these rows failed
    report.counts.missing === 0 && report.counts.unexpected === 0 && accounted &&
    !isClean(report) &&
    batch.status === "validation_failed" && !("result_url" in batch) &&
    control.cleanInput && control.droppedRowFlaggedMissing && control.strayRowFlaggedUnexpected;
  console.log(expectations ? "expectations met" : "expectations NOT met");
  // The exit code a scheduler sees: 1 when the example itself is broken, 2 when any row is not ok, else 0.
  process.exit(!expectations ? 1 : isClean(report) ? 0 : 2);
}

main().catch((err) => {
  console.error(err instanceof Error ? err.message : err);
  process.exit(1);
});

5. Run

npx tsc
node --env-file=.env dist/run-and-reconcile.js; echo "exit=$?"

npx tsc
node --env-file=.env dist/run-and-reconcile.js; echo "exit=$?"

6. Verify the result

Output on 2026-10-20, workspace 1322578:

{"scenario":"inline","runStatus":"complete","total":6,"finished":6,"ok":3,"failed":2,"empty":1,"missing":0,"unexpected":0,"duplicate":0}
{"id":"obscure","verdict":"empty"}
{"id":"nonexistent","verdict":"failed","message":"Function column \"Enrich Company\": Invalid company identifier."}
{"id":"reserved-tld","verdict":"failed","message":"Function column \"Enrich Company\": Invalid company identifier."}
{"scenario":"batch with an invalid line","status":"validation_failed","hasResultUrl":false,"error":{"message":"Batch input has 1 invalid row.","total_invalid_rows":1,"details":[{"line_number":2,"field":"Company Domain","message":"Missing required field: Company Domain"}]}}
{"control":{"cleanInput":true,"droppedRowFlaggedMissing":true,"strayRowFlaggedUnexpected":true}}
expectations met
exit=2

{"scenario":"inline","runStatus":"complete","total":6,"finished":6,"ok":3,"failed":2,"empty":1,"missing":0,"unexpected":0,"duplicate":0}
{"id":"obscure","verdict":"empty"}
{"id":"nonexistent","verdict":"failed","message":"Function column \"Enrich Company\": Invalid company identifier."}
{"id":"reserved-tld","verdict":"failed","message":"Function column \"Enrich Company\": Invalid company identifier."}
{"scenario":"batch with an invalid line","status":"validation_failed","hasResultUrl":false,"error":{"message":"Batch input has 1 invalid row.","total_invalid_rows":1,"details":[{"line_number":2,"field":"Company Domain","message":"Missing required field: Company Domain"}]}}
{"control":{"cleanInput":true,"droppedRowFlaggedMissing":true,"strayRowFlaggedUnexpected":true}}
expectations met
exit=2

Exit code 2 is the expected result for this fixture, because the fixture contains rows that are not ok. The run reports complete for all 6 rows. The two invalid hosts are reported as failed by their own row status. The checks assert properties: the run status is complete, nonexistent and reserved-tld are classified as failed, no row is missing or unexpected, and the six verdicts add up to six. The counts of ok and empty rows depend on what providers return for real domains on the day, so they are not asserted. In this run jerrysmith.net came back empty and the other three domains returned a count; that is an illustration of 2026-10-20 and not part of the check.

The in-memory controls show that reconcile can fail. The clean two-row input is reported clean, the same input with one row removed reports one missing, and the input with an added row z reports one unexpected. The controls use hand-built rows because the live API did not drop or invent a row in any run made for this example.

How it works

Each submitted id is looked up in the rows that came back. A returned row with an id that was never submitted is unexpected, and a second row with an id already seen is duplicate. For a submitted id with no returned row the verdict is missing. For one with a row, a status other than complete is failed and carries the row's error.message. A complete row is ok only when the key the caller needs holds a non-blank value, and empty otherwise.

The run-level status and the counts total and finished describe whether the run has ended. They did not distinguish rows that failed from rows that succeeded: finished was 6 of 6 with two failed rows. For that reason the per-row check runs on the rows themselves. The rows are read with limit=100 and the cursor, because the default page of 20 rows would show up in this check as missing, as described in Reading Every Row of a Run over REST with TypeScript.

Common issues

Inline validation rejects the whole request

One row with a blank, null or missing required input returns 400 and no routine_run_id, so none of the other rows in that request run. Reconciliation of an inline run therefore starts after validation has passed. Check required inputs in the caller first, using the same rules: non-empty after trimming, a string, and for Company Domain a valid hostname.

Batch input is validated after the start call

run-batch/start returned 202 with in_progress for a file with an invalid line. The first poll returned validation_failed, error.total_invalid_rows and a details array with the line number and field. The poll response had no result_url, so a script that waits for result_url without checking the status would wait on a run that has already ended. This example did not check whether the valid line of the file ran.

A row can be complete and still hold nothing

jerrysmith.net returned status complete with "Employee Count": "". Checking status === "complete" alone counts that row as a success. The empty verdict exists for it.

A complete row is not necessarily a correct row

Reconciliation checks that every submitted row came back and had a value. It does not check the value. Company Domain returned domains for inputs that were not company names: zzzzzzzz returned simscell.com, and Qzxv Wplk Trmbl Holdings 9981 returned trimble.com. Those rows were complete with a non-empty Domain, so they would reconcile as ok. Validate content separately when it matters.

Failed rows come back in the result set

A failed row appears in data with status: "failed" and an error object instead of result. It is not dropped, which is why missing stayed at 0 here. This example did not run a case where a row was absent from a finished run.

Result order differs from submission order

Rows came back in a different order from the one submitted, so the comparison is by id and never by position.

Next steps

verification:
  status: verified
  tested_at: "2026-10-20"
  product_version: "public/v0"
  command: "npx tsc && node --env-file=.env dist/run-and-reconcile.js; echo exit=$?"

verification:
  status: verified
  tested_at: "2026-10-20"
  product_version: "public/v0"
  command: "npx tsc && node --env-file=.env dist/run-and-reconcile.js; echo exit=$?"
  expected_result: "The run reports complete with 6 of 6 finished, the rows nonexistent and reserved-tld are classified failed, no row is missing or unexpected, the batch with an invalid line reports validation_failed without a result_url, the in-memory controls flag the missing and extra rows, and the script prints `expectations met` and exits 2."