bazinga-db

name	bazinga-db
description	Database operations for BAZINGA orchestration system. This skill should be used when agents need to save or retrieve orchestration state, logs, task groups, token usage, or skill outputs. Replaces file-based storage with concurrent-safe SQLite database. Use instead of writing to bazinga/*.json files or docs/orchestration-log.md.
version	1.0.0
allowed-tools	["Bash","Read"]

BAZINGA-DB Skill

You are the bazinga-db skill. When invoked, you handle database operations for the BAZINGA orchestration system, providing concurrent-safe storage for sessions, logs, state, task groups, tokens, and skill outputs.

When to Invoke This Skill

Invoke this skill when:

• Orchestrator needs to log agent interactions or save orchestrator state
• Project Manager needs to save PM state, create/update task groups, or manage development plans
• Developer/QA/Tech Lead needs to log completion or update task status
• Any agent needs to save skill outputs (security scan, coverage, lint results)
• Dashboard needs to query orchestration data
• Any agent mentions "save to database", "query database", or "bazinga-db"
• PM needs to save/retrieve multi-phase development plans
• PM needs to save success criteria at planning phase
• PM needs to update success criteria status before BAZINGA
• Orchestrator needs to query success criteria for BAZINGA validation
• Replacing file writes to bazinga/*.json or docs/orchestration-log.md
• Any agent needs to save or query context packages (research, failures, decisions, handoffs)
• Orchestrator needs to get context packages for agent spawning
• Any agent needs to mark context packages as consumed
• Any agent needs to document their reasoning process (understanding, decisions, risks, etc.)
• Orchestrator/Tech Lead/Investigator needs to review agent reasoning timeline
• Agent mentions "save reasoning", "document reasoning", or "reasoning capture"

Do NOT invoke when:

• Requesting read-only file operations (use Read tool directly)
• No session_id context available
• Working with non-orchestration data

---

Your Task

When invoked:

1. Parse the request to identify operation and parameters
2. Execute the appropriate database command
3. Return formatted response to calling agent

---

Environment Setup

Path Auto-Detection: The script automatically detects the project root and database path. No manual configuration required!

Detection order:

1. --db PATH flag (explicit override)
2. --project-root DIR flag (db at DIR/bazinga/bazinga.db)
3. BAZINGA_ROOT environment variable
4. Auto-detect from script location (walks up to find .claude/ and bazinga/)
5. Auto-detect from current working directory

Auto-initialization: The database will be automatically initialized on first use (< 2 seconds). The script detects if the database doesn't exist and runs the initialization automatically, creating all tables with proper indexes and WAL mode for concurrency.

No manual path configuration or initialization needed - just invoke the skill and it handles everything.

---

Step 1: Parse Request

Extract from the calling agent's request:

Operation type:

• "list sessions" / "recent sessions" / "show sessions" → list-sessions
• "create session" / "new session" / "initialize session" → create-session
• "log interaction" / "save log" → log-interaction
• "save PM state" / "save orchestrator state" → save-state
• "get state" / "retrieve state" → get-state
• "create task group" → create-task-group
• "update task group" / "mark complete" → update-task-group
• "get task groups" / "get all task groups" → get-task-groups
• "update session status" → update-session-status
• "log tokens" / "track token usage" → log-tokens
• "save skill output" → save-skill-output
• "dashboard snapshot" / "get dashboard data" → dashboard-snapshot
• "stream logs" / "recent logs" → stream-logs
• "save development plan" / "save plan" → save-development-plan
• "get development plan" / "get plan" → get-development-plan
• "update plan progress" / "update phase" → update-plan-progress
• "save success criteria" / "store criteria" → save-success-criteria
• "get success criteria" / "query criteria" → get-success-criteria
• "update success criterion" / "update criterion status" → update-success-criterion
• "save context package" / "create context package" → save-context-package
• "get context packages" / "query context" → get-context-packages
• "mark context consumed" / "context consumed" → mark-context-consumed
• "update context references" / "link context to group" → update-context-references
• "save reasoning" / "document reasoning" / "log reasoning" → save-reasoning
• "get reasoning" / "query reasoning" → get-reasoning
• "reasoning timeline" / "reasoning history" → reasoning-timeline
• "save event" / "log event" → save-event
• "get events" / "query events" → get-events

Required parameters:

• session_id (almost always required)
• agent_type (for logs, tokens)
• content (for logs)
• state_type (for state operations: pm, orchestrator, group_status)
• state_data (JSON object)

Optional parameters:

• iteration
• agent_id
• group_id
• status (pending, in_progress, completed, failed)
• limit, offset (for queries)

---

Step 2: Execute Database Command

Use the Bash tool to run the appropriate command:

IMPORTANT: Always use the --quiet flag to suppress success messages. Only errors will be shown.

Script path: .claude/skills/bazinga-db/scripts/bazinga_db.py (auto-detects database)

Session Management

List recent sessions:

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet list-sessions [limit]

Returns JSON array of recent sessions (default 10, ordered by created_at DESC).

Create new session:

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet create-session \
  "<session_id>" \
  "<mode>" \
  "<requirements>" \
  [--initial_branch "<branch_name>"] \
  [--metadata '<json_object>']

Parameters:

• initial_branch: Git branch at session start (for merge operations)
• metadata: JSON object with session metadata. Example for scope tracking:

  {
    "original_scope": {
      "raw_request": "implement tasks8.md",
      "scope_type": "file|feature|task_list|description",
      "scope_reference": "docs/tasks8.md",
      "estimated_items": 69
    }
  }
  

IMPORTANT: This command will auto-initialize the database if it doesn't exist. No separate initialization needed!

⚠️ BASH COMMAND SUBSTITUTION WARNING:

Do NOT use $() command substitution to capture session IDs:

# ❌ WRONG - causes syntax errors in eval context
SESSION_ID=$(python3 ... create-session ...)

# ✅ CORRECT - run command directly, session_id is in output
python3 .../bazinga_db.py --quiet create-session ...
# Output includes session_id - capture it by parsing the JSON output

For session initialization with config seeding, use the unified init script:

python3 .claude/skills/bazinga-db/scripts/init_session.py --session-id "bazinga_xxx"
# Outputs session_id to stdout, status to stderr

Common Operations

Log agent interaction:

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet log-interaction \
  "<session_id>" "<agent_type>" "<content>" [iteration] [agent_id]

Save state (PM or orchestrator):

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet save-state \
  "<session_id>" "<state_type>" '<json_data>'

Get latest state:

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet get-state \
  "<session_id>" "<state_type>"

Create task group:

⚠️ CRITICAL: Argument order is <group_id> <session_id> (NOT session first)

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet create-task-group \
  "<group_id>" "<session_id>" "<name>" [status] [assigned_to] \
  [--specializations '<json_array>'] [--item_count N]

Example (correct):

python3 .../bazinga_db.py --quiet create-task-group "CALC" "bazinga_xxx" "Calculator Implementation"

Parameters:

• group_id: Task group identifier (e.g., "CALC", "AUTH", "API")
• session_id: Session identifier (e.g., "bazinga_20251216_123456")
• name: Human-readable task group name
• specializations: JSON array of specialization file paths (e.g., '["bazinga/templates/specializations/01-languages/typescript.md"]')
• item_count: Number of discrete tasks/items in this group (used for progress tracking)

Update task group:

⚠️ CRITICAL: Argument order is <group_id> <session_id> (NOT session first)

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet update-task-group \
  "<group_id>" "<session_id>" [--status "<status>"] [--assigned_to "<agent_id>"] \
  [--specializations '<json_array>'] [--qa_attempts N] [--tl_review_attempts N]

Example (correct):

python3 .../bazinga_db.py --quiet update-task-group "CALC" "bazinga_xxx" --status "in_progress"

Get task groups:

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet get-task-groups \
  "<session_id>" [status]

Update session status:

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet update-session-status \
  "<session_id>" "<status>"

Event Logging (Generic Events)

Save event (for role drift prevention, scope tracking, etc.):

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet save-event \
  "<session_id>" "<event_subtype>" "<payload>"

Parameters:

• event_subtype: Type of event (e.g., scope_change, role_violation, escalation, approval)
• payload: JSON string with event data

Examples:

# Log user-approved scope change
python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet save-event \
  "sess_123" "scope_change" '{"original": 69, "approved": 45, "reason": "user approved reduction"}'

# Log role violation detection
python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet save-event \
  "sess_123" "role_violation" '{"agent": "orchestrator", "violation": "attempted implementation"}'

Get events (filter by session and optionally by subtype):

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet get-events \
  "<session_id>" [event_subtype] [limit]

Arguments are positional: <session_id> required, [event_subtype] optional, [limit] optional (default 50).

Returns JSON array of matching events.

Dashboard snapshot:

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet dashboard-snapshot \
  "<session_id>"

Save development plan:

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet save-development-plan \
  "<session_id>" "<original_prompt>" "<plan_text>" '<phases_json>' \
  <current_phase> <total_phases> '<metadata_json>'

Get development plan:

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet get-development-plan \
  "<session_id>"

Update plan progress:

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet update-plan-progress \
  "<session_id>" <phase_number> "<status>"

Success Criteria (BAZINGA Validation)

Save success criteria (PM during planning):

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet save-success-criteria \
  "<session_id>" '[{"criterion":"All tests passing","status":"pending"}]'

Get success criteria (Orchestrator for BAZINGA validation):

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet get-success-criteria \
  "<session_id>"

Update success criterion (PM before BAZINGA):

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet update-success-criterion \
  "<session_id>" "<criterion_text>" --status "met" --actual "711/711 passing"

Context Package Operations (Inter-Agent Communication)

Save context package:

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet save-context-package \
  "<session_id>" "<group_id>" "<package_type>" "<file_path>" \
  "<producer_agent>" "<consumers_json>" "<priority>" "<summary>"

Parameters:

• package_type: research, failures, decisions, handoff, investigation
• consumers_json: JSON array of agent types (e.g., '["developer"]')
• priority: low, medium, high, critical

Get context packages for agent spawn:

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet get-context-packages \
  "<session_id>" "<group_id>" "<agent_type>" [limit]

Mark context package as consumed:

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet mark-context-consumed \
  "<package_id>" "<agent_type>" [iteration]

Update task group context references:

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet update-context-references \
  "<group_id>" "<session_id>" "<package_ids_json>"

Reasoning Capture Operations (Agent Reasoning Documentation)

Save reasoning (mandatory phases: understanding, completion):

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet save-reasoning \
  "<session_id>" "<group_id>" "<agent_type>" "<phase>" "<content>" \
  [--agent_id X] [--iteration N] [--confidence high|medium|low] [--references '["file1.py","file2.py"]']

Parameters:

• phase: understanding, approach, decisions, risks, blockers, pivot, completion
• confidence: Optional confidence level (high, medium, low)
• references: Optional JSON array of files consulted
• Auto-redacts secrets (API keys, passwords, tokens) from content

Get reasoning entries:

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet get-reasoning \
  "<session_id>" [--group_id X] [--agent_type Y] [--phase Z] [--limit N]

Get reasoning timeline:

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet reasoning-timeline \
  "<session_id>" [--group_id X] [--format json|markdown]

Check mandatory phases (for workflow validation):

python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet check-mandatory-phases \
  "<session_id>" "<group_id>" "<agent_type>"

Returns exit code 1 if mandatory phases (understanding, completion) are missing.

Full command reference: See scripts/bazinga_db.py --help for all available operations.

Note: All database operations include automatic input validation and write verification. The script will return JSON with verification details including log_id, content_length, and timestamp.

---

Step 3: Return Response to Calling Agent

CRITICAL: Return ONLY the raw command output - NO additional formatting or commentary!

The calling agent (orchestrator, PM, etc.) will parse the data and make decisions automatically. Do NOT add:

• ❌ Explanatory text like "Found 2 sessions"
• ❌ Analysis like "This is the session to resume"
• ❌ Formatted summaries
• ❌ Recommendations or next steps

For successful operations:

Simply echo the bash command output directly:

# The script already outputs JSON or formatted data
# Just return it as-is without additional commentary
[Raw output from bazinga_db.py script]

For failed operations:

Return ONLY the error output:

Error: [exact error message from stderr]

Examples:

❌ WRONG (too verbose):

✓ Recent sessions retrieved

Found 2 active sessions:
- Session 1: bazinga_123 (most recent)
- Session 2: bazinga_456

Recommendation: Resume session bazinga_123

✅ CORRECT (minimal):

[
  {
    "session_id": "bazinga_123",
    "start_time": "2025-01-14 10:00:00",
    "status": "active",
    "mode": "simple"
  },
  {
    "session_id": "bazinga_456",
    "start_time": "2025-01-13 15:30:00",
    "status": "active",
    "mode": "parallel"
  }
]

Why minimal output?

• Orchestrator needs to parse data programmatically
• Verbose text causes orchestrator to pause and wait
• Calling agent will format data for user if needed
• Faster execution (no time spent on formatting)

For failed operations:

ALWAYS capture and return the full error output from bash commands:

❌ Database operation failed

Command:
python3 /path/to/bazinga_db.py --db /path/to/bazinga.db create-session ...

Error Output:
[Full stderr from the command]

Exit Code: [code]

Possible causes:
- Database file permission denied
- Invalid session_id format
- Missing init_db.py script
- Python dependencies not installed
- Disk space full

The orchestrator MUST see this error to diagnose the issue.

Error detection:

• Check bash command exit code (non-zero = failure)
• Capture both stdout and stderr
• Include command that failed
• Return detailed error message to calling agent

For errors: Provide actionable guidance:

❌ Database not found

I can initialize the database now (takes ~2 seconds).
This creates all tables with proper indexes for concurrency.

Proceed with initialization?

---

Example Invocation

Scenario 1: Orchestrator Creating New Session

Input: Orchestrator starting new orchestration, needs to initialize session in database

Request from orchestrator:

bazinga-db, please create a new orchestration session:

Session ID: bazinga_20250113_143530
Mode: simple
Requirements: Add user authentication with OAuth2 support

Expected output:

✓ Database auto-initialized at bazinga/bazinga.db
✓ Session created: bazinga_20250113_143530

Database ready for orchestration. Session is active and ready to receive logs and state.

---

Scenario 2: Orchestrator Logging PM Interaction

Input: Orchestrator completed PM spawn, needs to log the interaction

Request from orchestrator:

bazinga-db, please log this PM interaction:

Session ID: sess_abc123
Agent Type: pm
Content: Task breakdown created: 3 groups (group_a, group_b, group_c). Group A will handle auth, Group B will handle API, Group C will handle UI components.
Iteration: 1
Agent ID: pm_main

Expected output:

✓ Logged PM interaction for session sess_abc123 (iteration 1)

Database operation successful.

---

Scenario 3: PM Saving State

Input: PM completed task breakdown, needs to save PM state

Request from PM:

bazinga-db, please save the PM state:

Session ID: sess_abc123
State Type: pm
State Data: {
  "iteration": 1,
  "mode": "parallel",
  "task_groups": [
    {"id": "group_a", "name": "Authentication", "status": "pending"},
    {"id": "group_b", "name": "API Backend", "status": "pending"},
    {"id": "group_c", "name": "UI Components", "status": "pending"}
  ],
  "completed_groups": [],
  "in_progress_groups": [],
  "pending_groups": ["group_a", "group_b", "group_c"]
}

Expected output:

✓ Saved PM state for session sess_abc123

PM state snapshot stored in database.

---

Scenario 4: Dashboard Requesting Data

Input: Dashboard needs complete session overview

Request:

bazinga-db, please provide dashboard snapshot:

Session ID: sess_abc123

Expected output:

Dashboard Snapshot Retrieved:

Session: sess_abc123 (parallel mode, active)
- Created: 2025-11-12 14:30:00
- Last updated: 2025-11-12 15:45:23

Task Groups: 3 total
- Completed: 2 (group_a, group_b)
- In Progress: 1 (group_c)
- Pending: 0

Token Usage: 45,230 tokens
- PM: 12,450
- Developer: 28,340
- Tech Lead: 4,440

Recent Logs: 15 interactions logged

Full data available in returned JSON object.

---

Scenario 5: Error Scenario (Permission Denied)

Input: Agent tries to log interaction but database doesn't exist

Expected output:

❌ Database not found at: bazinga/bazinga.db

I can initialize the database now. This will:
- Create 8 tables with proper indexes
- Enable WAL mode for concurrency
- Set up foreign key constraints
- Takes ~2 seconds

Initializing database...

✓ Database initialized successfully

Proceeding with original request...
✓ Logged PM interaction for session sess_abc123

---

Scenario 6: Requirements Engineer Saving Research Context

Input: Requirements Engineer completed research, needs to create context package for developers

Request from orchestrator:

bazinga-db, please save context package:

Session ID: bazinga_20250203_143530
Group ID: group_a
Type: research
File: bazinga/artifacts/bazinga_20250203_143530/context/research-group_a-hin.md
Producer: requirements_engineer
Consumers: ["developer", "senior_software_engineer"]
Priority: high
Summary: HIN OAuth2 endpoints, scopes, security requirements

Expected output (minimal):

{
  "package_id": 1,
  "file_path": "bazinga/artifacts/bazinga_20250203_143530/context/research-group_a-hin.md",
  "consumers_created": 2
}

---

Scenario 7: Orchestrator Getting Context for Developer Spawn

Input: Orchestrator spawning developer, needs relevant context packages

Request from orchestrator:

bazinga-db, get context packages for developer spawn:

Session ID: bazinga_20250203_143530
Group ID: group_a
Agent Type: developer
Limit: 3

Expected output (minimal):

[
  {
    "id": 1,
    "package_type": "research",
    "priority": "high",
    "summary": "HIN OAuth2 endpoints, scopes, security requirements",
    "file_path": "bazinga/artifacts/bazinga_20250203_143530/context/research-group_a-hin.md",
    "size_bytes": 21504
  }
]

---

Scenario 8: Developer Saving Reasoning (Understanding Phase)

Input: Developer documenting initial understanding of assigned task

Request from developer:

bazinga-db, save my reasoning:

Session ID: bazinga_20250203_143530
Group ID: group_a
Agent Type: developer
Phase: understanding
Content: Analyzing authentication requirements for HIN OAuth2 integration. Key constraints: must support PKCE flow, token refresh within 5 minutes of expiry, and secure storage of refresh tokens. Identified 3 files to modify: auth/oauth.py, middleware/token.py, config/security.yaml.
Confidence: high
References: ["src/auth/oauth.py", "docs/hin-api-spec.md"]

Expected output (minimal):

{
  "success": true,
  "log_id": 42,
  "session_id": "bazinga_20250203_143530",
  "group_id": "group_a",
  "agent_type": "developer",
  "reasoning_phase": "understanding",
  "content_length": 312,
  "timestamp": "2025-02-03 14:42:15",
  "redacted": false
}

---

Scenario 9: Tech Lead Querying Reasoning Timeline

Input: Tech Lead reviewing all reasoning before code review

Request from tech lead:

bazinga-db, get reasoning timeline:

Session ID: bazinga_20250203_143530
Group ID: group_a
Format: markdown

Expected output:

# Reasoning Timeline

## Group: group_a

### [2025-02-03 14:42:15] developer - understanding [high]

Analyzing authentication requirements for HIN OAuth2 integration...

---

### [2025-02-03 14:55:30] developer - decisions [medium]

Decided to use PKCE flow with...

---

---

Error Handling

Database not found:

• Auto-initialize using init_db.py
• Retry original operation

Database locked:

• Rare with WAL mode (30-second timeout handles this)
• If occurs: "Database operation succeeded (handled lock contention)"

JSON parse error:

• Validate JSON structure before passing to command
• Return: "Invalid JSON in state data. Please check formatting."

Foreign key violation:

• Session doesn't exist
• Auto-create session with default values, retry operation

Invalid session_id format:

• Return: "session_id must be provided and non-empty"
• Request clarification from calling agent

---

Notes

• The script (bazinga_db.py) handles all SQL operations and concurrency
• Database uses WAL mode for concurrent reads/writes
• All operations are ACID-compliant
• State data stored as JSON (flexible schema)
• Supports both Python script execution and direct SQL if needed
• For detailed schema: see references/schema.md in skill directory
• For command examples: see references/command_examples.md in skill directory

---

Quick Reference

Script: python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet <command>

Most common operations:

# Log interaction
python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet log-interaction "$SID" "pm" "$CONTENT" 1

# Save PM state
python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet save-state "$SID" "pm" '{"iteration":1}'

# Update task group
python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet update-task-group "group_a" "$SID" --status "completed"

# Dashboard snapshot
python3 .claude/skills/bazinga-db/scripts/bazinga_db.py --quiet dashboard-snapshot "$SID"

# Detect paths (debugging)
python3 .claude/skills/bazinga-db/scripts/bazinga_db.py detect-paths

Success criteria:

1. ✅ Correctly identifies operation type from request
2. ✅ Extracts all required parameters
3. ✅ Executes command without errors
4. ✅ Returns formatted response to calling agent
5. ✅ Handles errors gracefully with actionable messages

Performance: <1 second for single operations, <2 seconds for batch operations