Barkley&Mewton Business Platform

One of the most important correctness problems in the platform was making sure that two customers could not successfully book the same grooming slot. At first, this sounds like a relatively simple availability problem. A customer selects a time, the application checks whether the slot is available, and the booking is created if nothing overlaps. The difficulty is that availability is not a static piece of information. Two customers can request the same slot at almost exactly the same time.

🔍 Click to expand

The problem is not that either request performed an incorrect query. Both queries were correct at the moment they executed. The problem is that the check and the insert are separate operations.

If the system relies exclusively on:

var overlapExists = await context.Bookings.AnyAsync(x =>
      x.Status != BookingStatus.Cancelled &&
      x.StartDateTimeUtc < endDateTimeUtc &&
      startDateTimeUtc < x.EndDateTimeUtc);

then there is a window between the AnyAsync() query and SaveChangesAsync() where another request can create a conflicting booking. This is called a check-then-act race. The application can therefore reject ordinary overlaps while still failing to guarantee the business invariant under concurrent requests.

The first layer: application-level validation The application still performs the overlap check before creating a booking. For normal requests, this is useful because it allows the service to identify an unavailable slot without relying on a database exception for every attempted conflict. The overlap calculation is based on the booking's actual calculated end time:

var startDateTimeUtc =
    DateTime.SpecifyKind(dto.StartDateTimeUtc, DateTimeKind.Utc);

var endDateTimeUtc =
    startDateTimeUtc.AddMinutes(durationMinutes);

var overlapExists = await context.Bookings.AnyAsync(x =>
    x.Status != BookingStatus.Cancelled &&
    x.StartDateTimeUtc < endDateTimeUtc &&
    startDateTimeUtc < x.EndDateTimeUtc);

The comparison deliberately uses the complete booking window rather than simply comparing start times. For two bookings to overlap:

Existing.Start < New.End 
 && 
 New.Start < Existing.End

This correctly handles cases such as:

Existing:  |---------|
 New:           |---------|
              OVERLAP

while allowing adjacent appointments:

Existing:  |---------|
New:                  |---------|
                      no overlap

That distinction matters for a grooming business because a 10:00–11:00 appointment followed by an 11:00–12:00 appointment should be valid. The service also ignores cancelled bookings when determining availability, allowing a cancelled slot to become available again. T he important realization, however, was that this query cannot be the final authority on concurrency.

The database must enforce the invariant

The actual business rule is: Two active customer bookings must never occupy overlapping time ranges.Postgres provides a particularly useful mechanism for enforcing this: an EXCLUDE USING GIST constraint (which I learned about way too late let me tell you...)

The migration defines the booking interval as a PostgreSQL tstzrange and prevents overlapping ranges:

ALTER TABLE "Bookings"
ADD CONSTRAINT bookings_no_overlap
EXCLUDE USING GIST
 (
     tstzrange(
         "StartDateTimeUtc",
         "EndDateTimeUtc"
     ) WITH &&
 )
WHERE ("Status" <> 4 AND "ScheduledByAdmin" = false);

This changes the problem from an application-level race into a database-enforced invariant. PostgreSQL now becomes the final authority over whether two customer bookings are allowed to coexist. The && range operator simply specifies that the time ranges cannot overlap. Because the constraint operates on the stored booking interval, it protects the data even when two requests reach the database concurrently.

Why this is different from another availability check

It would have been tempting to solve this entirely in application code with a sequence such that checks availability and makes a booking. But that does not establish exclusivity. The database constraint instead makes the insert itself conditional on the invariant, which is much more authoritative.

Note:

This entire scenario is sort of an edge case, since it blocks two requests that happen at the exact same time. I don't forsee it really being that common, but it means that with this enforcement, whether this system serves 10 people or 10 000 people, it will always successfully block double bookings.

Turning a database conflict into an API response

A database constraint is useful only if the application handles its failure deliberately. PostgreSQL reports an exclusion-constraint violation using SQLSTATE 23P01. The booking service allows that database exception to propagate after logging the conflict, while the controller identifies the specific PostgreSQL error:

catch (DbUpdateException ex)
    when (IsExclusionConstraintViolation(ex))
{
    _logger.LogWarning(ex,
                "Booking creation conflicted with another booking"); //We log these to send to our own custom monitoring platorm

    return Problem(
        statusCode: StatusCodes.Status409Conflict,
        title: "Booking Conflict",
        detail: "The requested time is no longer available.");
}

A 409 Conflict is important here because the request itself may be completely valid. The customer did not send malformed data, the requested appointment simply became unavailable because another booking won the race. The same conflict handling is also applied when an administrator updates a booking in a way that would violate the customer-booking constraint. (slight foreshadowing)

