We track device MAC addresses on our network for security auditing. Realized I could repurpose that data for presence detection - specifically knowing when employees arrive based on their phones connecting to WiFi.
Built a system that:
-
Polls UniFi API every 5 minutes for connected clients
-
Matches MACs against a PostgreSQL table of registered employee devices
-
Tracks the first arrival time per day (not just WiFi reconnections, which happen constantly)
-
Generates SMS via Twilio with Claude Sonnet for varied message content
-
Stores greeting history to prevent repetition (last 10 messages per person fed back into the prompt)
The n8n workflow handles the orchestration - schedule trigger, HTTP request to UniFi, SQL queries, LLM chain for text generation, Twilio node for delivery. Uses a Split In Batches node to loop through multiple simultaneous arrivals.
One gotcha: UniFi’s connectedAt timestamp updates on every roam/reconnect, so I had to implement separate first_seen_today tracking in the database that only resets when last_greeted is from a previous day.
Repo with workflow JSON, SQL schema, and docs: https://github.com/rjsears/n8n_unifi_employee_greeting