Syncing the Org Chart at Midnight

Every table in the estate from part 1 ultimately hangs off one question: who works here, and who do they report to? The answer lives in the HR system, not in the attendance platform — so the first executable in the hinterland is a mirror. Once a day, at midnight UTC, an Azure Function pulls the entire employee master over HTTP and upserts it into the database everyone else reads. If that mirror is stale, shifts get inferred for people who left, and delegations point at managers who no longer manage. This part is the anatomy of that sync — including the parts of it that should never have shipped.

The bootstrap all three functions share

The employee sync set the pattern that the leave sync and the notifier copied wholesale, so it is worth reading once, carefully:

public static class EmployeeSyncJob
{
    private static DbContextOptions<AttendanceContext> _options;

    [FunctionName("EmployeeSync")]
    public static void Run([TimerTrigger("0 0 0 * * *")] TimerInfo timer,
        ILogger log, ExecutionContext context)
    {
        var env = Environment.GetEnvironmentVariable("Environment");
        Directory.SetCurrentDirectory(context.FunctionAppDirectory);

        var config = new ConfigurationBuilder()
            .AddJsonFile($"appsettings.{env}.json")
            .Build();

        var connectionString = VaultClient.GetConnectionString(config);
        _options = new DbContextOptionsBuilder<AttendanceContext>()
            .UseSqlServer(connectionString)
            .Options;

        SyncEmployeesAsync(config).Wait();
    }
}

Read it slowly, because every line teaches something. A static class with static mutable state — the options builder result lands in a field shared across invocations, harmless only because the timer never overlaps itself. Directory.SetCurrentDirectory mutates process-wide state to make relative config paths resolve — in a Functions host that may be running other things. Configuration is loaded per invocation from appsettings.{environment}.json, with the environment name itself coming from an app setting. The connection string comes from Key Vault via DefaultAzureCredential with an exponential retry — genuinely the most modern code in the file. And then the whole async pipeline is driven with .Wait(), because nobody wired up FunctionsStartup, dependency injection, or async entry points. It is 2019-era Functions code written the way console apps were written in 2015.

None of this is the interesting part. It is the water the interesting parts swim in.

A handshake nobody listens to

Before fetching employees, the function performs a small ritual:

using var client = new HttpClient
{
    BaseAddress = new Uri("http://localhost:5001/")   // never used
};
client.DefaultRequestHeaders.Authorization =
    new AuthenticationHeaderValue("Basic", credentials);

// call one: response discarded
await client.GetAsync(config["HrSystem:HandshakeUrl"]);

// call two: token extracted, then never read again
var tokenResponse = await client.GetAsync(config["HrSystem:TokenUrl"]);
var token = ParseAuthToken(tokenResponse);   // dead value

// call three: the one that matters
var employees = await client.GetFromJsonAsync<List<HrEmployee>>(
    config["HrSystem:EmployeeMasterUrl"]);

The BaseAddress points at a developer's localhost port from years ago — harmless only because every configured endpoint is an absolute URL that overrides it. The first call's response is discarded entirely. The second call extracts an auth token that no subsequent request carries; Basic auth on the default headers does all the real work. Three HTTP calls, one of which is load-bearing.

This is what a vestigial organ looks like in code. At some point the HR system's API presumably required a session handshake and a token; the contract changed, Basic auth became sufficient, and the calls were never deleted because they didn't break anything. The cost is not performance — two wasted requests a day is nothing. The cost is that every future reader must reverse-engineer which parts of the client are alive, and the only way to find out is to delete things in production. Dead code in an integration client is a standing tax on comprehension.

Upsert, field by field

The sync is a full-master mirror, not a delta feed: the HR system returns everyone, and the function decides row by row whether to insert or update.

foreach (var api in employees)
{
    var existing = db.Employees
        .FirstOrDefault(e => e.EmployeeId == api.EmployeeId);

    if (existing is null)
    {
        db.Employees.Add(MapNewEmployee(api));   // TryParse everywhere
        continue;
    }

    if (HasChanged(existing, api))               // Parse everywhere
        UpdateFields(existing, api);
}
db.SaveChanges();

New customer names in the feed become new Customer rows. Active employees with no shift mapping get a default EmployeeShiftMapping row, so the shift calculator downstream never has to handle the missing case. Change detection is a literal field-by-field comparison — date of birth, contact number, department, manager, location — and an update touches only what differs. It is unglamorous and it works; a full mirror plus an idempotent upsert is a perfectly respectable way to consume a master data feed when you cannot get change events.

The re-org clause is the best code in the file

One rule elevates this sync above a dumb mirror. The platform lets managers delegate approval rights — a Delegate table maps an employee to a supervisor for a date range. Delegations are granted under one org structure, and a re-org silently invalidates them. The sync knows this:

if (existing.ManagerId != api.ManagerId)
{
    var delegations = db.Delegates.Where(d =>
        d.IsActive &&
        (d.EmployeeId == existing.EmployeeId ||
         d.SupervisorId == existing.EmployeeId));

    foreach (var d in delegations)
        d.IsActive = false;      // terminated by the re-org, not by a human

    existing.ManagerId = api.ManagerId;
}

When your manager changes, every active delegation you granted or hold is terminated automatically. Nobody has to remember that the person approving your attendance was authorised by a manager who no longer exists in your chain. This is exactly the kind of consistency rule that belongs next to the data change that triggers it, and putting it in the sync — the single place manager changes enter the system — was the right call. If I kept one design decision from this function, it is this one.

The honest ledger

Now the defects, in ascending severity.

1. The insert path and the update path disagree about trust. MapNewEmployee uses DateTime.TryParse and long.TryParse and shrugs off bad data. HasChanged, on the update path, calls DateTime.Parse(api.DateOfBirth) and long.Parse(api.ContactNumber) raw. One malformed date of birth on an existing employee and the comparison throws. The same feed, two standards of paranoia — classic copy-evolution, where the second author never saw the first author's defences.
2. An un-included navigation property. The change detector compares existing.Customer.CustomerName — but the query that loaded existing never included Customer, and this project has no lazy-loading proxies. For any employee that actually has a customer, that line is a NullReferenceException waiting on data shape. Nearby, customers.FirstOrDefault(c => c.CustomerName == api.Customer).Id will do the same the first time a name lookup misses.
3. One try/catch around the whole run, and no retries anywhere. Any of the above throws, and control lands in a single handler that writes one StatusSheet row — job name, "Failure", and e.Message + "|Stack|" + e.StackTrace — and returns cleanly. The function succeeds, as far as the Functions runtime can tell. No exception reaches the host, so nothing re-executes; no retry policy exists on the HTTP calls either. A failed midnight sync does not retry in five minutes — it waits until tomorrow midnight. For an org mirror feeding a payroll-adjacent system, a 24-hour repair window on a transient network blip is a genuinely bad trade, and it was bought for the price of one catch block.

The ILogger the runtime hands in? Never passed to the worker methods. The database is the only error channel — which is very on-brand for this estate, and exactly why the next part matters: that StatusSheet table the errors fall into is also the leave sync's watermark, its metrics store and its run history. Next, a watermark made of your own success log.