-- Terraform and OpenTofu state kept by the hub (oxygenhub#252): one row

-- per repository and state name, its body sealed with the hub's secrets

-- key, its lock, and the last versions for rolling back.

CREATE TABLE terraform_states (

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

    name       TEXT NOT NULL,

    body       BLOB NOT NULL,

    serial     INTEGER NOT NULL DEFAULT 0,

    lock_id    TEXT NOT NULL DEFAULT '',

    lock_info  TEXT NOT NULL DEFAULT '',

    locked_by  TEXT NOT NULL DEFAULT '',

    locked_ms  INTEGER,

    updated_by TEXT NOT NULL DEFAULT '',

    updated_ms INTEGER NOT NULL,

    PRIMARY KEY (repo_id, name)

);

CREATE TABLE terraform_state_versions (

    id         INTEGER PRIMARY KEY,

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

    name       TEXT NOT NULL,

    body       BLOB NOT NULL,

    serial     INTEGER NOT NULL,

    created_by TEXT NOT NULL,

    created_ms INTEGER NOT NULL

);

CREATE INDEX terraform_state_versions_state ON terraform_state_versions(repo_id, name, id);