# EIP — Complete Validation Report
**Date:** 2026-06-17  
**Scope:** GB5Framework · GB5Shared · EIPConversation.sql  
**Sources:** EIPSystem.md · eip-developer-guide.html · eip-admin-guide.html · eip_flow_and_guide.md · EIP_Full_SQLServer_Schema.sql · Live codebase  

---

## Part 1 — Corrected Database Script Summary

All corrections applied to `FrameworkDAL/Query/EIPConversation/Script/EIPConversation.sql`:

| # | Fix | Before | After |
|---|-----|--------|-------|
| 1 | `MEIPFLOWDEFINITION.PUBLISHEDBYID` — FK + NOT NULL DEFAULT -1 | `INT NOT NULL DEFAULT -1` + FK | `INT NULL` (FK stays; NULL is FK-safe) |
| 2 | `TEIPSCHEDULEDJOB.INTERACTIONSESSIONID` — FK + bad DEFAULT | `NOT NULL DEFAULT -1` + FK | `NOT NULL` (removed DEFAULT; always supplied) |
| 3 | `TEIPCONVERSATION.INTERACTIONSESSIONID` — FK + bad DEFAULT | `NOT NULL DEFAULT -1` + FK | `NOT NULL` (removed DEFAULT) |
| 4 | `TEIPIDEMPOTENCYRECORD.INTERACTIONSESSIONID` — may be absent | `NOT NULL DEFAULT -1` + FK | `INT NULL` (idempotency can run outside a session) |
| 5 | `TEIPIDEMPOTENCYRECORD.ACTIONID` — always required | `NOT NULL DEFAULT -1` + FK | `NOT NULL` (removed DEFAULT) |
| 6 | `LEIPAUDITEVENT.INTERACTIONSESSIONID` — optional in audit log | `NOT NULL DEFAULT -1` + FK | `INT NULL` |
| 7 | `LEIPAUDITEVENT.FLOWDEFINITIONID` — optional | `NOT NULL DEFAULT -1` + FK | `INT NULL` |
| 8 | `LEIPAUDITEVENT.CAPABILITYID` — optional | `NOT NULL DEFAULT -1` + FK | `INT NULL` |
| 9 | `LEIPAUDITEVENT.ACTIONID` — optional | `NOT NULL DEFAULT -1` + FK | `INT NULL` |
| 10 | `TEIPCONVERSATIONDETAIL` missing timestamp | No `CREATEDON` column | Added `CREATEDON DATETIME NOT NULL DEFAULT GETDATE()` |
| 11 | `MDIRECTACTION.DIRECTACTIONCODE` too narrow | `nvarchar(20)` | `nvarchar(50)` |
| 12 | `MDIRECTACTIONDETAIL.ACTIONCODE` too narrow | `nvarchar(20)` | `nvarchar(50)` |
| 13 | `TDIRECTACTIONTOKEN.ACTIONCODE` too narrow | `nvarchar(20)` | `nvarchar(50)` |

---

## Part 2 — Gap Analysis Report

### 2.1 Database Schema Gaps

| Gap | Impact | Recommendation |
|-----|--------|---------------|
| **No `MACTION.DIRECTACTIONID` FK constraint** — column exists but is a soft INT reference with no FK | Orphan references possible if MDIRECTACTION row deleted | Add FK constraint, or document the -1 sentinel pattern and enforce at BLL layer |
| **TEIPINTERACTIONSESSION never populated by current code** | Detailed execution-tracking table exists in schema but EIP engine only writes to TEIUSERSESSION | Either wire TEIPINTERACTIONSESSION writes in EIPFlowEngine, or remove it and use TEIUSERSESSION as the single session store |
| **TEIPSTEPEXECUTION, TEIPCONVERSATION, TEIPCONVERSATIONDETAIL — not written by current code** | Three tables in schema are never populated | Implement conversation logging in EIPFlowEngine (one row per step → TEIPSTEPEXECUTION; one conversation thread → TEIPCONVERSATION + detail) |
| **TEIPIDEMPOTENCYRECORD never used** | EIPActionEngine uses an in-memory `ConcurrentDictionary` for idempotency — not persisted, not multi-instance safe | Replace `_idempotencyStore` with DB-backed TEIPIDEMPOTENCYRECORD reads/writes |
| **TEIPEXTERNALACTIONPENDING never used** | EXTERNAL_ACTION step type is handled in-memory | Populate when a flow pauses on EXTERNAL_ACTION; poll/callback resolves it |
| **TEIPSCHEDULEDJOB never populated** | Background job table exists but no writer | Wire session-timeout cleanup job via Quartz or Dapr cron to read/write this table |
| **LEIPAUDITEVENT writes not confirmed** | Audit events may be dropped silently | Confirm EIPFlowEngine calls audit writer; add structured logging hook if absent |

