Skip to main content
Reference mapping for FirstCaribbean International Bank (FCIB) statement CSVs to Rainy Day’s normalized transaction model. Keep this in sync with packages/utils/src/types.ts and Supabase schema.

​
Source format

  • Header lines (first two):
    • Account Name:- SampleBank-XXXXXX-BSD (SAVINGS ACCOUNT) → account metadata (display name, masked number, currency BSD, bank = fcib).
    • Specified period:-(04/11/2025 - 04/12/2025) → statement period start/end (DD/MM/YYYY → 2025-11-04 to 2025-12-04), source bank = fcib, stored in statement.rawHeader.
  • Columns: Date, Cheque Number, Description, Debit Amount, Credit Amount, Running Balance.
  • Date format: DD/MM/YYYY.
  • Currency: BSD.

​
Field mapping

FCIB field / line                 → Normalized field(s)
Account Name header                → account.displayName, account.accountNumberMasked, account.currency=BSD, account.bank=fcib
Specified period header            → statement.periodStart, statement.periodEnd, statement.sourceBank=fcib, statement.rawHeader (JSON of the header lines)
Date                               → transaction.postedAt (DD/MM/YYYY → ISO date)
Cheque Number                      → transaction.bankMetadata.chequeNumber (nullable)
Description                        → transaction.description; transaction.bankMetadata.originalDescription
Debit Amount / Credit Amount       → transaction.amount (signed), transaction.direction (outflow if debit/negative, inflow if credit/positive); keep raw debit/credit in transaction.rawRow
Running Balance                    → transaction.balanceAfter; transaction.bankMetadata.runningBalance
Full CSV row                       → transaction.rawRow (and/or transaction.raw_row in DB)
Uploaded filename                  → transaction.bankMetadata.sourceFileName
Statement/account headers (raw)    → statement.rawHeader json; account.displayName/accountNumberMasked

​
Amount & direction rules

  • If Debit Amount has a value (or negative in CSV), treat as amount < 0 and direction = outflow.
  • If Credit Amount has a value (or positive), treat as amount > 0 and direction = inflow.
  • Running Balance is provided; store as balanceAfter but do not recompute amount from it.

​
Derived flags & kind heuristics

  • isPending: true if Description contains PENDING.
  • kind suggestions (adjust as you refine rules):
    • Contains VISA ABM WITHDRAWAL → atm_withdrawal
    • Contains VISA ABM FEE or FEE → fee
    • Contains AFFIRM, PAYPAL, AMAZON, UBER, GOOGLE, APPLE, 7-ELEVEN → purchase
    • Counterparty-style credits (e.g., Sample Payee) → transfer_in or income (pick based on future rules)
    • Large negative PayPal to person → transfer_out
  • category: may be unset initially; populated later by rules/manual assignment.
  • import_hash: hash of statement_id + raw_row (or filename + row data) to dedupe rows; required in schema.
  • statement hash: compute SHA-256 over stable inputs to dedupe uploads; required + unique in schema.
    • Inputs (in order): account_id, source_bank, period_start, period_end, source_filename, raw_header, normalized file contents
    • Normalization steps:
      1. Character encoding: All string inputs must be encoded as UTF-8.
      2. Line endings: Normalize all line endings in file contents to LF (\n).
      3. Whitespace: Trim leading/trailing whitespace from the entire file contents (do not alter internal spacing within lines). Also trim leading/trailing whitespace from source_filename but do not change internal spacing.
      4. JSON stability: Serialize raw_header with stable key ordering and no extra whitespace before hashing (so different serializers produce identical strings).
      5. Concatenation: Concatenate inputs in the order above, separated by a single | character (pipe).
    • Example pseudocode: 
      hash_input = account_id + "|" + source_bank + "|" + period_start + "|" + period_end + "|" + source_filename + "|" + raw_header + "|" + normalized_file_contents
statement_hash = SHA256(UTF8(hash_input))
    • This ensures consistent hash generation and prevents duplicate uploads due to minor formatting differences.

​
Example rows (from sample)

  1. 02/12/2025,,AMAZON PRIME PMTS Amzn.,-7.57,0,-1.02
    • postedAt: 2025-12-02; amount: -7.57; direction: outflow; description: AMAZON PRIME PMTS Amzn; kind: purchase; balanceAfter: -1.02; isPending: false.
  2. 27/11/2025,,Sample Payee,0,1000.00,1601.39
    • postedAt: 2025-11-27; amount: 1000.00; direction: inflow; description: Sample Payee; kind: transfer_in/income; balanceAfter: 1601.39; isPending: false.
  3. 28/11/2025,,UBER * EATS PENDING SAN F,-29.31,0,1552.09
    • postedAt: 2025-11-28; amount: -29.31; direction: outflow; description: UBER * EATS PENDING SAN F; kind: purchase; balanceAfter: 1552.09; isPending: true.
  4. 01/12/2025,,VISA ABM WITHDRAWAL,-816.00,0,122.49
    • postedAt: 2025-12-01; amount: -816.00; direction: outflow; description: VISA ABM WITHDRAWAL; kind: atm_withdrawal; balanceAfter: 122.49; isPending: false.

​
Edge cases & notes

  • Empty cheque numbers are allowed; keep nullable.
  • Multiple fees/withdrawals in the same day are common; rely on import_hash for dedupe.
  • Keep raw row data to aid troubleshooting (spacing in descriptions is significant).
  • Assume BSD; if future files include other currencies, capture in bankMetadata.currency.
  • Do not infer sign from Running Balance deltas; always trust Debit/Credit columns for amount.