Add BEFORE UPDATE trigger to repair dual sharding keys on notes

What does this MR do and why?

Adds a BEFORE UPDATE trigger on the notes table that sets NEW.namespace_id := NULL whenever a row has both namespace_id and project_id set (a "dual-key" row).

Why is this needed?

The CleanupDualShardingKeysInNotes BBM (!238033 (merged)) is nulling namespace_id on ~2.33B rows where both namespace_id and project_id are set, but it will take months to complete.

We want to add the strict num_nonnulls(namespace_id, organization_id, project_id) = 1 check constraint as NOT VALID soon (tracked in #601451), but several write paths issue plain UPDATEs that would fail on still-dirty rows once the constraint exists:

• ResolvableNote.resolve! / unresolve! (update_all)
• Note#bump_updated_at (update_columns)
• Users::MigrateRecordsToGhostUserService#migrate_notes (update_all)
• Generic note.touch

This trigger transparently repairs any dual-key row on every UPDATE, so those write paths continue to work correctly even before the BBM finishes.

Design decisions

• No-op for compliant rows: the trigger body only executes the assignment when NEW.namespace_id IS NOT NULL AND NEW.project_id IS NOT NULL, keeping overhead minimal on this very hot table.
• Organization-keyed rows are unaffected: personal snippet and abuse-report notes have project_id NULL, so the guard already excludes them.
• BEFORE UPDATE only (not INSERT): new inserts should already be written with a single sharding key; the trigger is scoped to UPDATE to avoid masking bugs in insert paths.
• with_lock_retries wraps the CREATE TRIGGER / DROP TRIGGER DDL because notes is in OverLimitTables and the migration principles require lock retries for trigger creation on high-traffic tables.

Files changed

File	Purpose
db/post_migrate/20260814000001_add_repair_dual_sharding_key_trigger_to_notes.rb	Post-deployment migration
db/structure.sql	Updated schema (function + trigger)
db/schema_migrations/20260814000001	Checksum file
spec/migrations/20260814000001_add_repair_dual_sharding_key_trigger_to_notes_spec.rb	Migration spec

References

• Issue: #614281 (closed)
• Cleanup BBM MR: !238033 (merged)
• NOT VALID constraint tracking: #601451
• Follow-up to drop this trigger: #618780

Screenshots or screen recordings

N/A - database migration only.

How to set up and validate locally

1. Run the migration: bundle exec rails db:migrate
2. In a Rails console, verify the trigger exists:

   SELECT tgname FROM pg_trigger WHERE tgrelid = 'notes'::regclass AND tgname = 'trigger_817aa51bc4f2';

3. Insert a dual-key note (bypassing the constraint) and verify an UPDATE nulls namespace_id:

   Note.connection.execute("INSERT INTO notes (noteable_type, note, project_id, namespace_id) VALUES ('Issue', 'test', 1, 1)")
   Note.last.update_columns(note: 'updated')
   Note.last.namespace_id  # => nil

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.

Database review required: This MR adds a trigger to the notes table (a high-traffic, over-limit table). Please request a database reviewer.