bucketeer/migrations/0001_schema.sql

-- ctcloud schema. Applied idempotently at startup; there is no external
-- migration tooling so the container needs no writable filesystem.

CREATE TABLE IF NOT EXISTS cloud_assets (
    id                  BIGSERIAL PRIMARY KEY,
    provider            TEXT        NOT NULL,
    service             TEXT        NOT NULL,
    hostname            TEXT        NOT NULL, -- original CT hostname (or CNAME source)
    provider_hostname   TEXT        NOT NULL, -- hostname that matched the provider
    resource_identifier TEXT        NOT NULL DEFAULT '',
    region              TEXT        NOT NULL DEFAULT '',
    resource_specific   BOOLEAN     NOT NULL DEFAULT TRUE,
    public_semantic     TEXT        NOT NULL DEFAULT 'none',
    exposure_state      TEXT        NOT NULL DEFAULT 'unknown',
    first_seen          TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_seen           TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (provider, service, provider_hostname)
);

CREATE INDEX IF NOT EXISTS cloud_assets_last_seen_idx ON cloud_assets (last_seen);

-- Reachability probe results (added after the initial schema; ALTER keeps this
-- idempotent for existing databases).
ALTER TABLE cloud_assets ADD COLUMN IF NOT EXISTS exposure_detail TEXT        NOT NULL DEFAULT '';
ALTER TABLE cloud_assets ADD COLUMN IF NOT EXISTS probed_at       TIMESTAMPTZ;
-- Readability capabilities, kept separate because they differ by provider
-- (e.g. Azure "Blob" = object readable only; R2 = object read, never listable).
ALTER TABLE cloud_assets ADD COLUMN IF NOT EXISTS listable        BOOLEAN     NOT NULL DEFAULT FALSE;
ALTER TABLE cloud_assets ADD COLUMN IF NOT EXISTS object_readable BOOLEAN     NOT NULL DEFAULT FALSE;

CREATE TABLE IF NOT EXISTS observations (
    id              BIGSERIAL PRIMARY KEY,
    asset_id        BIGINT      NOT NULL REFERENCES cloud_assets (id) ON DELETE CASCADE,
    observed_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
    source_hostname TEXT        NOT NULL,
    source_type     TEXT        NOT NULL,
    cname_chain     TEXT[]      NOT NULL DEFAULT '{}',
    ipv4            TEXT[]      NOT NULL DEFAULT '{}',
    ipv6            TEXT[]      NOT NULL DEFAULT '{}',
    cert_fingerprint TEXT       NOT NULL DEFAULT '',
    ct_log_id       TEXT        NOT NULL DEFAULT ''
);

CREATE INDEX IF NOT EXISTS observations_asset_idx ON observations (asset_id, observed_at DESC);

-- Metadata index of resources confirmed anonymously readable. Keys/sizes/dates
-- only; never object contents.
CREATE TABLE IF NOT EXISTS asset_entries (
    id           BIGSERIAL PRIMARY KEY,
    asset_id     BIGINT      NOT NULL REFERENCES cloud_assets (id) ON DELETE CASCADE,
    kind         TEXT        NOT NULL,
    name         TEXT        NOT NULL,
    size         BIGINT      NOT NULL DEFAULT 0,
    updated      TIMESTAMPTZ,
    content_type TEXT        NOT NULL DEFAULT '',
    etag         TEXT        NOT NULL DEFAULT '',
    indexed_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (asset_id, kind, name)
);

CREATE INDEX IF NOT EXISTS asset_entries_asset_idx ON asset_entries (asset_id);
CREATE INDEX IF NOT EXISTS asset_entries_name_idx ON asset_entries (name);

-- CT resume state. `state` is the JSON-encoded certspotter monitor.LogState
-- (download/verified Merkle positions and last verified STH). No certificate
-- contents are ever stored here.
CREATE TABLE IF NOT EXISTS ct_checkpoints (
    log_id     TEXT        PRIMARY KEY,
    state      JSONB       NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Signed tree heads downloaded but not yet verified, needed to resume safely.
-- The full STH is kept as JSONB so the certspotter structure round-trips
-- exactly; tree_size is duplicated as a typed column for cheap ordering.
CREATE TABLE IF NOT EXISTS ct_sths (
    log_id    TEXT        NOT NULL,
    sth_key   TEXT        NOT NULL,
    tree_size BIGINT      NOT NULL,
    sth       JSONB       NOT NULL,
    stored_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (log_id, sth_key)
);