# CLAUDE.md — GoodBooks GB5 Backend (.NET 9 Microservices)

## Project Overview

Enterprise-grade .NET 9 microservices ERP backend.
**Architecture:** 3-tier per module (SL → BLL → DAL) with shared framework libraries.
**No Entity Framework.** All data access uses **Dapper** with raw, parameterized SQL.
**No traditional MVC controllers.** All HTTP endpoints use **FastEndpoints**.

---

## Architecture Rules

### Layer Responsibilities

| Layer | Folder suffix | Responsibility |
|-------|--------------|----------------|
| Service Layer | `*SL` | HTTP endpoint definitions (FastEndpoints), request/response mapping, cache-key generation |
| Business Logic Layer | `*BLL` | Validation, orchestration, business rules, calling DAL |
| Data Access Layer | `*DAL` | Dapper queries, stored-procedure calls, transaction management |

**Strict rules:**
- SL must never call DAL directly — always go through BLL.
- BLL must never reference ASP.NET types (`HttpContext`, `IEndpointRouteBuilder`, etc.).
- DAL must never contain business logic; only data retrieval/mutation.
- DTOs cross all layers; domain objects stay inside BLL.

### Module Structure Template

```
ModuleSL/
Endpoints/
GetFoo.cs ← FastEndpoint
SaveFoo.cs
Parameters/
GetFooParameters.cs
Hubs/ ← only present for real-time modules
FooHub.cs ← SignalR Hub (inherits Hub<IFooClient>)
IFooClient.cs ← strongly-typed client contract interface
ModuleBLL/
Interfaces/
IFooBLL.cs
Implementations/
FooBLL.cs
ModuleDAL/
Interfaces/
IFooDAL.cs
Implementations/
FooDAL.cs
QueryBuilders/
FooQB.cs ← SQL constants only
DTOs/
FooDTO.cs
```

---

## Coding Standards

### Naming Conventions

```csharp
// Interfaces — prefix with I
public interface IAccountBLL { }
public interface IAccountDAL { }

// Implementations — no prefix
public class AccountBLL : IAccountBLL { }
public class AccountDAL : IAccountDAL { }

// Endpoints — verb + entity
public class GetAccount : BaseEndpoint<GetAccountParameters, ResponseStandardDTO<object>> { }
public class SaveAccount : BaseEndpoint<SaveAccountParameters, ResponseStandardDTO<object>> { }

// Query Builders — entity + QB
public static class AccountQB
{
public const string GET_ACCOUNT = "SELECT ...";
public const string SAVE_ACCOUNT = "INSERT INTO ...";
}

// Parameters (request DTOs) — PascalCase, suffix Parameters
public class GetAccountParameters { }

// DTOs — suffix DTO
public class AccountDTO { }
```

### Async/Await — Always Async All the Way

```csharp
// CORRECT — async throughout
public async Task<string> GetAccount(int id, LoginDTO login)
=> await _dal.GetAccount(id, login);

// WRONG — blocking on async
public string GetAccount(int id, LoginDTO login)
=> _dal.GetAccount(id, login).Result; // deadlock risk
```

### BLL Methods That Mutate Data — One DTO Parameter, Never Loose Scalars

Any BLL method that creates or mutates a record (`SaveX`, `AssignX`, `UpdateX`, etc.) must accept a **single DTO parameter** — never multiple loose scalar parameters, even for a small junction/assignment action with only 2-3 fields. Construct the DTO at the SL→BLL boundary, inside the endpoint's `ExecuteAsync`, not inside the BLL method.

```csharp
// CORRECT — one DTO parameter; SL builds it from the flat Parameters class
// SL:
protected override async Task<ResponseStandardDTO<object>> ExecuteAsync(
    AssignFooParameters req, LoginDTO login, CancellationToken ct)
{
    var dto = new FooAssignmentDTO { FooId = req.FooId, BarId = req.BarId };
    return await _bll.AssignFooAsync(dto, login, ct).ConfigureAwait(false);
}

// BLL:
public async Task<ResponseStandardDTO<object>> AssignFooAsync(FooAssignmentDTO dto, LoginDTO login, CancellationToken ct)
{
    // dto.FooId, dto.BarId — validate, generate PK, fill server-side fields on dto, save
}

// WRONG — loose scalars threaded through instead of a DTO
public async Task<ResponseStandardDTO<object>> AssignFooAsync(int fooId, int barId, LoginDTO login, CancellationToken ct)
```

Simple ID-based `DeleteX(int id, LoginDTO, ct)` and `GetXList(int pageNo, int pageSize, ..., LoginDTO, ct)`-style filter/read methods are exempt — this rule applies to mutate-shaped methods only.

### Cancellation Token Propagation

Always accept and forward `CancellationToken` in every async method chain.

```csharp
// SL (endpoint)
protected override async Task<ResponseStandardDTO<object>> ExecuteAsync(
GetAccountParameters req, LoginDTO login, CancellationToken ct)
{
return await _bll.GetAccount(req.AccountId, login, ct);
}

// BLL
public async Task<string> GetAccount(int id, LoginDTO login, CancellationToken ct)
=> await _dal.GetAccount(id, login, ct);

// DAL
public async Task<string> GetAccount(int id, LoginDTO login, CancellationToken ct)
{
string sql = AccountQB.GET_ACCOUNT;
return await _queryExecutor.QuerySingleAsync<string>(login, sql,
new { AccountId = id }, cancellationToken: ct);
}
```

---

## Data Access — Dapper Rules (No EF)

### Parameterized Queries — Non-Negotiable

```csharp
// CORRECT
string sql = "SELECT * FROM Account WHERE AccountId = @AccountId AND ClientId = @ClientId";
var result = await _queryExecutor.QuerySingleAsync<AccountDTO>(
loginDTO, sql, new { AccountId = id, ClientId = loginDTO.ClientId });

// WRONG — SQL injection vulnerability
string sql = $"SELECT * FROM Account WHERE AccountId = {id}";
```

### Query Builder Pattern

All SQL lives in `*QB.cs` files as `public const string`. Never embed SQL inline in BLL/DAL methods.

