experiments-database/docs/superpowers/specs/2026-05-01-numerical-csv-buckets-design.md

Numerical CSV-analysis buckets, persisted per animal

Date: 2026-05-01 Scope: frontend/src/components/CsvAnalysis.jsx, frontend/src/lib/csvAnalysis.js, frontend/src/api/client.js, frontend/src/pages/DailyStatusDetail.jsx, backend/prisma/schema.prisma, backend animal route.

Goal

In the daily-status CSV-analysis flow, let the user define an extra type of bucket — a numerical bucket — alongside the existing note-classification buckets (Success / Failure / Other / custom). A numerical bucket plots its assigned notes' numeric portion against the CSV frame column (or timestamp as fallback) and reports an average. The bucket structure and per-bucket note assignments persist on the Animal so subsequent CSV uploads for the same subject don't need to be re-classified from scratch.

Section 0 — Preconditions

Before applying the schema migration, back up the live Postgres database to a sibling archived/ folder.

mkdir -p /home/sam/docker-images/archived
docker exec experiments_postgres pg_dump -U expuser -d experiments_db -F c \
  > /home/sam/docker-images/archived/experiments_db_$(date +%Y%m%d_%H%M%S).dump

• Verify the dump file size is greater than 0 bytes before proceeding.
• Restore command (if needed):

  docker exec -i experiments_postgres pg_restore -U expuser -d experiments_db -c \
    < /home/sam/docker-images/archived/<dumpfile>
  

Section 1 — Data model

Schema change

Add one nullable JSON column to Animal:

model Animal {
  id                   String        @id @default(uuid())
  experiment_id        String
  animal_id_string     String
  animal_name          String
  subject_info         Json?
  csv_analysis_config  Json?         // <— new
  experiment           Experiment    @relation(fields: [experiment_id], references: [id], onDelete: Cascade)
  daily_statuses       DailyStatus[]

  @@map("animals")
}

Stored shape

{
  "buckets": [
    { "name": "Success",      "type": "note",      "notes": ["S", "S1"] },
    { "name": "Failure",      "type": "note",      "notes": ["F"] },
    { "name": "Other",        "type": "note",      "notes": [] },
    { "name": "Lick latency", "type": "numerical", "notes": ["L25", "L30"] }
  ]
}

Rules

• type: "note" — existing behavior (counted, run-length-encoded, contributes to daily-status summary).
• type: "numerical" — plotted vs. frame (or timestamp); reports average; excluded from category counts, consecutive runs, and the "Save metrics to daily status" calculation.
• The three default note buckets (Success, Failure, Other) are always present: seeded into buckets on first save and undeletable in the UI.
• Custom buckets (both note and numerical) can be removed via an ✕ control on the bucket. Removal puts any notes that were assigned to that bucket back into Unassigned for the current session and drops the bucket from buckets[] on next save.
• notes arrays persist as raw trimmed strings. A new CSV upload looks up each unique note in the persisted notes arrays to auto-classify; any leftover notes appear as Unassigned.
• A single note value belongs to at most one bucket. Assigning a note to bucket B removes it from any other bucket's notes[] first.
• Bucket names are unique within buckets. Renaming is out of scope for v1.
• csv_analysis_config may be null (animal never had CSV analysis run); UI falls back to DEFAULT_BUCKETS in that case.

Backend API

• GET /api/animals/:id — already returns the full animal row. csv_analysis_config is included automatically.
• PATCH /api/animals/:id/csv-config — body { buckets: [...] }. Validates:
  • buckets is an array.
  • Each entry has non-empty trimmed name, type ∈ {"note","numerical"}, notes is string[].
  • name values are unique within the array.
  • The three default note buckets (Success/Failure/Other) are present. Replaces the column atomically. Returns updated animal. Audit-logged via existing middleware.

Section 2 — Frontend changes

CsvAnalysis.jsx (the main refactor)

1. New prop animal — passed from DailyStatusDetail. Used to read & write csv_analysis_config.

2. State refactor: replace the parallel categories: string[] + customCategories: string[] + classifications: { [note]: category } with a single source of truth:

   const [buckets, setBuckets] = useState([]);
   // bucket: { name: string, type: 'note' | 'numerical', notes: string[] }
   

   Derive classifications, categories, customCategories from buckets for compatibility with downstream code (runAnalysis, color helpers).

3. Seeding on entering classify phase:

   • If animal.csv_analysis_config?.buckets exists, use it.
   • Otherwise initialize from DEFAULT_BUCKETS (Success/Failure/Other, all type: "note", empty notes).
   • Auto-classify: any uniqueNote whose value appears in some bucket's notes[] is considered already-assigned and does not appear in Unassigned.

