shira-hermes/scripts/migrations/001_topics.sql

-- Migration 001: introduce multi-topic support for the KB.
--
-- Phase 1 of Task Master #12 (espocrm-extensions/KnowledgeBase). Adds a
-- kb_topic table, seeds the single existing domain (ביטוח לאומי, id=1),
-- and pins every existing kb_source to it. After this runs, search/ask
-- endpoints can scope by topic_id; without scope, behavior is unchanged
-- because there is exactly one topic.
--
-- Idempotent — safe to run twice. Wrapped in a single transaction so a
-- partial failure leaves no half-applied state.
--
-- Run as the postgres superuser (kb_source is owned by postgres, so the
-- app role shira_kb cannot ALTER it):
--   docker exec -i <pg-container> psql -U postgres -d insurance_kb \
--     -f /tmp/001_topics.sql
-- The final GRANT block below mirrors shira_kb's existing privileges on
-- kb_source onto kb_topic so the app can read/write topics through its
-- normal DSN.

BEGIN;

-- 1) The topic table itself.
CREATE TABLE IF NOT EXISTS kb_topic (
  id                       SERIAL PRIMARY KEY,
  slug                     TEXT NOT NULL UNIQUE,
  name                     TEXT NOT NULL,
  description              TEXT,
  system_prompt_addendum   TEXT,
  is_active                BOOLEAN NOT NULL DEFAULT TRUE,
  created_at               TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at               TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 2) Seed the existing domain. The addendum is copied VERBATIM from the
--    hardcoded string in api/routes/kb_public.py::_build_ask_runner_context
--    — changing tone here would change Shira's answers.
--
--    The original Phase-0 hardcoded prompt referred to search_insurance_kb;
--    Phase 1 renames the tool to search_legal_kb without keeping an alias
--    (Anthropic prompt-cache lasts ~5min, no production conversations span
--    the migration). The addendum mirrors that rename.
INSERT INTO kb_topic (id, slug, name, description, system_prompt_addendum)
VALUES (
  1,
  'national-insurance',
  'ביטוח לאומי',
  $desc$חוק הביטוח הלאומי, התקנות, ספרי המבחנים והליקויים, וחוזרי המוסד.$desc$,
  $addendum$Context: The user is asking from the Knowledge Base UI of the Israeli National Insurance firm. Every question should be answered from the KB of חוק/תקנות/חוזרי הביטוח הלאומי. Before answering, call search_legal_kb to retrieve the relevant sections, and cite them explicitly with section_ref (ס' N, תקנה N, or חוזר N/YYYY). Never answer from generic legal training if the KB has a matching section — if no KB section matches, say so explicitly.$addendum$
)
ON CONFLICT (id) DO NOTHING;

-- Bump the sequence past the seeded id so the next INSERT auto-assigns id=2+.
SELECT setval(
  pg_get_serial_sequence('kb_topic', 'id'),
  GREATEST((SELECT MAX(id) FROM kb_topic), 1)
);

-- 3) Wire kb_source to kb_topic. Nullable first → backfill → NOT NULL,
--    so a half-run migration never leaves the column non-nullable while
--    still empty (which would block every subsequent INSERT).
ALTER TABLE kb_source
  ADD COLUMN IF NOT EXISTS topic_id INTEGER REFERENCES kb_topic(id);

UPDATE kb_source
   SET topic_id = 1
 WHERE topic_id IS NULL;

ALTER TABLE kb_source
  ALTER COLUMN topic_id SET NOT NULL;

-- 4) Index for topic-scoped queries (search/sources/ask all filter on it).
CREATE INDEX IF NOT EXISTS kb_source_topic_id_idx
  ON kb_source (topic_id);

-- Sanity check: this SELECT must return zero rows or the migration is wrong.
DO $check$
BEGIN
  IF (SELECT COUNT(*) FROM kb_source WHERE topic_id IS NULL) > 0 THEN
    RAISE EXCEPTION 'topic_id backfill incomplete';
  END IF;
END
$check$;

-- 5) Grant the app role the same privilege set it has on kb_source.
GRANT SELECT, INSERT, UPDATE, DELETE ON kb_topic TO shira_kb;
GRANT USAGE, SELECT, UPDATE ON SEQUENCE kb_topic_id_seq TO shira_kb;

COMMIT;