Replicate and expose the Duo Workflow drop reason

Chandra Saripakarequested to merge
631550-dap-session-failure-reason-clickhouse into master

What does this MR do and why?

Part of #631550, which asks why about 33,000 Developer flow sessions on the DAP Impact dashboard show as "Failed" with no reason.

This MR carries the drop_reason column into the enriched ClickHouse table and exposes it in the Duo Workflows aggregation engine. The Postgres column, its Siphon replication and the drop sites landed on master in !256455 (merged) (text column, string vocabulary: job_failed, no_status_reported, no_activity, workload_not_created, api_reported, preconditions_not_met, start_failed), which superseded !260682 (closed). This MR now targets master.

Changes:

  • Four ClickHouse migrations in db/click_house/migrate/main/:
    1. Add drop_reason LowCardinality(Nullable(String)) to duo_workflows_workflows_enriched, placed AFTER execution_mode. The Siphon column is Nullable(String); the views' APPEND TO insert converts it. The column stays Nullable because the engine treats NULL as "no reason recorded".
    2. Drop and recreate duo_workflows_workflows_enriched_delta_mv with drop_reason in the column list and the SELECT.
    3. Do the same for duo_workflows_workflows_enriched_mr_outcomes_mv.
    4. Backfill drop_reason on enriched rows that already exist (see the database review notes).
  • In both views, the dedup CTE uses argMax(drop_reason, ...) and the projection uses wf.drop_reason / unresolved.drop_reason. Refreshable materialized views cannot be altered, so recreating them is the only way to add a column. down recreates the original definitions. The view definitions are otherwise copied unchanged from db/click_house/main.sql.
  • db/click_house/main.sql and the schema cache file db/click_house/schema_cache/main/duo_workflows_workflows_enriched.yml are regenerated. The diff is only the new column. The engine reads its column list from the schema cache, so the cache must be updated for the dedup subquery to include the column.
  • ee/app/models/analytics/aggregation_engines/duo_workflows.rb:
    • DROP_REASONS reuses Ai::DuoWorkflows::Workflow::DROP_REASONS, so the engine cannot drift from the model's vocabulary.
    • New drop_reason dimension (string). Values pass through as stored; NULL stays NULL.
    • New drop_reason filter (exact match) and drop_reason_not filter (exclusion, max 100 values). Both keep only names in the vocabulary, so unrecognized names match nothing and exclude nothing, like the existing status filters.
  • In GraphQL these are DuoWorkflowsAggregationResponseDimensions.dropReason and the dropReason / dropReasonNot arguments. Rows with no reason are matched by neither dropReason nor dropReasonNot: NOT IN has SQL NULL semantics, the same as the existing projectIdNot.
  • doc/api/graphql/reference/_index.md is regenerated. doc/user/glql/data_sources/duo_workflows.md gains the dropReason filter and dimension. GLQL usage depends on the GLQL compiler change (glql!552 (merged)) and the package bump in MR 4.

Database review notes

  • Rows for flows that failed before !256455 (merged) merged (2026-10-11) have drop_reason NULL by construction. Postgres has no record of the cause, so there is nothing to backfill for them.
  • Rows for flows that failed after that merge do have the reason in siphon_duo_workflows_workflows, but their enriched rows were emitted before the column existed and read as NULL after ADD COLUMN. The delta view never re-emits them, because it only scans Siphon rows changed in its window and a failed flow is terminal. Migration 20261015085307 re-emits the current enriched row for each of those ids with the reason filled in, every other column taken with argMax(..., _version) so the later_* arrays are kept, and _version set to max(_version) + 1 microsecond, the same stamping rule as the mr_outcomes view. It skips rows that already carry a reason, so re-running it is idempotent; down is a no-op because the re-emitted rows differ only by _version. The affected set is bounded by failures over a few days, so it is a single INSERT ... SELECT.
  • The column has no default and no index.
  • The recreated views repopulate on their normal refresh schedule.

Tests

Three new examples in ee/spec/models/analytics/aggregation_engines/duo_workflows_spec.rb:

  • Dimension grouping, including the NULL bucket, and a two-version flow that checks the dedup subquery reads drop_reason from the latest version.
  • Positive filter.
  • Negative filter, which also drops NULL rows.
  • Unknown names in either filter (match nothing, exclude nothing), and a mixed list of known and unknown names (only the known ones apply).

spec/db/click_house/migrate/20261015085307_backfill_drop_reason_on_duo_workflows_workflows_enriched_spec.rb runs the backfill migration against fixture rows: the reason is filled only where the latest Siphon row has one and the enriched row has none, the later_* arrays survive, the re-emitted row sits one microsecond above the previous version, rows with no reason, an existing reason or a retry-cleared reason stay as they were, and a second run changes nothing.

