-- ============================================================================
-- OCMAS Standard v1.0 — Consolidated Portable Schema
-- ============================================================================
-- The complete substrate in one file. SQLite-flavored (the reference runs on
-- D1); adjust types/constraints for your engine but KEEP every NOT NULL and
-- CHECK — they are governance, not style (see Part 4 §4.4 rule 3: required
-- fields over hopeful fields).
--
-- Scoping convention (Part 1 §1.5): every table carries
--   scope_kind TEXT CHECK (scope_kind IN ('platform','tenant','project'))
--   scope_id   TEXT           -- NULL only for platform defaults
-- Resolution: project > tenant > platform > code fallback.
-- ============================================================================

-- ----------------------------------------------------------------------------
-- Part 2 — The Role Contract
-- ----------------------------------------------------------------------------

CREATE TABLE roles (
  id TEXT PRIMARY KEY,
  scope_kind TEXT NOT NULL DEFAULT 'platform' CHECK (scope_kind IN ('platform','tenant','project')),
  scope_id TEXT,
  role_key TEXT NOT NULL,                    -- 'cmo', 'senior_architect'
  title TEXT NOT NULL,
  persona_ref TEXT,                          -- prompt-registry key
  model TEXT,                                -- occupant: model tier | 'human' | 'fn:<name>'
  owns_levers TEXT,                          -- JSON array (the charter)
  can_request TEXT,                          -- JSON array
  reports_to TEXT,                           -- role_key (org chart renders from this)
  feature_flag TEXT,
  source TEXT NOT NULL DEFAULT 'default' CHECK (source IN ('default','agent','boardroom','human')),
  created_by TEXT, created_at TEXT NOT NULL,
  UNIQUE (scope_kind, scope_id, role_key)
);

CREATE TABLE prompt_registry (
  id TEXT PRIMARY KEY,
  scope_kind TEXT NOT NULL DEFAULT 'platform', scope_id TEXT,
  key TEXT NOT NULL,
  body TEXT NOT NULL,
  version INTEGER NOT NULL DEFAULT 1,        -- append-only: new version = new row
  parent_version INTEGER,
  change_source TEXT NOT NULL DEFAULT 'default'
    CHECK (change_source IN ('default','agent','boardroom','human','rollback')),
  changed_by TEXT, created_at TEXT NOT NULL,
  UNIQUE (scope_kind, scope_id, key, version)
);

CREATE TABLE agent_skills (
  id TEXT PRIMARY KEY,
  scope_kind TEXT NOT NULL DEFAULT 'platform', scope_id TEXT,
  key TEXT NOT NULL,
  title TEXT, body TEXT,                     -- the SOP; prompt-injected only when held
  version INTEGER NOT NULL DEFAULT 1,
  source TEXT NOT NULL DEFAULT 'default' CHECK (source IN ('default','agent','human')),
  updated_by TEXT, updated_at TEXT
);

CREATE TABLE agent_capabilities (            -- the grant matrix: skills/tools/context per role
  id TEXT PRIMARY KEY,
  scope_kind TEXT NOT NULL DEFAULT 'platform', scope_id TEXT,
  role_key TEXT NOT NULL,
  kind TEXT NOT NULL CHECK (kind IN ('skill','tool','context')),
  ref TEXT NOT NULL,                         -- skill.key | tool.name | kb.category
  granted_by TEXT, granted_at TEXT,
  source TEXT NOT NULL DEFAULT 'default' CHECK (source IN ('default','agent','human')),
  UNIQUE (scope_kind, scope_id, role_key, kind, ref)
);

-- ----------------------------------------------------------------------------
-- Part 3 — The Duty Roster (Law 3: coverage)
-- ----------------------------------------------------------------------------

