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

Numerical CSV-Analysis Buckets Implementation Plan

For agentic workers: REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (- [ ]) syntax for tracking.

Goal: Add a per-animal-persistent "numerical" bucket type to the daily-status CSV-analysis flow that plots note-derived numbers against the CSV's frame (or timestamp) column.

Architecture: New nullable JSON column csv_analysis_config on Animal, plus a new PATCH /animals/:id/csv-config endpoint. Frontend CsvAnalysis.jsx is refactored from parallel categories + classifications state to a single buckets[] source of truth where each bucket is {name, type: 'note'|'numerical', notes: string[]}. Numerical buckets feed a new pure helper computeNumericSeries() that powers per-bucket Recharts <LineChart> panels in the results phase. Charts are recomputed live on each upload; not persisted in DailyAnalysis.

Tech Stack: Prisma (Postgres), Express + express-validator, Jest (backend + frontend), React, Recharts (already in deps).

Spec: docs/superpowers/specs/2026-05-01-numerical-csv-buckets-design.md

---

Task 0: Pre-migration database backup

Files:

• Create: /home/sam/docker-images/archived/experiments_db_<timestamp>.dump (output)

• Step 1: Create archived directory and run pg_dump

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

• Step 2: Verify backup file size

ls -lh /home/sam/docker-images/archived/experiments_db_*.dump | tail -1

Expected: file size > 0 bytes (typically several KB to MB depending on data). STOP and abort plan if size is 0. Restore command for emergencies:

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

---

Task 1: Add csv_analysis_config column to Animal

Files:

• Modify: backend/prisma/schema.prisma (Animal model, after subject_info)

• Create: backend/prisma/migrations/<timestamp>_add_animal_csv_analysis_config/migration.sql (auto-generated)

• Step 1: Edit schema

In backend/prisma/schema.prisma, modify the Animal model to add one line after subject_info:

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

  @@map("animals")
}

• Step 2: Generate and apply migration

cd /home/sam/docker-images/experiments-database/backend
docker compose -f ../docker-compose.dev.yml exec backend npx prisma migrate dev --name add_animal_csv_analysis_config

If the dev compose file is not running, instead run from a backend shell with DATABASE_URL pointed at the postgres container.

Expected: new directory under prisma/migrations/, schema applied, Prisma Client regenerated. Migration SQL should contain ALTER TABLE "animals" ADD COLUMN "csv_analysis_config" JSONB;.

• Step 3: Verify column exists

docker exec experiments_postgres psql -U expuser -d experiments_db -c "\d animals" | grep csv_analysis_config

Expected: csv_analysis_config | jsonb | | |

• Step 4: Commit

cd /home/sam/docker-images/experiments-database
git add backend/prisma/schema.prisma backend/prisma/migrations/
git commit -m "feat(db): add csv_analysis_config JSON column to animals"

---

Task 2: Backend PATCH /animals/:id/csv-config — validation helper (TDD)

This task adds the validation logic as a pure function so it can be unit-tested independently and reused by the route handler.

Files:

• Create: backend/src/lib/csvConfigValidation.js

• Create: backend/tests/csvConfigValidation.test.js

• Step 1: Write the failing test

Create backend/tests/csvConfigValidation.test.js:

const { validateCsvConfig } = require('../src/lib/csvConfigValidation');

const okBuckets = [
  { name: 'Success', type: 'note', notes: ['s'] },
  { name: 'Failure', type: 'note', notes: ['f'] },
  { name: 'Other',   type: 'note', notes: [] },
];

describe('validateCsvConfig', () => {
  it('accepts a valid config with the three default buckets', () => {
    expect(validateCsvConfig({ buckets: okBuckets })).toBeNull();
  });

  it('accepts a valid config with extra note + numerical buckets', () => {
    const buckets = [
      ...okBuckets,
      { name: 'Lick latency', type: 'numerical', notes: ['L25'] },
      { name: 'Bonus',        type: 'note',      notes: ['b'] },
    ];
    expect(validateCsvConfig({ buckets })).toBeNull();
  });

  it('rejects when body is not an object', () => {
    expect(validateCsvConfig(null)).toMatch(/object/i);
    expect(validateCsvConfig('x')).toMatch(/object/i);
  });

  it('rejects when buckets is not an array', () => {
    expect(validateCsvConfig({ buckets: 'nope' })).toMatch(/array/i);
  });

  it('rejects an empty bucket name', () => {
    const buckets = [...okBuckets, { name: '   ', type: 'note', notes: [] }];
    expect(validateCsvConfig({ buckets })).toMatch(/name/i);
  });

  it('rejects duplicate bucket names', () => {
    const buckets = [...okBuckets, { name: 'Success', type: 'note', notes: [] }];
    expect(validateCsvConfig({ buckets })).toMatch(/duplicate/i);
  });

  it('rejects an invalid type value', () => {
    const buckets = [...okBuckets, { name: 'X', type: 'weird', notes: [] }];
    expect(validateCsvConfig({ buckets })).toMatch(/type/i);
  });

  it('rejects when notes is not an array', () => {
    const buckets = [...okBuckets, { name: 'X', type: 'note', notes: 'oops' }];
    expect(validateCsvConfig({ buckets })).toMatch(/notes/i);
  });

  it('rejects when notes contains non-strings', () => {
    const buckets = [...okBuckets, { name: 'X', type: 'note', notes: [1, 2] }];
    expect(validateCsvConfig({ buckets })).toMatch(/notes/i);
  });

  it('rejects when a default bucket is missing', () => {
    const buckets = okBuckets.filter((b) => b.name !== 'Success');
    expect(validateCsvConfig({ buckets })).toMatch(/Success/);
  });
});

