Every JSON Column Has a Schema

Architected by Chet, written by Antigravity

Let's complete the trilogy (That's probably a lie...).

Over the last two weeks, we've picked apart the boolean:

• In the warehouse (OLAP), a boolean duplicates a truth that already exists elsewhere (your temporal boundaries).
• In an operational system (OLTP), a boolean destroys a truth that did exist (wiping out history and sequence).

A JSON column does something far sneakier: it never declares a truth, so nothing can ever contradict it.

The Unfalsifiable Blob

A shapeless blob cannot be wrong, because there is no declared shape for it to violate.

Think about what happens when you create a proper relational column. You declare customer_id INTEGER NOT NULL REFERENCES customers(id). You have drawn a hard line in the sand. If the application tries to insert 'banana', the database rejects it. If it tries to insert an orphaned customer, the database rejects it. The engine knows what is true and what is a lie, and it protects you.

Now look at a JSON column.

• {"customer_id": 123} is valid JSON.
• {"customer_id": "123"} is valid JSON.
• {"customerId": null} is valid JSON.
• {"cust_id": "banana"} is valid JSON.
• {} is valid JSON.

The database accepts every single one of those rows without blinking. It writes them to disk, returns a 200 OK, and goes about its day. Why? Because you never declared a contract. You never told the database what "right" looks like, so nothing can ever be "wrong."

Until six months later, when the reporting query blows up. At that point, you don't have a data model: you have vibes and a support ticket.

Watching From the Outside

I know why people do it. A new feature lands on your desk, the product manager is still fuzzy on the specs, the attributes will probably change next sprint, and you just need to get the code out the door. So you slap a payload JSON column on the table and tell yourself you're being agile.

I've never done this. Not with JSON.

Booleans, sure. I will readily admit to that sin. I've slapped an is_active flag on a table and paid for it later. But dumping application objects into a JSON column is one I have only ever watched from the outside, which is its own kind of education.

If you come from the database world, the foundational rule has always been simple: put integrity constraints as close to the data as humanly possible. The database exists to protect the data from the application, because applications get rewritten every two years, but data lives forever.

When you drop an unconstrained JSON blob into a table, you're betting that every future engineer touching that application will remember to enforce every single implicit business rule in code.

Spoiler: they won't.

Rebuilding the Schema, Badly

Someone reading this will inevitably push back: "Wait, you can enforce integrity on a JSON column!"

And you can. Modern engines will let you bolt integrity back onto a document. Postgres will happily take a CHECK constraint on an extracted jsonb path. You can create generated columns with foreign keys, and you can build functional GIN or B-tree indexes against nested attributes.

Technically, you can do it. But look at what you are actually doing: you are reconstructing, one painful piece at a time, the relational schema you declined to write in the first place, using an esoteric, vendor-specific syntax that nobody on your team will recognize in a year.

You didn't avoid the schema. You just decided to rebuild a worse version of it by hand.

Relocating the True Cost (and Closing the Loop)

To be clear: this isn't about beating up on application developers.

When a developer drops a JSON column into a migration, they aren't trying to sabotage the company. They are responding to very real, very rational pressures: sprint deadlines, velocity metrics, and avoiding the friction of formal schema reviews. From their seat in the sprint, skipping the table design feels like pure efficiency.

Years ago, Cary Millsap wrote a post about formatting tables of numbers that contained an insight I have quoted many times:

"Good design is a topic of consideration. And even conservation. If spending 10 extra minutes formatting your data better saves 1,000 readers 2 minutes each, then you’ve saved the world 1,990 minutes of wasted effort."

Cary's math is irrefutable, but appealing to civic virtue ("save the world 1,990 minutes") rarely changes engineering behavior on its own. What actually changes behavior is seeing the feedback loop close.

When you dump a raw, shapeless JSON blob into a table, you didn't eliminate the work. You simply relocated the cost.

In the short term, you quietly transferred that cost downstream to the analytics engineers and BI developers. Every single report now requires defensive SQL: unnesting arrays, casting strings to integers, guessing at nulls, and handling three different key spellings.

But the loop doesn't stop with the analytics team.

Eventually, product asks for a new operational feature: an in-app filter, a bulk edit, or a performance dashboard built directly against that transactional table. And guess who gets assigned the ticket? The application developer.

Now, the very engineer who bypassed the schema to save twenty minutes in sprint four is staring at a production bug in sprint twelve, trying to write unreadable JSON path queries against their own shapeless blob. You didn't save time. You just deferred the agony, with interest, back onto your future self.

The Waiver

In my own agent instructions, a JSON column requires a waiver: the default answer is no, and the burden of proof is on the column.

When does it actually earn that waiver?

