# Build a Time Clock

> Build an App and an API that let operators start and stop work on jobs, store the time in a database Table, and total the hours on each job.

URL: https://www.trakhound.com/docs/trakhound-framework/tutorials/time-clock

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.

Diagram: Operators use the Time Clock App on a station screen to start and stop work on jobs. The App calls the Time Clock API, which writes time entries to the time\_entry Table through the timeclock Group's Router and SQLite Service. Supervisors, other systems, and AI assistants read hours by job from the same API.

## 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 and upload a Package.

## What You'll Build

| Package | Kind | Its job |
| --- | --- | --- |
| `TimeClock.Api` | [API](https://www.trakhound.com/docs/trakhound-framework/apis) | 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](https://www.trakhound.com/docs/trakhound-framework/apps) | 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](https://www.trakhound.com/docs/trakhound-framework/services), so it never connects to a database itself. For this tutorial, use SQLite, which needs no database server. In the admin UI:

1. Under **Packages**, install `TrakHound.Sqlite.Service`.
2. Under **Config > Services**, add a SQLite Service with the ID `timeclock-db`, and set its `database` parameter to a full file path, such as `C:\TrakHound\Data\timeclock.db`.
3. Under **Config > Routers**, add a Router named `timeclock` with one Route, with the pattern `Tables`, that targets `timeclock-db`. Click **Save**.
4. Under **Config > Groups**, click **Add Group** with the ID `timeclock`, and set its **Router** to `timeclock`.

Modules in the `timeclock` [Group](https://www.trakhound.com/docs/trakhound-framework/configuration#groups) 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 `ansi` subset with `@operator`, `@job`, and `@id` parameters, 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.
- `EnsureTable` creates the `time_entry` Table 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](https://www.trakhound.com/docs/trakhound-framework/packages#publishing) 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
```

1. In the admin UI, open **Packages > Upload**, drop both `.thp` files from `out`, and click **Install 2 Packages**.
2. Open **Config > Groups > timeclock**, and use **Add Module** to add the `TimeClock.Api` and `TimeClock.App` configurations.

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": [ ... ]
}
```

> TrakHound leaves values that are 0 out of its JSON, so `hours` isn't shown until a job has at least a minute of time.

`working` and `jobs/{job}` are [MCP Tools](https://www.trakhound.com/docs/trakhound-framework/mcp-server), 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

[Sign Operators In Use sign-in instead of a typed badge, and read the operator from the App's `Session`.](https://www.trakhound.com/docs/trakhound-framework/identity) [Protect the Hours Let only supervisors read hours by job, with permissions on the API's endpoints.](https://www.trakhound.com/docs/trakhound-framework/security) [Close Forgotten Entries Run a Function at the end of each shift that stops entries nobody stopped.](https://www.trakhound.com/docs/trakhound-framework/functions) [Next Tutorial: Message Queues Take results from stations through a Message Queue into a database, with a Worker and a scheduled Function.](https://www.trakhound.com/docs/trakhound-framework/tutorials/quality-check)
