And Then The Integration Became a CSV File in a Folder
Looking through my script library, the same model keeps coming up. An SFTP upload Suitelet. A saved search exporter that writes CSV to the file cabinet. A module for multipart file uploads. A validator for journal entry lines during CSV imports. Payment file templates for ACH. I’d love to call that a coincidence but seems to always happen in some capacity somehow… Money still moves as a file An ACH file is fixed length ASCII text. Every record is 94 characters, ten records to a block, and the first character says what kind of line it is. A 1 is the file header. A 5 starts a batch. A 6 is a payment entry. An 8 closes the batch and a 9 closes the file. Those last two are important. They carry counts, an entry hash, and total debits and credits, so the receiver can check the file against itself. Your CSV probably doesn’t have anything like that. I keep ACH and ISO 20022 payment file templates in a public repo, SEPA included. Different formats, same idea. The payment instructions travel as a file, and that file looks nothing like an API call. NetSuite can’t host the folder Here’s the constraint that shapes the design. Oracle’s documentation says NetSuite does not provide SFTP server functionality. SuiteScript 2.0 DOES have an SFTP library, but it only works one direction… NetSuite connects out to someone else’s server. Every SFTP transfer to or from NetSuite has to start from SuiteScript. So when a partner offers to just drop a file for you, the folder lives on a server somewhere else and NetSuite has to go get it. That means a script on a timer. It connects, lists the directory, downloads what it finds, and moves the file when it’s done. The N/sftp connection object has the methods for all of it: list, download, move, and removeFile. That’s often the whole integration. A folder somewhere else and a script on a timer. Why the folder keeps winning It isn’t laziness. A file waits. If the receiver is down overnight, nothing is lost. The file is still sitting there in the morning. A file is also easy to look at. You can open it, read it, and hand it to someone in finance who has never heard of a webhook. And it doesn’t care what the other system is built on. Where it hurts File drops fail in boring ways, and nothing pages anyone when they do. A NetSuite CSV import can finish with some records in and some out. Oracle’s own example of the job status reads “200 records of 205 imported.” The records that didn’t go through are in a results.csv file. The job still finished. Whether anyone opens that file is a separate question. It can get worse. Oracle also says that when an afterSubmit user event script fails after an import, the records are still created or updated, and you only need to retry the user event script, not the whole import. Rerun the whole file in Add mode and you can create every record twice. Treat the folder like an API The same rules apply. The folder just won’t enforce any of them for you. Give the file a trailer. Borrow the ACH idea: put a row count and a total on the last line, and have your script check them before it loads anything. Then validate every row before you load any of them. A partial import is a real outcome, so don’t start one you can’t finish. Ask the sender to upload under a temporary name and rename the file when it’s done, so you never pick up half of one. Keep a record of every file you’ve processed, so a file that gets sent twice doesn’t post twice. Move the original to an archive folder untouched. N/sftp has move, so an archive folder and an error folder cost almost nothing. Put failures where a human will see them, and send a message when you do. Then alert when a file doesn’t show up at all. Silence is a failure too. Nobody puts the CSV in the folder on an architecture slide. but nevertheless, legacy systems refuse to change. some random requirement comes in at the last min. Or user understanding of the transmission isnt quite where it needs to be… Build it like it it will probably happen anyway.