Centeva.Auditing 5.1.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.

Table of Contents

• Features
• BREAKING CHANGES in v5.0
• Migration from v4.x (Single-Table to Dual-Table)
• Audit Trigger Script Generation
• Audit Archiving
  • Archive Process
  • Ignoring Specific Tables or Columns
  • Column-Level Ignore Behavior
  • Adding Ignores After Archiving
• Getting Started (New Installations)
• Customizing Table and Schema Names
• Configuration Reference
• Example: Dependency Injection Setup
• Architecture
• Usage Notes
• Compatibility
• TODO
• Contributing
• License

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.

---

Audit Archiving

The audit archiving feature automatically moves old audit records from your application database to a centralized archive database (e.g., RPS_AuditArchive). This keeps your application database performant while preserving historical audit data for compliance and analysis.

Why Archive Audit Data?

• Performance: Keeps application audit tables small and queries fast
• Compliance: Preserves historical audit data in a centralized location
• Storage Management: Moves old data to dedicated archive infrastructure
• Multi-Application Support: Centralized archive database supports multiple applications using schema-based isolation

Archive Database Structure

The archive database uses schema-based isolation where each application gets its own schema:

• Application schemas: Licensing, TRP, OL, Oversight, etc.
• Tables in each schema: [SchemaName].[Audit] and [SchemaName].[AuditDetail]
• Archive tables include additional columns: ArchiveAuditId, AuditDetailId, ArchivedDate
• Original IDs (AuditId, AuditDetailId) are preserved for traceability

Configuration

1. Add Settings to appsettings.json

{
  "ConnectionStrings": {
    "Application": "Server=.;Database=YourApp;Integrated Security=true;",
    "AuditArchive": "Server=.;Database=RPS_AuditArchive;Integrated Security=true;"
  },
  "DatabaseMaintenance": {
    "Auditing": {
      "AuditSchema": "dbo",
      "AuditTable": "Audit",
      "AuditDetailTable": "AuditDetail",
      "AlwaysUpdateTriggers": true,
      "Archive": {
        "Enabled": true,
        "DaysBeforeArchive": 365,
        "DestinationSchema": "YourApplicationName",
        "BatchSize": 100000
      }
    }
  }
}

Configuration Options:

• Enabled - Enable/disable archiving (default: false)
• DaysBeforeArchive - Retention period in days before records are archived (default: 365)
• DestinationSchema - Application name for schema isolation in archive DB (required when enabled; e.g., Licensing, TRP)
• BatchSize - Number of detail records to process per batch (default: 100000)

2. Register Services in Program.cs

using Centeva.Auditing.Extensions;

// Register auditing and archiving services
builder.Services.AddCentevaAuditing(
    builder.Configuration,
    applicationConnectionString: builder.Configuration.GetConnectionString("Application"),
    archiveConnectionString: builder.Configuration.GetConnectionString("AuditArchive")
);

This registers:

• AuditConfig - Configuration for audit table names and connection
• ArchiveConfig - Configuration for archive settings
• IAuditArchiveService - Service for archiving operations (scoped lifetime)

Using the Archive Service

Manual Archiving

Inject IAuditArchiveService and call ArchiveItems():

using Centeva.Auditing;

public class AuditMaintenanceService(IAuditArchiveService archiveService, ILogger<AuditMaintenanceService> logger)
{
    public async Task ArchiveOldAuditRecordsAsync(CancellationToken cancellationToken = default)
    {
        logger.LogInformation("Starting audit archiving...");

        var success = await archiveService.ArchiveItems(cancellationToken);

        if (success)
        {
            logger.LogInformation("Audit archiving completed successfully.");
        }
        else
        {
            logger.LogWarning("Audit archiving failed or was disabled.");
        }
    }
}

Hangfire Scheduled Job (Recommended)

Run archiving on a schedule using Hangfire for reliable, persistent job scheduling:

1. Install Hangfire NuGet packages:

dotnet add package Hangfire.AspNetCore
dotnet add package Hangfire.SqlServer

2. Configure Hangfire in Program.cs:

using Hangfire;
using Hangfire.SqlServer;

// Add Hangfire services
builder.Services.AddHangfire(configuration => configuration
    .SetDataCompatibilityLevel(CompatibilityLevel.Version_180)
    .UseSimpleAssemblyNameTypeSerializer()
    .UseRecommendedSerializerSettings()
    .UseSqlServerStorage(builder.Configuration.GetConnectionString("Hangfire")));

// Add the processing server as IHostedService
builder.Services.AddHangfireServer();

var app = builder.Build();

// Configure Hangfire dashboard (optional - for monitoring)
app.UseHangfireDashboard("/hangfire", new DashboardOptions
{
    Authorization = new[] { new HangfireAuthorizationFilter() }
});

// Schedule the audit archiving job (runs daily at 2 AM)
RecurringJob.AddOrUpdate<AuditArchiveJob>(
    "audit-archiving",
    job => job.ExecuteAsync(CancellationToken.None),
    Cron.Daily(2) // 2 AM daily
);

3. Create the Hangfire job class:

using Centeva.Auditing;

public class AuditArchiveJob
{
    private readonly IAuditArchiveService _archiveService;
    private readonly ILogger<AuditArchiveJob> _logger;

    public AuditArchiveJob(
        IAuditArchiveService archiveService,
        ILogger<AuditArchiveJob> logger)
    {
        _archiveService = archiveService;
        _logger = logger;
    }

    public async Task ExecuteAsync(CancellationToken cancellationToken)
    {
        _logger.LogInformation("Starting scheduled audit archiving job...");

        try
        {
            var success = await _archiveService.ArchiveItems(cancellationToken);

            if (success)
            {
                _logger.LogInformation("Scheduled audit archiving completed successfully.");
            }
            else
            {
                _logger.LogWarning("Audit archiving was skipped (disabled or failed).");
            }
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error during scheduled audit archiving.");
            throw; // Hangfire will handle retries
        }
    }
}

