Support session artifact filters on the PostgreSQL path (!252330) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

Implements all session artifact filters in Ai::DuoWorkflows::SessionArtifacts::PostgresqlFinder so they work when ClickHouse is disabled. Previously, only workflow_id worked on the PostgreSQL path; all other filters raised ArgumentError.

Filters now supported on both paths:

Filter	Backing column
`workflow_definition`	`workflow_definition`
`project_path`	`project_id` (resolved via `Project.where_full_path_in`)
`workflow_created_after` / `workflow_created_before`	`workflow_created_at` (inclusive bounds, matching ClickHouse)
`triggered_by_user_id`	`user_id`
`not: { workflow_definition, project_path, triggered_by_user_id }`	negated forms of the above

Implementation approach:

New model scopes on SessionArtifact (for_workflow_definition, excluding_workflow_definition, for_project, excluding_project, for_user, excluding_user, created_after, created_before) keep ActiveRecord calls out of the finder class.
project_path is resolved to a project_id via Project.where_full_path_in; a missing inclusion path returns an empty set, a missing exclusion path is silently skipped (nothing to exclude). Namespace-scoped rows (project_id IS NULL) are preserved when excluding a project — a plain project_id != X would drop them, so excluding_project ORs the NULL case back in.
An inclusion project_path also narrows the in-operator array scope to the project's own namespace, so the LATERAL join runs once rather than once per namespace in the group. See Array scope narrowing below.
Both resolvers now return an empty unsupported_pg_args list, so no filter raises ArgumentError when ClickHouse is disabled.

References

Closes https://gitlab.com/gitlab-org/gitlab/-/issues/624214
Parent epic: https://gitlab.com/groups/gitlab-org/-/epics/22151

Screenshots or screen recordings

N/A — backend-only change.

How to set up and validate locally

Disable ClickHouse analytics: Gitlab::ClickHouse.stub(:globally_enabled_for_analytics?, false)
Query group.duoWorkflowSessionArtifacts(workflowDefinition: "chat") via GraphQL — should return filtered results instead of raising an error.

Database review

Summary

This MR adds WHERE predicates to an existing read in Ai::DuoWorkflows::SessionArtifacts::PostgresqlFinder, and narrows the in-operator array scope when an inclusion project_path is given.

Why the plans are not informative

The table is only written when ClickHouse is disabled. ClickHouse is enabled for analytics on GitLab.com, so the worker never runs there and the table stays empty.

This code path does not execute on GitLab.com at all. It serves Self-Managed and Dedicated instances without ClickHouse configured.

If there's an established way to get a representative plan for a GitLab.com-empty table, I'm happy to redo this.

What the review can rest on instead

Since I can't create meaningful plans for this, I can walk through the SQL instead and make an argument for the decisions.

The cost I'd expect this to add is extra heap fetches inside a namespace holding many rows that fail the filter — the scan walks further down the index before finding one matching row.

Array scope narrowing for `project_path`

The in-operator optimization runs its LATERAL join once per namespace id returned by the array scope. An inclusion project_path used to leave that array at the full group hierarchy and filter project_id inside the lateral, so a group with N namespaces paid N lateral executions to read rows that live in exactly one of them.

