DHI.Services.PostgreSQL for Notifications — Internal Developer Guide¶

A production-grade INotificationRepository implementation backed by PostgreSQL.

Auto-migrates a minimal table and index on first use.
Configurable table name and UTC handling via connection-string additions.
Operator semantics aligned with Domain Services QueryConditions.

Install¶

dotnet add package DHI.Services.Provider.PostgreSQL

How it fits¶

You swap the JSON repository with the PostgreSQL repository:

using DHI.Services;
using DHI.Services.Notifications;
using PgNotifRepo = DHI.Services.Provider.PostgreSQL.NotificationRepository;

var conn = "Host=localhost;Port=5432;Database=app;Username=app;Password=secret;" +
           "Table=public.MessageLog;Utc=true";

var repo   = new PgNotifRepo(conn);      // optional ILogger ctor overload available
var svc    = new NotificationService(repo);
ServiceLocator.Register(svc, "notifications-pg");

Call the Web API with {connectionId}=notifications-pg.

The Web API controller is provider-agnostic—your registration decides which repository backs it.

Connection string additions¶

The provider reads extra keys from the connection string (removed before passing to Npgsql):

Table (default: public.MessageLog) Fully qualified table name (schema.table).
Utc (default: true) When true, incoming DateTime is converted to UTC before storage. Stored as timestamp without time zone with kind normalized to Unspecified (see DateTime handling below).

Examples

Host=db;Database=app;Username=app;Password=secret;Table=public.MessageLog;Utc=true
Host=db;Database=app;Username=app;Password=secret;Table=audit.Logs;Utc=false

Auto schema & indexing¶

On first construction, the repository:

Creates the table if missing:

CREATE TABLE IF NOT EXISTS <schema>.<table> (
    id uuid NOT NULL PRIMARY KEY,
    datetime timestamp without time zone,
    notificationlevel integer,
    source varchar(255),
    tag varchar(255),
    machinename varchar(255),
    text text
);

Adds an index on datetime DESC:

CREATE INDEX IF NOT EXISTS <table>_index
    ON <schema>.<table> USING btree (datetime DESC);

The initialization runs once per process (guarded by a static flag). In tests, you can call NotificationRepository.ResetDatabaseUpdatedState() to force re-init.

Field mapping¶

Column	Type	Source field
`id`	`uuid`	generated per row
`datetime`	`timestamp without time zone`	`NotificationEntry.DateTime`
`notificationlevel`	`integer`	enum as `int`
`source`	`varchar(255)`	`Source` (empty string if null)
`tag`	`varchar(255)`	`Tag` (empty string if null)
`machinename`	`varchar(255)`	`MachineName` (empty string if null)
`text`	`text`	`Text` (empty string if null)

Metadata is not persisted by this provider.

Query semantics¶

The provider translates a list of QueryConditions into SQL with parameters.

Supported fields & operators

DateTime, NotificationLevel: >, >=, <, <=, =, !=
Source, Tag, MachineName, Text: = or !=

Examples

var q = new List<QueryCondition>
{
  new ("DateTime", QueryOperator.GreaterThanOrEqual, new DateTime(2025,10,1,0,0,0, DateTimeKind.Utc)),
  new ("NotificationLevel", QueryOperator.GreaterThanOrEqual, NotificationLevel.Warning),
  new ("Source", QueryOperator.Equal, "monitor-agent")
};
var entries = repo.Get(q);     // IEnumerable<NotificationEntry>
var last    = repo.Last(q);    // Maybe<NotificationEntry>

The Web API’s POST /query and POST /last forward your DTO as these QueryConditions—use them to leverage operators.

DateTime handling (important)¶

DB column type is timestamp without time zone.
When Utc=true (default), the provider:
- Converts incoming DateTime to UTC
- Normalizes to Kind=Unspecified for storage (so the literal timestamp represents UTC instant)
When Utc=false, the DateTime is stored “as provided,” normalized to Unspecified.

Recommendation: Keep Utc=true. Always send/consume Z timestamps at the API boundary.

Wiring with the Web API¶

Register the service with a distinct connectionId and hit the same endpoints:

var pgRepo = new DHI.Services.Provider.PostgreSQL.NotificationRepository(connString);
ServiceLocator.Register(new NotificationService(pgRepo), "notifications-pg");

Now call:

POST /api/notifications/notifications-pg (add)
GET /api/notifications/notifications-pg?source=...
POST /api/notifications/notifications-pg/query
POST /api/notifications/notifications-pg/last

Migration guide (JSON → PostgreSQL)¶

Create table automatically: just construct the repo with the right connection string; it self-creates if missing.

Switch registration:

// remove JSON registration
// ServiceLocator.Register(new NotificationService(new NotificationRepository("[AppData]notifications.json")), "notifications-json");

// add PostgreSQL
ServiceLocator.Register(new NotificationService(new PgNotifRepo(conn)), "notifications-pg");

Clients: change {connectionId} in the URLs from notifications-json to notifications-pg.

Observability & logging¶

The repo has an overload accepting an ILogger.
Initialization errors are re-thrown if no logger is provided; with a logger, errors are logged (LogError) and the app keeps running. Prefer passing a logger in production.

Troubleshooting¶

Timeout / auth errors → Check base connection string; the provider strips only its additions (Table, Utc), everything else goes to Npgsql.
Table not found → Ensure your DB user can create tables or pre-create the table and index.
No results but you expect some → Verify your operators. Remember equality/inequality only for Source/Tag/MachineName/Text.
Time windows off by hours → Confirm Utc=true and that you’re sending UTC at the API boundary.