Idempotent drafts: keeping a scheduled agent from writing the same post again

A scheduler is an at-least-once system wearing an exactly-once costume. You register a job, it fires on a clock, and for a while the mental model holds: the job runs when you said it would, the drafter calls a model, a row lands in the database, you go look at it.

Then reality shows up. The container restarts mid-run. You redeploy during a scheduled window. The host sleeps and the scheduler catches up on missed runs when it wakes. A model call times out at the transport layer, your retry wrapper fires, and the first call had already succeeded on the server side. The job body runs again, and because the job body is "draft something and insert it," you get another row.

Duplicate drafts are not a cosmetic problem. A drafting agent exists to feed a human review step, and review is the scarce resource. Every duplicate row is a decision a person has to make and then unmake. Worse, the duplicates are not identical: same schedule, same prompt, different sampling, so the reviewer cannot even skim and dismiss. They have to read both.

The fix is not a better scheduler. It is making the job body idempotent, so running it again is a no-op instead of a second draft.

The check-then-write trap

The obvious first move is to look before you leap:

def run(conn, platform, slot):
 existing = conn.execute(
 "SELECT id FROM drafts WHERE platform = ? AND slot = ?",
 (platform, slot),
 ).fetchone()
 if existing:
 return
 body = drafter.write(platform=platform, slot=slot)
 conn.execute(
 "INSERT INTO drafts (platform, slot, body, status) VALUES (?, ?, ?, 'ready')",
 (platform, slot, body),
 )
 conn.commit()

This is a time-of-check-to-time-of-use race with a very wide window in the middle, because drafter.write is a network call to a model. Anything that starts a second execution while the first one is waiting on that call passes the SELECT, because the insert has not happened yet. The check is honest and still wrong.

The general fix for this shape of bug is to stop asking the database a question and start giving it a constraint it can enforce.

Derive a key, let the database refuse

Give every draft a key that is a pure function of the work it represents, not of when the work happened:

def draft_key(platform: str, slot: str, recipe: str) -> str:
 return "|".join([platform, slot, recipe])

Three parts, and what goes in each one matters:

• platform is the destination. The same idea drafted for different destinations is different work.
• slot is the scheduled occurrence, derived from the run's scheduled time, not from the wall clock at execution. scheduled_for.strftime("%Y-%m-%dT%H") gives you a stable string that a catch-up run and the original run both produce. If you use now() here, a catch-up run gets its own key and you are back where you started.
• recipe is a version marker for the drafter and prompt. This is the escape hatch: when you intentionally want a fresh draft for a slot you already drafted, you bump the recipe, and the new key is legitimately new.

Keep the key readable if you can. A key you can eyeball in a SQL shell is worth a lot during debugging. If the parts get long, hash them, but then log the plain parts next to the hash.

Then put the constraint where it cannot be raced:

CREATE TABLE IF NOT EXISTS drafts (
 id INTEGER PRIMARY KEY,
 key TEXT NOT NULL,
 platform TEXT NOT NULL,
 slot TEXT NOT NULL,
 status TEXT NOT NULL,
 body TEXT
);

CREATE UNIQUE INDEX IF NOT EXISTS drafts_key ON drafts (key);

If a dashboard reads the same database through Prisma, express the constraint there too so migrations carry it rather than living in a stray .sql file:

model Draft {
 id String @id @default(cuid())
 key String @unique
 platform String
 slot String
 status String
 body String?
}

Claim the slot before you spend the model call

A unique index alone turns the duplicate into a failed insert, which is correct but wasteful: you already paid for the model call before the database told you the work was redundant. Invert the order. Claim the key first, cheaply, then fill in the body.

def claim(conn, key, platform, slot):
 cur = conn.execute(
 "INSERT INTO drafts (key, platform, slot, status) "
 "VALUES (?, ?, ?, 'claimed') "
 "ON CONFLICT(key) DO NOTHING",
 (key, platform, slot),
 )
 conn.commit()
 return bool(cur.rowcount)


def run(conn, platform, slot, recipe):
 key = draft_key(platform, slot, recipe)
 if not claim(conn, key, platform, slot):
 log.info("slot already claimed, skipping: %s", key)
 return
 body = drafter.write(platform=platform, slot=slot)
 conn.execute(
 "UPDATE drafts SET body = ?, status = 'ready' WHERE key = ?",
 (body, key),
 )
 conn.commit()

ON CONFLICT DO NOTHING plus rowcount is the whole trick. The insert either wins or it does not, decided inside the database, and the loser never calls the model. There is no window to race because the claim is one statement.

Two SQLite details make this behave under concurrency. Turn on WAL so a reader (a dashboard rendering the review queue) does not block the writer: PRAGMA journal_mode=WAL. And set a busy timeout on every connection, so a writer that arrives during another write waits for the lock instead of raising immediately. Without the timeout, a claim that collides with an unrelated write surfaces as a locked-database error, and you will misread it as a bug in the claim logic.

Keep writes short, too. Claim, release, do the slow network work outside any transaction, then reopen for the update. Holding a write transaction open across a model call is the fastest way to make a single-file database feel broken.

Where this approach stops helping

It catches exact re-execution of the same unit of work. It does not deduplicate meaning. Bump the recipe and you get a new key and a genuinely new draft, even if the model produces text that reads almost the same as what is already sitting in review. Semantic near-duplicates are a different problem and a unique index will never see them.

It leaves a half-finished state you have to handle. If the process dies between the claim and the update, you keep a claimed row with a null body, and the key is now taken, so retries skip it forever. You need a sweeper that finds claimed rows with no body older than a cutoff you choose and either resets them to unclaimed or deletes them. Pick that cutoff to be comfortably longer than your slowest model call, otherwise the sweeper starts stealing slots from runs that are still working.

It does nothing about ordering or about throughput. Idempotency is not a queue. If you need retries with backoff, priorities, or workers on separate machines, you want a real job store, and at that point SQLite is not the right shared surface.

And it does not solve the actual hard part of a content agent, which is the approval step. A clean, deduplicated queue that nobody reviews is still a pile of unpublished text. The value of the key is that it protects the reviewer's attention, which only matters if there is a place to review.

If you want that half already running instead of assembled from scratch, I build and maintain the AI Content Agent Kit: a Python drafting agent and a Next.js approval dashboard in one docker-compose, MIT licensed.

https://fulcrumenterprises.tech/go/content-agent-kit/?c=devto