# CSV Mapping Spec (Expression DSL) This spec documents the `csv_mapping` configuration object used to import a headered UTF-8 CSV into canonical records (deposit/withdrawal/trade/paired_trade/pay/invoice) via a small, typed JSON expression DSL. Key runtime behaviors: - Configuration is validated before import; invalid mappings return `E400-INVALID-CONFIG`. - CSV parsing/classification/evaluation errors return `E400-INVALID-CSV`. - Imports are all-or-nothing for errors: any row error fails the whole import, but rows that match no pattern are skipped and logged. - Row errors include a 1-based data row index (header excluded). ## Archive Records Successful imports persist row-level archive records plus a manifest under `ArchiveType::Imports`: - **Row payloads:** Each unique row produces a deterministic record id of `row:` computed from the serialized `CsvImportRowPayload` JSON. Row payloads reference the header list via `headers_sha256` instead of embedding headers per row. Duplicate rows (within or across imports) dedupe naturally by record id. - **Manifest:** Each import writes a `CsvImportManifestPayload` record with id `import:` where the hash is computed over the raw CSV bytes. The manifest captures filename/content_type, byte_len, sha256_hex, headers and headers_sha256, row counts, skipped rows, status, and created_at. The adapter writes an `in_progress` manifest before row archives and overwrites it with `complete` after all row batches succeed. - **No CSV base64:** Raw CSV bytes are not stored; replays use archived row payloads instead. ## Creating A Mapping (Checklist) Given a representative CSV sample (including headers): 1. Set `dialect` (delimiter/quote, header required, UTF-8 only). 2. Decide which canonical types you want to emit (`deposit`, `withdrawal`, `trade`, `paired_trade`, `pay`, `invoice`). 3. Create `row_patterns` in priority order (first match wins). - One `csv_mapping` is reused for every CSV import on the connection, so include all export types in a single mapping and distinguish them with `when` and/or `required_headers`. - If exports use different CSV dialects (delimiter/quote/header rules) or incompatible headers, normalize them before import or use separate connections. 4. For each `row_pattern`, implement: - `when` (a `bool` expression that classifies the row) - `fields` (canonical field expressions; required fields must exist) 5. Make option-typed fields explicitly option-typed (see "Option Recipes"). 6. Add `assert` where you want source-specific invariants or clearer messages (e.g., required non-empty IDs or debit/credit consistency). Canonical amount and fee fields are also rejected during row preparation if they evaluate to a negative value. 7. Ensure most rows match exactly one pattern; rows that match no pattern are skipped (and logged) instead of failing the import. ## Configuration Schema The adapter configuration must contain a `csv_mapping` object: ```jsonc { "csv_mapping": { "dialect": { "delimiter": ",", // required, 1 byte "has_header": true, // required, must be true "quote": "\"", // optional, 1 byte, default "\"" "encoding": "utf8", // optional, must be "utf8" when present }, "row_patterns": [ { "name": "unique-name", "canonical_type": "deposit", "when": { /* bool expression */ }, "required_headers": ["..."], // optional "fields": { /* canonical field expressions */ }, }, ], }, } ``` ### `dialect` - `delimiter`: required; a single-byte string such as `","` or `"\t"`. - `has_header`: required; must be `true` (headerless CSV is not supported). - `quote`: optional; single-byte string; default is `"\""`. - `encoding`: optional; must be `"utf8"` (case-insensitive). Non-UTF8 input must be transcoded before import. ### `row_patterns` - Non-empty array. - Evaluated in order for each row; the first pattern whose `when` evaluates to `true` is chosen. - If no pattern matches: the row is ignored (logged and counted as skipped). - `name` must be non-empty and unique across patterns. - `fields` may only contain the field names allowed for that `canonical_type`. - Patterns are only evaluated when their required headers are present. If no patterns are applicable to a CSV header: `E400-INVALID-CSV` (file-level error). ### `row_patterns[*].required_headers` (optional) - Array of strings. - Entries are trimmed and must be non-empty and unique. - Used for pattern applicability only (not for per-row validation). - Required headers for a pattern are the union of: - `row_patterns[*].required_headers` - every column referenced by `{ "col": "..." }` in `when` or `fields` - Columns referenced via `{ "col_opt": "..." }` do not contribute. ## Canonical Types And Fields Choosing `canonical_type`: - `deposit`: inbound transfer into a managed wallet (btc or fiat). - `withdrawal`: outbound transfer out of a managed wallet (btc or fiat). - `trade`: asset conversion (sell/buy) with optional fees (single-row). - `paired_trade`: asset conversion where each side is a separate CSV row (two-row). - `pay`: outgoing self-managed lightning payment/spend (BTC-only; no asset field exists). - `invoice`: incoming self-managed lightning invoice settlement (BTC-only; no asset field exists). Each `row_pattern.fields` object maps canonical field names to expressions with the expected types. Optional fields may be omitted entirely; if present, they must have the correct type. Asset-bearing amount and fee fields on `deposit`, `withdrawal`, `trade`, and `paired_trade` may evaluate to native `decimal` values or fixed `decimal_sats` values. During row preparation the importer resolves the associated asset and normalizes the value to that asset's catalog `native_precision`. Use `parse_decimal` when the CSV already contains asset units such as `1.20` USD or `0.000999` USDT. Use `decimal_sats` constructors for BTC/LN milli-unit columns or legacy mappings. BTC-only `pay` and `invoice` records still require `decimal_sats`. If a source system emits asset-native values with more fractional digits than the asset supports, use `quantize_decimal` at that field boundary so the lossy rounding policy is visible in the mapping. `canonical_id` is required for every canonical type. It is the stable upsert key and must not be empty after trimming. It must be unique per `canonical_type` across all imports. Before importing, inspect the column you plan to map to `canonical_id` and verify there are no duplicates across every file you intend to import; duplicate `canonical_id` values refer to the same logical record. `id` is optional in the mapping; if omitted, it defaults to `canonical_id`. ### `deposit` Required fields: - `canonical_id`: `string` - `timestamp`: `timestamp` - `amount`: `decimal` or `decimal_sats` - `asset`: `string` (asset code; see "Asset Codes") Optional fields: - `id`: `string` - `fee`: `option` or `option` - `btc_destination`: `option` (validated only for BTC assets; must be a valid BOLT11 invoice, BOLT12 invoice, 64-hex Lightning payment hash, or Bitcoin mainnet address. May be omitted for managed exchanges that do not expose on-chain addresses. For non-BTC assets this field is ignored. When a Lightning invoice is provided, the payment hash is extracted and stored.) - `btc_txid`: `option` (validated only for BTC assets; must be 64 hex characters and will be normalized to lowercase. For non-BTC assets this field is ignored.) - `btc_vout`: `option` (validated only for BTC assets; must be base-10 digits between 0 and 65535 inclusive, and requires `btc_txid` to be present. For non-BTC assets this field is ignored.) - `transfer_ref`: `option` (asset-agnostic source-provided transfer identity. Empty values are stored as absent; non-empty values are trimmed, limited to 512 bytes, and must not contain ASCII control characters.) - `network`: `option` (optional network attribution such as `ethereum`, `tron`, `base`, or source-specific printable ASCII. Values are trimmed and normalized to lowercase. Blank or missing values clear any previous network attribution for this deposit.) - `description`: `option` Semantics: - `amount` is the gross deposit (principal before fees). When a fee is present, net is `amount - fee`. - If the CSV provides a net amount plus a separate fee, map `amount` as `net + fee` so the stored `amount` remains gross and the `fee` is recorded separately. - If the CSV provides a single gross amount with no fee column, map it to `amount` and omit `fee`. - For on-chain deposits, include `btc_txid` (and `btc_vout` when available) to disambiguate reused addresses. - For BTC managed-to-managed transfers, `btc_txid` and `btc_vout` also provide on-chain transfer evidence. A matching withdrawal and deposit with the same txid, compatible vout, and exact amount are linked as a transfer even when one side also has `btc_destination`. - For Lightning managed-to-managed transfers, `btc_destination` may carry a BOLT11 invoice, BOLT12 invoice, or 64-hex payment hash. The journal linker matches only on that strong Lightning identity, not on amount or time alone. - For non-BTC external rails, use `transfer_ref` on both sides to link one managed withdrawal and deposit as the same principal movement. `transfer_ref` is not an instruction for Clams to fetch or validate that chain; external scripts remain responsible for deriving the reference. Both sides must use the exact same value, amount, and asset, and must belong to different connections. When `transfer_ref` is present, direct managed matching uses it instead of BTC txid or Lightning identities. Recommended format for Ethereum token transfers is `ethereum:1::`; do not use a bare Ethereum tx hash when one transaction can contain multiple token transfers. - Use `network` when the source identifies the asset rail for this deposit. It is stored as sidecar attribution for reporting/audit context only; it does not make Clams validate or fetch that network. ### `withdrawal` Required fields: - `canonical_id`: `string` - `timestamp`: `timestamp` - `amount`: `decimal` or `decimal_sats` - `asset`: `string` Optional fields: - `id`: `string` - `fee`: `option` or `option` - `btc_destination`: `option` (validated only for BTC assets; must be a valid BOLT11 invoice, BOLT12 invoice, 64-hex Lightning payment hash, or Bitcoin mainnet address. May be omitted for managed exchanges that do not expose on-chain addresses. For non-BTC assets this field is ignored. When a Lightning invoice is provided, the payment hash is extracted and stored.) - `btc_txid`: `option` (validated only for BTC assets; must be 64 hex characters and will be normalized to lowercase. For non-BTC assets this field is ignored.) - `btc_vout`: `option` (validated only for BTC assets; must be base-10 digits between 0 and 65535 inclusive, and requires `btc_txid` to be present. For non-BTC assets this field is ignored.) - `transfer_ref`: `option` (asset-agnostic source-provided transfer identity. Empty values are stored as absent; non-empty values are trimmed, limited to 512 bytes, and must not contain ASCII control characters.) - `network`: `option` (optional network attribution such as `ethereum`, `tron`, `base`, or source-specific printable ASCII. Values are trimmed and normalized to lowercase. Blank or missing values clear any previous network attribution for this withdrawal.) - `description`: `option` Semantics: - `amount` is the on-chain output value (net of fees). When a fee is present, the gross outflow is `amount + fee`. - If the CSV provides a gross amount and a separate fee, map `amount` as `gross - fee` so the stored `amount` remains net and the `fee` is recorded separately. - If the CSV provides a net amount plus a separate fee, map `amount` to that net value and store the fee as provided. - For on-chain withdrawals, include `btc_txid` (and `btc_vout` when available) to disambiguate reused addresses. - For BTC managed-to-managed transfers, `btc_txid` and `btc_vout` also provide on-chain transfer evidence. A matching withdrawal and deposit with the same txid, compatible vout, and exact amount are linked as a transfer even when one side also has `btc_destination`. - For Lightning managed-to-managed transfers, `btc_destination` may carry a BOLT11 invoice, BOLT12 invoice, or 64-hex payment hash. The journal linker matches only on that strong Lightning identity, not on amount or time alone. - For non-BTC external rails, use `transfer_ref` on both sides to link one managed withdrawal and deposit as the same principal movement. `transfer_ref` is not an instruction for Clams to fetch or validate that chain; external scripts remain responsible for deriving the reference. Both sides must use the exact same value, amount, and asset, and must belong to different connections. When `transfer_ref` is present, direct managed matching uses it instead of BTC txid or Lightning identities. - Use `network` when the source identifies the asset rail for this withdrawal. It is stored as sidecar attribution for reporting/audit context only; it does not make Clams validate or fetch that network. Example Gnosis Safe to ChainFlip USDT movement: ```csv id,type,ts,asset,amount,transfer_ref,description gnosis-1,withdrawal,2026-01-01T00:00:00Z,USDT,10000.000000,ethereum:1:0xabc:42,Gnosis Safe USDT to ChainFlip chainflip-1,deposit,2026-01-01T00:03:00Z,USDT,10000.000000,ethereum:1:0xabc:42,ChainFlip USDT deposit ``` ### `trade` Required fields: - `canonical_id`: `string` - `timestamp`: `timestamp` - `from_asset`: `string` - `from_amount`: `decimal` or `decimal_sats` - `to_asset`: `string` - `to_amount`: `decimal` or `decimal_sats` Optional fields: - `id`: `string` - `fee_asset`: `option` - `fee_amount`: `option` or `option` - `network`: `option` (optional network attribution such as `ethereum`, `tron`, `base`, or source-specific printable ASCII) - `network_asset`: `option` (optional target asset for nonblank `network`; must equal `from_asset` or `to_asset`; ignored when `network` is blank) - `trade_id`: `option` - `order_id`: `option` Notes: - Trade fees are posted to the fees expense account and do not automatically reduce asset balances. If the fee is paid in the `from_asset`, ensure `from_amount` is the gross amount (principal + fee) so the asset balance reflects the fee outflow. If the CSV provides net `from_amount` plus a separate fee, adjust the mapping to add the fee into `from_amount` when `fee_asset == from_asset`. - When `fee_asset` is omitted, the trade engine treats it as `from_asset`. - If the fee is paid in the `to_asset`, ensure `to_amount` is the net amount (after fees) so the asset balance reflects the fee outflow. If the CSV provides gross `to_amount` plus a separate fee, adjust the mapping to subtract the fee from `to_amount` when `fee_asset == to_asset`. - For fiat fee columns, prefer using the exchange-provided “total including fee” for the asset side that pays the fee (e.g., AUD buys: use `total_inc_fee` as `from_amount`; AUD sells: use `total_inc_fee` as `to_amount`). This avoids small residual fiat balances caused by leaving the fee outside the asset movement. - When `network` is present, `network_asset` may identify which trade side the network describes. If omitted, the importer uses the single stablecoin side when exactly one side is a stablecoin. Rows with `network` and no unambiguous target are rejected instead of guessing. - When `network` is blank or missing, `network_asset` is ignored and the row clears any previous network attribution for that trade. ### `paired_trade` A paired trade assembles a single canonical `Trade` from **two CSV rows** that share the same `pair_key`. Each row represents one side of the trade ("from" or "to"). This is useful for exchanges that export one row per leg instead of one row per trade. Required fields: - `pair_key`: `string` — key that links the two rows (must match exactly). - `side`: `string` — must evaluate to `"from"` or `"to"` (case-insensitive after trim). - `canonical_id`: `string` - `timestamp`: `timestamp` - `asset`: `string` - `amount`: `decimal` or `decimal_sats` Optional fields: - `id`: `string` - `fee_asset`: `option` - `fee_amount`: `option` or `option` - `trade_id`: `option` - `order_id`: `option` Semantics: - The "from" half becomes `from_asset` / `from_amount`; the "to" half becomes `to_asset` / `to_amount`. - `canonical_id`, `id`, `timestamp`, and `ordering_key` are taken from the "from" half. - Optional fields (`fee_asset`, `fee_amount`, `trade_id`, `order_id`) are merged: the "from" half's value is preferred; if absent, the "to" half's value is used. - If both halves have the same `side`: `E400-INVALID-CSV`. - If any `pair_key` has only one row after all rows are processed: `E400-INVALID-CSV`. - `side` values other than `"from"` or `"to"`: `E400-INVALID-CSV`. ### `pay` Required fields: - `canonical_id`: `string` - `timestamp`: `timestamp` - `amount`: `decimal_sats` - `fee`: `decimal_sats` Optional fields: - `id`: `string` - `destination`: `option` (typically a destination node pubkey; 66-char lowercase hex when available) - `description`: `option` - `zap`: `option` ### `invoice` Required fields: - `canonical_id`: `string` - `timestamp`: `timestamp` - `amount`: `decimal_sats` Optional fields: - `id`: `string` - `settled_amount`: `option` - `description`: `option` - `zap`: `option` ## Amount Encoding The DSL has two decimal types: - `decimal`: native asset units, preserving the parsed scale until the importer normalizes the value against the resolved asset. - `decimal_sats`: fixed scale-3 units produced by `to_decimal_sats_from_msat(i128)`, `parse_decimal_sats`, or `parse_decimal_sats_from_btc`. Asset-bearing canonical fields are stored using the resolved asset's catalog `native_precision`: BTC uses 3 fractional digits, USDT and USDC use 6, and USD uses 2. `parse_decimal` preserves the source scale. Native `parse_decimal` values are rejected if they contain more fractional digits than the asset supports, even if the extra digits are zero. Legacy `decimal_sats` mappings are also normalized to the asset precision, but they may only discard over-precision when every discarded digit is zero (`1.230` USD becomes `1.23`; `1.235` USD is rejected). Values with fewer fractional digits are padded to the asset precision (`12.345` USDT becomes `12.345000`). Use `quantize_decimal` when a source system emits native asset values that need an explicit lossy conversion before asset normalization, for example sub-cent fiat totals. The currently supported strategy is `midpoint_away_from_zero`. After quantization, the importer still validates the result against the resolved asset's `native_precision`. `pay` and `invoice` remain BTC-only Lightning records and require `decimal_sats`. `decimal_sats` interpretation: - The `i128` input to `to_decimal_sats_from_msat` is an integer count of milli-units of the asset (msats for BTC). - The output is `input / 1000` exactly, with scale 3. - `parse_decimal_sats` strips non-numeric characters (anything other than digits and the first `.`), including commas, parentheses, currency symbols, and sign markers, then truncates fractional digits beyond scale 3 without rounding. The cleaned value must still be a valid decimal string; an empty result is rejected. - `parse_decimal_sats_from_btc` accepts BTC-denominated strings with up to 11 fractional digits and rejects non-zero digits beyond that precision. - Scientific notation is not supported; `e`/`E` are treated as non-numeric characters and stripped like any other. - Canonical amount and fee fields must evaluate to non-negative magnitudes. `parse_decimal` preserves a leading `+` or `-`, so native asset columns must already be unsigned when they are mapped to canonical amount or fee fields. `parse_decimal_sats` strips sign markers before parsing, so use it only when fixed scale-3 units are correct. For integer source columns, use explicit arithmetic such as `abs_i128` before converting to `decimal_sats`. Common conversions: ```jsonc // Column already contains msats (or milli-units) { "to_decimal_sats_from_msat": { "parse_i128": { "trim": { "col": "amount_msat" } } } } // Column contains sats (or whole units); multiply by 1000 first { "to_decimal_sats_from_msat": { "mul_i128_const": { "value": { "parse_i128": { "trim": { "col": "amount_sats" } } }, "k": 1000 } } } // A literal 0.000 amount (no decimal literals exist) { "to_decimal_sats_from_msat": { "lit": { "type": "i128", "value": 0 } } } // Parse native asset units for deposit/withdrawal/trade asset amount fields { "parse_decimal": { "trim": { "col": "amount" } } } // Round source-native sub-cent fiat to cents before asset normalization { "quantize_decimal": { "value": { "parse_decimal": { "trim": { "col": "amount" } } }, "scale": 2, "strategy": "midpoint_away_from_zero" } } // Parse a scale-3 decimal string into decimal_sats { "parse_decimal_sats": { "trim": { "col": "amount" } } } // Parse a BTC-denominated string into decimal sats (up to 11 fractional digits) { "parse_decimal_sats_from_btc": { "trim": { "col": "amount_btc" } } } // Signed amount column where the canonical amount is the magnitude { "parse_decimal_sats": { "trim": { "col": "signed_amount" } } } // Debit/credit arithmetic where the canonical amount is the absolute difference { "to_decimal_sats_from_msat": { "abs_i128": { "sub_i128": [ { "parse_i128": { "trim": { "col": "credit_msat" } } }, { "parse_i128": { "trim": { "col": "debit_msat" } } } ] } } } ``` ## Expression DSL An expression is a JSON object with exactly one operator key, e.g.: ```json { "trim": { "col": "id" } } ``` Types are checked at configuration time: - `string` - `i128` - `decimal` - `decimal_sats` - `bool` - `timestamp` (`DateTime`) - `option` for any `T` ### Column And Literals - `{ "col": "header_name" } -> string` - Header name must match exactly (case/whitespace-sensitive). - Missing headers/cells are `E400-INVALID-CSV`. - `{ "col_opt": "header_name" } -> option` - Returns `none` when the header is missing. - Missing cells still fail with `E400-INVALID-CSV`. - `{ "lit": { "type": "string", "value": "..." } } -> string` - `{ "lit": { "type": "bool", "value": true } } -> bool` - `{ "lit": { "type": "i128", "value": 123 } } -> i128` - `i128` literals must fit in `i64`. - `{ "none": { "type": "" } } -> option` - `` is one of: `string`, `i128`, `decimal`, `decimal_sats`, `bool`, `timestamp`. - `{ "some": } -> option` ### String - `{ "trim": } -> string` - `{ "lower": } -> string` - `{ "concat": [ , ... ] } -> string` (1+ items) - `{ "contains": { "haystack": , "needle": } } -> bool` - `{ "empty_to_none": } -> option` (only converts `""`; use `trim` first) ### Option - `{ "coalesce": [ >, ... ] } -> option` (1+ items, same `T`) - `{ "unwrap_or": { "value": >, "default": } } -> T` ### Numeric / Parsing - `{ "parse_i128": } -> i128` - Rejects empty strings. - `{ "parse_decimal": } -> decimal` - Accepts a decimal string in native asset units. - Use this for comparisons, `when` clauses, and asset-bearing canonical amount or fee fields. When assigned to `deposit`, `withdrawal`, `trade`, or `paired_trade` amount fields, the importer validates it against the resolved asset's native precision before upsert. - `{ "parse_decimal_sats": } -> decimal_sats` - Accepts a decimal string containing digits and (optionally) a single `.`, with any other characters stripped first. - Truncates fractional digits beyond scale 3 (no rounding). - `{ "parse_decimal_sats_from_btc": } -> decimal_sats` - Accepts BTC unit strings with up to 11 fractional digits. - Extra fractional digits must be zero (no rounding). - `{ "quantize_decimal": { "value": , "scale": 2, "strategy": "midpoint_away_from_zero" } } -> decimal` - Rounds the native decimal to at most `scale` fractional digits. - `scale` must be an integer from 0 through 28. - Use only at an explicit source-system conversion boundary; asset-bearing canonical fields still validate the rounded value against the resolved asset's `native_precision`. - `{ "abs_i128": } -> i128` (rejects `i128::MIN`) - `{ "add_i128": [ , ] } -> i128` - `{ "sub_i128": [ , ] } -> i128` - `{ "add_decimal_sats": [ , ] } -> decimal_sats` - `{ "mul_i128_const": { "value": , "k": 1000 } } -> i128` - `k` must fit in `i64`. - `{ "max_i128_const": { "value": , "k": 0 } } -> i128` - `k` must fit in `i64`. - `{ "to_decimal_sats_from_msat": } -> decimal_sats` ### Time Timestamps without explicit offsets are interpreted as UTC. - `{ "parse_rfc3339": } -> timestamp` - Input must be RFC3339 (e.g., `2024-01-01T00:00:00Z`). - Timestamps must be >= Unix epoch; negative timestamps fail import. - `{ "parse_timestamp": } -> timestamp` - Best-effort parser for common formats (RFC3339, RFC2822, ISO-like, epoch, date-only). - Date-only formats include `YYYY-MM-DD` and `YYYY/MM/DD`. - Slash dates like `01/02/2024` are rejected unless unambiguous. - Use when you don't control the exact source timestamp format. - `{ "parse_timestamp_format": { "value": , "format": "%m/%d/%Y %I:%M:%S %p" } } -> timestamp` - Parses timestamps using a specific chrono format string. ### Comparisons And Boolean Logic - `{ "eq": { "a": , "b": } } -> bool` (types must match) - `{ "lt": { "a": , "b": } } -> bool` where `T` is `i128`, `decimal`, or `decimal_sats` (also: `lte`, `gt`, `gte`; both operands must be the same type) - `{ "and": [ , ... ] } -> bool` - `{ "or": [ , ... ] } -> bool` - `{ "not": } -> bool` ### Control Flow And Assertions - `{ "if": { "cond": , "then": , "else": } } -> T` - `{ "assert": { "cond": , "value": , "msg": "..." } } -> T` - If `cond` is false: `E400-INVALID-CSV` with `msg` (row-scoped). - `msg` must be <= 256 bytes. ## Common Recipes ### Row Classification (`when`) Most mappings classify rows by normalizing a string column and comparing it: ```json { "eq": { "a": { "lower": { "trim": { "col": "type" } } }, "b": { "lit": { "type": "string", "value": "deposit" } } } } ``` ### Building `canonical_id` Prefer a stable unique identifier from the source system. If you need to compose one, use `concat` over string columns/literals: ```json { "concat": [ { "lower": { "trim": { "col": "type" } } }, { "lit": { "type": "string", "value": ":" } }, { "trim": { "col": "id" } } ] } ``` Limitations: - There is no numeric-to-string conversion; if you want numbers in an ID, use the raw string columns, not parsed `i128` values. ### Enforcing Invariants With `assert` Example: reject negative `amount_msat` values before converting to `decimal_sats`: ```json { "to_decimal_sats_from_msat": { "assert": { "cond": { "gte": { "a": { "parse_i128": { "trim": { "col": "amount_msat" } } }, "b": { "lit": { "type": "i128", "value": 0 } } } }, "value": { "parse_i128": { "trim": { "col": "amount_msat" } } }, "msg": "amount_msat must be >= 0" } } } ``` ## Option Recipes ### Optional strings (`option`) Use `empty_to_none` (and usually `trim`) to treat empty cells as missing: ```json { "empty_to_none": { "trim": { "col": "description" } } } ``` ### Schema-flexible column reads Use `col_opt` with `coalesce` to support old/new header names: ```json { "unwrap_or": { "value": { "coalesce": [ { "col_opt": "old_amount" }, { "col_opt": "amount" } ] }, "default": { "lit": { "type": "string", "value": "" } } } } ``` ### Optional amounts (`option` or `option`) There is no `map` operator for options; use `if` to branch on the raw string: ```json { "if": { "cond": { "eq": { "a": { "trim": { "col": "fee_msat" } }, "b": { "lit": { "type": "string", "value": "" } } } }, "then": { "none": { "type": "decimal_sats" } }, "else": { "some": { "to_decimal_sats_from_msat": { "parse_i128": { "trim": { "col": "fee_msat" } } } } } } } ``` For asset-native optional fee columns, use `decimal` instead: ```json { "if": { "cond": { "eq": { "a": { "trim": { "col": "fee" } }, "b": { "lit": { "type": "string", "value": "" } } } }, "then": { "none": { "type": "decimal" } }, "else": { "some": { "parse_decimal": { "trim": { "col": "fee" } } } } } } ``` ### Optional bool (`option`) There is no `parse_bool`; derive bools from comparisons: ```json { "some": { "eq": { "a": { "lower": { "trim": { "col": "kind" } } }, "b": { "lit": { "type": "string", "value": "zap" } } } } } ``` ## Asset Codes Asset fields (`asset`, `from_asset`, `to_asset`, `fee_asset`) are parsed case-insensitively (whitespace is trimmed) and then resolved against the asset catalog for the importing account. Supported codes currently include: - `BTC`, `USD`, `EUR`, `JPY`, `GBP`, `CNY`, `AUD`, `CAD`, `CHF`, `HKD`, `NZD`, `SEK`, `KRW`, `SGD`, `MXN`, `INR`, `BRL`, `NOK`, `ZAR`, `DKK`, `USDT`, `USDC` Altcoins are not supported. If a trade has BTC on one side and an altcoin (for example, `ETH`) on the other, the CSV must be edited before import: - Fetch the fiat exchange rate for the altcoin at the trade timestamp. - Replace the altcoin side with its fiat value and a supported fiat asset code, so the row represents a BTC/FIAT trade. - Add a note column that records the original altcoin trade pair/amounts and the source of the fiat exchange rate used for the conversion. If you are an LLM agent, ask the user to fetch the exchange rates for the altcoin at each row timestamp and to update the CSV to replace the altcoin with the fiat value and asset code so Clams can process the trade. Also ask them to add a note column capturing the original trade details and the exchange-rate source. ## Runtime Constraints Validated at configuration time: - Max expression depth: 32 - Max nodes per expression: 256 - Max string literal length: 256 ## Error Semantics - Invalid mapping structures, unknown operators, type mismatches, field-name violations, or constraint violations -> `E400-INVALID-CONFIG`. - CSV parse issues, missing headers/cells, asset resolution failures, timestamp violations, assertion failures, or arithmetic/parse errors -> `E400-INVALID-CSV`. - Row-scoped errors include a 1-based data row index in the error message. ## Example Mappings ### Deposit + Withdrawal (single CSV) ```json { "csv_mapping": { "dialect": { "delimiter": ",", "has_header": true, "encoding": "utf8" }, "row_patterns": [ { "name": "deposit", "canonical_type": "deposit", "when": { "eq": { "a": { "lower": { "trim": { "col": "type" } } }, "b": { "lit": { "type": "string", "value": "deposit" } } } }, "fields": { "canonical_id": { "trim": { "col": "id" } }, "timestamp": { "parse_rfc3339": { "trim": { "col": "ts" } } }, "asset": { "trim": { "col": "asset" } }, "amount": { "parse_decimal": { "trim": { "col": "amount" } } }, "fee": { "if": { "cond": { "eq": { "a": { "trim": { "col": "fee" } }, "b": { "lit": { "type": "string", "value": "" } } } }, "then": { "none": { "type": "decimal" } }, "else": { "some": { "parse_decimal": { "trim": { "col": "fee" } } } } } }, "btc_destination": { "empty_to_none": { "trim": { "col": "destination" } } }, "description": { "empty_to_none": { "trim": { "col": "description" } } } } }, { "name": "withdrawal", "canonical_type": "withdrawal", "when": { "eq": { "a": { "lower": { "trim": { "col": "type" } } }, "b": { "lit": { "type": "string", "value": "withdrawal" } } } }, "fields": { "canonical_id": { "trim": { "col": "id" } }, "timestamp": { "parse_rfc3339": { "trim": { "col": "ts" } } }, "asset": { "trim": { "col": "asset" } }, "amount": { "parse_decimal": { "trim": { "col": "amount" } } } } } ] } } ``` ### Trade (single CSV) ```json { "csv_mapping": { "dialect": { "delimiter": ",", "has_header": true, "encoding": "utf8" }, "row_patterns": [ { "name": "trade", "canonical_type": "trade", "when": { "eq": { "a": { "lower": { "trim": { "col": "type" } } }, "b": { "lit": { "type": "string", "value": "trade" } } } }, "fields": { "canonical_id": { "trim": { "col": "id" } }, "timestamp": { "parse_rfc3339": { "trim": { "col": "ts" } } }, "from_asset": { "trim": { "col": "from_asset" } }, "from_amount": { "parse_decimal": { "trim": { "col": "from_amount" } } }, "to_asset": { "trim": { "col": "to_asset" } }, "to_amount": { "parse_decimal": { "trim": { "col": "to_amount" } } }, "fee_asset": { "empty_to_none": { "trim": { "col": "fee_asset" } } }, "fee_amount": { "if": { "cond": { "eq": { "a": { "trim": { "col": "fee_amount" } }, "b": { "lit": { "type": "string", "value": "" } } } }, "then": { "none": { "type": "decimal" } }, "else": { "some": { "parse_decimal": { "trim": { "col": "fee_amount" } } } } } }, "trade_id": { "empty_to_none": { "trim": { "col": "trade_id" } } }, "order_id": { "empty_to_none": { "trim": { "col": "order_id" } } } } } ] } } ``` ### Pay + Invoice (single CSV) ```json { "csv_mapping": { "dialect": { "delimiter": ",", "has_header": true, "encoding": "utf8" }, "row_patterns": [ { "name": "pay", "canonical_type": "pay", "when": { "eq": { "a": { "lower": { "trim": { "col": "type" } } }, "b": { "lit": { "type": "string", "value": "pay" } } } }, "fields": { "canonical_id": { "trim": { "col": "id" } }, "timestamp": { "parse_rfc3339": { "trim": { "col": "ts" } } }, "amount": { "to_decimal_sats_from_msat": { "parse_i128": { "trim": { "col": "amount_msat" } } } }, "fee": { "to_decimal_sats_from_msat": { "if": { "cond": { "eq": { "a": { "trim": { "col": "fee_msat" } }, "b": { "lit": { "type": "string", "value": "" } } } }, "then": { "lit": { "type": "i128", "value": 0 } }, "else": { "parse_i128": { "trim": { "col": "fee_msat" } } } } } }, "destination": { "empty_to_none": { "trim": { "col": "destination" } } }, "description": { "empty_to_none": { "trim": { "col": "description" } } } } }, { "name": "invoice", "canonical_type": "invoice", "when": { "eq": { "a": { "lower": { "trim": { "col": "type" } } }, "b": { "lit": { "type": "string", "value": "invoice" } } } }, "fields": { "canonical_id": { "trim": { "col": "id" } }, "timestamp": { "parse_rfc3339": { "trim": { "col": "ts" } } }, "amount": { "to_decimal_sats_from_msat": { "parse_i128": { "trim": { "col": "amount_msat" } } } }, "settled_amount": { "if": { "cond": { "eq": { "a": { "trim": { "col": "settled_amount_msat" } }, "b": { "lit": { "type": "string", "value": "" } } } }, "then": { "none": { "type": "decimal_sats" } }, "else": { "some": { "to_decimal_sats_from_msat": { "parse_i128": { "trim": { "col": "settled_amount_msat" } } } } } } }, "description": { "empty_to_none": { "trim": { "col": "description" } } } } } ] } } ```