AuditaX.PostgreSql 2.3.1

AuditaX.PostgreSql

PostgreSQL database provider for AuditaX audit logging library.

Installation

dotnet add package AuditaX
dotnet add package AuditaX.PostgreSql
dotnet add package AuditaX.Dapper             # OR AuditaX.EntityFramework

Features

• PostgreSQL-specific SQL query generation
• Support for native JSONB type for JSON storage (efficient indexing and queries)
• Native XML type for XML storage
• TIMESTAMPTZ for timezone-aware timestamps
• Snake_case naming convention support
• Compatible with PostgreSQL 12+

Configuration

Service Registration

services.AddAuditaX(configuration)
    .UseDapper<DapperContext>()       // OR .UseEntityFramework<AppDbContext>()
    .UsePostgreSql()                  // Add PostgreSQL provider
    .ValidateOnStartup();

Connection String

{
  "ConnectionStrings": {
    "DefaultConnection": "Host=localhost;Database=mydatabase;Username=postgres;Password=YourPassword"
  }
}

Recommended Configuration for PostgreSQL

{
  "AuditaX": {
    "TableName": "audit_log",
    "Schema": "public",
    "LogFormat": "Json",
    "AutoCreateTable": true,
    "EnableLogging": true
  }
}

Generated Table Schema

-- For JSON format (uses native JSONB type)
CREATE TABLE IF NOT EXISTS public.audit_log (
    log_id UUID NOT NULL DEFAULT gen_random_uuid(),
    source_name VARCHAR(64) NOT NULL,
    source_key VARCHAR(64) NOT NULL,
    audit_log JSONB NOT NULL,
    CONSTRAINT pk_audit_log PRIMARY KEY (log_id),
    CONSTRAINT uq_audit_log_source UNIQUE (source_name, source_key)
);

-- For XML format (uses native XML type)
CREATE TABLE IF NOT EXISTS public.audit_log (
    log_id UUID NOT NULL DEFAULT gen_random_uuid(),
    source_name VARCHAR(64) NOT NULL,
    source_key VARCHAR(64) NOT NULL,
    audit_log XML NOT NULL,
    CONSTRAINT pk_audit_log PRIMARY KEY (log_id),
    CONSTRAINT uq_audit_log_source UNIQUE (source_name, source_key)
);

CREATE INDEX IF NOT EXISTS ix_audit_log_source_name ON public.audit_log (source_name);
CREATE INDEX IF NOT EXISTS ix_audit_log_source_key ON public.audit_log (source_key);

Querying Audit Logs

Basic Query

SELECT * FROM audit_log
WHERE source_name = 'Product';

Query JSON Changes

SELECT
    log_id,
    source_name,
    source_key,
    entry->>'action' AS entry_action,
    entry->>'user' AS entry_user,
    entry->>'timestamp' AS entry_timestamp,
    field->>'name' AS field_name,
    field->>'before' AS old_value,
    field->>'after' AS new_value
FROM audit_log,
     jsonb_array_elements(audit_log->'auditLog') AS entry,
     jsonb_array_elements(entry->'fields') AS field
WHERE entry->>'action' = 'Updated';

Filter by Specific Field Change

-- Find all price changes (uses JSONB containment operator for efficient indexing)
SELECT * FROM audit_log
WHERE audit_log->'auditLog' @> '[{"fields": [{"name": "Price"}]}]';

Query XML Changes

SELECT
    log_id,
    source_name,
    source_key,
    unnest(xpath('//Entry/@Action', audit_log::xml))::text AS entry_action,
    unnest(xpath('//Entry/@User', audit_log::xml))::text AS entry_user,
    unnest(xpath('//Entry/@Timestamp', audit_log::xml))::text AS entry_timestamp,
    unnest(xpath('//Field/@Name', audit_log::xml))::text AS field_name,
    unnest(xpath('//Field/@Before', audit_log::xml))::text AS old_value,
    unnest(xpath('//Field/@After', audit_log::xml))::text AS new_value
FROM audit_log;

DapperContext for PostgreSQL

using System.Data;
using Npgsql;

public class DapperContext(IConfiguration configuration)
{
    private readonly string _connectionString = configuration.GetConnectionString("DefaultConnection")!;

    public IDbConnection CreateConnection() => new NpgsqlConnection(_connectionString);
}

Requirements

• PostgreSQL 12 or later
• Npgsql package (included as dependency)

Documentation

For complete documentation, see the main AuditaX repository.

• Dapper + PostgreSQL + JSON
• Dapper + PostgreSQL + XML
• EF Core + PostgreSQL + JSON
• EF Core + PostgreSQL + XML