PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;
CREATE TABLE meta (key TEXT PRIMARY KEY, value TEXT NOT NULL) STRICT;
CREATE TABLE events (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  id TEXT NOT NULL UNIQUE,
  at TEXT NOT NULL,
  business TEXT NOT NULL,
  kind TEXT NOT NULL CHECK (kind IN (
    'session.launched',
    'session.ended',
    'action.allowed',
    'action.refused',
    'task.created',
    'task.assigned',
    'task.state',
    'task.closed',
    'task.changed.external',
    'task.conflict',
    'task.missing',
    'review.requested',
    'review.verdict',
    'human.input',
    'config.refused',
    'credential.expiring',
    'credential.expired',
    'credential.changed',
    'launch.revoked',
    'launch.restored',
    'digest.sent')),
  actor_role TEXT, actor_run TEXT,
  subject TEXT,
  corrects TEXT REFERENCES events(id),
  body TEXT NOT NULL CHECK (json_valid(body))
) STRICT;
CREATE TABLE role_claims (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  at TEXT NOT NULL,
  business TEXT NOT NULL, role TEXT NOT NULL,
  op TEXT NOT NULL CHECK (op IN ('claim','release','revoke')),
  holder_run TEXT NOT NULL,
  harness TEXT NOT NULL, address TEXT,
  by TEXT NOT NULL, reason TEXT,
  decision TEXT
) STRICT;
CREATE TABLE decisions (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  id TEXT NOT NULL UNIQUE,
  at TEXT NOT NULL,
  business TEXT NOT NULL, project TEXT,
  raised_by_role TEXT NOT NULL, raised_by_run TEXT NOT NULL,
  class TEXT NOT NULL CHECK (class IN ('routine','within-role','cross-role','gated')),
  action TEXT NOT NULL,
  route_to TEXT NOT NULL,
  question TEXT NOT NULL,
  options TEXT NOT NULL CHECK (json_valid(options) AND json_array_length(options) BETWEEN 2 AND 9),
  recommendation TEXT NOT NULL,
  task_ref TEXT CHECK (task_ref IS NULL OR (task_ref GLOB 'vikunja:[1-9]*/[1-9]*' AND NOT substr(task_ref, 9) GLOB '*[^0-9/]*' AND NOT substr(task_ref, 9) GLOB '*/*/*')), requirement_ref TEXT,
  blocking INTEGER NOT NULL CHECK (blocking IN (0,1)),
  supersedes TEXT REFERENCES decisions(id)
, spec_path TEXT, spec_commit TEXT) STRICT;
CREATE TABLE decision_events (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  decision TEXT NOT NULL REFERENCES decisions(id),
  at TEXT NOT NULL,
  op TEXT NOT NULL CHECK (op IN ('seen','resolved','withdrawn','expired')),
  by TEXT NOT NULL,
  choice TEXT, note TEXT, via TEXT
) STRICT;
CREATE TABLE messages (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  id TEXT NOT NULL UNIQUE,
  at TEXT NOT NULL,
  business TEXT NOT NULL,
  from_role TEXT NOT NULL, from_run TEXT NOT NULL,
  to_role TEXT NOT NULL,
  class TEXT NOT NULL,
  in_reply_to TEXT REFERENCES messages(id),
  decision TEXT REFERENCES decisions(id),
  corrects TEXT REFERENCES messages(id),
  body TEXT NOT NULL
, to_name TEXT) STRICT;
CREATE TABLE deliveries (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  message TEXT NOT NULL REFERENCES messages(id),
  at TEXT NOT NULL,
  op TEXT NOT NULL CHECK (op IN ('routed','delivered','failed','read')),
  holder_run TEXT, transport TEXT, address TEXT, detail TEXT
) STRICT;
CREATE TABLE task_snapshots (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  at TEXT NOT NULL,
  business TEXT NOT NULL,
  task_ref TEXT NOT NULL CHECK (task_ref GLOB 'vikunja:[1-9]*/[1-9]*' AND NOT substr(task_ref, 9) GLOB '*[^0-9/]*' AND NOT substr(task_ref, 9) GLOB '*/*/*'),
  updated TEXT NOT NULL,
  etag TEXT,
  digest TEXT NOT NULL CHECK (length(digest) = 64 AND NOT digest GLOB '*[^0-9a-f]*'),
  fields TEXT NOT NULL CHECK (json_valid(fields)),
  source TEXT NOT NULL CHECK (source IN ('self','poll')),
  via TEXT CHECK (via IN ('board','cursor','task','reconcile')),
  read_at TEXT,
  role TEXT, run TEXT,
  CHECK ((source = 'self') = (role IS NOT NULL AND run IS NOT NULL)),
  CHECK ((source = 'poll') = (via IS NOT NULL AND read_at IS NOT NULL)),
  CHECK (json_type(fields, '$.bucket') IS 'integer'
         OR (source IS 'poll' AND via IS 'task' AND json_type(fields, '$.gone') IS 'text'))
) STRICT;
CREATE INDEX task_snapshots_ref ON task_snapshots (business, task_ref, seq);
CREATE TRIGGER decisions_resolve_once BEFORE INSERT ON decision_events
  WHEN NEW.op IN ('resolved','withdrawn','expired') AND EXISTS (
    SELECT 1 FROM decision_events WHERE decision = NEW.decision AND op IN ('resolved','withdrawn','expired'))
  BEGIN SELECT RAISE(ABORT, 'decision already closed'); END;
CREATE TRIGGER decisions_resolved_choice BEFORE INSERT ON decision_events
  WHEN NEW.op = 'resolved' AND (NEW.choice IS NULL OR NOT EXISTS (
    SELECT 1 FROM decisions d, json_each(d.options) o WHERE d.id = NEW.decision AND json_extract(o.value,'$.key') = NEW.choice))
  BEGIN SELECT RAISE(ABORT, 'resolution must name one of the options'); END;
CREATE TRIGGER role_one_holder BEFORE INSERT ON role_claims
  WHEN NEW.op = 'claim' AND (SELECT op FROM role_claims WHERE business = NEW.business AND role = NEW.role ORDER BY seq DESC LIMIT 1) = 'claim'
  BEGIN SELECT RAISE(ABORT, 'role already held'); END;
CREATE TRIGGER role_release_by_holder BEFORE INSERT ON role_claims
  WHEN NEW.op IN ('release','revoke') AND COALESCE((SELECT op FROM role_claims WHERE business = NEW.business AND role = NEW.role ORDER BY seq DESC LIMIT 1),'') <> 'claim'
  BEGIN SELECT RAISE(ABORT, 'role is not held'); END;
CREATE TRIGGER role_release_same_run BEFORE INSERT ON role_claims
  WHEN NEW.op = 'release' AND (SELECT holder_run FROM role_claims WHERE business = NEW.business AND role = NEW.role ORDER BY seq DESC LIMIT 1) <> NEW.holder_run
  BEGIN SELECT RAISE(ABORT, 'only the holder releases; others revoke'); END;
CREATE TRIGGER role_revoke_needs_decision BEFORE INSERT ON role_claims
  WHEN NEW.op = 'revoke' AND NEW.decision IS NULL AND NEW.reason IS NULL
  BEGIN SELECT RAISE(ABORT, 'revoke needs a resolved decision or a reason'); END;
