Backend, Data, and Platform Systems

C# API for collections QA (CRUD + auth)

public sealed record QaScoreRequest(
    string AccountNumber,
    string AgentId,
    int    Score,
    string Category,
    DateTimeOffset CallDateUtc);

[ApiController]
[Authorize(Policy = "CollectionsQaWrite")]
[ApiVersion("1.0")]
[Route("api/v{version:apiVersion}/qa-scores")]
public sealed class QaScoresController : ControllerBase
{
    private readonly QaScoreService _service;
    private readonly ILogger<QaScoresController> _logger;

    public QaScoresController(QaScoreService service, ILogger<QaScoresController> logger)
    {
        _service = service;
        _logger = logger;
    }

    [HttpPost]
    public async Task<IActionResult> Create(QaScoreRequest request)
    {
        if (!ModelState.IsValid)
        {
            return ValidationProblem(ModelState);
        }

        var result = await _service.StoreAsync(request, User);
        _logger.LogInformation("QaScore created {QaScoreId} for {Account}", result.Id, result.AccountNumber);

        return CreatedAtAction(nameof(GetByAccount),
            new { version = "1.0", accountNumber = result.AccountNumber }, result);
    }

    [HttpGet("{accountNumber}")]
    public async Task<ActionResult<QaScoreSummary>> GetByAccount(string accountNumber)
        => Ok(await _service.FetchByAccountAsync(accountNumber));
}

Validation, error handling, and standard responses

public sealed class QaScoreValidator : AbstractValidator<QaScoreRequest>
{
    public QaScoreValidator()
    {
        RuleFor(x => x.AccountNumber)
            .NotEmpty()
            .Length(10, 20)
            .Matches(@"^[0-9A-Z]+$");

        RuleFor(x => x.Score)
            .InclusiveBetween(0, 100);

        RuleFor(x => x.Category)
            .NotEmpty()
            .MaximumLength(32);
    }
}

app.UseExceptionHandler(appBuilder =>
{
    appBuilder.Run(async context =>
    {
        context.Response.StatusCode = StatusCodes.Status500InternalServerError;
        await context.Response.WriteAsJsonAsync(new ProblemDetails
        {
            Title = "Unexpected error",
            Detail = "Reference the trace id when reporting issues.",
            Instance = context.TraceIdentifier
        });
    });
});

Structured logging with correlation IDs

app.Use(async (context, next) =>
{
    context.Items["CorrelationId"] = Guid.NewGuid().ToString("N");
    using (LogContext.PushProperty("CorrelationId", context.Items["CorrelationId"]))
    {
        await next();
    }
});

Log.ForContext("Service", "CollectionsQa")
   .ForContext("Environment", env.EnvironmentName)
   .Information("Inbound request {Method} {Path}", context.Request.Method, context.Request.Path);

Background jobs + retry logic

services.AddHangfire(config => config.UseSqlServerStorage(connString));
services.AddHangfireServer();

RecurringJob.AddOrUpdate<QaScoreRollupJob>(
    "qa-score-rollup",
    job => job.ExecuteAsync(),
    "0 */2 * * *");

public async Task ExecuteAsync()
{
    await Policy
        .Handle<SqlException>()
        .WaitAndRetryAsync(3, retry => TimeSpan.FromSeconds(retry * 2))
        .ExecuteAsync(() => _repository.RollupDailyAsync());
}

Secure config + secrets handling

public sealed class SecretsProvider : ISecretsProvider
{
    public string GetDatabasePassword()
        => Environment.GetEnvironmentVariable("DB_PASSWORD") ?? "mock-secret";
}

builder.Configuration.AddEnvironmentVariables(prefix: "CollectionsQa_");
builder.Services.Configure<DatabaseOptions>(
    builder.Configuration.GetSection("Database"));

Config values are stored in env vars or a secrets manager. The UI below shows how values are masked for demos and logs.

Unit tests + coverage proof

