Reporting_Automation

Reporting automations for PrimeAutocare, a portfolio project built by a two-person team. Each report reads the Postgres database, builds an Excel workbook, and commits it to PrimeAutocare/Generated_Reports. A separate, on-demand workflow then reads those same workbooks back and asks Gemini to write a cross-report business review — trends, anomalies, and prioritized recommendations a single report wouldn't surface on its own.

This is the part of the project that keeps itself alive: a fortnightly cron runs all six reports with no human involved, so the pipeline produces fresh, dated output twice a month for as long as the database is up. The AI review is one click on top of that whenever a human wants a second opinion on what the numbers mean.

Architecture

flowchart LR
    CRON["reports.yml<br/>(cron: 1st &amp; 15th)"] --> S["6 Groovy scripts<br/>(JDBC)"]
    S --> DB[("PostgreSQL")]
    S -- "GitHub App commit" --> GR["Generated_Reports<br/>(.xlsx per report)"]

    MANUAL["review.yml<br/>(manual dispatch)"] --> RD["Read back workbooks<br/>as CSV"]
    GR --> RD
    RD -- "prompt" --> GEM["Gemini API"]
    GEM -- "GitHub App commit" --> GR2["Generated_Reports<br/>(AI Review .md)"]

Loading

Technical Highlights

• ETL pipeline — each script extracts data from PostgreSQL via JDBC, transforms it into report-ready aggregates (period filtering, aging buckets, reconciliation against payment records), and loads it into a formatted Excel workbook
• Batch processing & scheduled automation — a fortnightly cron job (GitHub Actions) runs all six reports as one atomic batch, plus a manual/on-demand workflow dispatch with parameterized inputs (report selection, reporting period)
• Business intelligence & reporting — payroll, utilization, receivables, revenue, work-in-progress, and attendance reports, each with dedicated reconciliation/exception-detection logic (e.g. flagging where invoice status disagrees with the underlying payment ledger)
• AI/LLM integration — a separate workflow performs cross-report synthesis by feeding tabular data to the Gemini API, producing a natural-language business review (anomaly detection, trend analysis, prioritized recommendations) beyond what any single report surfaces
• Cross-repository CI/CD — authenticates as a GitHub App (scoped, short-lived installation tokens exchanged from a private key) to publish generated artifacts to a separate repository, since the default workflow-scoped GITHUB_TOKEN cannot write cross-repo
• JVM-based scripting — self-contained Apache Groovy scripts using Apache POI for Excel workbook generation and the PostgreSQL JDBC driver for data access, with dependencies resolved via @Grab

Structure

scripts/
  PayrollReport/       Technician payroll — hours x hourly rate
  UtilizationReport/   How labour hours spread across technicians
  ReceivablesReport/   Billed vs collected vs outstanding
  RevenueReport/       What the workshop earned
  WipReport/           What is on the floor right now
  AttendanceReport/    Who clocked in, who forgot to clock out
  GeminiReview/         AI-generated review of the six reports above
.github/workflows/
  reports.yml          One workflow: cron runs all six, the button asks
  review.yml           Manual only: asks Gemini to review the latest reports

Workbooks are never committed here. They go to PrimeAutocare/Generated_Reports, a separate repository:

Generated_Reports            (separate repo)
  Payroll Report/
    Payroll_Report_July_2026_H2.xlsx    <- current
    archive/
      Payroll_Report_July_2026_H1.xlsx  <- everything it replaced
  Utilization Report/ Receivables Report/ Revenue Report/ WIP Report/
  Attendance Report/
  AI Review/
    AI_Review_July_2026_H2.md           <- current, written by review.yml
    archive/
      AI_Review_July_2026_H1.md

This repository therefore stays code-only — its history holds no business data and its log is not filled with bot commits — and the reports can be wiped by resetting a repo nobody's code depends on.

Writing to a second repository needs a credential that GITHUB_TOKEN cannot provide, because it is scoped to the repository that owns the workflow. That is what the GitHub App is for; see Setup.

No Releases are published. GitHub attaches Source code (zip) and Source code (tar.gz) to every release, generated from the tag with no way to disable them.

Each script is self-contained: it opens its own connection and builds its own workbook. That means the JDBC and POI boilerplate is repeated, which is a deliberate trade — every report is one file you can read top to bottom and run on its own.

Running

Automatically on the 1st and the 15th at 06:00 UTC — a scheduled run always produces all six reports.

By hand from Actions → Reports → Run workflow, which asks which reports you want (all ticked by default) and which period to report. Leave the period blank for the fortnight that just closed, or name one:

