Centeva.Auditing 5.0.0

Centeva.Auditing

This library provides audit entity models and Entity Framework Core configurations for SQL Server auditing. It is designed for use with EF Core (.NET 8, C# 12) and is intended to be ingested via NuGet by other applications.

Features

• Dual-Table Audit Schema: Separate Audit and AuditDetail tables for normalized, performant audit logging.
• Audit & AuditDetail Models: Standardized entities for audit logging with EF Core support.
• EntityTypeConfigurations: Fluent API configurations for Audit and AuditDetail tables.
• AuditIgnore Helpers: Utilities for ignoring tables, schemas, or columns during audit operations.
• AuditScriptCreator: Generates SQL scripts for audit triggers.
• AuditMigrationHelper: Automated tool for migrating from single-table to dual-table audit schema.
• Convention Support: Includes conventions for automatic trigger creation in EF Core.

BREAKING CHANGES in v5.0

Single-table audit logging has been removed. This library now exclusively uses a dual-table schema with separate Audit and AuditDetail tables.

Why Dual-Table?

The dual-table approach provides:

• Better Performance: Normalized schema reduces data duplication and improves query speed
• Flexibility: Easier to query operation-level or field-level changes independently
• Scalability: Optimized for large-scale audit databases (100M+ records)

Migration from v4.x (Single-Table to Dual-Table)

If you're upgrading from v4.x that used single-table auditing, you must migrate your existing audit data. The AuditMigrationHelper automates this process.

Prerequisites

• ⚠️ Create a full database backup before starting migration
• ⚠️ Plan for application downtime during the final swap phase
• Ensure you have sufficient database disk space (migration creates shadow tables)

Migration Process

Phase 1: Data Migration (No Downtime)

This phase migrates data to shadow tables while your application continues running:

using Centeva.Auditing.Helpers;
using Microsoft.Extensions.Logging;

var config = new AuditConfig(
    schema: "dbo",
    auditTable: "Audit",
    auditDetailTable: "AuditDetail",
    connectionString: "your-connection-string"
);

// Create logger (optional but recommended for progress tracking)
using var loggerFactory = LoggerFactory.Create(builder => builder.AddConsole());
var logger = loggerFactory.CreateLogger<Program>();

// Run migration (can take hours for large databases)
await AuditMigrationHelper.MigrateSingleToDualTable(
    config, 
    logger: logger,
    batchSize: 100_000  // Adjust based on your database size
);

Migration is resumable - if interrupted, you can re-run the same command to continue.

Phase 2: Table Swap (Requires Downtime)

After data migration completes, generate and execute the swap script during a maintenance window:

// Generate swap script
var scripts = await AuditMigrationHelper.GenerateMigrationScripts(config, logger);

// Save script to file for DBA review
File.WriteAllText("audit-table-swap.sql", scripts.SwapScript);

// Execute during maintenance window:
// 1. Stop application
// 2. Run the swap script in SQL Server Management Studio
// 3. Start application with updated trigger scripts

The swap script:

• Runs in a transaction (automatic rollback on failure)
• Includes pre-flight validation checks
• Takes ~30 seconds for most databases
• Transforms original Audit table → AuditDetail
• Renames Audit_Migration shadow table → Audit

Post-Migration Steps

1. Regenerate audit triggers for dual-table schema:

using var creator = new AuditScriptCreator(config);
foreach (var script in creator.GetTriggerScripts())
{
    // Execute against your database
}

2. Update your DbContext to reference both tables:

public DbSet<AuditTable> Audit { get; set; }
public DbSet<AuditDetail> AuditDetail { get; set; }

3. Remove single-table references from your codebase

Migration Safety Features

• ✅ Idempotent: Safe to re-run if interrupted
• ✅ Validation: Prevents running on already-migrated databases
• ✅ Transactional: Swap script uses transactions with automatic rollback
• ✅ Original Data Preserved: Source table unchanged during migration
• ✅ Progress Tracking: Detailed logging of migration progress

Rollback (If Needed)

If migration needs to be rolled back before the swap phase:

await AuditMigrationHelper.RollbackMigration(config, logger);

This removes shadow tables and re-enables audit triggers.

Audit Trigger Script Generation

Audit Logging

Summary audit data is logged to the Audit table, and field-level changes are logged to the AuditDetail table.

Configuring Auditing

using Centeva.Auditing.Helpers;
var config = new AuditConfig( schema: "dbo", auditTable: "Audit", auditDetailTable: "AuditDetail", connectionString: "your-connection-string" );

Generating Trigger Scripts

Use the AuditScriptCreator class to generate SQL scripts for audit triggers.

using Centeva.Auditing.Helpers;

var config = new AuditConfig( schema: "dbo", auditTable: "Audit", auditDetailTable: "AuditDetail", connectionString: "your-connection-string" );
using var creator = new AuditScriptCreator(config);

foreach (var script in creator.GetTriggerScripts(/* optional AuditIgnore rules */))
{
  // Execute script against your database
}

Note: The AuditScriptCreator class manages the connection lifecycle internally and implements IDisposable. Always use a using statement or call Dispose() when finished.

Getting Started (New Installations)

Note: If you're migrating from v4.x single-table schema, see the Migration from v4.x section above first.

1. Install the Package

Add the NuGet package to your project:

dotnet add package Centeva.Auditing

2. Add Entity Configurations to Your DbContext

In your application's DbContext, register the provided configurations in OnModelCreating:

using Centeva.Auditing.Configurations;

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
  modelBuilder.ApplyConfiguration(new AuditTableConfiguration(auditSchema, auditTable));
  modelBuilder.ApplyConfiguration(new AuditDetailConfiguration(auditSchema, auditDetailTable));

  // Register other configurations as needed
}

