schema.sql

-- Nucleus-notes schema. Single SQLite file. Idempotent (IF NOT EXISTS).

CREATE TABLE IF NOT EXISTS users (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  username      TEXT    NOT NULL UNIQUE,
  display_name  TEXT    NOT NULL,
  email         TEXT,
  password_hash TEXT    NOT NULL,
  role          TEXT    NOT NULL DEFAULT 'member',   -- 'admin' | 'member'
  color_index   INTEGER NOT NULL DEFAULT 0,          -- 0..9 avatar palette slot
  active        INTEGER NOT NULL DEFAULT 1,
  created_at    TEXT    NOT NULL,
  last_login_at TEXT
);

CREATE TABLE IF NOT EXISTS sessions (
  token       TEXT    PRIMARY KEY,
  user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  csrf        TEXT    NOT NULL,
  created_at  TEXT    NOT NULL,
  expires_at  TEXT    NOT NULL,
  ip          TEXT,
  user_agent  TEXT
);
CREATE INDEX IF NOT EXISTS idx_sessions_user ON sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at);

CREATE TABLE IF NOT EXISTS notes (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  author_id   INTEGER NOT NULL REFERENCES users(id),
  title       TEXT    NOT NULL DEFAULT '',
  body        TEXT    NOT NULL DEFAULT '',
  tags        TEXT    NOT NULL DEFAULT '',            -- space-separated, no '#'
  kind        TEXT    NOT NULL DEFAULT 'note',        -- 'note' | 'goal' | 'pinned'
  pinned      INTEGER NOT NULL DEFAULT 0,
  created_at  TEXT    NOT NULL,
  updated_at  TEXT    NOT NULL,
  updated_by  INTEGER REFERENCES users(id),
  deleted_at  TEXT                                    -- non-null => in trash
);
CREATE INDEX IF NOT EXISTS idx_notes_kind    ON notes(kind);
CREATE INDEX IF NOT EXISTS idx_notes_author  ON notes(author_id);
CREATE INDEX IF NOT EXISTS idx_notes_deleted ON notes(deleted_at);
CREATE INDEX IF NOT EXISTS idx_notes_updated ON notes(updated_at);

CREATE TABLE IF NOT EXISTS note_revisions (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  note_id     INTEGER NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
  editor_id   INTEGER NOT NULL REFERENCES users(id),
  prev_title  TEXT    NOT NULL,
  prev_body   TEXT    NOT NULL,
  prev_tags   TEXT    NOT NULL DEFAULT '',
  edited_at   TEXT    NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_rev_note ON note_revisions(note_id);

CREATE TABLE IF NOT EXISTS todos (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  title        TEXT    NOT NULL,
  note_id      INTEGER REFERENCES notes(id) ON DELETE CASCADE,  -- optional link to a note
  priority     TEXT    NOT NULL DEFAULT 'p2',                   -- legacy, unused
  assignee_id  INTEGER REFERENCES users(id),                    -- legacy, unused
  done         INTEGER NOT NULL DEFAULT 0,
  completed_by INTEGER REFERENCES users(id),
  position     REAL    NOT NULL DEFAULT 0,
  created_by   INTEGER NOT NULL REFERENCES users(id),
  created_at   TEXT    NOT NULL,
  done_at      TEXT
);
CREATE INDEX IF NOT EXISTS idx_todos_done ON todos(done);

-- Anyone can pick up a to-do; many people can work the same task.
CREATE TABLE IF NOT EXISTS todo_workers (
  id        INTEGER PRIMARY KEY AUTOINCREMENT,
  todo_id   INTEGER NOT NULL REFERENCES todos(id) ON DELETE CASCADE,
  user_id   INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  picked_at TEXT    NOT NULL,
  UNIQUE(todo_id, user_id)
);
CREATE INDEX IF NOT EXISTS idx_todo_workers_todo ON todo_workers(todo_id);

CREATE TABLE IF NOT EXISTS comments (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  note_id     INTEGER NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
  author_id   INTEGER NOT NULL REFERENCES users(id),
  body        TEXT    NOT NULL,
  created_at  TEXT    NOT NULL,
  deleted_at  TEXT
);
CREATE INDEX IF NOT EXISTS idx_comments_note ON comments(note_id);

CREATE TABLE IF NOT EXISTS reactions (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  note_id     INTEGER NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
  user_id     INTEGER NOT NULL REFERENCES users(id),
  emoji       TEXT    NOT NULL,
  created_at  TEXT    NOT NULL,
  UNIQUE(note_id, user_id, emoji)
);
CREATE INDEX IF NOT EXISTS idx_reactions_note ON reactions(note_id);

CREATE TABLE IF NOT EXISTS attachments (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  note_id     INTEGER NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
  uploader_id INTEGER NOT NULL REFERENCES users(id),
  filename    TEXT    NOT NULL,
  mime        TEXT    NOT NULL,
  size        INTEGER NOT NULL,
  storage     TEXT    NOT NULL,                       -- opaque name on disk
  created_at  TEXT    NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_attachments_note ON attachments(note_id);

-- Per-user secrets vault (envelope encryption). A random master key encrypts
-- every item; the master key is stored wrapped two ways: by a key derived
-- (scrypt) from the 4-digit PIN, and by a key derived from the account
-- password (for recovery if the PIN is forgotten). Both derivations mix in a
-- server-side pepper file. The server stores neither the PIN, the password,
-- nor the master key, so these rows are useless without one of the secrets.
CREATE TABLE IF NOT EXISTS vault_meta (
  user_id     INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  salt_pin    BLOB    NOT NULL,   -- scrypt salt for the PIN-derived wrap key
  salt_pw     BLOB    NOT NULL,   -- scrypt salt for the password-derived recovery key
  enc_mk_pin  BLOB    NOT NULL,   -- master key sealed with the PIN key
  enc_mk_pw   BLOB    NOT NULL,   -- master key sealed with the password key
  created_at  TEXT    NOT NULL
);

CREATE TABLE IF NOT EXISTS vault_items (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name        TEXT    NOT NULL,
  is_file     INTEGER NOT NULL DEFAULT 0,
  filename    TEXT    NOT NULL DEFAULT '',
  content     BLOB    NOT NULL,   -- nonce||ciphertext (AES-256-GCM)
  size        INTEGER NOT NULL DEFAULT 0,   -- plaintext byte length
  created_at  TEXT    NOT NULL,
  updated_at  TEXT    NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_vault_items_user ON vault_items(user_id);

-- Full-text search over notes (external-content table mirrors `notes`).
CREATE VIRTUAL TABLE IF NOT EXISTS notes_fts USING fts5(
  title, body, tags,
  content='notes', content_rowid='id', tokenize='unicode61'
);

CREATE TRIGGER IF NOT EXISTS notes_ai AFTER INSERT ON notes BEGIN
  INSERT INTO notes_fts(rowid, title, body, tags) VALUES (new.id, new.title, new.body, new.tags);
END;
CREATE TRIGGER IF NOT EXISTS notes_ad AFTER DELETE ON notes BEGIN
  INSERT INTO notes_fts(notes_fts, rowid, title, body, tags) VALUES('delete', old.id, old.title, old.body, old.tags);
END;
CREATE TRIGGER IF NOT EXISTS notes_au AFTER UPDATE ON notes BEGIN
  INSERT INTO notes_fts(notes_fts, rowid, title, body, tags) VALUES('delete', old.id, old.title, old.body, old.tags);
  INSERT INTO notes_fts(rowid, title, body, tags) VALUES (new.id, new.title, new.body, new.tags);
END;