-- Diffusion (#148, docs/diffusion.md §4.1): the reverse pin index. Each

-- Oxygen subrepo pin on a repository's default bookmark, and on the

-- bookmark its work lands on, recorded when a push moves that bookmark:

-- who consumes which upstream (by the upstream's Oxygen repository id,

-- hex), at which heads.

CREATE TABLE diffusion_pins (

    consumer_id  INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    bookmark     TEXT NOT NULL,

    path         TEXT NOT NULL,

    upstream     TEXT NOT NULL,

    heads        TEXT NOT NULL,

    updated_ms   INTEGER NOT NULL,

    PRIMARY KEY (consumer_id, bookmark, path)

);

CREATE INDEX diffusion_pins_by_upstream ON diffusion_pins (upstream);