DHI.Services.PostgreSQL for JSON Documents — Internal Developer Guide¶

The PostgreSQL provider is a first-class database repository for JSON Documents. It persists documents in a jsonb table and performs row-level permission checks in SQL using the caller’s ClaimsPrincipal. It implements IJsonDocumentRepository<string>, so you can wire it directly into JsonDocumentService<string>.

Use this provider when:

You want authoritative storage with DB-side filtering and projection.
You need enforced permissions on read/update/delete based on user principals.
You prefer server-side JSON projection (jsonb) to limit payloads.

Quick links¶

What you get¶

JsonDocumentRepositorySecured : IJsonDocumentRepository<string>
Auto-migration to add deleted column (for soft delete support).
Table bootstrap on first run (creates table + index if missing).
Server-side selectors: project parts of data with jsonb (see “Selectors”).
Query engine: translates Query<JsonDocument<string>> into SQL (see “Queries”).

Internals such as connection helpers, converters, and base classes live in PostgreSQL Providers.

Quick start¶

1) Create the repository¶

using DHI.Services.Provider.PostgreSQL;
using DHI.Services.JsonDocuments;

// Connection string example (tune to your env)
var cs = "Host=pg;Port=5432;Database=ds;Username=ds_user;Password=***;SearchPath=public";

// Optionally: add converters for custom metadata types (usually not needed)
var repo = new JsonDocumentRepositorySecured(cs /*, logger, converters */);

Default table is public.jsondocuments. Override via connection string addition: "...;Table=my_schema.my_docs".

2) Plug into the service¶

var svc = new JsonDocumentService<string>(repo);

3) Use it (remember: pass a user)¶

var user = BuildUser("alice", groups: new[] { "Readers", "Editors" });

// Add — must include permissions and at least one 'read'
var doc = new JsonDocument<string>(
    id: "configs/sim/Sim 43",
    name: "Sim 43",
    group: "configs/sim",
    data: "{\"params\":{\"dt\":180},\"notes\":\"retuned\"}"
)
{
    DateTime = DateTime.UtcNow,
    Permissions = new() {
        new("read",   new[] { "group:Readers", "user:alice" }),
        new("update", new[] { "group:Editors" }),
        new("delete", new[] { "group:Administrators" })
    },
    Metadata = new() { ["owner"] = "ops" }
};

repo.Add(doc); // Add does not require user but validates permissions

// Read with server-side projection
var one = repo.Get("configs/sim/Sim 43", new[] { "params", "notes" }, user).Value;

// Query by group (and project fields)
var inGroup = repo.GetByGroup("configs/sim", new[] { "params.dt", "notes" }, user);

// Update (requires 'update' permission)
doc = (repo.Get(doc.Id, user) | default)!;
doc.Data = "{\"params\":{\"dt\":240},\"notes\":\"final\"}";
repo.Update(doc, user);

// Soft delete (set Deleted and Update)
doc.Deleted = DateTime.UtcNow;
repo.Update(doc, user);

Table schema (created/updated automatically)¶

CREATE TABLE IF NOT EXISTS public.jsondocuments (
  id          varchar(255) PRIMARY KEY,
  name        varchar(255) NOT NULL,
  groupname   varchar(255),
  datetime    timestamp without time zone,
  data        jsonb,
  metadata    jsonb,
  added       timestamp without time zone NOT NULL,
  updated     timestamp without time zone,
  permissions jsonb,
  deleted     timestamp without time zone
);
CREATE INDEX IF NOT EXISTS jsondocuments_index ON public.jsondocuments (datetime DESC);

On startup the repo also ensures the deleted column exists (for JsonDocuments ≥ 1.5.0).

Security model (enforced in SQL)¶

All reads and protected writes are filtered by permissions:

Read requires a permission entry with Operation == "read" and any principal in user.GetPrincipals().
Update requires Operation == "update".
Delete requires Operation == "delete".

Important usage rules

Pass a non-null ClaimsPrincipal to every method that evaluates permissions: Get(...), GetAll(...), GetByGroup(...), Get(from,to,...), Get(query,...), Contains(...), ContainsGroup(...), Remove(...), Update(...), Count(user).
Add(...) does not take a user but validates that the document includes at least one "read" permission.
If the caller lacks the required permission, Update/Remove throw with a clear message.

The expected principal strings stored in permissions[].Principals look like group:Readers or user:alice. Your ClaimsPrincipal must map to those via your GetPrincipals() implementation in the Authorization layer.

Group semantics¶

groupname is treated hierarchically. GetByGroup("configs/sim", ...) matches rows where groupname matches the regex: ^configs/sim(/|$) — i.e., the exact group and any subgroups.

