From waggle-sqlite
SQLite-specific provider implementation for waggle. Loaded when the active provider is sqlite.
How this skill is triggered — by the user, by Claude, or both
Slash command
/waggle-sqlite:sqlite-providerThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
This file contains all SQLite-specific implementation details for waggle.
This file contains all SQLite-specific implementation details for waggle. Load this file when the active provider is sqlite.
When detecting-provider requests config retrieval for the SQLite provider:
WAGGLE_SQLITE_DB_PATH environment variable. If not set, default to ~/.waggle/tasks.db.headless_config session variable:
dbPath — the resolved path from step 1teamsDatabaseExists (optional — true if teams table has rows)sprintsDatabaseExists (optional — true if sprints table has rows)After loading config, verify the database exists and has the correct schema:
sqlite3 "<dbPath>" ".tables"
Expected tables: tasks, task_dependencies, teams, sprints, intake_log.
If any table is missing, run the init script to auto-repair:
bash ${CLAUDE_PLUGIN_ROOT}/skills/sqlite-provider/scripts/init-db.sh "<dbPath>"
sqlite3 "<dbPath>" "INSERT INTO tasks (title, description, acceptance_criteria, status, priority, executor, requires_review, execution_plan, working_directory, assignees) VALUES ('<title>', '<description>', '<criteria>', '<status>', '<priority>', '<executor>', <0|1>, '<plan>', '<dir>', '<assignees_json>'); SELECT last_insert_rowid();"
To get the generated ID, use:
sqlite3 "<dbPath>" "INSERT INTO tasks (title, status) VALUES ('<title>', 'Backlog') RETURNING id;"
IMPORTANT: Escape single quotes in values by doubling them: ' -> ''.
sqlite3 "<dbPath>" "UPDATE tasks SET <field> = '<value>', updated_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now') WHERE id = '<task_id>';"
For multiple fields:
sqlite3 "<dbPath>" "UPDATE tasks SET status = '<status>', agent_output = '<output>', updated_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now') WHERE id = '<task_id>';"
sqlite3 -json "<dbPath>" "SELECT t.*, GROUP_CONCAT(td.blocked_by_id) as blocked_by_ids FROM tasks t LEFT JOIN task_dependencies td ON t.id = td.task_id WHERE t.id = '<task_id>' GROUP BY t.id;"
sqlite3 "<dbPath>" "DELETE FROM tasks WHERE id = '<task_id>';"
Dependencies are automatically removed via ON DELETE CASCADE.
Add dependency:
sqlite3 "<dbPath>" "INSERT OR IGNORE INTO task_dependencies (task_id, blocked_by_id) VALUES ('<task_id>', '<blocker_id>');"
Remove dependency:
sqlite3 "<dbPath>" "DELETE FROM task_dependencies WHERE task_id = '<task_id>' AND blocked_by_id = '<blocker_id>';"
Use the query script for filtered queries with JSON output:
bash ${CLAUDE_PLUGIN_ROOT}/skills/sqlite-provider/scripts/query-tasks.sh \
"<dbPath>" '<where_clause>' '<order_clause>'
All tasks (no filter):
bash ${CLAUDE_PLUGIN_ROOT}/skills/sqlite-provider/scripts/query-tasks.sh "<dbPath>"
Ready tasks:
bash ${CLAUDE_PLUGIN_ROOT}/skills/sqlite-provider/scripts/query-tasks.sh "<dbPath>" "t.status = 'Ready'"
Tasks by executor and status (single executor):
bash ${CLAUDE_PLUGIN_ROOT}/skills/sqlite-provider/scripts/query-tasks.sh "<dbPath>" "t.status = 'Ready' AND t.executor = 'cowork'"
Tasks by executor and status (multiple executors — for cli/claude-desktop environments):
bash ${CLAUDE_PLUGIN_ROOT}/skills/sqlite-provider/scripts/query-tasks.sh "<dbPath>" "t.status = 'Ready' AND t.executor IN ('cli','claude-desktop','cowork')"
Tasks assigned to current user:
bash ${CLAUDE_PLUGIN_ROOT}/skills/sqlite-provider/scripts/query-tasks.sh "<dbPath>" "t.assignees LIKE '%<user_id>%'"
In Progress tasks (for concurrency check):
bash ${CLAUDE_PLUGIN_ROOT}/skills/sqlite-provider/scripts/query-tasks.sh "<dbPath>" "t.status = 'In Progress' AND t.assignees LIKE '%<user_id>%'"
Sort by Priority then Due Date:
bash ${CLAUDE_PLUGIN_ROOT}/skills/sqlite-provider/scripts/query-tasks.sh "<dbPath>" "" \
"CASE t.priority WHEN 'Urgent' THEN 1 WHEN 'High' THEN 2 WHEN 'Medium' THEN 3 WHEN 'Low' THEN 4 END ASC, t.due_date ASC"
Subtasks of a parent:
bash ${CLAUDE_PLUGIN_ROOT}/skills/sqlite-provider/scripts/query-tasks.sh "<dbPath>" "t.parent_task_id = '<parent_task_id>'"
Check if a task has children:
bash ${CLAUDE_PLUGIN_ROOT}/skills/sqlite-provider/scripts/query-tasks.sh "<dbPath>" "t.parent_task_id = '<task_id>'" | jq '.results | length'
Check if a candidate parent is itself a subtask:
bash ${CLAUDE_PLUGIN_ROOT}/skills/sqlite-provider/scripts/query-tasks.sh "<dbPath>" "t.id = '<candidate_parent_id>'" | jq '.results[0].parent_task_id'
If the result is non-null, the candidate is already a subtask and cannot be used as a parent (2-level limit).
blocked_by array is empty OR query each blocked_by task and confirm all have status = 'Done'.bash ${CLAUDE_PLUGIN_ROOT}/skills/sqlite-provider/scripts/query-tasks.sh "<dbPath>" '<where>' '<order>' | \
jq '[.results[] | {id, title, status, priority, executor, assignees, due_date, blocked_by: (.blocked_by | length | tostring) + " deps"}]'
When referring to a task in dispatch prompts and completion instructions, use:
id columnsqlite3 <dbPath> \"UPDATE tasks SET agent_output = '<result>', status = 'Done', updated_at = strftime('%Y-%m-%dT%H:%M:%SZ', 'now') WHERE id = '<task_id>';\""The following template is injected into dispatch prompts by executing-tasks. Placeholders are resolved at dispatch time.
Task ID: <task_id>
Database path: <db_path>
On completion:
1. Run: sqlite3 "<db_path>" "UPDATE tasks SET agent_output='<result>', status='Done', updated_at=strftime('%Y-%m-%dT%H:%M:%SZ','now') WHERE id='<task_id>';"
- If Requires Review = ON: set status to 'In Review' instead of 'Done'
2. On error: sqlite3 "<db_path>" "UPDATE tasks SET error_message='<error>', status='Blocked', updated_at=strftime('%Y-%m-%dT%H:%M:%SZ','now') WHERE id='<task_id>';"
After any task operation (create, update, delete), push fresh data to the local view server:
bash ${CLAUDE_PLUGIN_ROOT}/skills/sqlite-provider/scripts/query-tasks.sh "<dbPath>"
TASKS_JSON=$(bash ${CLAUDE_PLUGIN_ROOT}/skills/sqlite-provider/scripts/query-tasks.sh "<dbPath>" | jq -c '{tasks: [.results[] | {
id, title, description, acceptanceCriteria: .acceptance_criteria, status, blockedBy: .blocked_by,
priority, executor, requiresReview: .requires_review, executionPlan: .execution_plan,
workingDirectory: .working_directory, sessionReference: .session_reference,
dispatchedAt: .dispatched_at, agentOutput: .agent_output, errorMessage: .error_message,
context, artifacts, repository, dueDate: .due_date, tags, parentTaskId: .parent_task_id,
project, team, assignees, issuer, url: "", sprintId: .sprint_id, sprintName: null,
complexityScore: .complexity_score, backlogOrder: .backlog_order
}], updatedAt: (now | strftime("%Y-%m-%dT%H:%M:%SZ"))}')
curl -s http://localhost:3456/api/health -o /dev/null 2>/dev/null && \
curl -s -X POST http://localhost:3456/api/data \
-H "Content-Type: application/json" -d "$TASKS_JSON" -o /dev/null 2>/dev/null || true
| SQLite Column | TasksResponse Field |
|---|---|
| id | id |
| title | title |
| description | description |
| acceptance_criteria | acceptanceCriteria |
| status | status |
| blocked_by (via task_dependencies) | blockedBy |
| priority | priority |
| executor | executor |
| requires_review | requiresReview (boolean) |
| execution_plan | executionPlan |
| working_directory | workingDirectory |
| session_reference | sessionReference |
| dispatched_at | dispatchedAt |
| agent_output | agentOutput |
| error_message | errorMessage |
| context | context |
| artifacts | artifacts |
| repository | repository |
| due_date | dueDate |
| tags | tags (JSON array) |
| parent_task_id | parentTaskId |
| project | project |
| team | team |
| assignees | assignees (JSON array) |
| issuer | issuer |
| (empty string) | url |
| sprint_id | sprintId |
| complexity_score | complexityScore |
| backlog_order | backlogOrder |
Called by resolving-identity shared skill when active_provider = sqlite.
SQLite is local — no remote user system. Set:
id <- "local"name <- $USER environment variable or "local"email <- nullIf teams table has rows:
sqlite3 -json "<dbPath>" "SELECT * FROM teams;"current_user.namecurrent_user.teams and current_team per the same logic as other providersSQLite is local — return members from teams table if available, otherwise org_members: [].
sqlite3 -json "<dbPath>" "SELECT members FROM teams;" | jq '[.[].members | fromjson | .[] ] | unique_by(.name)'
| Error Category | Condition | Action |
|---|---|---|
| Database locked | SQLITE_BUSY | Retryable — wait 1-2 seconds and retry, max 3 attempts |
| File not found | DB path does not exist | Terminal — instruct user to run setting-up-tasks |
| Schema mismatch | Missing table or column | Auto-repair — run init-db.sh to create missing tables |
npx claudepluginhub kazukinagata/waggle --plugin waggle-sqliteGuides sqlite3 CLI usage to build composable SQLite knowledge databases, design schemas, query data, manage relationships, and output for agent parsing.
SQLite database implementation using LibSQL client and Drizzle ORM. This skill covers local development, Turso cloud, and Cloudflare D1 deployments.
Provides SQLite patterns for Python projects: WAL mode connections, context managers, async aiosqlite ops, migrations, gotchas, CLI. For local DB state, caching, concurrency.