-- ============================================================================
-- 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.';