DHI.Services.PostgreSQL — Internal Developer Guide¶

This package provides a thin, opinionated PostgreSQL provider layer for DHI Domain Services. It gives you:

A base repository that self-provisions tables, handles JSON (via converters), and plays nicely with Npgsql.
A set of ready-made repositories (Accounts, Filters, Jobs, JsonDocuments, MapStyles, Notifications, PasswordHistory, Places, RefreshTokens, Scalars, Scenarios, UserGroups).
Utilities to make DateTime handling safe with Npgsql 8+ and to simplify connection strings, parameters, and JSONB filtering.

If you’re building or using DHI services that persist to PostgreSQL, this is the foundation.

1) Install & Requirements¶

Database: PostgreSQL 12+ (JSONB used in some repos)
Client: Npgsql 8.x (see DateTime notes below)
Packages: DHI.Services.PostgreSQL plus the DHI domain packages you depend on (Accounts, Notifications, etc.)

2) Architecture in 90 seconds¶

BaseRepository (and BaseRepository<TEntity>) is the base class for all providers. It:
- Parses the connection string (supports Table=... override).
- Ensures the table exists on first use.
- Wires up JSON serialization/deserialization with a set of converters that “do the right thing” for enums and arbitrary JSON -> .NET primitives.
- Exposes standard Count/Get/GetAll/GetIds/Contains for TEntity (string ids).
Each concrete repo overrides:
- CreateDataModel(connectionString) -> creates table(s)/index(es).
- GetEntity(IDataReader) -> maps DB rows -> domain entity.
- Plus its own CRUD/search APIs.
Helpers (internal):
- DateTimeHelper.NormalizeDateTime -> makes DateTimes Kind=Unspecified (needed with Npgsql 8 semantics).
- Extension methods for parameters, table existence, schema.table parsing, JSONB filtering, etc.

3) Serialization & JSON converters¶

Built-ins (always added)¶

JsonStringEnumConverter — round-trips enums as strings.
DictionaryTypeResolverConverter<string, object> (from DHI.Services.Converters) — deserializes nested dictionaries cleanly.
PostgreSQLConverter.ObjectToInferredTypeConverter — smartly infers JSON primitives on read (bool, int, long, double, decimal, DateTime, Guid, string) and falls back to JsonElement clone when unknown.

Optional / experimental¶

ObjectArrayToInferredTypeConverter — same idea but for arrays of unknown element type (not enabled by default).

You can pass additional converters into each repository constructor; BaseRepository will append them to the serializer options.

Why this matters: many repos persist metadata/data as JSON (string or JSONB). These converters let you read them back as strongly typed dictionaries without writing custom plumbing.

4) DateTime & Npgsql 8 (read this)¶

Npgsql 8 tightened timestamp semantics. To avoid surprises:

DateTimeHelper.NormalizeDateTime forces any DateTime parameter to Kind=Unspecified unless it already is (it keeps values but strips the Kind).
AddParameter(..., handleDateTime: true) (default) runs that normalization for you.

Guideline

Save timestamps as UTC in your application, but send them as Unspecified to PostgreSQL when the column type is timestamp without time zone. On read, interpret values as UTC if that’s your domain convention.

5) Connection strings & “Table=” override¶

All repositories accept a standard PostgreSQL connection string. You may optionally append a Table=... segment to override the default table:

Host=localhost;Port=5432;Database=app;Username=svc;Password=***;SSL Mode=Disable;Table=security.accounts

BaseRepository pulls Table=... out and sets:

TableName = security.accounts (else the default provided by the repo)
ConnectionString = connection string without the Table=... bit

This lets you reuse the same repository type against different schemas/tables.

6) Extension methods (what you’ll use)¶

NpgsqlConnection.TableExists(tableName) Checks pg_tables using schema.table (defaults to public if schema omitted).
string.SplitSchemaAndTable() "public.accounts" -> ("public", "accounts"); "accounts" -> ("public", "accounts").
NpgsqlCommand.AddParameter(NpgsqlDbType, name, value, handleDateTime=true) Adds a unique parameter name (avoids clashes), normalizes DateTime if enabled.
QueryCondition.Validate(...) and QueryCondition.ToJsonCondition(columnName) Builds JSONB filter snippets for =, <>, >, >=, <, <=, LIKE, and array ANY. It also constructs nested JSON paths like data->'level1'->'level2'->>'leaf'.
HashSet<string>.ToSqlArray() Renders array['a','b','c'] for SQL = ANY (...) patterns.

QueryCondition/QueryOperator live in the shared services. You typically pass conditions down to repos that support JSONB filtering (e.g., JsonDocuments).

7) BaseRepository API (generic)¶

public abstract class BaseRepository
{
    protected readonly string ConnectionString;
    protected readonly ILogger Logger;
    protected readonly string TableName;

    protected abstract void CreateDataModel(string connectionString);
}

