Schema and Lineage

CREATE TABLE conversations (
  id            text PRIMARY KEY,             -- conv_...
  tenant_id     text NOT NULL,
  user_id       text NOT NULL,
  title         text,
  summary       text,                          -- running summary for long chats
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now(),
  deleted_at    timestamptz                    -- soft delete, hard delete by retention job
);

CREATE TABLE messages (
  id               text PRIMARY KEY,           -- msg_...
  tenant_id        text NOT NULL,
  conversation_id  text NOT NULL REFERENCES conversations(id),
  role             text NOT NULL CHECK (role IN ('user', 'assistant')),
  parent_id        text REFERENCES messages(id), -- supports edits and regenerations as branches
  client_id        uuid,                        -- idempotency from the browser
  status           text NOT NULL,               -- pending, done, stopped, failed, blocked
  content          jsonb NOT NULL,              -- typed parts: text, citations, tool cards
  created_at       timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, client_id)
);

-- One row per assistant message: how it was generated
CREATE TABLE generations (
  message_id       text PRIMARY KEY REFERENCES messages(id),
  tenant_id        text NOT NULL,
  model            text NOT NULL,              -- concrete model version actually used
  model_route      text NOT NULL,              -- logical name, e.g. answer
  fallback_used    boolean NOT NULL DEFAULT false,
  prompt_template  text NOT NULL,              -- e.g. answer
  prompt_version   text NOT NULL,              -- e.g. v14
  rewritten_query  text,
  input_tokens     int NOT NULL,
  output_tokens    int NOT NULL,
  cached_tokens    int NOT NULL DEFAULT 0,
  cost_usd         numeric(12,6) NOT NULL,
  ttft_ms          int,
  total_ms         int,
  trace_id         text NOT NULL,
  guardrail_flags  jsonb NOT NULL DEFAULT '[]'
);

-- Which chunks were given to the model, and which were cited
CREATE TABLE message_sources (
  message_id       text NOT NULL REFERENCES messages(id),
  tenant_id        text NOT NULL,
  n                int NOT NULL,               -- the citation number in the prompt
  chunk_id         text NOT NULL,
  document_id      text NOT NULL,
  document_version text NOT NULL,              -- content hash or source version at answer time
  rerank_score     real,
  cited            boolean NOT NULL DEFAULT false,
  PRIMARY KEY (message_id, n)
);

CREATE TABLE tool_calls (
  id            text PRIMARY KEY,
  tenant_id     text NOT NULL,
  message_id    text NOT NULL REFERENCES messages(id),
  tool          text NOT NULL,
  arguments     jsonb NOT NULL,
  status        text NOT NULL,                 -- proposed, confirmed, rejected, succeeded, failed
  confirmed_by  text,
  result        jsonb,
  created_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE feedback (
  id          text PRIMARY KEY,
  tenant_id   text NOT NULL,
  message_id  text NOT NULL REFERENCES messages(id),
  user_id     text NOT NULL,
  rating      smallint NOT NULL CHECK (rating IN (-1, 1)),
  reasons     text[] NOT NULL DEFAULT '{}',
  comment     text,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX ON conversations (tenant_id, user_id, updated_at DESC) WHERE deleted_at IS NULL;
CREATE INDEX ON messages (conversation_id, created_at);
CREATE INDEX ON message_sources (tenant_id, document_id);  -- find answers that used a document
CREATE INDEX ON feedback (tenant_id, created_at) WHERE rating = -1;
CREATE INDEX ON generations (tenant_id, prompt_version);

ALTER TABLE messages ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON messages
  USING (tenant_id = current_setting('app.tenant_id'));