Appouse.Observer 1.0.0

Appouse.Observer

Automatic, Detailed Audit Trail for .NET Applications

Appouse.Observer is a comprehensive audit trail library for .NET that automatically tracks every change to your database entities — both from your application code and from direct database operations.

✨ Features

• Hybrid Auditing — Tracks changes via EF Core interceptors (application-level) AND database triggers (DB-level)
• Multi-Database — PostgreSQL, SQL Server, Oracle, MySQL
• Column-Level Tracking — Records old and new values for every changed column
• Rich Metadata — Captures who, when, what, and how:
  • Application User, DB User
  • Method/Endpoint name
  • Activity ID, Trace Identifier, Correlation ID
  • Client IP, User Agent
• Immutable Audit Log — Append-only tables with DELETE/UPDATE protection at both EF Core and DB trigger level
• Retention Policies — Automatic cleanup of old records via background service (global + per-table)
• Zero-Code Audit — Just add IAuditableEntity to your entity and configure once
• Auto-Setup — Audit tables and triggers are automatically created at startup
• Message Queue Publishing — Publish audit events to RabbitMQ, Kafka, Azure Service Bus via MassTransit
• Transactional Outbox — Reliable message delivery with MassTransit's EF Core Outbox pattern
• Flexible Delivery — Database only, event bus only, or both — configure per environment
• Polly Resilience — Built-in retry (exponential backoff + jitter) and circuit breaker policies
• Idempotency — Each audit entry has a unique IdempotencyKey preventing duplicates across retries and redelivery

📦 Packages

Package	Description	NuGet
Appouse.Observer	Core library (abstractions, interceptor, models, Polly)
Appouse.Observer.PostgreSql	PostgreSQL provider
Appouse.Observer.SqlServer	SQL Server provider
Appouse.Observer.Oracle	Oracle provider
Appouse.Observer.MySql	MySQL provider
Appouse.Observer.MassTransit	MassTransit messaging integration
Appouse.Observer.MassTransit.Outbox	Transactional Outbox for reliable delivery

🚀 Quick Start

1. Install NuGet Packages

# Core + your database provider
dotnet add package Appouse.Observer
dotnet add package Appouse.Observer.PostgreSql    # or SqlServer, Oracle, MySql

# Optional: message queue publishing
dotnet add package Appouse.Observer.MassTransit

# Optional: transactional outbox for reliable delivery
dotnet add package Appouse.Observer.MassTransit.Outbox

2. Mark Your Entities

using Appouse.Observer.Abstractions;

public class Product : IAuditableEntity  // ← Add this interface
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public int StockQuantity { get; set; }

    [AuditIgnore]  // ← This property will NOT appear in audit logs
    public decimal InternalCost { get; set; }
}

public class User : IAuditableEntity
{
    public int Id { get; set; }
    public string Email { get; set; }
    public string FullName { get; set; }
    public string Role { get; set; }

    [AuditIgnore]  // Sensitive — never log
    public string PasswordHash { get; set; }

    [AuditIgnore]
    public string? SecurityStamp { get; set; }
}

3. Configure Services

// Program.cs
using Appouse.Observer.Extensions;
using Appouse.Observer.PostgreSql.Extensions; // or SqlServer, Oracle, MySql

var builder = WebApplication.CreateBuilder(args);

// Register Observer core
builder.Services.AddObserver(options =>
{
    options.AuditSchema = "observer";
    options.AutoCreateTables = true;
    options.AutoCreateTriggers = true;
    options.EnableRetention = true;
    options.RetentionPeriod = TimeSpan.FromDays(730); // 2 years
    options.ProtectAuditTables = true;
});

// Register database provider
builder.Services.AddObserverPostgreSql(
    builder.Configuration.GetConnectionString("DefaultConnection")!);

// Register your DbContext WITH Observer interceptors
builder.Services.AddDbContext<MyDbContext>((sp, options) =>
{
    options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection")!);
    options.AddObserverInterceptors(sp); // ← THIS enables EF Core interception
});

4. That's It! 🎉

Observer will automatically:

• Create audit tables (observer.audit_log and observer.audit_detail)
• Create database triggers on all IAuditableEntity tables
• Track every INSERT, UPDATE, DELETE with full metadata
• Assign a unique IdempotencyKey (Guid) to each audit entry
• Protect audit records from tampering

📊 What Gets Tracked

Audit Log Entry (observer.audit_log)

Audit Detail (observer.audit_detail)

🔑 Idempotency

Every audit log entry is assigned a unique IdempotencyKey (Guid) when it is created by the interceptor. This key is used to prevent duplicate entries across:

• Polly retries — If a DB write fails and is retried, the key ensures no duplicate rows are inserted.
• Message redelivery — If a message broker redelivers an event, consumers can check the key for deduplication.
• Outbox reprocessing — If the outbox delivery service reprocesses a message, the key prevents duplicates.

