Matthew Pavey | Faith, Family, Code

I run scheduled jobs on several systems that clean out data past its retention window. Anything older than ninety days, or a year, depending on the system, gets removed overnight. On the quiet tables that is nothing to think about. On the busy ones, the logging and event tables especially, "older than ninety days" can mean millions of rows, and that is where a delete stops being a one liner and starts being something you have to plan.

The obvious version is a single statement:

DELETE FROM dbo.EventLog
WHERE CreatedUtc < @Cutoff;

It runs fine in development against a few thousand rows. Against a few million in production, it can take the whole table down with it.

The reason is that this is one transaction. SQL Server acquires locks as it works through the rows, and once a single statement holds about five thousand locks on one table, it escalates to a lock on the entire table. Every other query that needs those rows now waits behind your cleanup. The transaction log grows the whole time, because nothing the delete touches can be cleared from the log until the statement commits; delete ten million rows and the log has to carry all ten million rows of change before it can release any of it. And if anything interrupts the statement partway through, SQL Server rolls the entire thing back, which costs about as much as the delete did in the first place.

The fix is to stop doing it all at once. Delete in small batches, let each batch commit on its own, and loop until nothing is left:

DECLARE @BatchSize INT = 4500;
DECLARE @Cutoff DATETIME2 = DATEADD(DAY, -90, SYSUTCDATETIME());
DECLARE @Rows INT = 1;

WHILE @Rows > 0
BEGIN
    DELETE TOP (@BatchSize)
    FROM dbo.EventLog
    WHERE CreatedUtc < @Cutoff;

    SET @Rows = @@ROWCOUNT;

    WAITFOR DELAY '00:00:01';
END

A batch of a few thousand keeps each statement under that five thousand lock threshold, so the table stays available to everyone else in the gaps between batches. @@ROWCOUNT tells the loop when the last batch came back empty, which is how it knows to stop. The one second WAITFOR is deliberate: it gives the disk and the log a moment to catch up, and on a busy system it leaves room for other queries instead of pounding the table in a tight loop.

One temptation to resist: do not wrap the whole loop in a single BEGIN TRANSACTION and COMMIT to make it tidy. That folds every batch back into one enormous transaction, and you have written the slow version with extra steps. Each DELETE statement is already atomic by itself. Let it commit by itself.

There is one more piece that trips people up, and it is the reason a batched delete sometimes still fills the log. Under the simple recovery model, SQL Server reclaims the log space after each batch checkpoints, so the log stays roughly flat no matter how many rows you remove. Under the full recovery model, that space is not reclaimed until a log backup runs; however, that means your log backup frequency, not your batch size, becomes the real limit. If your log backups run hourly and your delete runs for two hours, the log holds two hours of deletions no matter how neatly you batched them. On the systems where I run the big cleanups, I make sure the batch cadence and the log backup schedule actually agree with each other.

None of this helps if the column in your WHERE clause is not indexed. Each pass through the loop has to find its next few thousand rows to delete, and without an index on the filter column it scans the table to do that, every single time. An index on the date column turns each of those scans into a quick seek:

CREATE NONCLUSTERED INDEX IX_EventLog_CreatedUtc
    ON dbo.EventLog (CreatedUtc);

For a job that runs unattended at two in the morning, being well behaved counts for more than being fast. A batched delete does take longer on the clock than one big statement, and that is the trade you are making on purpose: it gives up some speed so the rest of the server keeps serving while the cleanup works. If you have a nightly job that clears more than a few thousand rows at a time, it is worth rewriting the delete before the table it cleans grows into the one that wakes somebody up.

A single sign-on application I work on used to calculate Active Directory password expiry by taking pwdLastSet, adding the domain's maxPwdAge, and using the result. That worked until the organization introduced fine-grained password policies, which allow different accounts to follow different expiry rules within the same domain.

