CREATE TABLE IF NOT EXISTS users (

id INTEGER PRIMARY KEY AUTOINCREMENT,

username TEXT NOT NULL UNIQUE,

token_hash TEXT NOT NULL,

password_hash TEXT,

created_at INTEGER NOT NULL DEFAULT (unixepoch()),

updated_at INTEGER NOT NULL DEFAULT (unixepoch())

);

CREATE TRIGGER IF NOT EXISTS users_modified

AFTER UPDATE ON users

BEGIN

UPDATE users SET updated_at = unixepoch() WHERE id = NEW.id;

END;

CREATE VIEW IF NOT EXISTS users_view AS

SELECT id, username, token_hash, password_hash,

datetime(created_at, 'unixepoch', 'localtime') AS created_at,

datetime(updated_at, 'unixepoch', 'localtime') AS updated_at

FROM users;

CREATE TABLE models (

model_id INTEGER PRIMARY KEY AUTOINCREMENT,

name TEXT NOT NULL UNIQUE,

created_at INTEGER NOT NULL DEFAULT (unixepoch())

);

CREATE VIEW IF NOT EXISTS models_view AS

SELECT model_id, name,

datetime(created_at, 'unixepoch', 'localtime') AS created_at

FROM models;

CREATE TABLE sessions (

session_id INTEGER PRIMARY KEY AUTOINCREMENT,

guid TEXT NOT NULL UNIQUE,

name TEXT,

name_source TEXT,

created_at INTEGER NOT NULL DEFAULT (unixepoch())

);

CREATE VIEW IF NOT EXISTS sessions_view AS

SELECT session_id, guid, name, name_source,

datetime(created_at, 'unixepoch', 'localtime') AS created_at

FROM sessions;

CREATE INDEX IF NOT EXISTS idx_sessions_name ON sessions(name);

CREATE TABLE turns (

turn_id INTEGER PRIMARY KEY AUTOINCREMENT,

user_text TEXT NOT NULL,

assistant_text TEXT,

model_id INTEGER REFERENCES models(model_id),

session_id INTEGER REFERENCES sessions(session_id),

created_at INTEGER NOT NULL DEFAULT (unixepoch()),

embedding_version INTEGER, -- NULL iff embedding IS NULL; tags which embedding_version produced `embedding`

embedding BLOB, -- embed(user_text + assistant_text); payload only, no version byte (see embedding_version)

phon TEXT -- phonize(user_text + assistant_text): Double Metaphone "sounds-like"

);

CREATE INDEX idx_turns_created_at ON turns(created_at);

CREATE INDEX idx_turns_session ON turns(session_id);

CREATE INDEX idx_turns_embedding_version ON turns(embedding_version);

CREATE VIEW IF NOT EXISTS turns_view AS

SELECT turn_id, user_text, assistant_text, model_id, session_id,

datetime(created_at, 'unixepoch', 'localtime') AS created_at,

embedding_version,

CASE WHEN embedding IS NULL THEN 0 ELSE 1 END AS has_embedding,

CASE WHEN phon IS NULL THEN 0 ELSE 1 END AS has_phon

FROM turns;

CREATE TABLE turn_summaries (

turn_summary_id INTEGER PRIMARY KEY AUTOINCREMENT,

text TEXT,

turn_id INTEGER REFERENCES turns(turn_id) ON DELETE SET NULL,

session_id INTEGER REFERENCES sessions(session_id),

turn_model_id INTEGER REFERENCES models(model_id),

summary_model_id INTEGER REFERENCES models(model_id),

turn_datetime INTEGER NOT NULL,

summarized_on INTEGER NOT NULL DEFAULT (unixepoch()),

embedding_version INTEGER, -- NULL iff embedding IS NULL

embedding BLOB, -- payload only, no version byte (see embedding_version)

phon TEXT

);

CREATE INDEX idx_turn_summaries_turn_id ON turn_summaries(turn_id);

CREATE INDEX idx_turn_summaries_session_id ON turn_summaries(session_id);

CREATE INDEX idx_turn_summaries_datetime ON turn_summaries(turn_datetime);

CREATE INDEX idx_turn_summaries_embedding_version ON turn_summaries(embedding_version);

CREATE UNIQUE INDEX idx_turn_summaries_turn_id_uniq

ON turn_summaries(turn_id) WHERE turn_id IS NOT NULL;

CREATE VIEW IF NOT EXISTS turn_summaries_view AS

turn_summary_id, text, turn_id, session_id, turn_model_id, summary_model_id,

datetime(turn_datetime, 'unixepoch', 'localtime') AS turn_datetime,

datetime(summarized_on, 'unixepoch', 'localtime') AS summarized_on,

embedding_version,

CASE WHEN embedding IS NULL THEN 0 ELSE 1 END AS has_embedding,

