LowCodeHub.Audit 0.0.2

LowCodeHub.Audit

A DI-first generic audit-log library for ASP.NET Core. Records who changed what, when, and which event caused it — with full before/after JSON snapshots and the list of changed properties — backed by SQL Server or PostgreSQL via Dapper. Supports transactional writes, event tagging, filterable metadata, multi-value history queries, and automatic retention cleanup. No EF Core dependency.

Why This Library?

Installation

dotnet add package LowCodeHub.Audit

Quick Start

builder.Services
    .AddAudit(builder.Configuration)
    .AddAuditSqlServer(builder.Configuration);   // or .AddAuditPostgreSql(...)

// Record a change (action inferred from the snapshots):
await auditLogger.LogAsync<Order>(new()
{
    Source = "Order",
    SourceId = order.Id.ToString(),
    Before = before,
    After = order,
}, ct);

// Read it back, newest first:
AuditPage history = await auditReader.GetHistoryAsync("Order", order.Id.ToString());

That's it. The library serializes both snapshots, computes which properties changed, stamps the actor / trace id / UTC timestamp, and persists one audit row — skipping updates where nothing changed.

Table of Contents

• How It Works
• Configuration
  • appsettings.json
  • Options Reference
  • Code-Based Configuration
• Recording Changes
  • Action Inference
  • Diff Semantics
• Atomic With Your Business Change
• Events and Metadata
• Who Did the Change
• Querying History
• Retention Cleanup
• Storage Backends
• Table Shape
• Health Checks
• Database Migrations
  • With LowCodeHub.Migration.SqlServer
  • With LowCodeHub.Migration.PostgreSql
  • With EF Core Migrations
• Custom Repository Implementations
• Requirements
• License

How It Works

┌────────────────────────────────────────────────────────────────┐
│  WRITE PATH                                                    │
│                                                                │
│  Your handler / service                                        │
│  └── auditLogger.LogAsync(new AuditChange<T> { … }, tx?)      │
│       1. Serialize both snapshots (System.Text.Json)           │
│       2. Infer action: Insert / Update / Delete                │
│       3. Compute top-level diff → ChangedColumns               │
│          └── Update with zero changes → skipped (no row)       │
│       4. Stamp Id (UUIDv7), actor, trace id, UTC timestamp     │
│       5. INSERT audit row                                      │
│          └── joins your DbTransaction when supplied            │
└────────────────────────────────────────────────────────────────┘

┌────────────────────────────────────────────────────────────────┐
│  RETENTION                                                     │
│                                                                │
│  AuditRetentionWorker (BackgroundService)                      │
│  └── every CleanupInterval:                                    │
│       batch-DELETE rows where CreatedAtUtc < now - Retention   │
│       (idles when Retention is not configured)                 │
└────────────────────────────────────────────────────────────────┘

Key design principles:

• Explicit capture — you pass a Source name, a SourceId, and plain Before/After objects at the call site; nothing hooks into an ORM and nothing is inferred from CLR type names. Works with Dapper, raw ADO.NET, EF, or anything else.
• Append-only — audit rows are never updated; the only delete path is retention cleanup.
• Provider-symmetric — identical behavior on SQL Server (dbo.AuditLogs, PascalCase) and PostgreSQL (public.audit_logs, snake_case + JSONB).

Configuration

appsettings.json

{
  "Audit": {
    "Retention": "90.00:00:00",
    "CleanupInterval": "01:00:00",
    "CleanupBatchSize": 1000,
    "SqlServer": {
      "ConnectionString": "Server=.;Database=App;Trusted_Connection=True;TrustServerCertificate=True",
      "Schema": "dbo",
      "Table": "AuditLogs"
    },
    "PostgreSql": {
      "ConnectionString": "Host=localhost;Database=app;Username=app;Password=***",
      "Schema": "public",
      "Table": "audit_logs"
    }
  }
}

Only the section for the provider you register is required.

Options Reference

Audit (AuditOptions)

Audit:SqlServer (SqlServerAuditOptions)

Audit:PostgreSql (PostgreSqlAuditOptions)

Schema and table names are validated (letters, digits, underscore only) before being used in SQL.

Code-Based Configuration

builder.Services
    .AddAudit(options =>
    {
        options.Retention = TimeSpan.FromDays(90);
        options.CleanupBatchSize = 500;
    })
    .AddAuditSqlServer(options =>
    {
        options.ConnectionString = builder.Configuration.GetConnectionString("App")!;
    });