Every artifact row carries the namespace that owns it: project rows get project.project_namespace_id, group-level rows get the group id (SessionArtifact.sync_from_workflow!, and CreateWorkflowService#container_attributes sets either project: or namespace:, never both). So the rows for one project all live in a single namespace and the array scope can be narrowed to it.

The narrowing chains onto the hierarchy scope rather than replacing it, so it stays an intersection: a project_path outside the requested hierarchy still matches nothing. The project_id predicate stays in the lateral as well. It is redundant while the invariant above holds, and is what keeps the filter correct if that ever stops being true.

The negated form deliberately does not do this. Removing the project's namespace from the array scope would also drop the project_id IS NULL rows a namespace-level session can place in that project namespace (ProjectNamespace is a Namespace), which is exactly what excluding_project exists to preserve. It would also gain little, since removing one namespace from N does not meaningfully reduce the lateral count. not: { project_path } therefore stays a row predicate.

Indexing

workflow_definition and workflow_created_at have no supporting index. I did not add one since the leading namespace_id column already bounds each scan to a single namespace, so the filter runs over a small slice and an extra index would add write cost for little gain.

If we determine in the future that this would benefit from indexing, I think we could add it then. But I'm hesitant to add one before knowing if it's a meaningful optimization.

Generated SQL

Below is the actual output of .to_sql. Placeholder values are marked.

<namespace_id> — root group whose hierarchy is being read
<project_id_include> / <project_id_exclude> — resolved from project_path (see Query 1)
<project_namespace_id_include> — project_namespace_id of the project resolved from project_path
<user_id_include> / <user_id_exclude>

Query 1 — project path resolution

project_path and not: { project_path } are resolved to a project_id in Ruby before the main query runs, via Project.where_full_path_in. The lookup is memoized, so the array scope narrowing and the row filter share this single query. Column list trimmed; the finder reads only id, full_path and project_namespace_id.

Click to view query

SELECT projects.id -- (full column list omitted)
FROM projects
WHERE projects.id IN (
  SELECT routes.source_id
  FROM routes
  WHERE routes.source_type = 'Project'
    AND (LOWER(routes.path) = LOWER('<include_path>')
      OR LOWER(routes.path) = LOWER('<exclude_path>'))
);

An inclusion path that doesn't resolve short-circuits to .none and the artifacts table is never queried. An exclusion path that doesn't resolve skips the predicate.

Query 2 — main query, all filters applied

Worst case: every filter active at once. LIMIT 20 stands in for the GraphQL page size. Note the array_cte: with an inclusion project_path it yields a single namespace id, so the LEFT JOIN LATERAL below it runs once.

Click to view query

SELECT *
FROM (
  WITH RECURSIVE "array_cte" AS MATERIALIZED (
    SELECT "namespaces"."id"
    FROM "namespaces"
    WHERE (traversal_ids @> ('{<namespace_id>}'))
      AND "namespaces"."id" = <project_namespace_id_include>
  ),
  "recursive_keyset_cte" AS (
    (
      SELECT NULL::duo_workflow_session_artifacts AS records,
             array_cte_id_array,
             duo_workflow_session_artifacts_workflow_updated_at_array,
             duo_workflow_session_artifacts_workflow_id_array,
             0::bigint AS count
      FROM (
        SELECT ARRAY_AGG("array_cte"."id") AS array_cte_id_array,
               ARRAY_AGG("duo_workflow_session_artifacts"."workflow_updated_at") AS duo_workflow_session_artifacts_workflow_updated_at_array,
               ARRAY_AGG("duo_workflow_session_artifacts"."workflow_id") AS duo_workflow_session_artifacts_workflow_id_array
        FROM (SELECT "array_cte"."id" FROM array_cte) array_cte
        LEFT JOIN LATERAL (
          SELECT "duo_workflow_session_artifacts"."workflow_updated_at" AS workflow_updated_at,
                 "duo_workflow_session_artifacts"."workflow_id" AS workflow_id
          FROM "duo_workflow_session_artifacts"
          WHERE "duo_workflow_session_artifacts"."workflow_definition" = '<definition>'
            AND "duo_workflow_session_artifacts"."workflow_definition" != '<not_definition>'
            AND "duo_workflow_session_artifacts"."workflow_created_at" >= '<created_after>'
            AND "duo_workflow_session_artifacts"."workflow_created_at" <= '<created_before>'
            AND "duo_workflow_session_artifacts"."user_id" = <user_id_include>
            AND "duo_workflow_session_artifacts"."user_id" != <user_id_exclude>
            AND "duo_workflow_session_artifacts"."project_id" = <project_id_include>
            AND ("duo_workflow_session_artifacts"."project_id" IS NULL
              OR "duo_workflow_session_artifacts"."project_id" != <project_id_exclude>)
            AND "duo_workflow_session_artifacts"."namespace_id" = "array_cte"."id"
          ORDER BY "duo_workflow_session_artifacts"."workflow_updated_at" DESC,
                   "duo_workflow_session_artifacts"."workflow_id" DESC
          LIMIT 1
        ) duo_workflow_session_artifacts ON TRUE
        WHERE "duo_workflow_session_artifacts"."workflow_updated_at" IS NOT NULL
          AND "duo_workflow_session_artifacts"."workflow_id" IS NOT NULL
      ) array_scope_lateral_query
      LIMIT 1
    )
    UNION ALL
    (
      SELECT (
               SELECT duo_workflow_session_artifacts
               FROM "duo_workflow_session_artifacts"
               WHERE "duo_workflow_session_artifacts"."workflow_id" = recursive_keyset_cte.duo_workflow_session_artifacts_workflow_id_array[position]
               LIMIT 1
             ),
             array_cte_id_array,
             recursive_keyset_cte.duo_workflow_session_artifacts_workflow_updated_at_array[:position_query.position-1]
               || next_cursor_values.workflow_updated_at
               || recursive_keyset_cte.duo_workflow_session_artifacts_workflow_updated_at_array[position_query.position+1:],
             recursive_keyset_cte.duo_workflow_session_artifacts_workflow_id_array[:position_query.position-1]
               || next_cursor_values.workflow_id
               || recursive_keyset_cte.duo_workflow_session_artifacts_workflow_id_array[position_query.position+1:],
             recursive_keyset_cte.count + 1
      FROM recursive_keyset_cte,
      LATERAL (
        SELECT workflow_updated_at, workflow_id, position
        FROM UNNEST(duo_workflow_session_artifacts_workflow_updated_at_array,
                    duo_workflow_session_artifacts_workflow_id_array)
             WITH ORDINALITY AS u(workflow_updated_at, workflow_id, position)
        WHERE workflow_updated_at IS NOT NULL
          AND workflow_id IS NOT NULL
        ORDER BY 1 DESC, 2 DESC
        LIMIT 1
      ) AS position_query,
      LATERAL (
        SELECT "record"."workflow_updated_at", "record"."workflow_id"
        FROM (VALUES (NULL, NULL)) AS nulls
        LEFT JOIN (
          SELECT "duo_workflow_session_artifacts"."workflow_updated_at" AS workflow_updated_at,
                 "duo_workflow_session_artifacts"."workflow_id" AS workflow_id
          FROM "duo_workflow_session_artifacts"
          WHERE "duo_workflow_session_artifacts"."workflow_definition" = '<definition>'
            AND "duo_workflow_session_artifacts"."workflow_definition" != '<not_definition>'
            AND "duo_workflow_session_artifacts"."workflow_created_at" >= '<created_after>'
            AND "duo_workflow_session_artifacts"."workflow_created_at" <= '<created_before>'
            AND "duo_workflow_session_artifacts"."user_id" = <user_id_include>
            AND "duo_workflow_session_artifacts"."user_id" != <user_id_exclude>
            AND "duo_workflow_session_artifacts"."project_id" = <project_id_include>
            AND ("duo_workflow_session_artifacts"."project_id" IS NULL
              OR "duo_workflow_session_artifacts"."project_id" != <project_id_exclude>)
            AND "duo_workflow_session_artifacts"."namespace_id" = recursive_keyset_cte.array_cte_id_array[position]
            AND (("duo_workflow_session_artifacts"."workflow_updated_at", "duo_workflow_session_artifacts"."workflow_id")
                 < (recursive_keyset_cte.duo_workflow_session_artifacts_workflow_updated_at_array[position],
                    recursive_keyset_cte.duo_workflow_session_artifacts_workflow_id_array[position]))
          ORDER BY "duo_workflow_session_artifacts"."workflow_updated_at" DESC,
                   "duo_workflow_session_artifacts"."workflow_id" DESC
          LIMIT 1
        ) record ON TRUE
        LIMIT 1
      ) AS next_cursor_values
    )
  )
  SELECT (records).*
  FROM "recursive_keyset_cte" AS "duo_workflow_session_artifacts"
  WHERE (count <> 0)
) duo_workflow_session_artifacts
LIMIT 20;

Without a project_path, the array_cte keeps only the traversal_ids predicate and is unchanged from before this MR.

Query 3 — inner LATERAL body in isolation

This is the part the MR actually changes. The recursive CTE runs it once per namespace in the array scope per page — which, for an inclusion project_path, is now one namespace.

Click to view query

SELECT duo_workflow_session_artifacts.*
FROM duo_workflow_session_artifacts
WHERE duo_workflow_session_artifacts.workflow_definition = '<definition>'
  AND duo_workflow_session_artifacts.workflow_definition != '<not_definition>'
  AND duo_workflow_session_artifacts.workflow_created_at >= '<created_after>'
  AND duo_workflow_session_artifacts.workflow_created_at <= '<created_before>'
  AND duo_workflow_session_artifacts.user_id = <user_id_include>
  AND duo_workflow_session_artifacts.user_id != <user_id_exclude>
  AND duo_workflow_session_artifacts.project_id = <project_id_include>
  AND (duo_workflow_session_artifacts.project_id IS NULL
    OR duo_workflow_session_artifacts.project_id != <project_id_exclude>)
  AND duo_workflow_session_artifacts.namespace_id = <project_namespace_id>
ORDER BY duo_workflow_session_artifacts.workflow_updated_at DESC,
         duo_workflow_session_artifacts.workflow_id DESC
LIMIT 1;

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Edited Sep 18, 2026 by Scott Hampton

Support session artifact filters on the PostgreSQL path

What does this MR do and why?

References

Screenshots or screen recordings

How to set up and validate locally

Database review

Summary

Why the plans are not informative

What the review can rest on instead

Array scope narrowing for project_path

Indexing

Generated SQL

Query 1 — project path resolution

Query 2 — main query, all filters applied

Query 3 — inner LATERAL body in isolation

MR acceptance checklist

Merge request reports

Array scope narrowing for `project_path`