Two Databases, One Rule

Most web apps start with one database. Users, settings, billing, content and the actual product data all sit side by side, joined freely. It's simple. It's fast to build. For most products, it's the right call.

For the healthcare data platform I've been building, I made a different call early: two databases, one rule.

Protected health information lives in one database. Nothing else does. And nothing in the other database is protected health information.

That sounds like an infrastructure detail. In practice it's the decision that shaped almost everything else: how auth works, how records are scoped, how data syncs and what I worry about.

What goes where#

The split is clean on purpose.

The platform database holds everything the web app needs to run as a product:

• Organizations and users
• Roles, memberships and an audit trail of permission changes
• Billing and subscriptions
• Marketing content and CRM data
• Pipeline schedules, runs and job status
• API keys for the public API

The PHI database holds clinical data and only clinical data:

• Medical records and their processing results
• Converted outputs, like FHIR bundles
• The standard vocabulary used for terminology mapping

There's also a small mirror of organizations and users in the PHI database, which I'll come back to.

Each one is its own managed Postgres instance, with its own credentials, its own connection string and its own access function in the code. When I write a query, I have to choose which database I'm talking to. There's no ambient connection that can reach both.

Why bother#

There are three reasons, and they get more important as you go down the list.

Blast radius. If the platform side has a bug, a leaked credential or an overly broad query, there's no patient data behind it to expose. The marketing pages, the CRM and the billing system can't leak PHI because PHI isn't in their database. The most common kinds of web app mistakes land on the side with the least at stake.

Clarity about what's sensitive. In a single database, sensitivity is a property of columns and tables, and it drifts. Someone adds a "notes" field to an otherwise boring table, and now that table is sensitive. With a hard split, the question "is this PHI?" has an architectural answer. If it is, it goes in the PHI database. That makes design reviews faster and audits much simpler.

Different operational rules. The PHI database can have stricter access, stricter backups, stricter logging and a smaller set of people and services that can touch it. The platform database can move faster. Trying to apply two sets of rules inside one database is where things get fuzzy.

The cost: you have to keep two worlds in sync#

The split isn't free. The biggest cost is that some facts need to exist on both sides.

A medical record belongs to an organization. Access to that record depends on who the user is and which organization they belong to. But users and organizations are platform concepts. So the PHI database needs to know about them, at least enough to scope records correctly and keep foreign keys honest.

The approach I took:

• The auth provider is the source of truth for organizations, memberships and users.
• A signed webhook fires on every change. The app verifies the signature, then writes the change to the platform database.
• The same handler mirrors the change into the PHI database, with a minimal set of fields.
• Identifiers are the same on both sides. An organization's ID is generated once and reused everywhere, so a session resolved against the platform database can scope a PHI query directly, with no translation table.

The "same IDs everywhere" rule sounds small. It's what makes the whole thing workable. Without it, every PHI query would need a lookup to translate one world's identity into the other's, and that lookup would become its own source of bugs.

Where it's still weak#

I want to be honest about where this design isn't finished, because these are the parts I'd want to know about if I were reading someone else's post.

Webhook sync can fail quietly. If the PHI database is unreachable when a membership changes, the mirror update fails. For now there's a manual sync script that catches the mirror up after an outage. That works, but it depends on someone noticing.

There's no reconciliation loop yet. Nothing periodically compares the auth provider's view of the world with both databases and repairs drift. That's on the roadmap, and it's the right answer. Event-driven sync plus a periodic reconciler is the boring, reliable pattern for anything that has to agree across systems.

Mirrors are a temptation. Once there's a copy of users in the PHI database, it's tempting to add a field or two there "just for convenience". The rule is that the mirror stays minimal. Everything about a user that isn't needed to scope clinical data stays on the platform side.

Scoping every query, every time#

Splitting the data is half of it. The other half is making sure every query against clinical data is scoped to what the caller is allowed to see.

The platform has four scopes, each with its own part of the app:

• Platform staff, who can see across organizations for support and operations
• Network operators, like health information exchanges, who see the provider organizations under them
• Providers, who see their own organization
• Patients, who see their own records

When a request comes in, the session is resolved once. The result carries the user, the scope, the organization, a set of capability flags and a data filter. Every data access goes through one function that applies that filter to the query before it runs.

In pseudo-code:

session = resolve_session(request)
query   = records.select(...)
query   = apply_data_filter(query, session.data_filter)
rows    = phi_db.run(query)

Two habits make this hold up:

Check capabilities, never role names. Code asks "can this session manage pipelines?" and never "is this user an admin?" Roles change. Capabilities are what the code actually means. The most common permissions bug I've hit in this codebase came from gating a provider action on a permission only network operators had. Thinking in capabilities makes that mistake easier to see.

Make the filtered path the easy path. If the scoped query helper is the shortest way to get data, people (and agents) will use it. If it's optional, someone will skip it on a deadline.

Analytics without raw PHI#

The last piece is analytics. Research-style queries across many records are exactly the kind of work you don't want running against identifiable data.

The design is that analytics run against a de-identified warehouse, not against the PHI database directly. The pipeline writes standardized outputs, and the analytics side gets a version with direct identifiers removed. Analysts get the shape and the statistics. They don't get names and addresses.

Small things that add up#

A few smaller rules sit around the big one:

• Secrets live in a secret manager, are pulled at runtime and never get committed. Local development pulls them into a gitignored, permission-restricted file that's regenerated on every start.
• Privileged keys never reach the browser. Anything that can bypass scoping runs server-side only.
• Webhooks are signature-verified, both for auth events and for payments.
• Development and testing use synthetic or de-identified data wherever possible.

None of these are unusual. Together, they make the default behavior safe, which is the only kind of safety I trust.

Would I do it again?#

Yes, without hesitation.

The split added real work: two schemas, two migration configs, a sync path and a mirror to keep honest. It also made a lot of hard questions easy. Where does this new field go? Who can see this table? What happens if this service is compromised? The answers mostly fall out of one rule.

If you're building anything that touches health data, or any data where a leak would really hurt someone, I'd start here. Draw the line in the infrastructure, not just in your head, and make every query cross it on purpose.

The takeaway#

The safest data is the data that isn't there. Separating patient data into its own database means most mistakes happen somewhere they can't hurt anyone. The price is a sync problem, and a sync problem is a problem I'd much rather have.