Draft: Sent access token expiry notifications at 30d and 60d

Draft Status

This MR has gotten quite large. It will be broken down into smaller MRs:

1. Add new columns seven_days_notification_sent_at and indices - !165257 (merged)
2. Backfill new columns from existing data and have ExpiringWorker dual-write to new columns - !165592 (merged)
3. Add new workers with 30d and 60d notifications and feature flag to enable them - !166683 (merged)
4. Cleanup MR(s):
   • finalize backfill batched background migration
   • remove temp index for batched background migration - #485856
   • remove feature flag and legacy ExpiringWorker
   • drop expire_notification_delivered column

What does this MR do and why?

• Refactors the existing ExpiringWorker into ExpiringPersonalTokenWorker and ExpiringBotTokenWorker. Retains the old ExpiringWorker with minimal changes so that we can deploy or roll back with a feature flag.
• The new workers send notifications at 60 days and 30 days in addition to the current 7 days before expiry.
• Webhooks for project bot tokens are only run at 7 days, following the current behavior. I didn't see any reference in the webhook payload to when the token expires, so adding new notifications here might be confusing for users.
• Adds db columns to PATs for 7, 30, and 60 days expiry notifications sent. Uses a "sent at" datetime_with_zone for each delivery rather than the previous expire_notification_delivered boolean. This may make it easier to debug deliverability issues, and may allow fast querying of notification deliveries
• Migrates the previous expire_notification_delivered column to the new seven_days_notification_sent_at column. We assume notifications were sent 7 days prior to expiration, as it's our best guess as to when they were sent.
• Legacy ExpiringWorker and new workers dual-write the expire_notification_delivered and seven_days_notification_sent_at columns, so that the feature flag can be enabled and disabled to roll back as much as possible.
• Adds expiring_pats_30d_60d_notifications feature flag to enable the new feature, and roll back in case of scaling problems, unanticipated bugs, or unexpected delivery issues. Rollout issue here

TODO's

• Add query plans and migration info for database review
• Determine if migrations need to be broken out into a separate MR to make database review possible
• Determine if the new workers should be in a Authn bounded context rather than top-level PersonalAccessTokens module
• Determine if db/migrate/20240822200619_add_indices_for_pat_expiry_columns.rb can be moved to a post-migration: this may be performance-critical for the workers, so a post-migration might not be good enough
• Update documentation to include new product features

Database Review

The queries for the new workers rely on the columns introduced in this MR (once the feature flag is enabled). In order to get the Postgres.ai plans, this may have to be broken out into separate MRs. For now, here are the queries introduced:

ExpiringBotTokenWorker

Fetch PrATs and GrATS expiring in 7d or less; 8-to-30d; 31-to-60d

SELECT DISTINCT "personal_access_tokens"."user_id" FROM "personal_access_tokens" LEFT OUTER JOIN "users" ON "users"."id" = "personal_access_tokens"."user_id" WHERE "personal_access_tokens"."impersonation" = FALSE AND ("personal_access_tokens"."revoked" = FALSE OR "personal_access_tokens"."revoked" IS NULL) AND "personal_access_tokens"."seven_days_notification_sent_at" IS NULL AND "personal_access_tokens"."expire_notification_delivered" = FALSE AND (expires_at BETWEEN '2024-09-05' AND '2024-09-12') AND "users"."user_type" = 6 AND 1=1 LIMIT 100

Example query plan without seven_days_notification_sent_at field. This query is repeated with expires_at BETWEEN using 7-day, 8-to-30-day, and 31-to-60-day intervals.

Update notification sent fields

UPDATE "personal_access_tokens" SET "seven_days_notification_sent_at" = '2024-09-05 05:00:28.614924', "expire_notification_delivered" = TRUE WHERE "personal_access_tokens"."id" IN (SELECT "personal_access_tokens"."id" FROM "personal_access_tokens" LEFT OUTER JOIN "users" ON "users"."id" = "personal_access_tokens"."user_id" WHERE "personal_access_tokens"."impersonation" = FALSE AND ("personal_access_tokens"."revoked" = FALSE OR "personal_access_tokens"."revoked" IS NULL) AND "personal_access_tokens"."seven_days_notification_sent_at" IS NULL AND "personal_access_tokens"."expire_notification_delivered" = FALSE AND (expires_at BETWEEN '2024-09-05' AND '2024-09-12') AND "users"."user_type" = 6 AND 1=1 AND 1=1 LIMIT 100)

