How should we handle deleted contacts/reports in cht-sync?

Development
1

As a part of @vikrantwiz02’s work to support bulk-deleting contacts and their hierarchy+reports we are leveraging the new archiving functionality that copies all the “archived” docs to a new medic-archive DB and them purges all trace of the docs from the medic db.

I want to continue a discussion here that @diana and I started on the archiving PR regarding how the data is going to look downstream in Postgres for deployments running cht-sync.

As things stand now, when you archive a doc, downstream in Postgres nothing happens. The data stays in place and no new changes will ever show up for those records (since the source doc is no longer in the medic db). This is different from when a doc is _deleted in medic which, as I understand it, actually causes the record to be removed in Postgres.

The problem:

The original thinking behind leveraging archiving when doing the bulk-contact-delete was that it offered a bit of safety from unintentional data-loss because the contact/report data still exists in medic-archive and could be recovered if necessary. However, it feels wrong to still see bulk-deleted contacts in Postgres. Just semantically that feels unexpected and most of the time when you are deleting a contact, it is because it is invalid and/or you do not want to track data for it at all any more.

The current options I see for how to proceed are:

  1. Leave it as is (deleted contacts/reports stay in Postgres).
  2. Update the bulk-delete code to _delete the docs instead of archiving - unfortunately, this makes deletes irreversible (you can pass the --dry-run param to at least check the blast radius first, but…).
  3. Do something to the doc in medic before it is archived that cht-sync can interpret so it knows the contact is gone.
    • Maybe we could _delete the doc before archiving it? If we just do this I am afraid we end up with an essentially empty doc in medic-archive, right?
    • Maybe we could have a custom “archive” flow that sends the doc to medic-archive, then _deletes it, then purges it. So, the last change in the changelog for the doc will be the delete and Postgres can drop the record.

@twier @diana do you have any thoughts here?? :folded_hands:

2

Thanks for starting this conversation @jkuester! Good stuff here.

CHT Sync’s Postgres is often used for month over month results, or year over year results. Consider Postgres shows 100k immunizations last year and another 20k this year, 120k total. A deployment decides immunization reporting in CouchDB older than 18mo isn’t relevant and uses the new cold storage feature. If these deletes percolate to Postgres, recalculate immunization numbers to be 70k and 90k, some how going negative, seems the wrong thing.

Maybe this is a bit of a contrived scenario, but it would suggest that option 1 above is best. More nuanced, and more complicated, though, is that option 1 might be good for cold storage on 1000s of reports, but not good for this threads discussion on bulk deleting contacts: if you’re deleting contacts you likely really want them gone everywhere.

Maybe this leads us to the cold storage API being flexible about if it does _delete or not and the the caller of the API can decide what’s most appropriate?

3

Thanks for jumping in here @mrjones!

Yeah, so basically this is the direction I was going in option #3. The one thing to note might be just how we say “cold storage API”. IMHO, the /api/v1/archive api endpoint should never _delete docs and (if we go with option #3) and the DELETE /api/v1/person-place api endpoints should always _delete docs before the archive. It is the inner archiving logic in Sentinel that could be made to be flexible about if the doc should be deleted, not the actual public-facing APIs.

4

The whole point of using _purge for archiving is that we want the data to remain available in Postgress for analytics. This was one of the main drivers of designing it this way and was a requirement from the partners requesting the feature: they don’t need the documents in the medicDb for current workflows, but they do need the documents in Postgres for historical analysis.

The flow of adding a _deleted before a purge won’t work unfortunately, because the purge will remove even the delete from the changes feed, and then Postgres won’t get the delete anyway. The flow should be:

  • add _deleted
  • make sure Postgres synced
  • purge
    I don’t think this is a realistic scenario to follow.

So, from a possibility point of view, only option (1) and (2) work.

I feel like there is some philosophical confusion around what the bulk-delete is for.
Archiving is very well defined: docs removed from medic, but docs stay in Postgres.

5

:thinking: Wait. Is there a race-condition (even for a normal archive) where some final changes to archived docs will not get pushed to the changes feed because the doc got purged? I assumed that any PUT for a doc with a success status from Couch would be reflected in the changes feed before the _purge call totally removes the doc for good…?

