Crud Purge Stale Eval Placeholders

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

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)}")