Availability is not treated as a reservation

This also influenced how the availability endpoint was designed. The availability endpoint can tell the frontend which slots appear to be available, but that result is inherently temporary. For example:

09:30    Available
 10:00    Available
 10:30    Available

does not mean that the 10:00 slot has been reserved for the customer who is currently viewing the page. Another request could successfully book it before the customer submits their own booking. The final POST /api/bookings operation therefore remains authoritative. The frontend uses availability to provide a good user experience.

Admin bookings are deliberately different (oh boy...)

There is also an important business distinction in the database constraint: The business required administrators to be able to schedule overlapping appointments when necessary. For example, staff may deliberately schedule concurrent grooming work that would not be permitted through the normal customer booking flow. Rather than weakening the entire constraint, the booking model records whether a booking was scheduled by an administrator:

ScheduledByAdmin = currentUser.IsAdmin,

The Postgres contraint only applies when "ScheduledByAdmin" = false

This was the preferable check since it means that the database can stay unaware of the distinctions between bookings, it would simply understand which records are subject to the invariant. Admins needed to be able to do close to whatever they wanted within the bounds of the business logic, but in that it exposes the weaknesses of this system: The system is unfortunately vulnerable to admins feeding in bad data.

Cancellation also interacts with the constraint

Cancelled bookings should no longer occupy a slot. The constraint therefore excludes cancelled bookings:

WHERE ("Status" <> 4 AND "ScheduledByAdmin" = false)

This means cancelling a booking effectively releases its time range for future customer bookings without deleting the historical record. This was an important choice since the business still wants to retain booking history, while the cancelled appointment no longer blocks the slot from being rebooked.

The Result

The final pipeline design deliberately uses multiple layers:

Customer request
│
▼
BookingTimeHelper
Valid booking window
│
▼
Application overlap check
Fast normal rejection
│
▼
EF Core insert
│
▼
PostgreSQL exclusion constraint
Final concurrency guarantee
           │
    ┌──────┴──────┐
    │             │
  Success      Conflict
    │             │
    ▼             ▼
201 Created    409 Conflict

The UI shows which slots are available. The application validates the request and provides a nice user experience. The controller translates the database conflict into a meaningful HTTP response. The database protects the integrity of the data when multiple requests come in at once.

And the coolest part to me is that is scales really well with users since the foundation for the Insert functions is solid. Combined with the rate limiting, means it can stand up to being overwhelmed in production.

Authentication and authorization were areas of the project where I was learning as I built. Before this application, I had very little practical experience working with JSON Web Tokens, claims, principals, refresh tokens, or role-based authorization in a real application. I understood the concepts at a high level (mostly), but this was the first time I had to design an authentication system where security decisions affected a real business and real customer data.

To complicate things further, this wasn't just about adding a login page and putting [Authorize] on a few controllers. The application containted two fundamentally different types of identity from the start:

• Customers:
who should only be able to access their own information and perform customer operations.
• Admins:
who required access to the operational side of the platform and could manage information belonging to the wider business.

And never the two shall meet.

The challenge therefore was to figure out:

Who is the user?
 AND
 What are they allowed to access?

The second question proved to be much more important than simply establishing that somebody was logged in.

Learning JWTs and principals through implementation

My starting point was to understand what actually happens after a successful login. A customer or administrator supplies credentials to the ASP.NET Core backend through a controller and once those credentials have been verified, the backend creates a JWT access token containing claims about the authenticated principal.

One of the most important claims in the application is the role:

var claims = new List<Claim>
{
    new(ClaimTypes.NameIdentifier, principalId.ToString()),
    new(ClaimTypes.Name, name),
    new(ClaimTypes.Email, email),
    new(ClaimTypes.Role, role)
};

ASP.NET Core can use the ClaimTypes.Role value when evaluating role-based authorization right on the controller:

[Authorize(Roles = "Admin")]

So what I had to learn was that JWT does not mean "This person is allowed to login", it's more about establishing and authentication principal with the information that the application can use to make authorization decisions.

Roles were deliberately controlled by the server

One of the first important security concerns to nail was that customers must never be able to choose their own role.

It sounds obvious but in essence it means we cant build a system which just accepts something like:

{
    "email": "someone@example.com",
    "password": "...",
    "role": "Admin"
}

The customer role is assigned by the server when the authentication principal is created, after email verification.

Customer and administrator identities are separate

The platform maintains separate Users and Admins identity records. This was deliberate decision because the two identities have vastly different security requirements and responsibilities.

The login process checks for an administrator identity first. If one exists, the credentials are verified against the administrator's password hash and an Admin principal is created. Otherwise, the application attempts customer authentication and creates a Customer principal.

