# CFN Docker Logging - Hybrid System

This directory contains both **text files** (human-readable) and **SQLite database** (powerful queries).

## Quick Start

### Text File Queries (Human Readable)
```bash
# View agent stdout
cat <agent-id>-stdout.log

# View agent stderr
cat <agent-id>-stderr.log

# View combined logs
cat <agent-id>-combined.log

# Search for errors
grep -i error *-combined.log

# View summary
cat <agent-id>-summary.txt
```

### SQLite Queries (Powerful Analysis)
```bash
# All queries use: queries/<script-name>.sh logs.db [task_id]

# Find failed containers
./queries/query-failed-containers.sh logs.db TASK_ID

# View agent timeline
./queries/query-agent-timeline.sh logs.db AGENT_ID

# Gate check results
./queries/query-gate-checks.sh logs.db TASK_ID

# Validator consensus history
./queries/query-consensus-history.sh logs.db TASK_ID

# Full analytics summary
./queries/analytics-summary.sh logs.db TASK_ID

# Coordination timeline
./queries/query-coordination-timeline.sh logs.db TASK_ID
```

## Database Schema

### Tables
- `container_logs`: Individual log lines (stdout/stderr)
- `container_events`: Lifecycle (spawn, exit, OOM)
- `coordination_events`: Redis coordination activity
- `gate_checks`: Loop 3 gate results
- `validator_consensus`: Loop 2 validator scores
- `product_owner_decisions`: Final decisions
- `performance_metrics`: Custom metrics

### Custom Queries
```bash
# Direct SQLite access
sqlite3 logs.db

# Example: Find slowest agents
sqlite3 logs.db "SELECT agent_id, duration_seconds FROM container_events WHERE event_type='exit' ORDER BY duration_seconds DESC LIMIT 10;"

# Example: Count errors in logs
sqlite3 logs.db "SELECT COUNT(*) FROM container_logs WHERE log_line LIKE '%error%' COLLATE NOCASE;"

# Example: Agent success rate
sqlite3 logs.db "SELECT agent_id, AVG(CASE WHEN exit_code=0 THEN 1.0 ELSE 0.0 END) as success_rate FROM container_events WHERE event_type='exit' GROUP BY agent_id;"
```

## File Structure
```
logs/docker-mode/TASK_ID/
├── logs.db                          # SQLite database
├── README.md                        # This file
├── queries/                         # Query scripts
│   ├── query-failed-containers.sh
│   ├── query-agent-timeline.sh
│   ├── query-gate-checks.sh
│   ├── query-consensus-history.sh
│   ├── analytics-summary.sh
│   └── query-coordination-timeline.sh
├── <agent-id>-stdout.log           # Agent stdout (text)
├── <agent-id>-stderr.log           # Agent stderr (text)
├── <agent-id>-combined.log         # Combined output (text)
└── <agent-id>-summary.txt          # Execution summary (text)
```

## Benefits

### Text Files
- ✅ Human-readable
- ✅ Standard Unix tools (grep, less, tail)
- ✅ Backward compatible
- ✅ Easy debugging

### SQLite
- ✅ Complex queries (joins, aggregations)
- ✅ Time-series analysis
- ✅ Performance metrics
- ✅ Cross-agent correlation
- ✅ Scalable (handles millions of rows)

## Examples

### Scenario 1: Find why agent failed
```bash
# 1. Check exit code
./queries/query-failed-containers.sh logs.db TASK_ID

# 2. View full timeline
./queries/query-agent-timeline.sh logs.db AGENT_ID

# 3. Read stderr
cat AGENT_ID-stderr.log
```

### Scenario 2: Analyze gate check trends
```bash
# View all gate checks
./queries/query-gate-checks.sh logs.db TASK_ID

# Get pass rate statistics
sqlite3 logs.db "SELECT AVG(pass_rate), MIN(pass_rate), MAX(pass_rate) FROM gate_checks WHERE task_id='TASK_ID';"
```

### Scenario 3: Validator consensus analysis
```bash
# View consensus history
./queries/query-consensus-history.sh logs.db TASK_ID

# Find disagreements (high score variance)
sqlite3 logs.db "SELECT iteration, MAX(score) - MIN(score) as variance FROM validator_consensus WHERE task_id='TASK_ID' GROUP BY iteration HAVING variance > 0.3;"
```

### Scenario 4: Performance optimization
```bash
# Full analytics
./queries/analytics-summary.sh logs.db TASK_ID

# Find bottleneck agents
sqlite3 logs.db "SELECT agent_id, duration_seconds FROM container_events WHERE event_type='exit' AND task_id='TASK_ID' ORDER BY duration_seconds DESC LIMIT 5;"
```