# 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:

```text
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:

```text
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:

```powershell
python main.py --run-date 2026-09-12 --environment TEST --steps 1
```

can be executed first, and Step 2 can be executed later:

```powershell
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:

```text
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:

```yaml
headless: false
```

For unattended execution:

```yaml
headless: true
```

---

# 5. Step 1 Output Files

For run date `2026-09-12`:

```text
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:

```yaml
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:

```text
0300014953
```

rather than as:

```text
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:

```text
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:

```text
REQUEST ID
    ↓
FTWZ BOE Import
```

or:

```text
REQUEST ID
    ↓
FTWZ DTA Sale
```

The child tables are then attached to that parent structure.

---

# 10. Frappe DocType Structures

## BE

Target parent:

```text
FTWZ BOE Import
```

Child tables:

```text
BOE Import Items
BOE Import Invoices
BOE Import Shipment
Attachments Details
```

The payload contains:

```text
parent fields
items[]
invoices[]
table_all_shipment_details[]
attachments[]
```

## DTA

Target parent:

```text
FTWZ DTA Sale
```

Child tables:

```text
DTA Sale Items
DTA Sale Invoices
DTA Sale Shipments
Attachments Details
```

The payload contains:

```text
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:

```text
AWB_HBL
Invoice
Packing List
```

The framework must not create:

```text
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:

```text
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:

```text
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:

```text
data/
└── payloads/
    └── BE/
        └── <request_id>.json
```

DTA payloads:

```text
data/
└── payloads/
    └── DTA/
        └── <request_id>.json
```

Example:

```text
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:

```python
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:

```text
NaN
 ↓
None
 ↓
JSON null
```

Other conversions include:

```text
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:

```text
parent fields
items[]
invoices[]
shipments[]
attachments[]
```

After normalization, strict validation is performed before the HTTP request.

The intent is:

```text
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:

```text
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:

```text
Excel blank
→ JSON null
```

not:

```text
Excel blank
→ 0
```

---

# 16. Example DTA Document Shape

A DTA payload follows the same nested structure expected by the Canofy DocType:

```json
{
  "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:

```text
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:

```text
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:

```text
PARSED
   ↓
DOCTYPE_CREATED
   ↓
PDF_DOWNLOADED
   ↓
ATTACHED
```

with failures recorded as:

```text
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:

```text
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:

```text
local data/
    ↓
future S3
```

and:

```text
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:

```text
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:

```text
duplicate DocType
duplicate PDF
duplicate attachment
```

when a run is repeated.

---

# 20. Data Folder Structure

A typical run produces:

```text
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:

```text
config/config.yaml
```

Typical structure:

```yaml
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:

```text
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`:

```powershell
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:

```powershell
Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass
.\venv\Scripts\Activate.ps1
```

---

# 23. Run Step 1 Only

For the current test date:

```powershell
python main.py --run-date 2026-09-12 --environment TEST --steps 1
```

Expected output files:

```text
data/raw/2026-09-12/BillOfEntry_2026-09-12.xls
data/raw/2026-09-12/DTASale_2026-09-12.xls
```

---

# 24. Check Pipeline Status

```powershell
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:

```powershell
python main.py --run-date 2026-09-12 --environment TEST --steps 2
```

Inspect the generated outbound payloads under:

```text
data/payloads/BE/
data/payloads/DTA/
```

---

# 26. Run Step 3 Only

After Step 2 has passed:

```powershell
python main.py --run-date 2026-09-12 --environment TEST --steps 3
```

BE PDFs are stored under:

```text
data/pdfs/2026-09-12/
```

---

# 27. Run Everything

All three steps:

```powershell
python main.py --run-date 2026-09-12 --environment TEST --steps all
```

`all` is the default, so this is equivalent:

```powershell
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.

```powershell
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:

```text
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:

```powershell
python main.py --run-date 2026-09-12 --environment TEST --steps 1
```

```powershell
python main.py --run-date 2026-09-12 --environment TEST --status
```

```powershell
python main.py --run-date 2026-09-12 --environment TEST --steps 2
```

```powershell
python main.py --run-date 2026-09-12 --environment TEST --status
```

```powershell
python main.py --run-date 2026-09-12 --environment TEST --steps 3
```

```powershell
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:

```text
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:

```text
total_attempted
succeeded
failed_count
success_rate
required_threshold
```

The default creation success threshold is:

```text
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:

```text
BE  : 569 rows
DTA : 2413 rows
```

Step 2 source filtering/grouping produced:

```text
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:

```text
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:

```text
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:

```powershell
python -c "from pipeline.step3_pdf_attach import Step3PdfAttach; print(Step3PdfAttach)"
```

Expected:

```text
<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:

```text
Out of range float values are not JSON compliant: nan
```

The expected architecture is:

```text
pandas NaN
    ↓
make_json_safe()
    ↓
None
    ↓
JSON null
```

Inspect:

```text
data/payloads/BE/
data/payloads/DTA/
```

to see the outbound payloads.

---

## Frappe Attachment Type error

Error example:

```text
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:

```text
AWB_HBL
Invoice
Packing List
```

The BOE PDF is processed in Step 3.

---

# 34. Security

Do not commit real credentials to source control.

Use:

```text
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:

```text
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:

```text
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

```text
                    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:

```text
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:

```text
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

```powershell
python -m pip install --upgrade pip
python -m pip install -r requirements.txt

python -m playwright install chromium
```

## Run Step 1 — NSDL Download

```powershell
python main.py --run-date 2026-09-12 --environment TEST --steps 1
```

## Run Step 2 — Transform + QC + Frappe DocType Creation

```powershell
python main.py --run-date 2026-09-12 --environment TEST --steps 2
```

## Run Step 3 — BOE PDF Download + Attachment

```powershell
python main.py --run-date 2026-09-12 --environment TEST --steps 3
```

## Run All Steps at Once

```powershell
python main.py --run-date 2026-09-12 --environment TEST --steps all
```

or:

```powershell
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.