table of content

Part 1 FinTech and the Automated Clearing House

1 Exploring FinTech and generative AI

1.1 The Futuristic FinTech project

1.2 ACH dashboard

1.2.1 How important is the ACH?

1.3 SAFe Agile

1.4 The ACH dashboard

1.4.1 Tools of the trade

1.5 A first look at generative AI

1.5.1 A better rubber duck

1.5.2 Hello, Check 21!

1.5.3 Help! This code is not working!

1.5.4 Help! I forgot the syntax!

1.5.5 Did I just give away my data?

1.5.6 Privacy and security

1.5.7 Staying up to date

Summary

2 Parsing payments

2.1 Modernizing our legacy software

2.2 Understanding the ACH

2.3 Parsing an ACH file

2.3.1 Asking ChatGPT to parse an ACH file

2.3.2 Parsing an ACH file with Copilot

2.3.3 Generative AI: Trust but verify

2.4 Automated testing

2.4.1 Testing the number of records read

2.4.2 Parsing the records

2.4.3 File header record (type 1)

2.4.4 Batch header record (type 5)

2.5 Entry detail record (type 6)

2.5.1 Addenda record (type 7)

2.5.2 Batch control record (type 8)

2.5.3 File trailer record (type 9)

2.5.4 Passed!

2.6 The not-so-happy path

2.6.1 File rejection

2.6.2 Batch rejection

2.6.3 Entry rejection

2.7 Interpreting the code

2.7.1 Where’s my switch statement?

2.7.2 Type hints

2.7.3 Secure coding

Summary

Part 2 A path to the minimum viable product

3 Getting started with Docker

3.1 Where to begin

3.2 Creating a docker-compose.yml file

3.2.1 Creating a CloudBeaver container

3.2.2 Create a PostgreSQL container

3.2.3 API container

3.2.4 Web server container

3.3 Connecting our containers

3.3.1 Connecting to the database

3.3.2 Troubleshooting PostgreSQL and FastAPI

3.4 Calling our API

3.4.1 Troubleshooting our web site

3.5 Container security

3.6 Optimizing Docker

3.6.1 General maintenance

3.6.2 Optimizing image size

3.6.3 Optimizing build time

3.7 Removing the hardcoded username/password

3.8 Health checks

Summary

4 APIs: The piping between our components

4.1 API-driven design

4.2 Our APIs

4.3 Building our APIs with FastAPI

4.3.1 Retrieving files with GET /files

4.3.2 Adding a file with POST /files

4.3.3 Finishing the rest of the APIs

4.4 Adding our API to Docker

4.4.1 Preparing the project

4.4.2 Docker setup

4.5 Documenting our API

4.5.1 Swagger

4.5.2 Redoc

4.5.3 What is so great about API documentation?

4.6 Managing our APIs

4.6.1 Benefits of API management

4.6.2 Adding WSO2 API manager to our project

4.6.3 OpenAPI Version

4.6.4 Create an API in WSO2

4.6.5 Troubleshooting

4.7 Testing with Postman

4.8 Load testing with Locust

4.9 Improving our code

4.9.1 Using a prefix and versioning

4.9.2 Adding Pydantic

4.9.3 Validation with Pydantic

4.9.4 Troubleshooting Pydantic

Summary

5 Storing our ACH files

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

6 Taking the next step with Next.js

6.1 Getting started with Next.js

6.2 Creating an upload page

6.2.1 Testing our upload page

6.3 Refactoring our navigation

6.4 Converting recent order to recent ACH uploads

6.5 Mocking our ACH uploads data

6.5.1 Adding WireMock to Docker

6.5.2 Setting up mappings in WireMock

6.5.3 Accessing endpoints with axios

6.6 Expanding the UI with an ACH batch view

6.6.1 Defining our WireMock data for the ACH batch view

6.6.2 Updating the dashboard to take us to the batches screen

6.6.3 Displaying batch details

6.7 Creating a ScatterPlot component

6.7.1 Scatterplot component

6.7.3 Scatterplot custom ticks

6.7.4 Using colors for the chart

6.8 Deploying the dashboard in Docker

Summary

7 Our minimum viable product

7.1 Which minimum are we talking about?

7.2 Preparing for the MVP

7.3 Building out the /files APIs

7.3.1 Testing the API

7.4 ACH batches APIs

7.4.1 Adding the /files/id/batches API