3. Use the Audit Models

using Centeva.Auditing.Models;

// Dual-table audit logging requires both DbSets
public DbSet<AuditTable> Audit { get; set; }        // Operation-level audit metadata
public DbSet<AuditDetail> AuditDetail { get; set; } // Field-level change details

4. Generate and Deploy Audit Triggers

using Centeva.Auditing.Helpers;

var config = new AuditConfig(
    schema: "dbo",
    auditTable: "Audit",
    auditDetailTable: "AuditDetail",
    connectionString: "your-connection-string"
);

using var creator = new AuditScriptCreator(config);

foreach (var script in creator.GetTriggerScripts(/* optional AuditIgnore rules */))
{
  // Execute script against your database
}

Customizing Table and Schema Names

You can configure the schema and table names for the Audit and AuditDetail entities at runtime, allowing you to match your existing database without requiring a migration.

1. Add Settings to appsettings.json

{
  "Auditing": {
    "AuditSchema": "dbo",
    "AuditTable": "Audit",
    "AuditDetailTable": "AuditDetail"
  }
}

2. Pass Settings to Configuration Classes

Update your DbContext to read these values and pass them to the configuration classes:

using Microsoft.Extensions.Configuration;
using Centeva.Auditing.Configurations;
public class YourDbContext(DbContextOptions<YourDbContext> options, IConfiguration configuration) : DbContext(options)
{
  private readonly IConfiguration _configuration = configuration;

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
    var auditSchema = _configuration["Auditing:AuditSchema"] ?? "dbo";
    var auditTable = _configuration["Auditing:AuditTable"] ?? "Audit";
    var auditDetailTable = _configuration["Auditing:AuditDetailTable"] ?? "AuditDetail";

    modelBuilder.ApplyConfiguration(new AuditTableConfiguration(auditSchema, auditTable));
    modelBuilder.ApplyConfiguration(new AuditDetailConfiguration(auditSchema, auditDetailTable));
  }
}

3. No Migration Needed

