Project push rules: [STEP 0] Remove sync triggers for organization_id column from push_rules table

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

• Work on this issue
• Close this issue

Context

When we eventually plan to drop the organization_id column from the push_rules table, we need to remember to drop the synchronization triggers first.

The triggers are specifically designed to synchronize data between push_rules and organization_push_rules tables based on the organization_id column. Removing this column will break this synchronization mechanism.

The current triggers maintain a relationship where:

1. The INSERT/UPDATE trigger (trigger_sync_organization_push_rules_insert_update) copies data from push_rules to organization_push_rules when organization_id IS NOT NULL AND is_sample = TRUE.

2. The DELETE trigger (trigger_sync_organization_push_rules_delete) removes records from organization_push_rules when a corresponding push_rules record is deleted.

Notes:

• Simply nullifying the organization_id column would NOT automatically trigger deletion of organization push rules, but would break the synchronization logic
• Any future deletions of push rules would fail to properly clean up related organization records once the column is altered
• The conditional logic in both triggers (organization_id IS NOT NULL AND is_sample = TRUE) will no longer function as intended after column removal.

Expectations

The correct migration sequence should be:

1. Remove these triggers first
2. Handle any data migration needs
3. Finally, modify or drop the organization_id column

In this issue, we should cover the first point. This approach ensures we maintain data integrity during the migration from the old to the new structure.

Implementation plan

Create a regular migration to drop the triggers. The MR needs to be reviewed and maintained by a database team member.

References

• Migration that introduced the triggers: 20250403181313_create_push_rules_sync_triggers.rb
• Triggers to drop:

  SYNC_TRIGGER_NAME = 'trigger_sync_organization_push_rules_insert_update'
  DELETE_TRIGGER_NAME = 'trigger_sync_organization_push_rules_delete'
  
  # Functions to drop:
  SYNC_FUNCTION_NAME = 'sync_organization_push_rules_on_insert_update'
  DELETE_FUNCTION_NAME = 'sync_organization_push_rules_on_delete'