Example query plan without seven_days_notification_sent_at field. For the 7-day interval, the legacy expire_notification_delivered field is updated. For 30-day and 60-day notifications, this field is not updated, only the new columns are set:

UPDATE "personal_access_tokens" SET "thirty_days_notification_sent_at" = '2024-09-05 05:00:28.666170' WHERE "personal_access_tokens"."id" IN (SELECT "personal_access_tokens"."id" FROM "personal_access_tokens" LEFT OUTER JOIN "users" ON "users"."id" = "personal_access_tokens"."user_id" WHERE "personal_access_tokens"."impersonation" = FALSE AND ("personal_access_tokens"."revoked" = FALSE OR "personal_access_tokens"."revoked" IS NULL) AND "personal_access_tokens"."thirty_days_notification_sent_at" IS NULL AND "personal_access_tokens"."expire_notification_delivered" = FALSE AND (expires_at BETWEEN '2024-09-13' AND '2024-10-05') AND "users"."user_type" = 6 AND 1=1 AND 1=1 LIMIT 100)

ExpiringPersonalTokenWorker

Fetch PATs expiring in 7d or less

SELECT DISTINCT "personal_access_tokens"."user_id" FROM "personal_access_tokens" LEFT OUTER JOIN "users" ON "users"."id" = "personal_access_tokens"."user_id" WHERE "personal_access_tokens"."impersonation" = FALSE AND ("personal_access_tokens"."revoked" = FALSE OR "personal_access_tokens"."revoked" IS NULL) AND "personal_access_tokens"."seven_days_notification_sent_at" IS NULL AND "personal_access_tokens"."expire_notification_delivered" = FALSE AND (expires_at BETWEEN '2024-09-05' AND '2024-09-12') AND "users"."user_type" = 0 LIMIT 100

Example query plan without seven_days_notification_sent_at field. This query is repeated with expires_at BETWEEN using 8-to-30d intervals, and 31-to-60d intervals.

SELECT DISTINCT "personal_access_tokens"."user_id" FROM "personal_access_tokens" LEFT OUTER JOIN "users" ON "users"."id" = "personal_access_tokens"."user_id" WHERE "personal_access_tokens"."impersonation" = FALSE AND ("personal_access_tokens"."revoked" = FALSE OR "personal_access_tokens"."revoked" IS NULL) AND "personal_access_tokens"."thirty_days_notification_sent_at" IS NULL AND "personal_access_tokens"."expire_notification_delivered" = FALSE AND (expires_at BETWEEN '2024-09-13' AND '2024-10-05') AND "users"."user_type" = 0 LIMIT 100

SELECT DISTINCT "personal_access_tokens"."user_id" FROM "personal_access_tokens" LEFT OUTER JOIN "users" ON "users"."id" = "personal_access_tokens"."user_id" WHERE "personal_access_tokens"."impersonation" = FALSE AND ("personal_access_tokens"."revoked" = FALSE OR "personal_access_tokens"."revoked" IS NULL) AND "personal_access_tokens"."sixty_days_notification_sent_at" IS NULL AND "personal_access_tokens"."expire_notification_delivered" = FALSE AND (expires_at BETWEEN '2024-10-06' AND '2024-11-04') AND "users"."user_type" = 0 LIMIT 100

Update notification sent fields

UPDATE "personal_access_tokens" SET "seven_days_notification_sent_at" = '2024-09-05 04:54:26.813394', "expire_notification_delivered" = TRUE WHERE "personal_access_tokens"."id" IN (SELECT "personal_access_tokens"."id" FROM "personal_access_tokens" LEFT OUTER JOIN "users" ON "users"."id" = "personal_access_tokens"."user_id" WHERE "personal_access_tokens"."impersonation" = FALSE AND ("personal_access_tokens"."revoked" = FALSE OR "personal_access_tokens"."revoked" IS NULL) AND "personal_access_tokens"."seven_days_notification_sent_at" IS NULL AND "personal_access_tokens"."expire_notification_delivered" = FALSE AND (expires_at BETWEEN '2024-09-05' AND '2024-09-12') AND "users"."user_type" = 0 AND "personal_access_tokens"."user_id" = 1)

