<p>Aside from customer-facing documentation there is a document in the source that details how the path is created called <a href="https://gitlab.com/gitlab-org/gitlab/-/blob/master/doc/development/uploads/working_with_uploads.md" rel="nofollow noreferrer"><em>&quot;Uploads guide: Adding new uploads&quot;</em> currently at <code>doc/development/uploads/working_with_uploads.md</code></a>. This document shows the magic sauce for the bucket structure everywhere in GitLab. Specifically for the job artifacts, the structure is</p>
<pre><code>/artifacts/&lt;proj_id_hash&gt;/&lt;date&gt;/&lt;job_id&gt;/&lt;artifact_id&gt;
</code></pre>
<p>The <code>proj_id_hash</code> is the sha256 is the <code>project_id</code> you can read more about that on this question,<a href="https://devops.stackexchange.com/q/17296/18965"> <em>&quot;What is the GitLab proj_id_hash for the bucket structure in the object store?&quot;</em></a></p>
<p>To calculate the rest, I've create a helper function which should greatly ease this process,</p>
<pre class="lang-sql prettyprint-override"><code>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$$;
</code></pre>
<p>Now you can query for the path like this,</p>
<pre class="lang-sql prettyprint-override"><code>SELECT generate_job_artifact_bucket_path(project_id, created_at, job_id, id, file)
FROM ci_job_artifacts;
</code></pre>
<p>Alternatively you can do something like this,</p>
<pre class="lang-sql prettyprint-override"><code>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;
</code></pre>
<p>This will provide a <a href="https://dba.stackexchange.com/a/322504/2639">polymorphic convention</a> to implement a <code>to_bucket_path</code> for multiple tables..</p>
<pre class="lang-sql prettyprint-override"><code>SELECT to_bucket_path(ci_job_artifacts) FROM ci_job_artifacts;
SELECT to_bucket_path(ci_pipeline_artifacts) FROM ci_pipeline_artifacts;
</code></pre>

CC BY-SA 4.0
2023-01-24T16:49:06.543Z
2023-01-24T19:48:31.857Z
2023-01-24T19:48:31.857Z
Evan Carroll
<p>Gitlab has an internal PostgreSQL database. There is a client wrapper for <code>psql</code> called <code>gitlab-psql</code> that I connect to it with. This allows me to query job artifacts through the table <code>ci_job_artifacts</code>. This table schema looks like this,</p>
<pre><code>gitlabhq_production=# \d ci_job_artifacts    
                                          Table &quot;public.ci_job_artifacts&quot;
      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
</code></pre>
<p>How do I convert this to a path that I can use to find the artifact in <a href="https://docs.gitlab.com/ee/administration/object_storage.html" rel="nofollow noreferrer">GitLab Object Storage</a>.</p>

<gitlab>
How can I create a path into GitLab Object Storage from the ci_job_artifacts table?
Evan Carroll
# How can I create a path into GitLab Object Storage from the ci_job_artifacts table?

(Asked by: username_0 on 2023-01-24)

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.

**Tags:** gitlab


---

## 1 answers

### Answer by username_0 on 2023-01-24. Score: 0 | Accepted answer

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////

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;


---

