Answers: <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>
  
Answers: CC BY-SA 4.0
Answers: 2023-01-24T16:49:06.543Z
Answers: 2023-01-24T19:48:31.857Z
Answers: 2023-01-24T19:48:31.857Z
Answers: Evan Carroll
Body: <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>
  
Tags: <gitlab>
Title: How can I create a path into GitLab Object Storage from the ci_job_artifacts table?
UserDisplayName: Evan Carroll
ThreadText: # 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](https://docs.gitlab.com/ee/administration/object_storage.html).
  
  **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`](https://gitlab.com/gitlab-org/gitlab/-/blob/master/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?"*](https://devops.stackexchange.com/q/17296/18965)
  
  To calculate the rest, I've create a helper function which should greatly ease this process,
  
  ```sql
  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,
  
  ```sql
  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,
  
  ```sql
  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](https://dba.stackexchange.com/a/322504/2639) to implement a `to_bucket_path` for multiple tables..
  
  ```sql
  SELECT to_bucket_path(ci_job_artifacts) FROM ci_job_artifacts;
  SELECT to_bucket_path(ci_pipeline_artifacts) FROM ci_pipeline_artifacts;
  
  ```
  
  
  ---
  
  