Tracking expiring contracts and certificates

Tracking expiring contracts and certificates is a recurring HR compliance task owned jointly by the HR Manager and HR Assistant. Several times a week, someone on the team manually opens a spreadsheet, scans for upcoming expiry dates across employment contracts, professional certifications, and medical checks, and then decides whether to send an alert and to whom. The process runs roughly 900 times a year, each instance taking around eight minutes on average, which adds up to approximately 120 hours of HR time annually. The current system depends entirely on a person remembering to check the spreadsheet, which means deadlines can and do get missed.

The process involves a handful of genuine decision points beyond simple date comparison. The team must verify whether dates were entered correctly, determine whether a case is straightforward or requires human judgment, choose the right person and channel for each alert, and identify whether a case might require works council consultation under German labour law. Roughly a quarter of cases fall outside the standard pattern and need some degree of human review before action is taken. The primary data source is a manual spreadsheet; the organisation also has an HR SaaS platform, but the API access needed to connect it programmatically sits behind a higher subscription tier they are not currently paying for.

The goal of any improvement here is simple: the HR team should be alerted automatically and in advance whenever a contract, certification, or medical check is approaching its expiry date, so that the right people can act before deadlines pass. The process is well-defined enough that the standard path requires no human involvement at all, while the non-standard quarter can be routed cleanly to a human reviewer rather than falling silently through the cracks as it does today.

A scheduled Traditional Code script is the right solution for this process, and the reasoning is straightforward. The core logic is pure date arithmetic: read a record, calculate how many days remain until expiry, compare that number against a set of configurable thresholds, and send an email alert to the appropriate HR contact. There is no ambiguity to resolve, no language to interpret, no unstructured input to classify. A deterministic script handles all of that reliably, cheaply, and in a way that is easy to audit, which matters when employee data and legal deadlines are involved.

The client explicitly noted that a script beats AI on both cost and reliability for this process, and the assessment confirms that view entirely. Introducing an AI Workflow or AI Agent would add infrastructure cost, latency, and compliance surface area under the EU AI Act without delivering a single capability that the process actually needs. The process does not require the AI to understand anything; it requires a computer to compare two dates and send an email. Using AI here would be the automation equivalent of hiring a surgeon to change a lightbulb.

The client has no strong technology stack preference, which is the right instinct given the constraints. A Python script is the practical recommendation because it is readable, widely understood, easy to maintain by a single IT resource who also handles support, and requires no new infrastructure beyond a scheduler such as a cron job or a simple cloud function. The script reads from the existing spreadsheet, calculates days to expiry, and dispatches email alerts at configurable intervals, for example at 60, 30, and 7 days before expiry. A simple log of alerts sent provides an audit trail that satisfies GDPR accountability requirements without any additional tooling.

The Hybrid path scored close behind Traditional Code, and in practice the two are not mutually exclusive. The recommended script should include a routing step for the roughly 25 percent of non-standard cases, flagging them for human review rather than attempting to act on them automatically. This keeps the human in the loop precisely where works council consultation obligations and GDPR-sensitive decisions require it, and it formalises what is currently an informal and inconsistent judgment call. The distinction between the two scored paths is really about emphasis: Traditional Code is the engine, and the hybrid gate is a feature of its output design, not a separate architecture.

RPA was considered and rejected cleanly. RPA earns its place when data is locked inside a user interface with no other access route, but the spreadsheet here is directly readable and the HR SaaS has an API that could be unlocked with a subscription upgrade. Wrapping a spreadsheet in an RPA bot would add fragility and licensing cost while solving none of the underlying data quality problems. Staying Manual is not a serious option: the current process demonstrably misses deadlines, and 120 hours of annual HR time is a quantifiable cost that a one-off script build of between 1,500 and 3,500 GBP recoups within roughly nine months.

Traditional Code is the clear winner here, and the reasoning is straightforward. The entire standard path of this process is date comparison and email alerting: structured inputs, deterministic logic, no ambiguity to resolve. A scheduled Python script reads those fields, calculates days remaining, and fires the right alert to the right person. There is nothing in that sequence that requires intelligence, and adding any would introduce cost and complexity without improving the outcome. The client said as much during discovery, and the assessment confirms it.

The Hybrid path scores close behind at eight, and in practice the recommended implementation already incorporates the hybrid principle. The script handles the roughly 75 percent of standard cases automatically, while non-standard cases and anything touching works council consultation are routed to a human review step. The difference is that scoring Hybrid separately draws attention to the importance of formalising that exception gate, rather than treating it as an afterthought. The script needs to know which cases it cannot act on autonomously, and that logic needs to be documented and maintained.

AI Workflow and RPA both score four, and neither is appropriate here. AI Workflow adds language model overhead to a process that has no natural language to interpret and no ambiguous classification to perform. The EU AI Act compliance burden that would come with any scoring or classification component is an additional reason to stay clear of it. RPA would only make sense if the data were locked inside a UI with no export path, but the spreadsheet is directly readable and the HR SaaS has an API waiting if the subscription is upgraded. RPA would add licensing cost and screen-scraping fragility to a problem that a few dozen lines of Python can solve cleanly.

The first thing to build is not the script: it is a trustworthy data source. The existing spreadsheet needs a full audit before any automation reads from it. The HR manager should walk through every record with the HR assistant, confirming that expiry dates are in a consistent format, that no entries are missing or duplicated, and that each row has a clearly identified responsible contact. The canonical fields the script will depend on should be agreed and locked at this stage: employee name, record type such as employment contract or medical check or certification, expiry date, and the email address of the HR contact who needs to receive the alert. Without this audit, the script will faithfully automate bad data, which is worse than the current manual process because mistakes will arrive silently rather than being caught during a human review.

Once the data is clean, the script itself is relatively short to build. Python is the right choice here because it is readable, well-documented, and something a generalist IT resource can maintain without specialist knowledge. The script runs on a schedule, daily is sensible, reads the spreadsheet using a library such as openpyxl or pandas, calculates the number of days between today and each expiry date, and sends email alerts via SMTP or a service like SendGrid at configurable thresholds. Three thresholds make practical sense: 60 days for early awareness, 30 days for action planning, and 7 days as a final warning. Every alert sent should be written to a simple log file or a separate sheet in the workbook so there is an auditable record of what was triggered and when. The script should also flag any row where the expiry date is missing or unparseable, sending a data quality alert to the HR manager rather than silently skipping the record.

The exception routing step deserves its own design conversation with the HR manager before the script goes live. The agreed list of case types that require human review before any action is taken should be encoded in the script as a configuration list, not buried in the logic. When the script encounters one of those case types, it does not send a standard alert: it sends a separate flagged notification to the HR manager indicating that a record needs review. This keeps the automation honest and ensures that works council-sensitive cases and non-standard contract types are never handled without a human decision. This configuration list should be stored somewhere the IT resource can update it without touching the script code, a simple CSV or a named range in the spreadsheet works well enough.

The final step before decommissioning the manual check is a four-week parallel run. The script operates live while the HR assistant continues the existing manual review, not to do the work twice, but to spot discrepancies. Any case the script missed or alerted on incorrectly should be logged and fed back into the script configuration or the data quality rules. After four weeks, if the error rate is low and the HR team is confident in the output, the manual check stops. The IT resource takes ownership of the script with documentation covering how to adjust thresholds, how to add new record types, and how to restart it if the scheduler fails. A review point at three months is worth scheduling to revisit whether upgrading the HR SaaS subscription for API access makes sense, since direct API reads would eliminate the spreadsheet synchronisation risk and give the automation access to real-time data.