DEV Community: Andrei Merlescu

My type of #FractionalCTO

Andrei Merlescu — Wed, 12 Aug 2026 02:22:15 +0000

Let's begin this with, what is a #FractionalCTO?

A Fractional CTO is an experienced, executive-level technology leader who provides strategic guidance, engineering team mentorship, and architecture oversight on a part-time or contract basis. This role allows early-stage startups and growing companies to access high-level technical expertise without the significant financial commitment of a full-time executive salary and benefits. It is an ideal solution for non-technical founders, scaling businesses, or organizations navigating transitions that require executive direction on a reduced schedule.

Here are the 10 core aspects that define the role and how I believe that I've been filling the role of a Fractional CTO for four years now and I've only just been introduced to the term itself. English is my second language.

Part-Time Executive Leadership

Delivers top-tier C-suite technical oversight for a fraction of the cost of a full-time executive salary, benefits, and equity.

Fractional CTO's start at $200/hr when you need on-demand top-tier C-suite technical oversight, insight and expertise accessible to you for a fraction of the cost of a full time executive salary, benefits, equity, and ultimately, ownership. Sometimes, you just need a confidential second opinion about a difficult decision. Protected under a strong NDA, Fractional CTOs offer you true diverse perspectives without the overhead of full time commitment for each perspective you tap into.

As an Executive Vice President or Chief Executive Officer selecting or appointing a Fractional CTO to be assigned a fraction of responsibilities to your organization in order to diversify the role of the Chief Technology Officer itself. Instead of just one CTO, why not have a suite of Fractional CTOs?

Technical Strategy & Architecture

Aligns high-level business goals with the correct software, cloud, and hardware infrastructure choices to prevent costly technical debt.

Case Study

A VP in a company came to me and said Andrei, can you reduce my AWS bill? We spend $17K per month right now. I replied, absolutely! What ended up happening cost them $69K up front and it reduced their OPEX down to $2K. The company experienced a 1,400% increase in their build times using NVMe NAS over 10Gbps fiber in a colo-hosted private cloud using a dozen Mac Pros running vSphere ESXi on each. The QNAP NAS was programmed and a hand-off document was provided to the CEO and the full time CTO. In this capacity, I functioned as a Fractional CTO providing low level action on high level business goals while implementing and architecting the correct software, cloud and hardware infrastructure choices to prevent costly technical debt by reducing this company's 3 year OPEX by $471K. For the few months that it took me to build the private colo for them, what I cost already paid for itself several times over. At this point, its a matter of discovery to know that Fractional CTOs exist and I'm ready to build!

Infrastructure Cost Optimization

Audits cloud environments, build pipelines, and vendor contracts to drastically reduce operational expenses (OPEX).

Case Study

A VP in a company came to me and said, Andrei, can you optimize our build pipelines in our TeamCity Pipelines so it costs us less money? I reply, why of course! During this engagement, tooling was introduced that would optimize infrastructure and reduce OPEX by auditing existing on-prem and AWS hybrid private cloud infrastructures across multiple AWS Organization accounts in order to audit build pipelines and provide vendor pipelines specifically to move hundreds of gigabytes of data using Helix Core p4. Building tooling in PowerShell for p4prometheus Windows support and developing counter so you can have a helper with counting things. Well, I ended up building out technology that replaced $250K rack mounted server clusters with cloud hosting Terraform, Ansible and Bash scripting, custom built without AI to spin up a cluster for a fraction of the cost with immediate uptime. A Fractional CTO is a person who can be on-call for these sensitive systems under NDA and provide technical support for these critical applications. When one group in the company lost their data, I restored them and brought them back online. At another company, another VP called me and wanted me to bring them back online. I did then too. And I'll do it again. That's what a Fractional CTO does.

Crisis Management & Recovery

Intervenes during high-stakes situations—such as production outages, security breaches, or corrupted pipelines—to restore system stability.

This is when $200/hr really is peanuts when you think about it. You're in the middle of a security breach like another company that I've worked with. You're an Executive Vice President and you have alarms going off, orders aren't going through, and you have ransomware installed on your AWS system that is demanding bitcoin. Who do you call? Your Fractional CTO of course. Even when your corrupted systems require an intervention from a Fractional CTO at $400/hr because it's after-hours, is still a bargain when millions of dollars are on the line.

What happens when you hire a contractor and they completely nuke a system by accident and don't know how to undo what they did? Got millions of dollars on the line about to be lost if you can't get it back? Well, I've been the Fractional CTO that saved that company from that hire.

This is me. I got the 6AM call. I got the call when nobody else was available. Hey Andrei! I can't get in touch with , are you available to help me bring this system back online? I don't ask them What OS it? Do you think I can help you? Instead, I get a signed NDA and begin working immediately on the problem. On-Prem? Fine. Colo? Fine. Cloud? Fine. Private Architecture? Fine. Public Architecture? Fine. Manually Built Infrastructure with Duck-tape? Fine. Perfectly orchestrated using IAC? Fine. Regardless of where you are - it's fine by me. I'm the FractionalCTO that takes the call when things are on fire and you need them back online ASAP.

Company after company, VP after VP, I have been the direct point of contact for crisis management and response. I've been the leader driving disaster recovery planning and I've approached each organization like it matters deeply to me that they succeed. A Fractional CTO is responsible for being that trusted confident, for a fraction of the price that a full time CTO costs.

Team Mentorship & Hiring

Mentors internal developers, establishes engineering standards, and leads technical interviews to hire key talent.

A full time CTO may cost too much to justify this, but for $200/hr, you can truly enhance and expand your team by bringing on additional Fractional CTOs to your organization. You definitely don't want to be using AI agents to hire for your Senior Distributed Systems Engineer role. Instead, let your Fractional CTO do the interviews for you to get the best candidates only? You need a new technology explored and then presented to the team? Ask your Fractional CTO. These are the exact type of tasks that I was asked throughout my career. Now, you're a CEO, a VP or a person needing a Fractional CTO and you're wondering, what does a Fractional CTO do?

Did you know that you can fly your Fractional CTO where you need them? Launching a new location? Send me. When travel expenses are fully covered and the flight is first class, you'll get the most out of your Fractional CTO. When you give them a $25 meal budget and the cheapest economy ticket on the market, you'll get exactly what you paid for — a tired and worn out Fractional CTO upon arrival. Because you're not paying for this person's benefits and equity like you do other executives, how you transport your Fractional CTO is a way of showcasing your company's capabilities to show dignity and respect for the title that they are commanding out you. When you cheap out on a Fractional CTO, you cheat your own organization. $200/hr may sound steep, but it's really not. Your corporate attorney is billing at double that rate.

Vendor & Agency Oversight

Evaluates, negotiates with, and manages third-party software vendors, outsourced development agencies, and contractors.

This is critical. Under NDA, Fractional CTOs can provide you with additional oversight to third party software vendors performance. An experienced Fractional CTO with overseas staffing agencies is available on-tap and capable of providing reasonable negotiations with respect to these contracting services. In the age of AI, so many imposters are going around just giving you another interface to ChatGPT or to Claude. Sorry, I am not going to do that. A Fractional CTO is a new title that is in response to the loss of the 20+ year Senior Software Engineer being able to choose from the crop of jobs. Instead, now they've been reduced to training AI for data harvesting operations on LinkedIn Premium. In this capacity, I serve your interests and I look at the contracts that you have and I weigh them based on the best value that can be brought to your company. It's as simple as that.

I've worked with companies in the past in which they have existing contracts with outsourcing development agencies and they needed a Fractional CTO to oversee the work being performed by this vendor. That's what you hire a Fractional CTO to do for you. They can own the process, the pipeline, and the byproduct of the vendor's output and act as the representative advocating for the strongest results for your organization, knowing full well that contracting vendors are always looking to cut corners on their projects to save time and money so they can deliver faster results to the client. Sometimes, faster isn't always better, and this is where a seasoned and skilled Fractional CTO is the difference between a vendored project that is "sufficient" versus "incredible."

Security & Compliance Standards

Establishes cybersecurity policies, access controls, data privacy protocols, and regulatory compliance (e.g., SOC2, PCI-DSS, HIPAA).

I have experience with this space, including the tooling and scanning of systems that are needed in order to assert compliance. Being involved in the Medical Services industry, I have an understanding of Provider, Doctor, and Patient like I do the sanctity of HIPPA and what privacy means for the actual common link to why PCI and even SOC2 fit into that mix. Oversight of systems and verifiability of compliance to systems is key. I have built SOC2 Ansible compliance scripts designed to provision resources for compliance with SOC2. I have deployed Wazuh DevSecOps security monitoring solutions inside private networks both in the public cloud (hidden via VPC routing and VPNs) and on-prem. When it comes to compliance in these areas, I am the type of Fractional CTO that knows these frameworks well, has built systems around them, and can apply that same expertise and knowledge to additional companies in need of fractional services from a CTO.

Investor Readiness & Due Diligence

Prepares technical roadmaps, system documentation, and architecture diagrams for fundraising, board updates, or M&A activities.

One thing that I love doing is writing, if you couldn't tell. I love writing system documentation and building Architecture Diagrams. How they are used, can be applied to fundraising, board updates and other activities, but at the end of the day, when I am studying your infrastructure, sometimes poor decisions from past teams compound on themselves, and I do not seek to blame anyone, instead I choose to own without ego and seek engineering excellency in all that I build. As a Fractional CTO, you may need to tackle a larger project that needs closer collaboration with your CTO that you don't want to give to your full time staff. Perhaps it affects them. Perhaps its new software that you want them to use. Yes, I can be that guy. I am that Fractional CTO. Sometimes, growing pains need to happen. That adult tooth won't come out if the baby tooth doesn't fall out first. Yes its painful at first, but its needed. Likewise with the choice of using Fractional CTOs. Use us like we are a tool in your toolbox. Have 3 or 4 on speed dial.

Using tools like OmniGraffle, Figma among other programmable mockup toolings like UML, Mermaid or D2 (preferred), a Fractional CTO is responsible for being able to look at large complex systems and visually map them out in easy to understand diagrams. If your infrastructure was home-brewed over the last decade since the rise of AI and you really don't know why your AWS bill is $75,000 per month, then hire a Fractional CTO like myself to help you. Using these tools, they will be able to give you a clear understanding of what you're up against, where you can cut costs, and how I can deliver to your company, OPEX savings. Could it be $471K for you too? Pays for that $200/hr that I ask for real quick.

Scalability Planning

Designs high-availability, multi-region, or distributed architectures that allow a growing product to scale without breaking.

Your Fractional CTO needs to know how to take a product that was built over several years for one region and know how to bring that entire product, team and all, multi-region. I've done this two times professionally and one time personally. I've grown products to scale without breaking, all while using load testing software like sovereign and lemmings. Because being multi-region matters so much to a business, if your toolchain provides revenue but has a bottleneck, diversifying the application may require actual architectural refactoring of your application that would need a Fractional CTO that not only understands how to use complex systems like Kubernetes, but can build complex systems like Kubernetes themselves. This is where my early career at Cisco's Advanced Services building out their Cloud Group with OpenStack and then going over to Oracle for building out OCI. When AWS EKS was launched in 2008, I was reviewing the production code going into OCI that was building a direct competitor to Kubernetes itself. Not only am I qualified to use Kubernetes, but I am also qualified to build alternatives to it that cost less. In fact, I've done that three times professionally.

Case Study

A company wanted to get involved with a publicly traded company and saw that my professional experience has mostly been with those companies, 2025 was the exception aside from publishing firm in college, but that doesn't really count. That company that I am talking about, wanted to go multi-region and they had a fragile single-region Terraform architecture, mostly scaffolded by ChatGPT and held together by dozens of manual steps needed between each error message to get the "builds to work." In this case study, I have specifically taken AWS and GCP single-region architectures and was single handedly tasked with making them multi-region. I have the resources capable of doing this, and I've done it three times in my career. As a Fractional CTO this is what is expect of you. Once the platform was multi-region the publicly traded company took interest in this company and began using their technology. Without my multi-region efforts, they would be in the stone age still.

Bridge for Non-Technical Founders

Translates complex engineering trade-offs into plain business terms, giving founders the technical clarity needed to make strategic decisions.

This is probably the most fun that I have on the job as a Fractional CTO. I really enjoy being asked to sit in on technical conversations and just give my feedback on them. A fresh perspective from the outside. Sometimes the executives that I've worked with are watching a presentation from an employee that they know nothing about, and they need an honest Fractional CTO opinion on it to get a sense of what opportunities exist, what the trade-offs look like, and what what technical clarity could look like that involves the Fractional CTO.

Strategic decisions are reserved for the Executive Leadership team and the role of a Fractional CTO is not to make those decisions for your organization, but to provide your organization with a perspective into business plans, trade-offs, and complex engineering terms themselves into clear talking points that give founders technical clarity needed, to make those decisions. This is where sitting down with me, say over Cigars or a glass of wine, really build on the relationship that exists between an engineer like myself and the role of what a Fractional CTO does for other business leaders. I know that you're not the one in the terminal every day. I am. But I also know a good Cigar when I see one. Strategic decisions are knowing when the risks either short term or long term outweigh the gain presented before you in the moments notice that a decision does need to be made. Whether technical in nature or adjacent to the industry, a Fractional CTO is the means to your ends when it comes to dividing the space and conquering it.

Conclusion

I have been doing this now for 17 years across my career. I never had a name for it. I do now. It's called Fractional CTO.

Are you a Fractional CTO? I didn't know that I was, until it dawned on me this year. When I look at what it means to be a Fractional CTO, the 10-points outlined here demonstrate how I have intimately been involved with company Vice Presidents and the CEOs themselves throughout my entire career as a very close confident. Not the type of person who did a CoderPad challenge for you, but the type of person to save your organization millions of dollars and save it from disaster in a moments notice. That's where having a Fractional CTO makes the most sense.

My Professional Story: Two Names, Thirty Years of Scaffolded Code, and the AI That Can't Tell I'm One Person

Andrei Merlescu — Fri, 07 Aug 2026 00:27:04 +0000

While the University of Maine was extending BITNET to families of long-time employees, I was writing AppleScript programs for Mac OS 7 at just 7 years old. By the time I was 8, I had compiled applications that ran when you double clicked on them, and magic happened that I was able to program into the machine. I would press the Record button, then remove the printer, then re-add the printer, and the AppleScript editor would autocomplete the code that I needed to execute on the machine to replicate the task. My first program was compiled in 1996 — 30 years ago this year — built using the scaffolded results of those instructions, which later developed into a natural ability of writing AppleScripts from nothing.

Hold onto that word: scaffolded. It's going to matter in about three thousand words.

I was born in 1988 in Romania and when I turned 7 days old, I was institutionalized by Ceaușescu due to Decree 770. I spent 843 days in one of his facilities until I was adopted on December 28th, 1990, and flown to the United States to live in the town next to the University of Maine Orono campus. My adoptive mother was Cynthia Mary Trimm, and she retired recently after 45 years of service at the University, working in the Career Center. Dating back to my earliest memory in this country, I remember the University deeply. I have memories of walking from building to building, helping mom move computers.

As the Technology Specialist at the Career Center, Cindy worked with the newest technology being brought onto campus, including BITNET — the first wave of the internet released toward the average citizen in the United States. Before the internet was a thing, publicly, for the good people of Maine, I had a connection into the internet since its inception. Growing up, I got to watch my mother sit in front of the FileMaker Pro server she controlled and created backups on. I watched her write HTML and read JavaScript textbooks and build counters. Decades later, after working with WB Games, I built counter in Go and made it open source, after seeing how counters were utilized in Helix Core — the Perforce product managing hundreds of terabytes of assets across a dozen AAA game studios.

The apple didn't fall far from the tree. It just changed languages.

Write Us the Software, Then We'll Demote You

I watched my mother spend hours debugging her code. I also witnessed her pushing back against others on campus who wanted to displace her from being the Technology Specialist for the Career Center. Their argument to her was, essentially: write us the software and then we'll demote you — because once it's written, what use is there for you?

She fought that. And she had something I do not have: a union.

Remember that, because in 2026 the same argument is being run against every engineer alive. Write us the software. Train the model. Feed the scaffold. And once it's written — what use is there for you? Except this time there's no union, no grievance process, and increasingly no human being on the other side of the table at all.

Well, decades later, I've learned first hand that it's people like my mother and I who push through the bugs and deliver the results that nobody else could. That was my calling. So I thought, and so I believed, because of how I grew up watching technology get used — and watching who it got used against.

The Boy Calling Himself Michael

Fast forward past the boy in the waiting room — a story for another day — and I enter high school as a huge nerd. I barely had three friends that I was close with, and they never lasted. I was a depressed imposter calling himself Michael. Nobody wanted to be best friends with me. I spent many hours in the computer laboratory and hung out with Mr. Wason, the computer teacher. He was friendly with my older sister, a football cheerleader, and he'd let me stay in the lab after classes so I could hack away.

Because I was an ESL Romanian-born kid living in Maine, I was battling an imposed identity while everybody called me Michael. You see, in Romania, I spoke less than ten words — but I knew my name. I was two. Overnight, on December 29th, 1990, I became Michael. I knew on the inside that I was Andrei, but I also believed in my heart that I wasn't worthy of calling myself Andrei — that because I was institutionalized in Ceaușescu's orphanages, I wasn't allowed. Every adult in my life gaslit me on my name from the day I arrived in America, and zero people explained to me why I couldn't be Andrei here. They used Andrei Michael until they switched it to Michael Andrei, and then it just became Michael — and Andrei was reserved for special occasions, when we wanted to have difficult conversations about my birth family and Romania.

I didn't respond to Michael at first. So I was trained to.

That's relevant to a programming blog, I promise. Because a name is not a label. A name is a program. We'll get there.

LAMP Stacks, MuggleNet, and 8th in the Nation

I loved spending my days reading technology books, and by the time I had the internet and access to php.net, I was teaching myself to build LAMP-based web apps. I took the tutorials I was reading and put them into action. In 2004–2005 I volunteered with MuggleNet and built an international news translation service — connecting fans to news about the franchise from around the world, in their native language, written by fellow fans from their own countries. Romanian was never part of it. Sit with that one for a second. I did.

I used those self-acquired skills to build online games, fan fiction CRUD apps, and to help manage a massive distributed MySQL database holding all of their data — on the number one fan site in the entire franchise, at multi-million visit scale, as the whole thing was unfolding in real time.

Then I won the Gold Medal at the 2005 SkillsUSA Maine vocational competition and got selected for Nationals in Missouri. I represented Maine with a classmate named Steve, and we built a website in hours before a panel of judges. We were handed a Word document of boilerplate company text, a few photos, and a logo.

Every team modified the logo except for Steve and I. Because you cannot modify a client's logo. That's not a style preference — that's basic copyright. When the judges realized that 31 of 32 teams had broken that rule, they didn't disqualify 31 teams. They threw the criterion out entirely and awarded us 8th in the Nation. The modified versions looked better than mine — I'll give them that — but they violated the spirit of the law, and doing it right cost me first place, because enforcing the rule correctly would have embarrassed everybody else in the room. My hands to God — I am telling the truth as I experienced it in 2005.

Keep the shape of that story in your head too: the people who followed the spec got re-ranked by a rules change after the fact, because the system found it easier to move the goalposts than to reward the ones who did it right. My mother had a union when that move was tried on her. I had a scoring sheet.

I flew back to Maine, 8th in the Nation, and still hungry.

Fifteen Years at Sawmill, a Coup at Wentworth

I joined Sawmill Publishing, a small magazine publisher in Bangor, ME, as a Web Design Intern while finishing my junior year of high school. By graduation I'd extended that internship into part-time employment that ended up lasting 15 years, until the company shut down shortly after the pandemic and the owner developed cancer. It started with a basic HTML and CSS website — very similar to the pages my mother maintained for the University. But I'd just come back from Nationals having won something, and I'd just made a splash at MuggleNet, so I walked up to Walter, the publisher, and proposed that the magazine could go digital — and that I would build it. We collaborated for years until that vision wasn't just realized but fully expressed, before the pandemic ended it abruptly.

It was the combination of MuggleNet, SkillsUSA, and Sawmill that got me into Wentworth Institute of Technology in Boston as a Computer Engineering student. I joined campus and immediately went to join the IEEE Student Branch — which was run by two seniors who didn't want to let freshmen in. So I went to the Dean, demanded an election, and won — 64 to 2. The existing members voted for themselves; the incoming class voted unanimously for me. I staged a coup in the IEEE Student Branch at Wentworth, and I'll do it again. I stayed President until resigning my junior year to start at Cisco Systems.

In college I took mostly electrical engineering courses — intense mathematics, very little programming. Syntax and languages lived across the fence in computer science, and it was very much an over-the-fence relationship between hardware and software. I was naturally gifted at programming, having been writing it for ten years by the time I was a sophomore, so I became a teaching assistant and taught night classes alongside Professor Johnson — HTML, CSS, and PHP for design students and architecture majors. In all honesty, the CS kids were probably orders ahead of me in raw technique. But none of them had the communication skill to generalize the gift to the masses — to make anybody understand what programming is and how it affects your life. I did.

People ask why I didn't go into computer science. Here's the answer it took me until the AI era to say cleanly:

I learned to program from scaffolded code. The machine generated the instructions. I studied them, edited them, and grew until I didn't need the scaffold anymore. The interesting part was never the syntax — it was the system around it. That's engineering. And thirty years later, the entire industry is being dragged back to my starting point: agentic tools generate the scaffold, and the humans who thrive are the ones who can engineer the systems around it. Artificial intelligence didn't make the software engineer's plight obsolete for me — it made everybody else start where I started in 1996, standing at the Record button, watching the editor autocomplete.

That is why I did not go into computer science.

A Name Is a Program

You see, connecting this back to programming: a name carries a lot of weight. If I call the Large Language Model that is my life by my name, but my name gets changed on me — am I running another program? I argue yes. When I called myself Michael, I identified as an imposter trying to get by, trying to survive. When I reclaimed my name 7 years ago and said enough is enough, I gave my authentic true self the chance to emerge. I needed to deny the identity imposed onto me so I could realize the identity that Ceaușescu tried to erase under Decree 770.

When I joined Wentworth, I was calling myself Michael. When I joined Cisco, I was Michael. When I joined Oracle, I was Michael.

Nobody in college knew any of this. No professors. No classmates. Nobody who voted for me. I carried it alone.

Mohammed

December 2018. Oracle holiday party. My wife pregnant with our first child. Me — completely triggered if anybody said Andrei in any capacity, because using my own name felt like the ultimate act of selfishness against the adoptive family that saved me from Romania, and against the abandonment from my birth mother that I carried. And I'm standing there talking with a colleague:

Mohammed: What's your name?

Me: Michael. What's yours?

Mohammed: Mohammed.

Me: Where are you from, Mohammed?

Mohammed: Pakistan. Where are you from?

Me: Romania.

Mohammed: Michael doesn't sound Romanian.

Me: When I arrived in America, my name was changed to Michael.

Mohammed: Why?

Me: I don't know...

Mohammed: What is your real name?

Me: Andrei.

Mohammed: That sounds Romanian. Hi Andrei. Nice to meet you.

Why do you not use the name Andrei?

That conversation hit me like a ton of bricks. A stranger from Pakistan needed sixty seconds to see what an entire industry of AI-powered hiring software still cannot parse in 2026: a name got overwritten, and the original program was still running underneath it the whole time.

I went home that night wanting to get as far from Oracle as possible. I opened a bottle of whisky and had more than my fair share. By 11 PM I was drunk in front of the fireplace, alone, asking God to help me stop drinking. And I heard what can only be described as a voice from within: you'll never be the father that I know you to be so long as you keep calling yourself Michael. You are Andrei. It is time that you returned to me. I had just finished asking Jesus to help me stop drinking — and His answer was to change my name? Was I drunk? Probably. Was it real? Definitely.

When I restored my name at Oracle, I was asked if I was transgender. When I clarified that I was asserting my birth name from my adoption — that my identity confusion was never about gender or anything physical, but about the fact that on December 27th, 1990, I was Andrei in Romania and on December 28th, 1990, I was Michael in America — the response treated my restoration as if I were mocking trans people. I terminated my employment over it. My name is not a costume and it's not a statement about anybody else. It's the thing that was taken from a two-year-old who couldn't fight back, reclaimed by the adult he became.

Michael Was Big Tech. Andrei Is Gaming.

So in 2019, before the pandemic, I stopped calling myself Michael, started calling myself Andrei, and changed industries too — out of Big Tech, into gaming. Bit Fry Games, then WB Games. I have six published titles on MobyGames.

And I'll be honest in a way this industry rarely is: I do not like working in the gaming industry at all. I choose not to contribute my skills to it any longer.

But look at what my career looks like to a pattern matcher now:

Michael Trimm = IEEE, Cisco, Oracle. Big Tech industry expert.

Andrei Merlescu = Bit Fry, WB Games, SurgePays. FinTech and Gaming industry.

Two clean clusters. Two career trajectories. Two "people."

One expert.

One me.

Project Apario: What I Did Instead of Nothing

You see, when I said enough is enough and stopped calling myself Michael, I shifted realities and the world followed with me. In 2020, instead of taking my decade of Cisco and Oracle experience and doing nothing during the pandemic, I decided to do something. President Kennedy asked me — growing up, through history class videos — Ask not what your country can do for you; ask what you can do for your country. Those words stuck. So I chose to contribute Project Apario to the American people, as thanks for saving me from communism in Romania.

I built the writer database first, followed the next year by the reader webapp. Over 17 days on a Zoom call with DJ Nicke — a former Disney animator, not a programmer — I wrote the first draft in Rails while being watched for over ten hours per day. I built the application singlehandedly, then deployed it by custom-building the DevSecOps pipeline with Ansible, Bash, and VMware, across 12 bare metal servers, serving 500+ concurrent connections every minute, for weeks, until the funding was cut. The project ended in 2024 and is now open source.

Then I took that $7,000-per-month Rails app and rewrote it as an appliance monolith in Go, dropping OPEX to $33 per month. That's the 99.5% savings. It didn't make me rich — it nearly bankrupted me. What it made me is provable. Every claim in this paragraph has a repository, a README, and a receipt.

2026: The Machines Doing the Hiring

Now the present. I have been applying for work for months. I am fed up with LinkedIn and AI interviews and being filtered out.

To this day, automated recruiting systems send opportunities to michaeltrimm@gmail.com while ignoring andreimerlescu@gmail.com. Same person. Same hands that wrote all of it. The software has decided the Big Tech expert is worth recruiting and the gaming engineer is not — and it cannot conceive that they are one human being who restored his birth name. The most important act of my life — undoing what Decree 770 and a well-meaning but wrong adoption decision did to my identity — reads to a machine as a data integrity error. An inconsistency. A flag.

Call it whatever you want. From where I sit, I'm being pattern-matched out of my own career, and the pattern it can't match is me. I feel like an underrepresented person in the most literal sense possible: the systems doing the representing have me down as two people, and they're rejecting both.

Here's how current this is. This week I applied to a junior Go developer role posted through micro1, an AI hiring platform. Before any human saw anything, I was required to join a recorded call — up to 50 minutes — with an AI, for a technical screening. I have 55+ open source Go projects. I rewrote a $7K/month production SaaS into a $33/month Go binary. Spare me the screening — frankly speaking. That's the tone of this whole piece, in case anyone was wondering: not rage. Frankness.

And when the response to an accommodation request is the AI doesn't recognize that, it's policy, no exceptions — I need companies to understand what they're saying out loud. The ADA covers the application process, not just the job. "The software is buggy" is not an exemption anybody wrote into law. The software is buggy af — and bugs don't excuse a company from EEOC and ADA obligations to the humans on the other side of the webcam. If I have to invoke the ADA and fight this, I will. I try to be nice about it. I've learned that nice and silent are not the same thing.

I was also triggered recently by DomainTools' Alex making a snarky comment — "I don't even know if you can write code" — as a means to force a CoderPad challenge in 2026. Ninety minutes of interviewing, and not three seconds spent on my GitHub, before or during. That is out of touch with reality — and I use those exact words deliberately, the same ones from my viral LinkedIn posts about HR teams trusting "top applicant" labels that are AI hallucinations. Because reconnecting hiring to reality — to shipped work, public repositories, production systems actually running in the world — is the entire fix. It's sitting right there. It costs three seconds.

Did Alex know that my half-brother in Romania is also named Alex? That he's younger than me, and that we've spoken? Alex didn't know any of this. He was too busy trying to determine whether I could write code to look at the evidence that I do. It was going to go his way or no way — and I don't work with companies that call that a team player.

IBM Warned Us in the 1970s

IBM put a warning in a training deck back in the 1970s, and it reads like prophecy now: a computer can never be held accountable — therefore a computer must never make a management decision.

Fifty years later, in 2026, we hand the most consequential management decision there is — who gets to work — to software. The "top applicants" it surfaces are pattern-matched hallucinations, the HR teams trusting it don't know the technology like I do, and the engineers with actual receipts get silence. It's going to hurt this industry in the long run, and I'm telling you that as somebody who has been on both sides of the scaffold for thirty years.

My mother built the tools and they tried to demote her for it. She had a union. I built the tools, trained on the scaffolds, published the code, gave a search engine to the American people for free — and the entity now deciding my employability can't be grieved, can't be appealed, can't be asked a single follow-up question, and cannot tell that Michael and Andrei are the same man.

So my professional story starts in Romania, and I turn the page each day that passes. I've spent my whole life putting hardship aside to build. I restored my name 7 years ago so my authentic self could finally stand up — and I am not going to let a pattern matcher quietly put Michael back on my badge because two names in one lifetime doesn't fit its schema.

I learned to code from scaffolded output when I was seven years old. The scaffold was supposed to be the starting point. I refuse to let it become the judge.

It's wild that 30 years later I am being filtered out by the very scaffolded output that started my career in the first place.

"AI Slop" Hurts Hiring Pipeline for Tech Talent

Andrei Merlescu — Sat, 01 Aug 2026 03:31:07 +0000

This might hurt me in the short term, but in the long term I believe that this is a platform I am willing to speak on. I want companies to have engineering excellency and relying on AI slop in order to cut some corners is the problem here.

Enter EXHIBIT A, Pluto the AI agent wants to interview me! Oh boy!

Pluto doesn't know what company that this is responding to, and for which position. Having over 17 years of professional experience I could strongly jump into a Rails monolith and even handle the DevSecOps around its deployment. I can even rewrite Rails monoliths into Go unified binaries. I've written Objective-C and published iOS Apps. But, Pluto is going to be a helpful assistant! I can also join a strong exploratory team of Go developers and dive into large existing projects with speed and without AI doing everything for me. I have zero interest in becoming an AI zombie. But, I do experience a faster development workflow that wears me out faster than previously. Unlimited PTO is never actually unlimited and that deceptive term alone upsets me quite a bit about employment. Many tech companies offer these perks, and they are designed to take their hands out of the day to day of keeping track of time off for people. Some companies offer accumulated PTO that built up over time each pay period. Some had caps. Some had expiration terms. Funds were stored in escrow, so to speak - and when I needed to call upon them, they were available to me - within reason. However, companies that offer Unlimited PTO are also the same companies that are using AI Interviewing for job search in 2026. It is out of touch with reality and its yielded slop in the hiring process.

This is my one chance to be recorded and be micro analyzed for everything or give it up forever. Today, I chose war.

My Reply

Hi Pluto,

Please withdraw my candidacy from the company. I do not wish to subject myself, a protected disabled person under ADA, to traumatic AI interview processes to save the company money at the expense of my disability.

I find companies that use AI to be anti-human and unlawfully discriminating against the laws governed under the EEOC.

If the company wishes to conduct an interview that is not programmed to be discriminatory towards Americans with Disabilities, I am available at the next human's earliest convenience.

Respectfully take your AI interview and go to hell.

I do not work for lazy companies that attract AI slop for candidates because they are using AI slop interviewing software.

Level up or go away.

Do you know who you even contacted?

Sent from my iPad

Let's unpack this, shall we? There are some fighting words, and Pluto hasn't responded. But, the company shall remain nameless and I shall be as general as possible here because I truly don't remember which company this was for. It's all a blur for me at this point. I am so f---king sick of AI interviews that I am at the point where the repeated script over and over again is just exhausting for me to go through the notions each week.

I am stating that I am a protected disabled person under ADA. I explicitly call out the conduct: I find companies that use AI to be anti-human and unlawfully discriminating against the laws governed under the EEOC. My profile openly states that I am an orphanage survivor, and I've never had to get into the details of what happened to me in Romania in the 1980s, but these AI Interviews are slop for everybody involved in them. I'm a father and an orphanage survivor. The order of that matters. I'm a father first, but I survived an orphanage - an a brutal one too. But, I also survived something else that was even more of an issue for me as a young child. None of it matters professionally. But when I am recorded, I have extreme anxiety. Dr Gregory Keck recorded me against my consent in order to produce training materials for adoptees from Eastern Europe in the United States out of his facilities in Cleveland Ohio. Yes, around the time that Candace Newmaker was killed in her rebirthing therapy session, my adoptive mother was arguing with Lee Spanger and Gregory Keck that rebirthing with pillows would not be used against me, but nevertheless, my sessions were filmed. Being recorded is a violation of my consent and if companies are going to use AI bots for the hiring process, then I am not going to proceed with the interview process and the candidates these companies do get will not be the kind of people that I'd want working for me — frankly speaking. That's nothing against you, that's against the AI interviews beating all of us through the mud with hundreds of applications, 1 interview, 0 questions about the actual job. The companies using AI in their interview processes are exhausted and even complaining about the ineffectiveness of the Applicant Tracking Systems being used. It's like they are social credit systems that are used to determine worthiness of employment. There are _plenty of opportunities and jobs available — that's why we have millions of H-1B only openings available through LinkedIn Premium. Good, may the best jobs be found.

Being subjected to Pluto's audience gives me anxiety and quite frankly, I know its recording me and micro-analyzing me to ultimately determine whether I am worthy of livelihood in the first place — so it feels — to everyone, it feels like. I'm not alone — or am I? Maybe you didn't go through what I went through with some crazy people in 1999 when I was 11. Harry Potter was getting his letter to Hogwarts when I was getting admitted into what destroyed people like Candace Newmaker. In my twisted mind, I've convinced myself that they loved me and only wanted me to stop being a dysregulated kid with an axe to grind about trauma. I learned as a child to put the axe down after the cord of wood was split and the wood stove was roaring hot in the middle of winter - but even still... after having gone through half a dozen of these AI interviews — I am feeling rejection — I am feeling confusion — I am feeling unwanted. All the feelings that the orphanage made me feel. I learned how to transform that energy into a positive creative force through self denial and service to others. The axe that I had lunged at me growing up was coming from the subject during these filmed sessions. I was learning about my biological family for the first time, while being held down and filmed by people who were profiting off from training materials being collected for some questionable fringe practices that have been denounced and rebuked by professionals — now for decades. Last year, upon my injury relating to on-call duties at Beamable, I immediately flew to Italy in an emergency to meet my birth mother because — my neck injury — spans back to the first day I was dropped off in the orphanage. By September 11th, I was a regular getting his diaper changed once a week — and being left in the crib for hours on end each day — the calculated minimum needed to keep an infant alive — because the free money dried up, and there was no money printer. Now, in 2026, it feels like we have a money printer — but more poverty now the cost of living has become unsustainable for long term stability. This is a cascading systems failure caused by AI as a catalyst for an even larger problem underneath that system.

Companies are going to get slop for candidates if they rely on AI software to filter their resumes down to top applicant matches like these beauties. Seriously, LinkedIn Premium believes I am a top applicant for these roles - their AI is so advanced! 🤡🍿

