139 lines
6.6 KiB
SQL
139 lines
6.6 KiB
SQL
-- Festinger schema
|
|
-- Run once at container init via db.py:init_schema()
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- models — LLM provider configuration
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS models (
|
|
id SERIAL PRIMARY KEY,
|
|
provider VARCHAR(32) NOT NULL, -- 'claude' or 'openai'
|
|
model_name VARCHAR(128) NOT NULL,
|
|
api_key TEXT NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- config — runtime key-value configuration
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS config (
|
|
key VARCHAR(64) PRIMARY KEY,
|
|
value TEXT NOT NULL,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
INSERT INTO config (key, value) VALUES
|
|
('saliency_read_threshold', '0.5'),
|
|
('saliency_write_threshold', '1.2'),
|
|
('recollection_confidence_floor','0.6'),
|
|
('recollection_recency_days', '90'),
|
|
('resolution_schedule', '0 2 * * *'),
|
|
('write_model_id', ''),
|
|
('resolve_model_id', '')
|
|
ON CONFLICT (key) DO NOTHING;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- soas — concept vocabulary and saliency
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS soas (
|
|
id SERIAL PRIMARY KEY,
|
|
token VARCHAR(256) UNIQUE NOT NULL,
|
|
encounter_count INT NOT NULL DEFAULT 0,
|
|
last_seen TIMESTAMPTZ,
|
|
saliency FLOAT NOT NULL DEFAULT 0.0,
|
|
novelty FLOAT NOT NULL DEFAULT 0.0
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS soas_token_idx ON soas (token);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- urd — acyclic concept graph (the IN table)
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS urd (
|
|
id INT NOT NULL REFERENCES soas(id),
|
|
parent_id INT NOT NULL REFERENCES soas(id),
|
|
dim_id INT NOT NULL REFERENCES soas(id),
|
|
is_isa BOOLEAN NOT NULL DEFAULT false,
|
|
confidence FLOAT NOT NULL DEFAULT 1.0,
|
|
last_confirmed TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
source VARCHAR(32) NOT NULL DEFAULT 'cloud_llm',
|
|
PRIMARY KEY (id, parent_id, dim_id)
|
|
);
|
|
|
|
-- One parent per concept per dimension — contradiction-resistance mechanism
|
|
CREATE UNIQUE INDEX IF NOT EXISTS urd_concept_dim_idx ON urd (id, dim_id);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- resolution_queue — pending conflicts waiting for nightly resolution
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS resolution_queue (
|
|
id SERIAL PRIMARY KEY,
|
|
concept_id INT NOT NULL REFERENCES soas(id),
|
|
existing_parent_id INT NOT NULL REFERENCES soas(id),
|
|
incoming_parent_id INT NOT NULL REFERENCES soas(id),
|
|
dim_id INT NOT NULL REFERENCES soas(id),
|
|
collision_type VARCHAR(32) NOT NULL, -- 'isa_isa', 'ispart_ispart', 'misclassification'
|
|
status VARCHAR(16) NOT NULL DEFAULT 'pending',
|
|
resolution JSONB,
|
|
priority BOOLEAN NOT NULL DEFAULT false, -- gutask conflicts reviewed first
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
resolved_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS rq_status_idx ON resolution_queue (status);
|
|
CREATE INDEX IF NOT EXISTS rq_concept_idx ON resolution_queue (concept_id);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- kg_write_log — immutable audit log of every knowledge graph write
|
|
-- ---------------------------------------------------------------------------
|
|
-- op values:
|
|
-- 'insert' — new URD edge written for the first time
|
|
-- 'rewrite' — existing edge replaced (ispart_ispart update)
|
|
-- 'decompose' — isa_isa collision resolved by splitting into two dimensions
|
|
-- 'reclassify' — concept re-inserted in the correct dimension
|
|
CREATE TABLE IF NOT EXISTS kg_write_log (
|
|
id SERIAL PRIMARY KEY,
|
|
op VARCHAR(16) NOT NULL,
|
|
concept_id INT NOT NULL REFERENCES soas(id),
|
|
concept_token TEXT NOT NULL,
|
|
parent_id INT NOT NULL REFERENCES soas(id),
|
|
parent_token TEXT NOT NULL,
|
|
prev_parent_id INT REFERENCES soas(id),
|
|
prev_parent_token TEXT,
|
|
dim_id INT NOT NULL REFERENCES soas(id),
|
|
dim_token TEXT NOT NULL,
|
|
is_isa BOOLEAN NOT NULL DEFAULT false,
|
|
confidence FLOAT NOT NULL DEFAULT 1.0,
|
|
source VARCHAR(32) NOT NULL DEFAULT 'cloud_llm',
|
|
resolution_queue_id INT REFERENCES resolution_queue(id),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS kwl_created_idx ON kg_write_log (created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS kwl_concept_idx ON kg_write_log (concept_id);
|
|
CREATE INDEX IF NOT EXISTS kwl_op_idx ON kg_write_log (op);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- agent_models — per-agent LLM model assignments
|
|
-- Maps an agent identity (from X-Agent-Name header) to a specific model.
|
|
-- Priority over write_model_id (global default) when agent_name is present.
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS agent_models (
|
|
agent_name TEXT PRIMARY KEY, -- normalised lowercase, e.g. 'gunnar', 'rind'
|
|
model_id INT NOT NULL REFERENCES models(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- recollection_log — every prompt where a recollection block was injected
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS recollection_log (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
agent_name TEXT NOT NULL DEFAULT '',
|
|
salient_tokens TEXT[] NOT NULL DEFAULT '{}',
|
|
recollection_block TEXT NOT NULL,
|
|
messages_json JSONB NOT NULL DEFAULT '[]'
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS rl_created_idx ON recollection_log (created_at DESC);
|