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.
POST /qualitycheck/api/inspections202 right awayquality-inspections holds results until they're storedinspection, read by the API for results and yieldQualityCheck.Cleanup runs every night and deletes old resultsBefore 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
| Package | Kind | Its job |
|---|---|---|
QualityCheck.Api | API | 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 | Takes results off the Queue, grades each one pass or fail, and writes it to the inspection Table. |
QualityCheck.Cleanup | Function | 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 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:
- Under Packages, install
TrakHound.Memory.ServiceandTrakHound.Sqlite.Service. - Under Config > Services, add a Memory Service with the ID
qualitycheck-queue. - Add a SQLite Service with the ID
qualitycheck-db, and set itsdatabaseparameter to a full file path, such asC:\TrakHound\Data\qualitycheck.db. -
Under Config > Routers, add a Router named
qualitycheckwith two Routes: one with the patternMessageQueuesthat targetsqualitycheck-queue, and one with the patternTablesthat targetsqualitycheck-db. Click Save. - Under Config > Groups, click Add Group with the ID
qualitycheck, and set its Router toqualitycheck.
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.
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; }
}
}
AddInspectionreturns 202 Accepted, not 200: the result is on the Queue, but isn't stored yet. ThemessageIdit returns becomes the row'sid.- The queries use the
ansisubset with@stationand@fromparameters, 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),
Errorreturns 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:
Pulltakes up to 50 results. They stay on the Queue, hidden from other consumers, until they're acknowledged or rejected.- All of them are written in one
Execute.overwrite: truereplaces a row with the sameId, so a result that's delivered twice is stored once. Acknowledgeremoves them from the Queue only after the write succeeds. If the write fails,Rejectwithrequeue: trueputs them back to try again.- 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.
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:
- In the Explorer, stop
QualityCheck.Worker. - Send a few more results. The API still answers 202 for each one, but
yielddoesn't change, because nothing is storing them. - Start the Worker again. Within a second, it takes the waiting results off the Queue, and
yieldincludes 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.
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. |
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
AddInspection.
Limit What It Can Reach
Add the Group to a Zone, so its modules can only reach their own Services.
Add a Dashboard
Show yield by station on a live page with an App, as in Build a Sensor Dashboard.