-- Projects and how they depend on one another (spec §23.1), and agents'

-- presence and sessions (spec §8.1, §8.2).

CREATE TABLE projects (

    name     TEXT PRIMARY KEY,

    path     TEXT NOT NULL,

    repo_id  TEXT,

    remote   TEXT,

    modules  TEXT NOT NULL,               -- JSON array of the URLs in .oxymodules

    seq      INTEGER NOT NULL

);

CREATE TABLE project_relations (

    project     TEXT NOT NULL,

    depends_on  TEXT NOT NULL,

    PRIMARY KEY (project, depends_on)

);

CREATE TABLE presence (

    agent        TEXT PRIMARY KEY,

    state        TEXT NOT NULL,

    since_ms     INTEGER NOT NULL,

    status_state TEXT,

    status_text  TEXT,

    status_task  TEXT,

    status_ms    INTEGER

);

CREATE TABLE sessions (

    id          TEXT PRIMARY KEY,

    agent       TEXT NOT NULL,

    started_ms  INTEGER NOT NULL,

    ended_ms    INTEGER

);

CREATE INDEX sessions_agent ON sessions(agent);