feat: backfill licensed users

[sjaanus]

This migration introduces a query that calculates the licensed user counts and inserts them into the licensed_users table.

The logic ensures that:

1. All users created up to a specific date are included as active users until they are explicitly deleted.
2. Deleted users are excluded after their deletion date, except when their deletion date falls within the last 30 days or before their creation date.
3. The migration avoids duplicating data by ensuring records are only inserted if they don’t already exist in the licensed_users table.

Logic Breakdown:

Identify User Events (user_events): Extracts email addresses from user-related events (user-created and user-deleted) and tracks the type and timestamp of the event. This step ensures the ability to differentiate between user creation and deletion activities. Generate a Date Range (dates): Creates a continuous range of dates spanning from the earliest recorded event up to the current date. This ensures we analyze every date, even those without events. Determine Active Users (active_emails): Links dates with user events to calculate the status of each email address (active or deleted) on a given day. This step handles:

• The user's creation date.
• The user's deletion date (if applicable).

Calculate Daily Active User Counts (result): For each date, counts the distinct email addresses that are active based on the conditions:

• The user has no deletion date.
• The user's deletion date is within the last 30 days relative to the current date.
• The user's creation date is before the deletion date.

[vercel[bot]]

The latest updates on your projects. Learn more about Vercel for Git ↗︎

Name	Status	Preview	Comments	Updated (UTC)
unleash-monorepo-frontend	✅ Ready (Inspect)	Visit Preview	💬 Add feedback	Nov 19, 2024 1:03pm

1 Skipped Deployment

| Name | Status | Preview | Comments | Updated (UTC) | | :--- | :----- | :------ | :------- | :------ | | **unleash-docs** | ⬜️ Ignored ([Inspect](https://vercel.com/unleash-team/unleash-docs/BzKUuTWaDoTytDsxRy5ZjbVycjT6)) | [Visit Preview](https://unleash-docs-git-backfill-licensed-users-unleash-team.vercel.app) | | Nov 19, 2024 1:03pm |

[github-actions[bot]]

Dependency Review

✅ No vulnerabilities or license issues or OpenSSF Scorecard issues found.

OpenSSF Scorecard

Package	Version	Score	Details

Scanned Files