About

1. Project Overview We require a robust Windows-based C# application that automates processing of order-related inbound emails from a vendor system. The application will: 1. Connect to one dedicated Exchange 2019 mailbox using Exchange Web Services. 2. Retrieve inbound Third Party order acknowledgement and shipping emails. 3. Extract order/account information via regex parsing. 4. Look up customer information in a PostgreSQL database. 5. Generate and send customer-facing emails: order acknowledgement + shipping confirmation. 6. Record outbound email status in a tracking table. 7. Move processed inbound emails into appropriate folders. Phase II NOTE: optional: Add automated NDR/bounce detection, database logging, and resend workflow. The final application must be secure, maintainable, configurable, and include full installation + user instructions. ________________________________________ 2. Technical Requirements 2.1 Platform & Language • Windows 10/11 or Windows Server 2019+ • C# .NET Framework or .NET 6/7 with EWS compatibility • Visual Studio solution • PostgreSQL database connectivity via Npgsql ________________________________________ 3. Functional Requirements 3.1 Email Retrieval - Inbound Vendor Emails The application must: • Connect to a single dedicated automation mailbox, e.g. orders-automation @ domain.com. • Use EWS to authenticate and retrieve emails. • Identify inbound emails using: o Subject patterns o Sender/domain filters o Or content indicators • Load full message, i.e. headers + body + HTML content • Convert HTML to plain text for parsing. • Prevent double-processing by: o Moving processed messages to a "Processed" folder o Or tagging with a category o Developer to propose optimal method ________________________________________ 3.2 Parsing & Data Extraction - Regex-Based Extract the following fields from inbound emails: • Order Number - e.g. MV6268726 • Client/Account Name • Order Status • Tracking URLs FedEx, UPS, etc. • Additional optional fields - as needed from examples Requirements: • Regex patterns must be externalized/configurable. • Parsing failures must be logged and messages moved to a ManualReview folder. ________________________________________ 3.3 PostgreSQL Integration Using Npgsql, the app must: • Query the existing database to match extracted order/account info to: o The correct customer o The set of active customer contact email addresses • Insert log rows into a sent_emails table with: o Order number o Customer ID o Recipient emails o Timestamp o Message ID or custom GUID token o Status - SENT, FAILED, etc. Developer and client will finalize schema collaboratively. ________________________________________ 3.4 Outbound Customer Email Generation Application must generate customer-facing emails: Acknowledgement Email Trigger: inbound vendor “order received/approved” email. Shipping Notification Email Trigger: inbound vendor “order shipped” email containing tracking links. Requirements: • Email templates must be external, editable without recompiling: o Plain text and/or HTML o Token-based, e.g., [OrderNumber], [CustomerName], [TrackingLinks] • Merge tokens into template to create final outbound message. • Send outbound emails using EWS as the sending mailbox. • Log all outbound emails in sent_emails table. ________________________________________ 3.5 Folder Management - Inbound Mailbox Application must automatically: • Move processed emails to a Processed folder. • Move failed/unparseable messages to a ManualReview folder. • Automatically create folders on first run if they do not exist. ________________________________________ 4. Phase II Optional Add-On: NDR/Bounce Handling 4.1 NDR Detection System receives bounce messages - NDRs in the same mailbox. Application must optionally support: • Identifying NDRs via ItemClass = REPORT.IPM.Note.NDR or subject patterns. • Extracting: o Failed recipient o SMTP error code o Original message headers o Custom GUID token • Updating sent_emails status → BOUNCED. ________________________________________ 4.2 Internal Notification - Optional Send an internal email or DB entry notifying staff: “Customer email for Order X to address Y bounced. Error: Z.” ________________________________________ 4.3 Resend Workflow - Optional Support: • Staff corrects customer email address in DB. • Developer will add logic to detect rows in sent_emails marked PENDING_RESEND. • App automatically recreates outbound message and resends. Developer must design Phase I with the architecture prepared for this optional upgrade, e.g., tracking table, GUIDs, clear interfaces. ________________________________________ 5. Non-Functional Requirements 5.1 Security • No hard-coded credentials. • Use environment vars, encrypted config, or Windows Credential Manager. • HTTPS/TLS secure channel to Exchange. • Parameterized SQL queries only. • Sanitized logging no full PII in logs ________________________________________ 5.2 Reliability & Maintainability • Clean architecture with clear separation: o Mail Client IMailClient o Parser o DB Repository o Template Engine o Processor Orchestrator • Modular design to support future migration to Exchange Online / Microsoft Graph. ________________________________________ 5.3 Configuration Configurable values: • Exchange URL • Mailbox name • Regex patterns • Template paths • Folders: Processed, ManualReview • Polling interval • DB connection string • NDR processing on/off ________________________________________ 6. Deliverables 6.1 Source Code • Complete Visual Studio solution • Clean and commented • No hard-coded secrets • Config-driven architecture ________________________________________ 6.2 Documentation Package A. Installation Guide Must include: 1. System prerequisites: Windows version, .NET version 2. EWS configuration 3. How to create/configure the dedicated mailbox 4. How to install required certificates if applicable 5. PostgreSQL setup and schema creation scripts 6. Step-by-step install instructions ________________________________________ B. Configuration Guide Includes: • How to edit templates • How to modify regex patterns • How to change folders • How to adjust logging • How to switch between dry-run and production modes ________________________________________ C. Usage Guide Documentation must describe: • How the service processes incoming messages • How to view outbound logs in DB • How to update templates • How to use ManualReview folder • How Phase II bounce handling would function if enabled ________________________________________ D. Testing Guide Must include: • Sample vendor emails for testing • Sample shipping emails with tracking URLs • Sample malformed emails • Instructions for sending test emails to inbox • Instructions for verifying correct DB entries • Instructions for verifying message movement to Processed • Instructions for testing outbound notifications If Phase II is purchased, also: • Sample NDR messages for validation ________________________________________ 6.3 Packaging & Deployment Deliverables • Compiled application • Windows installer OR instructions for: o Running as a Windows Service, or o Running via Task Scheduler on interval • Logging system file logs or Windows Event Log • Version tag included in build ________________________________________ 6.4 Post-Delivery Support Developer must provide: • 3–10 hours of bug-fix support after deployment • Reminder that enhancements are not part of support unless scoped ________________________________________ 7. Milestones Milestone 1 – Architecture & Project Setup • Folder structure • Interfaces • Initial config files • Exchange connectivity test Deliverable: architecture document + proof-of-connection test Payment: x% ________________________________________ Milestone 2 – Email Retrieval + Parsing Engine • Retrieve inbound emails from automation mailbox • HTML→text converter • Regex-based parser • ManualReview folder handling Payment: x% ________________________________________ Milestone 3 – PostgreSQL Integration • DB connection • Customer lookup • sent_emails table creation • Outbound logging Payment: x% ________________________________________ Milestone 4 – Outbound Email Generation • Template system • Token merging • EWS send • Processed folder automation Payment: x% ________________________________________ Milestone 5 – Packaging + Installation + Documentation • Installer or service setup • Full documentation set • Test plan Payment: x% ________________________________________ Optional Milestone 6 – Phase II: Bounce/NDR Handling • NDR parsing • Status update to DB • Internal notifications • Resend logic Payment: x% ________________________________________ 8. Acceptance Criteria The solution is complete when: • It reliably processes inbound vendor emails through the full workflow. • Outbound emails are correctly generated and sent. • Processed messages are moved appropriately. • All documentation is complete. • All configuration is external and editable. • No credentials are hardcoded. • Logs are useful and not leaking sensitive data. • Application builds cleanly in Visual Studio. If Phase II is included: • NDRs are correctly detected and logged. • Resend flow functions with corrected customer addresses.