Database Encryption

PII stored in Postgres (names, emails, addresses, phone numbers, domains) is encrypted at the application layer, not just at the disk layer. The mechanism is transparent: service code reads and writes ordinary Go values, and encryption/decryption happens inside the pgx driver as values cross the wire.

Three building blocks make this work:

1. internal/crypt — the cipher, the crypt.Encrypted type, and hashing helpers.
2. pgx type-map hooks — registered per-connection in AfterConnect, they intercept encode/scan plans for crypt.Encrypted.
3. The encrypted Postgres domain + one sqlc override — columns declared with the encrypted domain map to crypt.Encrypted in generated code via a single db_type rule.

graph LR
    SVC[Service code<br/>string / struct] -->|"crypt.Encrypted(name)<br/>or crypt.Marshal(v)"| SQLC[sqlc query]
    SQLC --> ENC[pgx encode plan<br/>AES-GCM encrypt]
    ENC -->|BYTEA| PG[(Postgres<br/>encrypted domain)]
    PG -->|BYTEA| SCAN[pgx scan plan<br/>AES-GCM decrypt]
    SCAN --> SQLC2[sqlc result<br/>crypt.Encrypted]
    SQLC2 --> SVC

The crypt package

Everything lives in internal/crypt:

File	Contents
crypt.go	Package state + Init; Encrypt / Decrypt (AES-GCM, []byte)
encrypted.go	crypt.Encrypted type + generic Marshal / Unmarshal helpers
register.go	pgx type-map registration + encode/scan plan wrappers
hash.go	HashField — HMAC-SHA256 blind indexes for lookups
password.go	HashPassword / ValidatePassword — bcrypt (cost 12)

The cipher — AES-GCM

The cipher is opaque: there is no exported Cipher type. crypt.Init is called once at startup and builds the package’s AES-GCM state from CRYPT_ENCRYPTION_KEY. The key must be 16, 24, or 32 bytes (AES-128/192/256).

func Init(cfg *config.Config) error // builds AES-GCM state + hash key
func Encrypt(plainText []byte) ([]byte, error)
func Decrypt(cipherText []byte) ([]byte, error)

• Encrypt generates a fresh random nonce per call and prepends it to the ciphertext, so each row is self-contained.
• Decrypt splits the nonce back off and opens the ciphertext. GCM authentication means tampered or wrong-key data fails loudly instead of decrypting to garbage.
• Both operate on []byte — the plaintext is treated as opaque bytes, whether it originated as a string or a JSON payload.

Because the nonce is random, encryption is non-deterministic: encrypting the same email twice produces different ciphertexts. This is what forces the blind-index pattern for lookups.

crypt.Encrypted

crypt.Encrypted is a []byte type that implements pgx’s pgtype.BytesValuer and pgtype.BytesScanner:

• BytesValue() encrypts the plaintext for storage in a BYTEA column.
• ScanBytes() decrypts BYTEA data read from the database.

Both methods delegate to the package cipher initialized by crypt.Init. The underlying type is []byte, so how you build and read the value depends on what it holds:

Scalar strings (names, emails, …) convert directly — no helper needed:

params.Email = crypt.Encrypted(email) // string -> Encrypted (write)
email := string(u.Email)              // Encrypted -> string (read)

Structured values (arrays of objects, structs) go through the generic helpers, which JSON-encode into the encrypted column:

enc, err := crypt.Marshal(officers)          // []Officer -> Encrypted (write)
officers, err := crypt.Unmarshal[[]Officer](s.Officers) // read back

Unmarshal[T] decodes an empty value to the zero value of T. Scalars are stored raw (no JSON quoting); only Marshal/Unmarshal add a JSON layer.

Note

Empty values are stored as a zero-length (non-NULL) BYTEA so they satisfy NOT NULL columns; ScanBytes maps zero-length data back to a nil Encrypted.

Tip

Do the JSON conversion in the service or FromDB layer — the model structs expose real string / []Officer fields on the wire, never crypt.Encrypted. Encryption stays automatic; the only manual step is Marshal/Unmarshal.

pgx integration

The wiring happens once per pooled connection in internal/db/db.go:

poolConfig.AfterConnect = func(ctx context.Context, conn *pgx.Conn) error {
    crypt.Register(conn.TypeMap())
    return nil
}

crypt.Register (in register.go) does three things to the connection’s pgtype.Map:

1. Prepends an encode-plan wrapper (TryWrapEncryptedEncodePlan). When a query argument is a crypt.Encrypted, the wrapper calls BytesValue() to encrypt, then delegates the resulting []byte to the standard bytea encoder.
2. Prepends a scan-plan wrapper (TryWrapEncryptedScanPlan). When a scan target is a *crypt.Encrypted, the wrapper receives the raw BYTEA bytes and calls ScanBytes() to decrypt in place.
3. Registers crypt.Encrypted as the default Go type for bytea, so pgx can resolve the type when scanning into an untyped destination (any).

Note

