agentleFS
open-thoughtsopen-thoughts/OpenThoughts-Agent/.agents/skills/crud-purge-stale-eval-placeholders/SKILL.md

crud-purge-stale-eval-placeholders

Skill309 starsChanged 4 months agoApache-2.0View on GitHub

Safely purge stale, never-populated `sandbox_jobs` placeholder rows (eval launches that died/stalled before scoring) from the OT-Agent Supabase registry. Removes ONLY dead `Pending`/`Started` rows WE OWN that are >36h old with null `metrics`/`stats`/`ended_at`, via the mandatory cross-user FK-safety pre-check + the REQUIRED grandchild→child→job cascade delete (`sandbox_trial_model_usage` → `sandbox_trials` → `sandbox_jobs`). Other users' stale rows are REPORTED, never deleted. DRY-RUN first, then delete, then re-read. Use when the registry is clogged with dead placeholder eval rows, when a sweep flags stale Pending/Started/"Running" eval entries, or when the eval listener's dedup is mis-firing on dead rows. The general CRUD/read/aggregation skill is `crud-otagent-supabase`.

The file

We split this skill into the rules it gives an agent

2 rules. About 0 are common, 0 are distinctive, and 2 are very specific to this project.

  • ≈0

    Common · estimate

    Many files like this have a rule of this kind.

  • ≈0

    Distinctive · estimate

    This author's own choices. Worth a look.

  • 2

    Very specific

    Tied to their project's names, files or tools. Probably skip.

  • Common
  • Distinctive
  • Very specific
  1. Very specific:

    Run from the Mac with the otagent env.

    line 23
  2. Very specific:

    DRY-RUN first, then delete, then re-read: Leave DELETE = False until you've reviewed the sample.

    line 157

≈ “Common” is an estimate: at least 1 in 5 of the 364,980 skill files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.

Keep what fits and drop the rest. Your version, ready for your agents.
Files
SKILL.md

crud-purge-stale-eval-placeholders

A guardrailed DELETE of dead placeholder sandbox_jobs rows left when an eval launch dies/stalls before it scores. Every launch creates a placeholder (Pending→Started) before a result exists; if the run dies, the placeholder remains (no Finished, no metrics, no stats), clogging the table and the eval listener's dedup. Removes only the dead placeholders we own.

This is a DELETE on a shared table. The cross-user FK-safety pre-check (§2) and the cascade (§3) are both MANDATORY — a plain sandbox_jobs delete FK-fails Postgres 23503, and skipping the ownership check can wipe another user's rows. DRY-RUN first, then delete, then re-read.

0. Connect (run LOCALLY)

Run from the Mac with the otagent env; source the local secrets (sets SUPABASE_URL + SUPABASE_SERVICE_ROLE_KEY).

cd /Users/benjaminfeuer/Documents
set -a; source "${DC_AGENT_SECRET_ENV:?set DC_AGENT_SECRET_ENV to the secrets file first}"; set +a
/Users/benjaminfeuer/miniconda3/envs/otagent/bin/python - <<'PY'
import os
from supabase import create_client
c = create_client(os.environ["SUPABASE_URL"], os.environ["SUPABASE_SERVICE_ROLE_KEY"])
PY
  • SUPABASE_SERVICE_ROLE_KEY bypasses RLS (full read/write) — nothing stops you mutating other users' rows, which is why §2 is mandatory.
  • Schema DDL lives at /Users/benjaminfeuer/Documents/OpenThoughts-Agent/schema/ — read sandbox_jobs / sandbox_trials / sandbox_trial_model_usage when unsure of a column.

The metrics field has TWO shapes — always extract via the helper

metrics is jsonb and appears as either a list of {"name","value"} dicts or a plain dict. The qualifier (§1) tests metrics is None via this shape-robust helper — never via metrics["accuracy"] directly:

def get_metric(metrics, key="accuracy"):   # key: "accuracy" or "accuracy_stderr"
    if metrics is None: return None
    if isinstance(metrics, dict):           # {"accuracy": 0.25, ...}
        return metrics.get(key)
    if isinstance(metrics, list):           # [{"name":"accuracy","value":0.25}, ...]
        for e in metrics:
            if isinstance(e, dict):
                if e.get("name") == key:    return e.get("value")
                if e.get(key) is not None:  return e.get(key)
    return None

