When the Status Column Is Updated from Everywhere: Transition Tables, State Machines and Status History

Version note: The C# code in this post was tested with .NET 10 and EF Core 10, and the SQL examples with SQL Server 2025.

Guess First: How Did an Approved Request Go Back to Draft?

Purchase request PO-2026-0107 was approved by a manager yesterday. Today the requester opens the edit screen, raises the total from 18,500 to 18,900 and saves. The screen sends back every field it loaded when the form was opened, status included. The edit endpoint looks like this:

PurchaseOrder po = await db.PurchaseOrders.SingleAsync(p => p.Id == dto.Id);
PurchaseOrderStatus before = po.Status;
po.Total = dto.Total;
po.Status = dto.Status;
await db.SaveChangesAsync();
Console.WriteLine($"Naive edit: {po.Number} {before} -> {po.Status}, no error, no history");

If the form was opened in the user's browser a day earlier, what value does dto.Status carry? What state is the request in after saving? Will approval be requested again even though the approved amount went up, or will purchasing place an 18,900 order thinking it's approved?

Context and Problem

About 1,400 requests are opened each month in HR Portal's purchasing module. Each one goes through a lifecycle: draft, submitted for approval, approved or rejected, ordered, received, cancelled. Today five different places write the PurchaseOrders.Status column: the edit screen's PUT endpoint, the approval endpoint, the managers' mobile app, a nightly job that pulls order status from the ERP, and the support team's admin panel. Each wrote its own if chain, and some wrote none at all.

The result: the rule has no single owner. "An approved request can only be ordered or cancelled by a manager" exists in one endpoint and not in another. Why a request was rejected is written only in the notification email. When two managers approve and reject the same request in the same minute, the last writer wins and nobody notices.

Symptoms

If the database has requests that went from Approved back to Draft, if the support team searches their inbox to answer "why was this request rejected", if both approval and rejection notifications were sent for the same request, if the "Approve" button is visible to the requester in the Angular screen and the API returns 500 when they click it, or if adding a new status (say, "Awaiting budget") requires changes in five files, your status column is being updated from everywhere.

The Naive Solution: Writing Status Like Any Other Field

The real output of the opening code:

Naive edit: PO-2026-0107 Approved -> Draft, no error, no history

The form had been opened a day earlier, while the request was still a draft. So dto.Status carried Draft, and the endpoint wrote it without question. No error, no history entry, the approval silently disappeared. This is the business-rule version of the mass assignment flaw from the OWASP series: the server accepts a field the client shouldn't be sending.

What actually breaks here isn't a missing validation, it's the model itself. Status is designed like an ordinary property; yet a status isn't a value, it's the result of allowed transitions. If the answer to "from which status to which, by whom, under what condition?" isn't in one place in the code, five endpoints give five different answers.

Options

Controlled Transitions Through Domain Methods

The first step is to close Status's setter and move transitions into methods: po.Submit(user), po.Approve(user), po.Reject(user, comment). Each method checks the current status and throws if the transition is invalid. The rule lives in one place, the compiler helps, and it's easy to test. The weakness: since the rule is in code, a change like "the Accounting role should be able to cancel too" needs a deploy, and answering "which actions can this user perform right now" means querying each method separately.

An Allowed Transitions Table

Turn the rules into data: StatusTransitions(FromStatus, ToStatus, Action, RequiredRole, CommentRequired). A single workflow service validates every action against this table. "Allowed actions for this user" becomes one query, and the Angular screen draws its buttons from it. The history table can be tied to the same table with an FK, so the database refuses to record an invalid transition. On the risk side: the rule now lives in data, so a wrong INSERT changes the workflow without changing code.

State Pattern or Stateless

If entry/exit behavior is attached to transitions (send a notification, reserve budget) and states become hierarchical, a state machine library is more expressive. The common choice in .NET is Stateless:

public static StateMachine<PurchaseOrderStatus, string> Create(PurchaseOrder po, UserInfo user)
{
    StateMachine<PurchaseOrderStatus, string> m = new(() => po.Status, s => po.Status = s);
    bool Is(string role) => user.Roles.Contains(role);

    m.Configure(PurchaseOrderStatus.Draft)
        .PermitIf("Submit", PurchaseOrderStatus.Submitted, () => Is("Requester"))
        .PermitIf("Cancel", PurchaseOrderStatus.Cancelled, () => Is("Requester"));
    m.Configure(PurchaseOrderStatus.Submitted)
        .PermitIf("Approve", PurchaseOrderStatus.Approved, () => Is("Manager"))
        .PermitIf("Reject", PurchaseOrderStatus.Rejected, () => Is("Manager"));
    m.Configure(PurchaseOrderStatus.Rejected)
        .PermitIf("Revise", PurchaseOrderStatus.Draft, () => Is("Requester"));
    m.Configure(PurchaseOrderStatus.Approved)
        .PermitIf("Order", PurchaseOrderStatus.Ordered, () => Is("Purchasing"))
        .PermitIf("Cancel", PurchaseOrderStatus.Cancelled, () => Is("Manager"));
    return m;
}