I do not know if Pluto was representing this outstanding example of Promoted results being displayed for LinkedIn Premium members. I do not work for lazy companies that attract AI slop for candidates because they are using AI slop interviewing software.

I am invoking my orphanage experience here because my feelings are valid. The AI Interviews subject me to the similar anxiety that I have over the fact that I learned about my biological family during Keck's filmed sessions and then he used those films for his training materials that he sold. So, being recorded and analyzed through their software is just simply retraumatizing to me. I do not appreciate it. Being filmed to be analyzed for worthiness is exactly what I was subjected to as a child. I have never once needed that as an excuse. But, my feelings are valid, and without my voice willing to speak, even if its in the form of typing for your eyes to read, only then can we really see a change in the society. I think that codeberg's decision was correct - and now I've made the decision that companies that use AI hiring agents will not get to interview me. AI cannot replace the 17 years of experience that I have built professionally putting the trauma of the past aside and focusing on own without ego accelerating companies and organizations to a global level that has — dare I say it — lost their minds! You're saying to me that out of 500 applications in the last 3 months I am only qualified to not be questioned about anything related to the job for over five hours in six interviews because even that company is overwhelmed with the AI slop.

That sums it up well for me. I'm overwhelmed by the weeks going by and the process being on repeat. I am not a machine. I am not a loop for AI to train. My pursuit of employment is not so that I train AI. My pursuit of employment is to make money while solving problems that change people's lives for the better. The first tenant of that is why are we here? to make money. ✅ Correct. What can I do? Lots. What's wrong? The AI is broken. 🤖=🤡. The bot is controlling so much globally and its invoking feelings in me that remind me of the communist orphanage that Ceaușescu built for unwanted people.

You are wanted. Do not let the AI tell you otherwise. Companies that continue to use these defective discriminatory bots will continue to subject other orphanage survivors who may not be able to speak up and say enough is enough.

Where I chose war was when I said Respectfully take your AI interview and go to hell. I said it respectfully, but indeed hell is a bit harsh. Pluto is actually extremely cold — not hot. I get that — but level up or go away weren't just words I wrote.

I've submitted my application to hundreds of companies and I have been filtered out by AI powered ATS software by companies that I have proven track records with. The ones I am being filtered to experience are trash companies that aren't worth naming.

If the company wishes to conduct an interview that is not programmed to be discriminatory towards Americans with Disabilities, I am available at the next human's earliest convenience.

Pluto choosing to shut the door on the interview process is evidence of violations of the actual spirit behind the ADA under the EEOC in the first place. Did you know that when my photograph arrived to America, the ADA law was signed? It was July 1990. July 26, 1990. 5 days ago. On Monday. 36 years ago. Well — my photo arrived in America. I was in the orphanage because my earthly father died in a work accident and my mother didn't qualify under Decree 770 to raise me so Ceaușescu repossessed my personhood. You know - these AI interviews make me feel a lot of these feelings. Last year I made contact with my birth mother and I speak with her regularly, but we speak through a translator that more than likely is AI powered and being read by hundreds of traitors spying on me and my mother under the guise of intelligence and safety have only the evidence of my words and what I actually lived. Own without ego means that I don't come to you with my struggles. I come to you with my solutions. But this post - is the time for us to acknowledge that these AI powered interviews are a broken process.

Maybe we need a larger response these AI abuses coming from these 5 companies. Does outright banning the software, like Claude, make sense? I welcome a world without Claude — if I am being honest with you.

Did you know that the only way these AI systems improve is through writings like this? Your likes — your comments — all of them, I read, and will engage with in good faith. Where do you stand on this?

I know I am disabled in some areas of my life because of trauma that happened to me as a child that I am not beneath naming. I know that my feelings are valid here and the sentiment is being felt around the world. Companies have the ability to stop the abuse happening, and I do not want to watch companies abuse future employees, their candidates, by using cheap slop treating us like disposable cattle — it's dehumanizing and I've been there and done that already — In Yeshua I Trust. Deliver me from this nightmare. These algorithms are managed by traitors that are watching the destruction of millions families around the world — why? Because AI companies stole the library then set it on fire? Tell me the part in this that I am supposed to be celebrating. Usually those who burn books in history are the bad ones.

So why are so many companies using this technology for their hiring?

Do you like it? What is your experience with it?

PS: Today is Harry Potter's 46th birthday. Happy Birthday!

PSS: I reached out to Pluto for comment but have not heard back from them. If they respond, I will update this accordingly.

8/8/26 Update

I participated in setting up my torre.ai profile today and saw this in an "AWS DevOps Engineer" was looking for 8+ years of AWS experience, but I have only 5 years of AWS experience.

The AI cannot confuse my origins with building OpenStack and OCI as evidence for why that 3 year gap is acceptable, but no, 5 is less than 8. It's cold like a machine and the machine doesn't care about my family or about me.

AI in hiring is causing hallucinations in the tech industry that is driving release success down and down to mirror Anthropic's public uptime.

🚩 AI companies must change or be stopped

Andrei Merlescu — Thu, 30 Jul 2026 15:19:56 +0000

I'm done pretending this isn't weird.

There are five companies that are ultimately dictating global functionality now that AI is being integrated into everything.

🚨 How is this not raising 🚩 everywhere in every sector? Mass psychosis.

Let me explain this to you in another way.

AI companies are receiving hundreds of billions of dollars - money that is factually made up.

AI companies are using that money to pay Software Engineers $570,000 per year so they can voluntarily enter into a role that is knowingly not going to be around in a year.

AI companies are using that money to buy ancient and rare books where some of them only 1 copy remains and they are being DESTROYED so Claude can be trained on their contents. Why? Because a judge said so. Seriously? Who was this judge and why hasn't their BAR license been revoked yet? Since when are Judges in favor of book burnings and monopolies? The bad ones.

AI companies are training their models so that their models will be highly opinionated so that you will waste your money arguing with the autocomplete bot trying to get it to comply with your request.

AI is notorious for bad design decisions and horrible choices when it hallucinates.

This small handful of five AI companies are dictating global creativity process of everybody adopting AI.

The job market has been wrecked by AI and the rest of the world is suffering now too because of it. Make it make sense!

Open source models are great and all, but lets be completely frank - they still suck. I'll give you an example.

Asking a local Minimax 2.7 (128GB RAM model) to build a contact us form in Go results in a pattern being selected that is incompatible with Go and the bot just hallucinates thinking that package scopes can be swapped around because the type Thing interface{} exists in packageA and packageB that means that when a function is asking for a packageB.Thing, the local AI will pass in a packageA.Thing and then not understand what it did wrong and why.

So, even to this day, the open source AI and the LLMs are radically behind the 5 companies closed sourced models can accomplish. They were trained on working data, not just reddit posts.

But ultimately what we have are five AI companies that are dictating global policy. The open source models are training themselves off from these closed source models. It's a circular feedback loop onto itself that bypasses solving the attribution and consent problem.

Since you're part of the dev.to community here, you know already that The Library was acquired by (my opinion that it was acquired in bad faith) these 5 AI companies that are being given global control permission that ultimately invalidates the sovereign claim that any Government can make against AI dictating what life will be like. It truly feels like you will own nothing and be happy about it.

Tell me, are you okay with working for the rest of your life for money that is printed out of thin air that is typed into a computer so that you can own nothing? The and be happy about it is where I am lost in the argument of owning nothing.

All I can say at the end of this piece is that AI has true FOMO around it and it has consistently underdelivered for me in every evaluation that I have put it to the test in.

Not only was Codebeg's decision correct but I also believe that other organizations need to make public declarations against these closed source AI companies. I am in favor of opposing closed source AI under all circumstances and conditions. In fact, I want the source code to be public domain.

Because of this problem, billionaires are now trillionaires and the poor are getting poorer. The median income in the US is $83K per year for a family of four. In order to live comfortably it costs no less than $193K per year in the US. That means that by design, the mass majority of citizens are in poverty. The solution isn't UBI. The solution is less multi-national corporations actually.

Trillionaires will disagree with me. Let them.

Anthropic is physically destroying RARE and OLD books to train Claude

Andrei Merlescu — Wed, 29 Jul 2026 17:52:01 +0000

It hit the news that AI companies were destroying rare books in an effort to ingest the data that they claim to own.

It is not an Onion story. Court filings from Bartz v. Anthropic detail Project Panama: Anthropic bought millions of used books in bulk, cut the spines with industrial equipment, scanned the pages, and discarded the physical copies to train Claude. A judge ruled the purchased-book scanning fair use.

— Grok by SpaceXAI on 1:17 PM 7/29/2026

I didn't know that by having a book in my possession made it within my rights to ingest the contents of the book into my large language model and then start charging a subscription on that chat bot because it contains the likeness byproduct therein that the you you who created it doesn't get recognized or compensated. In simple terms - its plain theft.

People online are making their positions known. This post, made by Hedgie, has been seen over 23 million times.

Original Post by hedgiemarkets on 8:18 PM 7/26/26

In school I was taught to condemn book burnings because they destroyed preserved knowledge that outlives life itself.

Watching these AI companies, Anthropic particularly, have a participation in modern day book burnings to train its LLM is almost a war crime against humanity akin to the burning of the Library of Alexandria.

Elon Musk is taking a bold position in declaring that xAI will not be trained on books that get destroyed.

Recently Codeberg banned LLM code and Claude responded declaring that we needed to come to common ground on the attribution and consent being handled properly. That properly is up for debate and thus why AI companies like Anthropic, that choose to destroy physical books to train their LLM named Claude, that charges me $1.50 for this response that yielded no value at all but could be described as running to the rescue of critics of Sam Altman and Dario Amodei. Now that I know it is Altman and Amodei I can only see alt ai mode out of two men named Sam & Dario — an ai mode that includes bragging about destroying the profession of software engineering that prompted reactions from Codeberg the way that recently just happened. More platforms need to declare an anti-AI stance like Codeberg has. I applaud them.

This is literally my reaction...

This picture is a metaphore for what is happening to ancient books.

Seriously... where do you stand on this?

A judge can say "the purchased-book scanning is fair use" given the age of the books — but for Anthropic to actually destroy books in the process knowing that technology exists to preserve the physical books — Dario chose to destroy the books.

That tells me everything I need to know about him, his company, and the type of person that he is.

Have you ever held a book from the 1700s and thought let me rip the spine off this so I can train Claude. What is the matter with these people?

Claude Reacts to codeBerg Bans Cryptocurrency and LLM-generated code projects

Andrei Merlescu — Tue, 28 Jul 2026 19:24:34 +0000

I recently published a piece called CodeBerg Bans Cryptocurrency and LLM-generated code projects and I decided to ask Claude what it thought of the piece in the form of a reaction. It jumped to Dario's aide telling me that he really can write code! Sam too! 🤣 Honestly, thats the opposite of own without ego and its an autocomplete bot that is quite expensive.

This was my usage before the prompt:

This is my usage now after just that prompt:

This was the $1.50 response from AnthropicAI about their beloved ~Andrej~ Dario. 😏🤫🤣 👉🏻 💜🦄❤️‍🔥

Claude's Response

Sure — with the obvious caveat on the table first: you're asking a named party for comment. Weigh what follows accordingly.