Some accounts began receiving warnings for expiry dates that did not apply to them. The calculation was correct for the default policy, but not necessarily for the account being checked.

The fix is to stop calculating the date. Active Directory exposes msDS-UserPasswordExpiryTimeComputed, a constructed attribute that the domain controller calculates when it reads the account. It applies the password policy that actually governs that account, including fine-grained policies.

Application code still needs to request the attribute explicitly. A missing constructed attribute does not necessarily raise an exception, so the search can appear to work while quietly returning no expiry value.

$auth = [System.DirectoryServices.AuthenticationTypes]::Secure -bor
        [System.DirectoryServices.AuthenticationTypes]::SecureSocketsLayer

$searchRoot = New-Object System.DirectoryServices.DirectoryEntry("LDAP://dc.example.com:636/DC=example,DC=com")
$searchRoot.AuthenticationType = $auth

$searcher = New-Object System.DirectoryServices.DirectorySearcher($searchRoot)
$searcher.Filter = "(&(objectCategory=person)(objectClass=user)(sAMAccountName=jdoe))"

[void]$searcher.PropertiesToLoad.Add("msDS-UserPasswordExpiryTimeComputed")

$result = $searcher.FindOne()
$raw = [Int64]$result.Properties["msds-userpasswordexpirytimecomputed"][0]

if ($raw -eq [Int64]::MaxValue) { "Password never expires" }
elseif ($raw -eq 0)             { "Must change at next logon" }
else                            { [DateTime]::FromFileTime($raw) }

There are three values worth handling before converting the result to a date. Int64.MaxValue means the password never expires. A value of zero means the user must change the password at the next logon. Any other value is a Windows file time and can be converted with DateTime.FromFileTime.

I compared both calculations against the live directory instead of trusting two test accounts. The comparison covered 4,230 accounts across three domains. The old and new results agreed for 4,041 accounts. Of the 121 accounts governed by fine-grained policies, 45 had been assigned a date that was wrong by 305 days. Another 123 accounts were off by exactly one hour across daylight saving transitions, because the old code used local calendar arithmetic while the domain controller calculated the value from UTC file-time data.

One thing to remember. Reading the attribute through a DirectorySearcher returns it as a System.Int64, which is why the cast above works. Reading the same attribute through a DirectoryEntry property returns it as an IADsLargeInteger COM object instead, with separate high and low parts, so that path has to convert the value before comparing it. The reader below goes through a ConvertLargeInteger helper for exactly that reason.

Private Function ReadComputedExpiry(entry As DirectoryEntry) As DateTime?

    Const AttributeName As String = "msDS-UserPasswordExpiryTimeComputed"

    If Not entry.Properties.Contains(AttributeName) Then
        Return Nothing
    End If

    Dim raw As Long = ConvertLargeInteger(entry.Properties(AttributeName).Value)

    If raw = Int64.MaxValue Then
        Return DateTime.MaxValue    ' password never expires
    End If

    If raw = 0 Then
        Return DateTime.MinValue    ' must change at next logon
    End If

    Return DateTime.FromFileTime(raw)
End Function

The order matters. Check the special values before converting them. Trying to turn the never-expires value into a date throws an exception.

If the constructed attribute is absent, the application should treat that as a directory-read problem rather than as "never expires." A fallback may still be necessary on an authentication path, but it should be visible in logs or metrics so a missing attribute does not look like a valid result.

Dim computed As DateTime? = ReadComputedExpiry(entry)

If computed.HasValue Then
    Return computed.Value
End If

If Not lastSet.Equals(DateTime.MinValue) Then
    Return lastSet.AddDays(GetDefaultMaxAgeDays())
End If

Return DateTime.MaxValue

The fallback preserves the old behavior when Active Directory cannot provide the computed value. It is no longer the normal path, though, and the application no longer has to guess which password policy applies to each account.

If your code adds days to pwdLastSet, replace that calculation with a read of msDS-UserPasswordExpiryTimeComputed. Ask the domain controller for the answer it already knows.