7.4.2 Supporting ACH batches in the dashboard

7.4.3 Uploading files

7.5 Putting the puzzle together

7.5.1 Cleaning up the configuration files

7.5.2 Ensuring APIs are accessible

7.5.3 Programming challenges

7.5.4 Exploring the dashboard

7.6 Gotta have more files

7.7 Adding a basic login page

7.8 Adding TLS to our dashboard

7.9 Testing with Playwright

7.10 MVP wrap-up

7.10.1 Troubleshooting the MVP

Summary

Part 3 Beyond the minimum viable product

8 Exceptions

8.1 Planning enhancements

8.2 Exceptions and warnings

8.3 Creating ACH files with exceptions

8.3.1 Creating ACH files with file-level exceptions

8.3.2 Creating ACH files with batch-level exceptions

8.3.3 Creating ACH files with entry-level exceptions

8.3.4 Clean-up of our file creation syntax

8.4 Expanding the ACH processing for exceptions

8.4.1 ACH exceptions in the database

8.4.2 Recovery options

8.5 Updating the ACH parser

8.5.1 Creating unit tests for the exceptions

8.5.2 Supporting the exceptions in our parser

8.5.3 Validation in Pydantic

8.6 Updating the APIs

8.6.1 Retrieving an unparsed record

8.7 Insights into exceptions with the UI

8.7.1 Showing all the exceptions

8.7.2 A visual cue for exceptions

8.8 Additional UI changes

8.8.1 Creating an exceptions component

8.8.2 Batch statistics component

8.8.3 Adding the application

8.8.4 Making our UI more responsive

Summary

9 Searching and auditing

9.1 Sprint planning

9.2 Searching ACH transactions

9.2.1 BDD testing for searching

9.2.2 Search API

9.2.3 UI search page

9.2.4 Integration testing with Playwright

9.3 Auditing user interactions

9.3.1 Database design

9.3.2 TDD testing for auditing

9.3.3 Auditing logic

9.4 Using middleware for our logging

9.5 Viewing the audit log

9.5.1 Creating the page

9.5.2 Creating the page components

9.5.3 Adding the APIs for the audit log

Summary

10 Company information

10.1 Sprint planning

10.2 Company Information

10.2.1 Database design: Company information

10.2.2 Using generative AI to generate mock data

10.2.3 API design

10.2.4 User interface

10.3 Company dashboard

10.3.1 Navigation for the company dashboard

10.3.2 Creating ACH files for our companies

10.4 Company Information component

10.5 Recent Batches component

10.5.1 Implementing the recent batches API

10.5.2 Displaying recent batches

10.6 Expected Files component

10.6.1 Storing expected files

10.6.2 Implementing the expected files API

10.6.3 Displaying the expected files

10.7 Company Limits component

10.7.1 Storing the company limits

10.7.2 Implementing the company limits API

10.7.3 Displaying the company limits

Summary

11 International ACH transactions and OFAC scanning

11.1 Sprint planning

11.2 International ACH transactions

11.2.1 IAT batches: An overview

11.3 Creating IAT batches

11.3.1 Database changes

11.4 IAT record parsing

11.5 IAT file processing

11.5.1 Unit testing

11.5.2 Updating file processing

11.6 Effects on the dashboard

11.6.1 The get_batches method

11.6.2 Batch entries

11.7 OFAC scanning

11.7.1 Sanctioned individuals and countries

11.7.2 Scanning for individuals

11.8 Application programming interface

11.8.1 Unit test

11.8.2 Creating the endpoint

11.8.3 Finishing the API

11.9 User interface

11.9.1 The OFAC page

11.9.2 OFAC components

11.9.3 OFAC results

Summary

12 Where to go from here

12.1 Backend processing

12.1.1 Asynchronous processing

12.1.2 ORM frameworks

12.1.3 Application programming interfaces

12.2 Infrastructure

12.2.1 Docker

12.2.2 Multitenant environment

12.2.3 Continuous integration and delivery

12.2.4 Testing

12.2.5 Database

12.3 User interface

12.3.1 Charts and forms

12.3.2 Configurable dashboards

12.3.3 Mobile version

12.4 Finance

12.4.1 Accounts and customers

12.4.2 ACH origination and returns

12.4.3 Positive Pay

12.4.4 Service charges

12.5 Final thoughts

Summary

Overview

