Published / updated29 Aug 2026

Guided API Project

Before you start

You need: all of Articles 01–11, the schema from Track 06, and the repositories from Track 07.

In Visual Studio: one solution, projects for Api, Core and Data, plus a test project.

Time: 12–16 hours across two weeks.

Goal

Demonstrate that you can build a secured, validated, multi-tenant Web API over SQL Server, prove its behaviour with tests, and document it well enough that a client developer needs no further explanation.

Assignment

Build the NexCoding School Portal API against the database from the SQL Server track.

Deliverable	Contents
Domain/	Entities and enums, no dependencies
Data/	Connection factory, repository interfaces, Dapper implementations
Services/	Business rules, transaction boundaries
Api/	Controllers, DTOs, Program.cs
Tests/	Unit and integration tests
postman/	A collection covering every endpoint
README.md	How to run it
DECISIONS.md	Choices, with reasons

Required endpoints

Method	Route	Notes
POST	/api/auth/login	Anonymous, rate limited, returns a JWT
GET	/api/students	Paged, filtered, sorted
GET	/api/students/{publicId}	With fee summary and recent results
POST	/api/students	201 with Location
PUT	/api/students/{publicId}	204
DELETE	/api/students/{publicId}	Soft delete, 204
GET	/api/exams/{examId}/results	With student and subject
POST	/api/exams/{examId}/results	Bulk save in one transaction
POST	/api/fee-accounts/{publicId}/payments	Payment and balance atomically
GET	/api/reports/outstanding-fees	Excludes cancelled payments
GET	/health/live, /health/ready	Liveness and readiness

Non-negotiable requirements

• SchoolId from the JWT claim, never from the request, and in every query's WHERE
• PublicId in every route; the integer Id never leaves the data layer
• Request and response DTOs only — entities never cross the API boundary
• FallbackPolicy requiring authentication; anonymous endpoints opt out explicitly
• Every decimal parameter with explicit precision and scale
• Absent exam results store null marks, never 0
• Payment insert and balance update in one transaction
• Global exception handler returning ProblemDetails with a traceId, no stack traces in production
• Structured logging with a correlation id; no passwords, tokens or connection strings logged
• CORS for named origins only, in the correct pipeline position
• Swagger with a security definition, Development only
• CancellationToken accepted and passed on every async path

Worked example: the layering

StudentsController  →  IStudentService  →  IStudentRepository  →  SQL Server
       DTOs                 entities            Dapper

[ApiController]
[Route("api/students")]
[Authorize]
[Produces("application/json")]
public sealed class StudentsController : ControllerBase
{
    private readonly IStudentService _studentService;

    public StudentsController(IStudentService studentService)
    {
        _studentService = studentService;
    }

    /// <summary>Creates a student.</summary>
    /// <response code="201">The student was created.</response>
    /// <response code="400">Validation failed.</response>
    /// <response code="409">The roll number is already in use.</response>
    [HttpPost]
    [Authorize(Policy = "CanManageStudents")]
    [ProducesResponseType(typeof(StudentDto), StatusCodes.Status201Created)]
    [ProducesResponseType(typeof(ValidationProblemDetails), StatusCodes.Status400BadRequest)]
    [ProducesResponseType(StatusCodes.Status409Conflict)]
    public async Task<ActionResult<StudentDto>> Create(
        StudentCreateRequest request, CancellationToken ct)
    {
        try
        {
            var created = await _studentService.CreateAsync(User.GetSchoolId(), request, ct);

            return CreatedAtAction(
                nameof(GetById), new { publicId = created.PublicId }, created);
        }
        catch (DuplicateRollNumberException ex)
        {
            ModelState.AddModelError(nameof(request.RollNumber), ex.Message);
            return ValidationProblem(ModelState);
        }
    }
}

The controller does four things and nothing else: read the tenant from claims, call the service, map an expected domain failure to a response shape, and choose the status code. No SQL, no business rules, no try/catch around anything the global handler covers.

Worked example: the payment endpoint

The one that carries money, and therefore the one a reviewer reads first.