For most of history, everything known about Pontius Pilate came from words on a page. The Gospels put him at the center of the most consequential trial ever held. Josephus and Philo mention him. Tacitus gives him a single line, noting that "Christus" was executed under him. Real sources, all of them, but all of them written accounts passed down through later copies. A determined skeptic could shrug and say Pilate was a literary device, a name the Gospel writers needed to move the story along. There was nothing you could put your hand on.

That changed in the summer of 1961.

An Italian team was digging at Caesarea Maritima, the coastal city Herod the Great built and the Romans used as their administrative capital in Judea. They were working through the ruins of the old theater when they turned over a block of limestone that had been reused, centuries after it was carved, as a step in a staircase. Someone had taken a Roman dedication stone and used it as building material. On its worn face were four lines of Latin, including the words "Pontius Pilatus" and the title that placed him: prefect of Judea.

That is not a manuscript about Pilate. It is not a later historian's recollection of him. It is a stone cut during his own administration, naming him and giving him the same office the Gospels do. The inscription is the first archaeological evidence we have that directly identifies the man behind the name.

Careful scholars had never quite said Pilate was invented. They said his existence lacked direct corroboration. The literary sources were real, but they had nothing solid underneath them. The Caesarea stone supplied that missing piece. One writer on the subject put it plainly: the fact that Pilate governed during Jesus' time "need not be doubted."

The inscription records Pilate dedicating a building, a Tiberieum, in honor of the emperor Tiberius. It was a bureaucrat's monument to his boss, political flattery carved into limestone. Pilate was not thinking about future Christians or future critics. He was doing his job, keeping the emperor pleased and putting his name on the work. The stone's value to later readers was accidental. Nobody made it for us.

The stone does not convert anyone, and it is not meant to. Faith does not stand or fall on a piece of limestone in the Israel Museum. It does remind us, though, that the Gospels are set in a real province, under real governors, within a chain of history you can uncover with a trowel. The writers were talking about people and places that existed outside the page.

He wanted his name fixed in stone. Within a few centuries, the monument had been broken up and used as a stair, walked over by people who had no idea whose name lay beneath their feet.

Pilate secured his place in the Gospels by asking a question he did not wait to hear answered: what is truth? He thought he was the judge that morning, deciding what to do with the man in front of him. Christians have always believed it was the other way around.

I recently built an integration for a client that keeps HubSpot contacts in sync with an internal user list. A console job runs every five minutes, compares the two sides, and pushes changes to HubSpot through their API. The job itself lives in a long-serving .NET Framework application; the samples below use current .NET for clarity, but the pattern is identical. Simple enough on the surface, and one easy trap shows up in the first version of a job like this. It looks like this:

var contact = await FindContactByEmailAsync(user.Email);

if (contact is null)
{
    await CreateContactAsync(user);
}
else
{
    await UpdateContactAsync(contact.Id, user);
}

Find the contact by email. Create it if missing, update it if found. Reasonable, readable, and the happy-path tests pass. Then someone gets married, or the company changes domains, and a user's email address changes. Now the search for the new address finds nothing, so the job creates a brand new contact. The old contact is still sitting in HubSpot under the old address, marked active, holding all the history. You have a duplicate and an orphan, and the job that caused it reports success.

The root mistake is treating the email address as the identity. It is not. It is a property of the identity, and properties change. HubSpot already knows this, which is why every contact has a stable record id. That id is what your system should remember. The first time you create or find a contact, store the HubSpot id in your own database next to the user. From then on, the normal sync path does not search at all. It goes straight to the record:

if (user.HubSpotContactId is null)
{
    user.HubSpotContactId =
        await ResolveContactIdByEmailAsync(http, user.Email)
        ?? await CreateContactAsync(http, user);

    // persist user.HubSpotContactId to your database here
}