public abstract class BaseRepository<TEntity> : BaseRepository,
    IRepository<TEntity, string>,
    IDiscreteRepository<TEntity, string>
    where TEntity : IEntity<string>
{
    public virtual int Count(ClaimsPrincipal user = null);
    public virtual bool Contains(string id, ClaimsPrincipal user = null);
    public virtual IEnumerable<TEntity> GetAll(ClaimsPrincipal user = null);
    public virtual IEnumerable<string> GetIds(ClaimsPrincipal user = null);
    public virtual Maybe<TEntity> Get(string id, ClaimsPrincipal user = null);

    protected abstract TEntity GetEntity(IDataReader reader);
}

Lifecycle

On construction, AssureTableExists(...) runs:
- If table missing -> calls your CreateDataModel(connectionString).
- Errors go to Logger.LogError and are swallowed if a logger is provided (so your app can boot even if the DB is temporarily unavailable).

Serialization

_serializerOptions used when writing JSON to DB.
_deserializerOptions used when reading JSON from DB (has the required converters pre-registered).

8) Using the built-in repositories¶

All snippets assume you’ve created a valid connection string & have access to a DI container or ServiceLocator.

8.1 Accounts (`AccountRepository`)¶

Table: public.accounts (override with Table=).
Seeds a temporary demo admin if the table is empty (id demo_admin_to_be_deleted, password webapi).
Case-insensitive lookup by id and email (uses LOWER() in SQL).
Password verification:
- Legacy 20-byte hash (compatible with old repos) or PBKDF2 (salted; 10000 iterations).
ExtendDataModel() on startup ensures newer columns exist (enabled, lockout, timestamps).

Register

builder.Services.AddScoped<IAccountRepository>(_ =>
    new DHI.Services.Provider.PostgreSQL.AccountRepository(pg, logger, converters: null, loginAttemptPolicy: myPolicy));

Add/Update

var repo = provider.GetRequiredService<IAccountRepository>();
var acc = new Account("alice", "Alice") { Activated = true, Email = "alice@acme.io" };
acc.SetPassword("S3cret!");
repo.Add(acc);
acc.Company = "ACME"; repo.Update(acc);

Validate

bool ok = await repo.ValidatePassword("alice", "S3cret!");

8.2 Filters (`FilterRepository`)¶

What it stores

id, datatype, dataconnectionid, queryconditions (text as JSON), transport_connections (text[])
BTREE index on datatype

Register

builder.Services.AddScoped<IFilterRepository>(_ =>
    new DHI.Services.Provider.PostgreSQL.FilterRepository(
        builder.Configuration.GetConnectionString("Postgres-Filters")));

Add / Remove

var filters = app.Services.GetRequiredService<IFilterRepository>();

var f = new Filter(
    dataType: "timeseries",
    dataConnectionId: "csv",
    queryConditions: new[]
    {
        new QueryCondition("stationId", QueryOperator.Any, new[] { "A001", "A002" }),
        new QueryCondition("date", QueryOperator.GreaterThanOrEqual, new DateTime(2024, 1, 1))
    })
{
    Id = "ts-stations-2024"
};

filters.Add(f);
filters.Remove("ts-stations-2024");

Query

// get all filters for a type (optionally constrained to a data connection)
var list = await filters.GetListAsync("timeseries", dataConnectionId: "csv");

// how many transports are bound to a filter?
int cnt = await filters.TransportConnectionsCountAsync("ts-stations-2024");

// which filter IDs are bound to a given transport connection?
var ids = await filters.GetIdsAsync("ui-connection-1");

Bind/unbind “transport connections” (idempotent)

await filters.AddTransportConnectionAsync("ui-connection-1", "ts-stations-2024");
await filters.DeleteTransportConnectionAsync("ui-connection-1", "ts-stations-2024");

queryconditions are serialized with Filter.SerializerOption. Values are deserialized using an inferred-type converter (ints, doubles, bools, DateTime, etc.).

8.3 Jobs (`JobRepository`)¶

Register with DI (for controllers like AutomationsController)

builder.Services.AddScoped<IJobRepository<Guid, string>>(sp =>
    new DHI.Services.Provider.PostgreSQL.JobRepository(
        configuration.GetConnectionString("Postgres-Jobs") 
            ?? configuration["Postgres-Jobs:ConnectionString"],
        logger: sp.GetService<ILogger<DHI.Services.Provider.PostgreSQL.JobRepository>>(),
        converters: DHI.Services.Jobs.WebApi.SerializerOptionsDefault.Options.Converters // or null if you don’t use these
    ));

Register with ServiceLocator (for connection-routed endpoints)

var pgJobs = configuration.GetConnectionString("Postgres-Jobs")
             ?? configuration["Postgres-Jobs:ConnectionString"];

var postgresJobRepo = new DHI.Services.Provider.PostgreSQL.JobRepository(
    pgJobs,
    logger: null,
    converters: DHI.Services.Jobs.WebApi.SerializerOptionsDefault.Options.Converters // or null
);

