╔════════════════════════════════════════════════════════════════════════════╗
║                                                                            ║
║  DATABASE SESSION MANAGEMENT INVESTIGATION - EXECUTIVE SUMMARY             ║
║                                                                            ║
╚════════════════════════════════════════════════════════════════════════════╝

CRITICAL ISSUE FOUND
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Location: src/backend/services/task_manager.py:38-42

    async def _get_db(self):
        async for session in get_session():
            return session  # ✗ BREAKS THE GENERATOR!

Problems:
  1. Premature session cleanup (commit/close run immediately)
  2. New session created per call (no transactional consistency)
  3. Error handling bypassed (no automatic rollback)
  4. Masked by SQLite's lazy connection reopening

Impact: CRITICAL
  - Partial operations can succeed (atomicity violated)
  - Would catastrophically fail with PostgreSQL/MySQL
  - Creates subtle data consistency bugs


EVIDENCE
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Test Results (test_session_investigation.py):

  ✓ TEST 1: Correct pattern works perfectly
  ✗ TEST 2: _get_db() breaks session lifecycle
  ✗ TEST 3: Multiple calls create multiple sessions
  ✗ TEST 4: Data inconsistency (1 of 2 tasks lost)
  ✓ TEST 5: Concurrent access works (thanks to SQLite WAL)
  ✓ TEST 6: NullPool is appropriate for SQLite
  ✓ TEST 7: Correct service pattern works

Errors Found:
  - IllegalStateChangeError during session cleanup
  - Connection leak warnings
  - Foreign keys not enabled (pragma not applied)


SOLUTION
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Refactor TaskManager to use dependency injection:

BEFORE (BROKEN):
───────────────────────────────────────────────────────────────────────────
    async def create_task(self, ...):
        db = await self._get_db()  # Session 1
        task = await service.create(...)

        db = await self._get_db()  # Session 2 (different!)
        await service.update_state(...)
        # Two separate transactions! ✗


AFTER (CORRECT):
───────────────────────────────────────────────────────────────────────────
    def __init__(self, session_factory):
        self._session_factory = session_factory

    async def create_task(self, ...):
        async for session in self._session_factory():
            service = TaskService(session)

            task = await service.create(...)
            await adapter.start(...)
            await service.update_state(...)

            # Single transaction - all or nothing! ✓
            return task


IMPLEMENTATION PLAN
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Day 1: Preparation
  - Create feature branch
  - Add monitoring/logging
  - Back up current code

Days 2-3: Core Refactoring
  - Update TaskManager.__init__() (add session_factory)
  - Update main.py (inject get_session)
  - Refactor 9 methods one-by-one
  - Test after each method

Day 3: Fix Foreign Keys
  - Update event listener in db/base.py

Days 4-5: Testing
  - Unit tests (session lifecycle)
  - Integration tests (transaction boundaries)
  - Load tests (100 concurrent requests)

Day 6: Documentation
  - Update docstrings
  - Create migration guide

Day 7: Review & Deploy
  - Code review
  - Final testing
  - Deploy with monitoring

ESTIMATED EFFORT: 5-7 days


VERIFICATION CHECKLIST
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

After fix, verify:

  ☐ No IllegalStateChangeError in logs
  ☐ No connection leak warnings
  ☐ Foreign keys enabled (PRAGMA foreign_keys = 1)
  ☐ WAL mode active (.wal file exists)
  ☐ All tests pass
  ☐ 100 concurrent requests succeed
  ☐ Startup reconciliation works
  ☐ Task state transitions are atomic
  ☐ Error handling works (rollback on failure)


FILES TO REVIEW
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  1. DATABASE_SESSION_INVESTIGATION_REPORT.md (full technical analysis)
  2. SESSION_FIX_ACTION_PLAN.md (implementation checklist)
  3. test_session_investigation.py (proof of issue)
  4. test_api_concurrent.py (load testing)


QUICK START
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  # See the issue yourself:
  python3 test_session_investigation.py

  # Read full report:
  open DATABASE_SESSION_INVESTIGATION_REPORT.md

  # Follow action plan:
  open SESSION_FIX_ACTION_PLAN.md

  # Start implementing:
  git checkout -b fix/database-session-management


QUESTIONS?
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Review the full investigation report for:
  - Phase 1: Current Architecture Analysis
  - Phase 2: SQLite + Async + SQLAlchemy Deep Dive
  - Phase 3: Issues Found (Detailed)
  - Phase 4: Root Cause Analysis
  - Phase 5: Recommended Solutions (with code)
  - Phase 6: Testing Strategy
  - Phase 7: Migration Plan
  - Appendices: Reference docs, test results, decision matrix

╔════════════════════════════════════════════════════════════════════════════╗
║  Status: READY TO IMPLEMENT                                                ║
║  Priority: CRITICAL                                                        ║
║  Risk: HIGH (without fix) → LOW (with fix)                                 ║
╚════════════════════════════════════════════════════════════════════════════╝