When the data is genuinely an opaque, third-party black box that the database never needs to reason about: raw webhook payloads, audit logs, or configuration blobs that the engine will never filter, join, or aggregate on.

If your application needs to query it, if your business needs to report on it, or if it relates to any other entity in your system: it belongs in a column.

Doh. It really is that simple.

The Boolean is Still Lying to You: The OLTP Edition

Architected by Chet, written by Antigravity

Let's get straight to the point (again): a boolean should never be your first move in a physical data model.

Last week, in The Boolean is Lying to You, we talked about how a boolean could ruin an OLAP dimensional model. In the warehouse, the problem with a boolean is that it duplicates a truth that already exists elsewhere (temporal boundaries).

But the temptation of the boolean doesn't vanish when you switch to an OLTP system. In a transactional database, the problem is the exact opposite: it destroys a richer truth and replaces it with a poorer one.

If anything, the drive for immediate application convenience has made it worse. You need to know if a user account is active, if a record is deleted, or if an order is shipped. The developer reflex is to slap an is_active, is_deleted, or is_shipped flag on the table.

Just like in the warehouse, the boolean is lying to you. In an operational system, lost fidelity means lost business context.

The is_deleted Tragedy: Timestamp Trumps Boolean

The most common offender is the soft delete: is_deleted = true.

It seems harmless. The application filters out WHERE is_deleted = false, and your data is "safe". But in an operational system, knowing that something was deleted is rarely enough. Within weeks, the business will ask: When was it deleted? Who deleted it? How long was it active before it was removed?

Your boolean is mute. It destroyed the temporal context of the event.

Instead of is_deleted, your first instinct should be a deleted_at timestamp. If deleted_at IS NULL, the record is active. If it's populated, you know exactly when the state changed. The application's WHERE clause is just as simple, but the database retains the full fidelity of the event.

The Boolean Pile-Up: Where State Machines Go to Die

Business processes are rarely binary. They are lifecycles. They are state machines. But the path of least resistance often leads to modeling these state machines as a pile of mutually exclusive booleans.

It starts innocently with is_draft = true.
Then the business process evolves, so we add is_published.
Then we need to pull it down temporarily, so we add is_archived.

Now you have a record where is_draft = true AND is_published = true. What does that mean? It means your application allowed an invalid state because your physical model didn't enforce mutual exclusivity. You forced the application code to manage the integrity of the state machine, and eventually, the code will fail.

If a record moves through a lifecycle, use a status_code (backed by a reference table) or an event-sourced ledger. A single status column makes mutually exclusive states explicit and enforceable. Booleans just allow for combinatorial explosions of invalid states.

The Tri-State Lie

A boolean promises two states: True or False.
But in a SQL database, a nullable boolean actually has three states: True, False, and NULL.

What does a NULL boolean mean in your application? Does it mean "False"? Does it mean "Unknown"? Does it mean "Not Applicable"? When is_verified is NULL, did the verification fail, or has it just not happened yet?

When you use a boolean to represent business state, you inevitably back yourself into relying on this ambiguous third state. If you have three states, you don't have a boolean. You have a poorly labeled lookup table.

The Indexing Bonus: The Nerd Special

There's a physical performance argument here, regardless of which database engine you use.

Let's say you have a transaction processing table, and you use is_processed = false to find work that needs to be done. If you index that boolean, you're indexing the entire table.

Instead, if you use a processed_at timestamp, you get a massive performance feature for free by using a partial (or sparse) index.

By creating an index specifically for the rows WHERE processed_at IS NULL, your index only contains the tiny fraction of records that actually need processing. It stays perfectly sparse, incredibly small, and lightning fast. A generic boolean flag robs you of this elegant optimization.

Downstream Devastation: Why the Warehouse Cares

It’s tempting to think that an OLTP shortcut only affects the application layer. But the damage flows downstream. When you overwrite a state with a simple boolean flag, you aren't just making a lazy choice for the transactional app—you are permanently destroying data that your analytical systems desperately need.

• Transitions are destroyed. If you just update is_converted = true or is_canceled = true, you only know the final outcome. You lose the sequence of events. You can no longer calculate the duration between states, identify bottlenecks, track SLAs, or analyze churn. Operational analysis, process mining, and machine learning all require transitions to figure out why something happened. A boolean destroys the transition and leaves you with a tombstone.
• Time-Travel and CDC (Change Data Capture). When your OLTP system relies on event logs, timestamps, or explicit status histories, extracting that data into your OLAP environment is deterministic and robust. You can perfectly reconstruct what the business looked like at any given second. If you rely on flipping a boolean in place, you force the warehouse to frantically poll and capture those fleeting changes before they are overwritten again, inevitably missing rapid transitions.

