Feature #99 » README_final_ftwz_pipeline.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 BE + DTA trade-data reports | Files exist, are non-empty, readable, and contain the required parser columns |
| 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
↓
Reports
↓
Trade Data
↓
BILL OF ENTRY export
↓
DTA SALE export
↓
Download validation
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
│
└── 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
max_captcha_read_attempts: 6
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: "..."
TEST:
NSDL_USERNAME: "..."
NSDL_PASSWORD: "..."
FRAPPE_URL: "..."
FRAPPE_API_KEY: "..."
FRAPPE_API_SECRET: "..."
PROD:
NSDL_USERNAME: "..."
NSDL_PASSWORD: "..."
FRAPPE_URL: "..."
FRAPPE_API_KEY: "..."
FRAPPE_API_SECRET: "..."
Credentials can also be injected through environment variables:
NSDL_USERNAME
NSDL_PASSWORD
FRAPPE_API_KEY
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.
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
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.