CASE WHEN phon IS NULL THEN 0 ELSE 1 END AS has_phon

FROM turn_summaries;

CREATE TABLE summaries (

summary_id INTEGER PRIMARY KEY AUTOINCREMENT,

text TEXT NOT NULL,

level TEXT NOT NULL, -- 'episode' | 'session' | 'project'

tags TEXT DEFAULT '' NOT NULL,

session_id INTEGER REFERENCES sessions(session_id),

model_id INTEGER REFERENCES models(model_id),

created_at INTEGER NOT NULL DEFAULT (unixepoch()),

updated_at INTEGER NOT NULL DEFAULT (unixepoch()),

embedding_version INTEGER, -- NULL iff embedding IS NULL

embedding BLOB, -- payload only, no version byte (see embedding_version)

phon TEXT -- phonize(text): Double Metaphone "sounds-like"

);

CREATE INDEX idx_summaries_level ON summaries(level);

CREATE INDEX idx_summaries_created_at ON summaries(created_at);

CREATE INDEX idx_summaries_session ON summaries(session_id);

CREATE INDEX idx_summaries_embedding_version ON summaries(embedding_version);

CREATE VIEW IF NOT EXISTS summaries_view AS

SELECT summary_id, text, level, tags, session_id, model_id,

datetime(created_at, 'unixepoch', 'localtime') AS created_at,

datetime(updated_at, 'unixepoch', 'localtime') AS updated_at,

embedding_version,

CASE WHEN embedding IS NULL THEN 0 ELSE 1 END AS has_embedding,

CASE WHEN phon IS NULL THEN 0 ELSE 1 END AS has_phon

FROM summaries;

CREATE TABLE decisions (

decision_id INTEGER PRIMARY KEY AUTOINCREMENT,

text TEXT NOT NULL,

status TEXT NOT NULL DEFAULT 'current', -- current|superseded|revisit|deprecated

tags TEXT NOT NULL DEFAULT '',

created_at INTEGER NOT NULL DEFAULT (unixepoch()),

embedding_version INTEGER, -- NULL iff embedding IS NULL

embedding BLOB, -- payload only, no version byte (see embedding_version)

phon TEXT -- phonize(text): Double Metaphone "sounds-like"

);

CREATE INDEX idx_decisions_status ON decisions(status);

CREATE INDEX idx_decisions_embedding_version ON decisions(embedding_version);

CREATE VIEW IF NOT EXISTS decisions_view AS

SELECT decision_id, text, status, tags,

datetime(created_at, 'unixepoch', 'localtime') AS created_at,

embedding_version,

CASE WHEN embedding IS NULL THEN 0 ELSE 1 END AS has_embedding,

CASE WHEN phon IS NULL THEN 0 ELSE 1 END AS has_phon

FROM decisions;

CREATE TABLE document_sources (

document_source_id INTEGER PRIMARY KEY AUTOINCREMENT,

title TEXT, -- identifies the publication

path TEXT, -- origin file

year INTEGER, -- publish year

tags TEXT NOT NULL DEFAULT '', -- general tags for the whole document

imported_at INTEGER NOT NULL -- unix epoch, user's local import time

);

CREATE INDEX idx_document_sources_imported_at ON document_sources(imported_at);

CREATE VIEW IF NOT EXISTS document_sources_view AS

SELECT document_source_id, title, path, year, tags,

datetime(imported_at, 'unixepoch', 'localtime') AS imported_at

FROM document_sources;

CREATE TABLE documents (

document_id INTEGER PRIMARY KEY AUTOINCREMENT,

text TEXT NOT NULL, -- body; chapter/section headings inline

tags TEXT NOT NULL DEFAULT '', -- chunk-specific tags only (see above)

chunk_index INTEGER,

document_source_id INTEGER REFERENCES document_sources(document_source_id),

modified_on INTEGER, -- unix epoch; = source.imported_at until edited

embedding_version INTEGER, -- NULL iff embedding IS NULL

embedding BLOB, -- payload only, no version byte (see embedding_version)

phon TEXT -- phonize(text + '\n' + title): Double Metaphone "sounds-like"

);

CREATE INDEX idx_documents_document_source_id ON documents(document_source_id);

CREATE INDEX idx_documents_embedding_version ON documents(embedding_version);

CREATE VIEW IF NOT EXISTS documents_view AS

SELECT document_id, text, tags, chunk_index, document_source_id,

datetime(modified_on, 'unixepoch', 'localtime') AS modified_on,

embedding_version,

CASE WHEN embedding IS NULL THEN 0 ELSE 1 END AS has_embedding,

CASE WHEN phon IS NULL THEN 0 ELSE 1 END AS has_phon

FROM documents;

