spb/search-box Public

1-- Search-box.ai2-- Author: Simon-Pierre Boucher3-- Contact: contact@spboucher.ai4-- File: packages/db/migrations/001_init.sql5-- Description: Initial database schema for research sessions, events and research state.67CREATE TABLE IF NOT EXISTS research_sessions (8  id            text PRIMARY KEY,9  question      text NOT NULL,10  status        text NOT NULL DEFAULT 'pending',11  answer        text,12  error         text,13  objectives    jsonb NOT NULL DEFAULT '[]',14  budgets       jsonb NOT NULL DEFAULT '{}',15  meta          jsonb NOT NULL DEFAULT '{}',16  created_at    timestamptz NOT NULL DEFAULT now(),17  updated_at    timestamptz NOT NULL DEFAULT now()18);1920CREATE TABLE IF NOT EXISTS research_events (21  seq           bigserial PRIMARY KEY,22  id            text NOT NULL UNIQUE,23  session_id    text NOT NULL REFERENCES research_sessions(id) ON DELETE CASCADE,24  type          text NOT NULL,25  payload       jsonb NOT NULL,26  created_at    timestamptz NOT NULL DEFAULT now()27);28CREATE INDEX IF NOT EXISTS research_events_session_seq ON research_events (session_id, seq);2930CREATE TABLE IF NOT EXISTS sources (31  id             text PRIMARY KEY,32  session_id     text NOT NULL REFERENCES research_sessions(id) ON DELETE CASCADE,33  url            text NOT NULL,34  title          text,35  domain         text NOT NULL,36  status         text NOT NULL DEFAULT 'found',37  citation_index integer,38  content        text,39  metadata       jsonb NOT NULL DEFAULT '{}',40  created_at     timestamptz NOT NULL DEFAULT now(),41  UNIQUE (session_id, url)42);4344CREATE TABLE IF NOT EXISTS evidence (45  id            text PRIMARY KEY,46  session_id    text NOT NULL REFERENCES research_sessions(id) ON DELETE CASCADE,47  source_id     text NOT NULL REFERENCES sources(id) ON DELETE CASCADE,48  claim_id      text,49  quote         text NOT NULL,50  note          text,51  stance        text NOT NULL DEFAULT 'context',52  created_at    timestamptz NOT NULL DEFAULT now()53);54CREATE INDEX IF NOT EXISTS evidence_session ON evidence (session_id);5556CREATE TABLE IF NOT EXISTS claims (57  id            text PRIMARY KEY,58  session_id    text NOT NULL REFERENCES research_sessions(id) ON DELETE CASCADE,59  text          text NOT NULL,60  status        text NOT NULL DEFAULT 'exploring',61  confidence    real NOT NULL DEFAULT 0.5,62  public_reason text,63  created_at    timestamptz NOT NULL DEFAULT now(),64  updated_at    timestamptz NOT NULL DEFAULT now()65);66CREATE INDEX IF NOT EXISTS claims_session ON claims (session_id);6768CREATE TABLE IF NOT EXISTS contradictions (69  id            text PRIMARY KEY,70  session_id    text NOT NULL REFERENCES research_sessions(id) ON DELETE CASCADE,71  claim_id      text NOT NULL REFERENCES claims(id) ON DELETE CASCADE,72  description   text NOT NULL,73  evidence_ids  jsonb NOT NULL DEFAULT '[]',74  resolution    text,75  created_at    timestamptz NOT NULL DEFAULT now()76);77CREATE INDEX IF NOT EXISTS contradictions_session ON contradictions (session_id);78