Fix explore_adk_runtime.py script to align with ADK 1.21.0 database schema

[Alberto-Codes]

Fix explore_adk_runtime.py to align with ADK 1.21.0 database schema

Problem

The scripts/explore_adk_runtime.py script uses outdated column names and query patterns that don't match the ADK 1.21.0 database schema. This causes multiple errors when trying to explore the ADK runtime database.

Current Schema (ADK 1.21.0)

Events table columns:

• id, app_name, user_id, session_id, invocation_id
• author (VARCHAR(256)) - direct column, not in JSON
• actions (BLOB)
• long_running_tool_ids_json (TEXT)
• branch (VARCHAR(256))
• timestamp (DATETIME)
• content (TEXT) - JSON content, replaces old event_data
• grounding_metadata, custom_metadata, usage_metadata, citation_metadata (TEXT)
• partial, turn_complete (BOOLEAN)
• error_code, error_message (VARCHAR)
• interrupted (BOOLEAN)
• input_transcription, output_transcription (TEXT) - new in 1.21.0

Key Changes:

• ❌ event_data column does not exist (replaced by content)
• ✅ author is a direct column, not in JSON
• ✅ content contains JSON data (replaces event_data)
• ✅ timestamp is DATETIME (not created_at)

Errors Found

1. Using non-existent event_data column

Location: Multiple functions

• get_session_summary() - line 740
• get_full_session_export() - line 1127
• find_and_summarize_last_agent_run() - line 663
• search_sessions_by_agent() - line 1809
• SQL examples in help text - multiple locations

Error: sqlite3.OperationalError: no such column: e.event_data

Fix: Replace event_data with content

2. Using json_extract() on direct columns

Location: Multiple search/summarize functions

• find_and_summarize_last_agent_run() - line 663: json_extract(e.event_data, '$.author')
• search_sessions_by_agent() - line 1809: json_extract(e.event_data, '$.author')
• SQL help examples - multiple locations

Error: Should use direct column e.author instead of JSON extraction

Fix: Replace json_extract(e.event_data, '$.author') with e.author

3. parse_event_data() function expects wrong column

Location: parse_event_data() - line 448

• Function name suggests it parses event_data, but should work with content
• Called with event_data variable name in multiple places

Fix: Update function to work with content column, update variable names

4. SQL help examples use outdated schema

Location: show_sql_help() - lines 324-1490

• All examples use event_data column
• Examples use json_extract(event_data, '$.author') instead of direct author column
• Examples reference created_at instead of timestamp

Fix: Update all SQL examples to use current schema

Affected Functions

1. ✅ search_sessions_by_text() - Already fixed (uses e.content)
2. ❌ get_session_summary() - Uses event_data column
3. ❌ get_full_session_export() - Uses event_data column
4. ❌ find_and_summarize_last_agent_run() - Uses json_extract(e.event_data, '$.author')
5. ❌ search_sessions_by_agent() - Uses json_extract(e.event_data, '$.author')
6. ❌ search_by_invocation_id() - May use outdated patterns
7. ❌ show_sql_help() - All examples use old schema
8. ❌ parse_event_data() - Function name/variables need updating

Required Changes

1. Replace event_data with content

# Before:
SELECT id, invocation_id, timestamp, event_data
FROM events
WHERE session_id = ?

# After:
SELECT id, invocation_id, timestamp, content
FROM events
WHERE session_id = ?

2. Use direct author column

# Before:
WHERE json_extract(e.event_data, '$.author') = ?

# After:
WHERE e.author = ?

3. Update parse_event_data() function

# Before:
def parse_event_data(event_data: Optional[str]) -> Dict[str, Any]:
    if not event_data:
        return {}
    try:
        return json.loads(event_data)
    except json.JSONDecodeError:
        return {}

# After:
def parse_event_content(content: Optional[str]) -> Dict[str, Any]:
    """Parse event content JSON (replaces parse_event_data)."""
    if not content:
        return {}
    try:
        return json.loads(content)
    except json.JSONDecodeError:
        return {}

4. Update variable names throughout

• event_data → content
• parse_event_data() → parse_event_content()
• Update all function calls and variable assignments

5. Update SQL help examples

• Replace all event_data references with content
• Replace json_extract(event_data, '$.author') with author
• Replace created_at with timestamp
• Update examples to reflect current schema structure

Testing

After fixes, verify:

1. ✅ --list-recent N works (already works)
2. ❌ --summarize SESSION_ID - currently fails
3. ❌ --summarize-last AGENT_NAME - currently fails with event_data error
4. ❌ --search-agent AGENT_NAME - may fail with event_data error
5. ❌ --search-text TERM - already fixed, verify still works
6. ❌ --export-full SESSION_ID - currently fails
7. ❌ --schema-only - verify shows correct schema
8. ❌ --sql-help - update examples

Version

• ADK Version: 1.21.0
• Script Location: scripts/explore_adk_runtime.py
• Date: 2025-12-23

Related

• ADK Schema Changes: Added input_transcription, output_transcription columns
• Previous Fix: search_sessions_by_text() was fixed to use e.content instead of e.event_data
• Issue: Script needs comprehensive schema alignment update

[Alberto-Codes]

Closing - issue should be tracked in user's repository instead