Consider accessing ODK Central database directly for submission data

[spwoodcock]

Issue

• Getting submissions is a very slow process currently, requiring an API call for each task.
• To alleviate this we currently cache the submissions in a zip within S3.
• This isn't a perfect solution, as the zip must be updated regularly.

Solution 1

• Make a request on ODK forums for an endpoint that fetches all submissions for a project at once.
• If the idea is accepted, make a PR to implement.
• The submissions can then be gathered much more efficiently, so there may be no need to cache the file in S3.

Solution 2

• We bundle ODK Central in the deployment for dev/staging.
• We could update so that ODK Central is a mandatory part of the stack, even for prod (i.e. migration from the HOT instance to a bundled one).
• Then we could simply access the ODK Central database to collect data we need very efficiently.
• This would solve any potential future requests we have too, as it's easy to do anything we like with the database data.

Note

• These solutions are not mutually exclusive.
• We could possibly implement solution 2, then go the route of seeking community approval / PR for the feature we need.

@Sujanadh @nrjadkry @manjitapandey for discussion / we should consider the best option

[spwoodcock]

An obvious implication of this would mean that users cannot use their own instance if ODK Central, for example an already existing instance in their organisation.

[nrjadkry]

We initially considered accessing the ODK Central database directly for our project. However, we had to introduce the feature of allowing users to integrate their own Central server when creating a project. This requirement forced us to depend on fetching data through the Central API. If in any possible way we can query the central db directly, most of our problems if fetching submissions will be solved.

[spwoodcock]

After discussion, there are multiple reasons why we shouldn't do this:

• We want to use the provided APIs instead of obscure 'underground pipes' (direct db connection) to get our data. It's more maintainable if we use the provided API and let ODK manage making it work with the db schema.
• More importantly, in the future we need to feasibly connect to other backends such as Kobo Toolbox. Or at least keep the option open to do this.