There is very little overlap in the data models. With Central, you should use the REST API because we don't guarantee a stable database structure.
@yanokwa Aggregate created a relational data model that allowed quite "easily" to create views/materialized views, with triggers copy data to other tables the moment forms were submitted, etc. I had a quick look to the API and I'm not sure would allow me do the same. Is there any info about the Central data model? What do you mean when you say you can change the database structure? at any time? in between major releases? Can you elaborate please?. Thanks.
Good morning @manghig ,
we faced the same dilemma in the past 12 month. We had a lot of dependencies between aggregates relational data model and our own internal PostGIS database we wanted to solve before migrate to Central.
I explored different ways as you seem to do now and following GetODK's team recommendations, I finally focus on the API data access.
In order to keep our workflow into PostgreSQL without extra tools, I worked during spring 21 on some functions that get the submissions from Central and automatically create the data base table (one for the core and one for each repeat group, as did aggregate. If new columns are add to the form, the functions update the data model. Those tools are used "in production" since April and 4000 submissions later, everything works really fine. The only extra tool needed on your database server is curl.
I am sure it can be improved but it's enough magical for the moment 
Note this tool is not an official GetODK tool and is an individual work, feel free to try it, make some feedback, and improve it.
3 Likes