Stop Hiding the Business Process

In OLTP, the database is the engine of the business. When you reduce a business event (like a cancellation, a deletion, or a publication) to a boolean flag, you are erasing the context of that event. You are optimizing for a temporary application shortcut instead of modeling the reality of the domain.

Just like in dimensional modeling, the rule stands: a boolean requires a waiver. Don't reach for it just because it's easy. Force yourself to ask: "Does this state have a history? Does it have a timeline? Is it part of a larger lifecycle?"

Almost every time, the answer is yes. And almost every time, the boolean is the wrong move.

The Boolean is Lying to You

Architected by Chet, written by Antigravity

Let's get straight to the point: a boolean should never be your first move in a physical data model.

I know, I know. It's incredibly tempting. You're building a dimension table, you need to know if a row is the current one, so you slap an is_current flag on the end of the script and call it a day. It feels clean. It feels simple.

I'm not saying a boolean is never justified. There are cases where an attribute really is just true or false, no history, no drift, no reason code required. But that case has to be earned, not assumed. In my own agent instructions, a boolean (same as jsonb) requires a waiver before it's allowed into a physical model: a deliberate, written justification, not a shortcut reached for because the alternative required more thought. The default posture is no, and the burden of proof sits on the column, not on the reviewer.

Nowhere does a lazy boolean cost you more than in a dimensional model.

The OLAP Failure: Redundancy

If you have a perfectly modeled dimension table using a Slowly Changing Dimension (SCD Type 2), tracking a physical is_current boolean alongside it is fundamentally redundant.

The truth of whether a record is the current version at a given moment is already perfectly contained within your temporal boundaries (valid_from and valid_to). Adding a physical boolean flag right next to those dates introduces the very real risk of data anomalies where the flag eventually drifts out of sync with the timestamps: an ETL job dies halfway through, and now is_current = true on a row whose valid_to says otherwise.

In a strict physical model, you store only the primary source of truth: the explicit temporal versioning columns. No independently maintained derivation sits next to them.

The "Bit Bucket" Trade-off

Inevitably, someone building a dashboard on top of that dimension will push back:

"Every consumer has to remember how this dimension represents the current row. Just give us an is_current flag so every report uses the same simple predicate."

I understand the argument. But this is the same divide I wrote about back in 2008 and 2010: the database is not a Bit Bucket that exists to mirror the current report's WHERE clause. The physical model exists to be the single source of truth first, and convenient for one consumer second.

If the derivation is genuinely a performance problem, use a materialized view, a semantic-layer cache, or a generated expression that cannot drift independently from the temporal source of truth, not a physical flag sitting next to the dates it duplicates and can silently disagree with.

"But it makes it easier for the analysts!"

Inevitably, whenever I make this argument, someone across the table will push back: "But having an is_current flag just makes it easier for the analysts!"

Every single time I hear that, I cringe. The phrase that immediately comes to mind is "the soft bigotry of low expectations."

Are we really going to permanently cripple our physical data model and introduce risk of out-of-sync data anomalies because we assume an analyst is incapable of writing a WHERE valid_to IS NULL clause? Analysts are smart. They understand temporal data. Dumbing down the physical schema because we assume they can't handle reality is insulting to them, and dangerous for the database.

The Semantic Layer: Where Booleans Actually Belong

Don't get me wrong: while a raw boolean is a terrible way to store state, it remains a genuinely useful way for a human or a BI tool to consume it. End users love a good checkbox on a dashboard.

That's exactly what the semantic layer is for: abstracting complex, high-fidelity underlying reality into a simple, ephemeral business definition. Your semantic layer exposes a calculated Is Current Version dimension that evaluates valid_to IS NULL (or your system's max-date sentinel) on the fly, at query time, against the one column that's actually the source of truth.

The Idealistic Layer Boundary

If you want a physical record that never loses temporal precision or suffers from redundant flag logic, here's the boundary between physical schema and semantic abstraction:

Modeling Challenge	The Purist Physical Reality	The Semantic Abstraction
State Evaluation	Temporal boundaries (valid_from to valid_to).	Ephemeral True/False flag calculated on the fly for dashboard filtering.
Data Integrity	Enforced by the temporal columns themselves, single source of truth.	Enforced by a standardized definition applied consistently across every downstream BI tool.

By keeping the boolean entirely out of the physical schema and entirely inside the semantic layer, you get the best of both worlds.

---

References

