Portal doesn't detect duplicate requests.

[kfogel]

Sometimes in the admin area we see multiple requests (often a series of them within a narrow time window) for a smoke alarm to be installed at the same address, with the same contact info. The system currently does nothing to detect such duplicate requests. It should at least flag them internally; what the installers do with that data is a policy question, but the system should at least show the situation.

Privacy note: If the system does detect duplicates, that's great, but the UI should not ever show that to submitters -- the fact of whether someone has or hasn't requested a smoke alarm isn't something one should be able to probe just by submitting that person's address. And on the admin side, the UI should maybe remind the admin not to reveal to a submitter (say, someone on the phone with a 311 operator who also happens to have admin access) that there is already a duplicate request in the system for them, for the same reason.

[cecilia-donnelly]

If we receive a duplicate request (one that has all the exact same information as an existing request) within a given time window of the original request (perhaps up to an hour), then update the old request's submission date and don't save a new record).

But in general a person could resubmit, e.g. a week or a month later, and we would want to keep track of the fact that they've done so because that suggests that they are resubmitting for a reason. Perhaps they worry the first request got lost because they haven't heard anything, or even because they missed an installation date. For that case, we should create a separate table for which the primary key is the request id, and store the datetimes of all submissions associated with that request. This audit trail of duplicate submissions should then be available to admins somewhere.

So, when a new request comes in, we need a new test.
IF all the information in the request exactly matches one or more existing requests, THEN check whether any of the existing requests were submitted within the past hour. IF YES, update the submission date on that recent request to the current datetime. IF NO, add a new record to the duplicate_requests table with the original request ID and the current datetime.

[pjsier]

@cecilia-donnelly this makes sense overall, but how strict should it be in terms of matching duplicates?

A LIKE query should get phone numbers pretty easily (maybe just LIKE %123%456%7890 to allow for country codes but ignore extensions if not provided), and since it's trimming whitespace already extensions shouldn't be a problem. Is an ILIKE for street address good enough, or do you think there could be other validation problems? The other fields seem straightforward

[cecilia-donnelly]

I think we want to be pretty strict! Much better to have some duplicates get past us than to accidentally not recognize a truly different request. I think ILIKE should work -- let's run some examples and see what happens. If we have doubts after trying it out we can even go with a straight =. I'd say by far the most common case is the double click / quick double submission addressed by #237. For other duplicates we can be more relaxed, in my opinion, and err on the side of showing both versions.

[pjsier]

@cecilia-donnelly That works! Other than phone numbers (which I'm guessing there could be variation on) I just defaulted to ILIKE on any of the fields with user input on #240

[pjsier]

Based on IRC conversation with @cecilia-donnelly, for now the admin view updates will just be adding updatedAt and a count of duplicates for each request to the CSV export.

In the future, we could also add an additional download button that just exports the RequestDuplicates table. Additionally, because the duplicate changes the updatedAt timestamp on the original request, would probably need to add that to the sorting options on the admin front-end, and potentially make it the default option.