CREATE TRIGGER meta_no_update BEFORE UPDATE ON meta BEGIN SELECT RAISE(ABORT, 'meta is append-only'); END;
CREATE TRIGGER meta_no_delete BEFORE DELETE ON meta BEGIN SELECT RAISE(ABORT, 'meta is append-only'); END;
CREATE TRIGGER events_no_update BEFORE UPDATE ON events BEGIN SELECT RAISE(ABORT, 'events is append-only'); END;
CREATE TRIGGER events_no_delete BEFORE DELETE ON events BEGIN SELECT RAISE(ABORT, 'events is append-only'); END;
CREATE TRIGGER role_claims_no_update BEFORE UPDATE ON role_claims BEGIN SELECT RAISE(ABORT, 'role_claims is append-only'); END;
CREATE TRIGGER role_claims_no_delete BEFORE DELETE ON role_claims BEGIN SELECT RAISE(ABORT, 'role_claims is append-only'); END;
CREATE TRIGGER decisions_no_update BEFORE UPDATE ON decisions BEGIN SELECT RAISE(ABORT, 'decisions is append-only'); END;
CREATE TRIGGER decisions_no_delete BEFORE DELETE ON decisions BEGIN SELECT RAISE(ABORT, 'decisions is append-only'); END;
CREATE TRIGGER decision_events_no_update BEFORE UPDATE ON decision_events BEGIN SELECT RAISE(ABORT, 'decision_events is append-only'); END;
CREATE TRIGGER decision_events_no_delete BEFORE DELETE ON decision_events BEGIN SELECT RAISE(ABORT, 'decision_events is append-only'); END;
CREATE TRIGGER messages_no_update BEFORE UPDATE ON messages BEGIN SELECT RAISE(ABORT, 'messages is append-only'); END;
CREATE TRIGGER messages_no_delete BEFORE DELETE ON messages BEGIN SELECT RAISE(ABORT, 'messages is append-only'); END;
CREATE TRIGGER deliveries_no_update BEFORE UPDATE ON deliveries BEGIN SELECT RAISE(ABORT, 'deliveries is append-only'); END;
CREATE TRIGGER deliveries_no_delete BEFORE DELETE ON deliveries BEGIN SELECT RAISE(ABORT, 'deliveries is append-only'); END;
CREATE TRIGGER meta_no_replace BEFORE INSERT ON meta WHEN EXISTS (SELECT 1 FROM meta WHERE key = NEW.key) BEGIN SELECT RAISE(ABORT, 'meta is append-only'); END;
CREATE TRIGGER events_no_replace BEFORE INSERT ON events WHEN EXISTS (SELECT 1 FROM events WHERE seq = NEW.seq OR id = NEW.id) BEGIN SELECT RAISE(ABORT, 'events is append-only'); END;
CREATE TRIGGER role_claims_no_replace BEFORE INSERT ON role_claims WHEN EXISTS (SELECT 1 FROM role_claims WHERE seq = NEW.seq) BEGIN SELECT RAISE(ABORT, 'role_claims is append-only'); END;
CREATE TRIGGER decisions_no_replace BEFORE INSERT ON decisions WHEN EXISTS (SELECT 1 FROM decisions WHERE seq = NEW.seq OR id = NEW.id) BEGIN SELECT RAISE(ABORT, 'decisions is append-only'); END;
CREATE TRIGGER decision_events_no_replace BEFORE INSERT ON decision_events WHEN EXISTS (SELECT 1 FROM decision_events WHERE seq = NEW.seq) BEGIN SELECT RAISE(ABORT, 'decision_events is append-only'); END;
CREATE TRIGGER messages_no_replace BEFORE INSERT ON messages WHEN EXISTS (SELECT 1 FROM messages WHERE seq = NEW.seq OR id = NEW.id) BEGIN SELECT RAISE(ABORT, 'messages is append-only'); END;
CREATE TRIGGER deliveries_no_replace BEFORE INSERT ON deliveries WHEN EXISTS (SELECT 1 FROM deliveries WHERE seq = NEW.seq) BEGIN SELECT RAISE(ABORT, 'deliveries is append-only'); END;
CREATE TRIGGER task_snapshots_no_update BEFORE UPDATE ON task_snapshots BEGIN SELECT RAISE(ABORT, 'task_snapshots is append-only'); END;
CREATE TRIGGER task_snapshots_no_delete BEFORE DELETE ON task_snapshots BEGIN SELECT RAISE(ABORT, 'task_snapshots is append-only'); END;
CREATE TRIGGER task_snapshots_no_replace BEFORE INSERT ON task_snapshots WHEN EXISTS (SELECT 1 FROM task_snapshots WHERE seq = NEW.seq) BEGIN SELECT RAISE(ABORT, 'task_snapshots is append-only'); END;
CREATE TRIGGER decisions_blocking_needs_task BEFORE INSERT ON decisions
  WHEN NEW.blocking = 1 AND NEW.task_ref IS NULL
  BEGIN SELECT RAISE(ABORT, 'a blocking decision cites the task it blocks'); END;
CREATE TRIGGER events_launch_by_human BEFORE INSERT ON events
  WHEN NEW.kind IN ('launch.revoked','launch.restored') AND (NEW.actor_role IS NOT NULL OR NEW.actor_run IS NOT NULL)
  BEGIN SELECT RAISE(ABORT, 'only the human revokes or restores launching'); END;
CREATE TRIGGER events_credential_body BEFORE INSERT ON events
  WHEN NEW.kind GLOB 'credential.*' AND (
    json_extract(NEW.body, '$.service') IS NULL OR json_extract(NEW.body, '$.service') NOT IN ('gitea','vikunja')
    OR json_extract(NEW.body, '$.instance') IS NULL)
  BEGIN SELECT RAISE(ABORT, 'credential events name a service and a role instance'); END;
CREATE TRIGGER events_task_missing_body BEFORE INSERT ON events
  WHEN NEW.kind = 'task.missing' AND (
    json_extract(NEW.body, '$.reason') IS NULL OR json_extract(NEW.body, '$.reason') NOT IN ('moved','not-found','no-access')
    OR (json_extract(NEW.body, '$.reason') = 'moved' AND json_type(NEW.body, '$.project') IS NOT 'integer'))
  BEGIN SELECT RAISE(ABORT, 'task.missing carries a reason, and the new project when moved'); END;
CREATE TRIGGER events_task_subject BEFORE INSERT ON events
  WHEN NEW.kind GLOB 'task.*' AND (NEW.subject IS NULL OR NOT (NEW.subject GLOB 'vikunja:[1-9]*/[1-9]*' AND NOT substr(NEW.subject, 9) GLOB '*[^0-9/]*' AND NOT substr(NEW.subject, 9) GLOB '*/*/*'))
  BEGIN SELECT RAISE(ABORT, 'a task event names its task in subject'); END;
CREATE TRIGGER events_task_created_body BEFORE INSERT ON events
  WHEN NEW.kind = 'task.created' AND (
    json_type(NEW.body, '$.request') IS NOT 'text'
    OR NOT EXISTS (SELECT 1 FROM events WHERE id = json_extract(NEW.body, '$.request')
                   AND kind = 'human.input' AND business = NEW.business)
    OR json_type(NEW.body, '$.requirement') IS NOT 'text'
    OR NOT json_extract(NEW.body, '$.requirement') GLOB 'REQ-[A-Z]*-[1-9]*'
    OR json_extract(NEW.body, '$.requirement') GLOB 'REQ-*[^A-Z0-9-]*')
  BEGIN SELECT RAISE(ABORT, 'task.created cites the human.input that asked for it and a requirement id'); END;
