crud-otagent-supabase

name

crud-otagent-supabase

description

Read, aggregate, and (carefully) write OT-Agent eval/model data in the Supabase registry. Use when asked to look up a model's ID/OOD benchmark scores, build/refresh an ablation or paper table from eval results, find unevaluated models, register a model/eval, or reconcile duplicate rows. Covers the sandbox_jobs/models/benchmarks schema, the metrics-field shape gotchas, the ID/OOD benchmark master list + per-benchmark task counts (for binomial SE), and the multiple-entries-per-model rule.

crud-otagent-supabase

OT-Agent Supabase is the source of truth for model eval results — the scores live in sandbox_jobs, not in any markdown file. This skill covers querying them, aggregating ID/OOD means + SE as the ablation/paper tables do, and writing rows safely.

Connect (run LOCALLY — faster than ssh-ing a cluster)

Run from the Mac with the otagent env (≈10 s faster than ssh+python on a cluster). Filter by your own username for any write (see Guardrails).

cd /Users/benjaminfeuer/Documents
set -a; source "${DC_AGENT_SECRET_ENV:?set DC_AGENT_SECRET_ENV to the secrets file first}"; set +a   # SUPABASE_URL + SUPABASE_SERVICE_ROLE_KEY (+ ANON_KEY, HF_TOKEN)
/Users/benjaminfeuer/miniconda3/envs/otagent/bin/python - <<'PY'
import os
from supabase import create_client
c = create_client(os.environ["SUPABASE_URL"],
                  os.environ.get("SUPABASE_SERVICE_ROLE_KEY") or os.environ["SUPABASE_ANON_KEY"])
# ... queries ...
PY

• SUPABASE_SERVICE_ROLE_KEY bypasses RLS (full read/write). Prefer it for reads; for writes it means nothing stops you mutating other users' rows — so the cross-user pre-check in Guardrails is mandatory.
• Schema DDL lives at /Users/benjaminfeuer/Documents/OpenThoughts-Agent/schema/ (sandbox_jobs, models, benchmarks, …). Read it when unsure of a column.

Schema (the tables you'll touch)

table	what it holds	key columns
sandbox_jobs	one row per (model × benchmark) eval job — the scores	model_id, benchmark_id, metrics (jsonb), stats (jsonb), job_status, n_rep_eval, n_trials, username, hf_traces_link, slurm_job_id
models	registered models	id, name (HF repo, e.g. laion/<run>-<step>-<size>), base_model_id (self-FK → models.id), dataset_id, agent_id, dataset_names, duplicate_of
benchmarks	benchmark catalog	id, name, duplicate_of (→ canonical benchmark id, for families)
agents	harness/agent rows	id, name
sandbox_trial_model_usage	per-trial model usage (FK→models)	model_id — watch for cross-user FKs before deleting a model

job_status is an enum: Pending / Started / Finished (+ failure states). Only Finished rows with a non-null accuracy are real scores — Pending/Started rows have metrics=None.

GOTCHA 1 — the metrics field has TWO shapes

metrics is jsonb and appears as either a list of {"name","value"} dicts or a plain dict. Always extract through a shape-robust helper, never 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}, {"name":"accuracy_stderr",...}]
        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

