
The discipline of Software Engineering is built on the successes and progress of those that came before. Each advancement either further replaces or abstracts the code surface from the 1s and 0s. Each layer transition represents an interface contract, or **schema** that is used to communicate up or down the abstraction stack.

Our conclusion here should be that **schemas** should not change without warning or planning, particularly those which are considered standard. There are a variety of ways that standard schema is determined.


## Who decides what "schema" is?

There are a variety of ways a standard schema is determined:

- Governing Organizations
    - ISO datetime standards (e.g., ISO 8601)
    - Units of measurement (SI)
    - Structured data interchange formats

These are slow to change, but extremely stable and broadly adopted.

- Community Coordination
    - For a modern example: the MCP server schema for client-server LLM integrations.
      While there is an MCP organization, the LLM development community rapidly prototypes and iterates on patterns — the best ones are then codified into the official MCP schema.

Allows innovation while still preserving a stable, shared contract.

  - Trading Partner Agreements
    - The EDI “Implementation Guides” that define which segments and elements are used.
    - Custom flat-file or XML definitions that evolve as the business relationship changes.

These are effectively bespoke standards — which makes schema validation even more critical.

## Can you trust your system?

I recently began an integration effort with a trading partner which does not have traditional EDI software. From the outside, it seems like a home-brewed ERP with what they described as "X12 Inspired" flatfiles. 

The resulting integration has proven to be brittle, and with nearly every new scenario that is tested, breaks further.

1. Type Codes (Multiple): Their system uses three character US Customs Commodity codes. X12 uses their own limited set of 2 character codes. The trading partner just overloads the field and tells us to figure it out based on a list of several hundred possible options. Not only does this break the X12 schema, but it's adding maintenance overhead.

2. Loss of Data Hierachy: Their system doesn't enforce accurate data entry (or accurate integration) for data with a hierachy. Think: Which products are on which pallet? That's important information for logistics operators to know about and plan for!

Between these two aspects, it has proven difficult to maintain a robust integration layer because our trading partner doesn't know if their system will break X12 schema, and has no way to report or predict violations.

## Okay so I can't trust my schema... what now?
Unfortunately, when trust can't be built, accountability must be enforced. 

From my perspective and experience, the only way to effectively implement an integration with a trading partner who's schema you can't trust is via monitoring and reporting. I am implementing an hourly digest that will be sent to this particular business lane's stakeholders, informing them of processing failures. To go further, I plan on ingesting this data into our Data Warehouse so we can see the latency caused by this particular trading partner.

Is this the only way? How would you handle an unreliable integration partner?


Building (and Enforcing) Integration Trust


This article is more of a knowledge base for various code snippets, samples and gists! Take any of the below at your own risk!
## PostgreSQL
<details>
  <summary>CLICK ME TO EXPAND POSTGRESQL</summary>

### Generated Value as Key


```sql
CREATE OR REPLACE FUNCTION public.generate_key(_len int)  
    returns varchar(50)  
as  
$$  
SELECT substr(md5(random()::text), 0, (_len + 1))::varchar;  
$$  
    language sql  
    immutable;

CREATE TABLE IF NOT EXISTS tableName(
    generatedKey varchar(8) NOT NULL GENERATED ALWAYS AS (public.generate_key(8)) STORED
);

```


### Make pseudo id column
When joining things that are pre-sorted, in order to join together, needs a pseudo ID field. Use ROW_NUMBER()

```sql
WITH docIds as (SELECT id from documents d order by id desc limit 15),  
     docIdsWithCount as (SELECT row_number() over (order by id) as rowNum, id from docIds),  
     refIds as (SELECT id from references r order by id desc limit 15),  
     refIdsWithCount as (SELECT row_number() over (order by id) as rowNum, id from refIds),  
SELECT r.id as refId, d.id as docId from docIdsWithCount d  
        left join refIdsWithCount r on d.rowNum = r.rowNum
```


### Delete all data from all tables
USE WITH CAUTION!
```sql
DO  
$$  
DECLARE  
    rec RECORD;  
BEGIN  
    -- Generate the truncate statements for all tables in the specified schema  
    FOR rec IN  
        SELECT tablename  
        FROM pg_tables  
        WHERE schemaname = 'edi_transactions'  
    LOOP  
        EXECUTE 'TRUNCATE TABLE edi_transactions.' || quote_ident(rec.tablename) || ' CASCADE;';  
    END LOOP;
END  
$$;
```