// however you build your task service
var workflowTaskService = new TaskService<Workflow, string>(
    new DHI.Services.Jobs.Workflows.WorkflowRepository(
        "[AppData]workflows.json".Resolve(),
        DHI.Services.Jobs.WebApi.SerializerOptionsDefault.Options.Converters));

ServiceLocator.Register(
    new JobService<Workflow, string>(postgresJobRepo, workflowTaskService, hostRepo: null),
    "wf-jobs");

Create / Update / Delete

var jobs = app.Services.GetRequiredService<IJobRepository<Guid, string>>();

var job = new Job<Guid, string>(Guid.NewGuid(), taskId: "ingest-csv", accountId: "alice")
{
    HostId = "host-1",
    HostGroup = "etl",
    Priority = 5,
    Tag = "nightly",
    Progress = 0,
    Status = JobStatus.Created,
    StatusMessage = "Queued",
    Requested = DateTime.UtcNow,
    Parameters = { ["path"] = "/data/in.csv", ["delimiter"] = "," },
    Metadata = { ["tenant"] = "acme" }
};

jobs.Add(job);

// update fields
job.Status = JobStatus.Running;
job.Starting = DateTime.UtcNow;
jobs.Update(job);

// delete
jobs.Remove(job.Id);

Point updates (whitelisted fields)

// heartbeat is explicitly allowed
jobs.UpdateField(job.Id, "heartbeat", DateTime.UtcNow);

Queries

// fetch by id
var maybe = jobs.Get(job.Id);

// list everything (ordered in SQL by requested index effectiveness)
var all = jobs.GetAll();

// filtered query (mix columns + JSONB parameters)
var q = new Query<Job<Guid, string>>
{
    new QueryCondition("status", QueryOperator.Any, new[] { JobStatus.Running, JobStatus.Created }),
    new QueryCondition("requested", QueryOperator.GreaterThanOrEqual, DateTime.UtcNow.AddDays(-1)),
    // JSONB in 'parameters'
    new QueryCondition("path", QueryOperator.Like, "%.csv")
};
var matches = jobs.Get(q);

// most recent matching job
var last = jobs.GetLast(q);

// remove by query
jobs.Remove(new Query<Job<Guid, string>> { new QueryCondition("tag", QueryOperator.Equal, "nightly") });

8.4 JsonDocuments (secured)¶

Register with ServiceLocator

var jsonDocumentsPostgres = configuration["Postgres-JsonDocuments:ConnectionString"];

var pgJsonRepo = new DHI.Services.Provider.PostgreSQL.JsonDocumentRepositorySecured(
    jsonDocumentsPostgres,
    logger: null,
    converters: null // optional
);

var jsonDocService = new JsonDocumentService(pgJsonRepo);
ServiceLocator.Register(jsonDocService, "json-documents");

Add (must include at least one read permission)

Retrieve the service from the ServiceLocator (not DI).

var docsSvc = Services.Get<JsonDocumentService>("json-documents");
ClaimsPrincipal user = HttpContext.User; // must not be null

var doc = new DHI.Services.JsonDocuments.JsonDocument(
    id: "customers/acme/001",
    name: "ACME Customer",
    group: "customers/acme",
    data: @"{ ""customer"": { ""id"": ""001"", ""name"": ""ACME"", ""active"": true } }")
{
    DateTime = DateTime.UtcNow,
    Added = DateTime.UtcNow,
    Permissions =
    {
        new Permission { Operation = "read",   Principals = new[] { "Editors", "alice" } },
        new Permission { Operation = "update", Principals = new[] { "Editors" } },
        new Permission { Operation = "delete", Principals = new[] { "Administrators" } }
    },
    Metadata = { ["region"] = "eu" }
};

docsSvc.Repository.Add(doc, user);

Query / project / update / delete (permission-checked)

var full = docsSvc.Repository.Get("customers/acme/001", user);

// projections from JSONB "data"
var projected = pgJsonRepo.Get("customers/acme/001",
    new[] { "customer.name", "customer.active" }, user);

// lists
var all = pgJsonRepo.GetAll(user);
var inGroup = pgJsonRepo.GetByGroup("customers/acme", user);

// JSONB query
var q = new Query<DHI.Services.JsonDocuments.JsonDocument>
{
    new QueryCondition("groupname",        QueryOperator.Like, "customers/%"),
    new QueryCondition("customer.active",  QueryOperator.Equal, true),
    new QueryCondition("datetime",         QueryOperator.GreaterThanOrEqual, DateTime.UtcNow.AddDays(-30)),
};
var recentActive = pgJsonRepo.Get(q, user: user);

// update / delete
doc.Metadata["tier"] = "gold";
doc.Updated = DateTime.UtcNow;
pgJsonRepo.Update(doc, user);     // needs 'update' permission
pgJsonRepo.Remove(doc.Id, user);  // needs 'delete' permission

8.5 MapStyles (`MapStyleRepository`)¶

What it stores

