index|idx_work_items_claim_expires|work_items|CREATE INDEX idx_work_items_claim_expires ON work_items(claim_expires_at)
index|idx_work_items_claimed_by|work_items|CREATE INDEX idx_work_items_claimed_by ON work_items(claimed_by)
index|idx_work_items_depth|work_items|CREATE INDEX idx_work_items_depth ON work_items(depth)
index|idx_work_items_parent|work_items|CREATE INDEX idx_work_items_parent ON work_items(parent_id)
index|idx_work_items_priority|work_items|CREATE INDEX idx_work_items_priority ON work_items(priority)
index|idx_work_items_role_changed|work_items|CREATE INDEX idx_work_items_role_changed ON work_items(role, role_changed_at)
index|idx_work_items_role|work_items|CREATE INDEX idx_work_items_role ON work_items(role)
index|idx_work_items_root_id|work_items|CREATE INDEX idx_work_items_root_id ON work_items(root_id)
table|work_items|work_items|CREATE TABLE "work_items" ( id BLOB PRIMARY KEY DEFAULT (randomblob(16)), parent_id BLOB REFERENCES "work_items"(id), title TEXT NOT NULL, description TEXT, summary TEXT NOT NULL DEFAULT '', role TEXT DEFAULT 'queue' CHECK (role IS NULL OR role IN ('queue', 'work', 'review', 'blocked', 'terminal')), status_label TEXT, previous_role TEXT CHECK (previous_role IS NULL OR previous_role IN ('queue', 'work', 'review', 'blocked', 'terminal')), priority TEXT NOT NULL DEFAULT 'medium' CHECK (priority IN ('high', 'medium', 'low')), complexity INTEGER, requires_verification INTEGER NOT NULL DEFAULT 0, depth INTEGER NOT NULL DEFAULT 0, metadata TEXT, tags TEXT, type TEXT, properties TEXT, created_at TIMESTAMP NOT NULL, modified_at TIMESTAMP NOT NULL, role_changed_at TIMESTAMP NOT NULL, version INTEGER NOT NULL DEFAULT 1, claimed_by TEXT DEFAULT NULL, claimed_at TEXT DEFAULT NULL, claim_expires_at TEXT DEFAULT NULL, original_claimed_at TEXT DEFAULT NULL, root_id BLOB, lifecycle TEXT, pinned_server_cfg BLOB REFERENCES config_versions(id), pinned_root_cfg BLOB REFERENCES config_versions(id), pinned_schema_key TEXT, pinned_schema_layer TEXT CHECK (pinned_schema_layer IS NULL OR pinned_schema_layer IN ('server', 'root')), pinned_at TEXT CHECK (pinned_at IS NULL OR length(pinned_at) = 23), claim_assignment_id BLOB, blocked_reason TEXT CHECK (blocked_reason IS NULL OR blocked_reason IN ('awaiting_decision', 'dependency', 'resource', 'manual')), blocked_ref BLOB, CHECK ((role IS NULL) = (lifecycle IS NOT NULL AND lifecycle = 'none')) )
trigger|work_items_cycle_check_update|work_items|CREATE TRIGGER work_items_cycle_check_update BEFORE UPDATE OF parent_id ON work_items WHEN NEW.parent_id IS NOT NULL BEGIN -- Self-reference guard: catches UPDATE that sets parent_id = id directly. SELECT RAISE(ABORT, 'cycle detected: parent_id equals id (self-reference)') WHERE NEW.parent_id = NEW.id; SELECT RAISE(ABORT, 'cycle detected: parent_id references a descendant of this item') WHERE EXISTS ( WITH RECURSIVE ancestors(node_id) AS ( SELECT parent_id FROM work_items WHERE id = NEW.parent_id AND parent_id IS NOT NULL UNION ALL SELECT wi.parent_id FROM work_items wi JOIN ancestors a ON wi.id = a.node_id WHERE wi.parent_id IS NOT NULL ) SELECT 1 FROM ancestors WHERE node_id = NEW.id ); END
trigger|work_items_cycle_check|work_items|CREATE TRIGGER work_items_cycle_check BEFORE INSERT ON work_items WHEN NEW.parent_id IS NOT NULL BEGIN -- Self-reference guard: the recursive CTE below cannot catch parent_id = id on -- INSERT because the row doesn't exist yet, so the ancestor walk seeds empty. SELECT RAISE(ABORT, 'cycle detected: parent_id equals id (self-reference)') WHERE NEW.parent_id = NEW.id; SELECT RAISE(ABORT, 'cycle detected: parent_id references a descendant of this item') WHERE EXISTS ( WITH RECURSIVE ancestors(node_id) AS ( SELECT parent_id FROM work_items WHERE id = NEW.parent_id AND parent_id IS NOT NULL UNION ALL SELECT wi.parent_id FROM work_items wi JOIN ancestors a ON wi.id = a.node_id WHERE wi.parent_id IS NOT NULL ) SELECT 1 FROM ancestors WHERE node_id = NEW.id ); END
trigger|work_items_fts_text_ad|work_items|CREATE TRIGGER work_items_fts_text_ad AFTER DELETE ON work_items BEGIN INSERT INTO work_items_fts_text(work_items_fts_text, rowid, title, summary, description) VALUES ('delete', old.rowid, old.title, old.summary, old.description); END
trigger|work_items_fts_text_ai|work_items|CREATE TRIGGER work_items_fts_text_ai AFTER INSERT ON work_items BEGIN INSERT INTO work_items_fts_text(rowid, title, summary, description) VALUES (new.rowid, new.title, new.summary, new.description); END
trigger|work_items_fts_text_au|work_items|CREATE TRIGGER work_items_fts_text_au AFTER UPDATE OF title, summary, description ON work_items BEGIN INSERT INTO work_items_fts_text(work_items_fts_text, rowid, title, summary, description) VALUES ('delete', old.rowid, old.title, old.summary, old.description); INSERT INTO work_items_fts_text(rowid, title, summary, description) VALUES (new.rowid, new.title, new.summary, new.description); END
trigger|work_items_fts_trigram_ad|work_items|CREATE TRIGGER work_items_fts_trigram_ad AFTER DELETE ON work_items BEGIN INSERT INTO work_items_fts_trigram(work_items_fts_trigram, rowid, title, summary, description) VALUES ('delete', old.rowid, old.title, old.summary, old.description); END
trigger|work_items_fts_trigram_ai|work_items|CREATE TRIGGER work_items_fts_trigram_ai AFTER INSERT ON work_items BEGIN INSERT INTO work_items_fts_trigram(rowid, title, summary, description) VALUES (new.rowid, new.title, new.summary, new.description); END
trigger|work_items_fts_trigram_au|work_items|CREATE TRIGGER work_items_fts_trigram_au AFTER UPDATE OF title, summary, description ON work_items BEGIN INSERT INTO work_items_fts_trigram(work_items_fts_trigram, rowid, title, summary, description) VALUES ('delete', old.rowid, old.title, old.summary, old.description); INSERT INTO work_items_fts_trigram(rowid, title, summary, description) VALUES (new.rowid, new.title, new.summary, new.description); END
