Schema Completo

O schema completo está definido no SDD em docs/SDD-FUNIL-CHATBOT.md. Esta página documenta as tabelas mais importantes.

organizations

CREATE TABLE organizations (
  id            UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name          TEXT NOT NULL,
  slug          TEXT UNIQUE NOT NULL,
  email         TEXT NOT NULL,
  cnpj          TEXT,
  plan_id       UUID REFERENCES plans(id),
  white_label   BOOLEAN DEFAULT FALSE,
  custom_domain TEXT,
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

contacts

CREATE TABLE contacts (
  id               UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  workspace_id     UUID NOT NULL REFERENCES workspaces(id),
  phone            TEXT NOT NULL,                -- E.164: +5511999887766
  first_name       TEXT,
  last_name        TEXT,
  full_name        TEXT GENERATED ALWAYS AS (
                     TRIM(COALESCE(first_name,'') || ' ' || COALESCE(last_name,''))
                   ) STORED,
  opted_in         BOOLEAN DEFAULT FALSE,
  is_archived      BOOLEAN DEFAULT FALSE,
  is_anonymized    BOOLEAN DEFAULT FALSE,        -- LGPD
  last_seen_at     TIMESTAMPTZ,
  created_at       TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (workspace_id, phone)
);

knowledge_chunks (pgvector)

CREATE TABLE knowledge_chunks (
  id                UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  knowledge_base_id UUID NOT NULL REFERENCES knowledge_bases(id),
  content           TEXT NOT NULL,
  embedding         vector(1536),  -- OpenAI text-embedding-3-small
  chunk_index       INTEGER,
  created_at        TIMESTAMPTZ DEFAULT NOW()
);

-- Índice para busca semântica
CREATE INDEX idx_knowledge_chunks_embedding ON knowledge_chunks
  USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

Busca semântica (RAG)

SELECT content, 1 - (embedding <=> $1) AS similarity
FROM knowledge_chunks
WHERE knowledge_base_id = ANY($2)
  AND 1 - (embedding <=> $1) > 0.75
ORDER BY embedding <=> $1
LIMIT 5;