Skip to content

Query, Views & Indexing - Audit Trail Platform (ATP)

Read-optimized, eventually consistent, tenant-isolated — ATP's query layer implements CQRS read models with denormalized projections, comprehensive indexing, full-text search capabilities, and multi-layered caching for sub-200ms query response times at scale.

📋 Documentation Generation Plan

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

Cycle Topics Estimated Lines Status
Cycle 1 Query Architecture & CQRS Principles (1-2) ~3,000 ⏳ Not Started
Cycle 2 Read Models & Projections Design (3-4) ~3,500 ⏳ Not Started
Cycle 3 Projection Workers Implementation (5-6) ~4,000 ⏳ Not Started
Cycle 4 Materialized Views (7-8) ~3,000 ⏳ Not Started
Cycle 5 Database Indexing Strategies (9-10) ~4,000 ⏳ Not Started
Cycle 6 Query Optimization Techniques (11-12) ~3,500 ⏳ Not Started
Cycle 7 Full-Text Search Implementation (13-14) ~4,000 ⏳ Not Started
Cycle 8 Advanced Search & Faceting (15-16) ~3,500 ⏳ Not Started
Cycle 9 Specification Pattern for Queries (17-18) ~3,000 ⏳ Not Started
Cycle 10 Pagination & Filtering (19-20) ~3,000 ⏳ Not Started
Cycle 11 Query Caching Strategies (21-22) ~3,500 ⏳ Not Started
Cycle 12 Multi-Tenant Query Isolation (23-24) ~3,000 ⏳ Not Started
Cycle 13 Row-Level Security (RLS) (25-26) ~2,500 ⏳ Not Started
Cycle 14 Query Performance Monitoring (27-28) ~3,000 ⏳ Not Started
Cycle 15 Projection Rebuild & Replay (29-30) ~3,000 ⏳ Not Started
Cycle 16 Search Index Management (31-32) ~3,000 ⏳ Not Started
Cycle 17 Testing Query Layer (33-34) ~2,500 ⏳ Not Started
Cycle 18 Best Practices & Troubleshooting (35-36) ~3,000 ⏳ Not Started

Total Estimated Lines: ~59,000

Purpose & Scope

This document provides the complete implementation guide for ATP's query layer, covering CQRS read models, projection workers, materialized views, database indexing, full-text search, query optimization, caching strategies, and multi-tenant isolation to deliver fast, reliable, and secure query capabilities.

Why Query Optimization for ATP?

  1. Performance: Sub-200ms query response times (P95) despite billions of audit records
  2. Scale: Support 10,000+ concurrent queries across thousands of tenants
  3. Flexibility: Rich query capabilities (filters, facets, full-text, date ranges, aggregations)
  4. Compliance: Tenant isolation, PII redaction, classification-aware queries
  5. Cost: Minimize database RU/DTU consumption and egress costs
  6. User Experience: Fast search, instant dashboards, responsive exports

ATP Query Capabilities

  • Timeline Queries: Chronological audit trail (sorted by timestamp)
  • Filtered Queries: By actor, resource, action, date range, classification
  • Full-Text Search: Keyword search across event descriptions, metadata
  • Faceted Search: Aggregations (top actors, most frequent actions, resource types)
  • Advanced Search: Combined filters, boolean logic, wildcards
  • Semantic Search: AI-powered relevance ranking (Enterprise edition)
  • Typeahead/Suggest: Auto-complete for actors, resources
  • Export Queries: Bulk retrieval with streaming

Query Layer Components

  • Read Models: Denormalized views optimized for specific query patterns
  • Projection Workers: Event consumers that update read models
  • Materialized Views: SQL views with indexed backing tables
  • Search Index: Elasticsearch/Azure Cognitive Search for full-text
  • Query Cache: Redis for frequent queries and aggregations
  • Specification Pattern: Domain-driven query building
  • Query API: REST endpoints with filtering, pagination, sorting

Detailed Cycle Plan

CYCLE 1: Query Architecture & CQRS Principles (~3,000 lines)

Topic 1: ATP Query Architecture Overview

What will be covered: - CQRS (Command Query Responsibility Segregation) 

Write Side (Command):
- Gateway → Ingestion → Append Store (HOT)
- Optimized for writes (append-only, minimal indexing)
- Authoritative source of truth
- Immutable, tamper-evident

Read Side (Query):
- Projection Workers → Read Models (WARM)
- Optimized for queries (denormalized, heavily indexed)
- Eventually consistent (projection lag: 1-5 seconds)
- Rebuildable from events

  • Separation of Concerns 

    flowchart LR
    subgraph Write Path
        API[REST API] --> ING[Ingestion]
        ING --> HOT[(HOT Storage<br/>Append-Only)]
        HOT --> OB[(Outbox)]
        OB --> BUS[Event Bus]
    end

    subgraph Read Path
        BUS --> PROJ[Projection Workers]
        PROJ --> WARM[(WARM Storage<br/>Read Models)]
        WARM --> QUERY[Query Service]
        QUERY --> API2[Query API]
    end

    subgraph Search Path
        PROJ --> IDX[(Search Index)]
        IDX --> SEARCH[Search Service]
        SEARCH --> API2
    end
    Hold "Alt" / "Option" to enable pan & zoom

  • Why Separate Write and Read Models?

  • Write Optimization: Minimal indexes, fast appends, WORM compliance
  • Read Optimization: Denormalized, indexed, cached for queries
  • Independent Scaling: Scale writes vs. reads independently
  • Schema Evolution: Read models can evolve without affecting writes

  • Performance: No query overhead on write path

  • ATP Read Model Strategy | Read Model | Purpose | Storage | Update Strategy | |------------|---------|---------|-----------------| | Timeline View | Chronological audit trail | Azure SQL | Real-time projection | | Actor View | Events by actor (user/service) | Azure SQL | Real-time projection | | Resource View | Events by resource | Azure SQL | Real-time projection | | Facet Aggregations | Top actors, actions, resources | Azure SQL + Redis | Incremental updates | | Search Index | Full-text search | Elasticsearch/Azure Search | Real-time indexing | | Export Cache | Pre-computed export datasets | Cosmos DB | Batch updates |

Code Examples: - CQRS architecture diagram - Write vs. read model comparison - Read model taxonomy

Diagrams: - CQRS data flow - ATP read models overview - Write-read separation

Deliverables: - Query architecture overview - CQRS design rationale - Read model catalog

Topic 2: Eventual Consistency Model

What will be covered: - Projection Lag 

Write Committed → Event Published → Projection Updated → Query Visible

Typical Timeline:
T+0ms:    Write committed to HOT storage
T+100ms:  Event published from outbox to Service Bus
T+500ms:  Projection worker receives event
T+800ms:  Read model updated
T+1000ms: Query cache invalidated
T+1200ms: Query API returns updated data

Total Lag: 1-2 seconds (P95)

  • Handling Eventual Consistency

  • Response Headers: 

    X-Index-Watermark: 01HZX123456789  (Last processed event ID)
X-Index-Lag: 1.2                   (Seconds behind write)
X-Projection-Version: timeline-v3   (Read model version)

  • Client Strategies: 

    // 1. Wait-and-poll (for critical reads)
var recordId = await CreateAuditRecordAsync(record);

// Wait for projection
await Task.Delay(2000);  // 2-second tolerance

var queryResult = await QueryByIdAsync(recordId);
if (queryResult == null)
{
    // Fallback: Read from authoritative store (slower)
    queryResult = await ReadFromHotStoreAsync(recordId);
}

// 2. Optimistic UI (show immediately, refresh asynchronously)
DisplayRecord(record);  // Show what was written
_ = Task.Run(async () =>
{
    await Task.Delay(2000);
    var updated = await QueryByIdAsync(recordId);
    RefreshUI(updated);  // Update with projection data
});

// 3. Read-your-writes (use sticky sessions or cache)
var cacheKey = $"audit:{recordId}";
_cache.Set(cacheKey, record, TimeSpan.FromSeconds(5));

  • ATP Tolerance Levels

  • Real-Time Critical (Export, Compliance): Read from HOT if projection lag >5s
  • Dashboard (Analytics): Tolerate 10-30s lag
  • Search: Tolerate 1-5s lag
  • Reports: Tolerate 1-5 minute lag

Code Examples: - Projection lag calculation - Client retry patterns - Fallback to authoritative store - Lag monitoring

Diagrams: - Eventual consistency timeline - Read-your-writes pattern - Fallback strategy

Deliverables: - Eventual consistency guide - Client patterns - Lag tolerance policies

CYCLE 2: Read Models & Projections Design (~3,500 lines)

Topic 3: Read Model Design Principles

What will be covered: - Denormalization Strategy 

// ❌ Normalized (requires joins)
public class AuditRecord
{
    public string Id { get; set; }
    public string ActorId { get; set; }  // FK to Actors table
    public string ResourceId { get; set; }  // FK to Resources table
}

public class Actor
{
    public string Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

// Query: SELECT * FROM AuditRecords JOIN Actors ON ... (slow)

// ✅ Denormalized (no joins)
public class AuditRecordView
{
    public string AuditRecordId { get; set; }
    public string TenantId { get; set; }

    // Denormalized actor data
    public string ActorId { get; set; }
    public string ActorName { get; set; }
    public string ActorEmail { get; set; }

    // Denormalized resource data
    public string ResourceId { get; set; }
    public string ResourceType { get; set; }
    public string ResourceName { get; set; }

    // Event data
    public DateTime OccurredAtUtc { get; set; }
    public string Action { get; set; }
    public string EventType { get; set; }

    // Classification & PII
    public string Classification { get; set; }
    public bool ContainsPII { get; set; }

    // Computed fields
    public string TimelineLabel { get; set; }  // "User john@example.com logged in"
    public string[] SearchableTags { get; set; }
}

// Query: SELECT * FROM AuditRecordView WHERE TenantId = 'acme' (fast, indexed)