CREATE VIEW launch_state AS
  SELECT business, CASE kind WHEN 'launch.revoked' THEN 'revoked' ELSE 'allowed' END AS state, at
  FROM events e WHERE kind IN ('launch.revoked','launch.restored')
    AND seq = (SELECT max(seq) FROM events WHERE business = e.business AND kind IN ('launch.revoked','launch.restored'));
CREATE VIEW task_external_changes AS
  SELECT p.business, p.task_ref, p.seq, p.via, p.updated, p.digest, ls.digest AS self_digest
  FROM task_snapshots p
  LEFT JOIN task_snapshots ls ON ls.seq = (SELECT max(seq) FROM task_snapshots
                                           WHERE business = p.business AND task_ref = p.task_ref AND source = 'self')
  WHERE p.source = 'poll'
    AND p.seq = (SELECT max(seq) FROM task_snapshots WHERE business = p.business AND task_ref = p.task_ref)
    AND (ls.seq IS NULL OR (p.updated >= ls.updated AND p.read_at > ls.at AND p.digest <> ls.digest));
CREATE VIEW task_current AS
  SELECT s.seq, s.at, s.business, s.task_ref, s.updated, s.etag, s.digest, s.fields, s.source, s.via, s.read_at, s.role, s.run
  FROM task_snapshots s
  WHERE s.seq = (SELECT max(c.seq) FROM task_snapshots c
                 WHERE c.business = s.business AND c.task_ref = s.task_ref
                   AND (c.source = 'self'
                        OR c.read_at > coalesce((SELECT ls.at FROM task_snapshots ls
                                                 WHERE ls.business = c.business AND ls.task_ref = c.task_ref AND ls.source = 'self'
                                                 ORDER BY ls.seq DESC LIMIT 1), '')));
CREATE VIEW tasks_open AS
  SELECT s.business, s.task_ref, json_extract(s.fields, '$.bucket') AS bucket, s.seq
  FROM task_current s
  WHERE json_type(s.fields, '$.gone') IS NULL
    AND json_extract(s.fields, '$.done') = 0;
CREATE VIEW urgent_inbox AS
  SELECT d.id, d.business, d.task_ref, d.question, d.at
  FROM decisions d
  WHERE d.class = 'gated' AND d.blocking = 1
    AND NOT EXISTS (SELECT 1 FROM decision_events x WHERE x.decision = d.id AND x.op IN ('resolved','withdrawn','expired'));
CREATE TABLE task_comment_cursors (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  business TEXT NOT NULL,
  task_id INTEGER NOT NULL CHECK (task_id > 0),
  last_comment INTEGER NOT NULL CHECK (last_comment > 0)
) STRICT;
CREATE INDEX task_comment_cursors_latest ON task_comment_cursors (business, task_id, seq);
CREATE TRIGGER task_comment_cursors_no_update BEFORE UPDATE ON task_comment_cursors
  BEGIN SELECT RAISE(ABORT, 'task_comment_cursors is append-only'); END;
CREATE TRIGGER task_comment_cursors_no_delete BEFORE DELETE ON task_comment_cursors
  BEGIN SELECT RAISE(ABORT, 'task_comment_cursors is append-only'); END;
CREATE TRIGGER task_comment_cursors_no_replace BEFORE INSERT ON task_comment_cursors
  WHEN EXISTS (SELECT 1 FROM task_comment_cursors WHERE seq = NEW.seq)
  BEGIN SELECT RAISE(ABORT, 'task_comment_cursors is append-only'); END;
CREATE TRIGGER task_comment_cursors_forward BEFORE INSERT ON task_comment_cursors
  WHEN NEW.last_comment <= COALESCE((SELECT last_comment FROM task_comment_cursors
    WHERE business = NEW.business AND task_id = NEW.task_id ORDER BY seq DESC LIMIT 1), 0)
  BEGIN SELECT RAISE(ABORT, 'comment cursor must advance'); END;
CREATE TABLE projects (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  id TEXT NOT NULL UNIQUE,
  at TEXT NOT NULL,
  business TEXT NOT NULL,
  name TEXT NOT NULL,
  repo TEXT,
  created_by TEXT NOT NULL,
  UNIQUE (business, name)
) STRICT;
CREATE TABLE threads (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  id TEXT NOT NULL UNIQUE,
  at TEXT NOT NULL,
  business TEXT NOT NULL,
  project TEXT NOT NULL REFERENCES projects(id),
  instance TEXT NOT NULL,
  owner TEXT NOT NULL,
  name TEXT NOT NULL,
  UNIQUE (project, name)
) STRICT;
CREATE TABLE sessions (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  id TEXT NOT NULL UNIQUE,
  at TEXT NOT NULL,
  business TEXT NOT NULL,
  thread TEXT NOT NULL REFERENCES threads(id),
  instance TEXT NOT NULL,
  owner TEXT NOT NULL,
  name TEXT NOT NULL,
  op TEXT NOT NULL,
  predecessor TEXT REFERENCES sessions(id),
  UNIQUE (thread, name),
  UNIQUE (thread, op)
) STRICT;
CREATE TABLE session_defaults (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  at TEXT NOT NULL,
  thread TEXT NOT NULL REFERENCES threads(id),
  session TEXT NOT NULL REFERENCES sessions(id),
  by TEXT NOT NULL
) STRICT;
CREATE TABLE thread_claims (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  at TEXT NOT NULL,
  business TEXT NOT NULL,
  thread TEXT NOT NULL REFERENCES threads(id),
  session TEXT NOT NULL REFERENCES sessions(id),
  generation INTEGER NOT NULL CHECK (generation >= 1),
  op TEXT NOT NULL CHECK (op IN ('claim','release','revoke')),
  holder_run TEXT NOT NULL,
  harness TEXT NOT NULL, address TEXT,
  by TEXT NOT NULL, reason TEXT,
  decision TEXT
) STRICT;
CREATE TABLE message_threads (
  message TEXT PRIMARY KEY REFERENCES messages(id),
  thread TEXT NOT NULL REFERENCES threads(id),
  session TEXT NOT NULL REFERENCES sessions(id)
) STRICT;
CREATE TABLE thread_opens (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  at TEXT NOT NULL,
  thread TEXT NOT NULL REFERENCES threads(id),
  session TEXT NOT NULL REFERENCES sessions(id),
  by TEXT NOT NULL
) STRICT;
CREATE INDEX thread_claims_thread ON thread_claims (thread, seq);
CREATE INDEX thread_claims_session ON thread_claims (session, seq);
CREATE INDEX message_threads_thread ON message_threads (thread);
CREATE INDEX message_threads_session ON message_threads (session);
CREATE INDEX session_defaults_thread ON session_defaults (thread, seq);
CREATE TRIGGER threads_project_business BEFORE INSERT ON threads
  WHEN (SELECT business FROM projects WHERE id = NEW.project) IS NOT NEW.business
  BEGIN SELECT RAISE(ABORT, 'a thread belongs to its project''s business'); END;