await http.PatchAsJsonAsync(
    $"/crm/v3/objects/contacts/{user.HubSpotContactId}",
    new
    {
        properties = new
        {
            email = user.Email,
            firstname = user.FirstName,
            lastname = user.LastName
        }
    });

Notice that a rename is no longer a special case. The email address is just one more property in the PATCH, updated in place on the same contact, with every association and every piece of history intact. When I proved this out against the live portal, the update stayed on the existing contact. The old address no longer identified that contact, and nothing new was left behind. Search by email happens once in a contact's life, on first sight, and here is that piece:

private static async Task<string?> ResolveContactIdByEmailAsync(
    HttpClient http, string email)
{
    var body = new
    {
        filterGroups = new[]
        {
            new
            {
                filters = new[]
                {
                    new { propertyName = "email", @operator = "EQ", value = email }
                }
            }
        },
        limit = 1
    };

    var response = await http.PostAsJsonAsync(
        "/crm/v3/objects/contacts/search", body);
    response.EnsureSuccessStatusCode();

    var result = await response.Content
        .ReadFromJsonAsync<ContactSearchResult>();

    return result?.Results?.FirstOrDefault()?.Id;
}

The stored id is not a substitute for error handling. If the contact was deleted or merged in HubSpot, the update can come back not found, and that is a signal to stop and recover deliberately. An automatic create at that moment is how you manufacture the exact duplicate this pattern exists to prevent. Likewise, if the new email already belongs to a different contact, the sync should stop and report the conflict for review rather than guess.

One more rule from the same project: for this integration, the sync never deletes a contact. When a user is deactivated on our side, the job flips a status property in HubSpot and walks away. Part of the reason is practical: deletion in a CRM destroys history, breaks associations, and cannot be taken back by a job that runs unattended at three in the morning. And part of it is just good manners: those contact records belong to the marketing team, and a background job has no business destroying another team's data. Deactivation is reversible, auditable, and honest about what actually happened, which is that a person became inactive, not that they never existed. Any legal deletion or retention requirement belongs in its own deliberate workflow, decided by people rather than a timer.

None of this is specific to HubSpot. Salesforce, Dynamics, Mailchimp, any system your code talks to: if the remote side has a stable id, store it, and treat every human-readable field as changeable, because sooner or later all of them are. The integrations that age well are the ones that remember who someone is, not just what they were called.

V for Vendetta sits at the very top of my movie list, and if you have seen it, you can probably guess the scene that put it there. Evey is locked in a cell, head shaved, being broken one interrogation at a time. Then a letter reaches her, written on toilet paper by a prisoner named Valerie. The letter comes from the graphic novel by Alan Moore and David Lloyd; the film adapts and condenses it, then gives it a voice that has stayed with me for years.

Valerie was an actress. She fell in love with Ruth, and for three years their London flat smelled of roses. As she remembers it:

For three years I had roses, and apologised to no one.

Then America's war grew worse and eventually came to London. After that, there were no roses anymore. Not for anyone. The regime made being different dangerous. They took Ruth while she was out buying food, and it was not long until they came for Valerie.

Now Valerie sits in a cell writing her life story on toilet paper for a stranger she will never meet. She tells that stranger about Sarah, Christina, her parents, Ruth, and the best years of her life. Then she closes by telling that stranger she loves them.

The letter does not reach Evey by accident. V later tells her that Valerie wrote it just before she died, and that he delivered it to Evey as it had been delivered to him. The letter becomes more than a confession. It becomes a handoff, one person's last act of integrity entrusted to another.

The center of the letter is a single idea:

Our integrity sells for so little, but it is all we really have. It is the very last inch of us. And within that inch, we are free. We must never lose it or give it away. We must never let them take it from us.

Everything else can be taken from a person. That inch can only be surrendered. The regime took her freedom, her future, and Ruth, but it never got that inch. She faces death with it intact, apologising to no one.

