Feature #99
closedFTWZ BOE & DTA SALE - End-to-End Automation Framework
Description
FTWZ End-to-End Automation Framework¶
Automated NSDL SEZ → Canofy/Frappe customs processing with gated
execution, data transformation, QC validation, DocType creation,
Request-ID state tracking, BOE PDF retrieval, and final attachment.
1. Executive Summary¶
The FTWZ Automation Framework was built to convert a collection of
separate, manually executed FTWZ automation scripts into a single,
controlled, repeatable end-to-end processing framework.
The original solution had independent automation assets for:
- Downloading Bill of Entry (BE) and DTA Sale trade-data
exports from NSDL SEZ Online - Transforming those exports into data suitable for Canofy /
Frappe - Creating FTWZ Frappe DocTypes
- Searching NSDL using Request IDs
- Downloading the corresponding BOE print PDFs
- Attaching those PDFs to the appropriate Frappe records
The major engineering work was to connect these independent activities
into one dependency-aware pipeline.
The final framework is centered around a single entry point:
┌──────────────────────────┐
│ main.py │
│ Single Pipeline Runner │
└────────────┬─────────────┘
│
┌──────────────────┼──────────────────┐
│ │ │
▼ ▼ ▼
┌────────────┐ ┌────────────┐ ┌────────────┐
│ STEP 1 │ │ STEP 2 │ │ STEP 3 │
│ NSDL Data │ --> │ Transform │ --> │ BOE PDF │
│ Download │ │ + QC + API │ │ + Attach │
└────────────┘ └────────────┘ └────────────┘
│ │ │
▼ ▼ ▼
BE / DTA Frappe PDF
raw files DocTypes attachment
The critical architectural rule is:
A later step is not allowed to execute until the previous step has
executed and passed its validation gate.
This is not merely a convention. The rule is enforced structurally by
the pipeline runner.
2. Project Objective¶
Business Objective¶
Automate the FTWZ customs-data lifecycle from NSDL SEZ Online into
Canofy / Frappe, reducing manual downloads, transformations,
uploads, document creation, PDF retrieval, and attachment work.
Engineering Objective¶
Create a framework that is:
- End-to-end
- Modular
- Re-runnable
- Auditable
- Validation-driven
- Multi-client capable
- API-ready
- Safe against duplicate creation
- Safe against duplicate PDF attachment
- Testable without live systems
- Structured for eventual AWS migration
Final Business Flow¶
NSDL SEZ Online
│
▼
Download BE + DTA Trade Data
│
▼
Validate Source Files
│
▼
Parse + Filter by Client IEC
│
▼
Group by Request ID
│
▼
Build FTWZ Frappe Payloads
│
▼
Run QC Checks
│
▼
Validate JSON
│
▼
Duplicate / Idempotency Check
│
▼
Create Frappe DocTypes
│
▼
Persist Request IDs + DocNames
│
▼
Search NSDL for BOE Request IDs
│
▼
View / Print Original BOE
│
▼
Generate PDF
│
▼
Validate PDF
│
▼
Attach PDF to FTWZ BOE Import
3. What Was Built¶
The project evolved from multiple standalone scripts into the following
framework:
FTWZ PIPELINE
│
┌───────────────┴───────────────┐
│ │
INPUT / PORTAL Frappe / ERP
│ │
▼ ▼
NSDL SEZ Online Canofy / Frappe
│ │
└───────────────┬───────────────┘
│
▼
┌───────────────────┐
│ main.py │
│ Orchestration │
└─────────┬─────────┘
│
┌───────────────┼────────────────┐
▼ ▼ ▼
STEP 1 STEP 2 STEP 3
Download Transform PDF
+ Validate + QC + Create + Attach
│ │ │
▼ ▼ ▼
Raw Data DocTypes BOE PDFs
│
▼
State Tracking
Major components introduced¶
Component Responsibility
main.py Single command-line entry point and
persisted step-gate enforcement
pipeline/base.py Core execution/validation framework
pipeline/step1_download.py NSDL BE/DTA report download
pipeline/nsdl_parser.py NSDL parsing, filtering, mapping,
grouping and QC
pipeline/step2_transform.py Transform + QC + duplicate check +
Frappe creation
pipeline/step3_pdf_attach.py Request-ID-driven BOE PDF download
and attachment
pipeline/frappe_client.py Frappe REST API client and
idempotency checks
state/store.py Persistent local state and
Request-ID tracking
utils/ocr_engine.py Shared CAPTCHA OCR implementation
utils/nsdl_browser.py Popup protection and reliable NSDL
browser interaction
utils/json_utils.py Recursive JSON normalization,
strict validation and payload audit
utils/logger.py Console + per-run file logging
config/config.py Configuration loading and
credential validation
tests/test_json_utils.py Automated JSON safety regression
test¶
4. Original Problem → Final Solution¶
Before¶
The original implementation consisted of separate scripts:
Script A
NSDL Export
│
└── manual execution
Script B
Excel Transformation
│
└── manual upload / processing
Script C
Request ID → BOE PDF → Frappe attachment
│
└── separate manual execution
There was no reliable shared state connecting the three operations.
The most important dependency was:
Transform + Create
│
▼
Which Request IDs were actually created?
│
▼
Only those Request IDs need BOE PDFs
Therefore, PDF processing could not safely operate as an unrelated
scheduled task.
After¶
The final framework connects them using a controlled stateful pipeline:
┌──────────────────────┐
│ STEP 1 │
│ NSDL source reports │
└──────────┬───────────┘
│
validation
│
▼
┌──────────────────────┐
│ STEP 2 │
│ Parse → QC → Create │
└──────────┬───────────┘
│
state persistence
│
▼
┌──────────────────────┐
│ STEP 3 │
│ Request ID → PDF │
│ → Frappe Attachment │
└──────────────────────┘
5. Core Architectural Principle --- Gated Pipeline¶
The most important framework enhancement is the execute → validate →
continue model.
Every pipeline step implements:
execute(ctx)
validate(ctx, result)
The runner performs:
STEP
│
├── execute()
│
▼
Result
│
├── validate()
│
▼
┌───────────────┐
│ Validation ? │
└───────┬───────┘
│
┌───┴───┐
│ │
PASS FAIL
│ │
▼ ▼
NEXT STOP
The actual framework has a single orchestration point in
pipeline/base.py.
Conceptually:
for step in steps:
result = step.execute(ctx)
verdict = step.validate(ctx, result)
if not verdict.passed:
stop_pipeline()
Why this matters¶
A failed Step 1 cannot silently feed bad files into Step 2.
A failed Step 2 cannot allow Step 3 to process an incomplete set of
created documents.
This makes the dependency chain explicit and enforceable.
6. Persisted Gates Across Separate Commands¶
The framework does not only protect steps within one Python process.
The run state is persisted using the run date.
Therefore:
Terminal Session 1
------------------
python main.py --run-date 2026-09-12 --steps 1
│
▼
Step 1 PASS
│
▼
state.json
│
│
Terminal Session 2
------------------
python main.py --run-date 2026-09-12 --steps 2
│
▼
Read persisted state
│
Step 1 = PASSED
│
▼
Step 2 allowed
If Step 1 has not passed:
--steps 2
│
▼
Read state
│
▼
Step 1 not PASSED
│
▼
REFUSE TO RUN
The same rule applies to Step 3.
This makes the pipeline safe even when individual steps are run hours
apart.
7. Step 1 --- NSDL Trade Data Automation¶
Responsibility¶
Step 1 obtains the authoritative source data from NSDL SEZ Online.
It handles both:
- Bill of Entry
- DTA Sale
Live flow¶
NSDL Login
│
▼
CAPTCHA
│
▼
OCR Processing
│
▼
Trade Data
│
├──────────────► BILL OF ENTRY
│
└──────────────► DTA SALE
│
▼
Download files
│
▼
Validate files
8. NSDL Browser Automation¶
The browser automation is implemented with Playwright.
The framework uses Chromium and supports:
headless = false
for visible debugging/local execution, and:
headless = true
for unattended execution.
The browser context also enables downloads and disables the normal
viewport restriction required by the NSDL automation.
9. CAPTCHA Automation¶
The NSDL login flow requires CAPTCHA handling.
A reusable OCR engine was extracted into:
utils/ocr_engine.py
Instead of maintaining separate CAPTCHA implementations in different
scripts, the final framework shares one implementation.
CAPTCHA processing pipeline¶
CAPTCHA image
│
▼
Resize 2.5×
│
▼
Grayscale
│
▼
Bilateral filtering
│
├─────────────┐
▼ ▼
Enhanced Otsu
│ │
└──────┬──────┘
▼
Adaptive
threshold
│
▼
EasyOCR
│
▼
Remove non-alphanumeric
│
▼
Confidence-weighted candidates
│
▼
Consensus result
The engine also:
- validates CAPTCHA length
- validates alphanumeric content
- supports retry attempts
- can refresh/retry the CAPTCHA
- uses a lazy EasyOCR reader so the model is not repeatedly
initialized within the same process
10. NSDL Popup / Overlay Protection¶
A practical issue discovered from the working NSDL automation was that
portal overlays could interfere with browser clicks.
The final framework centralizes this handling in:
utils/nsdl_browser.py
It provides:
- popup removal
- Driver.js overlay protection
- modal-backdrop removal
- body/document scrolling recovery
- pointer-event recovery
- welcome-tour handling
- reliable click fallback
Safe click model¶
Requested Click
│
▼
Clean Popup / Overlay
│
▼
Scroll into View
│
▼
Force Click
│
failure
▼
Clean Again
│
▼
Normal Click
This reduces the chance of an otherwise-correct automation failing
because a portal overlay is sitting above the target element.
11. Step 1 Validation Gate¶
Step 1 does not simply check that a file exists.
It validates:
- BE file exists
- DTA file exists
- Files are non-empty
- Files can be read in the actual NSDL format
- Required parser columns exist
- Row counts are recorded
- Zero-row behavior is explicitly handled
Gate¶
BE file ──┐
├──> readable? ──> schema valid? ──> PASS
DTA file ─┘
A schema change in the NSDL export should therefore be detected before
the transformation layer receives the data.
12. Important Real-Data Discovery --- .xls Is Not Actually Excel¶
One of the most important findings came from inspecting the actual NSDL
files.
The files use:
.xls
as the filename extension, but their content is actually:
tab-delimited text
with metadata lines before the header.
Therefore, the correct parser is:
pd.read_csv(
file_path,
sep="\t",
skiprows=2,
dtype=str,
engine="python"
)
The framework deliberately avoids treating these files as native binary
Excel workbooks.
This removed the unnecessary dependency on the old Windows Excel
conversion flow.
13. Real-Data Discovery --- IEC Must Be Treated as a String¶
The actual NSDL data contains client IEC values such as:
0300014953
The leading zero is meaningful.
Treating this value as an integer:
300014953
would cause the comparison to fail.
The final parser therefore performs string-based filtering:
NSDL CLIENT IEC
│
▼
strip whitespace
│
▼
string comparison
│
▼
configured IEC list
│
▼
matching rows
Configuration supports multiple client IEC codes:
client_iec_codes:
- "0300014953"
This makes the parser suitable for multi-client/multi-tenant source
files.
14. Step 2 --- Transform + QC + Frappe Creation¶
Step 2 is the central data-engineering stage.
Raw BE / DTA export
│
▼
Pandas DataFrame
│
▼
Client IEC filtering
│
▼
Request ID grouping
│
▼
Header transformation
│
├── Items
├── Invoices
├── Shipments
└── Attachments
│
▼
Business QC
│
▼
JSON normalization
│
▼
Strict JSON validation
│
▼
Frappe duplicate check
│
▼
Frappe REST POST
│
▼
Persist created record
15. Request ID as the Primary Transaction Key¶
The NSDL Request ID is the central identifier used to group source
rows.
Example:
Request ID
│
├── Header information
├── Item rows
├── Invoice rows
├── Shipment information
└── Attachment placeholders
│
▼
One Frappe record
This converts multiple flat Excel rows into one logical Frappe
transaction.
16. FTWZ BOE Import Payload¶
BE rows are transformed into:
FTWZ BOE Import
The payload contains parent-level information and child structures such
as:
FTWZ BOE Import
│
├── Parent fields
│
├── BOE Import Items
│
├── BOE Import Invoices
│
├── BOE Import Shipment
│
└── Attachments Details
The item transformation includes the mapped customs/duty information
from the NSDL export.
The parser also handles PO extraction from the item description when the
source follows the embedded pattern.
17. FTWZ DTA Sale Payload¶
DTA rows are transformed into:
FTWZ DTA Sale
with:
FTWZ DTA Sale
│
├── Parent fields
│
├── DTA Sale Items
│
├── DTA Sale Invoices
│
├── DTA Sale Shipments
│
└── Attachments Details
The DTA mapping accounts for its different source structure, including
buyer-specific fields.
18. Field Transformation¶
The parser performs multiple transformation operations.
Dates¶
NSDL dates are normalized to:
YYYY-MM-DD
Multiple source formats are handled before falling back to controlled
date parsing.
Numeric values¶
Blank numeric values are normalized according to the parser's business
rules.
PO extraction¶
Embedded item descriptions can contain:
^PO_NUMBER/ITEM_NUMBER^
which is converted into:
po_number
po_item_no
item_subtext
Duty mapping¶
The framework contains an explicit mapping from NSDL duty columns to
Canofy field names.
This allows the raw customs-duty columns to be converted into the Frappe
child-table structure.
19. Five-Level QC Model¶
Step 2 contains five core QC categories.
QC ENGINE
│
┌───────────┼────────────┐
▼ ▼ ▼
Header Invoice Item
Count Count Count
│ │ │
└───────────┼────────────┘
│
▼
Shipment Count
│
▼
Sum Validation
│
┌───────────┼────────────┐
▼ ▼ ▼
Quantity BCD Assessable
The checks compare source counts/sums with the transformed output.
The purpose is to catch data loss or duplication during transformation.
20. QC Gate¶
When strict QC is enabled:
step2_qc_strict: true
any failed QC check stops the pipeline.
QC
│
├── PASS → continue
│
└── FAIL → STOP
The framework therefore follows:
Bad transformation data must not silently reach Canofy.
21. JSON Safety Layer¶
A real-data issue was discovered during testing.
Pandas may represent blank cells as:
numpy.nan
pandas.NaT
pandas.NA
Strict JSON does not permit NaN.
The framework introduced:
utils/json_utils.py
to solve this.
Conversion¶
Pandas / NumPy values
│
▼
make_json_safe()
│
├── NaN → None
├── NA → None
├── NaT → None
├── np.int → int
├── np.float → float
├── np.bool → bool
└── date/datetime → ISO string
│
▼
Strict JSON
Missing data remains semantically missing:
Excel blank
↓
JSON null
rather than being incorrectly converted to:
0
22. Strict JSON Validation¶
Before the HTTP request:
json.dumps(payload, allow_nan=False)
is used to verify that the payload is valid strict JSON.
Therefore:
Invalid payload
│
▼
Detected locally
│
▼
No HTTP write
This is an important defensive layer between pandas and the Frappe API.
23. Payload Audit Files¶
The exact sanitized outbound payload is written locally for
audit/debugging.
data/
└── payloads/
├── BE/
│ └── <request_id>.json
│
└── DTA/
└── <request_id>.json
This provides a reproducible representation of what the framework
intended to send to Frappe.
The audit file is not the authoritative Frappe document; it is a local
trace of the outbound request.
24. Frappe REST API Integration¶
The framework contains a dedicated:
pipeline/frappe_client.py
instead of embedding HTTP operations directly inside the transformation
step.
This client handles:
- Frappe resource lookup
- duplicate checks
- document creation
- PDF attachment checks
- PDF upload
Creation flow¶
Transformed Payload
│
▼
Request ID lookup
│
├── Exists → Skip
│
└── Does not exist
│
▼
POST JSON
│
▼
Frappe DocType
The Frappe API endpoint is dynamically constructed from the configured
base URL and DocType.
25. Idempotency¶
The framework was deliberately designed to be re-runnable.
There are three important protection layers.
Layer 1 --- Frappe record existence¶
Before creating a document:
Does Request ID already exist?
If yes:
Skip creation
Layer 2 --- Local persisted state¶
Step 3 reads the persisted Request-ID state rather than rediscovering
everything from scratch.
Layer 3 --- Attachment existence¶
Before uploading a PDF:
Does this filename already exist
on this Frappe document?
If yes:
Skip duplicate upload
Combined model¶
Re-run
│
▼
┌─────────────────┐
│ Record exists? │
└───────┬─────────┘
│
yes ───┴─── no
│ │
▼ ▼
Skip Create
│
▼
Persist state
│
▼
Step 3 later
│
▼
PDF exists?
│ │
yes no
│ │
skip attach
26. Step 2 → Step 3 Handoff¶
This is one of the most important framework design decisions.
Step 3 should process only the BE Request IDs for which Step 2
successfully created a Frappe record.
The framework therefore persists:
Request ID
Doctype
Pipeline
Client IEC
Run Date
Status
Frappe DocName
PDF Path
Error Detail
Timestamp
This removes the need for an external Excel handoff file.
27. Local State Store¶
The current local implementation is:
state/store.py
It acts as the local persistence layer for the pipeline.
The state is stored under:
data/logs/{run_date}/
with:
state.json
requests.json
artifacts.json
run.log
28. Request Lifecycle¶
Each Request ID progresses through a controlled state model.
PARSED
│
▼
VALIDATED
│
▼
DOCTYPE_CREATED
│
▼
PDF_DOWNLOADED
│
▼
ATTACHED
Failure can move a record into:
FAILED
Practical BE lifecycle¶
NSDL Request ID
│
▼
Filtered
│
▼
Parsed
│
▼
QC passed
│
▼
Frappe DocType created
│
▼
State persisted
│
▼
NSDL PDF retrieved
│
▼
PDF validated
│
▼
PDF attached
│
▼
ATTACHED
29. Why State Persistence Was Necessary¶
A simple in-memory list would not be enough.
For example:
10:00 AM
Step 2 creates 40 BE records
12:00 PM
Python process exits
2:00 PM
Step 3 starts
Step 3 still needs to know:
Which 40 Request IDs were created?
What DocType was created?
What Frappe document corresponds to each Request ID?
Which PDFs are already attached?
The persisted state store solves this.
30. Step 3 --- BOE PDF Automation¶
Step 3 is specifically for BE records.
DTA records do not enter this BOE PDF workflow in the current design.
Flow¶
State Store
│
▼
Find BE records with:
DOCTYPE_CREATED
│
▼
Request ID list
│
▼
NSDL Login
│
▼
Search Request
│
▼
Open exact Request ID
│
▼
View / Print BOE
│
▼
Select Original
│
▼
Open print page
│
▼
Generate PDF
│
▼
Validate PDF
│
▼
Upload to Frappe
│
▼
Attach to matching FTWZ BOE Import
│
▼
ATTACHED
31. Request ID → PDF Retrieval¶
The portal interaction is deliberately tied to the persisted Request ID.
Request ID
│
▼
Search Request
│
▼
Exact Request ID match
│
▼
View / Print
│
▼
Original checkbox
│
▼
OK
│
▼
New print page
│
▼
page.pdf()
The generated PDF is stored as:
data/pdfs/{run_date}/{request_id}.pdf
32. PDF Validation¶
The framework does not assume that a successful browser operation means
a valid PDF was produced.
It verifies:
File exists
+
File size > 0
+
PDF header == %PDF-
Therefore:
Download reported successful
│
▼
File exists?
│
▼
Non-empty?
│
▼
%PDF- header?
│
▼
PASS
33. PDF Attachment¶
The Frappe client performs a final duplicate check before uploading.
PDF
│
▼
Check existing Frappe File
│
├── Already attached → Skip
│
└── Not attached
│
▼
upload_file
│
▼
Attach to document
This protects the system from duplicate attachments during retries.
34. Separation of Responsibilities¶
A key final design rule is:
DocType creation
≠
BOE PDF attachment
Step 2 creates:
FTWZ BOE Import
FTWZ DTA Sale
Step 3 subsequently retrieves and attaches the BOE PDF for successfully
created BE records.
The framework deliberately does not create a "Bill of Entry"
attachment placeholder during Step 2.
The normal Step-2 attachment placeholders are:
AWB_HBL
Invoice
Packing List
The actual BOE PDF is handled by Step 3.
35. Multi-Client Architecture¶
The parser supports a configurable list of client IECs.
client_iec_codes:
- "0300014953"
- "..."
The source file may contain records belonging to multiple clients.
The filtering model is:
NSDL Export
│
▼
All Clients Mixed
│
▼
CLIENT IEC filter
│
▼
Configured clients only
│
▼
Request ID grouping
This is more scalable than maintaining a separate parser loop for every
client.
36. Date Range Configuration¶
The framework supports:
--run-date--from-date--to-date- YAML-configured NSDL date range
Priority is:
CLI --from-date / --to-date
│
▼
config.yaml date range
│
▼
run_date as default single-day range
The NSDL UI expects:
dd/mm/yyyy
while the pipeline run identifier uses:
YYYY-MM-DD
37. Configuration Architecture¶
Configuration is centralized in:
config/config.yaml
config/config.sample.yaml
config/config.py
The configuration covers:
- NSDL URL
- Client IEC codes
- NSDL module IDs
- Date range
- Headless mode
- Login retry limits
- CAPTCHA retry limits
- Print/PDF retry controls
- Rate-limit delays
- QC strictness
- Success-rate thresholds
- Frappe environment credentials
Credential priority¶
Environment Variables
│
▼
config.yaml
│
▼
Application
Real credentials should not be committed to source control.
38. Dry-Run Mode¶
A full pipeline should be testable before connecting to live systems.
The framework therefore supports:
python main.py --run-date 2026-09-12 --environment TEST --dry-run --steps all
Dry-run behavior:
Step 1
↓
Use sample NSDL files
↓
Step 2
↓
Create fake Frappe docnames
↓
No live HTTP writes
↓
Step 3
↓
Generate valid placeholder PDFs
↓
Run PDF validation
↓
Complete all gates
This allows the entire architecture and gate logic to be tested without
live NSDL or Canofy access.
39. Logging and Auditability¶
Every run creates a log directory:
data/logs/{run_date}/
with:
state.json
requests.json
artifacts.json
run.log
The logger writes to:
Console
+
run.log
Each step records:
- Start
- Completion
- Errors
- Validation result
- Validation details
- Failed Request IDs
- Counts
- Success rates
- State changes
This provides a traceable execution history.
40. Artifact Architecture¶
The framework intentionally keeps different classes of artifacts
separate.
data/
│
├── raw/
│ └── {run_date}/
│ ├── BillOfEntry_*.xls
│ └── DTASale_*.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
This makes the pipeline observable at every stage.
41. Complete Project Structure¶
fw_verify/
│
├── main.py
├── README.md
├── requirements.txt
│
├── config/
│ ├── config.py
│ ├── config.yaml
│ └── config.sample.yaml
│
├── pipeline/
│ ├── __init__.py
│ ├── base.py
│ ├── nsdl_parser.py
│ ├── step1_download.py
│ ├── step2_transform.py
│ ├── step3_pdf_attach.py
│ └── frappe_client.py
│
├── state/
│ ├── __init__.py
│ └── store.py
│
├── utils/
│ ├── __init__.py
│ ├── logger.py
│ ├── nsdl_browser.py
│ ├── ocr_engine.py
│ └── json_utils.py
│
├── tests/
│ └── test_json_utils.py
│
├── sample_data/
│ ├── BillOfEntry_*.xls
│ └── DTASale_*.xls
│
└── data/
├── raw/
├── parsed/
├── payloads/
├── pdfs/
└── logs/
42. Module Responsibilities¶
main.py¶
The command-line controller.
Responsibilities:
- Parse arguments
- Resolve run date
- Resolve environment
- Load configuration
- Resolve requested steps
- Check persisted prerequisite gates
- Instantiate pipeline steps
- Execute the pipeline
- Print final status
pipeline/base.py¶
The framework's orchestration engine.
Contains:
PipelineContext
PipelineStep
StepResult
ValidationResult
run_pipeline()
This is the central gate enforcement layer.
pipeline/step1_download.py¶
Responsible for:
- NSDL browser startup
- Login
- CAPTCHA
- BE export
- DTA export
- File storage
- Step-1 validation
pipeline/nsdl_parser.py¶
Responsible for:
- NSDL text-based
.xlsparsing - IEC filtering
- Request ID grouping
- BE transformation
- DTA transformation
- Date normalization
- Numeric normalization
- PO extraction
- Duty mapping
- QC generation
pipeline/step2_transform.py¶
Responsible for:
- Calling BE parser
- Calling DTA parser
- Running Frappe duplicate checks
- Creating Frappe records
- Persisting created Request IDs
- Enforcing Step-2 QC and creation gates
pipeline/frappe_client.py¶
Responsible for:
- Frappe REST communication
- Record existence checks
- Record creation
- PDF attachment existence checks
- PDF upload
pipeline/step3_pdf_attach.py¶
Responsible for:
- Reading pending BE Request IDs
- NSDL login
- Request search
- BOE print
- PDF generation
- PDF validation
- Frappe attachment
- Final attachment validation
state/store.py¶
Responsible for:
- Step state
- Request state
- Artifact state
- Persistent run information
- Cross-command handoff
utils/ocr_engine.py¶
Responsible for shared CAPTCHA OCR.
utils/nsdl_browser.py¶
Responsible for browser-level popup protection and safe interactions.
utils/json_utils.py¶
Responsible for:
- JSON normalization
- strict serialization validation
- outbound payload audit files
utils/logger.py¶
Responsible for structured console and file logging.
43. Error Handling Model¶
The framework isolates failures at multiple levels.
Step-level failure¶
Step fails
↓
Validation fails
↓
Pipeline stops
Request-level failure¶
For Step 2 and Step 3, individual records are isolated where
appropriate:
Request A → success
Request B → failure
Request C → success
Request D → success
The failed request is recorded instead of immediately killing every
other request in the batch.
The final validation gate then determines whether the overall step meets
the configured success threshold.
44. Success-Rate Gates¶
The framework tracks:
total_attempted
succeeded
failed_count
success_rate
required_threshold
The current configuration uses:
Step 2 minimum creation success rate = 100%
Step 3 minimum PDF success rate = 100%
Therefore:
100% ──> PASS
99% ──> FAIL
unless the threshold is intentionally changed through configuration.
This prevents a partially successful batch from being reported as a
fully successful pipeline run.
45. Failure Example --- Proving the Gate¶
A deliberate Step-1 schema failure was tested by removing the
CLIENT IEC column from a copy of the source file.
Expected behavior:
Step 1
│
▼
Missing CLIENT IEC
│
▼
Validation FAILED
│
▼
PIPELINE HALTED
│
▼
Step 2 NOT INVOKED
│
▼
Step 3 NOT INVOKED
This verified that the gate is an actual execution barrier rather than a
logging-only mechanism.
46. Real-Data Testing and Verified Results¶
The framework was tested using actual NSDL export data and the current
packaged runtime artifacts.
A recorded run included:
BE source rows : 569
DTA source rows : 2413
A subsequent Step-2 execution recorded:
82 / 82 records created or confirmed
100% success
BE QC : PASS
DTA QC : PASS
A Step-3 execution recorded:
8 / 8 PDFs downloaded, validated and attached
100% success
The packaged run state currently contains:
Total persisted Request IDs : 111
BE : 40
DTA : 71
ATTACHED : 40
DOCTYPE_CREATED : 71
The state represents accumulated processing for the run date across
multiple step invocations, rather than one isolated single command
execution.
47. Important Testing Distinction¶
The project contains both offline/dry-run verification and live
TEST-environment evidence.
Dry-run¶
Verifies:
- Parser
- Transformation
- QC
- JSON normalization
- Gate enforcement
- State persistence
- Frappe integration flow without real writes
- PDF validation logic
Live TEST environment¶
The current packaged artifacts also contain evidence of:
- NSDL browser interaction
- NSDL login retry behavior
- Real BE PDF downloads
- Frappe document creation
- PDF attachment
- persisted Request-ID states
Production should still be treated as a separate deployment/verification
stage.
48. Automated Regression Test¶
The framework includes:
tests/test_json_utils.py
The test verifies that nested missing values are converted into
JSON-safe null values and that invalid NaN content does not survive
serialization.
The test covers values including:
numpy.nan
pandas.NA
pandas.NaT
numpy.float
date
This specifically protects the real-data bug discovered during
development.
49. Installation¶
From the directory 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
The dependency set includes:
pandas
numpy
requests
PyYAML
Playwright
EasyOCR
OpenCV
50. Step-by-Step Execution¶
Step 1¶
python main.py --run-date 2026-09-12 --environment TEST --steps 1
Purpose:
NSDL login
→ BE export
→ DTA export
→ source validation
Step 2¶
After Step 1 has passed:
python main.py --run-date 2026-09-12 --environment TEST --steps 2
Purpose:
Parse
→ filter
→ group
→ QC
→ JSON validation
→ duplicate check
→ Frappe creation
→ state persistence
Step 3¶
After Step 2 has passed:
python main.py --run-date 2026-09-12 --environment TEST --steps 3
Purpose:
Request IDs
→ NSDL search
→ BOE print
→ PDF
→ PDF validation
→ Frappe attachment
51. Run the Entire Framework¶
All steps:
python main.py --run-date 2026-09-12 --environment TEST --steps all
or:
python main.py --run-date 2026-09-12 --environment TEST
The second form uses all as the default.
52. Check Current Pipeline Status¶
python main.py --run-date 2026-09-12 --environment TEST --status
This does not execute any pipeline step.
It displays:
Step 1 → PASSED / FAILED / never run
Step 2 → PASSED / FAILED / never run
Step 3 → PASSED / FAILED / never run
53. Dry-Run Execution¶
For offline end-to-end validation:
python main.py --run-date 2026-09-12 --environment TEST --dry-run --steps all
The sample files are automatically discovered from:
sample_data/
using BE/DTA filename patterns.
54. Command-Line Execution Model¶
main.py
│
┌────────────┴────────────┐
│ │
--steps 1 --steps all
│ │
▼ ▼
Step 1 Step 1 → Step 2
→ Step 3
│
persisted state
│
▼
--steps 2
│
prerequisite check
│
▼
Step 2
│
persisted state
│
▼
--steps 3
│
prerequisite check
│
▼
Step 3
55. Local Architecture vs AWS Target Architecture¶
The local framework was intentionally designed as a precursor to an
eventual AWS deployment.
Current¶
Local Python
│
├── JSON state
├── local raw files
├── local payload audit
├── local PDFs
└── local logs
Future¶
AWS
│
├── Step Functions
│ │
│ ├── Lambda / container: Step 1
│ ├── Lambda / container: Step 2
│ └── Lambda / container: Step 3
│
├── DynamoDB
│ └── Request state
│
├── S3
│ ├── Raw NSDL exports
│ ├── JSON payloads
│ └── BOE PDFs
│
├── Secrets Manager
│ └── Credentials
│
└── EventBridge
└── Scheduling
The local state/store.py abstraction is intentionally separated from
the rest of the application so that the persistence implementation can
later be replaced by DynamoDB.
56. Designed AWS Mapping¶
Current Local Component Future AWS Equivalent
main.py Step Functions orchestration
pipeline/base.py State-machine gating
step1_download.py Lambda/container workload
step2_transform.py Lambda/container workload
step3_pdf_attach.py Lambda/container workload
state/store.py DynamoDB
data/raw/ S3
data/payloads/ S3
data/pdfs/ S3
config.yaml secrets Secrets Manager
Local scheduling EventBridge
This AWS architecture was designed as the target deployment model; the
current executable framework is the local gated implementation.
57. Why Step Functions Was Identified as the Natural Future Orchestrator¶
The pipeline has a real dependency graph:
Step 1
│
▼
Step 2
│
▼
Step 3
and each step has:
- execution
- validation
- failure handling
- state
- retry requirements
This is fundamentally an orchestration problem rather than three
unrelated scheduled scripts.
The local framework proves the orchestration model first.
58. Operational Safety¶
The framework contains several safety controls.
Source safety¶
Validate source structure before transformation
Data safety¶
QC before Frappe creation
API safety¶
Duplicate check before create
State safety¶
Persist successful Request IDs
PDF safety¶
Validate actual PDF bytes
Attachment safety¶
Check attachment before upload
Pipeline safety¶
Failed gate → STOP
59. End-to-End Dependency Graph¶
┌───────────────────┐
│ NSDL SEZ Online │
└─────────┬─────────┘
│
Playwright
│
CAPTCHA OCR
│
▼
┌───────────────────┐
│ STEP 1 │
│ BE + DTA exports │
└─────────┬─────────┘
│
Validation
│
▼
┌───────────────────┐
│ Raw NSDL Files │
└─────────┬─────────┘
│
pandas
│
▼
┌───────────────────┐
│ STEP 2 │
│ Parse + Transform │
└─────────┬─────────┘
│
IEC Filter + Grouping
│
▼
┌───────────────────┐
│ QC Engine │
└─────────┬─────────┘
│
JSON Safe
│
▼
┌───────────────────┐
│ Frappe REST API │
└─────────┬─────────┘
│
FTWZ BOE / DTA
│
▼
┌───────────────────┐
│ Persist State │
└─────────┬─────────┘
│
Request IDs
│
▼
┌───────────────────┐
│ STEP 3 │
│ BOE PDF retrieval │
└─────────┬─────────┘
│
NSDL Search
│
View / Print
│
▼
BOE PDF
│
PDF Validate
│
▼
┌───────────────────┐
│ Frappe File │
│ Attachment │
└───────────────────┘
60. Detailed Data Lifecycle¶
SOURCE
│
▼
NSDL Trade Data
│
▼
Raw `.xls` files
│
▼
Parser / DataFrame
│
▼
Client IEC Filter
│
▼
Request ID Grouping
│
┌───────┴────────┐
▼ ▼
BE DTA
│ │
▼ ▼
FTWZ BOE Import FTWZ DTA Sale
│ │
└───────┬────────┘
▼
QC PASS
│
▼
JSON-safe payload
│
▼
Frappe API
│
▼
Frappe document
│
▼
Persist Request ID
│
BE only ────────┐
▼
NSDL Request Search
│
▼
BOE Print
│
▼
PDF
│
▼
Frappe Attachment
61. Framework Characteristics¶
The final implementation provides:
Characteristic Implementation
Single command main.py
Modular steps Step 1 / 2 / 3 classes
Hard dependency gates run_pipeline()
Cross-command gates persisted state
Multi-client configurable IEC list
Real NSDL parsing tab-delimited .xls support
Data normalization parser + JSON utility
QC five-check validation model
API integration dedicated Frappe client
Duplicate prevention record + state + attachment checks
Auditability state, requests, artifacts, logs, payloads
Offline testing dry-run
Browser automation Playwright
CAPTCHA automation EasyOCR + OpenCV
PDF validation existence + size + %PDF- header
AWS readiness state abstraction + separated pipeline layers
62. Known Scope Boundaries¶
The final framework is intentionally explicit about what it does and
does not do.
It does¶
- Download BE and DTA NSDL trade-data exports
- Parse real NSDL exports
- Filter configured client IECs
- Group by Request ID
- Build FTWZ Frappe payloads
- Run QC
- Validate strict JSON
- Create Frappe DocTypes
- Persist processing state
- Retrieve BE BOE PDFs
- Validate PDFs
- Attach PDFs to the corresponding Frappe records
- Avoid duplicate creation/attachment
- Run step-by-step or all at once
It does not currently¶
- Deploy itself to AWS
- Run as an EventBridge scheduled job
- Use DynamoDB instead of the local JSON state store
- Use S3 instead of local artifact storage
- Use AWS Secrets Manager
- Automatically submit the created Frappe DocType
- Process DTA records through the BE-style BOE PDF attachment flow
These are deployment/extension concerns rather than missing pieces of
the local framework.
63. Security Considerations¶
Credentials are configuration-driven.
Do not commit production credentials into:
config/config.yaml
Preferred production pattern:
AWS Secrets Manager
│
▼
Runtime environment
│
▼
Pipeline
The current framework already supports environment variables for
sensitive credentials.
64. Development Evolution¶
The project can be summarized as five engineering phases.
PHASE 1
Existing standalone scripts
│
▼
PHASE 2
Understand real dependency chain
│
▼
PHASE 3
Design AWS target architecture
│
▼
PHASE 4
Build local gated framework
│
▼
PHASE 5
Test against real NSDL data
and fix real-data failures
The important point is that the final solution was not just a wrapper
around the old scripts.
The transformation and orchestration logic were redesigned around the
actual source format, Request-ID lifecycle, validation gates, and Frappe
REST model.
65. Key Problems Discovered and Fixed¶
Problem 1 --- Incorrect .xls assumption¶
Original assumption¶
.xls = native Excel workbook
Actual NSDL behavior¶
.xls = tab-delimited text export
Final solution¶
pd.read_csv(..., sep="\t", skiprows=2)
Problem 2 --- IEC type mismatch¶
Original behavior¶
NSDL → "0300014953"
Code → 300014953
Result¶
No matching rows
Final solution¶
String-based comparison
+
configurable IEC list
Problem 3 --- Pandas NaN in JSON¶
Result¶
Out of range float values are not JSON compliant: nan
Final solution¶
Recursive JSON normalization
+
strict JSON validation
Problem 4 --- Invalid BOE attachment placeholder¶
Invalid Step-2 placeholder¶
Bill of Entry
Problem¶
The target Frappe Attachment Type did not contain the expected linked
value.
Final solution¶
Step 2:
AWB_HBL
Invoice
Packing List
Step 3:
Actual BOE PDF attachment
Problem 5 --- No durable handoff¶
Before¶
Excel / manual Request ID list
Final solution¶
requests.json
+
state.json
This makes Step 2 → Step 3 a durable programmatic handoff.
66. Final Acceptance Model¶
A successful run means:
STEP 1
Source files valid
│
▼
STEP 1 PASS
│
▼
STEP 2
QC + creation success threshold
│
▼
STEP 2 PASS
│
▼
STEP 3
PDF validation + attachment threshold
│
▼
STEP 3 PASS
│
▼
PIPELINE COMPLETE
The framework does not consider:
Step 1 succeeded
to mean:
Entire pipeline succeeded
The final status requires every requested step to execute and pass its
own validation.
67. Final Project Outcome¶
The completed work transformed the FTWZ process from a collection of
manually executed automation scripts into a single, stateful,
validation-gated automation framework.
The final architecture provides:
┌────────────────────────────┐
│ FTWZ AUTOMATION │
└──────────────┬─────────────┘
│
┌───────────────────────┼───────────────────────┐
│ │ │
▼ ▼ ▼
NSDL Automation Data Engineering Frappe Integration
│ │ │
├── Login ├── Parse ├── Duplicate Check
├── CAPTCHA OCR ├── IEC Filter ├── Create DocType
├── BE Export ├── Group Request ID ├── Persist State
├── DTA Export ├── Map Fields ├── PDF Check
└── PDF Retrieval ├── QC └── Attach PDF
└── JSON Validation
│
▼
Validation Gates
│
▼
Auditable Pipeline
The result is a reusable foundation for moving from manual FTWZ customs
processing toward unattended, scheduled automation.
68. One-Line Project Description¶
Designed and implemented a gated FTWZ end-to-end automation
framework that extracts BE/DTA trade data from NSDL SEZ Online,
transforms and validates it into Request-ID-based FTWZ Frappe
DocTypes, persists execution state, retrieves corresponding BOE PDFs,
validates and attaches them to the created records, while providing
idempotency, audit logging, dry-run testing, multi-client filtering,
and an architecture ready for AWS orchestration.
69. Quick Reference¶
Install¶
python -m pip install --upgrade pip
python -m pip install -r requirements.txt
python -m playwright install chromium
Step 1¶
python main.py --run-date 2026-09-12 --environment TEST --steps 1
Step 2¶
python main.py --run-date 2026-09-12 --environment TEST --steps 2
Step 3¶
python main.py --run-date 2026-09-12 --environment TEST --steps 3
All Steps¶
python main.py --run-date 2026-09-12 --environment TEST --steps all
Status¶
python main.py --run-date 2026-09-12 --environment TEST --status
Offline End-to-End¶
python main.py --run-date 2026-09-12 --environment TEST --dry-run --steps all
Project Status¶
Framework implementation: Completed
Local gated pipeline: Implemented
NSDL BE/DTA parsing: Implemented and verified against real export
structure
Frappe REST integration: Implemented
Request-ID state handoff: Implemented
BOE PDF download and attachment: Implemented
Dry-run validation: Implemented
Real TEST-environment execution evidence: Present in the packaged
run artifacts
AWS deployment: Architecture prepared; deployment remains a separate
production transition
Files
RG Updated by Rajesh Gotur 22 days ago
- Tracker changed from Task to Feature
- Status changed from New to Closed
- % Done changed from 90 to 100