10 Company Information

This chapter describes an iteration that adds a company-level dashboard to the ACH processing application, turning previously file-centric capabilities into customer-focused insights for business clients. It lays out sprint goals and blockers, then incrementally delivers a Company Information view plus three operational widgets: Recent ACH Batches, Expected Files, and Company Limits. Along the way it highlights practical engineering choices—using Generative AI to seed realistic mock data, structuring isolated tests with Testcontainers, and balancing YAGNI/KISS against future extensibility—to keep development fast, testable, and user-centered.

The data foundation expands with normalized tables for companies, addresses, and phones, plus constrained enums for tax ID types, industries, address/phone/schedule types, and optional logo storage. The chapter shows how to expose this model through FastAPI and Pydantic, including list and detail endpoints, a flexible batches “search” endpoint, and SQL that leverages CTEs, JSON aggregation, and COALESCE for resilient joins when company context may be missing. It also covers building BDD inputs to generate company-specific ACH files, handling formatting issues like truncation/padding, reorganizing SQL bootstrap scripts for maintainability, and wiring a Next.js/Material UI interface that lists companies, validates base64 images, and uses route-driven navigation.

Each dashboard component is implemented end-to-end. Company Information returns a rich record with nested addresses and phones assembled either in application code or via SQL JSON aggregation. Recent Batches surfaces activity by company UUID, joining batch header/control data and fallback naming to handle non-customer batches. Expected Files introduces a schedule-driven tracker that computes next expected dates, marks whether a file arrived, and updates last-file timestamps on upload, with discussion of edge cases like weekends and holidays. Company Limits computes daily totals on the fly, flags breaches, and writes a structured exception during batch control parsing, then presents results with clear visual cues and an enhanced exception modal including company context and recovery guidance. The chapter closes by encouraging CRUD completion, performance profiling, and future extensions such as originating ACH files.

ACH Dashboard Enhancement Timeline

Component to list the companies

Recent Company Batches

Expected Files Component

Company Limits Component

Exception Details Modal

Summary

Sprint planning focused on building key components: Company Information, Recent Batches, Expected Files, and Company Limits, emphasizing the importance of comprehensive data structure design.
We explored creating a company-level dashboard to address the need for storing and displaying detailed company information beyond the initial project scope.
Building robust company data management systems was underscored, providing a framework for enhancing ACH processing services for business customers who need detailed batch information.
Designing foundational company information tables as blockers highlighted their dependency for further development and the consideration of early work on some components to reduce rework.
Leveraging Generative AI helps create meaningful test data quickly, enhancing productivity by generating more than just typical name and address information, including logos.
Implementing a Company Dashboard demonstrated the necessity of organizing UI structures for efficient navigation and detailed data presentation for specific company metrics.
Creating detailed UI components for company overviews taught us the value of user-friendly interfaces facilitating seamless navigation and interaction at the company level.
Designing the API for company data retrieval reinforced the importance of well-structured responses and validation mechanisms for dynamic user interaction with dashboards.
Developing recent batch components highlighted the importance of flexible search functions for batch data retrieval to meet diverse business needs.
Creating expected file components stressed the need for alerting mechanisms and schedule compliance to ensure timely processing of crucial documents and mitigate risks.
Proactive monitoring of ACH files was explored to shift from reactive to proactive measures for improved customer service.
Company limit components emphasized configurable alerts and thresholds to safeguard against unauthorized or unexpected financial transactions.
We learned the importance of expanding exception functionality, such as alert generation, to notify customers through various channels if limits are exceeded.
Utilizing Testcontainers enhances testing reliability by ensuring isolated environments, eliminating dependency on developer-specific configurations, and allowing robust and portable tests.

FAQ

What were the goals of the “Company Information” sprint, and why were company tables a blocker?

The sprint aimed to add a company-level dashboard with four components: Company Information, Recent Batches, Expected Files, and Company Limits. Company database tables were a blocker because most components depend on a company_id foreign key and core company fields. Some work (like Expected Files) could start early, but full functionality and correct displays required the company tables to exist first.

How is company data modeled in the database, and what is the role of ach_company_id?

Company data is stored in a company table with fields like name, tax_id_type (e.g., EIN, SSN), tax_id_number, industry, website, DUNS, and an optional logo. Custom ENUM types (e.g., tin_type, industry_type) enforce valid values. The ach_company_id maps to the ACH Batch Header’s company identification (10 positions, typically zero-padded in files) and ties batches to companies.

