-- Runner updates (oxygenhub#322, OxygenFlow docs/jetstream.md §8.2). The

-- policy of the site (scope 'site', key ''), an owner ('owner', its name),

-- a test group ('group', its id) or a runner ('runner', its id). A NULL

-- inherits from the next level out; the nearest setting wins; off unless

-- some level turns updates on.

CREATE TABLE runner_update_policies (

    scope       TEXT NOT NULL,

    key         TEXT NOT NULL,

    updates     BOOLEAN,

    -- 'stable', 'canary' or a version.

    channel     TEXT,

    -- Hours (UTC) in which updates may start: from, to (to may wrap past midnight).

    window_from BIGINT,

    window_to   BIGINT,

    dry_run     BOOLEAN,

    PRIMARY KEY (scope, key)

);

-- Test groups: named sets of the site's runners (owner '') or one owner's.

-- A runner is in one when chosen, or when a stable hash of its id falls in

-- the group's percentage, so the same machines are in it every rollout.

CREATE TABLE runner_groups (

    id         BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    owner      TEXT NOT NULL,

    name       TEXT NOT NULL,

    percent    BIGINT,

    created_ms BIGINT NOT NULL,

    UNIQUE (owner, name)

);

CREATE TABLE runner_group_members (

    group_id  BIGINT NOT NULL REFERENCES runner_groups (id) ON DELETE CASCADE,

    runner_id BIGINT NOT NULL REFERENCES flow_runners (id) ON DELETE CASCADE,

    PRIMARY KEY (group_id, runner_id)

);

-- What the runner last said about its update, and when.

ALTER TABLE flow_runners ADD COLUMN update_note TEXT NOT NULL DEFAULT '';

ALTER TABLE flow_runners ADD COLUMN update_ms BIGINT;