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

Table of Contents

• Features
• BREAKING CHANGES in v6.0
• BREAKING CHANGES in v5.0
• Migration from v4.x (Single-Table to Dual-Table)
• Audit Triggers
• Audit Archiving
  • Archive Process
  • Ignoring Specific Tables or Columns
  • Column-Level Ignore Behavior
  • Adding Ignores After Archiving
• Unified Audit Views
• Getting Started (New Installations)
• Customizing Table and Schema Names
• Configuration Reference
• Example: Dependency Injection Setup
• Architecture
• Usage Notes
• Compatibility
• 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.
• IAuditTriggerService: Creates, replaces, removes, disables and enables the audit triggers.
• AuditMigrationHelper: Automated tool for migrating from single-table to dual-table audit schema.
• Unified Audit Views: Package-owned views combining current and archived audit records.
• Convention Support: Includes conventions for automatic trigger creation in EF Core.

BREAKING CHANGES in v6.0

• AuditScriptCreator is removed. Inject IAuditTriggerService and call SyncAsync instead of executing generated scripts.
• Audit triggers are matched by the prefix audit_. Earlier versions also matched app triggers such as auditlog_upd and dropped them.

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 by calling IAuditTriggerService.SyncAsync() (see Audit Triggers).

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 Triggers

Summary audit data is logged to the Audit table, and field-level changes are logged to the AuditDetail table, by one trigger per audited table. IAuditTriggerService (registered by AddCentevaAuditing) maintains them:

Method	What it does
SyncAsync	Creates a trigger on each audited table that lacks one, replaces any whose definition is out of date, and removes triggers whose table is gone or now ignored. All changes run as one batch in one transaction.
DisableAllAsync	Disables every audit trigger, so maintenance writes such as seeding are not audited.
EnableAllAsync	Enables every disabled audit trigger, including any an earlier run left disabled.

Each method opens its own connection with the connection string passed to AddCentevaAuditing.

await auditTriggers.DisableAllAsync(cancellationToken);
try
{
    await dbContext.Database.MigrateAsync(cancellationToken);
    await auditTriggers.SyncAsync(cancellationToken);
}
finally
{
    await auditTriggers.EnableAllAsync(CancellationToken.None);
}

SyncAsync logs one Information summary (Audit triggers: 2 added, 1 replaced, 0 removed, 27 unchanged.) and a Debug entry per changed trigger, with the trigger name in the Trigger property. A failed sync logs an Error naming the trigger it failed on. DisableAllAsync logs a Warning naming any audit trigger that was already disabled, which means an earlier run stopped before enabling it.

Audit triggers are matched by the name prefix audit_. Triggers of your own with other names are never touched.

---

Audit Archiving

The audit archiving feature automatically moves old audit records from your application database to a configured archive database. 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 are selected by each consuming application.
• Tables in each schema: [SchemaName].[Audit] and [SchemaName].[AuditDetail]
• Archive tables include additional columns: ArchiveAuditId, ArchiveAuditDetailId, and 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=AuditArchive;Integrated Security=true;"
  },
  "DatabaseMaintenance": {
    "Auditing": {
      "AuditSchema": "dbo",
      "AuditTable": "Audit",
      "AuditDetailTable": "AuditDetail",
      "AlwaysUpdateTriggers": false,
      "Archive": {
        "Enabled": true,
        "DaysBeforeArchive": 365,
        "DestinationSchema": "YourApplicationName",
        "AuditIdRangeSize": 20000,
        "DeleteChunkSize": 2000,
        "MaxRunDuration": "00:45:00",
        "DelayBetweenBatches": "00:00:00.250",
        "MaxDeadlockRetries": 20,
        "PreReadPages": true
      }
    }
  }
}

Configuration Options:

• AlwaysUpdateTriggers - Replace every audit trigger on each run instead of only those whose table changed (default: false; was true before v5.3). See IAuditTriggerService.
• Enabled - Enable/disable archiving (default: false)
• DaysBeforeArchive - Retention period in days before records are archived (default: 365)
• DestinationSchema - Application schema used for isolation in the archive database (required for archiving and unified-view maintenance)
• AuditIdRangeSize - Width of each AuditId range processed as one transaction (default: 20000). Only the batching options below need changing in unusual environments; the defaults were measured on Azure SQL Managed Instance.
• DeleteChunkSize - Maximum AuditDetail rows deleted per statement, 1 to 5000 (default: 2000). Values above 5000 would trigger SQL Server lock escalation and are rejected.
• MaxRunDuration - Time budget for one ArchiveItems() call (default: 00:45:00). The run stops after the batch in progress and resumes on the next call. Keep it below any concurrency timeout on the scheduled job.
• DelayBetweenBatches - Pause between committed batches (default: 00:00:00.250). Lengthen it if the version store or transaction log grows faster than the server reclaims it.
• MaxDeadlockRetries - Retries per range after a deadlock or lock timeout (default: 20)
• PreReadPages - Read each batch's pages into memory with a prefetching query before deleting (default: true). Leave on; it is the single largest speed-up on remote storage.
• BatchSize - Removed in 7.0.0. If your configuration still sets it, the key is ignored; delete it.

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)
• IUnifiedAuditViewService - Service for generating and maintaining unified views (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

Archiving runs inside SQL Server; no audit data passes through the application. One ArchiveItems() call:

1. Checks the setup: archiving enabled, both databases on the same instance (not Azure SQL Database), the application login can insert into the archive tables, and AuditDetail has an index led by AuditId. Any failure is logged and the call returns false.
2. Creates the archive schema and tables in the archive database if they do not exist.
3. Finds the key range: the retention cutoff is computed on the server clock, and the run covers AuditId values from the lowest in the table up to the highest row older than the cutoff.
4. Walks that range in steps of AuditIdRangeSize. For each step it stages the eligible Audit keys and their detail keys in temp tables (applying ignore rules), skips the step if nothing is eligible, and optionally pre-reads the pages it is about to change.
5. Archives the step in one transaction: copies details and parents to the archive with INSERT ... SELECT ... WHERE NOT EXISTS, verifies every staged key is now in the archive, deletes the details in chunks of DeleteChunkSize by clustered key, deletes parents that have no detail rows left, and commits. A deadlock or lock timeout rolls back and retries the same step; any other error rolls back and ends the run.
6. Stops when caught up or when MaxRunDuration has elapsed, logs a summary, and returns true. The next call resumes from the first unarchived step.

Because every copy is guarded by NOT EXISTS and every delete happens after verification, re-running after any failure is safe and never duplicates archive rows.

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 stays in the source database as long as any detail rows remain for it. An audit record whose details are all ignored is never copied.
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: Every copy checks the archive for the same AuditId or AuditDetailId first, so re-archiving never duplicates rows
5. Audit records without details (for example from a trigger whose columns are all ignored for auditing) are archived like any other row. Versions before 7.0 never selected them.

Prefer a table-level ignore when every audited column of a table is ignored. Column-level ignores keep those audit rows in the source database, where they occupy the start of the key range and are re-examined on every run.

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.

Tuning the Batch Loop

The defaults (AuditIdRangeSize 20000, DeleteChunkSize 2000, DelayBetweenBatches 250 ms) were measured on an Azure SQL Managed Instance archiving 20 million detail rows in under two hours. Change them only with a reason:

• A typical batch log line looks like Archive batch 12 AuditId 240001-260000: audit 3540/3540, detail 7550/7550 (copied/deleted) [stage 180 preread 250 copyD 320 copyA 90 verify 25 delD 300 delA 480 ms]. Copied and deleted counts should match. If batches finish in well under a second, a larger AuditIdRangeSize reduces per-batch overhead.
• DeleteChunkSize must stay at or below 5000; above that SQL Server escalates to a table lock, which is exactly what deadlocks with the audit triggers.
• MaxRunDuration must be shorter than any concurrency timeout on the job. The Hangfire example below uses [DisableConcurrentExecution(timeoutInSeconds: 3600)] with the default 45-minute budget.
• On Azure SQL Managed Instance the write rate is capped by the tier's log-rate governor and Accelerated Database Recovery keeps deleted-row versions until cleanup runs. If sys.dm_tran_persistent_version_store_stats or data-file free space keeps growing between runs, lengthen DelayBetweenBatches.

{
  "DatabaseMaintenance": {
    "Auditing": {
      "Archive": {
        "AuditIdRangeSize": 40000,
        "MaxRunDuration": "00:20:00"
      }
    }
  }
}

Topology and Permissions

• The application and archive databases must be on the same SQL Server instance. The service compares SERVERPROPERTY('ServerName') on both connections and refuses to run otherwise.
• Azure SQL Database is not supported for archiving because it cannot run cross-database queries. SQL Server and Azure SQL Managed Instance are supported.
• The login on the application connection string copies rows into the archive, so it needs SELECT and INSERT on the archive Audit and AuditDetail tables. The run verifies this with HAS_PERMS_BY_NAME before touching any data.
• The archive connection string is used to detect the topology and to create the archive schema and tables.
• AuditDetail must have an index whose leading key is AuditId. The package's AuditDetailConfiguration declares it; if your migrations never created it, add it before enabling archiving:

CREATE NONCLUSTERED INDEX IX_AuditDetail_AuditId ON dbo.AuditDetail (AuditId) WITH (ONLINE = ON);

• An index led by UpdateDate on Audit (also declared by AuditTableConfiguration) turns the once-per-run bounds query into a seek. Without it the service logs a warning and scans Audit once per run.

Monitoring and Troubleshooting

The archive service logs at these levels:

• LogInformation - A line per committed batch with row counts and per-step timings, and a summary at the end of the run (caught up, or paused at the next AuditId because the time budget elapsed)
• LogWarning - A deadlock or lock-timeout retry, a missing UpdateDate index, or page pre-read disabled because an index hint could not be honored
• LogError - A failed run with the full exception; the batch in progress was rolled back

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
• "must be on the same SQL Server instance": Point both connection strings at one instance. See Topology and Permissions.
• "Azure SQL Database (EngineEdition 5) does not support cross-database queries": Archiving needs SQL Server or Azure SQL Managed Instance.
• "The application database login cannot INSERT into ...": Grant the application login SELECT and INSERT on both archive tables.
• "requires an index on ... with AuditId as its leading key": Create IX_AuditDetail_AuditId as shown above.
• Archive schema not created: Ensure the archive database connection has CREATE SCHEMA permissions
• Duplicate rows in the archive left by versions before 7.0: harmless to the service and to the unified views, which keep one row per key. To remove them:

;WITH d AS (SELECT ArchiveAuditDetailId, ROW_NUMBER() OVER (PARTITION BY AuditDetailId ORDER BY ArchiveAuditDetailId) rn
            FROM [YourSchema].[AuditDetail])
DELETE FROM d WHERE rn > 1;
;WITH d AS (SELECT ArchiveAuditId, ROW_NUMBER() OVER (PARTITION BY AuditId ORDER BY ArchiveAuditId) rn
            FROM [YourSchema].[Audit])
DELETE FROM d WHERE rn > 1;

Archive Database Setup

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

1. Schema Creation: Application-specific schema
2. Table Creation: Audit and AuditDetail tables with archive-specific columns
3. Indexes: Optimized indexes for common query patterns

The archive structure is created automatically through either package workflow:

• IAuditArchiveService.ArchiveItems() creates it at the start of every run.
• IUnifiedAuditViewService.EnsureViews() creates it before creating or updating the unified views.

Calling both is appropriate when an application performs both archiving and unified-view maintenance, but it is not required merely to initialize the archive tables. Archive-object initialization is idempotent in both workflows.

---

Unified Audit Views

The latest package creates and maintains two reporting views in the configured audit schema:

• vw_Audit combines current and archived Audit rows.
• vw_AuditDetail combines current and archived AuditDetail rows.

Invoke the idempotent ensure operation after the application's database-update step:

var unifiedViews = serviceProvider.GetRequiredService<IUnifiedAuditViewService>();
await unifiedViews.EnsureViews(cancellationToken);

When archive configuration is enabled and complete, each invocation creates missing archive schema objects and creates or updates both views. When archiving is disabled or incomplete, EnsureViews() is a successful no-op. Operational failures are logged and returned as false so the application can continue starting. The application and archive connection strings must target databases on the same SQL Server instance.

For DBA review, generate the scripts without changing either database:

var scripts = await unifiedViews.GenerateScripts(cancellationToken);

GenerateScripts() requires archiving to be enabled with an archive connection string and destination schema.

Queries use the application database views and reconstruct events through vw_Audit.AuditId = vw_AuditDetail.AuditId. Reporting identities need SELECT on the views and the referenced archive schema because cross-database ownership chaining is not required or enabled by the package. The identity running EnsureViews needs DDL rights in the source audit schema, permission to create the archive schema and tables when absent, and metadata access to both databases. The package does not create principal-specific grants.

---

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. Create the Audit Triggers

Register the services with AddCentevaAuditing (see Example: Dependency Injection Setup), then sync the triggers at startup after migrations:

await using var scope = app.Services.CreateAsyncScope();
await scope.ServiceProvider.GetRequiredService<IAuditTriggerService>().SyncAsync();

Centeva.DatabaseMaintenance does this for you, with the triggers disabled while migrations and seeders run.

---

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 IAuditTriggerService 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

IAuditTriggerService

• Purpose: Keeps one audit trigger on every audited table. See Audit Triggers.
• Change detection: The script each auditable table needs is generated on every run and compared with the trigger definition stored in sys.sql_modules (line endings normalised). Only triggers that are missing or whose definition differs are replaced, so a run with nothing to change is one catalog query and no DDL. Schema changes, AuditIgnore changes and package upgrades that alter the trigger body all surface as a differing definition. Triggers whose table is gone, now ignored, or has no usable primary key are removed.
• AlwaysUpdateTriggers: true replaces every audit trigger on each run regardless of the comparison. Default false (it was true before v5.3, which recreated every trigger on every startup).

AuditIgnore Helpers

• Use AuditIgnore.Create(schema), AuditIgnore.Create(schema, table), or AuditIgnore.Create(schema, table, column) to specify audit exclusions.
• Register them as an AuditIgnore[] singleton; IAuditTriggerService excludes those schemas, tables and columns.

---

Example: Dependency Injection Setup

AddCentevaAuditing binds the DatabaseMaintenance:Auditing section and registers AuditConfig, ArchiveConfig, IAuditTriggerService, IAuditArchiveService and IUnifiedAuditViewService. Register your ignore list before calling it; without one an empty AuditIgnore[] is registered.

services.AddDbContext<YourDbContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
services.AddSingleton<AuditIgnore[]>([AuditIgnore.Create("HangFire")]);
services.AddCentevaAuditing(
    Configuration,
    Configuration.GetConnectionString("DefaultConnection"),
    Configuration.GetConnectionString("AuditArchive"));

---

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

---

Contributing

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

---

License

Copyright © 2019