Engineering

Field-level encryption in Postgres with a Prisma client extension

We've now added minidauth to three open source apps, Formbricks, Twenty and Cal.com's scheduling platform, and by the third one the Postgres side had settled into a pattern we'd be happy to reuse. Cal.com's version is a single Prisma client extension that does nothing unless an environment variable is set. Getting there took a couple of mistakes in the first two apps, and those turned out to be the useful part, so they're in here too.

Why we used an extension instead of editing each query

A Prisma client extension wraps every query on the shared client, so create, update, upsert, createMany and all the find methods go through one piece of code. Cal.com writes bookings from a lot of different routes. If we'd patched each of them by hand, it would only have been a matter of time before a new route wrote a name to the database in plaintext because nobody remembered to seal it. With the extension in place, sealing happens whether or not the person writing the route knows about it.

Picking the fields to seal

Once a column holds ciphertext, Postgres can't sort, filter or search on it any more, so the field list ends up being a design decision rather than "everything that looks personal". For Cal.com we sealed an attendee's name and phone number and a booking's title and description, and left two personal fields readable on purpose. The attendee's email is an indexed lookup key that Cal.com uses for de-duplication and seats, and sealing it would have broken those queries. The booking's location is something the code branches on, since it can be an integration name, a URL or a street address, so sealing it would have broken routing rather than just hiding an address.

Here's what that looks like for one made-up booking, depending on who is looking:

In the extension itself, that decision is a small map:

const SEALED: Record<string, string[]> = {
  attendee: ["name", "phoneNumber"],
  booking: ["title", "description"],
};

Bookings arrive with their attendees attached

Cal.com usually creates a booking and its attendees in one nested write, and reads them back with the attendees included. An extension that only looked at the top-level model would have sealed the booking's title and sent every attendee's name to Postgres untouched. So there's a second map listing which relations to follow, and on the write side the extension walks into create, createMany.data and connectOrCreate.create:

const RELATIONS: Record<string, Record<string, string>> = {
  booking: { attendees: "attendee" },
  attendee: { booking: "booking" },
};

What happens when the sealing service is down

We made writes and reads fail in opposite directions. If the sealing service can't be reached while a booking is being saved, the save throws an error. The alternative would be storing the name in plaintext because a dependency had a bad minute, which is exactly the kind of failure nobody notices until an audit.

Reads go the other way. If there's no signed-in reader, the reader doesn't hold the role, or the service is unreachable, the booking still loads and the sealed fields just show up as ciphertext. Someone seeing a string of gibberish where a name should be is annoying, but it's a lot better than a booking page that won't open at all.

The shortcut that let plaintext through

This one we got wrong first, in Formbricks. Sealed values are stored as ms1: followed by the ciphertext, and when a form is saved again without changes, the unchanged answers come back already sealed. Sealing them a second time produced values that could never be opened. So we added a check that let anything shaped like a sealed value pass straight through.

The trouble is that anyone can type something shaped like a sealed value. A string that starts with ms1: but has plaintext after it passed the check and was stored exactly as it arrived, in a column everyone believed was sealed. You can try it here:

A local check can't tell a real sealed value from a fake one, because telling them apart means verifying a signature from the vendor key, and only the network can do that. We dropped the shortcut and now send every non-empty value headed for a sealed column to be sealed. To stop the double-sealing problem coming back, the app makes sure it only ever handles opened values, so an unchanged field goes back as plaintext and gets sealed once. Cal.com's extension was written after this, which is why its comments are so insistent about not skipping the prefix.

A field we thought was sealed and wasn't

Twenty stores a person's secondary links as a JSON string holding a list, and we wanted to seal both the URL and the label of each link. Two separate paths into the same field each parsed that string into its own copy of the list, sealed their part, and wrote their copy back. The second write won, so a link's label ended up sealed while its URL sat in the clear right next to it. The fix was to parse the list once and share it between both paths.

Around the same time we noticed task titles and bodies, and note bodies, had been left readable while the fields beside them were sealed. None of these showed up as errors, and that's the uncomfortable part of this kind of bug: the app behaves the same whether a field is sealed or not, so the only reliable check is to look at what Postgres has actually stored.

Opening a page of results in one go

The first version made one network round trip per field. That was fine for a single record and painful for a list, and it made sealing a table of existing records impractical. Now the extension collects every sealed value in a result, including the included attendees, and opens them together in a single fan-out to the network. For production, the fork's notes suggest going one step further: protect one data key per record with the network and encrypt that record's fields locally under it, so the cost is one network operation per record no matter how many fields it has.

Who does the reading

There's no service account that can open everything. Cal.com's session middleware attaches the signed-in user to each request, and the sealing service decrypts as that user, and only if the quorum's grant says they hold the reading role. A request without a user gets ciphertext back. That means there's no credential on the server that an attacker could use to decrypt whatever they like, which is usually the first thing they'd go looking for.

What it costs

You lose search and sorting on the sealed columns, reads that open fields make a network call, and you have to be honest about which personal fields are really lookup keys. What you get is a database, and every backup of it, where the fields that matter are ciphertext, and where reading them depends on a role your admins approved rather than on the app.

The Cal.diy project page lists what the fork seals, and the fork itself has the whole change. The Formbricks and Twenty pages cover the other two. If you're trying this on your own Prisma app and get stuck, come ask in the Discord.

Try it in two commands More posts