public async Task<FeePaymentResult> RecordPaymentAsync(
    int schoolId, Guid feeAccountPublicId, FeePaymentRequest request,
    string collectedBy, CancellationToken ct)
{
    // Validate before opening a transaction — a rejected payment takes no locks
    var account = await _feeRepository.GetAccountByPublicIdAsync(schoolId, feeAccountPublicId, ct);

    if (account is null)
    {
        throw new NotFoundException("Fee account not found.");
    }

    var outstanding = account.TotalFees - account.DiscountAmount - account.PaidAmount;

    if (request.Amount > outstanding)
    {
        throw new PaymentExceedsBalanceException(request.Amount, outstanding);
    }

    using var connection = _connectionFactory.Create();
    await connection.OpenAsync(ct);

    using var transaction = (SqlTransaction)await connection.BeginTransactionAsync(ct);

    try
    {
        var payment = new FeePayment
        {
            SchoolId = schoolId,
            FeeAccountId = account.Id,
            Amount = request.Amount,
            PaymentMode = request.PaymentMode,
            PaidOn = DateTime.UtcNow,
            CollectedBy = collectedBy
        };

        var paymentId = await _feeRepository.InsertPaymentAsync(
            connection, transaction, payment, ct);

        await _feeRepository.IncreasePaidAmountAsync(
            connection, transaction, schoolId, account.Id, request.Amount, ct);

        await transaction.CommitAsync(ct);

        _logger.LogInformation(
            "Payment {PaymentId} of {Amount} recorded for account {FeeAccountId} by {User}",
            paymentId, request.Amount, account.Id, collectedBy);

        return new FeePaymentResult { PaymentId = paymentId, ReceiptNumber = payment.ReceiptNumber };
    }
    catch
    {
        await transaction.RollbackAsync(ct);
        throw;
    }
}

Five things a reviewer will check:

Detail	What breaks without it
Validation before BeginTransaction	A rejected payment holds locks for no reason
Both writes in one transaction	A payment recorded against an unchanged balance — the receipt says paid, the system says owing
The transaction passed to both repository calls	The second write is not rolled back, which is worse than no transaction
Tenant filter on the account lookup	A leaked PublicId reaches another school's account
The database CHECK constraint still present	The application check cannot win a race between two clerks

The third row is the subtle one. Omitting transaction on one call compiles, runs, and looks correct in review — and leaves the database inconsistent only when something else fails.

Worked example: the results endpoint

public async Task SaveResultsAsync(
    int schoolId, int examId, List<ExamResultRequest> requests, CancellationToken ct)
{
    var exam = await _examRepository.GetByIdAsync(schoolId, examId, ct)
        ?? throw new NotFoundException("Exam not found.");

    foreach (var request in requests)
    {
        if (request.IsAbsent && request.MarksObtained is not null)
        {
            throw new ValidationException("An absent student cannot have marks.");
        }

        if (!request.IsAbsent && request.MarksObtained is null)
        {
            throw new ValidationException("Enter marks, or mark the student absent.");
        }

        if (request.MarksObtained > exam.MaxMarks)
        {
            throw new ValidationException($"Marks cannot exceed {exam.MaxMarks}.");
        }
    }

    var results = requests.Select(r => new ExamResult
    {
        SchoolId = schoolId,
        ExamId = examId,
        StudentId = r.StudentId,
        IsAbsent = r.IsAbsent,
        MarksObtained = r.IsAbsent ? null : r.MarksObtained   // null, never 0
    }).ToList();

    await _examRepository.SaveResultsAsync(schoolId, examId, results, ct);
}

r.IsAbsent ? null : r.MarksObtained is the line that keeps an absent student off a fail list. Writing 0 there produces a result sheet saying a student sat an exam and scored nothing.

MaxMarks comes from the Exam row, not a constant — which is why this rule cannot be a data annotation.

Worked example: Program.cs

var builder = WebApplication.CreateBuilder(args);

// ---- Configuration, validated at startup ----
builder.Services
    .AddOptions<JwtOptions>()
    .Bind(builder.Configuration.GetSection(JwtOptions.SectionName))
    .ValidateDataAnnotations()
    .ValidateOnStart();

// ---- Services ----
builder.Services.AddSchoolData(builder.Configuration);
builder.Services.AddSchoolServices();

builder.Services.AddControllers()
    .AddJsonOptions(o =>
    {
        o.JsonSerializerOptions.PropertyNamingPolicy = JsonNamingPolicy.CamelCase;
        o.JsonSerializerOptions.Converters.Add(new JsonStringEnumConverter());
    });