### 2.2 Application Code Gaps

| Gap | Severity | File | Recommendation |
|-----|----------|------|---------------|
| **In-memory idempotency store (`_idempotencyStore`)** | HIGH | `EIPActionEngine.cs` | Replace with TEIPIDEMPOTENCYRECORD lookup/insert; use DB UNIQUE constraint as the race-condition guard |
| **EIP services registered only under `schedulerEnabled \|\| celitixEnabled`** | HIGH | `FrameworkSL/Program.cs:841` | Move EIP service registration to an independent `EIP:Enable` feature flag in appsettings so EIP can run standalone |
| **In-memory rate limiter** | MEDIUM | `EIPResponseEngine.cs` | Acceptable for single-instance. For multi-instance, replace with Redis distributed rate limiter or Dapr state store |
| **No webhook signature validation** | MEDIUM | `RequestEIPConversation.cs`, `ReceiveEIPConversation.cs` | Both endpoints are `AllowAnonymous`. Add: WhatsApp X-Hub-Signature-256 HMAC check; Teams HMAC; Telegram bot-token check |
| **`EIPFlowDTO.TenantId` is `string` but DB column is `INT`** | MEDIUM | `EIPFlowDTO.cs:5` | Normalizer casts `payload.TenantId` (string) to int for DAL queries; ensure consistent typing. `EIPUserSessionDTO.TenantId` is already `int` |
| **`POST /Action/Resend` endpoint missing** | MEDIUM | Admin guide §Security | Admin guide documents this endpoint for re-generating tokens. Not found in codebase. Implement in DirectAction BLL |
| **NeedsInput=1 `FLOWID` column unused** | LOW | `MDIRECTACTIONDETAIL.FLOWID` | Column reserved for EIP conversational flow integration. No code reads it yet; implement or remove |
| **`MEIPMESSAGETEMPLATE` never queried by EIPResponseEngine** | LOW | `EIPResponseEngine.cs` | Response engine uses `context.Message` directly. Localised templates in MEIPMESSAGETEMPLATE are not resolved. Implement template lookup before `handler.SendAsync()` |
| **OTP flow does not pause the flow engine** | LOW | `EIPCapabilityEngine.cs` | When OTP is required, capability engine returns "OTP sent…" string but does not save a step pause in TEIUSERSESSION. Next message from user won't be routed to the OTP verify step |

### 2.3 Configuration Gaps

| Gap | Where | Resolution |
|-----|-------|-----------|
| `DirectAction:TokenSecret` appsettings key — not validated to exist at startup | `DirectActionTokenService.cs` | Add startup validation; fail-fast if key missing or too short |
| `DirectAction:BaseUrl` — must be set for link generation | `DirectActionTokenService.cs` | Validate at startup; throw descriptive exception if blank |
| No EIP-specific health check endpoint | — | Add `/health/eip` that verifies DB connectivity to TEIUSERSESSION, MEIPFLOWDEFINITION, and MEIPROUTINGRULE |
| WhatsApp provider config (Celitix/Meta API key) stored in MEIPCHANNELENDPOINT.PROVIDERCONFIGJSON | `WhatsAppChannelHandler.cs` | Ensure secrets are stored in Azure Key Vault, not in DB plain text |

---

## Part 3 — Architecture Validation Report

### 3.1 Six-Phase Execution Engine — Status

