[Send Account Tags] Fix database migration issues

[0xBigBoss]

Parent Issue: #1515

Priority: MEDIUM 🟡

Description

There are several database migration issues that need to be addressed for proper functionality and clean migrations.

Issues to Fix

1. Missing Column Drop

• Location: supabase/migrations/20250118103228_update_tag_functions.sql:593
• The migration references dropping referrals.tag column but this was never actually dropped
• Need to either drop the column or remove the reference

2. Function Compatibility

• Function: check_tags_allowlist_before_insert_func
• Still assumes direct user_id relationship
• Needs to be updated to work with the new send_account_tags architecture

3. Referrals Table Structure

• The referrals table still has a tag column that should be removed
• Already has tag_id column which should be used instead
• Migration 20250405121502_referrals_drop_tag_column.sql exists but may not have been applied

Required Changes

Fix 1: Handle referrals.tag column properly

-- Check if column exists before trying to drop
DO 84763 
BEGIN
  IF EXISTS (
    SELECT 1 
    FROM information_schema.columns 
    WHERE table_schema = 'public' 
    AND table_name = 'referrals' 
    AND column_name = 'tag'
  ) THEN
    ALTER TABLE public.referrals DROP COLUMN tag;
  END IF;
END 84763;

Fix 2: Update check_tags_allowlist_before_insert_func

Update the function to use auth.uid() properly and work with the new architecture.

Fix 3: Add migration to clean up any inconsistencies

Create a new migration to ensure data consistency after the rebase.

Testing Requirements

• Run all Supabase migrations on a fresh database
• Verify no errors occur during migration
• Test that tag allowlist functionality still works
• Verify referrals work with tag_id only

Definition of Done

• Referrals.tag column handled properly
• check_tags_allowlist_before_insert_func updated
• All migrations run cleanly on fresh database
• No data inconsistencies
• Tests pass