This distinction is important because an email address must not accidentally cause the wrong type of identity to be authenticated. The authentication service therefore contains explicit logic around the possibility of an address existing in both identity tables. The same concern is considered during refresh-token processing so that an existing customer refresh token cannot silently produce a customer session for an address that has subsequently become associated with an administrator account. This was one of the areas where the security work went beyond simply following a JWT tutorial.

Authentication is not authorization

One of the biggest lessons from the project was understanding that successfully authenticating a customer does not mean they are allowed to access arbitrary customer resources.

For example, suppose two customers exist within the system. Both may have completely valid JWTs and both may have:

Authenticated = true
 Role = Customer

Both being authentication, and having similar roles does not give them permission to view each other's data. It sounds like an intuitive truth, but making sure it works in the system became a very important distinction.

Preventing customer data from bleeding across accounts

This was an area I came very conscious of during development. For ever customer-facing endpoint, I had to consider what would happen if a valid customer deliberately changed an identified in the request.

For example:

GET /api/users/{some-other-user-id}

or

GET /api/bookings/{booking-id-belonging-to-someone-else}

We needed authorization checks that combine the authenticated principal with the resource being accessed.

The users service, for example, extracts the role from the ClaimsPrincipal and treats administrators differently from customers when determining whether a user record can be accessed. The same principle was applied across customer-owned resources. A customer can be authenticated and still be denied access because the requested resource belongs to somebody else.

The security audit mindset

I had a conscious mental model when I was designing the endpoints of: "How are people going to try and break this?"

I started thinking about hostile or malicious incorrect requests:

Customer A:
 Requests Customer A's data      → Allow
 Requests Customer B's data      → Deny
 Requests an admin resource      → Deny
 Changes an ID in the URL        → Deny if not owned
 Modifies an admin-only field    → Deny
 Attempts to self-assign Admin   → Never accepted (even considering IP bans here)

I was asking myself the question of: "What is the worst thing and authenticated user could attempt to do through this endpoint?"

That question exposed a much wider range of problems than just making sure the correct endpoints had [Authorize] on them.

The API had to be the authoritative security layer

The browser communicates with the Next.js application rather than directly communicating with the ASP.NET Core API. This created a Backend-for-Frontend boundary, but it also meant that the security model had to remain consistent across that boundary. The frontend could not be treated as the authority on what a customer was allowed to do.

It couldn't be as straight-forward as hiding an administrative button on the frontend and expect it to be enough, a malicious client could potentially construct the request manually without using the intended interface.

The security model therefore had to sit outside of the UI. The lesson to me was that: "The frontend can improve the user experience, but it cannot be the security boundary."

Authentication was itself treated as a layered system.

The security work also extended beyond the role claim. Customer authentication includes email verification before a customer can receive or continue using an authenticated session. The refresh flow checks the customer's verification state as well.

Passwords are never persisted directly.The authentication service hashes passwords before storing them and verifies supplied credentials against those hashes rather than comparing plaintext values. Refresh tokens are also treated as credentials rather than ordinary application data. The raw refresh token is returned to the client, while its hash is stored in PostgreSQL. Refreshing a session revokes the token that was just used and generates a replacement.

Testing was part of this security flow

The same thinking carried into the automated tests. The test suite creates authenticated principals with explicit NameIdentifier and Role claims, allowing customer and administrator behavior to be tested independently.

That made it possible to test scenarios such as:

Customer → own resource       → allowed
 Customer → another resource   → denied
 Customer → admin operation    → denied
 Admin    → protected resource → allowed
 Unauthenticated → protected   → denied

This was actually useful because authorization bugs were often invisible during normal development. If I login as myself and request my own data, everything looks correct. Testing therefore had to include deliberately incorrect identities and deliberately incorrect resource identifiers. Something that seems obvious to anyone working with security before, but something I had to learn as I went along.

What I learned by doing this

Before building the platform, JWTs were largely something I understood conceptually. Building the authentication system forced me to understand what the token actually represented, how claims become part of an ASP.NET Core ClaimsPrincipal, how role-based authorization consumes those claims, and—more importantly—where role authorization stops being enough. It was also important to get first-hand exposure to how these systems can be tricked and then learning to account for that.

A secure system has to continuosly be able to answer:

Who are you?
        ↓
 How did we authenticate you?
        ↓
 What role do you have?
        ↓
 What resource are you requesting?
        ↓
 Do you own that resource?
        ↓
 Are you allowed to perform this operation?
        ↓
 What data are you allowed to see?

For a system storing customer profiles, pets, bookings, orders, contact information and operational business data, preventing that kind of data leakage was more important than simply making the login flow work.

All the while trying to furiously google "What is a JWT secret key used for"