5 Storing our ACH files
This chapter explains why moving from flat ACH files to a relational database is essential for reliability, performance, and queryability, and then walks through designing a Postgres schema that preserves the ACH record hierarchy and data integrity. It emphasizes referential integrity through primary/foreign keys, constraints, and appropriate data types, and advocates an ELT-first path: load raw lines as-is, then parse and transform when needed. The work proceeds in short, research-driven cycles that expand APIs, evolve the parser, and weave testing into the workflow so the system remains correct as complexity grows.
The implementation starts by storing unparsed records with psycopg3 and context managers, then adds pytest-based tests and fixtures that isolate runs by truncating tables. The API gains a file-upload endpoint (FastAPI) that captures file metadata and a content hash, and the schema is refined to separate uploaded files from individual raw lines, connected by foreign keys and cascading rules. Parsed records are incrementally persisted (beginning with the file header), exceptions are stored in their own table, and supporting utilities consolidate database access and test helpers. Pydantic models encapsulate table shapes and validation while keeping SQL explicit, and generative AI tools help produce boilerplate SQL, schemas, and tests—accelerating iterations without sacrificing secure, parameterized queries.
As foreign keys surfaced design gaps, the schema was reorganized to maintain integrity across the full ACH hierarchy by splitting unparsed records per record type and adding a database view to present a unified raw stream. The team highlights lessons learned: prefer short feedback loops, rely on research spikes, and balance ELT/ETL needs with dashboard aggregation. They compare direct SQL to ORMs, discuss test isolation strategies (fixtures, in-memory options, dedicated test databases, containers), and stress maintainability, consistency, and safe refactoring under test. The outcome is a sturdier, evolvable foundation for storing, parsing, and serving ACH data with the integrity required by financial systems.
Diagram showing relationships between our tables
Flowchart for section 5.2
Flow and associated code listings for this section
Flow and associated code listings for this section
Flow and associated code listings for this section
Updated table diagram
Summary
- Design options for creating a database that can store an ACH file. We aimed to create a database that could store our unaltered records as well as a parsed version of the record which will be beneficial when we need to provide details on the loaded ACH files.
- We saw the implications of adding referential integrity to our database late in the process and the need to rework both our code and unit tests. Sometimes when a feature requires rework to be done correctly it can often be put on the back burner.
- Despite the extra work to implement these types of features it is important to champion them both to the team members and management when they are needed so they are not left by the wayside.
- Defining databases is crucial for data storage, querying, and integrity in applications.
- ACH files face challenges in performance, querying, and data integrity when handled as flat files.
- Relational databases offer advantages like primary and foreign keys, constraints, and data integrity.
- Implementing referential integrity prevents orphan records and ensures database consistency.
- A research spike is beneficial to evaluate database design and various implementation approaches.
- ELT vs. ETL approaches offer different benefits for processing ACH files and handling errors.
- Pydantic helps in modeling database tables, abstracting SQL, and enhancing documentation and validation.
- Uploading files and integrating APIs are foundational for expanding functionality in an ACH system.
- Data integrity and referential integrity are critical for relational databases to prevent errors and improve reliability.
- Continuous testing, refactoring, and revisiting initial design choices help maintain and improve database performance and structure.
FAQ
Why do we move from flat files to a relational database for ACH processing?
Flat files make querying, performance, and data integrity difficult at scale. A relational database lets us persist records across runs, query quickly (e.g., “when was a transaction loaded?”), enforce referential integrity, and maintain ordering and constraints. It also supports both storing raw (unparsed) lines and parsed fields for downstream analytics and dashboards.What are the key design goals of the ACH database schema?
- Maintain the original record order (sequence number)- Preserve unparsed lines so “raw” file context is never lost
- Store parsed fields in typed tables for fast querying and validation
- Enforce referential integrity with foreign keys so deletes cascade correctly (no orphans)
How is referential integrity enforced and why does it matter?
We use FOREIGN KEY constraints with ON DELETE CASCADE/ON UPDATE CASCADE to tie child records (e.g., batches, entries, addenda) to their parents (file, header, etc.). This prevents orphaned rows and ensures that deleting a file or batch automatically removes all related records, keeping the database consistent and reducing maintenance scripts.ETL vs ELT for ACH: why load unparsed records first?
- ETL (Extract, Transform, Load) can fail outright on malformed data.- ELT (Extract, Load, Transform) loads the raw lines first, then parses when needed, preserving the original file for audit/review and allowing “recoverable” adjustments some institutions accept. This improves resilience while retaining the source truth.
How do we connect to PostgreSQL from Python in this chapter’s setup?
We use psycopg (psycopg3). Key points:- Build a DATABASE_URL with dbname, user, password, host, port.
- When running from an IDE (not inside Docker), use host=localhost and expose port 5432 in docker-compose.
- Prefer context managers (with) to automatically close connections/cursors and commit.
How are unit tests structured for database interactions?
- Use pytest fixtures for setup/teardown; truncate tables between tests to keep isolation.- Optionally use a “truncate all” helper that iterates over information_schema to clear public tables.
- Be cautious with in-memory DBs (e.g., SQLite) due to type/feature mismatches with PostgreSQL; consider pytest-postgresql or Testcontainers as the project grows.
How is file upload implemented and what metadata is stored?
Via FastAPI’s UploadFile (multipart/form-data requires python-multipart). The backend:- Reads the uploaded bytes and computes an MD5 (or preferred) hash to detect duplicates.
- Inserts a row into ach_files with file_name, file_hash, created_at (default NOW).
- Uses a shared DbUtils to obtain a psycopg connection and parameterized SQL for security.
How do we parse and store the ACH File Header with validation?
- Parse the fixed-width line into a dictionary of fields.- Create a typed table (e.g., ach_file_headers) with appropriate VARCHAR lengths and NOT NULLs.
- Insert using parameterized queries (named parameters) to match parsed dictionary keys.
- Tests verify both the returned parsed dict and the row persisted in the database.
Build Financial Software with Generative AI (From Scratch) ebook for free
