chapter five

5 Storing our ACH files

 

This chapter covers

  • Creating tables within our PostgreSQL database
  • Designing a relational database capable of storing ACH files
  • Using Python and Pydantic to validate ACH records and store them in our database
  • Ensuring that our records are parsed and stored correctly by implementing unit testing with pytest

In this sprint, we use another research spike to explore how to define our database. Databases store and persist our data across application instances, while providing a way to query and ensure the integrity of that data. Here, we examine how to store our ACH files in a database. After initial analysis, we expand our APIs to store an ACH file in the database. Continuing along that track, we expand our ACH parser to store the individual fields as well. Finally, we wrap up the chapter by examining how storing ACH data affects our unit and load tests.

The introduction of a database is necessary in our project because an ACH file is a flat file. The current ACH system that Futuristic FinTech uses relies on flat files, and they can be challenging in many areas, including performance, querying, and data integrity. For instance, if a customer questions when a transaction was loaded, all the ACH files must be loaded and parsed to perform the search, which is time-consuming. Furthermore, keeping the parsed ACH files in memory becomes unfeasible given the number of records being dealt with.

5.1 Designing our database

5.2 Using SQL directly

5.2.1 Adding records to the ach_files table

5.3 Storing the file header record

5.3.1 Using generative AI

5.3.2 Full example

5.4 Storing the rest of our ACH records

5.4.1 Storing ACH files challenge: Lessons learned

5.5 Storing exceptions

5.6 Uploading an ACH file

5.7 Storing records with integrity

5.8 Using Pydantic

5.9 Lessons learned

5.10 Coding changes

5.10.1 Creating Pydantic schema for the unparsed ACH records

5.10.2 Creating Pydantic schema for the parsed ACH records

5.10.3 Unit test changes

5.11 Design and different approaches

Summary