  • Read Model Types 

    1. Timeline View
   - Chronological list of all events
   - Optimized for date range queries
   - Partitioned by tenant + date

2. Actor-Centric View
   - All events grouped by actor
   - Optimized for "show me all actions by user X"
   - Clustered index on (TenantId, ActorId, OccurredAtUtc)

3. Resource-Centric View
   - All events for a specific resource
   - Optimized for "show audit trail for resource Y"
   - Clustered index on (TenantId, ResourceId, OccurredAtUtc)

4. Facet/Aggregation View
   - Pre-computed counts and summaries
   - Top 10 actors, most frequent actions
   - Materialized daily/hourly

5. Search Document View
   - Full-text searchable representation
   - Optimized for keyword search, relevance ranking
   - Stored in search index (Elasticsearch/Azure Search)

  • Projection Update Frequency

  • Real-Time: Timeline, Actor, Resource views (1-2s lag)
  • Near Real-Time: Search index (5-10s lag)
  • Batch: Facet aggregations (1-5 minute lag)
  • On-Demand: Report datasets (generated when requested)

Code Examples: - Denormalized read model schema (complete) - All ATP read models - Update frequency configuration

Diagrams: - Read model taxonomy - Denormalization strategy - Update frequency timeline

Deliverables: - Read model design guide - Denormalization patterns - Update strategy

Topic 4: ATP Read Models Catalog

What will be covered: - Timeline View Schema 

CREATE TABLE audit.AuditRecordTimeline (
    AuditRecordId      VARCHAR(26) NOT NULL,            -- ULID
    TenantId           VARCHAR(64) NOT NULL,

    OccurredAtUtc      DATETIME2(3) NOT NULL,
    EventType          VARCHAR(128) NOT NULL,

    ActorId            VARCHAR(64) NULL,
    ActorType          VARCHAR(32) NULL,
    ActorName          NVARCHAR(256) NULL,
    ActorEmail         NVARCHAR(256) NULL,

    ResourceId         VARCHAR(128) NULL,
    ResourceType       VARCHAR(64) NULL,
    ResourceName       NVARCHAR(512) NULL,

    Action             VARCHAR(128) NOT NULL,
    Decision           VARCHAR(32) NULL,

    Classification     VARCHAR(32) NOT NULL DEFAULT 'SENSITIVE',
    ContainsPII        BIT NOT NULL DEFAULT 0,

    Message            NVARCHAR(MAX) NULL,              -- Redacted summary
    MetadataJson       NVARCHAR(MAX) NULL,              -- Additional context

    PolicyVersion      INT NOT NULL,

    -- Computed fields for UI
    TimelineLabel      NVARCHAR(512) NULL,

    -- Projection metadata
    ProjectedAtUtc     DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    ProjectionVersion  INT NOT NULL DEFAULT 1,

    CONSTRAINT PK_Timeline PRIMARY KEY CLUSTERED (TenantId, OccurredAtUtc DESC, AuditRecordId),
    INDEX IX_Timeline_Actor (TenantId, ActorId, OccurredAtUtc DESC),
    INDEX IX_Timeline_Resource (TenantId, ResourceId, OccurredAtUtc DESC),
    INDEX IX_Timeline_EventType (TenantId, EventType, OccurredAtUtc DESC),
    INDEX IX_Timeline_RecordId (AuditRecordId) INCLUDE (TenantId, OccurredAtUtc)
);

  • Actor View Schema 

    CREATE TABLE audit.ActorActivityView (
    ActorId            VARCHAR(64) NOT NULL,
    TenantId           VARCHAR(64) NOT NULL,

    ActorType          VARCHAR(32) NOT NULL,
    ActorName          NVARCHAR(256) NULL,
    ActorEmail         NVARCHAR(256) NULL,

    FirstActivityUtc   DATETIME2(3) NOT NULL,
    LastActivityUtc    DATETIME2(3) NOT NULL,

    TotalEvents        INT NOT NULL DEFAULT 0,
    ActionCounts       NVARCHAR(MAX) NULL,              -- JSON: {"login": 50, "logout": 48}
    ResourceAccess     NVARCHAR(MAX) NULL,              -- JSON: Top 10 resources accessed

    RiskScore          DECIMAL(5,2) NULL,               -- Computed risk indicator

    CONSTRAINT PK_ActorView PRIMARY KEY (TenantId, ActorId),
    INDEX IX_ActorView_LastActivity (TenantId, LastActivityUtc DESC),
    INDEX IX_ActorView_RiskScore (TenantId, RiskScore DESC) WHERE RiskScore IS NOT NULL
);

  • Resource View Schema 

    CREATE TABLE audit.ResourceActivityView (
    ResourceId         VARCHAR(128) NOT NULL,
    TenantId           VARCHAR(64) NOT NULL,
    ResourceType       VARCHAR(64) NOT NULL,
    ResourceName       NVARCHAR(512) NULL,

    FirstAccessUtc     DATETIME2(3) NOT NULL,
    LastAccessUtc      DATETIME2(3) NOT NULL,

    TotalEvents        INT NOT NULL DEFAULT 0,
    UniqueActors       INT NOT NULL DEFAULT 0,

    ActionCounts       NVARCHAR(MAX) NULL,
    AccessPatterns     NVARCHAR(MAX) NULL,

    CONSTRAINT PK_ResourceView PRIMARY KEY (TenantId, ResourceId),
    INDEX IX_ResourceView_Type (TenantId, ResourceType, LastAccessUtc DESC),
    INDEX IX_ResourceView_LastAccess (TenantId, LastAccessUtc DESC)
);

Code Examples: - Complete read model schemas (all views) - C# entity classes for read models - FluentNHibernate mappings

Diagrams: - Read model relationships - Schema diagrams

Deliverables: - Complete read model catalog - Schema definitions - Entity mappings

CYCLE 3: Projection Workers Implementation (~4,000 lines)

Topic 5: Projection Worker Architecture

What will be covered: - Projection Worker Components 

public class AuditRecordProjectionWorker : BackgroundService
{
    private readonly IServiceProvider _serviceProvider;
    private readonly ILogger<AuditRecordProjectionWorker> _logger;
    private readonly ProjectionWorkerOptions _options;

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        _logger.LogInformation("Projection worker starting");

        await using var scope = _serviceProvider.CreateAsyncScope();
        var eventConsumer = scope.ServiceProvider.GetRequiredService<IEventConsumer>();

        await eventConsumer.SubscribeAsync("audit.accepted", async (evt, ctx) =>
        {
            await ProcessAuditAcceptedEventAsync(evt, ctx, stoppingToken);
        }, stoppingToken);
    }

    private async Task ProcessAuditAcceptedEventAsync(
        AuditAcceptedEvent evt,
        MessageContext ctx,
        CancellationToken cancellationToken)
    {
        using var scope = _serviceProvider.CreateScope();

        var timelineProjector = scope.ServiceProvider.GetRequiredService<ITimelineProjector>();
        var actorProjector = scope.ServiceProvider.GetRequiredService<IActorProjector>();
        var resourceProjector = scope.ServiceProvider.GetRequiredService<IResourceProjector>();
        var searchIndexer = scope.ServiceProvider.GetRequiredService<ISearchIndexer>();

        // Check inbox for duplicate
        var inboxRepo = scope.ServiceProvider.GetRequiredService<IInboxRepository>();
        var messageId = ctx.MessageId;

        if (await inboxRepo.HasProcessedAsync("ProjectionWorker", messageId))
        {
            _logger.LogInformation("Duplicate event {MessageId}, skipping", messageId);
            return;  // Idempotent
        }

        // Process projections (idempotent upserts)
        await Task.WhenAll(
            timelineProjector.UpdateAsync(evt),
            actorProjector.UpdateAsync(evt),
            resourceProjector.UpdateAsync(evt),
            searchIndexer.IndexAsync(evt)
        );

        // Mark as processed
        await inboxRepo.MarkProcessedAsync("ProjectionWorker", messageId, DateTime.UtcNow);

        _logger.LogInformation("Processed event {EventId} for tenant {TenantId}",
            evt.AuditRecordId, evt.TenantId);
    }
}

  • Projector Interface 

    public interface IProjector<TEvent>
{
    Task UpdateAsync(TEvent domainEvent);
    Task RebuildAsync(string tenantId, DateTime fromUtc, DateTime toUtc);
    Task<ProjectionWatermark> GetWatermarkAsync(string tenantId);
}

public class ProjectionWatermark
{
    public string TenantId { get; set; }
    public string ProjectionName { get; set; }
    public string LastEventId { get; set; }
    public DateTime LastEventTimestamp { get; set; }
    public DateTime UpdatedAtUtc { get; set; }
    public long EventsProcessed { get; set; }
}

  • Idempotent Projection Updates 

    public class TimelineProjector : IProjector<AuditAcceptedEvent>
{
    private readonly ITimelineRepository _repository;

    public async Task UpdateAsync(AuditAcceptedEvent evt)
    {
        // Upsert (idempotent - safe to call multiple times)
        var timelineEntry = new AuditRecordTimeline
        {
            AuditRecordId = evt.AuditRecordId,
            TenantId = evt.TenantId,
            OccurredAtUtc = evt.OccurredAtUtc,
            EventType = evt.EventType,
            ActorId = evt.Actor?.Id,
            ActorName = evt.Actor?.Name,
            ActorEmail = evt.Actor?.Email,
            ResourceId = evt.Resource?.Id,
            ResourceType = evt.Resource?.Type,
            ResourceName = evt.Resource?.Name,
            Action = evt.Action,
            Decision = evt.Decision,
            Classification = evt.Classification,
            ContainsPII = evt.ContainsPII,
            Message = evt.RedactedMessage,
            TimelineLabel = BuildTimelineLabel(evt),
            ProjectedAtUtc = DateTime.UtcNow
        };

        await _repository.UpsertAsync(timelineEntry);
    }

