Add ci_config_author_source column to ci_project_metrics

Sahil Sharmarequested to merge
ss/596636-ci-config-author-source-column into master

What does this MR do and why?

Adds a ci_config_author_source text column to the ci_project_metrics table. This column records which agent or tool generated the CI config for a project, enabling the metrics layer to distinguish AI-generated pipeline configs from human-authored ones.

Two model-level additions accompany the column: the ai_generated scope (where.not(ci_config_author_source: nil)) for filtering records where a source was recorded, and the track_ai_generated_config! class method which upserts a row keyed on project_id. The setter validates the incoming author_source against KNOWN_AGENT_SOURCES (["ci_expert_agent/v1"]) and is a no-op for anything outside that allowlist, so arbitrary strings can never be persisted.

This MR is the persistence foundation for Identify agent-generated pipeline configs using... (#596636). The read path is deferred to a follow-up MR to keep this diff reviewable and the DB change isolated.

Issues linked

SQL queries

The ai_generated scope is defined as:

scope :ai_generated, -> { where.not(ci_config_author_source: nil) }

This generates:

SELECT "ci_project_metrics".*
FROM "ci_project_metrics"
WHERE "ci_project_metrics"."ci_config_author_source" IS NOT NULL;

The track_ai_generated_config! write path generates an upsert:

INSERT INTO "ci_project_metrics" ("project_id", "ci_config_author_source", "created_at", "updated_at")
VALUES ($1, $2, $3, $4)
ON CONFLICT ("project_id")
DO UPDATE SET "ci_config_author_source" = EXCLUDED."ci_config_author_source",
              "updated_at" = EXCLUDED."updated_at";

Execution plan

The ci_project_metrics table is new and has no data in production. The execution plan was generated locally:

EXPLAIN (ANALYZE, BUFFERS) SELECT "ci_project_metrics".* FROM "ci_project_metrics" WHERE "ci_project_metrics"."ci_config_author_source" IS NOT NULL

                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Seq Scan on ci_project_metrics  (cost=0.00..18.10 rows=806 width=72) (actual time=0.001..0.001 rows=0 loops=1)
   Filter: (ci_config_author_source IS NOT NULL)
 Planning:
   Buffers: shared hit=2
 Planning Time: 0.012 ms
 Execution Time: 0.004 ms
(6 rows)

Screenshots or screen recordings

N/A. DB-only change, no UI impact.

How to set up and validate locally

# 1. Run migrations
bin/rails db:migrate

# 2. Verify the column exists
bin/rails runner "puts Ci::ProjectMetric.column_names.include?('ci_config_author_source')"
# => true

# 3. Smoke-test the scope SQL
bin/rails runner "puts Ci::ProjectMetric.ai_generated.to_sql"

# 4. Smoke-test the setter (no-op for unknown source)
bin/rails runner "puts Ci::ProjectMetric.track_ai_generated_config!(1, author_source: 'fake').inspect"
# => nil

# 5. Smoke-test the setter (writes for known source)
bin/rails runner "Ci::ProjectMetric.track_ai_generated_config!(1, author_source: 'ci_expert_agent/v1'); puts Ci::ProjectMetric.find_by(project_id: 1).ci_config_author_source"
# => ci_expert_agent/v1

MR acceptance checklist

Database review

The database migration pipeline will be triggered before requesting database review.

Edited by Sahil Sharma

Merge request reports