Selectors (server-side JSONB projection)¶

Many read methods accept string[] dataSelectors. The repository builds a jsonb projection that returns only selected branches in data.

Supported

Dotted property paths: "params", "params.dt", "notes"

Not supported

JSONPath prefixes ($, $.foo…)
Wildcards/filters (*, ?(), $..)
Array addressing (e.g., items[0]) — paths are split only on . and mapped to -> 'key'.

Examples

// Return {"params": {...}, "notes": "..."}
var projected = repo.GetAll(new[] { "params", "notes" }, user);

// Return {"params":{"dt":180}}
var oneField = repo.Get("configs/sim/Sim 43", new[] { "params.dt" }, user).Value;

Under the hood, the provider composes json_build_object(...) trees and data::json -> 'key' chains with parameterized keys to prevent SQL injection.

Queries¶

Get(Query<JsonDocument<string>> query, string[] selectors, ClaimsPrincipal user) supports:

1) Scalar columns¶

id, name, groupname: Equal, NotEqual, Like (regex), Any (OR over a set)

2) Timestamps¶

datetime, added, updated, deleted: Equal, NotEqual, GreaterThan, GreaterThanOrEqual, LessThan, LessThanOrEqual

3) JSON conditions¶

Any other condition.Item is treated as a JSON path inside data and translated via condition.ToJsonCondition("data").

Example

var q = new Query<JsonDocument<string>>(
    new("groupname", "^configs/sim(/|$)", QueryOperator.Like),
    new("datetime", DateTime.UtcNow.AddDays(-7), QueryOperator.GreaterThanOrEqual),
    new("params.dt", 180, QueryOperator.Equal) // JSON condition
);

var hits = repo.Get(q, new[] { "params", "notes" }, user);

API surface (selected)¶

// Discovery / checks
bool Contains(string id, ClaimsPrincipal user);
bool ContainsGroup(string group, ClaimsPrincipal user);
int  Count(ClaimsPrincipal user);

// Read
Maybe<JsonDocument<string>> Get(string id, string[] selectors, ClaimsPrincipal user);
IEnumerable<JsonDocument<string>> GetAll(string[] selectors, ClaimsPrincipal user);
IEnumerable<JsonDocument<string>> GetByGroup(string group, string[] selectors, ClaimsPrincipal user);
IEnumerable<JsonDocument<string>> Get(DateTime from, DateTime to, string[] selectors, ClaimsPrincipal user);
IEnumerable<JsonDocument<string>> Get(Query<JsonDocument<string>> query, string[] selectors, ClaimsPrincipal user);

// Write
void Add(JsonDocument<string> document);                         // validates permissions on the entity
void Update(JsonDocument<string> document, ClaimsPrincipal user); // requires 'update'
void Remove(string id, ClaimsPrincipal user);                     // requires 'delete'

Soft delete

There is no implicit filtering on deleted. To soft delete, set doc.Deleted = DateTime.UtcNow and call Update(doc, user). Filter in reads via queries, e.g., new("deleted", null, QueryOperator.Equal) to exclude deleted documents in your app logic.

Connections module (optional)¶

If your application uses the Connections module, register the PostgreSQL JSON Documents connection like this:

{
  "$type": "System.Collections.Generic.Dictionary`2[[System.String, mscorlib],[DHI.Services.IConnection, DHI.Services]], mscorlib",
  "postgresql-json-documents": {
    "$type": "DHI.Services.JsonDocuments.WebApi.JsonDocumentServiceConnection, DHI.Services.JsonDocuments.WebApi",
    "ConnectionString": "YourConnectionString",
    "RepositoryType": "DHI.Services.Provider.PostgreSQL.JsonDocumentRepositorySecured,DHI.Services.Provider.PostgreSQL",
    "Name": "PostgreSQL JSON Documents",
    "Id": "postgresql-json-documents"
  }
}

Replace "YourConnectionString" with your Npgsql connection string.
Optionally add ;Table=my_schema.my_docs to point at a non-default table.

Tips¶

Always pass a ClaimsPrincipal to reads and guarded writes; the repo calls Guard.Against.Null(user, ...).
Include at least one 'read' permission on every document for Add/Update; the repo enforces this.
Selectors: Use dotted keys only ("params.dt"). JSONPath ("$.params.dt") and arrays are not supported by this provider’s selector builder.
Groups: GetByGroup("a/b") matches a/b and subgroups like a/b/c.
Soft delete: rows with a non-null deleted are not auto-filtered — your queries should decide visibility.

That’s the PostgreSQL provider: a secure, jsonb-powered repository with server-side projection and SQL-level permission checks that drops right into JsonDocumentService<string>.