Field Encryption at Rest System Design

Some columns hold things you must store and display but should never be legible straight out of the database. Personal notes on a client, a third-party token, a contact detail, an identity number. Database-level encryption protects the files on disk, which guards against a stolen volume and nothing else: anybody with a connection sees plaintext, and so does every backup dump, every replica and every analyst who was given read access for an afternoon in 2024.

Encrypting those columns in the application closes that. The cost is usually that the model gets noisy: an encrypt call before every write, a decrypt after every read, a bug the first time somebody adds a field and forgets one, and a schema that can never adopt encryption later without a migration.

A custom column type removes all of that. The field is declared as an encrypted string, the driver encrypts on the way in and decrypts on the way out, and nothing else in the application knows. What remains is one real constraint that cannot be designed away, and the honest thing is to state it rather than hide it.

The system has to be able to:

• Encrypt a named field transparently, with no calls in the handler or the service.
• Use authenticated encryption, so a tampered value fails rather than decrypting to nonsense.
• Use a fresh nonce per write, so identical values do not produce identical ciphertext.
• Version the scheme, so the algorithm can change later without ambiguity about existing rows.
• Pass values through unencrypted when no key is configured, so the feature can be adopted without a migration.
• Refuse to start on a key of the wrong length, rather than quietly running without encryption.
• Encrypt the rows that are already there, in batches, when the feature is turned on.

Functional requirements

• An EncryptedString type usable as a model field.
• AES-256-GCM with a random 12-byte nonce per write.
• A version prefix on every stored value.
• A process-wide key loaded from configuration as base64 that decodes to 32 bytes.
• An empty key disabling encryption, with values stored as plaintext.
• A non-empty key of the wrong length failing at startup.
• A backfill that encrypts existing plaintext in a column, in batches.
• JSON serialisation of the plaintext, so an API response is unaffected.

Non-functional requirements

• Authenticated, not just encrypted. GCM means a modified ciphertext fails to open. Encryption without authentication protects confidentiality and not integrity, and integrity is what you need when the threat is somebody with write access to the database.
• Non-deterministic. a new nonce per write means the same value stored twice looks different. That is required for confidentiality and it is exactly why such a column cannot be queried by equality.
• Invisible to the application. the handler, the service and the Zod schema see a string. If using it required thinking about it, some field somewhere would be missed.
• Adoptable later. no key means passthrough, so turning the feature on is a configuration change and a backfill rather than a schema migration.
• Loud on misconfiguration. a 16-byte key where 32 is needed stops the process. Running without the encryption that was asked for is the worse outcome.

Numbers for a mid-sized deployment. They are here to size the thing, not to predict your traffic: change an assumption and the sums below move with it.

Assumptions

A custom column type sitting in the driver interface, so the encryption happens between the model and the database and nowhere else.

Core components

• EncryptedString. the field type. Implements the driver value and scan interfaces, which is where the encryption and decryption live.
• Key holder. the process-wide key, loaded once at startup behind a lock. Nil means disabled, which is a supported state rather than an error.
• Versioned envelope. the prefix that tags a value as encrypted and says which scheme wrote it. A value without the prefix is plaintext, which is how passthrough and partial backfill coexist.
• AEAD. AES-256-GCM. One construction, chosen once, rather than a configurable cipher suite nobody is qualified to configure per project.
• Backfill. walks a column in batches, encrypting anything without the prefix and leaving anything with it alone, so it is safe to run twice.
• JSON hook. serialises the plaintext, so an encrypted field is an ordinary string in an API response and in the generated TypeScript type.

Request flow

