`db/migrations/002_chat.sql`

34 lines
-- wallco.ai chat + design lineage

CREATE TABLE IF NOT EXISTS wallco_chat_sessions (
  id            BIGSERIAL PRIMARY KEY,
  session_uuid  TEXT UNIQUE NOT NULL,
  context       TEXT NOT NULL DEFAULT 'catalog',   -- 'catalog' | 'design:<id>'
  visitor_ip    INET,
  visitor_email TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS wallco_chat_messages (
  id            BIGSERIAL PRIMARY KEY,
  session_id    BIGINT NOT NULL REFERENCES wallco_chat_sessions(id),
  role          TEXT NOT NULL CHECK (role IN ('user','assistant','tool','system')),
  content       TEXT NOT NULL,
  tool_name     TEXT,
  tool_args     JSONB,
  tool_result   JSONB,
  generated_design_id BIGINT,           -- if a generate_design tool fired, links here
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_wallco_chat_session_uuid ON wallco_chat_sessions(session_uuid);
CREATE INDEX IF NOT EXISTS idx_wallco_chat_session_last ON wallco_chat_sessions(last_at DESC);
CREATE INDEX IF NOT EXISTS idx_wallco_chat_msg_session  ON wallco_chat_messages(session_id, created_at);

-- Design lineage — when a design is generated via "vary this one", we link parent → child
ALTER TABLE spoon_all_designs ADD COLUMN IF NOT EXISTS parent_design_id BIGINT REFERENCES spoon_all_designs(id);
ALTER TABLE spoon_all_designs ADD COLUMN IF NOT EXISTS chat_session_id  BIGINT;
ALTER TABLE spoon_all_designs ADD COLUMN IF NOT EXISTS request_text     TEXT;  -- user's natural-language ask
CREATE INDEX IF NOT EXISTS idx_spoon_parent ON spoon_all_designs(parent_design_id);