Hackathon Friday: One Data Flow Insight That Simplified Our Onboarding Automation

I want to be upfront about something before this post starts: three months ago, I had never touched on-premises Active Directory in my professional life. I know Entra reasonably well, I would call myself intermediate there, but on-prem AD was a blank page for me. The unidirectional sync between on-prem AD and Entra ID? Also a blank page. Identity management in general was just never my corner of the world. And honestly, part of me did not want it to be. I had always heard that the DC was sacrosanct, the thing you do not mess with, the one server that if you get wrong takes everyone's logins with it. The idea of running anything on it, let alone writing to it, was genuinely daunting.

That context matters, because this post is not a "here's how an AD expert came in and saved the day" story. It is more a "here's how understanding data flow let someone with no AD experience challenge a proposal, build something simpler, and stress-test his own decisions afterwards" story. Those are different stories, and I think the second one is more useful.

The Problem and the Proposal

Some background. Ritchies runs a manual onboarding process. When new employees join, someone creates their Active Directory account, sets up their email, places them in the right OU for their depot, assigns group memberships. It works, but it involves emails, tickets, and a few people doing the same data entry across multiple systems. The goal was to automate it.

This was not my area. My primary focus is data and engineering, and this sat firmly in systems territory. I got pulled in at a proposal review stage, just to sense-check the direction.

Two things came out of that review. First, the initial proposal had BambooHR and AD as co-equal systems: a web form sitting outside of BambooHR that could write to both simultaneously. I flagged it to my boss. BambooHR has to be the single source of truth for anything staff-related. If you make a change to a staff member, you make it in BambooHR, and everything downstream reflects it. Having a second entry point creates two places where data can drift apart and nobody knows which one is right. The proposal was reworked: sync BambooHR to AD on a schedule, polling-based, one direction. That is the right shape. I gave my sign-off.

Second thing: the revised proposal came with a Statement of Work.

My boss is a former systems engineer. He looked at that list and his read was immediate: this is a lot of infrastructure for what is essentially a scheduled script. It was not any one line that flagged it, it was the cumulative picture. A Function App. A Logic App. Key Vault integration. An Azure Gateway reference neither of us could confidently place. Each of those resources makes sense in the right context. Together, for a polling sync that runs daily, it looked disproportionate to the problem. He came to me and asked: "Is this really necessary?"

My read was the same. None of that infrastructure was necessary for a daily scheduled sync. My boss was perfectly happy with daily. Honestly, even hourly would have been fine. We are currently running the whole process manually with emails and tickets. Daily is already a massive win over that. The moment I framed it that way, the architecture question answered itself.

And then I said something I probably should not have said out loud, because it immediately became a commitment: "Is this a challenge for me?"

It became a Hackathon Friday.

BambooHR Already Knows Who Changed

Before I wrote a single line of PowerShell, I spent time thinking about the actual data flow problem. Not the infrastructure problem. The data problem. Specifically: how do you know which employees in BambooHR need an AD account created or updated?

Looking at the proposed architecture, the Logic Apps and Function Apps suggested a design mindset that assumed you needed to build the change detection layer yourself. Whether or not that was the explicit intent, it is the infrastructure you reach for when you do not know the source system already handles this. And BambooHR does.

BambooHR has a custom report feature that lets you define exactly which fields to return, apply filters, and call the result via API on a schedule. One of those filters is "changed in the last X days." You can build a report that returns only employees whose records were modified in the last two days, with exactly the fields you care about, and call that report on a schedule.

That is your delta. That is your change detection mechanism. BambooHR built it for you. You just have to call a URL.

Once that clicked, the architecture became much simpler. A scheduled PowerShell script, running on our existing infrastructure, calls the BambooHR report API, gets back the list of recently changed employees, and for each one decides what to do in AD: create the account, update it, or disable it. No Function App. No Logic App. No Azure Gateway configuration. Just a script and a schedule.

The second insight was about how to know which employees actually need an AD account. Not everyone at Ritchies needs one. We hire tens of drivers every week, and of those, maybe one person per week needs a Ritchies email and AD account. Most drivers never interact with systems that require it. So the sync script cannot just create an account for everyone in BambooHR.

