Valtuutus.Data.Caching 1.6.0

• net10.0

  • Valtuutus.Core (>= 1.6.0)
  • Valtuutus.Data (>= 1.6.0)
  • ZiggyCreatures.FusionCache (>= 2.1.0)

• net11.0

  • Valtuutus.Core (>= 1.6.0)
  • Valtuutus.Data (>= 1.6.0)
  • ZiggyCreatures.FusionCache (>= 2.1.0)

• net8.0

  • Valtuutus.Core (>= 1.6.0)
  • Valtuutus.Data (>= 1.6.0)
  • ZiggyCreatures.FusionCache (>= 2.1.0)

• net9.0

  • Valtuutus.Core (>= 1.6.0)
  • Valtuutus.Data (>= 1.6.0)
  • ZiggyCreatures.FusionCache (>= 2.1.0)

This release adds opt-in V2 engines for both lookups, makes SQL Server's lookups and checks much cheaper by replacing table-valued parameters with JSON id lists, and fixes lookups that could grant what Check denies.

## Breaking changes

* **SQL Server 2016+ required** (#309). Id and name lists are passed as JSON read with , which needs database compatibility level 130+ (SQL Server 2016 or later, Azure SQL Database, Azure SQL Managed Instance). No migration: the  type stays in the database but is no longer used.
* ** lookup reads take a subject relation** (#315). , , , ,  and  take a non-null  ( = direct subject); a null no longer means anyrelation. Custom provider implementations must filter on it. The InMemory, Postgres and SQL Server providers are updated.
* **Lookup results can change** where they were wrong:
 * Lookups no longer return entities or subjects reached through a tuple whose subject relation the schema path doesn't name (#315). Example: with , a  tuple on  no longer grants 's members.
 * A terminal userset read through the joined fast path now spends a level of depth, as the tuple-to-userset path already did (#313 for LookupSubject, #314 for LookupEntity). Results change only at the exact depth limit; depth keeps counting schema hops, as documented.

## New: opt-in LookupSubject and LookupEntity V2 engines

Enable them with  and  (after , before ). The default engines are unchanged; V2 becomes the default in a later release.

* **Graph/step engines** (#304, #317). Each permission compiles once into a plan per (entity type, permission, subject type) (#310 for LookupSubject); a request binds its subject, depth, scope and context to the cached plans and runs every query a step waits on together. Results match the default engines: the shared suites compare V1 and V2 on InMemory, Postgres and SQL Server.
* **One round trip per step** on Postgres and SQL Server (#307, #320). A step's queries run as one , and a tokenless request resolves its snap token inside the first batch and pins it for the rest of the request.
* **Pooled per-request state** (#310, #318), so V2 allocates less than V1.
* **Fewer steps on recursive relations:**
 * LookupSubject reads several userset levels per query (#313).
 * LookupEntity walks a self-recursive relation such as  from the subject outward and stops at the first hop that finds nothing new, instead of unrolling to the request's depth (#322). It reads several hops per query (#323), the first one together with the direct grants (#324).
 * The number of levels per query is  on  /  (default ;  reads one level per step).
* **Extension points** for providers: , , ,  (Core), and ,  (Data.Db).  is a default-implemented member (), so existing option types keep compiling.

V1 → V2 on this release (dev cedf814), net10.0 BenchmarkDotNet, median of 3 interleaved rounds:

| Scenario | Postgres | SQL Server |
|---|---|---|
| LookupSubject | 69 → 53 µs (−23%) | 175 → 138 µs (−21%) |
| LookupSubject_Diamond | 69 → 52 µs (−24%) | 180 → 137 µs (−24%) |
| LookupSubject_SiblingBatch | 123 → 70 µs (−43%) | 203 → 179 µs (−12%) |
| LookupSubject_NestedGroup | 346 → 258 µs (−26%) | 605 → 377 µs (−38%) |
| LookupEntity | 389 → 373 µs (−4%) | 736 → 663 µs (−10%) |
| LookupEntity_SiblingBatch | 152 → 66 µs (−57%) | 199 → 167 µs (−16%) |
| LookupEntity_ConstrainedSiblingBatch | 131 → 70 µs (−47%) | 275 → 198 µs (−28%) |
| LookupEntity_NegateSiblingBatch | 129 → 48 µs (−63%) | 225 → 129 µs (−43%) |
| LookupEntity_Diamond | 66 → 61 µs (−7%) | 205 → 153 µs (−25%) |
| LookupEntity_NestedGroup | 1128 → 94 µs (−92%) | 1198 → 343 µs (−71%) |
| LookupEntity_Scoped | 502 → 532 µs (+6%) | 1017 → 1002 µs (tie) |

Allocations drop in every scenario (e.g. LookupEntity_NestedGroup 90 → 11 KB on Postgres) except LookupEntity and LookupEntity_Scoped on SQL Server (+5%). LookupEntity_Scoped on Postgres is the one scenario where V2 is slower: its first step's two scoped reads run one after the other in a batch, where V1 overlaps them on two connections.

## Performance (default engines too)

* **SQL Server id lists as JSON** (#309). Each  parameter cost ~0.5–1 ms per use;  costs ~50–80 µs. Check −7% to −39%, SubjectPermission −12% to −25%, LookupEntity −6% to −81%, LookupSubject −45% to −96%.
* **SQL Server cached plans that hold for every caller:**
 * The entity-ids relation reads no longer recompile on every execution (#308): LookupSubject_SiblingBatch −70%.
 * The joined lookup's inner rows seek by the listed ids (#309): LookupSubject_SiblingBatch another −88%.
 * Single-subject relation reads seek by subject (#325). A plan compiled for a sparse relation could read every tuple of a dense one later (up to ~14 ms per read); every compile order now costs under 40 µs.
* **Scoped lookups seek the scope per candidate** (#321), on both providers and both engines: LookupEntity_Scoped SQL Server 1680 → 951 µs (V1), 2331 → 981 µs (V2); Postgres V2 765 → 547 µs.
* **Postgres lookup relation reads seek under generic plans** (#312): a nested lookup on V2 went from ~14 ms to ~0.55 ms.

## Benchmarks

* New nested-userset scenarios for both lookups (#311, #316).

## Pull requests

* #304 feat: add LookupSubjectEngineV2 graph/step engine (opt-in)
* #307 perf(lookup): run LookupSubject V2 steps as one DbBatch on Postgres/SqlServer
* #308 perf(sqlserver): drop OPTION (RECOMPILE) from the entity-ids relation queries
* #309 perf(sqlserver): pass id lists as JSON read with OPENJSON
* #310 perf(lookup): compile LookupSubject V2 plans and pool its per-request state
* #311 bench(lookup): add nested userset LookupSubject scenario
* #312 perf(postgres): let lookup relation reads seek under generic plans
* #313 perf(lookup): read several userset levels per LookupSubject V2 query
* #314 fix(lookup): spend a level of depth on LookupEntity's joined userset path
* #315 fix(lookup): read only tuples whose subject relation the schema path names
* #316 bench(lookup): add nested userset LookupEntity scenario
* #317 feat(lookup): add LookupEntityEngineV2 graph/step engine (opt-in)
* #318 perf(lookup): pool LookupEntity V2 per-request state
* #320 perf(lookup): run LookupEntity V2 steps as one DbBatch on Postgres/SqlServer
* #321 perf(data): seek the scope probe per candidate in scoped relation reads
* #322 perf(lookup): stop LookupEntity V2's self-recursive walks when a hop finds nothing new
* #323 perf(lookup): walk LookupEntity V2's self-recursive relations several hops per query
* #324 perf(lookup): read LookupEntity V2's direct grants in the first levels query
* #325 perf(data): seek single-subject relation reads by subject on SqlServer