Reset an agent's event counter

Recompute agents.total_events, first_seen_at, and last_event_at from the events table when the denormalized counter has drifted.

When to use this

After a manual delete from events where ... that wiped a chunk of the agent's history. The 0036 trigger now decrements total_events automatically on every event delete, so this should rarely be needed — but it stays here for migrations from before that trigger landed and for any direct DB-write that bypasses normal channels.
After a backfill where the agent was pre-existing and the script's total_events increment overshot or undershot the actual count.

Steps

const { createClient } = require("@supabase/supabase-js");
const c = createClient(process.env.URL, process.env.KEY);

(async () => {
  const orgId = "...";
  const agentId = "...";

  const { count } = await c
    .from("events")
    .select("id", { count: "exact", head: true })
    .eq("org_id", orgId)
    .eq("agent_id_text", agentId);

  const { data: firstEv } = await c
    .from("events")
    .select("occurred_at")
    .eq("org_id", orgId)
    .eq("agent_id_text", agentId)
    .order("occurred_at", { ascending: true })
    .limit(1)
    .maybeSingle();

  const { data: lastEv } = await c
    .from("events")
    .select("occurred_at")
    .eq("org_id", orgId)
    .eq("agent_id_text", agentId)
    .order("occurred_at", { ascending: false })
    .limit(1)
    .maybeSingle();

  await c
    .from("agents")
    .update({
      total_events: count,
      first_seen_at: firstEv?.occurred_at ?? null,
      last_event_at: lastEv?.occurred_at ?? null,
    })
    .eq("org_id", orgId)
    .eq("agent_id", agentId);
})();

The first_seen_at is intentionally allowed to slide forward when older events are deleted; the trigger in migration 0036 keeps last_event_at recomputed but leaves first_seen_at alone, so this script lets you also rewind first_seen_at if that matches the customer's intent ("forget the deleted history entirely").