Persistence Layer - Audit Trail Platform (ATP)¶

DDD-driven persistence with NHibernate — ATP's persistence layer implements Repository, Unit of Work, and Specification patterns using NHibernate ORM with FluentNHibernate mappings, ConnectSoft.Extensions abstractions, and Azure SQL/Cosmos DB backends with Redis second-level caching for optimal performance.

📋 Documentation Generation Plan¶

This document will be generated in 15 cycles. Current progress:

Cycle	Topics	Estimated Lines	Status
Cycle 1	Persistence Architecture & DDD Patterns (1-2)	~2,500	⏳ Not Started
Cycle 2	NHibernate Configuration & Setup (3-4)	~3,000	⏳ Not Started
Cycle 3	Entity Mappings with FluentNHibernate (5-6)	~3,500	⏳ Not Started
Cycle 4	Repository Pattern Implementation (7-8)	~3,000	⏳ Not Started
Cycle 5	Unit of Work Pattern (9-10)	~2,500	⏳ Not Started
Cycle 6	Specification Pattern (11-12)	~3,000	⏳ Not Started
Cycle 7	Query Patterns & LINQ (13-14)	~2,500	⏳ Not Started
Cycle 8	Second-Level Caching (Redis) (15-16)	~2,500	⏳ Not Started
Cycle 9	Transaction Management & Concurrency (17-18)	~2,500	⏳ Not Started
Cycle 10	Multi-Tenant Isolation in Persistence (19-20)	~3,000	⏳ Not Started
Cycle 11	Event Sourcing & Outbox Pattern (21-22)	~3,000	⏳ Not Started
Cycle 12	Performance Optimization (23-24)	~2,500	⏳ Not Started
Cycle 13	Testing Strategies (25-26)	~2,500	⏳ Not Started
Cycle 14	MongoDB Alternative Implementation (27-28)	~2,500	⏳ Not Started
Cycle 15	Operations & Best Practices (29-30)	~2,500	⏳ Not Started

Total Estimated Lines: ~42,000

Purpose & Scope¶

This document defines ATP's persistence layer implementation using NHibernate ORM with DDD tactical patterns (Repository, Unit of Work, Specification) from the ConnectSoft.Extensions.PersistenceModel libraries, covering entity mappings, query strategies, transaction management, caching, and multi-tenant isolation.

Key Technologies & Patterns - NHibernate 5.x: Modern ORM with async support, batching, and LINQ provider - FluentNHibernate: Code-first mapping without XML configuration - Repository Pattern: Abstraction over data access with IGenericRepository<TEntity, TId> - Unit of Work Pattern: Transaction boundary management with IUnitOfWork - Specification Pattern: Domain-driven queries with ISpecification<TEntity, TId> - Azure SQL Database: Primary persistence backend with RLS and partitioning - Azure Cosmos DB: Optional for global-scale scenarios - Redis Second-Level Cache: NHibernate caching for read-heavy workloads - ConnectSoft.Extensions: Proven abstractions from microservice template

Detailed Cycle Plan¶

CYCLE 1: Persistence Architecture & DDD Patterns (~2,500 lines)¶

Topic 1: Persistence Architecture Overview¶

What will be covered: - ATP Persistence Layer Vision - Clean architecture persistence principles - Domain-driven design persistence patterns - Persistence ignorance in domain model - Infrastructure layer responsibilities

Layered Architecture
Domain Layer: Entities, aggregates, value objects (POCO, no ORM dependencies)
PersistenceModel.Abstractions: Repository/Specification interfaces
PersistenceModel.NHibernate: Concrete implementations, mappings
PersistenceModel.MongoDb: Alternative implementation (optional)
ApplicationModel: DI registration, configuration
DDD Tactical Patterns
Aggregate Root: Entry point for persistence operations
Repository: Collection-like interface for aggregates
Unit of Work: Transaction and session management
Specification: Domain-driven query encapsulation
Domain Events: Side-effect coordination (via outbox)
Technology Decisions
Why NHibernate over Entity Framework Core?
- Mature, proven in enterprise
- Better control over SQL generation
- Second-level caching out-of-the-box
- ConnectSoft.Extensions already built on NHibernate
Why FluentNHibernate mappings?
- No XML configuration files
- Type-safe, refactoring-friendly
- Separation from domain model (persistence ignorance)

Code Examples: - Layered architecture diagram - Domain entity (POCO, no attributes) - Repository interface definition - Specification interface definition

Diagrams: - ATP persistence architecture (layers) - DDD tactical patterns map - NHibernate in clean architecture

Deliverables: - Persistence architecture overview - DDD patterns catalog - Technology decision rationale

Topic 2: ConnectSoft.Extensions.PersistenceModel Overview¶

What will be covered: - ConnectSoft.Extensions.PersistenceModel library - Core abstractions (interfaces) - Base implementations - Specification framework - Unit of Work patterns

Key Interfaces

IGenericEntity<TIdentity>              // Base entity marker
IGenericRepository<TEntity, TIdentity> // Repository pattern
IGenericReadOnlyRepository<...>        // Query-only repository
IUnitOfWork                            // Transaction boundary
ISpecification<TEntity, TIdentity>     // Domain query
ISpecificationLocator                  // DI-based spec resolution
IUnitOfWorkConvertor                   // UoW → IQueryable

NHibernate-Specific Extensions
ConnectSoft.Extensions.PersistenceModel.NHibernate
NHibernateUnitOfWork: ISession wrapper
NHibernateRepository<TEntity, TIdentity>: Generic base
QueryableSpecification<TEntity, TIdentity>: LINQ-based
Registration extensions (AddNHibernateFromConfiguration)
MongoDB-Specific Extensions
ConnectSoft.Extensions.PersistenceModel.MongoDb
MongoDbUnitOfWork: IMongoDatabase wrapper
MongoDbRepository<TEntity, TIdentity>: Generic base
MongoDbQueryableSpecification<...>: Builder-based

