A flat file stores data as unstructured or minimally structured text rather than relational records. An ACH dashboard may save uploaded files directly on the filesystem, leaving parsing and persistence closely coupled. section 5.0 section 5.1
Flat-file storage becomes difficult as an ACH system grows because it makes teams:
- Load and parse every file to answer historical transaction questions. section 5.0
- Manage increasing records in memory, which becomes unfeasible at scale. section 5.0
- Build sophisticated querying and integrity checks without relational keys, constraints, or defined relationships. section 5.1 section 5.12
ACH dashboard migration
An ACH dashboard initially stores uploaded files as unstructured text files. Replacing that arrangement with PostgreSQL allows the application to retain original records, store parsed fields, relate record types, and query transactions without reparsing every file. section 5.1 section 5.12
Listing 5.1 PlantUML definition of our database
@startuml #1
object ach_file_uploads { #2
ach_files_id: UUID #3
filename: VARCHAR(255) #3
file_hash: VARCHAR(32) #3
credit_total: NUMERIC(12,2) #3
debit_total: NUMERIC(12,2) #3
}
object ach_files {
ach_files_id: UUID
record_type: VARCHAR(1)
record_id: UUID
parsed: BOOLEAN
sequence: NUMERIC
unparsed_record: VARCHAR(94)
}
object ach_file_header_records {
record_id: UUID
fields for record type
}
object ach_batch_header_records {
record_id: UUID
file_header_id: UUID
fields for record type
}
object ach_entry_detail_ppd_records {
record_id: UUID
batch_header_id: UUID
fields for record type
}
object ach_addenda_ppd_records {
record_id: UUID
entry_detail_id: UUID
fields for record type
}
object ach_batch_control_records {
record_id: UUID
batch_header_id: UUID
fields for record type
}
object ach_file_control_records {
record_id: UUID
file_header_id: UUID
fields for record type
}
ach_file_uploads::ach_files_id <-- ach_files::ach_files_id #4
#4
ach_files::record_id <-- ach_file_header_records::record_id #4
ach_files::record_id <-- ach_batch_header_records::record_id #4
ach_files::record_id <-- ach_entry_detail_ppd_records::record_id #4
ach_files::record_id <-- ach_addenda_ppd_records::record_id #4
ach_files::record_id <-- ach_batch_control_records::record_id #4
ach_files::record_id <-- ach_file_control_records::record_id #4
#4
ach_batch_header_records::file_header_id ->
ach_file_header_records::record_id #4
ach_entry_detail_ppd_records::batch_header_id ->
ach_batch_header_records::record_id #4
ach_addenda_ppd_records::entry_detail_id ->
ach_entry_detail_ppd_records::record_id #4
ach_batch_control_records::batch_header_id -->
ach_batch_header_records::record_id #4
ach_file_control_records::file_header_id -->
ach_file_header_records::record_id #4
#4
@enduml #5
Browse the relational schema used to replace flat-file storage and organize ACH uploads, records, parsed fields, and relationships. section 5.1
