khive-pack-knowledge 0.14.0

Knowledge verb pack — lore corpus (atoms/domains), TF-IDF retrieval, concept registration
Documentation
-- Design baseline: assign each knowledge namespace one exact trigram posting key.
-- Three Private Use Area codepoints are unchanged by FTS5's case fold and
-- tokenize as exactly one trigram. The persistent, unique slot map gives
-- disjoint generated namespace-key matches; unlike a hex phrase, no shared leading
-- trigram makes ordinary foreign namespaces part of the iterator's work.
-- A slot is never reused. Exhausting the 6400^3 - 1 supported slots fails the
-- atom write transaction instead of silently sharing a posting key.

DROP TRIGGER IF EXISTS fts_knowledge_ai;
DROP TRIGGER IF EXISTS fts_knowledge_ad;
DROP TRIGGER IF EXISTS fts_knowledge_au;
DROP TABLE IF EXISTS fts_knowledge;
DROP VIEW IF EXISTS knowledge_atoms_fts_content;

{namespace_keys_ddl}

INSERT INTO knowledge_fts_namespace_keys(namespace_value)
SELECT DISTINCT namespace FROM knowledge_atoms ORDER BY namespace;

{namespace_tokens_ddl}

CREATE VIEW knowledge_atoms_fts_content AS
SELECT a.rowid, a.id, a.namespace, k.namespace_key,
       a.slug, a.name, a.content
FROM knowledge_atoms AS a
JOIN knowledge_fts_namespace_tokens AS k ON k.namespace = a.namespace
WHERE a.deleted_at IS NULL;

CREATE VIRTUAL TABLE fts_knowledge USING fts5(
    id            UNINDEXED,
    namespace     UNINDEXED,
    namespace_key,
    slug,
    name,
    content,
    content=knowledge_atoms_fts_content,
    content_rowid=rowid,
    tokenize='trigram case_sensitive 0'
);

CREATE TRIGGER fts_knowledge_ai
AFTER INSERT ON knowledge_atoms WHEN new.deleted_at IS NULL BEGIN
    INSERT INTO knowledge_fts_namespace_keys(namespace_value)
        SELECT new.namespace WHERE NOT EXISTS (
            SELECT 1 FROM knowledge_fts_namespace_keys WHERE namespace_value = new.namespace
        ) ON CONFLICT(namespace_value) DO NOTHING;
    INSERT INTO fts_knowledge(rowid, id, namespace, namespace_key, slug, name, content)
        SELECT new.rowid, new.id, new.namespace, k.namespace_key,
               new.slug, new.name, new.content
        FROM knowledge_fts_namespace_tokens AS k WHERE k.namespace = new.namespace;
END;

CREATE TRIGGER fts_knowledge_ad
AFTER DELETE ON knowledge_atoms WHEN old.deleted_at IS NULL BEGIN
    INSERT INTO fts_knowledge(fts_knowledge, rowid, id, namespace, namespace_key,
                              slug, name, content)
        SELECT 'delete', old.rowid, old.id, old.namespace, k.namespace_key,
               old.slug, old.name, old.content
        FROM knowledge_fts_namespace_tokens AS k WHERE k.namespace = old.namespace;
END;

CREATE TRIGGER fts_knowledge_au
AFTER UPDATE OF id, namespace, slug, name, content, deleted_at ON knowledge_atoms BEGIN
    INSERT INTO fts_knowledge(fts_knowledge, rowid, id, namespace, namespace_key,
                              slug, name, content)
        SELECT 'delete', old.rowid, old.id, old.namespace, k.namespace_key,
               old.slug, old.name, old.content
        FROM knowledge_fts_namespace_tokens AS k
        WHERE k.namespace = old.namespace AND old.deleted_at IS NULL;
    INSERT INTO knowledge_fts_namespace_keys(namespace_value)
        SELECT new.namespace WHERE new.deleted_at IS NULL AND NOT EXISTS (
            SELECT 1 FROM knowledge_fts_namespace_keys WHERE namespace_value = new.namespace
        ) ON CONFLICT(namespace_value) DO NOTHING;
    INSERT INTO fts_knowledge(rowid, id, namespace, namespace_key, slug, name, content)
        SELECT new.rowid, new.id, new.namespace, k.namespace_key,
               new.slug, new.name, new.content
        FROM knowledge_fts_namespace_tokens AS k
        WHERE k.namespace = new.namespace AND new.deleted_at IS NULL;
END;

INSERT INTO fts_knowledge(fts_knowledge) VALUES ('rebuild');