Design and implementation to import data from many sources (for Issue #332)

[ilGobbo00]

BRAINSTORMING

Hi all, I'd like to discuss the design and implementation of the importing features with you (related to Issue #332). The idea is to create a modular system which can be improved in the future without re-designing and re-implementing everything. From an initial reasoning and based on the discussion in the Discord channel, this can be a way to implement the importing feature.

At the end of this message, I wrote some pros and cons of some implementation choices. I don't expect I've covered all critical parts, that's why I want to discuss this topic with you! :)

        INPUT                               OUTPUT

 LIST OF:                     _______
  Transaction                |       |
  RecurrentTransaction  ---> |  DB   | ---> storage
  BankAccount                |_______|

                  
                              ___________        LIST OF:
 inputFile                   |           |        Transaction
 mappings               ---> |  MIDDLE   | --->   RecurrentTransaction
                             |___________|        BankAccount

                              ___________       
                             |           |        inputFile
                             |    UI     | --->   mappings  [1]
                             |___________|        otherInfo [2]

• [1] mappings can be specified manually through the UI with a list of input="text" fields that the user fills or by uploading a file (format to decide)
• [2] Some app export data without account amounts or defined categories. If not existing in the inputFile, more data must be put in these fields (more info below).

 EXAMPLE OF inputFile:
 <account>
    <name>Revolut</name>
    <currentAmount>1000.00</currentAmount>
    <creationDate>01/01/2024</creationDate>
    <currency>EUR</currency>
    ...
 </account>
 <expense>
    <account>Revolut</account>
    <category>Entertainment</category>
    <subCategory>Dinner</subCategory>
    <date>13/04/2025</date>
    <amount>20.58</amount>
    ...
 </expense>
 ...
 <income>
    ...
 </income>

 EXAMPLE OF mappings:
  {
    bankAccount: "account"
    transaction: "expense, income, transfer"
    ...
  }

Discussion points

• Import flow
  • DB: the DB layer accept objects which reflects data in the DB. In this way it's easier to process data and this layer doesn't care about the source (that can be a CSV, XML, JSON, ..., and even a PDF).
  • MIDDLE: as input of this layer, the file containing data to import and the mappings are passed. This layer will read the input file data based on mappings and will create list of objects to be sent do DB layer.
    • [?] Idea: create importing file-specific functions (e.g. fromXML(file, mappings){ db.store(..) }) and app-specific functions (e.g. fromMoneyManager(file){ fromXML(file, mappingsMM)})
      • [+] We have both ready-to-use importing functions for well known app and general importing functions based on source file format.
• bankAccount and startingValue
  • Observation: when exporting/importing data in Sossoldi, the account amounts are expressed as the initial amount.
  • Issues:
    • Some apps don't export accounts nor current account amounts

      • [?] We can ask the user (within the UI) to create the bankAccunts and the current amount. Then, while processing all the transactions, we can calculate the initial amount before passing data to the next layer (MIDDLE)

        • [+] Having the bankAccount instance since the beginning ensures that future FOREIGN KEYs (transaction.idBankAccount -> bankAccount.id) won't be a problem for the db consistency. Moreover we can process bursts of data instead of loading all the transactions in the memory and then processing them.
        • [-] User effort required to inspect data and insert information in the UI

      • [?] We can understand bankAccounts by ourself and then the user corrects account amounts after the import ends.

        • [+] Require less effort from the user
        • [-] More implementation complexity. More data will be stored in the memory before being stored (might be an issue with large amounts of data). More difficult to understand bankAccounts (even more if this field was not exported from the previous app).

[Ic3b3rg]

After discussions with other developers facing similar challenges, I believe the most effective solution is to take a targeted approach for each banking institution. Instead of generalizing, we could implement an intuitive flow in which the user loads his or her data and selects the relevant bank. At that point, a dedicated parser (bank -> parser) specifically designed to interpret that bank's well-defined data format would be activated.

Given the relative stability of bank export formats, the use of deterministic parsers would provide us with greater reliability in processing the data. This would greatly simplify both the detection and resolution of any errors.

To further enhance this solution and foster collaboration, I would suggest encapsulating this logic in an external, open source library dedicated exclusively to our application and shared with the Mr. Rip community. In this way, we could benefit from the input of other developers and ensure more efficient maintenance over time.

[ilGobbo00]

Yeah it sounds good. The decision to implement generalised parsers (like for XML, XLS, ...) and then specific parsers which rely on the general ones came from the idea that it would take less time to implement a new parser for a specific entity. In this situation, if BANK PIPPO and BANK PLUTO have the same exported file extensions (e.g. TSV) but different field names, we just need to add mappings since we already rely on the general TSV parser (that accepts the input file and the mappings). In my opinion, this approach, which requires a little bit more effort at the beginning, can make our life easier in the future when we expand app compatibility (at least for imports)

I agree about the library, in this case, we won't make the code baseline too heavy

[Claffolo]

Hi everyone, I asked to start looking into this issue and I have it assigned. I can say my ideas go in both directions exposed until now.

So, in my opinion the app should have a standard lower level way of importing data, and a higher level of code (that can be provided by the external library as suggested by @Ic3b3rg ) that will support different formats from different apps/banks.

I started doing some experiments to see where this kind of approach can be stretched and I ended up doing it by using a .json file based mapping. The idea is to develop a piece of code that is capable of reading a .json (or even .yml) file and produce an output file that is in the format expected by the lower-level DB import code.

So when this piece of code is in place, stable and reliable (in the external library or in the app itself, that will be decided later I think), the future contributor that wants to add support for a particular bank or app to import data FROM, will need just to write a simple .json or .yml file and add it as a source file.

The app at that point will be capable of dynamically reading the list of supported banks/apps and show to the user said list. If the file is coherent with the app/bank rules then the import goes fine. Obviously everything needs to be designed as reversible so the export will come for free without much work.

I'll attach an example .json file that already works in my fork of the repo (I've not yet pushed anything because everything is very experimental, but the fundametals are there).
revolut.json

