Skip to content

Latest commit

 

History

History
518 lines (452 loc) · 13.5 KB

schema.md

File metadata and controls

518 lines (452 loc) · 13.5 KB
Raw

Database Schema

UNIQUE

CREATE UNIQUE INDEX objects_upload_id_idx ON objects USING btree (upload_id) WHERE (upload_id IS NOT NULL);

access_tokens

CREATE TABLE access_tokens (
    encrypted_access_token jsonb NOT NULL,
    hashed_access_token text NOT NULL,
    client_id text NOT NULL,
    redirect_uri text NOT NULL,
    identity text NOT NULL,
    identity_provider_id text NOT NULL,
    expires timestamp with time zone NOT NULL,
    client_details jsonb NOT NULL
);
ALTER TABLE access_tokens
    ADD CONSTRAINT access_tokens_pkey PRIMARY KEY (hashed_access_token);

authorization_codes

CREATE TABLE authorization_codes (
    code text NOT NULL,
    client_id text NOT NULL,
    redirect_uri text NOT NULL,
    identity text NOT NULL,
    identity_provider_id text NOT NULL,
    expires timestamp with time zone NOT NULL,
    client_details jsonb NOT NULL
);
ALTER TABLE authorization_codes
    ADD CONSTRAINT authorization_codes_pkey PRIMARY KEY (code);

azure_queue_messages

CREATE TABLE azure_queue_messages (
    message_id uuid NOT NULL,
    queue_name text NOT NULL,
    message_text text NOT NULL,
    inserted timestamp with time zone NOT NULL,
    visible timestamp with time zone NOT NULL,
    expires timestamp with time zone NOT NULL,
    pop_receipt uuid
);
ALTER TABLE azure_queue_messages
    ADD CONSTRAINT azure_queue_messages_pkey PRIMARY KEY (message_id);

cache_purges

CREATE TABLE cache_purges (
    cache_name text NOT NULL,
    before timestamp with time zone NOT NULL,
    expires timestamp with time zone NOT NULL,
    worker_pool_id text NOT NULL
);
ALTER TABLE cache_purges
    ADD CONSTRAINT cache_purges_pkey PRIMARY KEY (worker_pool_id, cache_name);

clients

CREATE TABLE clients (
    client_id text NOT NULL,
    description text NOT NULL,
    encrypted_access_token jsonb NOT NULL,
    expires timestamp with time zone NOT NULL,
    disabled boolean NOT NULL,
    scopes jsonb NOT NULL,
    created timestamp with time zone NOT NULL,
    last_modified timestamp with time zone NOT NULL,
    last_date_used timestamp with time zone NOT NULL,
    last_rotated timestamp with time zone NOT NULL,
    delete_on_expiration boolean NOT NULL
);
ALTER TABLE clients
    ADD CONSTRAINT clients_pkey PRIMARY KEY (client_id);

denylisted_notifications

CREATE TABLE denylisted_notifications (
    notification_type text NOT NULL,
    notification_address text NOT NULL
);
ALTER TABLE denylisted_notifications
    ADD CONSTRAINT denylisted_notifications_pkey PRIMARY KEY (notification_type, notification_address);

github_access_tokens

CREATE TABLE github_access_tokens (
    user_id text NOT NULL,
    encrypted_access_token jsonb NOT NULL
);
ALTER TABLE github_access_tokens
    ADD CONSTRAINT github_access_tokens_pkey PRIMARY KEY (user_id);

github_builds

CREATE TABLE github_builds (
    organization text NOT NULL,
    repository text NOT NULL,
    sha text NOT NULL,
    task_group_id text NOT NULL,
    state text NOT NULL,
    created timestamp with time zone NOT NULL,
    updated timestamp with time zone NOT NULL,
    installation_id integer NOT NULL,
    event_type text NOT NULL,
    event_id text NOT NULL
);
ALTER TABLE github_builds
    ADD CONSTRAINT github_builds_pkey PRIMARY KEY (task_group_id);

github_checks

CREATE TABLE github_checks (
    task_group_id text NOT NULL,
    task_id text NOT NULL,
    check_suite_id text NOT NULL,
    check_run_id text NOT NULL
);
ALTER TABLE github_checks
    ADD CONSTRAINT github_checks_pkey PRIMARY KEY (task_group_id, task_id);

github_integrations

CREATE TABLE github_integrations (
    owner text NOT NULL,
    installation_id integer NOT NULL
);
ALTER TABLE github_integrations
    ADD CONSTRAINT github_integrations_pkey PRIMARY KEY (owner);

hooks

CREATE TABLE hooks (
    hook_group_id text NOT NULL,
    hook_id text NOT NULL,
    metadata jsonb NOT NULL,
    task jsonb NOT NULL,
    bindings jsonb NOT NULL,
    schedule jsonb NOT NULL,
    encrypted_trigger_token jsonb NOT NULL,
    encrypted_next_task_id jsonb NOT NULL,
    next_scheduled_date timestamp with time zone NOT NULL,
    trigger_schema jsonb NOT NULL
);
ALTER TABLE hooks
    ADD CONSTRAINT hooks_pkey PRIMARY KEY (hook_group_id, hook_id);