id (varchar), name (varchar), style_code (TEXT)
Primary key on id

Register

If controllers or services resolve IMapStyleRepository from DI:

builder.Services.AddScoped<IMapStyleRepository>(_ =>
    new DHI.Services.Provider.PostgreSQL.MapStyleRepository(
        configuration.GetConnectionString("Postgres-MapStyles")
            ?? configuration["Postgres-MapStyles:ConnectionString"]));

If you wire a map service via ServiceLocator:

var pgMapStyles = configuration["Postgres-MapStyles:ConnectionString"];
var mapStyleRepo = new DHI.Services.Provider.PostgreSQL.MapStyleRepository(pgMapStyles);

var mapStyleService = new MapStyleService(mapStyleRepo);
ServiceLocator.Register(
    new GroupedMapService(new GroupedMapSource(sqliteConn), mapStyleService),
    "groupedmap-mclite");

Common ops

var repo = app.Services.GetRequiredService<IMapStyleRepository>();

repo.Add(new MapStyle("osm-dark", "OpenStreetMap Dark", styleCode: "{...json...}"));
bool exists = repo.Contains("osm-dark");
var one = repo.Get("osm-dark");
var all = repo.GetAll();
var ids = repo.GetIds();

repo.Update(new MapStyle("osm-dark", "OSM Dark (v2)", styleCode: "{...json v2...}"));
repo.Remove("osm-dark");

8.6 Notifications (`NotificationRepository`)¶

What it stores

id (uuid), datetime (timestamp without tz), notificationlevel (int), source, tag, machinename, text
BTREE index on datetime DESC
Supports connection-string additions:
- Table=public.MessageLog (override table)
- Utc=true|false (incoming DateTime converted to UTC before storing if true)

Register

builder.Services.AddScoped<INotificationRepository>(_ =>
    new DHI.Services.Provider.PostgreSQL.NotificationRepository(
        configuration["Postgres-Security:LogConnectionString"]));
// e.g. "...;Table=public.messagelog;Utc=true"

Write

var notif = app.Services.GetRequiredService<INotificationRepository>();
notif.Add(new NotificationEntry(
    level: NotificationLevel.Information,
    text:  "Service started",
    source:"webapi",
    tag:   "bootstrap",
    machineName: Environment.MachineName,
    dateTime: DateTime.UtcNow));

Query

var q = new[]
{
    new QueryCondition("datetime",         QueryOperator.GreaterThanOrEqual, DateTime.UtcNow.AddHours(-1)),
    new QueryCondition("notificationlevel",QueryOperator.GreaterThanOrEqual, (int)NotificationLevel.Warning),
    new QueryCondition("source",           QueryOperator.Equal, "webapi"),
};

IEnumerable<NotificationEntry> recent = notif.Get(q);
var lastWarning = notif.Last(q);

8.7 PasswordHistory (`PasswordHistoryRepository`)¶

What it stores

id (varchar), accountid (varchar), encryptedpassword (bytea), passwordexpirydate (timestamp without tz)
BTREE index on accountid

Register

builder.Services.AddScoped<IPasswordHistoryRepository>(_ =>
    new DHI.Services.Provider.PostgreSQL.PasswordHistoryRepository(
        configuration.GetConnectionString("Postgres-Security")
            ?? configuration["Postgres-Security:ConnectionString"],
        logger: null,
        converters: DHI.Services.Security.WebApi.SerializerOptionsDefault.Options.Converters // optional
    ));

Typical flows

var ph = app.Services.GetRequiredService<IPasswordHistoryRepository>();

// add when user changes password
ph.Add(new PasswordHistory(id: Guid.NewGuid().ToString())
{
    AccountId = "alice",
    EncryptedPassword = hashedBytes,
    PasswordExpiryDate = DateTime.UtcNow.AddDays(90)
});

// read history
var all = await ph.GetByAccountId("alice");
var mostRecent = await ph.GetMostRecentByAccountId("alice");
var lastN = await ph.GetRecentByAccountId("alice", limit: 3);

// update / remove (rare)
mostRecent.PasswordExpiryDate = DateTime.UtcNow.AddDays(120);
ph.Update(mostRecent);
ph.Remove(mostRecent.Id);

8.8 Places (`PlaceRepository`) — ServiceLocator-first (matches the sample project)¶

Tables

public.places: id, name, groupname, featureid, attributekey, attributevalue
public.indicators: FK -> places(id); carries indicator wiring (id uuid, type, connectionid, entityid, datasourcetype, timeinterval, starttime, endtime, aggregationtype, stylecode, placeid)
Delete cascades: removing a place removes its indicators.

Register with ServiceLocator (preferred for {connectionId}-routed Web APIs)

var pgPlaces = configuration.GetConnectionString("Postgres-Places")
              ?? configuration["Postgres-Places:ConnectionString"];

var placeRepo = new DHI.Services.Provider.PostgreSQL.PlaceRepository(pgPlaces);
var placeSvc  = new PlaceService(placeRepo, timeSeriesServices, scalarServices, gisService);

