~/projects/ncct-reports/README.md
A free-to-air TV network has to file monthly reports with the National Council for Children's Television (NCCT): a Daily Programming Guide and a daily children's-content compliance report. Both were retyped by hand from weekly program grids every month. This system takes the grids through a simple upload form and emails back finished Excel and PDF reports, with compliance percentages computed and data problems flagged.
cat architecture.flow
architecture.flow
Request
Engine
Delivery
cat problem.md
Every month, operators rebuilt two regulator reports by hand from the network's weekly program grids. The Daily Programming Guide (DPG) lists every show by day. The daily compliance report (MCR) computes how much airtime went to qualifying children's programming.
The source grids are made by people, so they contain typos, mistyped durations, inconsistent AM/PM markers, shows that run past midnight, and different layouts from week to week. Retyping them meant errors carried straight into a regulatory filing.
cat solution.md
I built a Python engine that parses the weekly grids, reconstructs the full month's schedule, looks up each show in a catalog for format, genre, rating and children's-content flags, and fills the official DPG and MCR Excel templates exactly as the regulator expects.
Operators use an n8n form: pick the month, enter a recipient, and upload the grid workbooks. The engine runs as an internal HTTP service. Gotenberg converts the workbooks to PDF, and n8n emails back the xlsx and PDF files with the compliance percentage per report, every data warning, and any titles missing from the catalog.
The show catalog was seeded from 313 historical report tabs, so the engine starts from the network's real history instead of an empty list.
ls ./components
The engine runs in its own container on the shared Docker network with no published ports, a token header and a memory limit. Only n8n can reach it.
Separate modules for grid parsing, schedule building, layout, the DPG and MCR fillers, catalog lookup and time parsing. Each is small enough to test on its own.
Reports are written into the regulator's own Excel templates, not regenerated look-alikes. Styles are re-applied per row so the output matches the official format.
Show metadata lives in a mounted CSV, so operators can add or correct titles without a code change or redeploy.
LibreOffice-based conversion through the Gotenberg service that is already shared by other apps on the homelab.
The workflow is wired to the shared error-alert workflow, so a failed run notifies me instead of failing silently.
./run --trace
The operator opens the form and enters the month, a recipient email, and the weekly grid workbooks.
The service picks the visible tabs that cover the month and parses each show block, normalising times and durations.
Days with identical schedules are grouped into report tabs, so a month produces as few tabs as the data allows.
Titles are resolved against the catalog, and children's-content compliance is computed per tab.
The DPG and MCR templates are filled and converted to PDF with Gotenberg.
n8n emails all xlsx and PDF files with the compliance percentages, warnings, and any unknown titles.
tail -f incidents.log
cat package.json | jq .stack
echo $EXIT_STATUS
outcome.log
A monthly regulatory filing that used to be retyped by hand is now one form submission, and the result is in the operator's inbox as finished Excel and PDF reports.
Just as important, the system catches mistakes in the source grids. Every correction and unknown title is reported back, so the filing is more accurate than the manual process it replaced.