/DB

Encrypted columns

Encrypt sensitive fields at rest with the [Encrypted] attribute — transparent encrypt-on-write / decrypt-on-read, stored as bytea, using a built-in AES encryptor or a pluggable IFieldEncryptor (e.g. HashiCorp Vault).

updated 5 Jun 20263 min readv0.3.1View as Markdown

Mark any property [Encrypted] and Socigy transparently encrypts it on write and decrypts it on read. The value is stored as bytea ciphertext; your code keeps working with the original CLR type.

using Socigy.OpenSource.DB.Attributes;

[Table("patients")]
public partial class Patient
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public string Email { get; set; } = "";   // plain — use this to look rows up

    [Encrypted] public string Ssn { get; set; } = "";
    [Encrypted] public DateTime BirthDate { get; set; }
    [Encrypted] public string? Notes { get; set; }
}

How it works

  • The column is generated as bytea. On INSERT/UPDATE the value is serialized to bytes, encrypted, and bound as bytea. On SELECT the bytes are decrypted and deserialized back to the property's type.
  • null is never encrypted — a nullable encrypted column stays SQL NULL.
  • Encryption uses the ambient IFieldEncryptor configured via SocigyFieldEncryption (see below). Generated entity code has no DI access, so the encryptor is process-wide ambient state — exactly like SocigyDbDiagnostics.

Deferred decryption (`AutoDecrypt = false`)

By default every encrypted column is decrypted automatically on read. When most reads don't need the plaintext, set AutoDecrypt = false to skip that cost — the source generator then:

  • fills a read-only {Property}RawEncrypted (byte[]) with the raw ciphertext, and
  • adds a getter-only {Property}Decrypted that decrypts on first access and caches the plaintext into the property itself.
[Table("patients")]
public partial class Patient
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    [Encrypted(AutoDecrypt = false)]
    public string Ssn { get; set; } = "";
}

// On read, Ssn stays empty and PatientRawEncrypted holds the ciphertext:
var p = await Patient.Query(x => x.Id == id).WithConnection(conn).ExecuteAsync().FirstAsync();
byte[]? raw = p.SsnRawEncrypted;   // raw ciphertext, no decryption performed
string ssn = p.SsnDecrypted;        // decrypts once, caches into p.Ssn
// p.Ssn == ssn now (cached)
NOTE
Writes always encrypt the plaintext property (Ssn). For a read-modify-write of an AutoDecrypt = false row, access {Property}Decrypted first so the plaintext is populated before you save.

Supported types

Any common CLR value type plus string and byte[]: bool, all integer / floating / decimal types, char, string, Guid, DateTime, DateTimeOffset, TimeSpan, byte[], enums, and Nullable<> of these. Other types throw NotSupportedException.

NOTE
[Encrypted] cannot be combined with [ValueConvertor], [JsonColumn], or [RawJsonColumn] in this version — the source generator reports error SCGDB002.

Encrypted columns are not queryable

Encryption is non-deterministic (a fresh random IV each time), so the same plaintext produces different ciphertext. Using an encrypted column in a WHERE, ORDER BY, SELECT-projection, or LIKE clause throws a clear NotSupportedException:

// ❌ throws — Ssn is [Encrypted]
await foreach (var p in Patient.Query(x => x.Ssn == ssn).WithConnection(conn).ExecuteAsync()) { }

// ✅ filter by a non-encrypted key, then read the decrypted value
var patient = (await Patient.Query(x => x.Email == email).WithConnection(conn).ExecuteAsync().FirstAsync());
var ssn = patient.Ssn; // decrypted automatically

Configuring the encryptor

Built-in AES (no external KMS)

AesFieldEncryptor is an authenticated AES-256-CBC + HMAC-SHA256 encryptor. Give it a 32-byte key from your secret store (never hard-code keys) and configure it once at startup:

using Socigy.OpenSource.DB.Core.Encryption;

byte[] key = Convert.FromBase64String(builder.Configuration["Encryption:Key"]!); // 32 bytes
SocigyFieldEncryption.Configure(new AesFieldEncryptor(key));

Do this before any encrypted column is read or written. If an [Encrypted] column is accessed with no encryptor configured, you get a clear InvalidOperationException.

HashiCorp Vault

For Vault-managed keys, use the optional Socigy.OpenSource.DB.HashiCorp package, which installs an IFieldEncryptor whose key is sourced from Vault.

Writing your own encryptor

Implement IFieldEncryptor (synchronous, local) and register it:

public sealed class MyEncryptor : IFieldEncryptor
{
    public byte[] Encrypt(byte[] plaintext) => /* ... */;
    public byte[] Decrypt(byte[] ciphertext) => /* ... */;
}

SocigyFieldEncryption.Configure(new MyEncryptor());
WARNING
Rotating the key requires re-encrypting existing rows — old ciphertext can't be read with a new key. Plan a migration before rotating.