BambooHR does not have a native way to flag "this person needs a Microsoft account." There is no built-in field for it, and we did not want to create a custom field that P&C had to remember to fill in. The solution we landed on is a bit of a hack, but a clean one: when a manager submits our onboarding form confirming that a new employee needs an IT account, the form calls the BambooHR API to set the employee's work email to a placeholder value, ritchies new. That is the trigger.

The delta report is filtered to return employees whose email contains "ritchies" and who were modified in the last two days. A brand new employee with ritchies new as their email will appear in that report the next time the sync runs. The script sees them, sees no matching AD account exists, and creates one. On success, it calls the BambooHR API again to replace ritchies new with the actual generated email address, built from the employee's name (e.g. joe.blogg@ritchies.co.nz).

It is using BambooHR's own delta report as a lightweight state machine. No webhooks. No event bus. No separate trigger table in a database. The source system's built-in change tracking does the job.

A Script, a Schedule, and a JSON Mapping File

Here is what the actual solution looks like, compared to what was proposed.

The script runs as three modules. One thing worth saying upfront on the code: around 90% of what ended up in production is built on a PowerShell foundation that was shared with us, and that foundation is solid. What I contributed was the architecture around it: how it gets triggered, where it lives, the logging, and the Power Automate notification layer. An orchestrator that handles the BambooHR pull, loops through employees, and routes each one to either create, update, or disable. A create module that runs validation guards, builds the AD account, and handles the BambooHR callback. An update module that diffs the BambooHR record against the current AD state and applies only what changed.

The depot-to-OU mapping is a flat JSON file that also came with that foundation. Every Ritchies depot maps to an AD organisational unit. When a new employee is created or an existing one moves locations, the script reads this file to place them in the right OU. Adding a new depot means adding one line to the JSON. No code changes. It is a good, simple design and I kept it as-is.

The one thing I want to change eventually is where that file lives. Right now it is a config file sitting alongside the script, which means any depot name change or new location requires someone technical to go edit it. The better long-term home is somewhere HR can update themselves, whether that is a SharePoint list, a BambooHR custom table, or even just a managed spreadsheet that the script reads. The mapping logic stays the same. The source just becomes something the people who know the depot structure can maintain without raising a ticket.

Worth being clear that this is not purely an onboarding tool. The offboarding side matters just as much. When someone leaves, their AD account needs to be disabled, their licenses revoked, access removed. A former employee with an active account and an active Microsoft 365 license is both a security risk and an unnecessary cost. The disable module handles that, and getting it right is if anything more important than the create side.

On notifications: the DC has no mail agent, so I needed a way to get the run report out. The answer was a Power Automate HTTP endpoint. All it does is receive a payload and send an HTML-formatted email. It costs nothing, it took about twenty minutes to set up, and it means every sync run produces a readable summary of what happened, what failed, and why. Cheap and it works. That is the whole design philosophy in one feature.

The create module runs five validation guards before touching AD. Missing employee number, missing name, duplicate SAM account, duplicate email, unknown location: any of these fails the create and sends a notification to the manager explaining exactly why, with enough detail that they can fix it in BambooHR without needing to involve IT. The next time the sync runs, it retries automatically. No manual re-trigger. No IT ticket.

The update module diffs twelve fields between BambooHR and AD. If the diff count is zero, it returns immediately without making any changes. If there are diffs, it applies only what changed: direct fields via Set-ADUser, LDAP-only attributes via -Replace (the main gotcha being that -Replace on a multi-valued attribute overwrites the whole set, not just one value, so attribute scoping matters here), and if the display name changed it also renames the CN. Location changes trigger an OU move. Manager changes trigger a manager lookup. The update module is idempotent: run it twice on the same employee with no BambooHR changes and it does nothing twice.

Proof of Concept to Production, Carefully

This is where I want to push back on one romanticised version of the "Hackathon Friday" story. The Friday produced a working proof of concept. It did not produce something I was comfortable pointing at a production domain controller the following Monday.

We do not have a test AD environment. These are real people's accounts. A bug that creates the wrong account, sets the wrong OU, or mishandles a name conflict does not produce a test failure: it produces a problem for a real employee on their first week. So I took a staged approach that I think is worth documenting, because it applies to any hackathon project that needs to graduate to production.

The PowerShell foundation we were working from included a WhatIf mode flag, and I kept it. When I spoke to the people who wrote it to close the loop on the project, I made sure to say that their script work was genuinely good and that it made the whole thing possible. I meant it.

