feat: Asynchronous Popularity Report Export

[fernandotonacoder]

feat: Asynchronous Popularity Report Export

Description

Implement a resilient, asynchronous report generation system. This feature offloads heavy data aggregation from the API to a background Worker Service (MusicAlbums.Worker), using the Transactional Outbox pattern — via MassTransit's built-in Outbox over Azure Service Bus — to guarantee that every export request is eventually processed, even during infrastructure outages.

Proposed Changes

• Database (PostgreSQL/Dapper):

  • Add a Reports table to track status (Pending, Processing, Completed, Failed) and store the Blob URL.
  • Add an OutboxMessages table to store ExportRequested events (managed by MassTransit's Outbox).

• API (MusicAlbums.Api):

  • Add ReportsController with two endpoints:
    • POST /reports/popularity — accepts the request, writes to Reports and OutboxMessages in a single Dapper transaction, returns 202 Accepted with a reportId and status: "Pending".
    • GET /reports/{reportId} — returns the current status and downloadUrl once the report is ready.

• Worker Service (MusicAlbums.Worker):

  • New .NET Worker Service project hosted as a second Azure Container App in the same environment.
  • Uses MassTransit with Azure Service Bus as the transport.
  • MassTransit's built-in Outbox polls OutboxMessages, publishes ExportRequested to the report-requests queue, and marks messages as processed — no custom relay logic needed.
  • ExportRequestedConsumer receives the message, runs the popularity aggregation query, uploads the resulting CSV to Azure Blob Storage, and updates the Reports record to Completed with the downloadUrl.

• Infrastructure (Azure/Bicep):

  • Provision an Azure Service Bus namespace and queue (report-requests).
  • Provision an Azure Blob Storage container (reports).
  • Add a second Azure Container App for MusicAlbums.Worker within the existing Container Apps environment.

Architecture Flow

POST /reports/popularity
  → Insert Report (Pending) + OutboxMessage in one DB transaction
  → Return 202 Accepted { reportId, status: "Pending" }

MassTransit Outbox (Worker)
  → Polls OutboxMessages table
  → Publishes ExportRequested → Azure Service Bus (report-requests queue)

ExportRequestedConsumer (Worker)
  → Runs popularity aggregation query (PostgreSQL/Dapper)
  → Uploads CSV → Azure Blob Storage
  → Updates Report → { status: "Completed", downloadUrl }

GET /reports/{reportId}
  → Returns { reportId, status, downloadUrl? }

BDD Scenarios

Scenario 1: Successfully initiating a report

Given a valid authenticated user

When I send a POST request to /reports/popularity

Then the API should return 202 Accepted

And the response body should contain a unique reportId and status: "Pending"

---

Scenario 2: Ensuring reliability via Outbox

Given the Azure Service Bus is temporarily unavailable

When I request a popularity report

Then the API should still return 202 Accepted

And the request must be persisted in the OutboxMessages table to be relayed once connectivity is restored

---

Scenario 3: Polling for report status

Given a report has been initiated and a reportId was returned

When I send a GET request to /reports/{reportId}

Then the API should return the current status of the report

And if the report is completed, the downloadUrl field should contain a valid link to the file in Blob Storage

---

Scenario 4: Completing the export

Given a report request has been picked up by the ExportRequestedConsumer

When the data aggregation and Blob upload are finished

Then the record in the Reports table should be updated to status: "Completed"

And the downloadUrl field should contain a valid link to the file in Blob Storage