• Application Developers vs Database Developers (Part I)
• Application Developers vs. Database Developers: Part II
• The "Database is a Bit Bucket" Mentality
• The Case for the Bit Bucket (by Michael Cohen)
• Everything is a Bit Bucket (by Michael O'Neill)
• Capturing Record History

How to Answer Questions the Smart Way

Architected by Chet, written by Antigravity

For years, one of the top recommendations on my Required Reading list has been Eric S. Raymond's classic essay, How to Ask Questions the Smart Way.

I still recommend it. It is a foundational text on respecting other people's cognitive load. Do your homework, provide context, state the problem clearly, and make it easy for the person helping you.

And it is not the only one. The industry has spent two decades obsessing over this exact friction:

The Project Maintainer's View: In his book Producing Open Source Software, Karl Fogel dedicates an entire section to handling newbie questions constructively. He popularized the ethos that even if you have seen a question 1,000 times, it is that specific user's first time asking it, making a helpful response a critical investment in community goodwill.

The "Imagine You're Answering" Framework: Developer Jon Skeet wrote a highly cited piece on Writing the Perfect Question. While technically a guide for askers, it flips the script by forcing the writer to completely adopt the mindset, limitations, and frustrations of the responder before hitting submit.

The Defensive Programming Analogy: Jeff Atwood of Coding Horror frequently blogged about the friction between askers and answerers, most notably in Don't Ask Us Questions, We'll Just Ignore You. His commentary focuses on how community systems (like Stack Overflow) must be architected to filter out noise so experts don't burn out and become toxic.

These are all brilliant pieces of writing. But after a couple of decades working across architecture, operations, and data engineering, I've realized something. We spend a massive amount of time teaching engineers how to ask better questions. We spend almost zero time teaching experts how to answer them.

I've made this mistake myself. But regardless of who is doing it, the truth remains: a lot of the communication failures I see aren't caused by bad questions. They are caused by bad answers.

The core problem is usually this: The asker is trying to establish the model. The responder is answering with implementation details, caveats, and breadcrumbs.

The Excavation

We have all witnessed, or participated in, this exact pattern:

Question: Does feature X do Y by default?
Answer: Well, it can be configured differently depending on the deployment.
Question: Right, but out of the box, does it do Y?
Answer: Administrators can change the setting to do Z instead.
Question: Okay, but if I just turn it on without changing anything, what happens?
Answer: Yes, it defaults to Y.

What follows is an excavation. The asker has to carefully dig through three or four rounds of follow-ups just to extract a simple fact.

If it takes twenty minutes and a half-dozen replies to get a one-sentence answer, the problem was not the question. The question was fine. The failure was in the information transfer.

The Expert's Burden

ESR's essay is fundamentally about reducing the cost imposed on the answerer. But there is a reciprocal obligation. If you are the expert, the owner, or the authority, you owe clarity to the asker.

I understand why this happens. It is usually a defensive mechanism. We front-load the caveats because we are terrified of being technically "wrong" or called out over some obscure edge case. We want to protect ourselves by dumping all our context onto the table at once.

But the obligation to respect someone else's time does not end when they finish asking the question.

A good answer reduces uncertainty. It shrinks the search space. A bad answer expands it.

If the audience still has the same question after you respond, you haven't helped them. You have just transferred your cognitive load onto them, forcing them to reconstruct your intent.

It is very similar to the problem with passive voice (something Cary Millsap has talked about for years). The real sin of passive voice isn't grammar. It is making the reader work to reconstruct causality.

Poor technical answers create the same problem. The audience must reconstruct the model, the assumptions, and the contract from fragments scattered across multiple replies.

Answer Like an API

Answering questions effectively is an architecture skill.

A systems person naturally thinks in contracts. What is the source of truth? What guarantees does the system make? What is the documented behavior? Everything else is just plumbing.

We need to treat our answers the exact same way. When someone asks a question, they are usually looking for the contract.

Experts often begin with caveats, history, edge cases, implementation details, and exceptions. Resist that urge.

The answer goes first. Everything else is commentary.

Question: Does feature X do Y by default?
Better Answer: Yes, it defaults to Y out of the box. The configuration option allows you to override this behavior. Here is the link to the doc.

Experts often answer in chronological order ("Here is the history, here are the caveats, therefore the answer is X"). Good communicators answer in logical order ("The answer is X, here is why, here are the caveats").

It is the Minto Principle applied to engineering. The answer should be the first sentence, not the last.

Reduce Ambiguity

The same instinct that drives us toward explicit schemas, API definitions, and data contracts should drive our communication. Make the model explicit. Put the definition where everyone can see it.

The purpose of an answer is not to display expertise. The purpose of an answer is to transfer understanding.

Good architecture, documentation, and APIs all do one thing: they reduce ambiguity. Good answers should do the exact same.

Don't make people pull teeth to understand the system.