The rollout went like this. Week one: everything in WhatIf mode. The script runs on schedule, logs everything it would do, sends reports, but makes zero changes to AD. I reviewed the logs for a full week. Were the right employees being picked up? Were the guards triggering correctly? Were the SAM account names being generated as expected? Were the OUs resolving correctly from the mapping file? Once I was satisfied the logic was sound, I started flipping things on, one module at a time.

Updates came on first. Existing AD users getting their fields synced from BambooHR. Low risk: if something goes wrong, no accounts are lost, just a field has a wrong value that can be corrected. I ran updates live for a week and watched the diff reports.

Creates came on next. New accounts being provisioned. I watched the first few manually, checking the created accounts in AD directly after each sync run. Names, OUs, group memberships, the BambooHR callback replacing ritchies new with the actual email. Once I was confident, I stopped watching every one.

Disables came last, and for good reason. Disabling an account has immediate downstream effects: the person cannot log in, their laptop locks, their email stops working. Getting it wrong on creates or updates is fixable. Getting it wrong on disables is someone's Monday morning.

On Learning On-Prem AD From Scratch

I said at the start that I had never touched on-prem AD before this. I want to say a bit more about what that meant in practice, because I think there is a tendency in posts like this to skip over the learning curve and make it sound like the solution just appeared.

The AD PowerShell module is genuinely not that hard once you understand what you are doing. The concepts are straightforward: users, groups, OUs, attributes, some of which you can set through named parameters in New-ADUser and Set-ADUser, and some of which are LDAP-only and require -OtherAttributes or -Replace. The tricky bits are knowing which attributes fall into which category, understanding how the distinguished name works for OU placement and CN renaming, and getting the unidirectional sync between on-prem AD and Entra to behave predictably.

AI helped me a lot here, and I am not going to pretend otherwise. I used it to understand the AD module, to debug PowerShell I had not written before, to understand why a particular -Replace call behaved differently to what I expected, and to think through the right way to structure the guard logic. The existing PowerShell we were working from also taught me a lot just by existing: reading code written by people who know a domain is one of the faster ways to get up to speed on that domain.

What surprised me most was that touching a production domain controller is not as frightening as I had imagined from the outside. The WhatIf mode helped with that. But more than that, once you understand what the AD PowerShell cmdlets actually do, they are largely predictable. New-ADUser with -Enabled $false creates a disabled account. Move-ADObject moves the object to the OU you specify. The surface area you are touching is the surface area you said you would touch, with the multi-valued attribute caveat on -Replace noted earlier being the main place that bites people.

On the Architecture Gap

The proposal was not wrong in principle. It was proportionate to a different problem than the one we actually had.

When you are designing scheduled cloud automation on the Microsoft stack, you naturally reach for Function Apps and Logic Apps. That is the pattern. What was not surfaced in the design was that BambooHR already exposes a delta report that eliminates the need for a bespoke change detection layer entirely. Once you see it, the architecture becomes much simpler. But you have to look for it first, and you only look if you start from the data rather than from the infrastructure. It is also worth being precise about what the proposal was: not an event-driven architecture. The original cut was a trigger-driven web form. The revised cut was a scheduled polling sync. The complexity came from infrastructure defaults for scheduled cloud automation, not from trying to solve a harder problem than we had.

The lesson I take from it is: before you design the plumbing, ask whether the source system already moves the water. Every platform you integrate with has a model for how it exposes data. Understanding that model before you start architecting usually reveals that you need to build less than you thought. And once you have made the decision about what to build, it should flow from your actual requirements, not from what the infrastructure pattern suggests you might need.

The other thing I keep thinking about is the source of truth question, because that is where this whole thing almost went sideways early. The initial proposal had a web form that could write to both AD and BambooHR simultaneously. Two systems both accepting writes for the same entity. You will eventually get them out of sync, and then you have to answer a question that gets harder over time: which one is right? The answer was always going to be BambooHR. That had to be settled before the technical design could go anywhere useful.

Picking your source of truth is not a technical decision. It is an organisational one. But it has to be made before the technical design starts, not after. And it has to be the thing that shapes the architecture, not something you figure out later when the integration is already half-built.

The Honest Gaps