    private string BuildTimelineLabel(AuditAcceptedEvent evt)
    {
        // Generate human-readable label
        return $"{evt.Actor?.Name ?? "System"} {evt.Action} {evt.Resource?.Type} {evt.Resource?.Name}";
    }
}

Code Examples: - Complete projection worker implementation - All projector implementations (Timeline, Actor, Resource, Search) - Idempotent update patterns - Watermark tracking

Diagrams: - Projection worker architecture - Parallel projection updates - Watermark flow

Deliverables: - Projection worker implementation - All projector implementations - Watermark system

Topic 6: Projection Orchestration

What will be covered: - Multi-Projector Coordination - Parallel projection updates (Timeline, Actor, Resource) - All-or-nothing semantics (transaction boundary) - Checkpoint advancement after all projections succeed

  • KEDA Autoscaling for Projectors 

    apiVersion: keda.sh/v1alpha1
kind: ScaledObject
metadata:
  name: projection-worker-scaler
spec:
  scaleTargetRef:
    name: projection-worker
  minReplicaCount: 2
  maxReplicaCount: 20
  triggers:
  - type: azure-servicebus
    metadata:
      queueName: projection-queue
      namespace: atp-prod
      messageCount: "50"
      activationMessageCount: "10"

  • Partition Strategy

  • Partition by TenantId (each worker processes specific tenants)
  • Preserve ordering within partition
  • Parallel processing across tenants

Code Examples: - Multi-projector orchestration - KEDA autoscaling configuration - Partition assignment logic

Diagrams: - Projector orchestration flow - KEDA scaling behavior

Deliverables: - Orchestration implementation - Autoscaling configuration - Partition strategy

CYCLE 4: Materialized Views (~3,000 lines)

Topic 7: SQL Materialized Views

What will be covered: - Indexed Views in SQL Server 

-- Create view
CREATE VIEW audit.DailyActivitySummary
WITH SCHEMABINDING
AS
SELECT 
    TenantId,
    CAST(OccurredAtUtc AS DATE) AS ActivityDate,
    COUNT_BIG(*) AS EventCount,
    COUNT_BIG(DISTINCT ActorId) AS UniqueActors,
    COUNT_BIG(DISTINCT ResourceId) AS UniqueResources
FROM audit.AuditRecordTimeline
GROUP BY TenantId, CAST(OccurredAtUtc AS DATE);

-- Create unique clustered index (materializes the view)
CREATE UNIQUE CLUSTERED INDEX IX_DailyActivity
ON audit.DailyActivitySummary (TenantId, ActivityDate);

-- Additional indexes for query patterns
CREATE INDEX IX_DailyActivity_EventCount
ON audit.DailyActivitySummary (TenantId, EventCount DESC);

  • Materialized View Benefits
  • Pre-computed aggregations (instant query response)
  • Automatically maintained by SQL Server
  • Query optimizer uses view instead of base tables

  • Reduced CPU for repeated aggregations

  • Materialized View Limitations

  • Update overhead (view maintained on every base table change)
  • Schema limitations (GROUP BY, no OUTER JOIN, etc.)
  • Disk space (view is physically stored)

  • Not supported in Azure SQL Database Basic tier

  • ATP Materialized Views 

    View Name                    | Purpose                       | Refresh Strategy
-----------------------------|-------------------------------|------------------
DailyActivitySummary         | Events per tenant per day     | Automatic (indexed view)
HourlyActivitySummary        | Events per hour (hot data)    | Automatic (indexed view)
TopActorsSummary             | Most active actors            | Manual refresh (nightly)
ResourceAccessMatrix         | Resource access patterns      | Manual refresh (nightly)
ComplianceReportView         | Pre-filtered compliance data  | On-demand refresh

Code Examples: - Materialized view creation scripts (all ATP views) - View refresh procedures - Query patterns using views

Diagrams: - Materialized view architecture - Refresh strategy timeline

Deliverables: - Materialized view library - Refresh procedures - Performance comparison

Topic 8: View Refresh Strategies

What will be covered: - Automatic Refresh (Indexed Views) - SQL Server maintains automatically - Updated on every base table change - Best for frequently queried, slowly changing data

  • Scheduled Refresh (Manual) 

    -- Refresh procedure
CREATE PROCEDURE audit.RefreshTopActorsSummary
    @TenantId VARCHAR(64)
AS
BEGIN
    DELETE FROM audit.TopActorsSummary WHERE TenantId = @TenantId;

    INSERT INTO audit.TopActorsSummary (TenantId, ActorId, ActorName, EventCount, LastActivityUtc)
    SELECT TOP 100
        TenantId,
        ActorId,
        MAX(ActorName) AS ActorName,
        COUNT(*) AS EventCount,
        MAX(OccurredAtUtc) AS LastActivityUtc
    FROM audit.AuditRecordTimeline
    WHERE TenantId = @TenantId
      AND OccurredAtUtc >= DATEADD(day, -30, GETUTCDATE())
    GROUP BY TenantId, ActorId
    ORDER BY EventCount DESC;
END;

-- Schedule with Azure SQL Agent or HangFire

  • Incremental Refresh

  • Track last refresh timestamp
  • Only process new/changed records
  • Use watermark/checkpoint pattern

Code Examples: - Refresh stored procedures - HangFire job scheduling - Incremental refresh logic

Diagrams: - Refresh strategies comparison - Scheduled refresh timeline

Deliverables: - Refresh procedures - Scheduling configuration - Incremental update implementation

CYCLE 5: Database Indexing Strategies (~4,000 lines)

Topic 9: Index Design Principles

What will be covered: - Index Types 

1. Clustered Index
   - Physical ordering of data
   - One per table
   - ATP: (TenantId, OccurredAtUtc DESC, AuditRecordId)

2. Non-Clustered Index
   - Separate B-tree structure
   - Multiple per table
   - ATP: Actor, Resource, EventType indexes

3. Covering Index (Include Columns)
   - Index includes all queried columns
   - No table lookup required
   - ATP: Include frequently selected columns

4. Filtered Index
   - Index subset of rows (WHERE clause)
   - Smaller, faster
   - ATP: Active tenants, recent events, PII events

5. Full-Text Index
   - Specialized for text search
   - ATP: Message, ResourceName, ActorName

6. Columnstore Index
   - Columnar storage for analytics
   - ATP: Cold storage aggregations

  • ATP Indexing Strategy 

    -- Primary clustered index (tenant + time)
CREATE CLUSTERED INDEX IX_Timeline_Primary
ON audit.AuditRecordTimeline (TenantId, OccurredAtUtc DESC, AuditRecordId);

-- Actor queries (covering index)
CREATE NONCLUSTERED INDEX IX_Timeline_Actor
ON audit.AuditRecordTimeline (TenantId, ActorId, OccurredAtUtc DESC)
INCLUDE (EventType, Action, ResourceType, ResourceName, Classification);

-- Resource queries (covering index)
CREATE NONCLUSTERED INDEX IX_Timeline_Resource
ON audit.AuditRecordTimeline (TenantId, ResourceId, OccurredAtUtc DESC)
INCLUDE (EventType, Action, ActorName, ActorEmail, Classification);

-- Event type filtering
CREATE NONCLUSTERED INDEX IX_Timeline_EventType
ON audit.AuditRecordTimeline (TenantId, EventType, OccurredAtUtc DESC)
INCLUDE (ActorName, ResourceName, Action);

-- Point lookup (by audit record ID)
CREATE UNIQUE NONCLUSTERED INDEX IX_Timeline_RecordId
ON audit.AuditRecordTimeline (AuditRecordId)
INCLUDE (TenantId, OccurredAtUtc, EventType, ActorName, ResourceName);

-- Filtered index (recent events, hot data)
CREATE NONCLUSTERED INDEX IX_Timeline_Recent
ON audit.AuditRecordTimeline (TenantId, OccurredAtUtc DESC)
WHERE OccurredAtUtc >= DATEADD(day, -30, SYSUTCDATETIME());

-- PII events (filtered index for compliance queries)
CREATE NONCLUSTERED INDEX IX_Timeline_PII
ON audit.AuditRecordTimeline (TenantId, OccurredAtUtc DESC, Classification)
WHERE ContainsPII = 1;

  • Index Selection Guidelines

  • Selectivity: Index columns with high cardinality
  • Query Patterns: Index columns in WHERE, JOIN, ORDER BY
  • Include Columns: Add SELECT columns to avoid lookups
  • Filtered: Index subsets for common filters (recent, PII, active)

Code Examples: - Complete index creation scripts (all ATP indexes) - Index usage analysis queries - Index recommendation scripts

Diagrams: - Index types comparison - Covering index structure - Filtered index benefits

Deliverables: - Complete indexing strategy - Index creation scripts - Analysis tools

Topic 10: Index Maintenance & Optimization

What will be covered: - Index Fragmentation 

-- Check fragmentation
SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
    AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- Rebuild fragmented indexes
ALTER INDEX IX_Timeline_Primary ON audit.AuditRecordTimeline REBUILD WITH (ONLINE = ON);

-- Reorganize moderately fragmented indexes
ALTER INDEX IX_Timeline_Actor ON audit.AuditRecordTimeline REORGANIZE;

  • Index Statistics Update
  • Automatic statistics (SQL Server default)
  • Manual update for critical queries

  • Statistics histogram monitoring

  • Missing Index Recommendations 

    -- Find missing indexes
SELECT 
    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    'CREATE INDEX IX_' + OBJECT_NAME(mid.object_id) + '_Missing_' + CONVERT(VARCHAR, migs.index_group_handle)
        + ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') + ISNULL(mid.inequality_columns, '')  + ')'
        + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY improvement_measure DESC;

Code Examples: - Index maintenance scripts - Fragmentation monitoring - Missing index analysis - Automated maintenance jobs

Diagrams: - Index lifecycle - Maintenance schedule