public class QaScoresControllerTests
{
    [Fact]
    public async Task Create_ReturnsCreated_WhenValidRequest()
    {
        var controller = BuildController();
        var request = new QaScoreRequest("99887766", "A102", 95, "Empathy", DateTimeOffset.UtcNow);

        var response = await controller.Create(request);

        var created = Assert.IsType<CreatedAtActionResult>(response);
        Assert.Equal(201, created.StatusCode);
    }
}

// Coverage output: 92.4% (line coverage)
// Gated via CI: minimum 85% to pass build

• Unit, integration, and contract tests for all CRUD endpoints.
• Coverage reported in CI and published to dashboards.
• Negative path tests: auth failures, validation errors, and retries.

Schema design + indexing

CREATE TABLE Collections.QaScores (
    QaScoreId       INT IDENTITY(1,1) PRIMARY KEY,
    AccountNumber   VARCHAR(20)  NOT NULL,
    AgentId         VARCHAR(16)  NOT NULL,
    Score           TINYINT      NOT NULL CHECK (Score BETWEEN 0 AND 100),
    Category        VARCHAR(32)  NOT NULL,
    CallDateUtc     DATETIME2(0) NOT NULL,
    CreatedUtc      DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME(),
    CreatedBy       VARCHAR(32)  NOT NULL DEFAULT SYSTEM_USER
);

CREATE INDEX IX_QaScores_AccountDate
    ON Collections.QaScores (AccountNumber, CallDateUtc DESC);

CREATE INDEX IX_QaScores_AgentDate
    ON Collections.QaScores (AgentId, CallDateUtc DESC)
    INCLUDE (Score, Category);

Legacy to clean schema migration

INSERT INTO Collections.QaScores (
    AccountNumber,
    AgentId,
    Score,
    Category,
    CallDateUtc,
    CreatedUtc,
    CreatedBy)
SELECT
    legacy.account_no,
    legacy.agent_code,
    legacy.score_value,
    legacy.score_type,
    legacy.call_ts,
    SYSUTCDATETIME(),
    'migration-script'
FROM Legacy.QaScoreDump legacy
WHERE legacy.score_value BETWEEN 0 AND 100;

• Data quality checks before load with reject tables for invalid scores.
• Batch size control for predictable migration windows.
• Hash-based reconciliation to verify record parity.

Reporting layer + KPI views

CREATE VIEW Reporting.QaScoresDaily AS
SELECT
    CAST(CallDateUtc AS DATE)  AS CallDate,
    AgentId,
    COUNT(*)                   AS TotalCalls,
    AVG(CAST(Score AS DECIMAL(5,2))) AS AvgScore,
    SUM(CASE WHEN Score >= 90 THEN 1 ELSE 0 END) AS HighScores
FROM Collections.QaScores
GROUP BY CAST(CallDateUtc AS DATE), AgentId;

Power BI style dashboard KPIs

• QA Score: Average QA score for the day, target ≥ 90.
• Compliance Score: % of calls passing policy checks.
• Time Saved: Manual reporting hours eliminated per week.
• Audit Coverage: % of agents reviewed each month.

These KPIs are modeled in SQL views and exposed to Power BI for operational dashboards and executive reporting.

Admin portal + role-based access control

services.AddAuthorization(options =>
{
    options.AddPolicy("AdminOnly", policy =>
        policy.RequireRole("Admin", "ComplianceLead"));
});

[Authorize(Policy = "AdminOnly")]
[HttpPut("users/{userId}/roles")]
public async Task<IActionResult> UpdateRoles(string userId, RoleUpdateRequest request)
    => Ok(await _roleService.UpdateRolesAsync(userId, request));

• RBAC enforced at endpoint and UI layers.
• Admin portal surfaces role assignments, permissions, and escalation contacts.
• Privileged actions require MFA confirmation and audit logging.

Dynamic form builder + scoring logic

{
  "formId": "qa-scorecard-v4",
  "sections": [
    {
      "title": "Call flow adherence",
      "questions": [
        { "id": "greeting", "type": "yesNo", "weight": 10 },
        { "id": "disclosures", "type": "yesNo", "weight": 15 }
      ]
    },
    {
      "title": "Customer experience",
      "questions": [
        { "id": "empathy", "type": "rating", "max": 5, "weight": 20 },
        { "id": "resolution", "type": "rating", "max": 5, "weight": 20 }
      ]
    }
  ]
}

