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

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

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

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

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

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

1. **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.
2. **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).
3. **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 as `i`). The model is loaded in the background while the login page opens.
4. **Consensus** — case-insensitive majority vote, then a confidence-weighted
   per-character vote; the case of `c o s u v w x z` is decided from the glyph
   height, a dotted glyph is `i` / `j`.
5. **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

1. **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
     for `OTP_EMAIL_ACCOUNT`.
2. **API user**: the user behind `FRAPPE_API_KEY` / `FRAPPE_API_SECRET` needs
   **read** permission on **Communication** and **Email Account**
   (System Manager has both).
3. **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.
4. 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.
5. Put the values in `config/config.yaml` under your environment:

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

6. Forward one real NSDL OTP mail to the OTP mailbox, then check the setup
   without logging in to NSDL:

   ```powershell
   python main.py --otp-check --environment TEST
   ```

   It confirms the Email Account is reachable, pulls mail and prints the OTP
   extracted from the latest OTP mail.

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

```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
│   ├── 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:

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

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

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

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:

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

## Check the Frappe OTP Mailbox

```powershell
python main.py --otp-check --environment TEST
```

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