Deliverables: - Index maintenance procedures - Monitoring scripts - Automation jobs

CYCLE 6: Query Optimization Techniques (~3,500 lines)

Topic 11: Query Performance Patterns

What will be covered: - Efficient Query Patterns 

// ❌ ANTI-PATTERN: N+1 queries
public async Task<List<AuditRecordDto>> GetAuditRecordsAsync(string tenantId)
{
    var records = await _repository.GetByTenantAsync(tenantId);

    foreach (var record in records)
    {
        // Separate query for each record (N+1 problem)
        record.Actor = await _actorRepo.GetByIdAsync(record.ActorId);
        record.Resource = await _resourceRepo.GetByIdAsync(record.ResourceId);
    }

    return records;
}

// ✅ PATTERN: Eager loading with includes
public async Task<List<AuditRecordDto>> GetAuditRecordsAsync(string tenantId)
{
    // Single query with denormalized view (no joins needed)
    var records = await _specification
        .Where(r => r.TenantId == tenantId)
        .OrderByDescending(r => r.OccurredAtUtc)
        .Take(100)
        .Select(r => new AuditRecordDto
        {
            Id = r.AuditRecordId,
            ActorName = r.ActorName,  // Already denormalized
            ResourceName = r.ResourceName,  // Already denormalized
            EventType = r.EventType,
            OccurredAtUtc = r.OccurredAtUtc
        })
        .ToListAsync();

    return records;
}

  • Query Execution Plan Analysis
  • Use EXPLAIN/Execution Plan in SQL Server Management Studio
  • Identify table scans (bad) vs. index seeks (good)
  • Detect missing indexes

  • Monitor actual vs. estimated rows

  • Batch Queries 

    // ❌ ANTI-PATTERN: Loop with queries
foreach (var actorId in actorIds)
{
    var events = await _repository.GetByActorAsync(actorId);
    results.Add(events);
}

// ✅ PATTERN: Single query with IN clause
var events = await _specification
    .Where(r => actorIds.Contains(r.ActorId))
    .ToListAsync();

  • Projection vs. Select 

    // ❌ Load full entities (unnecessary data transfer)
var records = await _repository.GetAllAsync();

// ✅ Project only needed fields (reduce payload)
var records = await _specification
    .Select(r => new { r.Id, r.EventType, r.OccurredAtUtc })
    .ToListAsync();

Code Examples: - All query optimization patterns - Execution plan analysis - Anti-pattern examples and fixes

Diagrams: - N+1 problem visualization - Execution plan comparison

Deliverables: - Query optimization guide - Pattern catalog - Performance analysis tools

Topic 12: Query Plan Caching & Parameterization

What will be covered: - Parameterized Queries 

// ✅ Parameterized (plan reuse)
var query = _session.Query<AuditRecordTimeline>()
    .Where(r => r.TenantId == tenantId && r.OccurredAtUtc >= fromDate)
    .OrderByDescending(r => r.OccurredAtUtc);

// SQL generated:
// SELECT * FROM AuditRecordTimeline 
// WHERE TenantId = @p0 AND OccurredAtUtc >= @p1
// ORDER BY OccurredAtUtc DESC

// Query plan cached and reused

  • Plan Guides (Advanced)
  • Force specific index usage
  • Override query optimizer decisions

  • Use sparingly (optimizer usually best)

  • Query Hints 

    -- Force index usage
SELECT * FROM audit.AuditRecordTimeline WITH (INDEX(IX_Timeline_Actor))
WHERE TenantId = @tenantId AND ActorId = @actorId;

-- Force recompile (dynamic queries)
EXEC sp_executesql @sql WITH RECOMPILE;

Code Examples: - Parameterized query patterns - Plan guide creation - Query hint usage

Diagrams: - Query plan caching flow

Deliverables: - Parameterization guide - Plan optimization techniques

CYCLE 7: Full-Text Search Implementation (~4,000 lines)

Topic 13: Full-Text Index Setup

What will be covered: - SQL Server Full-Text Search 

-- Enable full-text search on database
CREATE FULLTEXT CATALOG AuditFullTextCatalog AS DEFAULT;

-- Create full-text index on Timeline view
CREATE FULLTEXT INDEX ON audit.AuditRecordTimeline
(
    Message LANGUAGE 1033,           -- English
    ResourceName LANGUAGE 1033,
    ActorName LANGUAGE 1033
)
KEY INDEX PK_Timeline
WITH CHANGE_TRACKING AUTO;

-- Configure stopwords (the, and, or, etc.)
CREATE FULLTEXT STOPLIST AuditStopList;
ALTER FULLTEXT STOPLIST AuditStopList ADD 'the' LANGUAGE 1033;
ALTER FULLTEXT STOPLIST AuditStopList ADD 'and' LANGUAGE 1033;

-- Apply stoplist to index
ALTER FULLTEXT INDEX ON audit.AuditRecordTimeline SET STOPLIST = AuditStopList;

  • Full-Text Query Syntax 

    // Simple keyword search
var results = await _session.Query<AuditRecordTimeline>()
    .Where(r => r.TenantId == tenantId)
    .Where(r => NHibernateUtil.FreeText(r.Message, searchTerm))
    .ToListAsync();

// SQL: SELECT * FROM AuditRecordTimeline 
// WHERE TenantId = @p0 AND CONTAINS(Message, @p1)

// Phrase search
var results = await _session.Query<AuditRecordTimeline>()
    .Where(r => r.TenantId == tenantId)
    .Where(r => NHibernateUtil.Contains(r.Message, "\"user login\""))
    .ToListAsync();

// Boolean search (AND, OR, NOT)
var searchExpression = "login AND (success OR failure) AND NOT \"password reset\"";
var results = await _session.Query<AuditRecordTimeline>()
    .Where(r => r.TenantId == tenantId)
    .Where(r => NHibernateUtil.Contains(r.Message, searchExpression))
    .ToListAsync();

  • Ranking & Relevance

  • TF-IDF scoring (term frequency, inverse document frequency)
  • BM25 ranking algorithm
  • Custom relevance tuning (field weights)

Code Examples: - Full-text index creation (complete) - Full-text query patterns (all syntax types) - Ranking configuration

Diagrams: - Full-text index architecture - Query processing flow

Deliverables: - Full-text index setup - Query patterns library - Ranking configuration

Topic 14: Azure Cognitive Search Integration

What will be covered: - Azure Cognitive Search vs. SQL Full-Text | Feature | SQL Full-Text | Azure Cognitive Search | |---------|---------------|------------------------| | Performance | Good (< 1M docs) | Excellent (billions) | | Relevance Ranking | Basic (TF-IDF) | Advanced (BM25, semantic) | | Faceting | Manual (GROUP BY) | Built-in | | Typeahead | Manual | Built-in (suggester) | | Semantic Search | ❌ | ✅ (AI-powered) | | Cost | Included with SQL | Separate service ($$$) | | ATP Usage | Dev/Test, small tenants | Production, Enterprise |

  • Azure Cognitive Search Setup 

    services.AddAzureCognitiveSearch(options =>
{
    options.ServiceName = "atp-search-prod";
    options.AdminApiKey = configuration["Search:AdminApiKey"];  // From Key Vault
    options.IndexName = "audit-records";
    options.Credential = new DefaultAzureCredential();
});

// Index schema
var indexDefinition = new SearchIndex("audit-records")
{
    Fields =
    {
        new SimpleField("auditRecordId", SearchFieldDataType.String) { IsKey = true, IsFilterable = true },
        new SimpleField("tenantId", SearchFieldDataType.String) { IsFilterable = true, IsFacetable = true },
        new SearchableField("message") { IsSearchable = true, AnalyzerName = LexicalAnalyzerName.EnMicrosoft },
        new SearchableField("actorName") { IsSearchable = true, IsSortable = true },
        new SearchableField("resourceName") { IsSearchable = true, IsSortable = true },
        new SimpleField("occurredAtUtc", SearchFieldDataType.DateTimeOffset) { IsSortable = true, IsFilterable = true },
        new SimpleField("eventType", SearchFieldDataType.String) { IsFilterable = true, IsFacetable = true },
        new SimpleField("action", SearchFieldDataType.String) { IsFilterable = true, IsFacetable = true },
        new SimpleField("classification", SearchFieldDataType.String) { IsFilterable = true }
    }
};

await searchClient.CreateIndexAsync(indexDefinition);

  • Indexing Pipeline

  • Real-time: Projection worker → Search index
  • Batch: Bulk indexer (rebuild scenarios)
  • Incremental: Only index new/changed records

Code Examples: - Azure Cognitive Search configuration - Index schema definition - Indexing pipeline implementation

Diagrams: - Azure Search architecture - Indexing pipeline flow

Deliverables: - Azure Search setup guide - Index schema definitions - Indexing pipeline

CYCLE 8: Advanced Search & Faceting (~3,500 lines)

Topic 15: Faceted Search Implementation

What will be covered: - Faceting Basics 

Facets = Aggregations for filtering

Example: Search for "login" events
Results:
- 1,234 total results
- Facets:
    Event Type: login (800), logout (234), password_reset (200)
    Actor Type: user (1000), service (234)
    Decision: allow (1200), deny (34)
    Date Range: Last 24h (500), Last week (734), Older (0)

  • SQL Facet Queries 

    -- Facet by event type
SELECT 
    EventType,
    COUNT(*) AS Count
FROM audit.AuditRecordTimeline
WHERE TenantId = @tenantId
    AND Message LIKE '%login%'
GROUP BY EventType
ORDER BY Count DESC;

-- Multiple facets
SELECT 
    'EventType' AS FacetType, EventType AS FacetValue, COUNT(*) AS Count
FROM audit.AuditRecordTimeline
WHERE TenantId = @tenantId AND Message LIKE '%login%'
GROUP BY EventType
UNION ALL
SELECT 
    'ActorType', ActorType, COUNT(*)
