# MessageHub System — Full Technical Documentation

---

## Table of Contents

1. [System Overview](#1-system-overview)
2. [Architecture Layers](#2-architecture-layers)
3. [Full Flow — What It Does Today](#3-full-flow--what-it-does-today)
4. [Layer-by-Layer Breakdown](#4-layer-by-layer-breakdown)
5. [Platform Implementations](#5-platform-implementations)
6. [Database Schema Reference](#6-database-schema-reference)
7. [Current Issues & Gaps](#7-current-issues--gaps)
8. [Dynamic Patching — How It Works & What to Improve](#8-dynamic-patching--how-it-works--what-to-improve)
9. [Improvement Recommendations](#9-improvement-recommendations)
10. [Configuration Reference](#10-configuration-reference)

---

## 1. System Overview

MessageHub is a fully dynamic, multi-platform notification engine built for a large-scale ERP system. It is designed to send messages across WhatsApp, Teams, Telegram, Slack, SMS, and Email — all driven by database-configured templates, without any hardcoded message content in code.

The core idea is simple:

- A business module (screen/entity) triggers a notification by calling the API with only an `EntityId`.
- The system looks up all active templates configured for that entity.
- It fetches contextual data for the logged-in user from the database.
- It patches the template placeholders with real data.
- It routes the message to the correct platform and sends it.

This means adding a new notification for any screen in the ERP requires only database configuration — no code change.

---

## 2. Architecture Layers

```
API Request (EntityId + Login Header)
        |
        v
  [SL] GenerateMessageHubEndpoint          FastEndpoints POST /MessageHubGenerator/Generate
        |
        v
  [BLL] MessageHubGeneratorBLL             Orchestrates fetch → send → fallback
        |
        +---------> [DAL] MessageHubGeneratorDAL    Fetches templates + patches data
        |
        +---------> [Engine] MessageHubGeneratorEngine    Routes by ActionType
                        |
                        +---> PostmanPlatform       ActionType 0  (debug/test)
                        +---> WhatsAppPlatform      ActionType 7  (Celitix / Meta)
                        +---> TeamsPlatform         ActionType 8
                        +---> TelegramPlatform      ActionType 9
                        +---> SlackPlatform         ActionType 10
                        +---> SMSPlatform           (conversation only)
                        +---> EmailPlatform         (conversation only)
```

---

## 3. Full Flow — What It Does Today

### Step 1 — API Entry

The caller sends a POST to `/MessageHubGenerator/Generate` with:

- `Login` header (resolved to `LoginDTO` — contains `UserId`, `ClientId`, `TenantId`, `UserName`)
- Request body: `EntityId` (integer)

Validation at endpoint level:

- `EntityId` must not be 0
- `Login` header must not be null or empty

### Step 2 — BLL Orchestration (MessageHubGeneratorBLL)

The BLL receives `EntityId` and `LoginDTO`. It calls the DAL to generate a list of `MessageHubDTO` objects — one per template-action combination found for that entity.

If no messages are generated, it returns early with `Success = false`.

For each message returned:

- Sets `ReferenceId` from `TemplateId`
- Sends to `MessageHubGeneratorEngine.ProcessAsync()`
- Collects result into `MessageHubExecutionResultDTO.Results` (list of `MessageChannelResultDTO`)
- Tracks failed messages separately

After all messages are processed, any failed messages are published to the **OutBox** (Dapr-based fallback) for retry.

Final result:

- `Success = true` if at least one message succeeded
- `Success = false` if all messages failed
- `ErrorMessage` summarizes partial or full failure

### Step 3 — DAL Processing (MessageHubGeneratorDAL)

This is the core intelligence of the system.

**Step 3a — Fetch Templates**

Runs `GET_TEMPLATES_BY_ENTITYID` query. This joins:

```
MEVENTTYPE → MENTITY → MMAILTEMPLATE → MACTION → MEVENTTYPEACTION → MEVENTTYPEACTIONDETAIL
```

Returns all active templates for the EntityId, including:

- `MailTemplateId`, `MailTemplateCode`, `MailTemplateName`
- `Subject` (template subject / WhatsApp template name)
- `BodyHtml` (the template body with `##PLACEHOLDER##` variables)
- `ActionId`, `ActionType` (platform routing integer)
- `EventTypeId`, `EventTypeCode`
- `IsDirectCall`, `IsSchedulerCall`

**Step 3b — Resolve Data Payload**

Runs `GET_MESSAGE_HUB_PATCH_QUERY` using the logged-in `UserId`. This is a large context query joining ~15 tables:

- `MUSER` — user base info (email, mobile, work OU, language, etc.)
- `MEMPLOYEE` — employee details (name, department, designation, joining date, etc.)
- `MCONTACT` — contact details linked to employee
- `MORGANIZATIONUNIT` — the user's working OU
- `MORGANIZATIONGROUP` — OU's organization group
- `MPARTY` / `MPARTYBRANCH` — party/vendor/customer linked to user's work branch
- `MPERIOD` — current work period
- `MADDRESS` — default address of the party branch
- `MLOCATION` — geo-location of the address
- `MCITY` / `MSTATE` / `MCOUNTRY` — city/state/country chain
- `TCALL` (OUTER APPLY TOP 1) — latest service call for the employee
- `TVISITORPASS` (OUTER APPLY TOP 1) — latest visitor pass for the employee
- `TVISITORPUNCH` (OUTER APPLY TOP 1) — latest punch for that visitor pass

The result is a single `MessageHubContextDTO` mapped to a flat dictionary of ~120+ key-value pairs. Every column alias in the query becomes a key in this dictionary.

**Step 3c — Placeholder Extraction**

For each template, the system scans `BodyHtml` for `##VARIABLENAME##` patterns using a regex:

```
Regex.Matches(template.BodyHtml, @"##(.*?)##")
```

Only the variables actually used in the template are kept from the payload dictionary. This filtered dictionary becomes the `Parameters` of the message.

Two keys are always kept regardless of whether they appear in the template:

- `UserPrimaryMobile` — used as WhatsApp recipient
- `UserPrimaryMail` — used as email recipient

**Step 3d — Template Patching**

`PatchTemplateJson()` replaces every `##KEY##` in `BodyHtml` with the matching value from the filtered payload:

```
"Hello ##EmployeeName##, your call ##CallReferenceNumber## is assigned."
→
"Hello Rajan Kumar, your call CALL-2024-00123 is assigned."
```

`PatchTemplate()` does the same for the `Subject` field.

**Step 3e — Recipient Resolution**

Priority order:

1. Look inside the patched JSON for a `"to"` field
2. Fall back to `UserPrimaryMobile` from payload
3. Fall back to `UserPrimaryMail` from payload

**Step 3f — Mobile Number Normalization**

Mobile numbers are normalized to Indian format (country code 91):

- 10-digit → `91` + digits
- Numbers starting with `0` (11 digits) → strip leading 0, prepend `91`
- Numbers starting with `00` → strip `00`
- Already `91XXXXXXXXXX` (12 digits) → kept as-is

**Step 3g — Build MessageHubDTO**

Each template becomes a `MessageHubDTO` with:

- `ActionId`, `ActionType` — for routing
- `TemplateId`, `TemplateName`
- `Subject` — patched subject (used as WhatsApp template name)
- `MessageText` — human-readable text extracted from patched body
- `Recipient` — resolved phone/email
- `Parameters` — filtered key-value payload
- `Payload` — same as Parameters
- `TenantId`, `CreatedById`, `ModifiedById`, audit timestamps

### Step 4 — Engine Routing (MessageHubGeneratorEngine)

The engine receives a `MessageHubDTO` and routes by `ActionType`:

| ActionType | Platform | Notes |
|---|---|---|
| 0 | Postman | Debug/test — logs only, always returns success |
| 7 | WhatsApp | Celitix or Meta based on config flag |
| 8 | Teams | Webhook-based |
| 9 | Telegram | Bot API |
| 10 | Slack | Incoming Webhook |

The engine wraps execution in a Polly retry policy: 3 retries with exponential backoff (2s, 4s, 8s). Retries are logged with ActionType, Recipient, and TemplateId context.

### Step 5 — Platform Send

Each platform has two modes — Template and Conversation. The Generator flow always uses the Template mode.

**WhatsApp (ActionType 7)**

Supports two providers, selected by config flag:

- **Celitix** — custom header-based auth (`Key`, `WabaNumber` headers). Builds WhatsApp template payload with dynamic body parameters from `dto.Parameters`. Sends to Celitix REST endpoint.
- **Meta (Graph API)** — Bearer token auth. Sends to `https://graph.facebook.com/v18.0/{PhoneNumberId}/messages`. Same template structure.

Both build the WhatsApp template payload structure:

```json
{
  "messaging_product": "whatsapp",
  "to": "<recipient>",
  "type": "template",
  "template": {
    "name": "<TemplateName from Subject>",
    "language": { "code": "en" },
    "components": [{
      "type": "body",
      "parameters": [ { "type": "text", "text": "<value>" }, ... ]
    }]
  }
}
```

Both have their own retry policy (3 retries, exponential backoff) at the HTTP level.

**Teams (ActionType 8)**

Sends a simple JSON payload to a configured webhook URL:

```json
{ "text": "📢 TEMPLATE MESSAGE\n\n<MessageText>\n\nSent By: <UserName>" }
```

**Telegram (ActionType 9)**

Sends to Bot API `/sendMessage` endpoint. Supports:

- Plain text with `parse_mode: HTML`
- Pre-formed `PayloadJson` (bypasses text building, sends raw JSON directly to Telegram)
- Defaults `chat_id` to `DefaultRecipient` from config if recipient is empty

**Slack (ActionType 10)**

Sends to Incoming Webhook URL. Supports configurable `channel`, `username`, `icon_emoji`. Template messages prepend `*📢 TEMPLATE MESSAGE*`.

**Postman (ActionType 0)**

No actual send — logs Recipient, MessageText, and sender info. Returns success always. Used for testing and internal/debug flows.

**Email**

Not routed through the Generator Engine (no ActionType assigned). Exists as `IEmailPlatform`. Uses `System.Net.Mail.SmtpClient` with config-driven SMTP settings.

**SMS**

Not routed through the Generator Engine (no ActionType assigned). Exists as `ISMSPlatform`. Posts to a configured API URL with ApiKey, SenderId, Recipient, and message text.

### Step 6 — OutBox Fallback

After all messages are processed, any that failed are published to the OutBox (Dapr pub/sub):

```csharp
new OutboxDTO {
    ObjectTypeId = -1,
    ObjectId = f.TemplateId ?? 0,
    EventTypeId = -1,
    Version = 1,
    Payload = f
}
```

The OutBox subscriber is expected to retry delivery asynchronously.

---

## 4. Layer-by-Layer Breakdown

### GenerateMessageHubEndpoint (SL)

- Type: FastEndpoints `BaseEndpoint`
- Route: `POST /MessageHubGenerator/Generate`
- Auth: `AllowAnonymous` (Login resolved from header, not JWT)
- Input: `EntityId` (body), `Login` (header)
- Output: `ResponseStandardDTO<object>` wrapping `MessageHubExecutionResultDTO`
- Responsibilities: Input validation, logging, exception wrapping, delegate to BLL

### MessageHubGeneratorBLL

- Dependency: `IMessageHubGeneratorDAL`, `IMessageHubGeneratorEngine`, `IOutBox`
- Retry: Polly 3x exponential on DAL fetch only
- Responsibilities: Orchestrate DAL fetch, loop messages, collect results, publish failed to OutBox
- Returns: `MessageHubExecutionResultDTO` (Success flag + per-message `MessageChannelResultDTO` list)

### MessageHubGeneratorDAL

- Dependency: `IQueryExecutor`
- Key Methods:
  - `GenerateMessagesByEntityAsync` — main entry
  - `ResolvePayloadAsync` — runs the big context query
  - `ConvertDtoToDictionary` — reflection-based DTO to dictionary
  - `PatchTemplateJson` / `PatchTemplate` — regex placeholder replacement
  - `NormalizeMobileNumbers` — formats mobile numbers
  - `ExtractRecipientFromJson` — pulls `"to"` field from patched JSON
  - `ConvertJsonToText` — extracts readable text from patched body JSON

### MessageHubGeneratorEngine

- Dependency: All platform interfaces
- Retry: Polly 3x exponential per message send
- Routing: Integer switch on `ActionType`
- Returns: `MessageHubResponseDTO` (Success, Message, ResponseData)

---

## 5. Platform Implementations

| Platform | Class | Auth Method | Template Support | Conversation Support |
|---|---|---|---|---|
| Postman | PostmanPlatform | None (debug only) | Yes | Yes |
| WhatsApp (Celitix) | WhatsAppPlatform | Custom headers | Yes | Yes |
| WhatsApp (Meta) | WhatsAppPlatform | Bearer token | Yes | Yes |
| Teams | TeamsPlatform | Webhook URL | Yes | Yes |
| Telegram | TelegramPlatform | BotToken | Yes | Yes |
| Slack | SlackPlatform | Webhook URL | Yes | Yes |
| Email | EmailPlatform | SMTP credentials | No | Yes |
| SMS | SMSPlatform | API Key | No | Yes |

All platforms implement `IMessageHubPlatform` sub-interfaces. All return `MessageHubResponseDTO` or void (Email/SMS throw on failure).

---

## 6. Database Schema Reference

### Template Configuration Tables

| Table | Purpose |
|---|---|
| `MENTITY` | Entity registry — maps EntityId to module/screen |
| `MEVENTTYPE` | Event types per entity (e.g., CallAssigned, VisitorCheckin) |
| `MMAILTEMPLATE` | Template definitions — Subject, BodyHtml, linked to EventType |
| `MACTION` | Actions — ActionType (platform) linked to template and event |
| `MEVENTTYPEACTION` | Maps EventType to Action groups |
| `MEVENTTYPEACTIONDETAIL` | Detail: IsDirectCall, IsSchedulerCall, active flag |

### Message Tracking Table

| Table | Purpose |
|---|---|
| `TMESSAGEHUB` | Stores each sent/queued message with DeliveryStatus, SourceType, TenantId, audit fields |

### Context / Patch Data Tables (Read-Only in Generator)

MUSER, MEMPLOYEE, MCONTACT, MORGANIZATIONUNIT, MORGANIZATIONGROUP, MPARTY, MPARTYBRANCH, MPERIOD, MADDRESS, MLOCATION, MCITY, MSTATE, MCOUNTRY, TCALL, TVISITORPASS, TVISITORPUNCH

---

## 7. Current Issues & Gaps

### Critical Issues

**1. TemplateID and TicketNumber flow as 0 / null from Webhook**
In `MessageHubWebhookBLL`, `TemplateID = 0` and `TicketNumber = null` are hardcoded with TODO comments. These flow all the way to SQL update queries where they are used as lookup keys. This means approval actions (Accept/Reject/Forward) currently update zero rows silently.

**2. No confirmation message sent back to WhatsApp user after approval**
`ConvertToEIPResponseContext()` is built but never used. After an approval button click is processed, no reply is sent to the user.

**3. Hardcoded TenantId `-1399999958` in WebhookBLL**
A magic number that breaks for any tenant other than the one it was hardcoded for.

**4. Hardcoded FlowCode `"WA_TICKET_ASSIGNMENT"` in WebhookBLL**
Belongs in config or database, not in code.

### Design Issues

**5. Triple DTO Mapping in TemplateApprovalBLL**
`TemplateApprovalDTO` → `CallApprovalDTO` → `TemplateApprovalDTO (DAL)`. The same data mapped three times. The BLL internal DTO adds no value here.

**6. Duplicate `ApprovalAction` enum across DAL and BLL namespaces**
Two enums for the same concept with switch-mapping between them. Should be a single shared enum in `GB5Shared`.

**7. DAL rows-affected return value ignored**
`AcceptTemplateApproval`, `RejectTemplateApproval`, `ForwardTemplate` all return `int` (rows affected). The BLL discards this. A zero-row update (wrong key, already processed) passes silently.

**8. `BodyHtml` treated as JSON**
`PatchTemplateJson` and `ConvertJsonToText` assume the template body is a JSON string. If it is HTML, JSON parsing fails silently and raw content is sent as message text.

**9. Reflection on 100+ property DTO on every request**
`ConvertDtoToDictionary` calls `GetProperties()` each time on `MessageHubContextDTO`. For high-frequency calls this is unnecessary overhead.

**10. Mobile normalization is India-specific**
Country code `91` is hardcoded. International deployments will fail.

**11. `CancellationToken.None` hardcoded in TemplateApprovalBLL**
The upstream cancellation token is not propagated to the EIP engine call.

**12. Dual retry stack**
Both `MessageHubGeneratorBLL` (wraps DAL fetch) and `MessageHubGeneratorEngine` (wraps platform send) have independent Polly retry policies. The WhatsApp platform also has its own HTTP-level retry. A single failed send could technically trigger up to 9 attempts. This needs clear documentation and deliberate layering.

**13. Logging at INFO level with full DTO serialization**
Multiple BLL classes log complete DTO JSON at `LogInformation`. In production this floods logs and risks exposing PII (mobile numbers, email addresses, user IDs).

---

## 8. Dynamic Patching — How It Works & What to Improve

### Current Approach

The system is already highly dynamic. The core loop is:

```
EntityId
  → GET all templates for entity from DB
  → GET user context data (single large query, ~120 columns)
  → Per template: extract ##KEYS## used in body, filter context dict, replace ##KEYS## with values
  → Send
```

This means you can add a new template for any EntityId with any combination of placeholders — no code change required. The 120+ column context query covers virtually all standard ERP data points (user, employee, OU, party, address, location, latest call, visitor pass).

### The Core Problem for Large ERP (5000 Tables)

The current patch query is hardcoded to a single user-centric JOIN chain. It covers:

- The logged-in user and their employee record
- Their working OU, party branch, period, address
- Their latest service call
- Their latest visitor pass

This is sufficient for ~50-100 standard notification scenarios. But for a 5000-table ERP with potentially hundreds of module-specific data points, the single query approach breaks down in two ways:

1. **Data not in the query** — if a template for a Purchase Order needs `PONumber`, `SupplierName`, `TotalAmount`, none of those exist in the current patch query.
2. **Object-specific context** — the notification is triggered not just by a user but by a specific business object (a Call, a PO, a Ticket, an Invoice). The patch query needs to know which object to fetch data for.

### What to Do — Dynamic Context Extension

The recommended approach is to move from a single fixed query to a registered, object-aware context resolver system.

**Option A — Context Query Registry (Recommended for your architecture)**

Add a table `MMESSAGEHUBCONTEXTQUERY`:

| Column | Type | Purpose |
|---|---|---|
| `CONTEXTQUERYID` | INT PK | Identity |
| `EVENTTYPEID` | INT FK | Links to MEVENTTYPE |
| `OBJECTTYPEID` | INT | The business object type (Call, PO, Invoice, etc.) |
| `QUERYTEXT` | NVARCHAR(MAX) | The SQL query to run for this context |
| `PARAMETERSOURCE` | VARCHAR(50) | Where to get the query parameter: `UserId`, `ObjectId`, `PartyId`, etc. |
| `STATUS` | INT | Active/inactive |

When generating messages for an event type, if a context query is registered for it, run that query with the appropriate parameter, and merge the result into the base payload. Template placeholders can then reference columns from both the base user query and the object-specific query.

This requires passing `ObjectId` along with `EntityId` to the endpoint — which is a one-line change to the request model.

**Option B — JSON Payload Injection at Call Site**

Instead of fetching context from DB, the caller passes an additional `ExtraPayload` dictionary in the request body. The DAL merges this into the base payload before patching.

This gives immediate flexibility for any module to inject module-specific values without any DB config change. The downside is that callers must know what placeholders the template needs.

**Option C — Stored Procedure per EventType**

Each EventType has a registered stored procedure name. The DAL calls the procedure with `ObjectId` and `UserId`, and the procedure returns a single result row with named columns matching placeholder names. This is very flexible and keeps SQL out of the application layer entirely.

### Current Placeholder Convention

All placeholders use `##COLUMNNAME##` where `COLUMNNAME` must exactly match (case-insensitive) a key in the resolved payload dictionary. This convention is correct and should be kept.

The column aliases in `GET_MESSAGE_HUB_PATCH_QUERY` are the authoritative list of available placeholders. Any new context query added via the registry must follow the same aliasing convention.

### Template Body Format

Currently `BodyHtml` is treated as JSON in the DAL (`PatchTemplateJson`, `ExtractRecipientFromJson`, `ConvertJsonToText`). This implies the expected format is:

```json
{
  "to": "##UserPrimaryMobile##",
  "body": "Hello ##EmployeeName##, your request ##CallReferenceNumber## has been assigned.",
  "buttons": [
    { "title": "Accept", "payload": "ACCEPT_##CallObjectId##" },
    { "title": "Reject", "payload": "REJECT_##CallObjectId##" }
  ]
}
```

This is the correct format for a universal template that works across platforms. The `"to"` field drives recipient resolution. The `"body"` drives the message text. The `"buttons"` array enables interactive messages. This format should be formally documented as the template standard and validated on save in the UI.

---

## 9. Improvement Recommendations

### Priority 1 — Fix Breaking Issues

1. Parse `TemplateID` and `TicketNumber` dynamically from the webhook button payload instead of hardcoding `0` and `null`. Payload format should be `ACCEPT_<TemplateId>` or `ACCEPT_<TicketNumber>` — split on `_` to extract the ID.

2. Complete `ConvertToEIPResponseContext` usage — after approval processing, call the WhatsApp platform to send a confirmation reply to the user.

3. Move `TenantId` and `FlowCode` to configuration or database. Never hardcode in business logic.

### Priority 2 — Architecture Cleanup

4. Create a shared `ApprovalAction` enum in `GB5Shared` to eliminate the dual-namespace enum problem and the mapping boilerplate between them.

5. Validate DAL rows-affected return. If zero rows updated after Accept/Reject/Forward, log a warning and return a structured error — do not silently succeed.

6. Change all INFO-level full DTO serialization logs to DEBUG level. Log only key identifiers (TemplateId, EntityId, Recipient mask) at INFO.

7. Propagate `CancellationToken` from the endpoint down through every async call chain including EIP engine calls.

### Priority 3 — Dynamic Patching Extension

8. Add `ObjectId` and `ObjectTypeId` to the `GenerateMessageHub` request. Pass them through to the DAL so context queries can be object-aware rather than only user-aware.

9. Implement the `MMESSAGEHUBCONTEXTQUERY` registry table. When generating messages, if a context query exists for the EventType, run it and merge results into the base payload.

10. Cache `typeof(MessageHubContextDTO).GetProperties()` statically to eliminate per-request reflection overhead.

11. Extract mobile normalization to a configurable utility that reads country code from tenant settings rather than hardcoding `91`.

12. Add template body format validation on save — enforce that `BodyHtml` is valid JSON matching the expected schema (`to`, `body`, optional `buttons`). This prevents silent failures in `ConvertJsonToText`.

### Priority 4 — Retry Policy Cleanup

13. Document explicitly which layer owns retries. Recommended: HTTP-level retry in platform (handles transient network errors), no retry at Engine level, no retry at BLL level for sends. Keep BLL retry only for DAL fetch (database connection issues).

14. Add `OutBox` failure handling — if `Task.WhenAll(fallbackTasks)` throws, catch and log per-item failures rather than losing the entire batch.

---

## 10. Configuration Reference

### MessageHubSettings

```json
{
  "MessageHub": {
    "WhatsApp": {
      "AuthToken": "",
      "PhoneNumberId": "",
      "DefaultLanguageCode": "en_US"
    },
    "Teams": {
      "WebhookUrl": ""
    },
    "Telegram": {
      "BotToken": "",
      "DefaultRecipient": "",
      "BaseUrl": "https://api.telegram.org"
    },
    "Slack": {
      "WebhookUrl": "",
      "DefaultChannel": "",
      "DefaultUserName": "MessageHub",
      "DefaultIconEmoji": ":robot_face:"
    }
  }
}
```

### CelitixSettings

```json
{
  "Celitix": {
    "Enable": "Y",
    "BaseUrl": "",
    "MessageEndpoint": "",
    "Key": "",
    "KeyHeaderName": "",
    "WabaNumber": "",
    "WabaNumberHeaderName": "",
    "ContentType": "application/json"
  }
}
```

### MetaSettings

```json
{
  "Meta": {
    "Enable": "N",
    "PhoneNumberId": "",
    "AuthToken": "",
    "DefaultTemplateName": "",
    "DefaultLanguageCode": "en_US"
  }
}
```

### EmailSettings

```json
{
  "Email": {
    "Host": "",
    "Port": 587,
    "Username": "",
    "Password": "",
    "EnableSSL": true,
    "FromEmail": "",
    "FromName": "MessageHub"
  }
}
```

### SMSSettings

```json
{
  "SMS": {
    "ApiUrl": "",
    "ApiKey": "",
    "SenderId": "",
    "FromNumber": ""
  }
}
```

---

## Summary

The MessageHub system is architecturally sound for its intended purpose — a fully database-driven, multi-platform notification engine. The core concept of entity-driven template resolution with dynamic placeholder patching is correct and scalable for standard ERP notifications.

The most critical gaps today are the webhook approval flow (broken TemplateID/TicketNumber), the incomplete EIP reply after approval, and the hardcoded tenant values. These should be fixed before production use of the approval flow.

For long-term scalability across 5000 tables and 50+ modules, the context query registry approach (Option A above) is the recommended path. It keeps the system fully dynamic and configuration-driven without requiring code changes for any new notification scenario.