Recording Changes

Inject IAuditLogger. You describe the change once as an AuditChange<TRowLog>, then pick one of two overloads — with or without a transaction:

Task<Guid?> LogAsync<TRowLog>(       // returns the new row's id, or null when a no-op update is skipped
    AuditChange<TRowLog> change,
    CancellationToken cancellationToken = default) where TRowLog : class;

Task<Guid?> LogAsync<TRowLog>(       // same, enlisted in your open transaction
    AuditChange<TRowLog> change,
    DbTransaction transaction,
    CancellationToken cancellationToken = default) where TRowLog : class;

public sealed class AuditChange<TRowLog> where TRowLog : class
{
    public required string Source { get; init; }    // logical source, e.g. "Order" — always explicit, never inferred
    public required string SourceId { get; init; }  // instance id
    public TRowLog? Before { get; init; }           // snapshot before the change (null for inserts)
    public TRowLog? After { get; init; }            // snapshot after the change (null for deletes)
    public string? Event { get; init; }             // the event/operation that caused the change
    public Dictionary<string, object?>? Metadata { get; init; }   // free-form context, stored as JSON, filterable
}

Name the snapshot type on the call and the compiler builds the rest — new() needs no repetition of the type:

public sealed class OrderService(IAuditLogger auditLogger)
{
    public async Task ShipAsync(Order order, CancellationToken ct)
    {
        Order before = /* load current state */;
        // ... apply changes ...
        await auditLogger.LogAsync<Order>(new()
        {
            Source = "Order",
            SourceId = order.Id.ToString(),
            Before = before,
            After = order,
            Event = "OrderShipped",
        }, ct);
    }
}

LogAsync returns the persisted row's Guid, so you can correlate related records (e.g. a linked history table) to the exact audit row. It returns null when nothing was written — an update whose Before/After snapshots were identical.

Action Inference

The action is inferred from the snapshots — there is no Action property to get wrong:

Because Before and After are both TRowLog?, an insert or a delete just leaves one of them unset — no (Order?)null casts at the call site.

Diff Semantics

• Top-level only — each top-level property is compared by deep JSON equality (JsonElement.DeepEquals). Nested objects and collections compare as whole values and report the top-level property name.
• CLR-cased names — snapshots are serialized with JsonSerializerDefaults.General: property names keep their C# casing, so ChangedColumns entries and snapshot keys match your entity's property names.
• Null transitions count — null → value and value → null are both changes.
• Objects only — the snapshot must serialize to a JSON object; primitives and arrays throw InvalidOperationException.

Atomic With Your Business Change

Use the transaction overload and the audit row commits and rolls back with your data change, on the same connection — no distributed transaction:

await using var transaction = await connection.BeginTransactionAsync(ct);

await connection.ExecuteAsync(updateSql, order, transaction);
await auditLogger.LogAsync<Order>(new()
{
    Source = "Order",
    SourceId = order.Id.ToString(),
    Before = before,
    After = order,
    Event = "OrderShipped",
}, transaction, ct);

await transaction.CommitAsync(ct);   // both rows, or neither

The overload takes a non-nullable DbTransaction and throws ArgumentNullException if it is null, so a missing transaction can never silently degrade into a separately-committed audit row. Use the two-argument overload when you deliberately want a dedicated connection.

Using TransactionScope instead? That works too — the dedicated connection auto-enlists in an ambient System.Transactions.TransactionScope. Be aware that a scope spanning two connections escalates to a distributed transaction (Windows-only on modern .NET, unsupported on Azure SQL), so prefer passing the DbTransaction when you need guaranteed atomicity.

Events and Metadata

Event records why the row exists — the domain event or operation behind the change ("UnitMoved", "SubjectPublished", "order.shipped"). It's a first-class, indexed-by-query column you can filter on, so your UI can show "what happened" instead of deriving it from raw diffs.

Metadata is a Dictionary<string, object?> you fill with whatever your UI needs alongside the standard columns — cascade summaries, scope identifiers, reasons. The library serializes it to a JSON object on write, so you never hand-build JSON strings and the PostgreSQL JSONB column is always valid by construction. Each top-level key is filterable (see Querying History):

