Idempotency and retries

Agents retry on network errors. Without idempotency, a retry can cause duplicate writes. PerSQL caches the response of any request that includes an Idempotency-Key for 24 hours.

SDK

await db.query(
  "INSERT INTO events (id, name) VALUES (?, ?)",
  ["e-1", "click"],
  { idempotencyKey: "evt-e-1-2026-04-29" }
);

If the request succeeds, a retry with the same key (within 24 hours) returns the cached response without re-running the SQL. The HTTP response includes Idempotency-Replayed: true so you can tell.

What it guarantees

The key makes a retry safe — a second attempt sent after the first one returned. That’s the usual agent case: a timeout or dropped connection, then a retry a moment later.

It’s best-effort for simultaneous requests: two writes carrying the same key fired at the same instant can both run, because the cached response only exists once the first has returned. Idempotency keys behave this way across the industry — Stripe, for instance, returns a 409 for an in-flight key rather than de-duplicating it.

For a write that must be exactly-once even under genuine concurrency, enforce it in your schema with a UNIQUE or PRIMARY KEY constraint. Each PerSQL database has a single writer, so the constraint is applied atomically and the duplicate INSERT simply fails — the airtight backstop an idempotency key can’t provide on its own:

CREATE TABLE events (
  id   TEXT PRIMARY KEY,   -- the same id can never insert twice
  name TEXT NOT NULL
);

INSERT INTO events (id, name) VALUES (?, ?)
  ON CONFLICT (id) DO NOTHING;

Use both together: the key saves the round trip on a routine retry, the constraint guarantees correctness when two writes truly race.

Choosing a key

Good keys are:

• Stable across retries — pick something derived from the operation itself (e.g. evt-${eventId} or order-${orderId}-charge).
• Unique to that operation — don’t reuse keys across different writes.
• Short — capped at 200 characters.

A common pattern in agent loops is to derive the key from the model’s tool_use_id:

await db.query(input.sql, input.params, {
  idempotencyKey: toolUse.id,
});

Multi-step plans (Plan-Key)

Idempotency-Key covers a single request. An agent’s plan is usually a sequence — schema migration, then seed, then verify. If step 3 of a 5-step plan fails and the agent retries, you don’t want steps 1 and 2 to re-run.

Plan-Key + Plan-Step give you sequence-level idempotency. Pair a plan id (stable across the plan) with a step id (stable per step within the plan):

const planKey = `migrate-${migrationId}`;
await db.query(step1Sql, [], { planKey, planStep: "create-tables" });
await db.query(step2Sql, [], { planKey, planStep: "seed-defaults" });
await db.query(step3Sql, [], { planKey, planStep: "backfill" });

On retry, every step that already returned 2xx replays from cache; failed and never-reached steps re-run. The HTTP response carries Plan-Replayed: true on cache hits.

Storage shape: per-step KV row keyed by (tokenId, planKey, stepId), 24-hour TTL. Keys are namespaced by api token, so a leaked plan-key can’t hijack a different bearer’s plan.

Rate limits

There is no per-token throughput cap — the prepaid balance is the spend control. A coarse per-IP flood control at the unauthenticated edge can return 429 with a Retry-After header for anonymous floods; the SDK throws a RateLimitError you can catch:

import { RateLimitError } from "@persql/sdk";

try {
  await db.query("SELECT 1");
} catch (e) {
  if (e instanceof RateLimitError) {
    await new Promise((r) => setTimeout(r, e.retryAfterSeconds * 1000));
    // retry…
  }
}