Skip to content

schema.md

Latest commit

 

History

History
114 lines (98 loc) · 2.23 KB

schema.md

File metadata and controls

114 lines (98 loc) · 2.23 KB

Database Schema

Tables

tasks

Stores task metadata.

CREATE TABLE tasks (
    id TEXT PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    description TEXT NOT NULL,
    status TEXT NOT NULL,
    agent_id TEXT,
    project TEXT,
    task_type TEXT,
    priority INTEGER DEFAULT 0
);

executions

Records each task execution with outcome.

CREATE TABLE executions (
    id SERIAL PRIMARY KEY,
    task_id TEXT NOT NULL REFERENCES tasks(id),
    agent_id TEXT NOT NULL,
    started_at TIMESTAMP NOT NULL DEFAULT NOW(),
    completed_at TIMESTAMP,
    duration_min INTEGER,
    status TEXT NOT NULL,
    exit_code INTEGER,
    error_message TEXT,
    solution_summary TEXT,
    reference_task TEXT,
    confidence REAL,
    predicted_duration INTEGER
);

techniques

Tracks which techniques/patterns were used.

CREATE TABLE techniques (
    execution_id INTEGER REFERENCES executions(id),
    technique TEXT NOT NULL,
    PRIMARY KEY (execution_id, technique)
);

agent_stats

Materialized view of agent performance (auto-updated via trigger).

CREATE TABLE agent_stats (
    agent_id TEXT NOT NULL,
    task_type TEXT NOT NULL,
    total_tasks INTEGER DEFAULT 0,
    successes INTEGER DEFAULT 0,
    failures INTEGER DEFAULT 0,
    timeouts INTEGER DEFAULT 0,
    avg_duration REAL,
    success_rate REAL,
    last_updated TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (agent_id, task_type)
);

ChromaDB Collection

Collection: agent_tasks

Metadata per embedding:

{
  "agent": "frontend-developer",
  "status": "success",
  "duration": 12,
  "task_type": "ui-toggle",
  "success": true,
  "techniques": "react-context,css-variables"
}

Queries

Find similar tasks

similar = collection.query(
    query_embeddings=[embed("Add dark mode")],
    n_results=5,
    where={"success": True}
)

Get agent success rate

SELECT success_rate, avg_duration
FROM agent_stats
WHERE agent_id = 'frontend-developer'
  AND task_type = 'ui-toggle';

Recent failures

SELECT t.description, e.error_message, e.agent_id
FROM tasks t
JOIN executions e ON t.id = e.task_id
WHERE e.status = 'failed'
  AND e.started_at > NOW() - INTERVAL '7 days'
ORDER BY e.started_at DESC;