# CSV and export formats

The scripts read UTF-8 CSV files (a byte order mark is fine) with a header row. Header names are matched case-insensitively and trimmed; enumerated values are case-insensitive. A missing required column stops the script with exit code 2. Templates: [../assets/flow-inventory-template.csv](../assets/flow-inventory-template.csv), [../assets/field-map-template.csv](../assets/field-map-template.csv), [../assets/test-plan-template.csv](../assets/test-plan-template.csv).

## Flow inventory (one row per flow)

All 38 columns must be present in the header. Cells marked required must not be blank (CT01).

- `flow_id` (required): unique per row (CT02). Snake case, for example `billing_to_crm`.
- `source_system`, `source_object`, `destination_system`, `destination_object` (required). FO09 compares the field map's owner with `source_system`; ID01 reads `destination_object` to spot account-level objects.
- `trigger` (required): event, schedule, manual, file.
- `inbound_webhook` (required): yes, no.
- `method` (required): native, ipaas, event_pipeline, reverse_etl, custom, file, mcp.
- `method_rationale`: the clause each simpler method failed (CT04 WARN when blank and the method is not native).
- `tier` (required): 1, 2, 3.
- `decision`, `business_owner`, `technical_owner` (required); `on_call` (tier 1, CT03).
- `freshness_target_minutes` (required, a positive number, CT05).
- `volume_records_per_day`, `peak_per_minute`, `batch_size`: numbers.
- `match_key` (required): the field that holds the key.
- `match_key_kind` (required): source_id, email, name, domain, destination_id, composite.
- `destination_unique`: yes when the destination field enforces uniqueness.
- `creates_records` (required): yes, no.
- `side_effect` (required): none, task, message, order, conversion, audience, other.
- `idempotency_key`: the business effect key, for example `flow + source object + business version`.
- `removal_policy` (required): none, clear_fields, delete, archive, exit_audience, not_applicable.
- `membership_contract` (required): current, add_only, not_applicable.
- `retry_layers`: see the syntax below.
- `retry_after_unit`: seconds, milliseconds, http_date, none. For an inbound HubSpot workflow webhook, record the unit the receiver must send on a 429 (milliseconds).
- `sender_timeout_seconds`, `ack_p99_seconds`: numbers, for inbound webhooks.
- `ledger_retention_days`, `max_replay_window_days`: numbers.
- `exception_queue` (required): yes, no.
- `alert_oldest_age_minutes`: number.
- `reconciliation_cadence` (required): daily, weekly, monthly, none.
- `credential_owner` (required): service, person.
- `api_version`: the pinned version, for example `2026-09`.
- `runbook`: a path or link.

A value outside an allowed list, or text in a numeric column, is CT06 WARN.

### retry_layers syntax

- Semicolon-separated `name:attempts` pairs, for example `sdk:3;worker:5`. Attempts is a whole number of 1 or more and counts every try, not only retries.
- `none` means no retries (a product of 1). Blank is unknown and is RT01 WARN.
- A malformed part is CT06 WARN and ignored.
- RT01 multiplies the attempts: `sdk:3;worker:5` is 15 (FAIL); `sdk:2;worker:5` is 10 with two retrying layers (WARN); `zapier_autoreplay:6` is 6 (no finding).

## Field map (one row per destination field and direction)

Columns: `flow_id`, `destination_system`, `source_field`, `destination_object`, `destination_field`, `owner`, `writer`, `direction`, `conflict_rule`, `blank_policy`, `type`, `allowed_values`, `unit_or_currency`, `decision_driving`, `pii`, `other_writers`, `notes`. `source_field` and `notes` are optional.

- Required on every row: `flow_id`, `destination_system`, `destination_object`, `destination_field`, `owner`, `writer`, `type`. Also required when writer is this_flow: `direction`, `conflict_rule`, `blank_policy`, `decision_driving`, `pii`.
- `writer`: this_flow (the flow writes the field) or read_only (documented because the flow reads it, for example the match key). read_only rows are excluded from FO01, FO02, FO04 and FO09.
- `direction`: one_way, two_way. For a two-way field write one row per direction, with the system written to in `destination_system`.
- `conflict_rule`: none, version, effective_time, owner_wins, manual_review.
- `blank_policy`: preserve, clear_on_explicit, clear, skip_and_flag.
- `type`: string, number, integer, boolean, date, datetime, enum, currency, id. The read-back uses it to choose numeric, boolean or exact comparison.
- `allowed_values`: pipe-separated, for example `active|past_due|canceled`.
- `unit_or_currency`: ISO 4217 code, a column holding the currency, or a unit such as UTC.
- `decision_driving`, `pii`: yes, no.
- `other_writers`: every other process seen writing the field in history.

## Test plan (one row per case)

Columns: `test_id`, `flow_id`, `case`, `environment`, `steps`, `expected`, `evidence`, `status`, `approver`.

- Required: `test_id`, `flow_id`, `case`, `environment`, `status`.
- `case`: a code from [test-plan.md](test-plan.md); unknown codes are CT06 WARN.
- `environment`: sandbox, staging, production_approved (needs an approver, TP02).
- `status`: planned, pass, fail, blocked.