```csharp
public static class AccountQB
{
public const string GET_ACCOUNT = @"
SELECT a.AccountId, a.AccountCode, a.AccountName, a.IsActive
FROM Account a
WHERE a.AccountId = @AccountId
AND a.DatabaseName = @DatabaseName";

public const string SAVE_ACCOUNT = @"
INSERT INTO Account (AccountCode, AccountName, ClientId, CreatedBy, CreatedDate)
VALUES (@AccountCode, @AccountName, @ClientId, @CreatedBy, GETUTCDATE())";
}
```

### IQueryExecutor Usage Reference

```csharp
// Single record
var dto = await _qe.QuerySingleAsync<AccountDTO>(login, sql, param, ct);

// List
var list = await _qe.QueryAsync<AccountDTO>(login, sql, param, ct);

// Execute (INSERT/UPDATE/DELETE) — returns rows affected
int rows = await _qe.ExecuteAsync(login, sql, param, ct);

// Insert returning new identity  (SQL must end with SELECT SCOPE_IDENTITY() — no CT param)
int newId = await _qe.ExecuteIdentityAsync(login, sql, param);

// Scalar (COUNT, SUM, etc.)
int count = await _qe.ExecuteScalarAsync<int>(login, sql, param, ct);

// Paged result
var paged = await _qe.QueryPagedAsync<AccountDTO>(login, sql, countSql, param, page, size, ct);

// Streaming large datasets — never load >10k rows into memory at once
await foreach (var row in _qe.StreamAsync<AccountDTO>(login, sql, param, ct))
{
// process each row
}

// Multi-table join
var result = await _qe.QueryMultiMapAsync<AccountDTO, BranchDTO, AccountDTO>(
login, sql, (a, b) => { a.Branch = b; return a; }, "BranchId", param, ct);
```

### Transaction Management

**For saving a primary business entity, always route through `ExecuteSaveAsync`** on
`BaseEntityAppService<TDto>` (`GB5Shared/EntityHandler/EventHandler.cs`) — never hand-roll a
`BeginTransactionAsync`/commit/rollback block for an entity save. `ExecuteSaveAsync` is the
universal save pipeline: it opens the transaction, runs validation, workflow/WIP checks,
pre/post-persist hooks, publishes the Dapr event, writes the outbox record, and commits — all
inside one OTel `GB5.EntitySave` span. A hand-rolled transaction around a DAL save silently skips
all of that (no workflow check, no event publish, no audit trail).

```csharp
// CORRECT — entity save routes through the pipeline
public async Task<string> SaveAccount(AccountDTO dto, LoginDTO login, CancellationToken ct)
{
var isNew = dto.AccountId == 0;
await _baseEntityAppService.ExecuteSaveAsync(
dto.AccountId, EventTypes.CREATE, dto, login,
persistFunc: tx => _accountDAL.SaveAccount(dto, login, tx, ct),
isNewEntity: isNew);
return isNew ? $"{SuccessResponse.SaveSuccessMessage} {dto.AccountId}" : SuccessResponse.UpdateSuccess;
}

// WRONG — bypasses ExecuteSaveAsync: no workflow check, no event publish, no outbox write
await using var tx = await _queryExecutor.BeginTransactionAsync(login);
try
{
await _queryExecutor.ExecuteAsync(login, AccountQB.SAVE_ACCOUNT, dto, tx, ct);
await tx.CommitAsync(ct);
}
catch
{
await tx.RollbackAsync(ct);
throw;
}
```

**Manual `BeginTransactionAsync`/commit/rollback is only for legitimate non-entity or composite
operations** that don't map to a single `ExecuteSaveAsync` call — e.g. a multi-entity batch
job, a reconciliation routine, or a sub-step that accepts an externally-supplied
`DbTransaction? tx = null` and composes into a caller's own `ExecuteSaveAsync(externalTransaction: tx)`
call. If you use a manual transaction for a case like this, mark it with
`[ManualTransactionJustified("reason")]` so it's visibly reviewed rather than silently
indistinguishable from a bypass.

```csharp
// CORRECT — legitimate manual transaction: multi-entity composite, no single owning DTO
[ManualTransactionJustified("Reconciliation batch touches N unrelated ledger entries; no single entity owns this save.")]
public async Task<string> ReconcileBatch(List<LedgerEntryDTO> entries, LoginDTO login, CancellationToken ct)
{
await using var tx = await _queryExecutor.BeginTransactionAsync(login);
try
{
foreach (var entry in entries)
await _ledgerDAL.SaveEntry(entry, login, tx, ct);
await tx.CommitAsync(ct);
return SuccessResponse.UpdateSuccess;
}
catch
{
await tx.RollbackAsync(ct);
throw;
}
}
```

### Large Dataset Rules

- Use `StreamAsync<T>()` for any query potentially returning >5,000 rows.
- Use `BulkInsertAsync<T>()` for batch inserts of >100 rows — never loop `ExecuteAsync`.
- Paginate all list endpoints via `QueryPagedAsync<T>()` — no unbounded SELECT *.

---

## FastEndpoints — SL Layer

### Endpoint Template

```csharp
public class GetAccount : BaseEndpoint<GetAccountParameters, ResponseStandardDTO<object>>
{
private readonly IAccountBLL _bll;

public GetAccount(IAccountBLL bll) => _bll = bll;

public override void Configure()
{
Get("/Account/GetAccount");
AllowAnonymous(); // replace with Roles("Admin") for secured endpoints
}

protected override string? GetCacheKey(GetAccountParameters req, LoginDTO login)
=> $"Account:{login.ClientId}:{req.AccountId}"; // return null to skip cache

protected override async Task<ResponseStandardDTO<object>> ExecuteAsync(
GetAccountParameters req, LoginDTO login, CancellationToken ct)
=> await _bll.GetAccount(req.AccountId, login, ct);
}
```

### Route Convention

```
GET /Entity/GetEntity ← single record or list
POST /Entity/SaveEntity ← create
PUT /Entity/UpdateEntity ← update
DELETE /Entity/DeleteEntity ← delete
GET /Entity/GetSelectListEntity ← dropdown data
```

### Response Convention

Always return `ResponseStandardDTO<object>` from endpoints. BLL returns serialized JSON string; SL wraps it.

---

## Real-Time / SignalR Modules