As long as the schema and table names you specify match your existing database, no migration is required.

Configuration Reference

AuditConfig

• Controls schema, table names, and connection string for audit operations.
• Used by AuditScriptCreator and AuditMigrationHelper.

AuditTableConfiguration

• Maps the AuditTable entity to the Audit table.
• Configures columns, types, and relationships for operation-level audit data.

AuditDetailConfiguration

• Maps the AuditDetail entity to the AuditDetail table.
• Configures columns, types, and relationships for field-level change data.

AuditMigrationHelper

• Purpose: Migrates from single-table (v4.x) to dual-table (v5.0+) audit schema.
• Methods:
  • MigrateSingleToDualTable() - Performs data migration to shadow tables
  • GenerateMigrationScripts() - Generates SQL swap script for DBA execution
  • RollbackMigration() - Rolls back incomplete migration

AuditScriptCreator

• Purpose: Generates SQL scripts for audit triggers on database tables.
• Important: Always regenerate triggers after schema changes or version upgrades.

AuditIgnore Helpers

• Use AuditIgnore.Create(schema), AuditIgnore.Create(schema, table), or AuditIgnore.Create(schema, table, column) to specify audit exclusions.
• Pass to AuditScriptCreator.GetTriggerScripts() to exclude specific tables/columns from auditing.

Example: Dependency Injection Setup

If you use dependency injection, register your DbContext and any audit-related services as usual:

services.AddDbContext<YourDbContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

Architecture

This library exclusively uses a dual-table approach for audit logging:

Audit Table (Operation-Level)

Stores metadata about each audited database operation:

• AuditId - Unique identifier for the operation
• TableName - Name of the table being audited
• PK - Primary key value of the affected record
• Type - Operation type (I=Insert, U=Update, D=Delete)
• UpdateDate - Timestamp of the operation
• UserName - User who performed the operation

AuditDetail Table (Field-Level)

Stores individual field changes for each operation:

• Id - Unique identifier for the detail record
• AuditId - Foreign key to the Audit table
• FieldName - Name of the changed field
• OldValue - Previous value (NULL for inserts)
• NewValue - New value (NULL for deletes)

Benefits Over Single-Table Design

✅ Performance: Normalized schema reduces data duplication and improves query speed
✅ Flexibility: Query operation-level or field-level changes independently
✅ Scalability: Optimized for large databases (tested with 100M+ records)
✅ Maintainability: Cleaner schema with better separation of concerns
✅ Query Efficiency: Indexes can target specific access patterns

Example Query: Get all operations for a specific record:

SELECT * FROM Audit WHERE TableName = 'Users' AND PK = '12345'

Example Query: Get field-level changes for an operation:

SELECT ad.* 
FROM AuditDetail ad
INNER JOIN Audit a ON a.AuditId = ad.AuditId
WHERE a.TableName = 'Users' AND a.PK = '12345'
ORDER BY a.UpdateDate DESC

Usage Notes

• Schema Changes: The library exclusively uses dual-table schema. Single-table auditing is not supported in v5.0+.
• Migrations: If you change table names in the configuration, EF Core will generate a migration to rename the table. Table renames are fast metadata operations in SQL Server.
• Column Types: The default configuration uses nvarchar(max) for OldValue and NewValue in AuditDetail to support large audit values.
• Performance: For databases with 100M+ records, use appropriate batch sizes during migration (default: 100,000 records per batch).
• Extensibility: You can extend the models or configurations as needed for your application.
• Upgrading from v4.x: Use AuditMigrationHelper to migrate existing single-table audit data. See Migration from v4.x.

Compatibility

• .NET 8
• C# 12
• Microsoft.EntityFrameworkCore 8.x

TODO

• Refactor AuditScriptCreator to implement an interface and register as a service for improved testability and dependency injection support.

Contributing

Contributions and feedback are welcome. Please open issues or submit pull requests for improvements.

License

Copyright © 2019