Introduction

I built Loom Vault because I wanted a place to stash API keys, .env files, and private notes without trusting anyone else's cloud. No Notion. No 1Password. Just my own PostgreSQL instance running on a Proxmox LXC, a MinIO bucket for file attachments, and a Next.js app that talks to both.

Initially, storing environment variables as a single flat list worked fine. But as I spun up more homelab services, personal apps, and Coolify deployments, key collisions (like having multiple DATABASE_URL keys) and manual card copying became a headache. I recently refactored the vault to support folder-scoped Secret Groups and bulk .env developer view syncing—all while keeping PostgreSQL pgcrypto database-level encryption intact.

This post walks through the encryption architecture, the Prisma extension pattern, how I refactored the schema for folder-scoped secrets, and the honest limitations of this approach.

The Problem: Encrypt Secrets Without Trusting the App Layer

Most web apps encrypt secrets like this: grab the plaintext from a form, encrypt it in application code (Node, Go, Python), and store ciphertext in the database. Decryption reverses the flow: read from DB, decrypt in app, show to user.

This works. But it means the encryption key has to be available in the application process. If someone exploits a vulnerability in your API and dumps the environment, they get the key AND access to the database.

For Loom Vault, I wanted to shrink that surface by making the app a dumb pipe: receive a value, hand it to PostgreSQL, let Postgres handle the encryption via pgcrypto, and return decrypted results on read.

However, a single flat key namespace created a second problem: every homelab service or side project needs keys named DATABASE_URL, REDIS_PASSWORD, or API_KEY. Without folder scoping, keys collided globally. And when deploying to Coolify or Docker containers, copying secret cards one by one was agonizing. I needed project folders with raw .env bulk sync without sacrificing database encryption.

sequenceDiagram
    autonumber
    actor User
    participant Action as Next.js Server Action
    participant Extension as Prisma Client Extension
    participant PG as PostgreSQL (pgcrypto)
    participant DB as ByteA Storage
    User->>Action: 1. Submit Plaintext Secret
    Action->>Extension: 2. prisma.secret.createEncrypted()
    Extension->>PG: 3. SQL $executeRaw: pgp_sym_encrypt(value, env_key)
    PG->>DB: 4. Store OpenPGP Ciphertext (bytea)
    DB-->>User: 5. Secret Created (Plaintext never stored!)

The Solution: pgcrypto Inside PostgreSQL

PostgreSQL has shipped with the pgcrypto extension for years. It gives you pgp_sym_encrypt() and pgp_sym_decrypt(), which wrap OpenPGP symmetric encryption. You pass in a plaintext string and a passphrase, and Postgres hands you back a bytea value that is encrypted at rest inside the database.

graph TD
    User(["User"]) -->|6-digit PIN| PBKDF2["PBKDF2 SHA-512"]
    PBKDF2 -->|verify| AES["AES-256-GCM Session"]
    AES -->|httpOnly cookie| Action["Next.js Server Action"]
    Action --> Extension["Prisma Client Extension"]
    Extension -->|createEncrypted| Raw["$executeRaw SQL"]
    Raw -->|pgp_sym_encrypt| PG[("PostgreSQL pgcrypto")]
    PG -->|bytea column| Secrets[("Encrypted Secrets")]
    Action -->|upload| MinIO["MinIO S3 Proxy"]

The setup is a single line:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

To support folder scoping while preventing key collisions across projects, I introduced a SecretGroup table and updated the Secret model in Prisma to use PostgreSQL 18 uuidv7() primary keys and a compound unique index:

model SecretGroup {
  id        String   @id @default(dbgenerated("uuidv7()")) @db.Uuid
  name      String   @unique
  secrets   Secret[]
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  @@map("secret_groups")
}

model Secret {
  id             String       @id @default(dbgenerated("uuidv7()")) @db.Uuid
  key            String
  encryptedValue Bytes        @map("encrypted_value") @db.ByteA
  description    String?      @db.Text
  groupId        String?      @map("group_id") @db.Uuid
  group          SecretGroup? @relation(fields: [groupId], references: [id], onDelete: SetNull)
  createdAt      DateTime     @default(now()) @map("created_at")
  updatedAt      DateTime     @updatedAt @map("updated_at")

  @@unique([key, groupId])
  @@map("secrets")
}

Notice encryptedValue is Bytes @db.ByteA and the key uniqueness is scoped via @@unique([key, groupId]). This allows the exact same key name (DATABASE_URL) to exist in different project folders ("Coolify", "Staging", "Homelab").

The Prisma Extension Pattern

Prisma's $extends API lets you bolt custom methods onto generated models. I used it to create prisma.secret.createEncrypted(), prisma.secret.findManyDecrypted(), and prisma.secret.updateEncrypted(). Under the hood, these call $executeRaw and $queryRaw with parameterized SQL that invokes pgcrypto, now accepting an optional groupId:

// Prisma client extension for encrypted secrets using pgcrypto
export const prisma = prismaClient.$extends({
  model: {
    secret: {
      async createEncrypted(data: { key: string; value: string; groupId?: string | null; description?: string }) {
        const encryptionKey = process.env.ENCRYPTION_KEY;
        if (!encryptionKey) throw new Error('ENCRYPTION_KEY is not defined.');
        await prismaClient.$executeRaw`
          INSERT INTO "secrets" ("id", "key", "encrypted_value", "description", "group_id", "created_at", "updated_at")
          VALUES (
            uuidv7(),
            ${data.key},
            pgp_sym_encrypt(${data.value}::text, ${encryptionKey}::text),
            ${data.description || null},
            ${data.groupId || null}::uuid,
            NOW(),
            NOW()
          )
        `;
      },
      async findManyDecrypted() {
        const encryptionKey = process.env.ENCRYPTION_KEY;
        return prismaClient.$queryRaw`
          SELECT
            "id", "key",
            pgp_sym_decrypt("encrypted_value", ${encryptionKey}::text) as "value",
            "description", "group_id" as "groupId",
            "created_at" as "createdAt", "updated_at" as "updatedAt"
          FROM "secrets"
          ORDER BY "key" ASC
        `;
      },
    },
  },
});

To support raw bulk .env copy-pasting, I built syncSecretsAction. When you switch to "Developer View" within a project folder tab, you get a full text block of KEY=VALUE pairs. Modifying this block parses the raw text, diffs it against existing secrets in that specific folder scope, and executes atomic pgcrypto inserts, updates, or deletes.

The Full Encryption Flow

Here is how a secret moves through the system:

Write: Server Action receives plaintext → calls prisma.secret.createEncrypted() → Prisma extension reads ENCRYPTION_KEY from env → sends pgp_sym_encrypt(value, key) to PostgreSQL → Postgres encrypts the value using OpenPGP symmetric encryption → stores ciphertext in bytea column.
Read: Server Action calls prisma.secret.findManyDecrypted() → Prisma extension sends pgp_sym_decrypt(encrypted_value, key) in the SELECT clause → Postgres decrypts inside the query and returns plaintext as a result column → plaintext flows over the wire to the application (on localhost or private network).

Auth That Does Not Get in the Way

The app is single-user by design. On first launch, you set a 6-digit PIN. The PIN gets hashed with PBKDF2 (SHA-512, 100k iterations) and stored with a random salt. Login generates an AES-256-GCM encrypted session token, stored in an httpOnly cookie with a two-hour expiry. No user registration, no password reset flow, no OAuth dance. Just a numeric keypad and a session cookie.

File attachments go to MinIO (S3-compatible object storage) through a Next.js API route. The route checks the session token before proxying the upload, so the MinIO bucket is never publicly exposed. Images pasted into markdown notes stream directly to S3 behind the authenticated proxy.

Full-Text Search Across Encrypted Data

You cannot search inside encrypted values. That is the point of encryption. But you can search the key and description columns, which are plaintext. Loom Vault uses PostgreSQL tsvector indexes with prefix matching across notes, secrets, and config guides, all unified under a single Ctrl+K search bar. The search query hits keys and descriptions (not encrypted values), ranked by relevance. For a personal vault with maybe a few hundred secrets, this is more than enough.

The Result: What I Got and What I Gave Up

This architecture delivers exactly what I wanted: database-layer encryption with a clean ORM API and project-level isolation.

Here is what the updated UX looks like:

Folder Navigation: Sidebar tabs for "All Secrets", "Unassigned", or custom project groups ("Analytics", "Coolify Apps").
Visual Card Security: Eye icons to toggle value obfuscation (••••••••), explicit lock/unlock edit toggles, and one-click copy buttons (Copy Value and Copy KEY=VALUE).
Developer View: A raw .env text block editor scoped per folder for pasting or copying entire deployment configurations in seconds.

The trade-offs remain honest:

Encryption key in env var: ENCRYPTION_KEY lives in the server environment. The host LXC sits behind Tailscale on my own hardware.
Cipher: pgcrypto uses OpenPGP symmetric encryption (CAST5 by default).
Search limitations: Encrypted values cannot be indexed or searched; only keys and descriptions are indexed via tsvector.
Single-user design: Symmetric encryption with a global passphrase is intended for single-tenant homelab setups.

Key Takeaways

Database-level encryption shrinks the attack surface: The application never touches plaintext secrets outside of one env var reference.
Prisma extensions keep SQL DRY: You get the ergonomics of an ORM while keeping pgcrypto calls completely abstracted from server actions.
Compound unique constraints enable clean folder scoping: @@unique([key, groupId]) lets you reuse standard variable names across project folders without collisions.
Bridge visual cards with raw developer blocks: Visual cards with lock toggles prevent accidental edits, while raw .env text sync accelerates CLI and Docker container deployments.
Accept trade-offs and document them: A single ENCRYPTION_KEY is a pragmatic, secure-enough choice for personal homelab infrastructure without overcomplicating setup with cloud KMS services.

Encrypting Secrets at the Database Layer: pgcrypto, Prisma Extensions, and Why I Trust PostgreSQL with My Keys