Use SignalR only for modules that genuinely require server-push or bidirectional communication
(e.g., CollabSpace, live notifications, presence tracking).
REST endpoints (FastEndpoints) remain the standard for all request/response interactions.

### Folder Structure

Hub files live exclusively in `ModuleSL/Hubs/`. Two files per Hub:

```
ModuleSL/Hubs/
CollabSpaceHub.cs ← Hub implementation
ICollabSpaceClient.cs ← strongly-typed client contract
```

### Strongly-Typed Client Contract

Always use `Hub<TClient>` — never the untyped `Hub`. This gives compile-time verification
of every client-side method name and signature.

```csharp
// ICollabSpaceClient.cs
public interface ICollabSpaceClient
{
Task ReceiveMessage(CollabMessageDTO message);
Task ReceivePresenceUpdate(PresenceDTO presence);
Task ReceiveTypingIndicator(string senderName, bool isTyping);
Task ReceiveError(string errorMessage);
}
```

### Hub Template

```csharp
// CollabSpaceHub.cs
public class CollabSpaceHub : Hub<ICollabSpaceClient>
{
private readonly ICollabSpaceBLL _bll;
private readonly ILogger<CollabSpaceHub> _logger;

public CollabSpaceHub(ICollabSpaceBLL bll, ILogger<CollabSpaceHub> logger)
{
_bll = bll;
_logger = logger;
}

// ── Connection lifecycle ──────────────────────────────────────────

public override async Task OnConnectedAsync()
{
var login = GetLoginDTO();
await Groups.AddToGroupAsync(Context.ConnectionId, HubGroup(login));
_logger.LogInformation("SignalR connected: {ConnectionId} user {UserId}",
Context.ConnectionId, login.UserId);
await base.OnConnectedAsync();
}

public override async Task OnDisconnectedAsync(Exception? exception)
{
var login = GetLoginDTO();
await Groups.RemoveFromGroupAsync(Context.ConnectionId, HubGroup(login));
if (exception is not null)
_logger.LogWarning(exception, "SignalR disconnected with error: {ConnectionId}",
Context.ConnectionId);
await base.OnDisconnectedAsync(exception);
}

// ── Hub methods (all async Task, never async void) ───────────────

public async Task SendMessage(CollabMessageParameters param, CancellationToken ct)
{
var login = GetLoginDTO();
try
{
var result = await _bll.SendMessage(param, login, ct);
// Broadcast to the group — not back to caller only
await Clients.Group(HubGroup(login)).ReceiveMessage(result);
}
catch (ValidationException vex)
{
await Clients.Caller.ReceiveError(vex.Message);
}
catch (Exception ex)
{
_logger.LogError(ex, "SendMessage failed for user {UserId}", login.UserId);
await Clients.Caller.ReceiveError("An error occurred. Please retry.");
}
}

public async Task SetTypingIndicator(bool isTyping, CancellationToken ct)
{
var login = GetLoginDTO();
// Broadcast to group excluding the sender
await Clients.GroupExcept(HubGroup(login), Context.ConnectionId)
.ReceiveTypingIndicator(login.Username, isTyping);
}

// ── Helpers ──────────────────────────────────────────────────────

// ALWAYS reconstruct LoginDTO from HTTP context — never accept it from the client payload
private LoginDTO GetLoginDTO()
{
var httpContext = Context.GetHttpContext()
?? throw new InvalidOperationException("Hub invoked outside HTTP context.");
return LoginDTO.FromHttpContext(httpContext);
}

// Group name convention: {module}:{sessionId}
// Use ClientId for tenant-wide, SessionId for session-scoped, WorkOUId for OU-scoped
private static string HubGroup(LoginDTO login) => $"collabspace:{login.SessionId}";
}
```

### LoginDTO — Always from Hub Context, Never from Client

```csharp
// CORRECT — reconstruct from verified HTTP context (token/claims)
private LoginDTO GetLoginDTO()
=> LoginDTO.FromHttpContext(Context.GetHttpContext()!);

// WRONG — trusting client-supplied identity allows impersonation
public async Task SendMessage(CollabMessageParameters param, LoginDTO login) { }
```

### Server-Initiated Push via IHubContext

When BLL needs to push to connected clients outside of a Hub method (e.g., after a background job,
after a REST endpoint write, after a Dapr event), inject `IHubContext<THub, TClient>`.

```csharp
// In BLL or a background service
public class NotificationBLL(
IHubContext<CollabSpaceHub, ICollabSpaceClient> hubContext,
ILogger<NotificationBLL> logger) : INotificationBLL
{
public async Task BroadcastApproval(ApprovalDTO dto, LoginDTO login, CancellationToken ct)
{
var group = $"collabspace:{login.SessionId}";
await hubContext.Clients.Group(group)
.ReceiveMessage(dto.ToCollabMessageDTO());
logger.LogInformation("Approval broadcast to group {Group}", group);
}
}
```

### Hub Group Naming Convention

| Scope | Pattern | Example |
|-------|---------|---------|
| Per session | `{module}:{sessionId}` | `collabspace:abc123` |
| Per tenant/client | `{module}:client:{clientId}` | `collabspace:client:42` |
| Per organizational unit | `{module}:ou:{workOUId}` | `collabspace:ou:7` |
| Per user | `{module}:user:{userId}` | `collabspace:user:99` |

Never use connection ID as a group — SignalR already supports `Clients.Client(connectionId)` directly.

### DI Lifetime — Hub is Transient

Hubs are **transient** by design — a new instance is created per Hub method invocation.
Consequences:

```csharp
// WRONG — Hub instance fields do NOT persist across calls; don't store state here
public class FooHub : Hub<IFooClient>
{
private List<string> _messages = new(); // lost after each method call
}

// CORRECT — Hub has no mutable state; inject Scoped services normally
// Scoped services work correctly because Hub lifetime is within a single invocation scope
public class FooHub : Hub<IFooClient>
{
private readonly ICollabSpaceBLL _bll; // injected fresh each invocation — fine
public FooHub(ICollabSpaceBLL bll) => _bll = bll;
}

// For cross-invocation state (e.g., presence tracking) — use IDistributedCache or Redis
// via a Singleton service, NOT the Hub instance
```

