# ADR: What the database is actually doing

Status: accepted, 2026-09-03. The Admin tab now shows every SQL statement this
application sends, its own log, and how long both take. The interesting decision
is not that it shows them. It is that the SQL table has nowhere to put a
parameter value.

## In plain words

This page adds a live view of every database query the site sends, and how long each takes, to the public Admin tab. Because anyone can open that page, the record that holds each query has no field for the values sent with it (parameter values), so an email address has nowhere to appear.

What that is worth: a developer can see which request caused each query and read the slowest endpoint as a number, and the organization can show its data access in public without exposing anyone's data.

## Context

The Admin tab already showed health, Azure's view of the container, an hour of
Application Insights, and a ring of recent errors. What it could not show is the
one thing a reviewer actually asks about a data-access layer: what queries does
this thing send, and how long do they take.

Entity Framework knows. It writes a line like this for every command, at
Information, on the `Microsoft.EntityFrameworkCore.Database.Command` category:

```
Executed DbCommand (6ms) [Parameters=[@normalizedEmail='?' (Size = 18)],
CommandType='Text', CommandTimeout='30']
SELECT "a"."Id", "a"."Email", ... FROM "AspNetUsers" AS "a"
WHERE "a"."NormalizedEmail" = @normalizedEmail LIMIT 2
```

So the data exists. The question is how to get at it, and what to keep.

## The constraint that decided the design

**This page is public.** That is not an oversight, it is ADR-010: the site is a
portfolio and the Admin tab is part of what it is showing. Anyone can open it.

And the parameters of a registration carry an email address. So do the
parameters of a sign-in. There is no version of "show the raw SQL" that is safe
by default here.

The obvious answer is a redaction rule: show values, mask anything that looks
like an address. The obvious answer is wrong in a specific and boring way. A rule
that lists what to hide is correct only for the columns that existed when
somebody wrote it. Add a phone number, a postal address, a note field, and the
rule is silently wrong, and it is wrong on a public page, and nothing fails.

## Decision

**Read the command, not the log line, and never read `Value`.**

The type the table is built from has no field for a parameter value:

```csharp
/// <summary>One parameter of a SQL statement, described but never valued.</summary>
public sealed record SqlParameterShape(string Name, string Type, int? Size);
```

That is the whole safety argument. Not a rule about which values to hide: no
place to put one. A column added next year cannot leak through this, because
there is nothing for it to leak into.

Getting there means an interceptor rather than a log filter:

```csharp
public sealed class SqlLogInterceptor(ISqlLog log, ICurrentRequest request) : DbCommandInterceptor
{
    private void Record(DbCommand command, TimeSpan duration, string outcome)
    {
        var parameters = new List<SqlParameterShape>(command.Parameters.Count);
        foreach (DbParameter parameter in command.Parameters)
        {
            // Name, type, size. Never Value.
            parameters.Add(new SqlParameterShape(
                parameter.ParameterName,
                parameter.DbType.ToString(),
                parameter.Size == 0 ? null : parameter.Size));
        }
        ...
    }
}
```

Reading EF's own message and taking the values back out of it would mean the
values had already been formatted into a string, already been in memory as a
message, and already been handed to every other logging provider in the process,
Application Insights included. Removing something after it has been broadcast is
not removing it.

**A statement carries the request that caused it.** A list of SELECTs with no
cause is a screensaver. The same list beside `GET /api/vehicles?make=Ford` is an
explanation, and it is the thing an interviewer would actually ask about. The
interceptor gets it through a port:

```csharp
/// <summary>
/// Port: what request is in flight on this call path, as one short string like
/// "GET /api/vehicles". Infrastructure asks; the API answers from the ambient
/// HttpContext. Outside a request, such as at startup, the answer is null.
/// </summary>
public interface ICurrentRequest
{
    string? Describe();
}
```

Infrastructure does not learn what an `HttpContext` is, which is the same reason
`IVehicleSource` exists.

**Percentiles are computed on read.** A running percentile needs a sketch, and a
sketch needs a reason. These rings hold a few hundred entries; sorting a few
hundred longs costs microseconds and the answer is exact for the window rather
than approximate forever. Nearest rank, no interpolation, and the empty sample
answers zero rather than throwing.