Example query plan without seven_days_notification_sent_at field. For the 7d notification, the legacy field expire_notification_delivered is set. For the 30d and 60d notifications, this field is not set:

UPDATE "personal_access_tokens" SET "thirty_days_notification_sent_at" = '2024-09-05 04:54:26.821674' WHERE "personal_access_tokens"."id" IN (SELECT "personal_access_tokens"."id" FROM "personal_access_tokens" LEFT OUTER JOIN "users" ON "users"."id" = "personal_access_tokens"."user_id" WHERE "personal_access_tokens"."impersonation" = FALSE AND ("personal_access_tokens"."revoked" = FALSE OR "personal_access_tokens"."revoked" IS NULL) AND "personal_access_tokens"."thirty_days_notification_sent_at" IS NULL AND "personal_access_tokens"."expire_notification_delivered" = FALSE AND (expires_at BETWEEN '2024-09-13' AND '2024-10-05') AND "users"."user_type" = 0 AND "personal_access_tokens"."user_id" = 1)

ExpiringWorker

The only change to ExpiringWorker is also checking the seven_days_notification_sent_at field, and updating it when the notifications are sent.

Fetch PATs to notify query

SELECT "personal_access_tokens"."user_id", "personal_access_tokens"."id" AS t0_r0, "users"."id" AS t1_r0, "users"."email" AS t1_r1, "users"."encrypted_password" AS t1_r2, "users"."reset_password_token" AS t1_r3, "users"."reset_password_sent_at" AS t1_r4, "users"."remember_created_at" AS t1_r5, "users"."sign_in_count" AS t1_r6, "users"."current_sign_in_at" AS t1_r7, "users"."last_sign_in_at" AS t1_r8, "users"."current_sign_in_ip" AS t1_r9, "users"."last_sign_in_ip" AS t1_r10, "users"."created_at" AS t1_r11, "users"."updated_at" AS t1_r12, "users"."name" AS t1_r13, "users"."admin" AS t1_r14, "users"."projects_limit" AS t1_r15, "users"."failed_attempts" AS t1_r16, "users"."locked_at" AS t1_r17, "users"."username" AS t1_r18, "users"."can_create_group" AS t1_r19, "users"."can_create_team" AS t1_r20, "users"."state" AS t1_r21, "users"."color_scheme_id" AS t1_r22, "users"."password_expires_at" AS t1_r23, "users"."created_by_id" AS t1_r24, "users"."last_credential_check_at" AS t1_r25, "users"."avatar" AS t1_r26, "users"."confirmation_token" AS t1_r27, "users"."confirmed_at" AS t1_r28, "users"."confirmation_sent_at" AS t1_r29, "users"."unconfirmed_email" AS t1_r30, "users"."hide_no_ssh_key" AS t1_r31, "users"."admin_email_unsubscribed_at" AS t1_r32, "users"."notification_email" AS t1_r33, "users"."hide_no_password" AS t1_r34, "users"."password_automatically_set" AS t1_r35, "users"."encrypted_otp_secret" AS t1_r36, "users"."encrypted_otp_secret_iv" AS t1_r37, "users"."encrypted_otp_secret_salt" AS t1_r38, "users"."otp_required_for_login" AS t1_r39, "users"."otp_backup_codes" AS t1_r40, "users"."public_email" AS t1_r41, "users"."dashboard" AS t1_r42, "users"."project_view" AS t1_r43, "users"."consumed_timestep" AS t1_r44, "users"."layout" AS t1_r45, "users"."hide_project_limit" AS t1_r46, "users"."note" AS t1_r47, "users"."unlock_token" AS t1_r48, "users"."otp_grace_period_started_at" AS t1_r49, "users"."external" AS t1_r50, "users"."incoming_email_token" AS t1_r51, "users"."auditor" AS t1_r52, "users"."require_two_factor_authentication_from_group" AS t1_r53, "users"."two_factor_grace_period" AS t1_r54, "users"."last_activity_on" AS t1_r55, "users"."notified_of_own_activity" AS t1_r56, "users"."preferred_language" AS t1_r57, "users"."theme_id" AS t1_r58, "users"."accepted_term_id" AS t1_r59, "users"."feed_token" AS t1_r60, "users"."private_profile" AS t1_r61, "users"."roadmap_layout" AS t1_r62, "users"."include_private_contributions" AS t1_r63, "users"."commit_email" AS t1_r64, "users"."group_view" AS t1_r65, "users"."managing_group_id" AS t1_r66, "users"."first_name" AS t1_r67, "users"."last_name" AS t1_r68, "users"."static_object_token" AS t1_r69, "users"."role" AS t1_r70, "users"."user_type" AS t1_r71, "users"."static_object_token_encrypted" AS t1_r72, "users"."otp_secret_expires_at" AS t1_r73, "users"."onboarding_in_progress" AS t1_r74, "users"."color_mode_id" AS t1_r75, "users"."last_access_from_pipl_country_at" AS t1_r76 FROM "personal_access_tokens" LEFT OUTER JOIN "users" ON "users"."id" = "personal_access_tokens"."user_id" WHERE "personal_access_tokens"."revoked" = FALSE AND "personal_access_tokens"."expire_notification_delivered" = FALSE AND "personal_access_tokens"."seven_days_notification_sent_at" IS NULL AND (expires_at >= CURRENT_DATE) AND (expires_at <= '2024-09-12') AND "personal_access_tokens"."impersonation" = FALSE AND "users"."user_type" = 0 LIMIT 100