hooks_last_fires

CREATE TABLE hooks_last_fires (
    hook_group_id text NOT NULL,
    hook_id text NOT NULL,
    fired_by text NOT NULL,
    task_id text NOT NULL,
    task_create_time timestamp with time zone NOT NULL,
    result text NOT NULL,
    error text NOT NULL
);
ALTER TABLE hooks_last_fires
    ADD CONSTRAINT hooks_last_fires_pkey PRIMARY KEY (hook_group_id, hook_id, task_id);

hooks_queues

CREATE TABLE hooks_queues (
    hook_group_id text NOT NULL,
    hook_id text NOT NULL,
    queue_name text NOT NULL,
    bindings jsonb NOT NULL
);
ALTER TABLE hooks_queues
    ADD CONSTRAINT hooks_queues_pkey PRIMARY KEY (hook_group_id, hook_id);

index_namespaces

CREATE TABLE index_namespaces (
    parent text NOT NULL,
    name text NOT NULL,
    expires timestamp with time zone NOT NULL
);
ALTER TABLE index_namespaces
    ADD CONSTRAINT index_namespaces_pkey PRIMARY KEY (parent, name);

indexed_tasks

CREATE TABLE indexed_tasks (
    namespace text NOT NULL,
    name text NOT NULL,
    rank integer NOT NULL,
    task_id text NOT NULL,
    data jsonb NOT NULL,
    expires timestamp with time zone NOT NULL
);
ALTER TABLE indexed_tasks
    ADD CONSTRAINT indexed_tasks_pkey PRIMARY KEY (namespace, name);

object_hashes

CREATE TABLE object_hashes (
    name text NOT NULL,
    algorithm text NOT NULL,
    hash text NOT NULL
);
ALTER TABLE object_hashes
    ADD CONSTRAINT object_hashes_pkey PRIMARY KEY (name, algorithm);

objects

CREATE TABLE objects (
    name text NOT NULL,
    data jsonb NOT NULL,
    backend_id text NOT NULL,
    project_id text NOT NULL,
    expires timestamp with time zone NOT NULL,
    upload_id text,
    upload_expires timestamp with time zone
);
ALTER TABLE objects
    ADD CONSTRAINT objects_pkey PRIMARY KEY (name);

queue_artifacts

CREATE TABLE queue_artifacts (
    task_id text NOT NULL,
    run_id integer NOT NULL,
    name text NOT NULL,
    storage_type text NOT NULL,
    content_type text NOT NULL,
    details jsonb NOT NULL,
    present boolean NOT NULL,
    expires timestamp with time zone NOT NULL
);
ALTER TABLE queue_artifacts
    ADD CONSTRAINT queue_artifacts_pkey PRIMARY KEY (task_id, run_id, name);

queue_workers

CREATE TABLE queue_workers (
    worker_group text NOT NULL,
    worker_id text NOT NULL,
    recent_tasks jsonb NOT NULL,
    quarantine_until timestamp with time zone NOT NULL,
    expires timestamp with time zone NOT NULL,
    first_claim timestamp with time zone NOT NULL,
    task_queue_id text NOT NULL,
    last_date_active timestamp with time zone
);
ALTER TABLE queue_workers
    ADD CONSTRAINT queue_workers_pkey PRIMARY KEY (task_queue_id, worker_group, worker_id);

roles

CREATE TABLE roles (
    role_id text NOT NULL,
    scopes jsonb NOT NULL,
    created timestamp with time zone NOT NULL,
    description text NOT NULL,
    last_modified timestamp with time zone NOT NULL,
    etag uuid NOT NULL
);
ALTER TABLE roles
    ADD CONSTRAINT roles_pkey PRIMARY KEY (role_id);

secrets

CREATE TABLE secrets (
    name text NOT NULL,
    encrypted_secret jsonb NOT NULL,
    expires timestamp with time zone NOT NULL
);
ALTER TABLE secrets
    ADD CONSTRAINT secrets_pkey PRIMARY KEY (name);

sessions

CREATE TABLE sessions (
    hashed_session_id text NOT NULL,
    encrypted_session_id jsonb NOT NULL,
    data jsonb NOT NULL,
    expires timestamp with time zone NOT NULL
);
ALTER TABLE sessions
    ADD CONSTRAINT sessions_pkey PRIMARY KEY (hashed_session_id);

task_dependencies

CREATE TABLE task_dependencies (
    dependent_task_id text NOT NULL,
    required_task_id text NOT NULL,
    requires public.task_requires NOT NULL,
    satisfied boolean NOT NULL,
    expires timestamp with time zone NOT NULL
);
ALTER TABLE task_dependencies
    ADD CONSTRAINT task_dependencies_pkey PRIMARY KEY (required_task_id, dependent_task_id);

