shira-hermes/scripts/migrations/006_labels.sql

-- Migration 006: label-based sub-topic grouping (kb_label many-to-many kb_source).
--
-- Task Master #20 (v0.8.0); subsumes Task #19 (the original "subject vs
-- tags" design discussion). Many legal documents touch multiple subjects
-- ("ילד נכה / אוטיזם" AND "ילד נכה / תלות בזולת"), which makes a M:N
-- relationship the right shape — a single subject column would force one
-- bucket per source and lose information.
--
-- Schema choices:
--   • Flat table (no parent_id). Slash-separated names like
--     "ילד נכה / אוטיזם" are a UI display convention only — slug is
--     the unique key.
--   • Slug constrained to ASCII (lowercase letters, digits, hyphens).
--     Hebrew name → ASCII slug via transliteration in Python (unidecode).
--   • topic_id is nullable so a label can be cross-topic (rare but real:
--     "פסיקה כללית" might apply across multiple topics).
--   • usage_count denormalized so the autocomplete dropdown doesn't
--     COUNT(*) on every keystroke. Maintained by application code on
--     INSERT/DELETE of kb_source_label (no trigger — explicit is better).
--   • ON DELETE CASCADE on both FKs in kb_source_label: deleting a
--     source nukes its assignments; deleting a label nukes all
--     assignments. We never want orphans.
--
-- Idempotent — wrapped in a single transaction.
--
-- Run as postgres superuser:
--   docker exec -i <pg-container> psql -U postgres -d insurance_kb \
--     -f /tmp/006_labels.sql

BEGIN;

CREATE TABLE IF NOT EXISTS kb_label (
  id          SERIAL PRIMARY KEY,
  slug        TEXT NOT NULL UNIQUE
              CHECK (slug ~ '^[a-z0-9][a-z0-9-]*$' AND length(slug) BETWEEN 1 AND 200),
  name        TEXT NOT NULL,
  topic_id    INTEGER NULL REFERENCES kb_topic(id) ON DELETE SET NULL,
  -- Free-form userName from EspoCRM's X-User-Name header. Audit only.
  created_by  TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  -- Maintained by app code on every kb_source_label INSERT/DELETE so the
  -- autocomplete can ORDER BY usage_count DESC without a join.
  usage_count INTEGER NOT NULL DEFAULT 0
);

CREATE INDEX IF NOT EXISTS kb_label_topic_idx ON kb_label (topic_id);
CREATE INDEX IF NOT EXISTS kb_label_usage_idx ON kb_label (usage_count DESC);

CREATE TABLE IF NOT EXISTS kb_source_label (
  source_id INTEGER NOT NULL REFERENCES kb_source(id) ON DELETE CASCADE,
  label_id  INTEGER NOT NULL REFERENCES kb_label(id)  ON DELETE CASCADE,
  PRIMARY KEY (source_id, label_id)
);
CREATE INDEX IF NOT EXISTS kb_source_label_label_idx ON kb_source_label (label_id);

GRANT SELECT, INSERT, UPDATE, DELETE ON kb_label        TO shira_kb;
GRANT SELECT, INSERT, UPDATE, DELETE ON kb_source_label TO shira_kb;
GRANT USAGE, SELECT, UPDATE ON SEQUENCE kb_label_id_seq TO shira_kb;

COMMIT;