A lot of that is simply true. There is something in a human being that no power on earth can take. No one can take your conscience by force. You can only give it away. And Valerie is right about what it is worth. The world will buy it from you for almost nothing, and it is still worth more than everything you own. Jesus asked the same question a different way: what can a man give in exchange for his soul (Mark 8:36-37)?

Even the villains in this story teach something. The government wraps itself in the language of faith while it tortures people. When you see that, do not blame the faith. Blame the people wearing it as a costume. Anyone cruel to a person made in the image of God is showing you who they really serve, and it is not God.

But the letter can only carry its hope so far. Valerie hopes the stranger who finds her letter escapes that place. She hopes the world turns and that things get better. But by her own words, every last inch of her will perish except one. At first, that final inch seems to survive only as ink on paper in a stranger's hands. If death gets the last word, her integrity is beautiful, and it is also doomed.

But the film does not let death have the last word. V carries the letter forward. Evey receives it. What Valerie guarded in a cell becomes strength in another person. The inch survives not because Valerie escaped death, but because what she refused to surrender outlived her.

The gospel agrees with Valerie about the inch and disagrees about the ending. Jesus said that whoever clings to his life will lose it, and whoever hands his life over to Him will keep it (Mark 8:35). So the inch is real. The question is whether there is anyone you can trust to hold it.

Valerie guarded hers in isolation, all the way to the end, and I honor her for it. But listen to her hope again. The world turning. Things getting better. Roses again. That is almost a prayer, and there is a promise that answers it, from the One who makes all things new. Because of the resurrection, the last inch is not a keepsake. It is a seed.

Watch the scene if you never have, or even if you have. And if the story grabs you the way it grabbed me, there is a fan shrine to the book that has been maintained since 1997, which tells you I am not the only one this letter impacted. Then ask yourself the letter’s real question, the one Valerie answered in that cell: what part of you will you refuse to surrender, and who will you trust with it?

Spend ten minutes on YouTube or Medium and you will find all three camps. AI is about to make software developers obsolete. AI is worthless hype. And lately a third group, the one that interests me most: developers who went all in for a year and are now walking away entirely, not because the tools failed them, but because of what the tools did to them.

Their stories all sound the same. It started with code and then it crept. They found they could no longer make a decision without asking first. Not just design decisions, either. What to eat. What that symptom might mean. One described it as a kind of paralysis, and I believe him. The tool had not taken his job. It had taken his say.

I have said some version of this a thousand times: the human is meant to be the master, and the AI is meant to be the servant. Mastery is not bravado. It means you understand the thing, you verify it, you make the call, and you own what follows.

That order is not a preference. Get it right and the tool amplifies everything you already are. Get it backwards and you have the blind leading the blind.

A good servant, but a bad master.

Our grandparents understood this. They said it about fire, and they said it about money. Fire in the hearth warms the house. Fire on the loose takes it. And money is no different; you can spend your life directing it, or spend your life answering to it. The question with a powerful tool was never whether to use it. It is who answers to whom.

AI is the fastest apprentice you will ever hire. It has read more than any of us ever will, it never tires, and it never grumbles about the boring work. But it has no stake in the outcome, and it will never stand behind the result. It owns nothing. You own everything. Keep that straight and the relationship works.

Lose it, and you get the junior developer problem. With AI, a junior can produce code that looks senior, and used well, the same tool can genuinely help him learn faster than I ever could at his stage. I am glad for that. But can he support that code at two in the morning? Can he explain why it works, or notice the day it quietly stops working?

A capability you borrow is not a capability you have. And this goes well beyond software. A chatbot will hand anyone a confident medical answer, and the confidence is the dangerous part, because the person receiving it has no way to weigh it. Real expertise was never just information. It was information with someone accountable standing behind it.

There is a small version of this you have already lived through. GPS is a marvel, and a generation of us can no longer find our way across our own town without it. Skills you hand off completely do not wait around for you. They leave.