builder.Services.AddAuthentication(JwtBearerDefaults.AuthenticationScheme)
    .AddJwtBearer(o => { /* every Validate* flag true, ClockSkew 30s */ });

builder.Services.AddAuthorization(o =>
{
    o.AddPolicy("CanManageStudents", p => p.RequireRole("Admin", "Principal"));
    o.AddPolicy("CanCollectFees", p => p.RequireRole("Admin", "Staff"));

    o.FallbackPolicy = new AuthorizationPolicyBuilder().RequireAuthenticatedUser().Build();
});

builder.Services.AddCors(o => o.AddPolicy("SchoolPortal", p => p
    .WithOrigins(builder.Configuration.GetSection("Cors:Origins").Get<string[]>()!)
    .AllowAnyHeader().AllowAnyMethod()));

builder.Services.AddExceptionHandler<GlobalExceptionHandler>();
builder.Services.AddProblemDetails();
builder.Services.AddRateLimiter(/* login limiter */);
builder.Services.AddHealthChecks().AddSqlServer(connectionString, tags: new[] { "ready" });
builder.Services.AddSwaggerGen(/* security definition */);

var app = builder.Build();

// ---- Pipeline — order matters ----
app.UseForwardedHeaders();
app.UseExceptionHandler();

if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}
else
{
    app.UseHsts();
}

app.UseHttpsRedirection();
app.UseSecurityHeaders();
app.UseCorrelationId();

app.UseRouting();
app.UseCors("SchoolPortal");
app.UseRateLimiter();
app.UseAuthentication();
app.UseAuthorization();

app.MapControllers();
app.MapHealthChecks("/health/live", new() { Predicate = c => c.Tags.Contains("live") });
app.MapHealthChecks("/health/ready", new() { Predicate = c => c.Tags.Contains("ready") });

app.Run();

Three orderings are load-bearing and must be defended in DECISIONS.md: UseExceptionHandler early enough to catch everything; UseCors after UseRouting and before UseAuthentication; UseAuthentication before UseAuthorization.

Submission template

DECISIONS.md

Layering:
  Project list and reference direction:
  What each layer may and may not know:
  Where DTO mapping happens:

Pipeline:
  Full middleware order:
  Why UseExceptionHandler sits where it does:
  Why UseCors sits between routing and authentication:
  What breaks if authentication and authorization are swapped:

Multi-tenant isolation:
  Where SchoolId comes from:
  Every place it is applied:
  The test that fails if it is removed:

Security:
  Password hashing algorithm and work factor:
  Token lifetime, claims, and what is deliberately NOT in the payload:
  Every Validate* flag and ClockSkew:
  FallbackPolicy, and which endpoints opt out:
  Login response strategy, and what it avoids leaking:
  CORS origins per environment:

Validation:
  Rules as annotations:
  Rules in IValidatableObject:
  Rules in the service, and why they cannot be annotations:
  Rules enforced by database constraints:

Transactions:
  Which operations are multi-statement:
  Where the boundary is and why:
  How atomicity was verified:

Errors and logging:
  Exception-to-status mapping table:
  What a 500 returns in production:
  Correlation id approach:
  What is deliberately never logged:

API contract:
  Status code per outcome, per endpoint:
  Paging envelope shape:
  Error shape:
  Versioning approach:

Testing:
  Unit tests and what they cover:
  Integration tests, especially tenant isolation:
  Postman collection contents:

Deliberately not done, and why:

Verification

It runs from nothing. Create the database from the SQL Server track's scripts, set the connection string and JWT key via user secrets, run the API. Every endpoint works with no manual setup.

Configuration fails loudly. Remove the JWT key. Confirm the application refuses to start with a message naming the setting.

Tenant isolation holds. Seed two schools with a user in each. For every endpoint, confirm a school 1 token never returns or modifies school 2 data. Then remove SchoolId from one query's WHERE and confirm an integration test fails.

Authentication and authorization. No token → 401. Expired token → 401. Token signed with a different key → 401. Teacher token on an Admin endpoint → 403, not 401. Another school's PublicId → 404, not 403.

Fallback policy works. Add a new controller with no [Authorize]. Confirm it requires authentication.

