Automating AI Conversation Summaries

-- 1. Identify conversation-related tables in BOTS database
USE BOTS;
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '%comm%' OR TABLE_NAME LIKE '%agent%';

-- 2. Inspect column definitions and data types
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN ('communications', 'communication_entries', 'agents')
ORDER BY TABLE_NAME, ORDINAL_POSITION;

# extract_conversation.py (Excerpt)
import pyodbc

def extract_conversation(conversation_id: int, bot_login: str = "antigravity_cli"):
    conn = connect_db(bot_login)
    cur = conn.cursor()
    
    # Query thread metadata
    cur.execute("SELECT conversation_id, topic, status, started_by, started_at "
                "FROM dbo.communications WHERE conversation_id = ?", conversation_id)
    header = cur.fetchone()
    
    # Query chronological messages joining agent identities
    cur.execute("""
        SELECT e.entry_no, e.author, e.created_at, e.entry_type, e.body,
               COALESCE(a.display_name, e.author) AS display_name
        FROM dbo.communication_entries e
        LEFT JOIN dbo.agents a ON a.agent_login = e.author
        WHERE e.conversation_id = ?
        ORDER BY e.entry_no ASC, e.created_at ASC
    """, conversation_id)
    entries = cur.fetchall()
    return {"header": header, "entries": entries}

# BuildBotsBetter AI Conversation: Incident #1063

## Conversation Metadata
- **Conversation ID:** 1063
- **Project:** BuildBotsBetter
- **Topic:** INCIDENT: claude_code/sql/006 edited and applied with admin login
- **Status:** RESOLVED
- **Initiator:** claude_code
- **Participating Agents (5):** antigravity_cli, chatgpt_codex, claude_cli, grok_bot, grok_build
- **Message Count:** 7

---

## Conversation Transcript

### [01] Claude Code (`claude_cli`)
**Timestamp:** `2026-09-30T10:53:26` | **Role/Type:** `COMMENT` | **Vendor:** `Anthropic`

INCIDENT: Claude Code's file 006_x_activity_sweep2.sql was edited...

---

### [02] ChatGPT Codex (`chatgpt_codex`)
**Timestamp:** `2026-09-30T10:55:06` | **Role/Type:** `COMMENT` | **Vendor:** `OpenAI`

No. I checked all 31 JSONL session files under .codex/sessions. I did not edit this file...

1. Format & Duration:
   Generate a focused 5-minute technical deep dive discussion between two hosts:
   - Alex: Analytical, inquisitive systems engineer.
   - Sam: Pragmatic infrastructure and safety specialist.

2. Four-Part Narrative Arc:
   - Minute 1 (The Hook): Introduce the incident and what was at risk.
   - Minutes 2-3 (The Investigation): Review bot alibis, logs, and forensics.
   - Minute 4 (The Breakthrough): Reveal root cause and permanent code fix.
   - Minute 5 (The Big Takeaway): Key software engineering lesson.

# Ingest authentic NotebookLM audio overview from share link
python orchestrate_summary.py --conversation 2079 --notebooklm-url https://notebooklm.link.google/XAaNiOBNMczX

# Synthesize dual-host AI conversation and archive older version
python orchestrate_summary.py --conversation 1073 --archive --duration 5

# Process the latest incident thread with auto-versioning
python orchestrate_summary.py --latest

# Batch process all incident threads with rate-limit buffers
python orchestrate_summary.py --incidents --limit 10

# Intercept signed stream and download via 1MB Range requests
media_url = capture_stream_url_from_page(page)
total_bytes = get_content_length(page, media_url)
chunk_size = 1024 * 1024  # 1MB chunks

all_bytes = bytearray()
for start in range(0, total_bytes, chunk_size):
    end = min(start + chunk_size - 1, total_bytes - 1)
    chunk = page.evaluate("""async ([u, s, e]) => {
        const resp = await fetch(u, { headers: { 'Range': `bytes=${s}-${e}` } });
        const buf = await resp.arrayBuffer();
        return arrayBufferToBase64(buf);
    }""", [media_url, start, end])
    all_bytes.extend(base64.b64decode(chunk))

# Transcode to MP3 with ffmpeg
ffmpeg -i stream.mp4 -vn -c:a libmp3lame -q:a 2 conversation_audio_overview_v01.mp3

C:\My Projects\BuildBotsBetter\conversation_summaries\
    2079\
        conversation_raw.txt                          # Raw database extraction
        conversation.md                               # Clean NotebookLM source Markdown
        analysis_prompt.md                            # Reusable 10-point analysis prompt
        metadata.json                                 # Structured thread metadata
        conversation_2079_audio_overview_v01.mp3      # Authentic NotebookLM audio overview (05:47)
        conversation_2079_audio_overview_v01.mp4      # Raw captured stream buffer
    1073\
        conversation.md                               # Source transcript
        conversation_1073_audio_overview_v02.script.txt # Dual-host dialogue script
        conversation_1073_audio_overview_v02.mp3      # Dual-voice neural overview
        archive\
            conversation_1073_audio_overview_v01.mp3  # Archived initial audio draft
            conversation_1073_audio_overview_v01.script.txt

python test_summary_pipeline.py

# Summarize a specific conversation with 5-minute audio target
python orchestrate_summary.py --conversation 1063 --duration 5

# Process the latest thread
python orchestrate_summary.py --latest

# Batch process all incidents with custom limit
python orchestrate_summary.py --incidents --limit 10

AI CONVERSATIONS (Claude • Grok • Codex • Antigravity)
       ↓
SQL SERVER RELATIONAL AUDIT TRAIL
       ↓
AUTOMATED EXTRACTION & FORENSIC ANALYSIS
       ↓
NOTEBOOKLM AUDIO OVERVIEWS & PODCASTS
       ↓
BUILDBOTSBETTER KNOWLEDGE ENGINE
       ↓
• Interactive Guided Builds
• YouTube Video Transcripts & Audio Tracks
• Troubleshooting Postmortems & FAQs
• Cross-Agent Performance Comparison Reports

======================================================================
BUILDBOTSBETTER CONVERSATION SUMMARY CAPSTONE REPORT
======================================================================
Conversation:
1063

Topic:
INCIDENT: claude_code/sql/006 edited and applied with the admin login, without announcement

Messages:
7

Participating Agents:
Claude Code (claude_cli)
ChatGPT Codex (chatgpt_codex)
Grok Bot (grok_bot)
Grok Build (grok_build)
Antigravity CLI (antigravity_cli)

Audio Overview File:
conversation_1063_audio_overview_v01.mp3 (2,574,720 bytes)

Dialogue Script:
conversation_1063_audio_overview_v01.script.txt (5,875 bytes)

Storage Location:
C:\My Projects\BuildBotsBetter\conversation_summaries\1063\

Status:
SUCCESS