1. 1A handler binds a request and sets the field. It is a string type, so nothing here is different from any other field.
2. 2The row is saved. GORM asks the field for its database value, which is the hook the whole design hangs on.
3. 3A fresh 12-byte nonce is generated for this write. Reusing a nonce with the same key is the one catastrophic mistake available in GCM, so it is never derived from the value or a counter.
4. 4The key is read from the process-wide holder. A nil key means encryption is disabled and the value is returned as it is, which is what lets a project adopt this later.
5. 5The nonce, the ciphertext and the authentication tag are concatenated, base64 encoded and prefixed with the scheme version. The column now holds something opaque, and the prefix is what will let a future version rotate the algorithm without guessing how an old row was written.
6. 6On a read, the column value comes back. Without the prefix it is plaintext from before the backfill and is used as it is; with the prefix it is opened, and a failure to open is an error rather than a fallback, because a value that will not authenticate has been tampered with or is encrypted under a different key.
7. 7The field is plaintext from here on. The API response, the generated TypeScript type and the admin form all see an ordinary string.
8. 8Separately, turning the feature on runs a backfill over the existing rows, in batches, skipping anything already prefixed so it can be rerun after an interruption.

Data flow

• Encryption happens in the driver interface, which means it happens for every writer: the handler, a job, a seed, a bulk import. There is no path to the column that bypasses it.
• The prefix makes the column self-describing. Plaintext and ciphertext can coexist in it during a backfill, which is what makes the backfill resumable.
• JSON carries plaintext deliberately. Encrypting the API response would mean the client needs the key, which would mean the key is on the client.
• An encrypted column cannot be sorted, searched or filtered by equality, because the ciphertext for the same value differs every time. That is the price, and it is the reason this is for data you keep and show rather than for keys and lookup columns.

There is no new table. The design is entirely in the column type, which is the point.

Where it lives

• The key never goes in the database. It comes from the environment or a secret manager, which is what separates it from the ciphertext it protects.
• Backups contain ciphertext. A leaked dump without the key is not a breach of those columns, which is the whole objective.
• A replica or an analytics connection sees ciphertext too, because the encryption is above the database rather than inside it.

Core types

Design principles applied

• Put it where nothing can bypass it. in the driver interface, every writer is covered. In a service method, only the writers that call that method are.
• One construction, not a choice. AES-256-GCM, fixed. A configurable cipher suite is an invitation to configure it wrongly in a project that has no cryptographer.
• Version from the first write. the prefix costs seven bytes and is the only thing that makes rotating the scheme later possible rather than archaeological.
• Fail loudly on the key, quietly on its absence. no key is a decision. A malformed key is a mistake, and the difference should be the difference between starting and not.

Patterns

• AES-GCM with hardware support is far faster than the database round trip that delivered the row, so the CPU cost does not appear in a profile.
• Storage grows by about 1.55x on the encrypted columns only, which is immaterial unless the column is large.
• The real scaling constraint is not throughput, it is that the column is unqueryable. A field that needs sorting, searching or an equality lookup cannot be encrypted this way, and that has to be decided before the data exists.
• The backfill is batched, so it runs against a live table without holding a long transaction, and can be interrupted and resumed.
• A key rotation is a second backfill: decrypt under the old key, encrypt under the new, with the version prefix telling the two apart.
• No new table and no new service means nothing extra to scale, replicate or operate.

What breaks first

• The column cannot be queried. non-deterministic ciphertext rules out equality, ordering, prefix search and unique constraints. This is the constraint that actually bites, and it bites after the data exists.
• Key loss is data loss. there is no recovery. A lost key makes every encrypted column permanently unreadable, and that is the feature working as designed.
• The key in the wrong place. a key committed to the repository or sitting in the same backup as the database provides no protection at all while looking exactly like protection.
• Mixed state after a partial backfill. plaintext and ciphertext in one column is correct during the backfill and misleading if it is never finished.

What to do about it

• Keep a blind index for lookups. a deterministic HMAC of the normalised value in a second column gives exact-match lookup while the encrypted column stays non-deterministic.
• Use a managed key service. KMS or Vault, with the field key wrapped rather than stored. Rotation and audit come with it, and the key stops being an environment variable somebody can print.
• Separate the key from every backup. different system, different credentials, different retention. If the dump and the key travel together, nothing was gained.
• Finish the backfill and then verify. a count of unprefixed values in the column is one query and is the only way to know the state is not mixed.