Transactions are atomic. Force a failure between the payment insert and the balance update. Confirm neither happened. Then remove transaction from the balance-update call only, repeat, and confirm the balance change survives the rollback — then restore it.

Absent results store null. Save an absent result, read the row in SSMS, confirm MarksObtained is NULL. Confirm the CHECK constraint rejects IsAbsent = 1 with marks of 0 inserted directly.

Over-posting is prevented. POST a student with an extra "schoolId": 7. Confirm it is ignored and the student belongs to the token's school.

Errors are safe. Throw an unhandled exception in Production. Confirm a generic 500 with a traceId and no stack trace, and find the full detail in the logs by that id.

No secrets in logs. Search the log output for the JWT key, any password, and the connection string password. Zero results.

CORS. Call from an allowed origin and a disallowed one. Find the OPTIONS preflight in the Network tab. Move UseCors after UseAuthentication and confirm the preflight fails with 401 — then restore it.

Rate limiting. Six login attempts in a minute → 429.

Paging. Request pageSize=100000 → 400. Page through data with duplicate names and confirm no row appears twice or is skipped.

Cancellation. Cancel a slow request mid-flight and confirm the query stops in SQL Profiler.

Postman collection. Every endpoint, with a login request that stores the token in a collection variable, and tests asserting the status code for each success and failure path.

Swagger. Authorize in the UI and call a secured endpoint successfully. Confirm every action documents its response types.

AI practice

Three AI exercises from this track's syllabus. Do each after the API works, and apply Track 18's discipline — every answer is a hypothesis until you have run it.

1. Review generated controllers for validation. Ask for a fee payment endpoint. Before running it, check four things: does schoolId come from User.FindFirst("schoolId") or from a request parameter? Is Amount validated against zero and negatives? Does it return a DTO or the entity? Is Amount a decimal? Record how many of the four it got right — that rate is what you must catch in review, permanently.
2. Ask AI to explain middleware order. Ask what breaks if UseAuthorization runs before UseAuthentication, and if UseCors runs after UseAuthentication. Then reorder your own Program.cs and confirm each prediction. One produces 401 on everything; the other produces CORS errors only on authenticated calls.
3. Generate tests, then verify every assertion. Ask for tests covering the payment endpoint. Check whether they assert the behaviour you specified or merely the behaviour the code happens to have — and confirm each one fails when you comment out the code it covers. A test that passes either way tests nothing.

Exercise 1 is the calibration one. A generated endpoint accepting [FromQuery] int schoolId returns a 200 with another school's data, and nothing anywhere reports a problem.

Track 18 — Reviewing AI-generated code — has the full checklist.

Self-assessment

Your submission is complete when someone can follow the README, create the database, run the API, exercise every endpoint from your Postman collection, and read DECISIONS.md to see which choices were deliberate.

Four specific tests of quality:

• Does an integration test fail when the tenant filter is removed? If nothing fails, the isolation is unproven — and unproven isolation is the defect this whole track is built around.
• Does the payment operation survive a failure between its two writes? And does the dropped-transaction variant demonstrably break it? Knowing the failure mode is worth as much as avoiding it.
• Does a Teacher token get 403 on an admin endpoint and 404 on another school's record? Two different answers, for two different reasons.
• Does DECISIONS.md explain the middleware order? Anyone can copy a working Program.cs; explaining why UseCors sits between routing and authentication shows you understand the pipeline.

Track completion criteria

You can build basic MVC and Razor features, create validated REST APIs, use Dapper and SQL Server, test and debug APIs, and explain basic authentication and authorization.

Specifically, you can:

• Explain every line of Program.cs and the two halves it divides into
• Order a middleware pipeline and diagnose an ordering fault from its symptom
• Configure across environments with no credential in source control
• Choose the correct DI lifetime and recognise a captive dependency
• Design an unambiguous route table and return the right status for every outcome
• Build server-rendered CRUD with Post-Redirect-Get
• Bind and validate safely, and prevent over-posting
• Design a REST contract a client can predict
• Wire Dapper repositories with tenant isolation and service-owned transactions
• Return safe errors and write logs that make an incident diagnosable
• Secure an API with JWT, policies and correctly positioned CORS
• Document it so a client developer needs nothing more

Continue to Track 11 — Angular UI Development, or Track 12 — React UI Development.