The key is enforced with a unique index on the observer.audit_log table.

// The IdempotencyKey is automatically generated — no configuration needed.
// It flows through the entire pipeline: Interceptor → IAuditStore → IAuditEventPublisher

// On the consumer side, use it for deduplication:
public class AuditEventConsumer : IConsumer<AuditLogCreatedEvent>
{
    public async Task Consume(ConsumeContext<AuditLogCreatedEvent> context)
    {
        var key = context.Message.IdempotencyKey;
        
        // Check if already processed
        if (await _repository.ExistsAsync(key))
            return; // Skip duplicate
        
        // Process the audit event...
        await _repository.SaveAsync(context.Message);
    }
}

🔒 Immutability & Protection

Audit records are append-only. Protection is enforced at two levels:

1. EF Core Level: AuditProtectionInterceptor blocks DELETE/UPDATE on audit entities
2. Database Level: Protection triggers reject DELETE/UPDATE on audit tables

Only the retention service (with a special session variable) can clean up old records.

⚙️ Configuration Options

builder.Services.AddObserver(options =>
{
    // Schema & table names
    options.AuditSchema = "observer";           // Default: "observer"
    options.AuditLogTableName = "audit_log";    // Default: "audit_log"
    options.AuditDetailTableName = "audit_detail"; // Default: "audit_detail"

    // Delivery mode
    options.DeliveryMode = AuditDeliveryMode.DatabaseOnly; // Default
    // options.DeliveryMode = AuditDeliveryMode.EventBusOnly;
    // options.DeliveryMode = AuditDeliveryMode.Both;

    // Auto-setup
    options.AutoCreateTables = true;            // Default: true
    options.AutoCreateTriggers = true;          // Default: true

    // Retention (global default)
    options.EnableRetention = false;            // Default: false
    options.RetentionPeriod = TimeSpan.FromDays(365);
    options.RetentionCheckInterval = TimeSpan.FromHours(24);

    // Retention per table (overrides global)
    options.TableRetentionOverrides["public.orders"] = TimeSpan.FromDays(1825); // 5 years
    options.TableRetentionOverrides["public.logs"] = TimeSpan.FromDays(90);      // 90 days

    // Protection
    options.ProtectAuditTables = true;          // Default: true

    // Metadata
    options.IncludeClientIp = true;             // Default: true
    options.IncludeUserAgent = false;           // Default: false

    // Resilience (Polly)
    options.Resilience.Enabled = true;                // Default: false
    options.Resilience.MaxRetryAttempts = 3;           // Default: 3
    options.Resilience.BaseDelay = TimeSpan.FromMilliseconds(200); // Default: 200ms
    options.Resilience.EnableCircuitBreaker = true;    // Default: false

    // Exclude sensitive columns globally
    options.ExcludedColumns.Add("CreditCardNumber");
    options.ExcludedColumns.Add("SSN");
});

🏷️ Excluding Columns with [AuditIgnore]

The recommended way to exclude properties from audit logs is with the [AuditIgnore] attribute:

using Appouse.Observer.Abstractions;

public class User : IAuditableEntity
{
    public int Id { get; set; }
    public string Email { get; set; }       // ✅ Audited
    public string FullName { get; set; }    // ✅ Audited
    public string Role { get; set; }        // ✅ Audited

    [AuditIgnore]                            // ❌ NOT audited
    public string PasswordHash { get; set; }

    [AuditIgnore]                            // ❌ NOT audited
    public string? SecurityStamp { get; set; }

    [AuditIgnore]                            // ❌ NOT audited
    public string? TwoFactorSecret { get; set; }
}

You can also exclude columns globally via ObserverOptions.ExcludedColumns (useful for cross-cutting exclusions like CreditCardNumber that apply to all entities):

options.ExcludedColumns.Add("CreditCardNumber");
options.ExcludedColumns.Add("SSN");

Note: Both methods work together. A property is excluded if either [AuditIgnore] is present or the column name is in ExcludedColumns.

🗄️ Database Providers

PostgreSQL

builder.Services.AddObserverPostgreSql("Host=localhost;Database=mydb;...");

Uses SET LOCAL for session context and PL/pgSQL functions for triggers.

SQL Server

builder.Services.AddObserverSqlServer("Server=localhost;Database=mydb;...");

Uses sp_set_session_context / SESSION_CONTEXT() and T-SQL triggers.

Oracle

builder.Services.AddObserverOracle("Data Source=...;User Id=...;Password=...;");

Uses DBMS_SESSION.SET_CONTEXT / SYS_CONTEXT() and PL/SQL triggers.

MySQL

builder.Services.AddObserverMySql("Server=localhost;Database=mydb;...");

Uses @user_defined_variables and separate INSERT/UPDATE/DELETE triggers.