FROM audit.AuditRecordTimeline
WHERE TenantId = @tenantId AND Message LIKE '%login%'
GROUP BY ActorType
UNION ALL
SELECT 
    'Decision', Decision, COUNT(*)
FROM audit.AuditRecordTimeline
WHERE TenantId = @tenantId AND Message LIKE '%login%'
GROUP BY Decision;

  • Azure Cognitive Search Facets 

    var searchOptions = new SearchOptions
{
    Filter = $"tenantId eq '{tenantId}'",
    Facets = { "eventType,count:10", "action,count:10", "classification" }
};

var results = await searchClient.SearchAsync<AuditRecordDocument>(searchText, searchOptions);

// Results include facets
foreach (var facet in results.Facets["eventType"])
{
    Console.WriteLine($"{facet.Value}: {facet.Count}");
}

  • Pre-Computed Facets (Performance)

  • Materialize common facets (Top 10 actors, actions)
  • Refresh hourly/daily
  • Cache in Redis

Code Examples: - SQL facet queries (all facet types) - Azure Search faceting - Pre-computed facet tables - Facet caching

Diagrams: - Faceted search UI mockup - Facet computation flow

Deliverables: - Faceting implementation - Pre-computed facet system - Caching strategy

Topic 16: Typeahead & Autocomplete

What will be covered: - Suggester Configuration (Azure Search) 

var indexDefinition = new SearchIndex("audit-records")
{
    Suggesters =
    {
        new SearchSuggester("actor-suggester", "actorName", "actorEmail"),
        new SearchSuggester("resource-suggester", "resourceName", "resourceType")
    }
};

  • Suggest API 

    var suggestOptions = new SuggestOptions
{
    Filter = $"tenantId eq '{tenantId}'",
    Size = 10,
    UseFuzzyMatching = true
};

var suggestions = await searchClient.SuggestAsync<AuditRecordDocument>(
    searchText: "john",
    suggesterName: "actor-suggester",
    options: suggestOptions);

// Returns: ["john@example.com", "johnny.appleseed@acme.com", ...]

  • SQL-Based Typeahead 

    -- Typeahead for actor names
SELECT DISTINCT TOP 10
    ActorName,
    ActorEmail
FROM audit.AuditRecordTimeline
WHERE TenantId = @tenantId
    AND ActorName LIKE @prefix + '%'
ORDER BY ActorName;

Code Examples: - Suggester configuration - Suggest API implementation - SQL typeahead queries - Fuzzy matching

Diagrams: - Typeahead flow - Suggester architecture

Deliverables: - Typeahead implementation - Suggester configuration - API integration

CYCLE 9: Specification Pattern for Queries (~3,000 lines)

Topic 17: Specification Pattern Implementation

What will be covered: - ISpecification Interface (ConnectSoft.Extensions) 

public interface ISpecification<TEntity, TId>
{
    IQueryable<TEntity> Apply(IQueryable<TEntity> query);
    IQueryable<TEntity> Where(Expression<Func<TEntity, bool>> predicate);
    IQueryable<TEntity> OrderBy(Expression<Func<TEntity, object>> keySelector);
    IQueryable<TEntity> OrderByDescending(Expression<Func<TEntity, object>> keySelector);
    IQueryable<TResult> Select<TResult>(Expression<Func<TEntity, TResult>> selector);
    Task<List<TEntity>> ToListAsync();
    Task<TEntity> FirstOrDefaultAsync();
    Task<int> CountAsync();
}

  • Concrete Specification 

    public class AuditRecordTimelineSpecification : QueryableSpecification<AuditRecordTimeline, string>
{
    public AuditRecordTimelineSpecification(IUnitOfWorkConvertor unitOfWork)
        : base(unitOfWork)
    {
    }

    // Domain-driven query methods
    public IQueryable<AuditRecordTimeline> ByTenant(string tenantId)
    {
        return Where(r => r.TenantId == tenantId);
    }

    public IQueryable<AuditRecordTimeline> ByActor(string actorId)
    {
        return Where(r => r.ActorId == actorId);
    }

    public IQueryable<AuditRecordTimeline> ByResource(string resourceId)
    {
        return Where(r => r.ResourceId == resourceId);
    }

    public IQueryable<AuditRecordTimeline> InDateRange(DateTime from, DateTime to)
    {
        return Where(r => r.OccurredAtUtc >= from && r.OccurredAtUtc <= to);
    }

    public IQueryable<AuditRecordTimeline> WithEventType(string eventType)
    {
        return Where(r => r.EventType == eventType);
    }

    public IQueryable<AuditRecordTimeline> ContainsPII()
    {
        return Where(r => r.ContainsPII == true);
    }

    public IQueryable<AuditRecordTimeline> ChronologicalDescending()
    {
        return OrderByDescending(r => r.OccurredAtUtc);
    }
}

  • Fluent Query Building 

    // Build complex queries fluently
var results = await _specification
    .ByTenant(tenantId)
    .ByActor(actorId)
    .InDateRange(DateTime.UtcNow.AddDays(-30), DateTime.UtcNow)
    .WithEventType("user.login")
    .ChronologicalDescending()
    .Select(r => new AuditRecordDto
    {
        Id = r.AuditRecordId,
        ActorName = r.ActorName,
        OccurredAtUtc = r.OccurredAtUtc
    })
    .ToListAsync();

// SQL generated:
// SELECT AuditRecordId, ActorName, OccurredAtUtc
// FROM AuditRecordTimeline
// WHERE TenantId = @p0
//   AND ActorId = @p1
//   AND OccurredAtUtc >= @p2
//   AND OccurredAtUtc <= @p3
//   AND EventType = @p4
// ORDER BY OccurredAtUtc DESC

Code Examples: - Complete specification implementation - Fluent query builder - Complex query examples

Diagrams: - Specification pattern architecture - Query composition flow

Deliverables: - Specification implementation - Query builder library - Usage guide

Topic 18: Dynamic Query Building

What will be covered: - Expression Trees for Dynamic Filters 

public class DynamicQueryBuilder
{
    public IQueryable<AuditRecordTimeline> BuildQuery(
        IQueryable<AuditRecordTimeline> baseQuery,
        QueryFilter filter)
    {
        var query = baseQuery;

        // Apply tenant filter (always)
        query = query.Where(r => r.TenantId == filter.TenantId);

        // Dynamic filters
        if (!string.IsNullOrEmpty(filter.ActorId))
        {
            query = query.Where(r => r.ActorId == filter.ActorId);
        }

        if (!string.IsNullOrEmpty(filter.ResourceId))
        {
            query = query.Where(r => r.ResourceId == filter.ResourceId);
        }

        if (filter.EventTypes?.Any() == true)
        {
            query = query.Where(r => filter.EventTypes.Contains(r.EventType));
        }

        if (filter.FromDate.HasValue)
        {
            query = query.Where(r => r.OccurredAtUtc >= filter.FromDate.Value);
        }

        if (filter.ToDate.HasValue)
        {
            query = query.Where(r => r.OccurredAtUtc <= filter.ToDate.Value);
        }

        // Dynamic sorting
        query = filter.SortField switch
        {
            "occurredAt" => filter.SortDescending
                ? query.OrderByDescending(r => r.OccurredAtUtc)
                : query.OrderBy(r => r.OccurredAtUtc),
            "actorName" => filter.SortDescending
                ? query.OrderByDescending(r => r.ActorName)
                : query.OrderBy(r => r.ActorName),
            _ => query.OrderByDescending(r => r.OccurredAtUtc)  // Default
        };

        return query;
    }
}

  • Query Filter DTO 
    public class QueryFilter
{
    [Required]
    public string TenantId { get; set; }

    public string ActorId { get; set; }
    public string ResourceId { get; set; }
    public string[] EventTypes { get; set; }
    public DateTime? FromDate { get; set; }
    public DateTime? ToDate { get; set; }
    public string Classification { get; set; }
    public bool? ContainsPII { get; set; }

    public string SortField { get; set; } = "occurredAt";
    public bool SortDescending { get; set; } = true;

    public int Page { get; set; } = 1;
    public int PageSize { get; set; } = 100;
}

Code Examples: - Dynamic query builder - Expression tree manipulation - Filter DTO validation

Diagrams: - Dynamic query flow

Deliverables: - Dynamic query builder - Filter DTOs - Validation rules

CYCLE 10: Pagination & Filtering (~3,000 lines)

Topic 19: Pagination Patterns

What will be covered: - Offset-Based Pagination 

public async Task<PagedResult<AuditRecordDto>> GetPagedResultsAsync(
    string tenantId,
    int page = 1,
    int pageSize = 100)
{
    var query = _specification
        .ByTenant(tenantId)
        .ChronologicalDescending();

    // Get total count (cached)
    var totalCount = await query.CountAsync();

    // Get page
    var items = await query
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .ToListAsync();

    return new PagedResult<AuditRecordDto>
    {
        Items = items,
        Page = page,
        PageSize = pageSize,
        TotalCount = totalCount,
        TotalPages = (int)Math.Ceiling(totalCount / (double)pageSize),
        HasNextPage = page < totalPages,
        HasPreviousPage = page > 1
    };
}

  • Keyset Pagination (Better Performance) 

    // Cursor-based pagination (no SKIP, no COUNT)
