Audit Logs Reference

What Audit Logs capture#

Audit Logs answer:

Audit events are not debug logs, traces, metrics, or product analytics. Keep them structured, bounded, durable, and safe for an operator to inspect.

Service registration#

AddSqlOS<TContext>() registers ISqlOSAuditLogService.

builder.AddSqlOS<ExampleAppDbContext>(
    db => db.UseSqlServer(connectionString));

Inject the service into server-side application code. Frontends should not write audit rows directly.

The actor and organization are security context, not client input. For endpoints protected with RequireSqlOSAccessToken, derive them from GetSqlOSValidatedToken(). For cookie, API-key, or service authentication, resolve the equivalent identity from the authenticated server-side principal or credential mapping. Authorize the requested mutation before changing state or recording a success event.

var documentsApi = app.MapGroup("/api")
    .RequireSqlOSAccessToken(apiAudience);
 
documentsApi.MapPost("/documents/{id}/share", async (
    string id,
    ShareDocumentRequest request,
    WorkspaceDbContext db,
    IAuthorizationService authorization,
    ISqlOSAuditLogService auditLogs,
    HttpContext httpContext,
    CancellationToken ct) =>
{
    var token = httpContext.GetSqlOSValidatedToken();
    if (token?.UserId is not { Length: > 0 } userId)
    {
        return Results.Unauthorized();
    }
 
    if (token.OrganizationId is not { Length: > 0 } organizationId)
    {
        return Results.Forbid();
    }
 
    var document = await db.Documents.SingleOrDefaultAsync(
        x => x.Id == id && x.OrganizationId == organizationId,
        ct);
    if (document is null)
    {
        return Results.NotFound();
    }
 
    var authorized = await authorization.AuthorizeAsync(
        token.Principal,
        document,
        "document.share");
    if (!authorized.Succeeded)
    {
        return Results.Forbid();
    }
 
    document.SharedRole = request.Role;
    await db.SaveChangesAsync(ct);
 
    await auditLogs.RecordAsync(new SqlOSAuditLogRecordRequest(
        Action: "document.shared",
        OrganizationId: organizationId,
        ApplicationKey: "workspace-web",
        Source: "application",
        Actor: new SqlOSAuditActor(
            "user",
            userId,
            token.Principal.Identity?.Name),
        Targets:
        [
            new SqlOSAuditTarget("document", document.Id, document.Name)
        ],
        Context: SqlOSAuditContext.FromHttpContext(httpContext),
        Metadata: new Dictionary<string, object?>
        {
            ["result"] = "success",
            ["role"] = request.Role
        }),
        ct);
 
    return Results.NoContent();
});
 
public sealed record ShareDocumentRequest(string Role);

This example assumes the application defines the document.share authorization policy. The request DTO contains only the requested business value; document, actor, and tenant details come from trusted server-side state. SqlOS generates the audit event id, so an ordinary write like this does not need an idempotency key.

Record request#

SqlOSAuditLogRecordRequest

Actor#

SqlOSAuditActor

Targets#

SqlOSAuditTarget

Use multiple targets when one operation affects a hierarchy. For example, an inventory update can target both the location and the inventory_item.

Context#

SqlOSAuditContext.FromHttpContext(httpContext) captures:

• remote IP address
• user agent
• sid claim as session id
• X-Request-ID / X-Request-Id, falling back to HttpContext.TraceIdentifier
• X-Correlation-ID / X-Correlation-Id

Create SqlOSAuditContext manually for background jobs, service accounts, queues, and scheduled maintenance.

Idempotency namespace#

Every successful write receives a SqlOS-generated EventId. IdempotencyKey is a separate, optional retry token: omit it for ordinary writes, and every call records a new event. Supply it only when a retrying workflow, such as an outbox worker, needs repeated delivery of one business operation to converge on one audit event. SqlOS cannot generate this token because it cannot distinguish a retry from a second legitimate operation.

When a key is supplied, SqlOS hashes a versioned canonical representation of the normalized organization id (with global/null as a distinct value), resolved application id and key, source, exact action, and supplied key. The same key is independent across any of those scope dimensions. A retry can return only an event whose stored scope exactly matches the request.

Use a stable domain operation or outbox message id, for example share:{shareOperationId}. Do not generate a new request or trace id for each retry. Organization, application, source, and action are already in the namespace and do not need to be duplicated in the caller key.

Schema upgrades preserve existing rows and their legacy hashes without recovering raw keys. Legacy retries deduplicate only when every stored scope field matches. New writes use the scoped hash and a unique filtered SQL index. If an index/hash conflict cannot be resolved inside the exact caller scope, RecordAsync throws SqlOSAuditLogIdempotencyConflictException with Error == "idempotency_conflict" instead of returning another scope's event.

Metadata safety#

Metadata must be non-secret. Do not include:

• passwords
• access or refresh tokens
• API keys
• client secrets
• raw authorization headers
• cookies
• private keys
• raw exceptions or stack traces
• full request or response bodies

SqlOS redacts common secret-like metadata keys, but callers are still responsible for sending safe data. Prefer stable fields such as result, reason, counts, role names, and non-sensitive before/after values.

Query request#

SqlOSAuditLogListRequest

Results sort by OccurredAt descending, then IngestedAt descending.

Service methods#

public interface ISqlOSAuditLogService
{
    Task<SqlOSAuditLogRecordResult> RecordAsync(
        SqlOSAuditLogRecordRequest request,
        CancellationToken cancellationToken = default);
 
    Task<SqlOSAuditLogListResult> ListAsync(
        SqlOSAuditLogListRequest request,
        CancellationToken cancellationToken = default);
 
    Task<SqlOSAuditLogEvent?> GetAsync(
        string id,
        CancellationToken cancellationToken = default);
 
    Task<SqlOSAuditLogCsvExportResult> ExportCsvAsync(
        SqlOSAuditLogListRequest request,
        CancellationToken cancellationToken = default);
}

RecordAsync always returns the SqlOS-generated event id. When an optional idempotency key matches an event in the same namespace, it returns Created = false and the original event; without a key, each call creates a new event.

Dashboard#

The embedded dashboard exposes Audit Logs as a top-level governance page:

/sqlos/admin/audit/logs

Operators can filter by organization, application, source, action, actor, target, result/status metadata, text search, and date range. Selecting a row opens structured details for actor, targets, context, and metadata.

The older AuthServer audit route links into the same central view filtered to source=authserver.

Admin API#

Admin APIs are mounted when you call app.MapSqlOS(). They are protected by the same dashboard authorization as /sqlos/admin: password-mode dashboard sessions, your configured Dashboard.AuthorizationCallback, or your deployment's dashboard-facing proxy/SSO controls. Unauthorized requests return 404.

Default base path:

/sqlos/admin/audit/api

CSV export uses the same filters as listing. It is bounded to 5,000 rows and a maximum 366-day date range. If no dates are supplied, export defaults to the last 30 days.

Retail example#

The Retail example records host-application events with:

ApplicationKey = "northwind-retail"
Source = "application"

Successful chain, location, and inventory mutations appear in /sqlos/admin/audit/logs. Filter by northwind-retail to inspect only Retail events.

See also#

• Add audit logs to your app
• Dashboard
• API Reference