CREATE TABLE IF NOT EXISTS settings (

key TEXT PRIMARY KEY,

value TEXT NOT NULL

);

INSERT OR IGNORE INTO settings (key, value) VALUES ('db_version', '0.15');

CREATE VIRTUAL TABLE turns_fts USING fts5 (

user_text,

assistant_text,

content='turns',

content_rowid='turn_id'

);

CREATE TRIGGER turns_ai

AFTER INSERT

ON turns

INSERT INTO turns_fts(rowid, user_text, assistant_text)

VALUES (new.turn_id, new.user_text, new.assistant_text);

END;

CREATE TRIGGER turns_ad

AFTER DELETE

ON turns

INSERT INTO turns_fts(turns_fts, rowid, user_text, assistant_text)

VALUES ('delete', old.turn_id, old.user_text, old.assistant_text);

END;

CREATE TRIGGER turns_au

AFTER UPDATE

ON turns

INSERT INTO turns_fts(turns_fts, rowid, user_text, assistant_text)

VALUES ('delete', old.turn_id, old.user_text, old.assistant_text);

INSERT INTO turns_fts(rowid, user_text, assistant_text)

VALUES (new.turn_id, new.user_text, new.assistant_text);

END;

CREATE VIRTUAL TABLE summaries_fts USING fts5 (

text,

tags,

content='summaries',

content_rowid='summary_id'

);

CREATE TRIGGER summaries_ai

AFTER INSERT

ON summaries

INSERT INTO summaries_fts(rowid, text, tags)

VALUES (new.summary_id, new.text, new.tags);

END;

CREATE TRIGGER summaries_ad

AFTER DELETE

ON summaries

INSERT INTO summaries_fts(summaries_fts, rowid, text, tags)

VALUES ('delete', old.summary_id, old.text, old.tags);

END;

CREATE TRIGGER summaries_au

AFTER UPDATE

ON summaries

INSERT INTO summaries_fts(summaries_fts, rowid, text, tags)

VALUES ('delete', old.summary_id, old.text, old.tags);

INSERT INTO summaries_fts(rowid, text, tags)

VALUES (new.summary_id, new.text, new.tags);

END;

CREATE VIRTUAL TABLE turn_summaries_fts USING fts5 (

text,

content='turn_summaries',

content_rowid='turn_summary_id'

);

CREATE TRIGGER turn_summaries_ai

AFTER INSERT

ON turn_summaries

INSERT INTO turn_summaries_fts(rowid, text)

VALUES (new.turn_summary_id, new.text);

END;

CREATE TRIGGER turn_summaries_ad

AFTER DELETE

ON turn_summaries

INSERT INTO turn_summaries_fts(turn_summaries_fts, rowid, text)

VALUES ('delete', old.turn_summary_id, old.text);

END;

CREATE TRIGGER turn_summaries_au

AFTER UPDATE

ON turn_summaries

INSERT INTO turn_summaries_fts(turn_summaries_fts, rowid, text)

VALUES ('delete', old.turn_summary_id, old.text);

INSERT INTO turn_summaries_fts(rowid, text)

VALUES (new.turn_summary_id, new.text);

END;

CREATE VIRTUAL TABLE decisions_fts USING fts5 (

text,

tags,

content='decisions',

content_rowid='decision_id'

);

CREATE TRIGGER decisions_ai

AFTER INSERT

ON decisions

INSERT INTO decisions_fts(rowid, text, tags)

VALUES (new.decision_id, new.text, new.tags);

END;

CREATE TRIGGER decisions_ad

AFTER DELETE

ON decisions

INSERT INTO decisions_fts(decisions_fts, rowid, text, tags)

VALUES ('delete', old.decision_id, old.text, old.tags);

END;

CREATE TRIGGER decisions_au

AFTER UPDATE

ON decisions

INSERT INTO decisions_fts(decisions_fts, rowid, text, tags)

VALUES ('delete', old.decision_id, old.text, old.tags);

INSERT INTO decisions_fts(rowid, text, tags)

VALUES (new.decision_id, new.text, new.tags);

END;

CREATE VIRTUAL TABLE documents_fts USING fts5 (

text,

tags,

content='documents',

content_rowid='document_id'

);

CREATE TRIGGER documents_ai

AFTER INSERT

ON documents

INSERT INTO documents_fts(rowid, text, tags)

VALUES (new.document_id, new.text, new.tags);

END;

CREATE TRIGGER documents_ad

AFTER DELETE

ON documents

INSERT INTO documents_fts(documents_fts, rowid, text, tags)

VALUES ('delete', old.document_id, old.text, old.tags);

END;

CREATE TRIGGER documents_au

AFTER UPDATE

ON documents

INSERT INTO documents_fts(documents_fts, rowid, text, tags)