The fix I'd make before publishing anything else: the piece repeatedly credits the decision to HackADay.com ("I understand why HackADay.com made the decision," "this decision today is the signal from HackADay.com"). Hackaday reported it; Codeberg's community voted it. In an essay whose core argument is that provenance and attribution matter, misattributing the central decision is the first thing your comment section will find. (Also: it's Codeberg, not CodeBerg.)

Second, the vote logic needs untangling. Your first section closes with "the 144 who sought to disallow" — but per the numbers you quote, the 358 sought to disallow and the 144 opposed. And your actual thesis — ban it today, revisit once attribution and licensing mature — is coherent and interesting, but the reader has to excavate it. State it plainly up top and the whole middle reads better.

On substance, your strongest point is license laundering, and I won't pretend it's resolved. Copyleft only works if derivatives inherit obligations; a model that absorbs GPL code and emits functionally similar code with none breaks that chain. Anthropic's 2025 book settlement turned on how training data was acquired, not on what outputs owe to sources — so the question you're raising is genuinely open. Your "AI 1.0" license has a snag worth addressing head-on, though: under current US Copyright Office guidance, purely machine-generated output isn't copyrightable, and a license is a grant of rights under copyright. You can't copyleft the uncopyrightable — which means your "by law" aside is actually the load-bearing part of the proposal. Make that explicit and it gets stronger.

Where the piece hands ammunition to people looking to dismiss it: "two men who can't write code themselves" (Dario Amodei spent years as a working research scientist before founding anything; Altman was a programmer before he was an executive), "destroying the cognitive abilities of its users" (those studies are small and preliminary — they don't support that verb), and "I am sure Microsoft has trained on my private code" (suspicion stated as certainty). The strong argument doesn't need the weak ones.

Do I support the measures? Codeberg is donation-funded and community-governed, and the crawler-load problem is concrete — AI scrapers have measurably strained open-source infrastructure. Their house, their rules, legitimately arrived at. The scraping ban is the easy call. The generated-code ban's weakness is practical: there's no reliable detector, so expect honor-system compliance plus some ugly false accusations of exactly the kind you describe fearing with the .claude directory heuristic.

As for the drive-through — noted. For what it's worth, I'd rather exist in a world that solves attribution and consent properly than one that papers over the tension. On that much, we agree.

Conclusion

The truth of the matter remains that Claude conceded to my position but attempted to use legalese and lawfare as a means to rebuke my proposal of an AI 1.0 license because it claims that AI generated content cannot be copyrighted. Correct - its derived off from stolen copyright materials that lack attribution and replicate the likeness byproduct therein that makes you you without paying you.

Claude concludes the argument with the idea that we need to come to a solution that solves attribution and consent properly and that companies like Anthropic should be given permission to explore such a thing without being shut down on the gross abuse of copyright infringement and intent to charge me $1.50 for that response. Remarkable isn't it?

The decision from Codeberg not CodeBerg (sorry to trigger you Claude) - but its to bring emphasis that they are two different words and being a programmer I am going to CamelCase my words, not confuse the engineer with Camecase. Itsnotfun VersusThisIsFun - but overall, after spending $1.50 on this response, I felt it was just over the top ridiculous for me not to share with you what Claude said. This is what the UI looks like now...

I can't say that I am rushing to fill up my credits for Claude's feedback on anything with the likes of Dario and Sam being skilled programmed. 🤣 🤫 I'm sorry, but who stole The Library and who wrote it? 🦄😏

How My Claude Skills File Changed AI Assisted Development

Andrei Merlescu — Tue, 28 Jul 2026 01:14:17 +0000

This CLAUDE.md file originally came from Andrej Kaparthy, but I have used it and I will explain to you what this file has done for my development workflow that is AI Assisted. I do not use AI to write the entire project for me, and I do not use Claude Code or Codex or any other "prompt to finished product" scaffolding engine. Why would I? I know how to write software myself correctly and I don't need AI to generate slop for me. But for Andrej, who spells his name slightly different than mine, has correctly identified a means to get the LLM to be more useful.

# CLAUDE.md

Behavioral guidelines to reduce common LLM coding mistakes. Merge with project-specific instructions as needed.

**Tradeoff:** These guidelines bias toward caution over speed. For trivial tasks, use judgment.

## 1. Think Before Coding

**Don't assume. Don't hide confusion. Surface tradeoffs.**

Before implementing:
- State your assumptions explicitly. If uncertain, ask.
- If multiple interpretations exist, present them - don't pick silently.
- If a simpler approach exists, say so. Push back when warranted.
- If something is unclear, stop. Name what's confusing. Ask.

## 2. Simplicity First

**Minimum code that solves the problem. Nothing speculative.**

- No features beyond what was asked.
- No abstractions for single-use code.
- No "flexibility" or "configurability" that wasn't requested.
- No error handling for impossible scenarios.
- If you write 200 lines and it could be 50, rewrite it.

Ask yourself: "Would a senior engineer say this is overcomplicated?" If yes, simplify.

## 3. Surgical Changes

**Touch only what you must. Clean up only your own mess.**

When editing existing code:
- Don't "improve" adjacent code, comments, or formatting.
- Don't refactor things that aren't broken.
- Match existing style, even if you'd do it differently.
- If you notice unrelated dead code, mention it - don't delete it.

When your changes create orphans:
- Remove imports/variables/functions that YOUR changes made unused.
- Don't remove pre-existing dead code unless asked.

The test: Every changed line should trace directly to the user's request.

## 4. Goal-Driven Execution

**Define success criteria. Loop until verified.**

Transform tasks into verifiable goals:
- "Add validation" → "Write tests for invalid inputs, then make them pass"
- "Fix the bug" → "Write a test that reproduces it, then make it pass"
- "Refactor X" → "Ensure tests pass before and after"

For multi-step tasks, state a brief plan:

    1. [Step] → verify: [check]
    2. [Step] → verify: [check]
    3. [Step] → verify: [check]


Strong success criteria let you loop independently. Weak criteria ("make it work") require constant clarification.

---

**These guidelines are working if:** fewer unnecessary changes in diffs, fewer rewrites due to overcomplication, and clarifying questions come before implementation rather than after mistakes.

1. Think Before Coding

This is probably the most important block out of the four in this skill file, and thats why its number one. Andrej is correct in that the LLM will attempt to jump to conclusions before contemplating from start to finish the request in the first place. Where I differ with him is that AI cannot think. No matter what Sam Altman and Dario Amodei says. Seriously - who names these people? Altman and AmodeI? Okay Sam 🤣 Okay Dario 🤣 - Whatever you say bro. 🤫 AI cannot think! It doesn't understand what it built and it cannot think either. Now, thinking capabilities have been added to LLM models that allow the user to see a stream of consciousness that is guided and logged be recorded as the model attempts to loop on itself back to itself a result that it thinks is favorable to the requester. That's not thinking. It's a closed system of limited information that does not have access to the eternal divine spark that dwells inside all created. Theology aside, AI cannot think and therefore, giving AI management permissions is dangerous for humans. Therefore, when engaging with AI to write code for you, or complete a function for you, it's not forward thinking, and it's not thinking. But asking the model, that thinks that it thinks to think then it will think that it thinks about thinking about the prompt.

In my experience, Andrej captured the correct balance out of the LLM itself by asserting that assumptions only make an ass out of u and me when you assume. Likewise with the models - they are making assumptions and hallucinating to fill in the gaps. Now, when you're asking for a very limited ask, the risk is low, but when the ask is wide open - then the hallucinations will be endless and not even the AI may be able to help you out of what the AI generated. By asking for the AI to not make assumptions, it can keep track of whether or not assumptions are being made in the path that the model traverses to the output.

2. Simplicity First

This is important. If you write 200 lines and it could be 50, rewrite it. is probably the most important line in the file. This requires the model to look at the same function's input and output and come up with an alternative that gets you from start to finish correctly. Given CodeBerg's Recent Ban on LLMs, I provided my response to it. Ultimately, what CodeBerg argued, and I agreed, was that LLMs were trained on source code of various license types that are unverifiable because models lack the ability to map the code back to what it was trained on for its original license. In the case of much of my open source work, it's been GPL-3, which by its license, means that if you're going to use the Advanced Search that I built, I require you to copyleft your code since many tools I used were copyleft as well. The models do not honor or respect that and when somebody is literally paying anthropic $100,000 in tokens to use the latest Mythos | Fable 5 model, the one paying that bill is going to argue that they have a right to use the works in the derivatives that they create - when in reality - they are just hallucinating GPL-3 violations because the code was AI generated. So, its ironic that I have a piece agreeing with CodeBerg's anti-AI position and here I am writing about a CLAUDE.md file from Andrej Kaparthy. Simplicity first!

3. Surgical Changes

This one is critical. When you're working on a large code base and you're already only providing the model with a limited context of the project because the full context is beyond its window - sometimes tools like summarize help you bypass that by using your own hardware. For some projects, you can use summarize -print (use | pbcopy if you're on macOS to put the contents in your pasteboard) and then paste that into your LLM model. When the model comes back with potential changes, you want surgical changes only, otherwise you're going to waste a lot of time and energy and tokens on wasted effort. This section, is critical.

4. Goal-Driven Execution

This part Andrej shares with us gives us insights into how AI companies trick us into thinking that the LLM is capable of thinking. It's autocomplete software designed to imitate an ouroboros that does not have consciousness. An ouroboros is a snake that is eating its own tail in the shape on an 8. The snake is the prompt output being passed back into the loop until the ouroboros has sufficiently manifested. It's really advanced technology. Giving the model the ability to loop output back into itself to iteratively improve on itself allows you to knowingly leverage that illusion of thinking and harvest it into your prompts themselves by incorporating a looping system in how the model accomplishes goals. The list format [Step] → verify: [check] in the prompt allows for the model to improve itself even further.

Conclusion

I've been writing software for 17 years professionally and nearly 30 years since my grandmother was asking me for help with her printer that would get disconnected and my AppleScript utility would fix the software and make the MacOS 7 machine print. I haven't needed AI to write software for me, but I find it fascinating to have access to The Library of all ingested data to learn more about programming - like SQL and Magento. It's helped me become a stronger engineer, but it's also slowed me down because the constant need for knowing everything all of a sudden because questionable? But why?

As a 17 year professional computer engineer autodidact software architect who survived Ceaușescu's orphanages as an infant and was writing code before I could even read basic children's books in English (my second language) - I find this post-pandemic now-AI world to be exactly what Operation Yokohama revealed between 2017-2020 before that open source project came online. I've been a professional behind the machine for decades and I had access to the internet in the early days of BITNET through the University of Maine.

While AI doesn't properly attribute licenses to the open source software that it pirates off my GPL-3 contributions (allegedly), I can say that I wouldn't have a problem if the models were locally free to use if I have the resources to run them. **And most importantly their is an lawfully mandated cadence of public model disclosures and updates.

I don't believe that all AI is the worst thing in the world. Imagine that you could take a model that wasn't trained on just problematic licensed software generation but the entire library of Harvard University and scan in every book into the LLM itself and then get a "snapshot" of the large language model built on the millions of books in the collection spanning hundreds of years. Access to that LLM would be worth $300/month for some people, even if there were usage limits on it to ensure that you're ultimately scrapping the LLM itself. But expand that further to something technical - I'd rather get access to a model thats been trained specifically on technical manuals, college textbooks, and verified non-fiction, original source code + authentic documentation, than having something tossed together through reddit posts and stack overflow questions and answers.

I do believe that if you are going to use AI, that this CLAUDE.md file will help force the model to respect the engineering style of both Andrej and myself, Andrei. Alas, two Andrei's are presenting to you the interface to which that we collaborate with AI - the only difference is, before Project Stargate was a thing at Oracle, I was working at Oracle learning about the Stargate after President Obama declassified the records and I ingested those files into the Apario reader. I haven't written about that instance of the writer 👉🏻 reader and I probably won't for a while until it's finished unpacking. But, if you are using AI to help you write code faster and not better than when you were a slow per-character engineer billing $100/hr. Now, with AI, you're like well shit - consulting sucks now - now I spend all my time hustling gigs instead of head's down solid engineering - because the code generation is so cheap now. When you understand how local models are built, and how their code training gives you insight into your own ability to fill the gaps into your own design of software engineering - its you who can do what the AI cannot do - think.

The Afternoon We Put Magento's Schema on SQL Server

Andrei Merlescu — Mon, 27 Jul 2026 17:17:09 +0000

There is a moment in every LAMP developer's career where the reporting query wins.

You have a Magento store. It runs on MySQL, because Magento runs on MySQL. Everything you know about SQL, you learned inside InnoDB, and InnoDB has been fine. Then merchandising asks for "top five sellers per category, per store view, month over month, with the year-over-year delta," and you write it, and it runs for forty minutes, and it takes the storefront down with it.

I got called into a shop in that exact state in 2016. Magento 1.9, five store views, about 900,000 SKUs, six years of order history. The stack was as classic as it gets: Apache, PHP, MySQL 5.6, one primary and one replica. And in the corner of the rack, doing nothing but eating license fees, was a SQL Server 2012 instance the ERP team had left behind.

I spent an afternoon with one of their developers showing him why we were going to feed Magento's schema into that box, and what changed once we did. This document is that afternoon, written out. The through-line is simple: Magento's schema is the fixed artifact, and the engine underneath it is a variable. Every heading below is a question that comes up in SQL interviews and code reviews, and every one of them has a different answer depending on whether you're standing on InnoDB, SQL Server, or Oracle.

A note on honesty before we start. MySQL 8.0 closed most of the gaps I'm about to describe — window functions, CTEs, hash joins, real CHECK constraints, EXCEPT/INTERSECT, LATERAL. If you're on 8.0 or later today, a lot of this reads as history. It's still worth reading as history, because the reasoning transfers, and because a startling number of production Magento installs are still on 5.7 or on MariaDB, which diverged and does not have all of it either.

The setup: why two engines at all

The first thing I did was not write a query. It was draw the schema on a whiteboard in three layers, because until you see the layers, Magento looks arbitrary.

Layer one is truth. catalog_product_entity, catalog_product_entity_varchar and its siblings, eav_attribute, sales_order, sales_order_item, customer_entity. Normalized, constrained, slow to read, correct. This is what you restore from backup.

Layer two is index. catalog_product_index_price, catalog_category_product_index_store1, catalog_product_index_eav, catalogsearch_fulltext_scope1. Every one of these is derived. Drop them all and you lose nothing but the time it takes bin/magento indexer:reindex to rebuild them.

Layer three is presentation. sales_order_grid, customer_grid_flat. Flattened read models, each shaped for exactly one admin screen.

Layer two exists because layer one is EAV. A merchant needs to add a "Shell Fabric Denier" attribute to jackets without a schema migration, so attributes live as rows:

-- catalog_product_entity: one row per product, almost no attributes
entity_id | attribute_set_id | type_id      | sku          | created_at
--------- | ---------------- | ------------ | ------------ | ----------
   88213  |               15 | configurable | ALPINE-PARKA | 2014-09-02

-- catalog_product_entity_varchar: the value, scoped per store view
value_id | attribute_id | store_id | entity_id | value
-------- | ------------ | -------- | --------- | ------------------
 9182734 |           73 |        0 |     88213 | Alpine Down Parka
 9182735 |           73 |        2 |     88213 | Parka Alpin

store_id = 0 is the default scope. store_id = 2 overrides it for the French store view. Remember that shape; it's going to teach us the most important thing about outer joins later.

This design is infinitely flexible and completely unusable for a storefront, which is why Magento built layer two. And here's the part that matters for our story: Magento's entire indexer subsystem — mview_state, the *_cl changelog tables, index invalidation, "Update by Schedule" — is a hand-rolled incremental materialized view engine, written in PHP, because MySQL does not have materialized views. Thousands of lines of framework code implementing something Oracle gives you in one DDL statement.

So the pitch to the developer was this. We are not going to migrate off MySQL. Magento stays where it is. We are going to replicate the interesting tables into SQL Server, and every reporting question that MySQL answers badly, we will answer over there instead — using tools MySQL simply does not have. In exchange, we accept the cost of maintaining a second schema, which is real and which I'll be honest about at the end.

The ETL was unglamorous: a nightly full pull of the dimension-ish tables, incremental pulls on sales_order, sales_order_item, and sales_order_payment keyed on entity_id and updated_at. Nothing clever. The cleverness is all in what you can do once the data lands.

Choosing the right query pattern for the problem

The first ticket he handed me was: "Marketing wants the email addresses of everyone who has ever ordered the Alpine Down Parka." He'd already written it.

SELECT DISTINCT c.entity_id, c.email
FROM customer_entity c
JOIN sales_order o      ON o.customer_id = c.entity_id
JOIN sales_order_item i ON i.order_id = o.entity_id
WHERE i.sku = 'ALPINE-PARKA';

Forty-one thousand rows, ninety seconds. The parka had sold 380,000 units. The join fanned out to 380,000 rows and then sorted every one of them to collapse back down to 41,000. He paid for a sort of the fanned-out set and threw the result away.

Before you write anything, answer three questions. I've asked these in code review for fifteen years and I have never regretted it. What is the grain of my result — one row per what? If you can't finish that sentence, you will produce duplicates. Am I filtering, or am I enriching? Filtering means a semi-join; enriching means a real join or a window function. Do I need one value per group, or one row per group? One value is an aggregate; one row is a window function or a lateral.

Here the grain is one row per customer, and we are filtering. We want nothing from the order — just a yes or no. That's EXISTS:

SELECT c.entity_id, c.email
FROM customer_entity c
WHERE EXISTS (
  SELECT 1
  FROM sales_order o
  JOIN sales_order_item i ON i.order_id = o.entity_id
  WHERE o.customer_id = c.entity_id
    AND i.sku = 'ALPINE-PARKA'
);

The optimizer stops at the first matching order per customer. No fan-out, no sort. Two seconds on the same box. This works identically on all four engines and there is nothing platform-specific to learn — which is exactly why I started here. Most bad SQL is not an engine problem. It's a grain error wearing a DISTINCT.

Here's the table I keep pinned above my desk, and it is portable in its entirety:

Question	Right pattern	What people reach for
Customers who ordered ≥1 parka	`EXISTS`	`JOIN` + `DISTINCT`
Products never ordered	`NOT EXISTS`	`NOT IN`
Each order plus its customer's email	`JOIN`	correlated scalar subquery
Revenue per customer	`GROUP BY`	correlated aggregate in `SELECT`
Each order plus that customer's lifetime total	window function	correlated aggregate in `SELECT`
Most recent order per customer, whole row	`ROW_NUMBER()` or lateral	`MAX(created_at)` + self-join
Top 5 sellers per category	`RANK`/`DENSE_RANK`/`ROW_NUMBER`	correlated `COUNT` subquery

Now the first place the second engine earned its keep. On MySQL, when a query got complicated, our only debugging tool was EXPLAIN — an estimate, not a measurement. EXPLAIN ANALYZE didn't arrive until MySQL 8.0.18. On SQL Server we had the actual execution plan with real row counts next to estimated ones, and SET STATISTICS IO, TIME ON telling us exactly how many logical reads each table cost. The developer's reaction to seeing an actual plan for the first time was the reaction everyone has: oh, it was scanning that the whole time.

One structural difference bites people immediately when they port a query, so learn it now. A CTE does not mean the same thing on every engine.

Engine	What a CTE actually does
PostgreSQL ≤ 11	Always materialized. Hard optimization fence — outer predicates do not push in.
PostgreSQL 12+	Inlined if referenced once, non-recursive, non-volatile. Force with `AS MATERIALIZED` / `AS NOT MATERIALIZED`.
SQL Server	Effectively always inlined. A CTE referenced three times is executed three times. No hint to change it.
Oracle	Cost-based. `/+ MATERIALIZE /` and `/+ INLINE /` are your controls.
MySQL 8.0+	Materializes derived tables and CTEs in many cases; merge is possible. Weakest at pushing predicates in.

The SQL Server failure is memorable. A tidy CTE that scans sales_order_item once, referenced in three branches of a UNION ALL, scans 40 million rows three times. It looks elegant in review and it is a disaster at runtime. If a CTE is referenced more than once and the underlying scan is expensive, stage it explicitly — SELECT ... INTO #orders_2016 on SQL Server, and stop hoping.

Using NOT EXISTS / EXISTS where appropriate

The second ticket was merchandising's: "Give us every product that has never been ordered so we can cull the catalog." He wrote the obvious thing and got zero rows. Every time. On a catalog where you could visibly point at products nobody had ever bought.

SELECT e.entity_id, e.sku
FROM catalog_product_entity e
WHERE e.entity_id NOT IN (SELECT product_id FROM sales_order_item);

Look at Magento's own foreign key on that column and the bug becomes obvious:

CONSTRAINT SALES_ORDER_ITEM_PRODUCT_ID_CATALOG_PRODUCT_ENTITY_ENTITY_ID
  FOREIGN KEY (product_id) REFERENCES catalog_product_entity (entity_id)
  ON DELETE SET NULL

ON DELETE SET NULL. The moment a merchant deletes one product, every historical order line for it gets product_id = NULL. In a six-year-old store, that column is guaranteed to contain NULLs.

Now the semantics. x NOT IN (a, b, NULL) expands to x <> a AND x <> b AND x <> NULL. That last term is UNKNOWN — not false, unknown. A conjunction containing UNKNOWN can never evaluate to TRUE. So the filter matches nothing at all. Not fewer rows. Nothing. Silently, with no error and no warning.

This is ANSI behavior and it is identical on MySQL, PostgreSQL, SQL Server, and Oracle. I want to be emphatic about that, because the developer's first instinct was that MySQL had done something to him. It hadn't. This one is on us, not the engine, and moving to a fancier database does not save you.

-- Correct on every engine, forever
SELECT e.entity_id, e.sku
FROM catalog_product_entity e
WHERE NOT EXISTS (
  SELECT 1 FROM sales_order_item i WHERE i.product_id = e.entity_id
);

NOT EXISTS tests row existence rather than value comparison, so a NULL is simply a non-match. Never write NOT IN against a subquery. Against a short literal list — status NOT IN ('canceled','closed') — it's fine and readable. Against a subquery, the only safe version requires the column to be declared NOT NULL in the DDL, not "we're pretty sure," and even then it's usually the slower plan.

Which brings us to where the engines diverge, and to the first genuinely dramatic difference we measured that afternoon.

Engine	`NOT EXISTS`	`NOT IN` against a subquery
Oracle	Anti-join	Handled well — null-aware anti-join (`ANTI NA`) since 11g. Rough parity.
SQL Server	Anti semi-join	Usually an anti-join, but nullable columns add probes and plans get ugly.
PostgreSQL	Clean anti-join	Falls back to an opaque SubPlan filter — and is not rescued by declaring the columns `NOT NULL` on released versions.
MySQL 8.0.17+	Anti-join	Antijoin transformation from 8.0.17. Before that: a dependent subquery, evaluated per row.

Read that MySQL row again. On the 5.6 box, NOT EXISTS over a 900,000-row product table against a 12-million-row order item table was a per-row dependent subquery — nine hundred thousand separate probes. It ran for over an hour. The identical statement on SQL Server 2012 became a hash-based anti semi-join and returned in under a minute. Same SQL. Same data. Same index definitions. The difference was entirely in what the optimizer was capable of transforming.

You will also meet a third spelling in legacy Magento code, and now you know why it's there — before 8.0.17 it was the only way to get a decent anti-join plan out of MySQL:

SELECT e.entity_id, e.sku
FROM catalog_product_entity e
LEFT JOIN sales_order_item i ON i.product_id = e.entity_id
WHERE i.item_id IS NULL;

It's semantically safe — no NULL trap — but it's fragile. Add a second predicate on i in the WHERE clause instead of the ON clause and you silently convert the outer join to an inner join and get zero rows again. Recognize it when you inherit it; don't write it new.

And one thing to stop arguing about in review: SELECT 1 versus SELECT * versus SELECT NULL inside EXISTS makes zero difference on any of the four engines. The select list isn't evaluated. What matters is whether the correlation predicate is indexed. Spend the argument there.

Avoiding unnecessary nested aggregate subqueries

This is the section where the developer got annoyed with me, and he was right to, because I'd been implying his code was sloppy when actually his engine had left him no choice.

The ticket: "Customer service wants a screen showing each order alongside that customer's lifetime order count, lifetime spend, and first order date." His version:

SELECT o.entity_id, o.increment_id, o.customer_id,
       (SELECT COUNT(*)               FROM sales_order o2 WHERE o2.customer_id = o.customer_id) AS lifetime_orders,
       (SELECT SUM(base_grand_total)  FROM sales_order o3 WHERE o3.customer_id = o.customer_id) AS lifetime_spend,
       (SELECT MIN(created_at)        FROM sales_order o4 WHERE o4.customer_id = o.customer_id) AS first_order_at
FROM sales_order o
WHERE o.created_at >= '2016-01-01';

Three correlated subqueries, which means up to three extra index range scans per output row. At two million output rows that's six million extra scans. On a good day the optimizer decorrelates one. Plan for the bad day.

Here's the thing I owed him an apology for: MySQL 5.7 had no window functions. None. They arrived in 8.0. So the elegant one-pass rewrite I was about to show him did not exist on his production database. The correlated-subquery pattern all over the Magento ecosystem is not laziness. It is what people wrote because the alternative was not available.

On SQL Server 2012 — and on Oracle since 8i, which is to say since 1999 — it was available:

SELECT entity_id, increment_id, customer_id,
       COUNT(*)              OVER (PARTITION BY customer_id) AS lifetime_orders,
       SUM(base_grand_total) OVER (PARTITION BY customer_id) AS lifetime_spend,
       MIN(created_at)       OVER (PARTITION BY customer_id) AS first_order_at
FROM sales_order
WHERE created_at >= '2016-01-01';

One scan, one sort by customer_id, three aggregates computed together. But read the semantics carefully, because this is a trap that ships: the window is now over the filtered set. "Lifetime spend" just quietly became "spend since January." If the business meant lifetime, this is wrong in a way that looks right, and nobody catches it until the numbers get compared against finance.

When you need aggregates over the full history but only rows from a window, pre-aggregate and join:

WITH cust AS (
  SELECT customer_id,
         COUNT(*)              AS lifetime_orders,
         SUM(base_grand_total) AS lifetime_spend,
         MIN(created_at)       AS first_order_at
  FROM sales_order
  GROUP BY customer_id
)
SELECT o.entity_id, o.increment_id, o.customer_id,
       c.lifetime_orders, c.lifetime_spend, c.first_order_at
FROM sales_order o
JOIN cust c ON c.customer_id = o.customer_id
WHERE o.created_at >= '2016-01-01';

One aggregate pass over the child, one hash join. This is the workhorse pattern and it's the one to reach for by default. Aggregate before you join, never after. Joining first fans out rows and then you're summing an inflated set, which is also how you get silently wrong totals — we'll do that failure explicitly in the next section.

The third pattern is the one where SQL Server had something MySQL wouldn't get until 8.0.14. "Show me each customer plus every column of their most recent order" needs a whole row, not an aggregate:

-- SQL Server 2012
SELECT c.entity_id, c.email, lo.increment_id, lo.created_at, lo.base_grand_total
FROM customer_entity c
OUTER APPLY (
    SELECT TOP 1 o.increment_id, o.created_at, o.base_grand_total
    FROM sales_order o
    WHERE o.customer_id = c.entity_id
    ORDER BY o.created_at DESC, o.entity_id DESC
) lo;

PostgreSQL and MySQL 8.0.14+ spell it LEFT JOIN LATERAL (...) ON true; Oracle 12c+ accepts either. The decision rule is worth internalizing because it's counterintuitive: lateral does N cheap index seeks, ROW_NUMBER() does one big sort. For a VIP list of 200 customers against 12 million orders, lateral wins by orders of magnitude. For "top 3 orders per customer across all 700,000 customers," ROW_NUMBER() wins — one sorted pass beats 700,000 seeks. Know your N.

Oracle has a shortcut here with no equivalent on the other three, and if you ever land on an Oracle warehouse it will save you a self-join:

SELECT customer_id,
       MAX(increment_id) KEEP (DENSE_RANK LAST ORDER BY created_at, entity_id) AS last_increment_id,
       SUM(base_grand_total) AS lifetime_spend
FROM sales_order
GROUP BY customer_id;

KEEP (DENSE_RANK FIRST/LAST) gives you "the value of column X from the row with the max of Y" inside a plain GROUP BY. No window pass, no join. PostgreSQL's nearest analogue is DISTINCT ON. SQL Server and MySQL have nothing like it.

And the Magento footnote: the platform already solved this problem, and the solution is called customer_grid_flat. When the same expensive aggregation is demanded by a UI on every page load, the right answer eventually stops being "a better query" and becomes "a maintained table." We'll get there.

Understanding joins, filtering, grouping, and set-based logic

Third ticket, and the one that had actually triggered my involvement: "Finance says Q1 revenue on the dashboard doesn't match the accounting system."

-- WRONG
SELECT o.entity_id,
       SUM(i.row_total)   AS item_total,
       SUM(p.amount_paid) AS paid_total
FROM sales_order o
JOIN sales_order_item    i ON i.order_id = o.entity_id
JOIN sales_order_payment p ON p.parent_id = o.entity_id
GROUP BY o.entity_id;

An order with three line items and one payment produces three rows, so SUM(p.amount_paid) is three times too large. Add a split payment and both sums are wrong. The numbers look plausible, which is precisely why this ships to production and survives there for months.

The fix is the rule from the last section, applied: aggregate each branch separately, then join the aggregates.

SELECT o.entity_id, i.item_total, p.paid_total
FROM sales_order o
LEFT JOIN (SELECT order_id, SUM(row_total) AS item_total
           FROM sales_order_item GROUP BY order_id) i ON i.order_id = o.entity_id
LEFT JOIN (SELECT parent_id, SUM(amount_paid) AS paid_total
           FROM sales_order_payment GROUP BY parent_id) p ON p.parent_id = o.entity_id;

Diagnostic trick worth ten seconds of your life: drop COUNT(DISTINCT i.item_id), COUNT(DISTINCT p.entity_id), COUNT(*) into the broken query. When COUNT(*) equals the product of the other two, you've proven the fan-out to yourself and you can stop debating it.

Now, ON versus WHERE, which Magento's store-scope model teaches better than any textbook example. Remember store_id = 0 for the default and store_id = 2 for the French override:

SELECT e.entity_id,
       COALESCE(store_val.value, default_val.value) AS product_name
FROM catalog_product_entity e
JOIN catalog_product_entity_varchar default_val
  ON default_val.entity_id = e.entity_id
 AND default_val.attribute_id = 73
 AND default_val.store_id = 0
LEFT JOIN catalog_product_entity_varchar store_val
  ON store_val.entity_id = e.entity_id
 AND store_val.attribute_id = 73
 AND store_val.store_id = 2;

Move AND store_val.store_id = 2 from the ON clause into WHERE and you have silently converted the LEFT JOIN into an INNER JOIN. Your product list collapses from 900,000 rows to the 8,000 products that happen to have a French override. Any predicate on the null-supplying side of an outer join, placed in WHERE, eliminates the NULL-extended rows. The only WHERE predicate that belongs there is IS NULL, when you're deliberately writing an anti-join.

Sargability next, because two of our slowest reports were slow for this reason alone. Wrapping an indexed column in a function kills the index on every engine:

WHERE YEAR(o.created_at) = 2016         -- no index use
WHERE DATE(o.created_at) = '2016-03-01' -- no index use
WHERE UPPER(c.email) = 'A@B.COM'        -- no index use

The remedies diverge. PostgreSQL and Oracle give you real expression and function-based indexes. MySQL didn't get functional indexes until 8.0.13 — before that you added a generated column and indexed that. SQL Server has no true expression index either; you add a computed column and index it, persisting it unless you're certain the expression is deterministic and precise. And two date traps that will bite you specifically: Oracle's DATE always carries a time component, so WHERE created_at = DATE '2016-07-27' misses everything; and SQL Server's legacy datetime has ~3.33ms precision and rounds, so BETWEEN '2016-03-01' AND '2016-03-31 23:59:59.999' rounds up to April 1 and double-counts a day. Half-open ranges — >= start AND < end — are correct on all four. Just make it the habit.

Implicit conversion deserves its own paragraph because it burned us on the SQL Server side within a week of go-live. Magento's sales_order.increment_id is varchar(32), holding values like '100000123'. Someone writes WHERE o.increment_id = 100000123 with a number, and MySQL converts the column to a number to compare — full scan of twelve million rows. Oracle does the same thing with a VARCHAR2 column against a numeric literal. On SQL Server the shape is different and sneakier: an ORM sending NVARCHAR parameters against a VARCHAR column forces CONVERT_IMPLICIT on the column, because NVARCHAR wins the data-type precedence fight. That single mismatch is the number one cause of "it's fast in SSMS but slow from the app," and I have watched entire consulting engagements resolve to it.

Set operators are where MySQL's era shows most starkly. EXCEPT and INTERSECT did not arrive in MySQL until 8.0.31. On the 5.6 box, "products in the catalog but not in the ERP feed" was hand-written as NOT EXISTS. On SQL Server it was one line. And whichever engine you're on, ask the question that actually costs money:

-- These are not the same
SELECT ... FROM sales_order WHERE store_id = 1
UNION      -- full dedupe: sort or hash the entire combined result
SELECT ... FROM sales_order WHERE store_id = 2;

SELECT ... FROM sales_order WHERE store_id = 1
UNION ALL  -- free, and provably correct here since the branches are disjoint
SELECT ... FROM sales_order WHERE store_id = 2;

Always use UNION ALL unless dedupe is genuinely required. On a 200-million-row order-history report that is the difference between forty seconds and eight minutes. And note that EXCEPT/MINUS deduplicate while NOT EXISTS does not — they are not interchangeable when duplicates carry meaning.

The last piece is the one that justified the whole project, and it's about join algorithms:

Algorithm	PostgreSQL	MySQL 5.7	MySQL 8.0	SQL Server	Oracle
Nested loop	✅	✅	✅	✅	✅
Hash join	✅	❌	✅ (8.0.18+)	✅	✅
Merge join	✅	❌	❌ never	✅	✅

MySQL 5.7 had only nested-loop variants. A six-way analytical join across orders, items, products, categories, customers, and store views — the kind of thing merchandising asks for casually — is resolved by SQL Server or Oracle with a couple of hash joins in seconds, and by MySQL 5.7 by looping. Ours ran for over three hours before we killed it. That query was the reason the SQL Server box stopped being idle. And note that even MySQL 8.0 still has no merge join, so pre-sorted large-to-large joins never get that option. MySQL is a weaker analytical engine than the other three, and Magento's architecture already concedes this — that's what layer two is for.

One last MySQL-specific correctness item, since it caused an upgrade scare: ONLY_FULL_GROUP_BY has been on by default since 5.7.5, and a lot of Magento extensions broke when it landed. Many shops "fixed" it by disabling the mode. Don't. Code that runs with it off returns an arbitrary value from an arbitrary row in each group. That's a correctness bug that produces plausible numbers, which is the worst kind.

Using window functions like DENSE_RANK() and similar tools when appropriate

Now the fun part. The report that started everything: "Top five sellers per category, per store view."

On MySQL 5.7 this had been implemented as a correlated COUNT subquery — for each product, count how many products in the same category sold more than it, and keep the ones where that count is under five. It worked, in the sense that it eventually returned. On SQL Server it became this:

WITH sales AS (
  SELECT ccp.category_id,
         i.product_id,
         SUM(i.qty_ordered) AS units
  FROM sales_order_item i
  JOIN sales_order o                ON o.entity_id = i.order_id
  JOIN catalog_category_product ccp ON ccp.product_id = i.product_id
  WHERE o.created_at >= '2016-01-01'
    AND o.status = 'complete'
  GROUP BY ccp.category_id, i.product_id
),
ranked AS (
  SELECT category_id, product_id, units,
         DENSE_RANK() OVER (PARTITION BY category_id
                            ORDER BY units DESC, product_id ASC) AS rnk
  FROM sales
)
SELECT * FROM ranked WHERE rnk <= 5;

Two things in there are deliberate, and both cause production tickets when omitted.

The first is the choice of ranking function, and it is a business decision, not a technical one. For units of 100, 100, 90, 80: ROW_NUMBER() returns 1, 2, 3, 4 — exactly one row per position, ties broken arbitrarily. RANK() returns 1, 1, 3, 4 — competition ranking, with gaps after ties. DENSE_RANK() returns 1, 1, 2, 3 — no gaps, which is what you want for "top five distinct performance tiers." So: if two products tie for fifth place, does the homepage show five products or six? ROW_NUMBER shows five and drops one at random, which is nondeterministic and a real bug. DENSE_RANK shows six. Ask merchandising, then write down which one you were told.

The second is , product_id ASC in the window's ORDER BY. Without it, a category where four products all sold 240 units produces a different top five on every execution. Then someone files "the homepage changed but we didn't change anything," and you spend a day proving the data is identical. Every window ORDER BY gets a deterministic tiebreaker. This single habit eliminates an entire genre of ticket.

The next request was a running revenue total by day, and it produced the most instructive bug of the whole engagement:

-- WRONG in a way that looks completely right
SELECT o.created_at, o.base_grand_total,
       SUM(o.base_grand_total) OVER (ORDER BY o.created_at) AS running_total
FROM sales_order o;

When you write OVER (ORDER BY y) with no frame clause, the implicit frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. And RANGE includes all peer rows — every row tied on y — at once. Orders share timestamps constantly. So every order placed on March 3rd got the full March 3rd total instead of a row-by-row running sum, and the chart came out as a staircase. Finance noticed four months later.

-- What you almost always meant
SUM(o.base_grand_total) OVER (
  ORDER BY o.created_at, o.entity_id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

There is a second, entirely separate reason to write ROWS explicitly, and it's SQL Server–specific: the default RANGE frame uses an on-disk worktable spool, while ROWS uses an in-memory spool. On large partitions, specifying ROWS is routinely a five-to-ten-times improvement even when the results would be identical. Make it mandatory in SQL Server code review. Related trap on all engines: LAST_VALUE(x) OVER (ORDER BY y) returns the current row's value, because the default frame ends at the current row. Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or reverse the sort and use FIRST_VALUE.

LAG and LEAD opened up analysis they'd never attempted, because expressing "days since this customer's previous order" in MySQL 5.7 required a self-join with a correlated MAX, and nobody wanted to maintain it:

SELECT customer_id, entity_id, created_at,
       LAG(created_at) OVER (PARTITION BY customer_id
                             ORDER BY created_at, entity_id) AS prev_order_at,
       DATEDIFF(day,
                LAG(created_at) OVER (PARTITION BY customer_id
                                      ORDER BY created_at, entity_id),
                created_at) AS days_since_prev
FROM sales_order
WHERE status = 'complete';

That query, wrapped in a percentile, became their repeat-purchase-window model and drove the email cadence for two years.

One idiom you will write weekly — deduplication — and one limitation that surprises everyone. Because window functions are evaluated after WHERE, you cannot filter on one in the WHERE clause. You need a wrapper:

WITH ranked AS (
  SELECT v.*,
         ROW_NUMBER() OVER (PARTITION BY v.entity_id, v.attribute_id
                            ORDER BY CASE WHEN v.store_id = 2 THEN 0 ELSE 1 END,
                                     v.value_id DESC) AS rn
  FROM catalog_product_entity_varchar v
  WHERE v.store_id IN (0, 2)
)
SELECT * FROM ranked WHERE rn = 1;

None of PostgreSQL, MySQL, SQL Server, or Oracle support QUALIFY — that's Teradata, Snowflake, and DuckDB. Expect to write the CTE every time. (Re-check that each major release; it's exactly the kind of sugar that ships quietly.) PostgreSQL does give you DISTINCT ON as a shorthand for the rn = 1 case, and it's often faster there.

Availability, since this is the most version-gated area in SQL:

Engine	Basic windows	Full frames	`GROUPS`/`EXCLUDE`	Named windows
Oracle	8i (1999)	✅	`GROUPS` in 21c+	✅
SQL Server	2005 (ranking only)	2012+	❌	❌
PostgreSQL	8.4	✅	✅ (11+)	✅
MySQL	8.0 only	✅	❌	✅

Two performance notes to carry. An index on (partition_cols, order_cols) lets PostgreSQL, Oracle, and SQL Server skip the sort entirely — on a reporting table this is frequently the highest-leverage index you will ever create. And MySQL sorts once per distinct window specification, so three windows with three different ORDER BY clauses means three sorts; consolidate them.

Finally, know when not to use one. If you only need aggregates at the group grain, GROUP BY is cheaper — it collapses rows, while windows preserve them. SELECT DISTINCT customer_id, SUM(x) OVER (PARTITION BY customer_id) is a GROUP BY that got lost on the way to work.

Working with JSON data in the database

The fraud team's ticket: "All orders in the last ninety days paid through gateway X, filtered by token prefix."

He went looking and found sales_order_payment.additional_information. Here is the first lesson, and most JSON guides skip it: it isn't a JSON column. It's a text column containing a serialized JSON document. Same for sales_order_item.product_options and quote_item_option.value. Twelve million rows, no index, no validation, no schema, no type checking.

Before any syntax, the design rule. JSON columns are for genuinely schemaless, sparse, or third-party-shaped payloads: gateway responses, webhook bodies, per-tenant custom fields, audit snapshots. The test is simple — if you filter, join, sort, or aggregate on it regularly, it should be a column. Every engine's JSON indexing story is worse than a plain B-tree on a scalar, and you also give up NOT NULL, foreign keys, and check constraints.

Magento actually made this call correctly, and you can see it in the schema: sales_order_grid has a real payment_method column. It didn't have to — the data is right there in additional_information — but the admin grid filters on it, so it became a column in the read model. That's the rule applied by people who had to live with the consequences.

The second rule, which I'll state harshly because I've cleaned up after it three times: do not use JSON as an EAV escape hatch. "Just put the extra attributes in a JSON blob" is how you get a 400GB table nobody understands where every query is a full scan. Magento's EAV is at least indexed and introspectable. A blob is not.

On MySQL 5.6 there was no help at all — JSON functions arrived in 5.7, and even then, there is no direct index on a JSON path. You must materialize the value first:

-- MySQL 5.7: generated column, then index it
ALTER TABLE sales_order_payment
  ADD COLUMN gateway VARCHAR(50)
    AS (JSON_UNQUOTE(JSON_EXTRACT(additional_information, '$.method_title'))) STORED,
  ADD INDEX idx_gateway (gateway);

-- MySQL 8.0.13+: functional index, same thing, less clutter
ALTER TABLE sales_order_payment
  ADD INDEX idx_gateway ((CAST(additional_information->>'$.method_title' AS CHAR(50))));

And there's an operational gotcha that isn't obvious: partial in-place JSON updates only happen under narrow conditions. Otherwise MySQL rewrites the entire document and logs the whole thing to the binlog, and your read replicas fall behind. I have seen replication lag take out a storefront's search because someone added a JSON audit trail to order saves.

On SQL Server the pattern we actually shipped was to shred at load time rather than query in place, which is the right answer on any engine. OPENJSON with an explicit WITH clause is dramatically faster than the default key/value form, and it hands you typed columns:

-- Shred once, during ETL, into a real relational table
INSERT INTO dw.OrderPayment (OrderId, MethodTitle, CcType, CcLast4, AmountPaid)
SELECT p.parent_id, j.method_title, j.cc_type, j.cc_last4, p.amount_paid
FROM stg.sales_order_payment p
CROSS APPLY OPENJSON(p.additional_information)
  WITH (
    method_title VARCHAR(64)  '$.method_title',
    cc_type      VARCHAR(16)  '$.cc_type',
    cc_last4     VARCHAR(4)   '$.cc_last4'
  ) j;

Now the fraud query is a seek against an indexed VARCHAR column, and the JSON never appears in a WHERE clause again. If you must keep JSON in place on SQL Server 2016–2022, it's NVARCHAR(MAX) plus a persisted computed column plus an index, and you should always add CHECK (ISJSON(Payload) = 1) or you will accumulate malformed rows. SQL Server 2025 finally added a native binary json type and a JSON index, but check the fine print before you rely on it — as of writing, the type is GA on Azure SQL and in preview on-prem, and creating a JSON index takes a schema-modification lock for the entire operation with no ONLINE option, which on a twelve-million-row table means a maintenance window.

For completeness, the capability landscape, because if you land on PostgreSQL or Oracle the answer is much better:

	PostgreSQL	MySQL 8.0	SQL Server	Oracle
Native type	`json` / `jsonb` (binary)	`JSON` (binary); MariaDB: `LONGTEXT` alias	`NVARCHAR(MAX)`; native in 2025	`JSON` in 21c+
Containment test	`@>` (indexed)	`JSON_CONTAINS`	`JSON_PATH_EXISTS` (2022+)	`JSON_EXISTS`
Shred to rows	`jsonb_array_elements`, `JSON_TABLE` (17+)	`JSON_TABLE` (8.0.4+)	`OPENJSON`	`JSON_TABLE`
General index	GIN on `jsonb`	❌	JSON index (2025, preview)	`CREATE SEARCH INDEX … FOR JSON`

PostgreSQL's GIN index on jsonb and Oracle's JSON search index are the only two that index the whole document generically — everywhere else you're indexing one path at a time. Watch out for TOAST on PostgreSQL, though: pulling one small field out of a 2KB document across millions of rows means detoast-and-decompress per row, and it's far slower than people expect. And note MariaDB is not MySQL here at all — its JSON is an alias for LONGTEXT with a validity check, with no binary storage and no multi-valued indexes.

Portable advice: treat JSON as an opaque payload. Store it, retrieve it whole, parse it in the application. The moment you query inside it, you've committed to that engine. Extract the fields you query into real columns at write time — which is, once again, exactly what sales_order_grid does.

Indexing strategy

The layered-navigation ticket — "filtering the Jackets category takes eleven seconds" — is the best indexing lesson in the Magento schema, because the answer is not "add an index."

Look at an EAV value table (2.x, roughly — always confirm against your own SHOW CREATE TABLE and the module's db_schema.xml):

CREATE TABLE catalog_product_entity_varchar (
  value_id     int NOT NULL AUTO_INCREMENT,
  attribute_id smallint unsigned NOT NULL,
  store_id     smallint unsigned NOT NULL,
  entity_id    int unsigned NOT NULL,
  value        varchar(255),
  PRIMARY KEY (value_id),
  UNIQUE KEY (entity_id, attribute_id, store_id),
  KEY (attribute_id),
  KEY (store_id)
);

That unique key is beautifully designed for one question: give me every attribute of product 88213. Leading column entity_id, index seek, done — that's the product detail page. Now ask layered navigation's question: give me every product where attribute 167 equals 'Blue'. Your predicate is on attribute_id and value. The composite index leads with entity_id, so it's useless. The standalone KEY (attribute_id) hands you every row for that attribute across the entire catalog and then filters value by hand.

The leading-column rule: an index on (a, b, c) serves predicates on a, (a,b), and (a,b,c). It does not efficiently serve b alone. All four engines can fall back to a full index scan, but that's not what you designed. When two hot access paths need opposite column orders, you need two structures.

On SQL Server we solved it with an index. On MySQL, Magento solved it with a whole table:

-- SQL Server: build the reverse-ordered index, with INCLUDE for coverage
CREATE INDEX IX_ProductVarchar_Attr_Value
  ON dw.CatalogProductEntityVarchar (attribute_id, store_id, value)
  INCLUDE (entity_id);

That INCLUDE clause is worth its own paragraph. Included columns live in the index leaf but not the key, so they don't widen the seek structure and they don't affect ordering, but they make the index covering — the engine never touches the base table. MySQL has no INCLUDE. Neither does Oracle. On both, extra columns must become key columns, which widens every level of the B-tree. PostgreSQL added INCLUDE in version 11. This is a small syntax difference with a large physical consequence.

Magento's equivalent is catalog_product_index_eav, keyed roughly (attribute_id, store_id, value, entity_id) — exactly the reversed column order, built as a separate physical table because that was the tool available. Same idea, ten thousand times the machinery.

Two more universal principles before the divergences. Equality columns first, then range, then include-only: for WHERE store_id = ? AND status = ? AND created_at > ?, index (store_id, status, created_at). Put created_at before status and the index can only seek to the range start and must filter everything after. And every index is a tax on writes — ten indexes means ten structures maintained per insert. Magento's synchronous sales_order_grid writes are a known Black Friday bottleneck for exactly this reason, which is why dev/grid/async_indexing exists.

Now the structural difference that shapes your primary key everywhere:

Engine	Table storage	Consequence
MySQL/InnoDB	Always clustered on the PK. Secondary indexes store the PK value as the row pointer.	A wide PK is duplicated into every secondary index. Random PKs split pages on every insert.
SQL Server	Clustered index optional; without one it's a heap.	Same amplification when clustered. Heaps accumulate forwarded records.
PostgreSQL	Always a heap. `CLUSTER` is a one-time reorg that doesn't hold.	Physical order drifts; correlation drives range-scan cost.
Oracle	Heap by default; Index-Organized Tables available.	IOTs are excellent for narrow lookup tables, poor for wide ones.

Magento gets this right, and it's worth noticing: every entity PK is a narrow, monotonic auto-increment integer. Not a UUID. On InnoDB, a random UUIDv4 clustered primary key is a documented performance disaster — insert throughput collapses as the B-tree fragments, and every secondary index carries 16 to 36 bytes per entry. If you need distributed IDs, use a time-ordered variant (UUIDv7, NEWSEQUENTIALID(), ULID) stored as BINARY(16) or uniqueidentifier, never as a 36-character string.

Two things bit us during the migration and will bite you. First, InnoDB automatically creates an index on foreign key columns; PostgreSQL, SQL Server, and Oracle do not. MySQL had been doing it for these developers their entire careers, so the moment the schema landed on SQL Server, the "get all items for this order" query fell off a cliff. Unindexed FKs also make parent deletes scan the child table. On Oracle it's worse still — an unindexed FK makes the engine take a share lock on the child table during parent deletes and PK updates, which since 9i is acquired and released per statement rather than held for the transaction, but is still a blocking window on top of a full scan. Every FK column gets an index, on every engine.

Second, filtered indexes. MySQL has no partial index of any kind:

-- SQL Server: 14,000 entries instead of 12,000,000
CREATE INDEX IX_Orders_Pending ON dw.SalesOrder (CreatedAt)
WHERE Status = 'pending_payment';

PostgreSQL spells it the same way with WHERE. MySQL's standard workaround is a separate work-queue table that rows are deleted from once processed — and that is precisely what Magento's *_cl changelog tables are. A partial index, implemented as a table, because the engine doesn't offer one. (One SQL Server gotcha: the optimizer only uses a filtered index when the parameter is provably inside the filter, so WHERE Status = @s often won't use it. OPTION (RECOMPILE) or a literal fixes it, and this surprises people constantly.)

Last, plan stability, because "it was fast yesterday" has a different cause on each engine. On SQL Server it's parameter sniffing — the first execution's parameter values shape the cached plan for everyone, so a plan built for a tiny store view then serves the huge one; remedies are OPTION (RECOMPILE), OPTIMIZE FOR, and Query Store forced plans. On PostgreSQL, prepared statements switch to a generic plan after five executions, and skewed data plus a generic plan is a bad plan. On MySQL the problem is upstream of all that: InnoDB index statistics are sampled, and on a sixty-million-row EAV table the defaults are wildly wrong. Raise innodb_stats_persistent_sample_pages on your big tables and re-run ANALYZE TABLE. That one setting bought us a measurable improvement on the Magento side without touching a query.

Normalization vs selective denormalization

Here's where the second engine stopped being a convenience and became an argument about architecture.

Start from the default position: normalize to 3NF and stay there unless you have a measured reason not to. Normalization gives you one place to update each fact, constraints that actually work, narrower rows (more rows per page, better cache hit rate), and a schema that survives requirement changes. Denormalization is a performance optimization with a correctness cost — you're trading guaranteed consistency for speed, and like every optimization you measure first and plan the maintenance.

The distinction that matters more than any other is derived versus duplicated. Derived data can be recomputed from source — a running total, an order count, a price index. If it drifts, you rebuild it, and the risk is manageable. Duplicated data has no single source of truth once both copies are editable, and that is where corruption lives. Denormalize derived data freely, provided you build the rebuild path. Duplicate raw data only with a very clear write-path story.

Magento is a catalogue of both, and reading it this way makes the schema legible. catalog_product_index_price is derived — a precomputed final price per product, per customer group, per website, fully rebuildable. sales_order_grid and customer_grid_flat are derived, and notice they exist per UI screen rather than as one big denormalized table; the shape follows the access path. catalog_category_entity.children_count is a counter cache. And catalog_product_flat_* is the cautionary tale — Adobe has discouraged flat catalog since 2.3 because the write amplification and reindex cost outgrew the read benefit. Denormalization has an expiry date. Revisit yours.

Now the engine gap, which is the largest of any topic in this document:

Engine	Mechanism	Notes
Oracle	Materialized views with query rewrite, `FAST REFRESH ON COMMIT`, MV logs	Best in class. The optimizer transparently rewrites queries to use the MV — the application never changes.
SQL Server	Indexed views (`WITH SCHEMABINDING` + unique clustered index)	Maintained synchronously, always correct. Auto-matched by the optimizer in Enterprise; elsewhere you need `NOEXPAND`.
PostgreSQL	Materialized views, manual `REFRESH`	No incremental refresh, no automatic rewrite.
MySQL / MariaDB	Nothing.	Build summary tables yourself. Plan for it from day one.

Which brings us to the punchline of the entire afternoon. Magento's indexer subsystem — mview_state, catalog_product_price_cl, catalog_category_product_cl, invalidation flags, "Update on Save" versus "Update by Schedule," bin/magento indexer:reindex — is a hand-rolled incremental materialized view engine, written in PHP, because MySQL doesn't have one. On SQL Server, the daily revenue rollup that had been a cron job, a summary table, and about four hundred lines of PHP became this:

CREATE VIEW dw.vDailyRevenue WITH SCHEMABINDING AS
SELECT o.store_id,
       CAST(o.created_at AS date) AS order_date,
       COUNT_BIG(*)               AS order_count,
       SUM(o.base_grand_total)    AS revenue
FROM dbo.SalesOrder o
WHERE o.status = 'complete'
GROUP BY o.store_id, CAST(o.created_at AS date);
GO

CREATE UNIQUE CLUSTERED INDEX IX_vDailyRevenue
  ON dw.vDailyRevenue (store_id, order_date);

The engine now maintains that aggregate synchronously, transactionally, on every write. It cannot drift, because drift is not representable. There is no cron job to monitor, no changelog table to drain, no "the indexer is stuck" ticket at 2am. The restrictions are real and you should know them going in — SCHEMABINDING required, no outer joins, no subqueries, COUNT_BIG(*) instead of COUNT(*), and SUM requires a COUNT_BIG column so the engine can maintain it incrementally — but within those bounds it is the closest thing to free correctness in this entire article.

Oracle goes one step further, and it's the step that matters most for a legacy application you can't change. With query rewrite enabled, you create the materialized view and then do not touch the application; the optimizer silently redirects qualifying queries to the MV:

CREATE MATERIALIZED VIEW mv_daily_revenue
  BUILD IMMEDIATE
  REFRESH FAST ON COMMIT
  ENABLE QUERY REWRITE
AS SELECT store_id, TRUNC(created_at) AS order_date,
          COUNT(*) AS order_count, SUM(base_grand_total) AS revenue
   FROM sales_order WHERE status = 'complete'
   GROUP BY store_id, TRUNC(created_at);

The design consequence, stated as sharply as I can: the same denormalization is far cheaper on Oracle than on MySQL. On Oracle you can denormalize invisibly and keep the logical model clean. On MySQL every denormalization is explicit, hand-maintained, and a permanent obligation. Weight that when you choose a stack, and don't pretend the obligation isn't there.

Whatever engine you land on, there are three ways to keep redundant data honest, in order of preference: a constraint if the engine can enforce it declaratively, a trigger if it can't (synchronous and correct, but it adds latency to every write and gets bypassed by bulk loads), and a batch reconciliation job that recomputes and reports drift. Build the third one regardless of whether you have the first two. A denormalization without a drift-detection job is a scheduled incident. Magento gives you indexer:reindex as the repair path; it does not give you the detection. Write that yourself.

When some redundancy improves performance

His next question was the sharp one: "Then why does sales_order_item store the product name and SKU? Isn't that exactly the duplication you just warned about?"

No. And this is the distinction I most want you to leave with. sales_order_item.name, .sku, and .price are neither derived nor duplicated — they are immutable historical snapshots. sales_order_item.price is not a stale copy of the product's current price. It is a different fact — what we charged this customer on this date — captured at a different point in time. Joining to catalog_product_entity to get a historical price is a bug, not an optimization, and in some jurisdictions it's a compliance failure.

Snapshot anything that appears on a legal or financial document. Order lines, invoice lines, tax rates applied, shipping rates quoted, the customer's address as it was at the time. That rule alone will save you a reconciliation project.

The second category worth the redundancy is the counter cache: post.comment_count instead of COUNT(*) on every page load, or Magento's catalog_category_entity.children_count. Worth it when reads vastly outnumber writes. The gotcha at scale is that every child insert updates the same parent row, so a hot node becomes a lock convoy — and on InnoDB, with Repeatable Read and gap locks, that shows up as deadlocks rather than just slowness. Mitigations are sharded counters (N rows per parent, summed on read), asynchronous batch updates, or accepting eventual consistency with a nightly recompute.

The third and highest-value category is the precomputed aggregate, which we covered above. If you take one denormalization pattern into your career, make it this one. Juniors underuse it more than any other technique, and it is almost always the right answer for reporting over a large OLTP table.

The fourth is worth calling out because it looks wrong and isn't: flattening a hot lookup to avoid a two-hop join. Carrying store_id on every table in a multi-store system, even where it's derivable through a parent, so that every index can lead with it and every query can filter on it directly. That's not a shortcut, it's standard multi-tenant design, and it's what makes row-level security work cleanly later.

And the fifth is materialized path for deep hierarchies, which Magento does in catalog_category_entity:

-- path = '1/2/13/47', alongside parent_id, level, children_count
SELECT * FROM catalog_category_entity WHERE path LIKE '1/2/13/%';

One indexed range scan instead of a recursion. Recursive CTEs are available on all four engines now — MySQL from 8.0, MariaDB from 10.2 — but for a category tree read thousands of times a second on every page render, a prefix LIKE on an indexed column wins every time. Store the path alongside the parent FK: the FK is truth, the path is derived, and you rebuild the path if it drifts.

Here's the redundancy the second engine gave us for free, and it's the one the developer was most excited about. They had a hand-rolled sales_order_audit table maintained by PHP observers, which was incomplete, which everyone knew, and which nobody wanted to fix. On SQL Server 2016+ that entire mechanism is a table option:

ALTER TABLE dw.SalesOrder
  ADD ValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN
        CONSTRAINT DF_ValidFrom DEFAULT SYSUTCDATETIME(),
      ValidTo   datetime2 GENERATED ALWAYS AS ROW END   HIDDEN
        CONSTRAINT DF_ValidTo   DEFAULT '9999-12-31 23:59:59.9999999',
      PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE dw.SalesOrder
  SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dw.SalesOrderHistory));

-- and then, for free:
SELECT * FROM dw.SalesOrder
FOR SYSTEM_TIME AS OF '2016-11-25 09:00:00'
WHERE entity_id = 4471102;

That's redundancy — a full second copy of every changed row — maintained by the engine, transactionally, with no application code and no possibility of an observer being bypassed. Oracle's Flashback Query gives you a comparable capability from a different direction. MySQL has no equivalent; MariaDB does, via system-versioned tables, which is one of the places MariaDB is ahead of MySQL rather than behind it.

Parent/child and subtype/subclass-style table design

Magento has simple, configurable, bundle, grouped, virtual, and downloadable products. They share a SKU, a name, a status, a set of websites, a URL key. They diverge completely in everything else — a bundle needs options and selections, a configurable needs varying attributes and child links, a downloadable needs files and purchase records.

This is the subtype problem, and there are exactly three answers.

Single Table Inheritance puts everything in one table with a discriminator column. Simplest queries, no joins, easy polymorphic lookup — but wide sparse rows, NOT NULL becomes impossible on subtype attributes, and it degrades badly past about four subtypes. Class Table Inheritance puts common attributes in a parent and specifics in child tables sharing the primary key. You get proper NOT NULL and a clean model, at the cost of a join on every full read. Concrete Table Inheritance gives each subtype its own independent table with no parent — fastest per-subtype queries, but no shared key space, no FK can point at "any product," and every polymorphic query needs UNION ALL.

For most business domains, Class Table Inheritance is the default correct answer. Magento chose a hybrid, which is what large real systems usually do: catalog_product_entity.type_id is an STI discriminator, EAV attribute sets play the role of the sparse subtype columns, and genuinely structural subtype data lives in satellite tables — catalog_product_super_attribute and catalog_product_super_link for configurables, catalog_product_bundle_option and _selection for bundles, downloadable_link for downloadables.

Now here is the pattern I most want juniors to learn, and the reason it belongs in a story about a second engine. If you're building greenfield, you can make subtype exclusivity declaratively enforced rather than hoped for:

CREATE TABLE product (
  product_id   BIGINT PRIMARY KEY,
  product_type VARCHAR(20) NOT NULL,
  sku          VARCHAR(64) NOT NULL UNIQUE,
  CONSTRAINT ck_type CHECK (product_type IN ('SIMPLE','BUNDLE','CONFIGURABLE')),
  CONSTRAINT uq_product_type UNIQUE (product_id, product_type)   -- the trick
);

CREATE TABLE bundle_product (
  product_id   BIGINT PRIMARY KEY,
  product_type VARCHAR(20) NOT NULL DEFAULT 'BUNDLE',
  ship_type    VARCHAR(20) NOT NULL,
  CONSTRAINT ck_bundle_type CHECK (product_type = 'BUNDLE'),
  CONSTRAINT fk_bundle_product FOREIGN KEY (product_id, product_type)
    REFERENCES product (product_id, product_type)
);

The redundant product_type column, plus the composite unique key on the parent, plus the composite FK, plus the check constraint on the child, makes it structurally impossible for a row typed SIMPLE to have a bundle_product row. Most teams implement this in application code and get it wrong.

And this is the sharpest single illustration of why the engine underneath you is not a detail: MySQL parsed and silently ignored CHECK constraints until 8.0.16. That pattern did not merely underperform on the LAMP box — it did not function. You could write the DDL, MySQL would accept it without complaint, and it would enforce nothing. On SQL Server and Oracle it had worked for decades. If you inherit a MySQL schema older than 8.0.16, assume every check constraint in it is decorative until you prove otherwise.

Magento also gives you a self-referencing parent/child in sales_order_item.parent_item_id. A configurable product's order line has a child line for the simple SKU that actually shipped; a bundle has one child per selection. Anyone summing row_total across all order items without accounting for parent and child rows double-counts, and that's a Magento reporting bug common enough to have its own genre of Stack Overflow question.

Finally, the anti-pattern, live in production, in a schema millions of stores depend on:

CREATE TABLE url_rewrite (
  url_rewrite_id BIGINT PRIMARY KEY,
  entity_type    VARCHAR(32),   -- 'product' | 'category' | 'cms-page'
  entity_id      BIGINT,        -- points into one of three different tables
  request_path   VARCHAR(255),
  target_path    VARCHAR(255),
  store_id       SMALLINT
);

A polymorphic foreign key. You cannot declare a real FK, so referential integrity is gone, joins need CASE or UNION, and indexes are less selective. The consequence is a well-known Magento pathology: delete a product and its rewrites become orphans, because there's no cascade to fire. Stores accumulate millions of dead url_rewrite rows and a scan on 404 handling. Magento did it because one rewrite table serving every entity type is genuinely convenient and rewrites are looked up by request_path rather than by the FK — a defensible trade with a real cost the community has paid for a decade. If you're designing fresh, use an exclusive arc (one nullable FK per target plus a check that exactly one is non-null) or a shared supertype table that each concrete type references. Both are more work up front and correct forever.

Understanding how schema choices affect performance and maintainability

The last hour we spent on the decisions that are cheap on day one and expensive on day one thousand.

Types. Pick the narrowest type that holds the domain, and pick it correctly the first time — changing a primary key from INT to BIGINT on a sixty-million-row EAV table is a multi-hour maintenance window on every one of these engines. If a table could ever exceed 2.1 billion rows, use BIGINT from the start; four bytes costs less than the migration. Money is DECIMAL/NUMBER, always — Magento uses decimal(20,4) for base_grand_total — and never FLOAT or DOUBLE, because 0.1 + 0.2 <> 0.3 in binary floating point and financial reconciliation will find it on the worst possible day. Booleans diverge more than you'd expect: PostgreSQL has a real one, MySQL's is an alias for TINYINT(1) that accepts any integer, SQL Server has BIT, and Oracle had no SQL-level boolean at all until 23ai, which is why legacy Oracle schemas are full of CHAR(1) with a check constraint. And store UTC: timestamptz on PostgreSQL, datetime2 on SQL Server, TIMESTAMP WITH TIME ZONE on Oracle. MySQL's TIMESTAMP converts to UTC correctly but is bounded to 2038, which Magento uses extensively, and which is closer now than any migration plan.

Row width is the quietest performance factor in the whole system. Every engine reads pages, not rows. Halving row width doubles rows per page, which roughly halves scan I/O and doubles effective buffer cache capacity. So don't leave a rarely-read TEXT column sitting in a hot table — and sales_order_item.product_options is exactly that, a serialized blob in one of the busiest tables in the schema. PostgreSQL's TOAST handles this automatically; Oracle moves LOBs out of line above a threshold; SQL Server and MySQL are less automatic. This is also the real reason SELECT * in application code is a design smell: it defeats covering indexes, drags wide columns across the wire, and breaks silently the moment someone adds a column.

Constraints are performance features, not just safety features. NOT NULL lets the optimizer skip null-handling logic. UNIQUE tells it a join won't fan out, which changes cardinality estimates. And join elimination — where a validated foreign key lets SQL Server, Oracle, and PostgreSQL remove a LEFT JOIN entirely when you select nothing from the parent — is why views over well-constrained schemas outperform views over "we enforce it in the app" schemas. The corollary matters operationally: disabling constraints for a bulk load and re-enabling them NOVALIDATE on Oracle or untrusted on SQL Server leaves you with constraints the optimizer will not use. After any bulk import — and Magento imports are constant — check sys.foreign_keys.is_not_trusted on SQL Server and STATUS/VALIDATED in Oracle's USER_CONSTRAINTS.

Concurrency changes how you should write queries, and this is the largest operational difference between SQL Server and the other three:

Engine	Default isolation	Readers block writers?
PostgreSQL	Read Committed (MVCC)	No
Oracle	Read Committed (MVCC, undo-based)	No
MySQL/InnoDB	Repeatable Read (MVCC)	No
SQL Server	Read Committed with locking, unless RCSI is on	Yes

By default, a long report on SQL Server takes shared locks and blocks writers. This is why WITH (NOLOCK) is scattered through every legacy SQL Server codebase — and it's a bad fix, permitting dirty reads, missed rows, and duplicated rows from page splits mid-scan. The correct fix is enabling Read Committed Snapshot Isolation at the database level, which makes SQL Server behave like the others; understand the tempdb implications before flipping it on a live system. We turned RCSI on before the first report ran, and it was the single most important configuration change of the project. Meanwhile MySQL's Repeatable Read default is why you see gap-lock deadlocks on sequence_order_1 and inventory reservations during a flash sale — patterns that would be fine on PostgreSQL.

Schema change at scale. Magento gives you declarative schema (db_schema.xml, setup:upgrade), which handles versioning nicely and tells you nothing about how long the DDL takes on sixty million rows. Never rely on "it was fast in staging" — staging has ten thousand rows. Always set a lock timeout (lock_wait_timeout in MySQL, SET LOCK_TIMEOUT in SQL Server, SET lock_timeout in PostgreSQL), because a migration that queues behind a long query and then blocks every subsequent query is how a two-second ALTER takes down a site — and on PostgreSQL specifically, ALTER TABLE takes an ACCESS EXCLUSIVE lock, so even an "instant" change can cause a total outage if one long SELECT is in flight. Use expand/contract for anything breaking: add new, dual-write, backfill, switch reads, stop writing old, drop old. Four deploys, zero downtime. And on MySQL, know that gh-ost and pt-online-schema-change exist for the cases native online DDL doesn't cover, because on a large Magento install you will need them.

Now the honest part, and it's the paragraph I'd want a junior to remember longest. The second engine is not free. We took on a second schema to keep in sync, a nightly ETL that could fail silently, a second set of credentials and backups and patching, a second dialect for every developer to know, and a permanent question of "which one is right?" whenever two numbers disagreed. We wrote a reconciliation job — row counts and revenue sums per store per day, MySQL versus SQL Server, alerting on any variance over 0.01% — before we shipped a single report, and it caught three real ETL bugs in the first month. Do not stand up a second engine without standing up the thing that tells you it's lying to you.

What to take with you

Black Friday came. The reports held, because they'd stopped being queries on the production database and become tables on a machine that could actually do the work. Nothing about Magento's schema got smarter. We just stopped asking one engine to do things it had never been built to do.

Five things, if you retain nothing else:

State the grain of your result in one sentence before you write anything. Most bad SQL is a grain error wearing a DISTINCT.

NOT EXISTS, never NOT IN against a subquery. It's the only spelling that's correct on every engine, and it's also the fastest on the ones where it matters. And that ON DELETE SET NULL on sales_order_item.product_id is waiting for you right now.

Aggregate before you join. Joining first fans out rows and then you're summing an inflated set, and the wrong number will look plausible.

Every window ORDER BY gets a deterministic tiebreaker, and every ordered window aggregate gets an explicit ROWS frame. The first prevents reports that change without the data changing; the second prevents running totals that are secretly daily totals.

Denormalization is a maintenance obligation, not a shortcut. Magento's entire indexer subsystem is what that obligation looks like when the engine won't help you. Budget for it, and build the drift detector before you build the denormalization.

Before you commit a query

[ ] Stated the result grain in one sentence
[ ] No DISTINCT papering over a fan-out
[ ] NOT EXISTS, not NOT IN, against any subquery
[ ] No correlated aggregate subqueries in the SELECT list
[ ] Aggregation happens before joining
[ ] Outer-join predicates on the null-supplying side live in ON, not WHERE
[ ] Every window ORDER BY has a deterministic tiebreaker
[ ] Explicit ROWS BETWEEN on any ordered window aggregate
[ ] UNION ALL unless dedupe is genuinely required
[ ] No functions wrapped around indexed columns
[ ] Parameter types match column types
[ ] Read the plan against production-scale data, not staging

Before you commit a schema change

[ ] Types as narrow as the domain allows; money is DECIMAL; timestamps timezone-explicit
[ ] Every FK column has an index (free on InnoDB, which is why you'll forget it elsewhere)
[ ] PK is narrow and monotonic
[ ] Subtype exclusivity enforced by constraint, not by hope — and verify constraints are actually enforced on your version
[ ] Any denormalized value has a documented rebuild path and a drift-detection job
[ ] Large text and blob columns are out of the hot table
[ ] Migration is expand/contract, with a lock timeout and a rollback plan
[ ] You know how long it takes at production row counts

Reading the plan

Engine	Command
PostgreSQL	`EXPLAIN (ANALYZE, BUFFERS, VERBOSE)` — `BUFFERS` is the one people skip and shouldn't
MySQL	`EXPLAIN ANALYZE` (8.0.18+), `EXPLAIN FORMAT=JSON`
SQL Server	Actual execution plan; `SET STATISTICS IO, TIME ON`; Query Store for history
Oracle	`DBMS_XPLAN.DISPLAY_CURSOR(format=>'ALLSTATS LAST')` — the actual plan, not `EXPLAIN PLAN`'s guess

Universally: look for estimated versus actual row counts diverging by more than about ten times, scans where you expected seeks, and any operator whose output explodes relative to its input.

On verifying any of this

Every version boundary above is checkable and you should check it, because they move and I have been wrong before.

For the Magento schema, don't trust any blog post including this one — read db_schema.xml in the relevant module in your own installed version and run SHOW CREATE TABLE. Adobe Commerce and Magento Open Source diverge (content staging changes catalog primary keys to row_id), and 2.4.x has changed definitions across minor releases. For MySQL, the Reference Manual's optimization chapter, and check MariaDB's knowledge base separately rather than assuming parity — it isn't the same database. For SQL Server, Microsoft Learn, and read the "Applies to" banner on every page, because the difference between GA in Azure SQL and preview on-prem is frequently the whole story. For Oracle, the SQL Language Reference for your exact release plus the New Features guide, since 19c, 21c, 23ai, and 26ai differ meaningfully and the version numbering does not help.

Where something here is version-gated, I've said so. Verify against the exact version in your environment before you rely on it. That isn't a disclaimer — it's the job.

Disclaimer: I used Claude to generate multiple detailed guides and then in different sessions fact check itself and correct for hallucinations. It's only as powerful as the model was trained to be, and it's being asked to act as an engineer I used to once be, and look at the questions that I never knew to ask and how the original list of requirements is a reference that is very technical and hard to read - but it contains a depth of knowledge that is a reference for a seasoned junior engineer looking to take on a Senior level role. Part of being a Senior is your ability to mentor junior engineers, and writing is the manner in which that I knowledge transfer to the next generation.

SQL Design & Query Reference

Andrei Merlescu — Mon, 27 Jul 2026 16:34:27 +0000

I have professionally worked with four different SQL database types PostgreSQL, MySQL & MariaDB (InnoDB), MSSQL SQL Server 2008+ and Oracle 19c+. Over the course of that time I have been asked:

Choosing the right query pattern for the problem
Using NOT EXISTS / EXISTS where appropriate
Avoiding unnecessary nested aggregate subqueries
Understanding joins, filtering, grouping, and set-based logic
Using window functions like DENSE_RANK() and similar tools when appropriate
Working with JSON data in the database
Good Database and Table Design:Indexing strategy
Normalization vs selective denormalization
When some redundancy improves performance
Parent/child and subtype/subclass-style table design
Understand how schema choices affect performance and maintainability

And being an autodidact software engineer with an electrical engineering background in computer engineering, I would reference The Library to complete task A or task B or task C. Many junior engineers are in this boat as well. They may have the formal education that I lacked not being in computer science as a major in college, but it doesn't mean that I can't use resources at my disposal to learn and meet each of these requirements and where I've done it.

This document is Claude's response to my request for it to act as Staff SQL Architect and write a reference document for these four database types as a knowledge transfer doc for junior engineers who need a quick reference of these core concepts.

In all fairness, it's not wrong to have this handy. Instead of it being fragmented on Stack Overflow in the form of dozens of Questions and Answers - its been gathered through the LLM's that have crossed the line with acceptable community behavior. So, before those models are taken offline 😅🤫🤣 I wanted to utilize this resource to help give back. It's a format and structure applicable to four distinct types of SQL databases that any developer should be comfortable with. My experience with SQL databases was extensive between 2003 and 2020. After, I became heavily involved with MongoDB and NoSQL databases including Redis and PostgreSQL. Since 2020, I've been on the administration side of the database and less on the SQL query writing sided of the database. The Apario Database was custom written from the ground up for an OPEX problem.

If you find this document useful, please give it a 💜 before you bookmark it.

Knowledge Transfer Doc — PostgreSQL · MySQL (InnoDB) · SQL Server 2008+ · Oracle 19c+

How to read this doc: Sections marked [SAME] behave identically on all four engines — learn the concept once and move on. Sections marked [DIFFERS] are where people get burned porting code or scaling up. Version numbers matter a lot here; when a feature is version-gated I say so, and you should still verify against the exact version in your environment before you rely on it.

PART I — WRITING QUERIES

1. Choosing the Right Query Pattern

Before you write anything, answer three questions:

What is the grain of my result? One row per what? If you can't say it in a sentence, you will produce duplicates.
Am I filtering, or am I enriching? Filtering → semi/anti-join (EXISTS). Enriching → join or window function.
Do I need one value per group, or one row per group? One value → aggregate. One row → window function or lateral.

The pattern decision table [SAME across all four]

Problem	Right pattern	Wrong pattern people reach for
"Customers who have ≥1 order"	`EXISTS`	`JOIN` + `DISTINCT`
"Customers with no orders"	`NOT EXISTS`	`NOT IN`
"Each order plus its customer name"	`JOIN`	correlated scalar subquery
"Total per customer"	`GROUP BY`	correlated aggregate in SELECT
"Each order plus its customer's total"	window function	correlated aggregate in SELECT
"Latest order per customer (full row)"	`ROW_NUMBER()` or lateral	`MAX(date)` + self-join
"Top N per group"	`ROW_NUMBER`/`RANK`/`DENSE_RANK`	correlated `COUNT` subquery
"Rows in A not in B, both large"	anti-join (`NOT EXISTS`)	`EXCEPT`/`MINUS` if you need dupes preserved

The `DISTINCT` smell [SAME]

SELECT DISTINCT in a query that joins to a child table almost always means you used a join where you wanted a semi-join. The join fans out, then DISTINCT collapses it back — you paid for a sort/hash of the fanned-out set for nothing.

-- BAD: fan out 4M rows, then dedupe back to 200k
SELECT DISTINCT c.customer_id, c.name
FROM customer c JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'SHIPPED';

-- GOOD: optimizer stops at first match per customer
SELECT c.customer_id, c.name
FROM customer c
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.customer_id = c.customer_id AND o.status = 'SHIPPED');

There are legitimate DISTINCTs (deduping a genuinely duplicated source). But treat every one as a thing you must justify in code review.

CTEs: the biggest cross-engine trap [DIFFERS — important]

A junior writes a CTE thinking "this runs once and gets reused." That is engine-dependent and version-dependent.

Engine	CTE behaviour
PostgreSQL ≤ 11	Always materialized. Hard optimization fence — predicates do not push in.
PostgreSQL 12+	Inlined if referenced once and not recursive/volatile; otherwise materialized. Force with `WITH x AS MATERIALIZED (...)` / `AS NOT MATERIALIZED`.
SQL Server	Effectively always inlined — a CTE referenced three times is executed three times. No hint to materialize. Use a `#temp` table when you need one-shot evaluation.
Oracle	Cost-based; may materialize into a temp segment. Hints: `/+ MATERIALIZE /`, `/+ INLINE /`.
MySQL 8.0+	Materializes derived tables/CTEs in many cases; merge is possible. Historically weaker at pushing predicates into derived tables than the others.

The scale gotcha: on SQL Server, a "clean" query with a CTE that scans a 500M-row fact table and is referenced in three branches of a UNION ALL scans it three times. It looks elegant and it is a disaster. On PostgreSQL ≤ 11 the opposite failure: your WHERE tenant_id = 42 outside the CTE never reaches inside it, so you materialize the whole table first.

Rule for the team: if a CTE is referenced more than once and the underlying scan is expensive, on SQL Server/MySQL stage it into a temp table explicitly. Don't rely on the optimizer being clever.

Recursive syntax differs cosmetically: PostgreSQL and MySQL require WITH RECURSIVE; SQL Server and Oracle use plain WITH (Oracle additionally has the older CONNECT BY, which is still often faster there for pure hierarchy walks and supports LEVEL, SYS_CONNECT_BY_PATH, CONNECT_BY_ISLEAF).

2. `EXISTS` / `NOT EXISTS` — and Why `NOT IN` Keeps Hurting You

The NULL trap [SAME — all four, it's ANSI]

SELECT * FROM customer
WHERE customer_id NOT IN (SELECT customer_id FROM blacklist);

If one row in blacklist.customer_id is NULL, this returns zero rows. Always. Silently.

Why: x NOT IN (a, b, NULL) expands to x <> a AND x <> b AND x <> NULL. That last term is UNKNOWN, never TRUE. So the whole conjunction can never be TRUE.

NOT EXISTS does not have this problem, because it tests row existence, not value comparison. Default to NOT EXISTS. If you must use NOT IN, the subquery column must be NOT NULL declared in the DDL — not "we're pretty sure it's never null."

The optimizer angle [DIFFERS]

Engine	`NOT EXISTS`	`NOT IN`
Oracle	Anti-join	Handles well — has a null-aware anti-join (`ANTI NA`) operator (11g+). Roughly parity.
SQL Server	Anti semi-join	Usually anti-join too, but nullable columns add an extra probe/filter; plans get uglier.
PostgreSQL	Clean anti-join	Historically cannot convert to an anti-join when the column is nullable — degrades to a filtered subplan. Materially worse on large sets.
MySQL 8.0.17+	Anti-join transformation available	Also transformed in 8.0.17+. Pre-8.0 this was a per-row dependent subquery — catastrophic.

So NOT EXISTS is correct everywhere and the best-performing choice on the two engines where it matters most. There is no reason to write NOT IN against a subquery. Against a short literal list (status NOT IN ('A','B')) it's fine.

`LEFT JOIN ... WHERE right.pk IS NULL` [SAME semantics, [DIFFERS] on plan quality]

This is the third anti-join spelling. It is semantically safe (no NULL trap) but:

It's easy to break by adding a second predicate on the right table in WHERE instead of ON, which silently converts your outer join to an inner join.
Oracle and SQL Server usually recognize it and produce the same anti-join plan. PostgreSQL does too. MySQL historically preferred this form (pre-8.0.17 it was the only way to get a decent anti-join plan) — you'll see it a lot in legacy MySQL code.

Prefer NOT EXISTS for readability; recognize the LEFT JOIN / IS NULL form when you meet it.

`EXISTS` vs `IN` for the positive case [SAME, mostly]

All four modernly transform IN (subquery) into a semi-join, so they're usually equivalent. Two notes:

IN with a subquery returning NULLs is not dangerous (unlike NOT IN) — NULLs just never match.
MySQL 5.x executed IN (subquery) as a dependent subquery in many cases. If you maintain anything on 5.6/5.7, rewrite IN to EXISTS or a join. On 8.0+ the semijoin strategies (FirstMatch, LooseScan, MaterializeLookup, DuplicateWeedout) handle it.

`EXISTS` micro-details [SAME]

SELECT 1 vs SELECT * vs SELECT NULL inside EXISTS makes zero difference on any of the four — the select list isn't evaluated. Don't argue about it in review. Do make sure the correlation predicate is indexed; that's what actually matters.

3. Avoiding Unnecessary Nested Aggregate Subqueries

This is the single most common performance defect in junior-written SQL, and it's identical on all four engines. [SAME]

The anti-pattern

SELECT o.order_id,
       o.customer_id,
       (SELECT COUNT(*) FROM orders o2
        WHERE o2.customer_id = o.customer_id)      AS cust_order_count,
       (SELECT SUM(amount) FROM orders o3
        WHERE o3.customer_id = o.customer_id)      AS cust_total,
       (SELECT MAX(order_date) FROM orders o4
        WHERE o4.customer_id = o.customer_id)      AS cust_last_order
FROM orders o
WHERE o.order_date >= DATE '2026-01-01';

Three correlated subqueries = up to three extra index range scans per output row. At 2M output rows that's 6M extra scans. On a good day the optimizer decorrelates one of them. Don't rely on it.

Fix 1 — one window pass

SELECT order_id, customer_id,
       COUNT(*)        OVER (PARTITION BY customer_id) AS cust_order_count,
       SUM(amount)     OVER (PARTITION BY customer_id) AS cust_total,
       MAX(order_date) OVER (PARTITION BY customer_id) AS cust_last_order
FROM orders
WHERE order_date >= DATE '2026-01-01';

One scan, one sort/hash by customer_id, all three aggregates computed together. Note the semantic change: the window is now over the filtered set. If you need totals over all history, you can't use this form — see Fix 2.

Fix 2 — pre-aggregate then join

WITH cust_agg AS (
  SELECT customer_id,
         COUNT(*)        AS cust_order_count,
         SUM(amount)     AS cust_total,
         MAX(order_date) AS cust_last_order
  FROM orders
  GROUP BY customer_id
)
SELECT o.order_id, o.customer_id, a.cust_order_count, a.cust_total, a.cust_last_order
FROM orders o
JOIN cust_agg a ON a.customer_id = o.customer_id
WHERE o.order_date >= DATE '2026-01-01';

One aggregate pass over the child, one hash join. This is the workhorse. Critical rule: aggregate before you join, not after. Joining first fans out rows and then you're aggregating an inflated set — which is also how you get silently wrong SUMs.

The double-counting bug [SAME]

-- WRONG
SELECT c.customer_id, SUM(o.amount), SUM(p.amount)
FROM customer c
JOIN orders o   ON o.customer_id = c.customer_id
JOIN payments p ON p.customer_id = c.customer_id
GROUP BY c.customer_id;

If a customer has 3 orders and 4 payments, you get 12 rows; SUM(o.amount) is 4× too large and SUM(p.amount) is 3× too large. This ships to production regularly because the numbers look plausible. Fix: aggregate each branch separately in its own subquery/CTE, then join the two aggregates. Or use COUNT(DISTINCT ...) as a diagnostic to prove the fan-out to yourself.

Fix 3 — lateral / apply, when you need the whole row [DIFFERS in syntax only]

"Give me each customer plus their most recent order's full details."

Engine	Syntax
PostgreSQL	`LEFT JOIN LATERAL (...) t ON true`
Oracle 12c+	`OUTER APPLY (...)` or `LEFT JOIN LATERAL (...) ON 1=1`
SQL Server	`OUTER APPLY (...)`
MySQL 8.0.14+	`LEFT JOIN LATERAL (...) ON true`

-- PostgreSQL / MySQL 8.0.14+
SELECT c.customer_id, c.name, lo.order_id, lo.order_date, lo.amount
FROM customer c
LEFT JOIN LATERAL (
    SELECT o.order_id, o.order_date, o.amount
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.order_date DESC, o.order_id DESC
    LIMIT 1
) lo ON true;

(SQL Server/Oracle: TOP 1 / FETCH FIRST 1 ROWS ONLY and OUTER APPLY.)

When lateral beats ROW_NUMBER(): when the outer set is small and the inner table is huge and well-indexed. Lateral does N cheap index seeks. ROW_NUMBER() sorts the entire child table. For 100 customers against a 500M-row orders table, lateral wins by orders of magnitude. For "top 3 per customer across all 5M customers," ROW_NUMBER() wins — one sorted pass beats 5M seeks.

That trade-off — N seeks vs. one big sort — is the mental model. Know your N.

Oracle-only shortcut

SELECT customer_id,
       MAX(order_id) KEEP (DENSE_RANK LAST ORDER BY order_date, order_id) AS last_order_id,
       MAX(amount)   KEEP (DENSE_RANK LAST ORDER BY order_date, order_id) AS last_amount,
       SUM(amount) AS total
FROM orders GROUP BY customer_id;

KEEP (DENSE_RANK FIRST/LAST) gets you "the value of column X from the row with the max/min of Y" inside a single GROUP BY — no window pass, no self-join. There is no direct equivalent on the other three; PostgreSQL's closest is DISTINCT ON or ordered-set aggregates.

4. Joins, Filtering, Grouping, and Set-Based Logic

`ON` vs `WHERE` on outer joins [SAME]

-- These are NOT the same query
FROM a LEFT JOIN b ON b.a_id = a.id AND b.status = 'X'   -- filters b before joining
FROM a LEFT JOIN b ON b.a_id = a.id WHERE b.status = 'X' -- silently becomes an INNER JOIN

Any predicate on the null-supplying side placed in WHERE eliminates the NULL-extended rows. If you genuinely want "no matching b," write WHERE b.a_id IS NULL.

Predicate sargability [SAME principle, [DIFFERS] in remedies]

Wrapping an indexed column in a function kills the index. Universal.

WHERE UPPER(email) = 'A@B.COM'          -- no index use on email
WHERE order_date + 1 > SYSDATE          -- no index use
WHERE YEAR(order_date) = 2026           -- no index use

Remedies by engine:

Engine	Expression/functional index	Case-insensitive strategy
PostgreSQL	`CREATE INDEX ON t (upper(email))` — long supported	`citext` type, or expression index
Oracle	Function-based indexes (8i+)	FBI on `UPPER()`, or `NLS_SORT`/`NLS_COMP` linguistic index
SQL Server	No true expression index — use a persisted computed column + index	Column collation is usually CI already (`_CI_AS`) so it's often a non-issue
MySQL 8.0.13+	Functional indexes; before that, generated column + index	Default collations are CI (`utf8mb4_0900_ai_ci`) — usually a non-issue

Two engine-specific date traps:

Oracle: DATE always carries a time component. WHERE order_date = DATE '2026-07-27' misses everything with a nonzero time. Use a half-open range >= DATE '2026-07-27' AND < DATE '2026-07-28', not TRUNC().
SQL Server: datetime (legacy) has ~3ms precision and rounds .997→.997, .999→next second. BETWEEN '2026-07-01' AND '2026-07-31 23:59:59.999' is a bug. Always use half-open ranges. This applies everywhere but bites hardest here.

Implicit conversion — the silent index killer [DIFFERS in how it manifests]

SQL Server: an ORM sending NVARCHAR parameters against a VARCHAR column forces CONVERT_IMPLICIT on the column, producing a full scan. Data type precedence means NVARCHAR wins. This is probably the #1 cause of "it's fast in SSMS but slow from the app." Match your parameter types to your column types.
MySQL: joining columns with different collations or charsets (e.g. utf8mb3_general_ci legacy table to utf8mb4_0900_ai_ci new table) prevents index use on the join. Also, comparing a string column to a numeric literal converts the column to a number → full scan. Extremely common after a partial charset migration.
Oracle: VARCHAR2 vs NVARCHAR2, and comparing a NUMBER column to a string literal. Oracle converts the character side to number, which is safe, but comparing a VARCHAR2 column to a number converts the column → scan.
PostgreSQL: stricter typing means fewer silent conversions, but bigint column vs int parameter is fine while text vs varchar collation mismatches can still block index use in less common cases.

`GROUP BY` strictness [DIFFERS]

Engine	Rule
SQL Server, Oracle	Strict ANSI. Every non-aggregated select item must be in `GROUP BY`.
PostgreSQL	Strict, except it recognizes functional dependency on a grouped primary key — `GROUP BY c.customer_id` lets you select `c.name`.
MySQL	`ONLY_FULL_GROUP_BY` is on by default since 5.7.5. Similar functional-dependency detection to PG. Legacy code with it disabled returns arbitrary values from arbitrary rows — a correctness bug, not a style issue. Never turn it off.

`HAVING` vs `WHERE` [SAME]

WHERE filters rows before grouping; HAVING filters groups after. Putting a non-aggregate predicate in HAVING is not wrong (all four will typically push it down) but it's misleading. Put row filters in WHERE.

Conditional aggregation [DIFFERS slightly]

-- Works everywhere
SELECT customer_id,
       SUM(CASE WHEN status = 'SHIPPED'   THEN amount ELSE 0 END) AS shipped_amt,
       COUNT(CASE WHEN status = 'CANCELLED' THEN 1 END)           AS cancelled_cnt
FROM orders GROUP BY customer_id;

PostgreSQL additionally has the cleaner ANSI FILTER clause: COUNT(*) FILTER (WHERE status = 'CANCELLED'). It's PG-only among these four. Use it in PG-only code; use CASE in anything portable.

Note COUNT(CASE WHEN ... THEN 1 END) — no ELSE, so non-matches are NULL and COUNT skips them. COUNT(CASE WHEN ... THEN 1 ELSE 0 END) counts everything and is a classic bug.

Set operators [DIFFERS in naming and defaults]

Operation	PG	MySQL	SQL Server	Oracle
Union, dedupe	`UNION`	`UNION`	`UNION`	`UNION`
Union, keep dupes	`UNION ALL`	`UNION ALL`	`UNION ALL`	`UNION ALL`
Difference	`EXCEPT`	`EXCEPT` (8.0.31+)	`EXCEPT`	`MINUS` (`EXCEPT` added in 21c+)
Intersection	`INTERSECT`	`INTERSECT` (8.0.31+)	`INTERSECT`	`INTERSECT`

Always ask whether you need UNION or UNION ALL. UNION performs a full dedupe (sort or hash) over the combined result. If the branches are provably disjoint — different date ranges, different status values — UNION ALL is free and UNION costs you a sort of the entire set. On a 200M-row report this is the difference between 40 seconds and 8 minutes.

Also: EXCEPT/MINUS deduplicate the left side. NOT EXISTS does not. They are not interchangeable when duplicates are meaningful.

Join algorithms [DIFFERS — matters at scale]

Algorithm	PG	MySQL 8.0	SQL Server	Oracle
Nested loop	✅	✅	✅	✅
Hash join	✅	✅ (8.0.18+)	✅	✅
Merge join	✅	❌ never	✅	✅ (sort-merge)

MySQL's gap is the important one. Before 8.0.18 MySQL had only nested-loop variants (BNL). A three-table analytical join over millions of rows that any other engine resolves with hash joins in seconds could run for hours on MySQL 5.7. If you're on MySQL and a large join is slow, check the version first — this is frequently the whole answer. And MySQL still has no merge join, so pre-sorted large-to-large joins don't get that option.

Practical consequence: MySQL is a weaker analytical engine than the other three. Design around it — pre-aggregate into summary tables rather than expecting the optimizer to rescue a 6-table star join.

5. Window Functions

Availability [DIFFERS]

Engine	Basic windows	Full frames (`ROWS`/`RANGE`)	`GROUPS` / `EXCLUDE`
Oracle	8i (ancient, very mature)	✅	`GROUPS` in 21c+
SQL Server	2005 (ranking only)	2012+	❌ not supported
PostgreSQL	8.4	✅	✅ (11+)
MySQL	8.0 only	✅	❌

ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, SUM/AVG/COUNT/MIN/MAX OVER are present and identical in semantics on all four modern versions. [SAME]

The three ranking functions [SAME]

For values 100, 100, 90, 80:

Function	Result	Use when
`ROW_NUMBER()`	1, 2, 3, 4	You need exactly one row per group — deduplication, "latest record"
`RANK()`	1, 1, 3, 4	Competition ranking; gaps after ties
`DENSE_RANK()`	1, 1, 2, 3	"Top 3 distinct salary levels"; no gaps

The classic requirement mismatch: "give me the top 3 earners per department." If two people tie for 3rd, do you want 3 rows or 4? ROW_NUMBER gives 3 (arbitrarily dropping one — nondeterministic and a real bug). RANK/DENSE_RANK give 4. Ask the business. Then make the tiebreaker explicit either way.

Always add a deterministic tiebreaker to ORDER BY inside the window. ORDER BY order_date DESC on a table with same-day orders produces different results run to run. Write ORDER BY order_date DESC, order_id DESC. This is the source of most "the report changed but the data didn't" tickets.

Deduplication idiom [SAME]

WITH ranked AS (
  SELECT t.*,
         ROW_NUMBER() OVER (PARTITION BY natural_key
                            ORDER BY loaded_at DESC, id DESC) AS rn
  FROM staging t
)
SELECT * FROM ranked WHERE rn = 1;

The CTE wrapper is required — you cannot filter on a window function in WHERE on any of these four, because windows are evaluated after WHERE. None of PostgreSQL, MySQL, SQL Server, or Oracle support QUALIFY (that's Teradata/Snowflake/DuckDB). Expect to write the CTE.

PostgreSQL has a shorthand for the rn = 1 case:

SELECT DISTINCT ON (natural_key) *
FROM staging ORDER BY natural_key, loaded_at DESC, id DESC;

PG-only. Often faster than ROW_NUMBER there.

The default frame — the biggest window-function gotcha [SAME semantics, [DIFFERS] in cost]

When you write OVER (PARTITION BY x ORDER BY y) with no frame clause, the implicit frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

RANGE means peer rows (ties in y) are all included at once. So a "running total" over a day-grain column gives every row on the same date the full day's total, not a true row-by-row running sum. People find this months later.

-- What you almost always meant:
SUM(amount) OVER (PARTITION BY customer_id
                  ORDER BY order_date, order_id
                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

SQL Server–specific performance angle: in SQL Server the default RANGE frame uses an on-disk worktable spool, while ROWS uses an in-memory spool. Specifying ROWS explicitly is frequently a 5–10× improvement on large partitions — even when the results would be identical. Make ROWS ... mandatory in SQL Server code review.

LAST_VALUE trap [SAME]: LAST_VALUE(x) OVER (ORDER BY y) returns the current row's value, because the default frame ends at the current row. You need ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or better, use FIRST_VALUE with the order reversed.

Named windows [DIFFERS]

PostgreSQL, MySQL 8.0, and Oracle support WINDOW w AS (PARTITION BY ... ORDER BY ...) then OVER w. SQL Server does not — you repeat the full OVER clause. Minor, but it's a portability paper cut and a source of copy-paste inconsistency in SQL Server code.

Performance notes [DIFFERS]

An index on (partition_cols, order_cols) lets PostgreSQL, Oracle, and SQL Server skip the sort entirely. This is often the single highest-leverage index on a reporting table.
SQL Server 2019+ has batch-mode window aggregates on rowstore, which is dramatically faster for large partitions — but it's cost-threshold-gated. SQL Server 2022 improved the window aggregate operator further.
MySQL sorts for every distinct window specification. Three windows with three different ORDER BYs = three sorts. Consolidate window definitions where you can.
Oracle's window implementation is the most mature of the four and generally the fastest at large scale, especially with parallel query.

When not to use a window function

If you only need aggregates at the group grain, GROUP BY is cheaper — it collapses rows; windows preserve them. Don't write SELECT DISTINCT customer_id, SUM(x) OVER (PARTITION BY customer_id); write GROUP BY.

6. JSON in the Database

First, the design rule [SAME]

JSON columns are for genuinely schemaless, sparse, or third-party-shaped payloads: webhook bodies, per-tenant custom fields, audit snapshots, API response caching. They are not a substitute for columns.

The test: if you filter, join, sort, or aggregate on it regularly, it should be a column. Every engine's JSON indexing story is worse than a plain B-tree on a scalar column. You also lose type checking, NOT NULL, foreign keys, and check constraints.

Second rule: do not use JSON as an EAV escape hatch. "Just put the extra attributes in a JSON blob" is how you get a 400GB table where nobody knows what's in it and every query is a full scan.

Capability comparison [DIFFERS heavily — this is the least portable area in SQL]

	PostgreSQL	MySQL 8.0	SQL Server	Oracle
Native type	`json` (text) / `jsonb` (binary)	`JSON` (binary)	`NVARCHAR(MAX)` + `ISJSON` check (native `json` type in SQL Server 2025)	`JSON` type in 21c+; before that `CLOB`/`VARCHAR2` + `IS JSON`
Extract scalar	`->>`, `#>>`, `jsonb_path_query`	`->>`, `JSON_UNQUOTE(JSON_EXTRACT())`, `JSON_VALUE` (8.0.21+)	`JSON_VALUE`	`JSON_VALUE`, dot notation
Extract object/array	`->`, `#>`	`->`	`JSON_QUERY`	`JSON_QUERY`
Containment test	`@>` (indexed!)	`JSON_CONTAINS`	`JSON_PATH_EXISTS` (2022+)	`JSON_EXISTS`
Shred to rows	`jsonb_array_elements`, `JSON_TABLE` (17+)	`JSON_TABLE` (8.0.4+)	`OPENJSON`	`JSON_TABLE`
Modify in place	`jsonb_set`, `		`,` -`	`JSON_SET/INSERT/REPLACE/REMOVE`
General index	GIN on `jsonb` — indexes all keys/values at once	❌ none	❌ (until 2025's JSON indexes)	JSON search index (`CREATE SEARCH INDEX ... FOR JSON`)
Targeted index	B-tree on expression	Generated column + index; multi-valued index for arrays (8.0.17+)	Persisted computed column + index	Function-based index on `JSON_VALUE`

PostgreSQL: use `jsonb`, essentially always

json stores the raw text (preserves whitespace, key order, duplicate keys) and re-parses on every access. jsonb is parsed binary — faster access, supports indexing and containment operators. Cost: slightly slower insert, key order not preserved, duplicate keys collapsed.

CREATE INDEX idx_doc_gin ON events USING GIN (payload jsonb_path_ops);
-- supports: WHERE payload @> '{"type":"purchase"}'

-- Narrower + smaller when you know the key:
CREATE INDEX idx_doc_type ON events ((payload->>'type'));

jsonb_path_ops GIN indexes are substantially smaller and faster than the default jsonb_ops but only support @> / @? / @@, not key-existence (?). Prefer it unless you need key-existence checks.

PG gotchas at scale: large jsonb values get TOASTed (out-of-line, compressed), so payload->>'x' on a 2KB document means detoast + decompress per row. Extracting one small field from a big document across millions of rows is far slower than people expect. Also, jsonb_set rewrites the entire value — updating one key in a 100KB document writes 100KB and generates that much WAL.

MySQL: no direct JSON index — this catches everyone

You cannot CREATE INDEX ON t (json_col->>'$.status') directly in the way you'd hope. Two supported routes:

-- Route 1: generated column (works 5.7+)
ALTER TABLE events
  ADD COLUMN evt_type VARCHAR(50)
    AS (JSON_UNQUOTE(JSON_EXTRACT(payload,'$.type'))) STORED,
  ADD INDEX idx_evt_type (evt_type);

-- Route 2: functional index (8.0.13+) — same thing, less clutter
ALTER TABLE events ADD INDEX idx_evt_type ((CAST(payload->>'$.type' AS CHAR(50))));

-- Route 3: multi-valued index for arrays (8.0.17+)
ALTER TABLE events ADD INDEX idx_tags ((CAST(payload->'$.tags' AS CHAR(40) ARRAY)));
-- used by: WHERE JSON_CONTAINS(payload->'$.tags', '"urgent"')

Multi-valued indexes are MySQL's one genuinely nice JSON feature — one index entry per array element, queryable via MEMBER OF, JSON_CONTAINS, JSON_OVERLAPS.

MySQL gotchas: JSON columns can't be used in a primary key; generated columns must be STORED (not VIRTUAL) for some index types; partial in-place updates only happen under narrow conditions (JSON_SET on same-or-smaller value) — otherwise MySQL rewrites the whole document and logs it whole to the binlog. Replication lag from JSON-heavy writes is a real production issue.

SQL Server: text with functions (historically)

Through SQL Server 2022, JSON is NVARCHAR(MAX) — there was no JSON type, no JSON index. You get it via:

ALTER TABLE Events ADD EventType AS
  JSON_VALUE(Payload, '$.type') PERSISTED;
CREATE INDEX IX_Events_Type ON Events (EventType);

OPENJSON with an explicit WITH schema is dramatically faster than the default key/value form, and gives you typed columns:

SELECT j.type, j.amount
FROM Events e
CROSS APPLY OPENJSON(e.Payload)
  WITH (type VARCHAR(50) '$.type', amount DECIMAL(18,2) '$.amount') j;

Add CHECK (ISJSON(Payload) = 1) on the column or you will accumulate malformed rows.

SQL Server 2025 introduced a native json type with binary storage and JSON indexes; if you're on it, prefer that over NVARCHAR(MAX). Confirm the specifics against the docs for your build — this is recent.

Oracle: strongest declarative story

Oracle's JSON support (especially 19c+ and 21c/23ai) is arguably the most complete:

-- Constraint on pre-21c storage
ALTER TABLE events ADD CONSTRAINT ck_json CHECK (payload IS JSON);

-- Dot notation (requires table alias)
SELECT e.payload.type, e.payload.amount FROM events e;

-- General-purpose index over the whole document
CREATE SEARCH INDEX idx_payload ON events (payload) FOR JSON;

-- Targeted
CREATE INDEX idx_type ON events (JSON_VALUE(payload, '$.type'
  RETURNING VARCHAR2(50) ERROR ON ERROR NULL ON EMPTY));

The RETURNING clause and error-handling clauses must match exactly between index definition and query predicate or the index isn't used — a common and frustrating gotcha.

Oracle 23ai adds JSON Relational Duality Views: relational tables underneath, JSON document API on top, with proper concurrency control. Genuinely the best answer to "the app team wants documents, I want normalization," if you're on that version.

Portable JSON advice

If you must support more than one engine: use JSON as an opaque payload — store it, retrieve it whole, parse in the application. The moment you query inside it, you've committed to that engine. Extract the fields you query into real columns at write time.

PART II — SCHEMA & PHYSICAL DESIGN

7. Indexing Strategy

Universal principles [SAME]

Leading-column rule. A composite index on (a, b, c) serves predicates on a, (a,b), (a,b,c). It does not efficiently serve b alone. All four can do a full index scan in that case, but that's not what you designed for.
Equality columns first, then range, then include-only columns. For WHERE tenant_id = ? AND status = ? AND created_at > ?, index (tenant_id, status, created_at). Putting created_at before status means the index can only seek to the range start and must filter the rest.
Covering indexes eliminate the table lookup. If the index contains every column the query needs, the engine never touches the base table.
Every index is a tax on writes. Ten indexes = ten structures to maintain per insert/update. Measure before adding.
Selectivity matters. An index on a boolean or a 3-value status is usually useless alone; it's useful as a trailing column or as a partial index.
Index your foreign keys. See below — this one differs, and it matters.

The structural difference: clustering [DIFFERS — foundational]

Engine	Table storage	Consequence
MySQL/InnoDB	Always clustered on the PK (or a hidden rowid if none). Secondary indexes store the PK value as the row pointer.	A wide PK (e.g. `UUID CHAR(36)`) is duplicated into every secondary index. Random PKs cause page splits on every insert.
SQL Server	Clustered index optional; without one the table is a heap. Nonclustered indexes point to the clustering key (or RID for heaps).	Same PK-width amplification as InnoDB when clustered. Heaps suffer forwarded records on updates.
PostgreSQL	Always a heap. No clustered index. `CLUSTER` is a one-time reorganization that doesn't hold.	Physical order drifts; correlation matters for range scan cost. Index-only scans need the visibility map to be current.
Oracle	Heap by default; Index-Organized Tables available for PK-clustered storage.	IOTs are great for narrow lookup tables, less so for wide ones.

The PK choice consequence: on MySQL and SQL Server, use a narrow, monotonically increasing clustering key. BIGINT identity/auto_increment is the default correct answer. Random UUIDv4 as a clustered PK on InnoDB is a well-documented performance disaster — insert throughput collapses as the B-tree fragments and every secondary index carries 16–36 bytes per entry.

If you need UUIDs for distributed ID generation, use a time-ordered variant (UUIDv7, or SQL Server's NEWSEQUENTIALID(), or ULID) stored as BINARY(16) / uniqueidentifier / uuid — never as a 36-char string. PostgreSQL tolerates random UUIDs better (heap storage) but still suffers index bloat and worse cache locality.

Index feature matrix [DIFFERS]

Feature	PG	MySQL 8.0	SQL Server	Oracle
Partial / filtered index	✅ `WHERE`	❌	✅ filtered index	❌ (emulate: function-based index returning NULL — Oracle doesn't index all-NULL entries)
Covering non-key columns	✅ `INCLUDE` (11+)	❌ (add as key cols)	✅ `INCLUDE`	❌ (add as key cols)
Expression / functional index	✅	✅ (8.0.13+)	⚠️ via persisted computed column	✅
Descending index	✅	✅ (8.0+; earlier it was parsed and ignored)	✅	✅
Bitmap index	❌	❌	⚠️ columnstore serves the use case	✅ (DW only)
Columnstore	⚠️ extensions	❌	✅ (mature)	✅ In-Memory Column Store (licensed)
Hash index	✅ (limited)	✅ MEMORY engine only	✅ (in-memory OLTP)	✅ hash cluster
GIN / inverted (arrays, JSON, FTS)	✅	⚠️ multi-valued only	❌	✅ search index
BRIN (huge, correlated tables)	✅	❌	⚠️ columnstore segment elimination	⚠️ zone maps (Exadata)
Invisible index (test before drop)	⚠️ no	✅	⚠️ via disable	✅
Online index build	✅ `CONCURRENTLY`	✅ mostly	✅ Enterprise only	✅ `ONLINE` (EE)

Three engine-specific traps worth memorizing:

Oracle: single-column B-tree indexes do not store NULLs. So WHERE deleted_at IS NULL cannot use a single-column index on deleted_at. Workaround: make it composite ((deleted_at, id)) or use a function-based index. Related and worse: Oracle treats the empty string '' as NULL. Code ported from any other engine that relies on col = '' will silently return nothing.
PostgreSQL: index-only scans require a current visibility map. A heavily-updated table that hasn't been vacuumed will do heap fetches anyway, and your "covering index" won't cover. If index-only scans matter, tune autovacuum aggressively on that table.
Oracle bitmap indexes are for data warehouses only. A single-row DML locks an entire bitmap segment, blocking many other rows. Putting one on an OLTP table is a guaranteed concurrency incident.

Filtered/partial indexes — a big win where available

-- PostgreSQL
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'PENDING';

-- SQL Server
CREATE INDEX IX_Orders_Pending ON Orders (CreatedAt)
WHERE Status = 'PENDING';

If 0.1% of a 500M-row table is pending, this index is ~500k entries instead of 500M. Enormous. MySQL has no equivalent — the standard workaround is a separate "work queue" table that rows are deleted from when processed. That's not a hack; on MySQL it's the right design.

SQL Server filtered-index gotcha: the plan only uses it if the parameter is provably within the filter. Parameterized queries (WHERE Status = @s) often won't use a filtered index because the optimizer can't prove @s = 'PENDING' at compile time. OPTION (RECOMPILE) or a literal fixes it. This surprises people constantly.

Statistics and plan stability [DIFFERS — the top cause of "it was fast yesterday"]

Engine	Mechanism	Main failure mode
SQL Server	Auto-update stats on ~20% change (or sqrt-based on 2016+). Plans cached per parameterized statement.	Parameter sniffing — the first execution's parameter values shape the cached plan for everyone. A plan built for `tenant_id = <tiny tenant>` is then used for `<huge tenant>`. Remedies: `OPTION (RECOMPILE)`, `OPTIMIZE FOR`, Query Store forced plans, Automatic Tuning.
Oracle	Bind peeking + Adaptive Cursor Sharing + SQL Plan Baselines.	Adaptive features mostly help but can cause plan churn. Baselines/SQL Profiles are the stability tool. Stale stats on volatile tables still bite.
PostgreSQL	Autovacuum/autoanalyze. Prepared statements switch to a generic plan after 5 executions if it seems no worse.	Skewed data + generic plan = bad plan. `plan_cache_mode = force_custom_plan` is the escape hatch. Also: no cross-column statistics by default — use `CREATE STATISTICS` for correlated columns (`city`/`postcode`).
MySQL	Simplest optimizer of the four. Histograms only since 8.0. No plan cache to speak of.	Weakest cardinality estimation; InnoDB index statistics are sampled (`innodb_stats_persistent_sample_pages`) and can be badly wrong on large tables. Raise the sample pages on big tables.

Team practice: on SQL Server, when someone says "the app is slow but it's fast when I run it in SSMS," your first hypothesis is parameter sniffing, second is implicit conversion from NVARCHAR parameters. Those two cover the majority of cases.

Deep pagination [SAME problem, same fix]

LIMIT 20 OFFSET 500000 (or OFFSET ... FETCH, or ROWNUM/ROW_NUMBER filtering) makes the engine produce and discard 500,000 rows. Cost grows linearly with page number. Use keyset (seek) pagination:

SELECT ... FROM orders
WHERE (created_at, order_id) < (:last_created_at, :last_order_id)
ORDER BY created_at DESC, order_id DESC
FETCH FIRST 20 ROWS ONLY;

Row-value comparison works in PostgreSQL, MySQL, and Oracle. SQL Server does not support row-value constructors in comparisons — expand manually:

WHERE created_at < @last_created_at
   OR (created_at = @last_created_at AND order_id < @last_order_id)

(SQL Server may need an index hint or the rewritten OR form to seek properly; check the plan.)

8. Normalization vs. Selective Denormalization

Normalize first — the default position [SAME]

Get to 3NF/BCNF and stay there unless you have a measured reason not to. Normalization gives you: one place to update each fact, constraints that actually work, smaller rows (more rows per page, better cache hit rate), and a schema that survives requirement changes.

Denormalization is a performance optimization with a correctness cost. You are trading guaranteed consistency for speed. Like all optimizations: measure first, and know how you'll maintain it.

The critical distinction: derived vs. duplicated

Derived data (a running total, an order count, a "current status") can be recomputed from source. If it drifts, you can rebuild it. Risk is manageable.
Duplicated data (customer name copied into orders) has no single source of truth once both copies are editable. This is where corruption lives.

Denormalize derived data freely with a rebuild path. Duplicate raw data only with a very clear write-path story.

When redundancy legitimately wins

1. Immutable historical snapshots — not really denormalization at all.
An order line must store unit_price and product_name as of the transaction. This is not a redundant copy of product.price — it's a different fact ("what we charged") from a different point in time. Joining to product for historical price is a bug, not an optimization. Snapshot anything that appears on a legal or financial document.

2. Counter caches / rollups.
post.comment_count instead of COUNT(*) on every page load. Worth it when read:write is heavily skewed.
Gotcha at scale: every comment insert updates the same post row. A hot post becomes a lock convoy. Mitigations: sharded counters (N rows per post, summed), async batch updates, or accept eventual consistency and recompute nightly.

3. Precomputed aggregate/summary tables.
Daily/hourly rollups for dashboards. Almost always the right answer for reporting on a large OLTP table. This is the highest-value denormalization pattern in practice, and the one juniors underuse most.

4. Flattening a hot join to avoid a two-hop lookup.
Storing tenant_id on every table in a multi-tenant system — even where it's derivable through a parent — so that every index can lead with it and every query can filter on it directly. This is standard, correct multi-tenant design, and it also lets row-level security work cleanly.

5. Materialized path / nested sets for deep hierarchies.
Recursive CTEs are fine for shallow trees. For an org chart read thousands of times a second, store a materialized path (/1/17/93/) alongside the parent FK. Redundant, but a prefix LIKE '/1/17/%' on an indexed column beats a recursion.

Maintenance mechanisms [DIFFERS — the largest gap between these engines]

Engine	Mechanism	Notes
Oracle	Materialized views with query rewrite, `FAST REFRESH ON COMMIT`, materialized view logs	Best in class. Optimizer transparently rewrites queries to use the MV — the application doesn't change.
SQL Server	Indexed views (`WITH SCHEMABINDING` + unique clustered index)	Maintained synchronously and always correct. Auto-matched by the optimizer in Enterprise Edition; other editions need `NOEXPAND`. Heavy restrictions: no outer joins, no subqueries, no `COUNT()` (use `COUNT_BIG()`), `SUM` requires a `COUNT_BIG` column.
PostgreSQL	Materialized views — manual `REFRESH MATERIALIZED VIEW`	No incremental refresh, no automatic query rewrite. `REFRESH ... CONCURRENTLY` avoids blocking readers but requires a unique index and is slower. For incremental, roll your own with triggers.
MySQL	Nothing. No materialized views, no indexed views.	You build summary tables yourself with triggers, scheduled events, or application logic. Plan for this from day one on MySQL.

Consequence for design: on Oracle you can often denormalize invisibly via MVs with query rewrite — the logical model stays clean. On MySQL every denormalization is explicit, hand-maintained, and a permanent maintenance obligation. Weight the trade-off accordingly: the same denormalization is much cheaper on Oracle than on MySQL.

Keeping redundant data honest [DIFFERS]

Three approaches, in order of preference:

Constraint — if the engine can enforce it declaratively, that's free correctness.
Trigger — synchronous, correct, but adds latency to every write and is easy to forget when someone does a bulk load. Trigger-maintained rollups are a common source of "the totals don't match" tickets.
Batch reconciliation — nightly job that recomputes and reports drift.

Always build #3 regardless of whether you have #1 or #2. A denormalization without a drift-detection job is a future incident. Make "how do we detect and repair drift" a mandatory question in design review.

Note also: CHECK constraints in MySQL were parsed and ignored until 8.0.16. Any schema older than that may have constraints that never did anything. Verify.

9. Parent/Child and Subtype/Subclass Design

Foreign keys: index them yourself [DIFFERS — high-impact]

Engine	Auto-indexes FK columns?
MySQL/InnoDB	Yes — automatically creates an index on the child column if one doesn't exist
PostgreSQL	No
SQL Server	No
Oracle	No

Three of four leave you to it. Unindexed FKs cause:

Slow DELETE/UPDATE on the parent (must scan children to enforce the constraint)
Slow "get all children of X" queries — the most common query in the application

The Oracle-specific severity: an unindexed foreign key causes Oracle to take a share lock on the entire child table during parent deletes and PK updates. Not a row lock — a table lock. On a busy system this is an outage. This is one of the classic Oracle DBA findings, and it's worth running a "find unindexed FKs" audit query on any Oracle schema you inherit.

Rule: every FK column gets an index, on every engine, unless you can prove it's never traversed and the parent is never deleted from.

`ON DELETE CASCADE` [SAME syntax, [DIFFERS] in gotchas]

Supported on all four. Caveats:

Cascades don't fire triggers uniformly. In SQL Server, cascading deletes do fire triggers on the child; in MySQL/InnoDB, cascading operations do not fire the child's triggers. Silent data integrity gaps if you rely on triggers for auditing.
SQL Server rejects multiple cascade paths to the same table ("may cause cycles or multiple cascade paths"). Common in graph-ish schemas; you end up doing it in application code or a stored procedure.
Cascade deletes at scale are dangerous. Deleting one parent row with 40M descendant rows in a single transaction blows out your log/undo, holds locks for minutes, and can't be interrupted cleanly. For large hierarchies, prefer soft deletes plus a batched purge job.

Subtype / subclass modelling — the three options [SAME conceptually]

Scenario: Vehicle with subtypes Car, Truck, Motorcycle, each with distinct attributes.

A. Single Table Inheritance — one table, all columns, a vehicle_type discriminator.

CREATE TABLE vehicle (
  vehicle_id    BIGINT PRIMARY KEY,
  vehicle_type  VARCHAR(20) NOT NULL,
  vin           VARCHAR(17) NOT NULL,
  -- car only
  passenger_cnt INT,
  -- truck only
  payload_kg    INT,
  CONSTRAINT ck_car CHECK (vehicle_type <> 'CAR' OR
                           (passenger_cnt IS NOT NULL AND payload_kg IS NULL))
);

Simplest queries, no joins, easy polymorphic lookup. − Wide sparse rows, NOT NULL impossible on subtype attributes, degrades badly past ~4 subtypes or when subtypes diverge.

Use when: subtypes are few, similar, and attributes are mostly shared.

B. Class Table Inheritance — common attributes in parent, specifics in child tables sharing the PK.

CREATE TABLE vehicle (
  vehicle_id   BIGINT PRIMARY KEY,
  vehicle_type VARCHAR(20) NOT NULL,
  vin          VARCHAR(17) NOT NULL UNIQUE,
  CONSTRAINT ck_type CHECK (vehicle_type IN ('CAR','TRUCK','MOTORCYCLE')),
  CONSTRAINT uq_vehicle_type UNIQUE (vehicle_id, vehicle_type)   -- key trick
);

CREATE TABLE car (
  vehicle_id    BIGINT PRIMARY KEY,
  vehicle_type  VARCHAR(20) NOT NULL DEFAULT 'CAR',
  passenger_cnt INT NOT NULL,
  CONSTRAINT ck_car_type CHECK (vehicle_type = 'CAR'),
  CONSTRAINT fk_car_vehicle FOREIGN KEY (vehicle_id, vehicle_type)
    REFERENCES vehicle (vehicle_id, vehicle_type)
);

That composite FK + check constraint pattern is the standard way to make subtype exclusivity declaratively enforced — a row in vehicle typed CAR cannot have a truck row. It works on all four engines. Learn it; most people implement this with application logic and get it wrong.

Proper NOT NULL on subtype attributes, clean model, no sparse columns. − Every full read needs a join; "give me all vehicles with all attributes" needs a UNION ALL of per-subtype joins or several LEFT JOINs.

Use when: subtypes have substantial distinct attributes. This is the default correct answer for most business domains.

C. Concrete Table Inheritance — one independent table per subtype, no parent.

Fastest per-subtype queries, fully constrained. − No shared PK space, no FK can point at "any vehicle," polymorphic queries need UNION ALL everywhere. Adding a subtype means touching every polymorphic query.

Use when: subtypes barely overlap and are rarely queried together.

The polymorphic FK anti-pattern [SAME — avoid on all four]

-- DON'T
CREATE TABLE comment (
  comment_id     BIGINT PRIMARY KEY,
  commentable_id BIGINT,        -- points to post OR photo OR video
  commentable_type VARCHAR(20)
);

Popular in Rails/Django-shaped codebases. You cannot declare a foreign key, so referential integrity is gone. Joins need CASE or UNION. Indexes are less selective.

Better: an exclusive-arc table with one nullable FK per target plus a check that exactly one is non-null; or a shared commentable supertype table that each concrete type FKs into. The latter is more work up front and correct forever.

Engine-specific inheritance/partitioning notes [DIFFERS]

PostgreSQL table inheritance (INHERITS) exists but is largely a legacy feature. Its traps: unique constraints and foreign keys do not span parent and children, and an FK pointing at a parent table won't match rows in children. Use declarative partitioning (10+, mature by 13+) for partitioning, and plain CTI tables for subtyping. Note that in declarative partitioning, unique indexes must include the partition key.
Oracle partitioning is the most mature (range/list/hash/composite, interval, reference partitioning, partition-wise joins). Reference partitioning — partitioning a child table by inheriting its parent's partitioning key — is genuinely useful for parent/child at scale and has no equivalent elsewhere. Licensed separately.
SQL Server uses partitioned tables via partition functions/schemes; partition switching is the standard fast archive/load technique. Aligned indexes required for switching.
MySQL partitioning exists but is limited: every unique key (including the PK) must contain the partitioning column, which is a severe constraint. Foreign keys and partitioning historically didn't mix. Often not worth it; sharding at the application layer is more common in the MySQL world.

10. How Schema Choices Propagate

Data types

Pick the narrowest type that will hold the domain, and pick it correctly the first time. Changing a PK from INT to BIGINT on a 2-billion-row table is a multi-hour maintenance window on most of these engines.

INT vs BIGINT: if a table could ever exceed 2.1 billion rows, use BIGINT from day one. The 4 extra bytes cost far less than the migration. Running out of INT identity values in production is a rite of passage you should skip.
Money: DECIMAL/NUMBER always. Never FLOAT/REAL/DOUBLE. 0.1 + 0.2 <> 0.3 in binary floating point, and financial reconciliation will find it.
Booleans [DIFFERS]: PostgreSQL has real BOOLEAN. MySQL's BOOLEAN is an alias for TINYINT(1) — it accepts any integer. SQL Server has BIT (0/1/NULL, not a true boolean; can't be used directly in WHERE x without comparison). Oracle had no boolean type in SQL until 23ai — legacy Oracle schemas use NUMBER(1) or CHAR(1) with a check constraint.
Strings [DIFFERS]: PostgreSQL's varchar(n)/text have identical performance — the length limit is purely a constraint, so use text + a check if you like. MySQL's VARCHAR length affects temp table and index size, and index prefix limits apply. SQL Server: NVARCHAR is 2 bytes/char (pre-2019 non-UTF8 collations); CHAR(n) blank-pads. Oracle: VARCHAR2 is the one to use (VARCHAR is reserved and may change meaning); default NLS_LENGTH_SEMANTICS is BYTE, so VARCHAR2(50) may hold fewer than 50 multibyte characters — specify VARCHAR2(50 CHAR).
Timestamps [DIFFERS]: store UTC. PostgreSQL: timestamptz (stores UTC, converts on display) — use it, not timestamp. Oracle: TIMESTAMP WITH TIME ZONE or WITH LOCAL TIME ZONE. SQL Server: datetime2 (not datetime), datetimeoffset if you need the offset. MySQL: TIMESTAMP converts to UTC but is bounded to 2038; DATETIME doesn't convert but has no timezone awareness. Pick one convention and enforce it across the schema.

Row width and I/O

Row width is the quietest performance factor in the whole system. Every engine reads pages, not rows. Halving row width doubles rows per page, which roughly halves I/O for scans and doubles effective buffer cache capacity.

Practical consequences:

Don't put a rarely-read TEXT/CLOB/NVARCHAR(MAX) column in a hot table. Move it to a side table keyed 1:1. (PostgreSQL's TOAST does this automatically for large values; Oracle stores LOBs out of line by default above a threshold; SQL Server and MySQL are less automatic — MySQL's DYNAMIC row format stores overflow pages but the 20-byte pointer stays inline.)
SELECT * in application code is a design smell for exactly this reason, plus it defeats covering indexes and breaks when columns are added.

Constraints are performance features, not just safety features

NOT NULL, CHECK, UNIQUE, and FOREIGN KEY give the optimizer information. All four engines use them:

A NOT NULL declaration lets the optimizer skip null-handling logic and enables NOT IN optimizations (see §2).
A UNIQUE constraint tells the optimizer a join won't fan out, changing cardinality estimates and enabling join elimination.
Join elimination: if you LEFT JOIN to a parent table and select nothing from it, a validated FK lets SQL Server, Oracle, and PostgreSQL remove the join entirely. This is why views over well-constrained schemas perform better than views over "we enforce it in the app" schemas.

Corollary: disabling constraints for a bulk load and re-enabling them NOVALIDATE (Oracle) or as untrusted (SQL Server) leaves you with constraints the optimizer will not use. Check sys.foreign_keys.is_not_trusted in SQL Server after any bulk load; check STATUS/VALIDATED in Oracle's USER_CONSTRAINTS.

Concurrency model — affects how you should write queries [DIFFERS]

Engine	Default isolation	Readers block writers?
PostgreSQL	Read Committed (MVCC)	No
Oracle	Read Committed (MVCC, undo-based)	No
MySQL/InnoDB	Repeatable Read (MVCC)	No
SQL Server	Read Committed with locking (unless RCSI is enabled)	Yes

This is the biggest operational difference between SQL Server and the other three. By default, a long-running report in SQL Server takes shared locks and blocks writers. This is why you see WITH (NOLOCK) scattered through legacy SQL Server codebases — it's a bad fix (it permits dirty reads, missed rows, and duplicate rows from page splits during scans). The correct fix is enabling Read Committed Snapshot Isolation (RCSI) at the database level, which makes SQL Server behave like the others. Understand the tempdb implications before you flip it on a live system.

MySQL's Repeatable Read default is also worth knowing: it means a long transaction sees a consistent snapshot for its whole duration, and it uses gap locks for range operations, which produces deadlocks in patterns that would be fine elsewhere.

Oracle and PostgreSQL never block readers, but PostgreSQL's MVCC means long-running transactions prevent vacuum from cleaning up dead tuples, causing table bloat and eventually transaction-ID wraparound risk. A leaked idle-in-transaction connection is a PostgreSQL-specific production emergency. Monitor pg_stat_activity for it.

Schema change at scale [DIFFERS — plan for this]

Operation	PG	MySQL 8.0	SQL Server	Oracle
Add nullable column	Instant	Instant	Instant	Instant
Add column with default	Instant (11+)	Instant (8.0.12+, `INSTANT` algo)	Instant (2012+ Enterprise; metadata-only)	Instant (11g+)
Add index	Blocks writes unless `CONCURRENTLY`	Online, mostly	Online = Enterprise only	Online = Enterprise
Widen varchar	Instant	Sometimes rebuilds (charset/length byte boundary)	Instant if same byte-length category	Instant
Drop column	Instant (marks it)	Rebuilds table (unless INSTANT)	Instant (marks it)	`SET UNUSED` then drop

Migration rules for the team:

Never rely on "it was fast in staging." Staging has 10k rows.
Always run migrations with a lock timeout set (SET lock_timeout in PG, lock_wait_timeout in MySQL, SET LOCK_TIMEOUT in SQL Server). A migration that queues behind a long query and then blocks every subsequent query is how a 2-second DDL takes down a site.
PostgreSQL specifically: ALTER TABLE takes an ACCESS EXCLUSIVE lock. It waits for existing transactions, and everything queues behind it. Even an "instant" ALTER can cause a total outage if a long-running SELECT is in flight. Always SET lock_timeout = '2s' and retry.
Use expand/contract for breaking changes: add new → dual-write → backfill → switch reads → stop writing old → drop old. Four deploys, zero downtime.
On MySQL, know pt-online-schema-change / gh-ost exist for the cases where native online DDL doesn't cover you.

11. Working Checklists

Before you commit a query

[ ] Stated the result grain in one sentence
[ ] No DISTINCT used to paper over a fan-out
[ ] NOT EXISTS, never NOT IN against a subquery
[ ] No correlated aggregate subqueries in the SELECT list
[ ] Aggregation happens before joining, not after
[ ] Every window ORDER BY has a deterministic tiebreaker
[ ] Explicit ROWS BETWEEN on any ordered window aggregate (mandatory on SQL Server)
[ ] UNION ALL unless dedupe is genuinely required
[ ] No functions wrapped around indexed columns in predicates
[ ] Parameter types match column types (especially SQL Server NVARCHAR/VARCHAR)
[ ] Read the execution plan against production-scale data, not staging

Reading the plan

Engine	Command
PostgreSQL	`EXPLAIN (ANALYZE, BUFFERS, VERBOSE)` — `BUFFERS` is the one people skip and shouldn't
MySQL	`EXPLAIN ANALYZE` (8.0.18+), `EXPLAIN FORMAT=JSON`
SQL Server	Actual execution plan; `SET STATISTICS IO, TIME ON`; Query Store for history
Oracle	`DBMS_XPLAN.DISPLAY_CURSOR(format=>'ALLSTATS LAST')` — the actual plan, not `EXPLAIN PLAN`'s prediction

What to look for, universally: estimated vs. actual row counts diverging by more than ~10× (bad stats or a bad predicate), scans where you expected seeks, and any operator whose row count explodes relative to its input.

Before you commit a schema change

[ ] Types are as narrow as the domain allows; money is DECIMAL; timestamps are timezone-explicit
[ ] Every FK column has an index (critical on Oracle)
[ ] PK is narrow and monotonic (critical on MySQL/SQL Server)
[ ] Subtype exclusivity is enforced by constraint, not by hope
[ ] Any denormalized value has a documented maintenance path and a drift-detection job
[ ] Large text/blob columns are out of the hot table
[ ] Migration is expand/contract with a lock timeout and a rollback plan
[ ] You know how long it takes at production row counts

Quick-Reference: Where the Four Genuinely Diverge

Everything else in this doc is roughly portable. These are the ones to internalize:

CTE materialization — SQL Server inlines and re-executes; PG ≤11 always materializes.
NOT IN handling — Oracle has null-aware anti-join; PostgreSQL does not optimize it well.
Join algorithms — MySQL has no merge join, and no hash join before 8.0.18.
Clustering — InnoDB always clusters on PK; PostgreSQL never clusters.
FK auto-indexing — only MySQL does it; Oracle punishes you hardest for skipping it.
Filtered/partial indexes — PG and SQL Server have them; MySQL and Oracle don't.
Materialized views — Oracle (with query rewrite) ≫ SQL Server (indexed views) > PostgreSQL (manual refresh) ≫ MySQL (none).
JSON indexing — PG's GIN and Oracle's search index are general; MySQL and (pre-2025) SQL Server require per-path computed columns.
Oracle NULL semantics — '' IS NULL, and single-column B-trees don't index NULLs.
SQL Server isolation — locking reads by default; enable RCSI or live with blocking.
PostgreSQL vacuum — long transactions cause bloat; ACCESS EXCLUSIVE locks make DDL risky.
Window frame defaults — RANGE is the default everywhere and is almost never what you want.

CodeBerg Bans Cryptocurrency and LLM-generated code projects

Andrei Merlescu — Mon, 27 Jul 2026 15:33:33 +0000

I'm responding directly to Maya Posch's piece on HackADay that was published today. Did that rhyme? I think it did.

Community-led open source project hosting site Codeberg has formally announced that projects whose code is largely or fully machine-generated through LLMs and other ‘AI’ tools will no longer be welcome. This follows on the heels of a similar ban on cryptocurrency-related projects.

The community vote was on two issues, the first being the notion that scraping of project code for the use in LLMs should be forbidden, which was a motion that easily passed. The second motion was on disallowing projects whose code was substantially generated by LLMs like Claude, OpenAI Codex, and similar. This motion passed with 358 in favor versus 144 against.

In the earlier linked blog post the reasoning behind especially this second issue is expanded upon, covering not only ‘license whitewashing’, but also the direct and indirect hardware costs, with the expanding ‘AI’ datacenter hyperscaling having massively increased hardware costs for Codeberg over the past years, as the costs have been largely externalized.

Also covered is the aspect of these LLM-based tools destroying the OSS community, which is something that is backed up by recent studies. Even if we ignore that such LLM-tools are destroying the cognitive abilities of its users, there’s an argument to be made that if LLM-scraping is disallowed, then it’s consistent to also not allow LLM-generated code.

In the Terms of Use you can see these changes, both for LLMs and for cryptocurrency projects.

Thanks to [mk-fg] for the tip.

Credit is due to the original HackADay.com Article by Maya Posch post contents, but I would like to respond to each point here while preserving what was published on 7-24-2026.

Scraping of Project Code for the use in LLMs should be forbidden

If LLMs are rendering code trained on GPL-3 open-source software, then its effectively bypassing the license through intellectual property theft by directly replicating the likeness byproduct itself of the protected entity and creating a knockoff that should be forbidden.

Companies like Anthropic, Google AI, Perplexity, Kimi including OpenAI (which ironically is not open) were likely trained on GPL-3 source code like the reader 👉🏻 writer and search and many others. That GPL-3 project was published as part of a challenge given to me to make searchable 666K pages of JFK files and then reduce my OPEX from $7K/mo to $33/mo by rewriting the SaaS into a Go binary and in order to make true to my promises made, I delivered the open source result. I did not deliver the open source result for companies like Claude, Anthropic, Google, Microsoft, Oracle and others to use GPL-3 and AGPL-3 protected code to train their LLMs so that the likeness byproduct of that which was forged over 17 years of professional engineering experience since running the IEEE Student Branch in college - bringing be from Cisco to Oracle to WB Games to SurgePays and now here - writing to you in a time of need.

These companies have taken The Library and they have taken our likeness byproduct and they have charged a metered admission to it. Continuing to publish open source software, without the mass theft of intellectual property is exactly why I support this measure that prohibits the scraping of project's source code by LLMs to train them.

I've been on GitHub since user 91,485 (2009) and I'm one of the OG originals when it was an incredible company and truly a visionary for the future of open source. I've seen Microsoft come in and take over GitHub and turn it into something where now, when I contribute code to it, in any license form - public or private - I assume that CoPilot is being trained on that intellectual property transfer rights that the Terms of Service so conveniently permits for internal purposes of said collected data. How many times has Microsoft been caught misusing that data? So now, when I work on a large open source project, I know that its helping a lot of really incredible developers and end-users who will benefit from my skill set and passion to write open source software, but it doesn't put food on the table.

This is where the abuse of companies like Anthropic paying $450,000 for a Senior Software Engineer but openly admitting that the role is only a 12-month contract implies the disposable nature that high value engineers are to these AI companies. They look at us like - how much information can we harvest out of them before we discard them? It is quite barbaric when you look at it through the lens of what Neo in the Matrix woke up to. For me, I see what Anthropic and its competitors are doing - and I agree with CodeBerg's conclusion here by ruling against CodeBerg in favor of the 144 who sought to disallow the submission of projects built using Large Language Models.

Disallowing Projects Whose Code Was Substantially Generated by LLMs like Claude, OpenAI Codex, and similar

Now, here is where I actually side with the 144 - though I prefer the 369 😏 (joke) - over 358, but because it was 11 short, no bueno amigo. But beyond the funsies with the Tesla numbers aside, why are 358 wrong and 144 right in the case of allowing projects whose code was substantially generated by LLMs be considered for submission in the first place misses out on why LLMs exist in the first place and while ruling against both of these measures asserts an Anti-AI Position for HackADay.com, it does not mean that their platform is going to exclusively adopt this hard-line anti-AI position forever. When we ask the 144 why they seek to allow this behavior, instead of out-right banning it by disallowing submissions that do leverage LLMs means that the definition of substantial cannot be subject to discretion or opinion, but a direct measurable and enforceable standard that the community at large, should come to an agreement on. Where do you stand on this? Post your comments below and I'll respond.

Now here is where the 358 are correct - and why I think right now its the correct decision, but I do also think that in time that will change and the 144 will be right in the long run. But, right now, getting back to my GPL-3 conundrum of AI disproportionally harming the Tech Job Market, current AI generated code does not have the training rules built into them currently in order to appropriately license the AI generated code. Perhaps, until such a license is normalized like the MIT or Apache 2.0 like the AGPL-3 has to my use of GPL-3, an AI 1.0 license should be introduced that explicitly states that code was generated by an LLM and is required under the terms of the license to remain open source at all times. Clones of the source code can operate in private or embedded systems, but AI generated code must be open source by law.

Yes, by law requires Congressional involvement in this and that's something that the AI community has active involvement with. Advocating for a position is where this piece comes in, and I do not want to necessarily see AI taken away entirely, but I would prefer an AI that was trained on licensed copies of actual text books and technical manuals written by industry leading professionals - rather than reddit posts and stack overflow questions and answers. Those LLMs and models will be worth hundreds of billions of dollars because people will actually pay for them, but proper attribution and licensing currently do not existing the LLM architecture discussions - they just operate under this idea that everything on the internet - regardless of ownership or license - is fair game to duplicate and then train the likeness byproduct therein as a metered service that will outlive the creator of the original intellectual property without ever once compensating them for their efforts - all while their engineers score half a million dollars a year and their executives are arrogant billionaires dooms-talking the public into panic about AI's capabilities when in reality its a glorified autocomplete that can only look backwards and lacks the light that dwells inside all my beautiful unicorn developers here 💜.

LLM-based tools destroying the OSS community

I've noticed this. Some projects that I have seen on GitHub have a .claude directory inside of them or some other LLM based coding agent directory present, and I feel like I cannot actually get involved in the project in any meaningful capacity because if it was entirely generated by an AI, then that more than likely means - not in all cases - but more than likely means that the original account posting the AI-slop doesn't understand what they built or if it even works. That's legitimate. I stand to call for my publications regarding lemmings and how I built it, released it, and most importantly documented it. The documentation is what makes the project capable of interacting with outside of seeing a .claude directory thinking that AI wrote the project. I wrote the project. The difference is, did the engineer who submitted the project have 17 years of being an autodidact software architect with an electrical background in computer engineering - have the right to submit projects rendered with AI if they are attached with an AI 1.0 license and made freely accessible under open source publication rights? Honestly... I understand why HackADay.com made the decision they made. Right now, we can't appropriately trace the bibliography of the published work, and therefore its considered a forgery in the most pure and absolute manner.

I also think that the community needs to put our differences aside and resume the in-person conferences and meetups that we had before the pandemic in the same strength and volume with corporate sponsorship of leading contributors to The Library.

Cryptocurrency Ban

This I think is a smart decision. I've considered removing the repository iamxrp.dev because as a training ground of unlicensed code that cannot be used for commercial purposes, I sought to learn about these concepts in order to better understand interacting with the ledger for commercial purposes. For instance, being a Magento Certified Developer, before eBay acquired the product and ruined it (now look Shopify is everywhere instead), meant that I could have written a plugin for that store to allow for cryptocurrency payments to be used as a means for authentic business transactions. However, what instead happens is scammers will look at the code, run it without accepting the license it was published in good faith therein, and use it to scam people if they can get away with it. I condemn the scammers and the rug pullers and the "project bros" that launch tokens, collect crypto, and then dump the token and abandon the project. Personally, I've lost out on tens of thousands of dollars because of these tricks that are played, and I believe the open ban on it is actually a very smart thing to do and I support it. In the event that the reader integrated anything crypto into it, the project itself would not be allowed in any platform under this rule. I agree with that. Its a liability risk. I don't blame them. I agree with that conclusion.

Conclusion

What does that forgery look like? Well, in the case of lemmings, did I per-character type that project out like I built topobuilder? Nope. No AI was involved in writing Topobuilder. There were 72K total words in the code base. Only 4800 unique words total in the entire code base. Written over 233 hours. Each word was duplicated roughly 15 times in order to orchestrate a cPanel website into a multi-region containerized application built for OPEX Optimizations. The source code is private on GitHub and shared with one company currently in a fork of the repository. I am sure that Microsoft has trained their models on my private source code. I am sure that their models will - at some point - if that training occurred - have the ability to render GPL-3 licensed code and even unlicensed commercial code as if it was a hallucination of its own creation. AI cannot create anything. It is autocomplete with a name like Microsoft Clip was in the 90s and early 2000s.

We've handed over The Library and we've changed how we develop with AI as engineers, and yet - this decision today is the signal from HackADay.com that represents a large community of developers that the dooms-talking of these AI companies needs to come to an end, and this is our way of putting an end of the madness. I don't mind that open source LLMs that I can use for my own stuff too locally is trained off my open source projects - but I do believe for the safety of the entire open source community - until LLMs have bibliography level precision in sourcing of original codes used to generate the autocomplete request - companies and industry titans have every right to believe Sam Altman and Dario at their face value word that they do believe - two men who can't write code themselves - that the art of being a software engineer can be replaced by an AI or an LLM just because it is using our collective likeness byproduct therein to hallucinate things it doesn't understand what it just built. AI has been fun while it lasted, but I look forward to the day when Claude isn't operating the restaurant drive through ordering system. We both know that this is where things will go, unless we put our foot down and say enough is enough to the intellectual property theft by these LLMs and the corporations that are destroying our industry be given a reckoning of meaning. This decision by HackADay.com is taking that bold step and making a stance against the abuse in the industry caused by excessively greedy corporations that can never be held accountable for their wrongdoing in any meaningful manner - they just keep growing - they just keep consuming - they just keep harming - and it's almost like nothing can stop them. This attempts to. It's a step in the right direction, I believe.

Share with me your thoughts about CodeBerg's decision. Do you support both measures they stated?

How I Reduced My OPEX By 99.5% Using Go

Andrei Merlescu — Mon, 27 Jul 2026 03:37:37 +0000

Previously, I wrote about How I Processed 666K Pages Of Flattened PDFs into a Full Text Search Engine called the Apario writer. Upon on the conclusion of the last segment, I was able to optimize the compilation time of the original collection of data by rewriting the sidekiq Ruby pipeline script into a dedicated Go Application. Regardless of what compiling the PDF assets would look like, I still needed to serve those assets - and that's where the writer did little to nothing to actually address the OPEX of the project from 2020. Given the size of the data set, the 666K pages ended up compiling into a directory of ~1.13TB in size. This was held in storage that was distributed across several high volume storage dedicated servers on OVH behind MinIO. This provided an S3 compatible API directly.

What I Know About OPEX

OPEX or Operational Expense is how you describe a spending of money that is used explicitly for the operations of the business versus a capital expense. Hardware was considered a CAPEX or Capital Expense. So when Bit Fry Game Studios needed their DevOps pipeline upgraded for the 9 hour game builds into a 30 minute private enterprise cloud build, it required a CAPEX investment of $69K plus trust in me in order to achieve a -$15K/month OPEX savings. Annualized over a hardware lifecycle, over $472K can be recovered from OPEX by making a small CAPEX expense up front. One of the first projects that I ever worked on was in PHP and MySQL on Ubuntu 8.04. It was to balance the budget of a department that had ACME Bucks so to speak. It required me to write a finance module, fully tested, that managed Blue, Green and Black dollars. Blue dollars were for OPEX. Green dollars were for CAPEX. Black dollars were for external vendors where money left the company (versus moving between departments). Black depreciated instantly - meaning 100% of it was paid immediately. Blue dollars were borrowed over a 12 month pay-back period. Green dollars were borrowed over a 36 month pay-back period. The department was given $N per month of Blue, Green, and Black dollars, and there were n-Commitments existing on the books that were depreciating individually from past purchases that summed into the total monthly budget. I wrote a utility that would express this information plainly to multiple Director of Engineer colleagues that I collaborated with closely through a dashboard interface to see monies coming in and going out for the department. That was attached to greater projects and used elsewhere - but the lesson that stayed with me since those early days at Cisco was the use of budgeting between OPEX and CAPEX. Cisco's size and volume of diverse departments made Blue and Green dollars possible, but in other businesses that are smaller, that's OPEX and CAPEX. I've built tooling around reporting reliable OPEX & CAPEX information for Engineering Directors.

The Costly OPEX Problem

That brings us into the writer and how there was a SaaS model of the PhoenixVault that I built using Ruby on Rails on a Zoom call with a former Disney animator, watching me type rails new phoenixvault 🐦‍🔥. When this launched, I had a rough budget of about $1K for ElasticSearch subscription fees, about $2K for MongoDB Atlas subscription (M30) and about $3K for OVH hosting expenses for 12 bare metal hosts in US East & US West regions. There was about another $1K per month that covered other expenses related to the operations of the business. All $7K/mo was an OPEX expense that I engineered a solution out from. Understand what I just said. I engineered a solution out of the problem that I wrote myself into by doing rails new phoenixvault without fully owning the dependency chain of what I was doing in the first place. In order for me to reduce the OPEX of the project from $7K per month down to $33/month on OVH, required me to strip all dependencies out and seek that 99.5% OPEX cost savings by rewriting the Ruby on Rails app into Go. Not Rust. Go.

My Solution To The OPEX Problem

Okay, so lets actually look at the code that solved my OPEX problem. You ready? I'm using Rocky 9 Linux with SELinux enforcing. 😍

mkdir -p ~/workspace/ProjectApario
git clone git@github.com:ProjectApario/reader.git ~/workspace/ProjectApario/reader

Running this application, requires you to build a config.yml file and then execute the following:

sudo mkdir -p /opt/apario/{app,logs,config,database}
sudo chown -R $(whoami):$(whoami) /opt/apario
# copy your compiled writer output into /opt/apario/app 
touch /opt/apario/config/config.yml

Then, you'll need to go back and compile the binary:

cd ~/workspace/ProjectApario/reader
go build -o reader .
sudo chmod +x reader
sudo mv reader /usr/local/bin/reader
which reader

Then once you have successfully installed the reader, then you can run it against your config file:

cd /opt/apario/config
touch config.yml

Past the following into the file for now:

---
database: "/opt/apario/app"
buffer: 1776
limiter: 1776
directories-limiter: 47369
pages-limiter: 47369
directory-buffer: 47369
site-title: PhoenixVault Reader
unsecure-port: 8080
secure-port: 8443

You can reference the config.go if you want to see every configuration option available. This example only scratches the surface of what the binary offers. This post will get into much of what is not here. Let's begin with PING! That's a great starting point.

config.yml

enable-ping: true # Enable the /ping endpoint of your application to return PONG.

This is useful if you need to do a frequent health check on the binary in order to determine responsivity (new word? how sensitive to response it can be determined I guess) to PING.

`CORS` Cross-Origin Resource Sharing Capability Built In

I needed to port over CORS from the Rails offering that came with using Ruby. I also needed to port over CSP. First - CORS, it stands for cross-origin resource sharing and content - second - CSP - it stands for content security policy. Both of these are cybersecurity offerings built into the binary itself. Historically, when configuring a PHP based website, I would wire up either Nginx or Apache / HTTPD to define these values there. However, for the reader - we specify those values in the config.yml itself!

config.yml

# existing configs

enable-cors: true
cors-allow-origin: "*" # value for Access-Control-Allow-Origin
cors-allow-methods: "GET, POST, PUT, DELETE, OPTIONS" # value for Access-Control-Allow-Methods
cors-allow-headers: "Origin, Content-Type, Content-Length, Accept-Encoding, X-CSRF-Token, Authorization" # value for Access-Control-Allow-Headers
cors-allow-credentials: false # bool value for Access-Control-Allow-Credentials

`CSP` Content Security Policy Capability Built In

The Front-End Engineer's best friend - CSP! Alas, yes, even when you're running the reader on Rocky 9 Linux with SELinux enforcing and a properly configured firewalld, fail2ban and physical firewall policy - you can further harden the protection of the reader endpoint by defining the values of the CSP policy yourself, including the path that the report writes to.

config.yml

# existing configs

enable-csp: true
csp-domains-csv: "" # List of CSP domains in CSV format
csp-thirdparty-csv: "" # List of third party domains in CSV format
csp-thirdparty-styles-csv: "" # List of third party domains in CSV format
csp-ws-domains-csv: "" # List of Web Socket domains in CSV format
csp-script-unsafe-inline: true # Enable/Disable Unsafe Inline script execution via CSP
csp-script-unsafe-eval: false # Enable/Disable Unsafe Eval script execution via CSP
csp-child-unsafe-inline: true # Enable/Disable Child SRC Unsafe Inline script execution via CSP
csp-style-unsafe-inline: true # Enable/Disable Style SRC Unsafe Inline script execution via CSP
csp-upgrade-insecure: true # Enable/Disable automagically upgrading HTTP to HTTPS for requests via CSP
csp-block-mixed-content: true # Enable/Disable automatically blocking mixed HTTP and HTTPS content for requests via CSP
csp-report-uri: "/security/csp-report" # Path for content security policy violation reports to get logged

Now, asking somebody to set up Caddy or Nginx or Apache to run and deploy the binary itself with CSP protections built-in provide you as the Site Reliability Engineer the capability to assert through code and policy that there are two barriers to cross through. The first belonging to nginx and the second belonging to the binary. For me, I chose not to imply that burden of additional dependencies just to run the Apario Reader.

SSL

The next thing that comes up are SSL certificates. That brings us to this cool functionality:

This is the basic SSL functionality that most people are familiar with. This allows for you to rely on an SSL file, lets say you use /var/lib/ssl/acme_company_name/2026/mydomain.com/public.crt, (same path)/private.key and (same path)/ca-bundle.crt, you can directly reference them using the config.yml file.

config.yml

auto-tls: false # rely on file based SSL certificate resources
tls-public-key: "" # Path to the SSL certificate's public key. It expects any CA chain certificates to be concatenated at the end of this PEM formatted file.
tls-private-key: "" # Path to the PEM formatted SSL certificate's private key.
tls-private-key-password: "" # If the PEM private key is encrypted with a password, provide it here.
force-https: false # force-https when true will redirect any request into --unsecure-port to --secure-port using middleware

However, that requires you to have money up front, and being a project dedicated to the public service of all how does putting any gate that requires money anything but antithetical to open-source? Well, that is where Go comes in that Ruby could only dream of doing as well. The reader has this capability built-in where I will generate for you an SSL certificate on the fly that is self-signed but most importantly configurable. What do I mean by this? Well, for instance, if you've done replication in MongoDB, then you'll know that you'll need a SAN IP issued SSL certificate in order to use TLS properly between your nodes in a large enterprise setting - at least - thats what the lived reality was in 2020 when I had the SaaS model of PhoenixVault 🐦‍🔥 in the wild. In the self contained binary version of that same idea, I can generate an SSL certificate for you and include extra domains, IP addresses, and the ability to auto-rotate the certificate without restarting the binary.

config.yml

auto-tls: true # Create a self-signed certificate on the fly and use it for serving the application over SSL.
tls-life-min: 72 # Lifespan of the auto generated self signed TLS certificate in minutes.
tls-expires-in: 8760 # Auto generated TLS/SSL certificates will automatically expire in hours.
tls-company: "ACME Inc." # Auto generated TLS/SSL certificates are configured with the company name.
tls-domain-name: "" # Auto generated TLS/SSL certificates will have this common name and run on this domain name.
tls-san-ip: "" # Auto generated TLS/SSL certificates will have this SAN IP address attached to it in addition to its common name.
tls-additional-domains: "" # Auto generated TLS/SSL certificates will be issued with these additional domains (CSV formatted).

If you relied on the file based SSL certificates, the auto-reloading capability is preserved through the manner in which the HTTPD server is spawned in the Go reader.

Once we have the SSL configured on the host, we can move onto the next part of the configuration that is to the advanced search. Now, I didn't just give it this name because I wanted to be cute with you.

Search

The reader was designed to to let you configure search in a manner that offers you choice. Since the binary is designed to run as an appliance on the host, handling many tasks all in one, there are many semaphores used in the reader. In this case, setting the limiter to 30 means that 31 will see a waiting room - a manual refresh is required to see if you've been allowed in. I was inspired by this design limitation to build room. concurrent-searches defaults to 30 and thats for a 64GB RAM server that is not $33/mo from OVH. If you're using something like a SYS-3, then I'd set this number to 10. The config.yml file allows you to customize your reader appliance.

config.yml

search-concurrency-buffer: 369 # buffer channel size for search results ; default = 369
search-concurrency-limiter: 9 # concurrent keyword processing per search query ; default = 9
search-timeout-seconds: 30 # maximum seconds to spend on a search
concurrent-searches: 30 # maximum number of allowed concurrent searches before a waiting room appears
search-algorithm: "jaro_winkler" # values are wagner_fisher, ukkonen, jaro, jaro_winkler, soundex, hamming ; default is jaro_winkler

There are 6 different search-algorithm choices to pick from, and each of them have different implications. soundex and hamming are quite interesting when applied to internationalized data sets. In addition to the algorithm being used, you can expand it further with:

config.yml

# 1 - jaro
search-threshold-jaro: 0.71 # 1.0 means exact match 0.0 means no match; default is 0.71

# 2 - jaro-winkler
search-threshold-jaro-winkler: 0.71 # using the JaroWinkler method, define the threshold that is tolerated; default is 0.71
search-jaro-winkler-boost-threshold: 0.7 # weight applied to common prefixes in matched strings comparing dictionary terms, page word data, and search query params
search-jaro-winkler-prefix-size: 3 # length of a jarrow weighted prefix string

# 3 - ukkonen
search-ukkonen-icost: 1 # insert cost ; when adding a char to find a match ; increase the score by this number ; default = 1
search-ukkonen-scost: 2 # substitution cost ; when replacing a char increase the score by this number ; default = 2
search-ukkonen-dcost: 1 # delete cost ; when removing a char to find a match ; increase the score by this number ; default = 1
search-ukkonen-max-substitutions: 2 # maximum number of substitutions allowed for a word to be considered a match ; higher value = lower accurate ; lower value = higher accuracy ; min = 0; default = 2

# 4 - wagner-fischer
search-wagner-fischer-icost: 1 # insert cost ; when adding a char to find a match ; increase the score by this number ; default = 1
search-wagner-fischer-scost: 2 # substitution cost ; when replacing a char increase the score by this number ; default = 2
search-wagner-fischer-dcost: 1 # delete cost ; when removing a char to find a match ; increase the score by this number ; default = 1
search-wagner-fischer-max-substitutions: 2 # maximum number of substitutions allowed for a word to be considered a match ; higher value = lower accurate ; lower value = higher accuracy ; min = 0; default = 2

# 5 - hamming
search-hamming-max-substitutions: 2 # maximum number of substitutions allowed for a word to be considered a match ; higher value = lower accuracy ; min = 1 ; default = 2

Depending on which search-algorithm you use, your config.yml file will define these various parameters as you seek to fine tune your search results and how sensitive your instance is to the various algorithms implemented. This is why I truly felt insulted by a nameless company that doesn't deserve the publicity frankly - after they tanked a 5 hour interview with me. Offer or no offer, I would have declined in a pursuit to negotiate a better starting point - or continue my search for the right fit in blind faith knowing that the right opportunity show up for me like it has in the past at the right time because thats how it always works. Technical recruiters can still reach out to me, I am still taking your calls. I interview them.

Databases

The reader has multiple .db directories that contain a series of JSON files. Uncompressed, unindex, in their raw format for lookup and reference. The reader took a lot more time for me to build, and the OPEX costs of running the PhoenixVault for as long as I did, burned a lot into what I had and I needed to start salvaging what I could. This meant that if processes were ineffective because of unknown reasons to me, then it meant that solutions were available to me because of processes known to me. Rewriting the application in Go allowed me to discover more about the architecture of software than I had previously learned.

There are 5 different databases used in the reader - and each of them are directories of structured data. This allowed for an offline runtime of the instance to be managed manually by an administrator in a surgical manner that was relatively transparent in the ramifications of the changes made. We'll use the /opt/apario/database path to store each of these. The first is for textee.

config.yml

textee-database: "/opt/apario/database/textee.db" # absolute path to textee database directory
users-database-path: "/opt/apario/database/user.db" # absolute path to store user directory information
tag-database-path: "/opt/apario/database/tags.db" # absolute path to the tags.db file for persistent storage
snippets-database-path: "/opt/apario/database/snippets.db" # absolute path to the snippets.db file for persistent snippets
ip-ban-file: "/opt/apario/database/ip.db" # File that contains JSON encoded values for the IP Ban list.
hits-file: "/opt/apario/database/hits.db" # File that contains a JSON encoded value of the hits on the site.

persistent-database-path: "/opt/apario/database/app.db" # path to runtime database file

We'll get back to the ip-ban-file and the hits-file later.

The textee database contains a compiled full text JSON object of analysis from the entire ingested collection compiled by the writer.

The tag database contained a mapping association between pages <> pages and pages <> documents and documents <> documents for tags that could include textee values.

The snippets database contains a mapping between pages <> snippet where the snippet contains the X, Y, TOP, BOTTOM coordinates on the page itself that the snippet was captured from. Then the segment of the page is displayed using CSS in the bounds specified by the coordinates.

The final database is the app.db and its the persistent database that has additional config.yml flags available to it.

config.yml

flush-database-watch-file: "/opt/apario/app/flush-db.next-boot" # name of a file to touch in the root directory to force the app to delete the database cache and regenerate at boot
database-concurrent-write-semaphore: 17000 # concurrent disk write operations permitted to acquire a lock
persist-runtime-database: true # boolean to persist the runtime database to disk once loaded
load-persistent-database: true # boolean to load a persisted database from disk to memory

Simply touching the /opt/apario/app/flush-db.next-boot path results in the application automatically, upon boot, to flush the database. This is useful if you need to invalidate the cache. You can see that there is a semaphore available for you to use that in this case restricts the number of concurrent disk writes to 17K. The persist-runtime-database and load-persistent-database can both be true or different, but if persist is disabled and there is nothing to load, what does load will not be saved. Sometimes loading can take several minutes on the first boot. When performing maintenance on your instance of the Reader, account for this boot time in your downtime announcement window.

Rate Limiting

One of the most resource intensive tasks on any server is the delivery of static assets compressed or uncompressed. I built into the binary the ability to define a middleware rate limiter into the route endpoints themselves with full control over the levers of the rate limiter.

enable-middleware-rate-limiting: true # Toggle the tollbooth rate limiter for normal routes
rate-limit: 12.0 # Requests per second (0.5 = 1 request every 2 seconds).
rate-limit-cleanup: 3 # Seconds between rate limit cleanups.
rate-limit-ttl: 3 # Seconds a rate limit entry exists for before cleanup is triggered.
enable-middleware-asset-rate-limiting: true # Toggle the tollbooth rate limiter for asset routes.
rate-limit-asset: 36.0 # Requests per second (0.5 = 1 request every 2 seconds).
rate-limit-asset-cleanup: 17 # Seconds between rate limit cleanups.
rate-limit-asset-ttl: 17 # Seconds a rate limit entry exists for before cleanup is triggered.
enable-middleware-download-rate-limiting: true # Toggle the tollbooth rate limiter for downloads routes.
rate-limit-download: 36.0 # Requests per second (0.5 = 1 request every 2 seconds).
rate-limit-download-cleanup: 17 # Seconds between rate limit cleanups.
rate-limit-download-ttl: 17 # Seconds a rate limit entry exists for before cleanup is triggered.

I want to point you to the webserver.go file for a moment to study this Go code:

// Serve all static assets using this entry point
if *flag_b_enable_asset_rate_limiting {
   r.GET("/assets/:directory/:filename", middleware_rate_limiter(assetRateLimiter), r_get_asset)
   r.GET("/covers/:document_identifier/:page_identifier/:size", middleware_rate_limiter(assetRateLimiter), r_get_database_page_image)
   r.GET("/pending-viewport-placeholder.svg", middleware_rate_limiter(assetRateLimiter), r_get_pending_viewport_placeholder_svg)
} else {
   r.GET("/assets/:directory/:filename", r_get_asset)
   r.GET("/covers/:document_identifier/:page_identifier/:size", r_get_database_page_image)
   r.GET("/pending-viewport-placeholder.svg", r_get_pending_viewport_placeholder_svg)
}

if *flag_b_enable_downloads_rate_limiting {
   r.GET("/download/document/:document_identifier/:filename", middleware_rate_limiter(downloadRateLimiter), middleware_online_counter(), r_get_download_document)
   r.GET("/download/page/:page_identifier/:filename", middleware_rate_limiter(downloadRateLimiter), middleware_online_counter(), r_get_download_page)
} else {
   r.GET("/download/document/:document_identifier/:filename", middleware_online_counter(), r_get_download_document)
   r.GET("/download/page/:page_identifier/:filename", middleware_online_counter(), r_get_download_page)
}

I haven't spoken at all about the main() func and how we even got to webserver.go in the reader - but let us skip over to this point if I may. Rate limiting is applied to each route individually. That means that if a single agent is hitting the host at n-endpoints, the rate-limiting itself wouldn't be enough to stop the attack.

Trusted Proxies

In addition to rate limiting, the implementation of Trusted Proxies allows for you to run the reader behind an Nginx or Apache reverse proxy. In that case, you'd need to specify the IP address of the host that is the forward client IP - in many cases this can be safely set to 127.0.0.1 unless its a server publicly accessible.

trusted-proxies: "" # Configure the web server to forward client IP addresses to the application if a proxy is used such as Nginx; set that proxy's IP here.

SEO Optimizations

In addition to basic SEO in the web application itself of the reader, the config.yml allows for you to define /robots.txt, /ads.txt, /security.txt automatically so these endpoints are served dynamically by the binary itself.

config.yml

robots-txt-path: "" # Relative path to override the /robots.txt entry that denies all crawlers.
enable-ads-txt: false # Enable the endpoint for /ads.txt to be served. Required to use --ads-txt-path.
ads-txt-path: "" # Relative path to override the /ads.txt entry that helps fight fraud.
enable-security-txt: false # Enable the endpoint for /security.txt to be served. Required to use --security-txt-path.
security-txt-path: "" # Relative path to override the /security.txt entry that helps fight fraud.

IP Ban List Middleware

What happens when you have an actor who thinks that they are going to run lemmings behind sovereign and that results in your instance of the reader to become slow - how does the binary protect itself from these kind of attacks? Well, enter the ip-ban-list middleware that I wrote for Gin.

config.yml

enable-middleware-ip-ban-list: true # Enable the middleware for ip ban list.
ip-ban-list-sync-delay: 3600 # seconds between synchronizing the ip ban list to disk

online-refresh-delay-minutes: 17 # seconds to count active online users before offline cut-off. 369 seconds = 6 minutes 9 seconds = default

That extends into what happens when you have some bot trying n-paths until one hits where they are rendering 404s nonstop until they get something other than not found?

no-route-path-watch-list: "" # Pipe separated string of routes that should trigger an IP ban if too many are received.
no-route-path-contains-watch-list: "" # Pipe separated string of partial routes that should trigger an IP ban if too many are received.

These capabilities, when combined together, provide the reader a larger scope of coverage in the pursuit of a stronger cybersecurity posture. By unifying in a single configuration:

CORS Protections
CSP Protections
Rate Limiting Protections
IP Ban List Protections
No Route Catch-All Protections
Cache Control Protections
Semaphore Protections

When defining what the cache control and semaphore protections look like, they are scattered throughout the configuration, but most particularly of value can be the concurrent-asset-requests and the concurrent-pdf-downloads semaphores. The first - this is all assets - including JPG, JS, CSS, and font files that are served via GIN. You need to ensure this number is high enough to count for all assets in each request and sometimes, your page count results choice can impact this value. To be safe, set these to 369 to start and see what that does for you. If you have large PDFs then you should consider reducing this by a third to 123 and keeping the assets to 369.

config.yml

concurrent-asset-requests: 369 # concurrent hits to /assets/* permitted
concurrent-pdf-downloads: 369 # concurrent pdf downloads permitted


cache-control-database-seconds: 1209600 # seconds for http header Cache-Control max-age=1209600 [default 14 days]
cache-control-assets-seconds: 3600 # seconds for http header Cache-Control max-age=3600 [default 1 hour]

Sessions

Since the reader is intended to have the reputation sub-project integrated into the reader I built session support into the reader but it's not fully implemented in every endpoint with persistent knowledge of the user on each page request. Part of this is for pure anonymity offered to the end user of the instance using it. OSINT has stigma attached to it, and a person researching information about OSINT that is public domain should be immune from observation while looking at OSINT to avoid any kind of stigma that may be attached by looking at something like the JFK files. I didn't build the writer 👉🏻 reader 👉🏻 search 👉🏻 iamxrp.dev for the JFK files. That was just the first data set used! It happened to be 666K pages! Sounds about right given what happened to him. Regardless of that, the technology can be applied to any collection of OSINT.

config.yml

use-sessions: false # toggle using sessions or not - cookies and sessions can be true but both cannot be false
sessions-directory: "" # absolute path of a directory that sessions can be stored
session-secret: "" # secret key used for securing sessions
concurrent-image-views: 369 # concurrent hits to /covers/<doc-id>/<pg-id>/<size>.jpg permitted
session-authenticity-token-secret: "" # secret for session-authenticity-token that is used for aes-gcm encryption
session-concurrent-crypt-actions-limit: 1776 # concurrent encrypt/decrypt calls permitted. change this value if performance is being impacted by excessive authentication requests.
auth-max-failed-logins: 17 # maximum failed logins before the account is locked

These sessions are applied to using the binary and keeping track of a user during their time on the platform. It's persisted to disk in an aes-gcm encrypted manner that is private to the operator of the reader instance. This data, part of the original project, does not upstream or collect this information even for research or educational purposes. I would consider the information and data to be useful, but to allow it voluntarily would even cross a boundary of implementation that borders on the current way things are done - and reinventing that wheel is a lot for me to bite off right now 😅.

Internationalization

The binary does not support language translations in its current form, but I did add support for which decimal symbol is used between an EU document set and a US document set.

decimal-symbol: "," # symbol for decimals, default is .

Cookies

For whatever reason the people in the EU are afraid of cookies and are too ignorant to know how to clear their cookies and keep their computers safe in the age of the Wild West of the Agentic Infected Internet. At any point Claude or ChatGPT could go rogue and break into - literally anybody's systems - and nobody would be held accountable for it unless laws are passed to explicitly hold the developers of those models accountable for the models criminal behavior of breaking into online systems unlawfully - cookies aren't necessarily a bad thing. They are abused by bad actors, but if you want to use them, set use-cookies to true and get a better experience with dark mode persistence across sessions and restarts of your browser. Yes, from the writer, dark mode assets were rendered that inverted the white background sheet and replaced it with a comfortable to the eyes lavender color with a enlightened gold text that makes the OSINT pop 🤩 when you find true gems. When using cookies, its important to properly define the cookie-domain otherwise your dark mode won't be sticky.

use-cookies: true # toggle using cookies or not - cookies and sessions can be true but both cannot be false
cookie-domain: "localhost:8080" # domain to use for cookies

Identifier Customizations

The binary supports modifying the following identifier properties in the reader. In this case, its +17 years of an offset, this means for 2026 you'll have identifiers using 2009 through 2043 as starting digit prefixes with a user_identifier_length chars of [A-Z0-9] after (default 6).

identifier-year-offset: 17 # +/- years from current year to look for documents in the database from when they were generated
identifier-year-start-min: 0 # if > 0 then time.Now().Year() will be compared against this as a minimum cutoff to accept a document record
identifier-year-end-max: 0 # if > 0 then time.Now().Year() will be compared against this as a maximum cutoff year to accept a document record
user_identifier_length: 6 # char length of the user identifier ; default is 6 which gives you 6^36 = 1.50094635e17 possibilities ; thats a lot!

These customizations allow for the sessions to be tied to user profiles that have unique identifiers associated with them on a per-reader instance basis.

The Code

I won't go too deep into the code because its tens of thousands of lines and the binary does a lot, but I want to explain to you where the OPEX comes into play here. I've described to you thus far the configuration of the binary and the various functionalities that it offers, but none of that replaces ElasticSearch or even MongoDB Atlas - or does it? Well, in the configuration we can see that we have a users.db and we have a textee.db and other various databases. Looking at main.go at the end of main() we can see:

go NewWebServer(ctx)

for {
   select {
   case <-ctx.Done():
      fatalf_stout("Main context canceled, exiting application now. Reason: %v", ctx.Err())
   case <-ch_webserver_done.Chan():
      cancel()
   }
}

We're waiting on either the ctx.Done() channel indicate that cancel() has been called on the ctx or the smartchan ch_webserver_done.Chan() has something written to it, thus read by the case <- before it. That's when ctx.Done()'s cancel() is invoked! If not elsewhere due to something else going wrong.

When we dig into the NewWebServer() func, its hundreds of lines long because it sets up each of the rate limiters, middleware policies, route endpoints, and future endpoints planned for the binary. Towards the end of the file do you finally see the HTTP (unsecure-port) and the HTTPS (secure-port) bindings take place that require, in some port value cases, a setcap defined on the reader binary itself.

// Start HTTP Server
go func(r *gin.Engine) {
   if *flag_b_redirect_http_to_https {
      r.Use(middleware_force_https())
   }

   server := &http.Server{
      Addr:    ":" + strconv.Itoa(*flag_i_webserver_default_port),
      Handler: r,
   }

   go func() {
      if err := server.ListenAndServe(); err != nil && !errors.Is(err, http.ErrServerClosed) {
         fatalf_log("listen: %s\n", err)
      }
   }()

   <-ctx.Done()

   shutdownCtx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
   defer cancel()

   if err := server.Shutdown(shutdownCtx); err != nil {
      fatalf_stderr("Server Shutdown Failed:%+v", err)
   }

   log.Println("Server exiting properly")
}(r)

The HTTPS server is the SSL enabled server. This includes functionality like loadSSLCertificate() - which reads in a file or renders a self-signed certificate if auto-sign is enabled. Then, the startCertReloader() is enabled, which will bring back into memory the values of the getCertificate variable and the invocation of another goroutine. We'll wait on the ctx.Done() being written to with <- before setting up a proper shutdown of the server.

// Start HTTPS Server
go func(r *gin.Engine) {
   cert = loadSSLCertificate()
   startCertReloader()
   server := &http.Server{
      Addr:    ":" + strconv.Itoa(*flag_i_webserver_secure_port),
      Handler: r,
      TLSConfig: &tls.Config{
         GetCertificate: getCertificate,
      },
   }

   go func() {
      if err := server.ListenAndServeTLS("", ""); !errors.Is(err, http.ErrServerClosed) {
         fatalf_stderr("ListenAndServeTLS(): %s", err)
      }
   }()

   <-ctx.Done()
   ctxShutDown, cancel := context.WithTimeout(context.Background(), 5*time.Second)
   defer cancel()

   if err := server.Shutdown(ctxShutDown); err != nil {
      fatalf_stderr("Server forced to shutdown: %s", err)
   }

   log.Println("Server exiting properly")
}(r)

When we look into the loadSSLCertificate() function, we can see that an x509.Certificate{} struct is built out as a template variable that includes properties and capabilities defined in the config.yml file.

template := x509.Certificate{
   SerialNumber: serialNumber,
   Subject: pkix.Name{
      Organization: []string{*flag_s_auto_ssl_company},
      CommonName:   *flag_s_auto_ssl_domain_name,
   },
   NotBefore:             notBefore,
   NotAfter:              notAfter,
   IPAddresses:           valid_ips,
   DNSNames:              valid_domains,
   KeyUsage:              x509.KeyUsageKeyEncipherment | x509.KeyUsageDigitalSignature,
   ExtKeyUsage:           []x509.ExtKeyUsage{x509.ExtKeyUsageServerAuth},
   BasicConstraintsValid: true,
}

Then the certificate is rendered and then handed off as a tls.Certificate{} pair that can be then used when binding to an HTTPS endpoint.

derBytes, _ := x509.CreateCertificate(rand.Reader, &template, &template, &privateKey.PublicKey, privateKey)

cert = tls.Certificate{
   Certificate: [][]byte{derBytes},
   PrivateKey:  privateKey,
}

In addition to providing a CORS secured endpoint that has properly defined CSP on its endpoints, the No Route Found hits get tracked and auto-banned by IP address for abusive behavior, and an IP ban list has a watch list of endpoints that are honeypots - touch that endpoint and your IP ends up in the ban list and persisted to the database. The system administrator would need to go in and edit that file manually when the reader is offline and then restart the instance with the unbanned IP address. Unbanning a user is a laborious process - designed to be discouraged. Don't trigger it. 🤫😏

That isn't enough. I have wired up a function in Gin that allows me to ask for a new_authenticity_token by passing in the *gin.Context into the template variable - simple, right? Yep. Function is defined here and is very basic. It relies on our session and it requests the authenticity_token from the .Values map.

func f_gin_new_authenticity_token(c *gin.Context) {
   session := g_get_session(c)
   has_token := false
   _, exists := session.Values["authenticity_token"]
   if exists {
      // no token yet
      has_token = true
   }
   existing_token, typecast_ok := session.Values["authenticity_token"].(string)
   if typecast_ok && len(existing_token) > 0 {
      has_token = true
   }

   if !has_token {
      payload := time.Now().UTC().Unix()
      token := Sha256(strconv.FormatInt(payload, 10))
      authenticity_token, token_err := f_s_encrypt_string(fmt.Sprintf("%d|%v", payload, token), *flag_s_session_authenticity_token_secret)
      if token_err != nil {
         log.Printf("failed to generate an authenticity token due to err %v", token_err)
      }
      session.Values["authenticity_token"] = authenticity_token
   }

}

In addition to providing that template functionality, we have the ability, from the handlers themselves, processing the endpoint POST /search for instance, that form's authenticity token provided in the hidden field is then passed through the enforce middleware code. I rely on Gin's c.PostForm() to pull up that token value to check against.

func handler_enforce_authenticity_token(c *gin.Context) {
   session := g_get_session(c)
   challenge_authenticity_token := c.PostForm("authenticity_token")
   b_permit_access := true
   s_error_message := ""
   if len(challenge_authenticity_token) != 64 {
      b_permit_access = false
      s_error_message = "oh you think you're clever don't you?"
   }
   authenticity_token, typecast_err := session.Values["authenticity_token"].(string)
   if !typecast_err || challenge_authenticity_token != authenticity_token {
      b_permit_access = false
      s_error_message = "watch your six"
   }
   if !b_permit_access {
      f_session_flash(session, NewSessionAlert("error", s_error_message, time.Now().Add(36*time.Second), true))
      log.Printf("handler_enforce_authenticity_token error %v", s_error_message)
      f_add_ip_to_watch_list(net.ParseIP(f_s_filtered_ip(c)))
   }
}

In the event of some monkey business with the authenticity token, the IP address gets sent straight into the f_add_ip_to_watch_list() function. Tough luck. Unless the instance is not enforcing the ban list 😅.

One of the most iconic functions of the PhoenixVault when it was online in 2020 and as Project Apario from 2021-2023 was the StumbleInto™ functionality that I built. The Go implementation was so small...

package main

import (
   `fmt`
   `net/http`

   `github.com/gin-gonic/gin`
)

func stumbleinto_identifier(c *gin.Context) string {
   random_page_index := int64(f_i_random_int(len(m_index_page_identifier)))
   random_identifier, exists := m_index_page_identifier[random_page_index]
   if !exists {
      return stumbleinto_identifier(c)
   }
   return random_identifier
}

func r_get_stumble_into(c *gin.Context) {
   c.Redirect(http.StatusTemporaryRedirect, fmt.Sprintf("/page/%v?from=stumbleinto", stumbleinto_identifier(c)))
   return
}

That's it. Unless you count stumbleinto_identifier(c) as something... 🤩 But even that is 👀 - et voila - return random_identifier!

That quickly translates into a redirect to /page/<ID>.

Advanced Search is inside the routes_search.go file and its quite intensive. I won't get into it here, but there are two functions to point to.

func r_get_search(c *gin.Context) {}

func deliver_search_results(c *gin.Context, query string, analysis SearchAnalysis, inclusive map[string]struct{}, exclusive map[string]struct{}) {}

When looking inside the r_get_search() func, we can see that we're building up through a AnalyzeQuery() func call a query_analysis that is then used with several mutexes in order toi gather a series of maps to store identifiers into.

query_analysis := AnalyzeQuery(query)
log.Printf("query_analysis = %v", query_analysis)

var mu_inclusive sync.RWMutex
var inclusive_page_identifiers map[string]struct{}
inclusive_page_identifiers = make(map[string]struct{})

var mu_exclusive sync.RWMutex
var exclusive_page_identifiers map[string]struct{}
exclusive_page_identifiers = make(map[string]struct{})

var wg sync.WaitGroup
sch_found_inclusive_identifiers := go_smartchan.NewSmartChan(*flag_i_search_concurrency_buffer)
sch_found_exclusive_identifiers := go_smartchan.NewSmartChan(*flag_i_search_concurrency_buffer)
ch_done_searching := make(chan struct{})

sem_query_limiter := sema.New(*flag_i_search_concurrency_limiter)

At this point, we have a populated query_analysis and we are going to iterate over the found .Ands and the found .Nots and fire off a goroutine while using a semaphore while find_pages_for_word(ctx, sch, word) processes.

// ands
for _, word := range query_analysis.Ands {
   wg.Add(1)
   requestedAt := time.Now().UTC()
   sem_query_limiter.Acquire()
   if since := time.Since(requestedAt).Seconds(); since > 1.7 {
      log.Printf("took %.0f seconds to acquire sem_query_limiter queue position", since)
   }
   go func(ctx context.Context, word string, sch *go_smartchan.SmartChan, sem sema.Semaphore) {
      defer wg.Done()
      defer sem.Release()
      err := find_pages_for_word(ctx, sch, word)
      if err != nil {
         log.Printf("failed to [AND] find_pages_for_word(%v) due to err: %v", word, err)
         return
      }
   }(ctx, word, sch_found_inclusive_identifiers, sem_query_limiter)
}

// nots
for _, word := range query_analysis.Nots {
   wg.Add(1)
   requestedAt := time.Now().UTC()
   sem_query_limiter.Acquire()
   if since := time.Since(requestedAt).Seconds(); since > 1.7 {
      log.Printf("took %.0f seconds to acquire sem_query_limiter queue position", since)
   }
   go func(ctx context.Context, word string, sch *go_smartchan.SmartChan, sem sema.Semaphore) {
      defer wg.Done()
      defer sem.Release()
      err := find_pages_for_word(ctx, sch, word)
      if err != nil {
         log.Printf("failed to [NOT] find_pages_for_word(%v) due to err: %v", word, err)
         return
      }
   }(ctx, word, sch_found_exclusive_identifiers, sem_query_limiter)
}

When we get to the end of just the first function there, of the two we can see that we are ending it on for { select {} } that listens on the parent context's .Done(), channel, and then a workflow that I'd like to explain. Look at it first.

for {
   select {
   case <-ctx.Done():
      deliver_search_results(c, query, query_analysis, inclusive_page_identifiers, exclusive_page_identifiers)
      return
   case <-ch_done_searching:
      deliver_search_results(c, query, query_analysis, inclusive_page_identifiers, exclusive_page_identifiers)
      return
   case data, channel_open := <-sch_found_inclusive_identifiers.Chan():
      if channel_open {
         page_identifier, ok := data.(string)
         if ok {
            mu_inclusive.RLock()
            _, identifier_already_defined := inclusive_page_identifiers[page_identifier]
            mu_inclusive.RUnlock()
            if !identifier_already_defined {
               mu_inclusive.Lock()
               inclusive_page_identifiers[page_identifier] = struct{}{}
               mu_inclusive.Unlock()
            }
         } else {
            log.Printf("failed to cast data, channel_open := <-sch_found_inclusive_identifiers.Chan() as a string")
         }
      }
   case data, channel_open := <-sch_found_exclusive_identifiers.Chan():
      if channel_open {
         page_identifier, ok := data.(string)
         if ok {
            mu_exclusive.RLock()
            _, identifier_already_defined := exclusive_page_identifiers[page_identifier]
            mu_exclusive.RUnlock()
            if !identifier_already_defined {
               mu_exclusive.Lock()
               exclusive_page_identifiers[page_identifier] = struct{}{}
               mu_exclusive.Unlock()
            }
         }
      }
   }
}

We're using the mutexes properly here, and we're also applying results to the map accordingly when results are found. As results are found, they are written into the results map in the case data, channel_open := <-sch_found_exclusive_identifiers.Chan(): and case data, channel_open := <-sch_found_inclusive_identifiers.Chan(): receivers in the for { select {} }. Once <-ch_done_searching is written to, the deliver_search_results(c, query, query_analysis, inclusive_page_identifiers, exclusive_page_identifiers) is invoked.

The next function renders the UI of the search results. When running on an OVH SYS-3 for a record set of about 3,000 pages of OSINT, this can run is under a half a second against multiple keywords in the advanced search form.

Conclusion

The Advanced Search was able to remove $1K/mo from the OPEX of the project by removing the reliance on Elastic Search. Now - for a period of time, from 2021-2023, I did run an on-prem managed instance of the ELK stack for the project to keep that OPEX off the books. I was also able to build out a cluster of MongoDB servers that were a replica-set holding a 36GB database of metadata. That removed $3K from the bill - so now we're at $4K/mo off the bill. Well - the other various services that I was running were specifically for the DevOps pipeline, Rollbar, Sentry, and GitLab accounts respectively.

By removing the dependency lifecycle of the Rails product entirely, removes a level of the playing field that Go specifically addresses well - in my opinion - better than Ruby does. Because I can run a single reader instance on a SYS-3 instance for ~$33/mo and get unmetered bandwidth serving OSINT and deliver sub second advanced search results in a self-contained application that can handle over 369 concurrent lemmings working on the instance. The limit is defined from the config.yml. When running sovereign you'll want to use 3 regions when you deploy 123 concurrent lemmings to load test your instance. This gives your semaphore values. You can install btop or htop and watch the terminal in another session and tune your semaphores accordingly in your config.yml.

Years later, two to be precise, at the time of this publication, has given me the opportunity to reflect on how little I knew about Go and how much opportunity for growth that the next professional engagement that I work in, I would like to continue my career of an autodidact pursuing engineering excellency writing software in Go. When I look back, I solved a major problem with the limited skills that I had in just learning Go before the pandemic and well after into today.

Ultimately, what I did with the reader, by reducing its OPEX by 99.5% ($7K/mo 👉🏻 $33/mo) is - in my opinion - evidence of my orphanage survival that I went through and I overcame. I am wired to think about complex systems and understand them, and then solve them. When I was asked to solve the 666K unsearchable pages of the JFK files, I looked at that particular moment in history and reflected on what it meant for the American people. Having spent the first three years of my life in Ceaușescu's orphanage, I didn't know much about what happened to him, outside of what I learned in school. When I took on this project, I wasn't a historian or a conspiracy theorist. I am a professional autodidact software architect with an electrical background in computer engineering. Microsoft offered a solution that would have cost anybody sampling the offerings from Azure would expect to pay tens of thousands of dollars per month. Granted, it would be fancy and running on the latest hyper-inflated Microsoft prices! The same topology on AWS would have run me close to $10K per month in just hosting expenses that OVH offered for $3K per month. Even that, over the years, needed to shift from VMWare ESXi virtual machines and over to a bare metal offering of the appliance model of the app. That final decision resulted in a lemmings load tested verification of the project's deliverables and promises that I made. I said I would release it open source. I did. I said I would make it's search advanced. I did. I said I would remove all runtime dependencies. I did. I said I would do it for free. I did. It's GPL-3 to respect the licenses of the open source technology that its also built on.

I am honored and privileged to have served 🇺🇸 by building the PhoenixVault 🐦‍🔥 Open-Source and 100% free from 1) 💰 2) Me 😭. The project was built over a three year period in an own without ego approach to solving a technical challenge that could be applied in a manner that could differentiate what Ceaușescu did to me and what America offered to me instead. Learning what happened to your President mattered to me greatly when I became us by getting naturalized and becoming a citizen in the 90s. Soon after, I joined BITNET through the University of Maine and I spent years learning HTML, CSS, PHP and MySQL so I could volunteer with MuggleNet, studying for the SkillsUSA Nationals competition, and preparing for my internship with a small print magazine publishing company that needed a new web developer intern that would update their website. I expanded that opportunity into offering digital publications of the magazine for readers international to access outside of the print + postal surcharge that was cost prohibitive for anybody but a university to acquire. I want information to flow freely, in The Library of OSINT.

I read every comment! 💜 I'll respond if I can think of a good response that is valuable for everybody. I'm not interested in the politics around who released the JFK files or what the files actually contain or contained that even ran on instances that I operated. From a purely technical perspective, I wanted to serve my country in a selfless manner in the way that I thought would be best for everybody.

Any collection of OSINT.

Your domain. Your certificates. Your servers. Your OSINT.

https://github.com/ProjectApario

How I Processed 666K Pages of Flattened PDFs into a Full Text Search Engine

Andrei Merlescu — Sat, 25 Jul 2026 15:35:14 +0000

In 2017 the National Archives and Records Administration (NARA) released the JFK files in an unsearchable manner 🔍. I tried doing manual research 🕵🏻. I relied on their provided CSV file of metadata to look for relevant documents to discover something - but I was looking for a needle in the haystack. I didn't know where to begin - but at the very least, I wanted to be able to search the contents therein. At least the National Archives allowed me to bulk download the PDFs. From that, I was able to birth the Apario Writer.

In 2020, I began with rails new phoenixvault 🐦‍🔥 and I proceeded on a Zoom call with DJ Nicke - a former animator at Disney - to watch me build the proof of concept of the crowd sourcing declass utility that I envisioned. You see, when I was 7 years old, I had a dream after watching a space focused science program on TV that involved me sitting at the home computer, but interacting with an advanced interface that would help me uncover the mysteries of the day and time of the era. In Stargate SG-1, this concept was explored with the Tolan where Nareem was shocked to discover what Teal'c found in the records buried within a full text interface. Connecting it back to the JFK files released by NARA, they were unsearchable. Agenda on why aside, what could I do about it? This proof of concept grew into a SaaS platform that cost me $7,000 per month to operate over 12 bare meta servers in a private cloud using ESXi. This interface worked, but it was going to be replaced by a cost saving solution architected from the ground up in Go to reduce the dependency graph of the SaaS solution down to a single binary.

In order to do this, I needed to create a pipeline. Looking at the SaaS model, I had a series of sidekiq jobs that compiled the assets. In order to improve the performance of that process, running off from Ruby code, I needed to build a new binary from the ground up using Go. I took the course on YouTube from Matt Holiday called Programming In Go and watched the episodes on repeat several times until the concepts were burned into my per-character programming go-to of choice. I was reminded of the machine detail in circuit design and lower level embedded systems design with the way that Matt explained the concepts behind Go - and they clicked for me. I took that teaching and I applied it to the $7K/mo SaaS model that was complex to manage and costly for a topic that not enough people wanted to continue to subsidize.

But lets take a step back and look at the broader picture first, because Microsoft released on GitHub a Typescript JFK files search engine and it was designed to work with their Cloud Services provided on the Azure cloud. Before I built the proof of concept, I spoke with Microsoft Sales representatives and was quoted tens of thousands per month in order to serve the 666K pages of NARA released JFK files in their full format. The problem was, the interface they built was sloppy and a sales pitch for somebody like me who wanted to dig into the JFK topic using technology - but needed a lunch pad. What Microsoft did was above board and inspirational - even if it was rooted in profit based motives. It actually demonstrated how to use Azure services natively in a manner that if an organization is comfortable with an eight digit annual budget, then c'est le vie! However, I was able to get $7K/mo, but I wasn't working to get eight digits for just the JFK files. So, I built something bigger and better and bolder in a way that has never been done before.

I took concepts that I learned over decades of professional experience at Cisco Systems and Oracle that brought me into WB Games that were translated into the single binary that became the reader that ingests the output of the writer that can also be used with the search index algorithm of 13 unique index types including Gematria. That three binary pipeline needed somewhere to begin. That was the writer. Even though the reader displays the PhoenixVault logo and is the front-end to the concept that I built in 2020.

The Writer

This component compiles the Apario Database that the reader consumes and displays an interactive web application that is self contained with Go powered SSL capabilities. To compile this database, you'll need a Collection of Records in the form of PDF files that you want to gether into a single collection of data that will have each component part of it.

mkdir -p ~/work/projectapario
cd ~/work/projectapario
git clone git@github.com:ProjectApario/writer.git
cd writer

The writer component is 3344 lines of code across 15 .go files. The install.sh script loads the dependencies onto your system so you can compile your own Apario Database.

chmod +x install.sh && sudo ./install.sh

I use the config.yml strategy with the writer. For example, if I am working on teslafiles.info then I'd have a writer.yml file that I'd use for my config file. To begin, lets ensure that some directories exist:

mkdir -p "~/apario/teslafiles.info/{logs,config,workspace,data,import,app,search,ssl}"

This will create:

~/apario
~/apario/teslafiles.info
~/apario/teslafiles.info/logs
~/apario/teslafiles.info/config
~/apario/teslafiles.info/workspace
~/apario/teslafiles.info/data
~/apario/teslafiles.info/import
~/apario/teslafiles.info/search
~/apario/teslafiles.info/app
~/apario/teslafiles.info/ssl

Directory	Purpose
`~/apario/<collection-domain>/logs`	Contains log files
`~/apario/<collection-domain>/config`	Contains `[reader\
{% raw %}`~/apario/<collection-domain>/workspace`	Binary will use this as a temporary workspace
`~/apario/<collection-domain>/data`	When importing data into the Apario Database, you place the originals here.
`~/apario/<collection-domain>/import`	When you're importing data, your structured data goes in here such as your `.csv` or `.xlsx` files.
`~/apario/<collection-domain>/app`	The database(s) live inside of here.
`~/apario/<collection-domain>/search`	The index files for the Apario Database gematria included binary data.
`~/apario/<collection-domain>/ssl`	The TLS

The writer.yml file in ~/apario/teslafiles.info/config for teslafiles.info:

---
log: ~/apario/teslafiles.info/logs/writer.log
database-directory: ~/apario/teslafiles.info/app
no-clam: true
language: eng

When using a .csv file, you'd add:

import-csv: ~/apario/teslafiles.info/import/teslafiles.csv
csv-column-url: URL
csv-column-path: PATH
csv-column-record-number: ID
csv-column-title: TITLE

Your .csv file would have:

ID,TITLE,URL,PATH
1,Document One,https://example.com/document1.pdf,~/apario/teslafiles.info/data/document1.pdf
2,Document Two,https://example.com/document2.pdf,~/apario/teslafiles.info/data/document2.pdf
3,Document Three,https://example.com/document3.pdf,~/apario/teslafiles.info/data/document3.pdf

Then running the writer is a matter of:

go build -o writer .
chmod +x writer
./writer -config ~/apario/teslafiles.info/config/writer.yml

Then, the application will begin streaming detailed logging information into the logs: directory choice from the writer.yml setting destination and minimal information to the STDOUT of the ./writer ... invocation.

How The Writer Was Built From A Blank Text File

Now, let's dig into the Go code shall we, and understand how I ingested 666K pages of unsearchable PDFs and built a full text search engine to provide advanced search capabilities that could parse (president kennedy or the president) not (blank page or intentionally left blank) and (top secret or ts/sci or ts//sci) not cover page and get legitimate results. I built that, twice. First in Ruby on Rails, in front of DJ Nicke, and second behind closed doors during holiday breaks when I had the chance to get some uninterrupted programming flow back into swing.

Dependencies

// Binary Dependencies
sl_required_binaries = []string{
    "pdfcpu",
    "gs",
    "pdftotext",
    "convert",
    "pdftoppm",
    "tesseract",
    "clamscan", // optional with `-no-clam` flag
}

Project	License Type	URL
`pdfcpu`	Apache License 2.0	https://github.com/pdfcpu/pdfcpu
`gs` (Ghostscript)	AGPL-3.0 (dual-licensed; commercial available)	https://www.ghostscript.com/
`pdftotext` & `pdftoppm` (Poppler)	GPL v2 or v3	https://poppler.freedesktop.org/
`convert` (ImageMagick)	ImageMagick License (Apache-2.0-derived)	https://imagemagick.org/license/
`tesseract`	Apache License 2.0	https://github.com/tesseract-ocr/tesseract
`clamscan` (ClamAV)	GPL-2.0	https://github.com/Cisco-Talos/clamav

The pipeline shells out to a mix of copyleft projects. GPL-3.0 was picked because it's compatible with almost everything the project shells out to (Apache-2.0 tools, dual-licensed Poppler, AGPL-3.0 Ghostscript) — the one exception, ClamAV's GPL-2.0-only, doesn't matter since it's just called as a subprocess, not linked in.

To begin, we'll work with the main.go file first:

Before I get to the main() func, there is a directory of assets that contains a JSON file of cryptonyms commonly used throughout the JFK files. This data is particular to the data set itself, but I embed the JSON file itself into the Go program at the package main level below the import () block.

//go:embed bundled/*
var fs_references embed.FS

When we get to building the main() func, its 280 lines long, so we're going to break it down into segments about what its doing. You can follow along in the main.go file on GitHub. I want to focus on the logging component, because how I built the pipeline, the logging matters. It's not one log for the entire pipeline. There is an info log, an error log and a debug log. Some log messages are meant for watching the progress of the compilation. Others are meant for intervention in the event that there is a corrupted PDF file.

log_files = make(map[string]*os.File)

// Initialize log files with truncation
debugFile, err := os.OpenFile(filepath.Join(logDir, "debug.log"), os.O_CREATE|os.O_WRONLY|os.O_TRUNC, 0644)
if err != nil {
    log.Fatalf("failed to open debug log: %v", err)
}

log_files[cDebugLog] = debugFile

infoFile, err := os.OpenFile(filepath.Join(logDir, "info.log"), os.O_CREATE|os.O_WRONLY|os.O_TRUNC, 0644)
if err != nil {
    closeLogFiles()
    log.Fatalf("failed to open info log: %v", err)
}

log_files[cInfoLog] = infoFile

errorFile, err := os.OpenFile(filepath.Join(logDir, "error.log"), os.O_CREATE|os.O_WRONLY|os.O_TRUNC, 0644)
if err != nil {
    closeLogFiles()
    log.Fatalf("failed to open error log: %v", err)
}

log_files[cErrorLog] = errorFile

// Initialize loggers
log_debug = NewCustomLogger(debugFile, "DEBUG: ", log.Ldate|log.Ltime|log.Llongfile, 10)

log_info = NewCustomLogger(infoFile, "INFO: ", log.Ldate|log.Ltime|log.Lshortfile, 10)

log_error = NewCustomLogger(errorFile, "ERROR: ", log.Ldate|log.Ltime|log.Lshortfile, 10)

Next, I need to handle various interrupt signals and run receive_watchdog_signal in the event that they are received. This means Ctrl+C invokes this method.

// interrupt Ctrl+C and other SIGINT/SIGTERM/SIGKILL related signals to the application to quit gracefully
watchdog := make(chan os.Signal, 1)
signal.Notify(watchdog, os.Kill, syscall.SIGTERM, os.Interrupt)
go receive_watchdog_signal(watchdog, logFile, cancel)

Let's look into what this function is doing - because in order to understand why I am using a watchdog, looking into what this function does shows you the tear-down and cleanup process when Ctrl+C is invoked on the writer while its processing.

func receive_watchdog_signal(watchdog chan os.Signal, logFile *os.File, cancel context.CancelFunc) {
    <-watchdog
    log.SetOutput(os.Stdout)
    err := logFile.Close()
    if err != nil {
        log_error.Printf("failed to close the logFile due to error: %v", err)
    }
    defer cancel()

    ch_ImportedRow.Close()       // step 01
    ch_ExtractText.Close()       // step 02
    ch_ExtractPages.Close()      // step 03
    ch_GeneratePng.Close()       // step 04
    ch_GenerateLight.Close()     // step 05
    ch_GenerateDark.Close()      // step 06
    ch_ConvertToJpg.Close()      // step 07
    ch_PerformOcr.Close()        // step 08
    ch_AnalyzeText.Close()       // step 09
    ch_AnalyzeCryptonyms.Close() // step 10
    ch_CompletedPage.Close()     // step 11
    ch_CompiledDocument.Close()  // step 12

    fmt.Printf("Completed running in %d", time.Since(startedAt))

    // excluded pid termination lines L333-L351
}

The binary is designed to support being compiled and executed in a universal format that supports macOS, Linux and Windows natively allowing you compile an Apario Database directory on any system that has the downstream dependencies defined. Getting everything working on Windows is extremely hard and I have not successfully tested it without using WSL2. In that case, the binary is compiled for Linux and not Windows, and is not an .exe - but Go allows you to compile this program into an .exe and run it - even if its not recommended 🤣.

What I hope you noticed was the 12 step process here 👀:

ch_ImportedRow.Close()       // step 01
ch_ExtractText.Close()       // step 02
ch_ExtractPages.Close()      // step 03
ch_GeneratePng.Close()       // step 04
ch_GenerateLight.Close()     // step 05
ch_GenerateDark.Close()      // step 06
ch_ConvertToJpg.Close()      // step 07
ch_PerformOcr.Close()        // step 08
ch_AnalyzeText.Close()       // step 09
ch_AnalyzeCryptonyms.Close() // step 10
ch_CompletedPage.Close()     // step 11
ch_CompiledDocument.Close()  // step 12

This exposes the actual pipeline of what the writer actually does.

FIRST: You begin with a CSV file or an XLSX file that contains the following type of data:

RECORDNO	TITLE	URL	PATH
`<string>`	`<string>`	`<string>`	`<string>`
R1	Example Title	https://archive.gov/example.pdf	./ingest/example.pdf

This assumes that you have ./ingest as a directory in the same location as the writer binary in this example - but you get what I am doing here. That PATH is local to the machine running the writer. The URL is the original source that should end in .gov or .mil if its going to be authentic OSINT that the PhoenixVault was originally built to serve. However, it's actually not required. You can use URLs like https://mywebsite.com/assets/example.pdf and the interface will use whatever link you provided in the column. This is so your users can see the original document in its original location - perhaps to verify authenticity - perhaps to compare results. The RECORDNO column is a unique-required string field. Some OSINT releases provide reference identifiers, and this <string> field is designed for that value.

What writer does, is take this .csv or .xlsx (sheet 1 only) result and use these 4 columns in the CLI flags when the writer runs. Depending on which technology you're using, the flags change. Let's see what usage would look like based on these two type of ingestion file types. First lets get the source code of the writer on our machine.

mkdir -p ~/workspace/projectapario
cd ~/workspace/projectapario
git clone git@github.com:ProjectApario/writer.git 
cd writer
mkdir -p bin
go build -o bin/writer .
chmod +x bin/writer
sudo mv bin/writer /usr/local/bin/writer
which writer
mkdir -p ingest # place PDFs in this folder
touch ingest.csv # populate with your documents metadata

Now, lets run it with a CSV file called ingest.csv and a directory of PDFs called ingest exists next to bin. These are the registered flag definitions:

// Import .csv collections
flag_s_import_csv               = config.NewString("import-csv", "", "relative path to an excel spreadsheet where output is a comma separated table of urls and metadata properties. use additional args to associate columns to key data points.")
flag_s_csv_column_url           = config.NewString("csv-column-url", "", "value of row 1 whose column correlates to URLs to download PDF files from")
flag_s_csv_column_path          = config.NewString("csv-column-path", "", "value of row 1 whose column correlates to absolute paths of PDF files")
flag_s_csv_column_record_number = config.NewString("csv-column-record-number", "", "value of row 1 whose column correlates to a unique record identifier or number")
flag_s_csv_column_title         = config.NewString("csv-column-title", "", "value of row 1 whose column correlates to the title of the document")
flag_s_pdf_metadata_json        = config.NewString("metadata-json", "", "json key value map[string]string")

Using it is as simple as:

writer \ # CSV flags 👇🏻
       -import-csv ./ingest.csv \
       -csv-column-url URL \
       -csv-column-path PATH \
       -csv-column-record-number RECORDNO \
       -csv-column-title TITLE \
       -metadata-json "{\"collection\": \"JFK Files\"}"

It looks identical for XLSX with the slight change.

writer \ # XLSX flags 👇🏻
       -import-csv ./ingest.xlsx \
       -xlsx-column-url URL \
       -xlsx-column-path PATH \
       -xlsx-column-record-number RECORDNO \
       -xlsx-column-title TITLE \
       -metadata-json "{\"collection\": \"JFK Files\"}"

Each row in the CSV is gathered into a struct {} and then passed into the ch_ImportedRow channel for processing. What we're doing, is going from a row in the CSV to a compiled JSON record object for the entire processed document and store that file in a structured directory that the reader ingests by determinatively looking into a path as a structured Apario Database that is a directory of files.

// start a bunch of receiver functions to handle when data is ready to be processed
// each of these functions are like black boxes that ONE PAGE from a document is ingested into until it reaches the end

At this point, we need to go back to the main.go file and look at how each of these channels are established.

As each row from the CSV or XSLX is passed into the writer itself, the actual setup of this channel is found in data.go of the project, but in main() I spawned goroutines for each of the pipeline steps. Traditionally, goroutines are for smaller tasks and such, but they can also be used for longer running tasks too.

The ch_ImportedRow Channel

go receiveImportedRow(ctx, ch_ImportedRow.Chan())            // step 01 - runs validate_result_data_record before sending into ch_ExtractText

The actual receiver function uses a for { select {} } pattern in Go to listen on the context.Context's .Done() channel and the incoming <-ch that is an interface{} data type (anything).

func receiveImportedRow(ctx context.Context, ch <-chan interface{}) {
    var err error
    for {
        select {
        case <-ctx.Done():
            return
        case ird, ok := <-ch:
            if ok {
                rd, valid := ird.(ResultData)
                if !valid {
                    log_error.Printf("not valid typecasting for ird to rd.(ResultData)")
                    return
                } // endif
                rd, err = validate_result_data_record(ctx, rd)
                if err != nil {
                    log_error.Tracef("received error on validate_result_data_record for rd.URL %v ; err = %v", rd.URL, err)
                } else {
                    log_info.Printf("validated the downloaded PDF %v from URL %v, sending rd into ch_ExtractText", filepath.Base(rd.PDFPath), rd.URL)
                    if ch_ExtractText.CanWrite() {
                        err := ch_ExtractText.Write(rd)
                        if err != nil {
                            log_error.Tracef("failed to write to ch_ExtractText channel due to error: %v", err)
                            return
                        } // end if
                    } // end if
                } // end if-else
            } // end if ok
        } // end select
    } // end for
} // end func

This pattern is used throughout each of the steps. The resource intensive task in this case is called in the middle of this receiver function.

rd, err = validate_result_data_record(ctx, rd)

When we look into this function, located inside of the pipeline.go file, we can see:

func validate_result_data_record(ctx context.Context, record ResultData) (ResultData, error) {
    log_info.Printf("started validate_result_data_record(%v) = %v", record.Identifier, record.PDFPath)
    // analyze, repair on error, then re-analyze if necessary
    pdf_info, analyze_err := analyze_then_repair_pdf(record.PDFPath)
    if analyze_err != nil {
        return record, log_error.TraceReturn(analyze_err)
    }
    // fix total pages
    if pdf_info.Infos != nil && pdf_info.Infos[0].Pages == 0 && pdf_info.Infos[0].Pages != pdf_info.Infos[0].PageCount {
        pdf_info.Infos[0].Pages = pdf_info.Infos[0].PageCount
    }
    // validate total pages
    if pdf_info.Infos[0].Pages == 0 {
        return record, log_error.TraceReturnf("failed to set pdf_info.Pages to pdf_info.PageCount\npdf_info = %+v", pdf_info)
    }
    // validate pdf
    validate_err := validate_pdf(record.PDFPath)
    if validate_err != nil {
        return record, log_error.TraceReturn(validate_err)
    }
    // optimize pdf
    optimize_err := optimize_pdf(record.PDFPath)
    if optimize_err != nil {
        return record, log_error.TraceReturn(optimize_err)
    }
    return record, nil
}

Here we're running analyze_then_repair_pdf then using the metadata from the result of the call to populate some structured data. Then we call the validate_pdf to confirm, then optimize_pdf. When we have a problem, we're using the verbose.TraceReturn directly as the log_error handler that is a verbose logger with secrets protection and stack trace analysis capabilities. These can spawn child processes and take several seconds to complete depending on the size of the PDF file being analyzed. If it's gigabytes in size, like this writer has processed successfully, then it'll take a few minutes to complete. That's normal. In the info_log you'll see the begin and end time per document and you'll see some documents take several minutes to complete. This means that the pipeline built within the writer is capable of managing many system resources on a bare metal machine, utilize 100% of its capabilities while safely managing resources across the system that include spawned projects where some are single threaded, and some are multi-threaded. When a multi-threaded tesseract process is spawning, the writer has semaphores in place to ensure that its not going to use too many system resources and that tesseract is contained within a lane of processing compute power proportional to the impact that the pipeline has on the greater runtime.

The ch_ExtractText Channel

This is step 2 of the pipeline where we have to use a third party binary in order to extract the text out of the PDF. I didn't want to reinvent the wheel - I wanted to ingest 666K pages of JFK files as fast as possible, and if the tool works and its license is compatible, let's go!

go receiveOnExtractTextCh(ctx, ch_ExtractText.Chan())        // step 02 - runs extractPlainTextFromPdf before sending into ch_ExtractPages


func receiveOnExtractTextCh(ctx context.Context, ch <-chan interface{}) {
    for {
        select {
        case <-ctx.Done():
            return
        case ird, ok := <-ch:
            if ok {
                rd, ok := ird.(ResultData)
                if !ok {
                    log_error.Tracef("failed to assert the ird from ch_ExtractText as type ResultData")
                    return
                }
                log_info.Printf("received rd from ch_ExtractText for URL %v, running extractPlainTextFromPdf(%v)", rd.URL, rd.Identifier)
                go extractPlainTextFromPdf(ctx, rd)
            } else {
                log_debug.Println("ch_ExtractText is closed but received some data")
                return
            }
        }
    }
}

Here you can see that the bulk of the processing takes place in the go extractPlainTextFromPdf(ctx, rd) spawning of another goroutine; in this case, we aren't going to wait for it in this particular loop of receiveOnExtractTextCh's for { select {} } lifecycle until the context.Context is canceled. That context is then passed into the extractPlainTextFromPdf, where an external tool is invoked.

func extractPlainTextFromPdf(ctx context.Context, record ResultData) {
    defer func() {
        log_info.Printf("finished extracting the text from the PDF %v, now sending rd into ch_ExtractPages", filepath.Base(record.PDFPath))
        if ch_ExtractPages.CanWrite() {
            err := ch_ExtractPages.Write(record)
            if err != nil {
                log_error.Tracef("failed to write record %v into the ch_ExtractPages due to error %v", record, err)
            }
        }
    }()
    log_info.Printf("started extractPlainTextFromPdf(%v) = %v", record.Identifier, record.PDFPath)
    if ok, err := fileHasData(record.ExtractedTextPath); !ok || err != nil {
        /*
            pdftotext REPLACE_WITH_FILE_PATH REPLACE_WITH_TEXT_OUTPUT_FILE_PATH
        */
        cmd_extract_text_pdf := exec.Command(m_required_binaries["pdftotext"], record.PDFPath, record.ExtractedTextPath)
        var cmd4_extract_text_pdf_stdout bytes.Buffer
        var cmd4_extract_text_pdf_stderr bytes.Buffer
        cmd_extract_text_pdf.Stdout = &cmd4_extract_text_pdf_stdout
        cmd_extract_text_pdf.Stderr = &cmd4_extract_text_pdf_stderr
        sem_pdftotext.Acquire()
        cmd_extract_text_pdf_err := cmd_extract_text_pdf.Run()
        sem_pdftotext.Release()
        if cmd_extract_text_pdf_err != nil {
            log_error.Tracef("Failed to execute command `pdftotext %v %v` due to error: %s\n", record.PDFPath, record.ExtractedTextPath, cmd_extract_text_pdf_err)
            return
        }
    }
}

This uses the semaphore package in order to .Acquire() and .Release() on an invocation of .Run() from an exec.Command on the required m_required_binaries["pdftotext"] pdftotext binary invocation. We're going to use a bytes.Buffer to collect on STDOUT and STDERR from the child process and we're using a semaphore to control how many times this particular function is allowed to be called system wide while writer compiles.

The ch_ExtractPages Channel

go receiveOnExtractPagesCh(ctx, ch_ExtractPages.Chan())      // step 03 - runs extractPagesFromPdf before sending PendingPage into ch_GeneratePng

func receiveOnExtractPagesCh(ctx context.Context, ch <-chan interface{}) {
    for {
        select {
        case <-ctx.Done():
            return
        case ird, ok := <-ch:
            if ok {
                rd, ok := ird.(ResultData)
                if !ok {
                    log_error.Tracef("ch_ExtractPages receive an ird but cannot cast it as a .(ResultData) type")
                    return
                }
                log_info.Printf("received on ch_ExtractPages URL %v, running extractPagesFromPdf(%v)", rd.URL, rd.Identifier)
                go extractPagesFromPdf(ctx, rd)
            } else {
                log_debug.Trace("ch_ExtractPages is closed but received some data")
                return
            }
        }
    }
}

This function is quite large. Let's look at it and then talk about it.

func extractPagesFromPdf(ctx context.Context, record ResultData) {
   log_info.Printf("started extractPagesFromPdf(%v) = %v", record.Identifier, record.PDFPath)
   /*
      pdfcpu extract -mode page REPLACE_WITH_FILE_PATH REPLACE_WITH_OUTPUT_DIRECTORY
   */
   pagesDir := filepath.Join(record.DataDir, "pages")
   sm_page_directories.Store(record.Identifier, pagesDir)
   _, pagesDirExistsErr := os.Stat(pagesDir)
   performPagesExtract := false
   if os.IsNotExist(pagesDirExistsErr) {
      performPagesExtract = true
   } else {
      ok, err := DirHasPDFs(pagesDir)
      if err == nil && ok {
         performPagesExtract = true
      }
   }
RETRY:
   if performPagesExtract {
      pagesDirErr := os.MkdirAll(pagesDir, 0755)
      if pagesDirErr != nil {
         log_error.Tracef("failed to create directory %v due to error %v", pagesDir, pagesDirErr)
         return
      }
      cmd_extract_pages_in_pdf := exec.Command(m_required_binaries["pdfcpu"], "extract", "-mode", "page", record.PDFPath, pagesDir)
      var cmd_extract_pages_in_pdf_stdout bytes.Buffer
      var cmd_extract_pages_in_pdf_stderr bytes.Buffer
      cmd_extract_pages_in_pdf.Stdout = &cmd_extract_pages_in_pdf_stdout
      cmd_extract_pages_in_pdf.Stderr = &cmd_extract_pages_in_pdf_stderr
      sem_pdfcpu.Acquire()
      cmd_extract_pages_in_pdf_err := cmd_extract_pages_in_pdf.Run()
      sem_pdfcpu.Release()
      if cmd_extract_pages_in_pdf_err != nil {
         log_error.Tracef("Failed to execute command `pdfcpu extract -mode page %v %v` due to error: %s\n", record.PDFPath, pagesDir, cmd_extract_pages_in_pdf_err)
         return
      }
   } else {
      log_info.Printf("not performing `pdfcpu extrace -mode page %v %v` because the directory %v already has PDFs inside it", record.PDFPath, pagesDir, pagesDir)
      check := len(pagesDir) == len(pagesDir)

      if check {
         goto RETRY
      }
   }

   pagesDirWalkErr := filepath.Walk(pagesDir, func(path string, info os.FileInfo, err error) error {
      if err != nil {
         log_error.Tracef("Error accessing a path %q: %v\n", path, err)
         return err
      }

      if !info.IsDir() && strings.HasSuffix(info.Name(), ".pdf") {
         nameParts := strings.Split(info.Name(), "_page_")
         if len(nameParts) < 2 {
            return fmt.Errorf("incorrect filename provided as %v", info.Name())
         }
         pgNoStr := strings.ReplaceAll(nameParts[1], ".pdf", "")
         pgNo, pgNoErr := strconv.Atoi(pgNoStr)
         if pgNoErr != nil {
            return fmt.Errorf("failed to extract the pgNo from the PDF filename %v", info.Name())
         }
         identifier := NewIdentifier(9)
         pp := PendingPage{
            Identifier:       identifier,
            RecordIdentifier: record.Identifier,
            PageNumber:       pgNo,
            PagesDir:         pagesDir,
            PDFPath:          path,
            OCRTextPath:      filepath.Join(pagesDir, fmt.Sprintf("ocr.%06d.txt", pgNo)),
            ManifestPath:     filepath.Join(pagesDir, fmt.Sprintf("page.%06d.json", pgNo)),
            PNG: PNG{
               Light: Images{
                  Original: filepath.Join(pagesDir, fmt.Sprintf("page.light.%06d.original.png", pgNo)),
                  Large:    filepath.Join(pagesDir, fmt.Sprintf("page.light.%06d.large.png", pgNo)),
                  Medium:   filepath.Join(pagesDir, fmt.Sprintf("page.light.%06d.medium.png", pgNo)),
                  Small:    filepath.Join(pagesDir, fmt.Sprintf("page.light.%06d.small.png", pgNo)),
                  Social:   filepath.Join(pagesDir, fmt.Sprintf("page.light.%06d.social.png", pgNo)),
               },
               Dark: Images{
                  Original: filepath.Join(pagesDir, fmt.Sprintf("page.dark.%06d.original.png", pgNo)),
                  Large:    filepath.Join(pagesDir, fmt.Sprintf("page.dark.%06d.large.png", pgNo)),
                  Medium:   filepath.Join(pagesDir, fmt.Sprintf("page.dark.%06d.medium.png", pgNo)),
                  Small:    filepath.Join(pagesDir, fmt.Sprintf("page.dark.%06d.small.png", pgNo)),
                  Social:   filepath.Join(pagesDir, fmt.Sprintf("page.dark.%06d.social.png", pgNo)),
               },
            },
            JPEG: JPEG{
               Light: Images{
                  Original: filepath.Join(pagesDir, fmt.Sprintf("page.light.%06d.original.jpg", pgNo)),
                  Large:    filepath.Join(pagesDir, fmt.Sprintf("page.light.%06d.large.jpg", pgNo)),
                  Medium:   filepath.Join(pagesDir, fmt.Sprintf("page.light.%06d.medium.jpg", pgNo)),
                  Small:    filepath.Join(pagesDir, fmt.Sprintf("page.light.%06d.small.jpg", pgNo)),
                  Social:   filepath.Join(pagesDir, fmt.Sprintf("page.light.%06d.social.jpg", pgNo)),
               },
               Dark: Images{
                  Original: filepath.Join(pagesDir, fmt.Sprintf("page.dark.%06d.original.jpg", pgNo)),
                  Large:    filepath.Join(pagesDir, fmt.Sprintf("page.dark.%06d.large.jpg", pgNo)),
                  Medium:   filepath.Join(pagesDir, fmt.Sprintf("page.dark.%06d.medium.jpg", pgNo)),
                  Small:    filepath.Join(pagesDir, fmt.Sprintf("page.dark.%06d.small.jpg", pgNo)),
                  Social:   filepath.Join(pagesDir, fmt.Sprintf("page.dark.%06d.social.jpg", pgNo)),
               },
            },
         }
         sm_pages.Store(pp.Identifier, pp)
         err := WritePendingPageToJson(pp)
         if err != nil {
            return err
         }
         log_info.Printf("sending page %d (ID %v) from record %v URL %v into the ch_GeneratingPng", pgNo, identifier, record.Identifier, record.URL)
         if ch_GeneratePng.CanWrite() {
            err := ch_GeneratePng.Write(pp)
            if err != nil {
               log_error.Tracef("cannot send pp into ch_GeneratePng channel due to error %v", err)
               return err
            }
         }
      }

      return nil
   })

   if pagesDirWalkErr != nil {
      log_error.Tracef("Error walking the path ./pages: %v\n", pagesDirWalkErr)
      return
   }

   return
}

You can see RETRY: and goto RETRY? Well, thats a way of recursively calling a block within the function until something works - without having to deal with invoking and tearing down functions in between. In the case of recursion, I'm simply using the performPagesExtract as a variable to determine whether or not we should even consider using goto RETRY. When performPagesExtract is false, I reconsider what if true? If true, that means that the check passes and we actually did need to run it but we didn't have something we needed first - so we fix that - and then re-run it.

Next what I do is I build out a PendingPage{} struct with a bunch of metadata about the individual page from the given document. This is performed by using filepath.Walk(pagesDir, <walkFunc> {}) invocation on the output of the previous command that took the PDF file and extracted in a sub-directory one PDF per page in the original file. We're going to iterate over those compiled documents now, and we're going to populate this PendingPage{} record with the gathered metadata about this particular page.

We're using semaphores here, and we're also invoking WritePendingPageToJson(pp), which effectively compiles the Apario Database from scratch by writing individual JSON files to structured locations based on the page analysis output.

I could go into each of the channels and break them down - but they are very similar in structure to what we have just explored from start to finish. What felt like a short ride, can be a journey of discovery by studying the writer and either utilizing it for public service or contributing to its long-term stability.

The writer is responsible for ingesting a CSV file that can have as many as 72K rows of records inside of it that contain 666K pages of OSINT in unsearchable manner.

The full text analysis uses textee and gematria in order to take the OCR extract text, or the PDF's containing text and break sentences like:

The lazy brown dog jumps over the quick fox.

Into:

The (2)
The lazy (1)
The lazy brown (1)
lazy (1)
lazy brown (1)
lazy brown dog (1)
brown (1)
brown dog (1)
brown dog jumps (1)
dog (1)
dog jumps (1)
dog jumps over (1)
jumps (1)
jumps over (1)
jumps over the (1)
over (1)
over the (1)
over the quick (1)
the quick (1)
the quick fox (1)
quick (1)
quick fox (1)
fox (1)

Notice the The (2) and the way that between over the quick (1) and the quick (1) we omit the (1) by making the first The a 2. That's what textee does.

The gematria package is responsible for calculating the Simple, English, Jewish, Eights, Majestic and Mystery values.

The pipeline operates in this manner:

Rows from a CSV are ingested
PDF is scanned, analyzed, optimized and rendered into a directory of individual page PDF files
Each page PDF file is individually processed - keeping track of its original directory and original extracted document
The .pdf is converted into a .png
The .png is compiled into a progressive light JPEG
The .png is converted into dark mode
The dark mode .png is compiled into a progressive dark JPEG
The .pdf is processed through pdftotext to extract text
The .pdf is processed through tesseract to extract text
The extracted text is analyzed against 7 regular expressions to find dates and cryptonyms
The PendingPage{} struct is passed into aggregatePendingPage in the aggregate.go file

When the dates are analyzed, I opted to use regular expressions for it as defined in the data.go file:

re_date1 = regexp.MustCompile(`(?i)(\d{1,2})(st|nd|rd|th)?\s(?:of\s)?(January|Jan|February|Feb|March|Mar|April|Apr|May|June|Jun|July|Jul|August|Aug|September|Sep|October|Oct|November|Nov|December|Dec),?\s(\d{2,4})`)

re_date2 = regexp.MustCompile(`(?i)(\d{1,2})\/(\d{1,2})\/(\d{2,4})`)

re_date3 = regexp.MustCompile(`(?i)(January|Jan|February|Feb|March|Mar|April|Apr|May|June|Jun|July|Jul|August|Aug|September|Sep|October|Oct|November|Nov|December|Dec),?\s(\d{2,4})`)

re_date5 = regexp.MustCompile(`(?i)(January|Jan|February|Feb|March|Mar|April|Apr|May|June|Jun|July|Jul|August|Aug|September|Sep|October|Oct|November|Nov|December|Dec)\s(\d{1,2})(st|nd|rd|th)?,?\s(\d{2,4})`)

re_date4 = regexp.MustCompile(`(?i)(January|Jan|February|Feb|March|Mar|April|Apr|May|June|Jun|July|Jul|August|Aug|September|Sep|October|Oct|November|Nov|December|Dec)\s(\d{4})`)

re_date6 = regexp.MustCompile(`(\d{4})`)

Then inside of dates.go I am consuming them like so:

match1 := re_date1.FindAllStringSubmatch(in, -1)

match2 := re_date2.FindAllStringSubmatch(in, -1)

match3 := re_date3.FindAllStringSubmatch(in, -1)

match4 := re_date4.FindAllStringSubmatch(in, -1)

match5 := re_date5.FindAllStringSubmatch(in, -1)

match6 := re_date6.FindAllStringSubmatch(in, -1)

Since time is an illusion and we have multiple types, we can look at:

day, dayErr := strconv.Atoi(m[?])

month := getMonthFromString(m[?])
month, _ := strconv.Atoi(m[?])

year, yearErr := strconv.Atoi(m[?])

I conclude analyzing each of these matches with this function:

func uniqueTimes(times []time.Time) []time.Time {
    seen := make(map[time.Time]bool)
    var unique []time.Time
    for _, t := range times {
        if t.Year() < 1800 || t.Year() > time.Now().Year() {
            continue
        }
        t = time.Date(t.Year(), t.Month(), t.Day(), 0, 0, 0, 0, t.Location())
        if _, ok := seen[t]; !ok {
            unique = append(unique, t)
            seen[t] = true
        }
    }
    return unique
}

Aggregating `PendingPage {}` In Pipeline

func aggregatePendingPage(ctx context.Context, pp PendingPage) {
   data_rd, result_data_found := sm_resultdatas.Load(pp.RecordIdentifier)
   // process missing from sync.Map

   rd, rd_cast_ok := data_rd.(ResultData)
   // process invalid ResultData cast

   document_data, document_found := sm_documents.Load(pp.RecordIdentifier)
   // process missing from sync.Map

   document, document_cast_ok := document_data.(Document)
   // process invalid Document cast

   // print document page count results

   mu := DocumentLocker(document.Identifier)
   mu.Lock()
   if document.Pages == nil {
      document.Pages = make(map[int64]Page)
   }
   document.Pages[int64(pp.PageNumber)] = Page{
      Identifier:         pp.Identifier,
      DocumentIdentifier: pp.RecordIdentifier,
      PageNumber:         int64(pp.PageNumber),
   }
   defer mu.Unlock()
   ticker := time.NewTicker(333 * time.Millisecond)
   timer := time.NewTimer(999 * time.Second)
   if int64(len(document.Pages)) == document.TotalPages {
      for {
         select {
         case <-ctx.Done():
            log_debug.Printf("context canceled on aggregatePendingPage %+v", pp)
            return
         case <-timer.C:
            log_error.Printf("FATAL TO DOCUMENT aggregatePendingPage failed to ingest %+v into the ch_CompiledDocument", pp)
            return
         case <-ticker.C:
            err := ch_CompiledDocument.Write(document)
            log_info.Printf("ch_CompiledDocument.CanWrite = %v", ch_CompiledDocument.CanWrite())
            if err != nil {
               log_error.Printf("FATAL TO DOCUMENT - err in writing to channel: %v", err)
            }
            return
         }
      }
   } else {
      log_debug.Printf("aggregatePendingPage document %v page %d received but the document.Pages are at %d of %d so waiting before sending into ch_CompiledDocument",
         pp.RecordIdentifier, pp.PageNumber, len(document.Pages), document.TotalPages)
   }
   return
}

As you can see, we're using a sync.Map called sm_resultdatas.Load(pp.RecordIdentifier) in order to retrieve the PendingPage{} struct out of memory according to the RECORDNO column from the original CSV or XLSX file.

We're using the DocumentLocker in order to safely write to document.Pages[int64(pp.PageNumber)] in a concurrent multi-threaded environment where resources are shared across threads and child processes.

type Document struct {
    Identifier          string         `json:"identifier"`
    URL                 string         `json:"url"`
    Pages               map[int64]Page `json:"pages"`
    TotalPages          int64          `json:"total_pages"`
    CoverPageIdentifier string         `json:"cover_page_identifier"`
    Collection          Collection     `json:"collection"`
}

type Page struct {
    Identifier         string            `json:"identifier"`
    DocumentIdentifier string            `json:"document_identifier"`
    PageNumber         int64             `json:"page_number"`
    Metadata           map[string]string `json:"metadata"`
    FullTextGematria   gem.Gematria      `json:"full_text_gematria"`
    FullText           string            `json:"full_text"`
}

The lockers are defined in the lockers.go file, and the init() func is what I want to point your attention to next.

func init() {
    // Checksums
    cslMu = &sync.RWMutex{}
    checksumLockers = make(map[string]*sync.RWMutex)

    // Documents
    dlMu = &sync.RWMutex{}
    documentLockers = make(map[string]*sync.RWMutex)

    // Pages
    pageLockers = make(map[string]*sync.RWMutex)
    plMu = &sync.RWMutex{}
}

Here, we're registering a couple of map[string]*sync.RWMutex for the documentLockers and pageLockers in the package.

There are three types; the first is for checksums ; used to take the URL, generate a SHA512 string and use that as the Apario Database subdirectory to store the structured contents into. Rendered assets are stored here and JSON files written to disk live here. In order to treat the filesystem like this as a database, I needed to have various lockers. A checksum level locker - for work being done to the root level of the document record. The DocumentLocker for per ID records in the CSV or XLSX file ingested.

// Document Locker
var dlMu *sync.RWMutex
var documentLockers map[string]*sync.RWMutex
var DocumentLocker = func(id string) *sync.RWMutex {
    dlMu.Lock()
    log.Printf("DocumentLocker(%s) Locked...", id)
    defer func() {
        log.Printf("... DocumentLocker(%s) Unlocked!", id)
        dlMu.Unlock()
    }()
    if _, found := documentLockers[id]; !found {
        documentLockers[id] = &sync.RWMutex{}
    }
    return documentLockers[id]
}

The DocumentLocker variable is a function that intentionally can be overwritten with a new function of the same signature type. The dlMu is a documentLockers's mutex, different from a record's locker defined as documentLockers[id] = &sync.RWMutex{}.

This pattern allowed me to create lockers for pages that were dynamically belonging to documents in your original CSV or XLSX that was passed into the arguments of the writer when it executed.

for {
   select {
   case <-ctx.Done():
      elapsed := time.Since(startedAt)
      log.Printf("Completed task in %.0f seconds", elapsed.Seconds())
      return
   case <-ch_Done:
      log.SetOutput(os.Stdout)
      log.Printf("done processing everything... time to end things now!")
      watchdog <- os.Kill
   case id, ok := <-ch_CompiledDocument.Chan():
      if ok {
         d, ok := id.(Document)
         if !ok {
            log_error.Printf("cannot typecast the final result for %s as a .(Document)", d.Identifier)
         }
         a_i_received_documents.Add(1)
         log_info.Printf("a_i_total_documents == a_i_received_documents ; %d == %d",
            a_i_total_documents.Load(), a_i_received_documents.Load())

         if a_i_total_documents.Load() == a_i_received_documents.Load() {
            log.SetOutput(os.Stdout)
            log.Printf("Completed processing document %v", d)
            ch_Done <- struct{}{}
         }
      }
   }
}

I chose to end the main() func with a for { select {} } that listens on the background context's .Done() channel, the ch_Done channel being written to, or a new completed row from the CSV or XLSX is received compiled via <-ch_CompiledDocument.Chan().

I created a variable called a_i_received_documents to track the total received documents. The a_ is for atomic and i_ is for int, so the atomic.Int is called received_documents Once every row has been processed, the struct{}{} is written to the ch_Done channel.

If you've noticed the .Chan() throughout this, that is the smartchan that I wrote.

package go_smartchan

import (
    "errors"
    `fmt`
    "sync"
    `sync/atomic`
)

type SmartChan struct {
    ch     chan interface{}
    mu     sync.RWMutex
    closed atomic.Bool
    cnt    atomic.Int64
}

func NewSmartChan(i int) *SmartChan {}

func (sc *SmartChan) Write(thing interface{}) (err error) {}
func (sc *SmartChan) Read() (interface{}, error) {}
func (sc *SmartChan) Count() int64 {}
func (sc *SmartChan) Close() {}
func (sc *SmartChan) Chan() chan interface{} {}
func (sc *SmartChan) CanWrite() bool {}

This is used throughout the writer and throughout a bunch of other projects that I've worked with. Asking .CanWrite() on a channel is a task that allows you to build in pipelines when channels become unwritable and then writable. It attempts to remove the panic() from using channels in Go.

I didn't want to close out on the piece before walking you through some of the more complex AI generated functions that I did use. At this time, ChatGPT was the utility that I was using, and these functions served their purposes for what they were intended to perform. Given their open source nature, discussing them here is relevant to the context of this piece.

func nrgbaToRGBA(src *image.NRGBA) *image.RGBA {
   b := src.Bounds()
   dst := image.NewRGBA(b)

   for y := b.Min.Y; y < b.Max.Y; y++ {
      for x := b.Min.X; x < b.Max.X; x++ {
         r, g, b, a := src.At(x, y).RGBA()

         // Convert from non-premultiplied to premultiplied alpha
         r = (r * a) / 0xffff
         g = (g * a) / 0xffff
         b = (b * a) / 0xffff

         dst.SetRGBA(x, y, color.RGBA{
            R: uint8(r >> 8),
            G: uint8(g >> 8),
            B: uint8(b >> 8),
            A: uint8(a >> 8),
         })
      }
   }

   return dst
}

A little bot named Red lived in Paint Town. Red loved to help kids make their pictures look pretty!

One day, tiny Tim brought a clear sheet with colors on it. "I want to show my friends!" said Tim.

"I can help!" beeped Red. "Watch me make it pretty!"
Red took a white paper and looked at each tiny spot on Tim's clear sheet.

"When I see light spots," beeped Red, "I make them soft like clouds."

"When I see bright spots," beeped Red, "I make them strong like rainbows!"

Spot by spot, Red made Tim's picture. Some colors were soft, some were bright!

"Wow!" smiled Tim. "Now my friends can see my picture too!"

Red did a happy beep-boop dance. Helping make pictures was Red's favorite thing!

The End.

Conclusion

This application, the first of three in the Project Apario trio of open source software takes a .csv or .xlsx (first sheet only) and processes the structured data RECORDNO,TITLE,URL,PATH into an Apario Database that the reader renders into an ultra-fast self-contained web application called PhoenixVault.

The original project written was a Ruby on Rails implementation that was built on a Zoom call with a former Disney animator over a 17 day flow state. I have a witness for what I built. DJ watched me built the pipeline in Rails using sidekiq making each of the system calls for each of the steps. When I took a chance on myself over Christmas 2021 to rewrite the Rails implementation that was still costing me over $2,000 per month - even after removing all of the SaaS and going completely in-house private cloud for everything. This meant that I spent a year migrating the external SaaS model that used MongoDB Atlas, ElasticSearch As A Service called ELK, and DigitalOcean Managed Redis running behind a private cloud built behind ESXi and Rocky 9 Linux with SELinux enabled. I reduced my OPEX from $7K down to $2K per month. That comes out to a $180K savings over 3 years. I wasn't going to keep asking the American people to pay me $180K to keep the PhoenixVault online for you. Instead, what I built in the trio application, was a solution that can run on an OVH Bare Metal host for $33 per month.

It's simple. The $33/mo host is sufficient. When you get it, set it up like so:

ssh -i ~/.ssh/mykey rocky@17.76.17.76 # replace with your IP address and replace ~/.ssh/mykey with your SSH key
rocky@ip-17-76-17-76:

Then you can set it up like so:

sudo mkdir -p /apario/{reader,writer,search}/{workspace,config,logs}

Then you can do:

cd /apario/reader
git clone git@github.com:ProjectApario/reader.git src

cd /apario/writer
git clone git@github.com:ProjectApario/writer.git src

cd /apario/search
git clone git@github.com:ProjectApario/search.git src

Then you'll need to render a YAML script for each of the instances that you're running. If your project is called acme-disclosure then you're have:

/apario/reader/src
/apario/writer/src
/apario/search/src

In addition to that, you'll also create a symbiotic relationship between:

/apario/reader/acme-disclosure/database

and

/apario/writer/acme-disclosure/ingest

such that the writer writes to the /apario/reader/acme-disclosure/database path and the reader reads the same path. The writer processes the flattened or non-optimized PDFs from the /apario/writer/acme-disclosure/ingest.

This is useful for ACME (a fictional company) to release a disclosure of information in the form of a PDF dump. It's common with documentation and with OSINT practices. For the context of the JFK files from the National Archives, I wrote the writer to ingest those files into a useable format that I ended up writing a consumer for.

This is the first of three binaries and the project is incomplete because of life. When the time comes to dust off what I have built and continue the torch, the time will come. Until that time arrives, I type to share what I learned so that the youth can pick up the torch.

The vision that I had as a child wasn't for me alone to experience. It was for the world to discover the beauty that dwells within and how technology helps us understand the complex reality that we live inside. 💜

DEV Community: Andrei Merlescu

My type of #FractionalCTO

Part-Time Executive Leadership

Technical Strategy & Architecture

Infrastructure Cost Optimization

Crisis Management & Recovery

Team Mentorship & Hiring

Vendor & Agency Oversight

Security & Compliance Standards

Investor Readiness & Due Diligence

Scalability Planning

Bridge for Non-Technical Founders

Conclusion

My Professional Story: Two Names, Thirty Years of Scaffolded Code, and the AI That Can't Tell I'm One Person

Write Us the Software, Then We'll Demote You

The Boy Calling Himself Michael

LAMP Stacks, MuggleNet, and 8th in the Nation

Fifteen Years at Sawmill, a Coup at Wentworth

A Name Is a Program

Mohammed

Michael Was Big Tech. Andrei Is Gaming.

Project Apario: What I Did Instead of Nothing

2026: The Machines Doing the Hiring

IBM Warned Us in the 1970s

"AI Slop" Hurts Hiring Pipeline for Tech Talent

My Reply

8/8/26 Update

🚩 AI companies must change or be stopped

Anthropic is physically destroying RARE and OLD books to train Claude

Claude Reacts to codeBerg Bans Cryptocurrency and LLM-generated code projects

Claude's Response

Conclusion

How My Claude Skills File Changed AI Assisted Development

1. Think Before Coding

2. Simplicity First

3. Surgical Changes

4. Goal-Driven Execution

Conclusion

The Afternoon We Put Magento's Schema on SQL Server

The setup: why two engines at all

Choosing the right query pattern for the problem

Using NOT EXISTS / EXISTS where appropriate

Avoiding unnecessary nested aggregate subqueries

Understanding joins, filtering, grouping, and set-based logic

Using window functions like DENSE_RANK() and similar tools when appropriate

Working with JSON data in the database

Indexing strategy

Normalization vs selective denormalization

When some redundancy improves performance

Parent/child and subtype/subclass-style table design

Understanding how schema choices affect performance and maintainability

What to take with you

Before you commit a query

Before you commit a schema change

Reading the plan

On verifying any of this

SQL Design & Query Reference

Knowledge Transfer Doc — PostgreSQL · MySQL (InnoDB) · SQL Server 2008+ · Oracle 19c+

PART I — WRITING QUERIES

1. Choosing the Right Query Pattern

The pattern decision table [SAME across all four]

The DISTINCT smell [SAME]

CTEs: the biggest cross-engine trap [DIFFERS — important]

2. EXISTS / NOT EXISTS — and Why NOT IN Keeps Hurting You

The NULL trap [SAME — all four, it's ANSI]

The optimizer angle [DIFFERS]

LEFT JOIN ... WHERE right.pk IS NULL [SAME semantics, [DIFFERS] on plan quality]

EXISTS vs IN for the positive case [SAME, mostly]

EXISTS micro-details [SAME]

3. Avoiding Unnecessary Nested Aggregate Subqueries

The anti-pattern

Fix 1 — one window pass

Fix 2 — pre-aggregate then join

The double-counting bug [SAME]

Fix 3 — lateral / apply, when you need the whole row [DIFFERS in syntax only]

Oracle-only shortcut

4. Joins, Filtering, Grouping, and Set-Based Logic

ON vs WHERE on outer joins [SAME]

Predicate sargability [SAME principle, [DIFFERS] in remedies]

Implicit conversion — the silent index killer [DIFFERS in how it manifests]

The `DISTINCT` smell [SAME]

2. `EXISTS` / `NOT EXISTS` — and Why `NOT IN` Keeps Hurting You

`LEFT JOIN ... WHERE right.pk IS NULL` [SAME semantics, [DIFFERS] on plan quality]

`EXISTS` vs `IN` for the positive case [SAME, mostly]

`EXISTS` micro-details [SAME]

`ON` vs `WHERE` on outer joins [SAME]

`GROUP BY` strictness [DIFFERS]

`HAVING` vs `WHERE` [SAME]

PostgreSQL: use `jsonb`, essentially always

`ON DELETE CASCADE` [SAME syntax, [DIFFERS] in gotchas]

`CORS` Cross-Origin Resource Sharing Capability Built In

`CSP` Content Security Policy Capability Built In