database-schema

Database Schema

STATUS: NOT IMPLEMENTED IN MVP This skill documents the planned schema for post-MVP persistence. MVP currently has no database - all state is in-memory.

Location: ~/.multishot/database.sqlite

Tables

CREATE TABLE runs (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  status TEXT NOT NULL,  -- 'active' | 'completed'
  current_round_id TEXT,
  FOREIGN KEY (current_round_id) REFERENCES rounds(id)
);

CREATE TABLE rounds ( id TEXT PRIMARY KEY, run_id TEXT NOT NULL, round_number INTEGER NOT NULL, base_prompt TEXT NOT NULL, created_at INTEGER NOT NULL, winner_agent_id TEXT, winner_files_path TEXT, status TEXT NOT NULL, -- 'running' | 'selecting' | 'completed' FOREIGN KEY (run_id) REFERENCES runs(id), FOREIGN KEY (winner_agent_id) REFERENCES agents(id) );

CREATE TABLE agents ( id TEXT PRIMARY KEY, round_id TEXT NOT NULL, agent_number INTEGER NOT NULL, -- 1-4 sandbox_id TEXT, variation_prompt TEXT NOT NULL, status TEXT NOT NULL, -- 'initializing' | 'running' | 'completed' | 'failed' | 'selected' created_at INTEGER NOT NULL, completed_at INTEGER, error_message TEXT, FOREIGN KEY (round_id) REFERENCES rounds(id) );

CREATE TABLE output_logs ( id TEXT PRIMARY KEY, agent_id TEXT NOT NULL, timestamp INTEGER NOT NULL, content TEXT NOT NULL, FOREIGN KEY (agent_id) REFERENCES agents(id) );

CREATE TABLE credentials ( key TEXT PRIMARY KEY, value TEXT NOT NULL, -- Reference to safeStorage, not plaintext updated_at INTEGER NOT NULL );

## Indexes

```sql
CREATE INDEX idx_runs_created_at ON runs(created_at DESC);
CREATE INDEX idx_agents_round_id ON agents(round_id);
CREATE INDEX idx_logs_agent_id ON output_logs(agent_id);

better-sqlite3 Usage

import Database from 'better-sqlite3'

const db = new Database(path.join(os.homedir(), '.multishot', 'database.sqlite'))

// Synchronous API (no async/await needed)
const run = db.prepare('SELECT * FROM runs WHERE id = ?').get(runId)
const agents = db.prepare('SELECT * FROM agents WHERE round_id = ?').all(roundId)

db.prepare('INSERT INTO runs (id, name, created_at, status) VALUES (?, ?, ?, ?)')
  .run(id, name, Date.now(), 'active')

File Storage

Winner files extracted to: ~/.multishot/runs/[run-uuid]/round-N-winner/

Logs archived to: ~/.multishot/runs/[run-uuid]/logs/agent-N.log