PromptForge Studio

You are conducting a routine health, business rule, and data integrity audit for the PHCC Appointment SMS Pipeline (Azure Functions C# + Python simulator parity) for the date range: 2026-08-01T00:00:00Z to 2026-08-03T23:59:59Z (or Date: 2026-08-03).
Your task is to gather evidence across all pipeline telemetry, databases, messaging queues, and Dataverse CRM, verify that business rules are working as intended, and report any anomalies, failures, or risks.
════════════════════════════════════════════════════════════════════
0. NON-NEGOTIABLE GROUND RULES & PRE-REQUISITES
════════════════════════════════════════════════════════════════════
READ-ONLY EXECUTION ONLY: Do NOT run any mutation scripts (requeue, purge, clear, drain, or write back) during routine monitoring.
ENVIRONMENT BOUNDARY: NEVER inspect production roles fnc-sms-prd-qc / fnc-sms-prd-qc-secondary.
EVERY App Insights query MUST filter: cloud_RoleName == 'fnc-sms-tst-qc'.
AZ CLI TIMINGS: ALWAYS pass explicit --start-time/--end-time in ISO 8601 UTC format. Always use -o json output.
SCRIPT EXECUTION LOCATION: Run all Python tools from supporting-scripts/ so pipeline imports resolve correctly.
TIMEZONE CONTEXT:
Telemetry & Logs: UTC
Nightly receiver batch: ~21:00 UTC (Midnight Qatar LOCAL / UTC+3).
SQL senttime: Qatar LOCAL time stored as varchar. Parse with:
TRY_CONVERT(datetime2, LEFT(CAST(senttime AS varchar(40)), 19))
"SENT" GROUND TRUTH DEFINITION:
A message is officially "Sent" ONLY when BOTH exist:
a) App Insights trace: 'Stage-5:Sent-Complete'
b) SQL database row: dbo.custom_sms_MsgOut_Appointments
EVIDENCE CLASSIFICATION: Label every finding as either FACT (verified by log/DB evidence) or HYPOTHESIS.
════════════════════════════════════════════════════════════════════
AUTHENTICATION & SELF-TEST (Zero-Interaction)
════════════════════════════════════════════════════════════════════
Verify connectivity across all 4 control planes before running the audit:
App Insights:
az account set --subscription 31339e0a-0e17-4a58-bb40-e4cfe91e9a19
az account show -o json
Dataverse & Service Bus:
python diagnostics/check_permissions.py
Azure SQL MSAL Auth:
python -c "from pipeline.auth_utils import load_msal_cache, get_access_token; print(get_access_token(load_msal_cache())[:40])"
════════════════════════════════════════════════════════════════════
2. STEP-BY-STEP MONITORING AUDIT SEQUENCE
════════════════════════════════════════════════════════════════════
STEP 1: DAILY HEALTH REPORT & RAG STATUS EVALUATION
Generate full operational health artifact for the targeted window:
python diagnostics/daily_health_report.py --date 2026-08-03 --out auto
For multi-day window:
python diagnostics/daily_health_report.py --last 24h --json
STEP 2: SERVICE BUS QUEUE & DLQ CENSUS
Check depth, dead-letter accumulation, and broker properties across input, high, and standard queues:
python diagnostics/manage_input_queue.py --count
python diagnostics/verify_dlq_count.py
python diagnostics/inspect_dlq.py
python diagnostics/analyze_pending_dlq.py
STEP 3: UPSTREAM FEED SYNC & PRE-FLIGHT CHECK
Verify that all pending Dataverse mappings have matching hmc.dbo.AppointmentBookings rows (feed gap detection):
python diagnostics/check_booking_sync.py --days 3
STEP 4: DUPLICATE PROTECTION & STATUS ANOMALY DETECTOR
Audit Dataverse status resets and ensure duplicate guard (US-DUP-01: EligibilitySkip/SendHistoryExists) is actively preventing re-sends:
python diagnostics/dv_status_reset_detector.py --days 3 --check-guard --json
STEP 5: TELEMETRY & DISPOSITION ANALYSIS (App Insights)
Analyze execution counts, dispositions, stage transitions, and pipeline exceptions:
python diagnostics/appinsights_query.py --preset dispositions --last 3d
python diagnostics/appinsights_query.py --preset exceptions --last 3d
python diagnostics/appinsights_query.py --preset stage5 --last 3d
STEP 6: DELIVERY RECONCILIATION & SQL AUDIT
Cross-check handed-off SMS messages against gateway delivery tables:
python diagnostics/db_query.py --db SMS --sql "SELECT COUNT(*) as TotalSent, MIN(senttime) as FirstSMS, MAX(senttime) as LastSMS FROM dbo.custom_sms_MsgOut_Appointments WHERE TRY_CONVERT(datetime2, LEFT(CAST(senttime AS varchar(40)), 19)) BETWEEN '2026-08-01T00:00:00Z' AND '2026-08-03T23:59:59Z'"
════════════════════════════════════════════════════════════════════
3. BUSINESS RULE VERIFICATION CHECKLIST
════════════════════════════════════════════════════════════════════
Verify that all core business logic rules held true during the target date window:
[ ] Rule 1: No Duplicate Sends (US-DUP-01)
- Verify 'EligibilitySkip … reason=SendHistoryExists' or 'Stage-4:DuplicateSkip' logged for re-pick attempts.
- Confirm no mapping GUID has >1 record in custom_sms_MsgOut_Appointments for the same appointment window.
[ ] Rule 2: ETag Concurrency Claims (Stage-4b)
- Confirm ETag claims handled race conditions correctly ('Stage-4b:QueuedClaim (Won)' or 'Lost EtagMismatch').
[ ] Rule 3: Upstream Booking Feed Integrity
- Ensure 'AppointmentBooking NOT FOUND' errors match expected dead-letter counts and do not reflect systemic feed drops.
[ ] Rule 4: Dataverse Status Compliance
- Confirm Sent messages are updated to phcc_smsstatus = Sent (1).
- Note: statuscode = Open (1) with phcc_smsstatus = Sent (1) is a normal transient state unless the appointment is in the future without guard protections.
[ ] Rule 5: Ignore Processing Logic
- Mappings with phcc_ignoreprocessing = True must be skipped without error ('IgnoreProcessing=True').
════════════════════════════════════════════════════════════════════
4. REPORTING FORMAT
════════════════════════════════════════════════════════════════════
Structure your final monitoring report using the exact sections below:
EXECUTIVE VERDICT & RAG RATING
[GREEN / AMBER / RED]
Summary Statement (e.g., "Pipeline operating normally. 1,240 SMS sent, 0 unexpected DLQ, 100% feed sync.")
RAG Criteria Legend:
RED: Zero executions during batch window, full-batch dead-lettering, unhandled systemic crashes, active duplicate sends.
AMBER: Partial DLQ accumulation, upstream feed gaps ('AppointmentBooking NOT FOUND'), RenderFailed tokens, unresolved transient errors.
GREEN: Normal execution, zero unexpected DLQs, all guard rails firing, 100% feed sync.
EVIDENCE TIMELINE & AUDIT METRICS
Total Processed / Received: [Count]
Stage-5 Sent Hand-offs: [Count]
SQL Delivery Rows (dbo.custom_sms_MsgOut_Appointments): [Count]
DLQ Depth & Primary Reasons: [Count + Breakdown]
Duplicate Guard Triggers (US-DUP-01): [Count]
Booking Sync Status: [Sync Rate % / Missing Count]
FINDINGS & ANOMALIES (FACT vs HYPOTHESIS)
Issue 1: [Description]
Category: [Upstream Feed Gap / Template Error / Dataverse Reset / Pipeline Bug / Concurrency]
Evidence Level: [FACT (verified via logs/DB) | HYPOTHESIS]
Details & Trace References: [Log snippets, GUIDs, error strings]
RECOMMENDED REMEDIATIONS (Mutations require explicit user approval)
[List exact diagnostic/remediation scripts to run if issues were detected, e.g., selective requeue, template fix, or feed sync trigger].
WATCH ITEMS & PRE-FLIGHT RISKS
[List items to monitor before the next nightly 21:00 UTC batch].