Draft: L.rosa/poc lfk table split

Table Split Analysis for LFK

The Problem

We need to know which loose_foreign_keys_deleted_records belong to an organization so we can migrate them to another cell. Right now, we have mixed data: some records have sharding keys, some don't. We also want to enforce that org-scoped records always have at least one sharding key.

What I Tried

Approach 1: Two Separate Tables (Draft: L.rosa/poc lfk table split approach 1 (!224812 - closed))

The idea was simple: split into loose_foreign_keys_deleted_records (org data) and loose_foreign_keys_deleted_records_cell_local (cell-local data).

The trigger would route inserts:

IF sharding_keys = '{}'::jsonb THEN
  INSERT INTO loose_foreign_keys_deleted_records_cell_local ...
ELSE
  INSERT INTO loose_foreign_keys_deleted_records ...
END IF;

But, it's messy:

The codebase calls LooseForeignKeys::DeletedRecord everywhere. We needed a facade pattern:

module LooseForeignKeys
  class DeletedRecord
    SHARDED_MODEL = ::LooseForeignKeys::DeletedRecordSharded
    CELL_LOCAL_MODEL = ::LooseForeignKeys::DeletedRecordCellLocal

    class << self
      delegate_missing_to :default_model

      def load_batch_for_table(table, batch_size)
        model_for_table(table).load_batch_for_table(table, batch_size)
      end

      def mark_records_processed(records)
        for_each_model_with_count(records) { |model, recs| model.mark_records_processed(recs) }
      end
      # ... more routing methods
    end
  end
end

This works, but:

1. Need SharedModel concern to share partitioning logic between both models
2. source_model attribute on records to route updates back correctly
3. delegate_missing_to for scopes like status_pending
4. Explicit routing for new, load_batch_for_table, mark_records_processed, and any other methods.
5. Adding a new method consists of altering the facade and the table concern classes
6. Two tables to manage partitions for
7. More test changes to handle both models
8. We need to delegate one of the models (CellLocal or Clusterwide) to work as the default model in the facade

Approach 2: Single Table with clusterwide Column (this MR)

Keep one table. Add a boolean column. Use a constraint to enforce sharding keys.

ALTER TABLE loose_foreign_keys_deleted_records
ADD COLUMN clusterwide BOOLEAN NOT NULL DEFAULT false;

ALTER TABLE loose_foreign_keys_deleted_records
ADD CONSTRAINT sharding_key_required_when_clusterwide
CHECK (
  clusterwide = false 
  OR num_nonnulls(organization_id, namespace_id, project_id, user_id) >= 1
);

The trigger sets clusterwide based on whether sharding keys were passed:

DECLARE
  sharding_keys JSONB := CASE WHEN TG_NARGS > 0 THEN TG_ARGV[0]::jsonb ELSE '{}'::jsonb END;
  clusterwide BOOLEAN := sharding_keys != '{}'::jsonb;
BEGIN
...

No model changes, no facade, and no model routing.

	Two Tables	Single Table + Column
Schema changes	New table + columns on existing	Columns + constraint on existing
Model changes	Facade + 2 models + concern	None
Trigger changes	Routes to different tables	Single table, sets column
Test changes	Need to handle both models	Minimal
Enforcement	Separate tables (implicit)	CHECK constraint with num_nonnulls
Partition management	Two tables	One table
Querying	Route to correct table	*Filter by clusterwide column

*The filtering will happen only upon data migrating

Why two tables add complexity

The facade pattern works, but:

1. delegate_missing_to has some limitations, like status_pending only querying to a default model
2. Records will need a source_model attribute set via after_initialize
3. Every method that creates or queries records needs routing logic
4. Partition management for sliding list partitions runs separately for each table
5. Specs need to handle both models and the facade

Why single table is simpler

• Existing model works unchanged
• Constraint enforces sharding key requirement at the database level
• Daily partitions mean table size isn't a concern
• Partitions detach once drained anyway
• No routing, no facade, no source_model tracking

My recommendation

Both approaches work. The two-table approach gives cleaner separation, while the single-table approach is simpler to implement and maintain.

If we need strict schema enforcement (separate tables), we can pick Approach 1, but we accept more complexity, maintenance overhead, and partition management.

Otherwise, Approach 2 (this MR) is a go. It's simpler, and the constraint enforces what we need, and we don't have to touch the services or workers.