Feature #132 » README.md
FTWZ End-to-End Pipeline — Gated, Single-Command Runner
1. Overview
This project automates the FTWZ customs workflow from NSDL SEZ Online to Canofy / Frappe.
The pipeline is divided into three strictly gated steps:
STEP 1 — NSDL DOWNLOAD
↓
BE Excel + DTA Excel
↓
Validation Gate
↓
STEP 2 — TRANSFORM + QC + DOCTYPE CREATION
↓
Request-ID grouped Frappe payloads
↓
JSON normalization + strict JSON validation
↓
Duplicate check
↓
FTWZ BOE Import / FTWZ DTA Sale
↓
Validation Gate
↓
STEP 3 — BOE PDF DOWNLOAD + ATTACH
↓
Use Request ID from persisted state
↓
NSDL Search Request ID
↓
View / Print BOE
↓
Download Original PDF
↓
Attach PDF to the corresponding FTWZ BOE Import
The runner is designed so that a later step cannot run unless the previous step has already passed its validation gate.
2. Core Design Rule — Validate Every Step
The central rule is:
Only move to the next step after the current step has been executed and validated successfully.
The pipeline controller enforces:
result = step.execute(context)
verdict = step.validate(context, result)
if verdict failed:
STOP
There is no normal flow where a failed step is simply logged as a warning and the next step continues.
This gate is also persisted in the local state store, so the rule works across separate command invocations.
For example:
python main.py --run-date 2026-09-12 --environment TEST --steps 1
can be executed first, and Step 2 can be executed later:
python main.py --run-date 2026-09-12 --environment TEST --steps 2
Step 2 checks the persisted Step-1 status before doing any processing.
3. Pipeline Steps
| Step | Module | Responsibility | Validation Gate |
|---|---|---|---|
| 1 | pipeline/step1_download.py |
Login to NSDL and download the BE and DTA trade-data reports (each independently; missing data for one never stops the other) | Downloaded files exist, are non-empty, readable, and contain the required parser columns; stops cleanly when neither BE nor DTA has rows for the configured IECs |
| 2 | pipeline/step2_transform.py |
Parse Excel, filter IECs, group by Request ID, run QC, build Frappe payloads, perform duplicate checks, create DocTypes | BE + DTA QC pass, JSON is valid, creation success rate meets configured threshold, successful records are persisted |
| 3 | pipeline/step3_pdf_attach.py |
Retrieve BE Request IDs from state, download BOE PDFs from NSDL, upload and attach to Frappe | PDFs are valid/non-empty and attachment success rate meets configured threshold |
4. Step 1 — NSDL Report Download
Step 1 logs into NSDL SEZ Online using Playwright.
It performs:
NSDL Login
↓
CAPTCHA handling / OCR
↓
Email OTP (2FA) — read from Frappe Communication and submitted automatically
↓
Reports
↓
Trade Data
↓
BILL OF ENTRY export
↓
DTA SALE export
↓
Download validation
Login with email OTP (2FA)
Since 23.09.2026 NSDL SEZ Online requires an OTP after User ID +
Password + CAPTCHA (NDML Notice 88; details in Release Note v3.0, Notice 86).
Step 1 and Step 3 both log in through one shared module,
utils/nsdl_login.py, which completes the OTP automatically. The OTP mail is
read from Frappe (utils/otp_mailbox.py).
Portal rules the login is built around
| NSDL rule (Release Note v3.0) | How the framework handles it |
|---|---|
| Same OTP sent to registered mobile and email | OTP is read from email |
| OTP valid for 10 minutes | Waits for the mail up to wait_seconds (default 540 s), never past the validity |
| Resend allowed after 30 seconds, max 5 requests | Resend only after NSDL rejected an OTP; cooldown resend_cooldown_seconds (32 s); at most max_resends (3) |
| 5 wrong OTPs → 30-minute lockout | Never guesses or reuses an OTP; stops after max_submit_attempts (3) |
| Lockout / cooling-period / resend-limit message | Stops immediately, saves a screenshot, no retry |
Mail route
NSDL ──OTP mail──► manager's mailbox ──forward──► OTP mailbox (IMAP)
│
Frappe Email Account pulls ◄┘
│
Communication (Received) ◄───────┘
│
GET /api/resource/Communication ◄───────┘ (this framework)
The OTP mail
NSDL sends the mail from donotreply-ndmlsupport@ndml.in with subject
SEZOnline Authentication. The body reads:
658204 is your One Time Password (OTP) of User ID canofy01 for verification
on SEZ Online System. It is valid for 10 mins. Do not share your OTP with anyone.
The OTP is taken from that sentence (the number before "is your One Time
Password"). Numbers in the forwarding headers ("Sent: 10 October 2026
11:39:56"), the disclaimer or signatures are never used. A mail that contains
two different OTP sentences (a forward quoting an older OTP mail) is
ambiguous and is never used. With verify_user_id: true the User ID in the
mail must match NSDL_USERNAME.
How the right (newest, valid) OTP is picked
Frappe imports every unread mail when it pulls (UNSEEN sync) and stamps
each Communication's creation with the import time, so an old unread OTP
mail looks new. pull_emails also returns without importing anything when
Frappe's scheduler is already pulling the account. The framework therefore
anchors every OTP to the moment it was requested:
before clicking Login / Resend:
pull the mailbox until Frappe confirms a completed pull (old unread mails are imported NOW)
snapshot the existing OTP Communications (baseline)
record the request time on the Frappe server clock (HTTP Date header)
click Login / Resend
a mail is accepted for this request only if ALL of these hold:
1. it is not in the baseline and was never used before (Communication name, Message-ID, OTP value)
2. communication_date (the mail's own Date header) >= request time - tolerance
3. forwarded "Sent:" / "Date:" time of the original NSDL mail >= request time - tolerance
(a late forward of an old OTP is rejected even though the forward itself is new)
4. the OTP is still inside its 10-minute validity
5. subject, OTP sentence and User ID match
if several mails pass, the newest one is used
The tolerance (clock_skew_tolerance_seconds, 20 s) is never more than half
the gap to the previous request, so after a Resend a late mail of the
superseded OTP can never pass for the new one. Every rejected mail is logged
once with the exact reason, e.g.
Ignoring mail 'FW: SEZOnline Authentication' (j3b0bmbk27): forwarded copy of an older NSDL mail (original sent 15:32:27, OTP request #1 was at 15:36:27).
Login sequence
Login.aspx → fill + verify User Name / Password
↓
pull mailbox (complete) + snapshot ← done before the CAPTCHA is read
↓
read CAPTCHA (doubtful reading → refresh image, read again)
↓
record time, click Login (OTP request #1)
├─ CAPTCHA rejected → image kept in captcha_rejected/, new attempt
├─ User ID / password → STOP at once (retrying could lock the account)
├─ lockout message → STOP
└─ OTP page
wait (up to the OTP validity) for a mail that passes all checks
fill + verify OTP → Submit
├─ accepted → home page ✔
└─ rejected → reason captured (JS alert or page label) + screenshot
another valid OTP for this request already waiting? → submit it
otherwise → Resend (after the 30 s cooldown; result of the
Resend click is checked too) → wait again
Resend is not used just because the mail is slow — that was the cause of
the old "no OTP within 90 sec" loop (the resend invalidated the OTP whose mail
then arrived). Set resend_on_timeout: true only if you want one resend when
no mail at all arrives within the validity window.
Every login writes data/logs/{run_date}/login_audit.jsonl: OTP request
times, which Communication was submitted, every portal message (including
JavaScript alerts, which Playwright would otherwise dismiss silently) and the
classification (captcha, credentials, otp_invalid, otp_expired,
resend_limit, lockout).
CAPTCHA reading
utils/ocr_engine.py:
-
Filtering — the grey border is cropped; a darkness map from the
brightest colour channel turns the dark navy/brown/purple letters black
while the mid-grey noise curves and speckled background stay white;
Otsu threshold with a floor the noise cannot reach; speckles removed. -
Re-spacing — every letter is cut out and placed on a clean canvas with
even gaps (5 gap sizes, grey + binary), plus 4 variants of the original
layout (grey / binary / thin / thick). -
Recognition — EasyOCR's recogniser only, on the tight text box of each
variant (the slow text detector is skipped; it also used to drop letters
such asi). The model is loaded in the background while the login page opens. -
Consensus — case-insensitive majority vote, then a confidence-weighted
per-character vote; the case ofc o s u v w x zis decided from the glyph
height, a dotted glyph isi/j. -
Validation — the reading must be alphanumeric,
captcha.expected_length
characters long and agreed by enough variants; otherwise a fresh CAPTCHA is
requested instead of submitting a doubtful guess.
Measured on 151 NSDL-style CAPTCHAs (the real sample + synthetic images in
the same style): exact reads 50 % → 70 %, ignoring case 70 % → 86 %,
time per read 1.15 s → 0.32 s.
One-time setup in Frappe
-
Email Account (
/app/email-account/new):-
Email Address / Password of the OTP mailbox (for Gmail / Google
Workspace use an App Password, or OAuth through a Connected App). - Tick Enable Incoming and Use IMAP, and set the IMAP server.
- Leave Append To empty — the mail only needs to become a Communication.
- Save. The Email Account Name field (the record's name) is the value
forOTP_EMAIL_ACCOUNT.
-
Email Address / Password of the OTP mailbox (for Gmail / Google
-
API user: the user behind
FRAPPE_API_KEY/FRAPPE_API_SECRETneeds
read permission on Communication and Email Account
(System Manager has both). -
Forwarding: the manager's mailbox must forward the NSDL mail to the
OTP mailbox. Frappe rejects a mail whose sender is the OTP mailbox itself,
so forward from the manager's address, not from the OTP mailbox. -
Use a mailbox that nobody opens in a mail client. Frappe pulls unseen
mail by default, so a mail that was already read elsewhere is skipped. -
Put the values in
config/config.yamlunder your environment:OTP_EMAIL_ACCOUNT: "NSDL OTP" # name of the Frappe Email Account # Only if the Email Account is on a different Frappe site than FRAPPE_URL: # OTP_FRAPPE_URL: "https://mail-site.example.com" # OTP_FRAPPE_API_KEY: "..." # OTP_FRAPPE_API_SECRET: "..." -
Forward one real NSDL OTP mail to the OTP mailbox, then check the setup
without logging in to NSDL:python main.py --otp-check --environment TESTIt confirms the Email Account is reachable, pulls mail and prints the OTP
extracted from the latest OTP mail. -
The NSDL user's email ID must be verified on the portal, otherwise NSDL
does not send the email OTP. The one-time "mobile number and/or email ID is
pending for verification" screen has to be completed manually once.
OTP settings (common.nsdl_otp)
| Key | Default | Meaning |
|---|---|---|
enabled |
true |
Turn email-OTP login on/off |
subject_contains |
SEZOnline Authentication |
Only Communications whose subject contains this are read ("" = all) |
otp_length |
6 |
Digits in the OTP |
verify_user_id |
true |
Ignore mails for a User ID other than NSDL_USERNAME
|
allow_standalone_fallback |
false |
Accept a lone N-digit number when the OTP sentence is absent |
otp_validity_seconds |
600 |
NSDL OTP validity (60–600) |
wait_seconds |
540 |
Max wait for the OTP mail (30 – validity) |
submit_margin_seconds |
30 |
Never submit an OTP in its last N seconds |
resend_on_timeout |
false |
Resend once when no mail arrived within the validity window |
poll_interval_seconds |
3 |
Communication polling interval |
trigger_pull |
true |
Ask Frappe to fetch new mail immediately (pull_emails, Frappe v15+) |
pull_interval_seconds |
10 |
Minimum gap between two pulls (min 5) |
drain_timeout_seconds |
45 |
Before an OTP request, wait this long for a running Frappe pull to finish |
scan_limit |
20 |
Newest matching Communications checked per poll (1–100) |
clock_skew_tolerance_seconds |
20 |
Tolerated clock difference between mail servers and Frappe (0–120) |
verify_forwarded_sent_time |
true |
Also check the original "Sent:" time inside forwarded mails |
frappe_time_zone |
"" |
"" = read from Frappe; else an IANA name such as Asia/Kolkata
|
mail_time_zone |
"" |
Zone of the forwarded "Sent:" line; "" = same as Frappe |
resend_cooldown_seconds |
32 |
Wait before Resend (min 30) |
max_resends |
3 |
Resends per login (1–4; NSDL allows 5 requests) |
max_submit_attempts |
3 |
Rejected OTPs before stopping (1–4; NSDL locks at 5) |
otp_input_selector / otp_submit_selector / otp_resend_selector
|
built-in | Override only if NSDL changes the OTP page |
CAPTCHA settings (common.captcha)
| Key | Default | Meaning |
|---|---|---|
expected_length |
5 |
Characters in the NSDL CAPTCHA; null accepts 4–8 |
min_agreement |
0.34 |
Share of OCR variants that must agree, else a new CAPTCHA is read |
max_reads_per_attempt |
4 |
New CAPTCHA images tried per login attempt when a read is doubtful |
image_selector / refresh_selector
|
built-in | Override only if NSDL changes the CAPTCHA markup |
Other login settings in common: max_login_attempts (8),
nsdl_response_timeout_seconds (30).
Unsafe values are rejected when the config is loaded.
When login stops
| Message in log | Meaning / action |
|---|---|
No valid OTP mail arrived for request #N ... Mails seen but rejected — ... |
The listed mails were too old / for another user / already used. No new mail at all: check the forwarding rule and the Email Account (Pull Emails, Error Log) |
NSDL rejected N OTPs (last reason: ...) |
The portal's own reason is shown; compare login_audit.jsonl with the real mail (--otp-check) |
NSDL reports a login block |
Portal cooling period. Wait 30 minutes before re-running |
NSDL refused the OTP resend (limit reached) |
NSDL's resend limit; wait and re-run |
NSDL rejected the User ID / password |
Fix NSDL_PASSWORD; the login is not retried, to avoid locking the account |
Frappe refused to ... (HTTP 401/403) |
API key/secret wrong, or the user lacks read on Communication / Email Account |
Could not read Email Account ...: not found |
OTP_EMAIL_ACCOUNT does not match the Email Account name |
does not have 'Enable Incoming' switched on |
Tick Enable Incoming on the Email Account |
Frappe could not pull mail for Email Account |
IMAP login/settings of the Email Account are wrong |
A screenshot is saved to data/logs/{run_date}/ for every stop; rejected
CAPTCHA images are kept in data/logs/{run_date}/captcha_rejected/.
Missing BOE or DTA reports
NSDL produces no file when a module has no requests in the date range. Each
module is exported on its own, so no BOE never stops the DTA export (and
vice versa). A module counts as no data when NSDL says so (alert or page
message such as "No Records Found"), when no file arrives within
nsdl_export_timeout_seconds (30 s) and the page shows no error
(treat_export_timeout_as_no_data: true), or when the file has no data rows.
Rows are then counted per configured client IEC:
| BOE rows for IEC | DTA rows for IEC | Result |
|---|---|---|
| yes | yes | Both pipelines run |
| none | yes |
No BOE records for IEC ... logged; DTA is processed |
| yes | none |
No DTA records for IEC ... logged; BOE is processed |
| none | none |
There are no BOE and no DTA files/records for IEC ... Nothing to process — stopping here. The run stops cleanly (exit code 0); with --fail-on-zero-rows it fails instead |
A real export failure (navigation error, unexpected portal error) still fails
Step 1, after the other module has been attempted, with a screenshot.
Browser popup handling
The NSDL browser layer includes popup/overlay protection carried over from the working automation flow.
It includes:
- page initialization against Driver.js-style welcome overlays
- removal of known modal/overlay elements
- popup cleanup before sensitive clicks
- safe click behavior with cleanup and fallback
- body/html overflow recovery
- configurable headless mode
The popup layer is applied to the NSDL browser flows that require it.
Headless mode
For debugging:
headless: false
For unattended execution:
headless: true
5. Step 1 Output Files
For run date 2026-09-12:
data/
└── raw/
└── 2026-09-12/
├── BillOfEntry_2026-09-12.xls
└── DTASale_2026-09-12.xls
The downloaded NSDL files are the raw source-of-truth inputs for Step 2.
6. Important NSDL .xls Handling
The NSDL trade-data export may use an .xls extension while containing tab-delimited exported text rather than a conventional BIFF Excel workbook.
The parser therefore supports the NSDL export representation directly rather than assuming that every .xls file is a native binary Excel workbook.
The parser reads the report structure beginning after the NSDL header rows and uses the actual column names from the report.
7. Multi-Client / IEC Handling
The configured clients are represented as a list:
client_iec_codes:
- "0300014953"
The IEC comparison is string-based.
This is important because NSDL may provide the value as a zero-padded string such as:
0300014953
rather than as:
300014953
The parser therefore does not rely on an integer-only comparison.
One parser pass can handle multiple configured clients.
The configured client list is applied to the source data before Request-ID grouping.
8. Step 2 — Excel → Frappe Document Payload
Step 2 is the transformation and creation phase.
The logical flow is:
NSDL Excel
↓
pandas DataFrame
↓
Field mapping
↓
Client IEC filtering
↓
Request ID grouping
↓
Parent fields
+
Items
+
Invoices
+
Shipments
+
Attachment placeholders
↓
Business QC
↓
JSON-safe normalization
↓
Strict JSON validation
↓
Canofy duplicate check
↓
Frappe REST POST
The parser retains the existing business mappings and duty-field handling from the original transformation logic.
9. Request ID Is the Main Record Key
The NSDL REQUEST ID is the key used to group source rows into one logical customs transaction.
For each Request ID, the framework builds one parent Frappe document.
Examples:
REQUEST ID
↓
FTWZ BOE Import
or:
REQUEST ID
↓
FTWZ DTA Sale
The child tables are then attached to that parent structure.
10. Frappe DocType Structures
BE
Target parent:
FTWZ BOE Import
Child tables:
BOE Import Items
BOE Import Invoices
BOE Import Shipment
Attachments Details
The payload contains:
parent fields
items[]
invoices[]
table_all_shipment_details[]
attachments[]
DTA
Target parent:
FTWZ DTA Sale
Child tables:
DTA Sale Items
DTA Sale Invoices
DTA Sale Shipments
Attachments Details
The payload contains:
parent fields
items[]
invoices[]
table_all_shipment_details[]
attachments[]
11. Attachment Handling — Final Design
This is an important part of the final architecture.
Step 2 does NOT create a "Bill of Entry" attachment row
The normal attachment placeholders created during DocType creation are:
AWB_HBL
Invoice
Packing List
The framework must not create:
Bill of Entry
as a Step-2 Attachments Details row.
That value caused a Frappe Link Validation error because the Frappe Attachment Type master did not contain the exact linked value:
Bill of Entry
The final flow therefore keeps BOE PDF handling entirely in Step 3.
12. Step 3 — Request ID → BOE PDF → Frappe Attachment
Only successfully created BE records are processed for BOE PDF attachment.
The flow is:
Step 2 creates FTWZ BOE Import
↓
request_id persisted in state store
↓
Step 3 queries records ready for attachment
↓
Request ID
↓
NSDL Search
↓
View / Print BOE
↓
Original
↓
Download PDF
↓
Validate PDF
↓
Attach PDF to existing FTWZ BOE Import
↓
Mark request as ATTACHED
DTA records do not enter the BOE PDF phase in the current design.
13. Excel → JSON: Where the JSON Goes
The framework does not depend only on an in-memory payload.
For each Request ID, the final sanitized outbound payload is also stored locally for audit/debugging.
BE payloads:
data/
└── payloads/
└── BE/
└── <request_id>.json
DTA payloads:
data/
└── payloads/
└── DTA/
└── <request_id>.json
Example:
data/payloads/BE/172600305544.json
data/payloads/DTA/262600654982.json
These files represent the sanitized JSON-shaped Frappe document that is sent to the API.
The file is an audit copy.
The authoritative final document is still the Frappe DocType created on the Canofy server.
14. JSON Safety Fix
NSDL exports contain many blank cells.
When pandas reads those blanks, they can become values such as:
numpy.nan
pandas.NaT
pandas.NA
Standard strict JSON does not support NaN.
The final framework therefore normalizes the complete nested payload immediately before the Frappe API call.
Conceptually:
NaN
↓
None
↓
JSON null
Other conversions include:
numpy integer → Python int
numpy float → Python float
numpy bool → Python bool
datetime → ISO string
date → ISO string
The normalization is recursive, so it also handles:
parent fields
items[]
invoices[]
shipments[]
attachments[]
After normalization, strict validation is performed before the HTTP request.
The intent is:
invalid payload
↓
detected locally
↓
NO Frappe request
rather than allowing an invalid JSON value to fail during request serialization.
15. Frappe POST Flow
The final API path is conceptually:
build payload
↓
make JSON-safe
↓
strict JSON validation
↓
save outbound payload copy
↓
duplicate check
↓
POST to Frappe
The HTTP call uses the final sanitized payload.
The framework does not convert missing Excel values into zero merely to satisfy JSON.
Missing values remain semantically missing:
Excel blank
→ JSON null
not:
Excel blank
→ 0
16. Example DTA Document Shape
A DTA payload follows the same nested structure expected by the Canofy DocType:
{
"doctype": "FTWZ DTA Sale",
"client_iec": "300014953",
"request_id": "172600786632",
"request_status": "OutOfCharge",
"items": [
{
"doctype": "DTA Sale Items"
}
],
"invoices": [
{
"doctype": "DTA Sale Invoices"
}
],
"table_all_shipment_details": [
{
"doctype": "DTA Sale Shipments"
}
],
"attachments": [
{
"doctype": "Attachments Details",
"attachment_type": "AWB_HBL"
},
{
"doctype": "Attachments Details",
"attachment_type": "Invoice"
},
{
"doctype": "Attachments Details",
"attachment_type": "Packing List"
}
]
}
Frappe then generates server-side fields such as:
name
creation
modified
owner
modified_by
The framework does not hard-code those server-generated values.
17. State Store
Each run stores its local state under:
data/
└── logs/
└── {run_date}/
├── state.json
├── requests.json
├── artifacts.json
└── run.log
state.json
Contains step-level execution and validation state.
requests.json
Contains one persisted entry per NSDL Request ID.
It is the main bridge between Step 2 and Step 3.
The logical status progression is:
PARSED
↓
DOCTYPE_CREATED
↓
PDF_DOWNLOADED
↓
ATTACHED
with failures recorded as:
FAILED
Step 3 uses the persisted request data rather than re-parsing the original Excel files to rediscover which records need PDF attachment.
artifacts.json
Tracks generated/downloaded artifacts.
run.log
Contains the full timestamped execution log.
18. Local State Is the AWS Stand-In
The local state layer is intentionally isolated behind the state-store interface.
Current implementation:
state/store.py
The eventual AWS architecture can replace the local persistence internals with DynamoDB while keeping the same higher-level pipeline behavior.
The same concept applies to file storage:
local data/
↓
future S3
and:
local YAML credentials
↓
future AWS Secrets Manager
19. Idempotency
The pipeline is designed to be safely re-runnable.
Frappe creation
Before creating a new document, the API layer performs a duplicate/idempotency check using the Request ID.
Step 3 state
Step 3 works from persisted state and processes records that are ready for PDF attachment.
Once a record reaches:
ATTACHED
it is not processed again as a fresh attachment operation.
Attachment duplication
The Frappe attachment flow checks whether the PDF is already attached before attempting another upload.
The objective is to avoid:
duplicate DocType
duplicate PDF
duplicate attachment
when a run is repeated.
20. Data Folder Structure
A typical run produces:
project_root/
│
├── main.py
├── requirements.txt
├── README.md
│
├── config/
│ ├── config.yaml
│ ├── config.sample.yaml
│ └── config.py
│
├── pipeline/
│ ├── base.py
│ ├── step1_download.py
│ ├── step2_transform.py
│ ├── step3_pdf_attach.py
│ ├── nsdl_parser.py
│ └── frappe_client.py
│
├── state/
│ └── store.py
│
├── utils/
│ ├── json_utils.py
│ ├── logger.py
│ ├── nsdl_browser.py
│ ├── nsdl_login.py # shared NSDL login: CAPTCHA + email OTP
│ ├── ocr_engine.py
│ └── otp_mailbox.py # Frappe Communication OTP reader
│
└── data/
├── raw/
│ └── {run_date}/
│ ├── BillOfEntry_{run_date}.xls
│ └── DTASale_{run_date}.xls
│
├── payloads/
│ ├── BE/
│ │ └── {request_id}.json
│ └── DTA/
│ └── {request_id}.json
│
├── pdfs/
│ └── {run_date}/
│ └── {request_id}.pdf
│
└── logs/
└── {run_date}/
├── state.json
├── requests.json
├── artifacts.json
└── run.log
21. Configuration
Main configuration:
config/config.yaml
Typical structure:
common:
client_iec_codes:
- "0300014953"
nsdl_base_url: "https://www.sezonline-ndml.co.in/User/Login.aspx"
nsdl_modules:
BE: "17"
DTA: "26"
headless: false
pdf_pause_every: 15
pdf_pause_seconds: 120
pdf_hard_reset_every: 60
rate_limit_wait_seconds: 120
max_login_attempts: 8
nsdl_response_timeout_seconds: 30
nsdl_export_timeout_seconds: 30
treat_export_timeout_as_no_data: true
max_stuck_print_retries: 3
max_session_expiry_retries: 5
step2_qc_strict: true
step3_min_pdf_success_rate: 1.0
environments:
DEV:
NSDL_USERNAME: "..."
NSDL_PASSWORD: "..."
FRAPPE_URL: "..."
FRAPPE_API_KEY: "..."
FRAPPE_API_SECRET: "..."
OTP_EMAIL_ACCOUNT: "..."
TEST:
NSDL_USERNAME: "..."
NSDL_PASSWORD: "..."
FRAPPE_URL: "..."
FRAPPE_API_KEY: "..."
FRAPPE_API_SECRET: "..."
OTP_EMAIL_ACCOUNT: "..."
PROD:
NSDL_USERNAME: "..."
NSDL_PASSWORD: "..."
FRAPPE_URL: "..."
FRAPPE_API_KEY: "..."
FRAPPE_API_SECRET: "..."
OTP_EMAIL_ACCOUNT: "..."
The NSDL OTP settings (nsdl_otp) and CAPTCHA settings (captcha) are described in section 4.
Credentials can also be injected through environment variables:
NSDL_USERNAME
NSDL_PASSWORD
FRAPPE_API_KEY
FRAPPE_API_SECRET
OTP_EMAIL_ACCOUNT
OTP_FRAPPE_URL
OTP_FRAPPE_API_KEY
OTP_FRAPPE_API_SECRET
Environment variables take precedence over the YAML credential values.
22. Installation — Windows
From the folder containing main.py:
python -m venv venv
.\venv\Scripts\Activate.ps1
python -m pip install --upgrade pip
python -m pip install -r requirements.txt
python -m playwright install chromium
If PowerShell blocks activation:
Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass
.\venv\Scripts\Activate.ps1
23. Run Step 1 Only
For the current test date:
python main.py --run-date 2026-09-12 --environment TEST --steps 1
Expected output files:
data/raw/2026-09-12/BillOfEntry_2026-09-12.xls
data/raw/2026-09-12/DTASale_2026-09-12.xls
24. Check Pipeline Status
python main.py --run-date 2026-09-12 --environment TEST --status
This does not execute a pipeline step.
It reports the persisted state.
25. Run Step 2 Only
After Step 1 has passed:
python main.py --run-date 2026-09-12 --environment TEST --steps 2
Inspect the generated outbound payloads under:
data/payloads/BE/
data/payloads/DTA/
26. Run Step 3 Only
After Step 2 has passed:
python main.py --run-date 2026-09-12 --environment TEST --steps 3
BE PDFs are stored under:
data/pdfs/2026-09-12/
27. Run Everything
All three steps:
python main.py --run-date 2026-09-12 --environment TEST --steps all
all is the default, so this is equivalent:
python main.py --run-date 2026-09-12 --environment TEST
28. Offline / Dry-Run Mode
Dry-run mode avoids live NSDL and Canofy writes and is intended for pipeline testing.
python main.py --run-date 2026-09-12 --environment TEST --dry-run --steps all
When using dry-run sample files, the Step-1 sample discovery is based on filename keywords in sample_data/.
Typical discovery rules:
BE:
filename contains "bill" + "entry"
fallback: "boe"
DTA:
filename contains "dta" + "sale"
fallback: "dta"
If multiple matching files exist, the most recently modified file is selected.
29. Run Steps Individually
Recommended testing sequence:
python main.py --run-date 2026-09-12 --environment TEST --steps 1
python main.py --run-date 2026-09-12 --environment TEST --status
python main.py --run-date 2026-09-12 --environment TEST --steps 2
python main.py --run-date 2026-09-12 --environment TEST --status
python main.py --run-date 2026-09-12 --environment TEST --steps 3
python main.py --run-date 2026-09-12 --environment TEST --status
30. Step Gate Behavior
Trying to execute Step 2 before Step 1 has passed results in a refusal.
Conceptually:
REFUSING TO RUN:
step 2 requires step 1 to have already PASSED
Likewise, Step 3 requires Step 2 to have already passed.
This applies even when the commands are executed in separate terminal sessions or at different times because the gate uses persisted state.
31. QC
Step 2 preserves the business QC layer used for the customs transformation.
The QC stage validates the parsed/grouped data before a Frappe record is accepted as successfully created.
The framework also tracks:
total_attempted
succeeded
failed_count
success_rate
required_threshold
The default creation success threshold is:
100%
unless changed in configuration.
A partial Step-2 creation result therefore does not automatically count as a successful pipeline step.
32. What Was Verified During Development
The current framework was exercised against actual NSDL BE and DTA exports.
For the 2026-09-12 files used during testing, Step 1 produced:
BE : 569 rows
DTA : 2413 rows
Step 2 source filtering/grouping produced:
BE : 285 rows matched the configured IEC → 32 unique Request IDs
DTA : 378 rows matched the configured IEC → 50 unique Request IDs
The first Step-2 failure was caused by pandas NaN values entering JSON serialization.
That issue was fixed by adding recursive JSON-safe normalization and strict JSON validation.
The next Step-2 failure was caused by an invalid attachment placeholder:
Attachment Type: Bill of Entry
That issue was fixed by removing the BOE attachment placeholder from Step 2 and keeping actual BOE PDF attachment work in Step 3.
The framework should therefore be evaluated in layers:
Step 1 download
↓
Step 2 payload/QC/API creation
↓
Step 3 PDF attachment
A successful Step 1 does not by itself prove that Frappe creation or PDF attachment is successful.
33. Troubleshooting
Import error involving Step 3
Verify:
python -c "from pipeline.step3_pdf_attach import Step3PdfAttach; print(Step3PdfAttach)"
Expected:
<class 'pipeline.step3_pdf_attach.Step3PdfAttach'>
If the import fails, ensure the framework files were extracted from the same final package and that stale files from older framework versions were not mixed into the project directory.
JSON serialization error
Error example:
Out of range float values are not JSON compliant: nan
The expected architecture is:
pandas NaN
↓
make_json_safe()
↓
None
↓
JSON null
Inspect:
data/payloads/BE/
data/payloads/DTA/
to see the outbound payloads.
Frappe Attachment Type error
Error example:
Could not find Row #4: Attachment Type: Bill of Entry
The final framework does not create "Bill of Entry" as a Step-2 attachment placeholder.
Step 2 should contain:
AWB_HBL
Invoice
Packing List
The BOE PDF is processed in Step 3.
34. Security
Do not commit real credentials to source control.
Use:
config/config.yaml
or environment variables for local testing, and move credential storage to AWS Secrets Manager for production deployment.
The OTP reader only reads Communication and Email Account records and
asks Frappe to pull mail; it never sends, changes or deletes mail. Give the
API user no more permissions than it needs, and use a mailbox that only
receives the forwarded NSDL mails where possible.
35. Current Local Architecture vs Future AWS Architecture
The local framework is intentionally structured so AWS can be introduced later.
Conceptual migration:
LOCAL AWS
--------------------------------------------------
config.yaml → Secrets Manager
state/store.py → DynamoDB
data/raw → S3
data/payloads → S3
data/pdfs → S3
pipeline gates → Step Functions
pipeline step body → Lambda handlers
AWS/EventBridge/Lambda are not required for the current local execution.
36. Production Transition
Before production scheduling is enabled, verify:
NSDL live login
CAPTCHA handling
NSDL export availability/date range
BE parsing
DTA parsing
IEC filtering
Request-ID grouping
Business QC
JSON payload correctness
Frappe DocType creation
Frappe duplicate behavior
BOE PDF retrieval
PDF attachment
Re-run/idempotency behavior
Only after those validations should the local runner be moved into the eventual unattended AWS schedule.
37. Final End-to-End Data Flow
NSDL SEZ ONLINE
│
▼
┌─────────────────┐
│ STEP 1 │
│ NSDL Login │
│ CAPTCHA / OCR │
│ BE + DTA Export │
└────────┬────────┘
│
▼
data/raw/{run_date}/
BE.xls + DTA.xls
│
▼
┌─────────────────┐
│ STEP 2 │
│ Parse + Filter │
│ Request ID │
│ Grouping │
│ Business QC │
└────────┬────────┘
│
▼
JSON-safe payload
│
├──────────────► data/payloads/BE/*.json
│
├──────────────► data/payloads/DTA/*.json
│
▼
Canofy / Frappe
│
├── FTWZ BOE Import
└── FTWZ DTA Sale
│
▼
requests.json
│
▼
┌─────────────────┐
│ STEP 3 │
│ Request ID │
│ NSDL Search │
│ View / Print │
│ Original PDF │
└────────┬────────┘
│
▼
data/pdfs/{date}/
│
▼
Attach PDF to FTWZ BOE Import
│
▼
ATTACHED
38. Final Principle
The framework has three separate responsibilities:
STEP 1
Get the authoritative NSDL source files.
STEP 2
Transform those files into validated Frappe documents.
STEP 3
Use the persisted Request IDs to retrieve and attach the BOE PDFs.
The key separation is:
DocType creation ≠ BOE PDF attachment
The BOE PDF is a Step-3 artifact and is attached after the corresponding BE DocType has already been created and its Request ID is available from persisted pipeline state.
39. Quick Install and Run Commands
Use the following commands from the project directory containing main.py.
Install / Update Dependencies
python -m pip install --upgrade pip
python -m pip install -r requirements.txt
python -m playwright install chromium
Check the Frappe OTP Mailbox
python main.py --otp-check --environment TEST
Run Step 1 — NSDL Download
python main.py --run-date 2026-09-12 --environment TEST --steps 1
Run Step 2 — Transform + QC + Frappe DocType Creation
python main.py --run-date 2026-09-12 --environment TEST --steps 2
Run Step 3 — BOE PDF Download + Attachment
python main.py --run-date 2026-09-12 --environment TEST --steps 3
Run All Steps at Once
python main.py --run-date 2026-09-12 --environment TEST --steps all
or:
python main.py --run-date 2026-09-12 --environment TEST
The --steps all command and the command without --steps execute the complete gated pipeline in sequence.