Suggest changes: Building an Enterprise Booking System with ASP.NET Core 10 and SQL Server

Edit the text directly. Your proposal will remain pending until it is reviewed and approved. Only the content is displayed here — the title, date, and metadata cannot be changed from this form.

Content

Managing reservations for institutional properties—such as recreational vacation centers, country houses, and seasonal apartments for university faculty—presents unique business constraints. High-demand holiday seasons create race conditions where multiple users attempt to reserve the same unit simultaneously, and identity management must cater to verified institutional members with low-friction authentication.

I engineered **[fondo-xyz-booking-system](https://github.com/PedroCaballero741/fondo-xyz-booking-system)** to handle this end-to-end for the Fondo de Docentes de la Universidad Nacional de Colombia (FODUN). Built with **.NET 10**, **ASP.NET Core MVC**, **Entity Framework Core 10**, and **SQL Server 2022**, the system enforces strict booking rules, automated date conflict resolution, and transactional email confirmations.

---

## 4-Tier Clean Architecture

Enterprise systems demand clean separation of concerns to allow easy maintenance, automated testing, and future API exposure without polluting domain models:

```mermaid
flowchart TB
 subgraph Presentation ["1. Presentation Layer (FODUN.Web - ASP.NET Core 10 MVC)"]
 direction TB
 subgraph FacultyUI ["Faculty Member Portal"]
 CATALOG["Property Catalog & Availability Interactive calendar & facility filters"]
 RESERVE_FORM["Reservation Form & Date Picker Stay selection with immediate UI feedback"]
 PIN_LOGIN["PIN Authentication Gate Institutional ID + 4-digit PBKDF2 PIN"]
 end

subgraph AdminUI ["Management Dashboard"]
 AUDIT["Administrative Control Center Blackout schedules, maintenance windows, audit logs"]
 end
 end

subgraph BLL ["2. Application & Business Logic (FODUN.Business)"]
 direction TB
 AUTH_SVC["Institutional Identity Service PBKDF2 hasher, 5-attempt brute-force lockout policy"]
 BOOKING_SVC["Booking & Reservation Service Date overlap rules, seasonal price calculations"]
 DATE_CHECK{"Check Range Availability <code>IsDateRangeAvailableAsync</code>"}
 CONFLICT(["Reject & Display Guidance 409 Conflict / Visual clash toast"])
 EMAIL_SVC["Transactional Notification Service MailKit / MimeKit async HTML vouchers"]

PIN_LOGIN --> AUTH_SVC
        RESERVE_FORM --> BOOKING_SVC
        BOOKING_SVC --> DATE_CHECK
        DATE_CHECK -->|Overlapping Range Exists| CONFLICT
    end

subgraph DAL ["3. Data Access & Persistence (FODUN.Data)"]
 direction TB
 DBCONTEXT["EF Core 10 ApplicationDbContext ChangeTracker, LINQ query compiler, Migrations"]
 TX["Atomic Database Transaction Serializable isolation & row-level range locks"]
 REPOS["Domain Repositories Properties, Members, Bookings, Blackouts"]

DATE_CHECK -->|Date Validated| TX
        TX --> DBCONTEXT
        DBCONTEXT --> REPOS
    end

subgraph Database ["4. Relational Database (SQL Server 2022 in Docker)"]
 direction TB
 DB_INSTANCE[("SQL Server 2022 Container Port 1433, EF Core Migrations")]
 TABLES["Relational Schema: • Members (EmpID, PIN Hash, Roles) • Properties (Capacity, Tier, NightlyRate) • Bookings (CheckIn, CheckOut, Status) • Payments & BlackoutDates"]

REPOS --> DB_INSTANCE
        DB_INSTANCE --- TABLES
    end

subgraph External ["External Integrations"]
 SMTP[("Institutional SMTP Relay Authenticated TLS dispatch")]
 FACULTY_INBOX(["Faculty Email Inbox DKIM-signed HTML confirmation voucher"])

TX -.->|Transaction committed successfully| EMAIL_SVC
        EMAIL_SVC --> SMTP
        SMTP --> FACULTY_INBOX
    end
```

---

## 1. Concurrency Control and Double-Booking Prevention

The most critical requirement of any reservation platform is that **no two members can ever successfully book the same facility for overlapping dates**.

To guarantee database integrity under concurrent web requests, the business layer enforces strict overlapping validation before dispatching SQL transactions:

```csharp
// FODUN.Business/Services/BookingService.cs snippet
public async Task<bool> IsDateRangeAvailableAsync(int propertyId, DateTime checkIn, DateTime checkOut)
{
 return !await _context.Bookings
 .Where(b => b.PropertyId == propertyId && b.Status != BookingStatus.Cancelled)
 .AnyAsync(b => checkIn < b.CheckOutDate && checkOut > b.CheckInDate);
}
```

When coupled with SQL Server transaction isolation and database unique constraints on active booking intervals, race conditions are safely rejected, displaying clear user guidance instead of corrupting calendar availability.

---

## 2. Institutional Authentication with 4-Digit Security PINs

Unlike generic public SaaS platforms where users sign up with passwords and social logins, university faculty funds require identity verification matched against pre-approved member directories.

The application configures **ASP.NET Core Identity** to authenticate faculty members using their institutional employee ID combined with a secure 4-digit numeric PIN, hashed using PBKDF2:
- Minimizes login friction for senior faculty members.
- Protects accounts with brute-force lockout policies after 5 failed attempts.
- Enforces role-based authorization dividing regular faculty members from administrative managers who oversee maintenance schedules and audit logs.

---

## 3. Automated Transactional Receipts via MailKit

Upon reservation confirmation, members receive rich HTML booking vouchers including reservation codes, check-in instructions, pricing breakdowns, and cancellation policies.

Using **MailKit** and **MimeKit** over authenticated SMTP ensures reliable delivery, comprehensive DKIM compatibility, and structured error handling if institutional mail servers encounter temporary downtime.

---

## Local Development with Docker

To make testing and onboarding instantaneous for engineering evaluators, the entire SQL Server 2022 database is containerized via Docker:

```bash
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=FondoXYZ@2025" \
  -p 1433:1433 --name fondoxyz-sqlserver \
  -d mcr.microsoft.com/mssql/server:2022-latest
```

Running `dotnet ef database update` automatically applies migrations and seeds test recreational properties, sample apartments, and administrative test accounts.

---

## Conclusion

By applying domain-driven clean architecture to ASP.NET Core 10, `fondo-xyz-booking-system` delivers a resilient, high-performance platform capable of handling complex institutional workflows with bulletproof database integrity.