Healthcare staffing, US in-home care agency
Home Care Operations Automation: Overnight Safety Checks & Self-Filling KPIs
More than fifteen connected automations run live. Fixes were proven against the same nights where problems were reported, a 1,993-row discharge report was verified row for row, and each KPI was checked by the client before joining the scorecard. No time-saved, headcount or cost figure was measured.
- Our role
- Operations automation engineer, architecture, build and reconciliation
- Timeline
- Built incrementally; 15+ automations running in production
- Sector
- Healthcare staffing, US in-home care agency
At a glance
- Automations running live
- 15+
- Report feeds read automatically
- 27+
- Escalation touches before a person is needed
- 4: text, reminder, 2 calls
- Numbers copied by hand into the scorecard
- 0
- Overnight check cadence
- Every hour, every night
- Largest report reconciled row for row
- 1,993 rows
- Stack
- n8n, Google Sheets, Gmail, Twilio, cloud telephony, OpenAI
What was breaking
The core problem was evidential, not operational. Caregivers work alone overnight with vulnerable clients, and the agency had no record that anyone was awake and responsive at any given hour. Everyone believed the care was good; nobody could demonstrate it. In a sector where a family's question or a regulator's audit can arrive without warning, the absence of a record is itself the risk, and the obvious fix, having a human ring round every hour all night, would have cost a full-time salary to produce a record nobody could fully trust anyway.
The second problem was the Monday ritual. Leadership KPIs were assembled by hand from a pile of exported reports every week: slow, error-prone, and completely dependent on one person being in the office. When they were on leave, the numbers simply did not appear. A metric that stops when someone takes a holiday is not a metric, it is a habit.
Then a long tail of things nobody had capacity to watch. Missed inbound calls went unnoticed and uncounted, so the agency could not tell whether it was losing enquiries. Visits whose length did not match the billing code they were booked under went unchecked, which is both a revenue and a compliance exposure. Bonus letters were hand-written every two weeks. And dozens of daily care notes meant a fall, a hospital admission or a complaint could sit unread in a pile nobody had time to read end to end.
What made this build different from ordinary operations automation is the consequence profile. In most automation, a silent failure means a slightly stale dashboard. Here, a check-in cycle that failed quietly would produce a clean overnight record for a night that was never actually checked, which is worse than having no system, because it manufactures false assurance. That shaped nearly every design decision.
No evidence a caregiver was awake overnight
The agency could not answer a family's or a regulator's question about a specific night, and hourly manual checks would have cost a full-time salary.
Weekly KPIs depended on one person
Hand-assembled from exported reports every Monday. Slow, error-prone, and absent entirely whenever that person was away.
Missed calls went uncounted
Unanswered inbound calls were invisible, so nobody knew whether enquiries were being lost or how many.
Billing-length mismatches were unchecked
Visits whose duration fell outside the range expected for their billing code went unexamined, a revenue and compliance exposure at once.
Recognition was manual
Bonus letters were written by hand every two weeks, so the admin cost scaled with the number of caregivers being rewarded.
Critical events hid in a pile of notes
A fall, a hospital admission or a complaint could sit unread among dozens of daily care notes nobody had time to read fully.
A quiet failure would manufacture false assurance
In safety-critical work, a check-in cycle that silently breaks produces a clean record for an unchecked night, worse than no record at all.
What we built
The foundation came from a feature nobody thought of as an integration: the agency's management software could email any report on a schedule. That turned an opaque system with no usable API into a data source. Dozens of scheduled reports now land in a dedicated inbox, and almost every automation follows the same six steps, a report arrives, the workflow reads the Excel or PDF attachment, business rules are applied, results are written to a date-stamped sheet tab, texts or calls or emails go out, and sheet formulas count rows per week so the leadership scorecard pulls through automatically. Finding a scheduled-email export is often the difference between 'this system has no API' and 'this system has a daily feed'.
The overnight check-in cycle is the heart of it, and its design is all about distinguishing silence from unreachability. At three minutes past each hour, every caregiver whose shift is genuinely open (at least twenty minutes remaining, at least fifty-five minutes since their last check-in) receives a text. Replies are read back at eighteen and thirty-five minutes past, matched to the right check-in, and only count within a fifteen-minute window. At twenty past, a silent caregiver gets a violation record and a reminder. At thirty-seven past comes the first call, at fifty-two past the second, and only if nobody answers is the cycle marked unreachable and escalated to a person.
Voicemail detection is what makes that chain honest. Without answering-machine detection, a call that reaches voicemail looks exactly like a call that reached a caregiver, and the system would close a check-in that nobody actually answered. Only a live human voice counts as reached. This is the single most important technical detail in the build, because it is the difference between a safety record and a convincing-looking fiction.
The escalation ladder exists to protect the humans at the top of it. Four touches (text, reminder, two calls) happen before anyone is woken up, so managers are only disturbed by genuine unreachability. Escalation that fires on every missed text becomes noise, and noise gets muted, and a muted alert is no alert. Tuning the ladder is not a convenience feature; it is what keeps the alarm meaningful.
Alongside safety sits recognition, which I think is why the system was adopted rather than resented. Every morning each caregiver gets a personal text with their night result and their run of perfect nights, plus a link to a private mobile leaderboard. A night only counts once it has been graded, so a platform outage can never reset someone's streak. A monitoring system that only ever contacts people when they have failed teaches them to dread it; this one contacts them after a good night too.
The KPI pipeline makes the Monday ritual disappear. Eighteen report feeds are read into raw tabs, sheet formulas count rows per week, and the leadership scorecard reads those counts, so the numbers fill themselves identically regardless of who is in the office. Critically, each KPI was checked by the client before it was allowed to join the scorecard. A metric nobody has validated is a number people will quietly stop trusting, and once trust goes the whole dashboard becomes decoration.
The remaining automations each close one of the gaps nobody had capacity to watch: missed inbound calls emailed the moment they happen and counted weekly; every visit whose length falls outside its billing code's expected range flagged and emailed; bi-weekly personal bonus emails; a monthly list of active clients with no care-management contact in six months; discharges sorted into controllable, uncontrollable or needs-review; rehire eligibility tracked against new starts; and a daily AI brief that turns a day of client and caregiver notes into a one-minute leadership report that leads with safety. That last one refuses to send a partial report, if a source is missing, it says so rather than quietly summarising less.
Fail loudly: a silent zero is a bug
In safety-critical work, a report that publishes a reassuring blank when a source is missing is worse than one that fails. The quality KPIs refuse to send a silent zero, and the daily brief refuses to send partially.
Only a live human voice closes a check-in
Answering-machine detection is what separates a real safety record from a plausible-looking fiction. Without it, voicemail closes check-ins nobody answered.
Protect the escalation path
Four touches happen before a person is woken. Escalation that fires on every missed text becomes noise, noise gets muted, and a muted alert is no alert at all.
The office owns the settings
Thresholds, windows and recipients live in editable sheet tabs. Operational tuning should never require an engineer, or it will not happen.
Every write is safe to repeat
Fixed record IDs mean re-running after a failure never duplicates. Re-running is the normal recovery path in an unattended overnight system, so it has to be safe by construction.
Keep raw data separate from results
Source rows are retained and results are derived, so every number on the leadership scorecard can be traced back to the rows that produced it.
Reconcile against real events before sign-off
Fixes were proven against the exact nights where problems had been reported, not against synthetic tests. In operations work, the bug report is the test case.
Non-negotiables
- Fail loudly. In safety-critical work a silent zero is treated as a bug, not a value; a missing source must stop the report rather than publish a reassuring blank.
- The office owns the settings. Thresholds, windows and recipients live in sheet tabs the office can edit, not in code that needs an engineer.
- Every write must be safe to repeat. Fixed record IDs mean a re-run after a failure never duplicates anything.
- All times in the agency's local time zone, never the server's, an off-by-one-hour error in an overnight system is invisible and dangerous.
- Keep the raw data. Results are derived; the source rows stay, so every number on the scorecard can be traced back.
- Only genuinely unresolved cases reach a human, or escalation becomes noise people learn to ignore.
The pipeline
Drawn as text and shapes rather than shipped as an image, so the step labels stay readable and downloadable.
The overnight check-in cycle
Runs every hour, every night. Four touches happen before any human is disturbed, and only a live voice closes a check-in.
- Trigger
- Branch / filter
- Error handling
- Processing step
- Delivered output
- Data store or CRM
- Primary path
- Flow
- Conditional or exception path
The shared six-step report pattern
Almost every automation reuses this shape. The management software's scheduled-email export is what turned a closed system into a data feed.
- Trigger
- Processing step
- Branch / filter
- Error handling
- Data store or CRM
- Delivered output
- Primary path
- Conditional or exception path
How the work runs
Each stage, what it does, and the specific failure it is built to prevent.
Turn a scheduled email export into a data feed
The management software had no usable API but could email any report on a schedule. That single feature became the integration layer: dozens of scheduled reports now land in a dedicated data inbox. Before concluding a system is closed, check whether it can email you a report. It is frequently the difference between no integration and a daily feed.
Forwarded reports are unwrapped, the workflow opens the wrapper, reads the inner subject and attachment, and continues normally.
Read exports that are not really tables
The schedule export had banner rows, a header per client and total rows mixed into the data. Rather than assuming a clean grid, the workflow reads by column position, locates the real header wherever it sits, and rebuilds a clean list of shifts. Old Excel formats that the libraries could not open are converted through Drive, read, and the temporary copies deleted.
A discharge export that no spreadsheet library could parse (over 1,000 empty date cells) is unpacked and read directly, verified row for row across 1,993 rows.
Compute everything in the agency's local time
The server runs on universal time and the agency does not. Every date, eligibility window and log timestamp is calculated in the agency's local zone. In an overnight system an off-by-one-hour error is close to invisible and genuinely dangerous: it shifts which shifts look open and which check-ins look late.
Removes a whole class of silent error that would be nearly impossible to spot from the output.
Select only genuinely open shifts each hour
At three minutes past the hour the workflow finds caregivers with at least twenty minutes of shift remaining and at least fifty-five minutes since their last check-in. Both conditions matter: without the first you text someone as they clock out, and without the second a long shift gets checked twice in quick succession.
A nine-hour shift was initially getting one check-in too few because the final hourly tick landed seconds after the shift ended; check-ins are now allowed within five minutes of the end.
Read replies on a window, and match them correctly
Replies are read at eighteen and thirty-five minutes past and matched to the specific check-in they answer, with only replies inside a fifteen-minute window counting. Early on, caregivers who had replied were still being marked unreachable; the root cause was bursts of requests hitting rate limits so only the newest message was read. The fix was paced, retried requests plus a full scan of the reply window.
Prevents the worst possible false alarm: escalating a caregiver who actually answered.
Escalate through four touches before waking anyone
At twenty past, a silent caregiver gets a violation record and a reminder text. At thirty-seven past comes the first call, at fifty-two past the second, and only then is the cycle marked unreachable and escalated. Protecting the escalation path is the point, alerts that fire on every missed text get muted, and a muted alert is no alert.
Only a live human voice closes a check-in
Calls use answering-machine detection, and voicemail does not count as reached. This is the most consequential detail in the build: without it, a voicemail pickup looks identical to a caregiver answering, and the system would close check-ins nobody responded to, producing a clean record for an unchecked night.
Separates a genuine safety record from a convincing-looking fiction.
Keep the phone connection alive
The telephony login key expires, so it is stored and refreshed a minute before expiry. Without this the connection breaks silently in the middle of the night, the system keeps running and simply stops reaching anyone, which is precisely the failure mode that must not be quiet.
An expired credential cannot silently disable the overnight cycle.
Report to managers, and to caregivers
Managers get a per-caregiver morning report with cycles, violations, unreachable cycles and response rate, plus a Monday version with a day-by-day chart. Caregivers get a personal morning text with their night result and their streak of perfect nights, and a link to a private mobile leaderboard. A night only counts once graded, so an outage can never reset a streak.
Contacting people after a good night, not only a bad one, is what keeps a monitoring system from being resented.
Fill the KPI scorecard from the feeds
Eighteen report feeds are read into raw tabs, sheet formulas count rows per week, and the leadership scorecard reads those counts, so the Monday assembly ritual disappears and the numbers are identical regardless of who is in the office. Each KPI was checked by the client before it was allowed onto the scorecard.
An unvalidated metric is one people quietly stop trusting, and once trust goes the dashboard becomes decoration.
Refuse to publish a reassuring blank
The daily and weekly quality KPIs count four caregiver quality measures and will not send a silent zero when a source is missing. The AI daily brief likewise refuses to send a partial report. In safety-critical work, a figure that looks fine because its input vanished is the most dangerous output a system can produce.
Required columns are validated before any sheet is cleared or written, so a broken file cannot wipe a tab that is cleared and refilled.
Close the gaps nobody had capacity to watch
Missed inbound calls are emailed the moment they happen and counted weekly. Visits whose length falls outside their billing code's expected range are flagged and emailed. Bi-weekly personal bonus emails go to every active caregiver. A monthly report lists active clients with no care-management contact in six months, discharges are sorted into controllable, uncontrollable or needs-review, and rehire eligibility is tracked against new starts.
The 'uncontrollable' check runs before the 'controllable' one, because the word contains the other and would otherwise mislabel every loss.
Key records on something that cannot collide
Keying caregiver rows on name alone would have merged about forty-six different people. Rows are keyed on name plus date, which reduced collisions to two undated rows that never affect any count. Identity is the quiet foundation of every operations metric, get it wrong and the numbers are confidently incorrect.
Fixed record IDs also make every write safe to repeat, so a re-run after a failure never duplicates.
What changed
| Aspect | Before | After |
|---|---|---|
| Overnight coverage | No proof a caregiver was awake. | An hour-by-hour record for every caregiver every night, with full history. |
| False alarms | Missed replies and voicemails both looked like silence. | Paced requests, correct reply matching and voicemail detection, so managers act on real problems. |
| Weekly KPIs | Copied by hand every Monday, and absent when one person was away. | The scorecard fills itself from report feeds, identical whoever is in the office. |
| Missed calls | Unnoticed and uncounted. | Emailed the moment they happen and counted weekly. |
| Billing accuracy | Visit lengths unchecked against billing codes. | Every out-of-range visit listed and emailed, with a weekly count. |
| Caregiver recognition | Bonus letters written by hand every two weeks. | Personal bonus emails and a morning streak text after good nights. |
| Leadership awareness | A hundred notes nobody could read. | A one-minute daily brief, safety first, that never sends partially. |
| Client follow-up | Clients could go months without care-management contact. | A monthly list of anyone uncontacted for six months. |
| Traceability | Numbers with no audit trail. | Every message, reply, call, file and email logged; every figure traceable to its source rows. |
What it runs on
Every tool listed with the job it actually does in this build.
Orchestration
n8n
Orchestration for all fifteen-plus automations, reusing one six-step pattern so a new report feed is a variation rather than a new design. Each workflow is inspectable run by run, which matters when the client needs to answer a question about a specific night.
Data
Google Sheets
Three distinct roles: raw date-stamped tabs holding source rows, office-editable settings tabs so thresholds need no engineer, and formula-driven scorecards. Chosen because the office already lives in spreadsheets and can audit every number themselves.
Google Drive
Converting legacy Excel formats the libraries could not open, then deleting the temporary copies.
Integration
Gmail
The data inbox receiving 27+ scheduled report feeds, and the sender for manager reports, bonus emails and exception alerts.
Cloud telephony API
Check-in texts, reply reading and missed-call events, with the login key refreshed before expiry so the overnight cycle cannot break silently.
Delivery
Twilio
Escalation calls with answering-machine detection, the capability the whole safety record depends on, since voicemail must not count as reaching a caregiver.
AI
OpenAI
Turning a day of client and caregiver notes into a one-minute leadership brief ordered safety first, and refusing to produce a partial report when a source is missing.
Platform
Home care management software
The system of record for schedules, caregivers and billing. Integrated through its scheduled-email report export rather than an API.
What stopped being manual
Snippets are illustrative and generic. No client data appears in any of them.
Eligibility, in local time, with both guards
Replaces a person deciding who to ring this hour. Both conditions matter: without the first you text someone as they clock out, without the second a long shift gets checked twice in minutes.
javascript
// Server runs UTC; the agency does not. Everything in local time.
const now = localTime(AGENCY_TZ);
const eligible = shifts.filter(s =>
isOpenNow(s, now) &&
minutesRemaining(s, now) >= 20 && // don't text at clock-out
minutesSince(s.lastCheckIn, now) >= 55 // don't double-check
);
// A 9-hour shift was missing its final check-in because the last
// hourly tick landed seconds after the end, now allowed within 5 min.Voicemail is not a human
Replaces nothing a person did. It prevents the system from lying. Without answering-machine detection a voicemail pickup closes a check-in nobody answered, manufacturing a clean record for an unchecked night.
javascript
const call = await placeCall(caregiver, { answeringMachineDetection: true });
// ONLY a live human voice closes the check-in.
if (call.answeredBy === "human") {
return closeCheckIn("reached by call");
}
// voicemail, no-answer, busy -> still unreached
return escalateFurther(call.answeredBy);Refuse to publish a reassuring blank
Replaces a weekly number that silently reads zero when its source failed to arrive. In safety-critical reporting, the dangerous output is not an error. It is a figure that looks fine.
javascript
// A silent zero is treated as a BUG, not a value.
if (!source || source.rows.length === 0) {
await alertLog("source missing - report suppressed", source?.name);
return; // send nothing rather than a comforting 0
}
// Required columns are validated BEFORE anything is cleared,
// so a broken file cannot wipe a tab that is cleared and refilled.
assertColumns(source, REQUIRED_COLUMNS);Identity that cannot collide
Replaces the assumption that a name identifies a person. Keying on name alone would have merged about forty-six different caregivers into each other, producing numbers that are confidently wrong.
javascript
// name alone merged ~46 different caregivers
// const id = caregiver.name; // WRONG
const id = `${caregiver.name}::${shiftDate}`; // collisions -> 2 undated rows
// Fixed IDs also make every write safe to repeat, which matters
// because re-running is the normal recovery path overnight.
await upsertByFixedId(id, row);Order the string checks so one cannot shadow the other
Replaces a manual categorisation of why each client was lost. A small bug with a real consequence: 'Uncontrollable' contains 'Controllable', so a naive substring check mislabels every uncontrollable loss.
javascript
// "Uncontrollable" CONTAINS "Controllable", order matters.
if (reason.includes("Uncontrollable")) return "uncontrollable";
if (reason.includes("Controllable")) return "controllable";
return "needs review";What it was worth
The agency went from having no answer to having a record. If a family or a regulator asks what happened on a specific night, there is now an hour-by-hour account for every caregiver, including the escalations and who was contacted. That capability did not exist before at any price the agency could pay; hourly manual checks would have required a full-time salary, and would have produced a less reliable record than the automated one.
The Monday ritual disappearing is the change the office feels daily. The scorecard now fills itself identically whoever is in the building, which means a KPI no longer stops existing when someone takes leave. And because each metric was validated with the client before joining the scorecard, the numbers are trusted, which matters more than their precision, since an untrusted dashboard is one people stop opening.
What I would hold up as the real lesson is the failure philosophy. Almost every design decision here follows from one rule: in safety-critical work, failing loudly beats failing quietly. A silent zero is treated as a bug. The daily brief refuses to send partially. Required columns are validated before a sheet is cleared. Voicemail does not count as reaching a caregiver. Each of those is a case where the easy implementation produces output that looks fine and is wrong, and in this domain, output that looks fine and is wrong is the worst thing the system can produce.
I should be clear about what is not measured. No time-saved, headcount or cost figure was captured, so I am not going to attach one. What is verified is substantial and specific: the fixes were proven against the exact nights where problems had been reported rather than against synthetic tests, a discharge export was reconciled row for row across 1,993 rows, and every KPI was signed off by the client before it joined the scorecard. That is the honest evidence, and in operations work reconciling against real reported incidents is a stronger claim than a percentage.
Connected automations running live
Almost all reusing one six-step report pattern.
Report feeds read automatically
Turning a closed system into a data feed via scheduled email exports.
Touches before a human is needed
Text, reminder, then two calls with voicemail detection.
Numbers copied by hand
The leadership scorecard fills itself from the feeds.
Rows reconciled row for row
A discharge export no spreadsheet library could parse.
Caregivers a name-only key would have merged
Fixed by keying on name plus date, leaving two harmless undated rows.
Handed over
- An hourly overnight check-in and escalation cycle: text, reply matching on a 15-minute window, reminder, two calls with answering-machine detection, and escalation only when genuinely unreachable.
- Per-caregiver manager reports each morning and a Monday weekly version with a day-by-day chart.
- Caregiver streak texts and a private mobile leaderboard, with nights counting only once graded so an outage cannot reset a streak.
- A weekly KPI scorecard pipeline reading 18 report feeds into raw tabs, with each KPI validated by the client before inclusion.
- Missed-call detection emailing the office as calls end, with a weekly count.
- Billing-length monitoring flagging every visit outside the range expected for its billing code.
- A caregiver performance pipeline collecting and validating five reports, alerting on a missing source and resetting each bonus period.
- Bi-weekly personal bonus emails to every active caregiver with their numbers and whether they earned it.
- An AI daily care brief ordering a day of notes safety-first, which refuses to send a partial report.
- Daily and weekly quality KPIs across four measures that refuse to send a silent zero when a source is missing.
- A daytime text auto-reply answering unwatched messages once per person per day, pointing to the caregiver hotline.
- A monthly six-month no-contact report, discharge cause classification, rehire-versus-starts tracking, and a weekly combined in-person visit summary.
- Office-editable settings tabs, fixed record IDs making every write repeatable, local-time handling throughout, and retained raw data so every number traces to source.
Questions about this build
What teams usually ask when they recognise their own process here.
Because without it the system lies. A call that reaches voicemail returns the same 'call completed' signal as a call a caregiver actually answered, so the check-in closes and the night records as covered. You end up with a clean overnight log for a night nobody verified, which is worse than having no log, because it manufactures assurance a family or regulator might rely on. Only a live human voice closing the check-in is what makes the record mean anything.
By making four things happen before a human is involved: a check-in text, a reminder, then two calls. Only genuine unreachability wakes anyone. This is not a convenience decision, alerts that fire on every missed text get muted; muted alerts are no alerts, and then the one night it matters the escalation goes nowhere. Protecting the credibility of the alarm is part of building it.
Check whether it can email you a report on a schedule, which a surprising number of operational platforms can. That one feature was the entire integration layer here: dozens of scheduled reports now land in a dedicated inbox and get parsed. It is less elegant than an API and considerably more reliable than screen-scraping, and it turned a system everyone described as closed into a daily feed.
Because operational thresholds change and the office should not need an engineer to change them. Check-in windows, reply windows, recipients and ranges live in editable sheet tabs. The deeper reason is that settings buried in code do not get tuned, people work around them instead, and the system drifts away from how the business actually runs.
That a missing input stops the report rather than producing a comforting number. If a source feed does not arrive, the quality KPIs send nothing and raise a log alert instead of publishing a zero, and the daily brief refuses to send partially. Required columns are validated before any sheet is cleared, so a malformed file cannot wipe a tab. In safety-critical reporting the dangerous output is not an error message. It is a figure that looks plausible and is wrong.
Because a system that only ever contacts people when they have failed gets resented, worked around, and eventually undermined. Every morning each caregiver gets their night result and their streak of perfect nights. It costs almost nothing to build and it changes what the system is socially: not surveillance that catches you out, but something that notices when you did the job well. A night only counts once graded, so a platform outage can never unfairly reset someone's streak.
By reconciling against real events rather than synthetic tests. Fixes were proven against the exact nights where problems had been reported, including the case where caregivers who had replied were still marked unreachable, traced to rate-limited bursts where only the newest message was read. A discharge export that no spreadsheet library could parse was verified row for row across 1,993 rows. And every KPI was checked by the client before it was allowed onto the scorecard.
Nobody measured it, so I will not quote a figure. The honest framing is that the overnight record is a capability the agency did not previously have at any affordable price, and the weekly KPI assembly stopped depending on a specific person being in the office. What is verified is the reconciliation work, the client sign-off on each metric, and the fact that fifteen-plus automations run these parts of the business unattended. If I were starting again I would instrument the before-state, because the case would be easier to make with numbers than without.
Capabilities this used
Read next
All ai automation workHave a process that works like this one did?
Book a strategy call. We will walk the workflow with you and say plainly which parts are worth automating and which are not.