Keep SMS usage data longer

[sastels]

Description

As a Notify team member, I need to see all the SMS costing data.

WHY are we building?

We would like to know what services have been spending on SMS in the long term. Currently we keep a one week snapshot of the data.

WHAT are we building?

We are modifying the current system so that we do not delete the SMS usage data after a week. Note that currently the data contains destination phone numbers. We will need to remove those before we can keep the data long term.

VALUE created by our solution

Better estimate on service costs.

Acceptance Criteria

• [x] Sanitize the destination phone number in the sms usage report and save into a new bucket.
• [x] The SMS usage report is still deleted from S3 after a period of 3 days.
• [x] The sanitized SMS usage report in a new S3 bucket is never deleted.
• [x] QuickSight SmsUsage and Notifications with SMS pricing datasets have all the usage data collected so far, using the sanitized S3 report instead of the one with sensitive data, but still including the reference ID and the cost per SMS fragment.

QA Steps

• [x] Verify that the destination phone number does not appear in the sanitized S3 SMS usage report files.
• [x] Verify that the destination phone number does not appear in QuickSight datasets.
• [x] Create analyses with the Notifications with SMS pricing dataset and verify that the amount of data grows continuously, past the 3 days of data retention period.
• [x] Find a service with a fixed us-west number and check that their notifications are in the Notifications with SMS pricing dataset.
• [ ] Verify that there are no duplicate message ids in the dataset

[sastels]

Started with a script that will sanitize the usage logs and move to a different bucket that has no retention policy. https://github.com/cds-snc/notification-terraform/pull/1256

[sastels]

pr merged, ran the script, new sanitized files in new bucket :tada: Have to see what happened overnight in QuickSight...

dang it, the destination numbers are still there :/ Must not be looking to the correct bucket somehow. Will investigate...

[sastels]

The manifest file, which I smartly moved to the new bucket so it wouldn't expire, still points to the old bucket. Will fix after more caffeine...

[sastels]

Fix the manifest file https://github.com/cds-snc/notification-terraform/pull/1276

[sastels]

ok, remaining is to have a GitHub actio run this script every day

[P0NDER0SA]

This one is approved as well! moving to QA

[sastels]

PR for github action https://github.com/cds-snc/notification-terraform/pull/1294

[P0NDER0SA]

Jimmy will QA

[sastels]

PR approved, will be merged today

[sastels]

merged in, will release today and then we can QA

[ben851]

Needs to be released to prod and QA'ed

[sastels]

• everything automated
• data in QuickSight from Apr 14 - May 4

[sastels]

One thing we haven't done yet is use the us-west-2 data. It's being sanitized and stored but not added to QuickSight.

We should see if we can get the initial SMSUsage dataset to use that bucket along with the ca-central-1 bucket. ie can we create a dataset from 2 buckets? :thinking:

[sastels]

ooo you can just give a list of buckets to pull data from in the manifest file. Easy-peasy.

https://github.com/cds-snc/notification-terraform/pull/1304

[sastels]

merged and ready for release.

After release we can manually refresh the datasets to check if the us-west-2 data has been added.

[ben851]

We haven't done a Terraform release recently. Need to get this in there.

[ben851]

Terraform release went poorly yesterday - in that we never applied it... Will try again today.

[sastels]

I think all is in prod now :tada:

Ready for QA

[sastels]

Brought back from Done - SMSUsage dataset has duplicate rows. We need to dedup them (ie one line per message id). Started a PR to do this as part of removing the PII https://github.com/cds-snc/notification-terraform/pull/1335

[P0NDER0SA]

will be merged soon then we can QA it.

[sastels]

Update the prod sanitizer script: https://github.com/cds-snc/notification-terraform/pull/1345

[sastels]

all merged and running

[ben851]

@sastels to run the de-duplicate data in prod, and will be qa'ed

[sastels]

• verified in production Quicksight that we have duplicate rows in the SMSUsage dataset (ie for same message id)
• manually ran the sanitize / dedup script on the old production sms usage s3 buckets (ie to depup the old stuff)
• manually reran the SMSUsage dataset refresh
• verified that we no longer have duplicate rows in the SMSUsage dataset

remaining: refresh the "Notifications with SMS pricing" dataset. This is a 45 minute join of the full notifications dataset with the SMSUsage dataset - I decided to leave this to the nightly refresh.

[sastels]

• Notifications with SMS pricing refreshed in prod overnight
• verified that there are no duplicate notification_ids in the dataset
• read for someone else to QA

[ben851]

@Jimmy has assigned it to himself to QA

[jimleroyer]

I downloaded the sanitized report for June 9th and there were no duplicate message IDs sitting in the file, using a quick duplicate finder script. So this is all ✅.