// Cascade delete — one summary row instead of N child rows. No After, no cast:
await auditLogger.LogAsync<Unit>(new()
{
    Source = "Unit",
    SourceId = unitId.ToString(),
    Before = unitSnapshot,
    Event = "UnitCascadeDeleted",
    Metadata = new() { ["DeletedLessons"] = 12, ["DeletedContent"] = 40 },
}, transaction, ct);

// Publish flow — several entries sharing one event, findable as a group:
await auditLogger.LogAsync<Subject>(new()
{
    Source = "Subject",
    SourceId = subjectId.ToString(),
    Before = before,
    After = after,
    Event = "SubjectPublishedToLive",
    Metadata = new() { ["publicationId"] = publicationId, ["isDraft"] = false },
}, transaction, ct);

Values are serialized by their runtime type, so numbers stay numbers and booleans stay booleans — which is what makes MetadataContains comparisons work. Nested objects and arrays are allowed; only top-level keys are filterable. A null or empty dictionary stores NULL.

On the way back out, AuditEntry.Metadata is an IReadOnlyDictionary<string, object?> whose values are JsonElements materialized from the stored JSON, not the original CLR instances:

if (entry.Metadata?.TryGetValue("DeletedLessons", out object? value) is true)
{
    int deleted = ((JsonElement)value!).GetInt32();
}

Serializing an AuditEntry straight into an API response works as expected — JsonElement round-trips to the original JSON shape.

Who Did the Change

Implement IAuditActorProvider and pass it as the type argument of AddAudit — no separate registration needed. The default (no type argument) stores null:

public sealed class HttpContextAuditActorProvider(IHttpContextAccessor accessor) : IAuditActorProvider
{
    public string? GetCurrentActor()
        => accessor.HttpContext?.User.FindFirstValue(ClaimTypes.NameIdentifier);
}

builder.Services.AddHttpContextAccessor();
builder.Services.AddAudit<HttpContextAuditActorProvider>(builder.Configuration);

Both AddAudit shapes (IConfiguration and Action<AuditOptions>) have a <TActorProvider> variant. Prefer registering IAuditActorProvider yourself? That still works — register it before calling AddAudit and your registration wins (the library uses TryAdd).

Already have a "current user" abstraction? Adapt it in one line:

public sealed class ActorProviderAdapter(IActorProvider inner) : IAuditActorProvider
{
    public string? GetCurrentActor() => inner.GetActor();
}

In non-HTTP hosts (background workers, migration jobs), register a provider that returns a fixed identity such as "system" or the job name.

CorrelationId is filled automatically from Activity.Current (the W3C trace id) whenever a trace is active — in ASP.NET Core that means audit rows correlate with your request traces out of the box.

Querying History

Inject IAuditReader:

// One source's history, newest first:
AuditPage history = await auditReader.GetHistoryAsync("Order", orderId, page: 1, pageSize: 50, ct);

// Everything one actor changed, newest first:
AuditPage actorHistory = await auditReader.GetActorHistoryAsync("user@example.com", page: 1, pageSize: 50, ct);

// Filtered queries — every filter optional, combined with AND:
AuditPage results = await auditReader.QueryAsync(new AuditQuery
{
    Sources = ["Order", "OrderLine"],          // one or many
    Events = ["OrderShipped", "OrderCancelled"], // one or many
    Action = AuditAction.Update,
    Actors = ["user@example.com", "admin@example.com"], // one or many
    MetadataContains = new Dictionary<string, object?>
    {
        ["publicationId"] = "42",
        ["isDraft"] = false,
    },
    From = DateTimeOffset.UtcNow.AddDays(-7),   // inclusive
    To = DateTimeOffset.UtcNow,                  // exclusive
    Page = 1,
    PageSize = 50,
}, ct);

AuditQuery filters

Results are always ordered newest first (CreatedAtUtc DESC, id as tiebreaker). AuditPage carries Items, TotalCount, Page, and PageSize.

Retention Cleanup

When Audit:Retention is set, AuditRetentionWorker (a BackgroundService registered by AddAudit) wakes every CleanupInterval and batch-deletes entries older than the retention window, CleanupBatchSize rows at a time, draining the backlog before sleeping again.

• Retention unset → the worker idles; nothing is ever deleted.
• Changing options at runtime is picked up on the next cycle (IOptionsMonitor).
• Deletes use DELETE TOP (n) (SQL Server) / LIMITed id subquery (PostgreSQL) to keep locks short.

Storage Backends

SQL Server

