Sormas-project: Dedicated document database for SORMAS [0,5 d]

[markusmann-vg]

Situation Description

Prerequisite for
https://github.com/hzi-braunschweig/SORMAS-Project/issues/1771

Feature Description

Concept for a dedicated db & basic db-model/scheme

Possible Alternatives

=> Postgres as we us it for SORMAS already
=> Mongo
=> other DB
=> file-system

Additional Information

Performance needs to be considered
How is the download handled

[ftavin]

@bernardsilenou @MateStrysewske This issue is a prerequisite for incoming issues so I suggest that we initiate the discussion about the technical alternatives exposed in the description.

1) PostgreSQL

The major interest of this solution is to limit the impact of this new feature on server installation/configuration
In the other hand, the use of the initial db for file storage could quickly make it really big and the management of the space used to store the files will be limited to the DBMS options
In addition we need to check that hibernate can manipulate binary data easily

2) MongoDB or other DB

This solutions will complexify the installation and supervision processes (new services, new backups, ...) with technologies that may not be mastered by every server administrator
and have the same problems that the 1) concerning management of space and manipulation with hibernate

3) File-system

Using file-system, the size of the original db will not be impacted and we don't have to install/manage a new service.
The management of the space used to store the data will only be limited by the file-system of the OS which is more likely to be mastered by the server administrator.
In addition, with this solution the configuration of incremental backup for the stored files is possible which can save lots of space

Taking performances into consideration, we do not think that any of these solutions will make a difference as the operations will be simple ones (write, load, delete)

Note : all these solutions will need the addition of new tables in the SORMAS DB Model to keep track of metadata and relations between files and entities (cases, contacts, events...)

As far as we are concerned (@xavier-calland and myself), the 3rd solution (file-system) seems to be more interesting than the others as it provides more flexibility with no major impact on the installation/configuration/management operations.

[JonasCir]

CC @syntakker interesting for us as well as we need to store our file templates somewhere
Thank you @ftavin for starting the discussion and providing the summary!

[MateStrysewske]

@ftavin Thanks for your analysis! I'll link @markusmann-vg here so he can see where this discussion fits in our schedule.

Maybe @HolgerReiseVSys and @MartinWahnschaffeSymeda have some additional input on this topic based on experience.

[JonasCir]

@bernardsilenou @ftavin @MateStrysewske
I took another pass at this issue and I am on board with option 3 (file system) as well. The things which needs to be carefully designed in my opinion are:

1. Incremental backup
2. SORMAS DB Model to track the files on disk
3. Generic, reusable interface such that we can store, retrieve and link arbitrary files with SORMAS entities (we could reuse this for storing the quarantine templates as well)

[JonasCir]

I also think we should discuss implications to the final deployment as well. E.g., it might make sense to add and mount a dedicated docker volume to avoid that to much documents crash the main server :D

[StefanKock]

No matter what solution is considered, it should be considered if and how this can be integrated into JTA transaction and that serializing files take time and RAM to process.

[MartinWahnschaffeSymeda]

Document Meta Data

As mentioned by you before, the meta information on the files should be part of the SORMAS db, to make it easy to link between documents and persons/cases/tasks/whatever. This meta information should include:

• basic entity fields: uuid, id, creationDate, changeDate
• reporting/uploading user
• name
• file type (e.g. pdf) & size
• a "link" to the file (depending on the storage solution)
• uuid and type of related entity (e.g. case) -> will allow us to use files for a lot of different use cases
• file category (to be defined)

Document Storage

I aggree with @JonasCir that we need a reusable interface.
I have done some research on existing systems and it's surprisingly difficult to find something good. One obvious existing thing is the JCR (java content repository) 2 specification. It relies on a hierarchical model using nodes to store the data and comes with a lot of overhead, so I'm not a big fan of it (Jackrabbit Oak is the latest implementation).
Related to that I came across the Jackrabbit Oak BlobStore. The implementation seems to be quite simple (Github) and it comes with multiple implementations for storing on the file system (FileBlobStore and FileDataStore), in a mongo db and also on Amazon S3 and Azure.
@ftavin @JonasCir Would be good to get your thoughts on this.

I would not include versioning (at least not for users). From my experience most users have a really hard time to understand the concept and most often it just blows up the amount of stored data.

[JonasCir]

@MartinWahnschaffeSymeda Thanks for the investigation and the writeup :)

