# Changelog What changed in Socigy.OpenSource.DB. A single `InsertFields` enum (plus a `keep` selector) replacing the per-call insert booleans on the context, static, and bulk paths, plus fixes making migration apply idempotent across app restarts and safe across concurrent replicas, cancellation-token support on the generated query and write APIs, and a binary-COPY UTC-timestamp fix, in 0.3.5; modular-monolith and multi-project fixes (`required` members, flowing Npgsql/Bcl dependencies, a `contextName` for lowercase databases, and per-call `[Default]` control on every insert path) in 0.3.4; Binary COPY bulk insert, scalar/affected/DTO procedure returns, database-first scaffolding, and Transit data-key envelope encryption with per-column profiles and OpenBao support in 0.3.3; runtime-named typed tables ([TableType] and DynamicTable) in 0.3.2; the database-context bulk insert plus scalar and aggregate API in 0.3.1; and 0.3.0's field encryption, rotating credentials, and HashiCorp Vault package. ## v0.3.5 (unreleased) The per-call insert field control is now a single, explicit `InsertFields` enum with an optional per-column `keep` selector. ### Changed - **`InsertFields` enum replaces the per-call insert booleans.** The per-call insert field control on the context (`InsertAsync` / `InsertMultipleAsync`), static (`Table.InsertMultipleAsync` / `InsertMultipleCopyAsync`), and bulk (`BulkCopy.InsertMultipleCopyAsync`) paths is now a single `InsertFields` enum (`Default` / `IncludeAutoIncrement` / `ServerDefaults`) instead of the `includeAutoFields` / `excludeDbDefaults` booleans. The two opposite-polarity booleans were confusing (`includeAutoFields: false` did not exclude `[Default]` columns); the enum makes the three intents explicit and the contradictory combination unrepresentable. The fluent `Insert()` builder is unchanged. ### Added - **`keep` selector on the convenience insert methods.** A `keep` selector on those same methods writes specific `[Default]` columns yourself (e.g. a manual id) while the server fills the rest, the convenience-method equivalent of the fluent `ExcludeAutoFields(include)`, working on both batched `INSERT` and binary `COPY`. - **`CancellationToken` on the generated query and write APIs.** The streaming `ExecuteAsync`, `CountAsync`, `SumAsync` / `AvgAsync` / `MinAsync` / `MaxAsync`, `ScalarAsync`, the static `InsertAsync` / `UpdateAsync`, and the insert/update/delete command builders now accept an optional `CancellationToken` and flow it to `OpenAsync` and command execution, so a cancelled request (e.g. a dropped HTTP connection) stops the database work instead of running to completion and holding the connection. The parameter is optional and defaults to `default`, so existing call sites are unchanged. ### Fixed - **Migration apply is idempotent across app restarts again.** The second and later startups no longer re-run already-applied migrations and crash at boot with `42P07: relation "..." already exists`. The startup check for whether `_scg_migrations` exists read PostgreSQL's `to_regclass` value as a raw `regclass`, which Npgsql cannot materialize once the table is present, so the probe threw on every run after the first and the failure was swallowed into "no migrations applied". The probe now casts to a boolean (`to_regclass(...) IS NOT NULL`), matching the existence check already used for dynamic tables. See [Applying migrations](/database/0.3.5/migration/applying). - **A failed version read no longer triggers a destructive re-apply.** `GetCurrentMigrationVersion()` previously caught every exception and returned "no version", which the manager read as an empty database and answered by re-running every UP migration. It now logs and rethrows instead of swallowing, and the forward apply loop independently skips any migration already recorded as applied. The skip is rollback-aware, so an UP, then DOWN, then UP still re-applies the migration. - **Concurrent migration apply across replicas is serialized.** When several instances of an app start at once (a rolling deploy or a scaled-up replica set), each calling `EnsureLatest{Db}Migration()`, they previously raced the same `CREATE TABLE` / DDL and crashed with `42P07` / `42701` / `23505`, or left the schema half-applied. Migration apply now holds a PostgreSQL session-level advisory lock for its duration, so exactly one instance migrates at a time and the rest wait, then skip what was already applied. The lock auto-releases if a migrator process dies, so it cannot deadlock the fleet, and a database that is already current still short-circuits without taking the lock. See [Applying migrations](/database/0.3.5/migration/applying). - **Database creation no longer crashes on a concurrent first start.** `CREATE DATABASE` has no `IF NOT EXISTS` and cannot run in a transaction, so two instances creating the database at the same time raised `42P04`. The "already exists" race is now treated as success. - **Binary COPY no longer throws on a UTC `DateTime`.** `InsertMultipleCopyAsync` wrote `DateTime` values with an explicit `timestamp without time zone` type, which Npgsql rejects for a `Kind=Utc` value, so bulk-inserting `DateTime.UtcNow` threw. UTC values are now normalized to the column's wall-clock so they round-trip instead of failing. - **`InsertReturningAsync` works for `Guid` / `uuid` keys.** It routed the returned value through `Convert.ChangeType`, which throws for non-`IConvertible` targets like `Guid`, `DateTimeOffset`, and `byte[]`, so returning a generated `uuid` primary key failed. It now returns the value directly when it is already the requested type. - **`RETURNING *` propagation no longer silently drops server-generated values.** A column-name mismatch was caught by a blanket `catch` that skipped the column, leaving a generated key unset on the in-memory row while still reporting success. The catch is narrowed so a genuine reader fault surfaces instead. - **Single-table predicates quote column identifiers.** `WHERE` / `SELECT` / `ORDER BY` on a single table emitted bare column names (joins already quoted theirs), so a column whose name is a reserved word (`order`, `user`) or mixed-case produced invalid or wrong SQL. Identifiers are now quoted consistently with the join path. - **Dynamic-table column cache is bounded.** The per-`(type, runtime table name)` cache behind `DynamicTable.MapTypeAsync` grew without limit; in a multi-tenant app with many runtime table names it now evicts entries to stay within a fixed cap. - **Vault Transit decrypt cache no longer hands back a shared array.** The decrypt cache returned the same `byte[]` instance it stored, and a `byte[]`-typed encrypted column hands that array straight to the caller, so application code mutating the buffer corrupted the cached value for every later read of that row. The cache now keeps and returns private copies. - **Offline re-encryption fails fast on a NULL primary key.** Re-encryption pages the table by keyset comparison over the primary key, and a NULL key column would silently drop those rows from every batch after the first. It now throws a clear error naming the table and column instead. - **Aggregate overflow is now an actionable error.** `SumAsync` / `AvgAsync` widen the way PostgreSQL does (`SUM` of a `bigint` returns `numeric`), so requesting a too-narrow result type threw a bare `OverflowException`. The message now explains the widening and points you to a wider type such as `decimal`; the methods also document this. Read `SUM(bigint)` as `decimal`. - **Vault keyring parsing is culture-invariant and overflow-safe.** A corrupted keyring key/version field now fails as a clear "malformed keyring" error rather than an unscoped `FormatException` / `OverflowException`. - **Plain enum columns are readable again.** Querying any table with a plain (non-`[FlaggedEnum]`) enum column threw `System.InvalidCastException`, because the row materializer read it via `GetFieldValue`, for which Npgsql has no handler. Enum columns now read through their underlying integer. Verified end to end against a live database. - **Binary COPY and parameterized inserts handle `DateOnly` / `TimeOnly` / `TimeSpan` / `DateTimeOffset`.** These valid column types were missing from the COPY and parameter type maps and fell back to `text`, so binary COPY (and a NULL bind on the parameterized path) threw or wrote the wrong type. They now map to `date` / `time` / `interval` / `timestamptz`. - **`timestamptz` columns propagate back onto a `DateTimeOffset` property.** `WithValuePropagation()` (RETURNING *) read such a column as the `DateTime` Npgsql returns and could not convert it to a `DateTimeOffset` member; it now maps correctly. - **Stored-procedure `affected` / `void` methods accept and flow a `CancellationToken`.** The scalar, streaming, and DTO procedure methods already did; the non-query ones were missed. - **A `DateTime` (`Kind=Utc`) stored into a `timestamp` column is no longer shifted by the session time zone.** Because a non-null `DateTime` was bound without an explicit type, Npgsql inferred `timestamptz` from `Kind=Utc` and PostgreSQL converted it to local wall-clock on store, so under a non-UTC session `DateTime.UtcNow` landed hours off. The insert, update, bulk-COPY, dynamic-table, and delete paths now store the wall-clock verbatim. Verified against a live database. - **`== null` / `!= null` against a captured variable matches the right rows.** Only a literal `null` was translated to `IS NULL` / `IS NOT NULL`; a `null`-valued variable became `col = @p` / `col <> @p`, which (SQL three-valued logic) matched no rows. A null operand now uses `IS NULL` / `IS NOT NULL` whether literal or captured. - **`string.Equals(value, StringComparison.OrdinalIgnoreCase)` is case-insensitive.** The trailing `StringComparison` was ignored and the comparison ran case-sensitively; it now emits `LOWER(col) = LOWER(@p)` for the *IgnoreCase comparisons. - **`Contains` / `StartsWith` / `EndsWith` escape LIKE wildcards in projections.** The WHERE path already escaped `%` `_` `\`; the SELECT/ORDER BY path did not, so e.g. `Contains("50%")` matched any string containing `50`. Both paths now escape consistently. - **A full (`WithAllFields`) update with a custom `WHERE` no longer clobbers primary keys.** The `SET` clause included the primary-key and serial columns, so an update whose `WHERE` matched multiple rows wrote the instance's key into every one (duplicate-key error / data loss). The PK and auto-increment columns are now excluded from `SET`, and the PK-derived `WHERE` quotes its column. - **`jsonb` columns update correctly via `WithFields`.** A selective update of a JSON column bound the serialized string as `text` (`jsonb = text` error); it is now cast to `jsonb`. - **Instance `DELETE` handles enum, `DateTimeOffset`, and the wider numeric primary-key types.** Its type map lacked the enum and temporal cases the insert path has, so such a key was sent as `text` and threw or matched nothing. - **JOIN multi-key descending sort applies `DESC` to every key.** `OrderByDesc(x => new[]{ a, b })` emitted `ORDER BY a, b DESC`, sorting only the last key descending (and paging the wrong rows with `Limit`). Each key now gets `DESC`. - **`Limit(0)` on a JOIN or set-operation query returns zero rows** instead of the whole result (the guard was `> 0`, dropping `LIMIT 0`). - **An outer-join right side with no primary key yields `null`, not a zeroed instance.** The no-match test required all primary-key columns to be NULL; a PK-less table now falls back to all selected columns being NULL. - **`HasFlag` in a `WHERE` with a composite flag no longer silently returns nothing.** `x.Role.HasFlag(A | B)` bound the OR-ed integer and matched no junction row (each row holds one flag); it now throws a clear error telling you to combine flags with `&&` (`HasFlag(A) && HasFlag(B)`). Single-flag `HasFlag` is unchanged. (`HasFlag` predicates are also excluded from the query-shape cache so a composite value can't reuse a single-flag plan.) - **Granting a flagged-enum value is idempotent.** The junction `Insert{Flag}` ran a bare `INSERT`, so granting an already-present flag (or an `EditRole` / `Sync` batch containing a duplicate) threw `23505` and could half-apply. It now uses `INSERT ... ON CONFLICT DO NOTHING`. - **Changing a `[Default]` on an existing column generates valid DDL.** The `ALTER COLUMN ... SET DEFAULT` path emitted the raw `$socigy$…` token instead of translating it, so the migration failed at apply ("unterminated dollar-quoted string"). It is now translated like the create/add-column paths. - **A primary-key change has a correct DOWN.** The rollback dropped the new primary key but never restored the old one, leaving the table with no primary key; the prior key is now re-created in the DOWN script. - **Rolling back a seed row containing a NULL works.** The no-primary-key seed `DELETE` matched on `col = NULL` (never true), so the delete silently did nothing; it now uses `IS NULL`. A copy/paste guard in migration naming that skipped removed-constraint hashing was also corrected. - **The CLI returns a non-zero exit code on failure.** Generate/scaffold error paths (missing assembly, missing `--connection`/`--from-schema`, missing `schema.json`) logged an error but exited `0`, so a failed build-time generation looked like success in CI. They now return `1`. - **A malformed `socigy.json` reports the actual parse error.** The config loader caught and discarded the deserialization exception, leaving only an opaque "Invalid configuration"; the underlying JSON error is now surfaced and chained. - **Vault renewal retries quickly after a failure.** A failed token or credential renewal rescheduled at the long fallback interval (token: 30 minutes; credentials: 2/3 of a now-stale lease), which could fall after the lease/token had already expired. A failed attempt now retries at the ~30-second floor. - **The generated sequence accessor quotes the sequence name.** `GetNextValueAsync` used an unquoted identifier in `nextval(...)` while `PeekCurrentValueAsync` used a quoted one; they now agree (correct for case-sensitive sequence names). - **Database-first scaffolding hardening.** A `[Default]` value containing control characters is now JSON-escaped (valid C# string), and a foreign key with NULL column arrays no longer throws while reading the schema. - **Database-first scaffolding preserves `numeric(precision, scale)`.** A scaffolded `decimal`/`numeric` column collapsed to a bare `numeric`, silently dropping its precision and scale; the reader now captures them so a regenerated column matches the source. (Covered by a new live DB → schema → classes and a DB → schema → DDL → DB round-trip test.) - **`DbCheck.Value(nameof(Prop))` matches the real column name for acronyms.** The CHECK builder snake-cased naively (`IPAddress` → `i_p_address`), while columns use `JsonNamingPolicy.SnakeCaseLower` (`ip_address`), so a check referencing such a column failed at apply with "column does not exist". `DbCheck.Value` now uses the same policy as the generator. - **Rolling back a renamed column that has a UNIQUE/FK constraint produces valid DDL.** The DOWN re-add of the old constraint emitted the raw PascalCase property name (`UNIQUE ("Phone")`) instead of the column name, so the rollback failed. Unresolved constraint columns now fall back to the snake_case column name. - **A `DateTimeOffset` with a non-zero offset no longer crashes every insert path.** `DateTimeOffset.Now` carries the local UTC offset, but Npgsql only writes a `DateTimeOffset` at offset 0 to `timestamptz`, so inserting one threw `ArgumentException` ("only offset 0 (UTC) is supported"). The binary-COPY, single-row, multi-row, update, delete, dynamic-table, and stored-procedure paths now normalize to the same UTC instant (`ToUniversalTime()`) before binding. Verified against a live database. - **Unsigned integer columns (`ushort` / `uint` / `ulong`) no longer crash on write.** Npgsql has no wire mapping for unsigned CLR types, so a value bound without an explicit type threw `InvalidCastException`. Every write path now widens them to the signed/decimal type the column maps to (`int` / `long` / `numeric`) before binding. Verified against a live database. - **Stored-procedure parameters get the same value coercion as inserts.** The generated procedure-call binding set no explicit type and performed none of the enum/`DateTime`/`DateTimeOffset`/unsigned normalization the insert path does, so an `enum`, `DateTime.UtcNow`, `DateTimeOffset.Now`, or unsigned procedure argument threw at execution (or silently shifted by the session time zone). The procedure binder now applies the same normalization. - **`Limit(0)` on a single-table query returns zero rows.** Only the JOIN and set-operation builders honored `LIMIT 0`; on a plain `Table.Query(...)` the parser treated `limit == 0` as "no limit set" (the `-1` sentinel), took the cached no-`LIMIT` fast path on a filtered query, and returned the whole table. A computed `pageSize` that lands on 0 now correctly returns nothing. - **`DynamicTable.MapTypeAsync` resolves columns for the right schema.** It listed columns with a bare `WHERE table_name = @t` against `information_schema.columns`, which matches the name in *every* schema, so a same-named table in another schema (a common multi-tenant layout) merged foreign columns into the extra-column set and corrupted every materialized row. It now resolves the relation through the connection's `search_path` with `to_regclass` and reads exactly that table's columns, matching how the rest of the type's queries resolve the name. - **A database error mid-result is recorded as a failed span, not a successful one.** Npgsql streams rows, so a server-side error surfacing partway through enumeration would still let the reader's disposal mark the trace span `Complete` (success). The instrumented reader now records the failure on the span when a row read throws, so the error is observable in traces and the error counter. - **A custom parameter-redaction hook's output is length-capped.** `RedactParameter` output bypassed `MaxParameterValueLength`, so a hook that echoed or expanded the value could bloat every span and log line. The cap now applies to the hook's output as well. - **A nested call to a *different* database no longer runs on the wrong connection.** In a modular monolith with more than one generated context, calling one database's `ExecuteAsync` / `ExecuteTransactionAsync` from inside another's unit of work joined the ambient scope unconditionally (the scope pointer is process-wide), so the inner database's queries executed against the outer database's connection and transaction. A nested call now joins the ambient scope only when it targets the same database (same connection factory and key); a different database opens its own scope. A transactional call nested in a same-database non-transactional scope now throws instead of silently running with no transaction. - **Unsigned columns (`ushort` / `uint` / `ulong`) round-trip correctly.** The write side widens them to `integer` / `bigint` / `numeric`, but the read side cast straight back to the unsigned CLR type, which Npgsql has no reader handler for, so querying such a column threw `InvalidCastException` even though the insert succeeded. The generated row, projection, public `ReadValue`/`ConvertFrom`, and aggregate/scalar read paths now narrow from the widened storage type. The baked `[TableType]` `CREATE TABLE` also maps these types (and `sbyte`) to the matching widened SQL type instead of `text`. Verified against a live database. - **The public `ConvertFrom(reader, …)` materializer tolerates widened value types.** Reading a `byte` / `sbyte` (stored `smallint`) or an unsigned column through the documented manual-read API did an unboxing cast that only succeeds when the boxed storage type exactly equals the property type, so it threw. It now routes through the width-tolerant converter the engine uses. - **`MinAsync` / `MaxAsync` / `ScalarAsync` of a `DateTimeOffset` column no longer throw.** They converted the scalar with raw `Convert.ChangeType`, which throws for `DateTimeOffset` (not `IConvertible`) — and Npgsql returns a `timestamptz` as a UTC `DateTime`. They now use the same converter as the row path, which maps the UTC `DateTime` onto the `DateTimeOffset` (and handles the widened unsigned types). Verified against a live database. - **A `byte`-backed enum value convertor reads back correctly.** `DbEnumValueConvertor` required the DB value's type to exactly equal the enum's underlying type, but a `byte`-backed enum is stored as `smallint` (returned as `short`), so reading it threw. It now converts to the underlying type before materializing the enum. - **Compiled-query cache: a captured `null` operand no longer reuses the wrong SQL.** `x.Name == value` is translated to `IS NULL` when `value` is null at runtime and to `= @p` otherwise, but the structural cache key didn't distinguish the two, so caching one shape and replaying it for the other returned wrong rows. An (in)equality whose value operand is a captured (non-literal) nullable type is no longer cached (literals and non-nullable value-type keys such as `int`/`Guid` still cache). - **Compiled-query cache: case-insensitive string compares don't collide with case-sensitive ones.** `x.Name.Equals(s, StringComparison.OrdinalIgnoreCase)` emits `LOWER(…) = LOWER(…)` while the case-sensitive overload emits `= @p`, but both hashed the same, so the second query reused the first's SQL. The `StringComparison` value is now folded into the cache key (or the shape is left uncacheable when it's a runtime value). - **Compiled-query cache: `DB.CustomField("col")` no longer reuses another column's SQL.** The custom-column name is spliced into the SQL text but collapsed to a value token in the cache key, so two different column names shared one cached plan and queried the wrong column. `CustomField` predicates are now uncacheable, like `Custom` / `HasFlag`. - **Offline re-encryption no longer masks the original error on a failed batch.** The batch rollback was unguarded, so a rollback on an already-broken transaction threw and replaced the real failure; it is now wrapped like the other transactional sites. - **Concurrent migration rollback can't double-apply a DOWN.** The forward (UP) apply loop skips migrations already recorded as applied, but the rollback (DOWN) loop did not, so a rolling rollback across replicas (or a rollback racing another) could re-run a migration's `DownSql` for one already rolled back and write a duplicate rollback row. The DOWN loop now applies the same rollback-aware applied-set guard and skips a migration that is no longer applied. - **A value convertor on a primary key is honored in the `WHERE` clause.** An instance `UPDATE` / `DELETE` builds its `WHERE` from the primary key, but that path bound the raw CLR value while the column stores the convertor's output, so the update/delete silently matched no rows. The primary-key value (and its read-back) now go through the same convertor the insert/SET path uses. - **Updating an enum column backed by a custom value convertor no longer throws.** The `UPDATE` builder coerced by the declared enum type and forced the column's integer DB type, so a convertor that stores an enum as text (or any non-underlying representation) threw `InvalidCastException`. It now coerces and types the parameter by the runtime value, matching the insert path, so the convertor's output is written as-is. - **`ToLower()` / `ToUpper()` in a dynamic-table projection are no longer silently dropped.** The `SELECT` visitor emitted only the bare column for any string method other than `Contains` / `StartsWith` / `EndsWith`, so e.g. a case-insensitive compare became case-sensitive. It now emits `LOWER(...)` / `UPPER(...)` (matching the `WHERE` visitor) and throws on a genuinely unsupported string method instead of producing wrong SQL. - **A null argument to `Contains` / `StartsWith` / `EndsWith` / `Equals` in a predicate fails fast.** A null pattern became `LIKE '%%'` (matching every row) and `Equals(null)` became `col = NULL` (matching none); both now throw a clear `ArgumentNullException` pointing at the correct null check, in both the `WHERE` and `SELECT` visitors. - **Vault token renewal is serialized.** A scheduled renewal racing a manual one could both re-login and overwrite the shared client (wasted Vault logins and a nondeterministic active token). Renewal/relogin now runs under a lock so only one proceeds at a time. - **Each registered database keeps its own context options.** In a modular monolith calling `Add{Db}Context` for several databases, the options were registered as one shared (non-keyed) singleton and every factory resolved that single instance, so every database after the first silently inherited the first's `ConnectionKey` / connection lifetime (wrong connection string, wrong pinning). Each factory now captures the options configured for its own registration. - **Enum columns backed by a custom value convertor work on every write path.** The runtime-vs-declared enum check that fixed the parameterized update is now also applied to binary COPY, the dynamic-table writer, and the delete-by-key path, so a convertor that stores an enum as text (or any non-underlying representation) no longer throws `FormatException` / `InvalidCastException` on those paths. Binary COPY also derives the wire type from the actual value, so the convertor's output type is written correctly. Verified against a live database. - **A composite `[Flags]` value can't be written as one unqueryable junction row.** Granting/revoking a flagged-enum value with multiple bits set (e.g. `Reader | Writer`) stored a single junction row holding the combined integer, which no single-flag query could ever match (the read side already rejects composite values). The write side now rejects a composite value with a clear error; grant/revoke flags individually or pass them separately to the sync method. - **Adding an `[AutoIncrement]` column in a migration generates a working sequence.** An `ALTER TABLE ... ADD COLUMN` for a new auto-increment column created a table-qualified sequence but the column default referenced an un-prefixed name that was never created, so the migration failed at apply (`relation "_id_seq" does not exist`). The default now references the created sequence, the sequence is typed to the column, and the rollback drops the column before the sequence it depends on. - **A join aggregate over a `DateTimeOffset` column no longer throws.** `MinAsync` / `MaxAsync` on a `Join(...)` query used raw `Convert.ChangeType` (which can't target `DateTimeOffset` — Npgsql returns `timestamptz` as a UTC `DateTime`), throwing `InvalidCastException`. The join aggregate now uses the same converter as the single-table path. Verified against a live database. - **`DynamicTable.InstantiateAsync()` creates a usable table for enum columns.** The baked `CREATE TABLE` typed an enum column as `text`, but the insert path binds the enum as its underlying integer, so every insert into the created table failed (`column is of type text but expression is of type integer`). The baked DDL now types an enum column as its integral type. Verified against a live database. - **`ForEachAsync` honors its `CancellationToken` during the read.** The token was only checked between rows, not threaded into the streaming reader, so a cancellation could not interrupt an in-flight row read; it now flows to the database read. The streaming reader also disposes its `DbCommand` on every exit path. - **Database-first scaffolding maps `character(n)` (n>1) to `string`.** A fixed-length `char(n)` column scaffolded as a C# `char`, which can hold only one character (truncation); only `character(1)` now maps to `char`, wider ones to `string`. - **Database-first scaffolding skips a cross-schema foreign key instead of emitting uncompilable code.** A foreign key whose target table lives in another (un-scaffolded) schema produced a `[ForeignKey(typeof())]` reference; it is now skipped with a warning. - **A `byte` / `sbyte` DTO property (including a `byte`-backed enum) read from a procedure result no longer throws.** These are stored as `smallint` (Npgsql returns `short`), and the DTO mapper read them with `GetFieldValue`, which throws. They now narrow from `short`, matching the unsigned handling already in place. Verified against a live database. - **Rotating an envelope-encryption keyring no longer fails concurrent reads.** `RefreshAsync` / `RotateAsync` disposed the previous keyring synchronously, which zeroes its keys — so an in-flight `Encrypt` / `Decrypt` that had already captured it could fail its MAC check (a `CryptographicException` on valid data) or decrypt garbage. The old keyring is now disposed after a short grace window, so in-flight operations drain first while the old keys are still eventually zeroed. - **A from-scratch `CREATE TABLE` migration emits `NOT NULL` for required columns.** Non-nullable, non-primary-key columns were created NULLABLE because the analyzer marks a required column's nullability as "unset" rather than explicitly false, and the generator only emitted `NOT NULL` for an explicit false. Required columns are now correctly created `NOT NULL`. - **A get-only / computed / init-only property on a `[Table]` model is ignored instead of breaking the build.** Such a property was treated as a mapped column, and the generated materializer assigned to it (`CS0200`). A non-writable property is now skipped (like `[Ignore]`), so a computed property compiles and works. - **A `{{Type.Property}}` SQL placeholder for a non-column property is reported, not silently mis-resolved.** Referencing an `[Ignore]`, `[FlaggedEnum]`, static, or get-only property in a procedure `.sql` body fabricated a quoted column name with no backing column (a runtime "column does not exist"). It now reports the same unknown-property diagnostic as any other non-column reference. - **Migration ids include seconds, so two migrations generated in the same minute don't collide.** The id (the ordering prefix and part of the filename) was truncated to the minute, so two `generate` runs in the same minute could overwrite each other's file or sort ambiguously. Ids are now `yyyyMMddHHmmss`; an existing minute id remains a lexical prefix of a same-minute seconds id, so apply order is unchanged. - **The schema snapshot is written atomically after a `generate`.** The snapshot file was moved aside and only then rewritten, leaving a window where it didn't exist — a crash there made the next run treat the project as un-migrated and re-emit every migration. The new snapshot is now written to a temp file and moved into place, so it is never missing or half-written. - **`ExecuteReturningAsync` no longer throws.** It converted the `RETURNING` value with raw `Convert.ChangeType`, which throws for `DateTimeOffset` (a `timestamptz` comes back as a UTC `DateTime`); it now uses the same converter as the row/aggregate paths. Verified against a live database. - **Long-running migrations no longer time out.** The migration command used Npgsql's default 30-second timeout, so a slow DDL step (e.g. an `ALTER COLUMN ... TYPE` that rewrites a large table, or a data backfill) could be aborted mid-apply and rolled back. The migration command timeout is now disabled. - **`WHERE` filters on `DateTime` / `DateTimeOffset` / unsigned values now bind correctly.** The predicate parameter binding never got the normalization the write paths have, so a filter like `x => x.At == DateTime.UtcNow` (against a `timestamp` column) was inferred as `timestamptz` and shifted by the session time zone — silently matching the wrong rows — a non-UTC `DateTimeOffset` filter threw, and an unsigned-column filter threw. The WHERE parameter path now applies the same `Kind=Utc → Unspecified` relabel, `DateTimeOffset → UTC` normalization, and unsigned widening as the insert path. Verified against a live database. - **A selective (`WithFields`) update of a `DateTime` / `DateTimeOffset` / unsigned column binds correctly.** The `WithFields` SET path normalized only enums, so it had the same shift/throw as the WHERE path above; it now applies the full normalization like the full-field update. - **An empty `WithFields(...)` selector fails with a clear error** instead of emitting malformed `SET WHERE ...`; a duplicate member in a `WithFields` selector is de-duplicated (PostgreSQL rejects two assignments to the same column). - **Vault credential/token renewal is never scheduled past a short lease's expiry.** The 30-second busy-loop floor was applied even when it exceeded the lease lifetime, so a lease shorter than ~45s could be scheduled to renew at or after it had already expired. The floor is now capped against the lease, so a very short lease renews at 2/3 of its lifetime (before expiry) instead. - **A custom `[AutoIncrement("name")]` sequence works on a runtime-instantiated table.** `DynamicTable.InstantiateAsync()` baked the column as `serial`, which auto-creates a `{table}_{column}_seq` sequence rather than the custom-named one, so the column's value still flowed but the runtime sequence accessor (`GetNextValueAsync` / `PeekCurrentValueAsync`) targeted a sequence that didn't exist and threw. The baked DDL now creates the custom-named sequence and points the column default at it. - **A `Contains` filter (`= ANY(@array)`) normalizes each array element like a scalar `==` filter.** Filtering with a collection — `roles.Contains(x.Role)` (enum), `times.Contains(x.At)` (`DateTime`/`DateTimeOffset`), or an unsigned collection — threw (no Npgsql wire mapping for enum/unsigned arrays; a non-UTC `DateTimeOffset[]` rejected) or, for a `Kind=Utc DateTime[]`, silently matched the wrong rows. Each element is now normalized exactly as the scalar `=` path is, and the array binds with the matching element type. An array literal (`new[]{…}.Contains(x.Col)`, which binds to the span-based `Contains`) is also supported now. Verified against a live database. - **A null pattern in a cached `Contains` / `StartsWith` / `EndsWith` no longer matches every row.** The first translation rejected a null LIKE pattern, but a cached query shape replayed with a null value bypassed the guard and produced `LIKE '%%'` (every row). The guard now lives on the shared bind path, so translation and cache-replay behave identically. - **An `ELSE`-less `Select.Case()...End()` produces valid SQL.** The `End()` terminator emitted no `END`, so an `ELSE`-less projected `CASE` was malformed and failed at execution; it now closes the `CASE` block. - **`!=` against a nullable column includes NULL rows, matching C# semantics.** `x => x.NullableValue != 5` emitted `col <> @p`, which SQL evaluates as NULL (not true) for a NULL row, so NULL rows were silently dropped — but in C# `null != 5` is `true`. A `!=` over a nullable value-type column now emits `(col <> @p OR col IS NULL)` (as EF Core does), so those rows are included; `==` and non-nullable columns are unchanged. - **`StartsWith` / `EndsWith` / `Contains` honor a `StringComparison.*IgnoreCase` argument.** The case-insensitive overload silently emitted a case-sensitive `LIKE` (dropping rows the author expected to match), while the parallel `string.Equals` path already honored it; these now emit `ILIKE` for the *IgnoreCase comparisons. - **Capturing parameter values for diagnostics can no longer crash a query.** With `CaptureParameterValues` on, a parameter whose `ToString()` throws — or a custom `RedactParameter` hook that throws — propagated out of the (otherwise successful) command, and on the failure path could even mask the original database exception. Rendering is now exception-safe (a failing value renders as ``). - **Diagnostics render array/collection parameters and timestamps usefully.** An `= ANY(@p)` array parameter was logged as `System.Int32[]`; it now shows its (bounded) contents. `DateTime` / `DateTimeOffset` parameters now render in round-trip (`o`) format so the `Kind`/offset is visible when debugging time-zone issues. - **An inline constructor on the value side of a comparison binds as one value.** `x.D > new DateOnly(2020, 1, 1)` (or `new TimeOnly(...)`, `new DateTimeOffset(...)`) was shattered into one parameter per constructor argument — `("D" > @p0@p1@p2)` — a PostgreSQL syntax error; `x.Gid == new Guid("...")` bound the constructor's `string` instead of a `Guid` (`operator does not exist: uuid = text`). The `WHERE` visitor now folds an inline constructor into a single normalized parameter, matching a captured local, and throws on a column-dependent constructor. - **A column transform in single-table `ORDER BY` fails fast instead of ordering by the bare column.** `.OrderBy(x => new object[] { x.Name.ToUpper() })` silently dropped the `ToUpper()` and emitted `ORDER BY "Name"`, sorting by the raw value; an unsupported method-call transform now throws a clear `NotSupportedException`, matching how the operator path already rejects unsupported expressions. - **A DTO with multiple constructors maps through the widest one.** A procedure-return DTO that declares a convenience constructor alongside its primary one (e.g. a positional record plus a `(name)` overload) bound `InstanceConstructors[0]` (declaration order), which could pick the narrow overload and silently drop the unmapped members to `default`. The generator now selects the highest-arity constructor (and reports `SCGDB021` when two share the maximum arity rather than guessing). - **An inline constructor in a projection binds as one value.** The `SELECT` visitor had the same shatter as the `WHERE` visitor: a projected `new DateOnly(...)` / `new Guid("...")` (including in a `Case().Then(...)` / `.Else(...)`) emitted one parameter per constructor argument (`@p0@p1@p2`, invalid SQL) or bound a single-arg ctor's `string` instead of the `Guid`. It now folds an inline constructor to a single normalized parameter. - **An unsupported method call in a projection fails fast.** A column-dependent method call the `SELECT` visitor doesn't translate (e.g. `x.Created.AddDays(1)`) fell through to the base visitor, which emitted only the bare column and silently dropped the call. It now throws a clear `NotSupportedException`, matching the `WHERE` / `ORDER BY` paths. - **JOIN `ON` / `WHERE` parameters are normalized like single-table predicates.** The multi-join visitor normalized only enums, so a `DateTime` (`Kind=Utc`), an offset `DateTimeOffset`, or an unsigned value compared in a join `ON` / `WHERE` was bound without the relabel/convert/widen the single-table path applies — silently shifting a UTC value by the session time zone, throwing on a non-UTC offset, or failing with no wire mapping for the unsigned type, while the identical single-table query was correct. The join path now binds through the same `Normalize` as the single-table path. - **An inline constructor in a JOIN `ON` / `WHERE` binds as one value.** The multi-join visitor had the same constructor shatter: `a.Created > new DateTime(2020, 1, 1)` emitted `@p0@p1@p2` (invalid SQL) and `b.Gid == new Guid("...")` bound the `string` not the `Guid`. It now folds an inline constructor to a single normalized parameter. - **`DELETE`-by-instance on a type-changing value-convertor primary key works.** Delete forced the parameter's type from the *declared* PK type, so a PK whose `[ValueConvertor]` returns a different CLR type (e.g. an `enum` stored as its `string` name) had its converted `string` value forced to `Integer` and Npgsql threw — while the identical `UPDATE`-by-instance succeeded. Delete now mirrors update: it binds the converted value and lets Npgsql infer the type, forcing it only for a null or a real enum. - **`DynamicTable` aggregate / scalar reads no longer crash for enum and `DateTimeOffset` results.** `MinAsync` / `MaxAsync` / `ScalarAsync` on a `DynamicTable` coerced the result with `Convert.ChangeType`, which throws for an enum target (the column is read as its underlying `int`) and for a `DateTimeOffset` (not `IConvertible`). They now use the same width-tolerant converter as the join-aggregate path. - **Migration DDL maps unsigned integer types to their widened runtime types.** The CLI's CLR→PostgreSQL type map had no entry for `uint` / `ulong` / `ushort` / `sbyte`, so it fell through to the raw .NET name (`uint32`, …) and emitted invalid `CREATE TABLE` DDL that fails to apply — and would have diverged from the type the runtime actually reads/writes. These now map to `bigint` / `numeric` / `integer` / `smallint`, matching the source generator's widening. - **A captured value in an `ORDER BY` `CASE` is normalized like the other paths.** The `ORDER BY` visitor bound a captured value raw, so an enum / `Kind=Utc` `DateTime` / offset `DateTimeOffset` / unsigned value inside a `Select.Case()` (`When` / `Then` / `Else`) was bound without the relabel/convert/widen the WHERE path applies — silently mis-ordering a UTC value by the session time zone, or throwing on an offset / unsigned value. It also lacked the inline-constructor fold, so a `new DateOnly(...)` / `new Guid("...")` shattered. Both are now handled, matching the WHERE / SELECT / JOIN visitors. - **A `.Contains` over a nullable-enum / nullable-unsigned collection works.** `roles.Contains(x.NullableRole)` where `roles` is a `List` (or `List`) threw `InvalidCastException`: the array element type stayed `Role?` while each element was normalized to its underlying `int`. The element type is now unwrapped, widened, and re-wrapped as `Nullable`, so the array binds (and a `null` element is still representable). This is the nullable analog of the already-fixed non-nullable enum/unsigned array binding. - **Database-first scaffolding strips schema-qualified casts from column defaults.** Reading an existing schema, a column default with a schema-qualified or quoted type cast (`'x'::public.citext`, `'x'::"public"."citext"`) was only partially stripped — the type name's first segment was removed but the rest survived as `'x'.citext`, which scaffolded into a `[Default("'x'.citext")]` that forward-generates invalid DDL. The cast removal now consumes the whole qualified type name. - **Vault envelope encryption no longer overwrites the keyring on a transient read error.** Reading the envelope keyring caught *every* `VaultApiException` and treated it as "first run", so a non-404 failure (a 503 while Vault is sealed/standby, a 429, a network timeout, or a KV policy that grants write but not read) made the bootstrap path overwrite the existing keyring with a fresh `current=1` DEK — discarding every previously-wrapped key version and making all already-encrypted rows permanently undecryptable. Only a genuine 404 now triggers bootstrap; every other error propagates and the keyring is never replaced. - **Non-enum `byte` / `sbyte` table columns are readable.** Such a column is stored as `smallint`, but the default (fast) read path called `GetFieldValue` directly, for which Npgsql has no `int2`→`byte` handler, so the row threw `InvalidCastException` and never materialized — while the slow and procedure-DTO read paths narrowed correctly. The fast path now narrows from `short`, matching the others. The unsigned narrowings (`ushort`/`uint`/`ulong`) on all read paths also became `checked`, so an out-of-range stored value throws consistently instead of silently wrapping on one path and throwing on another. - **A `[Table]` maps columns inherited from a base class and declared across multiple `partial` files.** Column discovery read only the single class declaration that carried `[Table]`, so a property inherited from a base class — or declared in a different `partial` of the same class — was silently dropped from the entire column set (never created, inserted, selected, or filterable), and was inconsistent with the procedure placeholder resolver and DTO mapper, which already walked the base chain. Discovery now walks the symbol's base chain and all partial declarations (deduping by name, most-derived first). A flat single-class model is unaffected — its generated code is identical. - **Database-first scaffolding preserves single-column `UNIQUE` constraints.** The C# class emitter wrote `[ForeignKey]` attributes but not `[Unique]`, so scaffolding an existing schema dropped every unique constraint from the generated class — and the next `generate` then emitted a `DROP CONSTRAINT`, silently losing the uniqueness guarantee on a scaffold→migrate round-trip. A single-column unique now emits a property-level `[Unique]` (the form the analyzer reads back). - **Migration apply reads the current version under the advisory lock.** The "already at this version" short-circuit and the apply-vs-rollback direction were decided from a version read taken *before* the migration advisory lock, so a startup racing a concurrent rollback on another replica could see "already current" and return while the schema was actually moved, with no self-correction. The version is now read under the lock. The cost is that an already-current startup briefly takes the lock too — negligible. - **Database-first scaffolding preserves composite-key column order.** A composite primary key whose key order differed from the table's column declaration order was emitted in column order, silently changing the key (and its index prefix semantics). `[PrimaryKey]` now takes an optional position (`[PrimaryKey(order)]`); the schema reader records each key column's ordinal, the generator emits `PRIMARY KEY (...)` in that order, and the C# emitter writes the position so the order round-trips. Single-column keys and ordinary code-first models are unaffected. - **Database-first scaffolding preserves composite `UNIQUE` constraints.** A multi-column unique now emits a class-level `[Unique(nameof(A), nameof(B))]` (and the analyzer reads class-level `[Unique]` back), so a composite unique survives the scaffold→migrate round-trip instead of being dropped. - **A `Guid.Sequential` default no longer produces non-applyable DDL.** `[Default(DbDefaults.Guid.Sequential)]` translates to `uuid_generate_v1mc()`, which lives in the `uuid-ossp` extension that is not installed by default — so the migration failed to apply with "function uuid_generate_v1mc() does not exist". The migration now emits `CREATE EXTENSION IF NOT EXISTS "uuid-ossp"` ahead of any statement that uses it. (`Guid.Random` → `gen_random_uuid()` is built in and is unaffected.) - **`[Encrypted]` combined with `[StringLength]` is now a build error.** An encrypted column is non-deterministic `bytea`; combining it with `[StringLength]` produced an order-dependent `character varying(n)` DDL in the migration analyzer that contradicted the `bytea` the runtime writes. The existing `SCGDB002` diagnostic now also covers `[Encrypted]` + `[StringLength]` (alongside `[JsonColumn]` / `[ValueConvertor]`), failing the build instead of emitting an ambiguous column. - **A baked `[TableType]` CREATE TABLE maps an `object` column to `jsonb`.** The runtime baked DDL fell through to `text` for an `object`-typed column while the migration generator maps it to `jsonb`; they now agree, so a runtime-instantiated table and a migration-managed one don't diverge. - **String concatenation in a predicate emits `||`.** `x => x.Name + suffix == value` compiles to a `BinaryExpression` with `NodeType = Add`, which the WHERE translator rendered as SQL `+` — and PostgreSQL has no `text + text` operator, so the query failed with "operator does not exist: text + text". A string `Add` now emits `||`. - **A `char` comparison binds the character, not its code point.** `x => x.Initial == 'A'` (a `char` column stored as `character(1)`) is promoted by C# to `int == int`, so the translator bound the integer code point `65`, producing `character(1) = integer` (operator does not exist). The char-promoted comparison now binds the value back as a one-character string that compares against the column — on both the first translation and the cached query-shape replay (the rebind lives on the shared parameter-binding path, so a repeated char predicate is not silently rebound to the raw integer). - **A ternary in a predicate emits a SQL `CASE`.** `x => (x.A > 0 ? x.B : x.C) == 5` had no `VisitConditional` handling in the WHERE translator, so it emitted the branches with no `CASE`/`WHEN`/`THEN` scaffolding (malformed SQL). It now emits `CASE WHEN ... THEN ... ELSE ... END` (matching the `SELECT`/`UPDATE` translators); a constant test is folded to the chosen branch. - **Diagnostics logger cache is published safely under concurrency.** The double-checked-locking cache of the SQL logger read its two fields outside the lock without `volatile`, so on a weak memory model a concurrent caller could observe the published factory while still seeing a stale/null logger and drop a log line. Both fields are now `volatile`. (Logging only — never affected query results.) - **JOIN predicates handle string concatenation, char comparison, and ternaries.** The multi-table JOIN translator had the same three gaps as the single-table `WHERE` translator: `a.Name + x == b.Label` emitted SQL `+` (`text + text` has no operator), `a.Initial == 'A'` (a `char` column) bound the integer code point against `character(1)`, and a ternary `(a.X > 0 ? a.Y : a.Z) == b.W` emitted malformed SQL with no `CASE`. All three now translate correctly in join `ON` / `WHERE` clauses, matching the single-table path. - **A char comparison inside a projected or `ORDER BY` `CASE` binds the character.** A `Select.Case().When(x.Initial == 'A')` in a projection or an `ORDER BY` bound the integer code point `65` against the `character(1)` column (`character = integer`), like the original `WHERE` bug. The `SELECT` and `ORDER BY` translators now bind the value back as a one-character string inside their `CASE WHEN` comparisons. - **Rolling back a dropped table restores its foreign keys.** A `DROP TABLE` migration's DOWN re-created the table's columns, primary key, unique, and check constraints, but not its foreign keys (`CREATE TABLE` deliberately defers FKs to a later pass that the removed-table path was missing), so a rollback silently left the table without its referential integrity. The DOWN now re-adds each dropped table's foreign keys after all removed tables are re-created. - **Rolling back a dropped constraint + its column re-creates the column first.** When a migration dropped a constraint and a column it references in one step, the DOWN re-added the constraint before the column (`column "..." does not exist`), so the rollback failed to apply. Removed-constraint re-adds are now ordered after the column re-creations in the DOWN. - **Seed values survive the schema-snapshot JSON round-trip.** Restoring seed rows (a dropped table's DOWN, or a row add/remove) read them from the saved schema, whose values come back as `JsonElement` after JSON deserialization. The formatter decided quoting by re-parsing the text as a number, so a numeric-looking string (e.g. a `[Description("404")]`) lost its quotes and was emitted as an integer literal into a `text` column — failing the migration. `JsonElement` values are now formatted by their JSON kind (a string stays quoted, a number stays bare, bool/null map directly), and the numeric fallback parses culture-invariantly. - **Set-operation queries no longer leak a command.** Each `UNION` / `INTERSECT` / `EXCEPT` execution (`a.Union(b).ExecuteAsync()`) created an `NpgsqlCommand` that was never disposed — the enumerator disposed only the reader and the diagnostics scope. The command is now disposed when enumeration ends (completion, early `break`, or exception), matching the join and insert/update/delete paths. - **A `{{Type.Property}}` procedure placeholder resolves consistently with the generated columns.** For a `new`-shadowed property whose most-derived declaration is a non-column (`[Ignore]` / get-only), the placeholder resolver fell through to the shadowed base declaration and emitted a column the table generator never creates. It now takes the most-derived declaration and accepts it only if it is a real column (mirroring the table generator's name-dedup), so the placeholder and the emitted columns agree. - **A `[TableType]` baked `CREATE TABLE` emits the `[Default]` clause.** `DynamicTable.InstantiateAsync` baked a `[Default("...")]` column as `NOT NULL` with no `DEFAULT`, so the runtime-instantiated table diverged from the migration-generated one and an insert that omits the column (the `ServerDefaults` / `ExcludeAutoFields` path) failed with a not-null violation (or, for a nullable column, silently stored `NULL` instead of the default). The baked DDL now emits `DEFAULT `, translating `DbDefaults` tokens identically to the migration generator. - **Database-first scaffolding round-trips fixed-length `character(n)` columns.** The schema reader returned a bare `"character"` for a `char(n)` column while the forward map emits `"character(1)"`, so every `scaffold → generate` reported a spurious `ALTER COLUMN ... TYPE` change on those columns. The reader now preserves the length (`character(n)`), like it already does for `varchar(n)` and `numeric(p,s)`. - **Migration applied-state is resolved by insertion order, not the application clock.** The applied set and the current version were computed by ordering the `_scg_migrations` rows by `applied_at` only, with no tiebreaker and no `ORDER BY` on the read. Two rows can tie on `applied_at` (microsecond truncation, a coarse/virtualized clock, a tight rollback-then-reapply) or even invert under NTP skew, which could mis-net an UP/DOWN pair — leaving a rolled-back migration in the applied set (so its re-apply is skipped) or dropping an applied one (triggering a destructive re-run). Resolution now orders by the monotonic auto-increment `id` (the true apply order), which is immune to clock issues. - **A scalar procedure returning `DateTimeOffset` (or `DateOnly`/`TimeOnly`) materializes correctly.** `-- @returns scalar: DateTimeOffset` cast the boxed result directly, but a `timestamptz` is boxed by Npgsql as a `DateTime`, so `(DateTimeOffset)__scalar` threw `InvalidCastException`. The scalar path now routes the non-`IConvertible` types through the same width-tolerant converter as the row and aggregate read paths (mapping `DateTime` → `DateTimeOffset`). - **Two `[Table]` classes with the same simple name in different namespaces no longer crash the generator.** They produced identical generated-file hint names, so the source generator failed with an opaque "hintName already added" and emitted nothing. Hint names are now namespace-qualified, so e.g. `Auth.User` and `Billing.User` both generate. - **A generic or nested `[Table]` reports a clear diagnostic instead of uncompilable code.** The generator emits a non-generic, top-level `partial class`, which silently failed to compile (`CS0264`/`CS0260`) for a `[Table] class Foo` or a nested `[Table]` class. These now report `SCGDB025` ("a `[Table]` type must be a top-level, non-generic class") and skip codegen. - **A `[TableType]` without a primary key no longer warns.** The `SCGDB016` "no primary key" warning fired on a pure `[TableType]` (a runtime-named row shape used for projections that legitimately may have no key); it is now scoped to `[Table]` types, whose generated update/delete-by-key operations actually need one. - **A `[ValueConvertor]` column filtered in a `WHERE` predicate binds the converted value.** `Table.Query(x => x.Label == "world")` on a column with a `[ValueConvertor]` bound the raw CLR value while the column stores the convertor's output (e.g. an upper-cased `"WORLD"`), so the filter silently matched no rows. The `WHERE` translator now runs the comparison value through the same `ConvertToDbValue` the insert and `SET` paths use (for `==`, `!=`, and the relational operators), including the nullable-`!=` `OR col IS NULL` form. Such a predicate is excluded from the query-shape cache (the cache-replay path rebinds from the source and would skip the convertor), and a table with no convertor columns translates exactly as before (a single null check, no allocation). Verified against a live database. - **A `[FlaggedEnum]` junction back to a composite-key table generates one multi-column foreign key.** The auto-generated junction table emitted one single-column `FOREIGN KEY` per primary-key column of the main table, but no individual key column is unique on its own, so the migration failed to apply (`there is no unique constraint matching given keys`). The per-key references are now aggregated into a single composite `FOREIGN KEY (a, b) REFERENCES main (x, y)`. - **A wide-unsigned-backed enum column reads back correctly on every path.** An enum whose underlying type is `ushort` / `uint` / `ulong` is stored widened (`integer` / `bigint` / `numeric`), and two read paths cast it back wrong while the slow by-name path narrowed correctly. The fast ordinal row path passed a `numeric`-stored `ulong`-backed enum (which Npgsql boxes as `decimal`) straight to `Enum.ToObject`, which rejects a `decimal` and threw, so the whole row failed to materialize; and the procedure-DTO mapper read such a column with `GetFieldValue` / `` / ``, for which Npgsql has no handler, and threw. Both paths now narrow from the widened storage type before constructing the enum (matching the slow path, `ApplyDbValue`, and the non-enum unsigned columns). Verified against a live database. - **A column that is both renamed and altered in one migration rolls back correctly.** When a single model edit renamed a column and also changed its type (or nullability / default), the generated DOWN emitted the rename-back before the alteration-revert, but the revert still referenced the column's new name — which no longer existed after the rename-back — so the rollback failed at apply (`column "..." does not exist`). The DOWN now reverts the alteration (by the new name) before renaming the column back, and still re-creates a changed primary key by the old name afterward. - **Database-first scaffolding preserves single-column `UNIQUE` constraints (casing fix).** The schema reader stores a constraint's columns in PascalCase (the property name) while the column's DB name is snake_case, and the emitter matched the single-column unique against the DB name only — so the match failed and the `[Unique]` was never emitted, dropping the constraint on the next `generate`. The emitter now matches either casing, so a scaffolded single-column unique round-trips. - **Database-first scaffolding preserves foreign-key `ON DELETE` / `ON UPDATE` actions.** The class emitter wrote the FK target and key columns but never the referential actions, so scaffolding a `CASCADE` / `SET NULL` FK regenerated it without the action (silently losing the cascade) and showed a spurious `DROP`+`ADD` on every regenerate. The actions are now emitted as `OnDelete` / `OnUpdate` so they round-trip. - **Database-first scaffolding sanitizes non-identifier column/table names.** A DB name that is not a valid C# identifier (e.g. `2fa_enabled`, which begins with a digit, or a quoted name containing punctuation) was emitted verbatim as a property/class name and did not compile. The reverse-naming now splits on any non-alphanumeric separator and prefixes an underscore when the result would start with a digit, with the real DB name preserved via a `[Column]` attribute. - **Vault envelope encryption refuses to overwrite a keyring whose field is empty.** The 404-only bootstrap guard covered the read-*exception* path, but a secret that exists yet whose configured keyring field is missing or empty still fell through to bootstrap-and-overwrite (a misconfigured `KeyringField`, or a partial write), discarding the existing wrapped DEKs and making rows undecryptable. That case now fails loud instead of overwriting. - **`DbCheck.Value` preserves underscores in a property name.** The snake-case converter (which must match the generator's `JsonNamingPolicy.SnakeCaseLower`) stripped a leading underscore and collapsed a double underscore (`_Leading` → `leading`, `a__b` → `a_b`), so a `CHECK` on such a column referenced a name that does not exist and failed at apply. Underscores are now preserved verbatim, matching the policy exactly. - **Multiple `CHECK` constraints on one column get distinct names.** Two value checks on the same property (e.g. `[Min(5)]` and `[Max(100)]`, or a `[StringLength]` length check beside a value check) were both named `CHCK__`, so the `CREATE TABLE` emitted two same-named constraints (apply failed with `constraint "..." already exists`) and the `ALTER ... ADD` path collided with the existing one. A check's name now folds in its expression (stably), so each check over a column is uniquely named; `UNIQUE` / `FOREIGN KEY` names are unchanged. - **Database-first scaffolding: a NOT NULL column no longer reports a spurious change.** The schema reader stored a non-nullable column as `Nullable=false`, but the model analyzer represents it as `Nullable=null`, so every required column compared unequal and emitted a `SET NOT NULL` (and a `DROP NOT NULL` DOWN) on the first `scaffold` → `generate` round-trip. The reader now mirrors the analyzer (`true` when nullable, unset otherwise). - **Database-first scaffolding: `json` and unbounded `varchar` columns round-trip without a spurious type change.** A `json` column scaffolded as `json` while the `[RawJsonColumn]` analyzer reports `jsonb`, and an unbounded `character varying` scaffolded as `character varying` while a `string` regenerates as `text` — each produced a spurious (and data-touching) `ALTER COLUMN ... TYPE` on every round-trip. The reader now canonicalizes a JSON column to `jsonb` and an unbounded varchar to `text`. - **An optional foreign key to an enum table no longer crashes migration generation.** A nullable enum-table FK property (`public MyEnum? Role`, where `MyEnum` is a `[Table]` enum) passed the wrapped `Nullable` into the enum-table lookup, which looked for `[Table]` on `Nullable` (never present) and aborted the whole tool. The unwrapped enum type is now used, so an optional enum FK generates correctly. - **Property-level `[ForeignKey(TargetKeys = [...])]` is honored.** The explicit target columns were read with a cast that is always null under the migration analyzer's reflection context, so they were silently dropped and the FK was auto-resolved to the target's primary key instead (or hard-failed for a composite-PK target). The property-level form now reads `TargetKeys` the same way the class-level form does, so a FK to a non-PK unique column points where you asked. - **An enum table with an undescribed member applies its first migration.** The generated enum table's `description` column was created `NOT NULL`, but any enum member without a `[Description]` seeds that column with `NULL`, so the seed `INSERT` failed at apply (`null value in column "description" violates not-null constraint`) and broke the very first migration. The `description` column is now nullable (it is genuinely optional); `value` stays required. - **A nullable `[FlaggedEnum]` property no longer crashes migration generation.** A `public MyFlags? Roles` flagged-enum property passed the wrapped `Nullable` into the enum-table lookup, which aborted the whole tool (the same wrapped-type pitfall as the optional enum FK). The enum type is now unwrapped first, so an optional flagged-enum generates its junction table correctly. - **`[Encrypted]` is now authoritative for a column's type regardless of attribute order.** An encrypted column stores `bytea` ciphertext, but `[StringLength]` and `[Column(Type = "...")]` also set the column type, and the migration analyzer applied them in source order, last-wins — so `[Encrypted, StringLength(10)]` (a natural ordering) created a `varchar(10)` column and the encrypted `byte[]` was then written into it. The encrypted type now wins unconditionally, so the column is always `bytea`. - **`[Encrypted]` columns no longer emit a plaintext `DEFAULT`.** A C# property initializer (or `[Default]`) on an encrypted column produced `DEFAULT 'plaintext'` on the `bytea` column, which PostgreSQL rejects (invalid bytea literal) so the migration failed at apply. An encrypted column now carries no SQL default (a default on ciphertext is meaningless anyway). - **`[TableType]` `InstantiateAsync()` creates a non-nullable string/byte[] column as `NOT NULL`.** The runtime-baked `CREATE TABLE` forced every reference-type column nullable, so a non-nullable `string`/`byte[]` property was created `NULL`-able while the migration generator creates it `NOT NULL` — the two ways of creating the same table diverged, and a `NULL` row could materialize a null into a non-nullable CLR property. The baked DDL now follows the declared nullability, matching the migration. - **A joined column with a very long name round-trips instead of reading `NULL`.** The join `SELECT` aliased each column as an unquoted `aN_`; a column whose name pushed that alias past PostgreSQL's 63-byte identifier limit was truncated in the result label but not in the reader's lookup, so it silently read as `NULL` (and a mixed-case `[Column("Name")]` only survived via a case-insensitive fallback). The alias is now a short, quoted, positional `aN_cM`, so every joined column reads back regardless of its name. - **A procedure-DTO mapper no longer fails to compile on a rare name clash.** The generated mapper method name was derived by replacing every non-alphanumeric character of the DTO's full name with `_`, so two distinct DTO types whose full names differ only at a separator (e.g. `A.B.C` vs a `C` in namespace `A_B`) collapsed to the same name and produced a duplicate-method compile error. The name now includes a stable hash of the full type name, so the ids stay distinct. - **A custom (undeclared) `timestamptz` column reads back as `DateTimeOffset`.** `TryGetCustomValue(...)` used `Convert.ChangeType`, which throws for `DateTimeOffset` (so the read returned `false`), while a declared column read fine. Custom columns now use the same width-tolerant converter, so a captured `timestamptz` maps onto a `DateTimeOffset` like a declared one. - **Vault Transit (EaaS) re-encryption fails loud if run before priming.** `NeedsUpgrade` returned `false` when the encryptor had not yet loaded the current key version (before `RefreshAsync`), so a re-encryption pass run too early silently reported "nothing to upgrade" while doing nothing. It now throws a clear "not primed" error instead of a silent no-op. - **Clearer migration-analyzer errors for an incomplete `[ForeignKey]`.** A class-level `[ForeignKey]` missing its `Keys` threw a swallowed `NullReferenceException` reported as an opaque "Unexpected error"; and two `[FlaggedEnum]` properties resolving to the same junction table produced a duplicate `CREATE TABLE`. Both now report a clear, actionable message naming what to fix. ## v0.3.4 (unreleased) Integration fixes found wiring the library into a modular monolith (model project + API + Host). ### Added - **`contextName` config.** A new optional `socigy.json` field that decouples the generated C# identifiers from the physical database name, a lowercase `databaseName` like `identity` can now produce `IIdentityDb` / `AddIdentityDb()` while the connection-string key and physical database stay `identity`. Without it, the identifier is derived from `databaseName` (the first letter is upper-cased, so a lowercase name no longer trips `CS8981`). - **`excludeDbDefaults` on every insert path.** The context `InsertAsync` / `InsertMultipleAsync`, the static `Table.InsertMultipleAsync`, and `BulkCopy.InsertMultipleCopyAsync` now take `excludeDbDefaults: true` to omit `[Default]` columns so the server default applies, previously only the fluent `Insert().ExcludeAutoFields()` could do this, so a `[Default]` column left unset on those paths was silently written as the CLR default. ### Changed - **`required` members on `[Table]` models are supported.** Generated constructors emit `[SetsRequiredMembers]` (when the consumer targets a framework that has the attribute), so `public required string Email { get; set; }` no longer breaks the builders' `new()` constraint (`CS9040`). - **Dependencies now flow to consumers.** The package multi-targets `netstandard2.0;net8.0`; on `net8.0`+ it declares `Npgsql` and `Microsoft.Bcl.AsyncInterfaces` as normal dependencies, so the generated code compiles and migrations run without adding either package by hand (previously `CS0246` for `NpgsqlCommand`/`NpgsqlDbType`, and a runtime `FileNotFoundException` for `Microsoft.Bcl.AsyncInterfaces`). ### Fixed - **Generator no longer crashes in projects without `socigy.json`.** The analyzer flows transitively to consumer projects (e.g. an API or Host that references a model project); without a `socigy.json` it now emits nothing instead of throwing `CS8785` (`ArgumentNullException`), so the modular-monolith layout works without stripping the generator. - **Lowercase `databaseName` no longer produces invalid C# type names.** A Postgres-conventional `databaseName: "identity"` generated an all-lowercase `partial class identity`, tripping `CS8981` under `TreatWarningsAsErrors`. Generated identifiers are now valid regardless of the database name's casing. - **Multi-table migrations are named after all their tables.** A single migration that created `users` and `outbox` was auto-named `…_Addoutbox_…`; it is now `…_AddUsersAndOutbox_…` (and `…AndNMore` beyond two). ## v0.3.3 (22 June 2026) ### Added - **Binary COPY bulk insert.** `BulkCopy.InsertMultipleCopyAsync(rows, conn)` (and `DynamicTable.InsertMultipleCopyAsync`) load large batches via PostgreSQL's binary `COPY ... FROM STDIN (FORMAT BINARY)`, much faster than the parameterized multi-row insert and not bound by the 65,535-parameter limit. Values flow through the same per-column pipeline, so `[Encrypted]`, JSON, and value-convertor columns are handled identically; `NULL`s are written as SQL `NULL`. COPY cannot use `RETURNING`, so database-generated values are not propagated back. See [Bulk COPY](/database/0.3.5/querying/writing/bulk-copy). - **Scalar procedure returns.** `-- @returns scalar: T` generates a `Task` for single-value queries (`COUNT`, `MAX`, `EXISTS`, …), supporting primitives, `string`, `Guid`, date/time types, and their nullable forms. `NULL`/empty results map to `default(T)`; numeric widening (e.g. `COUNT`'s `bigint` → `int`) is handled. See [Procedure mapping](/database/0.3.5/advanced/procedure-mapping#scalar-returns). - **Affected-row procedure returns.** `-- @returns affected` generates a `Task` returning the number of rows a write affected, instead of the default `Task`. - **DTO procedure returns.** `-- @returns:` can now name a plain POCO or record (not just a `[Table]` type); the generator emits an AOT-safe, by-name materializer for it, ideal for projections and report shapes. - **Database-first scaffolding.** New CLI commands `scaffold schema` (live database → `structure.json`) and `scaffold classes` (database or `structure.json` → annotated `[Table]` C# classes), reusing the existing schema model so the result round-trips with `generate`. See [Database-first scaffolding](/database/0.3.5/migration/db-first). - **Source-linked debug symbols.** Packages ship with embedded PDBs and SourceLink, plus deterministic CI builds, so you can step into library source from your debugger. - **Public-API surface tracking.** The Core project now tracks its public API (`PublicAPI.*.txt`) to guard against accidental breaking changes ahead of 1.0. ### Changed - **New generator diagnostics** `SCGDB019`–`SCGDB022` validate the new procedure return directives (unsupported scalar type, conflicting/`malformed` `@returns`, and DTOs that cannot be mapped). See [Generator diagnostics](/database/0.3.5/advanced/generator-diagnostics). ### Fixed - **Migration generation on Linux/headless builds.** When no interactive name prompt is available, the generated migration's file and class name were derived from the multi-line schema-diff summary, producing an invalid name (embedded newlines and `:`). Headless builds now use a clean, deterministic `{timestamp}_{prefix}_{hash}` identifier. - **Package metadata** now points at the correct repository (`github.com/Socigy-org/Socigy.OpenSource.DB`). ### Security - **Versioned keyring envelope encryption.** `KeyringFieldEncryptor` encrypts under a current key while still decrypting values written under earlier keys (the key id is embedded in the ciphertext), so keys can be rotated without rewriting existing rows. - **Named encryption profiles.** Register multiple encryptors and route individual columns with `[Encrypted(Profile = "…")]`; reads are lock-free. Useful for mixing a fast local encryptor with a Vault-backed one for the most sensitive columns. - **HashiCorp Vault / OpenBao Transit.** New Transit-backed encryption modes (data-key envelope and direct EaaS) for the `Socigy.OpenSource.DB.HashiCorp` package, alongside the existing KV-v2 key and rotating database credentials. Verified against both HashiCorp Vault and the API-compatible [OpenBao](https://openbao.org/) fork. - **Offline re-encryption.** A batched `FieldReencryptor` upgrades existing rows (generated, dynamic, and `[TableType]` tables) to the current key version/profile. ## v0.3.2 (6 June 2026) ### Added - **Dynamic (runtime-named) tables.** New `[TableType]` attribute: declare a typed column shape once and bind the **table name at runtime** via `WithTableName(...)`, returning the typed entity (NativeAOT-safe). Full CRUD + aggregates, both standalone (`WithConnection`/`WithTransaction`) and through a context (`db.DynamicTable(name)`). See [Dynamic tables](/database/0.3.5/dynamic-tables/declaring). - **Custom (undeclared) columns.** `WithCustomColumns(...)` captures extra runtime columns into each row, `TryGetCustomValue(...)` reads them, and `DB.CustomField("name")` filters on them inside a normal predicate. `MapTypeAsync(name, conn)` auto-discovers a table's extra columns once and caches the schema. - **Runtime table lifecycle.** `InstantiateAsync()` (CREATE TABLE from the declared shape), `DeleteInstanceAsync()` (DROP TABLE), and `InstanceExistsAsync()`. `[TableType]` tables live outside the migration history, so the type manages its own DDL. - **Extended join builders.** Joins now support **3 and 4 tables** (chain `.Join(…).Join(…)`), **`OrderBy`/`OrderByDesc`**, **client-side projection** (`.Select((a,b,…)=>…)` → a typed result), and **aggregates** (`CountAsync`/`SumAsync`/`AvgAsync`/`MinAsync`/`MaxAsync`). See [Joins](/database/0.3.5/querying/reading/joins). ### Fixed - **Outer joins now return `null` for an unmatched side** (was a zeroed default instance). Join tuple elements are nullable, so you can distinguish "no match" from a real row of defaults. - **`Query(pred).Join<…>(…)` now filters the driving table.** The driving predicate was previously dropped. - **Migrations apply atomically.** Each migration's schema change and its `_scg_migrations` row now commit (or roll back) in a single transaction, so a crash can no longer leave the schema changed-but-unrecorded or recorded-but-not-changed. See [Applying migrations](/database/0.3.5/migration/applying). - **Migration order follows the `PreviousId` chain, not id sorting.** Ids are minute-granularity timestamps; two migrations created in the same minute (or any non-sortable id) could previously apply out of order. A broken or forked chain now fails loudly. - **Rollback-aware version detection.** The current version is computed from the full history honoring `is_rollback`, so a rolled-back migration is no longer reported as current. - **Deterministic constraint names.** Column-less constraints (e.g. raw CHECKs) no longer get a random `Guid` name, so regenerated migrations are reproducible. ### Changed - **Destructive and lossy migration statements are flagged.** Generated migrations prefix data-losing operations with `-- [SOCIGY:DESTRUCTIVE]` (table/column drops) or `-- [SOCIGY:LOSSY]` (narrowing/unsafe type casts), and the CLI lists them at generation time. See [Schema generation](/database/0.3.5/migration/schema-generation). ### Security - **Field encryption: associated-data binding (automatic).** Generated code now binds every encrypted value to its `table:column` (authenticated into the HMAC, not stored), so a value cannot be relocated to a different column/row and still decrypt. `IFieldEncryptor`/`FieldCrypto` also expose the optional `associatedData` for custom use. See [Encrypted columns](/database/0.3.5/defining-models/encrypted-columns). - **Field encryption: key zeroing & portable format.** `AesFieldEncryptor` implements `IDisposable` (zeroes key material) and now encodes values in a fixed little-endian byte order so ciphertext is portable across architectures. - **Vault: auth token is kept alive.** A background service renews the Vault token (renew-self, or AppRole relogin at max TTL) so long-running apps no longer fail once the initial token expires. - **Vault: credential renewal tracks the real lease TTL** (renews at ~2/3 of the lease) instead of a fixed interval that could outlast it. - **Vault: connection strings are built with `DbConnectionStringBuilder`**, so leased passwords containing `;`, `=`, quotes or spaces are escaped correctly. - **Vault: a warning is logged** when the Vault address uses plaintext HTTP to a non-loopback host. ## v0.3.1 (5 June 2026) ### Added - **`InsertMultipleAsync` on the database context.** `I{Table}Set` now exposes `InsertMultipleAsync(entities, includeAutoFields, ct)`, batching a whole collection into multi-row `INSERT`s within the unit-of-work scope. See [Database context → Table-set methods](/database/0.3.5/core-concepts/database-context). - **Auto-field control on context inserts.** `InsertAsync` and `InsertMultipleAsync` take `includeAutoFields` (default `false`); pass `true` to also write auto-increment columns (supply your own values), the context equivalent of `WithAllFields()`. Backed by a new `GetInsertPlan(bool includeAutoIncrement)`. - **Projecting `ForEachAsync`.** Streams matching rows, projects each through the callback, and returns the results (materialized inside the scope), so you can transform rows without a lazy enumerable escaping the connection. - **Scalar & aggregate queries.** `CountAsync` (a real `SELECT COUNT(*)`, replacing the previous client-side drain), plus `SumAsync`/`AvgAsync`/`MinAsync`/`MaxAsync` and a single-value `ScalarAsync`, on both the query builder and the database context, parameterized via the existing WHERE translation. See [Aggregates & scalars](/database/0.3.5/querying/reading/aggregates). ### Fixed - CI now builds, packs, and publishes the optional `Socigy.OpenSource.DB.HashiCorp` package independently of the main package (each is version-checked separately, so a re-run still ships one when the other is already published).