`src/db/schema.sql`

118 lines
-- DW Boardroom Governance Schema
-- All tables prefixed with gov_ to isolate from existing dw_unified tables
-- Idempotent: safe to run multiple times

-- Governance Decision Log
CREATE TABLE IF NOT EXISTS gov_decision_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  topic TEXT NOT NULL,
  decision_type TEXT NOT NULL CHECK (decision_type IN ('A', 'B', 'C')),
  recommendation TEXT,
  human_decision TEXT CHECK (human_decision IN ('approve', 'reject', 'modify', 'defer')),
  owner TEXT NOT NULL,
  autonomy_level INTEGER NOT NULL DEFAULT 1 CHECK (autonomy_level BETWEEN 1 AND 5),
  deadline TIMESTAMPTZ,
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected', 'modified', 'deferred', 'auto_approved')),
  source TEXT,
  score INTEGER CHECK (score BETWEEN 0 AND 10),
  reasoning TEXT,
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  resolved_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS idx_gov_decision_status ON gov_decision_log(status);
CREATE INDEX IF NOT EXISTS idx_gov_decision_owner ON gov_decision_log(owner);
CREATE INDEX IF NOT EXISTS idx_gov_decision_created ON gov_decision_log(created_at DESC);

-- Delegation Contracts
CREATE TABLE IF NOT EXISTS gov_delegation_contract (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  decision_id UUID REFERENCES gov_decision_log(id) ON DELETE SET NULL,
  owner TEXT NOT NULL,
  delegate TEXT NOT NULL,
  autonomy_level INTEGER NOT NULL DEFAULT 1 CHECK (autonomy_level BETWEEN 1 AND 5),
  metric TEXT,
  checkpoint_date TIMESTAMPTZ,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'completed', 'revoked', 'expired')),
  routing_keywords TEXT[],
  hierarchy_path TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS idx_gov_deleg_owner ON gov_delegation_contract(owner);
CREATE INDEX IF NOT EXISTS idx_gov_deleg_delegate ON gov_delegation_contract(delegate);
CREATE INDEX IF NOT EXISTS idx_gov_deleg_status ON gov_delegation_contract(status);

-- Initiative Registry
CREATE TABLE IF NOT EXISTS gov_initiative_registry (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  description TEXT,
  driver_agent TEXT NOT NULL,
  kpi TEXT,
  risk_level TEXT NOT NULL DEFAULT 'low' CHECK (risk_level IN ('low', 'medium', 'high', 'critical')),
  capital_allocated NUMERIC(12,2) DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'proposed' CHECK (status IN ('proposed', 'active', 'paused', 'completed', 'cancelled')),
  progress INTEGER DEFAULT 0 CHECK (progress BETWEEN 0 AND 100),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_gov_init_status ON gov_initiative_registry(status);
CREATE INDEX IF NOT EXISTS idx_gov_init_driver ON gov_initiative_registry(driver_agent);

-- Meeting Log
CREATE TABLE IF NOT EXISTS gov_meetings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  meeting_type TEXT NOT NULL,
  phase TEXT DEFAULT 'scheduled',
  started_at TIMESTAMPTZ,
  ended_at TIMESTAMPTZ,
  status TEXT NOT NULL DEFAULT 'scheduled' CHECK (status IN ('scheduled', 'in_progress', 'completed', 'halted', 'crashed')),
  attendees JSONB DEFAULT '[]',
  summary TEXT,
  action_items JSONB DEFAULT '[]',
  created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_gov_meeting_status ON gov_meetings(status);
CREATE INDEX IF NOT EXISTS idx_gov_meeting_created ON gov_meetings(created_at DESC);

-- Meeting Messages (live feed)
CREATE TABLE IF NOT EXISTS gov_meeting_messages (
  id SERIAL PRIMARY KEY,
  meeting_id UUID REFERENCES gov_meetings(id) ON DELETE CASCADE,
  agent_id TEXT NOT NULL,
  agent_name TEXT NOT NULL,
  message TEXT NOT NULL,
  message_type TEXT DEFAULT 'update',
  created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_gov_msg_meeting ON gov_meeting_messages(meeting_id, created_at);

-- Escalation Log
CREATE TABLE IF NOT EXISTS gov_escalation_log (
  id SERIAL PRIMARY KEY,
  decision_id UUID REFERENCES gov_decision_log(id) ON DELETE SET NULL,
  escalation_type TEXT NOT NULL DEFAULT '12hr',
  hours_elapsed REAL,
  action_taken TEXT,
  escalated_to TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_gov_escalation_created ON gov_escalation_log(created_at DESC);

-- Governance Config (key-value store)
CREATE TABLE IF NOT EXISTS gov_config (
  key TEXT PRIMARY KEY,
  value JSONB NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Seed defaults
INSERT INTO gov_config (key, value) VALUES
  ('governance_mode', '"supervised"'),
  ('autonomy_default', '1'),
  ('escalation_threshold_hours', '12'),
  ('max_agenda_items', '5'),
  ('engines', '{"meetings": true, "decisions": true, "delegations": true, "escalations": true}')
ON CONFLICT (key) DO NOTHING;