How can I create a path into GitLab Object Storage from the ci_job_artifacts table?

Question

Gitlab has an internal PostgreSQL database. There is a client wrapper for psql called gitlab-psql that I connect to it with. This allows me to query job artifacts through the table ci_job_artifacts. This table schema looks like this,

gitlabhq_production=# \d ci_job_artifacts    
                                          Table "public.ci_job_artifacts"
      Column       |           Type           | Collation | Nullable |                   Default                    
-------------------+--------------------------+-----------+----------+----------------------------------------------
 project_id        | integer                  |           | not null | 
 file_type         | integer                  |           | not null | 
 size              | bigint                   |           |          | 
 created_at        | timestamp with time zone |           | not null | 
 updated_at        | timestamp with time zone |           | not null | 
 expire_at         | timestamp with time zone |           |          | 
 file              | character varying        |           |          | 
 file_store        | integer                  |           |          | 1
 file_sha256       | bytea                    |           |          | 
 file_format       | smallint                 |           |          | 
 file_location     | smallint                 |           |          | 
 id                | bigint                   |           | not null | nextval('ci_job_artifacts_id_seq'::regclass)
 job_id            | bigint                   |           | not null | 
 locked            | smallint                 |           |          | 2
 original_filename | text                     |           |          | 
 partition_id      | bigint                   |           | not null | 100
 accessibility     | smallint                 |           | not null | 0

How do I convert this to a path that I can use to find the artifact in GitLab Object Storage.

Answer 1

Aside from customer-facing documentation there is a document in the source that details how the path is created called "Uploads guide: Adding new uploads" currently at doc/development/uploads/working_with_uploads.md. This document shows the magic sauce for the bucket structure everywhere in GitLab. Specifically for the job artifacts, the structure is

/artifacts/<proj_id_hash>/<date>/<job_id>/<artifact_id>

The proj_id_hash is the sha256 is the project_id you can read more about that on this question, "What is the GitLab proj_id_hash for the bucket structure in the object store?"

To calculate the rest, I've create a helper function which should greatly ease this process,

CREATE EXTENSION pgcrypto; -- needed for `digest(bytea, text) `

CREATE OR REPLACE FUNCTION generate_job_artifact_bucket_path(project_id bigint, created_at timestamp with time zone, job_id bigint, id bigint, file text)
RETURNS text
AS $$
  SELECT FORMAT(
    '%s/%s/%s/%s/%s/%s/%s',
    substring(proj_sha256 FROM 1 FOR 2),
    substring(proj_sha256 FROM 3 FOR 2),
    proj_sha256,
    to_char(created_at, 'YYYY_MM_DD'),
    job_id,
    id,
    file
  )
  FROM encode(digest(project_id::text::bytea, 'SHA256'), 'hex') AS proj_sha256
$$ LANGUAGE SQL IMMUTABLE;

COMMENT ON FUNCTION generate_job_artifact_bucket_path
IS $$Generates the relative bucket to `gitlab_rails['object_store']['objects']['artifacts']['bucket']` for more information see https://devops.stackexchange.com/q/17298/18965$$;

Now you can query for the path like this,

SELECT generate_job_artifact_bucket_path(project_id, created_at, job_id, id, file)
FROM ci_job_artifacts;

Alternatively you can do something like this,

CREATE OR REPLACE FUNCTION to_bucket_path(t ci_job_artifacts)
RETURNS text
AS $$
  SELECT FORMAT(
    '%s/%s/%s/%s/%s/%s/%s',
    substring(proj_sha256 FROM 1 FOR 2),
    substring(proj_sha256 FROM 3 FOR 2),
    proj_sha256,
    to_char(t.created_at, 'YYYY_MM_DD'),
    t.job_id,
    t.id,
    t.file
  )
  FROM encode(digest(t.project_id::text::bytea, 'SHA256'), 'hex') AS proj_sha256
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION to_bucket_path(t ci_pipeline_artifacts)
RETURNS text
AS $$
  SELECT FORMAT(
    '%s/%s/%s/pipelines/%s/artifacts/%s/%s',
    substring(proj_sha256 FROM 1 FOR 2),
    substring(proj_sha256 FROM 3 FOR 2),
    proj_sha256,
    t.pipeline_id,
    t.id,
    t.file
  )
  FROM encode(digest(t.project_id::text::bytea, 'SHA256'), 'hex') AS proj_sha256
$$ LANGUAGE SQL IMMUTABLE;

This will provide a polymorphic convention to implement a to_bucket_path for multiple tables..

SELECT to_bucket_path(ci_job_artifacts) FROM ci_job_artifacts;
SELECT to_bucket_path(ci_pipeline_artifacts) FROM ci_pipeline_artifacts;