Org Web Adapter

Editing pages/shortcut_226964_policy_renewal_at_wrong_time_spams_payments.org

:PROPERTIES:
:ID:       91cc038a-e68a-4ebc-8a2b-a79de950092f
:END:
#+title: Shortcut 226964 - Policy Renewal at wrong time spams payments

POLICY NUMBER: TAVFC2NPRU

First blush, this looks like a renewal that for some reason got stuck and did
not renew when it was supposed to and then, once it finally got un-stuck, all the
monthly schedule items were processed all one after the other.

On closer inspection, this does not appear to be a stuck policy, per-se, but
rather it looks more like the third and fourth terms of the user's policy were
not generated after the end of the second policy.

Charge event kicks in starting on April 14. Walking through with Casper and
[[id:c86da29b-fa63-4208-8e71-16e62116c6e1][Emmanuel Gasquez]] and it appears that a random "Policy Event" started
happening every day on this policy on April 14.

* Links
** RCA doc: https://www.notion.so/sure/RCA-Toggle-policy-renewal-charges-at-wrong-date-6f1160a75f864fa090ee5fe36784a96f
** Ticket: https://app.shortcut.com/sure/story/226964/
** Datadog: [[https://app.datadoghq.com/logs?query=renew%20%40params.policy_id%3A%22UUID%28%275cb3a258-813f-491d-8f29-e915f38e2003%27%29%22&cols=host%2Cservice&event=AQAAAYY4oT35WeBZ7wAAAABBWVk0b1ZFcUFBREhaUnJ2UVpqaUNRR1k&index=%2A&messageDisplay=inline&stream_sort=time%2Cdesc&viz=stream&from_ts=1675918800000&to_ts=1676091540000&live=false][renew [called] log message for this policy]]
* Questions
** When did the undesired behavior start? :answered:
February 10, 2023
** Did the user expect to have an active policy after their second term (May 2022)? :unanswered:
** Why were the eight payments unexpected for the user? :unanswered:
** What behavior did the user experience that was undesirable? :answered:

The user was charged for eight payments on February 10, 2023 that were unexpected.

*** Why were the payments unexpected for the user? :unanswered:

* Investigation
** Policy 78ab0bf3-c3a4-46d0-ade8-e9ac728ccd5e
+ Term 4
+ Starts 2022 12 01
+ Ends 2023 06 01
+ Created 2023 02 10
+ Updated 2023 02 12
+ Schedule items
  1. Due 2023-12-01, processed at: 2023-02-10 00:00:08.724741
  2. Due 2023-01-01, processed at: 2023-02-10 00:00:08.724741
  3. Due 2023-02-01, processed at: 2023-02-10 00:00:08.724741
  4. Due 2023-03-01, processed at: None
  5. Due 2023-04-01, processed at: None
  6. Due 2023-05-01, processed at: None

** Policy 7bd160f4-ea38-4dbb-b3de-fafb04e80aa7
+ Term 3
+ Start 2022 06 01
+ Ends 2022 12 01
+ Created 2023 02 10
+ Updated 2023 02 10
+ Schedule items
  1. Due 2022-06-01, processed at: 2023-02-10 00:00:08.724741+00:00
  2. Due 2022-07-01, processed at: 2023-02-10 00:00:08.724741+00:00
  3. Due 2022-08-01, processed at: 2023-02-10 00:00:08.724741+00:00
  4. Due 2022-09-01, processed at: 2023-02-10 00:00:08.724741+00:00
  5. Due 2022-10-01, processed at: 2023-02-10 00:00:08.724741+00:00
  6. Due 2022-11-01, processed at: 2023-02-10 00:00:08.724741+00:00

** All schedule items for every version of this policy
Some of this is quite telling. For starters, this didn't start with Term 4 ...
three payments from Term 4 were part of this glitch, but ALL the payments from
Term 3 were backed up.

In point of fact, things start at 16 below, which means the start of the logjam
was with the creation of the policies third term. Which, if we notice above us,
actually happened on February 10, 2023.

That means it's likely that the third term was never created after T2 ... not
sure how to tell if a policy is supposed to lapse, but maybe they didn't notice
they weren't being charged because they didn't think they'd renewed the policy
(and hadn't) but then on Feb. 10, something happened and Term 3 and Term 4 were
both created and all the payments that would have been processed monthly were
charged all at once.

1. 2021-06-01   2021-05-30 19:01:53.406197+00:00
2. 2021-07-01   2021-07-01 00:01:13.909646+00:00
3. 2021-08-01   2021-08-01 00:01:20.166968+00:00
4. T1 2021-09-01   2021-09-01 00:01:05.377557+00:00
5. T1 2021-09-01   None
6. T1 2021-10-01   2021-10-01 00:00:58.297472+00:00
7. T1 2021-10-01   None
8. T1 2021-11-01   None
9. T1 2021-11-01   2021-11-01 00:01:19.725005+00:00
10. T2 2021-12-01   2021-12-01 00:01:01.389252+00:00
11. T2 2022-01-01   2022-01-01 00:01:09.568572+00:00
12. T2 2022-02-01   2022-02-01 00:01:26.739252+00:00
13. T2 2022-03-01   2022-03-01 00:01:33.060496+00:00
14. T2 2022-04-01   2022-04-01 00:01:19.773903+00:00
15. T2 2022-05-01   2022-05-01 00:01:23.561947+00:00
16. # Here's where our trouble began
17. T3 2022-06-01   2023-02-10 00:00:08.724741+00:00
18. T3 2022-07-01   2023-02-10 00:00:08.724741+00:00
19. T3 2022-08-01   2023-02-10 00:00:08.724741+00:00
20. T3 2022-09-01   2023-02-10 00:00:08.724741+00:00
21. T3 2022-10-01   2023-02-10 00:00:08.724741+00:00
22. T3 2022-11-01   2023-02-10 00:00:08.724741+00:00
23. T4 2022-12-01   2023-02-10 00:00:08.724741+00:00
24. T4 2023-01-01   2023-02-10 00:00:08.724741+00:00
25. T4 2023-02-01   2023-02-10 00:00:08.724741+00:00
26. T4 2023-03-01   None
27. T4 2023-04-01   None
28. T4 2023-05-01   None
* Raw data

The four policy docs associated with the policy number in question:
#+begin_src python
In [12]: p[0].__dict__
Out[12]:
{'_state': <django.db.models.base.ModelState at 0x7fa838335e10>,
 'deleted': None,
 'id': UUID('78ab0bf3-c3a4-46d0-ade8-e9ac728ccd5e'),
 'carrier_id': UUID('8cb42ac6-0b2f-4b58-afc3-8907fe747620'),
 'brokerage_id': None,
 'broker_id': None,
 'client_id': UUID('08245060-baae-44f3-9d87-2135929fdff5'),
 'api_consumer_id': UUID('176235a6-f615-4291-8cf0-077c74eb1a57'),
 'vertical_id': 104,
 'partner_id': 1,
 'plan_id': UUID('cd0ca836-50d7-4adc-8d4c-57c815d9a041'),
 'rating_address_id': UUID('695f8639-b152-445d-a891-20ddcf492205'),
 'service_id': UUID('eed71ee1-63fe-48c7-bd8c-09ed4d7d1753'),
 'payment_method_id': UUID('a1bc5794-bc26-4098-9bec-40fcd76c728e'),
 'details': <PolicyDetails instance>,
 'status': 'inforce',
 'canceled_at': None,
 'billing_cadence': 'monthly',
 'policy_number': 'TAVFC2NPRU',
 'starts_at': datetime.datetime(2022, 12, 1, 0, 0, tzinfo=<UTC>),
 'ends_at': datetime.datetime(2023, 6, 1, 0, 0, tzinfo=<UTC>),
 'term_number': 4,
 'created_at': datetime.datetime(2023, 2, 10, 0, 7, 28, 918961, tzinfo=<UTC>),
 'updated_at': datetime.datetime(2023, 2, 12, 16, 51, 1, 933212, tzinfo=<UTC>),
 'updates_locked_by_user_id': None,
 'updates_locked_at': None}

In [13]: p[1].__dict__
Out[13]:
{'_state': <django.db.models.base.ModelState at 0x7fa8383345b0>,
 'deleted': None,
 'id': UUID('7bd160f4-ea38-4dbb-b3de-fafb04e80aa7'),
 'carrier_id': UUID('8cb42ac6-0b2f-4b58-afc3-8907fe747620'),
 'brokerage_id': None,
 'broker_id': None,
 'client_id': UUID('08245060-baae-44f3-9d87-2135929fdff5'),
 'api_consumer_id': UUID('176235a6-f615-4291-8cf0-077c74eb1a57'),
 'vertical_id': 104,
 'partner_id': 1,
 'plan_id': UUID('cd0ca836-50d7-4adc-8d4c-57c815d9a041'),
 'rating_address_id': UUID('695f8639-b152-445d-a891-20ddcf492205'),
 'service_id': UUID('eed71ee1-63fe-48c7-bd8c-09ed4d7d1753'),
 'payment_method_id': UUID('a1bc5794-bc26-4098-9bec-40fcd76c728e'),
 'details': <PolicyDetails instance>,
 'status': 'renewed',
 'canceled_at': None,
 'billing_cadence': 'monthly',
 'policy_number': 'TAVFC2NPRU',
 'starts_at': datetime.datetime(2022, 6, 1, 0, 0, tzinfo=<UTC>),
 'ends_at': datetime.datetime(2022, 12, 1, 0, 0, tzinfo=<UTC>),
 'term_number': 3,
 'created_at': datetime.datetime(2023, 2, 10, 0, 2, 16, 772246, tzinfo=<UTC>),
 'updated_at': datetime.datetime(2023, 2, 10, 0, 7, 32, 756247, tzinfo=<UTC>),
 'updates_locked_by_user_id': None,
 'updates_locked_at': None}

In [14]: p[2].__dict__
Out[14]:
{'_state': <django.db.models.base.ModelState at 0x7fa8383376a0>,
 'deleted': None,
 'id': UUID('5cb3a258-813f-491d-8f29-e915f38e2003'),
 'carrier_id': UUID('8cb42ac6-0b2f-4b58-afc3-8907fe747620'),
 'brokerage_id': None,
 'broker_id': None,
 'client_id': UUID('08245060-baae-44f3-9d87-2135929fdff5'),
 'api_consumer_id': UUID('176235a6-f615-4291-8cf0-077c74eb1a57'),
 'vertical_id': 104,
 'partner_id': 1,
 'plan_id': UUID('cd0ca836-50d7-4adc-8d4c-57c815d9a041'),
 'rating_address_id': UUID('695f8639-b152-445d-a891-20ddcf492205'),
 'service_id': UUID('eed71ee1-63fe-48c7-bd8c-09ed4d7d1753'),
 'payment_method_id': UUID('a1bc5794-bc26-4098-9bec-40fcd76c728e'),
 'details': <PolicyDetails instance>,
 'status': 'renewed',
 'canceled_at': None,
 'billing_cadence': 'monthly',
 'policy_number': 'TAVFC2NPRU',
 'starts_at': datetime.datetime(2021, 12, 1, 0, 0, tzinfo=<UTC>),
 'ends_at': datetime.datetime(2022, 6, 1, 0, 0, tzinfo=<UTC>),
 'term_number': 2,
 'created_at': datetime.datetime(2021, 12, 1, 0, 2, 58, 93269, tzinfo=<UTC>),
 'updated_at': datetime.datetime(2023, 2, 10, 0, 2, 23, 449775, tzinfo=<UTC>),
 'updates_locked_by_user_id': None,
 'updates_locked_at': None}

In [15]: p[3].__dict__
Out[15]:
{'_state': <django.db.models.base.ModelState at 0x7fa8384a51e0>,
 'deleted': None,
 'id': UUID('2790689f-f083-418c-aaf2-ca71d1a65579'),
 'carrier_id': UUID('8cb42ac6-0b2f-4b58-afc3-8907fe747620'),
 'brokerage_id': None,
 'broker_id': None,
 'client_id': UUID('08245060-baae-44f3-9d87-2135929fdff5'),
 'api_consumer_id': UUID('176235a6-f615-4291-8cf0-077c74eb1a57'),
 'vertical_id': 104,
 'partner_id': 1,
 'plan_id': UUID('cd0ca836-50d7-4adc-8d4c-57c815d9a041'),
 'rating_address_id': UUID('695f8639-b152-445d-a891-20ddcf492205'),
 'service_id': UUID('eed71ee1-63fe-48c7-bd8c-09ed4d7d1753'),
 'payment_method_id': UUID('a1bc5794-bc26-4098-9bec-40fcd76c728e'),
 'details': <PolicyDetails instance>,
 'status': 'renewed',
 'canceled_at': None,
 'billing_cadence': 'monthly',
 'policy_number': 'TAVFC2NPRU',
 'starts_at': datetime.datetime(2021, 6, 1, 7, 0, tzinfo=<UTC>),
 'ends_at': datetime.datetime(2021, 12, 1, 0, 0, tzinfo=<UTC>),
 'term_number': 1,
 'created_at': datetime.datetime(2021, 5, 30, 19, 1, 53, 281747, tzinfo=<UTC>),
 'updated_at': datetime.datetime(2021, 12, 1, 0, 3, 10, 151764, tzinfo=<UTC>),
 'updates_locked_by_user_id': None,
 'updates_locked_at': None}

In [16]:
#+end_src

#+begin_src python
In [7]: policy[0].schedule_items.all()[0].__dict__
Out[7]:
{'_state': <django.db.models.base.ModelState at 0x7fe88bce8250>,
 'deleted': None,
 'created_at': datetime.datetime(2023, 2, 10, 0, 7, 32, 752309, tzinfo=<UTC>),
 'updated_at': datetime.datetime(2023, 2, 10, 0, 16, 52, 254024, tzinfo=<UTC>),
 'id': UUID('453d34ce-2e58-44df-a818-eced7c085a02'),
 'policy_id': UUID('78ab0bf3-c3a4-46d0-ade8-e9ac728ccd5e'),
 'title': '$117.83 Monthly payment',
 'installment_number': 3,
 'due_at': datetime.date(2023, 2, 1),
 'starts_at': datetime.date(2023, 2, 1),
 'ends_at': datetime.date(2023, 3, 1),
 'currency_id': 1,
 'total_amount': Decimal('117.83'),
 'total_taxes_amount': Decimal('0.00'),
 'subtotal_amount': Decimal('117.83'),
 'processed_at': datetime.datetime(2023, 2, 10, 0, 0, 8, 724741, tzinfo=<UTC>),
 'canceled_at': None,
 'canceled_reason': None,
 'failed_at': None,
 'failed_reason': None,
 'retry_pay_at': None,
 'retry_pay_reason': None,
 'details': <BillingScheduleItemDetails instance>,
 'billing_cadence_type_id': 2}

In [8]: policy[0].schedule_items.all()[1].__dict__
Out[8]:
{'_state': <django.db.models.base.ModelState at 0x7fe88bce91b0>,
 'deleted': None,
 'created_at': datetime.datetime(2023, 2, 10, 0, 7, 32, 751949, tzinfo=<UTC>),
 'updated_at': datetime.datetime(2023, 2, 10, 0, 16, 48, 813838, tzinfo=<UTC>),
 'id': UUID('b3430bcf-4405-46cb-8a64-102881fcb954'),
 'policy_id': UUID('78ab0bf3-c3a4-46d0-ade8-e9ac728ccd5e'),
 'title': '$117.83 Monthly payment',
 'installment_number': 2,
 'due_at': datetime.date(2023, 1, 1),
 'starts_at': datetime.date(2023, 1, 1),
 'ends_at': datetime.date(2023, 2, 1),
 'currency_id': 1,
 'total_amount': Decimal('117.83'),
 'total_taxes_amount': Decimal('0.00'),
 'subtotal_amount': Decimal('117.83'),
 'processed_at': datetime.datetime(2023, 2, 10, 0, 0, 8, 724741, tzinfo=<UTC>),
 'canceled_at': None,
 'canceled_reason': None,
 'failed_at': None,
 'failed_reason': None,
 'retry_pay_at': None,
 'retry_pay_reason': None,
 'details': <BillingScheduleItemDetails instance>,
 'billing_cadence_type_id': 2}

In [9]: policy[0].schedule_items.all()[2].__dict__
Out[9]:
{'_state': <django.db.models.base.ModelState at 0x7fe88bceb1f0>,
 'deleted': None,
 'created_at': datetime.datetime(2023, 2, 10, 0, 7, 32, 751451, tzinfo=<UTC>),
 'updated_at': datetime.datetime(2023, 2, 10, 0, 16, 45, 758494, tzinfo=<UTC>),
 'id': UUID('c68575c3-69e7-4ab5-873b-3f83f9009293'),
 'policy_id': UUID('78ab0bf3-c3a4-46d0-ade8-e9ac728ccd5e'),
 'title': '$117.83 Monthly payment',
 'installment_number': 1,
 'due_at': datetime.date(2022, 12, 1),
 'starts_at': datetime.date(2022, 12, 1),
 'ends_at': datetime.date(2023, 1, 1),
 'currency_id': 1,
 'total_amount': Decimal('118.33'),
 'total_taxes_amount': Decimal('0.00'),
 'subtotal_amount': Decimal('117.83'),
 'processed_at': datetime.datetime(2023, 2, 10, 0, 0, 8, 724741, tzinfo=<UTC>),
 'canceled_at': None,
 'canceled_reason': None,
 'failed_at': None,
 'failed_reason': None,
 'retry_pay_at': None,
 'retry_pay_reason': None,
 'details': <BillingScheduleItemDetails instance>,
 'billing_cadence_type_id': 2}

In [10]: policy[0].schedule_items.all()[3].__dict__
Out[10]:
{'_state': <django.db.models.base.ModelState at 0x7fe88bd2d660>,
 'deleted': None,
 'created_at': datetime.datetime(2023, 2, 10, 0, 7, 32, 753151, tzinfo=<UTC>),
 'updated_at': datetime.datetime(2023, 2, 10, 0, 7, 32, 753162, tzinfo=<UTC>),
 'id': UUID('f0cc0894-69ca-454d-a7b6-0e603eb856cd'),
 'policy_id': UUID('78ab0bf3-c3a4-46d0-ade8-e9ac728ccd5e'),
 'title': '$117.83 Monthly payment',
 'installment_number': 6,
 'due_at': datetime.date(2023, 5, 1),
 'starts_at': datetime.date(2023, 5, 1),
 'ends_at': datetime.date(2023, 6, 1),
 'currency_id': 1,
 'total_amount': Decimal('117.83'),
 'total_taxes_amount': Decimal('0.00'),
 'subtotal_amount': Decimal('117.83'),
 'processed_at': None,
 'canceled_at': None,
 'canceled_reason': None,
 'failed_at': None,
 'failed_reason': None,
 'retry_pay_at': None,
 'retry_pay_reason': None,
 'details': <BillingScheduleItemDetails instance>,
 'billing_cadence_type_id': 2}

In [11]: policy[0].schedule_items.all()[4].__dict__
Out[11]:
{'_state': <django.db.models.base.ModelState at 0x7fe88bd2ed70>,
 'deleted': None,
 'created_at': datetime.datetime(2023, 2, 10, 0, 7, 32, 752863, tzinfo=<UTC>),
 'updated_at': datetime.datetime(2023, 2, 10, 0, 7, 32, 752877, tzinfo=<UTC>),
 'id': UUID('b414cecc-598f-4bf5-8cf9-108463424362'),
 'policy_id': UUID('78ab0bf3-c3a4-46d0-ade8-e9ac728ccd5e'),
 'title': '$117.83 Monthly payment',
 'installment_number': 5,
 'due_at': datetime.date(2023, 4, 1),
 'starts_at': datetime.date(2023, 4, 1),
 'ends_at': datetime.date(2023, 5, 1),
 'currency_id': 1,
 'total_amount': Decimal('117.83'),
 'total_taxes_amount': Decimal('0.00'),
 'subtotal_amount': Decimal('117.83'),
 'processed_at': None,
 'canceled_at': None,
 'canceled_reason': None,
 'failed_at': None,
 'failed_reason': None,
 'retry_pay_at': None,
 'retry_pay_reason': None,
 'details': <BillingScheduleItemDetails instance>,
 'billing_cadence_type_id': 2}

In [12]: policy[0].schedule_items.all()[5].__dict__
Out[12]:
{'_state': <django.db.models.base.ModelState at 0x7fe88bd2f100>,
 'deleted': None,
 'created_at': datetime.datetime(2023, 2, 10, 0, 7, 32, 752590, tzinfo=<UTC>),
 'updated_at': datetime.datetime(2023, 2, 10, 0, 7, 32, 752601, tzinfo=<UTC>),
 'id': UUID('d9b416f6-265e-4b49-ab42-2bac6fb45e74'),
 'policy_id': UUID('78ab0bf3-c3a4-46d0-ade8-e9ac728ccd5e'),
 'title': '$117.83 Monthly payment',
 'installment_number': 4,
 'due_at': datetime.date(2023, 3, 1),
 'starts_at': datetime.date(2023, 3, 1),
 'ends_at': datetime.date(2023, 4, 1),
 'currency_id': 1,
 'total_amount': Decimal('117.83'),
 'total_taxes_amount': Decimal('0.00'),
 'subtotal_amount': Decimal('117.83'),
 'processed_at': None,
 'canceled_at': None,
 'canceled_reason': None,
 'failed_at': None,
 'failed_reason': None,
 'retry_pay_at': None,
 'retry_pay_reason': None,
 'details': <BillingScheduleItemDetails instance>,
 'billing_cadence_type_id': 2}

In [13]:

#+end_src