### Registration in Program.cs

```csharp
// Add once per SL project that contains Hubs
builder.Services.AddSignalR(options =>
{
options.EnableDetailedErrors = builder.Environment.IsDevelopment();
options.MaximumReceiveMessageSize = 32 * 1024; // 32 KB — raise only if justified
options.ClientTimeoutInterval = TimeSpan.FromSeconds(60);
options.KeepAliveInterval = TimeSpan.FromSeconds(15);
});

// Map after app.UseAuthorization()
app.MapHub<CollabSpaceHub>("/hubs/collabspace");
```

### High-Frequency Push — Backpressure with Channel<T>

For real-time feeds (e.g., live price ticks, log streaming) that can overwhelm slow clients:

```csharp
public async Task StreamLiveFeed(CancellationToken ct)
{
var login = GetLoginDTO();
var channel = Channel.CreateBounded<FeedItemDTO>(
new BoundedChannelOptions(100)
{
FullMode = BoundedChannelFullMode.DropOldest // drop stale items under backpressure
});

_ = ProduceFeedAsync(channel.Writer, login, ct); // fire-and-forget producer

await foreach (var item in channel.Reader.ReadAllAsync(ct))
await Clients.Caller.ReceiveMessage(item.ToCollabMessageDTO());
}

private async Task ProduceFeedAsync(
ChannelWriter<FeedItemDTO> writer, LoginDTO login, CancellationToken ct)
{
try
{
await foreach (var item in _bll.StreamFeedAsync(login, ct))
await writer.WriteAsync(item, ct);
}
finally
{
writer.TryComplete();
}
}
```

### Memory Leak Prevention — SignalR Specific

```csharp
// WRONG — holding a reference to Clients/Groups outside of Hub scope
private IHubClients<IFooClient> _savedClients; // dangling reference after disconnect

// WRONG — not removing from group on disconnect
public override Task OnConnectedAsync()
{
Groups.AddToGroupAsync(Context.ConnectionId, "group"); // never removed
return base.OnConnectedAsync();
}

// CORRECT — always mirror AddToGroup with RemoveFromGroup in OnDisconnectedAsync

// WRONG — connection ID dictionary that never evicts
private static readonly Dictionary<string, LoginDTO> _connections = new();
// (grows unbounded as connections accumulate)

// CORRECT — use IDistributedCache with sliding TTL, or ConcurrentDictionary + OnDisconnected cleanup
private static readonly ConcurrentDictionary<string, LoginDTO> _connections = new();
public override Task OnDisconnectedAsync(Exception? ex)
{
_connections.TryRemove(Context.ConnectionId, out _); // always clean up
return base.OnDisconnectedAsync(ex);
}
```

### SignalR Rules Summary

- Hub classes live in `ModuleSL/Hubs/` — nowhere else.
- Always use `Hub<IClientContract>` (strongly typed) — never untyped `Hub`.
- All Hub methods are `async Task` — never `async void`, never synchronous.
- `LoginDTO` is always reconstructed from `Context.GetHttpContext()` — never accepted from client payload.
- No direct DAL access from Hubs — always through BLL, same as endpoints.
- No mutable state stored in Hub instance fields — Hub is transient.
- Always clean up group membership in `OnDisconnectedAsync`.
- Use `IHubContext<THub, TClient>` for server-push from BLL or background services.
- Use `Channel<T>` with bounded capacity for high-frequency streaming to prevent client overload.

---

## Caching

### Cache Level Selection

| Use Case | Level |
|----------|-------|
| Language / global config | `CacheKeyLevel.OVERALL` |
| Per database server | `CacheKeyLevel.DB_SERVER` |
| Per client/tenant | `CacheKeyLevel.CLIENT_LEVEL` |
| Per role | `CacheKeyLevel.ROLE_LEVEL` |
| Per user | `CacheKeyLevel.USER_LEVEL` |
| Session-specific data | `CacheKeyLevel.SESSION_LEVEL` |
| Highly volatile / transactional data | `CacheKeyLevel.NOT_REQUIRED` |

### Cache Key Generation

```csharp
// In GetCacheKey() override — include all factors that distinguish the result
protected override string? GetCacheKey(GetAccountParameters req, LoginDTO login)
{
// Multi-tenant: always include ClientId
// User-specific: include UserId or RoleId as needed
return CacheKeyGenerator.Generate(
objectId: req.AccountId.ToString(),
objectType: "Account",
login: login,
level: CacheKeyLevel.CLIENT_LEVEL);
}

// Return null for mutation endpoints (POST/PUT/DELETE)
protected override string? GetCacheKey(SaveAccountParameters req, LoginDTO login) => null;
```

### Cache Invalidation

Invalidate related keys after any write operation:

```csharp
// In BLL after successful save/update/delete
await _keyInvalidate.InvalidateAsync(
objectType: "Account",
clientId: login.ClientId,
level: CacheKeyLevel.CLIENT_LEVEL);
```

### Caching Anti-Patterns to Avoid

```csharp
// WRONG — caching transactional/financial data without expiry
// WRONG — using USER_LEVEL cache for data shared across users
// WRONG — caching mutable reference data at OVERALL level
// WRONG — no cache invalidation after writes
// CORRECT — financial ledger/journal entries: NOT_REQUIRED
// CORRECT — dropdown master data: CLIENT_LEVEL with 5-min TTL
// CORRECT — language resources: OVERALL level
```

---

## Performance

### Database

1. **Always filter by tenant** — every query must include `ClientId`/`DatabaseName`.
2. **Use covering indexes** — QB comments must note required indexes.
3. **Avoid N+1** — use `QueryMultiMapAsync` or JOIN instead of loops.
4. **Use `ValueTask`** for hot paths that usually return synchronously.
5. **Prefer `IAsyncEnumerable`** (StreamAsync) over `List<T>` for large result sets.

### Memory

1. **Never store `IDbConnection` as a field** — connections are managed by `IQueryExecutor`.
2. **Dispose `IAsyncEnumerable` enumerators** — use `await foreach` with `ConfigureAwait(false)`.
3. **Do not cache `LoginDTO`** — it contains sensitive session data; reconstruct per-request.
4. **Large string building** — use `StringBuilder` or `Span<T>`, not string concatenation in loops.
5. **PDF/Excel generation** — stream to `MemoryStream` and dispose immediately after send; never hold in-process.