I wrote them first and watched them fail with "the specified identifier is not available: 'drop_reason'". The fixture builder now inserts drop_reason. After !256455 (merged) merged, the type moved from an integer enum to the text vocabulary and the examples were updated to the stored names.

How to validate

  1. Run the ClickHouse migrations:

    bundle exec rake gitlab:clickhouse:migrate

  2. Run the engine spec:

    bundle exec rspec ee/spec/models/analytics/aggregation_engines/duo_workflows_spec.rb

  3. Run the Siphon schema spec (unchanged by this MR, kept as a guard):

    bundle exec rspec spec/db/clickhouse_siphon_tables_spec.rb

Related to #631550

Implementation plan (from the 4-MR plan for #631550)

MR 2 — Replicate to ClickHouse (gitlab-org/gitlab)

Stacked on MR 1. The Siphon table column itself ships in MR 1: spec/db/clickhouse_siphon_tables_spec.rb fails any MR that adds a Postgres column to a replicated table without the ClickHouse column, so the Siphon migration cannot be split out. MR 2 adds the enriched column, the views, and the engine.

Task 5: Siphon column, enriched column, refresh MVs

Files:

  • Create: db/click_house/migrate/main/<ts1>_add_drop_reason_to_siphon_duo_workflows_workflows.rb
  • Create: db/click_house/migrate/main/<ts2>_add_drop_reason_to_duo_workflows_workflows_enriched.rb
  • Create: db/click_house/migrate/main/<ts3>_recreate_duo_workflows_workflows_enriched_delta_mv_with_drop_reason.rb
  • Create: db/click_house/migrate/main/<ts4>_recreate_duo_workflows_workflows_enriched_mr_outcomes_mv_with_drop_reason.rb
  • Modify: db/click_house/main.sql (generated)
  • Test: spec/db/clickhouse_siphon_tables_spec.rb (existing, must stay green), new example in ee/spec/models/analytics/aggregation_engines/duo_workflows_spec.rb in Task 6 covers the data path.

Interfaces:

  • Produces: drop_reason Nullable(Int16) on siphon_duo_workflows_workflows and duo_workflows_workflows_enriched, copied by both refresh MVs.

  • Step 1: Siphon table column

# frozen_string_literal: true

class AddDropReasonToSiphonDuoWorkflowsWorkflows < ClickHouse::Migration
  def up
    execute <<~SQL
    ALTER TABLE siphon_duo_workflows_workflows
      ADD COLUMN IF NOT EXISTS drop_reason Nullable(Int16);
    SQL
  end

  def down
    execute <<~SQL
    ALTER TABLE siphon_duo_workflows_workflows
      DROP COLUMN IF EXISTS drop_reason;
    SQL
  end
end
  • Step 2: Enriched table column

Same shape against duo_workflows_workflows_enriched, placed after execution_mode with AFTER execution_mode so main.sql stays readable.

  • Step 3: Recreate the two refresh MVs

Copy the full CREATE MATERIALIZED VIEW duo_workflows_workflows_enriched_delta_mv ... statement from db/click_house/main.sql (around line 4047) into the migration, add `drop_reason` Nullable(Int16), to the column list after execution_mode, and add drop_reason to the SELECT list in the same position. The migration body is:

  def up
    execute 'DROP VIEW IF EXISTS duo_workflows_workflows_enriched_delta_mv'
    execute <<~SQL
      <full CREATE MATERIALIZED VIEW statement with the new column>
    SQL
  end

  def down
    execute 'DROP VIEW IF EXISTS duo_workflows_workflows_enriched_delta_mv'
    execute <<~SQL
      <the statement exactly as it is in main.sql today>
    SQL
  end

Do the same for duo_workflows_workflows_enriched_mr_outcomes_mv (around line 4287). Refreshable MVs are dropped and recreated, there is no ALTER for them.

  • Step 4: Run the ClickHouse migrations locally and regenerate main.sql
./tmp/run.sh bundle exec rake gitlab:clickhouse:migrate
RAILS_ENV=test ./tmp/run.sh bundle exec rake gitlab:clickhouse:migrate
git diff --stat db/click_house/main.sql

Expected: main.sql gains the column in three tables/views and both MV definitions change only by the new column.

  • Step 5: Run the Siphon schema spec
./tmp/run.sh bundle exec rspec spec/db/clickhouse_siphon_tables_spec.rb

Expected: pass. If it reports a type mismatch, check Gitlab::ClickHouse::SiphonGenerator::PG_TYPE_MAP for smallint.

  • Step 6: Commit
git add db/click_house
git commit -m "Replicate Duo Workflow drop_reason to ClickHouse"

Task 6: Engine dimension and filter

Files:

  • Modify: ee/app/models/analytics/aggregation_engines/duo_workflows.rb:21-54 (dimensions) and :134-145 (filters)
  • Test: ee/spec/models/analytics/aggregation_engines/duo_workflows_spec.rb
  • Modify: doc/api/graphql/reference/_index.md (generated by bundle exec rake gitlab:graphql:compile_docs)
  • Modify: doc/user/glql/data_sources/duo_workflows.md (add the field to the dimensions and filters tables)

Interfaces:

  • Produces: dimension drop_reason (GraphQL dropReason, String, formatted name) and filter dropReason: [String!] / dropReasonNot.

  • Step 1: Write the failing specs

In duo_workflows_spec.rb, inside describe 'dimensions':

    describe 'drop_reason' do
      let(:flows_data) do
        [
          { id: 1, status: 3, drop_reason: 1, created_at: 10.days.ago },
          { id: 2, status: 3, drop_reason: 3, created_at: 10.days.ago },
          { id: 3, status: 3, drop_reason: nil, created_at: 10.days.ago },
          { id: 4, status: 2, drop_reason: nil, created_at: 10.days.ago }
        ]
      end

      it 'groups failed flows by the recorded reason, with unknown as nil' do
        request = { dimensions: [{ identifier: :drop_reason }], metrics: [{ identifier: :total_count }] }

        expect(engine).to execute_aggregation(request).and_return(an_array_matching([
          { drop_reason: 'timeout', total_count: 1 },
          { drop_reason: 'ci_failure', total_count: 1 },
          { drop_reason: nil, total_count: 2 }
        ]))
      end
    end

Inside describe 'filters':

    describe 'drop_reason' do
      let(:flows_data) do
        [
          { id: 1, status: 3, drop_reason: 1, created_at: 10.days.ago },
          { id: 2, status: 3, drop_reason: 3, created_at: 10.days.ago }
        ]
      end

      it 'filters by reason name' do
        request = { filters: [{ identifier: :drop_reason, values: ['ci_failure'] }], metrics: [{ identifier: :total_count }] }

        expect(engine).to execute_aggregation(request).and_return([{ total_count: 1 }])
      end
    end

Use the status integer for failed from Ai::DuoWorkflows::Workflow.state_machines[:status].states[:failed].value instead of the literal 3 if the fixture helper does not map names. Add drop_reason: flow[:drop_reason] to the clickhouse_fixture row builder at the top of the spec.

  • Step 2: Run them to see them fail
./tmp/run.sh bundle exec rspec ee/spec/models/analytics/aggregation_engines/duo_workflows_spec.rb -e 'drop_reason'

Expected: FAIL, unknown identifier drop_reason.

  • Step 3: Implement

In the engine, after column :status:

        column :drop_reason, :string,
          description: 'Why a failed flow was dropped: timeout, reconcile, ci_failure, start_failure, ' \
            'agent_error. `null` for flows that did not fail or whose drop did not record a reason',
          formatter: ->(value) { ::Ai::DuoWorkflows::Workflow::DROP_REASONS.key(value)&.to_s }

In filters, after exact_match :status:

        exact_match :drop_reason, :string,
          description: 'Filter by one or many drop reasons (timeout, reconcile, ci_failure, start_failure, agent_error)',
          formatter: ->(values) { ::Ai::DuoWorkflows::Workflow::DROP_REASONS.values_at(*values.map(&:to_sym)).compact }

Mirror how status maps names to integers in this file (STATUSES.values_at), and add the exact_not_match twin if status has one.

  • Step 4: Run the engine spec, regenerate GraphQL docs
./tmp/run.sh bundle exec rspec ee/spec/models/analytics/aggregation_engines/duo_workflows_spec.rb
./tmp/run.sh bundle exec rake gitlab:graphql:compile_docs

Expected: spec green; doc/api/graphql/reference/_index.md gains DuoWorkflowsAggregationResponseDimensions.dropReason and the dropReason / dropReasonNot arguments.

  • Step 5: Update the GLQL data source doc

In doc/user/glql/data_sources/duo_workflows.md, add dropReason to the dimensions table and the filters table with the five values and the null meaning. US spelling, serial comma, no bold for emphasis.

  • Step 6: Commit and open MR 2
git add ee/app/models/analytics/aggregation_engines/duo_workflows.rb ee/spec doc
git commit -m "Expose Duo Workflow drop reason in the aggregation engine"

Title [skip ci] Replicate and expose the Duo Workflow drop reason. Labels from the issue plus backend, database, clickhouse. Request ~database review; state that drop_reason on rows written before MR 1 is NULL by construction.

Edited by Chandra Saripaka

Merge request reports