An end-to-end HR automation system that processes biometric attendance logs, detects late arrivals, tracks monthly infractions, and triggers AI-generated employee communications with escalation logic.
[Attendance_Muster_Report.xlsx]
|
v
[Flask /api/upload]
| Parses hierarchical Excel → punch-row JSON
v
[n8n Webhook: POST /webhook/attendance]
|
v
[Fetch Existing Daily Records] — single GET from Supabase (executeOnce)
|
v
[Split JSON Rows] — expands JSON array into individual items
|
v
[Process Attendance Data] — group by EmpID+Date, derive Check-In/Out, Late Flag
|
v
[Dedup Code] — in-memory filter using existing keys from Supabase
|
v
[Batch Daily Records] — collects all new rows into one array
|
v
[Append → Supabase: daily_records] — single bulk POST
|
v
[Is Late? YES/NO]
| YES
v
[Lookup Monthly Tracker] — single GET from Supabase (executeOnce)
|
v
[Calculate Late Count] — increments count, appends date to late_dates
|
v
[Batch Tracker Records] — collects all updates into one array
|
v
[Upsert → Supabase: monthly_tracker] — single bulk POST (ON CONFLICT update)
[HR Dashboard: "Proceed to Send Emails"]
|
v
[Flask /api/send-emails POST]
|
v
[n8n Webhook: POST /webhook/manual-send]
|
v
[Build Calendar Body] — creates Google Calendar event body for L3 employees
|
v
[Create Calendar Event] — Google Calendar API v3 (conferenceDataVersion=1)
| returns hangoutLink (Google Meet URL)
v
[Build Email Items] — per-employee prompt with name, dates, level, meet info
|
v
[HF Generate Email] — HuggingFace Mistral-7B via featherless-ai
| (continueOnFail: true — uses fallback on timeout)
v
[Parse Email Response] — extracts Subject/Body, applies level-aware fallback,
| appends meeting footer (date + time + link) for L3
v
[Send Gmail] — sends personalised email to each employee
[Flask /api/dashboard]
|
v
[templates/index.html] — read-only HR dashboard, auto-refresh 60s
| Layer | Technology |
|---|---|
| Orchestration | n8n (Docker, v2.12+) — two workflows |
| Data Storage | Supabase (PostgreSQL) — daily_records, monthly_tracker, employee_master |
| AI Email Generation | HuggingFace Mistral-7B-Instruct-v0.2 via featherless-ai router |
| Email Dispatch | Gmail API (via n8n Gmail node) |
| Calendar / Meet | Google Calendar API v3 (conferenceDataVersion=1) |
| Dashboard Backend | Flask (Python 3.10+) with 30s cache |
| Dashboard Frontend | Vanilla JS + Inter font — read-only, auto-refresh |
- Docker (for n8n)
- Python 3.10+
- Google Cloud project with:
- Google Calendar API enabled
- Gmail API enabled
- OAuth 2.0 credentials (set up via n8n UI — see
docs/setup.md)
- Supabase project with tables created (see
create_employee_master.sql) - n8n credentials configured via the n8n UI:
Google HR Account(Gmail OAuth2, Google Calendar OAuth2)HuggingFace API(HTTP Header Auth —Authorization: Bearer <HF_TOKEN>)
docker compose up -dn8n available at http://localhost:5678
Run create_employee_master.sql in the Supabase SQL Editor to create all three tables (daily_records, monthly_tracker, employee_master).
python3 seed_employee_master.py
# Reads Attendance_Muster_Report.xlsx → upserts 102 employees into employee_master- Open http://localhost:5678
- Import
n8n-workflow.json→ activate (handles Excel upload + Supabase write) - Import the Manual Email Send workflow → activate (handles
/webhook/manual-send) - Configure credentials (
Google HR Account,HuggingFace API)
pip install -r requirements.txtpython3 app.py
# Dashboard at http://localhost:5001Upload Attendance_Muster_Report.xlsx via the dashboard UI (Upload Attendance button) or CLI:
python3 upload_attendance.py Attendance_Muster_Report.xlsxThe system accepts the official Attendance_Muster_Report.xlsx format — a hierarchical Excel file with the following structure:
- Rows 1–3: Company header (ignored)
- Repeating blocks per employee:
- Employee header row:
Employee No (GDxxxx) | Name | DOJ | Manager No | Manager Name | Access Card No | Department | Designation | Email - Sub-header row: SNO | Attendance Date | Session1 Status | Session2 Status | In Time | Out Time | ...
- Daily attendance rows: one row per working day, with In Time/Out Time as
"DD Mon YYYY HH:MM"strings - Total row: skipped automatically
- Employee header row:
The Flask _parse_excel() function detects employee header rows (column A starts with "GD"), reads each day's In Time / Out Time, and emits punch-row JSON compatible with n8n:
{ "Employee_ID": "GD0001", "Name": "Alice Kumar", "Email": "alice@co.com",
"Date": "2026-02-03", "Time": "11:30", "Punch_Status": "IN" }A Check-in recorded after 11:00 AM is automatically flagged as Late.
- Check-in > 11:00 AM →
late_flag = YES- Check-in ≤ 11:00 AM →
late_flag = NO
Late detection uses biometric check-in time only. Missing OUT punches do not affect the late flag.
| Late Count | Level | Action |
|---|---|---|
| 1 | Level 1 | Friendly reminder — supportive tone, mentions biometric date |
| 2 | Level 2 | Firm notice — references both dates from biometric records, warns of formal warning |
| 3+ | Level 3 | Formal written warning — lists first 5 late dates + "and more", Google Meet scheduled, meeting details + link in email footer |
- AI emails generated by HuggingFace Mistral-7B (featherless-ai router)
- All prompts instruct the model to reference "our biometric attendance records" as the data source — never team members or colleagues
- L3 emails: only the first 5 late dates are listed, year appears once at the end (e.g. "February 3, February 5, February 7, February 9, February 16, 2026 and more.")
- Meeting details (date, time, duration, Google Meet link) are appended as a clean footer — not embedded in the AI body to avoid duplication
- If HuggingFace is unavailable, level-aware fallback templates fire automatically (
continueOnFail: true) — the employee still receives a correctly toned, personalised email with their name and dates
| Column | Type | Notes |
|---|---|---|
| employee_id | text | e.g. GD0001 |
| name | text | |
| date | date | YYYY-MM-DD |
| check_in | text | HH:MM or MISSING |
| check_out | text | HH:MM or MISSING |
| late_flag | text | YES / NO |
| status | text | Complete / Incomplete |
| Column | Type | Notes |
|---|---|---|
| employee_id | text | Primary key (with month_year) |
| name | text | |
| text | ||
| late_count | integer | Incremented on each late day |
| late_dates | text | Comma-separated YYYY-MM-DD list |
| last_warning_date | date | Date of most recent email sent |
| month_year | text | YYYY-MM |
| Column | Type | Notes |
|---|---|---|
| employee_no | text | Primary key (GDxxxx) |
| name | text | |
| date_of_joining | date | |
| manager_no | text | |
| manager_name | text | |
| access_card_no | text | |
| department | text | |
| designation | text | |
| text |
- Summary KPI cards: On-Time Today, Late Today, At Risk (2 strikes), Critical (3+ strikes)
- Monthly Strike Tracker: All employees ranked by late count, colour-coded rows
- Risk Distribution donut chart: Safe / At Risk / Critical percentages
- Auto-refresh: Every 60 seconds — no page reload needed
- Search: Filter employees by name or ID in real time
- Excuse days: Reduce an employee's late count directly from the dashboard
- Proceed to Send Emails: Opens a modal to schedule a Google Meet and send escalation emails to all employees with active strikes for the displayed month
- Smart month detection: Automatically shows the most recent month with data — so uploading February data in March still populates the dashboard correctly
| Setting | Location | Value |
|---|---|---|
| Supabase URL | app.py line 17 |
SUPABASE_URL = "https://..." |
| Supabase Service Key | app.py line 18 |
SUPABASE_KEY = "eyJ..." |
| n8n Attendance Webhook | app.py line 19 |
N8N_WEBHOOK = "http://localhost:5678/webhook/attendance" |
| Cache TTL | app.py line 20 |
CACHE_TTL = 30 (seconds) |
| Flask port | app.py last line |
port=5001 |
hr-attandance/
├── app.py # Flask backend — API endpoints + dashboard serving
├── templates/
│ └── index.html # Read-only HR dashboard (CSS + HTML + JS)
├── n8n-workflow.json # Attendance upload workflow (import via n8n UI)
├── n8n-manual-send-workflow.json # Email + calendar workflow (import via n8n UI)
├── create_employee_master.sql # SQL to create all Supabase tables
├── seed_employee_master.py # Seeds employee_master from Attendance_Muster_Report.xlsx
├── Attendance_Muster_Report.xlsx # Official biometric export (input file)
├── upload_attendance.py # CLI tool: Excel → JSON → n8n webhook
├── docker-compose.yml # n8n Docker setup
├── requirements.txt # Python dependencies
├── README.md # This file
└── docs/
├── setup.md # Step-by-step setup guide
└── PROJECT.md # Architecture, edge cases, scalability
# 1. Start n8n
docker compose up -d
# 2. Start Flask
python3 app.py
# 3. Upload attendance data
python3 upload_attendance.py Attendance_Muster_Report.xlsx
# Expected output: "Sending 3696 rows... Workflow was started"
# 4. Verify upload workflow
# - n8n executions at http://localhost:5678 → should show "Success"
# - Supabase daily_records table → new rows for each employee+date
# - Supabase monthly_tracker table → late counts incremented
# 5. Verify dashboard
# - Open http://localhost:5001
# - KPI cards show on-time/late counts for the most recent date in the data
# - Monthly Strike Tracker shows employees ranked by late count
# 6. Test email send
# - Click "Proceed to Send Emails" on the dashboard
# - Set meeting date/time → click "Send All Emails"
# - Gmail inbox → each employee receives a level-appropriate email
# - L3 employees → Google Meet link in email footer