Data integrity preserves accurate, consistent, complete, and valid data while it is stored and used. In an ACH system, relying only on flat files makes searching loaded transactions slow and makes large parsed datasets impractical to retain in memory. A relational database supports persistence, querying, and safeguards around the stored file data. section 5.0
Integrity starts in the schema rather than after a failure occurs. Give each table a primary key, connect related records with foreign keys, and apply constraints such as NOT NULL, UNIQUE, and DEFAULT. Choose field data types and sizes that fit the values being stored. These decisions make invalid, missing, or disconnected data harder to introduce. section 5.1
For a hierarchical ACH file, the design must preserve relationships as well as individual values:
- Keep record sequence information so the original file ordering can be maintained. section 5.1
- Store unparsed records alongside parsed forms, accommodating records that cannot be parsed while retaining their original content. section 5.1
- Link parsed records to their unparsed counterparts to maintain referential integrity. section 5.1
- Define deletion behavior deliberately. ON DELETE CASCADE can remove dependent records when a parent is deleted, preventing orphan records. section 5.1
Deleting an ACH batch
Suppose a batch is deleted. The expected result is that its associated type 6–8 records are removed too. A schema may appear correct until foreign keys expose that the deletion path leaves records behind. Treat this as a design problem: revisit requirements, evaluate alternatives, and refactor the database structure and tests rather than accepting incomplete cleanup. section 5.9 section 5.12
Integrity work can require extra implementation effort, especially when it is added after an initial design. Continuous testing, refactoring, and revisiting early database choices help keep the structure reliable as file processing, APIs, and data models grow. 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
Inspect the proposed ACH schema to see UUID identifiers, field types, parsed-state data, sequence values, and the relationships that foreign keys must enforce. section 5.1
