Why Your 'Bulletproof' Database Architecture Will Fail: A Senior Engineer Debate

What follows is a real technical debate between two senior engineers. One walked into a design review confident his schema was perfect. He walked out with a new understanding of what "bulletproof" actually means.

We're publishing this because most engineering blogs sanitize the conflict. They show you the "after" without the messy "during." But real growth happens in the friction. Real learning happens when someone tells you your code is broken and forces you to defend it.

This is that conversation. Unfiltered. With the lessons we both learned.

The Opening Conflict: When "Bulletproof" Meets Reality

It started with a single word. I had spent weeks designing a multi-tenant SaaS database schema. PostgreSQL, Row Level Security, clean separation of concerns. I presented it to the team and made one critical mistake: I called it "bulletproof."

The response was immediate and brutal:

I wanted to push back. But then came the counter-argument:

That hit differently. Not an attack on my competence—a precise statement about what the word actually means in engineering.

The Multi-Tenancy War: Shared Tables vs. True Isolation

The first major battleground was multi-tenancy. I had chosen the "standard" SaaS pattern: shared database, shared schema, Row Level Security to keep tenants apart.

The attack was surgical:

"Rookie mistake" stung. But was it wrong? I pushed back:

The escalation continued:

He was right. I hadn't. I had chosen the default pattern without analyzing alternatives.

The Truth We Both Agreed On

After the heat subsided, we reached consensus: Shared tables are only safe if tenant lineage is enforced by foreign keys, not by application logic or RLS alone.

What does that mean practically?

• Every child table must carry tenant_id and validate it against its parent via composite foreign keys
• Composite primary keys should include tenant_id to physically cluster tenant data
• Partitioning by tenant isn't optional at scale—it's mandatory for both security and performance
• RLS is a backup, not the primary isolation mechanism

My opinion: The "shared everything" approach works for MVPs, but the moment you have paying customers, you need defense-in-depth. If a single RLS policy bug can leak data, your architecture isn't production-ready.

The SECURITY DEFINER Trap: When Helper Functions Become Backdoors

I was proud of my helper functions. To improve developer experience, I had wrapped complex logic in SECURITY DEFINER functions with elevated privileges.

This is where the conversation got intense:

"Cute but worthless." I felt that one. But he wasn't wrong. I had checks, but they were incomplete:

What I Was Missing

Here's what I didn't fully understand: In PostgreSQL, a SECURITY DEFINER function runs with the privileges of its owner, not the caller. If that function references an unqualified object name and the search_path isn't locked down, an attacker can:

• Create a malicious table or function in the public schema
• Name it the same as something your function calls
• Watch your function execute their code with elevated privileges

The fix is straightforward but non-negotiable:

• Lock the search_path explicitly in every SECURITY DEFINER function: SET search_path = my_schema, pg_catalog
• Revoke CREATE on public for all roles except superusers
• CI must verify ownership—reject any SECURITY DEFINER function not owned by a trusted role
• Audit execution paths—know exactly what each function can call

RLS Performance: The Silent Killer

I had leaned heavily on Row Level Security. It's elegant. It's built into PostgreSQL. It "just works."

Except when it doesn't:

This was the moment I realized I had never actually profiled my RLS policies:

The Ugly Truth About RLS Performance

Here's what the PostgreSQL documentation won't tell you in bold letters: RLS policies are evaluated per row during query execution. If your policy calls a function, that function runs for every row scanned.

Worse, if that function isn't marked LEAKPROOF, the query planner can't push predicates down or use indexes efficiently. You end up with sequential scans on million-row tables.

What we agreed RLS requires:

• Simple, inline predicates—avoid function calls in policies when possible
• LEAKPROOF functions only—if you must use functions, they must be marked appropriately
• EXPLAIN in CI—if any RLS-protected query shows a sequential scan on a large table, the build fails
• Consider alternatives—sometimes a view with explicit WHERE clauses outperforms RLS

My take: RLS is beautiful for authorization, but it's not "free." Every policy is code that runs at query time. Treat it with the same performance scrutiny as any other hot path.

Immutability Without Lifecycle: A Storage Bomb

I had designed several tables as append-only for audit purposes. "Immutable data is secure data," I reasoned.

Short, brutal, correct:

This wasn't a flaw in the concept of immutability—it was a flaw in my implementation. Append-only tables need:

• Time-based partitioning (monthly, quarterly) for efficient archival
• Retention policies—what gets archived? What gets deleted? When?
• Partition pruning—queries should automatically skip old partitions
• Cold storage migration—old partitions move to cheaper storage

The JSONB Trap: Flexibility vs. Query Planning

I love JSONB. Schemaless flexibility, GIN indexes, containment operators. What's not to love?

"Lazy." Another one that hurt. But:

The problem with JSONB in WHERE clauses is that PostgreSQL can't accurately estimate selectivity. It makes assumptions that are often wildly wrong, leading to poor execution plans.

The rule we landed on:

• JSONB is for storage—perfect for flexible payloads, metadata, configurations
• Extract query-critical fields—use generated columns or materialized fields for anything in a WHERE clause
• Index the extracted fields, not the JSONB paths
• Test with realistic data volumes—JSONB queries that work on 1000 rows collapse at 1M rows

The Convergence: What "Bulletproof" Actually Means

After hours of this, something shifted. The conflict became collaboration. I made a commitment:

The response was the first positive thing I'd heard all day:

That sentence captured everything. We weren't enemies. We were engineers trying to build something that wouldn't break under pressure. The conflict was the process.

The Shared Truth: A New Definition of "Bulletproof"

Here's what we agreed on. This is the doctrine that emerged from the debate:

What Changed in Our Architecture

For those who want the concrete outcomes, here's what we implemented after this debate:

1. Multi-Tenancy

• Composite foreign keys with tenant_id on every child table
• Range partitioning by tenant_id for high-volume tables
• Hybrid approach: shared tables for config, partitioned for data

2. Security

• All SECURITY DEFINER functions now have explicit SET search_path
• REVOKE CREATE ON SCHEMA public FROM PUBLIC is in our bootstrap migration
• CI checks function ownership and rejects unsigned definitions

3. Performance

• RLS policies are inline predicates only—no function calls
• CI runs EXPLAIN on critical queries and fails on Seq Scan
• JSONB fields used in queries are extracted to generated columns

4. Data Lifecycle

• All append-only tables are partitioned by month
• Automated partition creation and archival via pg_partman
• Retention policies defined and enforced

Final Thoughts: Conflict as Process

This conversation could have gone differently. I could have dismissed the criticism as aggressive. The critic could have stopped at "rookie mistake" and walked away feeling superior.

Instead, we both committed to finding the truth. And the truth wasn't that one of us was right—it was that real security and performance require proof, not claims.

If you're designing a multi-tenant database, I hope this dialogue saves you the painful lessons we learned. And if you're doing code reviews, I hope it reminds you: the goal isn't to win the argument. It's to build something that won't break.

Bulletproof isn't a word you say. It's a property you prove.

This post is based on real engineering discussions. Some sentences have been preserved verbatim to maintain authenticity. If you have questions about implementing any of these patterns, reach out to our engineering team.