XFEServerManager

CREATE TABLE IF NOT EXISTS trigger_documents (
    trigger_id TEXT NOT NULL REFERENCES trigger_definitions(trigger_id) ON DELETE CASCADE,
    revision INTEGER NOT NULL,
    schema_version INTEGER NOT NULL CHECK (schema_version >= 1),
    document_json TEXT NOT NULL,
    created_at INTEGER NOT NULL,
    PRIMARY KEY(trigger_id, revision)
);

CREATE INDEX IF NOT EXISTS trigger_documents_latest_idx
    ON trigger_documents(trigger_id, revision DESC);

CREATE TABLE IF NOT EXISTS trigger_variable_values (
    trigger_id TEXT NOT NULL REFERENCES trigger_definitions(trigger_id) ON DELETE CASCADE,
    variable_name TEXT NOT NULL,
    storage TEXT NOT NULL,
    storage_key TEXT NOT NULL,
    value_type TEXT NOT NULL,
    value_json TEXT NOT NULL,
    expires_at INTEGER,
    revision INTEGER NOT NULL,
    updated_at INTEGER NOT NULL,
    PRIMARY KEY(trigger_id, variable_name, storage, storage_key)
);

CREATE INDEX IF NOT EXISTS trigger_variable_expiry_idx
    ON trigger_variable_values(expires_at) WHERE expires_at IS NOT NULL;

CREATE TABLE IF NOT EXISTS trigger_executions (
    execution_id TEXT PRIMARY KEY,
    trigger_id TEXT REFERENCES trigger_definitions(trigger_id) ON DELETE SET NULL,
    trigger_revision INTEGER NOT NULL,
    event_type TEXT NOT NULL,
    status TEXT NOT NULL CHECK (status IN (
        'QUEUED', 'RUNNING', 'WAITING', 'COMPLETED', 'FAILED', 'CANCELLED', 'NEEDS_REVIEW', 'SIMULATED')),
    seed INTEGER NOT NULL,
    action_cursor TEXT NOT NULL DEFAULT '',
    call_stack_json TEXT NOT NULL DEFAULT '[]',
    wait_state_json TEXT,
    summary_json TEXT NOT NULL DEFAULT '{}',
    error_message TEXT NOT NULL DEFAULT '',
    started_at INTEGER NOT NULL,
    updated_at INTEGER NOT NULL,
    completed_at INTEGER
);

CREATE INDEX IF NOT EXISTS trigger_executions_history_idx
    ON trigger_executions(started_at DESC, execution_id);
CREATE INDEX IF NOT EXISTS trigger_executions_resume_idx
    ON trigger_executions(status, updated_at);

CREATE TABLE IF NOT EXISTS trigger_execution_steps (
    execution_id TEXT NOT NULL REFERENCES trigger_executions(execution_id) ON DELETE CASCADE,
    sequence INTEGER NOT NULL,
    node_id TEXT,
    kind TEXT NOT NULL,
    result_json TEXT,
    writes_json TEXT NOT NULL DEFAULT '{}',
    instructions INTEGER NOT NULL,
    created_at INTEGER NOT NULL,
    PRIMARY KEY(execution_id, sequence)
);

CREATE TABLE IF NOT EXISTS trigger_event_capture_sessions (
    capture_id TEXT PRIMARY KEY,
    created_by TEXT NOT NULL,
    event_types_json TEXT NOT NULL,
    expires_at INTEGER NOT NULL,
    maximum_samples INTEGER NOT NULL CHECK (maximum_samples BETWEEN 1 AND 500),
    created_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS trigger_event_samples (
    sample_id TEXT PRIMARY KEY,
    capture_id TEXT NOT NULL REFERENCES trigger_event_capture_sessions(capture_id) ON DELETE CASCADE,
    event_type TEXT NOT NULL,
    context_json TEXT NOT NULL,
    captured_at INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS trigger_event_samples_capture_idx
    ON trigger_event_samples(capture_id, captured_at DESC);

CREATE TABLE IF NOT EXISTS trigger_libraries (
    namespace TEXT NOT NULL,
    version TEXT NOT NULL,
    manifest_json TEXT NOT NULL,
    package_json TEXT NOT NULL,
    installed_by TEXT NOT NULL,
    installed_at INTEGER NOT NULL,
    active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0, 1)),
    PRIMARY KEY(namespace, version)
);