Example query plan without seven_days_notification_sent_at field

Update PATs when notifications delivered

UPDATE "personal_access_tokens" SET "expire_notification_delivered" = TRUE, "seven_days_notification_sent_at" = '2024-09-05 05:26:14.033321' WHERE "personal_access_tokens"."user_id" = 1 AND "personal_access_tokens"."revoked" = FALSE AND "personal_access_tokens"."expire_notification_delivered" = FALSE AND "personal_access_tokens"."seven_days_notification_sent_at" IS NULL AND (expires_at >= CURRENT_DATE) AND (expires_at <= '2024-09-12') AND "personal_access_tokens"."impersonation" = FALSE

Example query plan without seven_days_notification_sent_at field

Fetch PrAT and GrAT tokens to notify

SELECT "personal_access_tokens"."id", "personal_access_tokens"."user_id", "personal_access_tokens"."id" AS t0_r0, "users"."id" AS t1_r0, "users"."email" AS t1_r1, "users"."encrypted_password" AS t1_r2, "users"."reset_password_token" AS t1_r3, "users"."reset_password_sent_at" AS t1_r4, "users"."remember_created_at" AS t1_r5, "users"."sign_in_count" AS t1_r6, "users"."current_sign_in_at" AS t1_r7, "users"."last_sign_in_at" AS t1_r8, "users"."current_sign_in_ip" AS t1_r9, "users"."last_sign_in_ip" AS t1_r10, "users"."created_at" AS t1_r11, "users"."updated_at" AS t1_r12, "users"."name" AS t1_r13, "users"."admin" AS t1_r14, "users"."projects_limit" AS t1_r15, "users"."failed_attempts" AS t1_r16, "users"."locked_at" AS t1_r17, "users"."username" AS t1_r18, "users"."can_create_group" AS t1_r19, "users"."can_create_team" AS t1_r20, "users"."state" AS t1_r21, "users"."color_scheme_id" AS t1_r22, "users"."password_expires_at" AS t1_r23, "users"."created_by_id" AS t1_r24, "users"."last_credential_check_at" AS t1_r25, "users"."avatar" AS t1_r26, "users"."confirmation_token" AS t1_r27, "users"."confirmed_at" AS t1_r28, "users"."confirmation_sent_at" AS t1_r29, "users"."unconfirmed_email" AS t1_r30, "users"."hide_no_ssh_key" AS t1_r31, "users"."admin_email_unsubscribed_at" AS t1_r32, "users"."notification_email" AS t1_r33, "users"."hide_no_password" AS t1_r34, "users"."password_automatically_set" AS t1_r35, "users"."encrypted_otp_secret" AS t1_r36, "users"."encrypted_otp_secret_iv" AS t1_r37, "users"."encrypted_otp_secret_salt" AS t1_r38, "users"."otp_required_for_login" AS t1_r39, "users"."otp_backup_codes" AS t1_r40, "users"."public_email" AS t1_r41, "users"."dashboard" AS t1_r42, "users"."project_view" AS t1_r43, "users"."consumed_timestep" AS t1_r44, "users"."layout" AS t1_r45, "users"."hide_project_limit" AS t1_r46, "users"."note" AS t1_r47, "users"."unlock_token" AS t1_r48, "users"."otp_grace_period_started_at" AS t1_r49, "users"."external" AS t1_r50, "users"."incoming_email_token" AS t1_r51, "users"."auditor" AS t1_r52, "users"."require_two_factor_authentication_from_group" AS t1_r53, "users"."two_factor_grace_period" AS t1_r54, "users"."last_activity_on" AS t1_r55, "users"."notified_of_own_activity" AS t1_r56, "users"."preferred_language" AS t1_r57, "users"."theme_id" AS t1_r58, "users"."accepted_term_id" AS t1_r59, "users"."feed_token" AS t1_r60, "users"."private_profile" AS t1_r61, "users"."roadmap_layout" AS t1_r62, "users"."include_private_contributions" AS t1_r63, "users"."commit_email" AS t1_r64, "users"."group_view" AS t1_r65, "users"."managing_group_id" AS t1_r66, "users"."first_name" AS t1_r67, "users"."last_name" AS t1_r68, "users"."static_object_token" AS t1_r69, "users"."role" AS t1_r70, "users"."user_type" AS t1_r71, "users"."static_object_token_encrypted" AS t1_r72, "users"."otp_secret_expires_at" AS t1_r73, "users"."onboarding_in_progress" AS t1_r74, "users"."color_mode_id" AS t1_r75, "users"."last_access_from_pipl_country_at" AS t1_r76 FROM "personal_access_tokens" LEFT OUTER JOIN "users" ON "users"."id" = "personal_access_tokens"."user_id" WHERE 1=1 AND "personal_access_tokens"."revoked" = FALSE AND "personal_access_tokens"."expire_notification_delivered" = FALSE AND "personal_access_tokens"."seven_days_notification_sent_at" IS NULL AND (expires_at >= CURRENT_DATE) AND (expires_at <= '2024-09-12') AND "personal_access_tokens"."impersonation" = FALSE AND "users"."user_type" = 6 LIMIT 100

