spb/chat-spboucher-ai
Public
1-- Chat SPBoucher AI — initial schema (PostgreSQL 17 + pgvector)2CREATE EXTENSION IF NOT EXISTS vector;3CREATE EXTENSION IF NOT EXISTS pgcrypto;45-- ---------------------------------------------------------------- owner & auth6CREATE TABLE IF NOT EXISTS owner (7 id smallint PRIMARY KEY DEFAULT 1 CHECK (id = 1),8 username text NOT NULL,9 password_hash text NOT NULL, -- argon2id10 totp_secret text, -- optional 2FA (encrypted at rest by app key)11 created_at timestamptz NOT NULL DEFAULT now(),12 updated_at timestamptz NOT NULL DEFAULT now()13);1415CREATE TABLE IF NOT EXISTS sessions (16 id text PRIMARY KEY, -- sha256(token)17 created_at timestamptz NOT NULL DEFAULT now(),18 expires_at timestamptz NOT NULL,19 last_seen timestamptz NOT NULL DEFAULT now(),20 user_agent text,21 ip text,22 revoked_at timestamptz23);24CREATE INDEX IF NOT EXISTS sessions_expires_idx ON sessions (expires_at);2526CREATE TABLE IF NOT EXISTS login_attempts (27 id bigserial PRIMARY KEY,28 ip text NOT NULL,29 at timestamptz NOT NULL DEFAULT now(),30 success boolean NOT NULL31);32CREATE INDEX IF NOT EXISTS login_attempts_ip_at_idx ON login_attempts (ip, at DESC);3334CREATE TABLE IF NOT EXISTS webauthn_credentials (35 id text PRIMARY KEY,36 public_key bytea NOT NULL,37 counter bigint NOT NULL DEFAULT 0,38 transports text[],39 label text,40 created_at timestamptz NOT NULL DEFAULT now()41);4243-- ---------------------------------------------------------------- settings (key/value)44CREATE TABLE IF NOT EXISTS settings (45 key text PRIMARY KEY,46 value jsonb NOT NULL,47 updated_at timestamptz NOT NULL DEFAULT now()48);4950-- ---------------------------------------------------------------- model registry51CREATE TABLE IF NOT EXISTS models (52 id text PRIMARY KEY,53 provider text NOT NULL CHECK (provider IN ('openai','anthropic','local')),54 display_name text NOT NULL,55 kind text NOT NULL DEFAULT 'chat', -- chat | chat-pro | search | image | tts | stt | embedding | moderation | realtime | video | legacy-completion | deep-research | audio-chat56 api text, -- responses | chat | messages57 status text NOT NULL DEFAULT 'untested', -- verified | unavailable | unsupported | untested58 available boolean NOT NULL DEFAULT false,59 tested boolean NOT NULL DEFAULT false,60 unavailable_reason text,61 capabilities jsonb NOT NULL DEFAULT '{}'::jsonb,62 context_window integer,63 max_output_tokens integer,64 pricing jsonb,65 benchmark jsonb,66 hidden boolean NOT NULL DEFAULT false,67 favorite boolean NOT NULL DEFAULT false,68 sort_order integer NOT NULL DEFAULT 0,69 last_verified_at timestamptz,70 first_seen_at timestamptz NOT NULL DEFAULT now(),71 updated_at timestamptz NOT NULL DEFAULT now()72);7374-- ---------------------------------------------------------------- projects75CREATE TABLE IF NOT EXISTS projects (76 id text PRIMARY KEY,77 name text NOT NULL,78 description text,79 instructions text,80 default_model text,81 color text,82 icon text,83 tool_permissions jsonb NOT NULL DEFAULT '{}'::jsonb,84 notes text,85 sort_order integer NOT NULL DEFAULT 0,86 archived_at timestamptz,87 created_at timestamptz NOT NULL DEFAULT now(),88 updated_at timestamptz NOT NULL DEFAULT now()89);9091-- ---------------------------------------------------------------- conversations & messages92CREATE TABLE IF NOT EXISTS conversations (93 id text PRIMARY KEY,94 project_id text REFERENCES projects(id) ON DELETE SET NULL,95 title text NOT NULL DEFAULT 'Nouvelle conversation',96 model_id text,97 settings jsonb NOT NULL DEFAULT '{}'::jsonb, -- effort, routing mode, tools enabled, system override…98 pinned boolean NOT NULL DEFAULT false,99 archived_at timestamptz,100 parent_id text REFERENCES conversations(id) ON DELETE SET NULL, -- branch origin101 branch_message_id text,102 mode text NOT NULL DEFAULT 'chat', -- chat | compare | research103 message_count integer NOT NULL DEFAULT 0,104 total_cost_usd numeric(12,6) NOT NULL DEFAULT 0,105 last_message_at timestamptz,106 created_at timestamptz NOT NULL DEFAULT now(),107 updated_at timestamptz NOT NULL DEFAULT now(),108 tsv tsvector109);110CREATE INDEX IF NOT EXISTS conversations_project_idx ON conversations (project_id);111CREATE INDEX IF NOT EXISTS conversations_updated_idx ON conversations (updated_at DESC);112CREATE INDEX IF NOT EXISTS conversations_tsv_idx ON conversations USING gin (tsv);113114CREATE TABLE IF NOT EXISTS messages (115 id text PRIMARY KEY,116 conversation_id text NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,117 parent_id text REFERENCES messages(id) ON DELETE SET NULL, -- message tree (branching / regenerate)118 role text NOT NULL CHECK (role IN ('user','assistant','system','tool')),119 parts jsonb NOT NULL DEFAULT '[]'::jsonb, -- [{type:text|image|file|tool_call|tool_result|reasoning|artifact|citation,...}]120 text text NOT NULL DEFAULT '', -- plain text (search + context)121 model_id text,122 provider text,123 settings jsonb, -- effort/routing used for THIS response124 routing jsonb, -- {mode, requested, selected, reason}125 usage jsonb, -- {input, output, cached, reasoning}126 cost_usd numeric(12,6),127 latency_ms integer,128 ttft_ms integer,129 status text NOT NULL DEFAULT 'complete', -- streaming | complete | error | cancelled130 error text,131 active boolean NOT NULL DEFAULT true, -- false for superseded siblings (regenerate/edit)132 created_at timestamptz NOT NULL DEFAULT now(),133 tsv tsvector GENERATED ALWAYS AS (to_tsvector('simple', coalesce(text,''))) STORED134);135CREATE INDEX IF NOT EXISTS messages_conv_idx ON messages (conversation_id, created_at);136CREATE INDEX IF NOT EXISTS messages_parent_idx ON messages (parent_id);137CREATE INDEX IF NOT EXISTS messages_tsv_idx ON messages USING gin (tsv);138139CREATE TABLE IF NOT EXISTS message_embeddings (140 message_id text PRIMARY KEY REFERENCES messages(id) ON DELETE CASCADE,141 embedding vector(1536) NOT NULL,142 model text NOT NULL DEFAULT 'text-embedding-3-small',143 created_at timestamptz NOT NULL DEFAULT now()144);145CREATE INDEX IF NOT EXISTS message_embeddings_idx ON message_embeddings USING hnsw (embedding vector_cosine_ops);146147-- ---------------------------------------------------------------- attachments & document chunks148CREATE TABLE IF NOT EXISTS attachments (149 id text PRIMARY KEY,150 conversation_id text REFERENCES conversations(id) ON DELETE SET NULL,151 project_id text REFERENCES projects(id) ON DELETE SET NULL,152 message_id text REFERENCES messages(id) ON DELETE SET NULL,153 kind text NOT NULL DEFAULT 'upload', -- upload | generated-image | audio | export154 filename text NOT NULL,155 mime text NOT NULL,156 size_bytes bigint NOT NULL,157 path text NOT NULL, -- relative to DATA_DIR/uploads158 sha256 text,159 extracted_text text,160 page_count integer,161 meta jsonb NOT NULL DEFAULT '{}'::jsonb,162 created_at timestamptz NOT NULL DEFAULT now()163);164CREATE INDEX IF NOT EXISTS attachments_conv_idx ON attachments (conversation_id);165CREATE INDEX IF NOT EXISTS attachments_project_idx ON attachments (project_id);166167CREATE TABLE IF NOT EXISTS document_chunks (168 id bigserial PRIMARY KEY,169 attachment_id text NOT NULL REFERENCES attachments(id) ON DELETE CASCADE,170 idx integer NOT NULL,171 page integer,172 content text NOT NULL,173 embedding vector(1536),174 tsv tsvector GENERATED ALWAYS AS (to_tsvector('simple', content)) STORED175);176CREATE INDEX IF NOT EXISTS document_chunks_att_idx ON document_chunks (attachment_id, idx);177CREATE INDEX IF NOT EXISTS document_chunks_tsv_idx ON document_chunks USING gin (tsv);178CREATE INDEX IF NOT EXISTS document_chunks_emb_idx ON document_chunks USING hnsw (embedding vector_cosine_ops);179180-- ---------------------------------------------------------------- memory181CREATE TABLE IF NOT EXISTS memories (182 id text PRIMARY KEY,183 category text NOT NULL DEFAULT 'context', -- preference | writing | research | project | infrastructure | objective | concept | context184 content text NOT NULL,185 source text NOT NULL DEFAULT 'manual', -- manual | extracted | imported186 source_conversation_id text REFERENCES conversations(id) ON DELETE SET NULL,187 source_message_id text REFERENCES messages(id) ON DELETE SET NULL,188 project_id text REFERENCES projects(id) ON DELETE CASCADE, -- null = global189 status text NOT NULL DEFAULT 'active', -- candidate | active | disabled190 confidence real,191 embedding vector(1536),192 use_count integer NOT NULL DEFAULT 0,193 last_used_at timestamptz,194 created_at timestamptz NOT NULL DEFAULT now(),195 updated_at timestamptz NOT NULL DEFAULT now(),196 tsv tsvector GENERATED ALWAYS AS (to_tsvector('simple', content)) STORED197);198CREATE INDEX IF NOT EXISTS memories_status_idx ON memories (status);199CREATE INDEX IF NOT EXISTS memories_emb_idx ON memories USING hnsw (embedding vector_cosine_ops);200CREATE INDEX IF NOT EXISTS memories_tsv_idx ON memories USING gin (tsv);201202-- ---------------------------------------------------------------- artifacts203CREATE TABLE IF NOT EXISTS artifacts (204 id text PRIMARY KEY,205 conversation_id text REFERENCES conversations(id) ON DELETE CASCADE,206 message_id text REFERENCES messages(id) ON DELETE SET NULL,207 project_id text REFERENCES projects(id) ON DELETE SET NULL,208 root_id text, -- first version id209 version integer NOT NULL DEFAULT 1,210 type text NOT NULL, -- html | react | typescript | python | markdown | table | chart | svg | mermaid | math | code211 language text,212 title text NOT NULL,213 content text NOT NULL,214 saved boolean NOT NULL DEFAULT false,215 created_at timestamptz NOT NULL DEFAULT now()216);217CREATE INDEX IF NOT EXISTS artifacts_conv_idx ON artifacts (conversation_id);218CREATE INDEX IF NOT EXISTS artifacts_root_idx ON artifacts (root_id, version);219220-- ---------------------------------------------------------------- usage & spending221CREATE TABLE IF NOT EXISTS usage_events (222 id bigserial PRIMARY KEY,223 at timestamptz NOT NULL DEFAULT now(),224 provider text NOT NULL,225 model_id text NOT NULL,226 kind text NOT NULL DEFAULT 'chat', -- chat | compare | research | title | memory | embedding | image | tts | stt | judge | tool227 conversation_id text,228 project_id text,229 message_id text,230 input_tokens integer NOT NULL DEFAULT 0,231 output_tokens integer NOT NULL DEFAULT 0,232 cached_tokens integer NOT NULL DEFAULT 0,233 reasoning_tokens integer NOT NULL DEFAULT 0,234 cost_estimated numeric(12,6),235 cost_confirmed numeric(12,6), -- filled when a provider billing export is imported236 latency_ms integer,237 ttft_ms integer,238 status text NOT NULL DEFAULT 'ok', -- ok | error | cancelled239 error text,240 meta jsonb241);242CREATE INDEX IF NOT EXISTS usage_at_idx ON usage_events (at DESC);243CREATE INDEX IF NOT EXISTS usage_model_idx ON usage_events (model_id, at DESC);244CREATE INDEX IF NOT EXISTS usage_project_idx ON usage_events (project_id, at DESC);245246-- ---------------------------------------------------------------- comparisons & research247CREATE TABLE IF NOT EXISTS comparisons (248 id text PRIMARY KEY,249 conversation_id text REFERENCES conversations(id) ON DELETE SET NULL,250 project_id text REFERENCES projects(id) ON DELETE SET NULL,251 prompt text NOT NULL,252 model_ids text[] NOT NULL,253 results jsonb NOT NULL DEFAULT '[]'::jsonb, -- [{modelId, text, usage, cost, latency, ttft, error}]254 judge jsonb, -- {modelId, verdict, text}255 preferred_model text,256 created_at timestamptz NOT NULL DEFAULT now()257);258259CREATE TABLE IF NOT EXISTS research_runs (260 id text PRIMARY KEY,261 conversation_id text REFERENCES conversations(id) ON DELETE SET NULL,262 project_id text REFERENCES projects(id) ON DELETE SET NULL,263 question text NOT NULL,264 model_id text NOT NULL,265 status text NOT NULL DEFAULT 'planning', -- planning | searching | reading | synthesizing | complete | error | cancelled266 plan jsonb,267 steps jsonb NOT NULL DEFAULT '[]'::jsonb,268 sources jsonb NOT NULL DEFAULT '[]'::jsonb,269 report_md text,270 cost_usd numeric(12,6) NOT NULL DEFAULT 0,271 error text,272 created_at timestamptz NOT NULL DEFAULT now(),273 completed_at timestamptz274);275276-- ---------------------------------------------------------------- tool executions (audit log)277CREATE TABLE IF NOT EXISTS tool_executions (278 id text PRIMARY KEY,279 conversation_id text,280 message_id text,281 tool text NOT NULL,282 input jsonb,283 output jsonb,284 status text NOT NULL, -- pending_approval | approved | denied | ok | error285 duration_ms integer,286 created_at timestamptz NOT NULL DEFAULT now()287);288CREATE INDEX IF NOT EXISTS tool_exec_conv_idx ON tool_executions (conversation_id, created_at DESC);289290-- ---------------------------------------------------------------- triggers291CREATE OR REPLACE FUNCTION conversations_tsv_update() RETURNS trigger AS $$292BEGIN NEW.tsv := to_tsvector('simple', coalesce(NEW.title,'')); RETURN NEW; END $$ LANGUAGE plpgsql;293DROP TRIGGER IF EXISTS conversations_tsv_trg ON conversations;294CREATE TRIGGER conversations_tsv_trg BEFORE INSERT OR UPDATE OF title ON conversations FOR EACH ROW EXECUTE FUNCTION conversations_tsv_update();295296CREATE OR REPLACE FUNCTION touch_updated_at() RETURNS trigger AS $$297BEGIN NEW.updated_at := now(); RETURN NEW; END $$ LANGUAGE plpgsql;298DROP TRIGGER IF EXISTS conversations_touch ON conversations;299CREATE TRIGGER conversations_touch BEFORE UPDATE ON conversations FOR EACH ROW EXECUTE FUNCTION touch_updated_at();300DROP TRIGGER IF EXISTS projects_touch ON projects;301CREATE TRIGGER projects_touch BEFORE UPDATE ON projects FOR EACH ROW EXECUTE FUNCTION touch_updated_at();302DROP TRIGGER IF EXISTS memories_touch ON memories;303CREATE TRIGGER memories_touch BEFORE UPDATE ON memories FOR EACH ROW EXECUTE FUNCTION touch_updated_at();304305-- default settings306INSERT INTO settings (key, value) VALUES307 ('memory', '{"enabled": true, "autoExtract": true, "requireApproval": false}'),308 ('spending', '{"dailyLimitUsd": 10, "monthlyLimitUsd": 150, "alertAtPercent": 80}'),309 ('routing', '{"defaultMode": "manual"}'),310 ('appearance', '{"theme": "dark"}')311ON CONFLICT (key) DO NOTHING;312