How to Replace Clay With Codex, Part 2: Build a Resumable Orchestrator

A Clay table appears to remember what happened because each row keeps its visible values. Once you replace that table with Codex workers and API calls, you must build that memory deliberately. The orchestrator is the component that remembers what was intended, what started, what finished, what failed, and what may safely run again.

A worker pool is not an orchestrator

A worker receives one bounded input and returns one bounded result. It should not decide the global batch size, invent a retry budget, merge the final file, or infer whether a previous run already paid for the same provider call.

The orchestrator owns those cross-row decisions:

• The immutable run manifest and its input hash.
• Stable row, batch, step, and attempt identities.
• Which work is eligible to start and which worker owns it.
• Concurrency limits for each provider, model, and destination.
• Checkpoint storage, retry timing, maximum attempts, and run budget.
• Deterministic assembly of final successes, holds, and failures.

OpenAI's current Codex documentation makes the same distinction at the agent layer. Codex can spawn specialized agents in parallel and collect their results, while the main thread keeps requirements, decisions, and final synthesis. It also warns that parallel write-heavy work creates coordination risk. That is why the database, not a worker's memory, owns execution state.

Freeze one immutable run manifest

Every run begins with a manifest that can be hashed and compared later. Do not point workers at a changing spreadsheet and ask them to process whatever they see.

{
  "run_id": "run-2026-07-16-001",
  "workflow_version": "clay-replacement-v2",
  "input_artifact": "leads-approved.csv",
  "input_sha256": "...",
  "row_key": "lead_id",
  "row_count": 5000,
  "batch_size": 100,
  "max_in_flight": 12,
  "max_provider_calls": 9000,
  "steps": [
    "normalize-v3",
    "domain-research-v2",
    "email-waterfall-v4",
    "qualification-v6"
  ]
}

If the input file, workflow version, step order, or budget changes, create a new run. A resumable system continues the same declared work. It does not quietly turn a changed input into the same run ID.

Use four levels of stable identity

1. Run ID. One immutable execution contract.
2. Batch ID. A bounded slice used for scheduling and reconciliation.
3. Row ID. A stable business key that survives file ordering and retries.
4. Step ID. A versioned operation such as `email-waterfall-v4`.

Attempt number is metadata, not identity. The third attempt at `run-001:lead-924:email-waterfall-v4` is still the same intended effect. That distinction is what lets the system retry without pretending the retry is new work.

Give every job an explicit state

A single `done` column cannot explain what happened after a crash. Use a small state machine instead.

Claim work atomically

Two workers must not be able to claim the same job. On one host, SQLite can provide a compact state store. On multiple hosts, use a shared transactional database such as PostgreSQL or a durable workflow engine.

CREATE TABLE jobs (
  run_id TEXT NOT NULL,
  row_id TEXT NOT NULL,
  step_id TEXT NOT NULL,
  status TEXT NOT NULL,
  lease_owner TEXT,
  lease_expires_at TEXT,
  attempt_count INTEGER NOT NULL DEFAULT 0,
  input_hash TEXT NOT NULL,
  output_hash TEXT,
  last_error_code TEXT,
  PRIMARY KEY (run_id, row_id, step_id)
);

UPDATE jobs
SET status = 'RUNNING',
    lease_owner = :worker_id,
    lease_expires_at = :lease_deadline,
    attempt_count = attempt_count + 1
WHERE run_id = :run_id
  AND row_id = :row_id
  AND step_id = :step_id
  AND status IN ('PENDING', 'RETRY_WAIT');

The worker proceeds only when that update changes exactly one row. SQLite's write-ahead log can improve same-host concurrency because readers and a writer can operate at the same time. SQLite also states that WAL requires all database processes to be on the same host, so it is not the right shared queue for workers spread across machines.

Build idempotency before you build retries

A retry is safe only when the intended side effect has a stable identity. A practical key is:

run_id : row_id : step_id : effect_version

run-2026-07-16-001 : lead-924 : email-waterfall-v4 : provider-call-v1

