--- 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:** ```bash 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:** ```bash python3 .claude\/skills\/bazinga-db\/scripts\/bazinga_db.py --quiet create-session \ "" \ "" \ "" \ [--initial_branch ""] \ [--metadata ''] ``` **Parameters:** - `initial_branch`: Git branch at session start (for merge operations) - `metadata`: JSON object with session metadata. Example for scope tracking: ```json { "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: ```bash # ❌ 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: ```bash 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:** ```bash python3 .claude\/skills\/bazinga-db\/scripts\/bazinga_db.py --quiet log-interaction \ "" "" "" [iteration] [agent_id] ``` **Save state (PM or orchestrator):** ```bash python3 .claude\/skills\/bazinga-db\/scripts\/bazinga_db.py --quiet save-state \ "" "" '' ``` **Get latest state:** ```bash python3 .claude\/skills\/bazinga-db\/scripts\/bazinga_db.py --quiet get-state \ "" "" ``` **Create task group:** ⚠️ **CRITICAL: Argument order is ` ` (NOT session first)** ```bash python3 .claude\/skills\/bazinga-db\/scripts\/bazinga_db.py --quiet create-task-group \ "" "" "" [status] [assigned_to] \ [--specializations ''] [--item_count N] [--initial_tier ""] \ [--component-path ""] [--complexity N] ``` **Example (correct):** ```bash python3 ...\/bazinga_db.py --quiet create-task-group "CALC" "bazinga_xxx" "Calculator Implementation" \ --specializations '["bazinga\/templates\/specializations\/01-languages\/python.md"]' \ --item_count 6 --initial_tier "Developer" --complexity 5 ``` 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) - `initial_tier`: Starting agent tier (`"Developer"` or `"Senior Software Engineer"`) - `component_path`: Monorepo component path (e.g., `"frontend\/"`, `"backend\/"`) for version lookup - `complexity`: Task complexity score (1-10). 1-3=Low (Developer), 4-6=Medium (SSE), 7-10=High (SSE) **Update task group:** ⚠️ **CRITICAL: Argument order is ` ` (NOT session first)** ```bash python3 .claude\/skills\/bazinga-db\/scripts\/bazinga_db.py --quiet update-task-group \ "" "" [--status ""] [--assigned_to ""] \ [--specializations ''] [--item_count N] [--initial_tier ""] \ [--component-path ""] [--qa_attempts N] [--tl_review_attempts N] \ [--security_sensitive 0|1] [--complexity N] ``` **Example (correct):** ```bash python3 ...\/bazinga_db.py --quiet update-task-group "CALC" "bazinga_xxx" --status "in_progress" ``` **Valid status values:** `pending`, `in_progress`, `completed`, `failed`, `approved_pending_merge`, `merging` **Valid initial_tier values:** `"Developer"`, `"Senior Software Engineer"` **Get task groups:** ```bash python3 .claude\/skills\/bazinga-db\/scripts\/bazinga_db.py --quiet get-task-groups \ "" [status] ``` **Update session status:** ```bash python3 .claude\/skills\/bazinga-db\/scripts\/bazinga_db.py --quiet update-session-status \ "" "" ``` ### Event Logging (Generic Events) **Save event (for role drift prevention, scope tracking, etc.):** ```bash python3 .claude\/skills\/bazinga-db\/scripts\/bazinga_db.py --quiet save-event \ "" "" "" ``` **Parameters:** - `event_subtype`: Type of event (e.g., `scope_change`, `role_violation`, `escalation`, `approval`) - `payload`: JSON string with event data **Examples:** ```bash # 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):** ```bash python3 .claude\/skills\/bazinga-db\/scripts\/bazinga_db.py --quiet get-events \ "" [event_subtype] [limit] ``` Arguments are positional: `` required, `[event_subtype]` optional, `[limit]` optional (default 50). Returns JSON array of matching events. **Dashboard snapshot:** ```bash python3 .claude\/skills\/bazinga-db\/scripts\/bazinga_db.py --quiet dashboard-snapshot \ "" ``` **Save development plan:** ```bash python3 .claude\/skills\/bazinga-db\/scripts\/bazinga_db.py --quiet save-development-plan \ "" "" "" '' \ '' ``` **Get development plan:** ```bash python3 .claude\/skills\/bazinga-db\/scripts\/bazinga_db.py --quiet get-development-plan \ "" ``` **Update plan progress:** ```bash python3 .claude\/skills\/bazinga-db\/scripts\/bazinga_db.py --quiet update-plan-progress \ "" "" ``` ### Success Criteria (BAZINGA Validation) **Save success criteria (PM during planning):** ```bash python3 .claude\/skills\/bazinga-db\/scripts\/bazinga_db.py --quiet save-success-criteria \ "" '[{"criterion":"All tests passing","status":"pending"}]' ``` **Get success criteria (Orchestrator for BAZINGA validation):** ```bash python3 .claude\/skills\/bazinga-db\/scripts\/bazinga_db.py --quiet get-success-criteria \ "" ``` **Update success criterion (PM before BAZINGA):** ```bash python3 .claude\/skills\/bazinga-db\/scripts\/bazinga_db.py --quiet update-success-criterion \ "" "" --status "met" --actual "711\/711 passing" ``` ### Context Package Operations (Inter-Agent Communication) **Save context package:** ```bash python3 .claude\/skills\/bazinga-db\/scripts\/bazinga_db.py --quiet save-context-package \ "" "" "" "" \ "" "" "" "

" ``` 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:** ```bash python3 .claude\/skills\/bazinga-db\/scripts\/bazinga_db.py --quiet get-context-packages \ "" "" "" [limit] ``` **Mark context package as consumed:** ```bash python3 .claude\/skills\/bazinga-db\/scripts\/bazinga_db.py --quiet mark-context-consumed \ "" "" [iteration] ``` **Update task group context references:** ```bash python3 .claude\/skills\/bazinga-db\/scripts\/bazinga_db.py --quiet update-context-references \ "" "" "" ``` ### Reasoning Capture Operations (Agent Reasoning Documentation) **Save reasoning (mandatory phases: understanding, completion):** ```bash python3 .claude\/skills\/bazinga-db\/scripts\/bazinga_db.py --quiet save-reasoning \ "" "" "" "" "" \ [--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:** ```bash python3 .claude\/skills\/bazinga-db\/scripts\/bazinga_db.py --quiet get-reasoning \ "" [--group_id X] [--agent_type Y] [--phase Z] [--limit N] ``` **Get reasoning timeline:** ```bash python3 .claude\/skills\/bazinga-db\/scripts\/bazinga_db.py --quiet reasoning-timeline \ "" [--group_id X] [--format json|markdown] ``` **Check mandatory phases (for workflow validation):** ```bash python3 .claude\/skills\/bazinga-db\/scripts\/bazinga_db.py --quiet check-mandatory-phases \ "" "" "" ``` 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: ```bash # 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): ```json [ { "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): ```json { "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): ```json [ { "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): ```json { "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: ```markdown # 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 ` **Most common operations:** ```bash # 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