Handle a data erasure request, with approval

Whenever someone posts the erase command with an email address in the Slack channel you nominate, the workflow searches your Airtable table for every record with that address, replies in the thread with what it found, and asks for approval, by email and in Lodol, before deleting anything. Nothing is deleted until a person approves it, and a request that is rejected or left to run out deletes nothing.

Your deletion log gets a row recording what was requested, by whom, which table was searched, how many records matched and their IDs. It is written before any deletion and updated afterwards with who approved or rejected it and what was erased, and the outcome is posted in the thread. Matching is on the whole address, so one person's address never catches another's. A request with no valid address is refused, as is one matching more records than a run may delete.

Before the first run you need an Airtable records table with an 'Email' column and a log table with 'Name' and 'Notes' columns.

SlackWhen someone types “erase” in a channel. Save it as request.When someone types “erase” in a channel. Save it as request.

Set airtableBaseId to blank.Set airtableBaseId to blank.

Set recordsTable to blank.Set recordsTable to blank.

Set deletionLogTable to blank.Set deletionLogTable to blank.

Set maxRecordsPerRequest to 25.Set maxRecordsPerRequest to 25.

Set requestedBySlackUser to request.user_id.Set requestedBySlackUser to request.user_id.

Set requestChannel to request.channel_id.Set requestChannel to request.channel_id.

Set requestMessageTs to request.message_ts.Set requestMessageTs to request.message_ts.

Set emailAddress to blank.Set emailAddress to blank.

If a step below fails, save the error as identifierErrorIf a step below fails, save the error as identifierError

Try

Make request.arguments lowercase and save it as emailAddress.Make request.arguments lowercase and save it as emailAddress.

On error

Stop and report a problem: That erase request carried no email address. Post it as: erase someone@example.com.Stop and report a problem: That erase request carried no email address. Post it as: erase someone@example.com.

If emailAddress is emptyIf emailAddress is empty

Yes

Stop and report a problem: That erase request carried no email address, so there is nothing to match. A blank identifier is refused rather than matched against every record. Post it as: erase someone@example.com.Stop and report a problem: That erase request carried no email address, so there is nothing to match. A blank identifier is refused rather than matched against every record. Post it as: erase someone@example.com.

No

No steps

If emailAddress is a valid email address is noIf emailAddress is a valid email address is no

Yes

Stop and report a problem: That erase request did not contain a valid email address. This workflow only erases records it can match on a whole email address. Post it as: erase someone@example.com.Stop and report a problem: That erase request did not contain a valid email address. This workflow only erases records it can match on a whole email address. Post it as: erase someone@example.com.

No

No steps

If emailAddress contains ' or emailAddress contains \If emailAddress contains ' or emailAddress contains \

Yes

Stop and report a problem: That email address contains a quote or a backslash, which this workflow will not put into an Airtable search formula. Erase this person's records by hand instead.Stop and report a problem: That email address contains a quote or a backslash, which this workflow will not put into an Airtable search formula. Erase this person's records by hand instead.

No

No steps

Set searchFormula to LOWER({Email}) = '@emailAddress'.Set searchFormula to LOWER({Email}) = '@emailAddress'.

Set searchLimit to maxRecordsPerRequest multiplied by 1 plus 1.Set searchLimit to maxRecordsPerRequest multiplied by 1 plus 1.

AirtableList records in recordsTable. Save it as searchResult.List records in recordsTable. Save it as searchResult.

Set matchedRecordIds to an empty list.Set matchedRecordIds to an empty list.

Set unreadableRecords to 0.Set unreadableRecords to 0.

For each record in searchResult.records:For each record in searchResult.records:

If a step below fails, save the error as recordErrorIf a step below fails, save the error as recordError

Try

Make record.fields.Email lowercase and save it as recordEmail.Make record.fields.Email lowercase and save it as recordEmail.

If recordEmail is emailAddressIf recordEmail is emailAddress

Yes

Add record.id to the end of matchedRecordIds and save it as matchedRecordIds.Add record.id to the end of matchedRecordIds and save it as matchedRecordIds.

No

No steps

On error

Add 1 to unreadableRecords.Add 1 to unreadableRecords.

Count the items in matchedRecordIds and save it as matchCount.Count the items in matchedRecordIds and save it as matchCount.

Join matchedRecordIds with , and save it as matchedIdList.Join matchedRecordIds with , and save it as matchedIdList.

If matchCount is 0If matchCount is 0

Yes

SlackReply in a thread in requestChannel.Reply in a thread in requestChannel. Reply To: requestMessageTsNo records match emailAddress in the recordsTable table, so there is nothing to erase. Nothing was deleted.

Finish the run: No matching records, so nothing was erased.Finish the run: No matching records, so nothing was erased.

No

No steps

If matchCount multiplied by 1 is greater than maxRecordsPerRequest multiplied by…If matchCount multiplied by 1 is greater than maxRecordsPerRequest multiplied by…

Yes

SlackReply in a thread in requestChannel.Reply in a thread in requestChannel. Reply To: requestMessageTsRefusing this erase request. emailAddress matches matchCount records, which is more than the limit of maxRecordsPerRequest set on the 'maxRecordsPerRequest' step. Nothing was deleted. Check the request is right before raising the limit.

Stop and report a problem: More records matched than one erasure request is allowed to delete, so nothing was erased.Stop and report a problem: More records matched than one erasure request is allowed to delete, so nothing was erased.

No

No steps

Set foundSummary to Erasure request for emailAddressSet foundSummary to Erasure request for emailAddress Found matchCount record(s) in the recordsTable table.