4. Add connection string to appsettings.json:

{
  "ConnectionStrings": {
    "Hangfire": "Server=.;Database=YourApp;Integrated Security=true;"
  }
}

Benefits of Hangfire:

• ✅ Persistent scheduling: Jobs survive application restarts
• ✅ Automatic retries: Failed jobs are automatically retried
• ✅ Dashboard: Monitor job history, failures, and performance at /hangfire
• ✅ Distributed: Supports multiple application instances without duplicate execution
• ✅ Cancellation: Jobs can be cancelled or rescheduled via dashboard
• ✅ Logging: Built-in job execution history and logging

Archive Process

The ArchiveItems() method performs the following steps:

1. Check Configuration: Validates that archiving is enabled and properly configured
2. Select Records: Queries audit records older than the retention period using batch processing
3. Create Archive Schema/Tables: Automatically creates the application schema and tables in the archive database if they don't exist
4. Bulk Copy: Uses SqlBulkCopy to efficiently copy records to the archive database
5. Selective Delete: Removes only the archived detail records from the source database. Audit records are deleted only when all their details have been archived (prevents data loss with column-level ignores)
6. Transaction Safety: All operations are transactional - if any step fails, changes are rolled back

Advanced Configuration

Ignoring Specific Tables or Columns

You can exclude specific schemas, tables, or columns from archiving:

using Centeva.Auditing.Helpers;

// Register custom ignore rules
builder.Services.AddSingleton<ArchiveIgnore[]>(provider => new[]
{
    ArchiveIgnore.Create("temp"),                          // Ignore entire schema
    ArchiveIgnore.Create("dbo", "Log"),                    // Ignore specific table
    ArchiveIgnore.Create("dbo", "Users", "Password")       // Ignore specific column
});

Column-Level Ignore Behavior:

When you use column-level ignores (e.g., ignoring the Password column):

1. First Archive: The ignored column details remain in the source database, while other field changes are archived
2. Audit Record Preservation: The parent audit record is preserved as long as any unarchived details exist
3. Remove Ignore & Re-Archive: If you later remove the ignore rule, the previously-ignored details will be archived on the next run
4. Duplicate Prevention: The archive service automatically detects existing audit records and only archives new detail records, preventing duplicates

Example Scenario:

// Initial configuration: ignore Password field
var ignores = new[] { ArchiveIgnore.Create("dbo", "Users", "Password") };

// First archive run:
// - Email, PhoneNumber details → archived ✓
// - Password details → remain in source database ✓
// - Audit record → remains in source database (has unarchived details) ✓

// Remove the ignore rule
var ignores = new ArchiveIgnore[0];

// Second archive run:
// - Password details → now archived ✓
// - Audit record → now deleted (all details archived) ✓
// - No duplicate audit records created ✓

** Adding Ignores After Archiving:**

If you add a new ArchiveIgnore rule after data has already been archived, those records have been permanently moved to the archive database and deleted from the source. To prevent future archiving of those records:

1. Add the ignore rule to your configuration
2. Manually restore records from archive to source (if needed for operational purposes)

Manual Restoration Process:

-- Example: Restore Password details that were already archived
-- Step 1: Copy audit records back to source (if they don't exist)
INSERT INTO [dbo].[Audit] (AuditId, Type, TableName, PK, UpdateDate, UserName)
SELECT AuditId, Type, TableName, PK, UpdateDate, UserName
FROM [ArchiveSchema].[Audit]
WHERE TableName = 'dbo.Users' 
  AND AuditId NOT IN (SELECT AuditId FROM [dbo].[Audit]);

-- Step 2: Copy specific detail records back to source
INSERT INTO [dbo].[AuditDetail] (AuditDetailId, AuditId, FieldName, OldValue, NewValue)
SELECT AuditDetailId, AuditId, FieldName, OldValue, NewValue
FROM [ArchiveSchema].[AuditDetail]
WHERE FieldName = 'Password'
  AND AuditDetailId NOT IN (SELECT AuditDetailId FROM [dbo].[AuditDetail]);

Important: Restored records will remain in the source database indefinitely if they match ignore rules. Consider your data retention policies before restoring archived data.

Custom Batch Size

Adjust batch size based on your database size and performance requirements:

{
  "DatabaseMaintenance": {
    "Auditing": {
      "Archive": {
        "BatchSize": 50000  // Smaller batches for limited memory
      }
    }
  }
}

Monitoring and Troubleshooting

The archive service logs detailed information at each step:

• LogInformation - Normal progress updates (records selected, copied, deleted)
• LogWarning - Archiving disabled or no records to archive
• LogError - Failures with full exception details

Common Issues:

• "Archive connection string is not configured": Verify connection string in appsettings.json
• "Schema name must be provided when archiving is enabled": Set Archive:DestinationSchema to your application name
• Archive schema not created: Ensure the archive database connection has CREATE SCHEMA permissions
• Performance issues: Reduce BatchSize or schedule archiving during off-peak hours

Archive Database Setup

The archive database is automatically initialized when archiving runs for the first time:

1. Schema Creation: Application-specific schema (e.g., [Licensing])
2. Table Creation: Audit and AuditDetail tables with archive-specific columns
3. Indexes: Optimized indexes for common query patterns

Manual Setup (Optional):

If you prefer to pre-create the archive structure:

-- Create application schema
CREATE SCHEMA [YourApplicationName];

-- Tables will be auto-created on first archive run
-- Or use AuditArchiveService.CreateArchiveTablesIfNotExists()

---

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