public async Task<CursorPagedResult<AuditRecordDto>> GetCursorPagedResultsAsync(
    string tenantId,
    string afterCursor = null,
    int pageSize = 100)
{
    DateTime? afterTimestamp = null;
    string afterRecordId = null;

    if (!string.IsNullOrEmpty(afterCursor))
    {
        // Decode cursor: base64(timestamp:recordId)
        var decoded = DecodeBase64(afterCursor);
        var parts = decoded.Split(':');
        afterTimestamp = DateTime.Parse(parts[0]);
        afterRecordId = parts[1];
    }

    var query = _specification.ByTenant(tenantId);

    if (afterTimestamp.HasValue)
    {
        // Seek to position (keyset pagination)
        query = query.Where(r =>
            r.OccurredAtUtc < afterTimestamp.Value ||
            (r.OccurredAtUtc == afterTimestamp.Value && r.AuditRecordId.CompareTo(afterRecordId) < 0)
        );
    }

    var items = await query
        .OrderByDescending(r => r.OccurredAtUtc)
        .ThenByDescending(r => r.AuditRecordId)
        .Take(pageSize + 1)  // Fetch one extra to check hasMore
        .ToListAsync();

    var hasMore = items.Count > pageSize;
    if (hasMore)
    {
        items = items.Take(pageSize).ToList();
    }

    string nextCursor = null;
    if (hasMore && items.Any())
    {
        var last = items.Last();
        nextCursor = EncodeBase64($"{last.OccurredAtUtc:O}:{last.AuditRecordId}");
    }

    return new CursorPagedResult<AuditRecordDto>
    {
        Items = items,
        NextCursor = nextCursor,
        HasMore = hasMore
    };
}

  • Pagination Performance | Method | Performance | Use Case | |--------|-------------|----------| | Offset (SKIP/TAKE) | Degrades with offset (page 1000 = slow) | Small datasets, random access | | Keyset (Cursor) | Constant performance (always fast) | Large datasets, sequential access | | ATP Default | Keyset for Timeline, Offset for filtered queries | Hybrid |

Code Examples: - Offset pagination implementation - Keyset pagination implementation - Cursor encoding/decoding - Performance comparison

Diagrams: - Pagination methods comparison - Keyset pagination flow

Deliverables: - Pagination implementations - Cursor system - Performance guide

Topic 20: Advanced Filtering

What will be covered: - Multi-Field Filters 

public class AdvancedFilter
{
    public string TenantId { get; set; }

    // Actor filters
    public string[] ActorIds { get; set; }
    public string[] ActorTypes { get; set; }
    public string ActorNameContains { get; set; }

    // Resource filters
    public string[] ResourceIds { get; set; }
    public string[] ResourceTypes { get; set; }
    public string ResourceNameContains { get; set; }

    // Event filters
    public string[] EventTypes { get; set; }
    public string[] Actions { get; set; }
    public string[] Decisions { get; set; }

    // Time filters
    public DateTime? FromDate { get; set; }
    public DateTime? ToDate { get; set; }
    public TimeRangePreset? Preset { get; set; }  // Last24Hours, Last7Days, Last30Days

    // Classification filters
    public string[] Classifications { get; set; }
    public bool? ContainsPII { get; set; }

    // Full-text search
    public string SearchText { get; set; }
}

  • Complex Filter Queries 
    var query = _specification.ByTenant(filter.TenantId);

// Apply multiple filters
if (filter.ActorIds?.Any() == true)
{
    query = query.Where(r => filter.ActorIds.Contains(r.ActorId));
}

if (!string.IsNullOrEmpty(filter.SearchText))
{
    query = query.Where(r => 
        EF.Functions.FreeText(r.Message, filter.SearchText) ||
        r.ActorName.Contains(filter.SearchText) ||
        r.ResourceName.Contains(filter.SearchText));
}

var results = await query.ToListAsync();

Code Examples: - Advanced filter implementation - Complex filter queries - Filter validation

Diagrams: - Filter composition - Query building flow

Deliverables: - Advanced filtering implementation - Filter DTOs - Validation rules

CYCLE 11: Query Caching Strategies (~3,500 lines)

Topic 21: Multi-Level Caching

What will be covered: - L1: In-Memory Cache (IMemoryCache) 

public class CachedQueryService
{
    private readonly IMemoryCache _cache;
    private readonly IQueryService _queryService;

    public async Task<PagedResult<AuditRecordDto>> GetTimelineAsync(
        string tenantId,
        int page,
        int pageSize)
    {
        var cacheKey = $"timeline:{tenantId}:p{page}:ps{pageSize}";

        if (_cache.TryGetValue(cacheKey, out PagedResult<AuditRecordDto> cachedResult))
        {
            return cachedResult;  // L1 cache hit (~0.1ms)
        }

        var result = await _queryService.GetTimelineAsync(tenantId, page, pageSize);

        // Cache for 5 minutes
        _cache.Set(cacheKey, result, TimeSpan.FromMinutes(5));

        return result;
    }
}

  • L2: Distributed Cache (Redis) 

    public class RedisQueryCache
{
    private readonly IDistributedCache _cache;

    public async Task<T> GetOrSetAsync<T>(
        string key,
        Func<Task<T>> factory,
        TimeSpan? ttl = null)
    {
        // Check Redis cache
        var cached = await _cache.GetStringAsync(key);
        if (cached != null)
        {
            return JsonSerializer.Deserialize<T>(cached);  // L2 cache hit (~2ms)
        }

        // Cache miss, execute query
        var value = await factory();

        // Store in Redis
        var json = JsonSerializer.Serialize(value);
        await _cache.SetStringAsync(key, json, new DistributedCacheEntryOptions
        {
            AbsoluteExpirationRelativeToNow = ttl ?? TimeSpan.FromMinutes(15)
        });

        return value;
    }
}

  • L3: Query Result Cache (SQL Server)

  • NHibernate second-level cache (Redis-backed)
  • Query cache for repeated queries

  • Entity cache for frequently accessed entities

  • Cache Invalidation 

    public class CacheInvalidationService
{
    public async Task InvalidateTenantCacheAsync(string tenantId)
    {
        // Invalidate all cache entries for tenant
        var pattern = $"*:{tenantId}:*";
        await _cache.RemoveByPatternAsync(pattern);
    }

    public async Task InvalidateOnProjectionUpdateAsync(ProjectionUpdatedEvent evt)
    {
        // Smart invalidation (only affected queries)
        var keys = new[]
        {
            $"timeline:{evt.TenantId}:*",
            $"actor:{evt.TenantId}:{evt.ActorId}:*",
            $"resource:{evt.TenantId}:{evt.ResourceId}:*"
        };

        foreach (var pattern in keys)
        {
            await _cache.RemoveByPatternAsync(pattern);
        }
    }
}

Code Examples: - Complete multi-level caching implementation - Cache invalidation strategies - Cache-aside pattern

Diagrams: - Multi-level cache architecture - Cache invalidation flow

Deliverables: - Caching implementation (all levels) - Invalidation strategies - Performance benchmarks

Topic 22: Query Cache Optimization

What will be covered: - Cache Key Design 

Pattern: {queryType}:{tenantId}:{filters}:{page}:{pageSize}

Examples:
- timeline:acme-corp:all:p1:ps100
- actor:acme-corp:actor-123:d30:p1:ps50
- resource:contoso:res-456:all:p1:ps100
- search:fabrikam:q-login:p1:ps20

  • Cache Warming
  • Pre-populate cache for common queries
  • Warm cache after projection update

  • Scheduled cache warming (daily reports)

  • Cache Monitoring

  • Hit rate (target: >80%)
  • Miss latency (database query time)
  • Eviction rate
  • Memory usage

Code Examples: - Cache key generation - Cache warming procedures - Cache metrics collection

Diagrams: - Cache key structure - Warming strategy

Deliverables: - Cache key standards - Warming implementation - Monitoring dashboards

CYCLE 12: Multi-Tenant Query Isolation (~3,000 lines)

Topic 23: Tenant-Scoped Queries

What will be covered: - Mandatory Tenant Filter 

public class TenantScopedQueryInterceptor : IInterceptor
{
    public void BeforeQuery(QueryEventArgs args)
    {
        // Inject tenant filter into ALL queries
        var tenantId = _tenantResolver.GetCurrentTenantId();

        if (string.IsNullOrEmpty(tenantId))
        {
            throw new SecurityException("TenantId required for all queries");
        }

        // Modify query to include tenant filter
        args.Query = args.Query.Where(e => 
            ((ITenantEntity)e).TenantId == tenantId);
    }
}

  • Tenant Resolver 

    public interface ITenantResolver
{
    string GetCurrentTenantId();
    Task<string> GetCurrentTenantIdAsync();
}

public class HttpContextTenantResolver : ITenantResolver
{
    private readonly IHttpContextAccessor _httpContextAccessor;

    public string GetCurrentTenantId()
    {
        var httpContext = _httpContextAccessor.HttpContext;

        // Priority 1: JWT claim
        var tenantClaim = httpContext?.User.FindFirst("tenantId");
        if (tenantClaim != null)
        {
            return tenantClaim.Value;
        }

        // Priority 2: Header
        if (httpContext?.Request.Headers.TryGetValue("X-Tenant-Id", out var header) == true)
        {
            return header.ToString();
        }

        throw new SecurityException("TenantId not found in JWT claims or headers");
    }
}

  • Query Validation

  • Reject queries without tenant context
  • Validate tenant ID against authenticated user
  • Audit all cross-tenant query attempts

Code Examples: - Tenant filter injection - Tenant resolver implementation - Query validation middleware

Diagrams: - Tenant resolution flow - Filter injection architecture

Deliverables: - Tenant-scoped query interceptor - Tenant resolver - Validation middleware

Topic 24: Partition-Based Isolation

What will be covered: - Table Partitioning by Tenant 

-- Partition function (by tenant hash)
CREATE PARTITION FUNCTION PF_TenantHash (VARCHAR(64))
AS RANGE LEFT FOR VALUES (
    'tenant-0000', 'tenant-0100', 'tenant-0200', ..., 'tenant-9900'
);

-- Partition scheme
CREATE PARTITION SCHEME PS_TenantHash
AS PARTITION PF_TenantHash
ALL TO ([PRIMARY]);

-- Partitioned table
CREATE TABLE audit.AuditRecordTimeline (
    ...
) ON PS_TenantHash(TenantId);

  • Partition Pruning
  • Query optimizer eliminates irrelevant partitions
  • Scan only partitions for specific tenant
  • Dramatic performance improvement for multi-tenant tables

