# Process Results with a Message Queue

> Take inspection results from stations through a Message Queue, store them in a Table with a Worker, and clean them up with a scheduled Function.

URL: https://www.trakhound.com/docs/trakhound-framework/tutorials/quality-check

In this tutorial you'll build an application that takes inspection results from stations on the shop floor, grades them, and stores them in a database. Stations send results to an API, which puts them on a Message Queue and answers right away. A Worker takes them off the Queue and stores them, and a Function runs every night to delete old results. It takes about 30 minutes.

Diagram: Stations post inspection results to the API, which puts each one on the quality-inspections Message Queue and answers right away. The Worker takes results off the Queue, grades them, and writes them to the inspection Table. The API reads the Table to list results and calculate yield. Every night a Scheduled Task runs the Cleanup Function, which deletes old results. The qualitycheck Group's Router sends the Queue to the Memory Service and the Table to the SQLite Service.

## Before You Start

Complete [Build a Sensor Dashboard](https://www.trakhound.com/docs/trakhound-framework/tutorials/sensor-dashboard) first. This tutorial uses the same tools, and it doesn't repeat how to create projects, link a shared file, or build a Package. You'll also need the admin account for your Instance, and a terminal you can run as administrator if the Instance is installed as a Windows Service.

## What You'll Build

| Package | Kind | Its job |
| --- | --- | --- |
| `QualityCheck.Api` | [API](https://www.trakhound.com/docs/trakhound-framework/apis) | Takes results from stations and puts them on the `quality-inspections` Queue. Lists results and calculates first pass yield from the Table. |
| `QualityCheck.Worker` | [Worker](https://www.trakhound.com/docs/trakhound-framework/workers) | Takes results off the Queue, grades each one pass or fail, and writes it to the `inspection` Table. |
| `QualityCheck.Cleanup` | [Function](https://www.trakhound.com/docs/trakhound-framework/functions) | Deletes results older than a number of days. A Scheduled Task runs it every night. |

Putting a Message Queue between the API and the database means:

- **Stations don't wait** - The API answers as soon as a result is on the Queue, however busy the database is.
- **Nothing is lost while the Worker is stopped** - Results wait on the Queue while you update or restart the Worker, or while the database is down, and are stored when it's back.
- **Each result is stored once** - The Worker removes a result from the Queue only after it's stored. If the Worker stops first, the result is delivered again, and it's written to the same row.

## 1. Set Up the Services

The Worker and API use [Services](https://www.trakhound.com/docs/trakhound-framework/services) for the Queue and the Table, and never connect to a broker or database themselves. For this tutorial, use the Memory Service for the Queue and SQLite for the Table, which need no servers. In the admin UI:

1. Under **Packages**, install `TrakHound.Memory.Service` and `TrakHound.Sqlite.Service`.
2. Under **Config > Services**, add a Memory Service with the ID `qualitycheck-queue`.
3. Add a SQLite Service with the ID `qualitycheck-db`, and set its `database` parameter to a full file path, such as `C:\TrakHound\Data\qualitycheck.db`.
4. Under **Config > Routers**, add a Router named `qualitycheck` with two Routes: one with the pattern `MessageQueues` that targets `qualitycheck-queue`, and one with the pattern `Tables` that targets `qualitycheck-db`. Click **Save**.
5. Under **Config > Groups**, click **Add Group** with the ID `qualitycheck`, and set its **Router** to `qualitycheck`.

Every module you put in the `qualitycheck` [Group](https://www.trakhound.com/docs/trakhound-framework/configuration#groups) sends its Service calls through that Router, without setting a Router on each one. **Map** on the Routers page shows where each kind of call goes.

> The Memory Service keeps the Queue in memory, so results still on the Queue are lost if the Instance restarts. In production, add a `TrakHound.RabbitMq.Service` and a SQL Server or PostgreSQL Service, and change the Router's Targets. The Packages don't change.

## 2. Create the Projects

```
mkdir QualityCheck
cd QualityCheck
dotnet new trakhound.api -n QualityCheck.Api
dotnet new trakhound.worker -n QualityCheck.Worker
dotnet new trakhound.function -n QualityCheck.Cleanup
```

Create the shared model in `QualityCheck.Worker/Inspection.cs`. `InspectionResult` is what a station sends, and `InspectionRecord` is a row of the `inspection` Table. Its attributes describe the Table, so the Worker can create it on any database.

```
using System;
using TrakHound.Services.Tables.Attributes;

namespace QualityCheck
{
    // What a station sends to the API for each part it measures
    public class InspectionResult
    {
        public string Station { get; set; }

        public string PartId { get; set; }

        public double Measurement { get; set; }

        public DateTime Timestamp { get; set; }
    }

    // A graded result, stored as a row of the "inspection" Table
    [Table("inspection")]
    [TableIndex("ix_inspection_timestamp", "timestamp desc")]
    public class InspectionRecord
    {
        // The Message ID from the Queue, so a Message that's delivered twice writes the same row
        [PrimaryKey]
        [Column(Length = 100)]
        public string Id { get; set; }

        [Column(Length = 100)]
        public string Station { get; set; }

        [Column(Length = 100)]
        public string PartId { get; set; }

        public double Measurement { get; set; }

        // "pass" or "fail"
        [Column(Length = 10)]
        public string Result { get; set; }

        public DateTime Timestamp { get; set; }
    }

    public static class Inspection
    {
        // The Message Queue that the API publishes to and the Worker reads from
        public const string QueueName = "quality-inspections";
    }
}
```

Link it into `QualityCheck.Api.csproj` and `QualityCheck.Cleanup.csproj`:

```
<ItemGroup>
    <Compile Include="..\QualityCheck.Worker\Inspection.cs" Link="Inspection.cs" />
</ItemGroup>
```

## 3. Write the API

Replace `QualityCheck.Api/Controller.cs`:

```
using System;
using System.Linq;
using System.Threading.Tasks;
using QualityCheck;
using TrakHound;
using TrakHound.Api;
using TrakHound.Clients.Services;
using TrakHound.Services.Tables;

public class Controller : TrakHoundApiController
{
    [ApiPost("inspections", Description = "Adds an inspection result from a station. It's graded and stored in the background.")]
    public async Task<TrakHoundApiResponse> AddInspection(
        [FromBody(ContentType = "application/json", Description = "The station, part ID, and measurement")] InspectionResult result)
    {
        if (result == null || string.IsNullOrEmpty(result.Station) || string.IsNullOrEmpty(result.PartId))
        {
            return BadRequest("station and partId are required");
        }

        if (result.Timestamp == default) result.Timestamp = DateTime.UtcNow;

        var response = await Client.Services.MessageQueues.Publish(Inspection.QueueName, result);
        if (!response.Success) return Error(response.StatusCode, response.Message);

        // 202 Accepted : the result is on the Queue, but not stored yet
        return Accepted(new { messageId = response.Result?.MessageId });
    }

    [ApiGet("inspections", Description = "Lists the most recent graded inspection results, newest first.")]
    [ApiMcpTool]
    public async Task<TrakHoundApiResponse> GetInspections(
        [FromQuery(Description = "Only this station (every station when empty)")] string station = null,
        [FromQuery(Description = "The most results to return (1 to 1,000)")] int take = 50)
    {
        var parameters = new TrakHoundTableParameterCollection();
        var where = "";
        if (!string.IsNullOrEmpty(station))
        {
            where = "where \"station\" = @station ";
            parameters.Add("station", station);
        }

        var query = $"select * from \"inspection\" {where}order by \"timestamp\" desc;";
        var response = await Client.Services.Tables.ReadMultiple<InspectionRecord>(TrakHoundTableRequest.Ansi(query, parameters, skip: 0, take: Math.Clamp(take, 1, 1000)));

        return response.Success ? Ok(response.Result) : Error(response.StatusCode, response.Message);
    }

    [ApiGet("yield", Description = "Gets how many parts each station inspected, passed, and failed, and its first pass yield.")]
    [ApiMcpTool]
    public async Task<TrakHoundApiResponse> GetYield(
        [FromQuery(Description = "How many hours back to include")] int hours = 8)
    {
        var parameters = new TrakHoundTableParameterCollection();
        parameters.Add("from", DateTime.UtcNow.AddHours(-Math.Max(hours, 1)));

        var query = "select \"station\", count(*) as \"total\", sum(case when \"result\" = 'pass' then 1 else 0 end) as \"passed\" " +
                    "from \"inspection\" where \"timestamp\" >= @from group by \"station\" order by \"station\";";

        var response = await Client.Services.Tables.ReadMultiple<StationCount>(TrakHoundTableRequest.Ansi(query, parameters));
        if (!response.Success) return Error(response.StatusCode, response.Message);

        return Ok(response.Result.Select(o => new
        {
            station = o.Station,
            total = o.Total,
            passed = o.Passed,
            failed = o.Total - o.Passed,
            yield = o.Total > 0 ? Math.Round(100.0 * o.Passed / o.Total, 1) : 0
        }));
    }

    // Returns a Service's failure (ex. 503 when the database can't be reached) to the caller
    private static TrakHoundApiResponse Error(int statusCode, string message)
    {
        return new TrakHoundApiResponse(statusCode, (message ?? $"Error {statusCode}").ToUtf8Bytes(), "text/plain");
    }

    private class StationCount
    {
        public string Station { get; set; }
        public int Total { get; set; }
        public int Passed { get; set; }
    }
}
```

- `AddInspection` returns **202 Accepted**, not 200: the result is on the Queue, but isn't stored yet. The `messageId` it returns becomes the row's `id`.
- The queries use the `ansi` subset with `@station` and `@from` parameters, so they run unchanged on SQLite, SQL Server, and PostgreSQL. Never put a caller's input into the SQL itself.
- When a Service fails (ex. **503** while the database is down), `Error` returns its status code to the caller, so a station can tell a bad request from an outage.

## 4. Write the Worker

Replace `QualityCheck.Worker/Worker.cs`:

```
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using QualityCheck;
using TrakHound;
using TrakHound.Services.MessageQueues;
using TrakHound.Services.Tables;
using TrakHound.Workers;

public class Worker : TrakHoundWorker
{
    private const int BatchSize = 50;

    private double _lowerLimit;
    private double _upperLimit;

    private CancellationTokenSource _stop;
    private Task _loop;


    public Worker(ITrakHoundWorkerConfiguration configuration) : base(configuration) { }


    protected override Task OnStartAsync()
    {
        // The tolerance a measurement must be within to pass
        _lowerLimit = Configuration.GetParameter<double>("lowerLimit");
        _upperLimit = Configuration.GetParameter<double>("upperLimit");
        if (_upperLimit <= _lowerLimit)
        {
            _lowerLimit = 9.95;
            _upperLimit = 10.05;
        }

        _stop = new CancellationTokenSource();
        _loop = Run(_stop.Token);
        return Task.CompletedTask;
    }

    protected override async Task OnStopAsync()
    {
        _stop.Cancel();
        await _loop;
        _stop.Dispose();
    }

    private async Task Run(CancellationToken cancellationToken)
    {
        // Create the Table before reading the Queue
        while (!cancellationToken.IsCancellationRequested && !await EnsureTable())
        {
            await Delay(TimeSpan.FromSeconds(10), cancellationToken);
        }

        while (!cancellationToken.IsCancellationRequested)
        {
            try
            {
                var response = await Client.Services.MessageQueues.Pull(Inspection.QueueName, count: BatchSize, cancellationToken: cancellationToken);
                if (!response.Success)
                {
                    Log(TrakHoundLogLevel.Warning, $"Unable to read the Queue : {response.StatusCode} : {response.Message}");
                    await Delay(TimeSpan.FromSeconds(10), cancellationToken);
                    continue;
                }

                var messages = response.Result?.ToList() ?? new List<TrakHoundQueueMessage>();
                if (messages.Count == 0)
                {
                    // The Queue is empty, so check again shortly
                    await Delay(TimeSpan.FromSeconds(1), cancellationToken);
                    continue;
                }

                await Process(messages);
            }
            catch (OperationCanceledException) { }
            catch (Exception ex)
            {
                // Log and continue, so one failure doesn't stop the Worker
                Log(TrakHoundLogLevel.Error, ex.Message);
                await Delay(TimeSpan.FromSeconds(10), cancellationToken);
            }
        }
    }

    private async Task Process(List<TrakHoundQueueMessage> messages)
    {
        var records = new List<InspectionRecord>();
        var valid = new List<string>();
        var invalid = new List<string>();

        foreach (var message in messages)
        {
            var result = message.GetJsonContentObject<InspectionResult>();
            if (result == null || string.IsNullOrEmpty(result.Station) || string.IsNullOrEmpty(result.PartId))
            {
                // A Message that can never be stored is discarded, so it doesn't come back
                Log(TrakHoundLogLevel.Warning, $"Discarding invalid Message {message.MessageId}");
                invalid.Add(message.DeliveryId);
                continue;
            }

            records.Add(new InspectionRecord
            {
                Id = message.MessageId,
                Station = result.Station,
                PartId = result.PartId,
                Measurement = result.Measurement,
                Result = result.Measurement >= _lowerLimit && result.Measurement <= _upperLimit ? "pass" : "fail",
                Timestamp = result.Timestamp != default ? result.Timestamp.ToUniversalTime() : DateTime.UtcNow
            });
            valid.Add(message.DeliveryId);
        }

        if (invalid.Count > 0) await Client.Services.MessageQueues.Reject(Inspection.QueueName, invalid, requeue: false);
        if (records.Count == 0) return;

        // overwrite replaces a row with the same Id, so a redelivered Message isn't stored twice
        var builder = new TrakHoundTableRequestBuilder();
        builder.AppendInsert(records, overwrite: true);
        var write = await Client.Services.Tables.Execute(builder.Build());

        if (write.Success)
        {
            // Remove the Messages from the Queue only once they're stored
            await Client.Services.MessageQueues.Acknowledge(Inspection.QueueName, valid);
        }
        else
        {
            // Return them to the Queue to try again
            Log(TrakHoundLogLevel.Warning, $"Unable to store {records.Count} results : {write.StatusCode} : {write.Message}");
            await Client.Services.MessageQueues.Reject(Inspection.QueueName, valid, requeue: true);
            await Task.Delay(TimeSpan.FromSeconds(5));
        }
    }

    private async Task<bool> EnsureTable()
    {
        var builder = new TrakHoundTableRequestBuilder();
        builder.AppendEnsureTable<InspectionRecord>();

        var response = await Client.Services.Tables.Execute(builder.Build());
        if (!response.Success) Log(TrakHoundLogLevel.Warning, $"Unable to create the inspection Table : {response.StatusCode} : {response.Message}");
        return response.Success;
    }

    private static async Task Delay(TimeSpan delay, CancellationToken cancellationToken)
    {
        try { await Task.Delay(delay, cancellationToken); }
        catch (OperationCanceledException) { }
    }
}
```

The order of the calls is what keeps each result safe:

1. `Pull` takes up to 50 results. They stay on the Queue, hidden from other consumers, until they're acknowledged or rejected.
2. All of them are written in one `Execute`. `overwrite: true` replaces a row with the same `Id`, so a result that's delivered twice is stored once.
3. `Acknowledge` removes them from the Queue only after the write succeeds. If the write fails, `Reject` with `requeue: true` puts them back to try again.
4. A result that can never be stored (ex. it has no station) is rejected with `requeue: false`, so it doesn't come back forever.

The pass limits are the `lowerLimit` and `upperLimit` parameters of the Worker's configuration (9.95 and 10.05 if they aren't set), so quality engineers can change them without a new build.

## 5. Write the Function

Replace `QualityCheck.Cleanup/Function.cs`:

```
using System;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using QualityCheck;
using TrakHound;
using TrakHound.Functions;
using TrakHound.Services.Tables;

[FunctionParameter("days", Description = "How many days of inspection results to keep", Type = TrakHoundFunctionParameterType.Integer, DefaultValue = "30")]
public class Function : TrakHoundFunction
{
    public Function(ITrakHoundFunctionConfiguration configuration) : base(configuration) { }

    protected override async Task<TrakHoundFunctionResponse> OnRun(IReadOnlyDictionary<string, string> parameters, CancellationToken cancellationToken)
    {
        if (!int.TryParse(parameters.GetValueOrDefault("days"), out var days) || days <= 0)
        {
            return BadRequest("days must be a whole number greater than 0");
        }

        var cutoff = DateTime.UtcNow.AddDays(-days);

        var builder = new TrakHoundTableRequestBuilder();
        builder.AppendQuery("delete from \"inspection\" where \"timestamp\" < @cutoff;");
        builder.Parameters.Add("cutoff", cutoff);

        var response = await Client.Services.Tables.Execute(builder.Build(), cancellationToken: cancellationToken);
        if (!response.Success) return InternalError($"Unable to delete : {response.StatusCode} : {response.Message}");

        Log(TrakHoundLogLevel.Information, $"Deleted the inspection results from before {cutoff:o}");
        return Ok($"Deleted inspection results older than {days} days");
    }
}
```

## 6. Build and Install

From the `QualityCheck` folder, build the three Packages:

```
th packages dotnet QualityCheck.Api --output ./out
th packages dotnet QualityCheck.Worker --output ./out
th packages dotnet QualityCheck.Cleanup --output ./out
```

Install them with the CLI so their configurations are added straight into the `qualitycheck` Group. `--path` is the folder with the Instance's `packages` and `config` folders (`C:\ProgramData\TrakHound\Instance` for the Windows installer, see [Installation](https://www.trakhound.com/docs/trakhound-framework/installation)), and the terminal must be running as administrator to write to it:

```
th packages install --file ./out/QualityCheck.Api-0.0.1.thp --configure --group qualitycheck --path "C:\ProgramData\TrakHound\Instance"
th packages install --file ./out/QualityCheck.Worker-0.0.1.thp --configure --group qualitycheck --path "C:\ProgramData\TrakHound\Instance"
th packages install --file ./out/QualityCheck.Cleanup-0.0.1.thp --configure --group qualitycheck --path "C:\ProgramData\TrakHound\Instance"
```

The Instance loads them within a few seconds. Open **Config > Groups > qualitycheck** to see the API (at `/qualitycheck/api`), the Worker, and the Function in it.

> You can upload the Packages under **Packages > Upload** instead, but their configurations aren't in a Group, so they use the `default` Router until you add them to `qualitycheck` with **Add Module** on the Group's page. Add them before sending any results, then restart the Worker from the Explorer.

## 7. Send Results

Send a result the way a station would:

```
curl -X POST http://localhost:8472/qualitycheck/api/inspections ^
     -H "Content-Type: application/json" ^
     -d "{\"station\":\"op-10\",\"partId\":\"P0001\",\"measurement\":10.02}"
{"messageId":"c2e248aa-193d-4738-bbf8-79061d460f67"}
```

These commands use Windows Command Prompt line breaks (`^`); use `\` in Bash. Send a few more for `op-10` and `op-20`, with some measurements outside 9.95 to 10.05, such as `10.08`. Then read them back:

```
curl "http://localhost:8472/qualitycheck/api/inspections?take=3"

curl http://localhost:8472/qualitycheck/api/yield
[
  { "station": "op-10", "total": 10, "passed": 7, "failed": 3, "yield": 70 },
  { "station": "op-20", "total": 10, "passed": 5, "failed": 5, "yield": 50 }
]
```

Both GET endpoints are [MCP Tools](https://www.trakhound.com/docs/trakhound-framework/mcp-server), so an AI assistant connected to the Instance can answer questions like "Which station had the lowest yield this shift?". `AddInspection` isn't one, so an assistant can't add results.

## 8. Stop the Worker

See the Queue do its job:

1. In the **Explorer**, stop `QualityCheck.Worker`.
2. Send a few more results. The API still answers **202** for each one, but `yield` doesn't change, because nothing is storing them.
3. Start the Worker again. Within a second, it takes the waiting results off the Queue, and `yield` includes them.

This is also what happens when you install a new version of the Worker, or the database is down for a few minutes: stations keep working, and nothing is lost.

## 9. Schedule the Cleanup

Open `QualityCheck.Cleanup` under **Explorer > Functions** and click **Run** with the default `days` of 30. It deletes nothing yet, and the run is recorded with its log. Then, on its **Schedule** tab, add a [Scheduled Task](https://www.trakhound.com/docs/trakhound-framework/functions#scheduled-tasks) with the schedule `0 2 * * *` to run it every night at 2:00 AM.

> Runs, Jobs, and the Schedule tab need the `TrakHound.Functions.Api` and `TrakHound.ScheduledTasks` Packages. Install them from **Packages** if the tab isn't there. A Scheduled Task without a Job runs the Function with no parameters, which is why `days` has a default.

## Troubleshooting

| Problem | What to do |
| --- | --- |
| The API returns **405**, or the Worker logs *Unable to read the Queue : 405* | The module's Router has no Service for that kind of data. Check that the module is in the `qualitycheck` Group, that its own configuration doesn't name a Router (which overrides the Group's), and that the Router's Routes are saved. |
| The Worker logs *Unable to create the inspection Table* | The SQLite Service can't open its `database` file. Use a full path in a folder the Instance's account can write to. |
| Results are accepted but never stored | Check that the Worker is running and read its log in the Explorer. Every module that uses the Queue must use the same Router, or they're using different Queues. |
| `th packages install` is denied access | Run the terminal as administrator, or upload the Packages in the admin UI as described in step 6. |

> Running these projects on your computer with `dotnet run` works the same way, but the debug host's Service calls use the Instance's `default` Router, not the Group's. Add the same two Routes to `default` on a development Instance, or debug inside the Instance with [`th dev run`](https://www.trakhound.com/docs/trakhound-framework/debugging#dev-run).

## Next Steps

[Move to Production Services Point the Router at RabbitMQ and SQL Server, with fallback Targets for when a server is down.](https://www.trakhound.com/docs/trakhound-framework/routing) [Give Each Station a Key Require an API key to add results, and let only the stations' keys call `AddInspection`.](https://www.trakhound.com/docs/trakhound-framework/security) [Limit What It Can Reach Add the Group to a Zone, so its modules can only reach their own Services.](https://www.trakhound.com/docs/trakhound-framework/zones) [Add a Dashboard Show yield by station on a live page with an App, as in Build a Sensor Dashboard.](https://www.trakhound.com/docs/trakhound-framework/tutorials/sensor-dashboard)
