Task #131
Updated by Suresh Kumar 9 days ago
# Email Automation Hub: ICEGATE Mail Processing and Milestone Update via REST API ## Summary Replace the Azure Logic App that processes ICEGATE (Indian Customs) emails with a Frappe-native **Email Automation Hub**. The hub receives ICEGATE mails, identifies the mail type, extracts Job No. / BE No. / BE Date, and updates the matching **Import Tracking** record on the company's own ERPNext site (SLCPL, later SLFL) using **Frappe's built-in REST API**. Nothing is installed on the target sites. ## User Story **As** an operations user at SLCPL / SLFL, **I want** ICEGATE customs mails to update the BE details, milestones, communication history and attachments on the matching Import Tracking record automatically, **so that** shipment status is always current without manual data entry, and without depending on the Azure Logic App. ## Background - Today an Azure Logic App reads ICEGATE mails and updates shipments. It is hard to change and has no visibility inside ERPNext. - The new solution runs inside Frappe, is configured from the UI, and records the result of every mail. --- ## Scope ### Architecture | Site | Role | Apps | |---|---|---| | **Email Hub** | Receives mails, identifies, extracts, routes and sends updates | frappe + `email_automation_hub_app` | | **SLCPL** (target) | Holds Import Tracking. Updated only via REST API | No custom app. One API user only | | **SLFL** (target, next phase) | Same as SLCPL (DocType "Import Shipment", configured via JSON mapping) | No custom app | ### End-to-end flow 1. Mail arrives in the company's ICEGATE mailbox → Frappe creates a **Communication**. 2. Hub copies it to **Email Repository** (duplicate check by Message-ID). 3. **Email Processing Rules** identify the mail type (sender + subject, plus body/attachment checks in code). 4. Data is extracted and validated (Job No., BE No., BE Date). An **ICEGATE Milestone Tracker** record is created. 5. **Route check:** the mailbox decides the target site (Email Repository Settings → Target Routes). 6. An **Outbox** message is queued. A worker (every minute) sends it over REST: find the record → read it → check for duplicates → upload files → save (with a modified-time check). 7. The result (Processed / Not Matched / Ignored / Failed) is written back to the tracker. ### Components (Hub app `email_automation_hub_app`, module "Email Automation") | Component | Purpose | |---|---| | Email Repository (+ Attachment) | Plain copy of every incoming mail | | Email Repository Settings | On/off, retry period (8 h), **Target Routes** (mailbox → Target System) | | Email Processing Rule (7) | Per mail type: senders, **Subject + Match Type** (Equals / Contains / Starts with / Ends with), milestone, enabled, **Test Subject** button | | ICEGATE Milestone Tracker (+ Step) | One record per identified mail: type, extracted data, status, step log, Target System, Target Record | | Target System | Connection (Base URL, API Key/Secret in Password fields, Enabled, Timeout) + **Field Mappings (JSON)** + **Check Fields** button | | Outbox | Delivery queue: one record per tracker per Target System, reused on every retry | ### Mail types | Mail type | Subject rule | Find record by | Update on target | |---|---|---|---| | BE Submission | Equals "On Submission" | Job No. | Milestone **BE Filed** | | BE Positive Acknowledgement | Ends with "_CACHI01_Success" | Job No. | BE No. + BE Date, milestone **BE Receipt** | | BE Text Format | Equals "Outbound file generated" | BE No. | Files only | | BE First Copy | Contains "Electronic first copy of BoE for Bill of Entry No" | BE No. | Milestone **Assessed** | | BE Examination Instructions | Equals "Bill of Entry Examination Instructions" | BE No. | Files only | | BE OOC Copy | Contains "Electronic Final OOC copy of BoE for Bill of Entry No" | BE No. | Milestone **Out Of Charge**, Under Query = No | | BE Under Query | Equals "Bill of entry pending for query reply" | BE No. | Under Query = Yes | Every processed mail also adds a row to the record's **ICEGATE Communication History** and attaches the mail's files. The milestone date is the mail's received time. ### Field Mappings (JSON) on Target System No target field names are hardcoded. Each automation has its own block, so a new automation or a site with different field names (e.g. SLFL "Import Shipment") needs only a JSON change. ```json { "icegate": { "doctype": "Import Tracking", "fields": { "job_no": "custom_icegate_job_no", "be_no": "custom_be_number", "be_date": "custom_be_date", "under_query": "custom_be_under_query" }, "tables": { "milestones": {"table": "milestones", "fields": {"milestone_name": "milestone_name", "actual_date": "actual_date"}}, "history": {"table": "custom_icegate_communication_history", "fields": {"sender": "email_from", "recipients": "email_sent_to", "received_on": "email_date_time", "subject": "subject", "body": "body", "attachments": "attachment_name"}} } } } ``` The JSON is validated on save. **Check Fields** reads the target DocType through the API and lists any missing field or table. ### Business rules - **Routing is by mailbox only.** Each company must have its own ICEGATE mailbox. A mailbox with no route → tracker **Failed** ("No route for this mailbox…"). After a route is added, **Retry** sends it. - **0 records found → Not Matched.** Retried hourly for 8 hours, and immediately when an Acknowledgement with the same BE No. is processed (handles mails arriving out of order). - **2+ records found → Failed.** Nothing is updated. - **Missing milestone row → Failed** with a reason. All checks run **before** any file upload. If a save fails after an upload, those files are deleted again. - **No overwrite of existing data:** - Milestone date already set → mail **Ignored** (no date change, no history row, no file). - Ack with a different BE No./Date than the record → **Failed**, nothing changed. - Files-only types: a file with the same name already on the record → **Ignored**. - Exception: a duplicate OOC still clears Under Query (Yes → No). - **Duplicate mail:** the same Message-ID is never processed twice. History rows are checked so the same mail is never added twice. - **Concurrent edits:** save uses the record's modified time. If a user changed it meanwhile, the hub reloads and retries (max 3). - **Delivery errors:** timeout / 5xx → retry after 1, 5, 15, 60 min, then Failed + Error Log. 401/403 (wrong key or permission) → Failed immediately + Error Log. Target System disabled → message waits until enabled. ### Target site setup (per site, no app install) - Role "Email Hub Target API": Import Tracking read/write, File read/write/create. - One API user with only this role. API key/secret generated and stored on the hub's Target System. --- ## Acceptance Criteria 1. Each of the 7 ICEGATE mail types, sent to a routed mailbox, updates the correct record on the target site, and the tracker shows **Processed**. 2. Mails that match no rule are stored in Email Repository only; no tracker is created. 3. An OOC received before its Ack is **Not Matched** first, then **Processed** automatically after the Ack. There is one Outbox record for it. 4. A duplicate mail never overwrites a milestone date, BE No./Date, or adds a second history row. 5. Under Query is set by the Under Query mail and cleared by OOC (including a duplicate OOC). 6. No route, missing milestone row, 2+ matches, wrong API key and disabled target each give the expected status and a clear reason. 7. Invalid Field Mappings JSON cannot be saved. Check Fields reports missing fields exactly. 8. No target field name is hardcoded in the app code. 9. All tracker and error messages are in plain English. --- ## Testing ### Automated (all passing) | Suite | Result | |---|---| | Parser tests | 97 OK | | Local e2e | 97 OK / 1 known fail (9.1: test Gmail sender is in 4 rules; clears when it is removed) | | Cross-site REST tests (remote_e2e) | 73 OK / 0 FAIL | | Real samples on REST path | 14 / 14 | ### Manual UI test cases (all passed) | # | Test | Expected | |---|---|---| | 1 | Submission → Ack → First Copy → OOC in order | All Processed. Milestones, BE No./Date, history rows and files on target | | 2 | OOC before Ack | Not Matched, then Processed after the Ack. One Outbox record | | 3 | Under Query, then OOC | Under Query Yes, then No | | 4 | Same mail sent again (new Message-ID) | Ignored. No date change, no new history row | | 5 | Unrelated mail | Repository copy only, no tracker | | 6 | Rule disabled | No tracker | | 7 | Test Subject button | Correct rule matched | | 8 | Mailbox without route | Failed. Add route + Retry → Processed | | 9 | Unknown Job No. | Not Matched | | 10 | Milestone row missing on target | Failed with reason. No file left on target | | 11 | Target System disabled | Outbox waits. Sent after enabling | | 12 | Wrong API key | Outbox Failed + Error Log | | 13 | Invalid JSON in Field Mappings | Save blocked with error | | 14 | Wrong field name in JSON + Check Fields | Missing field shown | ### Test tooling - `./demo.sh fresh`: one-command cleanup of all test data on hub and target (settings, rules, routes and Target Systems are kept; target backed up first). - Test scripts refuse to reset the demo record if it holds manual test data, unless `--force` is passed. --- ## Bugs Fixed During Development | Bug | Fix | |---|---| | `.ack` file parsing broke (splitlines splits on the field separator) | Split on newline only | | Ambiguous dates in First Copy mails | Zero-padded day and month | | Mails silently dropped when the naming counter fell behind | Error Log + hourly safety net re-captures them | | Tracker deadlock between two writers | Lighter lock | | Mail file left on target when the milestone row was missing | All checks run before upload. Uploaded files deleted if the save fails | | A retry created a new Outbox record each time | One Outbox record per tracker per target, re-queued on retry | | Duplicate OOC overwrote the milestone date and added a history row | No-overwrite rule (Ignored) | | Duplicate OOC did not clear Under Query | Duplicate OOC clears Under Query only | | Test scripts wiped manual test data on the demo record | Guard + `--force` + automatic target backup | | Message in mixed Hindi-English | Plain English messages | ## Design Changes Made During Development - Import Tracking removed from the hub. All updates go to target sites over REST only. - Email Processing Rule: 4 subject fields replaced by one **Subject + Match Type**. - Target System: separate mapping fields replaced by one **Field Mappings JSON** (per automation, includes child table columns). - App renamed from `slcpl_erpnext_self_development` to **`email_automation_hub_app`** (data, DocTypes and patch history kept). --- ## Out of Scope / Pending - Confirm the BE Under Query body wording with a **real ICEGATE mail** (no real sample available yet). - Remove the personal test email from the 4 rules and turn off the test mailbox after testing. - **SLFL onboarding:** API user, Target System with "Import Shipment" JSON mapping, route for SLFL mailbox. - Production setup: hub site, HTTPS URLs, real mailboxes (dedicated, so mails are not read before Frappe fetches them), DocType name-clash check. - Parallel run with the Azure Logic App, then switch-off. - No automation for deleting Communications. ## Dependencies - Separate ICEGATE mailbox per company (confirmed). - Import Tracking records exist on the target with Job No. and milestone rows filled in by users. - API user and role created on each target site. ## Definition of Done - All acceptance criteria met and all test suites passing. - Code committed locally on the `develop` branch of `email_automation_hub_app`. CLAUDE.md and docs updated. - Production checklist reviewed with the manager before deployment. Description