• I would add a lastChangedByUser field in addtion to the changeDate meta data. :+1: on the uuid and type of the related entity, this is super important b/c by this we can figure out where the document is used. Useful in case we want to filter, group and export the documents by just looking at them.

• There is a doc page which mentions that "segment storage is optimized for maximal performance in standalone deployments" while documents storage is better suited for distributed deployments. Segements will use TAR files on disc. _CNTRL + F "Bean" to find the interface in the Segments Doc._

• "Premature optimization is the root of all evil" but: They mentioned caching support and as we likely move a lot of documents around, I think this is worth a look.

• I think we have to account for export/retention/cold storage somehow b/c on single server deployments for a whole country, I guess we will have to deal with quite some GB after a couple of months.

• Related to the previous point: In Germany, we have the evil word "revisionssicher" which we likely need to account for. This was mentioned by several GAs and I will collect their requirements as soon as I can.

• As some of the GAs or districts use electronic files, they have the need to filter and export matching documents. I was specifically asked to make the document store accessible through a REST API such that a python script can go and fetch the documents. Before we do this however, I would really like to see the REST interface to be protected by JWT and not HTTP Basic Auth. Since this is a completely different story, I would postpone this point but keep it in mind.

I noticed that this issue is actually more complex than I initially thought @MartinWahnschaffeSymeda , therefore, excuse the somewhat unordered list :)

[ftavin]

@markusmann-vg @MateStrysewske @MartinWahnschaffeSymeda @JonasCir @bernardsilenou @carolinverset

This mecanism is a huge needed in France to handle the second wave of covid-19.

We hoped to be able to implement it for 1.51 but the discussions took too long for this delay to be matched.

We now REALLY need to implement it for 1.52

I would suggest :

• we develop quickly a first draft with our vision (we already did that type of development for other projects)
• we submit it to you via a pull request so you'll be able to review/test it
• this first version will not use Jackrabbit Oak Blobstore (which may be a little over the top considering the needs of the application)
• we can also propose that the storage mecanism could be configured via the parameters file (you will choose if your instance uses the "simple" implementation, an advanced implementation using Jackrabbit, another one using mongodb, ...). Each storage solution would implements the same API so we could start developping the simple version right now and wait for reflexion about other future solutions to develop alternatives.

Thanks in advance for your (quick) feedback !

[MartinWahnschaffeSymeda]

Hi @ftavin,

