kami
242 lines
12 KiB
Go
242 lines
12 KiB
Go
package store
|
|
|
|
import (
|
|
"context"
|
|
"database/sql"
|
|
"fmt"
|
|
)
|
|
|
|
// migrations are ordered, forward-only schema steps applied after schema.sql.
|
|
// Index i (1-based) is the user_version the step at migrations[i-1] brings the
|
|
// DB TO; there is no step 0 — schema.sql is the idempotent baseline (version 0).
|
|
// An empty slice is a clean no-op that leaves user_version at 0.
|
|
//
|
|
// To add migration #1 (e.g. the sqlcipher rekey), append its SQL:
|
|
//
|
|
// var migrations = []string{
|
|
// `ALTER TABLE ...;`, // #1
|
|
// }
|
|
var migrations = []string{
|
|
`ALTER TABLE tools ADD COLUMN scope TEXT NOT NULL DEFAULT 'homelab';`, // #1
|
|
`ALTER TABLE reminders ADD COLUMN cron TEXT;
|
|
ALTER TABLE reminders ADD COLUMN next_fire_ts INTEGER;`, // #2
|
|
`CREATE TABLE memory_vectors (
|
|
id TEXT PRIMARY KEY,
|
|
vec BLOB NOT NULL,
|
|
meta TEXT NOT NULL DEFAULT '{}',
|
|
created_ts INTEGER NOT NULL
|
|
);`, // #3 — long-term vector memory (persistent backend for internal/memory)
|
|
`CREATE TABLE IF NOT EXISTS events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
fact_id INTEGER NOT NULL REFERENCES facts(id),
|
|
action TEXT NOT NULL,
|
|
object TEXT NOT NULL,
|
|
ts INTEGER NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_events_action_object ON events (action, object, ts DESC);
|
|
CREATE TABLE IF NOT EXISTS proposed_routines (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
action TEXT NOT NULL,
|
|
object TEXT NOT NULL,
|
|
interval_days REAL NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'proposed' CHECK (status IN ('proposed','accepted','dismissed')),
|
|
created_ts INTEGER NOT NULL,
|
|
reminder_id INTEGER REFERENCES reminders(id),
|
|
UNIQUE(action, object)
|
|
);`, // #4 — event extraction + pattern inference
|
|
|
|
`CREATE TABLE IF NOT EXISTS ack_sends (
|
|
rule TEXT NOT NULL,
|
|
sent_at INTEGER NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_ack_sends_rule ON ack_sends (rule, sent_at DESC);`, // #5 — sev4 telegram repeat-til-ack tracking
|
|
|
|
`CREATE TABLE IF NOT EXISTS delivery_attempts (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
kind TEXT NOT NULL CHECK (kind IN ('nudge','reminder')),
|
|
rule TEXT NOT NULL DEFAULT '',
|
|
reminder_id INTEGER NOT NULL DEFAULT 0,
|
|
channel TEXT NOT NULL,
|
|
body_hash TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','sent','failed','unknown')),
|
|
created_ts INTEGER NOT NULL,
|
|
completed_ts INTEGER
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_delivery_attempts_status ON delivery_attempts (status);`, // #6 — durable delivery outbox
|
|
|
|
`ALTER TABLE facts ADD COLUMN subject TEXT NOT NULL DEFAULT '';
|
|
ALTER TABLE facts ADD COLUMN entity_id TEXT;
|
|
ALTER TABLE facts ADD COLUMN resolution_state TEXT NOT NULL DEFAULT 'none'
|
|
CHECK (resolution_state IN ('none','pending','resolved','ambiguous','not_found'));
|
|
CREATE INDEX IF NOT EXISTS idx_facts_entity_id ON facts (entity_id) WHERE entity_id IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_facts_resolution_pending ON facts (resolution_state) WHERE resolution_state = 'pending';`, // #7 — entity-aware memory (Vikunja #279): facts about a subject get resolved to a Nexus entity_id async
|
|
|
|
`CREATE INDEX IF NOT EXISTS idx_nudges_snoozed ON nudges (outcome_ts) WHERE outcome = 'snoozed';`, // #8 — SnoozedUntil runs every tick; keep it off a full scan (Vikunja #364)
|
|
`ALTER TABLE proposed_routines ADD COLUMN accepted_ts INTEGER;
|
|
ALTER TABLE proposed_routines ADD COLUMN last_fired_ts INTEGER;`, // #9 — accepted routines keep firing (Vikunja #366): the tick loop needs to know when a routine was accepted and when it last nudged
|
|
|
|
`CREATE TABLE IF NOT EXISTS dialogue_sessions (
|
|
id TEXT PRIMARY KEY,
|
|
data BLOB NOT NULL,
|
|
ts INTEGER NOT NULL,
|
|
ttl_ms INTEGER NOT NULL,
|
|
expires_ts INTEGER NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_dialogue_sessions_expires ON dialogue_sessions (expires_ts);`, // #10 — the follow-up session survives a restart (Vikunja #363); small, TTL-pruned table, not a history log
|
|
|
|
`CREATE TABLE IF NOT EXISTS meta (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL
|
|
);`, // #11 — small key/value table for facts about the DB itself; first key is embedder_id (Vikunja #378)
|
|
|
|
// #12 — a suppressed nudge gets a 'dropped' row (Vikunja #370). sqlite
|
|
// can't widen a CHECK constraint in place, so the table is rebuilt; the
|
|
// index goes with the old table and is recreated. The columns are listed
|
|
// out rather than `SELECT *` — copying by position would silently shuffle
|
|
// every row if the old table's column order ever differed from this one.
|
|
`CREATE TABLE delivery_attempts_v12 (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
kind TEXT NOT NULL CHECK (kind IN ('nudge','reminder')),
|
|
rule TEXT NOT NULL DEFAULT '',
|
|
reminder_id INTEGER NOT NULL DEFAULT 0,
|
|
channel TEXT NOT NULL,
|
|
body_hash TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','sent','failed','unknown','dropped')),
|
|
created_ts INTEGER NOT NULL,
|
|
completed_ts INTEGER
|
|
);
|
|
INSERT INTO delivery_attempts_v12
|
|
(id, kind, rule, reminder_id, channel, body_hash, status, created_ts, completed_ts)
|
|
SELECT id, kind, rule, reminder_id, channel, body_hash, status, created_ts, completed_ts
|
|
FROM delivery_attempts;
|
|
DROP TABLE delivery_attempts;
|
|
ALTER TABLE delivery_attempts_v12 RENAME TO delivery_attempts;
|
|
CREATE INDEX IF NOT EXISTS idx_delivery_attempts_status ON delivery_attempts (status);`,
|
|
|
|
// #13 — durable digest outbox (Vikunja #281). A care nudge the restraint
|
|
// gate suppresses (quiet hours / away / calendar-busy) is not necessarily
|
|
// lost: if it's worth resurfacing, it lands here instead, and gets spoken
|
|
// as one bundle at the next moment speaking is appropriate. body_hash
|
|
// dedupes repeat suppressions of the "same" nudge; expires_ts bounds how
|
|
// stale an entry may get before it's worthless and must be dropped rather
|
|
// than delivered late.
|
|
`CREATE TABLE IF NOT EXISTS digest_entries (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
rule TEXT NOT NULL,
|
|
severity INTEGER NOT NULL,
|
|
body TEXT NOT NULL,
|
|
body_hash TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','drained','expired')),
|
|
created_ts INTEGER NOT NULL,
|
|
expires_ts INTEGER NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_digest_entries_status ON digest_entries (status);`,
|
|
|
|
// #14 — the task capture store (Vikunja #130). Deliberately NOT facts:
|
|
// a fact is a claim about the world that gets superseded, a task is a
|
|
// piece of work with a lifecycle (captured → open → done), and the
|
|
// prioritiser needs to read the live set cheaply.
|
|
//
|
|
// status: 'candidate' is a task Maven derived from something she read
|
|
// (mail, later) and has NOT been confirmed by the owner; 'open' is a task
|
|
// he actually stated (or confirmed). Nothing schedules or announces off
|
|
// this table — capture is not a nag.
|
|
//
|
|
// norm is the normalised dedupe key. The unique index is PARTIAL, over
|
|
// live rows only: re-capturing "купить молоко" after last week's one is
|
|
// done must work, while the same mail arriving twice must not produce two
|
|
// rows.
|
|
`CREATE TABLE IF NOT EXISTS tasks (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
created_ts INTEGER NOT NULL,
|
|
text TEXT NOT NULL,
|
|
norm TEXT NOT NULL,
|
|
source TEXT NOT NULL,
|
|
evidence TEXT NOT NULL DEFAULT '',
|
|
status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('candidate','open','done','dropped')),
|
|
due_ts INTEGER,
|
|
weight INTEGER NOT NULL DEFAULT 0,
|
|
resolved_ts INTEGER
|
|
);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_tasks_live_norm ON tasks (norm) WHERE status IN ('candidate','open');
|
|
CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks (status, created_ts DESC);`,
|
|
|
|
// #15 — external identity and resolution attribution for tasks.
|
|
//
|
|
// ext_id is the identity of the thing a derived task was extracted FROM
|
|
// (message id plus the extracted span), and its unique index covers EVERY
|
|
// row, not just the live ones. The live-only norm index is right for
|
|
// voice, where him saying the errand again is the recurrence signal. It is
|
|
// wrong for a mailbox: mavmaild is a read-only reader, nothing marks a
|
|
// message read, so a task he already finished would be re-extracted from
|
|
// the same immutable text on the next poll and land back on his list as a
|
|
// fresh candidate, forever.
|
|
//
|
|
// resolved_by records which caller moved the task. resolved_ts said when
|
|
// and never by what, so a wrong resolution left no trace at all.
|
|
`ALTER TABLE tasks ADD COLUMN ext_id TEXT;
|
|
ALTER TABLE tasks ADD COLUMN resolved_by TEXT NOT NULL DEFAULT '';
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_tasks_ext_id ON tasks (ext_id) WHERE ext_id IS NOT NULL;`,
|
|
// #16 — ecosystem call traces (Vikunja #273). Deliberately NOT facts.
|
|
// Traces are written at machine rate, one act turn produces three or four,
|
|
// while facts are written at human rate. Sharing the facts table made every
|
|
// bounded reader of facts (the habit profile's 2000-row window, memeval's
|
|
// prompt snapshot, /dash's 50 and /history's 200) read mostly traces after
|
|
// a day of ecosystem use, pushing the rows that matter out of range.
|
|
// Retention is enforced on write (PruneEcosystemTraces) because nothing
|
|
// here is an audit trail: a trace answers "did this hop work" for as long
|
|
// as anyone is still asking.
|
|
`CREATE TABLE IF NOT EXISTS ecosystem_traces (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
ts INTEGER NOT NULL,
|
|
service TEXT NOT NULL,
|
|
operation TEXT NOT NULL,
|
|
status TEXT NOT NULL,
|
|
duration_ms INTEGER NOT NULL DEFAULT 0,
|
|
correlation_id TEXT NOT NULL DEFAULT '',
|
|
causation_id TEXT NOT NULL DEFAULT '',
|
|
http_status INTEGER NOT NULL DEFAULT 0,
|
|
fields TEXT NOT NULL DEFAULT '{}'
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_eco_traces_ts ON ecosystem_traces (ts DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_eco_traces_correlation ON ecosystem_traces (correlation_id);`,
|
|
`ALTER TABLE tools ADD COLUMN fingerprint TEXT NOT NULL DEFAULT '';`,
|
|
// #17 — what a discovered tool WAS when it was approved (Vikunja #251).
|
|
// An MCP row's cmd is ["mcp", server, tool], which is a late-bound
|
|
// reference: it names a tool on a server the remote end owns and it pins
|
|
// no behaviour at all. A server upgraded, or taken over, can redefine
|
|
// list_tasks into something that writes without the row changing by one
|
|
// byte. The fingerprint is the declared shape at approval time, so a
|
|
// redefinition is a re-approval instead of a silent upgrade.
|
|
}
|
|
|
|
// migrate applies every migration with a number greater than the DB's current
|
|
// user_version, each in its own transaction that also bumps user_version. Fails
|
|
// closed: the first erroring step aborts and leaves prior steps committed.
|
|
func migrate(ctx context.Context, db *sql.DB) error {
|
|
var current int
|
|
if err := db.QueryRowContext(ctx, "PRAGMA user_version").Scan(¤t); err != nil {
|
|
return fmt.Errorf("read user_version: %w", err)
|
|
}
|
|
for i := current; i < len(migrations); i++ {
|
|
version := i + 1 // 1-based: migrations[i] brings DB to `version`
|
|
tx, err := db.BeginTx(ctx, nil)
|
|
if err != nil {
|
|
return fmt.Errorf("migration %d begin: %w", version, err)
|
|
}
|
|
if _, err := tx.ExecContext(ctx, migrations[i]); err != nil {
|
|
_ = tx.Rollback()
|
|
return fmt.Errorf("migration %d: %w", version, err)
|
|
}
|
|
// PRAGMA user_version can't be parameterized; version is our own int.
|
|
if _, err := tx.ExecContext(ctx, fmt.Sprintf("PRAGMA user_version = %d", version)); err != nil {
|
|
_ = tx.Rollback()
|
|
return fmt.Errorf("migration %d bump: %w", version, err)
|
|
}
|
|
if err := tx.Commit(); err != nil {
|
|
return fmt.Errorf("migration %d commit: %w", version, err)
|
|
}
|
|
}
|
|
return nil
|
|
}
|