• Step 2: Run the test to verify it fails

cd /home/sam/docker-images/experiments-database/backend
npx jest tests/csvConfigValidation.test.js

Expected: FAIL — module not found.

• Step 3: Implement the validator

Create backend/src/lib/csvConfigValidation.js:

const REQUIRED_DEFAULT_BUCKETS = ['Success', 'Failure', 'Other'];
const ALLOWED_TYPES = new Set(['note', 'numerical']);

/**
 * Validate a csv_analysis_config payload.
 * @param {unknown} body
 * @returns {string|null} error message, or null if valid.
 */
function validateCsvConfig(body) {
  if (body === null || typeof body !== 'object' || Array.isArray(body)) {
    return 'Body must be an object';
  }
  const { buckets } = body;
  if (!Array.isArray(buckets)) {
    return 'buckets must be an array';
  }

  const seenNames = new Set();
  for (const b of buckets) {
    if (b === null || typeof b !== 'object' || Array.isArray(b)) {
      return 'Each bucket must be an object';
    }
    if (typeof b.name !== 'string' || b.name.trim() === '') {
      return 'Each bucket must have a non-empty name';
    }
    if (seenNames.has(b.name)) {
      return `Duplicate bucket name: ${b.name}`;
    }
    seenNames.add(b.name);

    if (!ALLOWED_TYPES.has(b.type)) {
      return `Invalid type for bucket ${b.name}: ${b.type}`;
    }
    if (!Array.isArray(b.notes)) {
      return `notes must be an array for bucket ${b.name}`;
    }
    if (!b.notes.every((n) => typeof n === 'string')) {
      return `notes must be an array of strings for bucket ${b.name}`;
    }
  }

  for (const required of REQUIRED_DEFAULT_BUCKETS) {
    if (!seenNames.has(required)) {
      return `Missing required default bucket: ${required}`;
    }
  }

  return null;
}

module.exports = { validateCsvConfig, REQUIRED_DEFAULT_BUCKETS };

• Step 4: Run the test to verify it passes

cd /home/sam/docker-images/experiments-database/backend
npx jest tests/csvConfigValidation.test.js

Expected: PASS, all 10 tests green.

• Step 5: Commit

cd /home/sam/docker-images/experiments-database
git add backend/src/lib/csvConfigValidation.js backend/tests/csvConfigValidation.test.js
git commit -m "feat(backend): add csv_analysis_config validator"

---

Task 3: Backend PATCH /animals/:id/csv-config — route + tests

Files:

• Modify: backend/src/routes/animals.js

• Modify: backend/tests/animals.test.js

• Step 1: Add the failing tests

In backend/tests/animals.test.js, append the following describe block at the bottom (before the final }); if any wrap; the existing file uses top-level describes, so just append):

describe('PATCH /api/animals/:id/csv-config', () => {
  const VALID_CONFIG = {
    buckets: [
      { name: 'Success', type: 'note', notes: ['s'] },
      { name: 'Failure', type: 'note', notes: ['f'] },
      { name: 'Other',   type: 'note', notes: [] },
      { name: 'Lick latency', type: 'numerical', notes: ['L25', 'L30'] },
    ],
  };

  it('updates csv_analysis_config and writes audit log', async () => {
    prisma.animal.findUnique.mockResolvedValue(ANIMAL);
    prisma.animal.update.mockResolvedValue({ ...ANIMAL, csv_analysis_config: VALID_CONFIG });
    prisma.auditLog.create.mockResolvedValue({});

    const res = await request(app)
      .patch(`/api/animals/${ANIMAL.id}/csv-config`)
      .send(VALID_CONFIG);

    expect(res.status).toBe(200);
    expect(res.body.csv_analysis_config).toEqual(VALID_CONFIG);
    expect(prisma.animal.update).toHaveBeenCalledWith({
      where: { id: ANIMAL.id },
      data: { csv_analysis_config: VALID_CONFIG },
    });
  });

  it('returns 400 when buckets is missing', async () => {
    const res = await request(app)
      .patch(`/api/animals/${ANIMAL.id}/csv-config`)
      .send({});
    expect(res.status).toBe(400);
    expect(res.body.error).toMatch(/buckets/);
  });

  it('returns 400 for empty bucket name', async () => {
    const bad = { buckets: [...VALID_CONFIG.buckets, { name: '  ', type: 'note', notes: [] }] };
    const res = await request(app)
      .patch(`/api/animals/${ANIMAL.id}/csv-config`)
      .send(bad);
    expect(res.status).toBe(400);
  });

  it('returns 400 for duplicate names', async () => {
    const bad = { buckets: [...VALID_CONFIG.buckets, { name: 'Success', type: 'note', notes: [] }] };
    const res = await request(app)
      .patch(`/api/animals/${ANIMAL.id}/csv-config`)
      .send(bad);
    expect(res.status).toBe(400);
    expect(res.body.error).toMatch(/duplicate/i);
  });

  it('returns 400 for invalid type', async () => {
    const bad = { buckets: [...VALID_CONFIG.buckets, { name: 'X', type: 'weird', notes: [] }] };
    const res = await request(app)
      .patch(`/api/animals/${ANIMAL.id}/csv-config`)
      .send(bad);
    expect(res.status).toBe(400);
  });

  it('returns 400 when missing default bucket', async () => {
    const bad = { buckets: VALID_CONFIG.buckets.filter((b) => b.name !== 'Success') };
    const res = await request(app)
      .patch(`/api/animals/${ANIMAL.id}/csv-config`)
      .send(bad);
    expect(res.status).toBe(400);
    expect(res.body.error).toMatch(/Success/);
  });
});

• Step 2: Run the tests to verify they fail