we have done some further research on this and have a strong tendency to use MinIO (https://min.io/) which would basically be a service operating on it's own to allow SORMAS to store documents. The Java SDK is documented here: https://docs.min.io/docs/java-client-api-reference.html

To come back to your actual question:
The essential part of implementing the document feature will be to set up the meta data and fascade classes in the backend.
The question on where exactly the documents are stored is secondary, I think.

This being said, it will make sense to go forward as suggested by you.
Please design it in a way that makes sure we can easily extend it to using MinIO.

[MartinWahnschaffeSymeda]

Hi @ftavin,

in case you are starting to work on this, please make sure to share your status as early as possible. Ideally create a list of todos that you want to take care of and put them here, so we can give feedback.

I had another look at MinIO with a colleague of mine. Most likely it will be even be easier to use MinIO than having to deal with the local file system. Especially for France, where a lot of data is handled in the system it will be beneficial to rely on MinIO, because it is ready to scale up, when needed.

[ftavin]

@MartinWahnschaffeSymeda we plan to start working on this issue today.

We will have a look at MinIO to estimate if its use is (or not) a risk on having this functionnality done in time for 1.52 (this point is really critical for France)

[carolinverset]

@MartinWahnschaffeSymeda @ftavin
I take the liberty of intervening to stress the importance of having this function in version 1.52. The epidemic situation in our country makes this functionality essential because it saves time for teams who are very busy in this phase of very strong viral circulation. Thank you verry much for all you can do !
Best regards

[MartinWahnschaffeSymeda]

@ftavin Have you been able to start working on this? As I said I'd suggest to share a list of todos from your point of view here, so we can give feedback and make sure you go into the right direction.

This is a very fundamental feature, so it's very likely that it will need a lot of refinement and going back and forth with the PR, which takes a lot of time.

I think it's unlikely that we can really finish this for 1.52, but we should at least try, so the earlier you can share something, the earlier we can give feedback.

[ftavin]

@MartinWahnschaffeSymeda : @tbroyer started working on this feature on monday and it is almost ready for a first look

As I said earlier, this feature is so important for France that it have to be in 1.52 and the deadline is too short to take time to discuss the points before starting development. That's why we proposed to start the devs and show it to you (with explainations) as soon as possible to have your feedback. I hope to give you that first look by monday or tuesday

[ftavin]

@MartinWahnschaffeSymeda @JonasCir @MateStrysewske

@tbroyer did the PR of his branch, you can go check/test/review it

I would suggest to have the discussion concerning the main idea (if needed) here, on the issue and to keep the PR comments for a later, code-scale, review

[MartinWahnschaffeSymeda]

@ftavin @tbroyer
Thanks for the quick PR! Code quality looks good - I still have to look into some details, tough.

Three things that we have to address:

1. The DocumentFacade should handle the whole storing in one call, instead of making one call to DocumentFacade.saveDoument and then one call to DocumentStorageFacade.store. I'd suggest to let the DocumentFacade call the DocumentStorageFacade. When something goes wrong with storing the file, the whole transaction will roll-back and the database will not have an invalid entry.
2. Please add a feature configuration for the whole thing that is disabled by default. This will allow us to roll-out and test the feature in France before bringing it to other SORMAS users
3. Let the document storage return the "key" (in this case filename, not the path) that is computed in computeFilePath. Then use this key to read documents. This will allow us to change the way this is generated in the future without having to migrate old documents and also to use different key patterns depending on the storage solution.

Two questions that came up:

1. I haven't understood why the delete functionality does not directly delete the document and instead relies on the cron-job. Deleting the file directly would have the benefit that we can show a message to the user when something goes wrong. What would be the benfit of lazily deleting the file?
2. How are DocumentContext and DocumentRelatedEntityType intended to be used? Can't we just combine them?

Side-note: In a later step we should introduce MinIO and add a service that migrates the documents from the local storage into the MinIO storage.

[tbroyer]

@MartinWahnschaffeSymeda

@ftavin @tbroyer
Thanks for the quick PR! Code quality looks good - I still have to look into some details, tough.

Three things that we have to address:

1. The DocumentFacade should handle the whole storing in one call, instead of making one call to DocumentFacade.saveDoument and then one call to DocumentStorageFacade.store. I'd suggest to let the DocumentFacade call the DocumentStorageFacade. When something goes wrong with storing the file, the whole transaction will roll-back and the database will not have an invalid entry.

I'm not really familiar with JavaEE (EJBs and JTA), so it's a misunderstanding from my side about how this all "magically" works. Will do the suggested changes.

1. Please add a feature configuration for the whole thing that is disabled by default. This will allow us to roll-out and test the feature in France before bringing it to other SORMAS users

k

1. Let the document storage return the "key" (in this case filename, not the path) that is computed in computeFilePath. Then use this key to read documents. This will allow us to change the way this is generated in the future without having to migrate old documents and also to use different key patterns depending on the storage solution.

:thinking: You mean something similar to how ExportFacade#generateDatabaseExportArchive returns a file path and DownloadUtil#createDatabaseExportStreamResource knows how to read from it (well, it's a file path here, so it's easy).

Or you mean storing that path into the database alongside the metadata?
Fwiw, I didn't do that because there's no actual need to do it right now, and migrating to another path generation algorithm and/or storage solution could add it at that time (add a column in the DB and either compute the value during the schema migration, or compute it as a fallback in the application when the column is NULL, meaning this is an "old file").
But sure I can add it now.

Two questions that came up:

1. I haven't understood why the delete functionality does not directly delete the document and instead relies on the cron-job. Deleting the file directly would have the benefit that we can show a message to the user when something goes wrong. What would be the benfit of lazily deleting the file?

This is for easier backups (see https://github.com/hzi-braunschweig/SORMAS-Project/pull/3492/commits/8ed4ec9b5b7c3cfbf5df349f418a6164c28c0b4f commit message)
If you delete the file on disk and in the database at the same time (assuming you delete on disk only if/when the transaction succeeds), then you need to have atomic backups of the both the files and database (e.g. disk snapshots).
If you don't have atomic backups and you backup the files before the DB, then you risk missing files that are being added in between the file backup and DB backup (you'll have the document metadata in DB but not the file on disk).
And if you backup the DB first and then the files (e.g. dump the DB to disk, then backup the whole thing), then you'll have the same situation if a document is deleted in between (metadata not yet deleted from DB as the action hadn't taken place at that time, but file already deleted on disk).
Hence, https://github.com/hzi-braunschweig/SORMAS-Project/pull/3492/commits/ebbf7465773d6f374bb67f305b02a14b477fe7ef: by doing a "soft" deletion, you can backup the DB, then the files, and still have a consistent system: the file might be missing on disk (if you backup at the same time as the cleanup job!) but that's not a problem because the metadata is already marked as deleted anyway.

