52433 Create ai_troubleshoot_job_event table in CH

What does this MR do and why?

we need to store /troubleshoot events in Clickhouse as we we want to correlate the adoption of Duo RCA with decreasing lead and cycle time and allowing to add tracking of Root cause analysis of unique users

Technical approach:

  • Creating ai_troubleshoot_job_daily_events to be used for data aggregation via the materialized view created
  • ai_troubleshoot_job_daily has the payload field, while the daily table as it doesn't fit the use case
  • Following the same pattern as code_suggestion_daily_events and duo_chat_daily_events
  • We still need ai_troubleshoot_job_daily if we needed to check the payload
  • Added unique_users_count field as to build up on #486523 (closed)

How to test locally:

  • Run the migration: bundle exec rake gitlab:clickhouse:migrate
  • Run gdk console
  • Create a Ai::TroubleshootJobEvent record:
event = Ai::TroubleshootJobEvent.new(
  user_id: User.last.id,
  job_id: Ci::Build.last.id,
  event: :troubleshoot_job,
  timestamp: DateTime.current
)

event.payload = { 
  pipeline_id: 1,
  merge_request_id: 1   
}

event.store_to_pg 
event.store_to_clickhouse

UsageEvents::DumpWriteBufferCronWorker.new.perform 
ClickHouse::DumpAllWriteBuffersCronWorker.new.perform
  • Testing data on clickhouse:
$ gdk clickhouse
  • Run Select query:
SELECT *
FROM gitlab_clickhouse_development.ai_troubleshoot_job_events

--- 

Query id: d61eb1e5-41eb-43ba-9c7e-1ef45e0ccc34

   ┌────────id─┬──────────────────timestamp─┬─user_id─┬─job_id─┬─project_id─┬─event─┬─namespace_path────────────┬─pipeline_id─┬─merge_request_id─┐
1. │ 123456789 │ 2025-03-28 21:27:34.806000 │      42 │  78901 │       5432 │     1 │ namespace/project         │       23456 │             7890 │
2. │ 123456790 │ 2025-03-28 21:27:34.806000 │      43 │  78902 │       5433 │     1 │ namespace/another-project │       23457 │             7891 │
   └───────────┴────────────────────────────┴─────────┴────────┴────────────┴───────┴───────────────────────────┴─────────────┴──────────────────┘
  • Testing via the Gitlab::Tracking::AiTracking:
job = Ci::Build.last 
user = job.user
job_attrs = job.attributes.transform_keys { |k| "job_#{k}" }

Gitlab::Tracking::AiTracking.track_event('troubleshoot_job', **job_attrs, user: user)
ClickHouse::DumpWriteBufferWorker.new.perform(Ai::TroubleshootJobEvent.clickhouse_table_name)
UsageEvents::DumpWriteBufferCronWorker.new.perform

# To Verify:
ClickHouse::Client.select("select * from #{Ai::TroubleshootJobEvent.clickhouse_table_name}", :main)
Ai::TroubleshootJobEvent.last

Related to #524333 (closed)

Edited by Amr Taha

Merge request reports

Loading