Draft: POC: LFK trigger for Cells (!222469) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

This is a POC for the trigger part of making LFK deletions compatible with cells organization moving.

Problem

When moving an organization to a separate cell, we need to ensure no orphaned rows are left behind in the source or destination database. The loose foreign keys system tracks deleted records for async cleanup, but currently there's no reliable way to attribute these records to their owning organization. For Cells migration we need to reliably attribute LFK records to their owning organization. Records without sharding keys are cell-local and should not be migrated.

Solving the trigger is the first blocker to all downstream decisions: whether we split the table, sub-partition by organization, or drain the queue before migration. All paths require a trigger that dynamically captures whatever sharding keys each table defines.

Proposed Sollution

Updating the insert_into_loose_foreign_keys_deleted_records function to be dynamic. The function reads sharding key names from the trigger arguments builds and stores the JSONB at execution time.

sharding_keys_metadata: '{"organization_id": 1, "project_id": 7}'

Purpose

The sharding_keys_metadata JSONB column is used exclusively during organization migration between cells. When moving an organization to a separate cell, the sharding keys identify which LFK records belong to that organization, ensuring no orphaned data remains in the source cell and no records are missing in the destination cell. The column is not used during normal LFK cleanup processing, which continues to query by fully_qualified_table_name and primary_key_value.

Trade-offs

Positives

Single source of truth: sharding keys defined only in YAML, triggers generated from it
Fewer schema changes: adding a sharding key requires only a trigger recreation migration, no column additions
Handles all 8+ sharding key types: security_policy_management_project_id, target_project_id, group_id, user_id, etc.
Doesn't hold to a downstream topology: works whether we split tables, sub-partition, or drain-before-migrate.
No backfill needed: old records drain naturally, and new records get sharding keys.
Smaller table changes: one JSONB column replaces N bigint columns.

Negatives

Trigger args are hardcoded in schema — changing sharding keys for a table requires a migration to recreate the trigger. (It can be mitigated by an automated task that generates the SQL from YAML and specs that ensure YAML and triggers are synchronized).
Dynamic SQL in trigger: EXECUTE skips plan caching, so there's a small overhead per execution.
Sharding key changes require a data strategy: if a table's sharding key definition changes (e.g. switching from project_id to namespace_id, or adding a new key), existing unprocessed LFK records in the database still have the old sharding keys stored in. This requires a strategy: either drain the queue before deploying the new trigger, backfill existing 4. records, or accept that records created before the change will have stale keys and handle them during migration.

Alternatives considered

Approach	Why not
Config table in DB	Two sources of truth
Dedicated column per sharding key	Doesn't scale with 8+ keys (requires a column per sharding key type)

Examples

# Deleting a project (sharding key: organization_id)
Project.last.destroy

LooseForeignKeys::DeletedRecord.where(fully_qualified_table_name: 'public.projects')
=> [<LooseForeignKeys::DeletedRecord:0x0000000147834260 id: 22, primary_key_value: 18, status: "pending", created_at: "2026-02-06 14:02:20.387428000 +0000", fully_qualified_table_name: "public.projects", consume_after: "2026-02-06 14:02:20.387428000 +0000", cleanup_attempts: 0, sharding_keys_metadata: {"organization_id"=>1}>]

# Deleting a note (sharding key: organization_id, project_id, namespace_id)
Note.where.not(organization_id: nil).where.not(project_id: nil).where.not(namespace_id: nil).last.destroy

LooseForeignKeys::DeletedRecord.where(fully_qualified_table_name: 'public.notes')
=> [<LooseForeignKeys::DeletedRecord:0x00000001475d4d80 id: 26, primary_key_value: 472, status: "pending", created_at: "2026-02-06 14:07:36.620823000 +0000", fully_qualified_table_name: "public.notes", consume_after: "2026-02-06 14:07:36.620823000 +0000", cleanup_attempts: 0, sharding_keys_metadata: {"project_id"=>1, "namespace_id"=>23, "organization_id"=>1}>]

# Deleting a note (sharding key: security_policy_management_project_id
Security::ApprovalPolicyRule.last.destroy

LooseForeignKeys::DeletedRecord.where(fully_qualified_table_name: 'public.approval_policy_rules')
=> [<LooseForeignKeys::DeletedRecord:0x0000000149755f18 id: 27, primary_key_value: 1, status: "pending", created_at: "2026-02-06 14:30:10.462211000 +0000", fully_qualified_table_name: "public.approval_policy_rules", consume_after: "2026-02-06 14:30:10.462211000 +0000", cleanup_attempts: 0, sharding_keys_metadata: {"security_policy_management_project_id"=>20}>]

LFK Tables and Sharding Keys List

[
  { "ai_active_context_connections" => nil },
  { "ai_conversation_threads" => ["organization_id"] },
  { "approval_policy_rules" => ["security_policy_management_project_id"] },
  { "chat_names" => ["user_id"] },
  { "ci_pipeline_artifacts" => ["project_id"] },
  { "ci_runner_machines" => nil },
  { "ci_runners" => nil },
  { "ci_secure_files" => ["project_id"] },
  { "ci_triggers" => ["project_id"] },
  { "cluster_agents" => ["project_id"] },
  { "clusters" => ["project_id", "group_id", "organization_id"] },
  { "dependency_proxy_blobs" => ["group_id"] },
  { "duo_workflows_workflows" => ["project_id", "namespace_id"] },
  { "issues" => ["namespace_id"] },
  { "lfs_objects" => nil },
  { "merge_request_diffs" => ["project_id"] },
  { "merge_requests" => ["target_project_id"] },
  { "namespaces" => ["organization_id"] },
  { "notes" => ["namespace_id", "project_id", "organization_id"] },
  { "organizations" => ["id"] },
  { "p_ai_active_context_code_enabled_namespaces" => ["namespace_id"] },
  { "p_ci_builds" => ["project_id"] },
  { "p_ci_pipelines" => ["project_id"] },
  { "p_ci_workloads" => ["project_id"] },
  { "packages_nuget_symbols" => ["project_id"] },
  { "packages_package_files" => ["project_id"] },
  { "plans" => nil },
  { "pool_repositories" => nil },
  { "project_wiki_repositories" => ["project_id"] },
  { "projects" => ["organization_id"] },
  { "push_rules" => nil },
  { "shards" => nil },
  { "slsa_attestations" => ["project_id"] },
  { "tags" => nil },
  { "terraform_state_versions" => ["project_id"] },
  { "users" => ["organization_id"] },
  { "virtual_registries_container_upstreams" => ["group_id"] },
  { "virtual_registries_packages_maven_upstreams" => ["group_id"] },
  { "virtual_registries_packages_npm_upstreams" => ["group_id"] },
  { "vulnerabilities" => ["project_id"] }
]

Related #535508

Edited Feb 06, 2026 by Leonardo da Rosa

Draft: POC: LFK trigger for Cells