CREATE TRIGGER sessions_thread_match BEFORE INSERT ON sessions
  WHEN NOT EXISTS (SELECT 1 FROM threads t WHERE t.id = NEW.thread AND t.business = NEW.business AND t.instance = NEW.instance AND t.owner = NEW.owner)
  BEGIN SELECT RAISE(ABORT, 'a session has its thread''s business, instance and owner'); END;
CREATE TRIGGER sessions_predecessor BEFORE INSERT ON sessions
  WHEN NEW.predecessor IS NOT NULL AND (SELECT thread FROM sessions WHERE id = NEW.predecessor) IS NOT NEW.thread
  BEGIN SELECT RAISE(ABORT, 'a session''s predecessor is in its thread'); END;
CREATE TRIGGER sessions_id_not_thread BEFORE INSERT ON sessions
  WHEN NEW.id IS NOT NEW.thread AND EXISTS (SELECT 1 FROM threads WHERE id = NEW.id)
  BEGIN SELECT RAISE(ABORT, 'a session id names another thread'); END;
CREATE TRIGGER threads_id_not_session BEFORE INSERT ON threads
  WHEN EXISTS (SELECT 1 FROM sessions WHERE id = NEW.id)
  BEGIN SELECT RAISE(ABORT, 'a thread id names a session'); END;
CREATE TRIGGER session_defaults_match BEFORE INSERT ON session_defaults
  WHEN (SELECT thread FROM sessions WHERE id = NEW.session) IS NOT NEW.thread
  BEGIN SELECT RAISE(ABORT, 'a default session is in its thread'); END;
CREATE TRIGGER thread_one_holder BEFORE INSERT ON thread_claims
  WHEN NEW.op = 'claim' AND (SELECT op FROM thread_claims WHERE session = NEW.session ORDER BY seq DESC LIMIT 1) = 'claim'
  BEGIN SELECT RAISE(ABORT, 'session already held'); END;
CREATE TRIGGER thread_one_per_run BEFORE INSERT ON thread_claims
  WHEN NEW.op = 'claim' AND EXISTS (SELECT 1 FROM thread_claims c WHERE c.holder_run = NEW.holder_run AND c.business = NEW.business
    AND c.seq = (SELECT max(seq) FROM thread_claims WHERE session = c.session) AND c.op = 'claim')
  BEGIN SELECT RAISE(ABORT, 'run already holds a session'); END;
CREATE TRIGGER thread_release_by_holder BEFORE INSERT ON thread_claims
  WHEN NEW.op IN ('release','revoke') AND COALESCE((SELECT op FROM thread_claims WHERE session = NEW.session ORDER BY seq DESC LIMIT 1),'') <> 'claim'
  BEGIN SELECT RAISE(ABORT, 'session is not held'); END;
CREATE TRIGGER thread_release_same_run BEFORE INSERT ON thread_claims
  WHEN NEW.op = 'release' AND (SELECT holder_run FROM thread_claims WHERE session = NEW.session ORDER BY seq DESC LIMIT 1) <> NEW.holder_run
  BEGIN SELECT RAISE(ABORT, 'only the holder releases; others revoke'); END;
CREATE TRIGGER thread_claim_generation BEFORE INSERT ON thread_claims
  WHEN NEW.generation IS NOT (CASE WHEN NEW.op = 'claim'
    THEN 1 + (SELECT count(*) FROM thread_claims WHERE session = NEW.session AND op = 'claim')
    ELSE (SELECT generation FROM thread_claims WHERE session = NEW.session ORDER BY seq DESC LIMIT 1) END)
  BEGIN SELECT RAISE(ABORT, 'a claim row names its session''s generation'); END;
CREATE TRIGGER thread_revoke_needs_decision BEFORE INSERT ON thread_claims
  WHEN NEW.op = 'revoke' AND NEW.decision IS NULL AND NEW.reason IS NULL
  BEGIN SELECT RAISE(ABORT, 'revoke needs a resolved decision or a reason'); END;
CREATE TRIGGER thread_claims_business BEFORE INSERT ON thread_claims
  WHEN (SELECT business FROM threads WHERE id = NEW.thread) IS NOT NEW.business
  BEGIN SELECT RAISE(ABORT, 'a thread claim names the thread''s business'); END;
CREATE TRIGGER thread_claims_session BEFORE INSERT ON thread_claims
  WHEN (SELECT thread FROM sessions WHERE id = NEW.session) IS NOT NEW.thread
  BEGIN SELECT RAISE(ABORT, 'a claim''s session is in its thread'); END;
CREATE TRIGGER message_threads_match BEFORE INSERT ON message_threads
  WHEN NOT EXISTS (SELECT 1 FROM messages m JOIN threads t ON t.id = NEW.thread
    WHERE m.id = NEW.message AND m.business = t.business AND t.instance IN (m.to_role, m.from_role))
  BEGIN SELECT RAISE(ABORT, 'a threaded message is to or from the thread''s instance'); END;
CREATE TRIGGER message_threads_session BEFORE INSERT ON message_threads
  WHEN (SELECT thread FROM sessions WHERE id = NEW.session) IS NOT NEW.thread
  BEGIN SELECT RAISE(ABORT, 'a threaded message''s session is in its thread'); END;
CREATE TRIGGER thread_opens_session BEFORE INSERT ON thread_opens
  WHEN (SELECT thread FROM sessions WHERE id = NEW.session) IS NOT NEW.thread
  BEGIN SELECT RAISE(ABORT, 'an open''s session is in its thread'); END;
CREATE TRIGGER delivery_same_session BEFORE INSERT ON deliveries
  WHEN NEW.op IN ('routed','delivered') AND (NEW.op = 'delivered' OR NEW.holder_run IS NOT NULL)
    AND EXISTS (SELECT 1 FROM message_threads mt JOIN messages m ON m.id = mt.message JOIN threads t ON t.id = mt.thread
      WHERE mt.message = NEW.message AND m.to_role = t.instance
        AND NOT EXISTS (SELECT 1 FROM thread_claims c WHERE c.seq = (SELECT max(seq) FROM thread_claims WHERE session = mt.session)
          AND c.op = 'claim' AND c.holder_run IS NEW.holder_run))
  BEGIN SELECT RAISE(ABORT, 'a threaded message goes only to its session''s holder'); END;
CREATE TRIGGER projects_no_update BEFORE UPDATE ON projects BEGIN SELECT RAISE(ABORT, 'projects is append-only'); END;
CREATE TRIGGER projects_no_delete BEFORE DELETE ON projects BEGIN SELECT RAISE(ABORT, 'projects is append-only'); END;
CREATE TRIGGER projects_no_replace BEFORE INSERT ON projects WHEN EXISTS (SELECT 1 FROM projects WHERE seq = NEW.seq OR id = NEW.id) BEGIN SELECT RAISE(ABORT, 'projects is append-only'); END;
-- #797 MVP3: the owner's reversible project archive. A project's state is the op of its latest
-- row, and the rows are the audit trail (who archived or restored it, and when). The projects
-- table itself stays as written; an archived project keeps every record and is never deleted.
CREATE TABLE project_archives (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  at TEXT NOT NULL,
  business TEXT NOT NULL,
  project TEXT NOT NULL REFERENCES projects(id),
  op TEXT NOT NULL CHECK (op IN ('archive','unarchive')),
  by TEXT NOT NULL
) STRICT;
CREATE INDEX project_archives_project ON project_archives (business, project, seq);
CREATE TRIGGER project_archives_no_update BEFORE UPDATE ON project_archives BEGIN SELECT RAISE(ABORT, 'project_archives is append-only'); END;
CREATE TRIGGER project_archives_no_delete BEFORE DELETE ON project_archives BEGIN SELECT RAISE(ABORT, 'project_archives is append-only'); END;
CREATE TRIGGER project_archives_no_replace BEFORE INSERT ON project_archives WHEN EXISTS (SELECT 1 FROM project_archives WHERE seq = NEW.seq) BEGIN SELECT RAISE(ABORT, 'project_archives is append-only'); END;
CREATE TRIGGER project_archives_business BEFORE INSERT ON project_archives
  WHEN (SELECT business FROM projects WHERE id = NEW.project) IS NOT NEW.business
  BEGIN SELECT RAISE(ABORT, 'an archive row belongs to its project''s business'); END;