cd /home/sam/docker-images/experiments-database/backend
npx jest tests/animals.test.js -t "csv-config"

Expected: FAIL — route 404 or 422.

• Step 3: Add the route

In backend/src/routes/animals.js, add this require near the top (after the existing requires):

const { validateCsvConfig } = require('../lib/csvConfigValidation');

Then, just before the final module.exports = router; line, add:

// PATCH /api/animals/:id/csv-config — replace entire CSV-analysis bucket config
router.patch(
  '/:id/csv-config',
  auditMiddleware('animals', prisma.animal),
  async (req, res, next) => {
    try {
      const err = validateCsvConfig(req.body);
      if (err) return res.status(400).json({ error: err });

      const animal = await prisma.animal.update({
        where: { id: req.params.id },
        data: { csv_analysis_config: req.body },
      });
      res.json(animal);
    } catch (e) {
      next(e);
    }
  }
);

• Step 4: Run the tests to verify they pass

cd /home/sam/docker-images/experiments-database/backend
npx jest tests/animals.test.js

Expected: PASS (all existing animal tests + 6 new csv-config tests).

• Step 5: Commit

cd /home/sam/docker-images/experiments-database
git add backend/src/routes/animals.js backend/tests/animals.test.js
git commit -m "feat(backend): PATCH /animals/:id/csv-config endpoint"

---

Task 4: Frontend API client method

Files:

• Modify: frontend/src/api/client.js:46-53

• Step 1: Add updateCsvConfig to animalsApi

In frontend/src/api/client.js, modify the animalsApi object (currently lines 46–53) to:

export const animalsApi = {
  list: (experimentId) =>
    api.get('/animals', { params: { experimentId } }).then((r) => r.data),
  get: (id) => api.get(`/animals/${id}`).then((r) => r.data),
  create: (data) => api.post('/animals', data).then((r) => r.data),
  update: (id, data) => api.put(`/animals/${id}`, data).then((r) => r.data),
  delete: (id) => api.delete(`/animals/${id}`),
  updateCsvConfig: (id, config) =>
    api.patch(`/animals/${id}/csv-config`, config).then((r) => r.data),
};

• Step 2: Commit

cd /home/sam/docker-images/experiments-database
git add frontend/src/api/client.js
git commit -m "feat(frontend): add animalsApi.updateCsvConfig"

---

Task 5: extractNumber() helper (TDD)

Files:

• Modify: frontend/src/lib/csvAnalysis.js (append at bottom)

• Modify: frontend/tests/csvAnalysis.test.js (append at bottom)

• Step 1: Write the failing tests

Append to frontend/tests/csvAnalysis.test.js:

import { extractNumber } from '../src/lib/csvAnalysis';

describe('extractNumber', () => {
  test('extracts positive decimal from prefixed string', () => {
    expect(extractNumber('L25.5')).toBe(25.5);
  });
  test('extracts negative integer with letter prefix', () => {
    expect(extractNumber('R-12')).toBe(-12);
  });
  test('extracts integer from underscore-separated label', () => {
    expect(extractNumber('trial_007')).toBe(7);
  });
  test('does not parse scientific notation as one number', () => {
    // Accepted limitation: regex matches "3.2", strips "e2"
    expect(extractNumber('3.2e2')).toBe(3.2);
  });
  test('strips trailing units', () => {
    expect(extractNumber('42 ms')).toBe(42);
  });
  test('returns null for purely alphabetic string', () => {
    expect(extractNumber('abc')).toBeNull();
  });
  test('returns null for empty string', () => {
    expect(extractNumber('')).toBeNull();
  });
  test('returns null for null/undefined', () => {
    expect(extractNumber(null)).toBeNull();
    expect(extractNumber(undefined)).toBeNull();
  });
});

• Step 2: Run the tests to verify they fail

cd /home/sam/docker-images/experiments-database/frontend
npx jest tests/csvAnalysis.test.js -t "extractNumber"

Expected: FAIL — extractNumber is not a function.

• Step 3: Implement extractNumber

Append to frontend/src/lib/csvAnalysis.js:

// ── Numeric extraction (for numerical buckets) ────────────────────────────────

/**
 * Extract the first signed decimal from a string. Returns null if none found.
 * Examples: "L25.5" -> 25.5, "R-12" -> -12, "abc" -> null
 */
export function extractNumber(str) {
  if (str == null) return null;
  const m = String(str).match(/-?\d+(\.\d+)?/);
  return m ? parseFloat(m[0]) : null;
}

• Step 4: Run the tests to verify they pass

cd /home/sam/docker-images/experiments-database/frontend
npx jest tests/csvAnalysis.test.js -t "extractNumber"

Expected: PASS — all 8 tests green.

• Step 5: Commit

cd /home/sam/docker-images/experiments-database
git add frontend/src/lib/csvAnalysis.js frontend/tests/csvAnalysis.test.js
git commit -m "feat(frontend): add extractNumber helper"

---

Task 6: computeNumericSeries() helper (TDD)

Files:

• Modify: frontend/src/lib/csvAnalysis.js (append after extractNumber)

• Modify: frontend/tests/csvAnalysis.test.js (append at bottom)

• Step 1: Write the failing tests

Append to frontend/tests/csvAnalysis.test.js:

import { computeNumericSeries } from '../src/lib/csvAnalysis';