| Phase | Component | Table(s) | Status |
|-------|-----------|----------|--------|
| 1 — Normalize | `EIPChannelNormalizer` | — | ✅ Implemented. Extracts UserIdentifier, FlowCode, sanitises message |
| 2 — Route | `EIPRoutingEngine` → `MEIPROUTINGRULE` | `TEIUSERSESSION`, `MEIPROUTINGRULE`, `MEIPFLOWDEFINITION` | ✅ Implemented. Session resume + routing rule priority match (EQUALS, STARTS_WITH, CONTAINS, REGEX) |
| 3 — Flow Engine | `EIPFlowEngine` | `TEIUSERSESSION`, `MEIPFLOWDEFINITION` | ✅ Implemented. 10 step types handled. TEIUSERSESSION UPSERT on each pause step |
| 4 — Capability | `EIPCapabilityEngine` | `MEIPCAPABILITY`, `MEIPCAPABILITYACTIONMAP`, `MEIPACTIONREGISTRY` | ✅ Implemented. Risk → OTP → Action chain. **Gap: OTP pause not saved to session** |
| 5 — Action | `EIPActionEngine` | `TEIPIDEMPOTENCYRECORD` *(not used)* | ⚠️ Implemented but idempotency is in-memory only |
| 6 — Response | `EIPResponseEngine` | `MEIPMESSAGETEMPLATE` *(not used)* | ⚠️ Implemented. Channel handlers correct. Message templates not resolved from DB |

### 3.2 Channel Support — Status

| Channel | Handler | ChannelType | Routing Rule | Admin Guide | Status |
|---------|---------|-------------|--------------|-------------|--------|
| WhatsApp (Celitix + Meta) | `WhatsAppChannelHandler` | 1 | ✅ | ✅ | ✅ Full support |
| Microsoft Teams | `TeamsChannelHandler` | 2 | ✅ | ✅ | ✅ Full support |
| Telegram | `TelegramChannelHandler` | 3 | ✅ | ✅ | ✅ Full support |
| Slack | `SlackChannelHandler` | 4 | ✅ | ✅ | ✅ Full support |
| SMS | `SmsChannelHandler` | 5 | ✅ | ✅ | ✅ Full support |
| Postman (testing) | `PostmanChannelHandler` | 0 | ✅ | ✅ | ✅ Full support |
| Email (DirectAction only) | via EmailActionHandler | — | — | ✅ | ✅ One-click approval only |

### 3.3 DirectAction Framework — Status

| Feature | Status | Notes |
|---------|--------|-------|
| HMAC-SHA256 token signing | ✅ | `DirectActionTokenService.cs` |
| 6-field token payload (actionCode:contextId:tenantId:userId:ticks:dbName) | ✅ | DB routing via dbName field |
| One-time-use enforcement | ✅ | TDIRECTACTIONTOKEN PK on TOKENHASH prevents duplicates |
| 48h expiry | ✅ | EXPIRESAT column; validated in token service |
| NeedsInput=0 direct click flow | ✅ | `GenericApiDirectActionHandler.cs` |
| NeedsInput=1 remarks form | ✅ | Token not recorded on GET; REMARKS stored on POST /Action/Submit |
| WhatsApp button prefix matching | ✅ | BUTTONPAYLOADPREFIX + "_" + ContextId |
| Email ##PLACEHOLDER## substitution | ✅ | EMAILPLACEHOLDER column drives template replacement |
| Revocation (STATUS=2) | ✅ | DB update; "revoked" page shown |
| Resend / re-issue tokens | ❌ MISSING | POST /Action/Resend documented but not implemented |
| Pool approval (one-per-approver) | ✅ | Documented and supported via one event per approver |

### 3.4 Multi-Tenancy — Status

| Aspect | Status |
|--------|--------|
| TENANTID FK → MCLIENT(CLIENTID) on all tables | ✅ |
| TENANTID = -1 for global/shared config rows | ✅ |
| Tenant-specific flow overrides global flow | ✅ (EIPFlowDAL checks tenant-specific first, falls back to -1) |
| Token payload includes tenantId for DB routing | ✅ |
| Session isolation by TENANTID in TEIUSERSESSION | ✅ |