Why store company logos in the database, and what are the trade‑offs?

Storing logos in a BYTEA column keeps everything in one system and avoids introducing external storage like S3 or a CDN—simplifying setup and portability. The trade‑off is potential UI performance and payload size, since Base64-encoded images increase response sizes. For production scale, external storage plus URL references often perform better.

How are addresses and phone numbers modeled for a company?

Addresses (mailing, street) are stored in a company_address table keyed by (company_id, address_type) to ensure at most one of each type per company. Phone numbers are stored in company_phone with a composite primary key (company_id, phone_type) and a unique constraint on (phone_number, extension). This enforces one “type” per company and prevents number reuse.

How can ENUM values (like tin_type) be exposed to the UI for dropdowns?

Although ENUMs live in the database, they can be queried from system catalogs to produce a list of valid labels for UI controls. This allows building dynamic dropdowns consistent with the database’s allowed values, avoiding drift between front end and schema.

How are the company APIs designed and tested to be reliable across environments?

APIs return typed Pydantic models (e.g., CompanyOverviewRecord and CompanyDetailRecord) and follow a predictable router layout (e.g., /api/v1/companies). Tests use Testcontainers to start/stop Dockerized dependencies, providing a fresh, isolated database per run and eliminating “works on my machine” issues—ideal for CI/CD.

How does the UI list companies and handle Base64 logos safely?

The Companies page fetches an overview list and renders it in a MUI DataGrid with on-row click navigation to the dashboard. An ImageBase64 component validates the src using a regex (data:image/...;base64,...) and shows nothing if it is malformed, preventing broken or unsafe image rendering.

How are company‑specific ACH files created, and what formatting rules matter?

BDD scenarios generate ACH files with batches tied to a company via company name and ach_company_id. Critical formatting includes truncating/padding company_name to 16 characters in the Batch Header and ensuring company identification is 10 characters (zero‑padded) to maintain ACH record alignment.

Why does the Recent Batches component use a generic search endpoint instead of /companies/{id}/batches?

A general endpoint (/api/v1/files/batches/search) provides flexible criteria (amounts, ranges, company id/name, or UUID), enabling reuse across contexts (not just company dashboards). The query joins ACH batch tables and uses COALESCE to prefer the company name from the companies table, then the Batch Header, then an empty string, covering cases where a batch isn’t tied to a known customer.

How do the Expected Files and Company Limits components determine status and trigger alerts?

Expected Files stores filename, schedule, and last_file_date; the API computes next_file_date via INTERVAL logic and marks file_loaded if a matching upload occurred, with optional weekend adjustments. Company Limits stores daily debit/credit limits, computes current totals from batch controls at runtime, and writes a COMPANY_LIMITS_EXCEEDED exception during batch control parsing when exceeded; the UI highlights exceeded totals and shows enhanced exception details (company name, recovery option).

pro $24.99 per month

access to all Manning books, MEAPs, liveVideos, liveProjects, and audiobooks!
choose one free eBook per month to keep
exclusive 50% discount on all purchases
renews monthly, pause or cancel renewal anytime

lite $19.99 per month

access to all Manning books, including MEAPs!

team

5, 10 or 20 seats+ for your team - learn more

eBook

pdf, ePub, online

$54.99 $27.49

you save $27.50 (50%)

include audio $24.99 $12.49

pro $24.99 per month

access to all Manning books, MEAPs, liveVideos, liveProjects, and audiobooks!
choose one free eBook per month to keep
exclusive 50% discount on all purchases
renews monthly, pause or cancel renewal anytime

lite $19.99 per month

access to all Manning books, including MEAPs!

team

5, 10 or 20 seats+ for your team - learn more

eBook

$54.99 $27.49

you save $27.50 (50%)

include audio $24.99 $12.49

eBook

pdf, ePub, online

$54.99 $27.49

you save $27.50 (50%)

include audio $24.99 $12.49

pro $24.99 per month

access to all Manning books, MEAPs, liveVideos, liveProjects, and audiobooks!
choose one free eBook per month to keep
exclusive 50% discount on all purchases
renews monthly, pause or cancel renewal anytime

lite $19.99 per month

access to all Manning books, including MEAPs!

team

5, 10 or 20 seats+ for your team - learn more