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.

{
  "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.