### Async Pitfalls

```csharp
// WRONG — sync-over-async causes threadpool starvation
var result = _bll.GetAccount(id, login).GetAwaiter().GetResult();

// WRONG — ConfigureAwait missing in library code
var result = await _dal.GetAccount(id, login); // in BLL/DAL (non-ASP.NET context)

// CORRECT — library/non-UI code
var result = await _dal.GetAccount(id, login).ConfigureAwait(false);

// WRONG — Task.Run wrapping async
var result = await Task.Run(() => _dal.GetAccount(id, login));
```

---

## Memory Leak Prevention

### Disposable Resources

```csharp
// IDbConnection — NEVER hold as instance field
// CORRECT — let QueryExecutor manage connection lifetime

// MemoryStream for documents
using var ms = new MemoryStream();
// ... generate PDF ...
await Response.SendStreamAsync(ms, "application/pdf", cancellation: ct);
// ms disposed automatically

// HttpClient — NEVER instantiate directly
// CORRECT — inject IHttpClientFactory
public class FooService(IHttpClientFactory factory)
{
private readonly HttpClient _http = factory.CreateClient("named-client");
}
```

### DI Lifetime Rules

| Service Type | Lifetime |
|-------------|----------|
| BLL, DAL, Validators | `Scoped` (per-request) |
| QueryExecutor, DbConnection | `Scoped` |
| Cache utilities, KeyGenerators | `Singleton` |
| HttpClient (via factory) | Managed by factory |
| LoginDTO / session state | **Never register** — pass as parameter |

```csharp
// WRONG — capturing scoped service in singleton
public class MySingleton(IScopedService scoped) { } // runtime error

// CORRECT — use IServiceScopeFactory if singleton needs scoped service
public class MySingleton(IServiceScopeFactory factory)
{
public async Task DoWork()
{
using var scope = factory.CreateScope();
var svc = scope.ServiceProvider.GetRequiredService<IScopedService>();
await svc.DoSomething();
}
}
```

### Event Handler & Timer Leaks

```csharp
// Always unsubscribe events in Dispose
public class FooService : IDisposable
{
private readonly Timer _timer;
public FooService() => _timer = new Timer(Callback, null, 0, 5000);
public void Dispose() => _timer?.Dispose();
}
```

### Static Collections

```csharp
// WRONG — unbounded static Dictionary grows forever
private static readonly Dictionary<string, object> _cache = new();

// CORRECT — use IMemoryCache / IHybridCache with expiry, or ConcurrentDictionary with eviction
```

---

## Security

### Multi-Tenancy

```csharp
// ALWAYS include tenant filter — never return cross-tenant data
string sql = @"
SELECT AccountId, AccountName
FROM Account
WHERE AccountId = @AccountId
AND DatabaseName = @DatabaseName"; // ← mandatory tenant filter
```

### Input Validation

```csharp
// BLL validates before calling DAL
public async Task<string> SaveAccount(AccountDTO dto, LoginDTO login, CancellationToken ct)
{
_validation.NotEmpty(dto.AccountCode, nameof(dto.AccountCode));
_validation.NotEmpty(dto.AccountName, nameof(dto.AccountName));
// ... more rules
return await _dal.SaveAccount(dto, login, ct);
}
```

### Sensitive Data

- Connection strings stored in **HashiCorp Vault** (`VaultSharp`) — never in appsettings.json.
- `LoginDTO` credentials encrypted before transport via `LoginEncryption`.
- Never log `LoginDTO` fields, connection strings, or financial amounts.
- Never return stack traces to the client — log server-side, return generic message.

---

## SqlWorkbench — Dev vs. Live Database Practice

