Capture deletes in subscription_user_add_on_assignments table

Background

The Data team is providing Duo seat assignment information to the GitLab sales teams to better help inform renewals.

The problem with current state of this data is that the assigned user data is often overestimated - this is because they are not able to filter out deleted records (specifically in the scenario of users that have been unassigned).

Exact ask from Data team:

In order to support Duo License Utilization analysis, we need the subscription_user_add_on_assignments to capture when records are deleted (essentially, unassignment events). Since the table doesn't currently do this, we cannot accurately account for unassignments, and as a result we will report inflated assigned users for each namespace.

What is needed?

The data team is looking for point-in-time Duo assigned user data. This means they need the following values on a daily basis:

  1. Count of Duo assigned seats - NOT including un-assigned/deleted records
  2. Count of Duo licensed seats - corresponds to Zuora subscription

This is the data we are providing for Self-managed, which works well for them.

Proposal

There are a few options that could provide the data team with the information they need for assigned user tracking that we should evaluate.

Option 1: Do not delete assignment records, instead track with is_deleted column

Add a metric to the subscription_user_add_on_assignments that allows for tracking of a deleted/un-assigned record. Rely on this column and no longer delete these records.

Having a timestamp column being extracted from source like deleted_at could potentially resolve this problem is what we are looking at. Another potential solution could be to just add a boolean flag is_deleted to figure out if a record was deleted at source and update the timestamp column updated_at when the record was deleted.

When a user is un-assigned, update the record to indicate it's deletion.

Effort required:

  • Stop deleting these records during unassignment, and during all other other deletion scenarios (such as post-expiration clean-up)
  • Add column to table and begin populating when a record is "deleted"

Option 2: Ensure PaperTrail has robust tracking of all deleted scenarios

Today, whenever user add-on assignments are destroyed, we create an entry in the database table subscription_user_add_on_assignment_versions. The data team could rely on this table for tracking point-in-time assignment data.

Effort required:

  • Not all deletion logic is currently creating entries in this table. Determine work needed to ensure ALL deletion events are tracked in PaperTrail
  • Data team to complete some work to begin using this table instead

Option 3: Provide aggregate seat assignment counts by subscription

TBD third option. where instead of modifying these existing tables, we track new metrics (maybe at the subscription level?) that show Duo assigned user count and Duo licensed user count (this one may already be provided with subscription data).

Effort required:

  • TBD
Edited by Courtney Meddaugh