Notebooks Endpoint 500 Error - Column Name Mismatch

Notebooks Endpoint 500 Error - Column Name Mismatch

Goal

Fix the /notebooks endpoint which is returning HTTP 500 errors due to a database column name mismatch.

Root Cause

The notebooks endpoint exists in api.py but queries a non-existent column name. The error from logs:

sqlite3.OperationalError: no such column: n.associated_analysis_id

Analysis:

• Database has column: analysis_id
• Query references: associated_analysis_id
• This mismatch causes the 500 error

The notebooks table schema:

id, title, path, analysis_id, created_at, description, associated_entities, rendered_html_path

Fix Strategy

Update the JOIN clause in both notebooks_page() functions to use the correct column name:

• Change: LEFT JOIN analyses a ON n.associated_analysis_id = a.id
• To: LEFT JOIN analyses a ON n.analysis_id = a.id

Acceptance Criteria

Work Log

2026-04-01 20:50 PT — Slot 7

Investigation:

• Got task from Orchestra (ID: fbfd69a5-635c-4502-8771-ce08343a5672)
• Curl test confirmed HTTP 500 error
• Checked logs: sqlite3.OperationalError: no such column: n.associated_analysis_id
• Verified database schema has analysis_id (not associated_analysis_id)
• Found 8 occurrences of incorrect column name in api.py

Implementation:

• Fixed all 8 occurrences of associated_analysis_id → analysis_id:

- Line 241: /api/notebooks JOIN clause
- Lines 274, 277: /api/notebooks/{id} detail function
- Line 1553: /notebooks HTML page JOIN clause
- Lines 1570, 1571: /notebooks analysis link generation
- Lines 1622, 1625: /notebook/{id} detail page

• Verified Python syntax: OK
• Ready for merge + API restart

Testing Plan:
After merge and API restart, verify:

• curl http://localhost:8000/notebooks → returns 200 OK
• curl http://localhost:8000/api/notebooks → returns valid JSON array
• No OperationalError in logs

Already Resolved — 2026-04-24 15:00Z

Evidence:

• curl -s -o /dev/null -w "%{http_code}" http://localhost:8000/notebooks → 200
• Response contains valid HTML page with title "SciDEX — Notebook Registry"
• PostgreSQL notebooks table has associated_analysis_id column (confirmed via information_schema.columns)
• api.py notebooks_page() at line 51856 uses LEFT JOIN analyses a ON n.associated_analysis_id = a.id — matches DB schema
• git diff origin/main -- api.py shows no pending changes; fix is on main

Fix SHA: Prior agents applied the DB migration and column-name fix to main; no task-tagged commit found but the endpoint is verified working.

Summary: The column mismatch has been resolved — notebooks table has associated_analysis_id matching the query, and /notebooks returns 200 with valid HTML.

File: fbfd69a5-635c-4502-8771-ce08343a5672_spec.md

Modified: 2026-04-25 23:40

Size: 3.0 KB