Code Examples: - Partition function/scheme creation - Partitioned table migration - Partition pruning verification

Diagrams: - Table partitioning architecture - Partition pruning flow

Deliverables: - Partitioning implementation - Migration scripts - Performance analysis

CYCLE 13: Row-Level Security (RLS) (~2,500 lines)

Topic 25: SQL Server RLS Implementation

What will be covered: - RLS Policy Creation 

-- Enable RLS on table
ALTER TABLE audit.AuditRecordTimeline ENABLE ROW LEVEL SECURITY;

-- Create security policy
CREATE SECURITY POLICY audit.TenantIsolationPolicy
ADD FILTER PREDICATE dbo.fn_TenantAccessPredicate(TenantId)
ON audit.AuditRecordTimeline,
ADD BLOCK PREDICATE dbo.fn_TenantAccessPredicate(TenantId)
ON audit.AuditRecordTimeline AFTER INSERT;

-- Predicate function
CREATE FUNCTION dbo.fn_TenantAccessPredicate(@TenantId VARCHAR(64))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS AccessResult
WHERE @TenantId = CAST(SESSION_CONTEXT(N'TenantId') AS VARCHAR(64))
    OR IS_MEMBER('db_owner') = 1  -- Allow admins
    OR IS_MEMBER('PlatformAdmin') = 1;

  • Setting Session Context 

    public class RlsConnectionInterceptor : IDbConnectionInterceptor
{
    private readonly ITenantResolver _tenantResolver;

    public override async ValueTask<InterceptionResult<DbCommand>> CommandCreatingAsync(
        DbConnection connection,
        DbCommandEventData eventData,
        InterceptionResult<DbCommand> result,
        CancellationToken cancellationToken = default)
    {
        // Set session context before each query
        var tenantId = _tenantResolver.GetCurrentTenantId();

        using var setContextCmd = connection.CreateCommand();
        setContextCmd.CommandText = "EXEC sp_set_session_context @key = N'TenantId', @value = @tenantId;";
        setContextCmd.Parameters.Add(new SqlParameter("@tenantId", tenantId));
        await setContextCmd.ExecuteNonQueryAsync(cancellationToken);

        return result;
    }
}

  • RLS Benefits & Trade-Offs | Aspect | Benefit | Trade-Off | |--------|---------|-----------| | Security | Enforced at database level | Performance overhead | | Defense-in-Depth | Works even if app bugs | Complex troubleshooting | | Compliance | Audit-friendly | Limited to SQL Server/PostgreSQL |

Code Examples: - Complete RLS setup (SQL + C#) - Session context management - Admin bypass patterns

Diagrams: - RLS architecture - Session context flow

Deliverables: - RLS implementation guide - Session context interceptor - Admin access patterns

Topic 26: Tenant Isolation Testing

What will be covered: - Isolation Test Suite 

[TestClass]
public class TenantIsolationTests
{
    [TestMethod]
    public async Task Should_OnlyReturnTenantData()
    {
        // Arrange: Create data for two tenants
        await CreateAuditRecordAsync("tenant-a", "record-a1");
        await CreateAuditRecordAsync("tenant-a", "record-a2");
        await CreateAuditRecordAsync("tenant-b", "record-b1");

        // Act: Query as tenant-a
        SetCurrentTenant("tenant-a");
        var results = await _queryService.GetTimelineAsync("tenant-a", page: 1, pageSize: 100);

        // Assert: Only tenant-a data returned
        Assert.AreEqual(2, results.TotalCount);
        Assert.IsTrue(results.Items.All(r => r.TenantId == "tenant-a"));
    }

    [TestMethod]
    public async Task Should_RejectCrossTenantQuery()
    {
        // Arrange
        SetCurrentTenant("tenant-a");

        // Act & Assert: Attempt to query tenant-b
        await Assert.ThrowsExceptionAsync<SecurityException>(async () =>
        {
            await _queryService.GetTimelineAsync("tenant-b", page: 1, pageSize: 100);
        });
    }
}

Code Examples: - Complete isolation test suite - Cross-tenant query prevention - RLS validation tests

Diagrams: - Isolation test architecture

Deliverables: - Isolation test suite - Security validation - Compliance tests

CYCLE 14: Query Performance Monitoring (~3,000 lines)

Topic 27: Query Metrics & Telemetry

What will be covered: - Query Performance Metrics 

public class QueryTelemetry
{
    // Query execution time
    [Histogram("query_duration_ms", "Query execution time")]
    public void RecordQueryDuration(double durationMs, string queryType, string tenantId);

    // Query result size
    [Histogram("query_result_count", "Number of results returned")]
    public void RecordResultCount(int count, string queryType);

    // Cache hit/miss
    [Counter("query_cache_hits_total", "Query cache hits")]
    public void RecordCacheHit(string queryType, string tenantId);

    [Counter("query_cache_misses_total", "Query cache misses")]
    public void RecordCacheMiss(string queryType, string tenantId);

    // Slow queries
    [Counter("query_slow_total", "Slow queries (>1s)")]
    public void RecordSlowQuery(string queryType, string tenantId, double durationMs);
}

  • Application Insights Queries 

    // Query performance (P50, P95, P99)
customMetrics
| where name == "query_duration_ms"
| extend QueryType = tostring(customDimensions.QueryType)
| extend TenantId = tostring(customDimensions.TenantId)
| summarize 
    P50 = percentile(value, 50),
    P95 = percentile(value, 95),
    P99 = percentile(value, 99),
    Count = count()
    by QueryType
| where P95 > 200  // SLO: P95 < 200ms
| order by P95 desc

// Slow query details
customMetrics
| where name == "query_slow_total"
| extend QueryType = tostring(customDimensions.QueryType)
| extend TenantId = tostring(customDimensions.TenantId)
| extend DurationMs = todouble(customDimensions.DurationMs)
| summarize SlowCount = sum(value), AvgDuration = avg(DurationMs) by QueryType, TenantId
| order by SlowCount desc

  • Query SLOs

  • P50 latency: <50ms
  • P95 latency: <200ms
  • P99 latency: <500ms
  • Cache hit rate: >80%

Code Examples: - Query telemetry implementation - Application Insights queries - SLO monitoring - Alert rules

Diagrams: - Telemetry flow - Dashboard layout

Deliverables: - Telemetry library - Monitoring queries - SLO dashboards

Topic 28: Query Execution Plan Monitoring

What will be covered: - Query Store (SQL Server) 

-- Enable Query Store
ALTER DATABASE AuditDb SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60,
    MAX_STORAGE_SIZE_MB = 1000,
    QUERY_CAPTURE_MODE = AUTO
);

-- Find expensive queries
SELECT TOP 10
    qt.query_sql_text,
    rs.avg_duration / 1000.0 AS avg_duration_ms,
    rs.avg_logical_io_reads,
    rs.count_executions
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE qt.query_sql_text LIKE '%AuditRecordTimeline%'
ORDER BY rs.avg_duration DESC;

  • Execution Plan Regression Detection
  • Monitor plan changes over time
  • Alert on plan regression (performance degradation)
  • Force previous plan if needed

Code Examples: - Query Store configuration - Expensive query detection - Plan regression monitoring

Diagrams: - Query Store architecture

Deliverables: - Query Store setup - Monitoring queries - Regression alerts

CYCLE 15: Projection Rebuild & Replay (~3,000 lines)

Topic 29: Projection Rebuild Architecture

What will be covered: - Rebuild Scenarios - Schema change (add new field to read model) - Bug fix in projection logic - Data corruption recovery - New read model introduction - Tenant onboarding (historical data)

  • Rebuild Process 

    public class ProjectionRebuildService
{
    public async Task RebuildProjectionAsync(RebuildRequest request)
    {
        // Validate request
        ValidateRebuildRequest(request);

        // Create rebuild job
        var job = new RebuildJob
        {
            Id = Ulid.NewUlid().ToString(),
            TenantId = request.TenantId,
            ProjectionName = request.ProjectionName,
            FromDate = request.FromDate,
            ToDate = request.ToDate,
            Status = RebuildStatus.Pending,
            CreatedAtUtc = DateTime.UtcNow
        };

        await _jobRepo.SaveAsync(job);

        // Queue rebuild job
        await _bus.Publish(new RebuildProjectionCommand
        {
            JobId = job.Id,
            TenantId = request.TenantId,
            ProjectionName = request.ProjectionName,
            FromDate = request.FromDate,
            ToDate = request.ToDate
        });

        return job.Id;
    }
}

public class RebuildWorker : IConsumer<RebuildProjectionCommand>
{
    public async Task Consume(ConsumeContext<RebuildProjectionCommand> context)
    {
        var cmd = context.Message;

        // Mark job as running
        await UpdateJobStatusAsync(cmd.JobId, RebuildStatus.Running);

        try
        {
            // Read events from authoritative store (HOT)
            var events = await _eventStore.ReadEventsAsync(
                tenantId: cmd.TenantId,
                fromDate: cmd.FromDate,
                toDate: cmd.ToDate);

            var projector = GetProjector(cmd.ProjectionName);

            // Clear existing projection data (for rebuild)
            await projector.ClearAsync(cmd.TenantId, cmd.FromDate, cmd.ToDate);

            // Replay events
            int processed = 0;
            foreach (var evt in events)
            {
                await projector.UpdateAsync(evt);
                processed++;

                if (processed % 1000 == 0)
                {
                    // Progress update
                    await UpdateJobProgressAsync(cmd.JobId, processed, events.Count());
                }
            }

            // Mark job as completed
            await UpdateJobStatusAsync(cmd.JobId, RebuildStatus.Completed, processed);

            _logger.LogInformation("Rebuild completed for {Projection}, processed {Count} events",
                cmd.ProjectionName, processed);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Rebuild failed for {Projection}", cmd.ProjectionName);
            await UpdateJobStatusAsync(cmd.JobId, RebuildStatus.Failed, error: ex.Message);
        }
    }
}

  • Rebuild Strategies

  • Full Rebuild: Clear and replay all events
  • Incremental Rebuild: Replay from specific checkpoint
  • Window Rebuild: Rebuild specific time range
  • Dry-Run: Validate without applying changes