Store that key with the request hash, provider request ID, response hash, cost, and final state. If the provider accepts idempotency keys, send it. If it does not, keep your own effect ledger and reconcile ambiguous outcomes before trying again.

SQLite's `UPSERT` behavior is useful for a local effect ledger because a uniqueness conflict can become an update or a no-op. But a local uniqueness constraint cannot make an external API exactly once by itself. If the process dies after the provider accepts a request but before your local checkpoint commits, only provider-side idempotency or a reliable provider readback can remove the ambiguity.

Checkpoint on both sides of a consequential effect

For a paid provider call, destination write, or model run, record at least these moments:

1. Intent committed. The stable effect key, payload hash, and budget reservation exist before the call.
2. Attempt started. The worker, attempt number, and timestamp are recorded.
3. Provider acknowledged. The remote request ID and raw status are stored.
4. Result accepted. Schema checks, evidence checks, cost, and output hash pass.
5. Step committed. The next step becomes eligible.

Do not unlock the next step merely because a command exited with code zero. The checkpoint should describe the accepted business result.

Limit concurrency by the bottleneck

More workers can make an I/O-heavy enrichment run faster, but only until a provider rate limit, model budget, database writer, or destination API becomes the bottleneck. Use separate caps, not one global number:

• A maximum number of rows in flight.
• A lower semaphore for each paid provider.
• A token or cost budget for model workers.
• A small write queue for the state database.
• Backpressure when unresolved results exceed the review capacity.

Python's `concurrent.futures` provides a high-level interface for asynchronous callables through thread, process, or interpreter executors. For network-heavy workers, a bounded `ThreadPoolExecutor` is often enough to start. The Python documentation also shows how workers that wait on other futures can deadlock. Keep dependency scheduling in the parent orchestrator instead of letting workers wait on child jobs.

with ThreadPoolExecutor(max_workers=8) as pool:
    futures = {
        pool.submit(run_one_step, job): job.job_id
        for job in claim_eligible_jobs(limit=8)
    }

    for future in as_completed(futures):
        job_id = futures[future]
        record_result(job_id, future.result())

Use Codex as a bounded worker interface

OpenAI documents `codex exec` for scripts and CI. It streams progress to standard error and places the final agent message on standard output, which gives an orchestrator a clean separation between logs and the result it needs to validate. It also runs read-only by default, so permissions can be expanded only for steps that truly need them.

payload = json.dumps({
    "job_id": job.job_id,
    "input": job.input,
    "required_schema": "qualification-v6.json"
})

result = subprocess.run(
    ["codex", "exec", "--ephemeral", WORKER_PROMPT],
    input=payload,
    text=True,
    capture_output=True,
    timeout=180,
    check=False
)

validate_json(result.stdout)
store_worker_log(result.stderr)

Codex subagents can also use different instructions and model configurations. Keep that routing declarative in the manifest. The orchestrator should know which worker profile is eligible, but Part 3 will define the evidence and schema contract that every profile must satisfy.

Classify failures before retrying

Retry policy belongs to the step definition. Do not use one generic `try three times` rule.

Controlled crash test: stop after the call, before the checkpoint

We built a synthetic local test for the most dangerous recovery window. It is a mechanism test, not a production case study.

1. Create 50 pending rows in one SQLite state database.
2. Use a second SQLite database as the simulated paid provider, with a unique idempotency key for every intended call.
3. Process rows sequentially and force the process to exit on row 23 immediately after the provider commits, but before the local job is marked complete.
4. Restart the orchestrator with five workers.
5. Recover the one interrupted running job, submit the same provider key again, and finish every remaining row.

The resumed run produced this exact summary:

{
  "total_rows": 50,
  "recovered_running_jobs": 1,
  "done": 50,
  "pending": 0,
  "running": 0,
  "failed": 0,
  "unique_paid_calls": 50,
  "provider_attempts": 51,
  "duplicate_paid_calls_prevented": 1,
  "max_job_attempts": 2
}