Code Examples: - IGenericEntity interface - IGenericRepository interface (full) - IUnitOfWork interface - ISpecification interface - Extension method: AddNHibernateFromConfiguration

Diagrams: - ConnectSoft.Extensions library structure - Interface hierarchy - NHibernate vs MongoDB extensions

Deliverables: - ConnectSoft.Extensions overview - Interface reference documentation - Extension points guide

CYCLE 2: NHibernate Configuration & Setup (~3,000 lines)¶

Topic 3: NHibernate Configuration Files¶

What will be covered: - hibernate.cfg.xml Structure - Session factory name - Dialect selection (MsSql2012Dialect, PostgreSQLDialect) - Connection driver (MicrosoftDataSqlClientDriver) - Connection string reference (from appsettings.json) - Batch size configuration - Show/format SQL (dev vs prod) - Command timeout - Keyword auto-quoting

Environment-Specific Configurations
hibernate.cfg.xml (local development)
hibernate-docker.cfg.xml (Docker containers)
hibernate-test.cfg.xml (unit/integration tests)

Second-Level Cache Configuration

<property name="cache.provider_class">
  NHibernate.Caches.StackExchangeRedis.RedisCacheProvider
</property>
<property name="cache.default_expiration">300</property>
<property name="cache.use_second_level_cache">true</property>
<property name="cache.use_query_cache">true</property>
<property name="cache.region_prefix">ConnectSoft.Audit</property>
<property name="cache.configuration">redis:6379</property>

Connection String Management
Stored in appsettings.json
Retrieved from Azure Key Vault (production)
Named connection strings (ConnectSoft.Audit.SqlServer)
Connection pooling settings (Min/Max pool size)
Diagnostic Settings
SQL logging (show_sql, format_sql)
Statistics gathering
Query plan generation
Logging integration (ILoggerFactory)

Code Examples: - Complete hibernate.cfg.xml (production) - Complete hibernate-docker.cfg.xml - appsettings.json connection string section - Key Vault connection string retrieval

Diagrams: - NHibernate configuration flow - Connection string resolution - Logging integration

Deliverables: - hibernate.cfg.xml templates - Configuration best practices - Troubleshooting guide

Topic 4: NHibernate Registration & DI Setup¶

What will be covered: - AddNHibernatePersistenceModel Extension Method

internal static IServiceCollection AddNHibernatePersistenceModel(
    this IServiceCollection services, 
    IConfiguration configuration)
{
    services.AddNHibernateFromConfiguration(
        filePath: Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "hibernate.cfg.xml"),
        nhibernateMappingsAssembly: typeof(AuditRecordEntityMap).Assembly,
        systemConfiguration: configuration,
        dependencyInjectionKey: AuditConstants.NHibernateDIKey);

    // Register repositories
    services.AddKeyedScoped<IAuditRecordRepository, AuditRecordRepository>(
        AuditConstants.NHibernateDIKey);

    // Register specifications
    services.AddKeyedScoped<IAuditRecordSpecification, AuditRecordQueryableSpecification>(
        AuditConstants.NHibernateDIKey);

    return services;
}

Keyed Dependency Injection
Support for multiple persistence providers (NHibernate + MongoDB)
Primary provider selection logic
Adapter pattern for unified interface
SessionFactory Registration
Singleton SessionFactory per AppDomain
Thread-safe session creation
Connection pool management
ISession Scoping
Scoped lifetime (per HTTP request or message handler)
Automatic session opening
Deferred session disposal
IUnitOfWork Registration
Scoped lifetime aligned with ISession
Transaction coordination
Rollback on exceptions

Code Examples: - Complete NHibernateExtensions.cs - Keyed DI registration pattern - SessionFactory singleton pattern - ISession scoped lifetime pattern - IUnitOfWork registration

Diagrams: - DI registration flow - SessionFactory singleton - ISession lifetime scope - Keyed DI resolution

Deliverables: - DI registration guide - SessionFactory setup - Lifetime management patterns

CYCLE 3: Entity Mappings with FluentNHibernate (~3,500 lines)¶

Topic 5: FluentNHibernate Mapping Fundamentals¶

What will be covered: - ClassMap Pattern - Inheriting from FluentNHibernate.Mapping.ClassMap<T> - Table and schema configuration - Column mappings - Primary key configuration - Conventions and overrides

