-- 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    BIGINT NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    name       TEXT NOT NULL,

    body       BYTEA NOT NULL,

    serial     BIGINT NOT NULL DEFAULT 0,

    lock_id    TEXT NOT NULL DEFAULT '',

    lock_info  TEXT NOT NULL DEFAULT '',

    locked_by  TEXT NOT NULL DEFAULT '',

    locked_ms  BIGINT,

    updated_by TEXT NOT NULL DEFAULT '',

    updated_ms BIGINT NOT NULL,

    PRIMARY KEY (repo_id, name)

);

CREATE TABLE terraform_state_versions (

    id         BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

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

    name       TEXT NOT NULL,

    body       BYTEA NOT NULL,

    serial     BIGINT NOT NULL,

    created_by TEXT NOT NULL,

    created_ms BIGINT NOT NULL

);

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