Arcrun/kbdb/migrations/0001_base.sql

-- KBDB Base — atomic universal table (3 tables, never changes)
-- SDD: .agents/specs/arcrun/kbdb-base/design.md
--
-- Plugin model (like PostgreSQL core + PGVector/AGE):
--   - Base = these 3 tables + plain CRUD + D1 LIKE search (D1 only, free, no credit card).
--   - embed module = optional, writes vectors to Vectorize (does NOT alter these tables).
--   - triplet module = separate repo, writes derived records into entry_values (does NOT alter base).
-- "Table never changes": new tech records its output elsewhere, never ALTERs the base.

-- ============================================================
-- 1. Three tables
-- ============================================================

-- Universal main table: each entry is one atomic datum.
-- entry_type extended for arcrun: 'block' | 'value' | 'template' | 'slot' | 'project' | 'workflow' | 'recipe_stat'
CREATE TABLE IF NOT EXISTS entries (
  id TEXT PRIMARY KEY,
  content TEXT,
  entry_type TEXT NOT NULL,
  owner_id TEXT,                   -- multi-tenant: namespace (self-hosted) or api_key (SaaS)

  -- tree structure (project -> workflow via parent_id; SDD Q1 decision)
  parent_id TEXT,

  -- optional block metadata (harmless plain columns)
  page_name TEXT,
  refs_json TEXT DEFAULT '[]',
  tags_json TEXT DEFAULT '[]',
  task_status TEXT,

  -- optional embed bookkeeping (set by optional embed module; base never reads them)
  content_hash TEXT,
  is_embedded INTEGER DEFAULT 0,

  -- metadata
  confidence REAL,
  metadata_json TEXT,

  created_at INTEGER DEFAULT (unixepoch()),
  updated_at INTEGER DEFAULT (unixepoch())
);

-- Template table: defines slots for a virtual table
CREATE TABLE IF NOT EXISTS templates (
  id TEXT PRIMARY KEY,
  name TEXT UNIQUE NOT NULL,
  description TEXT,
  slots_json TEXT NOT NULL,        -- JSON array, e.g. ["display_name","gender"]
  created_by TEXT,                 -- 'system' | 'ai' | owner_id
  created_at INTEGER DEFAULT (unixepoch()),
  updated_at INTEGER DEFAULT (unixepoch())
);

-- Slot link table: composes multiple entries into one structured record
CREATE TABLE IF NOT EXISTS entry_values (
  id TEXT PRIMARY KEY,
  record_id TEXT NOT NULL,
  template_id TEXT NOT NULL REFERENCES templates(id),
  slot_name TEXT NOT NULL,
  entry_id TEXT NOT NULL REFERENCES entries(id),
  created_at INTEGER DEFAULT (unixepoch()),
  UNIQUE(record_id, slot_name)
);

-- ============================================================
-- 2. Indexes
-- ============================================================

CREATE INDEX IF NOT EXISTS idx_entries_type ON entries(entry_type);
CREATE INDEX IF NOT EXISTS idx_entries_owner ON entries(owner_id);
CREATE INDEX IF NOT EXISTS idx_entries_parent ON entries(parent_id);
CREATE INDEX IF NOT EXISTS idx_entries_page ON entries(page_name);
CREATE INDEX IF NOT EXISTS idx_entries_task ON entries(task_status);
CREATE INDEX IF NOT EXISTS idx_entries_hash ON entries(content_hash);

CREATE INDEX IF NOT EXISTS idx_ev_record ON entry_values(record_id);
CREATE INDEX IF NOT EXISTS idx_ev_template ON entry_values(template_id);
CREATE INDEX IF NOT EXISTS idx_ev_template_slot ON entry_values(template_id, slot_name);
CREATE INDEX IF NOT EXISTS idx_ev_entry ON entry_values(entry_id);

-- ============================================================
-- 3. Seed templates used by arcrun base
-- ============================================================

-- recipe_stat: success/failure counters for a recipe (feeds recipe submission, SDD section 7)
INSERT OR IGNORE INTO templates (id, name, description, slots_json, created_by)
VALUES
  ('tpl-recipe-stat', 'recipe_stat', 'recipe success/failure counters', '["canonical_id","success_count","failure_count","last_status","last_at"]', 'system');