### 3.5 Security — Status

| Control | Status | Notes |
|---------|--------|-------|
| HMAC-SHA256 signed tokens | ✅ | Fixed-time comparison |
| Tamper-evident token expiry (ticks in payload) | ✅ | |
| One-time-use token enforcement | ✅ | UNIQUE PK on TOKENHASH |
| WhatsApp webhook signature validation (X-Hub-Signature-256) | ❌ MISSING | Endpoint is AllowAnonymous with no header check |
| Teams HMAC webhook validation | ❌ MISSING | |
| Telegram bot token header check | ❌ MISSING | |
| Token secret in appsettings (not Key Vault) | ⚠️ | Should be Key Vault / environment secret, not appsettings.json |
| SQL injection prevention | ✅ | Parameterised queries via QueryBuilder pattern |

---

## Part 4 — End-to-End Flow Validation Report

### 4.1 WhatsApp Conversational Flow (Mode 2)

```
User sends "apply leave" on WhatsApp
│
├─ POST /EIPConversation/RequestEIPConversation  ← AllowAnonymous [⚠️ no signature check]
│    └─ EIPWebhookBLL.HandleWebhookAsync()
│
├─ PHASE 1: EIPChannelNormalizer.NormalizeAsync()
│    ✅ Extracts UserIdentifier (phone), TenantId, ChannelType, Message
│
├─ PHASE 2: EIPRoutingEngine.ResolveAsync()
│    ├─ Check TEIUSERSESSION WHERE USERIDENTIFIER=phone AND STATUS=1
│    │    AND LASTACTIVITYAT > DATEADD(minute,-30,GETUTCDATE())
│    │    [✅ uses IX_TEIUSERSESSION_LOOKUP]
│    ├─ No active session → query MEIPROUTINGRULE ORDER BY PRIORITY
│    │    [✅ uses IX_MEIPROUTINGRULE_LOOKUP]
│    └─ STARTS_WITH 'leave' → FLOWDEFINITIONID=5 → load MEIPFLOWDEFINITION.JSONDEFINITION
│
├─ PHASE 3: EIPFlowEngine.ExecuteAsync()
│    ├─ Parse JSON → EIPFlowDTO (StartStepCode + Steps dict)
│    ├─ Execute GREETING step (MESSAGE) → reply sent
│    ├─ Execute GET_START_DATE step (INPUT) → reply sent
│    ├─ PAUSE ← save TEIUSERSESSION (CURRENTSTEPCODE='GET_START_DATE')
│    │    [✅ MERGE UPSERT via EIPSessionQB.UPSERT_SESSION]
│    │
│    │ [user replies "2024-08-01"]
│    │
│    ├─ Resume from TEIUSERSESSION.CURRENTSTEPCODE='GET_START_DATE'
│    ├─ Validate input (regex ^\d{4}-\d{2}-\d{2}$)
│    ├─ Store in CONTEXTJSON: {"LeaveStartDate":"2024-08-01"}
│    ├─ Execute GET_END_DATE → PAUSE (saves session again)
│    │    [continues until SUBMIT step]
│    │
│    └─ SUBMIT step (ACTION, CALL_API)
│         [⚠️ Idempotency check is in-memory only]
│
├─ PHASE 4/5: EIPCapabilityEngine / EIPActionEngine
│    └─ POST /Leave/SaveLeave internally → returns reference number
│
└─ PHASE 6: EIPResponseEngine → WhatsAppChannelHandler
     └─ "✅ Leave submitted. Reference: LV-2024-0892"
     [⚠️ MEIPMESSAGETEMPLATE not consulted — message hardcoded in flow JSON]
```

**Result: PASS with warnings**  
Warnings: No webhook signature validation; in-memory idempotency; message templates not resolved from DB.

---

### 4.2 Email DirectAction Flow — NeedsInput=0 (Approve)

