Gym Coach API — ASP.NET Core 8 / EF Core / SQL Server

REST API for a gamified strength-training tracker: exercise catalogue, workout logging with XP progression, 28-day training analysis, and a coaching assistant that is only ever allowed to describe numbers the database computed.

Originally built as a Node/TypeScript/PostgreSQL service and re-implemented here on the .NET stack. The interesting parts are the porting decisions, not the CRUD.

Runtime	.NET 8, ASP.NET Core Web API (controllers)
Data	EF Core 8, SQL Server (LocalDB / Express / container)
Auth	JWT bearer + rotating refresh tokens (BCrypt, work factor 12)
Docs	Swagger / OpenAPI with bearer auth wired in
Tests	xUnit, 44 tests over a real SQLite engine
CI	GitHub Actions: warnings-as-errors build, tests, migrations applied to a SQL Server service container

Running it

# 1. point it at a SQL Server instance (LocalDB, Express, or a container)
dotnet user-secrets set "ConnectionStrings:Default" "Server=.\SQLEXPRESS;Database=GymCoach;Trusted_Connection=True;TrustServerCertificate=True" --project src/GymCoach.Api
dotnet user-secrets set "Jwt:Secret" "<32+ character secret>" --project src/GymCoach.Api

# 2. run - migrations are applied and the catalogue is seeded on boot
dotnet run --project src/GymCoach.Api

Swagger UI is at /swagger in Development. Set Coach:GeminiApiKey to enable the model-backed advisor; without it the API falls back to a deterministic advisor and stays fully functional.

Endpoints

Method	Route	Auth	Purpose
GET	/health	–	Liveness
POST	/auth/register	–	Create account, returns token pair
POST	/auth/login	–	Exchange credentials for a token pair
POST	/auth/refresh	–	Rotate the refresh token
POST	/auth/logout	–	Revoke a refresh token
GET	/exercises	–	Catalogue, optional ?muscle= filter
GET	/exercises/{id}	–	One exercise
POST	/workouts	JWT	Log a session, award XP (idempotent)
GET	/workouts	JWT	Recent sessions, newest first
GET	/workouts/{id}	JWT	One session
GET	/me	JWT	Profile
GET	/me/progress	JWT	XP, level, distance to next level
GET	/me/stats	JWT	Raw 28-day aggregation, no model involved
GET	/me/analysis	JWT	Aggregation plus grounded commentary
POST	/chat	JWT	Ask the coach a question
GET	/chat/history	JWT	Conversation history

Design notes

Exactly-once workout logging. The mobile client queues sessions while offline and stamps each one with a UUID. A filtered unique index on (UserId, ClientRequestId) WHERE ClientRequestId IS NOT NULL makes SQL Server the arbiter. This is the one place the port was not a straight translation: PostgreSQL treats NULLs as distinct in a unique index, so the original schema could declare the pair unique outright; SQL Server does not, and an unfiltered index would have rejected every ordinary keyless log after the first. The service pre-checks for the common replay and catches error 2601/2627 for the case where two retries genuinely race.

The model never does arithmetic. /me/analysis runs a single parameterised three-table join that aggregates tonnage per muscle group, then hands the finished numbers to the advisor to phrase. Because correctness lives in SQL rather than in the prompt, dropping the model entirely costs fluency and nothing else — which is exactly what the no-API-key fallback demonstrates, and what the Advice_is_grounded_on_the_computed_statistics test pins down.

Refresh-token theft detection. Refresh tokens are opaque 320-bit values stored only as SHA-256 hashes and rotated on every use. Presenting a token that was already rotated means two parties hold it, so the entire token family for that user is revoked in a single set-based ExecuteUpdate.

Indexes are justified in the model. Every non-key index in GymCoachDbContext carries a comment naming the query it serves, including the one on SetEntries(WorkoutLogId) — a foreign key is not automatically indexed, and that column drives the join in the volume aggregation.

Errors are translated once. Controllers contain no try/catch. A domain exception hierarchy (NotFoundException, ConflictException, UnauthorizedException, AiUnavailableException) carries its own status code, and one middleware maps those — plus known EF Core failures — onto RFC 7807 ProblemDetails, logging 5xx and never leaking an internal message.

Tests

dotnet test

44 tests run against a real in-memory SQLite database created from the production EF model, so foreign keys, cascades and the filtered unique index are genuinely enforced rather than faked by the InMemory provider. Coverage is deliberately weighted toward the invariants that would be expensive to get wrong: XP is granted exactly once per idempotency key, a replay never re-triggers a level-up, one user's sessions never leak into another's aggregate, and a rotated refresh token kills its whole family.