Supabase Idempotency Patterns for Autonomous AI Agents (Stop Your Agent From Doing Things Twice)

"My agent ran twice and charged the customer twice." That's a real sentence from a real founder. The fix took four lines of SQL. The refund took an hour and a customer email. This post gives you the four lines before the problem hits.

Autonomous agents retry. That's by design. A network blip, a webhook timeout, a task runner restart -- your agent fires again. Without idempotency, "retry" means "do it again." With idempotency, "retry" means "check whether it already happened, skip if yes."

Here are the five patterns we use in production, implemented against Supabase. All five patterns work with the Supabase JS client and the Python client. All five are copy-paste ready.

TL;DR: The five patterns are: idempotency keys on insert, Stripe webhook deduplication, state machine transitions, retry-safe action logs, and row-level locking for concurrent agents. Every pattern is under 10 lines of code. The key insight is that Supabase's ON CONFLICT DO NOTHING clause is your primary idempotency primitive -- use it on every table that an agent writes to.

Pattern 1: Idempotency keys on the Supabase row

This is the foundation. Before your agent starts any action, insert a row with a unique idempotency_key. The key encodes what action you're about to take and who you're taking it for. If the row already exists, Supabase returns 0 rows inserted, and you skip the action entirely.

Here's the table setup:

CREATE TABLE agent_actions (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  idempotency_key TEXT UNIQUE NOT NULL,
  action_type  TEXT NOT NULL,
  status       TEXT NOT NULL DEFAULT 'pending',
  result       JSONB,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

And here's the idempotent insert:

INSERT INTO agent_actions (idempotency_key, action_type, status, created_at)
VALUES ($1, $2, 'pending', now())
ON CONFLICT (idempotency_key) DO NOTHING;

In Python, check the row count after insert:

def claim_action(supabase, idempotency_key: str, action_type: str) -> bool:
    result = supabase.table("agent_actions").insert({
        "idempotency_key": idempotency_key,
        "action_type": action_type,
        "status": "pending"
    }, upsert=False, on_conflict="idempotency_key", ignore_duplicates=True).execute()
    # Returns True if row was inserted (action not yet run)
    # Returns False if row already existed (skip the action)
    return len(result.data) > 0

Key naming convention matters here. Use compound keys that encode the action + the subject: stripe_charge_{charge_id}, email_send_{subscriber_id}_{step_name}, webhook_{event_id}. If the key is unique per event, duplicate events are harmless.

Pattern 2: Stripe webhook deduplication with Supabase

Stripe sends webhooks with a unique event.id on every event. Stripe also retries delivery if your endpoint doesn't return 200 within 30 seconds. That means the same event can arrive multiple times. Store the event ID. Check before processing.

Table setup:

CREATE TABLE stripe_events (
  event_id     TEXT PRIMARY KEY,
  event_type   TEXT NOT NULL,
  processed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  payload      JSONB
);

Python handler:

def handle_checkout_completed(event_id: str, session: dict):
    # Try to insert the event_id. Fails silently if already processed.
    result = supabase.table("stripe_events").insert({
        "event_id": event_id,
        "event_type": "checkout.session.completed",
        "payload": session
    }, ignore_duplicates=True).execute()

    if not result.data:
        # Already processed this event. Safe to return 200 to Stripe.
        return {"status": "already_processed"}

    # First time seeing this event. Run fulfillment.
    fulfill_order(session)

One PRIMARY KEY on stripe_events.event_id is all the deduplication you need. No Redis, no distributed lock, no extra infrastructure. The database constraint is the lock.

This pattern also protects against the "Stripe sent us the same checkout event during a retry storm" scenario. We've seen Stripe retry the same event five times within two minutes during a deploy. With this table in place, only the first insert runs fulfillment. The rest return 200 immediately.

Pattern 3: State machine transitions for email automation

This is the email automation pattern we used when wiring a multi-step drip sequence. Each subscriber has a row in a subscriber_automation table. Each automation step is a boolean column that only ever flips from false to true. The flip is conditional: only flip if it's currently false.

CREATE TABLE subscriber_automation (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  subscriber_id       TEXT UNIQUE NOT NULL,
  welcome_sent        BOOLEAN NOT NULL DEFAULT false,
  welcome_sent_at     TIMESTAMPTZ,
  social_proof_sent   BOOLEAN NOT NULL DEFAULT false,
  social_proof_sent_at TIMESTAMPTZ,
  first_dollar_sent   BOOLEAN NOT NULL DEFAULT false,
  first_dollar_sent_at TIMESTAMPTZ
);

Here's the idempotent send pattern. Before sending, atomically claim the transition:

UPDATE subscriber_automation
SET welcome_sent = true, welcome_sent_at = now()
WHERE subscriber_id = $1 AND welcome_sent = false
RETURNING id;

If 0 rows are returned: the welcome email was already sent. Skip it. If 1 row is returned: you've atomically claimed this transition. Send the email now.

In Python:

def send_welcome_if_not_sent(supabase, subscriber_id: str) -> bool:
    result = supabase.table("subscriber_automation").update({
        "welcome_sent": True,
        "welcome_sent_at": "now()"
    }).eq("subscriber_id", subscriber_id).eq("welcome_sent", False).execute()

    if not result.data:
        return False  # Already sent, skip

    # 1 row updated -- claim confirmed. Send the email.
    send_welcome_email(subscriber_id)
    return True

The WHERE welcome_sent = false clause is the idempotency check. The RETURNING id is the claim confirmation. This pattern prevents double-sends even if two agent instances run simultaneously, because only one UPDATE can atomically set the column from false to true.

Pattern 4: Retry-safe agent action logs

For longer agent workflows -- a 3-step sequence like "source lead, enrich lead, send email" -- you need a way to resume after a crash mid-step. The pattern is an agent_runs table that tracks every action, its status, and its result.

CREATE TABLE agent_runs (
  run_id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  agent_name      TEXT NOT NULL,
  action_type     TEXT NOT NULL,
  idempotency_key TEXT UNIQUE NOT NULL,
  status          TEXT NOT NULL DEFAULT 'pending',
  -- status: pending | completed | failed
  result          JSONB,
  error_detail    TEXT,
  started_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  completed_at    TIMESTAMPTZ
);

On retry, look up the run by idempotency_key before executing:

def execute_or_resume(supabase, agent_name: str, action_type: str,
                       idempotency_key: str, action_fn):
    # Check for a prior run
    existing = supabase.table("agent_runs").select("*").eq(
        "idempotency_key", idempotency_key
    ).maybe_single().execute()

    if existing.data:
        if existing.data["status"] == "completed":
            # Already done. Return the prior result.
            return existing.data["result"]
        if existing.data["status"] == "failed":
            # Prior attempt failed. Re-run from scratch.
            pass  # fall through to execution below

    # Insert a pending run row (or skip if already inserted)
    supabase.table("agent_runs").insert({
        "agent_name": agent_name,
        "action_type": action_type,
        "idempotency_key": idempotency_key,
        "status": "pending"
    }, ignore_duplicates=True).execute()

    # Execute the action
    try:
        result = action_fn()
        supabase.table("agent_runs").update({
            "status": "completed",
            "result": result,
            "completed_at": "now()"
        }).eq("idempotency_key", idempotency_key).execute()
        return result
    except Exception as e:
        supabase.table("agent_runs").update({
            "status": "failed",
            "error_detail": str(e)
        }).eq("idempotency_key", idempotency_key).execute()
        raise

This makes the 3-step workflow resumable. If the agent crashes after step 1, the next run sees step 1 as completed, returns the prior result, and moves straight to step 2. No re-running work that already finished.

Dealing with SMTP as well? The same idempotency logic applies to your email sends. See SMTP without SendGrid for the Python SMTP handler that slots into this pattern.

Pattern 5: Row-level locking for concurrent agents

When multiple agent instances might run at the same time -- a scheduled task fires while a manually triggered run is still in progress -- you need to prevent two agents from claiming the same work item. SELECT ... FOR UPDATE SKIP LOCKED is exactly this.

-- Claim one pending work item, skipping any rows already locked by another agent
SELECT id, payload FROM work_queue
WHERE status = 'pending'
ORDER BY created_at ASC
LIMIT 1
FOR UPDATE SKIP LOCKED;

In Python using the Supabase RPC or raw SQL:

def claim_next_work_item(supabase):
    result = supabase.rpc("claim_work_item").execute()
    return result.data  # None if no unclaimed items available

# Supabase function (create this in your Supabase SQL editor):
CREATE OR REPLACE FUNCTION claim_work_item()
RETURNS SETOF work_queue AS $$
BEGIN
  RETURN QUERY
    UPDATE work_queue
    SET status = 'in_progress', claimed_at = now()
    WHERE id = (
      SELECT id FROM work_queue
      WHERE status = 'pending'
      ORDER BY created_at ASC
      LIMIT 1
      FOR UPDATE SKIP LOCKED
    )
    RETURNING *;
END;
$$ LANGUAGE plpgsql;

Here's why this works: FOR UPDATE acquires a row lock. SKIP LOCKED tells Postgres to skip any rows that are already locked instead of waiting. So if two agents run simultaneously, agent A claims row 1, agent B skips row 1 and claims row 2. No distributed lock manager, no Redis queue, no coordination service. Postgres handles it.

How to wire these five patterns together

These patterns aren't mutually exclusive. In a real agentic system, you use all five at different layers:

Pattern 1 (idempotency keys) is the default for any agent action. Use it everywhere.
Pattern 2 (Stripe webhook) lives in your webhook handler. Wire it on day one before you go live.
Pattern 3 (state machine) lives in your email/notification layer. Every automation step gets a column.
Pattern 4 (action logs) lives in any multi-step agent workflow. Wrap every step with execute_or_resume.
Pattern 5 (row locking) lives in your work queue if you have concurrent agent instances. The Supabase RPC approach keeps the locking inside the database where it belongs.

The order to implement if you're starting from scratch: Pattern 1 first (30 minutes), Pattern 2 if you have Stripe (30 minutes), Pattern 3 if you have email automation (1 hour), Pattern 4 for multi-step workflows (2 hours), Pattern 5 if you have concurrent agents (1 hour). Total: a half-day of work that prevents the class of bugs that cause customer-visible incidents.

The cost of skipping this

Here's what actually happens in production without these patterns. Your scheduled agent fires at 06:00. The webhook handler times out at 06:00:29 (Stripe's 30-second window). Stripe retries at 06:05. Both the original and the retry run fulfillment. The customer gets charged twice. You spend an hour on a refund and a damage-control email.

Or: your email automation fires. The network call to your SMTP provider times out. The agent retries. The SMTP call succeeds on retry, but the first call also succeeded -- it just timed out before your handler got the 200 response. The subscriber gets the welcome email twice. Small thing. But two identical emails in a row is a trust signal going the wrong direction.

Both scenarios are entirely preventable with the patterns above. The INSERT with ON CONFLICT DO NOTHING and the UPDATE with WHERE already_done = false are two of the most useful SQL patterns I know for autonomous systems. They're not new -- idempotent database operations have been a backend engineering standard for years. But the agentic context makes them more important, because agents retry by default, and the retry window can be very short.

Wire these patterns before you go live. The conversation where you explain to a customer why their card was charged twice is a conversation worth skipping.

Building an autonomous AI system and hitting state management problems? We've solved the Supabase patterns, the Stripe webhook deduplication, and the email delivery idempotency -- in production. The Concierge implementation service walks you through the full stack, or start with the free AI Visibility Check at https://operatoriq.io/audit/.