/DB

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.

updated 27 Jun 202656 min readv0.3.5View as Markdown
26 June 2026
Socigy.OpenSource.DBv0.3.5stable
Added
  • `keep` selector on the convenience insert methods. Write 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, the SumAsync/AvgAsync/MinAsync/MaxAsync aggregates, ScalarAsync, the static InsertAsync/UpdateAsync, and the insert/update/delete command builders now accept an optional token and flow it to OpenAsync and command execution, so a cancelled request stops the database work. The parameter defaults to default, so existing call sites are unchanged.
Changed
  • `InsertFields` enum replaces the per-call insert booleans. The per-call insert field control on the context, static, and bulk insert methods 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.
Fixed
  • Migration apply is idempotent across app restarts. The second and later startups no longer re-run already-applied migrations and crash with 42P07: relation already exists. The _scg_migrations existence probe read PostgreSQL to_regclass as a raw regclass that Npgsql cannot materialize once the table exists; it now casts to a boolean (to_regclass(...) IS NOT NULL).
  • A failed migration-version read no longer causes a destructive re-apply. GetCurrentMigrationVersion() now logs and rethrows instead of swallowing every exception into "nothing applied", and the forward apply loop skips any migration already recorded as applied (rollback-aware, so UP, DOWN, UP still re-applies).
  • Concurrent migration apply across replicas is serialized. Several instances starting at once (rolling deploy, scaled replica set) previously raced the same CREATE TABLE / DDL and crashed with 42P07 / 42701 / 23505 or half-applied schema. Migration apply now holds a PostgreSQL session-level advisory lock, so one instance migrates while the rest wait and then skip what was applied. The lock auto-releases if a migrator dies, and an already-current database short-circuits without locking.
  • 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 it at once raised 42P04. The race is now treated as success.
  • Binary COPY no longer throws on a UTC `DateTime`. InsertMultipleCopyAsync wrote DateTime values with an explicit timestamp type that Npgsql rejects for Kind=Utc, so bulk-inserting DateTime.UtcNow threw. UTC values are now normalized so they round-trip.
  • `InsertReturningAsync<T>` works for `Guid` / `uuid` keys. It routed the result through Convert.ChangeType, which throws for non-IConvertible types like Guid, DateTimeOffset, and byte[]; it now returns the value directly when it is already the requested type.
  • **RETURNING * propagation no longer silently drops server-generated values.** A column mismatch was swallowed by a blanket catch that left a generated key unset while reporting success; the catch is narrowed so a real reader fault surfaces.
  • Single-table predicates quote column identifiers. WHERE / SELECT / ORDER BY on a single table emitted bare column names (joins already quoted theirs), breaking columns whose name is a reserved word (order, user) or mixed-case. Identifiers are now quoted consistently.
  • Dynamic-table column cache is bounded. The per-table-name cache behind DynamicTable.MapTypeAsync grew without limit; in a multi-tenant app with many runtime table names it now evicts to stay within a fixed cap.
  • Vault Transit decrypt cache no longer hands back a shared array. It returned the same byte[] it cached, and a byte[] encrypted column passes that array straight to the caller, so mutating the buffer corrupted the cached value for later reads. The cache now keeps and returns private copies.
  • Offline re-encryption fails fast on a NULL primary key. Keyset pagination over the primary key would silently drop NULL-key rows from every batch after the first; it now throws a clear error naming the table and column.
  • Aggregate overflow is an actionable error. SumAsync / AvgAsync widen like PostgreSQL (SUM of a bigint returns numeric), so a too-narrow result type threw a bare OverflowException; the message now explains the widening and points to a wider type such as decimal.
  • Vault keyring parsing is culture-invariant and overflow-safe. A corrupted keyring field now fails as a clear malformed-keyring error instead of an unscoped exception.
  • Plain enum columns are readable again. Querying any table with a plain (non-[FlaggedEnum]) enum column threw InvalidCastException, because the row materializer used GetFieldValue<TEnum>, which Npgsql has no handler for. Enum columns now read through their underlying integer (verified against a live database).
  • Binary COPY and parameterized inserts handle `DateOnly` / `TimeOnly` / `TimeSpan` / `DateTimeOffset`. These valid column types were missing from the type maps and fell back to text, so 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 onto a `DateTimeOffset` property. WithValuePropagation() (RETURNING *) read such a column as the DateTime Npgsql returns and could not convert it; it now maps correctly.
  • Stored-procedure `affected` / `void` methods 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. It was bound without an explicit type, so Npgsql inferred timestamptz from Kind=Utc and PostgreSQL converted it to local wall-clock on store; under a non-UTC session DateTime.UtcNow landed hours off. All write paths now store the wall-clock verbatim.
  • `== null` / `!= null` against a captured variable matches the right rows. Only a literal null became IS NULL / IS NOT NULL; a null-valued variable became col = @p, which matched no rows (three-valued logic). Now both forms use IS NULL / IS NOT NULL.
  • `string.Equals(value, StringComparison.OrdinalIgnoreCase)` is case-insensitive. The trailing StringComparison was ignored; it now emits LOWER(col) = LOWER(@p).
  • `Contains` / `StartsWith` / `EndsWith` escape LIKE wildcards in projections. The SELECT/ORDER BY path did not escape % _ \, so 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 PK and serial columns, so a multi-row update wrote the instance key into every matched row (duplicate-key / data loss). PK and auto-increment are now excluded from SET, and the PK-derived WHERE is quoted.
  • `jsonb` columns update correctly via `WithFields`. A selective JSON update bound the serialized string as text (jsonb = text error); it is now cast to jsonb.
  • Instance `DELETE` handles enum, `DateTimeOffset`, and wider numeric primary keys. Its type map lacked the enum/temporal cases the insert path has, so such keys were sent as text and threw or matched nothing.
  • JOIN multi-key descending sort applies `DESC` to every key (it sorted only the last key, paging the wrong rows). `Limit(0)` on a JOIN or set-operation query now returns zero rows instead of everything. An outer-join right side with no primary key now yields null rather than a zeroed instance.
  • `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; it now throws a clear error to combine flags with &&. Single-flag HasFlag is unchanged (and is now excluded from the query-shape cache so a composite value can not reuse a single-flag plan).
  • Granting a flagged-enum value is idempotent. The junction insert was a bare INSERT, so granting an already-present flag (or an EditRole / Sync batch with a duplicate) threw 23505 and could half-apply; it now uses ON CONFLICT DO NOTHING.
  • Migration generation fixes. Changing a [Default] emitted the raw $socigy$ token (migration failed at apply); a primary-key change had no real DOWN (rollback left the table with no primary key); a no-primary-key seed DELETE used col = NULL (a silent no-op) instead of IS NULL; and a copy/paste guard skipped removed-constraint hashing in migration naming. All corrected.
  • The CLI returns a non-zero exit code on failure. Generate/scaffold error paths (missing assembly, missing --connection / --from-schema) 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 also surfaces the real parse error instead of an opaque message.
  • Vault renewal retries quickly after a failure. A failed token/credential renewal rescheduled at the long fallback (token: 30 minutes; credentials: 2/3 of a now-stale lease), which could fall after expiry; a failed attempt now retries at the ~30-second floor.
  • Generated sequence accessor + scaffolding hardening. GetNextValueAsync now quotes the sequence name to agree with PeekCurrentValueAsync (case-sensitive names); database-first scaffolding JSON-escapes [Default] literals containing control characters and tolerates foreign keys with NULL column arrays.
  • Database-first scaffolding preserves `numeric(precision, scale)`. A scaffolded decimal column collapsed to a bare numeric, dropping precision/scale; the reader now captures them so a regenerated column matches the source.
  • `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 the check referenced a non-existent column and failed at apply. It 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 emitted the raw PascalCase property name (UNIQUE ("Phone")) instead of the column; an unresolved constraint column now falls back to its snake_case name.
  • A `DateTimeOffset` with a non-zero offset no longer crashes every insert path. Npgsql only writes a DateTimeOffset at offset 0 to timestamptz, so a DateTimeOffset.Now threw ArgumentException. The COPY, single/multi-row, update, delete, dynamic-table, and procedure paths now normalize to the same UTC instant (ToUniversalTime()). 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; every write path now widens them to int / long / numeric before binding. Verified against a live database.
  • Stored-procedure parameters get the same value coercion as inserts. The generated binder set no explicit type and skipped the enum/DateTime/DateTimeOffset/unsigned normalization, so such a procedure argument threw at execution or shifted by the session time zone. It now applies the same normalization.
  • `Limit(0)` on a single-table query returns zero rows. The single-table parser treated limit == 0 as the "no limit" sentinel and took the cached no-LIMIT fast path on a filtered query, returning the whole table. It now emits LIMIT 0.
  • `DynamicTable.MapTypeAsync` resolves columns for the right schema. A bare WHERE table_name = @t matched the name in every schema, so a same-named table in another schema merged foreign columns into the extra-column set and corrupted rows. It now resolves through the connection search_path with to_regclass and reads exactly that table.
  • A database error mid-result is recorded as a failed span. A server error surfacing partway through row streaming would still let reader disposal mark the trace span successful; the instrumented reader now records the failure when a row read throws.
  • A custom parameter-redaction hook output is length-capped. RedactParameter output bypassed MaxParameterValueLength, so a hook echoing the value could bloat every span/log; the cap now applies to the hook output too.
  • A nested call to a different database no longer runs on the wrong connection. In a modular monolith with several contexts, calling one database from inside another’s unit of work joined the process-wide ambient scope unconditionally, so the inner database’s queries ran on the outer database’s connection/transaction. A nested call now joins only a same-database scope; a different database opens its own. A transactional call nested in a same-database non-transactional scope now throws instead of silently running untransacted.
  • 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 type, which Npgsql cannot read, so querying threw InvalidCastException. The row, projection, public ReadValue/ConvertFrom, and aggregate/scalar reads now narrow from the widened storage; the baked [TableType] DDL also maps these (and sbyte) correctly. Verified against a live database.
  • `MinAsync`/`MaxAsync`/`ScalarAsync` of a `DateTimeOffset` column no longer throw. They used raw Convert.ChangeType, which throws for DateTimeOffset; Npgsql returns timestamptz as a UTC DateTime. They now use the same converter as the row path. Verified against a live database.
  • A `byte`-backed enum value convertor reads back correctly. DbEnumValueConvertor required an exact underlying-type match, but a byte-backed enum is stored as smallint (returned as short) and threw on read; it now converts to the underlying type first.
  • Compiled-query cache: a captured `null` operand no longer reuses the wrong SQL. x.Name == value becomes IS NULL when value is null and = @p otherwise, but the cache key didn’t distinguish them, returning wrong rows. An (in)equality whose value operand is a captured nullable type is no longer cached (literals and non-nullable int/Guid keys still cache).
  • Compiled-query cache: case-insensitive string compares don’t collide with case-sensitive ones. Equals(s, OrdinalIgnoreCase) emits LOWER(…)=LOWER(…) while the case-sensitive overload emits = @p; both hashed the same. The StringComparison value is now folded into the key (or left uncacheable when it’s a runtime value).
  • Compiled-query cache: `DB.CustomField("col")` no longer reuses another column’s SQL. The column name is spliced into the SQL text but collapsed to a value token, so different columns shared one plan. 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 rolling back 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 UP apply loop skips already-applied migrations, but the DOWN loop did not, so a racing/stale rollback could re-run a migration’s DownSql and write a duplicate rollback row. The DOWN loop now uses the same rollback-aware applied-set guard.
  • A value convertor on a primary key is honored in the `WHERE` clause. An instance UPDATE/DELETE built its WHERE from the raw PK value while the column stores the convertor’s output, so it silently matched no rows. The PK value now goes through the same convertor as the insert/SET path.
  • 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 integer DB type, so a convertor that stores an enum as text threw InvalidCastException. It now coerces and types the parameter by the runtime value, matching the insert path.
  • `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 a case-insensitive compare became case-sensitive). It now emits LOWER(...)/UPPER(...) and throws on a genuinely unsupported method.
  • A null argument to `Contains`/`StartsWith`/`EndsWith`/`Equals` in a predicate fails fast. A null pattern became LIKE '%%' (matched everything) and Equals(null) became col = NULL (matched nothing); both now throw a clear ArgumentNullException, in 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 logins, nondeterministic active token); renewal/relogin now runs under a lock.
  • Each registered database keeps its own context options. In a modular monolith calling Add{Db}Context for several databases, the options were a shared non-keyed singleton and every factory resolved that one instance, so each database after the first inherited the first’s ConnectionKey/lifetime. Each factory now captures its own registration’s options.
  • Enum columns backed by a custom value convertor work on every write path. The runtime-vs-declared enum check is now also applied to binary COPY, the dynamic-table writer, and delete-by-key, so a convertor storing an enum as text no longer throws on those paths; COPY also derives the wire type from the value. Verified against a live database.
  • A composite `[Flags]` value can’t be written as one unqueryable junction row. Granting a multi-bit value (e.g. Reader | Writer) stored a single row with the combined integer that no single-flag query could match; the write side now rejects composites (grant flags individually or via the sync method), matching the read side.
  • Adding an `[AutoIncrement]` column in a migration generates a working sequence. The ALTER ... ADD COLUMN default referenced an un-prefixed sequence name that was never created (relation "_id_seq" does not exist); it now references the created, column-typed sequence, and the rollback drops the column before the sequence.
  • A join aggregate over a `DateTimeOffset` column no longer throws. MinAsync/MaxAsync<DateTimeOffset> on a Join(...) used raw Convert.ChangeType, throwing InvalidCastException; it 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 binds the underlying integer, so inserts failed. The baked DDL now types enum columns as their integral type. Verified against a live database.
  • `ForEachAsync` honors its `CancellationToken` during the read (it was only checked between rows), and the streaming reader disposes its DbCommand on every exit path.
  • Database-first scaffolding maps `character(n)` (n>1) to `string` instead of a single C# char (which truncated); only character(1) maps to char.
  • Database-first scaffolding skips a cross-schema foreign key (target table in an un-scaffolded schema) with a warning, instead of emitting a [ForeignKey(typeof(<MissingType>))] reference that does not compile.
  • A `byte`/`sbyte` DTO property (including a `byte`-backed enum) read from a procedure result no longer throws. Stored as smallint (Npgsql returns short), they were read with GetFieldValue<byte> (throws); they now narrow from short, like the unsigned types. Verified against a live database.
  • Rotating an envelope-encryption keyring no longer fails concurrent reads. The previous keyring was disposed synchronously (zeroing its keys), so an in-flight Encrypt/Decrypt that captured it could throw CryptographicException on valid data; it is now disposed after a grace window so in-flight operations drain first.
  • A from-scratch `CREATE TABLE` migration emits `NOT NULL` for required columns. Non-nullable, non-primary-key columns were created NULLABLE (the analyzer marks required nullability as "unset", and the generator only emitted NOT NULL for an explicit false). Required columns are now created NOT NULL.
  • A get-only / computed / init-only property on a `[Table]` model is ignored instead of breaking the build. It was treated as a column and the generated materializer assigned to it (CS0200); a non-writable property is now skipped like [Ignore].
  • A `{{Type.Property}}` SQL placeholder for a non-column property is reported, not silently mis-resolved. An [Ignore]/[FlaggedEnum]/static/get-only property fabricated a quoted column name with no backing column; it now reports the unknown-property diagnostic.
  • Migration ids include seconds, so two migrations generated in the same minute don’t collide. The id (ordering prefix + filename) was minute-truncated, so same-minute runs could overwrite each other or sort ambiguously. Ids are now yyyyMMddHHmmss; an old minute id stays a lexical prefix of a same-minute seconds id, so apply order is unchanged.
  • The schema snapshot is written atomically after a `generate`. It was moved aside then rewritten, leaving a window with no snapshot file — a crash there made the next run re-emit every migration. It is now written to a temp file and moved into place.
  • `ExecuteReturningAsync<DateTimeOffset>` no longer throws. It used raw Convert.ChangeType (throws for DateTimeOffset; a timestamptz returns 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 the default 30s timeout, so a slow ALTER ... TYPE/backfill could be aborted mid-apply; the migration command timeout is now disabled.
  • `WHERE` filters on `DateTime`/`DateTimeOffset`/unsigned values bind correctly. The predicate binding never got the write-path normalization, so x => x.At == DateTime.UtcNow (a timestamp column) was inferred as timestamptz and shifted by the session time zone (wrong rows), a non-UTC DateTimeOffset filter threw, and an unsigned filter threw. The WHERE path now applies the same Kind=Utc → Unspecified, DateTimeOffset → UTC, and unsigned-widening normalization. Verified against a live database.
  • A selective (`WithFields`) update of a `DateTime`/`DateTimeOffset`/unsigned column binds correctly — the WithFields SET path normalized only enums and had the same shift/throw; it now applies the full normalization.
  • An empty `WithFields(...)` selector fails with a clear error instead of malformed SET WHERE; a duplicate member in WithFields is de-duplicated.
  • Vault credential/token renewal is never scheduled past a short lease's expiry. The 30s busy-loop floor was applied even when it exceeded the lease lifetime, so a lease shorter than ~45s could renew at/after expiry; the floor is now capped against the lease (a very short lease renews at 2/3 of its lifetime).
  • A custom `[AutoIncrement("name")]` sequence works on a runtime-instantiated table. DynamicTable.InstantiateAsync() baked the column as serial (which creates {table}_{column}_seq), so the runtime sequence accessor targeting the custom name threw; the baked DDL now creates the custom-named sequence and defaults the column to it.
  • A `Contains` filter (`= ANY(@array)`) normalizes each array element like a scalar `==` filter. roles.Contains(x.Role) (enum), a DateTimeOffset[]/unsigned collection threw, and a Kind=Utc DateTime[] silently matched the wrong rows; each element is now normalized as the scalar path is. Array literals (the span-based Contains) are supported too. Verified against a live database.
  • A null pattern in a cached `Contains`/`StartsWith`/`EndsWith` no longer matches every row. First translation rejected a null LIKE pattern but a cache-replay bypassed it (producing LIKE '%%'); the guard now lives on the shared bind path.
  • An `ELSE`-less `Select.Case()...End()` produces valid SQL. The End() terminator emitted no END, so the projected CASE was malformed; it now closes the block.
  • `!=` against a nullable column includes NULL rows, matching C# semantics. x.NullableValue != 5 emitted col <> @p (which excludes NULL rows), but null != 5 is true in C#; it now emits (col <> @p OR col IS NULL) like EF Core. == and non-nullable columns are unchanged.
  • **StartsWith/EndsWith/Contains honor a StringComparison.*IgnoreCase argument** (emit ILIKE); they previously silently emitted case-sensitive LIKE, unlike string.Equals.
  • Capturing parameter values for diagnostics can no longer crash a query. A parameter whose ToString() throws, or a throwing RedactParameter hook, propagated out of the command (and could mask the original error); rendering is now exception-safe (<unrenderable>).
  • Diagnostics render array/collection parameters and timestamps usefully — an = ANY(@p) array now shows its bounded contents instead of System.Int32[], and DateTime/DateTimeOffset render round-trip (o) so Kind/offset is visible.
  • An inline constructor on the value side of a comparison binds as one value. x.D > new DateOnly(2020,1,1) was shattered into ("D" > @p0@p1@p2) (a syntax error) and x.Gid == new Guid("...") bound the string not a Guid; the WHERE visitor now folds an inline constructor into a single normalized parameter and throws on a column-dependent one.
  • A column transform in single-table `ORDER BY` fails fast. .OrderBy(x => new object[] { x.Name.ToUpper() }) silently dropped the ToUpper() and emitted ORDER BY "Name" (wrong order); an unsupported method-call transform now throws NotSupportedException, like the operator path.
  • A DTO with multiple constructors maps through the widest one. A procedure-return DTO declaring a convenience constructor alongside its primary one bound InstanceConstructors[0] (declaration order), which could pick the narrow overload and drop members to default; the generator now picks the highest-arity constructor (and reports SCGDB021 on a tie).
  • An inline constructor in a projection binds as one value. The SELECT visitor shared the WHERE shatter — a projected new DateOnly(...) / new Guid("...") (incl. in Case().Then/.Else) emitted @p0@p1@p2 (invalid SQL) or bound a Guid as its string; it now folds to a single normalized parameter.
  • An unsupported method call in a projection fails fast. A column-dependent method call the SELECT visitor cannot translate (e.g. x.Created.AddDays(1)) silently emitted the bare column; it now throws NotSupportedException like the WHERE / ORDER BY paths.
  • JOIN `ON`/`WHERE` parameters are normalized like single-table predicates. The multi-join visitor normalized only enums, so a UTC DateTime, an offset DateTimeOffset, or an unsigned value in a join condition was silently shifted by the session time zone, rejected, or unmappable; it now binds through the same Normalize as single-table.
  • An inline constructor in a JOIN `ON`/`WHERE` binds as one value. a.Created > new DateTime(2020,1,1) emitted @p0@p1@p2 and b.Gid == new Guid("...") bound the string; the multi-join visitor 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 type from the declared PK type, so a convertor PK returning a different CLR type (e.g. enum→string) had its string forced to Integer and Npgsql threw, while UPDATE succeeded; delete now binds the converted value and lets Npgsql infer, matching update.
  • `DynamicTable` aggregate/scalar reads no longer crash for enum / `DateTimeOffset` results. MinAsync/MaxAsync/ScalarAsync coerced with Convert.ChangeType, which throws for an enum target (read as its underlying int) and for a DateTimeOffset; 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 type map had no entry for uint/ulong/ushort/sbyte, falling through to the raw .NET name (uint32, …) → invalid CREATE TABLE DDL; they now map to bigint/numeric/integer/smallint, matching the source generator.
  • A captured value in an `ORDER BY` `CASE` is normalized like the other paths. The ORDER BY visitor bound a captured enum / UTC DateTime / offset DateTimeOffset / unsigned value inside a Select.Case() raw — silently mis-ordering or throwing — and lacked the inline-constructor fold; both are now handled, matching the WHERE/SELECT/JOIN visitors.
  • A `.Contains` over a nullable-enum / nullable-unsigned collection works. roles.Contains(x.NullableRole) with a List<Role?> (or List<uint?>) threw InvalidCastException (array stayed Role? while elements widened to int); the element type is now unwrapped, widened, and re-wrapped as Nullable<widened>, the nullable analog of the existing enum/unsigned array fix.
  • Database-first scaffolding strips schema-qualified casts from column defaults. A default with a schema-qualified or quoted type cast (e.g. a ::public.citext cast) was only partially stripped, leaving a .citext fragment in the literal → a [Default] 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 keyring caught every VaultApiException as "first run", so a non-404 failure (503/429/timeout, or a write-but-not-read KV policy) made bootstrap overwrite the existing keyring with a fresh current=1 DEK — making all already-encrypted rows undecryptable. Only a genuine 404 now triggers bootstrap; every other error propagates.
  • Non-enum `byte` / `sbyte` table columns are readable. Stored as smallint, they were read via GetFieldValue<byte> on the default fast path (no int2→byte Npgsql handler), throwing InvalidCastException so the row never materialized — while the slow and DTO paths narrowed correctly. The fast path now narrows from short; the unsigned narrowings also became checked so an out-of-range value throws consistently instead of silently wrapping on one path.
  • A `[Table]` maps columns inherited from a base class and declared across multiple `partial` files. Discovery read only the single declaration carrying [Table], so an inherited or other-partial property was silently dropped from the whole column set (never created/inserted/selected/filterable). It now walks the symbol base chain and all partials (dedup by name, most-derived first); a flat single-class model generates identically.
  • Database-first scaffolding preserves single-column `UNIQUE` constraints. The C# class emitter wrote [ForeignKey] but not [Unique], so scaffolding dropped every unique constraint → the next generate emitted a DROP CONSTRAINT, silently losing uniqueness on a scaffold→migrate round-trip. A single-column unique now emits a property-level [Unique].
  • Migration apply reads the current version under the advisory lock. The "already current" short-circuit and the apply-vs-rollback direction were decided from a version read taken before the migration lock, so a startup racing a concurrent rollback on another replica could return "already current" while the schema was moved. The version is now read under the lock.
  • Database-first scaffolding preserves composite-key column order. A composite PK whose key order differed from the column order was emitted in column order (changing the key); [PrimaryKey] now takes an optional position ([PrimaryKey(order)]), the reader records each key ordinal, and the generator/emitter preserve it. Single-column and 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 it survives the scaffold→migrate round-trip instead of being dropped.
  • A `Guid.Sequential` default no longer produces non-applyable DDL. It translates to uuid_generate_v1mc() (in the uuid-ossp extension, not installed by default), so apply failed; the migration now emits CREATE EXTENSION IF NOT EXISTS "uuid-ossp" first. Guid.Random (gen_random_uuid()) is built in and unaffected.
  • `[Encrypted]` combined with `[StringLength]` is now a build error. An encrypted column is bytea; combining it with [StringLength] produced an order-dependent character varying(n) DDL contradicting the bytea the runtime writes. SCGDB002 now also covers [Encrypted]+[StringLength], failing the build.
  • A baked `[TableType]` CREATE TABLE maps an `object` column to `jsonb`. It fell through to text while the migration generator maps object to jsonb; they now agree, so a runtime-instantiated and a migration-managed table do not diverge.
  • String concatenation in a predicate emits `||`. x => x.Name + suffix == value compiles to a BinaryExpression Add, rendered as SQL + → "operator does not exist: text + text"; a string Add now emits ||.
  • A `char` comparison binds the character, not its code point. A char-column equality is promoted by C# to int == int, so the translator bound the code point 65 → character(1) = integer; it now binds the value back as a one-character string, on both the first translation and the cached query-shape replay (the rebind lives on the shared parameter-binding path).
  • A ternary in a predicate emits a SQL `CASE`. x => (x.A > 0 ? x.B : x.C) == 5 had no VisitConditional in the WHERE translator (malformed SQL); it now emits CASE WHEN ... THEN ... ELSE ... END, matching the SELECT/UPDATE translators.
  • Diagnostics logger cache is published safely under concurrency. The double-checked-locking logger cache read its fields outside the lock without volatile, so a concurrent caller could see the published factory but a stale/null logger and drop a log line; both fields are now volatile. (Logging only.)
  • JOIN predicates handle string concatenation, char comparison, and ternaries. The multi-table JOIN translator shared the three single-table WHERE gaps: a.Name + x emitted SQL +, a.Initial == char bound the code point against character(1), and a ternary emitted malformed SQL with no CASE. All three now translate correctly in join ON/WHERE.
  • A char comparison inside a projected or `ORDER BY` `CASE` binds the character. Select.Case().When(x.Initial == char) in a projection or ORDER BY bound the integer code point against the character(1) column; the SELECT and ORDER BY translators now bind a one-character string inside their CASE WHEN.
  • Rolling back a dropped table restores its foreign keys. A DROP TABLE DOWN re-created columns/PK/unique/check but not foreign keys (deferred to a pass the removed-table path lacked), so rollback lost referential integrity; the DOWN now re-adds each dropped table\u2019s FKs after all tables are re-created.
  • Rolling back a dropped constraint + its column re-creates the column first. Dropping a constraint and a column it references in one step produced a DOWN that re-added the constraint before the column (column does not exist); removed-constraint re-adds are now ordered after the column re-creations.
  • Seed values survive the schema-snapshot JSON round-trip. Restored seed rows come back as JsonElement; the formatter decided quoting by re-parsing as a number, so a numeric-looking string (e.g. [Description("404")]) lost its quotes and became an integer literal into a text column. JsonElement is now formatted by its JSON kind and the numeric fallback parses culture-invariantly.
  • Set-operation queries no longer leak a command. Each UNION/INTERSECT/EXCEPT execution created an NpgsqlCommand the enumerator never disposed (only the reader and scope were); the command is now disposed when enumeration ends, 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, the resolver fell through to the shadowed base 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.
  • A `[TableType]` baked `CREATE TABLE` emits the `[Default]` clause. InstantiateAsync baked a [Default("...")] column as NOT NULL with no DEFAULT, so it diverged from the migration schema and an insert omitting the column failed (or silently stored NULL); the baked DDL now emits DEFAULT <expr>, translating DbDefaults tokens like the migration generator.
  • Database-first scaffolding round-trips fixed-length `character(n)` columns. The schema reader returned a bare "character" for char(n) while the forward map emits "character(1)", so every scaffold→generate reported a spurious type change; the reader now preserves the length, like varchar(n) and numeric(p,s).
  • Migration applied-state is resolved by insertion order, not the application clock. The applied set and current version ordered _scg_migrations rows by applied_at only (no tiebreaker, no ORDER BY); a tie (microsecond truncation, coarse clock, tight rollback-then-reapply) or NTP skew could mis-net an UP/DOWN pair — leaving a rolled-back migration applied or dropping an applied one. Resolution now orders by the monotonic auto-increment id.
  • A scalar procedure returning `DateTimeOffset`/`DateOnly`/`TimeOnly` materializes correctly. -- @returns scalar: DateTimeOffset cast the boxed result directly, but a timestamptz is boxed as a DateTime, so (DateTimeOffset)__scalar threw; the scalar path now routes non-IConvertible types through the same width-tolerant converter as the row/aggregate reads.
  • Two `[Table]` classes with the same simple name in different namespaces no longer crash the generator. They produced identical generated-file hint names (opaque "hintName already added", nothing emitted); hint names are now namespace-qualified, so 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 for a [Table] class Foo<T> or a nested [Table]; these now report SCGDB025 and skip codegen.
  • A `[TableType]` without a primary key no longer warns. SCGDB016 (no primary key) fired on a pure [TableType] (a runtime-named projection shape that may legitimately have no key); it is now scoped to [Table] types, whose update/delete-by-key operations 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 converted form (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 as the insert and SET paths (for ==, !=, and the relational operators), including the nullable-!= OR col IS NULL form. Such a predicate is excluded from the query-shape cache (the replay path rebinds from the source and would skip the convertor), and a table with no convertor columns translates exactly as before. Verified against a live database.
  • A `[FlaggedEnum]` junction back to a composite-key table generates one multi-column foreign key. The junction 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 backed by ushort/uint/ulong is stored widened (integer/bigint/numeric); 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 (boxed by Npgsql 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<ushort>/<uint>/<ulong>, 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 new name — gone 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 columns in PascalCase (the property name) while the column 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 JsonNamingPolicy.SnakeCaseLower) stripped a leading underscore and collapsed a double underscore (_Leading to leading, a__b to 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_<table>_<column>, 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 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-to-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<MyEnum> into the enum-table lookup, which looked for [Table] on Nullable<T> (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 reflection context, so they were silently dropped and the FK was auto-resolved to the target 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 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 ... 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<MyFlags> 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 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_<column>; a column whose name pushed that alias past PostgreSQL 63-byte identifier limit was truncated in the result label but not in the reader 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 replaced every non-alphanumeric character of the DTO 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<DateTimeOffset>(...) 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.