Add pinned version FK for AI Catalog dependencies

What does this MR do and why?

A flow version needs to record which agent version it depends on. dependency_id is always set, and dependency_version_id optionally pins an exact version of that item. A composite FK from (dependency_id, dependency_version_id) to ai_catalog_item_versions (ai_catalog_item_id, id) guarantees the pinned version actually belongs to the dependency item, and the existing unique index on (version, dependency_id, organization_id) blocks duplicate dependencies on the same item, pinned or not. A model validation mirrors the FK. The FK has no ON DELETE action, so the database rejects deleting a version that a dependency row still pins; hard delete removes an item's dependent rows before destroying the item.

populate_dependencies in FlowHelper was already dead (its collaborators were removed earlier) and is deleted.

Original approach, reworked during review

This MR originally made dependency_id nullable and enforced exactly one of dependency_id or dependency_version_id via a check constraint, mirrored by a model validation. The unique index moved to (version, dependency_id, dependency_version_id, organization_id) with NULLS NOT DISTINCT, since one of the two columns was always NULL and default index semantics would treat those rows as distinct.

That shape allowed one row pointing at an item and another at a version of the same item, which is effectively a duplicate dependency. The always-set dependency_id shape closes that at the database level.

Database

Hard delete of an item now clears its dependency rows before destroying the item, in the same transaction and shape as the existing consumer cleanup: item.dependents.each_batch { |batch| batch.delete_all }. Hard delete is the admin-only force_hard_delete path; soft delete already refuses items with dependents. The table is empty in production (the feature is unreleased and nothing writes rows until !258326 (merged), which is blocked on this MR), and each flow version is capped at 20 references, so a batch is bounded by the number of flow versions that reference the item.

Statements as issued by each_batch, captured from a rolled-back run on a local instance with 20 seeded dependents. Both are served by the existing index_ai_catalog_item_version_dependencies_on_dependency_id; the planner chose a sequential scan on the 27-row local table, so the plans below were taken with enable_seqscan = off to show the index path.

Batch cursor:

SELECT "ai_catalog_item_version_dependencies"."id"
FROM "ai_catalog_item_version_dependencies"
WHERE "ai_catalog_item_version_dependencies"."dependency_id" = 1
ORDER BY "ai_catalog_item_version_dependencies"."id" ASC
LIMIT 1

Index Scan using index_ai_catalog_item_version_dependencies_on_dependency_id (actual rows=20)
  Index Cond: (dependency_id = 1)
Execution Time: 0.021 ms

Per batch delete:

DELETE FROM "ai_catalog_item_version_dependencies"
WHERE "ai_catalog_item_version_dependencies"."dependency_id" = 1
  AND "ai_catalog_item_version_dependencies"."id" >= 73
  AND "ai_catalog_item_version_dependencies"."id" < 93

Delete on ai_catalog_item_version_dependencies
  -> Index Scan using index_ai_catalog_item_version_dependencies_on_dependency_id (actual rows=20)
       Index Cond: (dependency_id = 1)
Execution Time: 0.058 ms

References

• Issue: #628830 (closed)
• Design decision: &23526 (closed)
• Blocked consumer: #628153 (closed)

Screenshots or screen recordings

Database-only change, no UI.

How to set up and validate locally

1. Run the migrations and confirm the schema:

   bundle exec rake db:migrate

2. In a rails console, confirm the constraints are enforced:

   version = Ai::Catalog::ItemVersion.first
   dep = Ai::Catalog::ItemVersionDependency.new(ai_catalog_item_version: version, organization: version.organization)
   dep.valid? # => false, dependency must exist
   
   dep.dependency = Ai::Catalog::Item.first
   dep.dependency_version = Ai::Catalog::ItemVersion.where.not(ai_catalog_item_id: dep.dependency_id).first
   dep.valid? # => false, dependency_version must be a version of the dependency item

3. Reversibility was verified locally: all three migrations migrate down and back up cleanly.

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.