4. "+ Add field" button (replaces today's "+ New category"):

   • Inline form: <input name> + radio toggle ( ) Note ( ) Numerical.
   • On submit, appends { name, type, notes: [] } to buckets.

5. CategoryBucket extension:

   • Accepts type prop.
   • Color allocation: getCategoryColors is updated to take the full bucket list and assign EXTRA_COLORS round-robin across all custom buckets (note + numerical combined), so adding numerical buckets does not shift the colors of existing note buckets.
   • Numerical buckets render with a "numerical" header badge to distinguish them visually.
   • Numerical buckets show an "Add all unassigned" button that bulk-moves every currently-unassigned unique note into this bucket's notes[].
   • Custom buckets (note or numerical) show an ✕ remove control on hover.
   • In results phase, numerical buckets show a footer line: Avg: 24.3 · 47/50 rows plottable.

6. compute():

   • Note-type buckets: pass through runAnalysis(rows, timestampCol, noteCol, classifications) as today, but classifications is built only from note-type buckets (numerical-bucket assignments are excluded so they don't pollute counts/runs).
   • Numerical-type buckets: for each, call new helper computeNumericSeries(rows, frameCol || timestampCol, noteCol, bucket) returning { points: [{x, value, raw}], avg, skipped, total, xLabel }.
   • After successful analysis, call animalsApi.updateCsvConfig(animal.id, { buckets }). Failure surfaces a non-blocking toast/inline error; analysis results still render.

7. Results phase additions:

   • Existing tables (Category Counts, Consecutive Runs) iterate buckets.filter(b => b.type === 'note') — numerical buckets are excluded.
   • New "Numerical Plots" section after RunSequenceView. One panel per numerical bucket, containing:
     • Bucket name, average, skipped-count.
     • Recharts <LineChart> with XAxis dataKey="x" (label = xLabel) and a single <Line dataKey="value">. Color from getCategoryColors(bucket.name, customCategories).
   • If a numerical bucket has zero plottable rows, render "No plottable values — N of N note rows had no extractable number." instead of a chart.

frontend/src/lib/csvAnalysis.js

// Extract first signed decimal from a string. Returns null if none found.
export function extractNumber(str) {
  if (str == null) return null;
  const m = String(str).match(/-?\d+(\.\d+)?/);
  return m ? parseFloat(m[0]) : null;
}

// Build a numerical series for one bucket.
// xColName: frameCol if set, else timestampCol. xLabel reflects which.
export function computeNumericSeries(rows, xColName, noteCol, bucket) {
  const noteSet = new Set(bucket.notes);
  const points = [];
  let skipped = 0;
  let total = 0;

  for (const r of rows) {
    const note = String(r[noteCol] ?? '').trim();
    if (!noteSet.has(note)) continue;
    total++;
    const value = extractNumber(note);
    const x = parseFloat(r[xColName]);
    if (value == null || Number.isNaN(x)) { skipped++; continue; }
    points.push({ x, value, raw: note });
  }

  points.sort((a, b) => a.x - b.x);
  const avg = points.length === 0
    ? null
    : points.reduce((s, p) => s + p.value, 0) / points.length;

  return { points, avg, skipped, total, xLabel: xColName };
}

frontend/src/api/client.js

Add to animalsApi:

updateCsvConfig: (animalId, config) =>
  request(`/animals/${animalId}/csv-config`, { method: 'PATCH', body: JSON.stringify(config) }),

frontend/src/pages/DailyStatusDetail.jsx

Single change: pass animal={status.animal} to <CsvAnalysis>.

Section 3 — Behavior details

• Frame column stays optional. If unset when running analysis with ≥1 numerical bucket, x-axis falls back to timestampCol. Chart axis label and the panel header reflect which axis is in use.
• Letter stripping uses /-?\d+(\.\d+)?/ (first signed decimal in the string). Notes with no extractable number are counted as skipped.
• Persistence trigger: config is saved after compute() succeeds. Editing buckets in classify phase without running analysis does not persist.
• Multiple numerical buckets: fully supported. Each gets its own chart and its own average.
• Same note in multiple buckets: prevented at the UI level — assigning a note to a bucket removes it from any other bucket. Backend does not enforce this (UI invariant).

Section 4 — Error handling

Failure	Behavior
Save config (PATCH) fails	Local state unchanged; non-blocking inline error in classify/results UI; analysis still renders.
Numerical bucket has zero plottable rows	Panel shows "No plottable values — N of N rows had no extractable number." No crash.
Frame column absent + numerical bucket exists	Falls back to timestamp; chart axis label reflects this.
Migration	Run the Section 0 backup first; abort if dump file size is 0 bytes.
Validation rejection on PATCH	UI shows server error message; user can re-edit and retry.

Section 5 — Testing

Frontend unit (Vitest, frontend/src/lib/)

• extractNumber():
  • "L25.5" → 25.5
  • "R-12" → -12
  • "trial_007" → 7
  • "3.2e2" → 3.2 (loses scientific notation — accepted limitation)
  • "42 ms" → 42
  • "abc" → null
  • "" → null
  • null → null
• computeNumericSeries():
  • All rows valid → correct points + average + skipped=0
  • Some unparseable → correct skipped count, points exclude bad rows
  • All unparseable → empty points, avg=null, skipped=total
  • xColName missing from rows → all skipped (NaN x)
  • Bucket with empty notes[] → empty points, total=0
  • Output sorted ascending by x

Backend unit

• PATCH /animals/:id/csv-config:
  • Valid body → 200, persisted, audit log row written
  • Empty bucket name → 400
  • Duplicate names → 400
  • Invalid type value → 400
  • notes not an array → 400
  • Missing default bucket (e.g. no "Success") → 400

Integration / manual

• Upload a CSV → create one numerical bucket → assign notes → run analysis → reload page → upload same CSV → confirm bucket is pre-populated and assignments are restored.
• Same flow with a CSV containing genuinely-new note values → confirm new notes show as Unassigned, old ones do not.
• Numerical bucket with frame col present → chart x-axis labeled with frame col name.
• Numerical bucket with frame col cleared → chart x-axis labeled with timestamp col name.

Section 6 — Out of scope (v1)

• Bucket renaming after creation.
• Y-axis range customization, multi-line overlays.
• Showing the chart for past DailyAnalysis records (frame data isn't stored; user re-drops the CSV to plot).
• Per-experiment defaults (config is per-animal only).
• Editing config from a settings page; config is only edited via the CSV-analysis flow.