You type	You get
(blank)	the latest reporting period that has ended
2026 July H2	15–31 July 2026
July 2026 H2	the same
2026-07-H2	the same
July H2	the most recent July H2 that has ended — so asked in July 2026 this means 2025, and the run says so

Case doesn't matter. Anything else is rejected with the accepted forms rather than guessing at what you meant.

All six run in one job and land in one commit. That is the point of a single workflow: six separate ones would each push on the same schedule and race each other.

GitHub cron has no fortnightly expression, so 1,15 is the standard stand-in. Scheduled runs can start late when GitHub is busy; nothing here depends on the exact minute.

Report	Folder	Answers
Payroll	Payroll Report/	What do we owe the technicians?
Utilization	Utilization Report/	Who is busy, who is idle?
Receivables	Receivables Report/	Who owes us, and for how long?
Revenue	Revenue Report/	What did we earn, from which services?
Workshop WIP	WIP Report/	What is on the floor, and how long has it been there?
Attendance	Attendance Report/	Who clocked in, who forgot to clock out?

The reporting period

A run reports the fortnight that has just closed, not the one it runs in.

Run date	Period covered	Filename
1 August	15–31 July	..._July_2026_H2.xlsx
15 August	1–14 August	..._August_2026_H1.xlsx

Reporting the period you are standing in would only ever produce a half-finished picture, and naming a file after the day it ran tells a reader nothing about what is inside it.

Payroll, Utilization, Revenue and Attendance are filtered to that window — a payroll run must cover the fortnight just worked, not every job in the database. Work counts against the period it was completed in.

Receivables and WIP are snapshots, not period statements, and are deliberately not filtered. A debt is owed as of now regardless of when it was billed; a car is on the ramp as of now or it isn't. The period only names the file, and each workbook's About sheet says so plainly.

Output and archiving

Before writing, whatever .xlsx is already in a report's folder moves into archive/ beside it. Re-running a period replaces the current file and overwrites its archived copy of that period: the archive keeps one file per period, not one per run.

Reading a report

Browse PrimeAutocare/Generated_Reports — each folder holds the current report, with every past period in archive/. Each run's summary names the files it wrote.

AI Review

review.yml is a separate, manual-only workflow (Actions → AI Review → Run workflow) — it does not run on the fortnightly cron, and it does not touch the database. It reads back whichever workbook is currently filed in each of the six Generated_Reports folders, converts every sheet to CSV, and sends all of it in one prompt to the Gemini API, asking for a review that reads across reports rather than one at a time — the kind of thing a single workbook can't surface on its own (a technician heavy in Payroll but absent from Attendance, a WIP job stalled on someone Utilization shows as idle, and so on).

The result is written to Generated_Reports/AI Review/ as a Markdown file named after the period it covers, with the previous run archived the same way the six xlsx reports are — one current file, everything before it in archive/.

The CSVs sent to Gemini are not redacted — customer names, phone numbers, and emails go to Google's API as-is, since this project's data is not production PII. If that ever changes, strip those columns in scripts/GeminiReview/GeminiReview.groovy before the CSV is built.

Setup

Four repository secrets:

Secret	Value
DATABASE_URL	Postgres connection URI, e.g. postgresql://user:pass@host/neondb?sslmode=require
APP_ID	The GitHub App's numeric ID
APP_PRIVATE_KEY	The App's .pem private key, whole file including the BEGIN/END lines
GEMINI_API_KEY	API key for the Gemini API, used only by review.yml

gh secret set DATABASE_URL    --repo PrimeAutocare/Reporting_Automation
gh secret set APP_ID          --repo PrimeAutocare/Reporting_Automation
gh secret set APP_PRIVATE_KEY --repo PrimeAutocare/Reporting_Automation < app.private-key.pem
gh secret set GEMINI_API_KEY  --repo PrimeAutocare/Reporting_Automation

Use the direct (non-pooled) Neon connection string. The script strips parameters the JDBC driver doesn't understand, so a copy-paste from the Neon console works as-is.

The GitHub App

Publishing writes to a different repository, which GITHUB_TOKEN cannot do however its permissions are set — it is scoped to the repository that owns the workflow. A GitHub App is how that gap is closed:

• Org-owned, so it does not break when a person leaves or rotates a token
• No expiry to babysit, unlike a personal access token
• Short-lived tokens: the private key is exchanged per run for an installation token that expires in about an hour
• Scoped to one repository, so a leak reaches nothing else

Deploy keys would also have worked, but they are disabled at the org level.