CREATE TABLE role_duties (
  id TEXT PRIMARY KEY,
  scope_kind TEXT NOT NULL DEFAULT 'tenant', scope_id TEXT,
  role_key TEXT NOT NULL,
  duty_key TEXT NOT NULL,
  title TEXT NOT NULL,
  description TEXT,
  trigger_kind TEXT NOT NULL CHECK (trigger_kind IN ('cron','event','dependency')),
  trigger_spec TEXT NOT NULL,                -- cron expr | event type constant | upstream duty_key
  expected_outcome TEXT NOT NULL,            -- Law 2 at write time: no assertion, no duty
  outcome_assertion TEXT,                    -- machine-checkable predicate
  sla_minutes INTEGER NOT NULL DEFAULT 60,
  max_cost_usd REAL,
  priority INTEGER NOT NULL DEFAULT 50,      -- events > calendar > backlog
  reads TEXT,                                -- JSON: data sources consumed (enables C3)
  levers TEXT,                               -- JSON: must be subset of charter (C2)
  charter_ref TEXT,                          -- which responsibility this covers (C1)
  version INTEGER NOT NULL DEFAULT 1,
  source TEXT NOT NULL DEFAULT 'default' CHECK (source IN ('default','agent','human')),
  active INTEGER NOT NULL DEFAULT 1,
  escalation TEXT,
  created_by TEXT, created_at TEXT NOT NULL,
  UNIQUE (scope_kind, scope_id, role_key, duty_key)
);

CREATE TABLE duty_runs (
  id TEXT PRIMARY KEY,
  duty_id TEXT NOT NULL REFERENCES role_duties(id),
  scope_id TEXT, role_key TEXT NOT NULL,
  due_at TEXT NOT NULL,
  fired_at TEXT,
  fired_by TEXT NOT NULL DEFAULT 'dispatcher',   -- dispatcher | conductor | human
  resolution TEXT CHECK (resolution IN ('delivered','skipped','blocked','missed','preempted')),
  reason TEXT,                               -- REQUIRED on any non-delivered resolution:
  CHECK (resolution IS NULL OR resolution = 'delivered' OR reason IS NOT NULL),
  outcome_ref TEXT,                          -- verified terminal row (dispatcher-checked)
  cost_usd REAL,
  resolved_at TEXT
);

-- ----------------------------------------------------------------------------
-- Part 4 — The Record + coordination lanes
-- ----------------------------------------------------------------------------

CREATE TABLE agent_actions (
  id TEXT PRIMARY KEY,
  scope_id TEXT NOT NULL,
  role_key TEXT NOT NULL,
  action_type TEXT NOT NULL,
  input TEXT, output TEXT,                   -- JSON; include chain-of-thought where available
  model TEXT, tokens_in INTEGER, tokens_out INTEGER, cost_usd REAL,
  duty_run_id TEXT,                          -- coverage linkage (AP-9 defense)
  outcome_type TEXT NOT NULL DEFAULT 'none', -- canonical domain vocabulary
  outcome_value TEXT,                        -- ref of the verified terminal row
  created_at TEXT NOT NULL
);

CREATE TABLE executive_requests (            -- the ONLY path for unowned levers
  id TEXT PRIMARY KEY,
  scope_id TEXT NOT NULL,
  from_role TEXT NOT NULL, to_role TEXT NOT NULL,
  lever TEXT NOT NULL, params TEXT, rationale TEXT,
  status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open','accepted','rejected','expired')),
  resolved_by TEXT,
  CHECK (status = 'open' OR resolved_by IS NOT NULL),   -- no silent terminal states (AP-7)
  resolution_note TEXT,
  created_at TEXT NOT NULL, resolved_at TEXT
);

CREATE TABLE validation_log (
  id TEXT PRIMARY KEY,
  scope_id TEXT NOT NULL,
  artifact_kind TEXT NOT NULL, artifact_ref TEXT,
  passed INTEGER NOT NULL,
  hard_block_rule TEXT,
  failed_lens TEXT, score REAL, revision_count INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL
);

CREATE TABLE agent_events (
  id TEXT PRIMARY KEY,
  scope_id TEXT,
  event_type TEXT NOT NULL,                  -- MUST come from the exported typed union (AP-3)
  payload TEXT,
  status TEXT NOT NULL DEFAULT 'new' CHECK (status IN ('new','claimed','consumed','dead_letter')),
  consumer_duty TEXT,                        -- the duty that names/consumes it
  created_at TEXT NOT NULL, consumed_at TEXT
);

CREATE TABLE approval_queue (
  id TEXT PRIMARY KEY,
  scope_id TEXT NOT NULL,
  kind TEXT NOT NULL, payload TEXT, preview TEXT,
  tier INTEGER NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','approved','rejected','expired')),
  decided_by TEXT,                           -- 'system:expired' counts; NULL forbidden on terminal:
  CHECK (status = 'pending' OR decided_by IS NOT NULL),
  decided_at TEXT, expires_at TEXT, created_at TEXT NOT NULL
);

