Sync group_push_rules on push_rules write operations

What does this MR do and why?

Sync group_push_rules on push_rules write operations

This adds PostgreSQL triggers and functions to synchronize the group_push_rules table when there's a write operation on the push_rules table

References

Screenshots or screen recordings

Before	After

How to set up and validate locally

1. Create a push rule and link it to a group

First, we'll create a push rule and associate it with a group using the Group's push_rule_id in console:

# Create a new push rule
push_rule = PushRule.create!(
  commit_message_regex: 'test commit message',
  deny_delete_tag: false,
  author_email_regex: 'test@test.com', 
  member_check: false, 
  file_name_regex: 'filename.png', 
  max_file_size: 120,
  prevent_secrets: true,
  branch_name_regex: 'branch_name',
  reject_unsigned_commits: true, 
  commit_committer_check: true, 
  commit_message_negative_regex: 'commit_negative_3', 
  reject_non_dco_commits: false, 
  commit_committer_name_check: true
)

# Get an existing group and associate the push rule with the group
group = Group.first 
group.update!(push_rule_id: push_rule.id)

Now check if a group_push_rules record was created:

GroupPushRule.where(group_id: group.id).first

You should see a GroupPushRule record similar to push_rule:

2. Update a push rule

Update the push rule and verify that the changes are reflected in the group_push_rules table:

# Get the push rule we created
push_rule = PushRule.find(push_rule.id)

# Update the push rule
push_rule.update!(
  commit_message_regex: 'new commit message',
  max_file_size: 150
)

Now check the group_push_rules record again:

GroupPushRule.where(group_id: group.id).first

You should see that the commit_message_regex and max_file_size have been updated.

3. Change a group's push rule association

Change the group's push_rule_id to a different push rule:

# Create a second push rule
new_push_rule = PushRule.create!(
  commit_message_regex: 'another message pattern',
  deny_delete_tag: true,
  author_email_regex: 'different@example.com', 
  member_check: true, 
  file_name_regex: 'different-pattern.rb', 
  max_file_size: 200,
  prevent_secrets: false
)

# Update the group to use the new push rule
group.update!(push_rule_id: new_push_rule.id)

Check if the group_push_rules record was updated:

GroupPushRule.where(group_id: group.id).first

You should see that the group_push_rules record now reflects the new push rule's properties.

4. Remove a push rule from a group

Set the group's push_rule_id to nil and verify that the group_push_rules record is deleted:

# Remove the push rule association from the group
group.update!(push_rule_id: nil)

Now check if the group_push_rules record was deleted:

GroupPushRule.where(group_id: group.id).first

This should return nil since the group_push_rules record should have been deleted.

5. Delete a push rule that's associated with a group

First, set up a push rule and associate it with a group:

# Create a new push rule
push_rule = PushRule.create!(commit_message_regex: 'test delete scenario')

# Associate it with a group
group = Group.first
group.update!(push_rule_id: push_rule.id)

# Verify the group_push_rules record exists
GroupPushRule.where(group_id: group.id).first

Now delete the push rule and check what happens to the group and group_push_rules:

# Delete the push rule
push_rule.destroy!

# Check if the group's push_rule_id was set to `nil`
group.reload
group.push_rule_id

# Check if the group_push_rules record was deleted
GroupPushRule.where(group_id: group.id).first

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.

Related to #498934 (closed)