After more than thirty years of writing software, I know where I land. I love this craft. Building things is part of what I was made for, and I take it seriously. I gladly hand AI the mundane: boilerplate, scaffolding, a second set of eyes on something I have stared at too long.

It makes my work faster and, used carefully, better. But the decisions are mine. The design is mine. The finished product goes out with my name on it, and I remain judge, jury, and executioner over every line. If I cannot explain a piece of it, it does not go out.

Both sides of that showed up in my own work just this month. An AI review caught a genuine mistake in something I had written, a technical claim that was flat wrong, and I was glad to be corrected. The same week, I threw out a confident answer from the same kind of tool because it added details that checked out nowhere. The tool was the same. What changed was whether I did my part.

The hard part is discipline, and I struggle with it like everyone else. It is easy to ask one more question, then accept one more answer unexamined. Depending on the tool happens gradually, usually before you notice it.

You do not need to write software to use this test. Before accepting what the machine gives you, ask:

• Can I explain it?
• Can I verify it?
• What happens if it is wrong?
• Am I prepared to own the result?

Four yeses and you are still the master, exercising your own judgment with a very fast servant at your side. Anything less, and the decision goes back to you, the old way, by doing the work.

The Count of Monte Cristo sits at the top of my favorites page, and the 2002 film gave me a line I have carried around for years. It is the toast Edmond gives at Albert's birthday celebration, the one that begins with life being a storm, sunlight one moment and rocks the next, and ends with the question of what a man does when that storm arrives.

Life is a storm, my young friend. You will bask in the sunlight one moment, be shattered on the rocks the next. What makes you a man is what you do when that storm comes. You must look into that storm and shout as you did in Rome. Do your worst, for I will do mine! Then the fates will know you as we know you: as Albert Mondego, the man!

That speech is powerful. It is also not quite the whole truth. Interestingly, the movie seems to know that.

On its own, the storm speech tells us to stand firm, face the weather, and refuse to be broken by it. There is real dignity in that kind of resolve, and it resembles the popular picture of Stoicism, if not quite the real thing. But listen to whom Edmond appeals at the end of the toast: the fates. That is where the speech leaves me unsatisfied. Fate, at least as the speech uses the word, cannot know anyone. You can defy a storm all you want, but the storm does not care who you are.

The film gives us another answer from the first act. In his cell at Château d'If, Edmond carves a promise into the stone: God will give him justice. The years grind that promise out of him, and when he finally tells the old priest that he no longer believes in God, Faria gives him the best line in the movie: “It doesn't matter. He believes in you.”

Edmond spends the rest of the story trying to become his own providence, dealing out justice with his own hands. But after vengeance nearly costs him everything that matters, he returns to Château d'If, admits that the priest was right all along, and promises that what he once used for revenge will be used for good.

Alexandre Dumas got there first. I read the novel back in 2014, roughly twelve hundred pages in my edition, and the book is even more openly a story about providence than the film. Dantès learns the hard way that assuming God's place can harm the innocent, and Dumas closes the whole thing with two words of counsel: wait and hope.

Not brace and shout. Wait and hope. Those are only sane instructions if Someone trustworthy is steering.

That is the difference between fate and providence. Fate is weather. Providence is a Person. The Gospels have their own storm scene, a boat taking on water while Jesus sleeps in the stern. When he stands up and stills the storm, the disciples ask the only question that matters: “Who then is this? Even the wind and the sea obey him!” (Mark 4:41).

The storm speech tells you to look the storm in the eye. That scene tells you the storm has a Master, and he is in the boat with you.

I have had a few storms of my own these past several years, and I can tell you which of those two ideas holds weight when the rocks are close.

So I will keep the quote at the top of my favorites page, because courage in the storm is right and good. I have simply learned to read it the way the movie reads it: as the middle of the story, not the end.

Watch it again sometime and see if you agree.