-- Projects, any kind of work, and the links the owner draws between them

-- (spec §23.1); agents' presence and sessions (spec §8.1, §8.2).

CREATE TABLE projects (

    name         TEXT PRIMARY KEY,

    path         TEXT,                    -- a folder, if the work has one; never read

    description  TEXT NOT NULL,

    url          TEXT,                    -- a link to it, if any

    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);