As you can see there are some fields in the file:

• A unique Id of the mapping (can be always useful)
• The mapping name, that will be displayed by the frontend
• A logo image to be used by the frontend
• columnMap: this field will map file columns to database columns (if a 1:1 mapping is available)
• valueMap: this field will map value conversions, for example standard strings in the bank will be mapped as standard strings in the Sossoldi app
• defaultValues: this field will tell the app what value to use in the DB for those fields that do not have a 1:1 mapping in the external app/bank file.

Let me know if any of this makes sense to you, and if you want to see the code in action feel free to reach me on Discord, I should be able to answer almost everyday.

I think though that for different Sossoldi entitites we should use different import files, because a giant file that tries to map everything can become hard to maintain in the future. So if you want to import transactions, or accounts, or some other entity you should use different files to do so.

[ilGobbo00]

Hi @Claffolo! Here some comments on what you've said:

the app should have a standard lower level way of importing data

Yeah, exactly what I was thinking and meaning with my schema. The lower level should not care about how data is passed to the app, it just see data (always in the same format) coming as input and its only duty is to store it.

---

The idea is to develop a piece of code that is capable of reading a .json (or even .yml) file and produce an output file that is in the format expected by the lower-level DB import code.

Exactly, that's why we were thinking about a middle layer, because we need something that does the translation between the incoming data and the data that the core of import expects (like a list of Transaction objects)

---

the future contributor that wants to add support for a particular bank or app to import data FROM, will need just to write a simple .json or .yml file and add it as a source file.

Also this is correct. The idea to include some well known app is to help the user to import data, but if a user wants to import data for a non listed app he/she can just load the mappings and the file with data.

---

The app at that point will be capable of dynamically reading the list of supported banks/apps and show to the user said list.

I didn't really get it, what do you mean with dynamically? I think an easy and effective way would be to present the list of well-known supported banks and also the possibility to load its custom mappings and data