Code Examples: - Complete rebuild service implementation - Rebuild worker - Progress tracking - Dry-run validation

Diagrams: - Rebuild workflow - Progress tracking flow

Deliverables: - Rebuild service implementation - Worker implementation - Progress dashboard

Topic 30: Replay from Event Store

What will be covered: - Event Replay Source - Read from HOT storage (authoritative) - Preserve event ordering - Filter by tenant, date range, event type

  • Replay Safeguards
  • Idempotent projections (safe to replay)
  • Checkpoint tracking (resume from failure)
  • Rate limiting (don't overwhelm database)

  • Cancellation support (graceful stop)

  • Replay API 

    [HttpPost("api/admin/projection/rebuild")]
[Authorize(Roles = "Administrator")]
public async Task<IActionResult> RebuildProjection([FromBody] RebuildRequest request)
{
    // Validate tenant access
    if (!await _authService.CanAccessTenantAsync(User, request.TenantId))
    {
        return Forbid();
    }

    // Start rebuild
    var jobId = await _rebuildService.RebuildProjectionAsync(request);

    return Accepted(new { jobId, status = "Queued" });
}

[HttpGet("api/admin/projection/rebuild/{jobId}")]
public async Task<IActionResult> GetRebuildStatus(string jobId)
{
    var job = await _jobRepo.GetAsync(jobId);

    return Ok(new
    {
        jobId = job.Id,
        status = job.Status,
        progress = job.ProcessedCount,
        total = job.TotalCount,
        startedAt = job.StartedAtUtc,
        completedAt = job.CompletedAtUtc
    });
}

Code Examples: - Event replay implementation - Safeguards and checkpoints - Rebuild API (complete)

Diagrams: - Replay flow - Checkpoint recovery

Deliverables: - Replay implementation - Admin API - Safety mechanisms

CYCLE 16: Search Index Management (~3,000 lines)

Topic 31: Search Index Lifecycle

What will be covered: - Index Creation - Schema definition - Field mappings and analyzers - Replica and shard configuration - Synonym sets

  • Index Versioning 

    Blue-Green Index Strategy:
1. Create new index: audit-records-v2
2. Populate from projection
3. Validate (smoke tests)
4. Switch alias: audit-records → audit-records-v2
5. Delete old index: audit-records-v1

  • Index Maintenance

  • Merge segments (reduce fragmentation)
  • Optimize index (force merge)
  • Delete old documents
  • Monitor shard health

Code Examples: - Index creation scripts - Blue-green deployment - Maintenance procedures

Diagrams: - Index lifecycle - Blue-green deployment flow

Deliverables: - Index management guide - Deployment procedures - Maintenance automation

Topic 32: Search Query Optimization

What will be covered: - Query Optimization - Use filters before full-text search - Limit searchable fields - Use field weighting (boost) - Optimize result size

  • Performance Patterns 
    // ✅ Filter first, then search
var searchOptions = new SearchOptions
{
    Filter = $"tenantId eq '{tenantId}' and occurredAtUtc ge {fromDate}",
    SearchFields = { "message", "actorName", "resourceName" },
    Select = { "auditRecordId", "eventType", "occurredAtUtc", "message" },
    Size = 20,
    Skip = 0
};

Code Examples: - Search optimization patterns - Field weighting configuration - Result size optimization

Diagrams: - Search optimization flow

Deliverables: - Search optimization guide - Best practices

CYCLE 17: Testing Query Layer (~2,500 lines)

Topic 33: Query Testing Strategies

What will be covered: - Unit Testing Specifications 

[TestClass]
public class AuditRecordSpecificationTests
{
    [TestMethod]
    public void Should_FilterByTenant()
    {
        // Arrange
        var spec = new AuditRecordTimelineSpecification(_unitOfWork);

        // Act
        var query = spec.ByTenant("test-tenant");

        // Assert
        var sql = query.ToQueryString();
        Assert.IsTrue(sql.Contains("TenantId = @p0"));
    }
}

  • Integration Testing Projections 

    [TestMethod]
public async Task Should_UpdateProjectionOnEvent()
{
    // Arrange
    var evt = new AuditAcceptedEvent
    {
        AuditRecordId = "test-record-1",
        TenantId = "test-tenant",
        ActorId = "user-123"
    };

    // Act
    await _timelineProjector.UpdateAsync(evt);

    // Assert
    var record = await _repository.GetByIdAsync("test-record-1");
    Assert.IsNotNull(record);
    Assert.AreEqual("user-123", record.ActorId);
}

  • Performance Testing

  • Load testing with k6
  • Query response time under load
  • Cache effectiveness
  • Index usage verification

Code Examples: - Complete test suites (unit, integration, performance) - Test data builders - Performance benchmarks

Diagrams: - Test architecture

Deliverables: - Complete test suite - Performance tests - Test utilities

Topic 34: Query Contract Testing

What will be covered: - API Contract Tests - Verify query response schemas - Pagination contract validation - Filter parameter validation - Error response validation

  • Projection Contract Tests
  • Verify read model schema matches events
  • Validate denormalization correctness
  • Check computed field logic

Code Examples: - API contract tests - Projection validation tests

Diagrams: - Contract testing flow

Deliverables: - Contract test suite - Validation framework

CYCLE 18: Best Practices & Troubleshooting (~3,000 lines)

Topic 35: Query Layer Best Practices

What will be covered: - Design Best Practices - ✅ Denormalize aggressively (avoid joins) - ✅ Index query patterns (WHERE, ORDER BY, JOIN columns) - ✅ Use covering indexes (INCLUDE columns) - ✅ Partition large tables (by tenant, date) - ✅ Cache frequent queries (Redis) - ✅ Use keyset pagination (large result sets) - ✅ Enforce tenant filters (all queries) - ✅ Monitor slow queries (>200ms) - ✅ Pre-compute aggregations (materialized views) - ✅ Test with production-scale data

  • Anti-Patterns to Avoid
  • ❌ N+1 queries (load related data in loop)
  • ❌ SELECT * (load unnecessary columns)
  • ❌ No indexes (table scans)
  • ❌ Over-indexing (slow writes)
  • ❌ No caching (repeated database hits)
  • ❌ Offset pagination (deep pages slow)
  • ❌ No tenant filter (security risk)
  • ❌ Mutable read models (break CQRS)

Code Examples: - Best practice implementations - Anti-pattern examples

Diagrams: - Best practices checklist

Deliverables: - Best practices handbook - Anti-pattern catalog

Topic 36: Troubleshooting Query Performance

What will be covered: - Common Problems - Problem: Slow queries (>1s) - Cause: Missing indexes, table scans, deep pagination - Solution: Add indexes, use covering indexes, keyset pagination - Debug: Execution plan, Query Store, missing index DMV

  • Problem: High projection lag (>10s)

    • Cause: Slow projection workers, database bottleneck
    • Solution: Scale workers (KEDA), optimize upserts, batch updates
    • Debug: Check worker logs, monitor queue depth

  • Problem: Cache not effective (<50% hit rate)

    • Cause: Low TTL, frequent invalidations, unique queries
    • Solution: Increase TTL, smart invalidation, cache warming
    • Debug: Monitor cache hit/miss rates

  • Problem: Cross-tenant data leakage

    • Cause: Missing tenant filter, RLS not enabled
    • Solution: Enable RLS, tenant filter interceptor
    • Debug: Query logs, security audit

Code Examples: - Troubleshooting queries - Debug utilities - Common fixes

Diagrams: - Troubleshooting decision tree

Deliverables: - Troubleshooting guide - Debug tools - Common problems catalog

Summary of Deliverables

Across all 18 cycles, this documentation will provide:

  1. CQRS & Architecture
  2. Query architecture with CQRS separation
  3. Eventual consistency model and client patterns

  4. Read model design principles

  5. Read Models & Projections

  6. Complete read model catalog (Timeline, Actor, Resource, Facets, Search)
  7. Projection worker implementation

  8. Watermark tracking and orchestration

  9. Materialized Views

  10. SQL indexed views
  11. View refresh strategies (automatic, scheduled, incremental)

  12. Performance optimization

  13. Database Indexing

  14. Complete indexing strategy (clustered, non-clustered, covering, filtered, full-text)
  15. Index maintenance procedures

  16. Missing index detection

  17. Query Optimization

  18. Performance patterns (avoid N+1, projection, batching)
  19. Query plan caching and parameterization

  20. Execution plan analysis

  21. Full-Text Search

  22. SQL Server full-text indexes
  23. Azure Cognitive Search integration

  24. Faceted search and typeahead

  25. Query Patterns

  26. Specification pattern for domain-driven queries
  27. Dynamic query building
  28. Pagination (offset and keyset)

  29. Advanced filtering

  30. Caching

  31. Multi-level caching (memory, Redis, query cache)
  32. Cache invalidation strategies

  33. Cache warming and monitoring

  34. Multi-Tenant Isolation

  35. Tenant-scoped queries
  36. Row-Level Security (RLS)
  37. Partition-based isolation

  38. Isolation testing

  39. Operations

    • Query performance monitoring and telemetry
    • Projection rebuild and replay
    • Search index management
    • Testing strategies
    • Best practices and troubleshooting

This documentation plan covers complete query layer implementation for ATP, from CQRS read models and projection workers to database indexing strategies, full-text search, specification pattern, multi-level caching, multi-tenant isolation, Row-Level Security, performance monitoring, projection rebuild, and operational best practices for delivering fast, reliable, and secure query capabilities at scale.