# MigrationClaude.md — GB5 Module Migration Guide

> **Audience:** Developers migrating legacy GB4/GB4.7 modules to GB5.
> **Scope:** Read (list + single), Save, Delete, multi-DB, caching, schema support, code hygiene, response formats.
> **Do not copy this file verbatim** — treat each section as a recipe to adapt.

---

## Table of Contents

1. [Overview — what changes](#1-overview)
2. [Shared infrastructure (GB5Shared/ListQuery/)](#2-shared-infrastructure)
3. [Pattern A — Direct handler (simple, single-variant)](#3-pattern-a--direct-handler)
4. [Pattern B — Router dispatch (multi-variant legacy endpoints)](#4-pattern-b--router-dispatch)
5. [Pilot example: Allocation / GetPendingAllocations](#5-pilot-example-allocation)
6. [Save pattern](#6-save-pattern)
7. [Delete pattern](#7-delete-pattern)
8. [Multi-DB support](#8-multi-db-support)
9. [Schema-qualified tables](#9-schema-qualified-tables)
10. [Dual search text (body + query param)](#10-dual-search-text)
11. [Caching](#11-caching)
12. [DI registration checklist](#12-di-registration-checklist)
13. [Anti-patterns — do not carry forward](#13-anti-patterns)
14. [Code hygiene — migration clean-up rules](#14-code-hygiene)
15. [Cross-entity dependency — use existing BLL, not copy-paste DAL](#15-cross-entity-dependency)
16. [Response formats — JSON, NDJSON stream, Excel, PDF](#16-response-formats)
17. [File naming & location reference](#17-file-naming--location-reference)
18. [Verification checklist](#18-verification-checklist)
19. [List Query Infrastructure — IQueryBuilder, GenericListHandler, zero handler files](#19--list-query-infrastructure)
20. [QueryGuard — mandatory filter enforcement](#20--queryguard--mandatory-filter-enforcement)
21. [Sort & Pagination — allowlist, ISortable, keyset guardrails](#21--sort--pagination)
22. [Critical Analysis Culture](#22--critical-analysis-culture)
23. [Layer Return Type Contract & Codegen Checklist](#23--layer-return-type-contract--codegen-checklist)
27. [Version Bump — mandatory in every schema-changing migration](#27--version-bump--mandatory-in-every-schema-changing-migration)

---

## 1. Overview

| Legacy pattern | GB5 replacement |
|----------------|----------------|
| Fat BLL method with 20+ `if/else` query-builder branches | `IListQuery<T>` + `IListHandler<TQ,T>` per variant, routed by `IListDispatcher` |
| Manual `for i / for j` criteria parsing loops | `CriteriaBinder.Bind<TCriteria>(dto)` — one line |
| Hardcoded table aliases like `"a."` in SQL helper methods | `QueryContext.Col("allocation","ALLOCATIONID")` → `"a.ALLOCATIONID"` |
| `new HttpClient()` / custom cache classes | Dapr state store via `BaseEndpoint` + `KeyGenerator` |
| MVC `[ApiController]` | FastEndpoints `BaseEndpoint<TReq,TRes>` |
| `IDbConnection` as a service field | `IQueryExecutor` (managed connection lifetime) |
| One giant SQL string constant in QB | `QueryContext` + `SqlClauses` + `ISqlDialect` — composable, multi-DB |
| Copy-pasted methods named `GetFooNew`, `GetFooFast`, `GetFooLatest` | One canonical method; variants become named `IListQuery` records |
| Commented-out code blocks left in files | Delete entirely — git history is the archive |
| Entity Y re-implementing Entity X's query to get X's data | Inject `IXxxBLL` into `YyyBLL`; call the existing method |
| All responses serialized as one opaque JSON string | Choose the right mode: paged JSON object, NDJSON stream, Excel, PDF (see §16) |

---

## 2. Shared Infrastructure

All files below live in **`GB5Shared/ListQuery/`** and are written **once** for the entire codebase.

| File | What it provides |
|------|-----------------|
| `ICriteria.cs` | `ICriteria`, `[CriteriaField]`, `CriteriaBinder`, `CriteriaRouterHelper` |
| `QueryContext.cs` | Alias + schema registry; `Col()`, `Table()`, `Alias()` |
| `SqlClause.cs` | `SqlClause`, `SqlClauses` reusable filter library |
| `ISqlDialect.cs` | `SqlServerDialect`, `PostgreSqlDialect`, `OracleDialect`, `SqlDialectFactory` |
| `IListQuery.cs` | `IListQuery<T>` (marker), `IListQuery<T,TCriteria>` (typed), `IListHandler<TQ,T>`, `IListDispatcher`, `ListDispatcher` |
| `SqlListHandler.cs` | `SqlListHandler<TQuery,TCriteria,TResult>` — abstract base; owns dialect resolution + `QueryPagedAsync` + `QueryStreamAsync`; subclasses override only `BuildSql` |

**Do not modify these files per-module.** Add module-specific `SqlClauses` methods only if they are reused across 2+ modules.

---

## 3. Pattern A — Direct Handler

Use when: **one endpoint → one query shape** (no discriminator branching needed).

### Files to create

```
ModuleDAL/DTO/Foo/FooListDTO.cs          ← result DTO + criteria class + query record
ModuleDAL/Query/Foo/FooQB.cs             ← SQL builder method (not const string)
ModuleDAL/CustomCode/Foo/FooHandler.cs   ← IListHandler implementation
ModuleBLL/Foo/IFooBLL.cs                 ← interface
ModuleBLL/Foo/FooBLL.cs                  ← injects IListHandler directly
ModuleSL/EndPoints/Foo/GetFooList.cs     ← FastEndpoint
```

### Criteria class template

```csharp
// ModuleDAL/DTO/Foo/FooListDTO.cs
public sealed class FooCriteria : ICriteria
{
    [CriteriaField("headername", "searchtext")]
    public string? SearchText  { get; set; }

    [CriteriaField("ouid")]
    public int?    OUId        { get; set; }

    [CriteriaField("pagesize")]
    public int     PageSize    { get; set; } = 100;

    [CriteriaField("pageoffset")]
    public int     PageOffset  { get; set; } = 0;

    // Add module-specific fields:
    [CriteriaField("partyid")]
    public int[]?  PartyIds    { get; set; }
}

// Query record — use the typed sub-interface so SqlListHandler can access Criteria directly
public sealed record FooListQuery(FooCriteria Criteria) : IListQuery<FooListDTO, FooCriteria>;

// Result DTO — lean auto-properties for list grid
public sealed class FooListDTO
{
    public int     FooId     { get; set; }
    public string? FooName   { get; set; }
    public int     TotalCount { get; set; }   // COUNT(*) OVER() from window function
}
```

### Query builder template

```csharp
// ModuleDAL/Query/Foo/FooQB.cs
public static class FooQB
{
    public static (string Sql, DynamicParameters Params) BuildFooList(
        FooCriteria c, ISqlDialect d)
    {
        var ctx = new QueryContext(d)
            .Register("header", "h")
            .Register("item",   "i");

        var (whereSql, whereParams) = SqlClause.AsWhere(new[]
        {
            SqlClauses.OUFilterSingle(ctx, c.OUId, "header"),
            SqlClauses.DocumentSearch(ctx, c.SearchText),
        });

        var (pagingSql, pagingParams) = SqlClauses.Paging(ctx, c, orderBy: "h.FOOID DESC");

        var sql = $@"
SELECT {d.TotalCountExpr()},
       h.FOOID   AS FooId,
       h.FOONAME AS FooName
FROM   tfoo h
{whereSql}
{pagingSql}";

        return (sql, SqlClause.Merge(whereParams, pagingParams));
    }
}
```

### Handler template

Handlers subclass `SqlListHandler<TQuery, TCriteria, TResult>` (GB5Shared). All boilerplate
(dialect resolution, `QueryPagedAsync`, `QueryStreamAsync`) lives in the base class — the
subclass provides only `BuildSql`.

```csharp
// ModuleDAL/CustomCode/Foo/FooHandler.cs
public sealed class FooListHandler
    : SqlListHandler<FooListQuery, FooCriteria, FooListDTO>
{
    public FooListHandler(IQueryExecutor qe) : base(qe) { }

    protected override (string Sql, DynamicParameters Params) BuildSql(
        FooCriteria c, ISqlDialect d)
        => FooQB.BuildFooList(c, d);
}
```

Do NOT override `HandleAsync` or `StreamAsync` unless the query has non-standard execution
requirements (multi-step, transaction-scoped, etc.).

### BLL template (Pattern A)

```csharp
// ModuleBLL/Foo/FooBLL.cs
public sealed class FooBLL : IFooBLL
{
    private readonly IListHandler<FooListQuery, FooListDTO> _handler;
    public FooBLL(IListHandler<FooListQuery, FooListDTO> handler) => _handler = handler;

    public async Task<string> GetFooList(
        CriteriaDTO criteria, string? searchText, int pageOffset, int pageSize,
        LoginDTO login, CancellationToken ct = default)
    {
        var merged = CriteriaRouterHelper.WithSearchAndPaging(criteria, searchText, pageOffset, pageSize);
        var c      = CriteriaBinder.Bind<FooCriteria>(merged);
        // HandleAsync returns PagedResult<T> (GB5Shared.QueryExecutor) — serialise directly
        var result = await _handler.HandleAsync(new FooListQuery(c), login, ct).ConfigureAwait(false);
        return JsonConvert.SerializeObject(result);
    }
}
```

---

## 4. Pattern B — Router Dispatch

Use when: **one endpoint → multiple possible query shapes** (legacy fat method with discriminator branching).

### Files to create

```
ModuleDAL/DTO/Foo/                         ← one criteria class + query record per variant
  FooListDTO.cs                            ← shared result DTO
  FooVariantACriteria.cs  (or combined)
  FooVariantBCriteria.cs
ModuleDAL/Query/Foo/FooQB.cs               ← one Build*() method per variant
ModuleDAL/CustomCode/Foo/FooHandlers.cs    ← one IListHandler class per variant
ModuleBLL/Foo/IFooBLL.cs
ModuleBLL/Foo/FooBLL.cs                    ← reads discriminator, dispatches via IListDispatcher
ModuleSL/EndPoints/Foo/GetFooList.cs
```

### BLL template (Pattern B)

```csharp
// ModuleBLL/Foo/FooBLL.cs
public sealed class FooBLL : IFooBLL
{
    private readonly IListDispatcher _dispatcher;
    public FooBLL(IListDispatcher dispatcher) => _dispatcher = dispatcher;

    public async Task<string> GetFooList(
        CriteriaDTO criteria, string? searchText, int pageOffset, int pageSize,
        LoginDTO login, CancellationToken ct = default)
    {
        var merged = CriteriaRouterHelper.WithSearchAndPaging(criteria, searchText, pageOffset, pageSize);
        var flat   = CriteriaRouterHelper.Flatten(merged);
        int typeId = CriteriaRouterHelper.GetInt(flat, "objectheadertypeid");

        // DispatchAsync returns PagedResult<T> (GB5Shared.QueryExecutor)
        PagedResult<FooListDTO> result;
        if (typeId == EntityConstant.OBJECTVARIANTB)
        {
            var c = CriteriaBinder.Bind<FooVariantBCriteria>(merged);
            result = await _dispatcher.DispatchAsync(new FooVariantBQuery(c), login, ct)
                                      .ConfigureAwait(false);
        }
        else
        {
            var c = CriteriaBinder.Bind<FooVariantACriteria>(merged);
            result = await _dispatcher.DispatchAsync(new FooVariantAQuery(c), login, ct)
                                      .ConfigureAwait(false);
        }

        return JsonConvert.SerializeObject(result);
    }
}
```

### Key rule: discriminator fields

Read discriminator fields with `CriteriaRouterHelper.GetInt()` / `GetStr()` — never with raw criteria binding. The flat dictionary is the discriminator source; typed criteria only carry the fields used by the SQL query.

---

## 5. Pilot Example: Allocation / GetPendingAllocations

The allocation module uses Pattern B (4 variants, one endpoint).

| Discriminator | Query type |
|---|---|
| `objectheadertypeid` = -1899997952 (OBJECTMMHEAD) + no `allitemsview` | `PendingMMDocumentsQuery` — header-level aggregate |
| `objectheadertypeid` = -1899997952 + `allitemsview=true` | `PendingMMAllItemsQuery` — detail-level line view |
| `objectheadertypeid` = -1899997569 (OBJECTINDENT) + no `multiprocess` | `PendingIndentsQuery` |
| `objectheadertypeid` = -1899997569 + `multiprocess=true` | `PendingIndentMultiProcessQuery` |

**Files created:**

| File | Layer | Purpose |
|------|-------|---------|
| [MMDAL/DTO/Allocation/PendingAllocationListDTO.cs](GB5Solution/MM/MMDAL/DTO/Allocation/PendingAllocationListDTO.cs) | DAL | Result DTO, 4 criteria classes, 4 query records |
| [MMDAL/Query/Allocation/PendingAllocationQB.cs](GB5Solution/MM/MMDAL/Query/Allocation/PendingAllocationQB.cs) | DAL | SQL builders (QueryContext + SqlClauses + ISqlDialect) |
| [MMDAL/CustomCode/PendingAllocation/PendingAllocationHandlers.cs](GB5Solution/MM/MMDAL/CustomCode/PendingAllocation/PendingAllocationHandlers.cs) | DAL | 4 IListHandler implementations |
| [MMBLL/Allocation/IPendingAllocationBLL.cs](GB5Solution/MM/MMBLL/Allocation/IPendingAllocationBLL.cs) | BLL | Interface |
| [MMBLL/Allocation/PendingAllocationBLL.cs](GB5Solution/MM/MMBLL/Allocation/PendingAllocationBLL.cs) | BLL | Router — reads discriminator, dispatches |
| [MMSL/EndPoints/Allocation/GetPendingAllocations.cs](GB5Solution/MM/MMSL/EndPoints/Allocation/GetPendingAllocations.cs) | SL | FastEndpoint (POST, CriteriaDTO body + optional SearchText param) |

---

## 6. Save Pattern

See also: `CLAUDE.md` → **BLL Save Pattern — ExecuteSaveAsync**.

```
BeginTransaction
  → (if new) GetAutoNumber → assign to DTO
  → ExecuteSaveAsync
      → (inside callback) DAL.Save or DAL.Update
  → CommitTransaction
catch
  → RollbackTransaction + rethrow
```

**Key rules:**
- `BeginTransactionAsync` is called **before** the `isNew` check.
- Auto-number is assigned to the DTO **before** `ExecuteSaveAsync`, not inside the callback.
- The DAL callback receives `tx` and must pass it to every DAL call.
- `CommitAsync` on the happy path only. `RollbackAsync` always in `catch`, always with `throw`.

```csharp
public async Task<string> SaveFoo(FooDTO dto, LoginDTO login)
{
    var Trans = await _QueryExecutor.BeginTransactionAsync(login);
    try
    {
        bool isNew = dto.FooId == 0;
        if (isNew)
        {
            var auto = await _AutoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.FOO, login);
            dto.FooId = auto.StartNumber;
        }

        await _baseEntityAppService.ExecuteSaveAsync(
            EntityConstant.FOO,
            EventTypeConstant.SAVEFOOEVENTTYPEID,
            dto,
            login,
            async tx => isNew
                ? await _FooDAL.SaveFoo(dto, login, tx)
                : await _FooDAL.UpdateFoo(dto, login, tx),
            new Dictionary<string, object?> { { "ParentId", dto.ParentId } },
            BIZTRANSACTIONCLASSCONSTANT.FOO,
            BizTransactionConstant.FOO,
            Trans);

        await _QueryExecutor.CommitAsync(Trans);
        return isNew ? "Details saved successfully." : "Details updated successfully.";
    }
    catch (Exception)
    {
        await _QueryExecutor.RollbackAsync(Trans);
        throw;
    }
}
```

---

## 7. Delete Pattern

```csharp
public async Task<string> DeleteFoo(int fooId, LoginDTO login)
{
    var Trans = await _QueryExecutor.BeginTransactionAsync(login);
    try
    {
        await _baseEntityAppService.ExecuteSaveAsync(
            EntityConstant.FOO,
            EventTypeConstant.DELETEFOOEVENTTYPEID,
            new FooDTO { FooId = fooId },
            login,
            async tx => await _FooDAL.DeleteFoo(fooId, login, tx),
            null,
            BIZTRANSACTIONCLASSCONSTANT.FOO,
            BizTransactionConstant.FOO,
            Trans);

        await _QueryExecutor.CommitAsync(Trans);
        return "Details deleted successfully.";
    }
    catch (Exception)
    {
        await _QueryExecutor.RollbackAsync(Trans);
        throw;
    }
}
```

---

## 8. Multi-DB Support

GB5 targets SQL Server, PostgreSQL, and Oracle simultaneously.
**Two mechanisms work together:**

| Mechanism | What it handles |
|-----------|----------------|
| `IQueryExecutor` auto-converter (`ConvertSqlToPostgres`) | Routine SQL Server → PostgreSQL rewriting (column names, basic syntax) |
| `ISqlDialect` (explicit) | Intentional dialect differences the converter cannot handle reliably |

### When to use ISqlDialect explicitly

- Paging: `ORDER BY x OFFSET @o ROWS FETCH NEXT @s ROWS ONLY` vs `LIMIT @s OFFSET @o`
- Case-insensitive LIKE: `LIKE` vs `ILIKE` (PostgreSQL)
- Null coalescing: `ISNULL(x,0)` vs `NVL(x,0)` vs `COALESCE(x,0)`
- Lateral subquery: `OUTER APPLY (...)` vs `LEFT JOIN LATERAL (...) ON TRUE`
- Window count: `COUNT(*) OVER()` column case differs
- Parameter prefix: `@name` vs `:name` (Oracle)

### How to resolve dialect in a handler

```csharp
// In every handler.HandleAsync() — derive at execution time from LoginDTO
var dialect = SqlDialectFactory.Get(login.DatabaseType);
// login.DatabaseType is byte: 0=SQL, 1=Oracle, 2=PostGre, 3=MySQL(≈SQL)
```

### Passing dialect to the QB

```csharp
var (sql, param) = FooQB.BuildFooList(query.Criteria, dialect);
```

The QB method receives `ISqlDialect d` and calls:
- `d.TotalCountExpr()` — COUNT(*) OVER()
- `d.Param("name")` — `@name` or `:name`
- `d.LikeCaseOp` — `LIKE` or `ILIKE`
- `d.IsNull(expr, fallback)` — `ISNULL` / `NVL` / `COALESCE`
- `ctx.Dialect.Paging(orderBy, "pgOffset", "pgSize")` via `SqlClauses.Paging()`

---

## 9. Schema-Qualified Tables

New tables in module-specific schemas (e.g. `mm.tmtrrecord`) are registered with a schema.
Existing tables remain unqualified (default `dbo`).

```csharp
var ctx = new QueryContext(d)
    .Register("allocation", "a")                        // no schema → "tallocation"
    .Register("mtrrecord",  "r", schema: "mm")          // → "mm.tmtrrecord"
    .Register("mtrdetail",  "d", schema: "mm");         // → "mm.tmtrdetail"

// In FROM/JOIN clause:
$"FROM {ctx.Table("mtrrecord", "tmtrrecord")} r"        // → "FROM mm.tmtrrecord r"
$"FROM {ctx.Table("allocation", "tallocation")} a"      // → "FROM tallocation a"

// Column qualification (alias is unchanged):
ctx.Col("mtrrecord", "MTRRECORDID")                     // → "r.MTRRECORDID"
```

**Rule:** Register every table at the top of the QB method. Never hardcode alias prefixes (`"a."`) inside clause strings.

---

## 10. Dual Search Text

Support search text from both:
- **Body CriteriaDTO** (legacy clients): embedded as `headername` or `searchtext` field inside a section
- **Query param** (new clients): `?SearchText=...` passed to endpoint

### How it works

`CriteriaRouterHelper.WithSearchAndPaging()` merges them:
- `pageoffset` and `pagesize` are **always** injected (override body defaults)
- `headername` (search text) is **only injected when `searchTextParam` is non-null** — leaving the body value intact when no query param was supplied

```csharp
// In BLL
var merged = CriteriaRouterHelper.WithSearchAndPaging(criteria, searchText, pageOffset, pageSize);
```

### Endpoint signature

```csharp
public record Params(
    [property: FromHeader] string Login,
    [property: FromBody]   CriteriaDTO CriteriaDTO,
    [property: QueryParam] string? SearchText = null,   // new clients
    [property: QueryParam] int     PageOffset = 0,
    [property: QueryParam] int     PageSize   = 100
);
```

Legacy clients that POST criteria without the query params continue to work unchanged.

---

## 11. Caching

### Read (list) endpoints — MUST implement GetCacheKey

All read endpoints must override `GetCacheKey`. `BaseEndpoint` automatically checks the Dapr statestore using the returned key — no caching code needed inside BLL or DAL.

```csharp
// List endpoint — CriteriaDTO overload
protected override string? GetCacheKey(Params req, LoginDTO login)
    => KeyGenerator.KeyGeneration(
           req.CriteriaDTO,
           req.PageOffset,
           CacheKeyLevel.CLIENT_LEVEL,
           login);

// Single-record GET endpoint — objectId overload
protected override string? GetCacheKey(GetFooParams req, LoginDTO login)
    => KeyGenerator.KeyGeneration(
           req.FooId,
           EntityConstant.OBJECTFOO,
           CacheKeyLevel.CLIENT_LEVEL,
           login);
```

Choose the right level:

| Data type | Level |
|-----------|-------|
| Shared across tenant | `CLIENT_LEVEL` |
| Varies by user/role | `USER_LEVEL` / `ROLE_LEVEL` |
| Highly transactional (stock, ledger) | `NOT_REQUIRED` (return null) |
| Global config | `OVERALL` |

### Write (save/delete) endpoints

Do NOT override `GetCacheKey` — the default returns `null` and BaseEndpoint skips cache.

```csharp
// No GetCacheKey override on Save/Delete endpoints — default null is correct
```

### Cache invalidation after write

BLL calls `KeyInvalidate.AllInvalidateCache(key)` **after** `CommitAsync`. The key must be built using the same `KeyGenerator.KeyGeneration()` call as the read endpoint uses.

```csharp
// In BLL SaveFoo — after CommitAsync
await _QueryExecutor.CommitAsync(Trans);
var cacheKey = KeyGenerator.KeyGeneration(
    dto.FooId, EntityConstant.OBJECTFOO, CacheKeyLevel.CLIENT_LEVEL, login);
await _keyInvalidate.AllInvalidateCache(cacheKey);
```

---

## 12. DI Registration Checklist

The Scrutor assembly scan in each module's `Program.cs` auto-registers:
- All `IListHandler<TQ,T>` implementations in MMDAL (non-abstract, non-interface classes)
- All BLL and DAL classes

**One manual registration is required** — `IListDispatcher` lives in `GB5Shared`, outside the scan:

```csharp
// In ModuleSL/Program.cs — add after the Scrutor scan
builder.Services.AddListDispatcher();
```

This registers `ListDispatcher` as `IListDispatcher` (Scoped).

---

## 13. Anti-Patterns — Do Not Carry Forward

| Anti-pattern from legacy | Why wrong | GB5 replacement |
|--------------------------|-----------|-----------------|
| `for i / for j` loop over `SectionCriteriaList` | O(n²), verbose, fragile | `CriteriaBinder.Bind<TCriteria>(dto)` |
| Magic alias strings `"a."`, `"b."` inside helper methods | Alias change breaks all clauses | `ctx.Col("table","COLUMN")` via `QueryContext` |
| One giant `const string` SQL with `+` concatenation for filters | SQL injection risk; hard to test | `SqlClause.Combine()` + `SqlClauses.*` |
| `IDbConnectionFactory.DbType` for dialect | GB5 uses `LoginDTO.DatabaseType` | `SqlDialectFactory.Get(login.DatabaseType)` |
| Custom `IGenericListCache<T>` / Redis wrapper | Parallel cache stack; dual invalidation | `BaseEndpoint` Dapr cache via `KeyGenerator` |
| `[ApiController]` / MVC Controller | Not used in GB5 | `BaseEndpoint<TReq,TRes>` (FastEndpoints) |
| `GenericListRepository` | Duplicates `IQueryExecutor` | Delete; inject `IQueryExecutor` directly |
| Returning `PagedResult<T>` from endpoints | SL must return `ResponseStandardDTO<object>` | BLL returns `JsonConvert.SerializeObject(result)`; SL wraps with `Response.CreateSuccessResponse` |
| Deriving dialect inside QB (static method) | Dialect must come from login at runtime | Pass `ISqlDialect` as parameter into QB method |
| Methods named `GetFooNew`, `GetFooFast`, `GetFooLatest`, `GetFooV2` | Parallel copies diverge silently; no one removes the old one | One method; variants are named `IListQuery` records dispatched through the router |
| Commented-out code blocks in production files | Dead weight; confuses the reader; "just in case" is what git is for | Delete; commit message explains why |
| Duplicate SQL filter logic copied across QB files | One bug fix must be applied everywhere | Extract to `SqlClauses.*` static method when the same filter appears in 2+ QB files |

---

## 14. Code Hygiene — Migration Clean-Up Rules

During migration, **do not carry legacy noise forward**. Apply these rules to every file touched.

### Rule 1 — Delete all commented-out code

```csharp
// WRONG — commented code blocks left in files
//public string GetAllocation_New(int id, LoginDTO login)
//{
//    // TODO: replace old version
//    ...
//}

// CORRECT — delete entirely; the old implementation is in git history
// Commit message: "Remove GetAllocation_New — superseded by IListHandler pattern"
```

### Rule 2 — No suffix variants (New, Fast, Latest, V2, etc.)

When legacy code has:
```csharp
public string GetPendingAllocation(...)      { /* 200 lines */ }
public string GetPendingAllocationNew(...)   { /* copy with minor tweak */ }
public string GetPendingAllocationFast(...)  { /* another copy */ }
```

In GB5 this becomes **one** BLL method + **one or more named query records**:
```csharp
// One BLL method, routing by discriminator
public Task<string> GetPendingAllocations(CriteriaDTO c, ...) { ... }

// Named variants are query records — the name is in the type, not the method
public sealed record PendingMMDocumentsQuery(PendingMMDocumentCriteria Criteria) : IListQuery<PendingAllocationListDTO>;
public sealed record PendingMMAllItemsQuery(PendingMMAllItemsCriteria  Criteria) : IListQuery<PendingAllocationListDTO>;
```

Callers choose the variant through the `CriteriaDTO` discriminator field — not through a different method name.

### Rule 3 — Consolidate duplicate SQL clauses

If the same WHERE filter logic appears in two or more QB files, extract it to `SqlClauses`:

```csharp
// WRONG — same filter copy-pasted in AllocationQB.cs and IndentQB.cs
// AllocationQB.cs:
$"a.OUID = @ouId OR d.INTEROUID = @ouId"
// IndentQB.cs:
$"ih.OUID = @ouId OR id.INTEROUID = @ouId"

// CORRECT — one method in SqlClauses.cs, used everywhere:
public static SqlClause OUFilter(QueryContext ctx, int? ouId) { ... }
```

### Rule 4 — Consolidate duplicate DTO properties

If two result DTOs have identical column sets (same query, different code paths), use one shared DTO. If they differ by 1–2 columns, add nullable fields with a comment rather than creating a second type.

### Rule 5 — No dead `using` statements or empty regions

Migration tools often leave `#region`/`#endregion` blocks and stale `using` directives. Remove both during migration.

---

## 15. Cross-Entity Dependency — Use Existing BLL, Not Copy-Paste DAL

### The problem

In legacy code, entity Y often needs data owned by entity X. Instead of calling X's service, a new DAL method is written inside Y's class — duplicating X's query with minor differences. Over time X's logic changes but Y's copy does not.

```csharp
// WRONG — YyyDAL re-implements a query that XxxDAL already owns
public class YyyDAL : IYyyDAL
{
    public async Task<List<XxxDTO>> GetXxxForYyy(int yyyId, LoginDTO login)
    {
        // 40-line copy of XxxDAL.GetXxxList with a minor extra filter
        string sql = "SELECT ... FROM txxx WHERE yyyId = @yyyId ...";
        return await _qe.QueryAsync<XxxDTO>(login, sql, new { yyyId });
    }
}
```

### The rule

**BLL calls BLL. DAL calls DAL only for the entity it owns.**

```csharp
// CORRECT — YyyBLL injects IXxxBLL and calls it
public class YyyBLL : IYyyBLL
{
    private readonly IYyyDAL _yyyDal;
    private readonly IXxxBLL _xxxBll;   // ← inject, not re-implement

    public YyyBLL(IYyyDAL yyyDal, IXxxBLL xxxBll)
    {
        _yyyDal = yyyDal;
        _xxxBll = xxxBll;
    }

    public async Task<string> GetYyyWithXxx(int yyyId, LoginDTO login, CancellationToken ct)
    {
        var yyy = await _yyyDal.GetYyy(yyyId, login, ct);
        var xxx = await _xxxBll.GetXxxList(yyy.XxxId, login, ct);   // reuse existing method
        // ... combine and return
    }
}
```

### When a JOIN is more efficient

If Y needs one or two columns from X in a list query (N+1 risk), JOIN in the QB instead of calling X's BLL in a loop:

```csharp
// CORRECT — JOIN at the QB level when Y needs X columns in a list
var sql = $@"
SELECT y.YYYID, y.YYYNAME, x.XXXCODE
FROM   tyyy y
LEFT JOIN txxx x ON x.XXXID = y.XXXID
{whereSql}
{pagingSql}";
```

The DTO then carries the columns from both tables. **No second round-trip, no N+1.**

### Decision rule

| Scenario | Approach |
|----------|----------|
| Y list needs 1–3 columns from X | JOIN in QB; include columns in result DTO |
| Y's business logic depends on X's full object | Inject `IXxxBLL` into `YyyBLL`; call existing method |
| Y needs X's data only in save validation | Inject `IXxxBLL`; call in BLL before `ExecuteSaveAsync` |
| Y duplicates X's query with minor filter tweak | Extract shared filter to `SqlClauses`; share the QB method |

---

## 16. Response Formats — JSON, NDJSON Stream, Excel, PDF

The current standard is **serialized JSON string** (BLL returns `JsonConvert.SerializeObject(result)`, SL wraps in `ResponseStandardDTO<object>`). This is correct for most endpoints but not optimal for all scenarios.

Choose the response format based on the use case:

| Use case | Format | How |
|----------|--------|-----|
| Standard grid / list | Serialized JSON (default) | `JsonConvert.SerializeObject(PagedListResult<T>)` in BLL |
| Large export (>5k rows) | NDJSON stream | Separate `Stream*` endpoint using `IListDispatcher.StreamAsync` |
| Excel export | `.xlsx` binary | Separate `Export*` endpoint using ClosedXML; stream `MemoryStream` |
| PDF report | `.pdf` binary | Separate `Export*` endpoint using iText7/QuestPDF; stream `MemoryStream` |
| Single-record detail | Serialized JSON (default) | `JsonConvert.SerializeObject(dto)` in BLL |

### 16a — Standard (serialized JSON) — default for all list/detail endpoints

```csharp
// BLL
public async Task<string> GetFooList(...) 
{
    var result = await _handler.HandleAsync(new FooListQuery(c), login, ct);
    return JsonConvert.SerializeObject(result);   // PagedListResult<FooListDTO>
}

// SL endpoint
protected override async Task<ResponseStandardDTO<object>> ExecuteAsync(Params req, LoginDTO login, CancellationToken ct)
{
    var result = await _bll.GetFooList(req.CriteriaDTO, req.SearchText, req.PageOffset, req.PageSize, login, ct);
    return await GB5Shared.ResponseStandard.Response.CreateSuccessResponse(result, CacheKeyLevel.CLIENT_LEVEL, login);
}
```

### 16b — NDJSON streaming — for large datasets (>5k rows)

`IListHandler.StreamAsync` returns `IAsyncEnumerable<T>`. Wire a **separate** streaming endpoint — do not add streaming to the same route as the paged endpoint.

```csharp
// SL — streaming endpoint (separate route, no cache key — streaming is never cached)
public class StreamFooList : BaseEndpoint<StreamFooList.Params, IAsyncEnumerable<FooListDTO>>
{
    private readonly IFooBLL _bll;
    public StreamFooList(IFooBLL bll) => _bll = bll;

    public override void Configure()
    {
        Post("/Foo/StreamFooList");
        AllowAnonymous();
    }

    public record Params(
        [property: FromHeader] string Login,
        [property: FromBody]   CriteriaDTO CriteriaDTO
    );

    protected override string? GetCacheKey(Params req, LoginDTO login) => null; // never cache streams

    protected override async Task<IAsyncEnumerable<FooListDTO>> ExecuteAsync(
        Params req, LoginDTO login, CancellationToken ct)
    {
        // BaseEndpoint serializes IAsyncEnumerable as NDJSON automatically
        return _bll.StreamFooList(req.CriteriaDTO, login, ct);
    }
}

// BLL — streaming variant
// Use QueryStreamAsync (supports CancellationToken). StreamAsync does not accept a CancellationToken.
public IAsyncEnumerable<FooListDTO> StreamFooList(
    CriteriaDTO criteria, LoginDTO login, CancellationToken ct)
{
    var dialect  = SqlDialectFactory.Get(login.DatabaseType);
    var c        = CriteriaBinder.Bind<FooCriteria>(criteria);
    var (sql, p) = FooQB.BuildFooList(c, dialect);
    return _qe.QueryStreamAsync<FooListDTO>(login, sql, p, ct);
}
```

Rules for streaming endpoints:
- Route name: `Stream{Entity}` — never reuse the `Get{Entity}` route
- No `GetCacheKey` — return `null`; streams are never cached
- No paging parameters — the stream is unbounded; the caller reads until done
- Client aborts via `CancellationToken`; always propagate it

### 16c — Excel export

Use ClosedXML. Always stream the `MemoryStream` — never hold it in a field.

```csharp
// SL — Excel export endpoint
public class ExportFooListExcel : BaseEndpoint<ExportFooListExcel.Params, IResult>
{
    private readonly IFooBLL _bll;
    public ExportFooListExcel(IFooBLL bll) => _bll = bll;

    public override void Configure()
    {
        Post("/Foo/ExportFooListExcel");
        AllowAnonymous();
    }

    public record Params(
        [property: FromHeader] string Login,
        [property: FromBody]   CriteriaDTO CriteriaDTO
    );

    protected override string? GetCacheKey(Params req, LoginDTO login) => null;

    protected override async Task<IResult> ExecuteAsync(Params req, LoginDTO login, CancellationToken ct)
    {
        using var ms = new MemoryStream();
        await _bll.ExportFooListExcel(req.CriteriaDTO, login, ms, ct);
        ms.Position = 0;
        return Results.File(ms.ToArray(),
            contentType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            fileDownloadName: $"FooList_{DateTime.UtcNow:yyyyMMdd}.xlsx");
    }
}

// BLL — builds Excel from IAsyncEnumerable stream
public async Task ExportFooListExcel(
    CriteriaDTO criteria, LoginDTO login, Stream output, CancellationToken ct)
{
    using var wb = new XLWorkbook();
    var ws = wb.Worksheets.Add("Foo List");

    // Write header row
    ws.Cell(1, 1).Value = "Foo ID";
    ws.Cell(1, 2).Value = "Foo Name";
    int row = 2;

    await foreach (var dto in StreamFooList(criteria, login, ct).ConfigureAwait(false))
    {
        ws.Cell(row, 1).Value = dto.FooId;
        ws.Cell(row, 2).Value = dto.FooName;
        row++;
    }

    wb.SaveAs(output);
}
```

**Key rules for Excel export:**
- Call the streaming BLL method — never load all rows into a `List<T>` first
- `MemoryStream` is `using` — disposed after the response bytes are copied
- Route name: `Export{Entity}Excel` — never reuse the list route
- No cache key — binary exports are never cached

### 16d — PDF export

Pattern mirrors Excel. Use iText7 or QuestPDF. Stream output; never hold in memory.

```csharp
// BLL sketch — same IAsyncEnumerable stream, written to PDF
public async Task ExportFooListPdf(
    CriteriaDTO criteria, LoginDTO login, Stream output, CancellationToken ct)
{
    // QuestPDF example
    var rows = new List<FooListDTO>();
    await foreach (var dto in StreamFooList(criteria, login, ct).ConfigureAwait(false))
        rows.Add(dto);  // QuestPDF needs full list to paginate; only acceptable for PDF

    Document.Create(container =>
    {
        container.Page(page =>
        {
            page.Content().Table(table =>
            {
                table.ColumnsDefinition(c => { c.RelativeColumn(); c.RelativeColumn(); });
                foreach (var r in rows)
                {
                    table.Cell().Text(r.FooId.ToString());
                    table.Cell().Text(r.FooName ?? "");
                }
            });
        });
    }).GeneratePdf(output);
}
```

> **Note:** PDF generation requires all rows in memory for pagination layout. Acceptable for reports; document the row-count limit in the QB comment.

### Summary — which format to use

```
Is this a standard list grid used by the FE?          → Serialized JSON (16a)  [default]
Is this a data export where the user downloads rows?  → Excel (16c)
Is this a formatted printable report?                 → PDF (16d)
Does the grid need >5k rows streamed live to client?  → NDJSON stream (16b)
```

---

## 17. File Naming & Location Reference

```
ModuleDAL/
  DTO/
    Foo/
      FooListDTO.cs          ← result DTO + criteria class(es) + query record(s)
      FooDTO.cs              ← save/edit DTO (existing pattern, unchanged)
  Query/
    Foo/
      FooQB.cs               ← static Build*() methods using QueryContext + SqlClauses
  CustomCode/
    Foo/
      FooHandlers.cs         ← IListHandler<TQ,T> implementations
      FooDAL.cs              ← existing DAL for single-record gets, saves (unchanged)

ModuleBLL/
  Foo/
    IFooBLL.cs
    FooBLL.cs                ← Pattern A: inject IListHandler; Pattern B: inject IListDispatcher

ModuleSL/
  EndPoints/
    Foo/
      GetFooList.cs          ← POST, CriteriaDTO body + optional search/paging params
      GetFoo.cs              ← GET, QueryParam id (single record, existing pattern)
      SaveFoo.cs             ← POST, saves
      DeleteFoo.cs           ← DELETE
```

### Namespace conventions

```
MMDAL.DTO.Allocation           ← DTOs (result + criteria + query records)
MMDAL.Query.Allocation         ← QB methods
MMDAL.CustomCode.PendingAllocation  ← handlers
MMBLL.Allocation               ← BLL interfaces + implementations
MMSL.EndPoints.Allocation      ← endpoints
```

---

## 18. Verification Checklist

After each module migration:

**Functional correctness**
- [ ] List endpoint returns same rows as legacy for identical criteria body
- [ ] Paging: `PageOffset=0&PageSize=10` returns exactly 10 rows; `TotalCount` reflects unpaged count
- [ ] Search text via query param: `?SearchText=ABC` filters correctly
- [ ] Search text via body: legacy client body `headername` field also filters correctly when no query param
- [ ] Discriminator routing: each variant of the endpoint produces the expected SQL (log SQL in dev)

**Caching**
- [ ] Cache: second identical request returns from Dapr (no DB hit visible in logs)
- [ ] Cache invalidation: after a save, next read hits DB again

**Multi-DB**
- [ ] Schema-qualified tables: new `mm.` tables resolve correctly in query text
- [ ] PostgreSQL: same endpoint against PG connection uses `ILIKE` and `LIMIT/OFFSET`
- [ ] Oracle: same endpoint uses `NVL`, `:paramName`, and `OFFSET/FETCH`

**Save / Delete**
- [ ] Save: `ExecuteSaveAsync` fires audit event; auto-number assigned correctly; transaction committed
- [ ] Save error: transaction rolls back; no partial data persisted

**Security & hygiene**
- [ ] Multi-tenant: queries always filter by `DatabaseName` / `ClientId`
- [ ] No stack traces returned to client on error (only generic message)
- [ ] No commented-out code blocks remain in migrated files
- [ ] No suffix-variant method names (New, Fast, Latest, V2) remain
- [ ] Entity Y's BLL injects entity X's BLL where X's data is needed — no duplicated DAL queries
- [ ] Any SQL filter shared with another QB file has been extracted to `SqlClauses`

**Response format**
- [ ] Standard list/detail uses serialized JSON (§16a)
- [ ] Large export endpoint (if applicable) uses streaming NDJSON (§16b) or Excel/PDF (§16c/d)
- [ ] Export endpoints have no `GetCacheKey` (return `null`)

**List Query Infrastructure (§19)**
- [ ] Criteria class implements `IListCriteria` (not bare `ICriteria`)
- [ ] Sort fields declared with `[CriteriaField("sortby")]` etc.
- [ ] QB class implements `IQueryBuilder<TQuery, TResult>` with `_sort` allowlist
- [ ] `SqlClauses.OrderBy()` called in `Build()` — never raw sort concatenation
- [ ] No handler file created — `AddListInfrastructure()` wires `GenericListHandler` automatically
- [ ] If mandatory filters exist: `IQueryGuard<TQuery>` class added in DAL project

---

## §19 — List Query Infrastructure

### New query = QB class only

```
IListCriteria (criteria)  +  IListQuery (record)  +  IQueryBuilder (QB class)
→ DI scan at startup → GenericListHandler auto-registered
→ Zero handler files
```

### Criteria template (IListCriteria)

```csharp
public sealed class FooCriteria : IListCriteria
{
    [CriteriaField("headername", "searchtext")]
    public string? SearchText  { get; set; }
    [CriteriaField("ouid")]
    public int?    OUId        { get; set; }
    [CriteriaField("pagesize")]
    public int     PageSize    { get; set; } = 100;
    [CriteriaField("pageoffset")]
    public int     PageOffset  { get; set; } = 0;

    // ISortable
    [CriteriaField("sortby")]
    public string?   SortBy     { get; set; }
    [CriteriaField("sortdesc", IsFlag = true)]
    public bool      SortDesc   { get; set; }
    [CriteriaField("sortfields")]
    public string[]? SortFields { get; set; }

    // IPageable
    [CriteriaField("cursorid")]
    public int?  CursorId  { get; set; }
    [CriteriaField("skipcount", IsFlag = true)]
    public bool  SkipCount { get; set; }
}
```

### Query record template

```csharp
[QuerySource(QueryIntent.Reporting)]   // optional — omit for Transactional (default)
public sealed record FooListQuery(FooCriteria Criteria)
    : IListQuery<FooListDTO, FooCriteria>;
```

### IQueryBuilder template

```csharp
public sealed class FooListQB : IQueryBuilder<FooListQuery, FooListDTO>
{
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["headerdate"]  = "h.FOOHEADERDATE",
            ["name"]        = "h.FOONAME",
        };

    public (string Sql, DynamicParameters Params) Build(
        FooListQuery query, ISqlDialect dialect)
    {
        var orderBy = SqlClauses.OrderBy(
            query.Criteria.SortBy, query.Criteria.SortDesc,
            _sort, defaultCol: "h.FOOID DESC");
        return FooQB.BuildFooList(query.Criteria, dialect, orderBy);
    }
}
```

### DI wiring (Program.cs)

```csharp
// One call, replaces AddListDispatcher()
builder.Services.AddListInfrastructure(fooDALAssembly);
```

### Static QB method signature convention

```csharp
// Accept orderBy as optional string so callers without sort still work
public static (string Sql, DynamicParameters Params) BuildFooList(
    FooCriteria c, ISqlDialect d, string orderBy = "h.FOOID DESC")
{
    // ...
    var (pagingSql, pagingParams) = SqlClauses.Paging(ctx, c, orderBy: orderBy);
    // ...
}
```

---

## §20 — QueryGuard — Mandatory Filter Enforcement

Use when a query must require specific criteria before SQL is built (e.g. date range, OU filter for heavy queries).

```csharp
public sealed class RequiresDateRange : IQueryGuard<FooListQuery>
{
    public void Validate(FooListQuery query, LoginDTO login)
    {
        if (query.Criteria.DateFrom is null || query.Criteria.DateTo is null)
            throw new ArgumentException("DateFrom and DateTo are required.");
        if (query.Criteria.DateTo - query.Criteria.DateFrom > TimeSpan.FromDays(366))
            throw new ArgumentException("Date range cannot exceed 1 year.");
    }
}
```

Rules:
- Place in the module's DAL project — `AddListInfrastructure()` scans and registers automatically.
- Guards are **sync** — no DB calls, no async, no side effects.
- Throw `ArgumentException` (or a domain exception) to reject. GenericListHandler lets it propagate.
- Multiple guards per query type are allowed. All run in order.
- Empty (no guard registered) = permissive. Default for simple queries.

---

## §21 — Sort & Pagination

### Sort

Always use `SqlClauses.OrderBy()` — never concatenate client input into ORDER BY.

```csharp
// Single sort
var orderBy = SqlClauses.OrderBy(criteria.SortBy, criteria.SortDesc, _sort, "h.FOOID DESC");

// Compound sort (["headerdate:desc","name:asc"])
var orderBy = SqlClauses.MultiOrderBy(criteria.SortFields, _sort, "h.FOOID DESC");
```

`_sort` is a `Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)` — logical name → safe SQL column expression. Unknown keys fall back to `defaultCol`.

### Pagination

**Offset paging (default):** `PageOffset` + `PageSize` embedded in SQL via `SqlClauses.Paging()`. `TotalCount` from `COUNT(*) OVER()`.

**Keyset paging (infinite-scroll only):**
- Only use when sort is on a stable PK column.
- Set `CursorId` to the last-seen PK value. Add `WHERE h.FOOID > @cursorId` before paging.
- Set `SkipCount = true` to omit `COUNT(*) OVER()` for speed.
- Never use keyset for "jump to page N" UIs — it doesn't support arbitrary page jumps.

---

## §22 — Critical Analysis Culture

Every developer on this migration must apply critical thinking to requirements, designs, and code — including suggestions from Claude.

### What to push back on

| Pattern | Why wrong |
|---------|-----------|
| Migrating a legacy fat method unchanged | The goal is refactoring, not porting bugs |
| Duplicating infrastructure that already exists in GB5Shared | "We already have X" is the correct answer |
| Designs that don't account for GB5's abstractions | `IQueryExecutor`, `BaseEndpoint`, `CriteriaBinder` exist — use them |
| Speculative abstractions for unconfirmed future needs | Build for today, extend when needed |
| `ConcurrentDictionary<int, SemaphoreSlim>` per-tenant throttle | Unbounded memory leak — use sliding-window counter with TTL |
| Global query queue at the application layer | Wrong layer — holds HTTP connections open; use DB-level connection pool limits |
| Sort columns from client input concatenated into SQL | SQL injection — always validate through `SqlClauses.OrderBy()` allowlist |

### How to flag it

"This introduces [specific problem] because [specific reason]. The correct approach is [alternative]."
Not: vague concern, refusal without alternative, or silent compliance with a bad design.

### Non-negotiables

- No pattern from a sample or suggestion gets implemented without being verified against GB5's existing abstractions.
- Truth over comfort. A wrong design corrected early costs far less than one fixed in production.
- No cowpath paving: a bad GB4 pattern migrated faithfully to GB5 is still a bad pattern.

---

## §23 — TENANTID — Mandatory in All GET Queries

Every GET SQL query (single record and list) must include `TENANTID` in both:
1. **WHERE clause** — `AND t.TENANTID = @TenantId`
2. **SELECT list** — `t.TENANTID AS TenantId`

`@TenantId` is provided automatically by `IQueryExecutor` from `loginDTO.TenantId` — no manual assignment in DAL.

```csharp
// CORRECT
public const string GET_FOO = @"
SELECT f.FOOID     AS FooId,
       f.FOONAME   AS FooName,
       f.TENANTID  AS TenantId     -- always select TENANTID
FROM   TFOO f
WHERE  f.FOOID    = @FooId
AND    f.TENANTID = @TenantId";    -- mandatory: prevents cross-tenant data leak

// WRONG — security violation
public const string GET_FOO_BAD = @"
SELECT f.FOOID, f.FOONAME
FROM   TFOO f
WHERE  f.FOOID = @FooId";         -- missing TENANTID filter!
```

This applies to:
- Single-record GET queries
- All paged list builders (BuildFooList)
- Pick-list / select-list queries
- Any subquery or CTE that reads from a tenant-scoped table

**Verification checklist item:** Every QB file must be reviewed for TENANTID presence before marking migration complete.

---

## §24 — What NOT to Add in BLL/DAL

`BaseEndpoint` handles these cross-cutting concerns automatically. Adding them in BLL/DAL creates duplication, noise, and incorrect behavior.

| DO NOT add in BLL or DAL | Why |
|--------------------------|-----|
| `ActivitySource.StartActivity(...)` OTel spans | `BaseEndpoint.HandleAsync` starts a root span for every request automatically |
| `Logger.LogInformation(...)` per-request entry logs | `BaseEndpoint` logs request received and response |
| Direct `DaprClient.PublishEventAsync(...)` for audit events | Use `IOutBox` via `ExecuteSaveAsync` — ensures transactional delivery |
| Try/catch blocks that just rethrow (`catch (Exception) { throw; }`) | No-op — remove them; exceptions propagate naturally |
| `ConfigureAwait(false)` in BLL methods called from endpoints | ASP.NET Core has no `SynchronizationContext`; `ConfigureAwait(false)` is a no-op. Only needed in true library code. |
| Setting audit fields (`CreatedById/On`, `ModifiedById/On`) in BLL | Set these inside DAL `Save`/`Update` — same place that executes the SQL |

---

## §25 — Qualifier Executors — Per-Entity Validation

The qualifier engine runs custom validation and business rules through `ExecuteSaveAsync` automatically. Per the RoleBLL pattern, create executor instances inline in the BLL's Save method.

### Executor pattern

```csharp
// In *DAL or *BLL project — two executors per entity minimum:
// 1. PreValidate — field required / format checks (runs before workflow check)
// 2. PrePersist — business rule checks (runs before DB write)

public class FooNameValidatorExecutor : IQualifierExecutor
{
    public string ExecutorKey => "FooNameValidator";

    public Task<QualifierResult> ExecuteAsync(object dto, LoginDTO login)
    {
        var foo = (FooDTO)dto;
        if (string.IsNullOrWhiteSpace(foo.FooName))
            return Task.FromResult(QualifierResult.Fail("Foo Name is required."));
        return Task.FromResult(QualifierResult.Ok());
    }
}

public class FooBusinessRuleExecutor : IQualifierExecutor
{
    public string ExecutorKey => "FooBusinessRule";

    public Task<QualifierResult> ExecuteAsync(object dto, LoginDTO login)
    {
        // entity-specific business rules
        return Task.FromResult(QualifierResult.Ok());
    }
}
```

### Wiring in BLL SaveFoo

```csharp
var definitions = new List<QualifierDefinitionDTO>
{
    new() { ExecutorKey = "FooNameValidator", IsActive = true,
            Stage = QualifierStage.PreValidate, QualifierType = QualifierType.Validate,
            EntityId = EntityConstant.OBJECTFOO, Priority = 1 },
    new() { ExecutorKey = "FooBusinessRule",  IsActive = true,
            Stage = QualifierStage.PrePersist,  QualifierType = QualifierType.BusinessRule,
            EntityId = EntityConstant.OBJECTFOO, Priority = 2 },
};
var executors = new List<IQualifierExecutor>
{
    new FooNameValidatorExecutor(),
    new FooBusinessRuleExecutor(),
};
var engine  = new QualifierEngine(definitions, executors);
var facade  = new QualifierFacade(engine);
var baseSvc = new BaseEntityAppService<FooDTO>(facade, _IWorkFlowEngine, _outBox, _queryExecutor);

await baseSvc.ExecuteSaveAsync(
    EntityConstant.OBJECTFOO,
    EventTypeConstant.SAVEFOOEVENTTYPEID,
    dto, login,
    async tx => isNew
        ? await _FooDAL.SaveFoo(dto, login, tx)
        : await _FooDAL.UpdateFoo(dto, login, tx),
    null, -1, -1, Trans);
```

---

## §26 — Internationalization (i18n) — Mandatory for All Migrated BLL/DAL

### Why This Matters

GB4 WCF services return raw English strings. Copying these strings into GB5 BLL/DAL makes it
impossible to centrally update messages or support multiple languages. GB5 already has a
strongly-typed resource infrastructure — use it.

### The Rule

Every BLL (and DAL, when it returns an operation message) that returns a success string **must**
use `GB5Shared.Resource.Response.SuccessResponse` properties, never hardcoded English.

### Required Using Statement

```csharp
using GB5Shared.Resource.Response;
```

Add this to every BLL file that returns a success/delete message.

### Mandatory Replacement Table

| GB4 / hardcoded pattern (FORBIDDEN in GB5) | GB5 resource replacement |
|--------------------------------------------|--------------------------|
| `"Details saved successfully."` | `SuccessResponse.SaveSuccess` |
| `"Details updated successfully."` | `SuccessResponse.UpdateSuccess` |
| `"Details deleted successfully."` | `SuccessResponse.DeleteSuccessMessage` |
| `"Details Saved Successfully"` (DAL TVP return) | `SuccessResponse.SaveSuccess` |
| Any other custom success text | Add a new key to `SuccessResponse.resx` |

### Correct Pattern

```csharp
// CORRECT
using GB5Shared.Resource.Response;
// ...
return isNew ? SuccessResponse.SaveSuccess : SuccessResponse.UpdateSuccess;
return SuccessResponse.DeleteSuccessMessage;

// WRONG — do not copy from GB4
return isNew ? "Details saved successfully." : "Details updated successfully.";
return "Details deleted successfully.";
```

### Validation Messages

Field-level validation messages (e.g., `"Account code is required."`) have no resource equivalents
in `ErrorResponse` yet. These may remain as hardcoded strings for now — but they must be raised
as `ArgumentException` or a typed validation exception, never returned as plain strings.

### SL Endpoint ex.Message Forwarding

All SL endpoint catch blocks pass `ex.Message` to `CreateExceptionError`. This is a known
systemic pattern consistent across all GB5 modules — do not change it during migration.

### Adding New Message Keys

When your operation needs a message not in the table above:
1. Add a `<data>` element to `GB5Shared/Resource/Response/SuccessResponse.resx`
2. Add the corresponding `public static string` property to `SuccessResponse.Designer.cs`
   following the exact auto-generated pattern already in that file

### Reference Implementation

`GB5Solution/Costing/CostingBLL/CostAnalysis/CostAnalysisBLL.cs` — uses `SuccessResponse.SaveSuccessMessage`.

---

## §23 — Layer Return Type Contract & Codegen Checklist

> **Purpose:** Code generation (human or AI) must pass every item in this checklist.
> Manual review should only be needed for business logic correctness — not for layer standards violations.

### Canonical Reference Files

| Layer | File | What it demonstrates |
|-------|------|---------------------|
| DAL | `GB5Solution/MM/MMDAL/CustomCode/SupplyGroup/SupplyGroupDAL.cs` | Typed reads, `Result<string>` writes, `DbTransaction tx`, audit in DAL, `ConfigureAwait` |
| BLL | `GB5Solution/MM/MMBLL/SupplyGroup/SupplyGroupBLL.cs` | All 5 injections, `ExecuteSaveAsync`, `KeyInvalidate`, typed read pass-throughs |
| SL | `GB5Framework/FrameworkSL/Endpoints/GOP/TargetOperation/GetGopTargetOperation.cs` | No try/catch, `CreateSuccessResponse(typedDto)` |

---

### DAL Checklist

- [ ] Read methods return **typed DTOs** — `Task<FooDTO?>`, `Task<IEnumerable<FooDTO>>`, or `Task<PagedResult<FooDTO>>`
- [ ] Read methods **never** return `Task<string>` (no `JsonConvert.SerializeObject` anywhere in DAL)
- [ ] Write methods return `Task<Result<string>>` (not `Task`, not `Task<string>`)
- [ ] Write methods accept `DbTransaction tx` as second-to-last param (before `CancellationToken ct`)
- [ ] DAL does **not** call `BeginTransactionAsync` / `CommitAsync` / `RollbackAsync`
- [ ] Audit fields (`CreatedById`, `CreatedOn`, `ModifiedById`, `ModifiedOn`) are set inside DAL using `loginDTO.UserId` / `DateTime.UtcNow`
- [ ] All `await` calls use `.ConfigureAwait(false)`
- [ ] `CancellationToken ct` is forwarded to every `_QueryExecutor` call

```csharp
// DAL — correct signature
public async Task<FooDTO?> GetFoo(int fooId, LoginDTO loginDTO, CancellationToken ct)
{
    return await _QueryExecutor.QuerySingleAsync<FooDTO>(loginDTO, FooQB.GET_FOO,
        new { FooId = fooId }).ConfigureAwait(false);
}

public async Task<Result<string>> SaveFoo(FooDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
{
    dto.CreatedById  = loginDTO.UserId;
    dto.CreatedOn    = DateTime.UtcNow;
    dto.ModifiedById = loginDTO.UserId;
    dto.ModifiedOn   = DateTime.UtcNow;
    int rows = await _QueryExecutor.ExecuteAsync(loginDTO, FooQB.SAVE_FOO, dto, tx).ConfigureAwait(false);
    return rows > 0
        ? Result<string>.Success("Foo saved.")
        : Result<string>.Failure("Failed to save Foo.");
}
```

---

### BLL Checklist

- [ ] Injects all **five required dependencies**: `I{Entity}DAL`, `AutoNumber`, `IQueryExecutor`, `KeyInvalidate`, `BaseEntityAppService<{Entity}DTO>`
- [ ] Read methods return **typed DTOs** (pass-through from DAL) — never serialize to JSON
- [ ] Save uses `_BaseEntityAppService.ExecuteSaveAsync(...)` — never calls DAL write methods directly
- [ ] `BeginTransactionAsync` is called **before** auto-number allocation, at the very top of the save method
- [ ] Auto-numbers allocated **before** `ExecuteSaveAsync` (outside the callback), rollback in `catch`
- [ ] `CommitAsync` immediately followed by `KeyInvalidate.AllInvalidateCache(cacheKey)`
- [ ] `RollbackAsync` in every `catch`, followed by `throw`
- [ ] Success messages use `SuccessResponse.SaveSuccessMessage` / `UpdateSuccessMessage` / `DeleteSuccessMessage`
- [ ] All `await` calls use `.ConfigureAwait(false)`

```csharp
// BLL — correct save structure
public async Task<string> SaveFoo(FooDTO fooDTO, LoginDTO loginDTO, CancellationToken ct)
{
    if (fooDTO == null) throw new ArgumentNullException(nameof(fooDTO));
    ValidateFoo(fooDTO);  // throws ArgumentException — before BeginTransaction

    bool isNew = fooDTO.FooId == 0;
    var Trans  = await _QueryExecutor.BeginTransactionAsync(loginDTO);
    try
    {
        if (isNew)
        {
            var auto = await _AutoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.FOO, loginDTO);
            fooDTO.FooId = auto.StartNumber;
        }

        await _BaseEntityAppService.ExecuteSaveAsync(
            EntityConstant.OBJECTFOO,
            isNew ? EventTypeConstant.SAVEFOOEVENTTYPEID : EventTypeConstant.UPDATEFOOEVENTTYPEID,
            fooDTO, loginDTO,
            async tx =>
            {
                _ = isNew
                    ? await _FooDAL.SaveFoo(fooDTO, loginDTO, tx, ct).ConfigureAwait(false)
                    : await _FooDAL.UpdateFoo(fooDTO, loginDTO, tx, ct).ConfigureAwait(false);
                return fooDTO.FooId;
            },
            null, -1, -1, Trans);

        await _QueryExecutor.CommitAsync(Trans);

        var cacheKey = KeyGenerator.KeyGeneration(
            fooDTO.FooId, EntityConstant.OBJECTFOO, CacheKeyLevel.CLIENT_LEVEL, loginDTO);
        await _KeyInvalidate.AllInvalidateCache(cacheKey);

        return isNew
            ? $"{SuccessResponse.SaveSuccessMessage} {fooDTO.FooId}"
            : $"{SuccessResponse.UpdateSuccessMessage} {fooDTO.FooId}";
    }
    catch (Exception)
    {
        await _QueryExecutor.RollbackAsync(Trans);
        // rollback auto-numbers here if allocated
        throw;
    }
}
```

---

### SL Checklist

- [ ] `ExecuteAsync` has **no try/catch** — `BaseEndpoint.HandleAsync` owns error handling
- [ ] Read endpoints override `GetCacheKey` using `KeyGenerator.KeyGeneration(objectId, entityConstant, level, login)`
- [ ] Write endpoints do **not** override `GetCacheKey` (default returns null)
- [ ] `CreateSuccessResponse(result, level, loginDTO)` — `result` is a typed DTO, not a pre-serialized string

```csharp
// SL — correct endpoint pattern
protected override string? GetCacheKey(GetFooParameters req, LoginDTO loginDTO)
    => KeyGenerator.KeyGeneration(req.FooId, EntityConstant.OBJECTFOO, CacheKeyLevel.CLIENT_LEVEL, loginDTO);

protected override async Task<ResponseStandardDTO<object>> ExecuteAsync(
    GetFooParameters req, LoginDTO loginDTO, CancellationToken ct)
{
    var result = await _bll.GetFoo(req.FooId, loginDTO, ct);
    return await Response.CreateSuccessResponse(result, CacheKeyLevel.CLIENT_LEVEL, loginDTO);
}
```

---

### Constants Checklist

For every new entity, add to `GB5Shared/GB5Constant/Constant.cs`:

```csharp
// In EntityConstant class
public static int OBJECTFOO = <unique negative int>;

// In EventTypeConstant class
public static int SAVEFOOEVENTTYPEID   = <unique negative int>;
public static int UPDATEFOOEVENTTYPEID = <unique negative int>;
public static int DELETEFOOEVENTTYPEID = <unique negative int>;

// In AUTONUMBERCONSTANT class (if entity uses auto-number)
public const string FOO = "FOO";
```

ID allocation: use the next available number below the last entry in each class.
Current watermark (as of 2026-04-17):
- `EntityConstant`: last used `-1387000006` (OBJECTMTR)
- `EventTypeConstant`: last used `-1386999895` (DELETESUPPLYGROUPEVENTTYPEID)

---

### Quick Anti-Pattern Reference

| Anti-Pattern | Correct Pattern |
|---|---|
| `DAL.GetFoo(...)` returns `Task<string>` | Returns `Task<FooDTO?>` |
| `JsonConvert.SerializeObject(dto)` in DAL | Remove — return typed object |
| `BeginTransaction` inside DAL | Remove — DAL receives `DbTransaction tx` |
| BLL calls `_dal.SaveFoo()` directly | Wrap in `ExecuteSaveAsync` callback |
| BLL missing `KeyInvalidate` after commit | Add `_KeyInvalidate.AllInvalidateCache(cacheKey)` |
| SL endpoint has try/catch in ExecuteAsync | Remove — BaseEndpoint handles errors |
| SL passes pre-serialized string to `CreateSuccessResponse` | Pass typed DTO directly |

---

## §27 — Version Bump — Mandatory in Every Schema-Changing Migration

`VersionDAL.VersionCheck()` (`FrameworkDAL/CustomCode/Version/VersionDAL.cs`) compares two DB-stored
values on every login/session bootstrap and blocks access with "Database schema is ahead of the
application version" (or the reverse) on a mismatch:

- **`MDBLevelSetting.BUILDVERSION`** — lives in each **tenant database**; the schema/migration level
  that database is actually at.
- **`MAppVersion.VERSIONNUMBER`** (WHERE `ISAPPLICABLE = 0`) — lives in **GB5System**; the app
  release the running backend build expects.

Both are plain `NVARCHAR` version strings (e.g. `"4.1.6.40.1"`), compared segment-by-segment as
integers (`VersionBLL.CompareVersionStrings`) — never edit them to anything but a dot-separated
numeric string.

**There is no automated release step for either value today.** Every migration script that changes
tenant-database schema **must** end with an `UPDATE MDBLevelSetting SET BUILDVERSION = ...`
statement bumping the version to match what the application (as of that migration) expects — do not
rely on someone updating it out-of-band after the fact. Skipping this means every tenant that runs
the migration is silently left on a stale `BUILDVERSION`, and the version-check endpoint will not
catch it (since the row was never updated) until a *later* migration changes it and cross-tenants
start comparing mismatched versions.

**Template — append to the end of every schema-changing migration `.sql` file:**

```sql
-- Bump schema version so VersionDAL.VersionCheck() sees this tenant DB as up to date.
-- Keep in sync with the release's MAppVersion.VERSIONNUMBER (GB5System, updated separately —
-- see below). Segment-by-segment numeric compare: use dot-separated integers only.
UPDATE MDBLevelSetting
SET    BUILDVERSION = '<NEW_VERSION>'   -- e.g. '4.1.6.41.1' — bump the segment this migration affects
WHERE  1 = 1;   -- MDBLevelSetting is a single-row-per-tenant-DB table; no WHERE-key needed
```

**Postgres dialect** — apply the same `ConvertSqlToPostgres` rules used elsewhere in this file
(quoted identifiers, `:param` style) if the migration is dual-dialect; the UPDATE itself has no
SQL-Server-only syntax so it converts cleanly.

**The `MAppVersion` side is separate and NOT part of a per-tenant migration file** — it's a single
GB5System-wide row marking which release is "applicable" release-wide, not per-tenant schema state.
When cutting a new release that bumps `VERSIONNUMBER`:
1. Insert (or update) the `MAppVersion` row for the new version with `ISAPPLICABLE = 0`.
2. Only after every tenant's migration has run (and their `MDBLevelSetting.BUILDVERSION` matches),
   flip the *previous* applicable row's `ISAPPLICABLE` away from 0 if the rollout requires a hard
   cutover — otherwise leave both rows `ISAPPLICABLE = 0` during a dual-version rollout window
   (`VersionDAL.VersionCheck` already supports multiple simultaneously-applicable rows; see its
   comment on `results`).

**Verification:** after applying a migration, call `GET /Version/VersionCheck?ConnectionName=<name>`
and confirm `MatchStatus = 0` ("Matching Version between Database and Application") for the tenant
the migration targeted.