Multi-Tenant Postgres Done Right: Three Rules From Building Cartographer

Evidence, Not Guesses, About How AI Sees You

Cartographer exists to answer a question that used to be unanswerable: when an AI search engine or assistant looks for information about your brand, what does it actually find, and how does that compare to your competitors? It runs continuous, evidence-first analysis against real provider traffic on behalf of paying organizations, and every organization’s data has to stay strictly separated from every other’s – at the database layer, not just in the application code sitting on top of it.

Building and operating that isolation surfaced three rules we now apply to every multi-tenant Postgres system we touch, on this product and every client engagement since.

Rule One: The Policy Is the Authority, Not the Query

It’s tempting to think of a row-level security policy as a backstop – the thing that catches a mistake in the application query. In a system doing real multi-tenant isolation, the relationship runs the other way: the policy is the actual rule, and the application query is just one of several things that has to agree with it.

The failure mode we learned to watch for is subtle: a rule gets relaxed in application code – a requirement dropped from a query, a tolerance loosened in a validation function – while the RLS policy enforcing the old, stricter version is never updated to match. The application believes the behavior changed. The database was never told. The result isn’t a security hole; it’s usually the opposite and more confusing kind of bug – correct data that the database refuses to show, because the two layers now disagree about what “correct” means.

The fix is procedural, not clever: whenever an access rule changes, check the policy definition directly, not just the application code that calls into it. If a rule lives in RLS, changing the application’s idea of that rule is not the same as changing the rule.

Rule Two: Verify Migrations by Rebuilding and Diffing

A migration history that applies cleanly, in order, on a fresh database is not proof the resulting schema matches what’s actually running in production. Grants, role permissions, and one-off provisioning steps can drift away from the migration files that are supposed to describe them, especially in a schema that’s been through months of incremental change.

The verification we now run as routine, not just when something looks wrong: dump the schema directly from a healthy, live environment, replay the full migration history onto a disposable branch, and diff the two. A clean diff – or one with only expected, intentional differences – is the actual proof that the migration history is a trustworthy specification. Anything else means the history and reality have quietly diverged, and it’s better to find that on a disposable branch than during an incident.

Rule Three: Compare Timestamps at the Precision Your Data Actually Supports

Different systems in a pipeline often don’t record time at the same precision. An application might record a start time down to the microsecond while an upstream provider reports its own timestamps truncated to the whole second. If a validation rule compares those two values directly – checking that one happened no earlier than the other – the coarser-precision value will, by construction, sometimes look earlier than it really was, simply because of rounding, not because anything actually happened out of order.

The fix isn’t a wider tolerance window, which just moves the boundary around and hides the same bug at a different threshold. It’s comparing both sides at the coarser precision the weaker data source actually supports – truncating the more precise timestamp down to match, rather than asking the less precise one to pretend to a resolution it doesn’t have. It’s a small rule, but it’s the kind of thing that’s invisible in code review and only shows up once you trace exactly what each side of a comparison is measuring.

The Takeaway

None of these three rules are exotic. They’re the kind of thing that’s obvious once written down and easy to miss while building under normal pressure, because each one requires checking a layer slightly below the one you’re already looking at. Frame every multi-tenant guarantee around which layer actually enforces it – the database, not the application sitting on top of it. Build verification into the migration process itself, as a rebuild-and-diff step, not a one-time audit. Prove timestamp logic against the actual precision of every system it touches, not the precision you assumed. Transfer the specific rule, in writing, so the next engineer on the system inherits the lesson instead of rediscovering it.

If your product needs the same rigor around multi-tenant data isolation, see how we approach engineering engagements, browse our other projects, or talk to us about what you’re building. And if you want to see what evidence-first AI visibility looks like in practice, Cartographer is live.