[Send Account Tags] Add missing database indexes for performance

[0xBigBoss]

Parent Issue: #1515

Priority: MEDIUM 🟡

Description

Several tables are missing indexes that would improve query performance, especially as the number of tags and relationships grows.

Missing Indexes

1. referrals.tag_id

• Used in tag referral lookups
• Foreign key reference to tags.id
• Frequently queried when checking referral relationships

2. send_accounts.main_tag_id

• Used in profile and activity feed queries
• Foreign key reference to tags.id
• Queried on nearly every user profile load

3. send_account_tags foreign keys

• send_account_tags.send_account_id
• send_account_tags.tag_id
• Both are frequently used in joins

Suggested Implementation

Create a new migration file:
\\

Performance Testing

• Measure query performance before and after index creation
• Use EXPLAIN ANALYZE on common queries:
  • Profile lookup with main tag
  • Activity feed generation
  • Tag ownership checks
  • Referral lookups

Considerations

• Indexes will slightly slow down inserts/updates
• Benefits outweigh costs for read-heavy operations
• Monitor index usage after deployment

Definition of Done

• Migration file created with all indexes
• Migration tested on development database
• Query performance improved (measured)
• No negative impact on write operations
• Migration runs successfully in CI/CD