# 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).

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.

```csharp
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.

```csharp
[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`](/database/0.3.1/advanced/generator-diagnostics).

## 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`:

```csharp
// ❌ 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:

```csharp
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`](/database/0.3.1/integrations/hashicorp-vault) package, which installs an `IFieldEncryptor` whose key is sourced from Vault.

## Writing your own encryptor

Implement `IFieldEncryptor` (synchronous, local) and register it:

```csharp
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.