Stateless permitted from Approved: Cancel
Stateless: No valid leaving transitions are permitted from state 'Approved' for trigger 'Revise'. Consider ignoring the trigger.

Stateless 5.20.1 lets you keep the state in the entity's own property (the two lambdas in the constructor) and returns the allowed actions with GetPermittedTriggersAsync. The rules are still in code; you lose the table option's "change without deploy" advantage but gain compile-time safety and rich behavior. Writing GoF's State pattern yourself sits in the same spot; we'll build it in the State Pattern post of the planned Design Patterns series.

A Workflow Engine

If the approval flow differs from company to company and you need multi-level approval, delegation, timeouts and parallel approval, moving to a BPMN-based workflow engine is worth considering. That isn't a library, it's an infrastructure decision: its own database, its own versioning problem (what happens to running flows when the definition changes?) and its own operational load. It's too much for a purchase request with seven states.

Implementation: A Transition Table, Status History and Concurrency

In HR Portal, the business changes roles often and some of the 120 companies say "give cancel permission to the purchasing team too". So the choice is a transition table. The model:

public enum PurchaseOrderStatus { Draft, Submitted, Approved, Rejected, Ordered, Received, Cancelled }

public class PurchaseOrder
{
    public int Id { get; set; }
    public int CompanyId { get; set; }
    public string Number { get; set; } = "";
    public decimal Total { get; set; }
    public PurchaseOrderStatus Status { get; set; }
    public byte[] RowVersion { get; set; } = [];
}

public class StatusTransition
{
    public PurchaseOrderStatus FromStatus { get; set; }
    public PurchaseOrderStatus ToStatus { get; set; }
    public string Action { get; set; } = "";
    public string RequiredRole { get; set; } = "";
    public bool CommentRequired { get; set; }
}

public class StatusHistory
{
    public long Id { get; set; }
    public int PurchaseOrderId { get; set; }
    public PurchaseOrderStatus? FromStatus { get; set; }
    public PurchaseOrderStatus ToStatus { get; set; }
    public string ChangedBy { get; set; } = "";
    public DateTimeOffset ChangedAt { get; set; }
    public string? Comment { get; set; }
}

The configuration has two important parts. First, the contents of the transition table are seed data that comes with migrations: instead of editing the table by hand in production, every change goes through a PR and a migration. Second, the history table's (FromStatus, ToStatus) pair is tied to the transition table with a composite FK:

modelBuilder.Entity<StatusTransition>(e =>
{
    e.HasKey(x => new { x.FromStatus, x.ToStatus });
    e.Property(x => x.FromStatus).HasConversion<string>().HasMaxLength(20);
    e.Property(x => x.ToStatus).HasConversion<string>().HasMaxLength(20);
    e.HasData(
        T(PurchaseOrderStatus.Draft, PurchaseOrderStatus.Submitted, "Submit", "Requester"),
        T(PurchaseOrderStatus.Draft, PurchaseOrderStatus.Cancelled, "Cancel", "Requester"),
        T(PurchaseOrderStatus.Submitted, PurchaseOrderStatus.Approved, "Approve", "Manager"),
        T(PurchaseOrderStatus.Submitted, PurchaseOrderStatus.Rejected, "Reject", "Manager", commentRequired: true),
        T(PurchaseOrderStatus.Rejected, PurchaseOrderStatus.Draft, "Revise", "Requester"),
        T(PurchaseOrderStatus.Approved, PurchaseOrderStatus.Ordered, "Order", "Purchasing"),
        T(PurchaseOrderStatus.Approved, PurchaseOrderStatus.Cancelled, "Cancel", "Manager", commentRequired: true),
        T(PurchaseOrderStatus.Ordered, PurchaseOrderStatus.Received, "Receive", "Warehouse"));
});

