This project builds on our in-house RFID time clock: we previously implemented a Raspberry Pi system that captures time stamps via RFID modules (badges) and pushes them to our back office. We have now completed the pipeline: starting from FileMaker data, we generate an HTML report with updated hours, highlight exceptions (late entries, early exits, missing stamps), ask the AI for a short commentary, and send the whole package via email.
TL;DR
- Attendance collection with Raspberry Pi + RFID, consolidation in FileMaker.
- Data extraction via OData from FileMaker Server.
- (Optional) Trigger a FileMaker Script from Node-RED.
- Generate HTML (daily + monthly) and highlights.
- Request an AI comment via API (ChatGPT) and email the final report.
Architecture at a glance
[Raspberry + RFID] → [FileMaker] → (OData) → [Node-RED] → [OpenAI API] → [Email HTML]
↑ ↘ (FileMaker Script on-demand)
How it works
1) Data from FileMaker
Node-RED queries the current month hours and today’s stamps via OData. We normalize dates/times, compute per-user minutes and monthly totals.
2) FileMaker Script from Node-RED (optional)
Before generating the report, we can ask FileMaker to run a script (e.g. consolidation or recalculation) through a simple HTTP call from the flow.
3) HTML report with tables and highlights
- Hours worked today (user → HH:MM).
- Highlights: morning late entries, exits before noon or between 15:00–17:30, missing check-ins (if a roster is provided).
- Monthly recap (days × users) with totals.
4) AI comment (ChatGPT via API)
The flow sends either a compact JSON payload (date, hours, highlights, totals) or the report HTML. The AI returns a short Italian commentary in a neutral/professional tone, summarizing the day and the monthly trend.
5) Email delivery
We merge the report HTML with an “AI Comment” block and send it via SMTP (Gmail/Workspace) with a dated subject line. A fallback guarantees the base report is sent even if the AI is unavailable.
Why it matters
- Time saver: no manual end-of-day notes.
- Clarity: exceptions are highlighted in a non-judgmental way.
- Scalable: same pipeline for multiple teams/sites.
Security & best practices
- API keys stored in HTTP node credentials (not in code).
- Gmail via App Passwords and 2FA (or OAuth2).
- Certificates: self-signed OK on LAN; plan a valid cert later.
- Separate test branch for AI before wiring the official email.
Outcome
Every evening we receive an HTML email with daily hours, highlights, a monthly table, and a concise AI comment summarizing the situation.
Next steps
- Selectable comment tone (formal/friendly/concise).
- Optional CSV/Excel attachment.
- Real-time web dashboard with Node-RED.
Example output (October 21, 2025)
Hours worked today (21/10/2025)
| User | Hours |
|---|---|
| Alex Marino | 08:00 |
| Brian Reed | 08:30 |
| Michael Reed | 08:30 |
| Patrick Fumani | 09:00 |
| Peter Cole | 00:00 |
Highlights (today)
- Andrew Reed did not check in for the afternoon.
- Maurice Reed checked in at 13:45.
- Maurice Reed checked out at 09:13.
- Peter Cole did not check in for the morning.
- Peter Cole did not check in for the afternoon.
Monthly recap (10/2025)
| Day | Alex Marino | Brian Reed | Michael Reed | Patrick Fumani | Peter Cole |
|---|---|---|---|---|---|
| 01 | 08:00 | 08:30 | 08:30 | 09:00 | 09:00 |
| 02 | 08:00 | 08:30 | 08:30 | 09:00 | 09:00 |
| 03 | 08:00 | 08:30 | 08:30 | 09:00 | 09:00 |
| 04 | |||||
| 05 | |||||
| 06 | 08:00 | 08:30 | 08:30 | 09:00 | 09:00 |
| 07 | 08:00 | 08:30 | 08:30 | 09:00 | 05:00 |
| 08 | 08:00 | 08:30 | 08:30 | 09:00 | 09:00 |
| 09 | 08:00 | 08:30 | 08:30 | 09:00 | 09:00 |
| 10 | 08:00 | 08:30 | 08:30 | 09:00 | 09:00 |
| 11 | |||||
| 12 | |||||
| 13 | 08:00 | 08:30 | 08:30 | 09:00 | 09:00 |
| 14 | 08:00 | 08:30 | 08:30 | 09:30 | 09:00 |
| 15 | 08:00 | 08:30 | 08:30 | 08:30 | 09:00 |
| 16 | 08:00 | 08:30 | 08:30 | 09:00 | 09:00 |
| 17 | 08:00 | 06:30 | 08:30 | 09:00 | 09:00 |
| 18 | |||||
| 19 | |||||
| 20 | 08:00 | 08:30 | 08:30 | 09:00 | 09:00 |
| 21 | 08:00 | 08:30 | 08:30 | 09:00 | |
| 22 | |||||
| 23 | |||||
| 24 | |||||
| 25 | |||||
| 26 | |||||
| 27 | |||||
| 28 | |||||
| 29 | |||||
| 30 | |||||
| 31 | |||||
| Monthly total | 120:00 | 125:30 | 127:30 | 135:00 | 122:00 |
AI comment
The report for October 21, 2025 shows hours recorded by each team member—Alex Marino at 8:00, Brian and Michael Reed at 8:30, and Patrick Fumani at 9:00. A few exceptions stand out: Andrew Reed did not check in for the afternoon, while Maurice Reed registered a late check-in at 13:45 and an early check-out at 09:13. Peter Cole missed both the morning and afternoon check-ins. These items should be reviewed to ensure accurate attendance tracking.
Why this matters
- Time saver: no manual notes at the end of the day.
- Clarity: highlights are surfaced in a neutral, factual way.
- Scalable: same pipeline for multiple teams/sites.
What’s under the hood
FileMaker (OData) → Node-RED (HTML generation + AI prompt) → OpenAI API → SMTP (Gmail/Workspace). We keep API keys in node credentials, use App Passwords for Gmail (2FA), and validate the AI branch in test before wiring the official email.
Leave a Reply