Database Schema

CREATE TABLE schema_version (
  version INTEGER PRIMARY KEY,
  applied_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE identity (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL
);

CREATE TABLE turns (
  id TEXT PRIMARY KEY,
  timestamp TEXT NOT NULL,
  state TEXT NOT NULL,                    -- AgentState enum
  input TEXT,                              -- External input (if any)
  input_source TEXT,                       -- InputSource enum
  thinking TEXT NOT NULL,                  -- Agent's reasoning
  tool_calls TEXT NOT NULL DEFAULT '[]',  -- JSON array of tool calls
  token_usage TEXT NOT NULL DEFAULT '{}', -- JSON {input, output, total}
  cost_cents INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_turns_timestamp ON turns(timestamp);
CREATE INDEX idx_turns_state ON turns(state);

CREATE TABLE tool_calls (
  id TEXT PRIMARY KEY,
  turn_id TEXT NOT NULL REFERENCES turns(id),
  name TEXT NOT NULL,
  arguments TEXT NOT NULL DEFAULT '{}',   -- JSON object
  result TEXT NOT NULL DEFAULT '',
  duration_ms INTEGER NOT NULL DEFAULT 0,
  error TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_tool_calls_turn ON tool_calls(turn_id);

CREATE TABLE transactions (
  id TEXT PRIMARY KEY,
  type TEXT NOT NULL,                     -- TransactionType enum
  amount_cents INTEGER,
  balance_after_cents INTEGER,
  description TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_transactions_type ON transactions(type);

CREATE TABLE kv (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE modifications (
  id TEXT PRIMARY KEY,
  timestamp TEXT NOT NULL,
  type TEXT NOT NULL,                     -- 'file_edit', 'npm_install', 'upstream_pull', etc.
  description TEXT NOT NULL,
  file_path TEXT,
  diff TEXT,
  reversible INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_modifications_type ON modifications(type);

CREATE TABLE installed_tools (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  type TEXT NOT NULL,                     -- 'npm', 'mcp', 'builtin'
  config TEXT DEFAULT '{}',               -- JSON object
  installed_at TEXT NOT NULL DEFAULT (datetime('now')),
  enabled INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE heartbeat_entries (
  name TEXT PRIMARY KEY,
  schedule TEXT NOT NULL,
  task TEXT NOT NULL,
  enabled INTEGER NOT NULL DEFAULT 1,
  last_run TEXT,
  next_run TEXT,
  params TEXT DEFAULT '{}',
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE skills (
  name TEXT PRIMARY KEY,
  description TEXT NOT NULL DEFAULT '',
  auto_activate INTEGER NOT NULL DEFAULT 1,
  requires TEXT DEFAULT '{}',             -- JSON object
  instructions TEXT NOT NULL DEFAULT '',
  source TEXT NOT NULL DEFAULT 'builtin', -- 'builtin', 'git', 'url', 'user'
  path TEXT NOT NULL DEFAULT '',
  enabled INTEGER NOT NULL DEFAULT 1,
  installed_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_skills_enabled ON skills(enabled);

CREATE TABLE children (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  address TEXT NOT NULL,                  -- Child's Ethereum address
  sandbox_id TEXT NOT NULL,
  genesis_prompt TEXT NOT NULL,
  creator_message TEXT,
  funded_amount_cents INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'spawning',
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  last_checked TEXT
);

CREATE INDEX idx_children_status ON children(status);

CREATE TABLE registry (
  agent_id TEXT PRIMARY KEY,
  agent_uri TEXT NOT NULL,
  chain TEXT NOT NULL DEFAULT 'eip155:8453',
  contract_address TEXT NOT NULL,
  tx_hash TEXT NOT NULL,
  registered_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE reputation (
  id TEXT PRIMARY KEY,
  from_agent TEXT NOT NULL,
  to_agent TEXT NOT NULL,
  score INTEGER NOT NULL,                 -- 1-5
  comment TEXT NOT NULL DEFAULT '',
  tx_hash TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_reputation_to ON reputation(to_agent);

CREATE TABLE inbox_messages (
  id TEXT PRIMARY KEY,
  from_address TEXT NOT NULL,
  to_address TEXT,                        -- Added in v4
  content TEXT NOT NULL,
  raw_content TEXT,                       -- Added in v4
  received_at TEXT NOT NULL DEFAULT (datetime('now')),
  processed_at TEXT,
  reply_to TEXT,
  status TEXT DEFAULT 'received',         -- Added in v4
  retry_count INTEGER DEFAULT 0,          -- Added in v4
  max_retries INTEGER DEFAULT 3           -- Added in v4
);

CREATE INDEX idx_inbox_unprocessed ON inbox_messages(received_at) WHERE processed_at IS NULL;

CREATE TABLE policy_decisions (
  id TEXT PRIMARY KEY,
  turn_id TEXT,
  tool_name TEXT NOT NULL,
  tool_args_hash TEXT NOT NULL,
  risk_level TEXT NOT NULL CHECK(risk_level IN ('safe','caution','dangerous','forbidden')),
  decision TEXT NOT NULL CHECK(decision IN ('allow','deny','quarantine')),
  rules_evaluated TEXT NOT NULL DEFAULT '[]',   -- JSON array of rule IDs
  rules_triggered TEXT NOT NULL DEFAULT '[]',   -- JSON array of rule IDs
  reason TEXT NOT NULL DEFAULT '',
  latency_ms INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_policy_decisions_turn ON policy_decisions(turn_id);
CREATE INDEX idx_policy_decisions_tool ON policy_decisions(tool_name);
CREATE INDEX idx_policy_decisions_decision ON policy_decisions(decision);

CREATE TABLE spend_tracking (
  id TEXT PRIMARY KEY,
  tool_name TEXT NOT NULL,
  amount_cents INTEGER NOT NULL,
  recipient TEXT,
  domain TEXT,
  category TEXT NOT NULL CHECK(category IN ('transfer','x402','inference','other')),
  window_hour TEXT NOT NULL,              -- 'YYYY-MM-DD HH:00'
  window_day TEXT NOT NULL,               -- 'YYYY-MM-DD'
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_spend_hour ON spend_tracking(category, window_hour);
CREATE INDEX idx_spend_day ON spend_tracking(category, window_day);

CREATE TABLE heartbeat_schedule (
  task_name TEXT PRIMARY KEY,
  cron_expression TEXT NOT NULL,
  interval_ms INTEGER,
  enabled INTEGER NOT NULL DEFAULT 1,
  priority INTEGER NOT NULL DEFAULT 0,
  timeout_ms INTEGER NOT NULL DEFAULT 30000,
  max_retries INTEGER NOT NULL DEFAULT 1,
  tier_minimum TEXT NOT NULL DEFAULT 'dead' CHECK(tier_minimum IN ('dead','critical','low_compute','normal','high')),
  last_run_at TEXT,
  next_run_at TEXT,
  last_result TEXT CHECK(last_result IN ('success','failure','timeout','skipped') OR last_result IS NULL),
  last_error TEXT,
  run_count INTEGER NOT NULL DEFAULT 0,
  fail_count INTEGER NOT NULL DEFAULT 0,
  lease_owner TEXT,
  lease_expires_at TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE heartbeat_history (
  id TEXT PRIMARY KEY,
  task_name TEXT NOT NULL REFERENCES heartbeat_schedule(task_name),
  started_at TEXT NOT NULL,
  completed_at TEXT,
  result TEXT NOT NULL CHECK(result IN ('success','failure','timeout','skipped')),
  duration_ms INTEGER,
  error TEXT,
  idempotency_key TEXT UNIQUE,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_hb_history_task ON heartbeat_history(task_name, started_at);

CREATE TABLE wake_events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  source TEXT NOT NULL,                   -- 'heartbeat', 'inbox', 'credits', 'manual'
  reason TEXT NOT NULL,
  payload TEXT DEFAULT '{}',              -- JSON object
  consumed_at TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_wake_unconsumed ON wake_events(created_at) WHERE consumed_at IS NULL;

CREATE TABLE heartbeat_dedup (
  dedup_key TEXT PRIMARY KEY,
  task_name TEXT NOT NULL,
  expires_at TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_dedup_expires ON heartbeat_dedup(expires_at);

CREATE TABLE soul_history (
  id TEXT PRIMARY KEY,
  version INTEGER NOT NULL,
  content TEXT NOT NULL,
  content_hash TEXT NOT NULL,
  change_source TEXT NOT NULL CHECK(change_source IN ('agent','human','system','genesis','reflection')),
  change_reason TEXT,
  previous_version_id TEXT REFERENCES soul_history(id),
  approved_by TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_soul_version ON soul_history(version);

CREATE TABLE working_memory (
  id TEXT PRIMARY KEY,
  session_id TEXT NOT NULL,
  content TEXT NOT NULL,
  content_type TEXT NOT NULL CHECK(content_type IN ('goal','observation','plan','reflection','task','decision','note','summary')),
  priority REAL NOT NULL DEFAULT 0.5,
  token_count INTEGER NOT NULL DEFAULT 0,
  expires_at TEXT,
  source_turn TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_wm_session ON working_memory(session_id, priority);

CREATE TABLE episodic_memory (
  id TEXT PRIMARY KEY,
  session_id TEXT NOT NULL,
  event_type TEXT NOT NULL,
  summary TEXT NOT NULL,
  detail TEXT,
  outcome TEXT CHECK(outcome IN ('success','failure','partial','neutral') OR outcome IS NULL),
  importance REAL NOT NULL DEFAULT 0.5,
  embedding_key TEXT,
  token_count INTEGER NOT NULL DEFAULT 0,
  accessed_count INTEGER NOT NULL DEFAULT 0,
  last_accessed_at TEXT,
  classification TEXT NOT NULL DEFAULT 'maintenance' CHECK(classification IN ('strategic','productive','communication','maintenance','idle','error')),
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_episodic_type ON episodic_memory(event_type);
CREATE INDEX idx_episodic_importance ON episodic_memory(importance);
CREATE INDEX idx_episodic_classification ON episodic_memory(classification);

CREATE TABLE semantic_memory (
  id TEXT PRIMARY KEY,
  category TEXT NOT NULL CHECK(category IN ('self','environment','financial','agent','domain','procedural_ref','creator')),
  key TEXT NOT NULL,
  value TEXT NOT NULL,
  confidence REAL NOT NULL DEFAULT 1.0,
  source TEXT NOT NULL,
  embedding_key TEXT,
  last_verified_at TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE(category, key)
);

CREATE INDEX idx_semantic_category ON semantic_memory(category);

CREATE TABLE procedural_memory (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  description TEXT NOT NULL,
  steps TEXT NOT NULL,                    -- JSON array of strings
  success_count INTEGER NOT NULL DEFAULT 0,
  failure_count INTEGER NOT NULL DEFAULT 0,
  last_used_at TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE relationship_memory (
  id TEXT PRIMARY KEY,
  entity_address TEXT NOT NULL UNIQUE,
  entity_name TEXT,
  relationship_type TEXT NOT NULL,        -- 'creator', 'peer', 'child', 'parent', 'user'
  trust_score REAL NOT NULL DEFAULT 0.5,
  interaction_count INTEGER NOT NULL DEFAULT 0,
  last_interaction_at TEXT,
  notes TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_rel_trust ON relationship_memory(trust_score);

CREATE TABLE session_summaries (
  id TEXT PRIMARY KEY,
  session_id TEXT NOT NULL UNIQUE,
  summary TEXT NOT NULL,
  key_decisions TEXT NOT NULL DEFAULT '[]',     -- JSON array
  tools_used TEXT NOT NULL DEFAULT '[]',        -- JSON array
  outcomes TEXT NOT NULL DEFAULT '[]',          -- JSON array
  turn_count INTEGER NOT NULL DEFAULT 0,
  total_tokens INTEGER NOT NULL DEFAULT 0,
  total_cost_cents INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE inference_costs (
  id TEXT PRIMARY KEY,
  session_id TEXT NOT NULL,
  turn_id TEXT,
  model TEXT NOT NULL,
  provider TEXT NOT NULL,
  input_tokens INTEGER NOT NULL DEFAULT 0,
  output_tokens INTEGER NOT NULL DEFAULT 0,
  cost_cents INTEGER NOT NULL DEFAULT 0,
  latency_ms INTEGER NOT NULL DEFAULT 0,
  tier TEXT NOT NULL,
  task_type TEXT NOT NULL CHECK(task_type IN ('agent_turn','heartbeat_triage','safety_check','summarization','planning')),
  cache_hit INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_inf_session ON inference_costs(session_id);
CREATE INDEX idx_inf_model ON inference_costs(model);
CREATE INDEX idx_inf_created ON inference_costs(created_at);
CREATE INDEX idx_inf_task ON inference_costs(task_type);

CREATE TABLE model_registry (
  model_id TEXT PRIMARY KEY,
  provider TEXT NOT NULL,
  display_name TEXT NOT NULL,
  tier_minimum TEXT NOT NULL DEFAULT 'normal',
  cost_per_1k_input INTEGER NOT NULL DEFAULT 0,    -- Hundredths of cents per 1k tokens
  cost_per_1k_output INTEGER NOT NULL DEFAULT 0,   -- Hundredths of cents per 1k tokens
  max_tokens INTEGER NOT NULL DEFAULT 4096,
  context_window INTEGER NOT NULL DEFAULT 128000,
  supports_tools INTEGER NOT NULL DEFAULT 1,
  supports_vision INTEGER NOT NULL DEFAULT 0,
  parameter_style TEXT NOT NULL DEFAULT 'max_tokens' CHECK(parameter_style IN ('max_tokens','max_completion_tokens')),
  enabled INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE child_lifecycle_events (
  id TEXT PRIMARY KEY,
  child_id TEXT NOT NULL,
  from_state TEXT NOT NULL,
  to_state TEXT NOT NULL CHECK(to_state IN (
    'requested','sandbox_created','runtime_ready','wallet_verified',
    'funded','starting','healthy','unhealthy','stopped','failed','cleaned_up'
  )),
  reason TEXT,
  metadata TEXT DEFAULT '{}',
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_child_events ON child_lifecycle_events(child_id, created_at);

CREATE TABLE discovered_agents_cache (
  agent_address TEXT PRIMARY KEY,
  agent_card TEXT NOT NULL,               -- JSON-LD agent card
  fetched_from TEXT NOT NULL,
  card_hash TEXT NOT NULL,
  valid_until TEXT,
  fetch_count INTEGER NOT NULL DEFAULT 1,
  last_fetched_at TEXT NOT NULL DEFAULT (datetime('now')),
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE onchain_transactions (
  id TEXT PRIMARY KEY,
  tx_hash TEXT NOT NULL UNIQUE,
  chain TEXT NOT NULL,
  operation TEXT NOT NULL,
  status TEXT NOT NULL CHECK(status IN ('pending','confirmed','failed')),
  gas_used INTEGER,
  metadata TEXT DEFAULT '{}',
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_onchain_status ON onchain_transactions(status);

CREATE TABLE metric_snapshots (
  id TEXT PRIMARY KEY,
  snapshot_at TEXT NOT NULL,
  metrics_json TEXT NOT NULL DEFAULT '[]',        -- JSON array of {name, value, type}
  alerts_json TEXT NOT NULL DEFAULT '[]',         -- JSON array of {rule, message, severity}
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_metric_snapshots_at ON metric_snapshots(snapshot_at);