# NSDL SEZ BOE/DTA Automation — Project Blueprint **Status:** Design finalized, pending repo scaffolding **Owner system:** Siddhartha Logistics FTWZ NSDL account → multiple client Canofy/Frappe instances **Precursor:** `fw_verify` local 3-step pipeline (already proven against real data — 2026-09-12 run: 569 BE / 2,413 DTA rows parsed, 82/82 records created, PDFs attached) --- ## 1. Objective Automate the daily download of SEZ trade data (BE + DTA) from the NSDL portal, transform and validate it, and push it into the correct client's Frappe/Canofy instance — including creating the BOE PDF as an attachment — with no manual intervention, running as an AWS Lambda-based microservice sitting alongside (but architecturally separate from) the existing `canofy-document-extraction-lambda` suite (courier/shipping/ewaybill services). ## 2. Why this isn't a single simple pipeline One NSDL login (Siddhartha FTWZ's SEZ Online account) returns trade data for **multiple client companies mixed together** in one export, each client living on its **own separate Canofy/Frappe instance** with its own IEC. This single fact drives most of the architecture below: routing, gating, and QC all have to happen *per client*, not once per run. --- ## 3. Two-phase architecture The pipeline is split into two independently-triggered phases, because they have fundamentally different resource constraints: | | Phase A — Fetch | Phase B — Deliver | |---|---|---| | Does | NSDL login, download BE+DTA, parse, per-client QC, stage results | Pull one client's staged records, push to that client's Frappe, attach PDFs | | Scope | Shared — one NSDL login serves all clients | Per-client — fully independent invocation per client | | Trigger | EventBridge, daily schedule | Each client's own Frappe scheduled job calling our API | | Compute | Heavy (Playwright + Chromium + OCR) | Light (HTTP calls to Frappe + PDF fetch) | | Failure blast radius | Affects every client if it fails | Isolated to one client | **Why Phase A stays on EventBridge, not Frappe-triggered:** it's a single shared, expensive, credential-sensitive operation that must run exactly once per day regardless of any individual client site's uptime. AWS's own scheduler is more dependable for that than depending on one designated "ops" Frappe site's scheduler being alive. **Why Phase B is Frappe-triggered per client, not AWS fan-out:** it gives each client's own ops team visibility and control from their own Canofy instance, isolates one client's Frappe outage from affecting any other client, and reuses the exact trigger pattern (`X-Frappe-Client` header via API Gateway) already proven in the courier/shipping services. **Accepted trade-off:** since Phase B has no AWS-side fallback trigger, a disabled/crashed Frappe scheduler on one client's site means that client's records silently pile up `STAGED` until noticed. This is compensated by a watchdog alarm (see §8). --- ## 4. Phase A — Fetch, in detail 1. **Trigger:** EventBridge daily rule invokes Lambda A with `{"run_date": ""}`. 2. **Date range:** computed at invocation time, not hand-edited — `to_date = run_date`, `from_date = run_date - 7 days`. The 7-day rolling lookback exists because NSDL data has confirmed lag (a shipment can post several days after the fact). Overlap with prior runs is safe because everything downstream is idempotent (see §6). 3. **Idempotent re-download check:** before logging into NSDL, check the `run_date` control record (§7) — if already `PASSED` for this exact date and no forced-refresh flag is set, skip the NSDL login/download entirely and reuse the existing S3 file. A forced re-download creates a new version (`_v2`, etc.) rather than overwriting, and the control record is repointed to it. 4. **Download:** Playwright + OCR login (ported directly from `fw_verify/pipeline/step1_download.py`), exports BE and DTA `.xls`, uploads to S3. 5. **Parse & partition by client:** rows are parsed, then split by `client_name`, resolved via a `CLIENT IEC → client_name` reverse map built once per invocation from the `CLIENTS` block in Secrets Manager (§9). An IEC with no match is logged and skipped — not a hard failure — so onboarding a new client later never requires a code change. 6. **QC — per client, not global:** the 5 existing QC checks (header/invoice/item/shipment counts + sum totals) run independently on each client's partition. One client's mismatch cannot block another client's data from being staged. 7. **Stage:** each client's validated, request-ID-grouped, Frappe-ready payloads are written to DynamoDB with `status = STAGED`. --- ## 5. Phase B — Deliver, in detail 1. **Trigger:** the client's own Frappe site scheduled job (`hooks.py` → `scheduler_events`) calls our API Gateway endpoint: ``` POST /sez/deliver-pending Headers: x-api-key: , X-Frappe-Client: ``` 2. **Lookup:** Lambda B queries DynamoDB for `client_name = AND status = STAGED`. If none, responds `{"delivered": 0, "message": "No staged records for client ''."}` — a normal, expected response, not an error. 3. **Direct push:** for each staged record, Lambda B uses a **dedicated, narrowly-scoped API user** for that client's Frappe site — `Create`-only role on exactly `FTWZ BOE Import` / `FTWZ DTA Sale` (+ child tables) and the file-upload endpoint, nothing broader — and calls `FrappeClient.insert()` directly (the same evolution of `fw_verify`'s existing, already-proven push logic). 4. **PDF attach:** for each newly created `FTWZ BOE Import`, Lambda B separately fetches the BOE PDF from NSDL and attaches it via a direct scoped file-upload call — kept outside the structured-JSON push path since it's binary content. 5. **Status update:** DynamoDB record moves `STAGED → PUSHED → ATTACHED`. 6. **Audit log:** Lambda B writes a lightweight `NSDL Sync Run` doc back to that client's Frappe (see §10) — visibility for ops, no dependency on it for correctness. ### Why direct push, not the `Canofy API Call` / `custom_postresponse_method` callback pattern That pattern (used by courier/shipping) is well-suited to *one document, one event, one response* — an email arrives, one bill gets extracted, one record gets created. Our job is a scheduled *batch* of 30–80 records a day. Forcing it through that mechanism would mean cramming an array of full nested payloads into one `Long Text` field, rebuilding per-record failure isolation *inside* `custom_postresponse_method` (duplicating logic `fw_verify` already has and has already proven), and requiring an app-code deployment + new doctype/migration to every client site before onboarding is even possible. Direct push with a tightly scoped credential gets the meaningful part of that pattern's security property (narrow blast radius) without the batch-loop reimplementation or deployment cost. **Security note (direct vs. callback):** the callback model's real security property isn't "more permission-checked" — `custom_postresponse_method` runs with `ignore_permissions=True`, so anything in `response_json` gets inserted unconditionally regardless of role. What it actually narrows is *where the trust boundary sits*: a leaked callback credential can only write to one `Canofy API Call` field. Direct push achieves the equivalent narrow blast radius purely through **strict role scoping** on the dedicated per-client API user — that scoping is the control, not a side effect. --- ## 6. Idempotency Every insert is guarded by a duplicate check keyed on `request_id` before creation — this is what makes the 7-day rolling re-query, forced re-downloads, and retried Phase B invocations all safe to repeat without creating duplicate records. --- ## 7. S3 layout (raw files) ``` s3://nsdl-boe-raw/{run_date}/be_export_v{n}.xlsx s3://nsdl-boe-raw/{run_date}/dta_export_v{n}.xlsx ``` One DynamoDB control record per `run_date` is the single source of truth for "which version is current" — downstream steps always read through this pointer, never via an S3 listing/heuristic: ```json { "run_date": "2026-09-12", "be_s3_key": "2026-09-12/be_export_v2.xlsx", "dta_s3_key": "2026-09-12/dta_export_v2.xlsx", "download_status": "PASSED", "version": 2, "downloaded_at": "2026-09-12T09:14:02Z" } ``` **Retention:** raw files kept for an audit/reprocessing window, then lifecycle-transitioned to IA/Glacier, then deleted — on a longer horizon than DynamoDB staging records. ⚠️ **Open item:** confirm whether Indian customs/SEZ recordkeeping rules impose a minimum retention period before finalizing a hard delete date. --- ## 8. DynamoDB schema **Staged record (Phase A writes, Phase B reads/updates):** ```json { "request_id": "172601351484", "client_name": "eap", "pipeline": "BE", "status": "STAGED", "payload": { "...full Frappe doc payload..." }, "run_date": "2026-09-12", "staged_at": "2026-09-12T15:11:25Z", "error_message": null, "error_type": null, "retry_count": 0, "last_attempted_at": null } ``` `status` lifecycle: `STAGED → PUSHED → ATTACHED` (or a failure state with `error_type` set: `QC_MISMATCH | UNMAPPED_IEC | FRAPPE_REJECTED | PDF_FETCH_FAILED`). **Per-run, per-client gate record** (the "did today's sync succeed for this client" view): ```json { "run_date": "2026-09-12", "client_name": "eap", "status": "PASS", "record_count": 12, "qc_summary": { "...5 check results..." }, "first_error": null } ``` **TTL policy — based on status, never blanket age:** - `STAGED` / `PUSHED` (non-terminal) → **no TTL, ever.** A record only leaves by reaching a terminal state, never by aging out — an age-based TTL here would silently delete undelivered data. - `ATTACHED` (terminal success) → TTL set at that point, e.g. 30–90 days out (supports orphaned-record reconciliation if an IEC was ever misrouted). - Permanently failed (exhausted retries) → **no auto-delete** — needs human review; TTL would erase the evidence. --- ## 9. Multi-tenant configuration (Secrets Manager) ```json { "NSDL_ACCOUNTS": { "siddhartha_ftwz": { "NSDL_USERNAME": "...", "NSDL_PASSWORD": "...", "entity_id": "3417" } }, "CLIENTS": { "eap": { "client_iec_codes": ["300014953", "0300014953"], "FRAPPE_URL": "https://eapx.canofy.tech", "api_key": "...", "api_secret": "..." } } } ``` **Normalization rule:** IECs are normalized (stripped, zero-padded to a fixed width) identically on both the secret's `client_iec_codes` list and the parsed dataframe column, at load time — not scattered across call sites. This is what prevents a `"300014953"` vs `"0300014953"` mismatch from silently misrouting a record. **Collision guard:** the `IEC → client_name` reverse map is built once at cold start; if any IEC maps to more than one `client_name`, this is a **hard configuration error that aborts the run** — it must never resolve silently, since a misresolution here means cross-tenant data leakage, the worst failure mode in this system. **Per-group consistency check (QC check #6):** every row within one `request_id` group must agree on IEC after normalization. A group that doesn't is rejected on its own — not the whole batch. **Scaling note:** a single Secrets Manager secret has a 64KB limit and every deploy/rotation touches every client's credentials at once. Fine for a handful of clients; if this grows toward dozens, revisit — one secret per client, or a small DynamoDB config table. **Client onboarding checklist** (manual, per new client): verify the target Canofy instance's `Attachment Type` master and `FTWZ BOE Import`/`FTWZ DTA Sale` doctype schema match what the push code expects (the original `"Bill of Entry"` attachment-type bug on `eapx.canofy.tech` was exactly this class of issue) — before adding the client to `CLIENTS`. --- ## 10. Frappe-side components - **`NSDL Sync Run` doctype** (new, lightweight — created specifically because there's no natural parent document for a schedule-triggered call, unlike courier/shipping's `Communication`-triggered flow): | Field | Purpose | |---|---| | `run_date` | Which NSDL export date this sync pulled from | | `client_name` | Which client this run belongs to | | `status` | Triggered / Delivered / No Pending Records / Failed | | `records_delivered`, `pdfs_attached` | Simple counts | | `error_message` | Set on failure | - **Scheduled job** on each client's Canofy site, using the **existing shared outbound-call helper** already used by other integrations (confirmed to exist) to fire the call to `/sez/deliver-pending`. - Explicitly **not used:** the `API Configuration - Canofy` / `Canofy API Call` / `custom_postresponse_method` pattern — see §5's rationale. --- ## 11. Monitoring & error handling (4 layers) | Layer | What | Audience | |---|---|---| | 1 — CloudWatch Logs | Raw technical trail per Lambda invocation (stack traces, OCR confidence, raw NSDL responses) — credentials/secrets always scrubbed before logging | Engineers debugging a specific failure | | 2 — DynamoDB staged-record status | `error_message`, `error_type`, `retry_count` per record | "Why is this one record stuck" | | 3 — Per-run, per-client gate record | QC breakdown + success rate for that client's batch that day | "Did today's sync succeed for client X" | | 4 — `NSDL Sync Run` doc in Frappe | Status + error visible from the client's own Canofy instance | Ops, without touching AWS | **Alerting:** | Trigger | Source | Channel | |---|---|---| | Phase A fails entirely | CloudWatch Alarm on Lambda error metric | SNS — blocks every client, needs immediate attention | | One client's Phase B gate fails | Layer 3 `FAIL` status | SNS, scoped to that client only | | Records `STAGED` > 24h | Watchdog Lambda scanning DynamoDB | SNS — catches a silently-dead Frappe scheduler | | Phase B async invocation itself fails before any logging happens | SQS Dead Letter Queue on the Lambda's async config | Same pattern already used in the courier/shipping stack — required because a cold-start crash produces no DynamoDB write and no application log at all | --- ## 12. Repository strategy **Decision: separate repository**, not added to `canofy-document-extraction-lambda-main`. Reasoning: courier/shipping/ewaybill are live, proven, production services with a narrow, known egress profile (Frappe/Google APIs only). This project is still actively being designed, needs Playwright + Chromium (a materially heavier and differently-scoped image), reaches the open NSDL portal (a wider, more sensitive egress profile), and introduces infra types (EventBridge schedule, DynamoDB) not present in the existing stack. A shared `template.yaml` would mean a change here risks the CloudFormation stack for already-stable production services. **What is shared, via an internal library (Lambda Layer or private package) rather than a shared repo:** - `FrappeClient(url, api_key, api_secret)` wrapper pattern - Secrets Manager loading/parsing helper - Shared error-callback logic **Proposed layout:** ``` nsdl-sez-boe-automation/ ├── template.yaml ├── app/ │ ├── phase_a_fetch/ │ │ ├── Dockerfile │ │ └── lambda_handler.py │ └── phase_b_deliver/ │ ├── Dockerfile │ └── lambda_handler.py ├── shared/ └── frappe_app/ ``` --- ## 13. Open items requiring confirmation before finalizing 1. S3 raw-file retention horizon — pending confirmation of any Indian customs/SEZ minimum recordkeeping requirement. 2. Exact signature of the existing shared Frappe outbound-call helper (needed to wire the new scheduled job to it precisely rather than by assumption). 3. Secrets Manager scaling approach once client count grows beyond "a handful."