Real World: Banking FTP Integration

This guide covers integrating NetSuite with bank SFTP systems for payment file processing, including IP whitelisting requirements and ACK1/ACK2 acknowledgment patterns.

Overview

Banking SFTP integration is one of the most common middleware patterns in enterprise environments. Banks typically require:

Static IP whitelisting for security compliance
ACK files to confirm file receipt and processing status
Specific file formats unique to each bank

Architecture Options

Two main approaches depending on whether NetSuite IP is whitelisted by the bank:

SCENARIO A: Full VPS Middleware (NetSuite IP NOT whitelisted)
─────────────────────────────────────────────────────────────────
NetSuite → VPS (Static IP) → Bank SFTP
           VPS polls bank for ACK files
           NetSuite polls VPS for ACK files

SCENARIO B: Hybrid - Direct Upload + VPS ACK Bridge (NetSuite IP whitelisted)
─────────────────────────────────────────────────────────────────
NetSuite → Bank SFTP (direct)
           VPS polls bank for ACK files
           NetSuite polls VPS for ACK files

Why VPS Middleware for Banking?

Challenge	Solution
Bank requires static IP whitelist	VPS has static IP (e.g., 203.0.113.50)
NetSuite IPs are dynamic	NetSuite uploads to VPS, VPS uploads to bank
Bank uses legacy SFTP only	VPS handles SFTP with proper authentication
ACK files need polling	Cron jobs on VPS poll bank periodically
Security compliance	VPS in controlled environment with audit logs

ACK1/ACK2 File Pattern

PAYMENT FILE LIFECYCLE
─────────────────────────────────────────────────────────────────

File Naming Convention:
PAYMENT_[BATCHID]_[YYYYMMDD]_[SEQ].txt     ← Payment instruction
PAYMENT_[BATCHID]_[YYYYMMDD]_[SEQ].ACK1    ← File received by bank
PAYMENT_[BATCHID]_[YYYYMMDD]_[SEQ].ACK2    ← Processing result

Example:
PAYMENT_BATCH001_20240115_001.txt
PAYMENT_BATCH001_20240115_001.ACK1
PAYMENT_BATCH001_20240115_001.ACK2

Timeline:
─────────────────────────────────────────────────────────────────
T+0         T+5min              T+2hours
│           │                   │
▼           ▼                   ▼
Upload      ACK1                ACK2
(.txt)      (Received)          (Processed/Rejected)

Stage	File Extension	Content	Timing
Payment Instruction	`.txt`	Payment details (bank format)	Immediate
ACK1 (Acknowledgment 1)	`.ACK1`	File received confirmation	1-30 minutes
ACK2 (Acknowledgment 2)	`.ACK2`	Processing result with status codes	1-24 hours

Scenario A: Full VPS Middleware

Use when: NetSuite IP is NOT whitelisted by the bank.

FULL VPS MIDDLEWARE ARCHITECTURE
─────────────────────────────────────────────────────────────────

NetSuite                    VPS Middleware              Bank SFTP
(Dynamic IP)                (Static IP: 203.0.113.50)   (Firewall)
Can't connect               Whitelisted by Bank         Only allows
directly                                                whitelisted IPs
     │                           │                          │
     │                           │                          │
     │  1. Generate Payment      │                          │
     │     Instruction File      │                          │
     │                           │                          │
     │  2. SFTP Upload to VPS    │                          │
     │ ─────────────────────────▶│                          │
     │     /incoming/            │                          │
     │                           │  3. Cron: Upload to Bank │
     │                           │ ────────────────────────▶│
     │                           │     /outgoing/           │
     │                           │                          │
     │                           │                          │ Bank processes
     │                           │                          │ (minutes to hours)
     │                           │                          │
     │                           │  4. Cron: Poll for ACK1  │
     │                           │◀──────────────────────── │
     │                           │     PAYMENT_001.ACK1     │
     │                           │                          │
     │  5. Scheduled Script      │                          │
     │     Poll VPS for ACK      │                          │
     │◀───────────────────────── │                          │
     │     /ack/                 │                          │
     │                           │                          │
     │  6. Update Payment        │  7. Cron: Poll for ACK2  │
     │     Status: ACK1_RECEIVED │◀──────────────────────── │
     │                           │     PAYMENT_001.ACK2     │
     │                           │                          │
     │  8. Poll VPS for ACK2     │                          │
     │◀───────────────────────── │                          │
     │                           │                          │
     │  9. Update Payment        │                          │
     │     Status: COMPLETED     │                          │
     │     or REJECTED           │                          │