It is not so much confusion about what it is for. We know we want general-purpose DELETE endpoints that can effectively replace the cht-conf actions for managing contacts.

This morning, @vikrantwiz02 gave me the language I was missing. My hope is to implement the DELETE endpoints as “soft-deletes”. The cht-conf actions we are replacing required a two-phase operation where you used the delete-contacts to queue up a bunch of doc changes and then the upload-docs action to execute the changes. This provided a built-in intermediary step where you can see what is going to change before it happens. Also, in the end, you still had all the doc data stored locally (where you could backup/restore as necessary).

By moving this all to the server, consumers do not have this control (for better or worse). The dry_run query param does allow you to see the blast radius of a delete before actually executing it. However, I really wanted to implement a “soft-delete” here as some protection against unintentional data loss.

:thinking: Maybe the real answer is to just properly implement a “soft-delete” (e.g. copy the doc to a new medic-deleted database and then _delete the doc in medic) instead of trying to overload the archive operation…

6

I tested the purge race on both a 3-node cluster and a single node, and can confirm it. :+1:

I created a doc, noted the seq where a lagging consumer would be, updated it, waited for the PUT to succeed, and then purged it. Before the purge, _changes?since=<seq> contains the update. After the purge, _changes?since=0 has no trace of the doc at all.

So this does confirm the race you were wondering about for normal archiving: a consumer that has already processed the change keeps it, while one that hasn’t reached it before the purge never sees it.

I also checked the other side of it. A plain _delete with no purge leaves a durable tombstone, "deleted": true visible from _changes?since=0, so a lagging consumer still picks it up whenever it catches up. The previous revision also stays readable by explicit rev until compaction runs, which gives a recovery window on its own, but copying the live document into a separate DB would make that recovery independent of revision retention/compaction.

So I’m +1 on the separate medic-deleted DB approach. :+1: The copy should happen while the document is still live, followed by _delete, with no purge afterward, so the preserved copy contains the complete document rather than relying on the deleted revision/history.

I also followed the proposed medic-deleted → _delete → restore path through cht-sync/cht-pipeline to see what an undelete would look like downstream. :magnifying_glass_tilted_left:

document_metadata has post_hook='delete from {{this}} where _deleted=true', so the Postgres row gets physically removed when the deletion reaches the pipeline. Following the restore path through the code, restoring the original document should cause couch2pg to upsert it again with a new saved_timestamp.

That changes saved_timestamp, but the pipeline’s modelled business dates are derived from fields in the doc rather than from saved_timestamp. For example, reported comes from doc->>'reported_date', which would be preserved with the document. So the restore should preserve those original business dates, while saved_timestamp would reflect when the restored document was processed again.

I also checked the new DB side: COUCHDB_DBS is an explicit opt-in list, so a medic-deleted DB wouldn’t start syncing to Postgres unless explicitly configured.

One caveat :slightly_smiling_face:: the purge/_delete behavior above I tested directly. The Postgres restore behavior is from tracing the cht-sync/cht-pipeline code rather than running an end-to-end restore test.

7

Circling back around to this. @diana and I and others have had more conversations around this. The conclusions are:

  • The new DELETE contact endpoints should support “soft-deleting” the hierarchy and not hard-deleting where unintentional data loss is pretty much guaranteed. (Also, worth reiterating the general sentiment that a heath information management system should probably never hard-delete anything except as required by data privacy laws, ect.)
  • “soft-delete” !== “archive”. Semantically they are different. They should be used for different things. Their impact on the data (particularly down-stream in Postgres) should be different. So, instead of leveraging the archive functionality for deleting contacts, we are going to implement a separate (but parallel) soft-delete functionality. This will copy the doc to a separate DB (e.g. medic-delete) and then _delete the doc from medic.
8

Also, on a related note, we should deprecated the legacy bulk-delete endpoint in favor of the new DELETE endpoints.

9

@diana one final question here: do you think we should purge the Sentinal info docs for contacts/reports that we have “soft-deleted”?

I am planning to just purge them, but let me know if you think that might break something…:thinking: