Sync service

aniram commented

2026-02-14 14:51:44 +00:00

Owner

Food for thought:
i) Use in-memory cache(redis, memcached) for user's favorites.
ii) Use a caching layer -> on a dedicated service retrieve projects, authors and related data from the API as JSON files*, transform and store them as my own entity with the properties as I need them(aggregate information scattered across requests into one entity) to my DB and fetch from there. Depending how fresh the data needs to be, retrieve from the API accordingly.

*their website claims they would provide JSON files updated daily with whole tables. I could use those files instead of making requests timed by a cronjob which could overload their system unnecessarily.

Originally posted by @aniram in aniram/cidadon#6 (comment)

Food for thought: i) Use in-memory cache(redis, memcached) for user's favorites. ii) Use a caching layer -> on a dedicated service retrieve projects, authors and related data from the API as JSON files*, transform and store them as my own entity with the properties as I need them(aggregate information scattered across requests into one entity) to my DB and fetch from there. Depending how fresh the data needs to be, retrieve from the API accordingly. *their website claims they would provide JSON files updated daily with whole tables. I could use those files instead of making requests timed by a cronjob which could overload their system unnecessarily. _Originally posted by @aniram in https://git.marinabsa.com/aniram/cidadon/issues/6#issuecomment-6_

aniram added this to the 2026.1 milestone

2026-02-14 14:51:53 +00:00

aniram referenced this issue

2026-02-14 14:53:47 +00:00

Paginate through list of projects from search by keyword #24

aniram commented

2026-02-14 14:54:21 +00:00

Author

Owner

Check out #24 on how I could verify when to update the cache.

aniram changed title from ~~Cache system~~ to Cache service

2026-02-14 14:54:29 +00:00

aniram added this to the Release 2026.1 project

2026-03-14 22:15:48 +00:00

aniram commented

2026-03-25 20:23:56 +00:00

Author

Owner

@aniram wrote in aniram/cidadon#34 (comment):

Check out #24 on how I could verify when to update the cache.

Nevermind, the HEAD request does not return X-Total-Count.

@aniram wrote in https://git.marinabsa.com/aniram/cidadon/issues/34#issuecomment-188: > Check out #24 on how I could verify when to update the cache. Nevermind, the HEAD request does not return X-Total-Count.

aniram added the

Priority

High

Code/DevOps

labels

2026-04-01 12:06:35 +00:00

aniram commented

2026-05-09 12:27:48 +00:00

Author

Owner

The structure between the CSV and JSON files differ.
For example in the JSON file "ultimoStatus" is an object while in the CSV it has been flattened out to single keys like "ultimoStatus_dataHora" and "ultimoStatus_despacho". Another difference is that the CSV contains "ultimoStatus_idOrgao" while this key inside the JSON file is a key called "codOrgao" on the "ultimoStatus" object.
Also the The CSV is separated by semicolons so in order to read it with sqlite the mode must be set to CSV AND the separator must be set to semicolon like so .separator ;

The structure between the CSV and JSON files differ. For example in the JSON file "ultimoStatus" is an object while in the CSV it has been flattened out to single keys like "ultimoStatus_dataHora" and "ultimoStatus_despacho". Another difference is that the CSV contains "ultimoStatus_idOrgao" while this key inside the JSON file is a key called "codOrgao" on the "ultimoStatus" object. Also the The CSV is separated by semicolons so in order to read it with sqlite the mode must be set to CSV AND the separator must be set to semicolon like so ``.separator ;``

aniram self-assigned this

2026-05-09 12:28:59 +00:00

aniram changed title from ~~Cache service~~ to Sync service

2026-05-10 19:20:52 +00:00

aniram commented

2026-05-10 19:22:00 +00:00

Author

Owner

The offline API does not return data on the history of propositions.

TODO

On the daily cronjob which updates all tables, update also the state. Append current state to a table called proposition_history if it has changed. I cannot create a real timeline since a proposition might change its state multiple times during the day, and the cronjob runs only once. The purpose is to notify the user on the state changes.

The offline API does not return data on the history of propositions. TODO - [x] On the daily cronjob which updates all tables, update also the state. Append current state to a table called proposition_history if it has changed. I cannot create a real timeline since a proposition might change its state multiple times during the day, and the cronjob runs only once. The purpose is to notify the user on the state changes.

aniram commented

2026-05-10 19:34:51 +00:00

Author

Owner

The CSV files are not typed, everything is a string so I'll go with the JSON files.

~~aniram referenced this issue 2026-05-10 19:38:45 +00:00~~

Sync history #109

aniram commented

2026-05-16 19:00:49 +00:00

Author

Owner

TODO

Run cronjob that fetches and bulk inserts all tables from 2001 until the current year.

TODO - [x] Run cronjob that fetches and bulk inserts all tables from 2001 until the current year.

aniram added reference feature/cache-system

2026-05-27 18:55:58 +00:00

aniram commented

2026-06-13 16:53:04 +00:00

Author

Owner

Currently the sync service works but has a few quirks that have to be addressed later:

When fetching the IDs of propositions to be synced it holds all of them inside a array of integers which gets saved on the RAM. If there are 50k IDs the server will take a performance hit.
When creating the INSERT and UPDATE(history_last_sync) statements it all happens in one transaction which keeps the DB hostage for as long as it runs, meaning that nothing can be written to the DB and also if something goes wrong all is lost.
When fetching the JSON files of all members, propositions from 2001 - current year etc, there is no exponential back-off.

Currently the sync service works but has a few quirks that have to be addressed later: - [ ] When fetching the IDs of propositions to be synced it holds all of them inside a array of integers which gets saved on the RAM. If there are 50k IDs the server will take a performance hit. - [ ] When creating the INSERT and UPDATE(history_last_sync) statements it all happens in one transaction which keeps the DB hostage for as long as it runs, meaning that nothing can be written to the DB and also if something goes wrong all is lost. - [ ] When fetching the JSON files of all members, propositions from 2001 - current year etc, there is no exponential back-off.

aniram closed this issue

2026-06-13 16:53:18 +00:00

Rows
Columns

Sync service #34