VALUES ('delete', old.document_id, old.text, old.tags);

INSERT INTO documents_fts(rowid, text, tags)

VALUES (new.document_id, new.text, new.tags);

END;

CREATE VIRTUAL TABLE turns_phon_fts USING fts5 (

phon,

content='turns',

content_rowid='turn_id'

);

CREATE TRIGGER turns_pai

AFTER INSERT

ON turns

INSERT INTO turns_phon_fts(rowid, phon) VALUES (new.turn_id, new.phon);

END;

CREATE TRIGGER turns_pad

AFTER DELETE

ON turns

INSERT INTO turns_phon_fts(turns_phon_fts, rowid, phon)

VALUES ('delete', old.turn_id, old.phon);

END;

CREATE TRIGGER turns_pau

AFTER UPDATE

ON turns

INSERT INTO turns_phon_fts(turns_phon_fts, rowid, phon)

VALUES ('delete', old.turn_id, old.phon);

INSERT INTO turns_phon_fts(rowid, phon) VALUES (new.turn_id, new.phon);

END;

CREATE VIRTUAL TABLE summaries_phon_fts USING fts5 (

phon,

content='summaries',

content_rowid='summary_id'

);

CREATE TRIGGER summaries_pai

AFTER INSERT

ON summaries

INSERT INTO summaries_phon_fts(rowid, phon) VALUES (new.summary_id, new.phon);

END;

CREATE TRIGGER summaries_pad

AFTER DELETE

ON summaries

INSERT INTO summaries_phon_fts(summaries_phon_fts, rowid, phon)

VALUES ('delete', old.summary_id, old.phon);

END;

CREATE TRIGGER summaries_pau

AFTER UPDATE

ON summaries

INSERT INTO summaries_phon_fts(summaries_phon_fts, rowid, phon)

VALUES ('delete', old.summary_id, old.phon);

INSERT INTO summaries_phon_fts(rowid, phon) VALUES (new.summary_id, new.phon);

END;

CREATE VIRTUAL TABLE turn_summaries_phon_fts USING fts5 (

phon,

content='turn_summaries',

content_rowid='turn_summary_id'

);

CREATE TRIGGER turn_summaries_pai

AFTER INSERT

ON turn_summaries

INSERT INTO turn_summaries_phon_fts(rowid, phon) VALUES (new.turn_summary_id, new.phon);

END;

CREATE TRIGGER turn_summaries_pad

AFTER DELETE

ON turn_summaries

INSERT INTO turn_summaries_phon_fts(turn_summaries_phon_fts, rowid, phon)

VALUES ('delete', old.turn_summary_id, old.phon);

END;

CREATE TRIGGER turn_summaries_pau

AFTER UPDATE

ON turn_summaries

INSERT INTO turn_summaries_phon_fts(turn_summaries_phon_fts, rowid, phon)

VALUES ('delete', old.turn_summary_id, old.phon);

INSERT INTO turn_summaries_phon_fts(rowid, phon) VALUES (new.turn_summary_id, new.phon);

END;

CREATE VIRTUAL TABLE decisions_phon_fts USING fts5 (

phon,

content='decisions',

content_rowid='decision_id'

);

CREATE TRIGGER decisions_pai

AFTER INSERT

ON decisions

INSERT INTO decisions_phon_fts(rowid, phon) VALUES (new.decision_id, new.phon);

END;

CREATE TRIGGER decisions_pad

AFTER DELETE

ON decisions

INSERT INTO decisions_phon_fts(decisions_phon_fts, rowid, phon)

VALUES ('delete', old.decision_id, old.phon);

END;

CREATE TRIGGER decisions_pau

AFTER UPDATE

ON decisions

INSERT INTO decisions_phon_fts(decisions_phon_fts, rowid, phon)

VALUES ('delete', old.decision_id, old.phon);

INSERT INTO decisions_phon_fts(rowid, phon) VALUES (new.decision_id, new.phon);

END;

CREATE VIRTUAL TABLE documents_phon_fts USING fts5 (

phon,

content='documents',

content_rowid='document_id'

);

CREATE TRIGGER documents_pai

AFTER INSERT

ON documents

INSERT INTO documents_phon_fts(rowid, phon) VALUES (new.document_id, new.phon);

END;

CREATE TRIGGER documents_pad

AFTER DELETE

ON documents

INSERT INTO documents_phon_fts(documents_phon_fts, rowid, phon)

VALUES ('delete', old.document_id, old.phon);

END;

CREATE TRIGGER documents_pau

AFTER UPDATE

ON documents

INSERT INTO documents_phon_fts(documents_phon_fts, rowid, phon)

VALUES ('delete', old.document_id, old.phon);

INSERT INTO documents_phon_fts(rowid, phon) VALUES (new.document_id, new.phon);

END;