modelBuilder.Entity<StatusHistory>(e =>
{
    e.Property(x => x.FromStatus).HasConversion<string>().HasMaxLength(20);
    e.Property(x => x.ToStatus).HasConversion<string>().HasMaxLength(20);
    e.HasOne<PurchaseOrder>().WithMany().HasForeignKey(x => x.PurchaseOrderId);
    // Only an allowed transition can be written to history; if FromStatus is NULL (first entry) the FK isn't checked
    e.HasOne<StatusTransition>().WithMany()
        .HasForeignKey(x => new { x.FromStatus, x.ToStatus })
        .IsRequired(false)
        .OnDelete(DeleteBehavior.Restrict);
});

We store statuses as string; as discussed in the enum versus lookup table post, so the reporting team doesn't have to ask what 3 means. RowVersion is the concurrency token through IsRowVersion().

A Single Authority: The Workflow Service

public async Task<TransitionResult> ExecuteAsync(PurchaseOrder po, string action, UserInfo user, string? comment, CancellationToken ct = default)
{
    StatusTransition? transition = await db.StatusTransitions
        .AsNoTracking()
        .SingleOrDefaultAsync(t => t.FromStatus == po.Status && t.Action == action, ct);
    if (transition is null)
    {
        return TransitionResult.InvalidTransition;
    }
    if (!user.Roles.Contains(transition.RequiredRole))
    {
        return TransitionResult.Forbidden;
    }
    if (transition.CommentRequired && string.IsNullOrWhiteSpace(comment))
    {
        return TransitionResult.CommentRequired;
    }

    db.StatusHistory.Add(new StatusHistory
    {
        PurchaseOrderId = po.Id,
        FromStatus = po.Status,
        ToStatus = transition.ToStatus,
        ChangedBy = user.Name,
        ChangedAt = clock.GetUtcNow(),
        Comment = comment
    });
    po.Status = transition.ToStatus;

    try
    {
        await db.SaveChangesAsync(ct);
        return TransitionResult.Done;
    }
    catch (DbUpdateConcurrencyException)
    {
        // Someone changed this request's status before us; the rowversion didn't match
        db.ChangeTracker.Clear();
        return TransitionResult.Conflict;
    }
}

The service never accepts a "new status"; it accepts an action name. The client can't say Status = Approved, only "Approve", and the table decides the target status. The status change and the history entry are written in the same SaveChangesAsync, so in the same transaction. The real output of a series of actions:

Allowed for ada on PO 2:   Cancel, Submit
Allowed for grace on PO 2:
Submit by ada:    Done
Approve by ada:   Forbidden
Reject by grace:  CommentRequired
Allowed for grace on PO 2: Approve, Reject

Manager Grace has no actions on a draft request; after Ada submits, Grace can approve or reject, but not reject without a reason. Ada can't approve her own request.

Two Managers, One Request

Grace and Alan open the request at the same time. Grace approves, Alan rejects a few seconds later with the reason "Budget frozen". The entity in Alan's hands is still Submitted, and Reject is a valid transition for it; the transition rule alone can't catch this race. The rowversion does:

Approve by grace: Done
Reject by alan:   Conflict
Approve again:    InvalidTransition
History of PO-2026-0108:
  2026-05-04 10:00 ada    Draft      -> Submitted
  2026-05-04 10:00 grace  Submitted  -> Approved

Alan's UPDATE affected zero rows because of the WHERE RowVersion = @old condition, and EF Core threw DbUpdateConcurrencyException; the mechanics are in the Concurrency post of the EF Core series. Grace's second approval attempt was refused because there's no Approve transition from Approved; a double click is harmless that way. The history has only the two transitions that really happened.

The Database Says No Too

If an invalid history entry is written from the admin panel or a script, the composite FK steps in:

DB guard: 547 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_StatusHistory_StatusTransitions_FromStatus_ToStatus". The conflict occurred in database "HrPortal", table "dbo.StatusTransitions".

Let's be honest about the limit: this FK protects the history table, not the PurchaseOrders.Status column. To catch a script that runs UPDATE PurchaseOrders SET Status = ... directly without writing history, you need a trigger on the table that compares the deleted and inserted statuses against the transition table (and, as we saw in the temporal data post, HasTrigger on the EF Core side). As long as the workflow service is the only path inside the application, the FK is a sufficient second line for most teams.

API and Angular

The API exposes two endpoints: the list of allowed actions and executing an action. Results are translated to HTTP in one place:

