Add member expiring email notification
What does this MR do and why?
related to issue #12704 (closed)
- Add column
expiry_notified_at
tomembers
- Add cronjob
Members::ExpiringWorker
runEveryday at At 01:00
to send email notification to user when the membership in group or project is about to expire in 7 days.
This feature is under a new feature flag member_expiring_email_notification
Migration log
db:migrate
main: == [advisory_lock_connection] object_id: 223500, pg_backend_pid: 96769
main: == 20230707003301 AddExpiryNotifiedAtToMember: migrating ======================
main: -- transaction_open?()
main: -> 0.0000s
main: -- add_column("members", "expiry_notified_at", :datetime_with_timezone)
main: -> 0.0373s
main: == 20230707003301 AddExpiryNotifiedAtToMember: migrated (0.1805s) =============
main: == [advisory_lock_connection] object_id: 223500, pg_backend_pid: 96769
ci: == [advisory_lock_connection] object_id: 223820, pg_backend_pid: 96773
ci: == 20230707003301 AddExpiryNotifiedAtToMember: migrating ======================
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- add_column("members", "expiry_notified_at", :datetime_with_timezone)
ci: -> 0.0196s
ci: == 20230707003301 AddExpiryNotifiedAtToMember: migrated (0.1787s) =============
ci: == [advisory_lock_connection] object_id: 223820, pg_backend_pid: 96773
main: == [advisory_lock_connection] object_id: 223640, pg_backend_pid: 22034
main: == 20230714015909 AddIndexForMemberExpiringQuery: migrating ===================
main: -- transaction_open?()
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0965s
main: -- index_exists?(:members, [:expires_at, :access_level, :id], {:where=>"requested_at IS NULL AND expiry_notified_at IS NULL", :name=>"index_members_on_expiring_at_access_level_id", :algorithm=>:concurrently})
main: -> 0.0161s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0008s
main: -- add_index(:members, [:expires_at, :access_level, :id], {:where=>"requested_at IS NULL AND expiry_notified_at IS NULL", :name=>"index_members_on_expiring_at_access_level_id", :algorithm=>:concurrently})
main: -> 0.0129s
main: -- execute("RESET statement_timeout")
main: -> 0.0015s
main: == 20230714015909 AddIndexForMemberExpiringQuery: migrated (0.1546s) ==========
main: == [advisory_lock_connection] object_id: 223640, pg_backend_pid: 22034
ci: == [advisory_lock_connection] object_id: 223880, pg_backend_pid: 22036
ci: == 20230714015909 AddIndexForMemberExpiringQuery: migrating ===================
ci: -- transaction_open?()
ci: -> 0.0002s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0031s
ci: -- index_exists?(:members, [:expires_at, :access_level, :id], {:where=>"requested_at IS NULL AND expiry_notified_at IS NULL", :name=>"index_members_on_expiring_at_access_level_id", :algorithm=>:concurrently})
ci: -> 0.0560s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0017s
ci: -- add_index(:members, [:expires_at, :access_level, :id], {:where=>"requested_at IS NULL AND expiry_notified_at IS NULL", :name=>"index_members_on_expiring_at_access_level_id", :algorithm=>:concurrently})
ci: -> 0.0124s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0022s
ci: == 20230714015909 AddIndexForMemberExpiringQuery: migrated (0.1585s) ==========
db:rollback:main
main: == [advisory_lock_connection] object_id: 223340, pg_backend_pid: 95535
main: == 20230707003301 AddExpiryNotifiedAtToMember: reverting ======================
main: -- transaction_open?()
main: -> 0.0001s
main: -- remove_column("members", "expiry_notified_at")
main: -> 0.0644s
main: == 20230707003301 AddExpiryNotifiedAtToMember: reverted (0.1129s) =============
main: == [advisory_lock_connection] object_id: 223340, pg_backend_pid: 95535
main: == [advisory_lock_connection] object_id: 223400, pg_backend_pid: 22727
main: == 20230714015909 AddIndexForMemberExpiringQuery: reverting ===================
main: -- transaction_open?()
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.3602s
main: -- indexes(:members)
main: -> 0.1299s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0012s
main: -- remove_index(:members, {:algorithm=>:concurrently, :name=>"index_members_on_expiring_at_access_level_id"})
main: -> 0.0234s
main: -- execute("RESET statement_timeout")
main: -> 0.0021s
main: == 20230714015909 AddIndexForMemberExpiringQuery: reverted (0.6759s) ==========
main: == [advisory_lock_connection] object_id: 223400, pg_backend_pid: 22727
db:rollback:ci
ci: == [advisory_lock_connection] object_id: 223180, pg_backend_pid: 96117
ci: == 20230707003301 AddExpiryNotifiedAtToMember: reverting ======================
ci: -- transaction_open?()
ci: -> 0.0002s
ci: -- remove_column("members", "expiry_notified_at")
ci: -> 0.0076s
ci: == 20230707003301 AddExpiryNotifiedAtToMember: reverted (0.8306s) =============
ci: == [advisory_lock_connection] object_id: 223180, pg_backend_pid: 96117
ci: == [advisory_lock_connection] object_id: 223340, pg_backend_pid: 23283
ci: == 20230714015909 AddIndexForMemberExpiringQuery: reverting ===================
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.1152s
ci: -- indexes(:members)
ci: -> 0.0746s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0014s
ci: -- remove_index(:members, {:algorithm=>:concurrently, :name=>"index_members_on_expiring_at_access_level_id"})
ci: -> 0.0072s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0019s
ci: == 20230714015909 AddIndexForMemberExpiringQuery: reverted (0.3623s) ==========
ci: == [advisory_lock_connection] object_id: 223340, pg_backend_pid: 23283
How to set up and validate locally
see email preview in http://127.0.0.1:3000/rails/mailers/notify/member_about_to_expire_email
example:
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
/cc @daveliu
Edited by Linjie Zhang