Transfer ownership of addon assignment history to Seat Management group

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

• Collaborate/take over this issue

Summary

At groupoptimize we need to use history of addon assignments for specific addon purchase and user. We implemented PaperTrail versioning for UserAddOnAssignment model, which falls under groupseat management ownership. So we need to transfer ownership of this history to groupseat management to prevent bugs and incidents like Backfill missing papertrail versions for orphan... (#566534 - closed)

Handover

1. Model: UserAddOnAssignment

The model uses PaperTrail to track version history:

• Tracks create, update, and delete events
• Stores versions in subscription_user_add_on_assignment_versions table
• Syncs version data to ClickHouse for historical analytics

3. Service: GitlabSubscriptions::Duo::BulkAssignService

Key Implementation Detail:

• Uses upsert_all for bulk operations
• Initially bypassed ActiveRecord callbacks, causing missing version records
• Fixed in issue #566532 (closed) to manually create PaperTrail versions after upsert_all

Database Schema

Primary Table: subscription_user_add_on_assignments

• Stores current state of addon assignments
• Fields: id, user_id, add_on_id, created_at, updated_at

Version Table: subscription_user_add_on_assignment_versions

• Stores historical changes via PaperTrail
• Fields include: id, item_id, event, whodunnit, object, created_at
• Synced to ClickHouse for analytics

Historical Issues and Resolutions

Issue #566532 (closed): Missing PaperTrail Versions in BulkAssignService

Status: ✅ Closed (2025-09-24)

Problem:

• GitlabSubscriptions::Duo::BulkAssignService used upsert_all which bypassed ActiveRecord callbacks
• PaperTrail requires callbacks to create version records
• Result: ~9,000 orphaned addon assignments without version records

Solution:

• Updated BulkAssignService to manually create PaperTrail version records after upsert_all
• Followed patterns from previous fixes (MR !177421 (merged), MR !178570 (merged))
• Ensures proper event type ('create' or 'update') and timestamps

Related MRs:

• MR !203662 (merged) - Create papertrail versions fix
• MR !177421 (merged) - Previous similar fix
• MR !178570 (merged) - Previous similar fix

Issue #566534 (closed): Backfill Missing PaperTrail Versions

Status: ✅ Closed (2025-10-12)

Problem:

• Existing orphaned records needed historical version data
• Initial count: ~17,009 missing assignments
• After first migration: 27 missing
• Continued growth: 67 missing (indicating ongoing issue before fix)

Solution:

• Created data migration to backfill missing version records
• Identified assignments without corresponding PaperTrail versions
• Created subscription_user_add_on_assignment_versions records with:
  • Proper event='create'
  • Correct timestamps
  • Synced to ClickHouse

Related MRs:

• MR !203303 (merged) - Backfill migration
• MR !196272 (merged) - Previous backfill work

SQL Query for Identifying Orphans:

SELECT a.id, a.created_at, a.updated_at, a.user_id, a.add_on_id
FROM subscription_user_add_on_assignments a
LEFT JOIN subscription_user_add_on_assignment_versions v ON a.id = v.item_id
WHERE v.id IS NULL
ORDER BY a.created_at DESC;

Current State Summary

✅ Completed Work

1. PaperTrail Integration: Fully implemented on UserAddOnAssignment model
2. Bug Fixes: All known issues with missing versions resolved
3. Data Backfill: Historical data backfilled and synced to ClickHouse
4. Service Updates: BulkAssignService now properly creates version records

⚒️ Outstanding work:

1. Papertrail gem ownership (MR: !214243)