klod68/implement-persistence-layer

---

name: implement-persistence-layer description: "Use when adding database persistence to a Truenorth.Components.* library, migrating legacy persistence code to the standard pattern, or adding new entities to an existing component following the stored-procedure-only architecture with EXECUTE-only permissions. Covers the 8-artifact pattern: embedded settings, language constants, static repository, component factory, DbMappings, Insert/Update/Delete commands, SelectAll/SelectById/Exists queries, and a CrudPersistenceHelper-based persistence service with IList/IListCollectable/ITableCollectable output variants. Domain: Data Access / Persistence. Level: Advanced. Tags: persistence, stored-procedure, CRUD, ADO.NET, provider-agnostic."

Implement Persistence Layer

Problem

When adding database persistence to a Truenorth.Components.* library, you must follow the standard persistence pattern: stored-procedure-only access, provider-agnostic abstractions, CrudPersistenceHelper-based error routing, output variants (IList / IListCollectable / ITableCollectable), and correct async/await discipline. Deviation from the pattern causes architectural drift and maintainability problems.

Solution: Standard Persistence Pattern

Each entity's persistence layer consists of 8 artifact types, created in dependency order:

1. Settings class        → Embedded JSON config (stored procedure names, connection string)
2. Language class         → Error message constants
3. Repository class       → Static Lazy<> connection + provider cache
4. Component Factory      → Domain object creation (MakeFoo, MakeFooId)
5. DbMappings class       → Parameter setters + record-to-object mappers
6. Command classes        → Insert, Update, Delete (one class per SP)
7. Query classes           → SelectAll (base), SelectById, SelectByUUID (filtered)
8. Persistence Service    → internal sealed, delegates to CrudPersistenceHelper

Plus infrastructure:

• Health check class
• DI composition root (Pattern A) or factory methods (Pattern B)
• Public interface for the service

Implementation

Prerequisite: NuGet Reference

<PackageReference Include="Truenorth.Components.Persistence" Version="1.*" />

Artifact 1: Embedded Settings

Create Settings/{Component}Settings.cs (public static) and Settings/{component}-appsettings.json (EmbeddedResource).

Naming conventions:

• Commands: {Verb}{Entity}Command → CreateFooCommand, UpdateFooCommand, DeleteFooCommand
• Queries: Retrieve{Scope}{Entity}Query → RetrieveAllFoosQuery, RetrieveFooByIdQuery
• Repository keys: {Component}RepositoryKey, {Component}DbConnectionString

Artifact 2: Language Constants

Create Language/{Component}Language.cs (internal static). Categories:

• ERROR_CREATING_*, ERROR_UPDATING_*, ERROR_DELETING_*, ERROR_RETRIEVING_* — error messages
• MISSING_*_ID, MISSING_*_UUID — validation messages
• NO_*_DATA — no-data-found messages

Artifact 3: Repository

Create Services/Connection/{Component}Repository.cs (internal static).

internal static class {Component}Repository
{
    private static readonly Lazy<IPersistenceServiceProviderProperties> _provider = ...;
    private static readonly Lazy<string> _connectionString = ...;
    private static readonly Lazy<byte[]> _keyBytes = ...;

    internal static IDbResiliencePolicy DefaultResiliencePolicy { get; } =
        DbResiliencePolicyFactory.CreateFast();

    internal static DbConnection GetConnection() { ... }
    internal static IPersistenceServiceProviderProperties GetPersistenceServiceProvider() => _provider.Value;
}

Resilience presets:

| Preset | Retries | Initial Delay | Max Delay | Use Case | |--------|---------|---------------|-----------|----------| | CreateFast() | 3 | 50 ms | 2 s | User-facing OLTP | | Default | 3 | 100 ms | 30 s | General purpose | | CreatePatient() | 5 | 500 ms | 60 s | Batch / background | | None | 0 | — | — | Tests, health checks |

Artifact 4: Component Factory

Create Factories/{Component}Factory.cs (public static).

public static class {Component}Factory
{
    public static I{Entity} Make{Entity}() => new {Entity}();
    public static I{Entity}Id Make{Entity}Id() => new {Entity}Id();
    public static I{Entity}Id Make{Entity}Id(int id) => new {Entity}Id { {Entity}Id = id };
}

Artifact 5: DbMappings

Create Services/Servers/Mappings/{Entity}DbMappings.cs (internal static).

Two sections:

1. Parameter setters — consumed by both commands and queries
2. Record mappers — consumed by GetMappedObject<T> in queries

Artifact 6: Commands

Create in Services/Servers/Commands/{Entity}/:

| Class | Base | Override | Service Method | |-------|------|----------|----------------| | {Entity}InsertCommand | DbInsertCommand | SetNewRecordParameters | InsertAsync(spName, ct) → int | | {Entity}UpdateCommand | DbUpdateCommand | SetCommandParameters | UpdateAsync(spName, ct) → bool | | {Entity}DeleteCommand | DbDeleteCommand | SetCommandParameters | DeleteAsync(spName, ct) → bool | | {Entity}DeactivateCommand | DbUpdateCommand | SetCommandParameters | UpdateAsync(spName, ct) → bool |

Constructor pattern: Two constructors — default (sets PersistenceProviderType + Connection from repository) and testing (accepts provider + connection as parameters).