CREATE TRIGGER project_archives_no_repeat BEFORE INSERT ON project_archives
  WHEN (SELECT op FROM project_archives WHERE business = NEW.business AND project = NEW.project ORDER BY seq DESC LIMIT 1) = NEW.op
  BEGIN SELECT RAISE(ABORT, 'a project archive row changes state'); END;
CREATE TRIGGER threads_no_update BEFORE UPDATE ON threads BEGIN SELECT RAISE(ABORT, 'threads is append-only'); END;
CREATE TRIGGER threads_no_delete BEFORE DELETE ON threads BEGIN SELECT RAISE(ABORT, 'threads is append-only'); END;
CREATE TRIGGER threads_no_replace BEFORE INSERT ON threads WHEN EXISTS (SELECT 1 FROM threads WHERE seq = NEW.seq OR id = NEW.id) BEGIN SELECT RAISE(ABORT, 'threads is append-only'); END;
CREATE TRIGGER sessions_no_update BEFORE UPDATE ON sessions BEGIN SELECT RAISE(ABORT, 'sessions is append-only'); END;
CREATE TRIGGER sessions_no_delete BEFORE DELETE ON sessions BEGIN SELECT RAISE(ABORT, 'sessions is append-only'); END;
CREATE TRIGGER sessions_no_replace BEFORE INSERT ON sessions WHEN EXISTS (SELECT 1 FROM sessions WHERE seq = NEW.seq OR id = NEW.id) BEGIN SELECT RAISE(ABORT, 'sessions is append-only'); END;
CREATE TRIGGER session_defaults_no_update BEFORE UPDATE ON session_defaults BEGIN SELECT RAISE(ABORT, 'session_defaults is append-only'); END;
CREATE TRIGGER session_defaults_no_delete BEFORE DELETE ON session_defaults BEGIN SELECT RAISE(ABORT, 'session_defaults is append-only'); END;
CREATE TRIGGER session_defaults_no_replace BEFORE INSERT ON session_defaults WHEN EXISTS (SELECT 1 FROM session_defaults WHERE seq = NEW.seq) BEGIN SELECT RAISE(ABORT, 'session_defaults is append-only'); END;
CREATE TRIGGER thread_claims_no_update BEFORE UPDATE ON thread_claims BEGIN SELECT RAISE(ABORT, 'thread_claims is append-only'); END;
CREATE TRIGGER thread_claims_no_delete BEFORE DELETE ON thread_claims BEGIN SELECT RAISE(ABORT, 'thread_claims is append-only'); END;
CREATE TRIGGER thread_claims_no_replace BEFORE INSERT ON thread_claims WHEN EXISTS (SELECT 1 FROM thread_claims WHERE seq = NEW.seq) BEGIN SELECT RAISE(ABORT, 'thread_claims is append-only'); END;
CREATE TRIGGER message_threads_no_update BEFORE UPDATE ON message_threads BEGIN SELECT RAISE(ABORT, 'message_threads is append-only'); END;
CREATE TRIGGER message_threads_no_delete BEFORE DELETE ON message_threads BEGIN SELECT RAISE(ABORT, 'message_threads is append-only'); END;
CREATE TRIGGER message_threads_no_replace BEFORE INSERT ON message_threads WHEN EXISTS (SELECT 1 FROM message_threads WHERE message = NEW.message) BEGIN SELECT RAISE(ABORT, 'message_threads is append-only'); END;
CREATE TRIGGER thread_opens_no_update BEFORE UPDATE ON thread_opens BEGIN SELECT RAISE(ABORT, 'thread_opens is append-only'); END;
CREATE TRIGGER thread_opens_no_delete BEFORE DELETE ON thread_opens BEGIN SELECT RAISE(ABORT, 'thread_opens is append-only'); END;
CREATE TRIGGER thread_opens_no_replace BEFORE INSERT ON thread_opens WHEN EXISTS (SELECT 1 FROM thread_opens WHERE seq = NEW.seq) BEGIN SELECT RAISE(ABORT, 'thread_opens is append-only'); END;
CREATE TABLE instance_names (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  at TEXT NOT NULL,
  business TEXT NOT NULL,
  instance TEXT NOT NULL,
  name TEXT NOT NULL,
  by TEXT NOT NULL
) STRICT;
CREATE TRIGGER instance_names_unique BEFORE INSERT ON instance_names
  WHEN EXISTS (SELECT 1 FROM instance_names n WHERE n.business = NEW.business AND n.name = NEW.name
    AND n.instance <> NEW.instance
    AND n.seq = (SELECT max(seq) FROM instance_names WHERE business = n.business AND instance = n.instance))
  BEGIN SELECT RAISE(ABORT, 'an instance name is unique in its business'); END;
