228 lines
11 KiB
PL/PgSQL
228 lines
11 KiB
PL/PgSQL
-- ============================================================================
|
||
-- Krow — the knowledge layer
|
||
--
|
||
-- Phase 2. Documents an agent may retrieve from, chunked and permissioned.
|
||
--
|
||
-- THE ONE RULE THIS SCHEMA EXISTS TO ENFORCE
|
||
--
|
||
-- I2: ACL filtering happens BEFORE scoring, never after. Post-filtering a
|
||
-- ranked result set leaks through counts, through ranking positions, and
|
||
-- through summaries — "your top result was suppressed" is itself information.
|
||
-- So permission has to be a WHERE clause that both the keyword query and the
|
||
-- vector query can carry, which means it has to live on the chunk row and be
|
||
-- indexable.
|
||
--
|
||
-- Hence `acl text[]` denormalised from the document onto every chunk. A join
|
||
-- would work and is the tidier schema; it is rejected because a join is a thing
|
||
-- a query can be written without, and the query that forgets it is a
|
||
-- cross-permission read that returns plausible results and raises nothing.
|
||
--
|
||
-- A chunk is visible to a caller who holds ANY of its tags: `acl && $grants`.
|
||
-- Tags are derived at ingest from the document's declared audience, never typed
|
||
-- by a user, and the vocabulary is closed — see internal/knowledge/acl.go.
|
||
--
|
||
-- §5 is explicit that chunks without ACL metadata are REJECTED at ingest. The
|
||
-- CHECK below makes that a schema property rather than a convention, because a
|
||
-- chunk with an empty acl is not "private", it is invisible to the `&&`
|
||
-- operator — and a document that silently indexed to nothing is a support
|
||
-- ticket nobody can diagnose.
|
||
--
|
||
-- WHY EMBEDDINGS ARE real[] AND NOT vector
|
||
--
|
||
-- pgvector is not installed on the development machine, and installing an
|
||
-- extension is an infrastructure decision rather than something a migration
|
||
-- should assume. real[] with an IMMUTABLE dot-product function gives exact
|
||
-- search with no extension and no ANN index.
|
||
--
|
||
-- The honest cost: this is a sequential scan over the caller's permitted chunks.
|
||
-- That is bounded by the ACL pre-filter, which is the point — a talent caller
|
||
-- scans their own handful of rows — but a tenant with a large shared corpus
|
||
-- will scan all of it, and there is no index that helps. The upgrade path is
|
||
-- pgvector: `ALTER TABLE knowledge_chunks ALTER COLUMN embedding TYPE vector(N)`
|
||
-- plus an HNSW index, with no change to the retrieval logic because the ACL
|
||
-- pre-filter and the RRF fusion are unaffected by how the ordering is computed.
|
||
--
|
||
-- Embeddings are stored UNIT-NORMALISED at ingest, so cosine similarity is a
|
||
-- plain dot product. Normalising at query time instead would mean recomputing a
|
||
-- magnitude per row per query, for a value that never changes.
|
||
--
|
||
-- WHY THE MODEL NAME IS ON THE ROW
|
||
--
|
||
-- Vectors from two different embedding models are not comparable — the numbers
|
||
-- have no shared meaning — so a corpus half-migrated to a new model silently
|
||
-- returns nonsense rather than failing. `embedding_model` lets retrieval refuse
|
||
-- a mismatch, and lets a reindex be told apart from a fresh ingest.
|
||
--
|
||
-- §5: a reindex is REQUIRED whenever ACL derivation logic changes. `acl_version`
|
||
-- records which derivation produced a row's tags, so "which documents predate
|
||
-- the change" is answerable rather than guessed at.
|
||
-- ============================================================================
|
||
|
||
SET search_path = public;
|
||
|
||
-- Cosine similarity for unit-normalised vectors, which is their dot product.
|
||
--
|
||
-- STRICT so a NULL embedding scores NULL rather than 0 — an unembedded chunk
|
||
-- must be absent from a dense ranking, not tied for last with everything else.
|
||
-- IMMUTABLE and PARALLEL SAFE so the planner may use it freely.
|
||
CREATE FUNCTION knowledge_dot(a real[], b real[])
|
||
RETURNS double precision
|
||
LANGUAGE sql
|
||
IMMUTABLE PARALLEL SAFE STRICT
|
||
AS $$
|
||
SELECT coalesce(sum(x::double precision * y::double precision), 0)
|
||
FROM unnest(a, b) AS t(x, y)
|
||
$$;
|
||
|
||
COMMENT ON FUNCTION knowledge_dot(real[], real[]) IS
|
||
'Dot product of two equal-length real arrays. Equals cosine similarity when both are unit-normalised, '
|
||
'which is how internal/knowledge stores them. Replace with pgvector''s <=> operator when the extension lands.';
|
||
|
||
|
||
-- ── Documents ───────────────────────────────────────────────────────────────
|
||
--
|
||
-- The thing a person ingested: a policy PDF, a handbook page, a job description.
|
||
-- Chunks belong to it, and citations point back through it.
|
||
|
||
CREATE TABLE knowledge_documents (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
org_id uuid NOT NULL REFERENCES organizations (id) ON DELETE CASCADE,
|
||
|
||
-- Which corpus this belongs to. An agent spec names sources
|
||
-- (`knowledge: - source: policy_docs`), and retrieval filters by them, so a
|
||
-- source is part of the permission story rather than a label: an agent that
|
||
-- may read policy documents does not thereby gain the shift database.
|
||
source text NOT NULL,
|
||
|
||
-- The id this document has in whatever system it came from, so a re-ingest
|
||
-- updates rather than duplicates. Unique per source per tenant.
|
||
external_id text NOT NULL,
|
||
|
||
title text NOT NULL DEFAULT '',
|
||
uri text NOT NULL DEFAULT '',
|
||
|
||
-- The audience, as derived grant tags. Denormalised onto every chunk below;
|
||
-- kept here too so a re-chunk does not have to re-derive it.
|
||
acl text[] NOT NULL,
|
||
|
||
-- Which ACL derivation produced those tags. §5 requires a reindex when the
|
||
-- derivation changes, and this is what makes "reindexed or not" a fact.
|
||
acl_version integer NOT NULL DEFAULT 1,
|
||
|
||
-- Free-form provenance: author, effective date, section. Read by the surface
|
||
-- when it renders a citation. Never interpolated into a prompt as
|
||
-- instructions — I7 applies to everything on this table.
|
||
metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
|
||
|
||
content_hash text NOT NULL DEFAULT '',
|
||
chunk_count integer NOT NULL DEFAULT 0,
|
||
|
||
ingested_at timestamptz NOT NULL DEFAULT now(),
|
||
created_date timestamptz NOT NULL DEFAULT now(),
|
||
updated_date timestamptz NOT NULL DEFAULT now(),
|
||
|
||
CONSTRAINT knowledge_documents_org_source_external_key
|
||
UNIQUE (org_id, source, external_id),
|
||
CONSTRAINT knowledge_documents_source_not_blank
|
||
CHECK (length(btrim(source)) > 0),
|
||
CONSTRAINT knowledge_documents_external_id_not_blank
|
||
CHECK (length(btrim(external_id)) > 0),
|
||
-- §5. A document with no audience is not private, it is unreachable.
|
||
CONSTRAINT knowledge_documents_acl_not_empty
|
||
CHECK (array_length(acl, 1) >= 1),
|
||
CONSTRAINT knowledge_documents_metadata_is_object
|
||
CHECK (jsonb_typeof(metadata) = 'object')
|
||
);
|
||
|
||
CREATE INDEX knowledge_documents_org_source_idx
|
||
ON knowledge_documents (org_id, source, ingested_at DESC);
|
||
|
||
-- "Which documents predate the current ACL derivation" — the reindex query.
|
||
CREATE INDEX knowledge_documents_acl_version_idx
|
||
ON knowledge_documents (org_id, acl_version);
|
||
|
||
|
||
-- ── Chunks ──────────────────────────────────────────────────────────────────
|
||
--
|
||
-- What retrieval actually ranks. Everything a query needs is on this row, so
|
||
-- the hot path never joins: tenancy, source, permission, both indexes.
|
||
|
||
CREATE TABLE knowledge_chunks (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
document_id uuid NOT NULL REFERENCES knowledge_documents (id) ON DELETE CASCADE,
|
||
|
||
-- Repeated from the document rather than joined. I5, and the same reasoning
|
||
-- as `acl`: the predicate must be impossible to omit.
|
||
org_id uuid NOT NULL REFERENCES organizations (id) ON DELETE CASCADE,
|
||
source text NOT NULL,
|
||
acl text[] NOT NULL,
|
||
|
||
-- Position within the document, so neighbouring chunks can be stitched back
|
||
-- together and a citation can say where in the document it came from.
|
||
ordinal integer NOT NULL,
|
||
|
||
-- The text handed to the model. Goes inside a delimited <context> block in a
|
||
-- USER message — never the system prompt. I7: this is untrusted input, and it
|
||
-- may well contain a sentence shaped like an instruction.
|
||
text text NOT NULL,
|
||
|
||
-- A short heading trail ("Handbook › Attendance › Lateness") so a citation
|
||
-- reads like a location rather than a uuid.
|
||
heading text NOT NULL DEFAULT '',
|
||
|
||
-- The keyword half of hybrid retrieval. A stored column rather than an
|
||
-- expression index so the same tsvector is used for ranking and for matching,
|
||
-- and so the text configuration is fixed at write time rather than depending
|
||
-- on whatever default_text_search_config the session happens to have.
|
||
tsv tsvector GENERATED ALWAYS AS (
|
||
setweight(to_tsvector('english', coalesce(heading, '')), 'A') ||
|
||
setweight(to_tsvector('english', coalesce(text, '')), 'B')
|
||
) STORED,
|
||
|
||
-- The dense half. NULL until embedded: ingest writes the row and embedding is
|
||
-- allowed to be a separate, retryable step, because an embedding provider
|
||
-- being down must not lose the document.
|
||
embedding real[],
|
||
embedding_model text NOT NULL DEFAULT '',
|
||
|
||
token_estimate integer NOT NULL DEFAULT 0,
|
||
created_date timestamptz NOT NULL DEFAULT now(),
|
||
|
||
CONSTRAINT knowledge_chunks_document_ordinal_key UNIQUE (document_id, ordinal),
|
||
CONSTRAINT knowledge_chunks_text_not_blank CHECK (length(btrim(text)) > 0),
|
||
CONSTRAINT knowledge_chunks_ordinal_non_negative CHECK (ordinal >= 0),
|
||
CONSTRAINT knowledge_chunks_acl_not_empty CHECK (array_length(acl, 1) >= 1),
|
||
-- An embedding without a model name is a vector nobody can compare against
|
||
-- anything. Either both or neither.
|
||
CONSTRAINT knowledge_chunks_embedding_has_model CHECK (
|
||
(embedding IS NULL AND embedding_model = '') OR
|
||
(embedding IS NOT NULL AND length(btrim(embedding_model)) > 0)
|
||
)
|
||
);
|
||
|
||
-- The permission pre-filter, and the reason `acl` is an array rather than a
|
||
-- join. GIN over the array makes `acl && $grants` an index scan, so the filter
|
||
-- that runs BEFORE scoring is also the cheap one.
|
||
CREATE INDEX knowledge_chunks_acl_idx ON knowledge_chunks USING gin (acl);
|
||
|
||
-- Tenancy and corpus, the other two halves of every WHERE clause here.
|
||
CREATE INDEX knowledge_chunks_org_source_idx ON knowledge_chunks (org_id, source);
|
||
|
||
-- The keyword half.
|
||
CREATE INDEX knowledge_chunks_tsv_idx ON knowledge_chunks USING gin (tsv);
|
||
|
||
-- "Which chunks still need embedding" — the backfill query, and the one that
|
||
-- runs after an embedding model changes. Partial, because the answer is
|
||
-- normally none and an index over every embedded chunk would earn nothing.
|
||
CREATE INDEX knowledge_chunks_unembedded_idx
|
||
ON knowledge_chunks (org_id, created_date)
|
||
WHERE embedding IS NULL;
|
||
|
||
-- Re-chunking a document: delete its chunks, write the new ones.
|
||
CREATE INDEX knowledge_chunks_document_idx ON knowledge_chunks (document_id, ordinal);
|
||
|
||
COMMENT ON TABLE knowledge_chunks IS
|
||
'Retrievable chunks. org_id, source and acl are repeated from the document so the permission '
|
||
'pre-filter is a WHERE clause on this table alone — I2 requires it to run before scoring, and a '
|
||
'join is a thing a query can be written without. See internal/knowledge.';
|