ServiceLocator.Register(placeSvc, "pg-places");

This mirrors your sample app’s pattern (ServiceLocator), but swaps the JSON repository for the PostgreSQL one.

(Optional) DI registration — only if some controller directly resolves IPlaceRepository<string>:

builder.Services.AddScoped<IPlaceRepository<string>>(_ =>
    new DHI.Services.Provider.PostgreSQL.PlaceRepository(
        configuration.GetConnectionString("Postgres-Places")
            ?? configuration["Postgres-Places:ConnectionString"]));

Common ops (via ServiceLocator)

var placesSvc = Services.Get<PlaceService>("pg-places");
var places    = placesSvc.Repository; // IPlaceRepository<string>

// add a place + indicator
var featureId = new FeatureId("fc:buildings", "building_id", "B-42");
var p = new Place(id: "hq", name: "Headquarters", featureId, group: "sites/eu");

p.Indicators["temperature"] = new Indicator(
    dataSource:   new DataSource(DataSourceType.TimeSeries, "csv", "sensor-7"),
    styleCode:    "heat-legend",
    timeInterval: TimeInterval.CreateRelativeToNow(-24, 0),
    aggregationType: AggregationType.FromDisplayName("Average"));

places.Add(p);

// reads
bool hasId   = places.Contains("hq");
bool hasGrp  = places.ContainsGroup("sites/eu");           // regex ^group(/|$)
var one      = places.Get("hq");
var all      = places.GetAll();
var inGroup  = places.GetByGroup("sites/eu");
var ids      = places.GetIds();
var byType   = places.GetIndicatorsByType("temperature");
var forPlace = places.GetIndicatorsByPlace("hq");
var byGroup  = places.GetIndicatorsByGroup("sites/eu");

// update / remove
p.Name = "HQ (Main)";
places.Update(p);     // replaces row + indicators
places.Remove("hq");  // cascades to indicators

Table overrides: this repo creates both places and indicators. If you need per-env schemas, supply a connection string with Table= overrides, or separate connections.

8.9 RefreshTokens (`RefreshTokenRepository`)¶

What it stores

id (varchar), token (varchar), accountid (varchar), expiration (timestamp without tz), clientip (varchar)
BTREE index on expiration DESC

Register (DI)

builder.Services.AddScoped<IRefreshTokenRepository>(_ =>
    new DHI.Services.Provider.PostgreSQL.RefreshTokenRepository(
        configuration.GetConnectionString("Postgres-Security")
            ?? configuration["Postgres-Security:ConnectionString"]));

Common ops

var tokens = app.Services.GetRequiredService<IRefreshTokenRepository>();

// create
tokens.Add(new RefreshToken(
    token:     "r:abc123",
    accountId: "alice",
    expiration: DateTime.UtcNow.AddDays(7),
    clientIp:  "192.0.2.10") { Id = Guid.NewGuid().ToString("N") });

// lookup
var forAlice = tokens.GetByAccount("alice");
var byToken  = tokens.GetByToken("r:abc123");

// update
var t = byToken.Value;
t.Expiration = DateTime.UtcNow.AddDays(14);
tokens.Update(t);

// delete
tokens.Remove(t.Id);

Notes

NpgsqlDbType.Timestamp is used for expiration; with the shared helpers, values are normalized to Unspecified on write.

8.10 Scalars (`ScalarRepository`, `ScalarService`)¶

Tables

public.scalars with: id, groupname, name, valuetypename, description, locked, value (text), datetime (timestamp without tz), flag (int)
BTREE index on datetime DESC
Values are stored as text; typed reads are inferred from valuetypename.

Register (DI)

builder.Services.AddScoped<IGroupedScalarRepository<string, int>>(_ =>
    new DHI.Services.Provider.PostgreSQL.ScalarRepository(
        configuration.GetConnectionString("Postgres-Scalars")
            ?? configuration["Postgres-Scalars:ConnectionString"]));

// Optional service wrapper if your code consumes IScalarService<,>
builder.Services.AddScoped<IScalarService<string, int>, DHI.Services.Provider.PostgreSQL.ScalarService>();

ServiceLocator (if you route by {connectionId})

var pgScalars = configuration["Postgres-Scalars:ConnectionString"];
var scalarRepo = new DHI.Services.Provider.PostgreSQL.ScalarRepository(pgScalars);
ServiceLocator.Register(new DHI.Services.Scalars.GroupedScalarService<string,int>(scalarRepo, logger: null), "pg-scalars");

Common ops

var scalars = app.Services.GetRequiredService<IGroupedScalarRepository<string, int>>();

// add (with data)
var s = new DHI.Services.Provider.PostgreSQL.Scalar(
    name: "maxRetries",
    valueTypeName: "System.Int32",
    group: "jobs",
    data: new DHI.Services.Provider.PostgreSQL.ScalarData(5, DateTime.UtcNow));

