← back to Dw Boardroom V2

src/db/schema.sql

59 lines

-- ═══════════════════════════════════════════════
-- DW Boardroom V2 — SQLite Schema
-- Ephemeral meeting data (persistent governance data via API on port 4020)
-- ═══════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS br2_meetings (
  id TEXT PRIMARY KEY,
  meeting_type TEXT NOT NULL CHECK(meeting_type IN ('strategic','initiative','ops','emergency')),
  phase TEXT NOT NULL DEFAULT 'caucus',
  status TEXT NOT NULL DEFAULT 'scheduled' CHECK(status IN ('scheduled','in_progress','completed','halted','crashed')),
  started_at TEXT,
  ended_at TEXT,
  summary TEXT,
  attendees TEXT DEFAULT '[]',        -- JSON array of agent IDs
  action_items TEXT DEFAULT '[]',     -- JSON array of action item objects
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS br2_messages (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  meeting_id TEXT NOT NULL REFERENCES br2_meetings(id),
  agent_id TEXT NOT NULL,
  agent_name TEXT NOT NULL,
  message TEXT NOT NULL,
  message_type TEXT NOT NULL DEFAULT 'dialogue' CHECK(message_type IN ('dialogue','phase','system','action','decision')),
  phase TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS br2_agenda (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  meeting_id TEXT NOT NULL REFERENCES br2_meetings(id),
  topic TEXT NOT NULL,
  recommendation TEXT,
  decision_type TEXT DEFAULT 'B' CHECK(decision_type IN ('A','B','C')),
  owner TEXT,
  position INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','discussed','resolved','deferred')),
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS br2_voice_config (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_br2_messages_meeting ON br2_messages(meeting_id);
CREATE INDEX IF NOT EXISTS idx_br2_messages_agent ON br2_messages(agent_id);
CREATE INDEX IF NOT EXISTS idx_br2_agenda_meeting ON br2_agenda(meeting_id);
CREATE INDEX IF NOT EXISTS idx_br2_meetings_status ON br2_meetings(status);

-- Default voice config
INSERT OR IGNORE INTO br2_voice_config (key, value) VALUES ('tts_enabled', 'false');
INSERT OR IGNORE INTO br2_voice_config (key, value) VALUES ('tts_speed', '1.0');
INSERT OR IGNORE INTO br2_voice_config (key, value) VALUES ('tts_volume', '0.8');
INSERT OR IGNORE INTO br2_voice_config (key, value) VALUES ('auto_speak', 'false');