-- New runtime database only. Never apply this migration to the legacy memory DB.
BEGIN IMMEDIATE;
PRAGMA application_id = 1263288882; -- KLB2
PRAGMA user_version = 1;
CREATE TABLE releases (
id TEXT PRIMARY KEY,
path TEXT NOT NULL
) STRICT;
CREATE TABLE sessions (
id TEXT PRIMARY KEY,
state TEXT NOT NULL DEFAULT 'ready' CHECK(state IN ('ready','waiting')),
wake_at_ms INTEGER,
hook_revision TEXT REFERENCES releases(id),
hook_epoch INTEGER NOT NULL DEFAULT 0 CHECK(hook_epoch >= 0),
CHECK(state = 'waiting' OR wake_at_ms IS NULL)
) STRICT;
CREATE TABLE events (
seq INTEGER PRIMARY KEY AUTOINCREMENT,
session_id TEXT NOT NULL REFERENCES sessions(id),
kind TEXT NOT NULL CHECK(kind IN (
'input','scratchpad','execution.started','execution.completed',
'local.sent','delivery.held','kernel.reset','session.waited',
'session.woke','hooks.activated'
)),
source_key TEXT,
payload TEXT NOT NULL CHECK(json_valid(payload)),
created_ms INTEGER NOT NULL CHECK(created_ms >= 0),
UNIQUE(session_id, source_key)
) STRICT;
CREATE INDEX events_session_order ON events(session_id, seq);
CREATE TRIGGER events_no_update BEFORE UPDATE ON events BEGIN
SELECT RAISE(ABORT, 'historical events are immutable');
END;
CREATE TRIGGER events_no_delete BEFORE DELETE ON events BEGIN
SELECT RAISE(ABORT, 'historical events are immutable');
END;
CREATE TABLE inbox (
event_id INTEGER PRIMARY KEY REFERENCES events(seq),
status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','consumed'))
) STRICT;
CREATE TABLE executions (
id TEXT PRIMARY KEY,
session_id TEXT NOT NULL REFERENCES sessions(id),
generation TEXT NOT NULL,
hook_revision TEXT NOT NULL REFERENCES releases(id),
state TEXT NOT NULL CHECK(state IN ('running','ok','error','yielded','interrupted')),
started_event INTEGER NOT NULL UNIQUE REFERENCES events(seq),
completed_event INTEGER UNIQUE REFERENCES events(seq)
) STRICT;
CREATE UNIQUE INDEX one_running_execution ON executions(session_id) WHERE completed_event IS NULL;
CREATE TABLE effects (
id TEXT PRIMARY KEY,
execution_id TEXT NOT NULL REFERENCES executions(id),
request_id TEXT NOT NULL,
text TEXT NOT NULL,
status TEXT NOT NULL CHECK(status IN ('proposed','confirmed','held')),
event_id INTEGER UNIQUE REFERENCES events(seq),
reason TEXT,
UNIQUE(execution_id,request_id),
CHECK((status = 'confirmed' AND event_id IS NOT NULL AND reason IS NULL)
OR (status = 'held' AND event_id IS NULL AND reason IS NOT NULL)
OR (status = 'proposed' AND event_id IS NULL AND reason IS NULL))
) STRICT;
CREATE TABLE hook_traces (
effect_id TEXT PRIMARY KEY REFERENCES effects(id),
revision TEXT NOT NULL REFERENCES releases(id),
decision TEXT NOT NULL CHECK(json_valid(decision)),
fault TEXT
) STRICT;
COMMIT;