-- Migration 006: closed maintenance assessments, attempt binding, and truthful
-- revisit state for deferred work.
BEGIN IMMEDIATE;
-- Bounded revisit policy persisted on the work item itself. NULL deadline
-- means "wait on the dependency only"; a deadline makes the item eligible
-- again only at or after that wall-clock ms.
ALTER TABLE work_items ADD COLUMN revisit_deadline_ms INTEGER;
ALTER TABLE work_items ADD COLUMN revisit_reason TEXT;
-- Durable record of each accepted assessment, bound to the work item, the
-- session, and the attempt that owned the item at submission time.
CREATE TABLE maintenance_assessments (
session_id TEXT NOT NULL REFERENCES sessions(id),
operation_id TEXT NOT NULL,
work_item_id TEXT NOT NULL REFERENCES work_items(id),
attempt_id TEXT NOT NULL,
assessment TEXT NOT NULL CHECK(json_valid(assessment)),
event_seq INTEGER NOT NULL REFERENCES events(seq),
created_ms INTEGER NOT NULL CHECK(created_ms >= 0),
PRIMARY KEY (session_id, operation_id)
) STRICT;
CREATE INDEX idx_maintenance_assessments_work ON maintenance_assessments(work_item_id);
CREATE INDEX idx_work_items_revisit ON work_items(revisit_deadline_ms)
WHERE revisit_deadline_ms IS NOT NULL;
PRAGMA user_version = 6;
COMMIT;