🛡️ Resilience (Polly)

Observer includes built-in Polly v8 resilience support for audit operations. When enabled, all audit store operations (DB writes and/or event publishing) are automatically wrapped with:

• Retry with exponential backoff + jitter
• Circuit Breaker (optional) to prevent cascading failures

builder.Services.AddObserver(options =>
{
    options.Resilience.Enabled = true;

    // Retry configuration
    options.Resilience.MaxRetryAttempts = 3;                        // Default: 3
    options.Resilience.BaseDelay = TimeSpan.FromMilliseconds(200);  // Default: 200ms
    options.Resilience.MaxDelay = TimeSpan.FromSeconds(5);          // Default: 5s

    // Circuit breaker (optional)
    options.Resilience.EnableCircuitBreaker = true;                 // Default: false
    options.Resilience.CircuitBreakerFailureThreshold = 0.5;        // 50% failure rate
    options.Resilience.CircuitBreakerSamplingDuration = TimeSpan.FromSeconds(30);
    options.Resilience.CircuitBreakerBreakDuration = TimeSpan.FromSeconds(15);
    options.Resilience.CircuitBreakerMinimumThroughput = 10;
});

The resilience pipeline is applied as a decorator around whatever IAuditStore is active (EfAuditStore, CompositeAuditStore), so it works with all delivery modes. Combined with IdempotencyKey, retries are fully safe — duplicate entries are automatically filtered out.

📡 Message Queue Integration (MassTransit)

Observer can publish audit events to message brokers like RabbitMQ, Kafka, or Azure Service Bus using MassTransit. You can choose to write to the database only, publish events only, or do both.

Install Packages

# Core + DB provider + MassTransit
dotnet add package Appouse.Observer
dotnet add package Appouse.Observer.PostgreSql       # or SqlServer, Oracle, MySql
dotnet add package Appouse.Observer.MassTransit

# Optional: Transactional Outbox for reliable delivery
dotnet add package Appouse.Observer.MassTransit.Outbox

Delivery Modes

Example: DB + RabbitMQ

using Appouse.Observer.Extensions;
using Appouse.Observer.PostgreSql.Extensions;
using Appouse.Observer.MassTransit.Extensions;
using Appouse.Observer.Models;

var builder = WebApplication.CreateBuilder(args);

// 1. Observer core — write to both DB and event bus
builder.Services.AddObserver(options =>
{
    options.DeliveryMode = AuditDeliveryMode.Both;
    options.AutoCreateTables = true;
    options.AutoCreateTriggers = true;
    options.Resilience.Enabled = true;  // Retry on failures
});

builder.Services.AddObserverPostgreSql(
    builder.Configuration.GetConnectionString("DefaultConnection")!);

// 2. MassTransit with RabbitMQ
builder.Services.AddMassTransit(x =>
{
    x.UsingRabbitMq((context, cfg) =>
    {
        cfg.Host("rabbitmq://localhost");
        cfg.ConfigureEndpoints(context);
    });
});

// 3. Connect Observer to MassTransit
builder.Services.AddObserverMassTransit();

Example: RabbitMQ with Transactional Outbox

For guaranteed delivery, use the Outbox pattern. Messages are stored in the database first and delivered by a background service:

using Appouse.Observer.MassTransit.Outbox.Extensions;

// 1. Observer core
builder.Services.AddObserver(options =>
{
    options.DeliveryMode = AuditDeliveryMode.Both;
    options.Resilience.Enabled = true;
});
builder.Services.AddObserverPostgreSql(connectionString);

// 2. MassTransit with RabbitMQ + EF Core Outbox
builder.Services.AddMassTransit(x =>
{
    x.AddEntityFrameworkOutbox<AuditDbContext>(o =>
    {
        o.UsePostgres();   // or UseSqlServer(), UseMySql()
        o.UseBusOutbox();  // Enable Bus Outbox for publish scope
    });

    x.UsingRabbitMq((context, cfg) =>
    {
        cfg.Host("rabbitmq://localhost");
        cfg.ConfigureEndpoints(context);
    });
});

// 3. Observer MassTransit + Outbox
builder.Services.AddObserverMassTransit();
builder.Services.AddObserverMassTransitOutbox();

Example: Event Bus Only (No DB)

builder.Services.AddObserver(options =>
{
    options.DeliveryMode = AuditDeliveryMode.EventBusOnly;
});

builder.Services.AddMassTransit(x =>
{
    x.UsingRabbitMq((context, cfg) => cfg.Host("rabbitmq://localhost"));
});

builder.Services.AddObserverMassTransit();

Example: Kafka

builder.Services.AddMassTransit(x =>
{
    x.UsingInMemory();
    x.AddRider(rider =>
    {
        rider.AddProducer<AuditLogCreatedEvent>("audit-events-topic");
        rider.UsingKafka((ctx, k) => k.Host("localhost:9092"));
    });
});