SqlWorkbench (`GB5Solution/SqlWorkbench/`) is a **general-purpose DDL/DML sync tool** — it is
usable by any project, not exclusive to GB5. GB5 itself is one of its tenants: GB5 also runs as
a live product instance for GB itself, operating as a real company (a genuine client usage, same
as any other tenant's production database). Developers must never author or test migration
scripts directly against that live instance.

**Practice (applies to any project onboarded onto SqlWorkbench, not GB5-specific):**

- Register a dedicated non-production `SW.MSWCLIENTDATABASE` row for development
  (e.g. `CLIENTDBCODE = '<PROJECT>-DEV'`) under the **same `DBMODELID`** as the project's real
  live client-usage database. No new schema is needed — `MSWCLIENTDATABASE` already supports
  multiple registrations against one `DBMODELID`.
- Developers author `SW.MSWDDLSCRIPT` rows as Draft, on a `SW.MSWSCRIPTBRANCH` (`BRANCHTYPE`
  Feature) scoped to their initiative, and test exclusively against the dev-designated
  `MSWCLIENTDATABASE`. This gives per-developer, per-initiative track/review/confirmation of
  in-flight scripts from day one — not a free-for-all against a shared database.
- The live client-usage database only ever receives changes as a released `SW.MSWUPGRADEPACKAGE`,
  through the normal release/promotion path — never a direct or ad hoc script run.
- This is the same tenant-isolation model SqlWorkbench already applies to every other client
  database, just applied reflexively to the project's own internal usage.

### `SW.MSWDBSERVER` — register two rows per physical server, not one

A single physical SQL Server box is commonly reachable two different ways, and one
`MSWDBSERVER` row cannot serve both:

- **External port mapping** (e.g. `localhost:14330` reached through an SSH tunnel, or any
  other externally-facing forwarded port) — works for a caller connecting *from outside* the
  box, but does **not** resolve for an in-process caller running *on that same machine* (e.g.
  `PlatformHost`, which hosts `SwSL` itself) trying to loop back to it — confirmed live: this
  blocked `ProvisionClientDatabase` outright the first time an in-process caller tried to use
  the already-registered external-mapping row.
- **Internal loopback** (`localhost:1433`, the real SQL Server port on that box) — resolves
  correctly for a co-located caller, but is meaningless to anything outside the box.

**Convention: register both, as two separate `MSWDBSERVER` rows against the same physical
server**, with `SERVERDESC` stating plainly which callers each is for. Confirmed live example
on the shared Dev/QC box (`217.216.78.142`) — `Gb5system.SW.MSWDBSERVER`:

| `DBSERVERID` | `HOSTNAME:PORT` | For |
|---|---|---|
| `0` | `localhost:1433` | in-process/co-located callers (e.g. `PlatformHost`) |
| `1` | `localhost:14330` | external callers reaching the box through a forwarded port |

New `MSWCLIENTDATABASE` registrations for a database that will be provisioned/executed
in-process must reference the loopback row (`0` in the example above), not whichever row an
older, externally-authored registration happened to use — check which callers will actually
be provisioning/executing against a given `MSWCLIENTDATABASE` row before picking its
`DBSERVERID`, don't default to copying an existing row's value.

**A code-level auto-detect-and-prefer-local-connection fix was considered and rejected** for
now — reliably detecting "the caller and the target happen to be co-located" is fragile across
containerized/multi-host deployments and would need its own careful design; the two-row
convention above is the safe, already-proven alternative and costs nothing beyond one extra
registration per server.

---

## Dependency Injection Registration

Modules use **assembly scanning** — follow this pattern exactly so auto-registration picks up new classes:

```csharp
// Program.cs — already configured; just ensure classes implement the interface
services.AddScopedFromAssembly(typeof(IAccountBLL).Assembly); // BLL assembly
services.AddScopedFromAssembly(typeof(IAccountDAL).Assembly); // DAL assembly
```

New BLL/DAL classes are auto-registered if they implement their interface. No manual registration needed.

---

## Observability

### Logging

```csharp
// Use structured logging — never string interpolation in log calls
_logger.LogInformation("Account {AccountId} saved by user {UserId}", dto.AccountId, login.UserId);

// WRONG
_logger.LogInformation($"Account {dto.AccountId} saved by user {login.UserId}");

// Log levels:
// Debug — high-frequency dev diagnostics (disabled in prod)
// Info — significant business events (saves, approvals, logins)
// Warning — recoverable issues (cache miss, retry)
// Error — caught exceptions that affect a request
// Critical — process-level failures
```

### Event Log Publishing (Dapr)

Publish business events for audit trail:

```csharp
await _eventLogPublish.PublishEventLogAsync(new EventLogDTO
{
EventText = "Account Created",
UserId = login.UserId,
Data = JsonSerializer.Serialize(dto),
EventTypeId = EventTypes.CREATE,
MachineIP = login.MachineIP
}, login);
```

### OpenTelemetry Tracing

```csharp
// Add spans for significant operations
using var activity = ActivitySource.StartActivity("SaveAccount");
activity?.SetTag("account.id", dto.AccountId);
activity?.SetTag("client.id", login.ClientId);
```

---

## Error Handling

### BLL Pattern

```csharp
public async Task<string> SaveAccount(AccountDTO dto, LoginDTO login, CancellationToken ct)
{
try
{
_validation.Validate(dto);
var result = await _dal.SaveAccount(dto, login, ct);
await _eventLog.PublishAsync(...);
return ResponseHelper.Success(result);
}
catch (ValidationException vex)
{
return ResponseHelper.ValidationError(vex.Message);
}
catch (Exception ex)
{
_logger.LogError(ex, "SaveAccount failed for AccountCode {Code}", dto.AccountCode);
return ResponseHelper.SystemError();
}
}
```

### Never Swallow Exceptions

```csharp
// WRONG — silent failure hides bugs
catch (Exception) { return string.Empty; }

// CORRECT — log and return structured error
catch (Exception ex)
{
_logger.LogError(ex, "...");
throw; // or return error DTO
}
```

---

## New Module Checklist

When adding a new module, follow this order:

- [ ] Create `ModuleDAL/DTOs/FooDTO.cs`
- [ ] Create `ModuleDAL/QueryBuilders/FooQB.cs` — SQL constants with index notes
- [ ] Create `ModuleDAL/Interfaces/IFooDAL.cs`
- [ ] Create `ModuleDAL/Implementations/FooDAL.cs` — inject `IQueryExecutor`
- [ ] Create `ModuleBLL/Interfaces/IFooBLL.cs`
- [ ] Create `ModuleBLL/Implementations/FooBLL.cs` — inject `IFooDAL`, `IValidation`, `ILogger<FooBLL>`
- [ ] Create `ModuleSL/Parameters/GetFooParameters.cs`
- [ ] Create `ModuleSL/Endpoints/GetFoo.cs`, `SaveFoo.cs`, `DeleteFoo.cs`
- [ ] Add cache key logic in each read endpoint's `GetCacheKey()`
- [ ] Add cache invalidation in BLL after every write
- [ ] Register new `.csproj` references if new project added
- [ ] Add required DB indexes to migration script

**If module requires real-time (SignalR):**

- [ ] Create `ModuleSL/Hubs/IFooClient.cs` — strongly-typed client contract
- [ ] Create `ModuleSL/Hubs/FooHub.cs` — inherits `Hub<IFooClient>`, no DAL access
- [ ] Implement `OnConnectedAsync` / `OnDisconnectedAsync` with group add/remove
- [ ] Implement `GetLoginDTO()` extracting from `Context.GetHttpContext()` — not from params
- [ ] Register `app.MapHub<FooHub>("/hubs/foo")` in `Program.cs`
- [ ] Add `builder.Services.AddSignalR()` if not already present in this SL project
- [ ] Inject `IHubContext<FooHub, IFooClient>` into BLL methods that need server-push

---

## Anti-Patterns — Prohibited

| Anti-Pattern | Why Prohibited |
|-------------|----------------|
| EF Core DbContext (for CRUD) | Not used — Dapper + raw SQL is the standard |
| MVC Controllers | Not used — FastEndpoints only |
| `async void` methods | Exceptions are unobservable; use `async Task` |
| `.Result` / `.Wait()` on Tasks | Deadlocks in ASP.NET context |
| `Thread.Sleep` | Blocks threadpool; use `Task.Delay` |
| Global mutable static state | Race conditions, memory leaks |
| Storing `IDbConnection` as a field | Connection leaks |
| `new HttpClient()` | Socket exhaustion; use `IHttpClientFactory` |
| Unbounded `List<T>` from DB | OOM on large tables; use paging or streaming |
| SQL string concatenation | SQL injection |
| Cross-tenant data access | Security violation — always filter by `ClientId`/`DatabaseName` |
| Logging sensitive fields (passwords, tokens) | Security violation |
| Caching financial/ledger data | Stale data in accounting; always query live |
| `async void` Hub methods | Exceptions are silently swallowed; use `async Task` |
| Untyped `Hub` base class | No compile-time check on client method names; use `Hub<TClient>` |
| Accepting `LoginDTO` as a Hub method parameter | Client-supplied identity enables impersonation |
| Mutable state in Hub instance fields | Hub is transient — state is lost between invocations |
| Missing `OnDisconnectedAsync` group cleanup | Groups accumulate stale connection IDs — memory leak |
| Calling DAL directly from a Hub | Bypasses BLL validation and event publishing |
| Unbounded connection tracking dictionary | Grows forever without disconnect cleanup |

---

## Technology Stack Reference

| Concern | Technology |
|---------|-----------|
| Runtime | .NET 9, C# 13 |
| HTTP Framework | FastEndpoints 6.x |
| Data Access | Dapper 2.x (no EF) |
| SQL Server driver | Microsoft.Data.SqlClient 6.x |
| PostgreSQL driver | Npgsql 8.x |
| Distributed messaging | Dapr (pub/sub, state store) |
| Caching | HybridCache + Redis (StackExchange.Redis) |
| Secrets | HashiCorp Vault (VaultSharp) |
| Serialization | System.Text.Json (primary) + Newtonsoft.Json (legacy) |
| PDF generation | iText7 / QuestPDF / PuppeteerSharp |
| Excel | ClosedXML |
| Real-time / bidirectional | ASP.NET Core SignalR (`Hub<TClient>`, `IHubContext`) |
| Observability | OpenTelemetry → Zipkin/Jaeger |
| Multi-database | SQL Server + PostgreSQL (runtime switchable per `LoginDTO.DatabaseType`) |

---

## MCP Tools — Live Schema & API Access

Four MCP servers are configured in `.claude/settings.json`. **Use them before writing any code.**

| Tool | When to use |
|------|-------------|
| `gb5-schema` (SQL Server MCP) | **Schema introspection** — verify columns, data types, FK relationships on the OLTP business DB (`GB5_SCHEMA_DB`). Change `GB5_SCHEMA_DB` in `~/.gb5-mcp.env` to switch environments or client DBs. |
| `gb5-meta` (SQL Server MCP) | **Metadata lookups** — query `uscdevsys` for menu IDs, form IDs, report links, and ID↔code mappings. Never use for business table schema. |
| `gb5-filesystem` (filesystem MCP) | Search QB files, DAL files, existing patterns across the repo |
| `gb5-api` (API MCP) | Verify an endpoint exists and returns the expected shape |
| `gb5-cypress` (Cypress MCP) | Run E2E specs from `gb4.7-e2e` to confirm end-to-end correctness |

**Two-database model:**
- `gb5-schema` → OLTP business DB — all `M*` and `T*` tables, common schema across clients. Schema introspection, column verification, and QB validation all use this.
- `gb5-meta` → `uscdevsys` — menus, forms, reports, system config, ID mappings. Use for `SELECT MENUID FROM MMENU WHERE ...`, not for `INFORMATION_SCHEMA.COLUMNS` queries.
- To switch client or environment: update `GB5_SCHEMA_HOST` / `GB5_SCHEMA_DB` in `~/.gb5-mcp.env` and restart Claude Code. The schema structure is the same across standard clients.

**Schema reference files (always available offline):**
- `docs/GB5-Schema-Registry.md` — 484 tables extracted from QB INSERT statements; re-run `bash tools/extract-schema.sh` after migrations
- `docs/api-snapshot.json` — swagger snapshot; refresh with `curl -s $GB5_API_BASE/swagger/v1/swagger.json > docs/api-snapshot.json`

---

## Deployment Logging — Mandatory for Every Manual Deploy

There is no CI/CD deploy path for the shared dev server (217.216.78.142) — `.gitlab-ci.yml` doesn't define any host there, so every deploy is a manual `scp`/`rsync` + `systemctl restart` sequence. This repo also has multiple concurrent git worktrees (each a separate agent/dev session) that can independently deploy to this same server with zero coordination. Git commit authorship does **not** help trace a bad deploy back to its source — every commit shows the same configured author regardless of which worktree/session made it.

**On 2026-07-18, a broken build was deployed to `gb5-engagementhost` with no way to identify which session/worktree did it** — this convention exists specifically to prevent that from happening again.

**Rule: the last step of any manual deploy to 217.216.78.142 is to append one row to `/root/GB5BuildFile/DEPLOY_LOG.md`** on the server:

```bash
ssh <user>@217.216.78.142 "echo '$(date -u +%Y-%m-%dT%H:%M:%SZ) | <service-name> | $(git rev-parse --abbrev-ref HEAD) | $(git rev-parse --short HEAD) | $(pwd) | <one-line note>' | sudo tee -a /root/GB5BuildFile/DEPLOY_LOG.md > /dev/null"
```

Row format: `TIMESTAMP_UTC | HOST/SERVICE | GIT_BRANCH | GIT_COMMIT_SHA | WORKTREE_PATH | NOTE`. This is not optional — a deploy isn't done until this line is written.

---

## Critical Analysis & Verification Protocol

This protocol is **mandatory** for every task that generates or modifies C# code, SQL, or migrations. Skip no step.

### Phase 1 — Planning (before writing a single line)

**Step 1 — Schema introspection for every table you will touch:**
```sql
-- Run via gb5-schema MCP for each table in the task
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME = 'TINDENTDETAIL'   -- replace with actual table
ORDER  BY ORDINAL_POSITION
```
Do not proceed until you have confirmed every column name from the live schema or `GB5-Schema-Registry.md`. Never infer column names from DTO property names — they are aliases.

**Step 2 — Search existing QB files before writing new SQL:**
Use `gb5-filesystem` → `search_files` to find:
- All existing queries against the same table (reveals column usage patterns, join patterns, alias conventions)
- Whether the QB constant already exists in another module (avoid duplication)
- Cross-module usage: if a table appears in two modules, read BOTH QB files

**Step 3 — Critical analysis questions (answer all before coding):**
- Which columns are nullable vs NOT NULL? (schema query)
- Does `PENDINGQUANTITY` appear as a direct column or only via `TPENDINGALLOCATION`? (it is never a direct column on `TINDENTDETAIL`)
- Is `COMPANYID` a direct column on this table or does it require a `MORGANIZATIONUNIT` JOIN?
- Does the DTO property name match the DB column name exactly, or is it an alias?
- For shared tables (e.g. `TGATEENTRY` in both MM and PayRoll): which module is authoritative for each column's population?
- Is this a new table with no migration yet — if so, schema query will fail. Write migration first.

**Step 4 — Verify `IQueryExecutor` method choice:**
Valid methods: `QueryAsync<T>`, `QuerySingleAsync<T>`, `ExecuteAsync`, `ExecuteScalarAsync<T>`, `ExecuteIdentityAsync`, `StreamAsync<T>`, `QueryPagedAsync<T>`, `QueryMultiMapAsync`.
`QuerySingleOrDefaultAsync` does NOT exist — use `QueryAsync<T>` + `.FirstOrDefault()`.

---

### Phase 2 — Verification (after generating code, before reporting done)

**Step 1 — Run `dotnet build` on the affected module:**
```bash
dotnet build GB5Solution/MM/MMSL/MMSL.csproj   # replace with actual module
```
Zero errors required. Do not report completion with build errors.

**Step 2 — Verify the endpoint with the API MCP:**
For any new or modified FastEndpoint, call it live:
```
gb5_call_endpoint  GET  /mms/MMHead/GetMMHead  {MMHeadId: "1"}
```
Expected: `HTTP 200` with a populated `ResponseStandardDTO.Body`.
A `400` response means wrong parameter names — fix before reporting done.
A `404` means the route string is wrong — fix before reporting done.

**Step 3 — Cross-verify DTO ↔ DB column mapping:**
For every DTO property that maps to a DB column, confirm:
- The SELECT alias in the QB matches the DTO property name (case-insensitive for Dapper)
- The INSERT column list matches what you queried in Phase 1 Step 1
- No column appears in the INSERT that does not exist in `INFORMATION_SCHEMA.COLUMNS`

**Step 4 — Run Cypress spec if one exists for this entity:**
```
cypress_list_specs  filter="MMHead"
cypress_run_spec    "cypress/src/features/API_Tests/MM/MMHeadAPI.feature"
```
A failing spec that was previously passing is a regression — fix before reporting done.

---

## SQL Code Generation Safeguards

### Before writing any SQL constant

**1. Table schema verification — mandatory: use MCP before guessing**
- Query `gb5-schema` MCP: `SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'X' ORDER BY ORDINAL_POSITION`
- Fallback (offline): search `docs/GB5-Schema-Registry.md` for the table
- Final fallback: read the existing `INSERT INTO <TABLE>` query in the owning module's QB file
- Every column in the new query must be confirmed from one of these three sources — never infer

**2. Column name precision**
- DTO property names do NOT equal DB column names: e.g. `INDENTDETAILQUANTITY` (DB) vs `Quantity` (DTO alias)
- Never guess from property names alone; use `gb5-filesystem` to grep `*QB.cs` in the owning module

**3. IQueryExecutor method names**
- `QuerySingleOrDefaultAsync` does NOT exist — use `QueryAsync<T>` + `.FirstOrDefault()`
- Valid methods: `QueryAsync<T>`, `QuerySingleAsync<T>`, `ExecuteAsync`, `ExecuteScalarAsync<T>`, `ExecuteIdentityAsync`, `StreamAsync<T>`, `QueryPagedAsync<T>`, `QueryMultiMapAsync`

**4. Cross-module column ownership**
- A column that EXISTS on a table may be NULL if it's only populated by a different module
- Never assume populated; JOIN to the authoritative source table instead
- Always check both QB files when a table is shared across modules

**5. TPENDINGALLOCATION join pattern by entity type**
- TMMDETAIL: `pa.OBJECTDETAILID = d.DOCUMENTDETAILID` (no type ID filter needed for GB5 path)
- TINDENTDETAIL: `pa.OBJECTID = id_.INDENTDETAILID AND pa.OBJECTTYPEID = -1899997529 AND pa.OBJECTHEADERTYPEID = -1899997569`
- Never use a direct `PENDINGQUANTITY` column on TINDENTDETAIL — always get it from TPENDINGALLOCATION

**6. CompanyId source**
- TMMHEAD has `COMPANYID` directly
- TINDENT, TGATEENTRY, and other module document tables do NOT — get via `MORGANIZATIONUNIT.COMPANYID` joined on the document's OUID

**7. Quantity column names by table**
- TMMDETAIL: `QUANTITY`, `PENDINGQUANTITY` (direct columns)
- TINDENTDETAIL: `INDENTDETAILQUANTITY` (no PENDINGQUANTITY — use TPENDINGALLOCATION)
- TGATEENTRYDETAIL: `LOTQUANTITY` (no PENDINGQUANTITY — use LOTQUANTITY for both Quantity and PendingQuantity on load)

### After generating code — self-review checklist

- [ ] `gb5-schema` MCP queried for every table touched: columns confirmed
- [ ] `gb5-filesystem` searched for existing QB patterns on same table: no duplication
- [ ] Every table in FROM/JOIN: column names verified from schema, not guessed
- [ ] DTO property aliases match QB SELECT aliases exactly
- [ ] `PENDINGQUANTITY` on any table: confirmed it comes from TPENDINGALLOCATION join, not direct column
- [ ] `COMPANYID` source: TMMHEAD direct, or MORGANIZATIONUNIT join for all other document tables
- [ ] No `QuerySingleOrDefaultAsync` calls
- [ ] No `.Result` or `.Wait()` on async calls
- [ ] `ConfigureAwait(false)` on every `await` in BLL and DAL
- [ ] Tenant filter `AND TENANTID = @TenantId` on every table touched
- [ ] `dotnet build` passes — zero errors
- [ ] `gb5_call_endpoint` called on new/modified endpoints — HTTP 200 confirmed