Provider Options

Mandatory:

• ConnectionString: The PostgreSQL Server connection string. Defaults is Server=127.0.0.1;Port=5432;User Id=postgres;Password=admin;Database=postgres;
• TableName: The events table name. If not specified, the default table name is event.
• DataColumnName: The column name of the event table where the JSON will be stored. Can be set to NULL to avoid including the JSON column. If not specified, the default column name is data.
• IdColumnName: The column name of the event identifier (the primary key). If not specified, the default column name is id.

Optional:

• Schema: The PostgreSQL schema to use.
• DataType: The type of the data column that stores the events. Can be JSON, JSONP or STRING. (Default is JSON).
• LastUpdatedDateColumnName: The datetime column name to update when replacing events.
• CustomColumn: Additional columns to store information from the audit event. (optional)

Table constraints

• The table must exists.
• The table must have a single ID column (Unique or Primary key).
• The type of the ID column must be convertible to STRING.

For example:

CREATE TABLE public.event
(
    id bigserial NOT NULL,
    inserted_date timestamp without time zone NOT NULL DEFAULT now(),
    updated_date timestamp without time zone NOT NULL DEFAULT now(),
    data jsonb NOT NULL,
    event_type varchar(50),
    user varchar(50) NULL,
    CONSTRAINT event_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

Query events

This provider implements GetEvent and GetEventAsync methods to obtain an audit event by id:

var event = dataProvider.GetEvent(1000);

The Postgre SQL data provider also includes basic support for querying the events collection.

Use the EnumerateEvents() method on PostgreSqlDataProvider class to run SQL-like queries against the audit events. The EnumerateEvents() method accepts any valid Postgre WHERE clause as a parameter.

For example:

IEnumerable<AuditEvent> events = postgreDataProvider.EnumerateEvents(
       "data #> '{Environment,UserName}' = '\"John\"'");

Will return the events whose property Environment.UserName is equal to 'John'.

This post contains information about the query syntax supported by JSONP data type. And here is the PostgreSQL documentation about JSON operators.

For complex querying capabilities, you should use the npgsql driver or the Npgsql EntityFramework provider directly.