1. What makes a row "stale and removable"

Schema facts that drive the filter (verified against schema/sandbox_jobs):

  • Timestamps are created_at / started_at / ended_at / submitted_at — there is NO updated_at. Use created_at for absolute age, and started_at (set when the job leaves Pending) as the secondary recency gate.
  • n_trials is the PLANNED trial count from config, NOT progress — a brand-new placeholder already has n_trials=128. Do NOT read n_trials as a "populated" signal.
  • The real "never populated" signals are metrics IS NULL (no score) AND stats IS NULL (no per-trial progress). Empirically every Pending/Started row has BOTH null; a live job that had begun scoring would have a non-null stats. (ended_at is also always null for these.)
  • job_status enum: Pending / Started / Finished (+ failure states). There is no literal "Running" status — a stale "running" entry is a stale Started row.

A row qualifies for removal iff ALL hold:

  1. job_status IN ('Pending','Started') — never Finished/a failure state.
  2. metrics IS NULL (no real accuracy via get_metric) AND stats IS NULL (never populated).
  3. ended_at IS NULL (didn't terminate into a recorded result).
  4. Age > 36h: created_at ≥ 36h ago AND, if started_at is set, started_at ≥ 36h ago (whichever is more recent must still be older than 36h) — so a legitimately-RUNNING recent eval (Pending/Started but <36h) is EXCLUDED.

2. Cross-user FK safety (MANDATORY pre-check)

Restrict every delete to rows you own; never delete another user's rows without authorization.

Default-scope to OUR rows (the eval/re-eval owners feuer1, bfeuer00, penfever, benjaminfeuer — all four are the operator's own accounts; matches the sibling crud-purge-below-gate-evals OURS). Stale rows owned by GENUINELY OTHER users (zhuang1, richard.zhuang, …) are REPORTED with counts, never deleted — surface them to the supervisor. The match and the job-delete are both scoped by username IN OURS so a scope error cannot leak across users.

3. The cascade — sandbox_jobs.id IS FK'd (REQUIRED)

sandbox_jobs.id IS FK'd by a child chain — a plain delete fails Postgres 23503 foreign-key-violation. The chain is:

sandbox_trial_model_usage.trial_id → sandbox_trials.id → sandbox_jobs.id
         (grandchild)                     (child)            (job)

To delete a sandbox_jobs row you MUST cascade grandchild → child → job: delete its sandbox_trial_model_usage rows, then its sandbox_trials rows, then the sandbox_jobs row. The children carry NO username — ownership is TRANSITIVE from the job, so once you've asserted you own the JOB (§2), the whole cascade is FK-safe and yours. Still NEVER delete a job (or its cascade) you don't own.

4. Procedure — DRY-RUN, review, delete, re-read

import os
from datetime import datetime, timezone, timedelta
from supabase import create_client
c = create_client(os.environ["SUPABASE_URL"], os.environ["SUPABASE_SERVICE_ROLE_KEY"])
NOW = datetime.now(timezone.utc); CUTOFF_H = 36
OURS = {"feuer1", "bfeuer00", "penfever", "benjaminfeuer"}   # the operator's eval/re-eval owners we may delete

def age_h(ts):                          # hours since an ISO ts (None -> None)
    return None if not ts else (NOW - datetime.fromisoformat(ts)).total_seconds()/3600

def qualifies(r):
    if r["job_status"] not in ("Pending", "Started"):       return False
    if get_metric(r["metrics"]) is not None:                return False   # has a real score
    if r["stats"] is not None:                              return False   # has progress -> not "never populated"
    if r["ended_at"] is not None:                           return False   # terminated into a result
    ca = age_h(r["created_at"]); sa = age_h(r["started_at"])
    recent = min(x for x in (ca, sa) if x is not None)      # most-recent activity
    return recent is not None and recent > CUTOFF_H         # older than 36h

rows = c.table("sandbox_jobs").select(
    "id,job_name,username,job_status,created_at,started_at,ended_at,n_trials,metrics,stats,model_id,benchmark_id"
).in_("job_status", ["Pending", "Started"]).execute().data
q = [r for r in rows if qualifies(r)]

# Safety assert: nothing we matched may carry a real score/progress (never guess-delete)
bad = [r for r in q if r["stats"] is not None or get_metric(r["metrics"]) is not None]
assert not bad, f"STOP: {len(bad)} matched rows have stats/metrics — ambiguous, surface to supervisor"

ours   = [r for r in q if r["username"] in OURS]
others = [r for r in q if r["username"] not in OURS]
from collections import Counter
bm = {b["id"]: b["name"] for b in c.table("benchmarks").select("id,name").execute().data}
mn = {m["id"]: m["name"] for m in c.table("models").select("id,name").execute().data}
print(f"QUALIFY total={len(q)}  OURS={len(ours)}  OTHERS(report-only)={len(others)}")
print("OURS by user:", Counter(r['username'] for r in ours))
print("OTHERS by user:", Counter(r['username'] for r in others))
for r in ours[:10]:                     # sample: id, user, model, benchmark, status, age
    print(f"  {r['id']} | {r['username']} | {mn.get(r['model_id'],'?')[:40]} | "
          f"{bm.get(r['benchmark_id'],'?')} | {r['job_status']} | {age_h(r['created_at']):.0f}h")

# --- DELETE (ours only, idempotent, scoped id + username) — run AFTER reviewing the dry-run ---
# ⚠️ CASCADE: sandbox_jobs.id IS FK'd — `sandbox_trials.job_id → sandbox_jobs.id` and
# `sandbox_trial_model_usage.trial_id → sandbox_trials.id`. A plain sandbox_jobs delete FK-fails (23503);
# delete grandchild → child → job. Children carry no username (ownership transitive from the job you own).
DELETE = False                          # flip to True to execute
if DELETE:
    for r in ours:
        trial_ids = [t["id"] for t in
                     c.table("sandbox_trials").select("id").eq("job_id", r["id"]).execute().data]
        for i in range(0, len(trial_ids), 200):       # chunk to keep the IN() lists sane
            chunk = trial_ids[i:i+200]
            if chunk:
                c.table("sandbox_trial_model_usage").delete().in_("trial_id", chunk).execute()  # grandchild
        c.table("sandbox_trials").delete().eq("job_id", r["id"]).execute()                       # child
        c.table("sandbox_jobs").delete().eq("id", r["id"]).eq("username", r["username"]) \
         .in_("job_status", ["Pending", "Started"]).execute()                                    # job (yours)
    # re-read: confirm gone + that NO Finished/scored row was touched
    left = c.table("sandbox_jobs").select("id").in_("id", [r["id"] for r in ours]).execute().data
    fin  = c.table("sandbox_jobs").select("id").eq("job_status", "Finished") \
            .in_("id", [r["id"] for r in ours]).execute().data
    assert not left, f"{len(left)} of ours survived"; assert not fin, "touched a Finished row!"
    print(f"DELETED {len(ours)} ours; OTHERS left for supervisor: {Counter(r['username'] for r in others)}")

Guardrails

  • Cross-user FK safety is MANDATORY. Scope both the match and the job-delete by username IN OURS. Other users' stale rows are reported with counts, never deleted.
  • The cascade is REQUIRED. sandbox_jobs.id IS FK'd; a plain delete fails 23503. Delete grandchild (sandbox_trial_model_usage) → child (sandbox_trials) → job, in that order.
  • DRY-RUN first, then delete, then re-read. Leave DELETE = False until you've reviewed the sample. After deleting, re-read to confirm the rows are gone AND that no Finished/scored row was touched (the post-delete assert).
  • Never guess-delete. STOP + surface if any qualifying row has a non-null stats/metrics (ambiguous "never populated").
  • Never raise CUTOFF_H — the 36h floor excludes legitimately-running recent evals.
  • n_trials is planned, not progress. A placeholder already has n_trials=128; do not read it as a populated signal.
  • Reads are free; deletes are dangerous. The service-role key bypasses RLS.

Related

  • crud-otagent-supabase — the general Supabase read/aggregate/write skill (ID/OOD scores, model registration, the full get_metric/set_stat helpers). Reach for that one for anything other than the stale-placeholder purge.

Download now (ZIP)

Does it work?

Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.

No reviews yet

People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.

  • It worked
  • Partly
  • It didn't
What did you use it for? e.g. Reviewing pull requests in a Rails app

Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.

Your trail

Files you open will collect here, so you can find them again.

Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.

Now do this for your own.

Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.