PostgreSQL RAG 管道

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id bigint NOT NULL,
  source_uri text NOT NULL,
  source_version text NOT NULL,
  title text NOT NULL,
  access_scope text[] NOT NULL DEFAULT '{}',
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, source_uri, source_version)
);

CREATE TABLE document_chunks (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  document_id bigint NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  ordinal integer NOT NULL CHECK (ordinal >= 0),
  content text NOT NULL,
  token_count integer NOT NULL CHECK (token_count > 0),
  embedding vector(1536) NOT NULL,
  embedding_model text NOT NULL,
  search_vector tsvector GENERATED ALWAYS AS
    (to_tsvector('simple', content)) STORED,
  UNIQUE (document_id, ordinal)
);

SELECT
  c.id,
  c.document_id,
  c.ordinal,
  c.content,
  c.embedding <=> $1::vector AS distance
FROM document_chunks AS c
JOIN documents AS d ON d.id = c.document_id
WHERE d.tenant_id = $2
  AND d.access_scope && $3::text[]
ORDER BY c.embedding <=> $1::vector
LIMIT 40;