Skip to main content

Storage

Goblin uses SQLite for persistence and git worktrees for isolated development.

SQLite Database​

The database is stored at .goblin/goblin.db.

Schema Overview​

-- Projects: Maps repos to Linear teams
CREATE TABLE projects (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    repo_path TEXT NOT NULL,
    linear_org TEXT NOT NULL,
    linear_team_id TEXT NOT NULL,
    linear_team_key TEXT NOT NULL,
    created_at TEXT NOT NULL
);

-- Pipelines: Issue lifecycle tracking
CREATE TABLE pipelines (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    linear_issue_id TEXT NOT NULL,
    stage TEXT NOT NULL DEFAULT 'scope',
    branch_name TEXT,
    worktree_path TEXT,
    base_commit TEXT,
    pr_url TEXT,
    scoper_agent_id TEXT,
    builder_agent_id TEXT,
    reviewer_agent_id TEXT,
    tester_agent_id TEXT,
    scope_result TEXT,
    review_result TEXT,
    test_result TEXT,
    test_report TEXT,
    qa_steps TEXT,
    assignee_user_id TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

-- Agents: Worker instances
CREATE TABLE agents (
    id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'idle',
    linear_issue_id TEXT,
    session_file TEXT,
    pid INTEGER,
    created_at TEXT NOT NULL,
    last_seen_at TEXT NOT NULL
);

-- Events: Audit log
CREATE TABLE events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    event_type TEXT NOT NULL,
    agent_id TEXT,
    linear_issue_id TEXT,
    data TEXT,
    created_at TEXT NOT NULL
);

-- Project config: Per-project settings
CREATE TABLE project_config (
    project_id TEXT NOT NULL,
    key TEXT NOT NULL,
    value TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    PRIMARY KEY (project_id, key)
);

-- Pipeline stage history: Transition tracking
CREATE TABLE pipeline_stage_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    pipeline_id TEXT NOT NULL,
    stage TEXT NOT NULL,
    status TEXT NOT NULL,
    agent_id TEXT,
    summary TEXT,
    result TEXT,
    started_at TEXT NOT NULL,
    completed_at TEXT
);

-- Preview environments: Running previews
CREATE TABLE preview_environments (
    id TEXT PRIMARY KEY,
    pipeline_id TEXT NOT NULL,
    project_id TEXT NOT NULL,
    linear_issue_id TEXT NOT NULL,
    docker_project_name TEXT NOT NULL,
    port_offset INTEGER NOT NULL DEFAULT 0,
    service_urls TEXT,
    status TEXT NOT NULL DEFAULT 'running',
    provider TEXT,
    instance_id TEXT,
    public_ip TEXT,
    public_url TEXT,
    created_at TEXT NOT NULL,
    stopped_at TEXT
);

-- Linear credentials
CREATE TABLE linear_orgs (
    name TEXT PRIMARY KEY,
    token TEXT NOT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

Database Operations​

from goblin.storage.db import Database

db = Database(".goblin/goblin.db")
db.initialize()  # Creates tables from schema

# Execute queries
db.execute("INSERT INTO projects ...", params)
result = db.execute_one("SELECT * FROM pipelines WHERE id = ?", (id,))
results = db.execute_many("SELECT * FROM agents WHERE status = ?", ("working",))

Git Worktrees​

Each pipeline gets an isolated git worktree:

worktrees/
├── ENG-123-add-auth/          # Worktree for ENG-123
│   ├── .git                   # Worktree git dir
│   ├── .goblin/               # Signal files
│   │   ├── build-complete
│   │   └── GOBLIN_NOTES.md
│   └── src/                   # Project files
├── ENG-124-fix-bug/
└── ENG-125-new-feature/

Worktree Operations​

from goblin.core.worktree import WorktreeManager

wm = WorktreeManager(repo_path, worktrees_dir)

# Create worktree
worktree = wm.create("ENG-123", branch_name="ENG-123-add-auth")

# Get worktree
worktree = wm.get("ENG-123")

# Delete worktree
wm.delete("ENG-123")

# Get diff
diff = wm.get_diff("ENG-123")  # Against base commit

Benefits of Worktrees​

  1. Isolation: Each issue has its own working directory
  2. Parallel Work: Multiple agents work simultaneously
  3. Clean Diffs: Compare against known base commit
  4. No Conflicts: Agents don't interfere with each other

Event Log​

All significant events are logged:

-- Event types
'pipeline_created'
'pipeline_scope_started'
'pipeline_scope_completed'
'pipeline_build_started'
'pipeline_build_completed'
'pipeline_review_started'
'pipeline_review_completed'
'pipeline_test_started'
'pipeline_test_completed'
'pipeline_done'
'pipeline_failed'
'agent_spawned'
'agent_stalled'
'agent_recovered'

Querying Events​

# View recent events
goblin events --limit 50

# Filter by issue
goblin events --issue ENG-123

# Filter by type
goblin events --type pipeline_failed

Backup & Recovery​

Database Backup​

# Backup database
cp .goblin/goblin.db .goblin/goblin.db.backup

# Restore
cp .goblin/goblin.db.backup .goblin/goblin.db

Worktree Recovery​

# List all worktrees
git worktree list

# Prune stale worktrees
git worktree prune

Performance​

  • Indexes: Created on frequently queried columns
  • In-memory mode: For testing with :memory:
  • Connection pooling: Not needed (SQLite handles this)

Important Indexes​

CREATE INDEX idx_pipelines_project ON pipelines(project_id);
CREATE INDEX idx_pipelines_stage ON pipelines(stage);
CREATE INDEX idx_pipelines_issue ON pipelines(linear_issue_id);
CREATE INDEX idx_agents_project ON agents(project_id);
CREATE INDEX idx_agents_status ON agents(status);
CREATE INDEX idx_events_type ON events(event_type);

Next Steps​