To create it: Org settings → Developer settings → GitHub Apps → New GitHub App. Untick Webhook → Active. Under Repository permissions set Contents: Read and write. Choose Only on this account. Create it, then Generate a private key and keep the .pem.

Then Install App on the org and grant it access to Generated_Reports only — the workflow reads this repository through GITHUB_TOKEN, so the App needs nothing here.

The App's ID is on its settings page. Both it and the .pem go into the secrets above. The .pem is the one long-lived credential in this setup; if it leaks, regenerate the key on the App's page and update the secret.

Reports

PayrollReport

Pay is derived, not stored. Completed jobs carry the hours worked (JOBS.hours), EMPLOYEE carries the rate (emp_hourly_rate), and payroll is hours × rate. Only completed work (JOBS.status = 'C') is payable — the database guarantees those rows have both a technician and hours. JOBS has no dedicated completion-date column, so updated_at stands in for "when it was completed."

Sheets: Payroll, About. There is no by-month sheet: the report covers a single fortnight, so grouping by month would produce one row restating the total.

UtilizationReport

Each technician's share of the workshop's logged hours for the period, and a sheet listing technicians who logged nothing in it — idle for a fortnight is the signal worth surfacing.

This is not capacity-based utilization. The schema records no shifts or contracted hours, so hours-worked ÷ hours-available cannot be computed. The report shows share of total logged hours instead. Adding a capacity column to EMPLOYEE would turn this into a true utilization percentage.

Counts both completed and in-progress work — a car on the ramp consumes the technician's time either way. That is a wider net than PayrollReport, which pays only for completed work, so the two reports do not tie out. That is intended.

Sheets: Utilization, Idle Technicians, About.

ReceivablesReport

A snapshot, not a period statement — every open invoice as of the run, not just those raised in the period. A debt is owed regardless of when it was billed, and the oldest invoice is exactly the one worth chasing.

Outstanding is derived per invoice as inv_total - SUM(payments) rather than trusted from inv_status — the status is a label a human sets, the payments are the facts. Where the two disagree, the Exceptions sheet lists it. Void invoices are excluded from money totals. VEHICLE.cust_no is nullable, so a vehicle not yet linked to a customer still appears on the register rather than being dropped.

Sheets: Summary, Invoice Register, Aging, Payment Methods, Exceptions, About.

RevenueReport

Revenue is work done in the period (JOBS.cost on jobs completed within it), which is not the same as billed or collected. Work can be complete but not yet invoiced, so this will not tie out against ReceivablesReport — which is a snapshot of all time rather than the period.

Sheets: Summary, By Service Type (every catalog entry, including ones that sold nothing this period), By Customer, About.

WipReport

A snapshot, not a period statement — "still on the ramp" is only true as of now, so this is never filtered to the period.

The schema merged job creation and execution into a single JOBS table with no visit grouping, so this report works one row per open job (status in Pending or In-progress), not per visit — a vehicle with three open jobs shows three rows. Stalled Jobs lists jobs created over a week ago and still Pending.

Sheets: Open Jobs, By Status, Aging, Stalled Jobs, About.

AttendanceReport

Hours logged per employee from the ATTENDANCE table (clock in/out), separate from JOBS — this tracks presence, not billable work. Flags shifts still missing a clock-out and employees with no attendance rows at all in the period.

Comparing this against PayrollReport (hours paid for completed jobs) is how a gap between "present" and "productive" would surface — that comparison isn't done automatically, which is exactly the kind of cross-report read the AI Review below is for.

Sheets: Attendance, Missing Clock-Outs, No Attendance, About.

Local development

export DATABASE_URL='postgresql://...'
groovy scripts/PayrollReport/PayrollReport.groovy
# -> out/Payroll_Report_<Month>_<Year>_<H>.xlsx

Any report runs the same way — swap the path. Output lands in out/, which is gitignored. A script only writes the file; filing and committing it is the workflow's job, so a local run never touches GitHub.

PERIOD names the fortnight to report; leave it unset for the one that just closed:

PERIOD='2026 July H2' groovy scripts/PayrollReport/PayrollReport.groovy
PERIOD='July 2026 H2' groovy scripts/PayrollReport/PayrollReport.groovy
PERIOD=2026-07-H2     groovy scripts/PayrollReport/PayrollReport.groovy
PERIOD='July H2'      groovy scripts/PayrollReport/PayrollReport.groovy  # most recent ended

Requires Java 11+ and Groovy 3.x. Dependencies (Apache POI, the Postgres JDBC driver) are resolved by @Grab on first run.

Team

• Inuka Wijerathna
• Senuka Wijerathna