The interrupted row was attempted twice, but its stable provider key created only one unique paid call. The second attempt was recorded as a prevented duplicate. Every row reached a terminal success state, and no pending or running job remained.

Recovery is a procedure, not a reset button

When a process stops, use this order:

1. Freeze new dispatch for the affected run.
2. Identify expired leases and prove the prior worker is gone.
3. Keep committed successes immutable.
4. Read back ambiguous external effects before retrying them.
5. Return only safe interrupted jobs to pending.
6. Apply the original retry and budget limits.
7. Reconcile row counts, step counts, unique effect keys, and final output hashes.

A resume that starts the CSV from row one is not recovery. It is a new run with a higher chance of duplicate spend.

Know when SQLite is no longer enough

SQLite is a good starting point when one machine owns the orchestrator, the state fits comfortably on local disk, and you want a transactional file with few moving parts. WAL can improve same-host read and write concurrency.

Move to PostgreSQL when multiple machines need to claim work from the same queue, when operational reporting needs shared access, or when database availability must outlive one host. Use a durable workflow engine when runs last hours or days across many services and you do not want to hand-build timers, replay, and crash recovery. Temporal's current documentation describes that category as durable execution that resumes applications after crashes, network failures, or infrastructure outages.

Acceptance tests for the orchestrator

Do not approve Part 2 because the happy path produced a CSV. Run these tests:

• Kill the process before a provider call and confirm the job can retry.
• Kill it after the provider call but before the local checkpoint and confirm readback or idempotency prevents a duplicate effect.
• Return 429, timeout, permanent 400, and malformed JSON responses and verify different routes.
• Run the same manifest twice and confirm committed steps are skipped.
• Change the input hash and confirm the orchestrator refuses to resume the old run.
• Exhaust the provider and model budgets and confirm new paid work stops.
• Leave one worker lease active and confirm another worker cannot steal it early.
• Reconcile expected, succeeded, held, dead, and missing row counts to the manifest.

Definition of done for Part 2

1. Every intended unit has stable run, batch, row, and step identity.
2. Only one worker can claim a job at a time.
3. Every consequential effect has a stable idempotency or readback path.
4. Concurrency is bounded separately for workers, providers, models, and writers.
5. Failures are typed before retries are scheduled.
6. A forced crash can resume without rerunning committed work.
7. Final reconciliation accounts for every row and every paid effect.

What Part 3 will build

The orchestrator can now move work safely, but it still needs to know what a valid worker result looks like. Continue to Part 3 for versioned request and response schemas, evidence arrays, provenance, confidence semantics, typed failures, and fail-closed model routing.

Frequently asked questions

What should replace Clay?

A Codex orchestrator can replace Clay's execution layer when you pair it with a durable state store, the data providers you still need, tested worker contracts, and downstream systems such as your CRM or sequencer.

What is an alternative to Clay for custom outbound automation?

For a technical team, one alternative is a Python or TypeScript orchestrator that invokes Codex or Claude Code workers, provider APIs, and deterministic validation. For a non-technical team, keeping Clay or using a hybrid can be the better operational choice.

Can Codex process lead batches in parallel?

Yes. Codex supports parallel subagent workflows, and `codex exec` can run inside scripts. The parent system still needs bounded concurrency, stable job identity, checkpoints, and deterministic result validation.

How do I prevent duplicate enrichment charges?

Create one stable effect key per run, row, step, and version. Send it to providers that support idempotency. Otherwise store an effect ledger and read back ambiguous provider outcomes before retrying.

Is retrying the same as resuming?

No. A retry repeats one failed step under a defined policy. A resume reconstructs the whole run state, preserves committed work, resolves ambiguous effects, and dispatches only eligible unfinished jobs.

How many workers should I run?

Start below the strictest provider, model, database, or destination limit. Increase concurrency only after measuring error rate, queue time, cost, and backpressure. There is no universal safe worker count.

Does SQLite guarantee exactly-once API calls?

No. SQLite can make local claims and ledger inserts transactional. Exactly-once external behavior still requires provider-side idempotency or a reliable remote readback and reconciliation path.