1. How are DocumentContext and DocumentRelatedEntityType intended to be used? Can't we just combine them?

Probably.
I haven't grokked what those "contexts" are really meant to represent, so this "document context" is kind of cargo-culting on how things have been done elsewhere (I'm completely new to this code base).
From my PoV, DocumentContext was more of a "conceptual" thing (where the document is being used, where several contexts could possibly related to the same DocumentRelatedEntityTypes), whereas DocumentRelatedEntityType really is about the data, indicating what the related_entity_uuid points to.
Now I can delete DocumentContext to make things simpler.

[MartinWahnschaffeSymeda]

Or you mean storing that path into the database alongside the metadata?
...
But sure I can add it now.

Yes, that's what I mean. Thanks :-)

This is for easier backups (see 8ed4ec9 commit message)

Makes perfect sense :-)

Besides that I'd like to have a basic unit test that checks saving, duplicates and the soft-delete (not the actual deletion, since this would be a bit tricky). You will probably have to mock the part that does the actual storing of the document.

Let me know when the PR is ready. Please also check the travis CI builds that are automatically done for the PR.

[tbroyer]

PR updated with:

• feature configuration (db2f006d38bd5799dad6ba632297a54d84bfadb1)
• merging DocumentStorageFacade into DocumentFacade, keeping the underlying DocumentService and DocumentStorageService (2a6325bc041e2d45fbca8dbfa553b4fbb67a377a)
• basic unit test, which surfaced a bug (3021e0699cdd8d6a72e3f37e04dd4ee21c40ff62)
• storing a reference to the file into the database (fb0cc72b97d47a23370f8a4bd222bcff4001e56f)
• fixing the failure in Travis (abbf1bc9955000c3cf04ef534a29ee399f5fd357)

[MartinWahnschaffeSymeda]

@tbroyer Thanks! Almost done :-)

I have reviewed it again and have a few things left:
https://github.com/hzi-braunschweig/SORMAS-Project/pull/3492#pullrequestreview-534167507
https://github.com/hzi-braunschweig/SORMAS-Project/pull/3492#pullrequestreview-534211480
https://github.com/hzi-braunschweig/SORMAS-Project/pull/3492#pullrequestreview-534224281

Also please provide the configuration adjustments that have to be done in SORMAS-Docker and a short summary on how the document storage works, that can be added as javadoc to DocumentFacadeEjb.

[tbroyer]

Also please provide the configuration adjustments that have to be done in SORMAS-Docker

Do expect a PR to the SORMAS-Docker repository?

AFAICT, the same changes need to be done to setup-server.sh, and a volume needs to be declared in the docker-compose files, similar to how this is done for /opt/sormas/custom.

[MartinWahnschaffeSymeda]

Yes, a PR for SORMAS-Docker would be perfect. As far as I understand you are also using this in France, right?

[tbroyer]

Yes, a PR for SORMAS-Docker would be perfect.

https://github.com/hzi-braunschweig/SORMAS-Docker/pull/138

As far as I understand you are also using this in France, right?

That's right, afaik.

[MartinWahnschaffeSymeda]

Merged :-)

[carolinverset]

@MartinWahnschaffeSymeda @ftavin @olivierleuci

Thank you so much !!!!!! French users can now use SORMAS for almost the entire contact tracing process
Viele Grüsse aus Burgung

[JonasCir]

Great work everybody! :rocket:

[MartinWahnschaffeSymeda]

@MateStrysewske For release notes:

A new directory for the document storage has been added to the server setup. For docker environments this will automatically be handled. For script-based/manual server setups you will have to execute the following statements:

DOCUMENTS_DIR=/opt/sormas/documents
mkdir -p "${DOCUMENTS_DIR}"
setfacl -m u:${USER_NAME}:rwx "${DOCUMENTS_DIR}"
setfacl -m u:postgres:rwx "${DOCUMENTS_DIR}"

If you use a different directory, make sure to also set the entry in sormas.properties

documents.path=/opt/sormas/documents/