PostgreSQL to Weekly Email Automation

Many teams keep reliable operational data in PostgreSQL but still copy weekly metrics into spreadsheets or draft status emails by hand. I built this self-initiated technical demo to turn that recurring task into a narrow, testable workflow.

The workflow calculates the last closed week in the America/Lima timezone, queries PostgreSQL with parameters, builds a formatted executive email, and delivers it to a local Mailpit inbox. The fixture, company, addresses, and results are fully synthetic.

Reliability is part of the design. The TypeScript reference core uses a deterministic idempotency key, skips repeat delivery, escapes HTML, records sanitized audit events, and allows a retry after a provider failure. Six behavior tests cover the successful path, duplicate execution, empty input, failure and retry, time boundaries, invalid input, and recipient configuration.

The local smoke run completed the PostgreSQL to n8n to Mailpit path. It produced two paid orders, USD 190 net sales, and USD 10 refunds from the deterministic fixture. These are test values, not customer outcomes.

The repository documents the production boundary honestly: SMTP cannot guarantee exactly-once delivery, and a client pilot would need a durable delivery ledger before activation.

Public repository: https://github.com/Danielrp551/postgres-weekly-email-demo