### jsonb_typeof
Detect type of json structure in parameter - perform looping if array, etc.
```sql
if jsonb_typeof(_schemaarray) = 'array' THEN   
  -- DO THINGS HERE
END IF;
```

</details>


## jq
<details>

<summary>CLICK ME TO EXPAND JQ</summary>

Check out [jqCheatSheet](https://lzone.de/cheat-sheet/jq) for fast help!

### Use args in jq filter
```bash
jq --arg v "$PRJNAME" '.dev.projects[$v]' config.json 

```

### JSON.parse, but in jq
read property that contains a string as json
```bash
cat response.text | jq ' . | fromjson.title'
```

### Minify JSON
Minify the string
```
echo '{ "foo": "bar" }' | jq -r tostring
```


Minify a file

```
jq -r tostring file.json
```

### Merge Objects
Take two json objects stored in bash variable and merge them together based on key
```bash
jq -n --argjson a "$a" --argjson b "$b" '$a + $b'
```

### Recursive Merge 
[Recursive JSON Object Merge with jq](https://stackoverflow.com/questions/19529688/how-to-merge-2-json-objects-from-2-files-using-jq)
```bash
# note: jq 1.4 or later
jq -s '.[0] * .[1]' file1 file2
```

### Array of Objects to CSV
```
cat json-filename.json | jq -r '(map(keys) | add | unique) as $cols | map(. as $row | $cols | map($row[.])) as $rows | $cols, $rows[] | @csv'
```

### CSV file to JSON
Requires manual setting of column names, but could automate that using GPT pipeline or other script.
```
jq --slurp --raw-input --raw-output \
  'split("\n") | .[1:] | map(split(",")) |
      map({"status": .[0],
           "date": .[1],
           "description": .[2],
           "debit": .[3],
           "credit": .[4],
           "member": .[5]})' \
  Since\ Nov\ 15,\ 2023.CSV
```

</details>


Snippets, Samples, and Gists


## History

iXsystems is known for their very stable and well adopted enterprise NAS operating system TrueNAS CORE. This is ran on FreeBSD. In 2022, they released TrueNAS SCALE. SCALE is ran on top of Debian Linux, and thus can be run on a broader range of hardware, making it an option for a broader market.[^1] 
s
When initially deciding on requirements for SCALE, iXsystems wanted to give their users the ability to deploy services alongside their NAS. Coming from an enterprise services perspective, Kubernetes was an obvious option. From their initial GA SCALE release named Angelfish, until the most recent release in 2024, Dragonfish, Kubernetes was the supported container orchestration platform. Throughout the two years, I personally read many support articles and blog posts with the single complaint that SCALE did not support Docker natively. ixSystems apparently heard the complains and have decided to "pivot". 

As of their next release, Electric Eel, iXsystems will no longer support Kubernetes for their containerization service. In my opinion, this is a welcome change. However, an entire ecosystem was created around Kubernetes supported TrueNAS SCALE apps. The most notable was the [Truecharts](https://truecharts.org/) organization. Truecharts builds and maintains a large list of opinionated Helm charts that make spinning up pods quickly a breeze, particularly via the SCALE GUI. Many casual users of SCALE took advantage of the broad catalog of applications. But with the release of Electric Eel, the rug has been pulled on those not using "Official" TrueNAS Applications. iXsystems will thankfully provide an automatic transition to the new containerization service: Docker.



## Truecharts Response
Truecharts received the news of Kubernetes deprecation along with the rest of the community. While they have a "path forward", it [won't include TrueNAS SCALE](https://truecharts.org/news/scale-deprecation/). Rather, they will recommend a Linux distribution called Talos. Read more [here](https://truecharts.org/news/clustertool-update/). 


## My Setup
I currently run a home server with an i5-6600 processor, 32GB DDR4 RAM, 2x 256GB NVME SSD for mirrored boot drives, and 3x 8TB HDDs in a ZFS Pool. If it isn't obvious by now, this is running TrueNAS SCALE on bare metal. I run exclusively Truecharts apps for many use-cases: NextCloud, Tailscale, Media Management Applications, Plex, uptime-kuma, and Kavita. This means that I am among the individuals forced to transition my applications to official TrueNas Apps, or else. . .

## Proof of Concept
Electric Eel won't be released for GA until late October 2024, but I wanted to get a head start - summers are busy!

Effectively, my plan is to:

1. Backup and/or store application configurations from Truecharts applications.

2. Screenshot or document the Truechart's application configuration, as there is not an automated way to do this.

3. Start a new version of the application from TrueNAS SCALE's official release, mirroring as much as possible from Truecharts.

4. Verify that directories can be mounted and resources accessed.

Except there are some additional complications.

### Mounted Directories

iXsystems, in an attempt to safety belt users, added checks to prevent mapping host path directories. This means there's two options. The first is to place all data into a system managed dataset. The second is to mount a network SMB share. 

While SMB is not the ideal path, this is the one I found to be the most straight-forward.

### VPN
All of the Truecharts applications leveraged a Gluetun add-in that allowed application traffic going over the internet to use a VPN provider. The TrueNAS SCALE applications don't currently have any configuration for VPN. I'm hopeful that the Docker implementation will allow for creating Docker stacks, so I can combine my current services with an additional VPN container.

## Whats Next?
While I'm optimistic about Electric Eel's release, and the implementation of Docker, the lack of migration support for Truecharts applications from iXsystems is somewhat frustrating. It seems like iXsystems shouldn't have opened the gate to third party app providers unless they were confident in their implementation of Kubernetes. I'm taking this as a learning experience in leveraging third-party plugins to any ecosystem. While helpful in the short term, they shouldn't be depended upon unless SLAs are communicated.


[^1] See https://www.ixsystems.com/history/ for a comprehensive history of ixSystems


Truecharts Deprecation and TrueNAS SCALE


# Race Conditions in Event Streaming Dependent Services

**Disclaimer:** Details below have been changed to retain some level of obfuscation to my employer's tech stack, ERP systems, etc.

## "Standard Operating Procedure"

Everyone with a year or more experience in almost any industry has heard the phrase: "It's standard operating procedure." For me personally, this phrase sets off alarm bells when stated in a requirements or business logic discovery meeting. There is an other common phrase, typically said less in the workplace, and I feel its an appropriate dichotomy to SOP. That is: "Rules are meant to be broken."

If a business leader ever states, "Its standard operating procedure" or "That should never happen". You should bet the house on the exact thing occurring within 6 months of pushing a feature or product that depends on SOP being followed. When that same leader comes to your desk and says "Well I didn't mean _that_ process, or _that_ customer!". 


## Required Reading

My employer operates in the Transportation and Logistics industry. This industry is a notoriously difficult one for "disruptors" to establish themselves, regardelss of how much [VC money they collect](https://www.geekwire.com/2023/convoy-collapse-read-ceos-memo-detailing-sudden-shutdown-of-seattle-trucking-startup/). I will probably write another blog post about why I think this is, but for this article, you just need to know that the majority of data entered, configured, or communicated about shipments has a human being typing, scanning, or literally writing it down prior to any automated processes get involved. The other context you need to know is that there are typically three minimum "documents" required for moving goods: Shipments, Routings, and Invoices. **Shipments** represent the customer's view (Supplier Warehouse to customer's store). **Routings** represent the legs of the shipment (Truck from warehouse to ocean port, ocean port to ocean port, then finally ocean port to customer). **Invoices** - well those are obvious - its how we get paid after successful execution of the shipment.


**Standard Operating Procedure**:

1. An overseas agent is responsible for booking shipments into a company's ERP system on behalf of customers looking to import to the US. They also are required to enter the first Routings to coordinate moves from origin to origin ocean port. These agents are graded on metrics like "number of clicks to booking", or "minutes spent on document entry". Their incentives are such that they will find ways to do things as fast as possible.


2. Data from the ERP system is extracted via an asynchronous polling job (1 minute intervals) based on change capture tables. This data is streamed into Kafka topics.


3. "Middleware" system disects the Kafka stream based on business segment, vertical or customer for use in downstream integrations.


## The Problem Statement
The below flowchart illustrates the data feed coming from the ERP, entering Kafka Topics, and a few relevant microservices who are subscribed to them.


![StreamingFlow](/images/posts/kafka-overpressure-start.jpg)


As anyone familiar with Kafka or flow charts would be able to tell you, there is a very obvious race condition in this architecture. If a document from ERP system reaches the dispatcher prior to getting registered to one or more of our "domains" (this is the specific business segment, vertical, or customer), it will fail to be dispatched to the downstream consumers who are patiently waiting for that data.

This race condition can really only happen under a couple of scenarios, both of which are outside "SOP":


1. Changes to parent/child documents occur in the same polling interval window (1 minute)


2. An outage occurs (either Middleware or ERP system) and there is a large backlog of data.


The outage scenario is on our engineering team to solve, however, due to the nature of our ERP integration, its relatively straighforward to "rewind" any missed data, so this was much less of a concern.


### Discovery
This issue was only discovered because in our UAT, the testers were using non-SOP data entry methods. They would pre-populate both the Shipment and Routing documents, then save both in rapid succession. This was not defined in the business's SOP, but it occured twice in UAT testing. This, combined with the KPIs used to grade our overseas agents, and knowing they will be doing everything they can to enter data as quickly as possible, a solution was required.

## Solution

The team put their heads together and came up with some ideas:

- **Upstream Seeding:** Trigger new **dispatcher** events after business segment registration.
    - Pro: Guaranteed matching for same-batch messages
    - Con: O(n) x (Number of Linked documents - 1) rather than just O(n)


- **Forced Order:** Designate the order of document extract in each polling batch from the ERP so that Shipments would always be processed prior to Routings
    - Pro: Guarantees order of dependent documents
    - Con: Works for this specific scenario, but what if a business segment is registered by Routings? Then the Shipments would never find a registered Routing.


- **Delay Queue:** Delay processing of the router by a few seconds
    - Pro: Will solve this specific race condition, and allows for future scenarios unlike #2.
    - Con: Has the potential to need longer delays as the system scales
    - Con: will constantly run application until the next message is older than set lag.



We elected to implement the Delay Queue. Not only was it a straightforward code change, but the lag can be extended in the future to buy us time to find a more scalable solution. Additionally, due to our chosen Cloud Provider, we pay for our compute whether we are using the resources or not. Running the router and having it exit after a few lines of code has no impact on our compute spend.

The DelayQueue was implemented in two hours into our UAT environment and confirmed working the following business day.

## Lessons Learned

Rules are meant to, and will be broken.




Race Conditions in Event Streaming Dependent Services


Hello World!

Setting up this static site was a project of patience, test of my prompt engineering, and Google search term skills.

## Goal of this Blog
My goal here is to document and share my path of discovery and exploration along new technologies. This is of course mostly for my reference, but if it can assist someone else, I am glad to have contributed!

This boiled down to three requirements:

  1. Static Site - this is a blog and basic skills demonstration site. Nothing fancy required.

  2. Automatic Deployment

  3. Available at my domain: [https://barrancolab.com](https://barrancolab.com)
 

## Step 1: Static Site Front End

My first step was to identify a front end framework. I am personally not passionate about page design, color picking, or even UI/UX in general. I like to make things work, and work well. My requirements were that the Front End Framework was well documented, the pages were generated from Markdown, and supported image imbedding.

One of the first Google searches for "Static Site Front End Github" is [Awesome Static Generators](https://github.com/myles/awesome-static-generators) Here is where I found the [StaticHunt](https://statichunt.com/) collection of static site front ends.

I picked out the [Hydrogen Theme](https://statichunt.com/themes/hugo-hydrogen) because NextJS appears to have great documentation and online support.

### Github Configuration
Just as any project, this one begins with the creation of a new repository in my Github. Thus, [jaredbarranco/barrancolab-blog](https://github.com/jaredbarranco/barrancolab-blog) was forked from the Hydrogen theme repository.

After cloning the main branch to my local working directory, I went into the Github Repository Settings and configured a deployment environment with my main branch and required approvals for deployments.

#### Deployment Environment
![production-deployment-environment](/images/posts/production-deployment-env.png)

#### Approval Required
![production-deployment-rules](/images/posts/production-deployment-rules.png)

### Updates to the Hydrogen repo
Hydrogen theme repo used NextJS and Tailwind. I won't be updating any of the Tailwind configuration - I chose the theme for a reason.


#### next.config.js

To get static output from a NextJS file, we need to "output: export" and enable "unoptimized" for the [Image Component](https://nextjs.org/docs/pages/api-reference/components/image#unoptimized)

```
  const nextConfig = {
    reactStrictMode: true,
    output: 'export',
    images: {
      unoptimized: true,
    }
  };
```

#### Nav Bar Re-Org
I wanted to keep the "Home" page of my site as plain as possible, with minimal links. This means I needed to re-org the Nav Bar. 

The theme has three main page types: main, page, and post. The "main" page is the home landing page. The "page" type can be treated as a "subpage" since it contains a fast link back to "main". Finally, the "post" page is for blog posts. To begin, I [added a Dedicated blog page to the menu.json array](https://github.com/jaredbarranco/barrancolab-blog/commit/0bf2ce1b0a390f3a30b9ff7b587b60ce72009a78). 

All pages gets links added to them based on "Header.js". I found where the Nav Links are selected and added. I [reassigned](https://github.com/jaredbarranco/barrancolab-blog/commit/46d90b38bd388757fbc9b87de26b2b8cc2f9828a#diff-134f059f6afa4857c72a9b09e4a74672cba3f61299b8ad7f4311821d7f71541a) this functionality to the "/blog" page. Now, only the Blog will contain the categories identified by the taxonomy parser.

The Blog page's format is basically the lower half of the original Hydrogen theme. A quick copy/paste later, we have the [blog sub-page](https://github.com/jaredbarranco/barrancolab-blog/commit/9ed77496febc6aa0fb08ed103a398bdb24144cf1). The page's slug will automatically use this as the source.

#### Disqus Integration
Disqus is a discussionb board API and platform. I created a free account, setup a site, and [commmitted the updates to enable Disqus](https://github.com/jaredbarranco/barrancolab-blog/commit/d13a6938a0fca4a032b685ec59be56e622645c71).

#### template.md and scripts/new_post.sh
I wanted to make creating new posts as easy as running a quick script. I am quite familiar with bash. So I had ChatGPT generate me a [bash script](https://github.com/jaredbarranco/barrancolab-blog/commit/6aff51d7f0c43fe66fed44cc2dd1ce3a4f75b534) to copy/paste a new [template.md](https://github.com/jaredbarranco/barrancolab-blog/commit/aa2f3b4120dc4bd3fcb5ae50fdb400ce33188f50) file, and replace some key values that make the Discus integration mostly invisible to me in the future.

### Github Workflows
I have written tons of Github workflows for my "real job", so this one was mostly trivial after discovering the required runner permissions for [AWS integration](https://github.com/aws-actions/configure-aws-credentials) and the [Origin Access Control](https://aws.amazon.com/blogs/networking-and-content-delivery/amazon-cloudfront-introduces-origin-access-control-oac/) setup in AWS.

The workflow checks out the repostiory, installs npm, runs the build command, then uses S3 sync with a OAC defined role in my personal AWS VPC.

### Cloudfront and Cloudflare
I already own the domain "barrancolab.com" which is currently registered at Cloudflare. I have other services I run via their ZeroTrust tunnel, so I don't want to move it to AWS. I wanted to take advantage of the Cloudfront CDN, and since this is a static website, it should be an extremely short time to first paint!

There is some setup required though . . .

#### Amazon Certificate Manager
In order to [enable SSL/TLS](https://docs.aws.amazon.com/AmazonCloudFront/latest/DeveloperGuide/cnames-and-https-requirements.html) via my "barrancolab.com" domain, we have to generate a Certificate. AWS Provides two options, one is DNS Validation and the other is manual validation via Email. The former will remain valid as long as the CNAME DNS entry exists. 

So to accomplish the above, I logged into the AWS Console, and followed [this set of instructions](https://docs.aws.amazon.com/acm/latest/userguide/gs-acm-request-public.html#request-public-console) to create a new ACM Certificate for my Cloudflare domain. Next, I performed the [DNS Validation](https://docs.aws.amazon.com/acm/latest/userguide/dns-validation.html) by logging into my Cloudflare dashboard and adding the specified source and target values to my DNS settings as a CNAME. 

**IMPORTANT**
Make sure you are not "proxy"-ing any of the CNAME entries you create! We are simply allowing Cloudflare DNS to point the domain at the Cloudfront endpoint.


#### Cloudfront Configuration
Now that I have a certificate that can be used on Cloudfront, its time to connect all the elements together.

This is the [AWS Developer Guide](https://docs.aws.amazon.com/Route53/latest/DeveloperGuide/getting-started-cloudfront-overview.html) I followed, except I skipped anything dealing with Route53/DNS configuration since that is being handled by Cloudflare. Additionally, I am only hosting the webstie on my root domain, so I also skipped redirecting S3 API requests from root to subdomain (like "wwww"). 


At this point, my site was up and running. All thats left to say is:
```
  ___________
< Hello World >
  -----------
         \   ^__^ 
          \  (oo)\_______
             (__)\       )\/\\
                 ||----w |
                 ||     ||
```


Jared Barranco

Building (and Enforcing) Integration Trust

Snippets, Samples, and Gists

Truecharts Deprecation and TrueNAS SCALE

Race Conditions in Event Streaming Dependent Services

Static Website with S3, Cloudfront, and Cloudflare