VPS Directory Structure

/opt/bank-integration/
├── bin/
│   ├── upload_to_bank.sh       # Upload payment files to bank
│   ├── download_ack.sh         # Download ACK files from bank
│   └── cleanup_old_files.sh    # Archive old files
├── config/
│   └── bank_sftp.conf          # Bank SFTP credentials
├── data/
│   ├── incoming/               # Files from NetSuite (to upload)
│   ├── outgoing/               # Uploaded to bank (waiting ACK)
│   ├── ack/                    # ACK files for NetSuite to fetch
│   ├── processed/              # Completed files (archive)
│   └── failed/                 # Failed files for investigation
├── logs/
│   └── integration.log         # All activity logs
└── keys/
    └── bank_sftp_key           # SSH private key for bank SFTP

VPS Cron Jobs

Schedule	Script	Purpose
Every 5 min	`upload_to_bank.sh`	Upload payment files to bank
Every 10 min	`download_ack.sh`	Download ACK files from bank
Daily midnight	`cleanup_old_files.sh`	Archive old files

Scenario B: Hybrid - Direct Upload + VPS ACK Bridge

Use when: Bank allows NetSuite direct SFTP connection for uploads (NetSuite IP whitelisted), but ACK files need VPS bridge because NetSuite can't poll bank frequently enough.

HYBRID ARCHITECTURE - DIRECT UPLOAD + VPS ACK BRIDGE
─────────────────────────────────────────────────────────────────

NetSuite                    VPS Middleware              Bank SFTP
(IP Whitelisted)            (Static IP)                 (Firewall)
     │                           │                          │
     │  1. Generate Payment      │                          │
     │     Instruction File      │                          │
     │                           │                          │
     │  2. SFTP Upload DIRECT to Bank ─────────────────────▶│
     │     (NetSuite IP whitelisted)   /outgoing/           │
     │                           │                          │
     │                           │                          │ Bank processes
     │                           │                          │ (minutes to hours)
     │                           │                          │
     │                           │  3. Cron: Poll for ACK   │
     │                           │◀──────────────────────── │
     │                           │     /ack/                │
     │                           │                          │
     │                           │  4. Copy ACK to VPS      │
     │                           │     /data/ack/           │
     │                           │                          │
     │  5. Scheduled Script      │                          │
     │     Poll VPS for ACK      │                          │
     │◀───────────────────────── │                          │
     │                           │                          │
     │  6. Move processed ACK    │                          │
     │     to /data/arch/        │                          │
     │ ─────────────────────────▶│                          │
     │                           │                          │
     │  7. Update Payment        │                          │
     │     Status in NetSuite    │                          │

Why This Hybrid Approach?

Advantage	Description
Lower latency	NetSuite uploads directly to bank (no VPS hop)
Reduced VPS load	VPS only handles small ACK files
Bank compliance	Bank still controls ACK file access
Reliable polling	VPS cron provides consistent ACK polling

VPS Directory Structure (Simplified)

/opt/bank-integration/
├── bin/
│   ├── download_ack.sh        # Poll bank for ACK files
│   └── cleanup_arch.sh        # Archive old ACK files
├── config/
│   └── bank_sftp.conf         # Bank SFTP credentials
├── data/
│   ├── ack/                   # Fresh ACK files for NetSuite
│   └── arch/                  # Processed ACK files (archive)
└── logs/
    └── ack_poll.log           # ACK polling logs

NetSuite Custom Record: Payment Batch

Create custom record customrecord_payment_batch:

Field ID	Type	Purpose
`custrecord_pb_batch_id`	Text	Unique batch identifier
`custrecord_pb_filename`	Text	Generated filename
`custrecord_pb_file_content`	Long Text	Payment file content
`custrecord_pb_status`	List	PENDING, UPLOADED, ACK1_RECEIVED, ACK2_RECEIVED, COMPLETED, REJECTED, FAILED
`custrecord_pb_upload_date`	Date/Time	When uploaded to VPS
`custrecord_pb_ack1_date`	Date/Time	When ACK1 received
`custrecord_pb_ack2_date`	Date/Time	When ACK2 received
`custrecord_pb_ack2_code`	Text	Bank response code from ACK2
`custrecord_pb_error`	Long Text	Error message if failed

Status Flow Diagram

PAYMENT BATCH STATUS FLOW
─────────────────────────────────────────────────────────────────

    ┌─────────┐
    │ PENDING │  ← Created, not yet uploaded
    └────┬────┘
         │ Upload to VPS successful
         ▼
    ┌──────────┐
    │ UPLOADED │  ← File on VPS, waiting for bank ACK
    └────┬─────┘
         │
    ┌────┴────────────────────────────────┐
    │                                      │
    ▼                                      ▼
┌──────────────┐                    ┌────────────┐
│ ACK1_RECEIVED│  ← Bank received   │   FAILED   │  ← Upload/Connection error
└──────┬───────┘    the file        └────────────┘
       │
       │ Bank processes (1-24 hours)
       ▼
┌──────────────┐
│ ACK2_RECEIVED│  ← Bank finished processing
└──────┬───────┘
       │
  ┌────┴────┐
  │         │
  ▼         ▼
┌─────────┐ ┌──────────┐
│COMPLETED│ │ REJECTED │  ← Bank rejected (bad data, etc)
└─────────┘ └──────────┘

Comparison: Full VPS vs Hybrid

Aspect	Full VPS (Scenario A)	Hybrid (Scenario B)
Payment upload path	NetSuite → VPS → Bank	NetSuite → Bank (direct)
ACK retrieval path	Bank → VPS → NetSuite	Bank → VPS → NetSuite
VPS responsibility	Full file relay	ACK bridge only
Latency	Higher (2 hops)	Lower (1 hop for upload)
VPS disk usage	Higher	Lower
Complexity	Medium	Medium
Best for	NetSuite IP not whitelisted	NetSuite IP whitelisted by bank

Decision Guide

CHOOSING BETWEEN FULL VPS AND HYBRID
─────────────────────────────────────────────────────────────────

Is NetSuite IP whitelisted by bank?
    │
    ├── No  ──▶ Use Scenario A (Full VPS)
    │           NetSuite cannot connect to bank directly
    │
    └── Yes ──▶ Use Scenario B (Hybrid)
                NetSuite uploads direct, VPS bridges ACK files

VPS Setup Checklist

Step	Action
1	Provision VPS with static IP (AWS, DigitalOcean, Linode, etc.)
2	Provide IP to bank for whitelisting
3	Configure VPS firewall (allow SSH from office, SFTP from NetSuite)
4	Install `openssh-sftp-server`
5	Create integration user and directories
6	Generate SSH key for bank SFTP connection
7	Deploy bash scripts to `/opt/bank-integration/bin/`
8	Configure cron jobs
9	Test connectivity to bank SFTP
10	Set up monitoring and alerting

Troubleshooting

Issue	Check	Solution
Upload fails	VPS disk space	Clean up old files
Bank rejects connection	IP whitelist	Verify VPS IP with bank
ACK files not appearing	Bank processing time	Wait, check bank status
Status file empty	Cron not running	Check `systemctl status cron`
Permission denied	File ownership	`chown bankint:bankint /opt/...`
SFTP timeout	Network/firewall	Check VPS firewall rules

Middleware Pattern - General middleware architecture
File-Based Integration - Alternative file exchange methods
Push Out - NetSuite pushes to external systems

Overview​

Architecture Options​

Why VPS Middleware for Banking?​

ACK1/ACK2 File Pattern​

Scenario A: Full VPS Middleware​

VPS Directory Structure​

VPS Cron Jobs​

Scenario B: Hybrid - Direct Upload + VPS ACK Bridge​

Why This Hybrid Approach?​

VPS Directory Structure (Simplified)​

NetSuite Custom Record: Payment Batch​

Status Flow Diagram​

Comparison: Full VPS vs Hybrid​

Decision Guide​

VPS Setup Checklist​

Troubleshooting​

Related Patterns​