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.