Consuming Audit Events

Any service can consume the published events:

public class AuditEventConsumer : IConsumer<AuditLogCreatedEvent>
{
    public async Task Consume(ConsumeContext<AuditLogCreatedEvent> context)
    {
        var audit = context.Message;

        // Use IdempotencyKey for deduplication
        if (await _repo.ExistsAsync(audit.IdempotencyKey))
            return;

        Console.WriteLine($"[{audit.Operation}] {audit.SchemaName}.{audit.TableName} " +
                          $"by {audit.ApplicationUser} at {audit.TimestampUtc}");

        foreach (var detail in audit.Details)
        {
            Console.WriteLine($"  {detail.ColumnName}: {detail.OldValue} → {detail.NewValue}");
        }

        await _repo.SaveAsync(audit);
    }
}

🔧 Custom Metadata Provider

Override the default metadata provider for custom scenarios:

public class CustomMetadataProvider : IAuditMetadataProvider
{
    public string? GetApplicationUser() => "custom-user";
    public string? GetMethodName() => "custom-method";
    public string? GetActivityId() => Activity.Current?.Id;
    public string? GetTraceIdentifier() => null;
    public string? GetCorrelationId() => "custom-correlation";
    public string? GetClientIp() => null;
    public string? GetUserAgent() => null;
    public IDictionary<string, string>? GetAdditionalData() => 
        new Dictionary<string, string> { ["TenantId"] = "tenant-1" };
}

// Register before AddObserver
builder.Services.AddSingleton<IAuditMetadataProvider, CustomMetadataProvider>();

🏗️ Architecture

Your App ──→ EF Core ──→ ObserverInterceptor ──→ ResilientAuditStore (Polly)
                                                         │
                                                  CompositeAuditStore
                                                         │
                                              ┌──────────┴──────────┐
                                              ▼                     ▼
                                        EfAuditStore         IAuditEventPublisher
                                        (DB + Idempotency)   (MassTransit Publish)
                                                                   │
                                                    ┌──────────────┴──────────────┐
                                                    ▼                             ▼
                                              Direct Publish              Outbox Pattern
                                              (fire & forget)            (transactional)
                                                    │                             │
                                                    ▼                             ▼
                                              ┌─────────┐                ┌──────────────┐
                                              │RabbitMQ  │                │  DB Outbox   │
                                              │Kafka     │                │    Table     │
                                              │Azure SB  │                │      │       │
                                              │InMemory  │                │      ▼       │
                                              └─────────┘                │  Delivery    │
                                                                         │  Service → MQ │
                                                                         └──────────────┘

    Direct SQL / Other Apps  ──→  Database  ──→  DB Triggers  ──→  Audit Tables

• EF Core Interceptor: Captures changes via SaveChangesInterceptor, enriches with HTTP/Activity metadata
• DB Triggers: Captures ALL changes (including raw SQL), reads session context set by the interceptor
• CompositeAuditStore: Routes audit entries to DB, event bus, or both based on DeliveryMode
• ResilientAuditStore: Polly decorator providing retry + circuit breaker around any store
• IdempotencyKey: Guid assigned once, enforced with unique DB index, carried through events

📦 Build & Pack

# Build
dotnet build Appouse.Observer.sln -c Release

# Run tests
dotnet test Appouse.Observer.sln -c Release

# Pack all packages (or use pack.bat)
dotnet pack Appouse.Observer.sln -c Release -o ./artifacts/packages

# Or simply:
.\pack.bat

# Push to local feed
dotnet nuget push ./artifacts/packages/*.nupkg -s C:\LocalNuGetFeed

# Push to NuGet.org
dotnet nuget push ./artifacts/packages/*.nupkg -s https://api.nuget.org/v3/index.json -k YOUR_API_KEY

📁 Project Structure

Appouse.Observer/
├── src/
│   ├── Appouse.Observer/                    # Core library (Polly, Idempotency)
│   ├── Appouse.Observer.PostgreSql/         # PostgreSQL provider
│   ├── Appouse.Observer.SqlServer/          # SQL Server provider
│   ├── Appouse.Observer.Oracle/             # Oracle provider
│   ├── Appouse.Observer.MySql/              # MySQL provider
│   ├── Appouse.Observer.MassTransit/        # MassTransit messaging
│   └── Appouse.Observer.MassTransit.Outbox/ # Transactional Outbox
├── tests/
│   ├── Appouse.Observer.UnitTests/
│   └── Appouse.Observer.IntegrationTests/
├── samples/
│   └── Appouse.Observer.Sample/
├── docs/
│   └── ARCHITECTURE.md
└── pack.bat                                 # NuGet package build script

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

🤝 Contributing

Contributions are welcome! Please open an issue or submit a pull request.