• Form definitions stored as JSON to enable rapid updates.
• Weighted scoring logic calculates overall QA scores.
• Conditional questions triggered by previous responses.

Audit log (who changed what and when)

INSERT INTO AuditLog (
    EntityType, EntityId, Action, ChangedBy, ChangedUtc, DetailJson)
VALUES (
    'QaScoreForm', 'qa-scorecard-v4', 'UPDATE', @userId, SYSUTCDATETIME(), @detailJson);

Recent audit trail entries:

[2025-01-08 09:02] jlee updated form qa-scorecard-v4 (sections=2, questions=6)
[2025-01-08 09:11] kpatel updated role compliance_lead (added audit.write)
[2025-01-08 09:21] mrobinson toggled feature flag new-qa-dashboard ON

Feature flags + config driven behavior

public sealed class FeatureFlagService
{
    private readonly IDictionary<string, bool> _flags;

    public FeatureFlagService(IOptions<FeatureFlagOptions> options)
        => _flags = options.Value.Flags;

    public bool IsEnabled(string name) => _flags.TryGetValue(name, out var isOn) && isOn;
}

• Flags scoped by environment and business unit.
• Rollout rules tied to user roles and cohorts.
• Feature exposure tracked in audit logs.

CI pipeline YAML (build, test, lint)

name: ci
on:
  pull_request:
  push:
    branches: [ main ]
jobs:
  build:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-dotnet@v4
        with:
          dotnet-version: "8.0.x"
      - run: dotnet restore
      - run: dotnet build --configuration Release
      - run: dotnet test /p:CollectCoverage=true /p:CoverletOutputFormat=lcov
      - run: dotnet format --verify-no-changes

Deployment steps

1. Merge to main after CI passes and code review approval.
2. Tag release: v1.x.y for API version compatibility.
3. Deploy to staging with smoke tests and monitoring checks.
4. Promote to production after stakeholder approval.
5. Post-deploy validation: API health, job status, and logs.

Code quality checks

• Static analysis: analyzers + security rules.
• Style checks: dotnet format + linting gates.
• Coverage thresholds to block regression.
• Performance regression tests for reporting queries.

Branching strategy

main
├── feature/qa-score-endpoints
├── feature/rollup-jobs
├── hotfix/auth-timeout
└── release/v1.4.0

• Short-lived feature branches with PR reviews.
• Release branches for controlled deployments.
• Hotfix branches for rapid production fixes.

Authorization checks and least privilege

services.AddAuthorization(options =>
{
    options.AddPolicy("QaRead", policy =>
        policy.RequireClaim("permission", "qa.read"));

    options.AddPolicy("QaWrite", policy =>
        policy.RequireClaim("permission", "qa.write"));
});

[Authorize(Policy = "QaRead")]
[HttpGet("qa-scores")]
public Task<IActionResult> GetScores() => _service.FetchAllAsync();

Secure logging + data masking

public static string MaskSsn(string ssn)
    => ssn?.Length == 9 ? $"***-**-{ssn[^4..]}" : "***-**-****";

_logger.LogInformation(
    "Account lookup {Account} requested by {User}",
    MaskAccount(request.AccountNumber),
    userId);

SELECT
    AccountNumber,
    CONCAT('***-**-', RIGHT(Ssn, 4)) AS MaskedSsn,
    DateOfBirth
FROM Customers;

Audit readiness checklist

• Access logs retained for 12+ months with immutable storage.
• Role reviews quarterly, with approvals logged.
• PII masking enforced in logs and reports.
• Incident response runbooks attached to on-call rotations.
• Evidence packs generated per release and per audit.

Input validation guardrails

if (!Regex.IsMatch(request.AccountNumber, @"^[0-9A-Z]+$"))
{
    return BadRequest(new { error = "Invalid account number format." });
}

if (request.Score < 0 || request.Score > 100)
{
    return BadRequest(new { error = "Score must be between 0 and 100." });
}