```
PurchaseOrderBLL.SavePO() called
│
├─ Publish ActionEventDto {DirectActionId=10, ContextId=5001, AssigneeUserId=42, ...}
│    → Dapr pubsub → EmailActionHandler
│
├─ EmailActionHandler:
│    ├─ Load MDIRECTACTION WHERE DIRECTACTIONID=10
│    ├─ Load MDIRECTACTIONDETAIL WHERE DIRECTACTIONID=10  (3 buttons)
│    ├─ For each button:
│    │    token_payload = "PO_APPROVE:5001:42:99:{ticks}:{dbName}"
│    │    signed_token  = Base64Url(payload) + "." + Base64Url(HMAC-SHA256(payload, secret))
│    └─ Replace ##APPROVE_URL##, ##REJECT_URL##, ##RETURN_URL## in email body
│
User clicks [Approve] → GET /Action/Execute?token=<signed_token>
│
├─ DirectActionTokenService.ValidateToken()
│    ├─ Split on "." → decode payload (6 fields) + signature
│    ├─ Recompute HMAC → fixed-time compare ✅
│    ├─ Check expiry (field 5 = ticks) ✅
│    └─ Parse contextId=5001, tenantId=42, assigneeUserId=99, dbName="goodbooks_prod_42"
│
├─ Compute SHA-256 hash of full token → TOKENHASH (64 chars)
├─ INSERT INTO TDIRECTACTIONTOKEN (TOKENHASH, CONTEXTID, ACTIONCODE, ..., STATUS=1, USEDAT=NOW)
│    [if duplicate key → "Already Actioned" page]
│
├─ GenericApiDirectActionHandler.ExecuteAsync()
│    ├─ Load MDIRECTACTIONDETAIL WHERE ACTIONCODE='PO_APPROVE'
│    ├─ Substitute {ContextId}=5001 in PAYLOADTEMPLATE
│    └─ POST /PurchaseOrder/ApprovePO  {"PurchaseOrderId":5001}
│
└─ Return "✅ Action Successful" page
```

**Result: PASS**

---

### 4.3 Email DirectAction Flow — NeedsInput=1 (Reject with Remarks)

```
User clicks [Reject] → GET /Action/Execute?token=<signed_token>
│
├─ Validate token (same as 4.2) ✅
├─ NeedsInput=1 detected
├─ Token NOT recorded (GET is safe to reload)
└─ Return HTML remarks form ("Reject Purchase Order" title, textarea)

User fills remarks "Over budget" → POST /Action/Submit {token, remarks}
│
├─ Validate token again ✅
├─ Compute TOKENHASH → check TDIRECTACTIONTOKEN for existing row
│    [if found → "Already Actioned"; prevents replay on resubmit]
├─ INSERT INTO TDIRECTACTIONTOKEN (..., STATUS=1, USEDAT=NOW, REMARKS="Over budget")
├─ Substitute {ContextId}=5001 and {Remarks}="Over budget" in PAYLOADTEMPLATE
└─ POST /PurchaseOrder/RejectPO  {"PurchaseOrderId":5001,"RejectionReason":"Over budget"}
└─ Return "✅ Action Submitted" page
```

**Result: PASS**

---

### 4.4 WhatsApp Button Press — Mode 1 (Template Approval)

```
User taps [Approve] button on WhatsApp template message
Button payload = "WF_APPROVE_5001"
│
├─ POST /MessageHubGenerator/RequestMessageHubGenerator
│    (existing MessageHub pipeline)
│
├─ EIPWebhookBLL detects button payload format (ACTION_prefix_contextId)
├─ Lookup MDIRECTACTIONDETAIL WHERE BUTTONPAYLOADPREFIX = 'WF_APPROVE'
├─ Extract ContextId = 5001 from suffix
│
├─ Call APIENDPOINT with substituted PAYLOADTEMPLATE
│    [⚠️ No token → no one-time-use enforcement — API must be idempotent]
│
└─ Reply to user: "Action completed"
```

**Result: PASS with caveat**  
WhatsApp buttons by design are not one-time-use; API idempotency is mandatory.

---

## Part 5 — Final Production-Ready Recommendation

### Priority 1 — Must Fix Before Production