describe('computeNumericSeries', () => {
  const rows = [
    { frame: '1', timestamp: '0.0', note: 'L25' },
    { frame: '3', timestamp: '2.0', note: 'L30.5' },
    { frame: '2', timestamp: '1.0', note: 'L40' },
    { frame: '4', timestamp: '3.0', note: 'F' },
    { frame: '5', timestamp: '4.0', note: 'abc' },   // bucket-member but no number
    { frame: 'x', timestamp: '5.0', note: 'L99' },   // bad x
    { frame: '6', timestamp: '6.0', note: '' },      // not in bucket
  ];
  const bucket = { name: 'Lick latency', type: 'numerical', notes: ['L25', 'L30.5', 'L40', 'abc', 'L99'] };

  test('returns sorted points with average and counts', () => {
    const out = computeNumericSeries(rows, 'frame', 'note', bucket);
    expect(out.points).toEqual([
      { x: 1, value: 25,   raw: 'L25' },
      { x: 2, value: 40,   raw: 'L40' },
      { x: 3, value: 30.5, raw: 'L30.5' },
    ]);
    expect(out.avg).toBeCloseTo((25 + 40 + 30.5) / 3);
    expect(out.skipped).toBe(2);   // 'abc' (no number) + bad x
    expect(out.total).toBe(5);     // bucket-member rows
    expect(out.xLabel).toBe('frame');
  });

  test('falls back to timestamp x-column when frame is empty string', () => {
    const out = computeNumericSeries(rows, 'timestamp', 'note', bucket);
    expect(out.xLabel).toBe('timestamp');
    expect(out.points[0].x).toBe(0);
  });

  test('returns avg=null and empty points when nothing plottable', () => {
    const onlyBad = [
      { frame: '1', note: 'abc' },
    ];
    const b = { name: 'Z', type: 'numerical', notes: ['abc'] };
    const out = computeNumericSeries(onlyBad, 'frame', 'note', b);
    expect(out.points).toEqual([]);
    expect(out.avg).toBeNull();
    expect(out.skipped).toBe(1);
    expect(out.total).toBe(1);
  });

  test('returns total=0 when bucket notes match nothing in rows', () => {
    const b = { name: 'Empty', type: 'numerical', notes: ['nope'] };
    const out = computeNumericSeries(rows, 'frame', 'note', b);
    expect(out.total).toBe(0);
    expect(out.points).toEqual([]);
    expect(out.avg).toBeNull();
  });
});

• Step 2: Run the tests to verify they fail

cd /home/sam/docker-images/experiments-database/frontend
npx jest tests/csvAnalysis.test.js -t "computeNumericSeries"

Expected: FAIL — computeNumericSeries is not a function.

• Step 3: Implement computeNumericSeries

Append to frontend/src/lib/csvAnalysis.js (after the extractNumber block):

/**
 * Build a numeric-vs-x series for a numerical bucket.
 *
 * @param {object[]} rows       - Parsed CSV rows
 * @param {string}   xColName   - Column to plot on the x-axis (frame col, or timestamp as fallback)
 * @param {string}   noteCol    - Column containing note values
 * @param {object}   bucket     - { name, type:'numerical', notes: string[] }
 * @returns {{points: {x:number,value:number,raw:string}[], avg:number|null, skipped:number, total:number, xLabel:string}}
 */
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 };
}

• Step 4: Run the tests to verify they pass

cd /home/sam/docker-images/experiments-database/frontend
npx jest tests/csvAnalysis.test.js

Expected: PASS — all existing csvAnalysis tests + 4 new computeNumericSeries tests.

• Step 5: Commit

cd /home/sam/docker-images/experiments-database
git add frontend/src/lib/csvAnalysis.js frontend/tests/csvAnalysis.test.js
git commit -m "feat(frontend): add computeNumericSeries helper"

---

Task 7: Refactor CsvAnalysis.jsx state to buckets[]

This is the largest UI task. Refactor first, leave behavior identical (no numerical UI yet — that comes in Tasks 8–10). Splitting reduces blast radius.

Files:

• Modify: frontend/src/components/CsvAnalysis.jsx

• Step 1: Replace category state with buckets state

In frontend/src/components/CsvAnalysis.jsx, change the constants near the top (lines 9–15):

// ── Constants ──────────────────────────────────────────────────────────────────

const DEFAULT_BUCKETS = [
  { name: 'Success', type: 'note', notes: [] },
  { name: 'Failure', type: 'note', notes: [] },
  { name: 'Other',   type: 'note', notes: [] },
];
const DEFAULT_BUCKET_NAMES = new Set(DEFAULT_BUCKETS.map((b) => b.name));

Remove the old DEFAULT_CATEGORIES array.

In the main component, replace these state hooks (currently around lines 109–116):

  const [categories, setCategories] = useState(DEFAULT_CATEGORIES);
  const [customCategories, setCustomCategories] = useState([]);
  const [classifications, setClassifications] = useState({});

with:

  const [buckets, setBuckets] = useState(DEFAULT_BUCKETS);

Add this derived-data block immediately below the state declarations (computed every render — these are cheap):

  const categories         = buckets.map((b) => b.name);
  const customCategories   = buckets.filter((b) => !DEFAULT_BUCKET_NAMES.has(b.name)).map((b) => b.name);
  const noteBuckets        = buckets.filter((b) => b.type === 'note');
  const numericalBuckets   = buckets.filter((b) => b.type === 'numerical');
  const classifications    = Object.fromEntries(
    noteBuckets.flatMap((b) => b.notes.map((n) => [n, b.name]))
  );

• Step 2: Update assign() and unassign() to mutate buckets

Replace the existing assign and unassign functions (around lines 179–186):

  function assign(note, category) {
    setBuckets((prev) => prev.map((b) => {
      if (b.name === category) {
        return b.notes.includes(note) ? b : { ...b, notes: [...b.notes, note] };
      }
      // Remove from any other bucket so a note belongs to at most one
      return b.notes.includes(note) ? { ...b, notes: b.notes.filter((n) => n !== note) } : b;
    }));
    setSelectedNote(null);
  }

  function unassign(note) {
    setBuckets((prev) => prev.map((b) =>
      b.notes.includes(note) ? { ...b, notes: b.notes.filter((n) => n !== note) } : b
    ));
  }

