Jay Brown
103 lines
4.6 KiB
SQL
103 lines
4.6 KiB
SQL
-- Chatbot backend support, milestone M1: chatbot tables.
|
|
-- See plans/chatbot_plan_codex.v10.md §4 (Data Model) for the canonical
|
|
-- definition. Existing tables retain camelCase column names (per migrations
|
|
-- 1-130); new bot_* tables use snake_case to match migrations 127-130.
|
|
--
|
|
-- Tables created in dependency order:
|
|
-- 1. bot_sessions — owner-scoped chatbot conversations
|
|
-- 2. bot_session_documents — explicit per-session document scope
|
|
-- 3. bot_session_folders — explicit per-session folder scope
|
|
-- 4. bot_turns — append-only turn log per session
|
|
|
|
-- ---------- bot_sessions ----------
|
|
|
|
CREATE TABLE bot_sessions (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v7(),
|
|
client_id varchar(255) NOT NULL REFERENCES clients(clientId) ON DELETE CASCADE,
|
|
created_by varchar(255) NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT NOW(),
|
|
updated_at timestamptz NOT NULL DEFAULT NOW(),
|
|
title text NOT NULL DEFAULT '',
|
|
state jsonb NOT NULL DEFAULT '{}'::jsonb,
|
|
is_deleted boolean NOT NULL DEFAULT false,
|
|
CONSTRAINT pk_bot_sessions PRIMARY KEY (id)
|
|
);
|
|
|
|
CREATE INDEX idx_bot_sessions_client_user_active
|
|
ON bot_sessions (client_id, created_by, updated_at DESC)
|
|
WHERE is_deleted = false;
|
|
|
|
CREATE INDEX idx_bot_sessions_client_active
|
|
ON bot_sessions (client_id, updated_at DESC)
|
|
WHERE is_deleted = false;
|
|
|
|
-- ---------- bot_session_documents (scope) ----------
|
|
|
|
CREATE TABLE bot_session_documents (
|
|
session_id uuid NOT NULL REFERENCES bot_sessions(id) ON DELETE CASCADE,
|
|
document_id uuid NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
|
|
CONSTRAINT pk_bot_session_documents PRIMARY KEY (session_id, document_id)
|
|
);
|
|
|
|
CREATE INDEX idx_bsd_document ON bot_session_documents(document_id);
|
|
|
|
-- ---------- bot_session_folders (scope) ----------
|
|
|
|
CREATE TABLE bot_session_folders (
|
|
session_id uuid NOT NULL REFERENCES bot_sessions(id) ON DELETE CASCADE,
|
|
folder_id uuid NOT NULL REFERENCES folders(id) ON DELETE CASCADE,
|
|
CONSTRAINT pk_bot_session_folders PRIMARY KEY (session_id, folder_id)
|
|
);
|
|
|
|
CREATE INDEX idx_bsf_folder ON bot_session_folders(folder_id);
|
|
|
|
-- ---------- bot_turns ----------
|
|
--
|
|
-- bot_turns_status_fields_consistent groups session_deleted with errored
|
|
-- and abandoned: completion IS NULL AND error IS NOT NULL. This matches
|
|
-- plan §4 verbatim.
|
|
|
|
CREATE TABLE bot_turns (
|
|
session_id uuid NOT NULL REFERENCES bot_sessions(id) ON DELETE CASCADE,
|
|
ordinal int NOT NULL CHECK (ordinal > 0),
|
|
prompt text NOT NULL,
|
|
completion text,
|
|
status text NOT NULL DEFAULT 'in_flight',
|
|
attempt_id uuid NOT NULL,
|
|
attempt_started_at timestamptz NOT NULL DEFAULT NOW(),
|
|
created_at timestamptz NOT NULL DEFAULT NOW(),
|
|
latency_ms int,
|
|
tokens_in int,
|
|
tokens_out int,
|
|
error jsonb,
|
|
CONSTRAINT pk_bot_turns PRIMARY KEY (session_id, ordinal),
|
|
CONSTRAINT bot_turns_status_values CHECK (
|
|
status IN ('in_flight', 'completed', 'errored', 'abandoned', 'session_deleted')
|
|
),
|
|
-- bot_turns_status_fields_consistent enforces the (status, completion,
|
|
-- error) consistency matrix from plan §4. The trailing OR-clause
|
|
-- (`status NOT IN (...)`) is an evaluation-order guard, not a relaxation
|
|
-- of the rule: Postgres evaluates table-level CHECK constraints in
|
|
-- alphabetical order by constraint name, so without this guard a row
|
|
-- with an UNKNOWN status (e.g. 'frobnicated') trips this constraint
|
|
-- before bot_turns_status_values gets a chance to reject the unknown
|
|
-- enum value. With the guard, unknown statuses pass this consistency
|
|
-- check (every branch becomes irrelevant) and bot_turns_status_values
|
|
-- owns the rejection. For every documented status value the plan §4
|
|
-- literal still holds verbatim.
|
|
CONSTRAINT bot_turns_status_fields_consistent CHECK (
|
|
(status = 'in_flight' AND completion IS NULL AND error IS NULL)
|
|
OR (status = 'completed' AND completion IS NOT NULL AND error IS NULL)
|
|
OR (status IN ('errored', 'abandoned', 'session_deleted') AND completion IS NULL AND error IS NOT NULL)
|
|
OR status NOT IN ('in_flight', 'completed', 'errored', 'abandoned', 'session_deleted')
|
|
)
|
|
);
|
|
|
|
CREATE UNIQUE INDEX bot_turns_one_inflight_per_session
|
|
ON bot_turns (session_id)
|
|
WHERE status = 'in_flight';
|
|
|
|
CREATE INDEX idx_bot_turns_session_completed
|
|
ON bot_turns (session_id, ordinal)
|
|
WHERE status = 'completed';
|