Fields: Always private readonly — set once in constructor, never mutated.

Artifact 7: Queries

Create in Services/Servers/Queries/{Entity}/:

| Class | Base | Sealed? | Purpose | |-------|------|---------|---------| | {Entity}SelectAllQuery | DbQueryBase | internal class (not sealed) | Base query — defines GetMappedObject<T> | | {Entity}SelectByIdQuery | {Entity}SelectAllQuery | internal sealed class | Adds ID parameter | | {Entity}SelectByUUIDQuery | {Entity}SelectAllQuery | internal sealed class | Adds UUID parameter | | {Entity}ExistsQuery | DbValidationQuery | internal sealed class | Returns bool |

Sealed rule: Leaf classes → internal sealed. Parent classes → internal class.

GetMappedObject<T> pattern:

protected override I{Entity} GetMappedObject<I{Entity}>(IDataRecord record)
{
    var entity = {Component}Factory.Make{Entity}();
    {Entity}DbMappings.Map{Entity}RecordToObject(record, entity);
    return (I{Entity})entity;
}

Artifact 8: Persistence Service

Create Services/Servers/{Entity}PersistenceService.cs (internal sealed).

Declaration: internal sealed class {Entity}PersistenceService : SystemNotifiableBase, I{Entity}PersistenceService

Output variants pattern: Every "retrieve all" method exposes three overloads that delegate to private *Control methods:

| Public Overload Returns | Control Method Calls | |------------------------|---------------------| | Task<IList<T>> | RetrieveItemList | | Task<int> (IListCollectable) | RetrieveItemCollection with assignFunc: r => items.List = r | | Task<int> (ITableCollectable) | RetrieveItemCollection with q.RetrieveTableAsync |

CrudPersistenceHelper Decision Matrix

| I need to... | Method | async? | Returns | |---|---|---|---| | Insert and get new ID | CreateItem | ✅ Yes | Task<int> | | Insert and get status | CreateItemWithStatus | ✅ Yes | Task<InsertExecutionResult> | | Update / Upsert | UpdateItem | ❌ No | Task<bool> | | Delete (hard or soft) | UpdateItem or DeleteItem | ❌ No | Task<bool> | | Get one record by ID | RetrieveItemByIdentifier | ❌ No | Task<T?> | | Get all → IList<T> | RetrieveItemList | ❌ No | Task<IList<T>> | | Get filtered → IList<T> | RetrieveItemListByIdentifier | ❌ No | Task<IList<T>> | | Get all → collection container | RetrieveItemCollection | ❌ No | Task<int> | | Get filtered → collection container | RetrieveItemCollectionByIdentifier | ❌ No | Task<int> | | Multi-param filter (2+ params) | RetrieveItemList with closure | ❌ No | varies | | Check existence | query.HasRecordAsync() directly | ❌ No | Task<bool> |

DbParameterBuilder vs SharedDbParameterSetter

| Condition | Use | |-----------|-----| | ≤ 4 simple parameters | SharedDbParameterSetter static calls | | 5+ parameters | DbParameterBuilder fluent chain | | Mix of Input + Output + ReturnValue | DbParameterBuilder | | Single ID parameter | SharedDbParameterSetter.SetIdParameter | | Reusable across commands/queries | DbMappings class calling SharedDbParameterSetter |

When to Use

• Adding database access to a new Truenorth.Components.* library
• Migrating legacy persistence code to the standard pattern
• Adding new entities to an existing component
• Building any .NET library that needs stored-procedure-based data access

When NOT to Use

• EF Core direct-table-access projects (use EF patterns instead)
• In-memory data stores (no stored procedures)
• Projects that do not reference Truenorth.Components.Persistence

Gotchas

1. Never use async on UpdateItem/DeleteItem/Retrieve methods — return Task directly. Only CreateItem needs async + ConfigureAwait(false) because it awaits to capture the new ID.

2. Never wrap CrudPersistenceHelper in try/catch — the helper handles error routing to ISystemNotifiable. Adding your own catch block duplicates error handling.

3. Always use the component factory in GetMappedObject<T> — never new Entity(). The factory returns the interface type from an internal sealed concrete class.

4. noDataFoundMessage can be null — pass null when not-found is a valid outcome (single-record lookups). Pass the language constant when no data is unexpected (collection retrieval).

5. The string? command parameter in control methods — enables settings-forwarding: one control method serves multiple public overloads that use different stored procedures (e.g., "all" vs "living only").

6. Seal leaves, not parents — Base queries that serve as inheritance roots must NOT be sealed. Only leaf classes (no other class inherits from them) are internal sealed class.

7. Two constructors on every command/query — Default (self-wiring from repository) and testing (provider + connection injection). Both assign the same private readonly fields.

8. DbMappings prevents duplication — Parameter-setting logic is shared between commands and queries. Record-mapping logic is shared across the query inheritance chain. Without DbMappings, you duplicate code in every command and query.

Related Skills

• retry-exponential-backoff — Resilience policy configuration for the repository
• guard-clause-validation — Precondition guards in persistence service methods
• interceptor-pipeline — Cross-cutting concerns (audit, multi-tenancy) on commands
• internal-by-default-library-design — Visibility rules for persistence classes
• embedded-resource-configuration — Settings class with 3-tier config precedence
• health-check-endpoint — Database connectivity health check registration