CREATE TABLE sensitive_source_inputs (
    sensitive_source_ref TEXT PRIMARY KEY,
    source_input JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL
);

CREATE TABLE execution_grants (
    grant_id TEXT PRIMARY KEY,
    owner_id TEXT NOT NULL,
    target_store TEXT NOT NULL,
    target_bucket TEXT NOT NULL,
    target_prefix JSONB NOT NULL,
    policy_version TEXT NOT NULL,
    credential_id TEXT,
    credential_version_id TEXT,
    status TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    revoked_at TIMESTAMPTZ,
    CONSTRAINT execution_grants_status_check CHECK (status IN ('active', 'revoked')),
    CONSTRAINT execution_grants_revocation_check CHECK (
        (status = 'active' AND revoked_at IS NULL)
        OR (status = 'revoked' AND revoked_at IS NOT NULL)
    ),
    CONSTRAINT execution_grants_credential_check CHECK (
        (credential_id IS NULL) = (credential_version_id IS NULL)
    ),
    CONSTRAINT execution_grants_authority_tuple_unique UNIQUE (
        grant_id,
        status,
        credential_id,
        credential_version_id
    )
);

ALTER TABLE execution_grant_credential_pins
    ADD CONSTRAINT execution_grant_pins_authoritative_grant_fk
    FOREIGN KEY (grant_id, status, credential_id, version_id)
    REFERENCES execution_grants (
        grant_id,
        status,
        credential_id,
        credential_version_id
    )
    MATCH FULL
    DEFERRABLE INITIALLY DEFERRED;

CREATE TABLE jobs (
    job_id TEXT PRIMARY KEY,
    owner_id TEXT NOT NULL,
    label TEXT,
    revision BIGINT NOT NULL DEFAULT 1 CHECK (revision >= 1),
    phase TEXT NOT NULL CHECK (phase IN ('queued', 'running', 'retry_wait', 'cancelling', 'succeeded', 'failed', 'cancelled')),
    lifecycle TEXT NOT NULL CHECK (lifecycle IN ('visible', 'soft_deleted', 'hard_deleting', 'hard_deleted')),
    source_projection JSONB NOT NULL,
    sensitive_source_ref TEXT NOT NULL UNIQUE REFERENCES sensitive_source_inputs (sensitive_source_ref),
    target_store TEXT NOT NULL,
    target_bucket TEXT NOT NULL,
    target_prefix JSONB NOT NULL,
    execution_grant_id TEXT NOT NULL UNIQUE REFERENCES execution_grants (grant_id) DEFERRABLE INITIALLY DEFERRED,
    dispatch_needed BOOLEAN NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL,
    CONSTRAINT jobs_label_length CHECK (label IS NULL OR char_length(label) BETWEEN 1 AND 200)
);

ALTER TABLE execution_grants
    ADD CONSTRAINT execution_grants_job_fk
    FOREIGN KEY (grant_id)
    REFERENCES jobs (execution_grant_id)
    DEFERRABLE INITIALLY DEFERRED;

CREATE INDEX jobs_owner_created_idx ON jobs (owner_id, created_at DESC, job_id DESC);
CREATE INDEX jobs_dispatch_idx ON jobs (created_at, job_id) WHERE dispatch_needed;