**Everything is in this process's memory and the page says so.** Three rings:
200 statements, 300 log lines, 500 requests. They empty on every container roll.
A log store is exactly the kind of thing that turns a free tier into a bill, and
this is a demo.

## What the tests hold

The test that matters registers an account and then reads the endpoint:

```csharp
string email = $"leak-canary-{Guid.NewGuid():N}@example.com";
await _client.PostAsJsonAsync("/api/auth/register", new { email, password = "correct horse battery" });

string body = await _client.GetStringAsync("/api/admin/sql");

Assert.Contains("AspNetUsers", body, StringComparison.Ordinal);
Assert.DoesNotContain(email, body, StringComparison.OrdinalIgnoreCase);
```

It asserts both halves. That the statements are there, so the test cannot pass by
the section being broken, and that the address is not. A second test walks every
parameter in the response and asserts the serialized object has exactly three
fields, so a future `Value` property fails here rather than on the live site. The
browser suite repeats the same check against the rendered page.

## Consequences

- A reviewer can watch the queries this application runs, see which request
  caused each one, and see how long the database took, without a login and
  without a trace tool.
- The slowest endpoint on the site is now a number on a page rather than a guess.
- A parameter value cannot reach the page through this path, and the reason is
  structural rather than a rule somebody has to maintain.
- Three more fixed-size buffers in memory, about a megabyte at capacity.
- Timing is measured outside the error middleware, so a request that fails is
  still timed. An endpoint that fails slowly is the one worth seeing.

## Addendum, 2026-09-08: the card has a sibling

This record describes a world with one store in it, and as of 1.0.0.89 there are
two. The SQL card is unchanged and is what the first container shows. The second
container shows the document store's counterpart, with the request charge and
the partition beside every operation, fed by a parallel port rather than a
widening of `ISqlLog`, for reasons given where the sibling is decided
(ADR: What the store is actually doing). The no-values rule carries over exactly,
and the canary test in this record now reads whichever card the container it is
running on has.

The port the statements are written into, read from this build. The entry type has no field for a parameter's value, which is why no redaction rule is needed for a column that does not exist yet:

```csharp
// The Admin tab shows the raw SQL this application runs (ADR: What the database
// is actually doing). Two things about the shape of these types are deliberate.
//
// First, a statement carries the request that caused it. A list of SELECTs with
// no cause is a screensaver; the same list beside "GET /api/vehicles?make=Ford"
// is an explanation.
//
// Second, there is no place to put a parameter value. Not a redacted one, not a
// masked one: the type has no field for it. The Admin tab is public and the
// parameters of a registration carry an email address. A rule that says "redact
// this column" is one new column away from being wrong; a type with nowhere to
// write the value cannot leak it whatever anyone adds later.

/// <summary>One parameter of a SQL statement, described but never valued.</summary>
/// <param name="Name">The parameter name, such as <c>@p0</c>.</param>
/// <param name="Type">The parameter's database type.</param>
/// <param name="Size">The declared size of the parameter, or null when it has none.</param>
public sealed record SqlParameterShape(string Name, string Type, int? Size);

/// <summary>
/// One SQL statement the application ran: its text, the shape of its
/// parameters, how long the database took, and the HTTP request that caused it.
/// </summary>
/// <param name="At">When the statement ran.</param>
/// <param name="Text">The SQL text as sent to the database.</param>
/// <param name="Parameters">The shape of each parameter: name, type and size, never the value.</param>
/// <param name="DurationMs">How long the database took, in milliseconds.</param>
/// <param name="Outcome">How the statement ended, such as success or the failure it met.</param>
/// <param name="Request">The HTTP request that caused the statement, like "GET /api/vehicles", or null when none did.</param>
public sealed record SqlStatement(
    DateTimeOffset At,
    string Text,
    IReadOnlyList<SqlParameterShape> Parameters,
    long DurationMs,
    string Outcome,
    string? Request);

/// <summary>Port: where recent SQL statements are kept for the Admin tab.</summary>
public interface ISqlLog
{
    void Record(SqlStatement statement);
}

/// <summary>
/// Port: what request is in flight on this call path, as one short string like
/// "GET /api/vehicles". Infrastructure asks; the API answers from the ambient
/// HttpContext. Outside a request, such as at startup, the answer is null.
/// </summary>
public interface ICurrentRequest
{
    string? Describe();

    /// <summary>
    /// Something that tells this request apart from the next one to the same
    /// path, for the store log's per-request arithmetic. The trace identifier,
    /// on the API; null outside a request. Never shown on a page.
    /// </summary>
    string? Identify();
}

/// <summary>The port wired to nothing, for the tests and the design-time tools.</summary>
public sealed class NullSqlLog : ISqlLog
{
    public static readonly NullSqlLog Instance = new();

    private NullSqlLog() { }

    public void Record(SqlStatement statement) { }
}

/// <summary>The port wired to nothing: no request, ever.</summary>
public sealed class NoCurrentRequest : ICurrentRequest
{
    public static readonly NoCurrentRequest Instance = new();

    private NoCurrentRequest() { }

    public string? Describe() => null;

    public string? Identify() => null;
}
```
*Live from [`api/TheYard.Application/SqlLog.cs`](https://github.com/SteveStout/TheYard/blob/1d6b50b/api/TheYard.Application/SqlLog.cs#L4-L84), region `sql-log-port`, read from this build at 1d6b50b.*

## Where it sits

Each ring holds one piece: Application/SqlLog.cs declares ISqlLog, ICurrentRequest and SqlParameterShape, Infrastructure's SqlLogInterceptor records each command, and the host Api answers ICurrentRequest from HttpContext in HttpCurrentRequest.cs and keeps the rings in AdminObservability.cs. Dependency inversion is plain to see here, because Infrastructure needs the request in flight and gets it by asking a port Application defines, so it never sees an HttpContext. ICurrentRequest has a single method, which is interface segregation at its smallest. The cost is about a megabyte of memory and a history that empties on every roll; several instances or a need to look back past a deploy would justify a real log store.

## Files

- [`api/TheYard.Application/SqlLog.cs`](https://github.com/SteveStout/TheYard/blob/main/api/TheYard.Application/SqlLog.cs): the types with nowhere to put a value, and the two ports.
- [`api/TheYard.Infrastructure/SqlLogInterceptor.cs`](https://github.com/SteveStout/TheYard/blob/main/api/TheYard.Infrastructure/SqlLogInterceptor.cs): reading the command instead of the log line.
- [`api/TheYard.Api/AdminObservability.cs`](https://github.com/SteveStout/TheYard/blob/main/api/TheYard.Api/AdminObservability.cs), [`api/TheYard.Api/LogRingBuffer.cs`](https://github.com/SteveStout/TheYard/blob/main/api/TheYard.Api/LogRingBuffer.cs) and [`api/TheYard.Api/RequestTimings.cs`](https://github.com/SteveStout/TheYard/blob/main/api/TheYard.Api/RequestTimings.cs): the three rings, the logging provider, and the percentiles.
- [`api/TheYard.Api/Composition/ObservabilityRegistration.cs`](https://github.com/SteveStout/TheYard/blob/main/api/TheYard.Api/Composition/ObservabilityRegistration.cs): where they are wired (region admin-rings).
- [`api/TheYard.Api/Composition/RequestPipeline.cs`](https://github.com/SteveStout/TheYard/blob/main/api/TheYard.Api/Composition/RequestPipeline.cs): the timing middleware (region request-timing).
- [`api/TheYard.Tests/AdminObservabilityTests.cs`](https://github.com/SteveStout/TheYard/blob/main/api/TheYard.Tests/AdminObservabilityTests.cs): the canary, the shape check, and the percentile table.
- [`src/components/admin/SqlCard/SqlCard.tsx`](https://github.com/SteveStout/TheYard/blob/main/src/components/admin/SqlCard/SqlCard.tsx): the three sections.
- [`docs/decisions/ADR-010-observability.md`](https://github.com/SteveStout/TheYard/blob/main/docs/decisions/ADR-010-observability.md): why this page is public in the first place.