## Lint rules for completeness and method misuse

- CT01 FAIL: a required column missing (exit 2) or a required cell blank.
- CT02 FAIL: duplicate `flow_id`.
- CT03: tier 1 without `on_call` or `runbook` is FAIL; tier 2 without `runbook` is WARN.
- CT04 WARN: method other than native with a blank `method_rationale`.
- CT05 WARN: `freshness_target_minutes` not a positive number.
- CT06 WARN: a value outside an allowed list, text in a numeric column, a malformed retry layer, an unknown test case, or a test row for a flow not in the inventory.
- MS01 FAIL: method mcp with trigger event or schedule (agent access used as the scheduler or delivery path).
- MS02 WARN: method reverse_etl with `freshness_target_minutes` below 15.

## What check_ipaas_export.py reads

n8n workflow JSON (Download, or `get_workflow_details` from the n8n MCP; the `{"workflow": {...}, "triggerInfo": "..."}` wrapper it returns and a `{"data": {...}}` wrapper are unwrapped). Prefer the Download, because MCP output is sanitized:

- `nodes[]`: `name`, `type`, `parameters` (`method` or `requestMethod`, `operation`, `authentication`, `responseMode`, `headerParameters`, `queryParameters`), `retryOnFail`, `maxTries`, `waitBetweenTries`, `onError`, `continueOnFail`. A node with no `parameters` key skips IX01, IX05, IX06 and IX07 and is listed under Not checked, and those four rules print no PASS.
- `connections`: walked to find whether a node's output reaches a write node (IX02) and whether a webhook's path calls external systems (IX07).
- `settings.errorWorkflow`, `settings.saveDataErrorExecution`, `active`. A copy of nodes without a `settings` block reports IX04 as not checked.
- `activeVersion`: when `sameAsDraft` is false, the script reads `activeVersion.nodes` and `activeVersion.connections`, because the top-level graph is an unpublished draft, and says so in the header. `detailLevel` "execution" omits the node graph, so that output exits 2 and asks for `detailLevel` "full" or the Download (n8n MCP tools reference, checked 2026-11-06).

Make blueprint JSON:

- Shapes accepted: the Export Blueprint file (top-level `name`, `flow` and `metadata`, with settings in `metadata.scenario`), a Make API response wrapping it in `response.blueprint` or `blueprint`, and a top-level `modules` list with settings in `config`. The first shape was confirmed against public exports on 2026-11-01. Anything else exits 2 and names the manual checklist.
- Per module: `id`, `module`, `mapper` and `parameters` (`method` for HTTP modules, header names), `onerror` (directives `builtin:Break`, `builtin:Ignore`, `builtin:Resume`, `builtin:Commit`, `builtin:Rollback`), `routes[].flow` (walked recursively), `metadata.designer.name` when present.
- `mapper.headers` and `mapper.qs`: name and value lists on HTTP modules (confirmed against public exports on 2026-11-06). A secret-named entry whose value holds no `{{` mapping is IX05 FAIL; the value is never printed.
- Scenario settings: `dlq` (Store incomplete executions), `dataloss`, `sequential`, `maxErrors`, `confidential`, and `metadata.instant`.
- Writes are found by module name (create, add, insert, send, post, update, upsert, delete, remove, upload, archive, publish, replace, append) or HTTP method. Modules starting `builtin:`, `gateway:`, `util:`, `json:`, `regexp:` or `placeholder:` are not writes. Module names in the regression fixture are synthetic.

## Read-back exports

- Expected state: a CSV from the source of truth (warehouse query, source export or a script output), using the destination's field names, with the key and the owned fields only.
- Destination: an export with the key, the record ID and the owned fields; add the non-owned columns you want watched with `--baseline`. Pass the record ID column as `--dest-id`: without it the baseline joins on the key, so a duplicate destination record reads as false NON_OWNED_CHANGED findings.
- Every owned field must be a column in both files. A missing one exits 2 rather than comparing fewer fields than the summary claims; pass `--fields` with `--map` and `--flow` to compare a named subset with the map's types.
- HubSpot: an export or list view of the object with the unique property and the owned properties. UI exports usually head columns with property labels and Record ID; rename them to the internal names in the field map before running.
- Salesforce: a Bulk API 2.0 query, or a report with a `CASESAFEID(Id)` formula column, because reports return the 15-character form. Join on 18-character IDs.
- Keep both files to stable IDs and the flow's fields. On person flows, leave `pii` yes columns out of both exports unless the check needs them, and pass `--fields` with the other owned fields. The script never prints values of columns the field map marks `pii` yes (on any flow's row) or values that look like email addresses; when a `--baseline` column holds other personal data, run with `--hide-values`, which also keeps values out of `--json` and `--json-out`.
- Use a stable ID as the key, never an email: findings print keys, so a key, `--dest-id` or `--expected-absent` value that looks like an email address exits 2.
- A source export with no keyed row exits 2, so an empty export can never read as a pass. With `--expected-absent` the run goes ahead, reads only those keys and marks R1 to R5, R9 and R10 NOT CHECKED.