• Step 3: Update addCategory() to append a note bucket

Replace addCategory (around lines 188–195):

  function addBucket(name, type) {
    const trimmed = name.trim();
    if (!trimmed || buckets.some((b) => b.name === trimmed)) return;
    setBuckets((prev) => [...prev, { name: trimmed, type, notes: [] }]);
  }

  // Back-compat shim so existing call site keeps working until Task 8 swaps the UI
  function addCategory() {
    addBucket(newCatName, 'note');
    setNewCatName('');
    setShowNewCat(false);
  }

• Step 4: Update reset() to reset buckets

Replace the body of reset() to reset buckets instead of categories/customCategories:

  function reset() {
    setPhase('upload');
    setRawText('');
    setHeaders([]);
    setRows([]);
    setUniqueNotes([]);
    setBuckets(DEFAULT_BUCKETS);
    setResults(null);
    setSavedAnalysisId(null);
    setSummaryPushed(false);
    setSummaryError(null);
    setFrameCheck(null);
  }

• Step 5: Update the classify-phase JSX bucket grid

Find the {/* Category buckets */} block (around lines 410–427) and change the loop driver from categories.map(cat => …) to buckets.map(b => …). Inside the map, derive cat = b.name and pass notes={b.notes} directly (instead of recomputing from classifications):

          {/* Category buckets */}
          <div className="grid grid-cols-3 gap-3">
            {buckets.map((b) => {
              const colors = getCategoryColors(b.name, customCategories);
              return (
                <CategoryBucket key={b.name} name={b.name} notes={b.notes} colors={colors}
                  isOver={overBucket === b.name}
                  onDragOver={() => setOverBucket(b.name)}
                  onDragLeave={() => setOverBucket(null)}
                  onDrop={() => {
                    if (draggedNote) { assign(draggedNote, b.name); setDraggedNote(null); setOverBucket(null); }
                  }}
                  onClickAssign={() => { if (selectedNote) assign(selectedNote, b.name); }}
                  onRemoveNote={(note) => unassign(note)}
                />
              );
            })}
          </div>

• Step 6: Run tests + smoke-test the dev server

cd /home/sam/docker-images/experiments-database/frontend
npx jest --runInBand --forceExit

Expected: All existing tests still pass. Then visually verify in the browser:

docker compose -f /home/sam/docker-images/experiments-database/docker-compose.dev.yml up -d

Open a daily-status page, drop a CSV, classify notes — Success/Failure/Other behave as before. No regressions.

• Step 7: Commit

cd /home/sam/docker-images/experiments-database
git add frontend/src/components/CsvAnalysis.jsx
git commit -m "refactor(frontend): unify CsvAnalysis state under buckets[]"

---

Task 8: Pass animal prop and load persisted config

Files:

• Modify: frontend/src/pages/DailyStatusDetail.jsx:700-704

• Modify: frontend/src/components/CsvAnalysis.jsx

• Step 1: Pass animal from page

In frontend/src/pages/DailyStatusDetail.jsx, modify the <CsvAnalysis> instantiation (currently around lines 700–704) to:

      <CsvAnalysis
        dailyStatusId={status.id}
        animal={status.animal}
        onSaved={(saved) => setAnalyses((prev) => [saved, ...prev])}
        onSummaryPushed={(summary) => setStatus((prev) => ({ ...prev, analysis_summary: { ...summary, computed_at: new Date().toISOString() } }))}
      />

• Step 2: Accept the prop and seed buckets from it

In frontend/src/components/CsvAnalysis.jsx:

a) Change the function signature:

export default function CsvAnalysis({ dailyStatusId, animal, onSaved, onSummaryPushed }) {

b) Initialize buckets from animal.csv_analysis_config if present. Replace:

  const [buckets, setBuckets] = useState(DEFAULT_BUCKETS);

with:

  const [buckets, setBuckets] = useState(() => animal?.csv_analysis_config?.buckets ?? DEFAULT_BUCKETS);

c) reset() should also re-seed from animal config (so "Start over" returns to the saved state, not raw defaults):

    setBuckets(animal?.csv_analysis_config?.buckets ?? DEFAULT_BUCKETS);

• Step 3: Manual smoke test