Set foundDetail to Matched on a whole-address, case-insensitive match of the Email column.Set foundDetail to Matched on a whole-address, case-insensitive match of the Email column. Record ids: matchedIdList Records skipped because their Email could not be read: unreadableRecords.

SlackReply in a thread in requestChannel.Reply in a thread in requestChannel. Reply To: requestMessageTsfoundSummary foundDetail Nothing has been deleted. This run is now waiting for a person to approve the erasure in Lodol.

Set logRequestLine to Requested by Slack user requestedBySlackUser in channel requestChannel (message requestMessageTs).Set logRequestLine to Requested by Slack user requestedBySlackUser in channel requestChannel (message requestMessageTs).

Set logSearchLine to Identifier requested: emailAddressSet logSearchLine to Identifier requested: emailAddress Searched table: recordsTable in base airtableBaseId Match rule: whole-address, case-insensitive match on the Email column.

Set logOpening to logRequestLineSet logOpening to logRequestLine logSearchLine.

Set logFindings to logOpeningSet logFindings to logOpening foundDetail Written before any deletion. Status at this point: awaiting approval, nothing erased.

AirtableAdd a record to deletionLogTable. Save it as deletionRecord.Add a record to deletionLogTable. Save it as deletionRecord.

Set deletionRecordId to deletionRecord.id.Set deletionRecordId to deletionRecord.id.

Set approvalHeader to Requested by Slack user requestedBySlackUser in channel requestChannel.Set approvalHeader to Requested by Slack user requestedBySlackUser in channel requestChannel.

Set approvalBody to Erase every record for: emailAddressSet approvalBody to Erase every record for: emailAddress Airtable base: airtableBaseId Table: recordsTable foundDetail.

Set approvalFooter to Approving permanently deletes these matchCount record(s) and cannot be undone. Rejecting it, or leaving it until the request runs out, cancels the request and deletes nothing.Set approvalFooter to Approving permanently deletes these matchCount record(s) and cannot be undone. Rejecting it, or leaving it until the request runs out, cancels the request and deletes nothing.

Set approvalDetails to approvalHeaderSet approvalDetails to approvalHeader approvalBody approvalFooter.

Request approval: Permanently erase matchCount record(s) for emailAddress? Save it as erasureReview.Request approval: Permanently erase matchCount record(s) for emailAddress? Save it as erasureReview.approvalDetails

Set decidedBy to not recorded.Set decidedBy to not recorded.

If a step below fails, save the error as deciderErrorIf a step below fails, save the error as deciderError

Try

Trim erasureReview.decided_by.email and save it as deciderEmail.Trim erasureReview.decided_by.email and save it as deciderEmail.

Trim erasureReview.decided_by.name and save it as deciderName.Trim erasureReview.decided_by.name and save it as deciderName.

If deciderEmail isn't emptyIf deciderEmail isn't empty

Yes

Change decidedBy to deciderEmail.Change decidedBy to deciderEmail.

If deciderName isn't emptyIf deciderName isn't empty

Yes

Change decidedBy to deciderName (deciderEmail).Change decidedBy to deciderName (deciderEmail).

No

No steps

Else if @deciderName isn't empty

Change decidedBy to deciderName.Change decidedBy to deciderName.

Otherwise

No steps

On error

Show in the output: The approval did not say who decided it, so the deletion record says 'not recorded'..Show in the output: The approval did not say who decided it, so the deletion record says 'not recorded'..

Set reviewNote to blank.Set reviewNote to blank.

If erasureReview.comment isn't blankIf erasureReview.comment isn't blank

Yes

Change reviewNote to Change reviewNote to Note from the approval: erasureReview.comment.

No

No steps

Set erasedIds to an empty list.Set erasedIds to an empty list.

Set failedIds to an empty list.Set failedIds to an empty list.

Set outcome to blank.Set outcome to blank.

If erasureReview.approved is yesIf erasureReview.approved is yes

Yes

For each recordId in matchedRecordIds:For each recordId in matchedRecordIds:

If a step below fails, save the error as deleteError If a step below fails, save the error as deleteError

Try

AirtableDelete recordId from recordsTable.Delete recordId from recordsTable.

Add recordId to the end of erasedIds and save it as erasedIds.Add recordId to the end of erasedIds and save it as erasedIds.

On error

Add recordId to the end of failedIds and save it as failedIds.Add recordId to the end of failedIds and save it as failedIds.

Change outcome to APPROVED and erased by decidedBy.Change outcome to APPROVED and erased by decidedBy. Records erased: the length of erasedIds of matchCount Erased ids: erasedIds joined by , Could not be erased: failedIds joined by , @reviewNote.

Else if @erasureReview.timed_out is yes

Change outcome to NO DECISION: the approval request ran out before anyone decided, so nothing was erased. The matchCount matching record(s) are untouched.Change outcome to NO DECISION: the approval request ran out before anyone decided, so nothing was erased. The matchCount matching record(s) are untouched.

Otherwise

Change outcome to DECLINED at the approval step by decidedBy. Nothing was erased. The matchCount matching record(s) are untouched.@reviewNote.Change outcome to DECLINED at the approval step by decidedBy. Nothing was erased. The matchCount matching record(s) are untouched.@reviewNote.

If a step below fails, save the error as logUpdateErrorIf a step below fails, save the error as logUpdateError

Try

AirtableUpdate deletionRecordId in deletionLogTable.Update deletionRecordId in deletionLogTable.

On error

Show in the output: Could not close the deletion record: logUpdateError as text.Show in the output: Could not close the deletion record: logUpdateError as text.

SlackReply in a thread in requestChannel.Reply in a thread in requestChannel. Reply To: requestMessageTsErasure request for emailAddress outcome