As I thought through this more carefully after building it, and as people pointed things out along the way, a few things came up worth being honest about. Some of them I have addressed. Some are still on the list.

The first one that came up: running scheduled jobs on a domain controller is frowned upon in enterprise systems engineering. I took that feedback seriously. But "it's frowned upon" is not a reason on its own, and I wanted to understand exactly what the risk was before deciding what to do about it.

So I kept asking. Not because I'm a DC security expert, I'm clearly not, but because I find that if you ask "but why exactly?" enough times you eventually get to something concrete you can actually reason about. What I landed on, and what others confirmed when I checked my thinking, is that the standard guidance is mostly an availability and Tier-0 hygiene rule rather than a security rule, at least for a build like this.

The security concerns I kept getting pointed at, when I traced them through, did not seem to apply here. Let me be clear: a Domain Controller compromise is a catastrophic event. If an attacker gets Domain Admin, they have the keys to the kingdom. But this scheduled task does not materially change that risk surface. The DC is admin-only access. Anyone who could tamper with the scheduled task or its script file is already inside that admin boundary by definition, so the task does not hand them anything they did not already have. The AD write capability lives in the service account's security context, not in a credential sitting in a file somewhere. The script only loads modules from default admin-only system paths. The one thing in an environment variable is the BambooHR API key, which is read-only on one endpoint: worst case if it leaked, someone reads recently-changed employee records from one report. I went through each risk one by one and either found a control already in place or concluded it did not apply to this specific setup. I could be wrong on something, but I at least understand what I am accepting.

The real reason to prefer a member server, as far as I can tell, is availability. A DC is a Tier-0 asset authenticating the whole company. A misbehaving process on it, a runaway loop, a hung outbound HTTP call, logs filling the disk, can degrade authentication org-wide. The same bug on a member server is just an app outage. For a script that runs once daily and exits cleanly, that risk felt low. We weighed it, accepted it, and will revisit if the script grows. A deliberate tradeoff, not an overlooked gap.

The second gap worth naming: the whole design rests on a two-day delta window. If the scheduled task is down for longer than two days, those changes age out of the BambooHR report and are never picked up. There is no periodic full reconciliation pass to catch the drift. That is the one I would fix first if I were hardening this further.

We do have a partial mitigation for this that is worth mentioning. We load BambooHR data into our data warehouse for reporting purposes. One of those reports surfaces any employee who still has ritchies new as their work email. If the automation has missed someone, they show up there. It is not a code-level fix, it is a reporting safety net, but it means the failure mode is visible rather than silent. We understand the risk, we have accepted it, and we have a way to catch it when it happens.

One thing worth clarifying on the writeback scenario: if the AD account is created successfully but the BambooHR email callback fails, the next sync run matches on employeeNumber as the primary key, finds the existing AD account, and routes to the update module rather than the create module. The update module diffs twelve fields including email. If BambooHR still holds ritchies new and AD already has the real email, the diff detects a mismatch and would attempt to overwrite. The email field is handled specifically to avoid writing a placeholder value back into AD in this state. Either way, the run report captures it and it lands in the email notification. It is a visible failure that someone can action, not silent drift.

Running in Production, One Module at a Time

Updates, creates, and disables are all running in production. The sync runs on schedule, picks up BambooHR changes, provisions new accounts, keeps existing ones in sync with HR data, and disables accounts when someone's termination date passes. Managers get notifications when something fails, with enough context to fix it themselves. The self-healing retry logic means that most failures resolve automatically on the next sync run without IT involvement.

The total incremental cloud cost of this solution is essentially zero: it runs on infrastructure we already have. The script is a few hundred lines of PowerShell. The mapping file is a JSON document. The schedule is a Windows Task Scheduler entry. If the whole thing needed to be handed to someone else tomorrow, the handover document would be short.

What is still on the list: proper secret management, a heartbeat so we know if the scheduled task silently stops running, and a periodic full reconciliation pass as a backstop for the delta window. None of that is a reason not to ship the initial version. It is just an honest accounting of what "production" means for a Hackathon Friday project versus what it means six months later.

That, in the end, is what I keep coming back to. Not the cleverness of the ritchies new hack, or the guard logic, or the self-healing retries. Just the fact that the right solution here was a proportionate one. And the reason it could be proportionate was that we understood how the data already moved, before we started designing anything.