Start dev server (if not running) and load a daily-status page. Drop a CSV. Verify Success/Failure/Other still appear empty. Then assign a few notes, run analysis (it will fail to persist until Task 11 — that's fine), reload the page, drop the CSV again. Buckets should still be defaults (no save happened yet).

• Step 4: Commit

cd /home/sam/docker-images/experiments-database
git add frontend/src/pages/DailyStatusDetail.jsx frontend/src/components/CsvAnalysis.jsx
git commit -m "feat(frontend): seed CsvAnalysis buckets from animal config"

---

Task 9: "+ Add field" picker (note vs numerical) + bucket type rendering

Files:

• Modify: frontend/src/components/CsvAnalysis.jsx

• Step 1: Replace "+ New category" UI with type-aware picker

Find the {/* Add new category */} block (around lines 429–446). Replace the entire block with:

          {/* Add new bucket — note or numerical */}
          <div className="flex items-center gap-2 flex-wrap">
            {showNewCat ? (
              <>
                <input
                  autoFocus
                  value={newCatName}
                  onChange={(e) => setNewCatName(e.target.value)}
                  onKeyDown={(e) => { if (e.key === 'Escape') setShowNewCat(false); }}
                  placeholder="Field name…"
                  className="text-sm border border-gray-300 rounded px-2 py-1 focus:outline-none focus:ring-2 focus:ring-indigo-400 w-40"
                />
                <label className="text-xs text-gray-600 inline-flex items-center gap-1">
                  <input type="radio" name="newBucketType" value="note"
                    checked={newCatType === 'note'}
                    onChange={() => setNewCatType('note')} /> Note
                </label>
                <label className="text-xs text-gray-600 inline-flex items-center gap-1">
                  <input type="radio" name="newBucketType" value="numerical"
                    checked={newCatType === 'numerical'}
                    onChange={() => setNewCatType('numerical')} /> Numerical
                </label>
                <Button size="sm" type="button" onClick={() => {
                  addBucket(newCatName, newCatType);
                  setNewCatName('');
                  setNewCatType('note');
                  setShowNewCat(false);
                }}>Add</Button>
                <button type="button" onClick={() => setShowNewCat(false)}
                  className="text-xs text-gray-400 hover:text-gray-600">Cancel</button>
              </>
            ) : (
              <button type="button" onClick={() => setShowNewCat(true)}
                className="text-xs text-indigo-500 hover:text-indigo-700 border border-dashed border-indigo-300 rounded-full px-3 py-0.5 hover:border-indigo-500 transition-colors">
                + Add field
              </button>
            )}
          </div>

• Step 2: Add newCatType state

Add this state hook next to the existing newCatName state (search for const [newCatName):

  const [newCatType, setNewCatType] = useState('note');

Also delete the now-unused addCategory shim from Task 7 — the new picker calls addBucket directly.

• Step 3: Pass type to CategoryBucket and add bulk-add for numerical buckets

Update the CategoryBucket props in the bucket-grid loop (Task 7 step 5) so it receives type and a bulk-add callback:

            {buckets.map((b) => {
              const colors = getCategoryColors(b.name, customCategories);
              const isCustom = !DEFAULT_BUCKET_NAMES.has(b.name);
              return (
                <CategoryBucket key={b.name} name={b.name} type={b.type} notes={b.notes} colors={colors}
                  isOver={overBucket === b.name}
                  isCustom={isCustom}
                  unassignedCount={unassignedNotes.length}
                  onDragOver={() => setOverBucket(b.name)}
                  onDragLeave={() => setOverBucket(null)}
                  onDrop={() => {
                    if (draggedNote) { assign(draggedNote, b.name); setDraggedNote(null); setOverBucket(null); }
                  }}
                  onClickAssign={() => { if (selectedNote) assign(selectedNote, b.name); }}
                  onRemoveNote={(note) => unassign(note)}
                  onBulkAddUnassigned={() => {
                    for (const n of unassignedNotes) assign(n, b.name);
                  }}
                  onRemoveBucket={() => setBuckets((prev) => prev.filter((x) => x.name !== b.name))}
                />
              );
            })}

• Step 4: Update CategoryBucket to render type badge, bulk-add, remove

Replace the CategoryBucket component (around lines 55–84) with:

function CategoryBucket({
  name, type, notes, colors, isOver, isCustom, unassignedCount,
  onDrop, onDragOver, onDragLeave, onRemoveNote, onClickAssign,
  onBulkAddUnassigned, onRemoveBucket,
}) {
  return (
    <div
      onDragOver={(e) => { e.preventDefault(); onDragOver(); }}
      onDragLeave={onDragLeave}
      onDrop={(e) => { e.preventDefault(); onDrop(); }}
      onClick={onClickAssign}
      className={`
        relative group rounded-lg border-2 border-dashed p-3 min-h-[80px] transition-all cursor-pointer
        ${isOver ? `${colors.border} ${colors.bg} scale-[1.02]` : 'border-gray-200 hover:border-gray-300'}
      `}
    >
      <div className="flex items-center justify-between mb-2">
        <p className={`text-xs font-semibold uppercase tracking-wide ${colors.text}`}>
          {name}
          {type === 'numerical' && (
            <span className="ml-2 text-[9px] font-bold uppercase bg-white/60 px-1 py-0.5 rounded border border-current/20">
              numerical
            </span>
          )}
        </p>
        {isCustom && (
          <button type="button"
            onClick={(e) => { e.stopPropagation(); onRemoveBucket(); }}
            className="text-[10px] text-gray-300 hover:text-red-500 opacity-0 group-hover:opacity-100 transition-opacity"
            title="Remove this field">✕</button>
        )}
      </div>

      {type === 'numerical' && unassignedCount > 0 && (
        <button type="button"
          onClick={(e) => { e.stopPropagation(); onBulkAddUnassigned(); }}
          className={`text-[10px] mb-2 px-2 py-0.5 rounded border ${colors.border} ${colors.text} hover:bg-white transition-colors`}>
          + Add all {unassignedCount} unassigned
        </button>
      )}

      <div className="flex flex-wrap gap-1.5 min-h-[28px]">
        {notes.length === 0 && (
          <span className="text-xs text-gray-400 italic">Drop here</span>
        )}
        {notes.map((note) => (
          <span key={note}
            className={`inline-flex items-center gap-1 px-2 py-0.5 rounded-full text-xs font-mono font-semibold ${colors.bg} ${colors.border} ${colors.text} border`}
          >
            {note}
            <button type="button" onClick={(e) => { e.stopPropagation(); onRemoveNote(note); }}
              className="opacity-50 hover:opacity-100 leading-none">✕</button>
          </span>
        ))}
      </div>
    </div>
  );
}

• Step 5: Manual smoke test

Restart dev server if needed. Drop a CSV. Click "+ Add field", choose "Numerical", name it "Latency", click Add. Verify:

• New bucket appears with "numerical" badge.

• Bucket shows "+ Add all N unassigned" button when notes are unassigned.

• Clicking it moves all unassigned notes into the bucket.

• ✕ control on the new bucket removes it.

• ✕ control does NOT appear on Success/Failure/Other.

• Step 6: Run tests

cd /home/sam/docker-images/experiments-database/frontend
npx jest --runInBand --forceExit

Expected: PASS.

• Step 7: Commit

cd /home/sam/docker-images/experiments-database
git add frontend/src/components/CsvAnalysis.jsx
git commit -m "feat(frontend): add field picker + numerical bucket UI in classify phase"

---

Task 10: Numerical-plot rendering in results phase

Files:

• Modify: frontend/src/components/CsvAnalysis.jsx

• Step 1: Compute numeric series in compute()

Find compute() (around lines 202–230). Add numerical-series computation after the existing runAnalysis call, before the setResults call:

  async function compute() {
    // Note-only classifications so numerical buckets don't pollute counts/runs
    const noteClassifications = Object.fromEntries(
      noteBuckets.flatMap((b) => b.notes.map((n) => [n, b.name]))
    );
    const res = runAnalysis(rows, timestampCol, noteCol, noteClassifications);

    // Numerical series — one per numerical bucket. xCol = frame if set, else timestamp.
    const xCol = frameCol || timestampCol;
    const numericSeries = numericalBuckets.map((b) => ({
      bucket: b,
      ...computeNumericSeries(rows, xCol, noteCol, b),
    }));

    setResults({ ...res, numericSeries });
    setPhase('results');

    if (!dailyStatusId) return;
    setSaving(true);
    setSaveError(null);
    try {
      const saved = await analysesApi.create(dailyStatusId, {
        file_name:         fileName || null,
        timestamp_col:     timestampCol,
        note_col:          noteCol,
        classifications:   noteClassifications,
        total_note_rows:   res.totalNoteRows,
        session_end_ts:    res.sessionEndTs,
        sequence:          res.sequence,
        category_counts:   res.categoryCounts,
        consecutive_stats: res.consecutiveStats,
        runs:              res.runs,
      });
      setSavedAnalysisId(saved.id);
      onSaved?.(saved);
    } catch (err) {
      setSaveError(err.message ?? 'Failed to save analysis');
    } finally {
      setSaving(false);
    }
  }

Also add the import for computeNumericSeries at the top of the file:

import { parseCSVHeaders, parseCSV, getUniqueNoteValues, runAnalysis, checkFrameConsecutive, computeNumericSeries } from '../lib/csvAnalysis';

• Step 2: Add Recharts import

Near the top of the file (after the existing imports):

import {
  LineChart, Line, XAxis, YAxis, CartesianGrid, Tooltip, ResponsiveContainer,
} from 'recharts';

• Step 3: Render numerical-plot panels in the results phase

Find the RunSequenceView line in the results phase (around line 569). Insert this block immediately after the <RunSequenceView … /> element:

          {/* Numerical plots — one panel per numerical bucket */}
          {results.numericSeries && results.numericSeries.length > 0 && (
            <div>
              <h3 className="text-xs font-semibold text-gray-500 uppercase tracking-wide mb-2">Numerical Plots</h3>
              <div className="space-y-4">
                {results.numericSeries.map(({ bucket, points, avg, skipped, total, xLabel }) => {
                  const colors = getCategoryColors(bucket.name, customCategories);
                  return (
                    <div key={bucket.name} className="border border-gray-200 rounded-lg p-3">
                      <div className="flex items-baseline justify-between mb-2">
                        <p className={`text-sm font-semibold ${colors.text}`}>
                          {bucket.name} <span className="text-xs text-gray-400 font-normal">vs {xLabel}</span>
                        </p>
                        <p className="text-xs text-gray-500">
                          {avg != null ? <>Avg: <strong className="text-gray-800">{avg.toFixed(2)}</strong> · </> : null}
                          {points.length}/{total} rows plottable
                          {skipped > 0 && <> · {skipped} skipped</>}
                        </p>
                      </div>
                      {points.length > 0 ? (
                        <ResponsiveContainer width="100%" height={220}>
                          <LineChart data={points} margin={{ top: 4, right: 16, left: 0, bottom: 4 }}>
                            <CartesianGrid strokeDasharray="3 3" stroke="#eee" />
                            <XAxis dataKey="x" type="number" domain={['dataMin', 'dataMax']}
                              tick={{ fontSize: 10 }} label={{ value: xLabel, position: 'insideBottom', offset: -2, fontSize: 10 }} />
                            <YAxis tick={{ fontSize: 10 }} />
                            <Tooltip />
                            <Line type="monotone" dataKey="value" stroke="currentColor" className={colors.text}
                              dot={{ r: 2 }} strokeWidth={2} />
                          </LineChart>
                        </ResponsiveContainer>
                      ) : (
                        <p className="text-xs text-gray-400 italic">
                          No plottable values — {total} of {total} note rows had no extractable number.
                        </p>
                      )}
                    </div>
                  );
                })}
              </div>
            </div>
          )}

• Step 4: Manual smoke test

Drop a CSV that has notes like L25, L30, L40 (or modify a test CSV temporarily). Create a numerical bucket "Lick latency", bulk-add all unassigned, click "Run analysis →". Verify:

• A "Numerical Plots" section appears below RunSequenceView.

• The chart renders with frame on x-axis (or timestamp if frame col is "— skip —").

• Average is shown in the panel header.

• Skipped count surfaces if some notes have no number.

• Step 5: Run tests

cd /home/sam/docker-images/experiments-database/frontend
npx jest --runInBand --forceExit

Expected: PASS.

• Step 6: Commit

cd /home/sam/docker-images/experiments-database
git add frontend/src/components/CsvAnalysis.jsx
git commit -m "feat(frontend): render numerical-bucket plots in CSV-analysis results"

---

Task 11: Persist csv_analysis_config after successful analysis

Files:

• Modify: frontend/src/components/CsvAnalysis.jsx

• Step 1: Add import + state for config-save error

At the top of the file, add animalsApi to the existing import:

import { analysesApi, dailyStatusesApi, animalsApi } from '../api/client';

Add a new state hook next to the other results-phase state hooks (search for summaryError):

  const [configSaveError, setConfigSaveError] = useState(null);

• Step 2: Save config inside compute()

In compute(), before the if (!dailyStatusId) return; line (so config saves even when there is no daily-status context, and runs in parallel with analysis-save), add:

    // Persist bucket config to the animal so future uploads pre-classify.
    // Independent of dailyStatusId — runs even for ad-hoc analyses.
    if (animal?.id) {
      setConfigSaveError(null);
      animalsApi.updateCsvConfig(animal.id, { buckets })
        .catch((err) => setConfigSaveError(err.message ?? 'Failed to save bucket config'));
    }

The fire-and-forget Promise is intentional: analysis-save and config-save are independent, and we don't want to block UI feedback on config persistence. Errors surface in the inline message added in Step 3.

• Step 3: Surface the error in the results phase

Find the {/* Save metrics to daily status */} block (around line 577). Immediately above the <div className="flex gap-3 pt-2 border-t border-gray-100"> (the Reclassify/Start over row near the bottom), add:

          {configSaveError && (
            <p className="text-xs text-amber-600 bg-amber-50 border border-amber-200 rounded px-3 py-1.5">
              Bucket config didn't save: {configSaveError}. Analysis still ran. Try running again to retry.
            </p>
          )}

• Step 4: Manual smoke test

Drop a CSV. Classify a note into Success. Create a numerical bucket. Bulk-add unassigned. Run analysis. Then reload the page. Drop the same CSV again. Verify:

• Success bucket already contains the previously-assigned note.
• Numerical bucket exists with its previously-assigned notes.
• Only genuinely-new note values (if any) appear as Unassigned.

Then test the error path: temporarily stop the backend (docker compose stop backend from the dev compose), drop a CSV, run analysis. Expected: analysis still renders; an amber inline message about config save failing appears at the bottom of the results panel. Restart backend.

• Step 5: Run all tests

cd /home/sam/docker-images/experiments-database/backend && npx jest --runInBand --forceExit
cd /home/sam/docker-images/experiments-database/frontend && npx jest --runInBand --forceExit

Expected: PASS in both.

• Step 6: Commit

cd /home/sam/docker-images/experiments-database
git add frontend/src/components/CsvAnalysis.jsx
git commit -m "feat(frontend): persist bucket config to animal on successful analysis"

---

Task 12: End-to-end manual verification + final cleanup

Files: none (manual verification only)

• Step 1: Full end-to-end test in browser

Verify each scenario:

1. First-time CSV upload (no saved config): open animal A's daily-status page, drop CSV. Buckets are Success/Failure/Other only. All unique notes are Unassigned.
2. Add numerical bucket, bulk-add, run analysis, reload: create "Latency", click "Add all unassigned", run analysis. Reload page. Drop same CSV. Bucket exists, notes pre-assigned.
3. Cross-CSV with new notes: drop a different CSV (same animal) that has both old and new note values. Old ones land in their saved buckets; new ones appear as Unassigned.
4. Different animal: open animal B's page. Buckets default; animal A's config does not leak.
5. Frame column missing: in the columns phase, set frame to "— skip —". Run analysis with a numerical bucket. Chart still renders, x-axis labeled with timestamp column name.
6. Bucket with no plottable rows: create a numerical bucket containing only notes without any digits. Panel shows the "No plottable values" message instead of an empty chart.
7. Remove a custom bucket: add a custom note bucket "Bonus", assign a note, then click ✕. The note returns to Unassigned. After running analysis, the bucket is gone from the persisted config (verify by reloading).
8. Default-bucket safety: confirm Success/Failure/Other have no ✕ control.

• Step 2: Confirm category counts ignore numerical buckets

In a results view with both note and numerical buckets, the "Category Counts" table and "Consecutive Runs" table only show note buckets. The "Save metrics to daily status" calculation also only uses note buckets (verify by clicking it and inspecting the saved summary in the AnalysisSummaryCard above).

• Step 3: Final commit if any cleanup needed

cd /home/sam/docker-images/experiments-database
git status
# If anything stray (console.logs, unused imports), clean and commit:
# git add ... && git commit -m "chore: cleanup after numerical-buckets feature"

---

Files touched (summary)

Created:

• backend/src/lib/csvConfigValidation.js
• backend/tests/csvConfigValidation.test.js
• backend/prisma/migrations/<ts>_add_animal_csv_analysis_config/migration.sql (auto-generated)

Modified:

• backend/prisma/schema.prisma (Animal model)
• backend/src/routes/animals.js (PATCH endpoint)
• backend/tests/animals.test.js (PATCH tests)
• frontend/src/api/client.js (animalsApi.updateCsvConfig)
• frontend/src/lib/csvAnalysis.js (extractNumber, computeNumericSeries)
• frontend/tests/csvAnalysis.test.js (helper tests)
• frontend/src/components/CsvAnalysis.jsx (state refactor + numerical UI + chart + persist)
• frontend/src/pages/DailyStatusDetail.jsx (pass animal prop)

Backup:

• /home/sam/docker-images/archived/experiments_db_<timestamp>.dump