app.MapGet("/purchase-orders/{id:int}/actions", async (int id, PurchaseOrderWorkflow wf, ClaimsPrincipal user) =>
    Results.Ok(await wf.AllowedActionsAsync(id, user.ToUserInfo())));

app.MapPost("/purchase-orders/{id:int}/actions/{action}", async (int id, string action, ActionRequest body, PurchaseOrderWorkflow wf, ClaimsPrincipal user) =>
    (await wf.ExecuteAsync(id, action, user.ToUserInfo(), body.Comment)).ToHttpResult());

public static IResult ToHttpResult(this TransitionResult result) => result switch
{
    TransitionResult.Done => Results.NoContent(),
    TransitionResult.NotFound => Results.NotFound(),
    TransitionResult.Forbidden => Results.Forbid(),
    TransitionResult.CommentRequired => Results.ValidationProblem(
        new Dictionary<string, string[]> { ["comment"] = ["A comment is required for this action."] }),
    TransitionResult.InvalidTransition => Results.Problem(statusCode: 409, title: "This action is not allowed in the current status."),
    TransitionResult.Conflict => Results.Problem(statusCode: 409, title: "The purchase order was changed by someone else. Reload and try again."),
    _ => Results.StatusCode(500)
};

The request detail screen in Angular draws its buttons from the list returned by GET .../actions: "Submit" and "Cancel" for Ada, "Approve" and "Reject" for Grace. That's a user experience convenience, not security; even if a button is hidden, the rule is checked again in the API. The "Reject" button opens a reason field, and when a 409 arrives the screen reloads the request and shows "This request changed while you were looking at it". We'll standardize that response with ETag and 412 in the series post Optimistic Concurrency End to End. And the edit screen's DTO no longer has a Status field.

Trade-off Table

When Not to Use It

For fields with two or three states and obvious transitions (IsActive, "read/unread") don't build a transition table and a history table; a domain method is more than enough. Where transition rules rarely change and aren't role-based, a table moves the job of code into data and makes it less testable. If you've reached the point where rules differ per tenant and the business wants to design flows itself, consider a real workflow engine instead of growing the transition table; the moment you start adding a "condition" column to the table, you've started writing your own engine.

Checklist

• Never accept Status as a writable field in any DTO; the client sends an action, not a status.
• Keep transition rules in one place (domain method, table or state machine) and route every write path through it.
• On every transition, write who, when, from which status to which and why to history in the same transaction.
• Make a reason mandatory for actions such as reject and cancel.
• Put a rowversion on the entity carrying the status; return conflicts as a meaningful 409.
• Return allowed actions from the API; let Angular draw buttons from them, and check the rule again in the API.
• Manage transition table changes through migrations and code review; don't edit it by hand in production.

Related Patterns and Sources

The rowversion mechanics are in the EF Core series post Concurrency in EF Core: Tokens, rowversion and DbUpdateConcurrencyException; whether status strings should be an enum or a lookup table is in this series' Enum or Lookup Table? post. Accepting a field the client shouldn't send is covered in the OWASP Top 10 series post OWASP A08: Software or Data Integrity Failures — Deserialization and Mass Assignment; role and resource-based authorization in the Authorization post of the ASP.NET Core Web API series. GoF's State pattern itself is in the planned Design Patterns series (State Pattern: Managing Order States with State Objects Instead of switch); how the history table relates to a general audit trail is in this series' Audit Trail post; and the end-to-end conflict screen with ETag and 412 is in the series post Optimistic Concurrency End to End. Sources: Stateless (dotnet-state-machine) README and version 5.20.1; Microsoft Learn, Handling Concurrency Conflicts (EF Core); Microsoft Learn, Data Seeding (HasData); Gamma, Helm, Johnson, Vlissides, Design Patterns (State).

A Small Experiment

★☆☆ Add the rule "the Accounting role can also cancel an approved request" by changing only the seed data. How many files changed? How many files change when you make the same change in the Stateless version?

★★☆ Write a trigger on PurchaseOrders that, when Status changes, checks the deleted/inserted pair against StatusTransitions, and verify that the naive edit endpoint now fails. What did you have to change on the EF Core side?

★★★ Requests above 50,000 TL need two levels of approval: first the department manager, then the finance director. How would you model this with the transition table: a new status, a condition column on the transition, or a separate approvals table? Assess how close your chosen model comes to the warning in the "When Not to Use It" section.

In the next post we move from flat lists to trees: we'll compare adjacency lists, recursive CTEs, closure tables and hierarchyid for the query "everyone under this manager" in the department hierarchy.