| # | Issue | File | Action |
|---|-------|------|--------|
| P1-1 | **Webhook signature validation missing** | `RequestEIPConversation.cs`, `ReceiveEIPConversation.cs` | Add WhatsApp X-Hub-Signature-256 HMAC middleware; Teams HMAC; Telegram bot token |
| P1-2 | **EIP services behind wrong feature flag** | `FrameworkSL/Program.cs:841` | Add `EIP:Enable` key to appsettings; register EIP services independently of scheduler/celitix |
| P1-3 | **In-memory idempotency not production-safe** | `EIPActionEngine.cs` | Replace `_idempotencyStore` with TEIPIDEMPOTENCYRECORD DB reads/writes; use INSERT + catch duplicate-key as the atomic guard |
| P1-4 | **`DirectAction:TokenSecret` must be in Key Vault** | appsettings | Move to Azure Key Vault or environment variable; add startup validation (null/short check) |
| P1-5 | **Run the corrected EIPConversation.sql** | DB | Execute `EIPConversation.sql` — all tables with correct schema are now ready |

### Priority 2 — Should Fix Before Go-Live

| # | Issue | Action |
|---|-------|--------|
| P2-1 | OTP step does not pause flow | Save pause state in TEIUSERSESSION with `CURRENTSTEPCODE='OTP_VERIFY'`; next message routes to OTP verification |
| P2-2 | `MEIPMESSAGETEMPLATE` not used | In EIPResponseEngine, before `handler.SendAsync()`, lookup localised template by `(TenantId, MessageKey, LanguageCode, ChannelType)` |
| P2-3 | `POST /Action/Resend` not implemented | Add endpoint in DirectAction BLL; generate new tokens for a contextId + directActionId |
| P2-4 | Conversation logging not wired | In EIPFlowEngine, write rows to `TEIPCONVERSATION` + `TEIPCONVERSATIONDETAIL` at each step |
| P2-5 | Step execution tracking not wired | Write rows to `TEIPSTEPEXECUTION` in EIPFlowEngine step handler methods |

### Priority 3 — Recommended Enhancements

| # | Enhancement | Value |
|---|-------------|-------|
| P3-1 | Distributed rate limiter (Redis/Dapr) | Required for multi-instance EIP deployment |
| P3-2 | MEIPCHANNELENDPOINT.PROVIDERCONFIGJSON encryption | Keep WhatsApp/provider secrets encrypted at rest |
| P3-3 | Flow Designer UI (DSL editor) | `dsldesign.html` in the docs is an admin console prototype; integrate with MEIPFLOWDEFINITION save API |
| P3-4 | `TEIPSCHEDULEDJOB` writer + Quartz job | Schedule session-timeout cleanup at EXECUTEATON |
| P3-5 | Health check endpoint `/health/eip` | Verify TEIUSERSESSION, MEIPFLOWDEFINITION, MEIPROUTINGRULE tables accessible at startup |
| P3-6 | Remove `TEIPCONVERSATION.SESSIONTOKEN` redundancy | Same value already in `TEIPINTERACTIONSESSION`; remove to avoid drift |

---

## Summary Scorecard

| Area | Status | Critical Issues |
|------|--------|----------------|
| **DB Schema** | ✅ Fixed | 9 FK bugs corrected; 3 column widths fixed; CREATEDON added |
| **EIP Channel Support** | ✅ All 6 channels | No gaps |
| **DirectAction (Email)** | ✅ Production-ready | Resend endpoint missing |
| **DirectAction (WhatsApp)** | ✅ Production-ready | API must be idempotent |
| **Conversational Flow Engine** | ⚠️ Functional | OTP pause not saved; message templates not resolved |
| **Session Management** | ✅ Correct | TEIUSERSESSION works correctly |
| **Idempotency** | ❌ In-memory only | Must move to TEIPIDEMPOTENCYRECORD before production |
| **Security** | ❌ Missing webhook validation | Add before any public webhook goes live |
| **Multi-tenancy** | ✅ Correct | All tables tenant-isolated |
| **Audit Trail** | ⚠️ Partial | LEPAUDITEVENT schema correct; writes need verification |
| **Scalability** | ⚠️ Single-instance safe | Rate limiter and idempotency need distributed backing store |