Push transactions to Subaio via SFTP

This document aims to help developers create zipped and encrypted transactions / account mappings data files.

1. Prerequisites

Generating SSH private / public key

$ ssh-keygen -t rsa -b 4096 -f subaio_sftp_integration

2. Implementation

The data should be exported in two files. The files must be in a CSV format, with quoted fields, semicolon separator, and a header row.

Files should be encrypted and compressed using streaming technologies such as PGP (encryption) and GZIP (compression). They should be compressed first and then encrypted to improve compression rate and entropy.

2.1 Extract transaction data

How transaction data is extracted is up to each bank, but we can provide some notes on the data export.

All transactions should be included in the data delivery with a few exceptions that can be excluded:

2.2 Generate CSV files

2.2.1 CSV format

Setting Description
Record separator semicolon (;)
Quoted double quoted ("example value") - all fields
 Header row Yes
Line separator \n

2.2.2 Account mapping

The first file contains a list of all accounts a user has access to that are payment accounts in the scope of PSD2. All accounts should always be sent no matter if there are transactions for it or not. The file should contain the following fields:

Field Example Description
bank_id "acmebank_dk"  
user_id "900000001"  
account_number "1234567890"  
Example
"acmebank_dk";"900000001";"1234567890"

2.2.3 Transaction data

The second file contains the list of all transactions that have been cleared on the accounts specified in the first file within the timespan of the specific export. Each transaction is linked to an account. The file should contain the following fields:

Field Example Description
bank_id "acmebank_dk"  
account_number "1234567890"  
transaction_id "bb758b88-87f2-42f0-8baf-547566998307"  
transaction_date "2019-12-24" ISO 8601 YYYY-MM-DD format
type "CARD_PURCHASE" CARD_PURCHASE, OTHER, ACCOUNT_TRANSFER, DIRECT_DEBIT, INVOICE
currency "DKK" ISO 4217 alphabetic currency code for the transaction currency, i.e. the accountholder’s own currency.
amount "-158.99" Purchase amount in cardholder currency. Purchases (money is taken from card/account) have a negative amount, and refunds have a positive amount.
original_currency "EUR" ISO 4217 alphabetic currency code for original transaction currency if purchase done in some other currency.
original_amount "-187792.687" Original amount if purchase done in some other currency. May contain more than two significant decimal digits if the originating currency supports them.
merchant_name "PAYPAL *NETFLIX.COM" Merchant name
mcc "4899" VISA Merchant Category Code when available.
Example
"acmebank_dk";"1234567890";"bb758b88-87f2-42f0-8baf-547566998307";"2019-12-24";"CARD_PURCHASE";"DKK";"-158.99";"EUR";"-187792.687";"PAYPAL *NETFLIX.COM";"4899"

2.3 Check CSV files

You should use the transaction validator tool to check all your CSV files

java -jar transactionvalidator-1.1.0.jar \
  -mode csv \
  -file example.csv

If you want to whitelist more transaction types you can use the -types flag

java -jar transactionvalidator-1.1.0.jar
  -mode csv \
  -file example.csv \
  -types PURCH,P2PCR,REFUN

2.4 Compress

Example java code that compresses CSV stream

try (var outputStream = Files.newOutputStream(newFilePath, CREATE, TRUNCATE_EXISTING);
     var zippedStream = GZIP.zip(outputStream);
     var inputReader = new BufferedInputStream(Files.newInputStream(filePath))) {
    StreamCopyHelper.writeFromInputToOutput(zippedStream, inputReader);
    zippedStream.flush();
    zippedStream.finish();
}

Example bash command to compress CSV file

gzip -c example.csv > example.csv.gz

You can also use the transaction validator tool to compress your CSV files

java -jar transactionvalidator-1.1.0.jar \
  -mode compressed_csv \
  -file example.csv

This should create a new file named example.csv.gz

2.5 Encrypt

You can use the transaction validator tool to encrypt your compressed CSV files

java -jar transactionvalidator-1.1.0.jar \
  -mode encrypted_csv \
  -file example.csv.gz \
  -recipient 0x8FCC2D5C \

By default it will use :

This should create a new file named example.csv.gz.pgp

2.6 Upload to SFTP server

sftp -P 2229 sftpuser@sample-sftp.integration.subaio.com
cd upload
put /Users/qte/mysql-prompt.sh /upload/mysql-prompt2.sh
rename /upload/mysql-prompt2.sh /upload/mysql-prompt3.sh

3. Verification

4. Integration environment

Subaio integration environment (*.integration.subaio.com) contains an example SFTP server where you can test upload files. Files are not processed, but deleted every hour.

Setting Value
Host name sample-sftp.integration.subaio.com
Port 2229
Username sftpuser
SSH private key You can use the example SSH private key subaio_sftp_example to login.

Files

Name
gpg_example_privatekey.asc
gpg_example_publickey.asc
subaio_sftp_example
subaio_sftp_example.pub
transactionvalidator-1.1.0.jar