TrakHound Docs TrakHound Framework Process Results with a Message Queue

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.
Framework Docs Menu

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.

StationsGauges, test benches, or any system that can send HTTP
POST /qualitycheck/api/inspections
Group: qualitycheck
APIQueues each result and answers 202 right away
Message Queuequality-inspections holds results until they're stored
WorkerGrades each result pass or fail, and acknowledges it once it's stored
The Worker writes, the API reads, and the Function deletes
Tableinspection, read by the API for results and yield
FunctionQualityCheck.Cleanup runs every night and deletes old results
The Group's Router decides where data goes: the Queue goes to the Memory Service and the Table to the SQLite Service. In production, point the Router at RabbitMQ and SQL Server without changing any code.
What you'll build: a station never waits for the database, and no result is lost while the Worker is stopped.

Before You Start

Complete Build a 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

PackageKindIts job
QualityCheck.ApiAPITakes results from stations and puts them on the quality-inspections Queue. Lists results and calculates first pass yield from the Table.
QualityCheck.WorkerWorkerTakes results off the Queue, grades each one pass or fail, and writes it to the inspection Table.
QualityCheck.CleanupFunctionDeletes 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 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 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), 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, 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 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

ProblemWhat 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.

Next Steps