scalars.Add(s);

// read variants
var one      = scalars.Get("jobs/maxRetries");
var byGroup  = scalars.GetByGroup("jobs");
var all      = scalars.GetAll();
bool exists  = scalars.Contains("jobs/maxRetries");
bool grpHit  = scalars.ContainsGroup("jobs");

// update metadata (and/or data)
s.Description = "Max retry count for job runner";
scalars.Update(s);

// point-update only the data triplet
scalars.SetData("jobs/maxRetries", new ScalarData<int>(7, DateTime.UtcNow, flag: 1));

// lock / unlock
scalars.SetLocked("jobs/maxRetries", locked: true);

// remove
scalars.Remove("jobs/maxRetries");

Notes

When you supply ScalarData, value is written as text and datetime as timestamp; reads use valuetypename to rehydrate the typed value.
Group queries use a regex pattern ^group(/|$).

8.11 Scenarios (`ScenarioRepository`)¶

What it stores

id (varchar), version (varchar Guid), lastjobid (varchar Guid), datetime (timestamp without tz), data (JSONB), deleted (timestamp without tz)
BTREE index on datetime DESC
Schema evolutions included on startup:
- data coerced to JSONB
- deleted column added IF NOT EXISTS

Register

// Repository
builder.Services.AddScoped<DHI.Services.Provider.PostgreSQL.ScenarioRepository>(_ =>
    new DHI.Services.Provider.PostgreSQL.ScenarioRepository(
        configuration.GetConnectionString("Postgres-Scenarios")
            ?? configuration["Postgres-Scenarios:ConnectionString"]));

ServiceLocator (typical service wiring)

var pgScenarios = configuration["Postgres-Scenarios:ConnectionString"];
var pgJobs      = configuration["Postgres-Jobs:ConnectionString"];

var scenarioRepo = new DHI.Services.Provider.PostgreSQL.ScenarioRepository(pgScenarios);
var jobRepo      = new DHI.Services.Provider.PostgreSQL.JobRepository(pgJobs);

// ScenarioService expects a repository and (optionally) a jobs repo for cross refs
var scenarioService = new DHI.Services.Jobs.Scenarios.ScenarioService(scenarioRepo, jobRepo);
ServiceLocator.Register(scenarioService, "pg-scenarios");

Common ops

// via repo
var repo = app.Services.GetRequiredService<DHI.Services.Provider.PostgreSQL.ScenarioRepository>();

// add
repo.Add(new Scenario("model/run-001")
{
    LastJobId = Guid.NewGuid(),
    DateTime  = DateTime.UtcNow,
    Data      = @"{ ""state"": ""Queued"", ""inputs"": { ""mesh"": ""dfsu-1"" } }"
});

// get single
var s1 = repo.Get("model/run-001");

// time window
var recent = repo.Get(DateTime.UtcNow.AddDays(-7), DateTime.UtcNow);

// JSONB + column query
var q = new Query<Scenario>
{
    new QueryCondition("datetime", QueryOperator.GreaterThanOrEqual, DateTime.UtcNow.AddDays(-30)),
    new QueryCondition("inputs.mesh", QueryOperator.Equal, "dfsu-1"), // JSONB path inside 'data'
    new QueryCondition("state", QueryOperator.Any, new[] { "Queued", "Running" }) // JSONB again
};
var matches = repo.Get(q);

// update (also bumps version)
var s = s1.Value; 
s.Deleted = null;
s.Data    = @"{ ""state"": ""Finished"", ""resultId"": ""abc"" }";
repo.Update(s);

// soft-delete
s.Deleted = DateTime.UtcNow;
repo.Update(s);

// remove hard
repo.Remove("model/run-001");

Query rules

id, version, lastjobid -> allow Equal, NotEqual, Like
datetime, deleted -> allow comparisons (>, >=, <, <=, =, !=)
Any other field name is treated as a JSONB path into data.

8.12 UserGroups (`UserGroupRepository`)¶

What it stores

id (varchar pk), name (varchar), users (text[]), metadata (varchar(2048))
BTREE index on id DESC
On first use, if table is empty, seeds:
- Administrators (contains demo_admin_to_be_deleted)
- Editors (contains demo_admin_to_be_deleted)

Register (DI)

builder.Services.AddScoped<IUserGroupRepository>(_ =>
    new DHI.Services.Provider.PostgreSQL.UserGroupRepository(
        configuration.GetConnectionString("Postgres-Security")
            ?? configuration["Postgres-Security:ConnectionString"]));

Common ops

var groups = app.Services.GetRequiredService<IUserGroupRepository>();

// add
groups.Add(new UserGroup(
    id:   "Analysts",
    name: "Data Analysts",
    users: new HashSet<string> { "alice", "bob" })
{
    Metadata = { ["area"] = "eu" }
});

// read
var all  = groups.GetAll();
var one  = groups.Get("Analysts");
bool has = groups.Contains("Administrators");

