In this tutorial you'll build a time clock for the shop floor. Operators enter their badge number and a job on a station screen, and start and stop work on it. An API stores each stretch of time in a database Table, shows who is working right now, and totals the hours on a job. It takes about 20 minutes.
/timeclock on a station screen: badge, job, Start, Stop/timeclock/api starts and stops jobs, and reports hourstime_entry in SQLite, through the timeclock Group's Router/timeclock/api/jobs/{job}, and a barcode scanner could start jobs without the App.
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 and upload a Package.
What You'll Build
| Package | Kind | Its job |
|---|---|---|
TimeClock.Api | API | Starts and stops jobs, lists who is working, and totals the hours on a job. Stores each time entry in the time_entry Table. |
TimeClock.App | App | The station screen at /timeclock: a badge and a job, Start and Stop, and who is working now. |
Each time entry has an operator, a job, a start time, and an end time, which is empty while the operator is still working. When an operator starts a new job, the API stops the one they were on, so nobody is ever clocked onto two jobs at once.
1. Set Up the Database
The API stores time entries through a Table Service, so it never connects to a database itself. For this tutorial, use SQLite, which needs no database server. In the admin UI:
- Under Packages, install
TrakHound.Sqlite.Service. - Under Config > Services, add a SQLite Service with the ID
timeclock-db, and set itsdatabaseparameter to a full file path, such asC:\TrakHound\Data\timeclock.db. - Under Config > Routers, add a Router named
timeclockwith one Route, with the patternTables, that targetstimeclock-db. Click Save. - Under Config > Groups, click Add Group with the ID
timeclock, and set its Router totimeclock.
Modules in the timeclock Group send their Table requests to SQLite. To use SQL Server or PostgreSQL later,
add its Service and change the Router's Target. The Packages don't change.
2. Create the Projects
mkdir TimeClock
cd TimeClock
dotnet new trakhound.api -n TimeClock.Api
dotnet new trakhound.app -n TimeClock.App
Create the time entry in TimeClock.Api/TimeEntry.cs. Its attributes describe the Table, so the API can create it on any database:
using System;
using TrakHound.Services.Tables.Attributes;
namespace TimeClock
{
// One stretch of time an operator worked on a job, stored as a row of the "time_entry" Table
[Table("time_entry")]
[TableIndex("ix_time_entry_started_at", "started_at desc")]
public class TimeEntry
{
[PrimaryKey]
[Column(Length = 50)]
public string Id { get; set; }
// The operator's badge number
[Column(Length = 50)]
public string Operator { get; set; }
[Column(Length = 50)]
public string Job { get; set; }
public DateTime StartedAt { get; set; }
// Empty while the operator is still working on the job
public DateTime? EndedAt { get; set; }
// A method, not a property, so it isn't stored as a Column
public double GetHours() => Math.Round(((EndedAt ?? DateTime.UtcNow) - StartedAt).TotalHours, 2);
}
// What the station sends to start or stop
public class ClockRequest
{
public string Operator { get; set; }
public string Job { get; set; }
}
}
Link it into TimeClock.App.csproj:
<ItemGroup>
<Compile Include="..\TimeClock.Api\TimeEntry.cs" Link="TimeEntry.cs" />
</ItemGroup>
3. Write the API
Replace TimeClock.Api/Controller.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using TimeClock;
using TrakHound;
using TrakHound.Api;
using TrakHound.Clients.Services;
using TrakHound.Services.Tables;
public class Controller : TrakHoundApiController
{
private static bool _tableReady;
[ApiPost("start", Description = "Starts an operator on a job. If they're working on another job, it's stopped first.")]
public async Task<TrakHoundApiResponse> Start(
[FromBody(ContentType = "application/json", Description = "The operator's badge number and the job")] ClockRequest request)
{
if (string.IsNullOrWhiteSpace(request?.Operator) || string.IsNullOrWhiteSpace(request.Job))
{
return BadRequest("operator and job are required");
}
if (!await EnsureTable()) return Error(503, "The time_entry Table isn't available");
var now = DateTime.UtcNow;
var entry = new TimeEntry { Id = Guid.NewGuid().ToString(), Operator = request.Operator.Trim(), Job = request.Job.Trim(), StartedAt = now };
// Both steps run in one Transaction, so an operator is never on two jobs at once
var builder = new TrakHoundTableRequestBuilder();
builder.AppendQuery("update \"time_entry\" set \"ended_at\" = @now where \"operator\" = @operator and \"ended_at\" is null;");
builder.Parameters.Add("now", now);
builder.Parameters.Add("operator", entry.Operator);
builder.AppendInsert(new[] { entry });
var response = await Client.Services.Tables.Execute(builder.Build());
return response.Success ? Ok(entry) : Error(response.StatusCode, response.Message);
}
[ApiPost("stop", Description = "Stops the job an operator is working on.")]
public async Task<TrakHoundApiResponse> Stop(
[FromBody(ContentType = "application/json", Description = "The operator's badge number")] ClockRequest request)
{
if (string.IsNullOrWhiteSpace(request?.Operator)) return BadRequest("operator is required");
if (!await EnsureTable()) return Error(503, "The time_entry Table isn't available");
var entry = (await ReadEntries("where \"operator\" = @operator and \"ended_at\" is null", ("operator", request.Operator.Trim()))).FirstOrDefault();
if (entry == null) return NotFound($"{request.Operator} isn't working on a job");
entry.EndedAt = DateTime.UtcNow;
var builder = new TrakHoundTableRequestBuilder();
builder.AppendQuery("update \"time_entry\" set \"ended_at\" = @now where \"id\" = @id;");
builder.Parameters.Add("now", entry.EndedAt.Value);
builder.Parameters.Add("id", entry.Id);
var response = await Client.Services.Tables.Execute(builder.Build());
return response.Success ? Ok(entry) : Error(response.StatusCode, response.Message);
}
[ApiGet("working", Description = "Lists the operators working on a job right now, and how long they've worked on it.")]
[ApiMcpTool]
public async Task<TrakHoundApiResponse> GetWorking()
{
if (!await EnsureTable()) return Error(503, "The time_entry Table isn't available");
return Ok(await ReadEntries("where \"ended_at\" is null"));
}
[ApiGet("jobs/{job}", Description = "Gets the hours worked on a job, in total and by operator.")]
[ApiMcpTool]
public async Task<TrakHoundApiResponse> GetJob(
[FromRoute(Description = "The job number")] string job)
{
if (!await EnsureTable()) return Error(503, "The time_entry Table isn't available");
var entries = await ReadEntries("where \"job\" = @job", ("job", job));
if (entries.Count == 0) return NotFound($"No time recorded for job {job}");
return Ok(new
{
job,
hours = Math.Round(entries.Sum(o => o.GetHours()), 2),
operators = entries.GroupBy(o => o.Operator).Select(o => new { @operator = o.Key, hours = Math.Round(o.Sum(e => e.GetHours()), 2) }),
entries
});
}
private async Task<List<TimeEntry>> ReadEntries(string where, params (string Name, object Value)[] parameters)
{
var tableParameters = new TrakHoundTableParameterCollection();
foreach (var parameter in parameters) tableParameters.Add(parameter.Name, parameter.Value);
var request = TrakHoundTableRequest.Ansi($"select * from \"time_entry\" {where} order by \"started_at\" desc;", tableParameters, skip: 0, take: 1000);
var response = await Client.Services.Tables.ReadMultiple<TimeEntry>(request);
return response.Success ? response.Result.ToList() : new List<TimeEntry>();
}
// Creates the Table the first time the API is used
private async Task<bool> EnsureTable()
{
if (_tableReady) return true;
var builder = new TrakHoundTableRequestBuilder();
builder.AppendEnsureTable<TimeEntry>();
var response = await Client.Services.Tables.Execute(builder.Build());
if (!response.Success) Log(TrakHoundLogLevel.Warning, $"Unable to create the time_entry Table : {response.StatusCode} : {response.Message}");
_tableReady = response.Success;
return _tableReady;
}
private static TrakHoundApiResponse Error(int statusCode, string message)
{
return new TrakHoundApiResponse(statusCode, (message ?? $"Error {statusCode}").ToUtf8Bytes(), "text/plain");
}
}
- Start stops the operator's open entry and adds the new one in one Table Request. Its steps run in one Transaction, so if either fails, neither is saved.
- The queries use the
ansisubset with@operator,@job, and@idparameters, so they run unchanged on SQLite, SQL Server, and PostgreSQL. Never put a caller's input into the SQL itself. - Hours are calculated in C#, not in SQL, because date arithmetic is different on every database.
EnsureTablecreates thetime_entryTable the first time the API is used, so there's nothing to set up in the database.
4. Write the App
In TimeClock.App/trakhound.package.json, serve the App at /timeclock and make it depend on the API, so installing the App from a
Package server installs both:
"metadata": {
...
".defaultName": "Time Clock",
".defaultRoute": "timeclock"
},
"dependencies": [
{ "id": "TimeClock.Api", "version": "0.*" }
]
Add the API's route to parameters in TimeClock.App/trakhound.debug.json:
"parameters": {
"apiRoute": "timeclock/api"
},
Replace TimeClock.App/Pages/Index.razor:
@page "/"
@inherits TrakHoundComponentBase
@layout Layout
@using System.Threading
@using TimeClock
<div class="index-page">
<h3>Time Clock</h3>
<div style="display: flex; gap: 8px; align-items: center; flex-wrap: wrap;">
<RadzenTextBox @bind-Value="_operator" Placeholder="Badge" />
<RadzenTextBox @bind-Value="_job" Placeholder="Job" />
<RadzenButton Text="Start" ButtonStyle="ButtonStyle.Success" Click="Start" />
<RadzenButton Text="Stop" ButtonStyle="ButtonStyle.Danger" Click="Stop" />
</div>
@if (_message != null)
{
<p>@_message</p>
}
<h4>Working Now</h4>
<table>
<thead>
<tr><th>Badge</th><th>Job</th><th>Started</th><th>Hours</th></tr>
</thead>
<tbody>
@foreach (var entry in _working ?? new List<TimeEntry>())
{
<tr>
<td>@entry.Operator</td>
<td>@entry.Job</td>
<td>@entry.StartedAt.ToLocalTime().ToString("t")</td>
<td>@entry.GetHours().ToString("0.00")</td>
</tr>
}
</tbody>
</table>
</div>
@code {
private readonly CancellationTokenSource _stop = new CancellationTokenSource();
private string _apiRoute;
private string _operator;
private string _job;
private string _message;
private List<TimeEntry> _working;
protected override void OnInitialized()
{
_apiRoute = GetParameter("apiRoute");
if (string.IsNullOrEmpty(_apiRoute)) _apiRoute = "timeclock/api";
_ = RefreshLoop(_stop.Token);
}
public override void Dispose()
{
_stop.Cancel();
_stop.Dispose();
base.Dispose();
}
private async Task Start()
{
var response = await Client.Api.Post(Url.Combine(_apiRoute, "start"), new ClockRequest { Operator = _operator, Job = _job });
_message = response.Success ? $"{_operator} started job {_job}" : response.GetContentUtf8String();
await AfterClock(response.Success);
}
private async Task Stop()
{
var response = await Client.Api.Post(Url.Combine(_apiRoute, "stop"), new ClockRequest { Operator = _operator });
_message = response.Success ? $"{_operator} stopped" : response.GetContentUtf8String();
await AfterClock(response.Success);
}
private async Task AfterClock(bool success)
{
// Clear the form for the next operator
if (success)
{
_operator = null;
_job = null;
}
await Load();
}
// Reads who is working every 5 seconds while the page is open, so every station shows the same list
private async Task RefreshLoop(CancellationToken cancellationToken)
{
using var timer = new PeriodicTimer(TimeSpan.FromSeconds(5));
try
{
do
{
await Load();
await InvokeAsync(StateHasChanged);
}
while (await timer.WaitForNextTickAsync(cancellationToken));
}
catch (OperationCanceledException) { }
}
private async Task Load()
{
try
{
_working = await Client.Api.GetJson<List<TimeEntry>>(Url.Combine(_apiRoute, "working"));
}
catch (Exception ex)
{
_message = $"Unable to read from {_apiRoute} : {ex.Message}";
}
}
}
The App only calls the API. The Working Now list refreshes every 5 seconds, so a screen at each station shows the same list, and the hours count up while an operator works.
5. Build and Install
From the TimeClock folder, build both Packages:
th packages dotnet TimeClock.Api --output ./out
th packages dotnet TimeClock.App --output ./out
- In the admin UI, open Packages > Upload, drop both
.thpfiles fromout, and click Install 2 Packages. - Open Config > Groups > timeclock, and use Add Module to add the
TimeClock.ApiandTimeClock.Appconfigurations.
Add them to the Group before you use the App. Until then, the API would use the Instance's default Router, and could create its Table somewhere else.
6. Clock In
Open http://localhost:8472/timeclock. Enter badge 1042 and job J-100, and click Start. Then start badge 2077 on the same job.
Both appear under Working Now. Start 1042 on J-200: their J-100 entry stops, and they move to the new job. Click Stop for 2077.
Open the App in a second browser window, as a second station would. Its list updates within 5 seconds of a change at the first one.
A supervisor's report, an ERP system, or a script reads the hours on a job from the API:
curl http://localhost:8472/timeclock/api/jobs/J-100
{
"job": "J-100",
"hours": 1.75,
"operators": [
{ "operator": "2077", "hours": 1.5 },
{ "operator": "1042", "hours": 0.25 }
],
"entries": [ ... ]
}
hours isn't shown until a job has at least a minute of time.
working and jobs/{job} are MCP Tools, so an AI assistant connected to the Instance can answer
"Who is working on J-100 right now?" or "How many hours are on J-200?". start and stop aren't, so an assistant can't clock anyone in or out.
To change the App with breakpoints now that the API is installed, run dotnet run in TimeClock.App and open http://localhost:5092.
It calls the installed API, so it uses the same Table.
Troubleshooting
| Problem | What to do |
|---|---|
| Start shows The time_entry Table isn't available, or the API returns 405 | The API's Router has no Table Service. Check that TimeClock.Api is in the timeclock Group, that its configuration doesn't name its own Router, and that the Router's Route is saved. The API's log in the Explorer has the Service's error. |
| The App shows Unable to read from timeclock/api | The API is at a different route. Set the App configuration's apiRoute parameter to it. |
| Stop shows isn't working on a job | That badge has no open entry. Check the badge number under Working Now. |
Next Steps
Session.
Protect the Hours
Let only supervisors read hours by job, with permissions on the API's endpoints.
Close Forgotten Entries
Run a Function at the end of each shift that stops entries nobody stopped.
Next Tutorial: Message Queues
Take results from stations through a Message Queue into a database, with a Worker and a scheduled Function.