The Missing Row: Auto-Provisioning Derived Records Without the Race Condition

The Problem

Let's use a fictional product: a collaboration tool called Loop. In Loop, the important entities are:

• An Organization (a paying customer).
• A Member (a person invited into an organization under a plan).
• A Team - a grouping that members belong to, keyed by (OrganizationId, PlanCode).

The admin dashboard lists Teams. Each team card shows a member count. Here's the catch in the original design: creating a Member wrote a member row. Creating a Team was a separate, manual step - an admin was expected to do it first. If an admin invited members without first creating the matching team, the dashboard showed nothing even though the members clearly existed.

From the user's point of view, they did everything right. From the system's point of view, a required row simply didn't exist.

Why It Matters

The Team record isn't independent information. It is fully derivable from the first member invited under a plan. When one entity's existence is implied by another, forcing a human to create it manually is a design smell. It leads to:

• Empty states that look like outages. Users can't tell "no data" from "misconfigured."
• Support load. Every skipped step becomes a ticket.
• Silent data drift. Members exist with no grouping to roll them up.

The fix is to let the system provision the derived record at the moment it first becomes necessary - the first write - instead of relying on a prerequisite step.

The Naive Solution (and Its Two Traps)

The obvious move: when a member is created, create the team if it isn't there yet.

public async Task AddMemberAsync(NewMember input, CancellationToken ct)
{
    var member = Member.Create(input);
    await _members.AddAsync(member, ct);

    // Create the team if this is the first member on this plan.
    var exists = await _teams.AnyAsync(
        t => t.OrganizationId == input.OrganizationId
          && t.PlanCode == input.PlanCode, ct);
    if (!exists)
    {
        var team = new Team(input.OrganizationId, input.PlanCode);
        await _teams.AddAsync(team, ct);
    }

    await _unitOfWork.SaveChangesAsync(ct);
}

This works in a demo. In production it has two traps.

Trap 1: The Check-Then-Insert Race

AnyAsync(...) and AddAsync(...) are not atomic. If two members are invited to the same new team at the same moment, both requests can observe exists == false, and both insert a team. Now the dashboard shows two identical team cards, and every downstream count is split across them.

An application-level AnyAsync check can never close this window on its own. The only reliable guard is a unique constraint, so the database - the one component that sees all writes - rejects the second insert.

// EF Core model configuration
modelBuilder.Entity<Team>()
    .HasIndex(t => new { t.OrganizationId, t.PlanCode })
    .IsUnique();

With the constraint in place, turn the "check" into a "try, and treat a uniqueness violation as success" - because a violation means someone else already created exactly the row you wanted:

private async Task EnsureTeamExistsAsync(
    Guid organizationId, string planCode, CancellationToken ct)
{
    var exists = await _teams.AnyAsync(
        t => t.OrganizationId == organizationId
          && t.PlanCode == planCode, ct);
    if (exists) return;

    _teams.Add(new Team(organizationId, planCode));

    try
    {
        await _unitOfWork.SaveChangesAsync(ct);
    }
    catch (DbUpdateException ex) when (IsUniqueViolation(ex))
    {
        // A concurrent request created the same team first.
        // That is the desired end state, so this is a no-op, not an error.
    }
}

The application-level AnyAsync still earns its keep: it avoids a failed insert on the common path. The constraint is what makes the code correct under concurrency. You want both - the check for efficiency, the constraint for truth.

Trap 2: The Normalization Mismatch

The team and the member are joined on PlanCode. If one side stores "pro" and the other stores "pro " (trailing space) or "PRO", the join silently returns nothing. You'll create a team and members, and the dashboard still shows a count of zero - a bug that looks identical to the original one.

Normalize the key in exactly one place, and make both sides use it:

public Team(Guid organizationId, string planCode)
{
    OrganizationId = organizationId;
    // Trim once, here, so the stored key matches how members store it.
    PlanCode = planCode.Trim();
    Id = Guid.NewGuid();
}

Whatever rule you choose - trim, lowercase, collapse - apply it on every path that reads or writes the key. A join is only as reliable as the normalization behind it.

The Retroactive Gap

Here's the part teams forget. This fix runs at write time. It fixes every member invited after you ship it. It does nothing for data that already exists. Organizations that invited members before the change still have no team row. They will look broken until either:

• someone invites one more member (the new code then provisions the missing team), or
• you run a backfill for the existing data.

Don't skip the backfill and hope the write path heals everything. Write a one-time job that creates the missing rows from what's already there:

-- Create the missing team for every distinct (org, plan) that has members
-- but no team yet. Idempotent: safe to run more than once.
INSERT INTO teams (id, organization_id, plan_code, created_at_utc)
SELECT gen_random_uuid(),
       m.organization_id,
       TRIM(m.plan_code),
       now()
FROM members m
LEFT JOIN teams t
    ON t.organization_id = m.organization_id
   AND t.plan_code = TRIM(m.plan_code)
WHERE t.id IS NULL
GROUP BY m.organization_id, TRIM(m.plan_code);

A forward fix plus a backfill is one complete change, not two optional ones. Ship them together.

Best Practices

• Let the system create what it can derive. If record B's existence is implied by record A, don't make a human create B first.
• Guard uniqueness at the database, not just in code. Application checks are an optimization; constraints are the guarantee.
• Make "already exists" a success, not an exception. Idempotent provisioning should converge on the same state no matter how many callers race.
• Normalize join keys once and everywhere. Most "the data is there but the count is zero" bugs are a normalization mismatch.
• Pair every write-time fix with a backfill. The forward path won't repair history.

Common Mistakes

• Relying on AnyAsync / SELECT ... IF NOT EXISTS alone under concurrency.
• Adding the unique index but not handling the violation - turning a race into a 500 for the second caller.
• Backfilling but forgetting to normalize in the backfill query, so it re-inserts near-duplicates.
• Provisioning the derived row in a separate transaction from the triggering write, so a partial failure leaves one without the other.

Lessons Learned

An empty response is not always "no data." Sometimes it's "the row you're reading from was never anyone's job to create." The most durable fix is to move that responsibility from the user to the system, and then make the system's version safe under concurrency and honest about history.

The interesting engineering isn't the get-or-create. It's everything around it: the constraint that makes it correct, the normalization that makes the join real, and the backfill that makes it true for data that already exists.

LinkedIn Account: LinkedIn
Twitter Account: Twitter
Credit: Graphics sourced from Medium