CREATE TRIGGER instance_names_no_update BEFORE UPDATE ON instance_names BEGIN SELECT RAISE(ABORT, 'instance_names is append-only'); END;
CREATE TRIGGER instance_names_no_delete BEFORE DELETE ON instance_names BEGIN SELECT RAISE(ABORT, 'instance_names is append-only'); END;
CREATE TRIGGER instance_names_no_replace BEFORE INSERT ON instance_names WHEN EXISTS (SELECT 1 FROM instance_names WHERE seq = NEW.seq) BEGIN SELECT RAISE(ABORT, 'instance_names is append-only'); END;
-- Row 124 (#1618): immutable source snapshots and turn outcomes, never transcript text.
CREATE TABLE usage_records (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  at TEXT NOT NULL,
  business TEXT NOT NULL,
  run TEXT NOT NULL,
  instance TEXT NOT NULL,
  provider TEXT,
  model TEXT,
  account TEXT,
  turn TEXT NOT NULL,
  source TEXT NOT NULL,
  phase TEXT NOT NULL CHECK (phase IN ('begin','end')),
  status TEXT NOT NULL CHECK (status IN ('known','unknown')),
  code TEXT,
  cursor TEXT CHECK (cursor IS NULL OR json_valid(cursor)),
  input INTEGER,
  output INTEGER,
  cache_write INTEGER,
  cache_read INTEGER,
  UNIQUE (business,run,turn,source,phase),
  CHECK ((status='unknown' AND input IS NULL AND output IS NULL AND cache_write IS NULL AND cache_read IS NULL)
    OR (status='known' AND input >= 0 AND output >= 0 AND cache_write >= 0 AND cache_read >= 0
      AND input IS NOT NULL AND output IS NOT NULL AND cache_write IS NOT NULL AND cache_read IS NOT NULL))
) STRICT;
CREATE TRIGGER usage_records_no_update BEFORE UPDATE ON usage_records BEGIN SELECT RAISE(ABORT, 'usage_records is append-only'); END;
CREATE TRIGGER usage_records_no_delete BEFORE DELETE ON usage_records BEGIN SELECT RAISE(ABORT, 'usage_records is append-only'); END;
CREATE TRIGGER usage_records_no_replace BEFORE INSERT ON usage_records WHEN EXISTS (SELECT 1 FROM usage_records WHERE seq=NEW.seq OR (business=NEW.business AND run=NEW.run AND turn=NEW.turn AND source=NEW.source AND phase=NEW.phase)) BEGIN SELECT RAISE(ABORT, 'usage_records is append-only'); END;
CREATE TABLE hosts (
  business TEXT NOT NULL,
  host TEXT NOT NULL,
  address TEXT NOT NULL,
  port INTEGER NOT NULL CHECK (port BETWEEN 1 AND 65535),
  user TEXT NOT NULL,
  sudo INTEGER NOT NULL CHECK (sudo IN (0,1)),
  host_keys TEXT NOT NULL CHECK (json_valid(host_keys) AND json_type(host_keys) = 'array' AND json_array_length(host_keys) > 0),
  installer_fingerprint TEXT,
  state TEXT NOT NULL CHECK (state IN ('pending-install','ready')),
  decision TEXT NOT NULL REFERENCES decisions(id),
  checked_at TEXT,
  PRIMARY KEY (business, host),
  CHECK (state = 'pending-install' OR (installer_fingerprint IS NOT NULL AND checked_at IS NOT NULL))
) STRICT;
CREATE TABLE host_grants (
  business TEXT NOT NULL,
  agent TEXT NOT NULL,
  host TEXT NOT NULL,
  commands TEXT NOT NULL CHECK (json_valid(commands) AND json_type(commands) = 'array' AND json_array_length(commands) > 0),
  rotate_by TEXT NOT NULL,
  tag TEXT NOT NULL CHECK (length(tag) = 39 AND tag GLOB 'mosaic-*' AND NOT substr(tag, 8) GLOB '*[^0-9a-f]*'),
  decision TEXT NOT NULL REFERENCES decisions(id),
  PRIMARY KEY (business, agent, host),
  FOREIGN KEY (business, host) REFERENCES hosts(business, host)
) STRICT;
CREATE TRIGGER events_grant_actor BEFORE INSERT ON events
  WHEN NEW.kind = 'action.allowed'
    AND (json_extract(NEW.body, '$.action') GLOB 'grant.*' OR json_extract(NEW.body, '$.operation') GLOB 'grant.*')
    AND NOT (NEW.actor_role IS NULL AND NEW.actor_run IS 'grant')
  BEGIN SELECT RAISE(ABORT, 'only the grant writer records grant events'); END;
CREATE TABLE gitea_users (
  business TEXT NOT NULL,
  agent TEXT NOT NULL,
  user TEXT NOT NULL,
  id INTEGER NOT NULL CHECK (id > 0),
  nonce TEXT NOT NULL CHECK (length(nonce) = 16 AND NOT nonce GLOB '*[^0-9a-f]*'),
  decision TEXT NOT NULL REFERENCES decisions(id),
  PRIMARY KEY (business, agent)
) STRICT;
CREATE TABLE gitea_grants (
  business TEXT NOT NULL,
  agent TEXT NOT NULL,
  user TEXT NOT NULL,
  token_id INTEGER NOT NULL CHECK (token_id > 0),
  token_name TEXT NOT NULL,
  projects TEXT NOT NULL CHECK (json_valid(projects) AND json_type(projects) = 'array' AND json_array_length(projects) > 0),
  rotate_by TEXT NOT NULL,
  decision TEXT NOT NULL REFERENCES decisions(id),
  PRIMARY KEY (business, agent),
  FOREIGN KEY (business, agent) REFERENCES gitea_users(business, agent)
) STRICT;
-- Row 206 (193b2, #1704, row 193 design 5.2): a thread owner's read-only shares. The latest row per
-- (thread, target) is the share's state. Append-only.
CREATE TABLE thread_shares (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  at TEXT NOT NULL,
  business TEXT NOT NULL,
  thread TEXT NOT NULL REFERENCES threads(id),
  op TEXT NOT NULL CHECK (op IN ('share','unshare')),
  target TEXT NOT NULL,
  by TEXT NOT NULL
) STRICT;
CREATE INDEX thread_shares_thread ON thread_shares (thread, target, seq);
CREATE TRIGGER thread_shares_no_update BEFORE UPDATE ON thread_shares BEGIN SELECT RAISE(ABORT, 'thread_shares is append-only'); END;
CREATE TRIGGER thread_shares_no_delete BEFORE DELETE ON thread_shares BEGIN SELECT RAISE(ABORT, 'thread_shares is append-only'); END;
CREATE TRIGGER thread_shares_no_replace BEFORE INSERT ON thread_shares WHEN EXISTS (SELECT 1 FROM thread_shares WHERE seq = NEW.seq) BEGIN SELECT RAISE(ABORT, 'thread_shares is append-only'); END;
CREATE TABLE session_ops (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  at TEXT NOT NULL,
  business TEXT NOT NULL,
  thread TEXT NOT NULL REFERENCES threads(id),
  session TEXT NOT NULL REFERENCES sessions(id),
  op TEXT NOT NULL,
  stage TEXT NOT NULL CHECK (stage IN ('requested','drained','uncertain','exited','retired','failed','cancelled','start-held','start-started','start-refused')),
  attempt INTEGER NOT NULL CHECK (CASE WHEN stage GLOB 'start-*' THEN attempt >= 1 ELSE attempt = 0 END),
  work_attempt INTEGER CHECK (work_attempt IS NULL OR (stage GLOB 'start-*' AND work_attempt >= 1)),
  successor TEXT NOT NULL,
  successor_name TEXT NOT NULL,
  run TEXT,
  generation INTEGER CHECK (generation IS NULL OR generation >= 1),
  digest TEXT,
  refusal TEXT,
  UNIQUE (thread, op, stage, attempt)
) STRICT;
CREATE TABLE message_cancels (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  at TEXT NOT NULL,
  message TEXT NOT NULL UNIQUE REFERENCES messages(id),
  session TEXT NOT NULL REFERENCES sessions(id),
  op TEXT NOT NULL
) STRICT;
CREATE INDEX session_ops_session ON session_ops (session, seq);
CREATE TRIGGER session_ops_no_update BEFORE UPDATE ON session_ops BEGIN SELECT RAISE(ABORT, 'session_ops is append-only'); END;
CREATE TRIGGER session_ops_no_delete BEFORE DELETE ON session_ops BEGIN SELECT RAISE(ABORT, 'session_ops is append-only'); END;
CREATE TRIGGER session_ops_no_replace BEFORE INSERT ON session_ops WHEN EXISTS (SELECT 1 FROM session_ops WHERE seq = NEW.seq) BEGIN SELECT RAISE(ABORT, 'session_ops is append-only'); END;
CREATE TRIGGER session_ops_one_open BEFORE INSERT ON session_ops
  WHEN NEW.stage = 'requested' AND EXISTS (SELECT 1 FROM session_ops o WHERE o.session = NEW.session
    AND o.seq = (SELECT max(seq) FROM session_ops WHERE thread = o.thread AND op = o.op)
    AND o.stage IN ('requested','drained','uncertain','exited'))
  BEGIN SELECT RAISE(ABORT, 'session has an open clear'); END;
CREATE TRIGGER message_cancels_no_update BEFORE UPDATE ON message_cancels BEGIN SELECT RAISE(ABORT, 'message_cancels is append-only'); END;
CREATE TRIGGER message_cancels_no_delete BEFORE DELETE ON message_cancels BEGIN SELECT RAISE(ABORT, 'message_cancels is append-only'); END;
CREATE TRIGGER message_cancels_no_replace BEFORE INSERT ON message_cancels WHEN EXISTS (SELECT 1 FROM message_cancels WHERE seq = NEW.seq) BEGIN SELECT RAISE(ABORT, 'message_cancels is append-only'); END;
CREATE TABLE request_events (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  business TEXT NOT NULL,
  request TEXT NOT NULL REFERENCES events(id),
  revision INTEGER NOT NULL CHECK (revision >= 1),
  kind TEXT NOT NULL CHECK (kind IN ('opened','selection.proposed','selection.staged','selection.confirmed','setup.wait','bootstrap.stale','bootstrap.completed','prd.submitted','prd.activated','prd.invalidated','question.asked','question.answered','attach.selected','mode.changed','advanced','cancelled')),
  actor_role TEXT, actor_run TEXT,
  at TEXT NOT NULL,
  body TEXT NOT NULL CHECK (json_valid(body) AND json_type(body) = 'object'),
  CHECK ((actor_role IS NULL) = (actor_run IS NULL)),
  UNIQUE (business,request,revision)
) STRICT;
CREATE INDEX request_events_request ON request_events (business,request,seq);
CREATE TRIGGER request_events_origin BEFORE INSERT ON request_events
  WHEN NOT EXISTS (SELECT 1 FROM events e JOIN messages m ON m.id=e.subject AND m.id=json_extract(e.body,'$.message')
    WHERE e.id=NEW.request AND e.business=NEW.business AND m.business=e.business AND e.kind='human.input'
      AND e.actor_role IS NULL AND e.actor_run IS NULL AND json_extract(e.body,'$.kind')='instruction' AND m.from_role='human')
  BEGIN SELECT RAISE(ABORT, 'request-origin-refused'); END;
CREATE TRIGGER request_events_revision BEFORE INSERT ON request_events
  WHEN NEW.revision <> 1 + coalesce((SELECT max(revision) FROM request_events WHERE business=NEW.business AND request=NEW.request),0)
    OR (NEW.revision=1) <> (NEW.kind='opened')
  BEGIN SELECT RAISE(ABORT, 'request-revision-conflict'); END;
CREATE TABLE request_effects (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  at TEXT NOT NULL,
  version INTEGER NOT NULL CHECK (version=1),
  business TEXT NOT NULL,
  request TEXT NOT NULL REFERENCES events(id),
  op TEXT NOT NULL,
  kind TEXT NOT NULL CHECK (kind IN ('bootstrap','prd-submit','prd-activate','task-create','continuation','delivery')),
  digest TEXT NOT NULL CHECK (length(digest)=64 AND digest NOT GLOB '*[^0-9a-f]*'),
  stage TEXT NOT NULL CHECK (stage IN ('prepared','committed','uncertain','refused')),
  result TEXT CHECK (result IS NULL OR (json_valid(result) AND json_type(result)='object')),
  code TEXT,
  CHECK (stage<>'prepared' OR (result IS NULL AND code IS NULL)),
  UNIQUE (business,request,op,stage)
) STRICT;
CREATE TRIGGER request_effects_binding BEFORE INSERT ON request_effects
  WHEN NOT EXISTS (SELECT 1 FROM request_events WHERE business=NEW.business AND request=NEW.request)
    OR EXISTS (SELECT 1 FROM request_effects WHERE business=NEW.business AND request=NEW.request AND op=NEW.op AND (digest<>NEW.digest OR kind<>NEW.kind))
    OR (NEW.stage<>'prepared' AND NOT EXISTS (SELECT 1 FROM request_effects WHERE business=NEW.business AND request=NEW.request AND op=NEW.op AND stage='prepared'))
    OR EXISTS (SELECT 1 FROM request_effects WHERE business=NEW.business AND request=NEW.request AND op=NEW.op AND stage IN ('committed','refused'))
  BEGIN SELECT RAISE(ABORT, 'request-effect-conflict'); END;
CREATE TABLE request_snapshots (
  id TEXT PRIMARY KEY,
  at TEXT NOT NULL,
  business TEXT NOT NULL,
  project TEXT NOT NULL,
  body TEXT NOT NULL CHECK (json_valid(body) AND json_type(body)='object'
    AND json_extract(body,'$.version')=1 AND json_extract(body,'$.origin')='product'
    AND json_extract(body,'$.id')=id AND json_extract(body,'$.business')=business AND json_extract(body,'$.project')=project)
) STRICT;
CREATE TABLE request_snapshot_events (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  at TEXT NOT NULL,
  business TEXT NOT NULL,
  project TEXT NOT NULL,
  revision INTEGER NOT NULL CHECK (revision>=1),
  snapshot TEXT NOT NULL REFERENCES request_snapshots(id),
  kind TEXT NOT NULL CHECK (kind IN ('activated','revoked')),
  cause TEXT NOT NULL,
  UNIQUE (business,project,revision)
) STRICT;
CREATE TABLE request_project_events (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  at TEXT NOT NULL,
  business TEXT NOT NULL,
  project TEXT NOT NULL,
  revision INTEGER NOT NULL CHECK (revision>=1),
  kind TEXT NOT NULL CHECK (kind IN ('attached','mode.changed')),
  body TEXT NOT NULL CHECK (json_valid(body) AND json_type(body)='object'),
  UNIQUE (business,project,revision)
) STRICT;
CREATE TRIGGER request_events_no_update BEFORE UPDATE ON request_events BEGIN SELECT RAISE(ABORT, 'request_events is append-only'); END;
CREATE TRIGGER request_events_no_delete BEFORE DELETE ON request_events BEGIN SELECT RAISE(ABORT, 'request_events is append-only'); END;
CREATE TRIGGER request_events_no_replace BEFORE INSERT ON request_events WHEN EXISTS (SELECT 1 FROM request_events WHERE seq=NEW.seq OR (business=NEW.business AND request=NEW.request AND revision=NEW.revision)) BEGIN SELECT RAISE(ABORT, 'request_events is append-only'); END;
CREATE TRIGGER request_effects_no_update BEFORE UPDATE ON request_effects BEGIN SELECT RAISE(ABORT, 'request_effects is append-only'); END;
CREATE TRIGGER request_effects_no_delete BEFORE DELETE ON request_effects BEGIN SELECT RAISE(ABORT, 'request_effects is append-only'); END;
CREATE TRIGGER request_effects_no_replace BEFORE INSERT ON request_effects WHEN EXISTS (SELECT 1 FROM request_effects WHERE seq=NEW.seq OR (business=NEW.business AND request=NEW.request AND op=NEW.op AND stage=NEW.stage)) BEGIN SELECT RAISE(ABORT, 'request_effects is append-only'); END;
CREATE TRIGGER request_snapshots_no_update BEFORE UPDATE ON request_snapshots BEGIN SELECT RAISE(ABORT, 'request_snapshots is append-only'); END;
CREATE TRIGGER request_snapshots_no_delete BEFORE DELETE ON request_snapshots BEGIN SELECT RAISE(ABORT, 'request_snapshots is append-only'); END;
CREATE TRIGGER request_snapshots_no_replace BEFORE INSERT ON request_snapshots WHEN EXISTS (SELECT 1 FROM request_snapshots WHERE id=NEW.id) BEGIN SELECT RAISE(ABORT, 'request_snapshots is append-only'); END;
CREATE TRIGGER request_snapshot_events_no_update BEFORE UPDATE ON request_snapshot_events BEGIN SELECT RAISE(ABORT, 'request_snapshot_events is append-only'); END;
CREATE TRIGGER request_snapshot_events_no_delete BEFORE DELETE ON request_snapshot_events BEGIN SELECT RAISE(ABORT, 'request_snapshot_events is append-only'); END;
CREATE TRIGGER request_snapshot_events_no_replace BEFORE INSERT ON request_snapshot_events WHEN EXISTS (SELECT 1 FROM request_snapshot_events WHERE seq=NEW.seq OR (business=NEW.business AND project=NEW.project AND revision=NEW.revision)) BEGIN SELECT RAISE(ABORT, 'request_snapshot_events is append-only'); END;
CREATE TRIGGER request_project_events_no_update BEFORE UPDATE ON request_project_events BEGIN SELECT RAISE(ABORT, 'request_project_events is append-only'); END;
CREATE TRIGGER request_project_events_no_delete BEFORE DELETE ON request_project_events BEGIN SELECT RAISE(ABORT, 'request_project_events is append-only'); END;
CREATE TRIGGER request_project_events_no_replace BEFORE INSERT ON request_project_events WHEN EXISTS (SELECT 1 FROM request_project_events WHERE seq=NEW.seq OR (business=NEW.business AND project=NEW.project AND revision=NEW.revision)) BEGIN SELECT RAISE(ABORT, 'request_project_events is append-only'); END;
-- Per-project selections are a human-authored append-only settings trail.
CREATE TABLE project_system_types (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  at TEXT NOT NULL,
  business TEXT NOT NULL,
  project TEXT NOT NULL REFERENCES projects(id),
  types TEXT NOT NULL,
  by TEXT NOT NULL
) STRICT;
CREATE INDEX project_system_types_project ON project_system_types (business, project, seq);
CREATE TRIGGER project_system_types_no_update BEFORE UPDATE ON project_system_types BEGIN SELECT RAISE(ABORT, 'project_system_types is append-only'); END;
CREATE TRIGGER project_system_types_no_delete BEFORE DELETE ON project_system_types BEGIN SELECT RAISE(ABORT, 'project_system_types is append-only'); END;
CREATE TRIGGER project_system_types_no_replace BEFORE INSERT ON project_system_types WHEN EXISTS (SELECT 1 FROM project_system_types WHERE seq = NEW.seq) BEGIN SELECT RAISE(ABORT, 'project_system_types is append-only'); END;
CREATE TRIGGER project_system_types_business BEFORE INSERT ON project_system_types
  WHEN (SELECT business FROM projects WHERE id = NEW.project) IS NOT NEW.business
  BEGIN SELECT RAISE(ABORT, 'a system types row belongs to its project business'); END;
-- U4a (#1873): the task store built into each organization, the default task-tracker module
-- (REQ-TASK-1). Each row is the whole task after one write, so a task's state is its latest row
-- and the rows are its history: who wrote it (a role and run, or the person), when, under which
-- verb, and the event that records the write. Task numbers count up per organization and are
-- never reused. A delete is a last row: the task leaves every read and its history stays until the
-- organization is removed, which removes this whole database with it. Only a new append-only
-- table, its index and its guards; no existing row changes and nothing is deleted.
CREATE TABLE store_tasks (
  seq INTEGER PRIMARY KEY AUTOINCREMENT,
  at TEXT NOT NULL,
  business TEXT NOT NULL,
  task INTEGER NOT NULL CHECK (task >= 1),
  revision INTEGER NOT NULL CHECK (revision >= 1),
  op TEXT NOT NULL CHECK (op IN ('create','change','archive','unarchive','delete')),
  verb TEXT NOT NULL,
  by_role TEXT,
  by_run TEXT,
  by_human TEXT,
  event TEXT NOT NULL REFERENCES events(id),
  fields TEXT NOT NULL CHECK (json_valid(fields) AND json_type(fields) = 'object'),
  CHECK ((by_human IS NULL) = (by_role IS NOT NULL AND by_run IS NOT NULL)),
  UNIQUE (business, task, revision)
) STRICT;
CREATE INDEX store_tasks_task ON store_tasks (business, task, seq);
CREATE TRIGGER store_tasks_no_update BEFORE UPDATE ON store_tasks BEGIN SELECT RAISE(ABORT, 'store_tasks is append-only'); END;
CREATE TRIGGER store_tasks_no_delete BEFORE DELETE ON store_tasks BEGIN SELECT RAISE(ABORT, 'store_tasks is append-only'); END;
CREATE TRIGGER store_tasks_no_replace BEFORE INSERT ON store_tasks WHEN EXISTS (SELECT 1 FROM store_tasks WHERE seq = NEW.seq OR (business = NEW.business AND task = NEW.task AND revision = NEW.revision)) BEGIN SELECT RAISE(ABORT, 'store_tasks is append-only'); END;
CREATE TRIGGER store_tasks_revision BEFORE INSERT ON store_tasks
  WHEN NEW.revision IS NOT COALESCE((SELECT MAX(revision) FROM store_tasks WHERE business = NEW.business AND task = NEW.task), 0) + 1
  BEGIN SELECT RAISE(ABORT, 'a store task revision follows the last one'); END;
CREATE TRIGGER store_tasks_create BEFORE INSERT ON store_tasks
  WHEN (NEW.op = 'create') IS NOT (NEW.revision = 1)
    OR (NEW.op = 'create' AND NEW.task IS NOT COALESCE((SELECT MAX(task) FROM store_tasks WHERE business = NEW.business), 0) + 1)
  BEGIN SELECT RAISE(ABORT, 'a store task is created once, with the next number'); END;
CREATE TRIGGER store_tasks_deleted BEFORE INSERT ON store_tasks
  WHEN EXISTS (SELECT 1 FROM store_tasks WHERE business = NEW.business AND task = NEW.task AND op = 'delete')
  BEGIN SELECT RAISE(ABORT, 'a deleted store task takes no more rows'); END;
CREATE TRIGGER store_tasks_archive BEFORE INSERT ON store_tasks
  WHEN NEW.op IN ('archive','unarchive')
    AND (COALESCE((SELECT op FROM store_tasks WHERE business = NEW.business AND task = NEW.task AND op IN ('archive','unarchive') ORDER BY seq DESC LIMIT 1), 'unarchive') = 'archive') = (NEW.op = 'archive')
  BEGIN SELECT RAISE(ABORT, 'a store task archive row changes state'); END;