Encryption is keyed on the Go type (crypt.Encrypted), not the column OID. The wrap-plans fire whenever the value/target is crypt.Encrypted, which is what the sqlc override guarantees. RegisterDefaultPgType only covers the untyped-scan fallback — it does not mean “all BYTEA is encrypted”. Plain []byte columns (hash columns, future blobs) are never wrapped.

The encrypted domain + sqlc

Rather than list every encrypted column in sqlc.yaml, PII columns are declared with a PostgreSQL domain over BYTEA:

CREATE DOMAIN "encrypted" AS BYTEA;

Columns then use encrypted as their type, and a single db_type override in sqlc.yaml maps the domain to crypt.Encrypted:

-- in the table's migration
name    encrypted NOT NULL DEFAULT '',
address encrypted NOT NULL DEFAULT '',

# sqlc.yaml — one rule covers every encrypted column
- db_type: "encrypted"
  go_type:
    import: "github.com/huddlesurety/api/internal/crypt"
    type: "Encrypted"
- db_type: "encrypted"
  nullable: true
  go_type:
    import: "github.com/huddlesurety/api/internal/crypt"
    type: "Encrypted"

Caution

The nullable variant is required. A single db_type override only matches non-nullable columns; a nullable encrypted column falls back to interface{} without the nullable: true entry (same pattern as the uuid override).

Generated code then looks like:

type User struct {
    ID        id.ULID         `json:"ID"`
    Name      crypt.Encrypted `json:"name"`
    Email     crypt.Encrypted `json:"email"`
    EmailHash []byte          `json:"emailHash"`
    Password  string          `json:"-"`
    // ...
}

Adding a new encrypted column is a one-step change: declare it with the encrypted domain type in the table’s migration (which is also the schema sqlc reads), then regenerate. No per-column sqlc.yaml entry is needed.

Tip

The encrypted domain is defined once, in migration db/001_encrypted.migration.sql (CREATE DOMAIN "encrypted" AS BYTEA;), which sorts first and therefore runs before any table that uses it. Because sqlc reads the migrations themselves as its schema (schema: "db/*.migration.sql"), there are no separate per-package schema.sql files that would each need to repeat the domain.

Encrypted columns today

Table	Encrypted columns	Blind index
auth.user	name, email	email_hash
auth.organization	name, domain	domain_hash
auth.surety	name, address, phone, resident_name, state_of_authority	—
auth.contractor_account	name, address, phone	—
auth.invite	email	—

auth.otp.code_hash is HMAC-only (the code itself is never stored), and auth.user.password is bcrypt-hashed, not encrypted. The *_hash columns stay plain BYTEA — they are not the encrypted domain, because they must hold deterministic HMAC output, not ciphertext.

Blind indexes for lookups

Since AES-GCM ciphertexts are non-deterministic, WHERE email = $1 can never match an encrypted column. Fields that need equality lookups or uniqueness get a companion *_hash BYTEA column holding an HMAC-SHA256 of the normalized value:

func HashField(field string) ([]byte, error) {
    normalized := strings.ToLower(strings.TrimSpace(field))
    h := hmac.New(sha256.New, s.hashKey) // key from crypt.Init
    // ...
}

The service layer computes the hash on both write and lookup:

emailHash, _ := crypt.HashField(email)
user, err := s.db.Auth.User.GetByEmailHash(ctx, emailHash)

• Uniqueness is enforced on the hash column (uniq_auth_user_email_hash), not the ciphertext.
• All blind indexes share a single HMAC key (CRYPT_HASH_KEY). Email, domain, and OTP-code hashes all derive from it.
• OTP codes reuse HashField — only code_hash is persisted, and verification compares hashes.

Caution

Blind indexes support exact equality only. LIKE, prefix search, ordering, and SQL-side aggregation are impossible on encrypted columns — any such logic must happen in Go after decryption.

Configuration

CryptConfig (internal/config/crypt.go) is populated from the environment:

Env var	Purpose
CRYPT_ENCRYPTION_KEY	AES key — exactly 16, 24, or 32 bytes
CRYPT_HASH_KEY	HMAC key for all blind indexes (email/domain/OTP)

crypt.Init(cfg) is called once in service.New() (and in cmd/nuke) before db.New(), so the cipher is ready when connections register. It takes the full *config.Config and reads cfg.Crypt internally; an invalid encryption key is returned as an error at startup.

Danger

Losing CRYPT_ENCRYPTION_KEY means the PII columns are unrecoverable, and there is currently no key-rotation mechanism — rotating the key requires a migration that decrypts with the old key and re-encrypts with the new one. Rotating CRYPT_HASH_KEY similarly invalidates every stored blind index.

Operational notes

• Tracing: the otelpgx tracer is configured without query parameters precisely because bind values are plaintext at the driver boundary — they must never land on spans (see internal/db/db.go).
• JSON: a crypt.Encrypted scalar carries plaintext bytes; anything that serializes a db model (API responses, audit logs) emits decrypted data — the encryption boundary is the database, not the process. Note that model structs expose plain string / typed fields, converted from crypt.Encrypted in FromDB, so wire schemas never expose the encrypted type.
• Failure mode: a wrong or changed encryption key surfaces as failed to decrypt data errors on read (GCM authentication failure), not silent corruption.