An unparsed record preserves an ACH record as its original formatted string rather than relying only on fields extracted from it. Keeping this representation lets a file be retained even when invalid content, such as non-numeric data in a numeric field, prevents a parsed record from being written. section 5.1 section 5.9
In an ACH-file design, each record can carry an identifier, a parsed flag, its sequence number, and an unparsed_record value. Sequence numbers preserve file ordering; parsed records can refer back to their retained originals, supporting referential integrity across the file hierarchy. section 5.1
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
The proposed schema places unparsed_record alongside sequence and parsing metadata, while record-type tables hold the fields extracted for successfully parsed records. section 5.1
Retaining unparsed records supports several practical needs:
- Accept records that cannot be parsed cleanly, instead of requiring every incoming file value to fit database constraints. section 5.9
- Preserve a record for exception investigation when no parsed version was written. section 8.6.1
- Verify processing through regression tests that count unparsed records, parsed records, and exceptions. section 11.5.1
- Avoid repeating common fields by defining a base schema with unparsed_record and sequence_number for multiple ACH record types. section 5.10.1
Storing only unparsed records simplifies the database to one table, but shifts hierarchy cleanup and data-integrity work into application code. Storing only parsed fields has the opposite problem: malformed records may be rejected and lost. A combined design retains the original record while organizing usable values into related record-type tables. section 5.9 section 5.1
Viewing the record behind an exception
An exceptions dashboard can show a summary list first, then retrieve one unparsed record when a user clicks its information icon. A separate endpoint avoids sending unused data in every list response and allows authentication checks and viewing audit trails because a record may contain account numbers or other nonpublic information. section 8.6.1 section 8.7.1
