schema.sql

-- Team shared memory — D1 schema
-- Single source of truth; the MCP tools read/write here.
-- Safe full (re)install on an empty DB: drop old objects, recreate.

DROP TRIGGER IF EXISTS memories_ai;
DROP TRIGGER IF EXISTS memories_ad;
DROP TRIGGER IF EXISTS memories_au;
DROP TABLE IF EXISTS memories_fts;
DROP TABLE IF EXISTS memory_history;
DROP TABLE IF EXISTS memories;

CREATE TABLE memories (
  rowid       INTEGER PRIMARY KEY,           -- matches FTS content_rowid
  name        TEXT UNIQUE NOT NULL,          -- kebab-case slug
  description TEXT NOT NULL,                  -- one-line summary returned in the index
  type        TEXT NOT NULL DEFAULT 'shared', -- feedback | project | reference | shared
  status      TEXT NOT NULL DEFAULT 'preferred', -- preferred | tentative | contested
  body        TEXT NOT NULL,                  -- lazy-loaded body (markdown)
  author      TEXT NOT NULL DEFAULT 'unknown',-- who wrote it (from the token, verified)
  project     TEXT,                           -- optional scope
  fold        TEXT NOT NULL DEFAULT '',       -- diacritic + case folded "name description body" (search)
  version     INTEGER NOT NULL DEFAULT 1,     -- incremented on every update
  deleted_at  INTEGER,                        -- soft-delete: NULL = active
  created_at  INTEGER NOT NULL,
  updated_at  INTEGER NOT NULL
);

CREATE INDEX idx_memories_updated ON memories(updated_at);
CREATE INDEX idx_memories_project ON memories(project);
CREATE INDEX idx_memories_active  ON memories(deleted_at, updated_at);

-- Full-text search over a single `fold` column (diacritic / case insensitive).
-- The query is folded the same way, so accents and letter case never split a match.
CREATE VIRTUAL TABLE memories_fts USING fts5(
  fold,
  content='memories', content_rowid='rowid'
);

CREATE TRIGGER memories_ai AFTER INSERT ON memories BEGIN
  INSERT INTO memories_fts(rowid, fold) VALUES (new.rowid, new.fold);
END;

CREATE TRIGGER memories_ad AFTER DELETE ON memories BEGIN
  INSERT INTO memories_fts(memories_fts, rowid, fold) VALUES ('delete', old.rowid, old.fold);
END;

CREATE TRIGGER memories_au AFTER UPDATE ON memories BEGIN
  INSERT INTO memories_fts(memories_fts, rowid, fold) VALUES ('delete', old.rowid, old.fold);
  INSERT INTO memories_fts(rowid, fold) VALUES (new.rowid, new.fold);
END;

-- Change history — the previous row is archived here before every upsert/delete.
-- Enables restore (memory_restore) and a "who changed what, when" audit trail.
CREATE TABLE memory_history (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  name        TEXT NOT NULL,
  version     INTEGER NOT NULL,               -- the archived version number
  description TEXT NOT NULL,
  type        TEXT NOT NULL,
  status      TEXT NOT NULL,
  body        TEXT NOT NULL,
  author      TEXT NOT NULL,                  -- who wrote that version
  project     TEXT,
  changed_by  TEXT NOT NULL,                  -- who made this change (from the token)
  change_kind TEXT NOT NULL,                  -- 'upsert' | 'delete'
  archived_at INTEGER NOT NULL
);

CREATE INDEX idx_history_name ON memory_history(name, version DESC);