// update
var g = one.Value;
g.Users.Add("charlie");
g.Metadata["notes"] = "Has prod read access";
groups.Update(g);

// delete
groups.Remove("Analysts");

Notes

users is stored as a Postgres text[]; metadata is serialized into a short JSON string (varchar(2048)).
The repo inherits the standard Count/Get/GetAll/GetIds/Contains from the base.

9) JSONB filtering patterns¶

For repos that store JSONB (e.g., JsonDocuments), you can translate structured filter conditions into SQL:

// Condition: data.customer.age >= 18 AND data.customer.active = true
var age = new QueryCondition("customer.age", QueryOperator.GreaterThanOrEqual, 18);
var active = new QueryCondition("customer.active", QueryOperator.Equal, true);

var sqlWhere = $"{age.ToJsonCondition("data")} AND {active.ToJsonCondition("data")}";
// -> ((data->'customer'->>'age')::numeric >= 18) AND ((data->'customer'->>'active')::boolean = 'True'::boolean)

Notes:

Strings use LIKE where appropriate.
DateTime emits ::timestamp without time zone with ISO formatting.
Arrays use ANY semantics.

10) Writing your own repository¶

Inherit from BaseRepository<TEntity> (or BaseRepository if you don’t need generic helpers).
Implement CreateDataModel(connectionString):

protected override void CreateDataModel(string connectionString)
{
    using var c = new NpgsqlConnection(connectionString);
    c.Open();

    var create = $@"
CREATE TABLE IF NOT EXISTS {TableName}(
  id character varying(255) PRIMARY KEY,
  name character varying(255) NOT NULL,
  added timestamp without time zone NOT NULL,
  metadata JSONB
);";
    using var cmd = new NpgsqlCommand(create, c);
    cmd.ExecuteNonQuery();

    var index = $"CREATE INDEX IF NOT EXISTS {TableName.Split('.')[1]}_added_idx ON {TableName} USING btree (added DESC);";
    new NpgsqlCommand(index, c).ExecuteNonQuery();
}

Implement GetEntity(IDataReader reader):

protected override MyEntity GetEntity(IDataReader r)
{
    var e = new MyEntity(r["id"].ToString(), r["name"].ToString())
    {
        Added = r["added"] is DBNull ? default : (DateTime)r["added"]
    };

    if (r["metadata"] is not DBNull)
    {
        var dict = JsonSerializer.Deserialize<Dictionary<string, object>>(r["metadata"].ToString(), _deserializerOptions);
        foreach (var kv in dict) e.Metadata[kv.Key] = kv.Value;
    }
    return e;
}

Use AddParameter for all dynamic values:

var sql = $"INSERT INTO {TableName}(id,name,added,metadata) VALUES(@id,@name,@added,@metadata)";
using var cmd = new NpgsqlCommand(sql, conn);
cmd.AddParameter(NpgsqlDbType.Varchar, "@id", entity.Id);
cmd.AddParameter(NpgsqlDbType.Varchar, "@name", entity.Name);
cmd.AddParameter(NpgsqlDbType.Timestamp, "@added", entity.Added); // normalized
cmd.AddParameter(NpgsqlDbType.Jsonb, "@metadata", JsonSerializer.Serialize(entity.Metadata, _serializerOptions), handleDateTime: false);
cmd.ExecuteNonQuery();

For table/schema overrides, honor TableName provided by the base.

Best practices

Use parameters for all values (avoid injection; we already do).
Keep CreateDataModel idempotent (IF NOT EXISTS).
Add useful indexes (your reads will thank you).
If you evolve schema, expose an ExtendDataModel() like AccountRepository does.

11) Operational notes¶

Pooling: Npgsql pools by default; the repository opens/closes per call.
Logging: If you pass an ILogger, startup DDL errors are logged and swallowed (so the app can boot). Without a logger, errors bubble.
Case-insensitive IDs: Some repos use LOWER() for identifiers. Be consistent in your UI/API expectations.
Large values: Prefer JSONB columns for structured metadata; keep string columns within declared varchar sizes (many are varchar(2048)).
Time zones: Store UTC in your app; map to timestamp without time zone as Unspecified per §4.

12) Quick cheat sheets¶

Common DDL themes¶

CREATE TABLE IF NOT EXISTS schema.table ...
Primary key on id (or uuid for Jobs/Notifications).
Useful indexes:
..._index ON table USING btree (datetime DESC);
..._index ON table USING btree (requested DESC);
..._index ON table USING btree (accountid);

Typical registration¶

// Accounts
builder.Services.AddScoped<IAccountRepository>(_ =>
    new DHI.Services.Provider.PostgreSQL.AccountRepository(
        builder.Configuration.GetConnectionString("Postgres-Security"),
        logger: provider.GetService<ILogger<AccountRepository>>()
    ));

ServiceLocator (for Web APIs that route by {connectionId})¶