builder.Services
    .AddAudit(builder.Configuration)
    .AddAuditSqlServer(builder.Configuration);

PostgreSQL

builder.Services
    .AddAudit(builder.Configuration)
    .AddAuditPostgreSql(builder.Configuration);

Both register the repository (TryAddScoped), expose it as IAuditReader, and add a readiness health check. Snapshots land in NVARCHAR(MAX) on SQL Server and JSONB on PostgreSQL (queryable with @>, ->>, etc.).

Table Shape

Indexes

Health Checks

Registered automatically by the provider extensions:

app.MapHealthChecks("/health/ready", new() { Predicate = r => r.Tags.Contains("readiness") });

Database Migrations

LowCodeHub.Audit does not create or migrate its own database schema. The DDL ships as embedded, idempotent SQL scripts (safe to re-run); the consuming application owns when and how they are applied.

The scripts create the default dbo.AuditLogs / public.audit_logs. If you configure a custom schema/table in the options, adapt the script when you run it.

With LowCodeHub.Migration.SqlServer

using LowCodeHub.Audit.Migrations;
using LowCodeHub.Migration.SqlServer.Extensions;

builder.Services.AddSqlServerMigrations(migrations =>
{
    migrations.AddTarget<IAuditScriptScanner>(o =>
    {
        o.ConnectionString = builder.Configuration.GetConnectionString("App")!;
        o.Directories = ["LowCodeHub.Audit.Repositories.SqlServer.Scripts."];
    });
});

await app.RunSqlServerMigrationAsync();

With LowCodeHub.Migration.PostgreSql

using LowCodeHub.Audit.Migrations;
using LowCodeHub.Migration.PostgreSql.Extensions;

builder.Services.AddPostgreSqlMigrations(migrations =>
{
    migrations.AddTarget<IAuditScriptScanner>(o =>
    {
        o.ConnectionString = builder.Configuration.GetConnectionString("App")!;
        o.Directories = ["LowCodeHub.Audit.Repositories.PostgreSql.Scripts."];
    });
});

await app.RunPostgreSqlMigrationAsync();

Do not scan the whole LowCodeHub.Audit assembly without a directory filter — the package contains scripts for both providers.

With EF Core Migrations

EF Core will not discover the embedded scripts automatically. Execute them from an application-owned migration:

using LowCodeHub.Audit.Migrations;
using Microsoft.EntityFrameworkCore.Migrations;

public partial class AddLowCodeHubAuditSchema : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        foreach (var resource in SqlAudit.SqlServerResources)
        {
            migrationBuilder.Sql(SqlAudit.ReadFromResource(resource));
        }
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // Drop audit objects here if your migration policy requires reversible migrations.
    }
}

For PostgreSQL, loop over SqlAudit.PostgreSqlResources instead. You can also read the DDL directly via SqlAudit.ReadFromResource(...) for any other SQL runner.

Custom Repository Implementations

Persistence sits behind one public interface. Implement it to target a different database:

public interface IAuditRepository : IAuditReader
{
    Task AddAsync(AuditEntry entry, DbTransaction? transaction = null, CancellationToken cancellationToken = default);
    Task<int> DeleteOlderThanAsync(DateTimeOffset cutoffUtc, int batchSize, CancellationToken cancellationToken = default);
}

public interface IAuditReader
{
    Task<AuditPage> QueryAsync(AuditQuery query, CancellationToken cancellationToken = default);
    // GetHistoryAsync has a default implementation that delegates to QueryAsync
}

AuditEntry reaches your repository with ChangedColumns and Metadata still typed (a list and a dictionary), so the storage format is yours to choose — the bundled SQL providers serialize both to JSON columns, while a document store would persist them natively.

Register your implementation before calling AddAudit; skip the provider extension entirely:

builder.Services.AddScoped<IAuditRepository, MyMongoAuditRepository>();
builder.Services.AddScoped<IAuditReader>(sp => sp.GetRequiredService<IAuditRepository>());
builder.Services.AddAudit(builder.Configuration);
// No AddAuditSqlServer / AddAuditPostgreSql needed

The library uses TryAddScoped, so your registrations take precedence.

Requirements

• .NET 10 or later
• SQL Server (via Microsoft.Data.SqlClient) or PostgreSQL (via Npgsql) — or your own repository implementation
• Dapper is an internal implementation detail; your app does not need to use it

License

MIT © Ahmed Abuelnour