-- Durable execution-grant credential pins. Resolution always re-reads this table:
-- revoked or missing grants fail closed even if a worker still holds a grant id.

CREATE TABLE execution_grant_credential_pins (
    grant_id TEXT PRIMARY KEY,
    credential_id TEXT NOT NULL,
    version_id TEXT NOT NULL,
    status TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    revoked_at TIMESTAMPTZ,
    CONSTRAINT execution_grant_credential_pins_status_check CHECK (
        status IN ('active', 'revoked')
    ),
    CONSTRAINT execution_grant_credential_pins_revoked_at_check CHECK (
        (status = 'active' AND revoked_at IS NULL)
        OR (status = 'revoked' AND revoked_at IS NOT NULL)
    )
);

CREATE INDEX execution_grant_credential_pins_credential_idx
    ON execution_grant_credential_pins (credential_id, status);