-- ----------------------------------------------------------------------------
-- Part 6 — The Accountability Loop
-- ----------------------------------------------------------------------------

CREATE TABLE executive_decisions (
  id TEXT PRIMARY KEY,
  scope_id TEXT NOT NULL, role_key TEXT NOT NULL,
  input_metrics TEXT,
  decision TEXT NOT NULL, rationale TEXT NOT NULL,
  lever TEXT, params TEXT,
  action_ref TEXT,                           -- agent_actions.id (AP-9 defense)
  expected_effect TEXT NOT NULL,             -- {metric ∈ lever.expected_metrics, baseline (live),
                                             --  predicted (≠ baseline), horizon_days}
  tier INTEGER NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('proposed','committed','executed','unfalsifiable','skipped')),
  measured_effect TEXT,                      -- {actual, hit, measured_at} — grader-written only
  created_at TEXT NOT NULL
);

CREATE TABLE agent_grades (
  id TEXT PRIMARY KEY,
  scope_id TEXT NOT NULL, role_key TEXT NOT NULL,
  grade TEXT NOT NULL, basis TEXT,
  graded_by TEXT NOT NULL,                   -- must differ from role_key (grade the graders)
  period TEXT, created_at TEXT NOT NULL
);

CREATE TABLE ledger_rules (                  -- binding lessons (Part 6 §6.7)
  id TEXT PRIMARY KEY,
  scope_id TEXT NOT NULL, role_key TEXT NOT NULL,
  rule TEXT NOT NULL,
  seeded_from TEXT,                          -- gate_failure | human_correction | retrospective
  source TEXT NOT NULL DEFAULT 'agent' CHECK (source IN ('agent','human')),
  active INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL
);

-- ----------------------------------------------------------------------------
-- Part 8 — The Constitution Plane
-- ----------------------------------------------------------------------------

CREATE TABLE bos_config (
  scope_kind TEXT NOT NULL DEFAULT 'tenant', scope_id TEXT,
  key TEXT NOT NULL,
  value TEXT NOT NULL,
  source TEXT NOT NULL CHECK (source IN ('default','agent','boardroom','human')),
  set_by TEXT, set_at TEXT NOT NULL,
  PRIMARY KEY (scope_kind, scope_id, key)
);

CREATE TABLE config_snapshots (              -- APPEND on every write, no exceptions (AP-4)
  id TEXT PRIMARY KEY,
  scope_kind TEXT, scope_id TEXT, key TEXT NOT NULL,
  old_value TEXT, new_value TEXT NOT NULL,
  source TEXT NOT NULL, set_by TEXT, set_at TEXT NOT NULL
);

CREATE TABLE human_directives (
  id TEXT PRIMARY KEY,
  scope_id TEXT NOT NULL,
  directive TEXT NOT NULL,
  addressed_to TEXT NOT NULL,                -- role_key
  translated_into TEXT,                      -- lever pull ref | executive_requests.id
  status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open','translated','declined','expired')),
  issued_by TEXT NOT NULL, created_at TEXT NOT NULL, resolved_at TEXT
);

-- ----------------------------------------------------------------------------
-- Part 7 — Resilience state
-- ----------------------------------------------------------------------------

CREATE TABLE circuit_breakers (
  dependency TEXT PRIMARY KEY,               -- 'llm', 'github', 'ci', vendor names…
  state TEXT NOT NULL DEFAULT 'closed' CHECK (state IN ('closed','open','half_open')),
  consecutive_failures INTEGER NOT NULL DEFAULT 0,
  trip_count INTEGER NOT NULL DEFAULT 0,     -- 0 forever = dead mechanism; test-trip at install
  last_transition_at TEXT, cooldown_until TEXT
);

CREATE TABLE trust_tiers (                   -- earned autonomy per (scope, role, task_type)
  scope_id TEXT NOT NULL, role_key TEXT NOT NULL, task_type TEXT NOT NULL,
  tier INTEGER NOT NULL DEFAULT 2 CHECK (tier IN (0,1,2)),
  approvals INTEGER NOT NULL DEFAULT 0, approval_rate REAL,
  last_demotion_at TEXT, demotion_reason TEXT,
  updated_at TEXT NOT NULL,
  PRIMARY KEY (scope_id, role_key, task_type)
);
-- NOTE: the pinned set (NEVER_AUTO) is intentionally NOT a table.
-- It is a code constant, checked after every mode gate (Part 7 §7.1).