Example query plan without seven_days_notification_sent_at field

Update PrAT and GrAT when notifications delivered

UPDATE "personal_access_tokens" SET "expire_notification_delivered" = TRUE, "seven_days_notification_sent_at" = '2024-09-05 05:26:14.073606' WHERE "personal_access_tokens"."id" IN (SELECT "personal_access_tokens"."id" FROM "personal_access_tokens" LEFT OUTER JOIN "users" ON "users"."id" = "personal_access_tokens"."user_id" WHERE 1=1 AND "personal_access_tokens"."revoked" = FALSE AND "personal_access_tokens"."expire_notification_delivered" = FALSE AND "personal_access_tokens"."seven_days_notification_sent_at" IS NULL AND (expires_at >= CURRENT_DATE) AND (expires_at <= '2024-09-12') AND "personal_access_tokens"."impersonation" = FALSE AND "users"."user_type" = 6 AND 1=1 LIMIT 100)

Example query plan without seven_days_notification_sent_at field

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Before	After

How to set up and validate locally

1. Ensure database migrations are run
2. Enable the feature flag: Feature.enable(:expiring_pats_30d_60d_notifications)
3. Create a personal access token, group access token, or project access token expiring 30 days from the current day
4. Run the appropriate workers on the command line:

   PersonalAccessTokens::ExpiringPersonalTokenWorker.new.perform
   PersonalAccessTokens::ExpiringBotTokenWorker.new.perform

5. Check the emails on your local GDK at https://gdk.test:3443/rails/letter_opener
6. Verify PersonalAccessToken.where.not(thirty_days_notification_sent_at: nil).count should be nonzero

Related to #464040 (closed)