The list-of-dicts form also carries accuracy_stderr (the eval's own reported SE) — available if you want it, but the ablation tables recompute a pooled binomial SE (see "Aggregation").

GOTCHA 2 — multiple entries per model; sibling model rows

A single logical model can have more than one score per benchmark, and even more than one models row. Three rules:

1. Multiple entries per (model, benchmark): there are often a Pending/Started row (acc None) and a Finished row for the same benchmark, or two reruns.
   • Drop non-Finished/null rows.
   • When ≥2 complete entries exist with identical evaluation settings, AVERAGE them (don't pick max, don't pick first). Different settings (e.g. a different n_rep_eval or harness) are not "identical settings" — keep them separate / pick the canonical one.
2. Sibling model rows: the same HF model may be registered under multiple models.id (trainer auto-push + a manual -<step>-<size> row, or a duplicate). Query models by ilike on a name stub, not exact match, and union the sandbox_jobs across all matching model_ids before aggregating — a benchmark "missing" on one row may be Finished on a sibling.
3. Benchmark families (duplicate_of): a benchmark may have family members (e.g. dev_set_v2 → DCAgent_dev_set_v2, dev_set_v2_2.0x). Resolve via the duplicate_of field (follow it to the canonical row) the way scripts/database/query_unevaled_models.py does, when a score seems absent under the exact name.

def get_model_scores(name_stub):
    bm = {b["id"]: b["name"] for b in c.table("benchmarks").select("id,name").execute().data}
    mods = c.table("models").select("id,name").ilike("name", f"%{name_stub}%").execute().data   # sibling rows
    perb = {}                                   # benchmark name -> list of (acc, status)
    for m in mods:
        for j in c.table("sandbox_jobs").select("benchmark_id,metrics,job_status").eq("model_id", m["id"]).execute().data:
            bn = bm.get(j["benchmark_id"], j["benchmark_id"])
            perb.setdefault(bn, []).append((get_metric(j["metrics"]), j["job_status"]))
    scores = {}                                 # benchmark -> averaged fraction over complete entries
    for bn, entries in perb.items():
        done = [a for a, s in entries if a is not None and s == "Finished"]
        if done:
            scores[bn] = sum(done) / len(done)  # AVERAGE identical-setting complete repeats
    return scores, mods, perb

ID / OOD benchmark master list (memorize — easy to get wrong)

The ablation tables split the agent benchmarks into ID ("Core") and OOD. The split is NOT stored in the schema and is NOT the CORE_BENCHMARKS list in eval/check_progress.py (that grouping is display-ordering only and gives wrong table numbers). Use exactly this:

set	benchmarks	task count N (for SE)
ID (Core)	swebench-verified-random-100-folders	100
	terminal_bench_2	89
	dev_set_v2 (partial-credit — see SE note below)	— (excluded from binomial SE)
OOD	swebench-verified (the full 500-task set)	500
	aider_polyglot	225
	bfcl-parity	123
	financeagent_terminal	50
	gaia_127	127
	medagentbench	300

dev_set_v2 is ID (3-member set: {swebench, v2, tb2}) but partial-credit — it's in the ID mean but excluded from the pooled binomial SE (SE pools only over swebench-100 + tb2; see Aggregation). For model-vs-model rankings, compare on a shared binary benchmark (swebench-100 or tb2), never on dev_set_v2.

The #1 trap: swebench-verified (full, 500) is OOD; the swebench-verified-random-100-folders subset (100) is ID. Same accuracy range, so a wrong pick barely moves the mean but flips ID↔OOD membership and changes the SE N (100 vs 500). Always check which swebench row is Finished.

ID  = {"swebench-verified-random-100-folders", "terminal_bench_2", "dev_set_v2"}
OOD = {"swebench-verified", "aider_polyglot", "bfcl-parity",
       "financeagent_terminal", "gaia_127", "medagentbench"}
# dev_set_v2 is partial-credit → in ID for the MEAN, but NOT in NTASK / the binomial SE.
ID_SE_BENCHMARKS = {"swebench-verified-random-100-folders", "terminal_bench_2"}  # SE pools over these only
NTASK = {"swebench-verified-random-100-folders": 100, "terminal_bench_2": 89,
         "swebench-verified": 500, "aider_polyglot": 225, "bfcl-parity": 123,
         "financeagent_terminal": 50, "gaia_127": 127, "medagentbench": 300}

Aggregation (reproduces the ablation/paper tables exactly)

Per set (ID or OOD): the cell mean is the unweighted average of the per-benchmark averaged accuracies; the SE is a pooled binomial over the total tasks of the present benchmarks. (Verified: this reproduces the existing rl_ablation_table.tex rows.) For ID, the SE pools over ID_SE_BENCHMARKS only (swebench-100 + tb2) — dev_set_v2 is in the ID mean but partial-credit, so it has no clean binomial N and is excluded from the SE pool.

import math
def set_stat(S, scores):                        # scores from get_model_scores()
    present = {b: scores[b] for b in S if scores.get(b) is not None}
    missing = sorted(set(S) - set(present))
    if not present: return None
    mean = sum(present.values()) / len(present) # fraction
    N    = sum(NTASK[b] for b in present)
    se   = math.sqrt(mean * (1 - mean) / N) * 100
    return round(mean*100, 1), round(se, 1), sorted(present), missing   # (mean%, SE, present, missing)

• Completeness: a set is complete only if missing == []. Report partial means with the missing-benchmark list (e.g. a paper-table dagger). A model can be ID-complete but OOD-incomplete (or vice-versa) — check each set separately.
• SE scales with N: ID SE ≈ 2.6–3.0 (N=189), OOD SE ≈ 1.1–1.2 (N=1325 when all 6 present). If you see ID SE ≈ 1.7 you (or a prior table) used the full 500-task swebench in ID by mistake — the membership trap above.

CREATE / UPDATE (registration)

Do not hand-insert rows; use the maintained scripts. Full flags are in OpenThoughts-Agent/CLAUDE.md.

• Register a model (after an HF upload): scripts/database/manual_db_push.py --hf-model-id <repo> --base-model <hf> --dataset-name <name|comma,list> [--training-type RL] (defaults to SFT; pass RL for RL models). Multi-dataset → comma-list sets dataset_names. The --base-model <hf> flag is NOT stored as text — it's resolved to the models row of that base and stored as base_model_id (the self-FK). So a wrong --base-model registers a wrong uuid pointer, fixed by repointing it (next bullet), not by editing a string.
• Repair a mis-registered base model (base_model_id points at the wrong base, e.g. a 27B model registered against the 9B base): a single-column repoint, NOT a re-register. Both the model and its correct base are models rows — find both by name (ilike), then update only base_model_id. It does not touch sandbox_jobs/scores or the row's own id (so no cross-user FK breakage — the models table has no per-row owner; writes are scoped by id), and the new value must be a valid models.id. Guard the write on the old value so it's idempotent + can't clobber a since-fixed row:

  idname = {m["id"]: m["name"] for m in c.table("models").select("id,name").execute().data}
  wrong  = c.table("models").select("id,name,base_model_id").ilike("name","%<model-stub>%").execute().data
  RIGHT  = next(i for i,n in idname.items() if n == "<correct/base-hf-name>")   # the base's models.id
  for m in wrong:                                  # may be >1 sibling row — check each
       idname.get(RIGHT)                     
      c.table().update({: RIGHT}).eq(, m[]).eq(, m[]).execute()
  

  Then re-read to confirm the new base resolves correctly, that sibling rows you did NOT mean to touch (e.g. the real 9B model) still point at their own base, and that the count is unchanged.

Cross-linking eval traces to the leaderboard (hf_traces_link)

After an eval's HF trace dataset is uploaded, the leaderboard's trace icon keys off the sandbox_jobs.hf_traces_link column (a text URL on the job row, NOT on models) — see .agents/projects/ota-leaderboard/ota-leaderboard.md (DB-contract §5). Cross-linking = setting that one column to the trace repo URL. Touch nothing else (never scores/metrics, model_id, status).

Discover the eval → HF-repo mapping (don't guess the repo from the run-tag — the auto-push often fails or truncates). For each eval, the run dir is $EVAL_JOBS_DIR/eval-<safe_model>_<safe_dataset> (safe_* = /→_); read its two files:

• meta.env → DB_JOB_ID (the sandbox_jobs.id to update), SLURM_JOB_ID, MODEL, REPO_ID.
• upload.log → the [uploader] upload_eval_results(... hf_repo_id='<org>/<run_tag>') line is the repo the auto-harvest attempted. It is frequently NOT the live repo — the auto-push targets DCAgent2/<run_tag> (no -traces suffix) and on the shared DCAgent2 org it commonly 403s on storage quota (exceeded your public storage space). The real, manually re-uploaded dataset lands in laion/<run_tag>-traces (the canonical <org>/<run_tag>-traces convention; rl-agentic-job-cleanup §8 / eval-agentic-cleanup §1). Always confirm on HF which repo actually exists + is non-empty before linking — do not link the failed DCAgent2 attempt.

from huggingface_hub import HfApi; api = HfApi(token=os.environ["HF_TOKEN"])
rid = f"laion/{run_tag}-traces"                 # run_tag = the eval run-dir basename
info = api.dataset_info(rid, files_metadata=True)   # raises if missing
assert any(s.rfilename.endswith(".parquet") for s in info.siblings), f"{rid} empty"
url  = f"https://huggingface.co/datasets/{rid}"

96-char trap: HF repo names cap at 96 chars after the org/. A long run-tag (e.g. eval-laion_swesmith-coldstart-complete-lt32k-2ep-8B_DCAgent2_swebench-verified-random-100-folders-traces, 109 chars) cannot be created — the uploader truncates+hashes it (…swebench-verified80dacb91) or the push silently never lands. If dataset_info raises HFValidationError on length, the canonical-named repo does not exist → flag that eval as un-linkable, do NOT invent a link.

FK-safe, idempotent, single-column update (same cross-user rule as DELETE/MUTATE below — assert ownership before writing, scope the write to id + username, never overwrite a good link):

ME = "feuer1"   # the cluster username that OWNS the rows — NOT the Mac's os.environ["USER"]
row = c.table("sandbox_jobs").select("id,username,hf_traces_link").eq("id", job_id).execute().data[0]
assert row["username"] == ME, f"FK-SAFETY STOP: owned by {row['username']} != {ME} — do NOT mutate"
if row["hf_traces_link"] == url:    pass                      # idempotent: already correct → skip
elif row["hf_traces_link"]:         pass                      # a DIFFERENT good link exists → don't clobber
else:
    c.table("sandbox_jobs").update({"hf_traces_link": url}).eq("id", job_id).eq("username", ME).execute()

Then re-read each row to confirm the link set and that metrics/model_id/job_status are unchanged. Report any eval whose trace repo was missing/empty/over-length (couldn't link).

DELETE / MUTATE — cross-user FK safety (MANDATORY pre-check)

Before deleting or updating ANY row, confirm no other user's rows depend on it. Restrict every write to rows you own; if an FK forces touching someone else's row, STOP and ask — do not repoint/delete it.

import os
me = os.environ.get("USER", "<your_user>")
fk = (c.table("sandbox_jobs").select("id,username,model_id")
        .eq("model_id", target_model_id).neq("username", me).execute().data)
if fk:
    print(f"STOP: {len(fk)} other-user sandbox_jobs FK this model — leave it, surface to user.")
else:
    ...  # safe to delete the model row / your own jobs

Also check sandbox_trial_model_usage (FK→models) the same way. Reads are always safe; the danger is exclusively delete/update.

Purging stale placeholder evals → separate skill

Removing dead, never-populated Pending/Started placeholder rows (eval launches that died/stalled before scoring) is its own guardrailed DELETE skill: crud-purge-stale-eval-placeholders — the >36h qualifier, the cross-user FK-safety pre-check, and the grandchild→child→job cascade delete all live there. Reach for it when the registry is clogged with stale Pending/Started/"Running" placeholders or when the eval listener's dedup is mis-firing on dead rows.

Quick recipes

• "What are model X's ID/OOD scores?" → get_model_scores(stub) → set_stat(ID,…), set_stat(OOD,…); report mean±SE + any missing benchmarks.
• "Build/refresh an ablation row." → same, then format mean_{SE}. Means use the averaged-complete-entries rule; SE uses the binomial N from NTASK.
• "Find models not yet evaluated on benchmark Y." → scripts/database/query_unevaled_models.py --benchmark <family> --size <8|32> (resolves families via duplicate_of).
• "Reconcile a duplicate model row." → find sibling rows by ilike, run the cross-user FK pre-check, then dedup only your own rows.

Recipe: ALL models NOT yet evaled (on ANY benchmark)

The general "which models have no eval at all" (vs query_unevaled_models.py, which is benchmark-specific and counts a Started/Pending job as "evaluated"). A model is EVALED iff it has ≥1 sandbox_jobs row that is Finished AND has a non-null accuracy; everything else (no jobs, only Started/Pending, or Finished-but-null) is UN-EVALED. Sibling-aware: a model NAME is evaled if ANY of its models.id rows is evaled (§GOTCHA 2). PAGINATE — models (~1300) and sandbox_jobs (~9000) both exceed the 1000-row default.

def page(table, cols):
    out=[]; off=0
    while True:
        d=c.table(table).select(cols).range(off,off+999).execute().data
        out+=d
        if len(d)<1000: break
        off+=1000
    return out
models=page("models","id,name"); jobs=page("sandbox_jobs","model_id,job_status,metrics")
evaled_ids={j["model_id"] for j in jobs if j["job_status"]=="Finished" and get_metric(j["metrics"]) is not None}  # get_metric = the shape-robust helper (§GOTCHA 1)
id2name={m["id"]:m["name"] for m in models}
evaled_names={id2name[i] for i in evaled_ids if i in id2name}
unevaled=sorted({m["name"] for m in models if m["name"] not in evaled_names})

**⚠️ TRIAGE the result before reporting it — it is mostly DATA-QUALITY NOISE, not real eval gaps:

• (A) Real trained checkpoints (the actionable set) — DCAgent*/…, laion/…, bare run-names. Cross-check: in-progress (Started row — evaled right NOW, no Finished yet) vs CONCLUDED series (a3-*); flag both.
• (B) API / served baselines — openai/, together_ai/, or bare o4-mini/gpt-*. Reference models, intentionally outside our eval pipeline.
• (C) FILESYSTEM-PATH junk registrations — names that are a local path (^/…/models--<org>--<name>/snapshots/<hash>): registered by on-disk path instead of HF repo name (path-analog of the served-id trap, §eval-agentic-cleanup §2). The underlying model is usually ALREADY evaled under its proper HF name → a cleanup candidate, not a re-eval candidate. So report it bucketed A/B/C with the in-progress / concluded flags — never a raw name dump (the raw count wildly overstates the real eval gap).

Recipe: per-task timeouts + turn counts + outcome breakdown (model × benchmark)

To answer "what timeout/turns did model X actually get on benchmark Y, and how did its trials end?" — the numbers are split across three sources; no single column has them:

1. The multiplier is in sandbox_jobs.config (jsonb). Pull the job row and read: config.timeout_multiplier (the scalar applied to every task's declared timeout), config.agents[0].max_timeout_sec (the hard ceiling — effective timeout is capped here), config.verifier.max_timeout_sec, n_attempts, orchestrator.n_concurrent_trials.

   j = c.table("sandbox_jobs").select("config,stats,metrics,hf_traces_link") \
         .eq("model_id", model_id).eq("benchmark_id", tb2_id).eq("job_status","Finished").execute().data[0]
   mult = j["config"]["timeout_multiplier"]           # e.g. 2.0 for the eval team's real tb2 runs
   cap  = j["config"]["agents"][0]["max_timeout_sec"]  # 7200 ceiling
   

   Note: the eval team runs terminal_bench_2 at timeout_multiplier: 2.0 (the terminal_bench_2_2.0x benchmark family + the listener's per-bench EVAL_TIMEOUT_MULTIPLIER); the sbatch default of 1.0 is NOT what TB2 actually uses.

2. The real per-task timeout (seconds) = each task's declared agent.timeout_sec × timeout_multiplier, hard-capped at agents[0].max_timeout_sec. The declared base is in the benchmark's task set (one task.toml per task), NOT in supabase. For TB2 the task set is DCAgent2/terminal_bench_2:

   import tomllib; from huggingface_hub import HfApi, hf_hub_download
   api=HfApi()
   tomls=[s.rfilename for s in api.dataset_info("DCAgent2/terminal_bench_2").siblings if s.rfilename.endswith()]
   base={tl.split()[]: tomllib.load((hf_hub_download(,tl,repo_type=),))[][]  tl  tomls}
   eff = {t: (b*mult, cap)  t,b  base.items()}   
   

Aggregate-only accuracy/error counts are also in sandbox_jobs.stats (stats.evals.<key>.n_errors / n_trials / reward_stats.reward lists tasks by reward), but stats does NOT have per-task timeouts or turns.

Guardrails

• Reads are free; writes are dangerous. The service-role key bypasses RLS — always run the cross-user FK pre-check before any delete/update, and never mutate another user's rows.
• Average complete identical-setting reruns; never silently pick one. Check ALL entries per (model, benchmark) AND all sibling models rows by ilike.
• ID vs OOD: swebench-verified-random-100-folders = ID; full swebench-verified = OOD; dev_set_v2 = neither. Don't trust check_progress.py's CORE list.
• metrics is list-OR-dict — always go through get_metric.
• Honor no-DB series (enable_db_registration: false, Delphi grid, etc.).
• A Finished SLURM/eval job is necessary but not sufficient — confirm a numeric accuracy in metrics (evalchemy can exit 0 with empty results).