Crud Otagent Supabase

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)

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:

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
      assert idname.get(RIGHT)                     # target base row exists
      c.table("models").update({"base_model_id": RIGHT}).eq("id", m["id"]).eq("base_model_id", m["base_model_id"]).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 sandbox_jobs count is unchanged.
• Register/repair an eval job (auto-upload failed): scripts/database/manual_db_eval_push.py --job-dir trace_jobs/<RUN_TAG> [--hf-repo …] [--skip-hf] [--forced-update]. Verify the registered model name afterward — for vLLM-served models the auto-detected name can be a numeric served-id, not the HF repo; the CLAUDE.md "Manual Eval Upload" section has the fix-up queries.
• Per-series exceptions: some series are HF-upload-ONLY (e.g. the Delphi RL scaling-laws SFT grid) — do not register those. Honor any enable_db_registration: false and documented no-DB series.

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).