task_groups

CREATE TABLE task_groups (
    task_group_id text NOT NULL,
    scheduler_id text NOT NULL,
    expires timestamp with time zone NOT NULL
);
ALTER TABLE task_groups
    ADD CONSTRAINT task_groups_pkey PRIMARY KEY (task_group_id);

task_queues

CREATE TABLE task_queues (
    expires timestamp with time zone NOT NULL,
    last_date_active timestamp with time zone NOT NULL,
    description text NOT NULL,
    stability text NOT NULL,
    task_queue_id text NOT NULL
);
ALTER TABLE task_queues
    ADD CONSTRAINT task_queues_pkey PRIMARY KEY (task_queue_id);

tasks

CREATE TABLE tasks (
    task_id text NOT NULL,
    scheduler_id text NOT NULL,
    task_group_id text NOT NULL,
    dependencies jsonb NOT NULL,
    requires public.task_requires NOT NULL,
    routes jsonb NOT NULL,
    priority public.task_priority NOT NULL,
    retries integer NOT NULL,
    retries_left integer NOT NULL,
    created timestamp with time zone NOT NULL,
    deadline timestamp with time zone NOT NULL,
    expires timestamp with time zone NOT NULL,
    scopes jsonb NOT NULL,
    payload jsonb NOT NULL,
    metadata jsonb NOT NULL,
    tags jsonb NOT NULL,
    extra jsonb NOT NULL,
    runs jsonb NOT NULL,
    taken_until timestamp with time zone,
    ever_resolved boolean NOT NULL,
    task_queue_id text,
    project_id text
);
ALTER TABLE tasks
    ADD CONSTRAINT tasks_pkey PRIMARY KEY (task_id);

tcversion

CREATE TABLE tcversion (
    version integer
);

worker_pool_errors

CREATE TABLE worker_pool_errors (
    error_id text NOT NULL,
    worker_pool_id text NOT NULL,
    reported timestamp with time zone NOT NULL,
    kind text NOT NULL,
    title text NOT NULL,
    description text NOT NULL,
    extra jsonb
);
ALTER TABLE worker_pool_errors
    ADD CONSTRAINT worker_pool_errors_pkey PRIMARY KEY (error_id);

worker_pools

CREATE TABLE worker_pools (
    worker_pool_id text NOT NULL,
    provider_id text NOT NULL,
    owner text NOT NULL,
    description text NOT NULL,
    email_on_error boolean NOT NULL,
    created timestamp with time zone NOT NULL,
    last_modified timestamp with time zone NOT NULL,
    config jsonb NOT NULL,
    provider_data jsonb NOT NULL,
    previous_provider_ids jsonb NOT NULL
);
ALTER TABLE worker_pools
    ADD CONSTRAINT worker_pools_pkey PRIMARY KEY (worker_pool_id);

workers

CREATE TABLE workers (
    worker_pool_id text NOT NULL,
    worker_group text NOT NULL,
    worker_id text NOT NULL,
    provider_id text NOT NULL,
    created timestamp with time zone NOT NULL,
    expires timestamp with time zone NOT NULL,
    state text NOT NULL,
    provider_data jsonb NOT NULL,
    capacity integer NOT NULL,
    last_modified timestamp with time zone NOT NULL,
    last_checked timestamp with time zone NOT NULL,
    etag uuid DEFAULT public.gen_random_uuid() NOT NULL,
    secret jsonb
);
ALTER TABLE workers
    ADD CONSTRAINT workers_pkey PRIMARY KEY (worker_pool_id, worker_group, worker_id);

undefined

CREATE INDEX azure_queue_messages_inserted ON azure_queue_messages USING btree (queue_name, inserted);
CREATE INDEX github_builds_organization_repository_sha_idx ON github_builds USING btree (organization, repository, sha);
CREATE INDEX github_checks_check_suite_id_check_run_id_idx ON github_checks USING btree (check_suite_id, check_run_id);
CREATE INDEX hooks_last_fires_time ON hooks_last_fires USING btree (hook_group_id, hook_id, task_create_time);
CREATE INDEX sha512_index_namespaces_idx ON index_namespaces USING btree (public.sha512(parent), name);
CREATE INDEX sha512_indexed_tasks_idx ON indexed_tasks USING btree (public.sha512(namespace), name);
CREATE INDEX task_dependencies_dependent_task_id_idx ON task_dependencies USING btree (dependent_task_id) WHERE (NOT satisfied);
CREATE INDEX tasks_task_group_id_idx ON tasks USING btree (task_group_id);
CREATE INDEX tasks_task_group_id_unresolved_idx ON tasks USING btree (task_group_id) WHERE (NOT ever_resolved);
CREATE INDEX worker_pool_errors_reported_idx ON worker_pool_errors USING btree (reported);
CREATE INDEX workers_state_idx ON workers USING btree (state);