SQLite DOI transaction db results in erroneous caching of deleted DOIs

[alexdunnjpl]

🐛 Describe the bug

Submitted DOI/PDS id pairings are stored in the local sqlite database. If a DOI is deleted (common for development), this deletion is not reflected in the cached transactions, and doi-service will erroneously raise an IllegalDOIActionException for submission of a DOI for an existing PDS identifier

📜 To Reproduce

Steps to reproduce the behavior:

1. Reserve a DOI from a given xlsx template
2. Delete the DOI via Fabrica or the REST API (doi-service doesn't appear to implement the deletion action)
3. Attempt to reserve a DOI using the same xlsx template

🕵️ Expected behavior

The reservation should succeed

⚙️ Engineering Details

It's unclear to me (due to unfamiliarity) what the core need is for the sqlite db, but it seems like it should be a simple thing to fix by modifying pds_doi_service.core.outputs.doi_validator._check_for_preexisting_identifier() or perhaps removing it entirely, given that it's not really a validation concern, and the DOI REST API should throw back a descriptive error if we try to submit a duplicate (or is the unique PDS identifier constraint purely our requirement?)

[alexdunnjpl]

Just checked by deleting the relevant db row and resubmitting without deleting the DOI record, resulting in two DOI records, so I guess it's a matter of it being our requirement, not DataCite's.

@jordanpadams @jimmie @viviant100 how do y'all want to approach this (if at all)?

[jordanpadams]

@alexdunnjpl if I am understand this correctly, this seems like something that is specific to development testing (I am pretty sure you cannot delete DOIs in the operational Fabrica API). Do foresee this causing other issues elsewhere?

[alexdunnjpl]

@jordanpadams this is true once the DOI is published/findable, but is not true while the DOI is in a draft state. In doi-service vernacular, you "reserve" a DOI, resulting in a draft-state record, and "release" a draft-state record, resulting it becoming findable, if I'm understanding correctly?

It could be that our workflows don't really deal with that intermediate draft state and we don't care to expose that functionality, though if that's truly the case I'd argue that the reserve/release actions shouldn't be distinct. I'm guessing this isn't the case though, and that updating draft-state DOIs is supported for a reason.

I don't think we necessarily need to add support for deletion through doi-service in a way that updates the db, since if it affected non-dev use someone would have raised this issue before, but figured it'd be worth asking the question.

[jordanpadams]

@alexdunnjpl copy that, and for sure. the DOI Service currently does not support deletion of DOIs, but we can always do it through DataCite or through direct curl commands. I think we can table this for now, but will definitely track it in the future in case the issue comes up in operations.