-- Generated by schema/codegen.js
-- Schema version: 1
-- DO NOT EDIT - regenerate with: yarn schema:codegen

-- Unified content storage - URLs, text notes, tagsets, and images
CREATE TABLE IF NOT EXISTS items (
  id TEXT PRIMARY KEY NOT NULL,
  type TEXT NOT NULL CHECK(type IN ('url', 'text', 'tagset', 'image', 'series', 'feed', 'entity', 'event')),
  content TEXT,
  mimeType TEXT DEFAULT '',
  metadata TEXT DEFAULT '{}',
  syncId TEXT DEFAULT '',
  syncedAt INTEGER DEFAULT 0,
  createdAt INTEGER NOT NULL,
  updatedAt INTEGER NOT NULL,
  deletedAt INTEGER DEFAULT 0,
  starred INTEGER DEFAULT 0,
  archived INTEGER DEFAULT 0
);

CREATE INDEX IF NOT EXISTS idx_items_type ON items(type);
CREATE INDEX IF NOT EXISTS idx_items_syncId ON items(syncId);
CREATE INDEX IF NOT EXISTS idx_items_deletedAt ON items(deletedAt);
CREATE INDEX IF NOT EXISTS idx_items_createdAt ON items(createdAt DESC);
CREATE INDEX IF NOT EXISTS idx_items_starred ON items(starred);

-- Tag definitions with frecency tracking
CREATE TABLE IF NOT EXISTS tags (
  id TEXT PRIMARY KEY NOT NULL,
  name TEXT NOT NULL UNIQUE,
  frequency INTEGER DEFAULT 1,
  lastUsed INTEGER NOT NULL,
  frecencyScore REAL DEFAULT 0.0,
  createdAt INTEGER NOT NULL,
  updatedAt INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_tags_name ON tags(name);
CREATE INDEX IF NOT EXISTS idx_tags_frecency ON tags(frecencyScore DESC);

-- Junction table linking items to tags
CREATE TABLE IF NOT EXISTS item_tags (
  itemId TEXT NOT NULL,
  tagId TEXT NOT NULL,
  createdAt INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_item_tags_itemId ON item_tags(itemId);
CREATE INDEX IF NOT EXISTS idx_item_tags_tagId ON item_tags(tagId);
CREATE UNIQUE INDEX IF NOT EXISTS idx_item_tags_unique ON item_tags(itemId, tagId);

-- Event-dispatch rules — declarative subscribers that tie an event topic + match predicate to a script. Drives the rule engine that lets features participate in event pipelines without being permanently resident. See peek MCP plan 3d7d9239.
CREATE TABLE IF NOT EXISTS rules (

);


-- Events/entries for series and feeds - append-only time-series data
CREATE TABLE IF NOT EXISTS item_events (
  id TEXT PRIMARY KEY NOT NULL,
  itemId TEXT NOT NULL,
  content TEXT,
  value REAL,
  occurredAt INTEGER NOT NULL,
  metadata TEXT DEFAULT '{}',
  createdAt INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_item_events_item_time ON item_events(itemId, occurredAt DESC);
CREATE INDEX IF NOT EXISTS idx_item_events_occurred ON item_events(occurredAt DESC);