ATP Entity Mapping: AuditRecordEntityMap
```
{                                                
```
href="#__codelineno-3-1">public class AuditRecordEntityMap : ClassMap<AuditRecordEntity> pan> public AuditRecordEntityMap() { // Schema and table this.Schema("`ConnectSoft.Audit`"); this.Table("`AuditRecords`"); // Primary key (ULID assigned by application) this.Id(m => m.AuditRecordId) .Column(nameof(AuditRecordEntity.AuditRecordId)) .GeneratedBy.Assigned() .UnsavedValue(null); // Tenant isolation this.Map(m => m.TenantId) .Column(nameof(AuditRecordEntity.TenantId)) .Not.Nullable() .Length(128); // Timestamps this.Map(m => m.CreatedAt) .Column(nameof(AuditRecordEntity.CreatedAt)) .Not.Nullable(); this.Map(m => m.ObservedAt) .Column(nameof(AuditRecordEntity.ObservedAt)) .Not.Nullable(); // Event core this.Map(m => m.Action) .Column(nameof(AuditRecordEntity.Action)) .Not.Nullable() .Length(255); // Complex properties (JSON) this.Map(m => m.PayloadJson) .Column(nameof(AuditRecordEntity.PayloadJson)) .CustomSqlType("NVARCHAR(MAX)") .Not.Nullable(); // Classification and policy this.Map(m => m.Classification) .Column(nameof(AuditRecordEntity.Classification)) .CustomType<DataClassification>(); // Indexes (covered in SQL scripts, but can hint here) // Partitioning handled by database-level partition scheme } }
Value Object Mapping
Component mappings for embedded VOs
Custom type mappings for enums
Conversion strategies

Code Examples: - Complete AuditRecordEntityMap - IntegrityBlockEntityMap - SegmentEntityMap - PolicyDecisionEntityMap - Enum custom type mapping - Component (value object) mapping

Diagrams: - Entity-to-table mapping - Value object embedding - Custom type conversion

Deliverables: - FluentNHibernate mapping guide - ATP entity map implementations - Value object mapping patterns

Topic 6: Complex Mappings & Relationships¶

What will be covered: - One-to-Many Relationships - Collections mapping (HasMany<T>) - Cascade options (all, save-update, delete) - Inverse relationships - Lazy loading configuration

Many-to-One Relationships
References mapping (References<T>)
Foreign key columns
Not found behavior
Many-to-Many Relationships (rare in ATP)
Link table pattern
Composite keys
Inheritance Strategies
Table per class hierarchy (discriminator)
Table per subclass (joined)
Table per concrete class
ATP choice: Minimal inheritance, favor composition
Projection Mappings
AuditEventsEntityMap (denormalized projection)
ResourceEventsEntityMap
ActorEventsEntityMap
Composite Keys (if needed)
CompositeId() configuration
KeyProperty and KeyMany-to-One

Code Examples: - One-to-many mapping (Tenant → Audit Records) - References mapping (AuditRecord → IntegrityBlock) - Projection entity maps - Inheritance mapping (if used)

Diagrams: - Relationship mapping strategies - Projection entity schemas - Inheritance strategy comparison

Deliverables: - Relationship mapping guide - Projection entity maps - Inheritance patterns

CYCLE 4: Repository Pattern Implementation (~3,000 lines)¶

Topic 7: Generic Repository Base Classes¶

What will be covered: - IGenericRepository Interface

public interface IGenericRepository<TEntity, TIdentity>
    where TEntity : class, IGenericEntity<TIdentity>
{
    // CRUD operations
    void Insert(TEntity entity);
    Task InsertAsync(TEntity entity, CancellationToken ct);
    void Update(TEntity entity);
    Task UpdateAsync(TEntity entity, CancellationToken ct);
    void Delete(TEntity entity);
    Task DeleteAsync(TEntity entity, CancellationToken ct);

    // Queries
    TEntity GetById(TIdentity id);
    Task<TEntity> GetByIdAsync(TIdentity id, CancellationToken ct);
    IEnumerable<TEntity> GetAll();
    Task<IEnumerable<TEntity>> GetAllAsync(CancellationToken ct);

    // Specification pattern
    TSpecification Specify<TSpecification>()
        where TSpecification : class, ISpecification<TEntity, TIdentity>;

    // Query pattern
    IEnumerable<TEntity> Query(Expression<Func<TEntity, bool>> filter);
    Task<IEnumerable<TEntity>> QueryAsync(
        Expression<Func<TEntity, bool>> filter, CancellationToken ct);
}

GenericRepository Base Class
Implementation in ConnectSoft.Extensions
Constructor injection (IUnitOfWork, ISpecificationLocator)
CRUD method implementations
Query method implementations
Specification resolution logic

ATP Aggregate-Specific Repositories

// Interface (in PersistenceModel.Abstractions)
public interface IAuditRecordRepository 
    : IGenericRepository<IAuditRecord, string> // ULID as string
{
    // Domain-specific methods (if needed)
    Task<IAuditRecord> GetByIdempotencyKeyAsync(
        string tenantId, string idempotencyKey, CancellationToken ct);
}

// Implementation (in PersistenceModel.NHibernate)
public class AuditRecordRepository 
    : GenericRepository<IAuditRecord, string>, IAuditRecordRepository
{
    public AuditRecordRepository(
        IUnitOfWork unitOfWork, 
        ISpecificationLocator specificationLocator)
        : base(unitOfWork, specificationLocator)
    {
    }

    public async Task<IAuditRecord> GetByIdempotencyKeyAsync(
        string tenantId, string idempotencyKey, CancellationToken ct)
    {
        var query = this.UnitOfWork
            .ToQueryable<IAuditRecord, string>()
            .Where(r => r.TenantId == tenantId 
                     && r.IdempotencyKey == idempotencyKey);

        return await query.FirstOrDefaultAsync(ct);
    }
}

Code Examples: - IGenericRepository interface (complete) - GenericRepository base class (key methods) - IAuditRecordRepository interface - AuditRecordRepository implementation - Other ATP repositories (IntegrityBlock, Segment, etc.)

Diagrams: - Repository pattern class diagram - ATP repository hierarchy - Repository method flow

Deliverables: - Repository pattern implementation guide - ATP repository implementations - Usage examples

Topic 8: Repository Operations & Patterns¶

What will be covered: - Insert Operations - New entity insertion - Identity assignment (ULID generation) - Validation before insert - Optimistic concurrency checks

Update Operations
Attached entity updates
Detached entity reattachment
Merge strategies
Change tracking
Delete Operations
Hard delete vs. soft delete (ATP uses lifecycle states, not soft delete)
Cascade delete handling
Bulk Operations
Batch inserts (NHibernate batch size)
Bulk updates (NHibernate DML)
Performance considerations
Idempotent Operations
Insert or update (upsert) pattern
Idempotency key checking
Duplicate detection
Asynchronous Operations
Async/await patterns
CancellationToken support
ConfigureAwait(false) best practices

Code Examples: - Insert method implementation - Update method implementation - Idempotent insert (check + insert) - Batch insert pattern - Async repository methods

Diagrams: - Insert operation flow - Idempotent upsert logic - Batch operation performance

Deliverables: - Repository operation patterns - Idempotency implementation - Async patterns guide

CYCLE 5: Unit of Work Pattern (~2,500 lines)¶

Topic 9: IUnitOfWork Interface & Implementation¶

What will be covered: - IUnitOfWork Interface

public interface IUnitOfWork : IDisposable
{
    // Transaction management
    void BeginTransaction();
    void Commit();
    void Rollback();

    // Explicit flush
    void Flush();
    Task FlushAsync(CancellationToken ct);

    // Queryable access
    IQueryable<TEntity> ToQueryable<TEntity, TIdentity>()
        where TEntity : class, IGenericEntity<TIdentity>;

    // Transactional execution
    void ExecuteTransactional(Action action);
    Task ExecuteTransactionalAsync(Func<Task> action, CancellationToken ct);
}

NHibernateUnitOfWork Implementation
Wraps ISession
Transaction lifecycle management
Automatic rollback on exception
Flush strategies (auto vs. explicit)
Transaction Boundaries
Per HTTP request scope
Per message handler scope
Nested transactions (savepoints)
Distributed transactions (rare in ATP)

ExecuteTransactional Pattern

public void ExecuteTransactional(Action action)
{
    using (var transaction = this.session.BeginTransaction())
    {
        try
        {
            action();
            transaction.Commit();
        }
        catch
        {
            transaction.Rollback();
            throw;
        }
    }
}

Code Examples: - IUnitOfWork interface (complete) - NHibernateUnitOfWork implementation - ExecuteTransactional usage - Async transaction pattern - Transaction scope management

Diagrams: - Unit of Work pattern - Transaction lifecycle - Nested transaction handling

Deliverables: - Unit of Work implementation - Transaction management guide - Usage patterns

Topic 10: Transaction Management & Isolation Levels¶

What will be covered: - Isolation Levels - Read Uncommitted (not used) - Read Committed (default for ATP) - Repeatable Read (for critical operations) - Serializable (rare, performance cost) - Snapshot (SQL Server-specific)

ATP Transaction Strategy
Default: Read Committed
Integrity operations: Repeatable Read or Serializable
Read-only queries: No transaction or Read Uncommitted (with caution)
Optimistic Concurrency Control
Rowversion column (SQL Server)
Timestamp-based detection
Conflict resolution strategies
Pessimistic Locking
LockMode.Upgrade (SELECT FOR UPDATE)
Use cases in ATP (rare, e.g., integrity block sealing)
Deadlock Handling
Retry with exponential backoff
Deadlock detection and logging
Deadlock prevention strategies

Code Examples: - Isolation level configuration - Optimistic concurrency entity annotation - Pessimistic lock usage - Deadlock retry pattern - Conflict resolution handler

Diagrams: - Isolation level comparison - Optimistic vs. pessimistic locking - Deadlock scenario and resolution

Deliverables: - Transaction strategy document - Concurrency control patterns - Deadlock handling guide

CYCLE 6: Specification Pattern (~3,000 lines)¶

Topic 11: Specification Pattern Fundamentals¶

What will be covered: - ISpecification Interface

public interface ISpecification<TEntity, TIdentity>
    where TEntity : class, IGenericEntity<TIdentity>
{
    // Initialization from UnitOfWork
    void Initialize(IUnitOfWork unitOfWork);

    // Result retrieval
    ISpecificationResult<TEntity, TIdentity> ToResult();
}

public interface ISpecificationResult<TEntity, TIdentity>
{
    // Query execution
    IEnumerable<TEntity> ToList();
    Task<IEnumerable<TEntity>> ToListAsync(CancellationToken ct);
    TEntity SingleOrDefault();
    Task<TEntity> SingleOrDefaultAsync(CancellationToken ct);

    // Pagination
    ISpecificationResult<TEntity, TIdentity> Skip(int count);
    ISpecificationResult<TEntity, TIdentity> Take(int count);

    // Ordering
    ISpecificationResult<TEntity, TIdentity> OrderBy<TKey>(
        Expression<Func<TEntity, TKey>> keySelector);
    ISpecificationResult<TEntity, TIdentity> OrderByDescending<TKey>(
        Expression<Func<TEntity, TKey>> keySelector);
}

QueryableSpecification Base Class
Wraps IQueryable
Fluent query building
LINQ provider integration
Composition patterns
Specification Locator
ISpecificationLocator interface
DI-based resolution
Automatic initialization with UnitOfWork

ATP-Specific Specifications

// Interface (domain-oriented, in PersistenceModel.Abstractions)
public interface IAuditRecordSpecification 
    : ISpecification<IAuditRecord, string>
{
    // Domain query methods
    IAuditRecordSpecification ForTenant(string tenantId);
    IAuditRecordSpecification CreatedBetween(DateTime start, DateTime end);
    IAuditRecordSpecification WithAction(string action);
    IAuditRecordSpecification WithResource(string resourceType, string resourceId);
    IAuditRecordSpecification WithActor(string actorId);
}

// Implementation (in PersistenceModel.NHibernate.Specifications)
public class AuditRecordQueryableSpecification 
    : QueryableSpecification<IAuditRecord, string>, IAuditRecordSpecification
{
    public IAuditRecordSpecification ForTenant(string tenantId)
    {
        this.Queryable = this.Queryable.Where(r => r.TenantId == tenantId);
        return this;
    }

    public IAuditRecordSpecification CreatedBetween(DateTime start, DateTime end)
    {
        this.Queryable = this.Queryable.Where(r => 
            r.CreatedAt >= start && r.CreatedAt < end);
        return this;
    }

    // ... other methods
}

Code Examples: - ISpecification interface (complete) - QueryableSpecification base class - IAuditRecordSpecification interface - AuditRecordQueryableSpecification implementation - Specification usage in service layer

Diagrams: - Specification pattern class diagram - Specification locator resolution - Query composition flow

Deliverables: - Specification pattern guide - ATP specification implementations - Query composition patterns

Topic 12: Advanced Specification Patterns¶

What will be covered: - Specification Composition - AND/OR composition - NOT negation - Specification algebra

Parameterized Specifications
Constructor parameters
Method chaining for filters
Complex criteria building
Projection Specifications
Select specific fields
DTO projections
Performance optimization
Eager Loading in Specifications
Fetch joins (NHibernate)
Include paths
Avoiding N+1 queries
Pagination Specifications
Skip/Take pattern
Cursor-based pagination
Keyset pagination for large datasets
Caching in Specifications
Query cache hints
Second-level cache interaction
Cache invalidation strategies

Code Examples: - Specification composition (AND/OR) - Parameterized specification - Projection specification - Eager loading specification - Pagination specification - Cacheable specification

Diagrams: - Specification composition algebra - Eager loading strategy - Pagination patterns

Deliverables: - Advanced specification patterns - Composition strategies - Performance optimization guide

CYCLE 7: Query Patterns & LINQ (~2,500 lines)¶

Topic 13: LINQ Query Patterns¶

What will be covered: - NHibernate LINQ Provider - Supported LINQ operators - Limitations and workarounds - Query translation to SQL - Performance considerations

Simple Queries
Where clauses
OrderBy/ThenBy
Select projections
First/Single/Count operations
Join Queries
Inner joins
Left outer joins
Group joins
Multiple join optimization
Aggregation Queries
Count, Sum, Average, Min, Max
GroupBy aggregations
Having clauses
Complex Filters
String operations (Contains, StartsWith, EndsWith)
Date/time comparisons
Enum comparisons
Nullable handling
Subqueries
Correlated subqueries
EXISTS clauses
IN clauses with subquery

Code Examples: - Simple query (Where + OrderBy) - Join query - Aggregation query - Complex filter (multiple conditions) - Subquery pattern - Query performance analysis

Diagrams: - LINQ to SQL translation - Query execution pipeline - Join strategy visualization

Deliverables: - LINQ query patterns - Performance best practices - Common pitfalls and solutions

Topic 14: Raw SQL & Stored Procedures¶

What will be covered: - When to Use Raw SQL - Complex queries beyond LINQ capabilities - Performance-critical operations - Bulk operations - Database-specific features

CreateSQLQuery API
Executing raw SQL
Parameter binding
Result mapping to entities
Scalar results
Stored Procedures
Calling stored procedures (rare in ATP)
Parameter mapping
Output parameters
Result sets
Views and Functions
Mapping views as read-only entities
Table-valued functions
Scalar functions in queries
SQL Injection Prevention
Parameterized queries only
No string concatenation
Input validation
Audit logging

Code Examples: - CreateSQLQuery for complex query - Stored procedure call (if used) - View mapping - Parameterized query pattern - SQL injection prevention

Diagrams: - Raw SQL execution flow - Stored procedure invocation

Deliverables: - Raw SQL usage guide - Security best practices - Performance optimization

CYCLE 8: Second-Level Caching (Redis) (~2,500 lines)¶

Topic 15: NHibernate Second-Level Cache with Redis¶

What will be covered: - Second-Level Cache Overview - Session-level (first-level) cache vs. SessionFactory-level (second-level) - Read-heavy workload optimization - Cache regions and strategies - Eviction policies

Redis as Cache Provider
NHibernate.Caches.StackExchangeRedis package
Connection configuration
Serialization (JSON or binary)
Expiration policies

Cache Configuration

<property name="cache.provider_class">
  NHibernate.Caches.StackExchangeRedis.RedisCacheProvider
</property>
<property name="cache.default_expiration">300</property>
<property name="cache.use_second_level_cache">true</property>
<property name="cache.use_query_cache">true</property>
<property name="cache.region_prefix">ConnectSoft.Audit</property>
<property name="cache.configuration">
  redis-primary.cache.windows.net:6380,
  password=...,ssl=true,abortConnect=False
</property>

Entity-Level Caching
Cache attribute on entity maps
Read-write vs. read-only strategies
Cache regions per entity type
Query Caching
SetCacheable(true) on queries
Query cache invalidation
Parameter-based cache keys

Code Examples: - Redis cache configuration (complete) - Entity map with caching - Cacheable query usage - Cache invalidation code - Cache hit/miss monitoring

Diagrams: - Second-level cache architecture - Cache hierarchy (L1 → L2 → DB) - Cache invalidation flow

Deliverables: - Redis cache setup guide - Caching strategy per entity - Monitoring and tuning

Topic 16: Cache Invalidation & Consistency¶

What will be covered: - Cache Invalidation Strategies - Time-based expiration (TTL) - Event-based invalidation - Manual cache clearing - Distributed cache coordination

Consistency Challenges
Stale data risks
Multi-instance coordination
Eventual consistency trade-offs
ATP Cache Strategy
AuditRecords: No caching (append-only, write-heavy)
Projections: Cache with 5-minute TTL
Policies: Cache with event-based invalidation
Metadata: Cache with long TTL (1 hour)
Cache Warming
Pre-populate cache on startup
Background refresh jobs
Cache-aside pattern
Monitoring Cache Health
Hit/miss ratio tracking
Eviction rate monitoring
Memory usage alerts
Latency metrics (cache vs. DB)

Code Examples: - Event-based cache invalidation - Cache warming on startup - Cache health monitoring query - Hit/miss ratio calculation

Diagrams: - Cache invalidation strategies - ATP cache strategy per entity type - Monitoring dashboard

Deliverables: - Cache invalidation patterns - ATP caching policy - Monitoring setup

CYCLE 9: Transaction Management & Concurrency (~2,500 lines)¶

Topic 17: Advanced Transaction Patterns¶

What will be covered: - Outbox Pattern for Reliable Messaging - Transactional outbox table - Atomic write + event publish - Outbox processor background worker - Idempotency guarantees

Saga Pattern for Long-Running Transactions
NHibernate saga state persistence
Compensation logic
Timeout handling
State machine mapping
Two-Phase Commit (2PC) (rare in ATP)
Distributed transaction coordinator
When to avoid 2PC
ATP avoids cross-database transactions
Eventual Consistency Patterns
Event-driven data synchronization
Read-your-writes consistency
Projection lag monitoring
Compensating transactions

Code Examples: - Outbox table schema - Atomic write + outbox insert - Outbox processor background worker - Saga state entity map - Compensation handler

Diagrams: - Outbox pattern sequence - Saga state machine - Eventual consistency timeline

Deliverables: - Outbox pattern implementation - Saga pattern guide - Consistency strategy document

Topic 18: Concurrency Control & Locking¶

What will be covered: - Optimistic Concurrency in NHibernate - Version column (int, timestamp) - Automatic version checking - StaleObjectStateException handling - Retry logic

Pessimistic Locking
LockMode.Upgrade (SELECT FOR UPDATE)
Timeout configuration
Deadlock risk mitigation
ATP Concurrency Strategy
AuditRecords: No version (append-only, no updates)
IntegrityBlocks: Optimistic locking (version column)
Policies: Optimistic locking with version
Segments: Immutable after sealing (no locking needed)
Handling Concurrent Writes
Retry with exponential backoff
Circuit breaker pattern
Maximum retry attempts
User-facing error messages

Code Examples: - Entity with version column mapping - Optimistic concurrency conflict handling - Pessimistic lock usage - Retry with exponential backoff - Circuit breaker pattern

Diagrams: - Optimistic concurrency flow - Pessimistic locking sequence - Retry logic with backoff

Deliverables: - Concurrency control guide - ATP locking strategy - Conflict resolution patterns

CYCLE 10: Multi-Tenant Isolation in Persistence (~3,000 lines)¶

Topic 19: Tenant-Scoped Queries & RLS¶

What will be covered: - TenantId in Every Entity - Required property on all aggregates - Enforced at mapping level - Validation in domain layer

Automatic Tenant Filtering
NHibernate filter configuration
Enable filter per session
Filter parameters (tenantId from context)
Row-Level Security (RLS) Integration
RLS policies in SQL (see data-architecture.md)
SESSION_CONTEXT setup
NHibernate RLS coordination
Tenant Context Propagation
Extract tenantId from JWT
Store in AsyncLocal or HttpContext
Pass to repository/specification methods
Query Interception
IInterceptor implementation
Inject tenant filter on all queries
Log queries without tenant filter (alert)

Code Examples: - NHibernate filter definition (tenant filter) - Filter enablement in session - Tenant context middleware - Query interceptor implementation - RLS SESSION_CONTEXT setup

Diagrams: - Tenant context propagation - Query filter injection - RLS enforcement flow

Deliverables: - Multi-tenant persistence guide - Filter configuration - Tenant context patterns

Topic 20: Tenant Data Isolation Testing¶

What will be covered: - Cross-Tenant Access Prevention Tests - Test: Query for Tenant A returns no Tenant B data - Test: Insert with wrong tenantId fails validation - Test: Update cannot change tenantId

Automated Test Suite
Integration tests with multiple tenants
Assert isolation at query level
Assert isolation at RLS level (SQL queries)
Penetration Testing
Attempt SQL injection to bypass filters
Attempt parameter tampering (tenantId in URL)
Verify audit logging of suspicious activity
Compliance Validation
Generate isolation proof report
Demonstrate no cross-tenant queries in logs
Provide evidence for SOC 2 audits

Code Examples: - Cross-tenant isolation test - RLS enforcement test (SQL) - Automated test suite structure - Isolation proof report generator

Diagrams: - Test scenario matrix - Isolation test flow

Deliverables: - Isolation test suite - Penetration test scenarios - Compliance evidence generation

CYCLE 11: Event Sourcing & Outbox Pattern (~3,000 lines)¶

Topic 21: Outbox Pattern Implementation¶

What will be covered: - Outbox Table Schema

CREATE TABLE ConnectSoft.Audit.Outbox (
    OutboxId CHAR(26) PRIMARY KEY,      -- ULID
    TenantId NVARCHAR(128) NOT NULL,
    EventType NVARCHAR(255) NOT NULL,
    EventPayload NVARCHAR(MAX) NOT NULL, -- JSON
    CreatedAt DATETIME2(3) NOT NULL,
    ProcessedAt DATETIME2(3) NULL,
    Status TINYINT NOT NULL,             -- 0=Pending, 1=Processed, 2=Failed
    RetryCount INT NOT NULL DEFAULT 0,
    ErrorMessage NVARCHAR(MAX) NULL,
    INDEX IX_Outbox_Status_CreatedAt (Status, CreatedAt)
);

Outbox Entity Mapping
OutboxMessageEntity
FluentNHibernate mapping
Repository interface

Atomic Write + Outbox Insert

unitOfWork.ExecuteTransactional(() =>
{
    // 1. Insert audit record
    auditRecordRepository.Insert(auditRecord);

    // 2. Insert outbox message (same transaction)
    var outboxMessage = new OutboxMessage
    {
        OutboxId = Ulid.NewUlid().ToString(),
        TenantId = auditRecord.TenantId,
        EventType = "audit.accepted",
        EventPayload = JsonSerializer.Serialize(domainEvent),
        CreatedAt = DateTime.UtcNow,
        Status = OutboxStatus.Pending
    };
    outboxRepository.Insert(outboxMessage);
});

Outbox Processor Background Worker
IHostedService implementation
Poll outbox table for pending messages
Publish to message bus (Azure Service Bus)
Mark as processed or failed
Retry logic with exponential backoff
Idempotency in Outbox
Unique constraint on (TenantId, IdempotencyKey)
Duplicate detection before insert
At-least-once delivery guarantee

Code Examples: - Outbox table DDL - OutboxMessageEntityMap - Atomic write + outbox insert - Outbox processor background worker - Idempotency check logic

Diagrams: - Outbox pattern sequence - Background processor flow - Retry with backoff timeline

Deliverables: - Outbox pattern implementation - Background processor setup - Idempotency guarantees

Topic 22: Domain Events & Event Store¶

What will be covered: - Domain Events in DDD - Event notification pattern - Events emitted by aggregates - Event collection and persistence

AuditRecords as Event Store
Every AuditRecord is an immutable event
Complete history for event sourcing
Replay capability for projections
Event Replay for Projections
Read all events in order (by CreatedAt, AuditRecordId)
Apply to projection (idempotent upsert)
Update checkpoint watermark
Handle schema version changes (upcasting)
Event Versioning
SchemaVersion field in payload
Backward-compatible evolution
Upcasting old events to new schema
Snapshotting (if needed)
Periodic projection snapshots
Snapshot + delta replay
Performance optimization for large event stores

Code Examples: - Domain event interface - Event collection in aggregate - Event replay query (NHibernate) - Projection update from event - Upcasting old event schema - Snapshot entity map

Diagrams: - Event sourcing architecture - Event replay flow - Snapshotting strategy

Deliverables: - Event sourcing implementation - Replay procedures - Versioning strategy

CYCLE 12: Performance Optimization (~2,500 lines)¶

Topic 23: Query Performance Tuning¶

What will be covered: - Execution Plan Analysis - Enable SQL logging (show_sql, format_sql) - Review generated SQL - Analyze execution plans (SQL Server Management Studio) - Identify missing indexes

Index Strategy
Primary indexes on ID columns
Covering indexes for hot queries
Filtered indexes for partitions
Columnstore indexes for analytics
Query Optimization Techniques
Avoid SELECT * (project only needed columns)
Use Exists instead of Count > 0
Pagination with cursors (not offset)
Minimize joins (denormalize if needed)
Use appropriate fetch strategies (lazy vs. eager)
N+1 Query Problem
Detection: Watch for repeated queries in loop
Solution: Eager loading with Fetch()
Solution: Batch loading
Solution: Projection to DTO
Batch Fetching
NHibernate batch size configuration
Fetch multiple entities in one round-trip
Future queries (batch multiple queries)

Code Examples: - Execution plan capture - Query with covering index hint - Eager loading with Fetch() - Batch fetching configuration - Future queries pattern - Projection to DTO (avoid N+1)

Diagrams: - Query execution plan example - N+1 query problem illustration - Batch fetching flow

Deliverables: - Query performance tuning guide - Index recommendations - N+1 prevention patterns

Topic 24: Write Performance Optimization¶

What will be covered: - Batch Inserts - NHibernate batch size setting (adonet.batch_size) - Flush and clear pattern for large batches - Memory management during bulk inserts

Stateless Session
IStatelessSession for bulk operations
No change tracking overhead
No second-level cache interaction
Use case: Data migrations, bulk imports
DML-Style Operations
NHibernate HQL UPDATE/DELETE
Bypass entity loading
Use case: Bulk status updates
Connection Pooling
Min/max pool size tuning
Connection lifetime settings
Pool exhaustion monitoring
Transaction Batching
Commit every N records in bulk operations
Balance: transaction size vs. failure recovery
Asynchronous Writes
Fire-and-forget for non-critical operations
Background queues (Azure Service Bus)
Eventual consistency trade-offs

Code Examples: - Batch insert with flush/clear - Stateless session usage - HQL DML update - Connection pooling configuration - Transaction batching pattern - Async write to queue

Diagrams: - Batch insert flow with flush - Stateless session lifecycle - Connection pool management

Deliverables: - Write performance optimization guide - Batch operation patterns - Connection pool tuning

CYCLE 13: Testing Strategies (~2,500 lines)¶

Topic 25: Unit Testing Persistence Layer¶

What will be covered: - In-Memory Testing - SQLite in-memory database for tests - Schema generation with SchemaExport - Fast test execution - Limitations (dialect differences)

Test Fixtures
Arrange: Create test entities
Act: Execute repository operation
Assert: Verify results
Mocking vs. Real Database
Mock IUnitOfWork for pure unit tests
Real in-memory DB for integration tests
Trade-offs: speed vs. accuracy
Entity Mapping Tests
Verify mappings load without errors
Test CRUD operations on each entity
Verify constraints (nullable, unique, FK)
Repository Method Tests
Test Insert, Update, Delete
Test GetById, GetAll
Test Query and Specify methods
Test domain-specific methods

Code Examples: - SQLite in-memory setup for tests - Test fixture class (repository tests) - Entity mapping validation test - Repository CRUD test - Query test with assertions

Diagrams: - Test pyramid (unit vs. integration) - In-memory test setup

Deliverables: - Unit test suite structure - Test fixture library - Mapping validation tests

Topic 26: Integration Testing with Real Database¶

What will be covered: - Test Database Setup - Docker container for SQL Server - Schema creation with FluentMigrator - Test data seeding - Cleanup after tests

Transaction Rollback Pattern
Begin transaction before test
Rollback after test (no data persists)
Isolation between tests
Integration Test Scenarios
Multi-entity operations
Complex queries with joins
Transaction rollback behavior
Concurrency conflicts
Tenant isolation verification
Performance Benchmarking
Measure query execution time
Compare indexed vs. non-indexed
Identify slow queries
Set performance budgets

Code Examples: - Docker Compose for test database - Integration test with transaction rollback - Multi-entity operation test - Concurrency conflict test - Performance benchmark test

Diagrams: - Integration test architecture - Transaction rollback flow - Performance test results

Deliverables: - Integration test suite - Test database setup - Performance benchmarks

CYCLE 14: MongoDB Alternative Implementation (~2,500 lines)¶

Topic 27: MongoDB Persistence (Optional)¶

What will be covered: - When to Use MongoDB for ATP - Global-scale scenarios (multi-region writes) - Document-oriented data model fit - Horizontal scaling requirements

MongoDB Entity Mappings
BsonClassMap registration
Serialization conventions
Document structure
MongoDB Repository Implementation
IMongoCollection
CRUD operations
FilterDefinitionBuilder
Aggregation pipeline
MongoDB Specification Pattern
FilterDefinitionBuilder-based specifications
Complex filter composition
Projection specifications
MongoDB-Specific Features
Change streams for real-time events
Transactions (multi-document)
Time-series collections (if applicable)
Aggregation pipelines

Code Examples: - MongoDB entity mapping (BsonClassMap) - MongoDB repository implementation - FilterDefinition-based specification - Change stream consumer - Aggregation pipeline query

Diagrams: - MongoDB persistence architecture - Document model vs. relational - Change stream flow

Deliverables: - MongoDB persistence guide (optional) - Entity mappings for MongoDB - Repository implementations

Topic 28: Dual Persistence Strategy¶

What will be covered: - Supporting Multiple Backends - NHibernate (SQL) as primary - MongoDB as alternative - Keyed dependency injection

Abstraction Layer
Common repository interfaces
Provider-agnostic specifications
Configuration-based selection
Migration Scenarios
Migrate from SQL to MongoDB
Dual-write for transition period
Data validation and reconciliation
Trade-offs
Complexity: Two implementations to maintain
Flexibility: Choose best backend per use case
ATP recommendation: Start with SQL, add MongoDB if global scale needed

Code Examples: - Keyed DI configuration (primary provider selection) - Dual-write pattern (transitional) - Provider factory pattern - Configuration-based provider selection

Diagrams: - Dual persistence architecture - Migration strategy timeline - Provider selection logic

Deliverables: - Dual persistence strategy - Migration guide - Provider selection criteria

CYCLE 15: Operations & Best Practices (~2,500 lines)¶

Topic 29: Operational Considerations¶

What will be covered: - Monitoring & Diagnostics - SQL query logging - Slow query alerts (> 1 second) - Connection pool metrics - Transaction duration tracking - Deadlock detection and logging

Health Checks
Database connectivity check
SessionFactory health
Connection pool availability
Database Maintenance
Index maintenance (rebuild fragmented indexes)
Statistics updates
Transaction log management
Backup verification
Schema Migrations
FluentMigrator integration (see database-migrations.md)
Zero-downtime migrations
Rollback procedures
Disaster Recovery
Database backup strategies
Point-in-time recovery
Geo-replication
Failover testing

Code Examples: - Health check endpoint (database) - Slow query alert configuration - Connection pool metrics query - Index maintenance script - Backup verification script

Diagrams: - Monitoring dashboard layout - Health check flow - DR failover sequence

Deliverables: - Operations manual - Monitoring setup - Maintenance runbooks - DR procedures

Topic 30: Best Practices & Anti-Patterns¶

What will be covered: - Persistence Best Practices - Keep domain entities POCO (no ORM attributes) - Use FluentNHibernate mappings (separate from domain) - Repository per aggregate root (not per entity) - Unit of Work per use case (scoped lifetime) - Specification for queries (domain-oriented) - Async all the way (no blocking calls) - Batch operations for bulk writes - Cache read-heavy entities (policies, metadata) - Monitor slow queries (alerts + optimization) - Test mappings (unit tests for each entity map)

Code Examples: - Domain entity (POCO, good example) - Domain entity with ORM attributes (bad example) - Repository per aggregate (good) - Repository per table (bad) - Async repository method (good) - Blocking synchronous method (bad)

Diagrams: - Best practices reference architecture - Anti-patterns to avoid (before/after)

Deliverables: - Best practices guide - Anti-pattern catalog - Code review checklist - Performance checklist

Summary of Deliverables¶

Across all 15 cycles, this documentation will provide:

Architecture & Patterns
Persistence architecture overview
DDD tactical patterns (Repository, Unit of Work, Specification)
ConnectSoft.Extensions.PersistenceModel library reference
NHibernate Implementation
Configuration files (hibernate.cfg.xml)
DI registration patterns
SessionFactory and ISession management
Entity Mappings
FluentNHibernate mapping patterns
ATP entity mappings (AuditRecord, IntegrityBlock, Segment, etc.)
Complex relationship mappings
Repository & Unit of Work
Generic repository base classes
ATP-specific repository implementations
Transaction management patterns
Outbox pattern implementation
Specification Pattern
Queryable specification base classes
ATP-specific specifications
Query composition patterns
Advanced querying techniques
Performance & Caching
Query optimization strategies
Write performance patterns
Redis second-level cache setup
Cache invalidation strategies
Multi-Tenant Isolation
Tenant-scoped queries
RLS integration
Cross-tenant isolation testing
Testing
Unit test patterns (in-memory database)
Integration test patterns (real database)
Mapping validation tests
Performance benchmarks
Operations
Monitoring and diagnostics
Health checks
Database maintenance
Best practices and anti-patterns

Next Steps¶

Review & Approval: Validate cycle plan with architecture and data teams
Cycle 1 Generation: Begin content generation for persistence architecture overview
Entity Map Templates: Create FluentNHibernate mapping templates for ATP entities
Repository Base Classes: Implement generic repository base classes for ATP
Specification Library: Develop specification implementations for ATP queries

Data Architecture: Storage tiers, multi-tenant isolation, data models
Data Model: Entity specifications (AuditRecord, aggregates, VOs)
Database Migrations: FluentMigrator schema evolution
Aggregates & Entities: DDD domain model design
Architecture Overview: Clean architecture and layering
Outbox/Inbox Pattern: Reliable messaging with outbox
Messaging: Event-driven architecture and message bus integration
Testing: Testing strategies for persistence layer

This documentation plan covers the complete persistence layer implementation for ATP, from DDD patterns and NHibernate configuration to advanced querying, performance optimization, and operational best practices, fully leveraging the ConnectSoft.Extensions.PersistenceModel libraries and proven patterns from the microservice template.