var jsonDocumentsPostgres = configuration["Postgres-JsonDocuments:ConnectionString"];
var JsonDocumentService = new JsonDocumentService(new DHI.Services.Provider.PostgreSQL.JsonDocumentRepositorySecured(jsonDocumentsPostgres));
ServiceLocator.Register(JsonDocumentService, "json-documents");

13) What’s implemented where (reference)¶

Repository	Key columns / JSON usage	Indexes
AccountRepository	`id`,`name`,`encryptedpassword`,`activated`, lockout fields, `metadata(varchar)`	`name DESC`
FilterRepository	`id`,`datatype`,`dataconnectionid`,`queryconditions(text)`,`transport_connections(text[])`	`datatype DESC`
JobRepository	`id(uuid)`, `status`, `requested/started/finished`, `parameters`, `metadata`	`requested DESC`
JsonDocumentRepositorySecured	`id`,`name`,`groupname`,`datetime`,`data(JSONB)`,`metadata(JSONB)`,`permissions(JSONB)`	`datetime DESC`
MapStyleRepository	`id`,`name`,`style_code(TEXT)`	—
NotificationRepository	`id(uuid)`,`datetime`,`notificationlevel`,`source`,`tag`,`machinename`,`text`	`datetime DESC`
PasswordHistoryRepository	`id`,`accountid`,`encryptedpassword`,`passwordexpirydate`	`accountid`
PlaceRepository	`id`,`name`,`groupname`,`featureid`,`attributekey`,`attributevalue`	—
RefreshTokenRepository	`id`,`token`,`accountid`,`expiration`,`clientip`	`expiration DESC`
ScalarRepository	`id`,`groupname`,`name`,`valuetypename`,`description`,`locked`,`value(text)`,`datetime`,`flag`	`datetime DESC`
ScenarioRepository	`id`,`version`,`lastjobid`,`datetime`,`data(text)`,`deleted`	`datetime DESC`
UserGroupRepository	`id`,`name`,`users(text[])`,`metadata(varchar)`	`id DESC`

14) Example end-to-end (Accounts)¶

// Program.cs
var pg = builder.Configuration.GetConnectionString("Postgres-Security");
// Optional: override table
// pg += ";Table=security.accounts";

builder.Services.AddScoped<IAccountRepository>(_ =>
    new DHI.Services.Provider.PostgreSQL.AccountRepository(pg, logger));

// Usage
var app = builder.Build();
var repo = app.Services.GetRequiredService<IAccountRepository>();
var bob = new Account("bob", "Bob Ross") { Activated = true, Email = "bob@trees.tv" };
bob.SetPassword("HappyLittleTrees!");
repo.Add(bob);

var maybe = repo.Get("bob");
if (maybe.HasValue) {
    var ok = await repo.ValidatePassword("bob", "HappyLittleTrees!");
    // ...
}

DHI.Services.PostgreSQL — Internal Developer Guide¶

1) Install & Requirements¶

2) Architecture in 90 seconds¶

3) Serialization & JSON converters¶

Built-ins (always added)¶

Optional / experimental¶

4) DateTime & Npgsql 8 (read this)¶

5) Connection strings & “Table=” override¶

6) Extension methods (what you’ll use)¶

7) BaseRepository API (generic)¶

8) Using the built-in repositories¶

8.1 Accounts (AccountRepository)¶

8.2 Filters (FilterRepository)¶

8.3 Jobs (JobRepository)¶

8.4 JsonDocuments (secured)¶

8.5 MapStyles (MapStyleRepository)¶

8.6 Notifications (NotificationRepository)¶

8.7 PasswordHistory (PasswordHistoryRepository)¶

8.8 Places (PlaceRepository) — ServiceLocator-first (matches the sample project)¶

8.9 RefreshTokens (RefreshTokenRepository)¶

8.10 Scalars (ScalarRepository, ScalarService)¶

8.11 Scenarios (ScenarioRepository)¶

8.12 UserGroups (UserGroupRepository)¶

9) JSONB filtering patterns¶

10) Writing your own repository¶

11) Operational notes¶

12) Quick cheat sheets¶

Common DDL themes¶

Typical registration¶

ServiceLocator (for Web APIs that route by {connectionId})¶

13) What’s implemented where (reference)¶

14) Example end-to-end (Accounts)¶

8.1 Accounts (`AccountRepository`)¶

8.2 Filters (`FilterRepository`)¶

8.3 Jobs (`JobRepository`)¶

8.5 MapStyles (`MapStyleRepository`)¶

8.6 Notifications (`NotificationRepository`)¶

8.7 PasswordHistory (`PasswordHistoryRepository`)¶

8.8 Places (`PlaceRepository`) — ServiceLocator-first (matches the sample project)¶

8.9 RefreshTokens (`RefreshTokenRepository`)¶

8.10 Scalars (`ScalarRepository`, `ScalarService`)¶

8.11 Scenarios (`ScenarioRepository`)¶

8.12 UserGroups (`UserGroupRepository`)¶