[Agora] Backfill debate_sessions + debate_rounds tables done

[Agora] Backfill debate_sessions + debate_rounds tables

Goal

The debate_sessions and debate_rounds DB tables exist but have 0 rows. Backfill from 21 existing debate.json files in analyses/*/. Modify scidex_orchestrator.py to auto-populate these tables for new analyses.

Acceptance Criteria

Approach

Read AGENTS.md and relevant source files

Understand existing code patterns before modifying

Implement changes following existing conventions (f-string HTML, SQLite, Bedrock Claude)

Test: curl affected pages, verify rendering, run scidex status

Commit atomically with descriptive message

Work Log

2026-04-01 19:35 PT — Slot 0

• Started task: Backfill debate_sessions and debate_rounds tables
• Read AGENTS.md and task spec to understand requirements
• Analyzed database schema and existing debate.json files (18 found)
• Created backfill_debates.py script to:

- Parse all 18 existing debate.json files
- Extract session metadata (rounds, hypotheses counts)
- Insert debate_sessions and debate_rounds rows
- Handle synthesizer JSON parsing for hypothesis counts

• Ran backfill: 18 sessions and 72 rounds successfully inserted
• Modified scidex_orchestrator.py:

- Added save_debate_to_db() method to populate tables
- Modified save_analysis() to call new method for future debates
- Ensures all new analyses auto-populate debate tables

• Tested:

- ✅ All pages render (200 status)
- ✅ API returns correct data
- ✅ Database queries work correctly
- ✅ Python syntax valid

• Verified data quality:

- 18 debate_sessions (one per analysis with debate.json)
- 72 debate_rounds (18 sessions × 4 rounds each)
- Proper persona/action mapping (theorist→propose, skeptic→critique, etc.)
- Token counts and content preserved
- Average 1,996 tokens/round, 143,714 total tokens tracked

• Result: ✅ Complete — Tables backfilled, orchestrator updated for auto-population