--- phase: quick-260911-nke verified: 2026-09-11T15:55:00Z status: passed score: 13/13 must-haves verified covered_files: - .planning/quick/260911-nke-mandantentrennung-etappe-3b-benutzerdime/260911-nke-PLAN.md - .planning/quick/260911-nke-mandantentrennung-etappe-3b-benutzerdime/260911-nke-SUMMARY.md - apps/api/prisma/migrations/20260911120000_rls_user_dimension_personal_tables/migration.sql - apps/api/src/prisma/prisma-tenant.extension.ts - apps/api/src/prisma/prisma-tenant.extension.spec.ts - apps/api/src/groups/migration-sql.spec.ts - apps/api/scripts/rls-scratch-check.mjs - apps/api/src/tenders/tender-saved-search.service.ts - apps/api/src/tenders/tender-saved-search.service.spec.ts - apps/api/src/tenders/tender-email-config.service.ts - apps/api/src/tenders/tender-email-config.service.spec.ts - apps/api/src/tenders/tender-notification-pref.service.ts - apps/api/src/tenders/tender-notification-pref.service.spec.ts - apps/api/src/tenders/tender-rss-feed.service.ts - apps/api/src/tenders/tender-rss-feed.service.spec.ts - apps/api/src/tenders/tender-triage.service.ts - apps/api/src/tenders/tender-triage.service.spec.ts - apps/api/src/dashboard/dashboard.service.ts - apps/api/src/dashboard/dashboard.service.spec.ts - apps/api/src/calendar/calendar.service.ts - apps/api/src/calendar/calendar.service.spec.ts - apps/api/src/favorites/favorites.service.ts - apps/api/src/favorites/favorites.service.spec.ts - apps/api/src/tenders/tender-digest.scheduler.ts - docs/mandantentrennung-zugriffsklassifikation.md - docs/mandantentrennung-etappe2-fehlerrichtung.md - docs/mandantentrennung-datenbankrolle.md - docs/anleitung-entwicklung.md - docs/mandantentrennung-etappe3-auftrag.md - .planning/WINDOWS.md covered_digest: "v1:sha256:852ed4823d7b522129e2a3f7d343be2dcc14952203fb955d7c444972b9d7ca9f" behavior_unverified: 0 overrides_applied: 0 human_verification: - test: "T-NKE-02 / documented shortcut: revert exactly one of the 34 call sites (e.g. calendar.service.ts `addSource`) from three-arg back to two-arg, then run the full test suite and the per-file spec for that file." expected: "Human should observe whether the existing per-file `toHaveBeenCalledWith(prisma, tenantId, userId)` assertion (pinned to only ONE method per file, e.g. `getSources`) fails to catch a regression in a DIFFERENT method of the same file (e.g. `addSource`), and that no CI-wired test other than a manual re-run of the plan's own `` grep gate would catch it." why_human: "This is a repo-policy risk judgment (is the accepted, documented gap in WINDOWS #34 tolerable pending Etappe 4) rather than a pass/fail code check; the phase's own threat model already classifies it 'accept (mit Aufzeichnung)', so this item is reported for awareness, not because a truth failed." --- # Quick Task 260911-nke: Mandantentrennung Etappe 3b — Benutzerdimension Verification Report **Task Goal:** `current_user_id()`, `forTenant(prisma, tenantId, userId?)`, user predicate in `IS NULL OR` form on ten personal-data tables, 34 user-CRUD call sites pass the user, six hole-asserting checks each inverted into two, coherent record. **Verified:** 2026-09-11 (fresh, against the live container `tessera-ctl-db-1`, IP 172.19.0.2) **Status:** passed **Commits reviewed:** f0b531b, 07fc653, b62a905 (base 3e57d91 / 8829999) ## Summary of Independent Verification Every claim in the SUMMARY was re-measured directly against the live database and the working tree, not taken on trust. All measurements below were run fresh in this session. ### 1. `IS NULL OR` form on all ten tables Queried `pg_policies` live for all fourteen `userId`-bearing tables: - **Eight single-rule tables** (CalendarSource, DashboardLayout, FavoriteLink, TenderEmailConfig, TenderNotificationPref, TenderSavedSearch, TenderTriage, WidgetInstance): each carries exactly one `tenant_isolation_policy` with `qual = ("tenantId" = current_tenant_id()) AND ((current_user_id() IS NULL) OR ("userId" = current_user_id()))`. ✓ VERIFIED - **SearchProvider**: four command-separated rules confirmed — `tenant_user_read_policy` (SELECT, includes `"userId" IS NULL` for the shared row), `tenant_user_insert_policy`/`tenant_user_update_policy`/`tenant_user_delete_policy` (no shared-row exception). Tenant half is `"tenantId" = current_tenant_id()` unchanged (still tenant-strict, per jab's rebutted premise). ✓ VERIFIED - **TenderRssFeedSource**: four rules under the SAME names as 260910-jab (`tenant_platform_read_policy`, `tenant_insert_policy`, `tenant_update_policy`, `tenant_delete_policy`). Read policy retains `("tenantId" = current_tenant_id()) OR ("tenantId" IS NULL)` — the platform-wide read allowance is untouched; all three write policies keep the tenant-mandatory `"tenantId" = current_tenant_id()` half. ✓ VERIFIED - **Four exceptions** (GroupMembership, ModuleGrant, PasswordResetToken, TenderMatch): live `pg_policies` query confirms zero occurrences of `current_user_id()` in any of their rules — untouched as documented. ✓ VERIFIED ### 2. Both directions measured through the generated client Re-ran `apps/api/scripts/rls-scratch-check.mjs` fresh against the live container (own IP resolved this session): **`Alle 203 Pruefungen bestanden.`** — matches the SUMMARY's claim exactly. Spot-checked the named checks for two tables: - `tendersavedsearch-benutzer-a-sieht-eigene-zeile`: bestanden - `tendersavedsearch-benutzer-a-sieht-kollegen-nicht`: bestanden (user A cannot see `ss-a2`) - `tendersavedsearch-ohne-benutzer-sieht-beide`: bestanden (no-user call sees both) - `tendersavedsearch-schreiben-als-a-mit-kennung-b-abgelehnt`: bestanden (SQLSTATE 42501) - `calendarsource-benutzer-a-sieht-eigene-zeile` / `-benutzer-a-sieht-kollegen-nicht` (encryptedPassword of colleague returns `undefined`) / `-ohne-benutzer-sieht-beide` / `-schreiben-als-a-mit-kennung-b-abgelehnt`: all bestanden All four required directions are present for both spot-checked tables. ✓ VERIFIED ### 3. Six inversions became twelve Confirmed via anchored grep that none of the six old identifiers (`tendersavedsearch-fremder-nutzer-desselben-mandanten-sichtbar`, `dashboardlayout-…-gebunden-sichtbar`, `widgetinstance-…-gebunden-sichtbar`, `searchprovider-…-gebunden-sichtbar`, `calendarsource-…-gebunden-sichtbar`, `favoritelink-liste-generierter-client-gebunden-eigener-mandant-liefert-eigene-zeilen`) still appears as a live identifier (`grep -c "^\s*'',\s*$"` = 0 for all six), while each is still referenced in the tool's message text (1–3 references each) pointing to its replacement. All twelve new identifiers (`-ohne-benutzer-sieht-beide-nutzer-desselben-mandanten`, `-benutzer-a-sieht-kollegen-nicht-gebunden` for tendersavedsearch, dashboardlayout, widgetinstance, searchprovider, calendarsource, favoritelink) exist and passed in the fresh tool run. None deleted, none asserts the old defect (each old-form check reads the opposite semantics — "sees both" — under its new name, and is documented as the *intended* property for no-user calls). ✓ VERIFIED ### 4. 34 call sites, 8 files, three-arg Counted directly against the live tree with anchored regex `forTenant\(this\.prisma, (ctx\.)?tenantId, (ctx\.)?userId\)`: | File | Count | |---|---| | calendar.service.ts | 6 | | dashboard.service.ts | 9 | | favorites.service.ts | 5 | | tender-email-config.service.ts | 3 | | tender-notification-pref.service.ts | 2 | | tender-rss-feed.service.ts | 2 | | tender-saved-search.service.ts | 4 | | tender-triage.service.ts | 3 | | **Total** | **34** | Two-arg form (`forTenant(this.prisma, [a-zA-Z.]+)`) is **absent** in all 8 files (0 matches each). `tender-digest.scheduler.ts` still uses the two-arg form (1 occurrence, commented as intentional — background job, Etappe 3c). All admin/management call sites (ldap, groups, user, tenant, auth, dkv, module-registry, `rls-access-inventory.spec.ts` fixtures) remain two-arg, unaffected. `tender-rss-feed.service.ts`'s `createPlatform`/`remove` remain unbound as documented (WINDOWS #24). ✓ VERIFIED The gate "no two-arg form in the eight files" is real — it is the exact shell check re-run above, not paraphrased. ### 5. `forTenant()` extended, `$transaction` two entries, `NULLIF('')` yields NULL — falsified - Read the function body directly: `forTenant(prisma, tenantId, userId?)` builds ONE tagged `$executeRaw` with both `set_config` calls, then `$transaction([setContext, query(args)])` — exactly two array entries, matching WINDOWS #20's required pattern. No `forTenantAndUser` sibling helper exists (0 matches). ✓ VERIFIED - Live psql: `current_user_id()` unset → NULL, set to `''` → NULL (via `NULLIF`), set to `'user-a1'` → `'user-a1'`. All three cases measured directly against the live database this session (not assumed). ✓ VERIFIED - **Falsification performed:** temporarily edited the migration file to strip `NULLIF(..., '')` down to a bare `current_setting(...)`, re-ran the scratch tool fresh — result: `current-user-id-leer-ist-null: FEHLGESCHLAGEN` plus 32 cascading failures (`33 von 203 Pruefungen fehlgeschlagen`), confirming the named check goes red exactly as expected. Restored the file from a pre-edit backup; re-ran the tool again — back to `Alle 203 Pruefungen bestanden.` Working tree confirmed clean after restore (`git diff` empty on the migration file). ✓ VERIFIED ### 6. 13 extraction redirects, `regelstand-eindeutig` gates Confirmed zero remaining calls of the old-source form for all ten tables: `extractPolicySql(remainingMigrationSql, '')` = 0 for all eight single-rule tables; `extractAllPolicySql(widenMigrationSql, 'TenderRssFeedSource')` = 0; old-source `extractPolicySql(*, 'SearchProvider')` = 0. All extraction call sites for the ten tables now read from `readRlsUserDimensionMigrationSql()` (13 distinct extraction sites counted, matching the plan's own count). Both `regelstand-eindeutig` gates (`calendarsource-regelstand-eindeutig`, `favoritelink-regelstand-eindeutig`) were read in full: each now compares "does widen have its own rule" AND "does the new user-dimension migration have its own rule", aborting/failing if either check is ambiguous — a stale extraction (reverted to the widen or remaining-tenant-tables source) would be caught by this gate as currently written. `smtpconfig-regelstand-eindeutig` untouched, out of scope. ✓ VERIFIED ### 7. Inertness with the switch OFF Live query: `SELECT rolname, rolbypassrls FROM pg_roles WHERE rolname = 'tessera';` → `tessera | t` — the application role has BYPASSRLS, so none of the new policies apply to it regardless of what `set_config('app.current_user', ...)` receives. `git diff --name-only 8829999` contains no `docker-compose*.yml` and no `.env*` file (checked by pattern match without reading secret contents). `schema.prisma` diff against 8829999 is empty. The change is exactly what the SUMMARY claims: the application now additionally sends a session variable that the currently-active role (BYPASSRLS) ignores. ✓ VERIFIED ### 8. The documented shortcut — per-file, not per-method, three-arg assertions Confirmed the shortcut is real and exactly as characterized in the SUMMARY: `calendar.service.ts` has 6 three-arg call sites but only ONE `toHaveBeenCalledWith(prisma, ..., 'user-a1')`-style assertion in its spec file (for a single method), not six. **Judgment on coverage (per the orchestrator's explicit ask):** the "no two-arg form" grep gate is a one-time shell check executed during plan verification — it is **not** wired into the CI/test suite (`npm test` does not re-run it), and `rls-access-inventory.spec.ts`'s detector only classifies tenant-bound vs. tenant-unbound, not user-bound vs. user-unbound (confirmed by reading the plan's own context notes and the detector regex). The `rls-scratch-check.mjs` tool measures the deployed SQL policy directly via a throwaway schema-identical table and the generated client — it does **not** invoke the application's service methods, so it cannot observe whether a given service method passes `userId` to `forTenant()`. Consequently: **a method other than the one pinned by the single per-file assertion COULD silently regress from three-arg to two-arg without any automated test noticing** — only a manual re-run of the exact grep gate from this phase's `` block would catch it. This is not a concealed gap: it is exactly the risk the phase's own threat model records as `T-NKE-02: accept (mit Aufzeichnung)` and the exact wording of the new WINDOWS #34 entry ("ein Waechter... ist NICHT gebaut"). Routed to human verification below for awareness, not because any must-have failed — the phase never claimed to build that guard; it explicitly deferred the decision to Etappe 4. ### 9. Record coherence - `docs/mandantentrennung-etappe2-fehlerrichtung.md`: new section `## Regelschluss Benutzerdimension (Etappe 3b, 260911-nke)` with all five subsections `(b1)`–`(b5)` present (confirmed by line-anchored grep). 10 dated `Nachtrag (260911-nke)` entries found (plan required ≥9). ✓ - `docs/mandantentrennung-zugriffsklassifikation.md`: 3 inventory rows (calendarSource, widgetInstance, favoriteLink) carry `Benutzerdimension seit 20260911120000 (260911-nke)`; `Aufgelöst (260911-nke)` marker present; new `**Stand 260911-nke:**` paragraph present, explicitly stating the pair count (72) and class distribution are unchanged. ✓ - `docs/anleitung-entwicklung.md`: old half-sentence "keine Benutzerdimension kennt" replaced (0 remaining occurrences); `current_user_id` mentioned. ✓ - `docs/mandantentrennung-datenbankrolle.md`: new paragraph on `app.current_user`/`current_user_id()`; the three SECURITY-DEFINER header comments are untouched (`git diff 8829999` shows no deletion of `auth_lookup_user_by_username|auth_lookup_user_by_email|auth_lookup_reset_token`). ✓ - `docs/mandantentrennung-etappe3-auftrag.md`: `**Erledigt (260911-nke, f0b531b/07fc653...)**` sentence present. ✓ - `.planning/WINDOWS.md`: entry #34 present in both the markdown table and the JSON block, `status: open`, `phase: quick-260911-nke`, text matches the risk described in item 8 above. ✓ - **Allow-list / scope**: `git diff --name-only 8829999` lists exactly the migration, the helper + its spec, the migration-sql spec, the scratch tool, the 8 service files + 8 specs, the scheduler, the 5 docs, and WINDOWS.md — plus `.planning/STATE.md` and this phase's own `PLAN.md`/`SUMMARY.md` (workflow bookkeeping, expected). No `schema.prisma`, no compose file, no `.env*`, no controller file, no login/auth function (`auth.service.ts` absent from the diff). ✓ VERIFIED ## Additional Independent Checks - **Tests:** fresh run this session — `Test Files 62 passed (62)`, `Tests 1020 passed (1020)`. Matches SUMMARY exactly. - **Type-check:** `tsc --noEmit` — no errors. - **Debt markers:** no `TBD`/`FIXME`/`XXX`/`TODO`/`HACK`/`PLACEHOLDER` found in any file touched by this phase (grepped every modified file under `apps/api/src`, `apps/api/scripts`, `apps/api/prisma`). - **Push state:** `git log origin/main..HEAD` is empty; `git log --oneline` shows f0b531b/07fc653/b62a905 directly on top of 8829999/3e57d91, matching the SUMMARY's commit hashes exactly. ## Observable Truths | # | Truth | Status | Evidence | |---|-------|--------|----------| | 1 | `current_user_id()` exists, NULLIF-folds empty string to NULL, all three cases measured live | ✓ VERIFIED | Live psql: unset/empty → NULL, set → value; falsification of NULLIF confirmed | | 2 | `forTenant(prisma, tenantId, userId?)` — optional third param, no sibling helper, detector regex still matches | ✓ VERIFIED | Function body read; `forTenantAndUser` absent; three-arg calls match `const X = forTenant(` pattern | | 3 | Both `set_config` in ONE tagged statement, `$transaction` still exactly two entries, empty string not omission | ✓ VERIFIED | Function body: `$transaction([setContext, query(args)])`, `userId ?? ''` | | 4 | Ten tables carry `IS NULL OR` form | ✓ VERIFIED | Live `pg_policies` for all ten | | 5 | Four exceptions unchanged, no `current_user_id()` reference | ✓ VERIFIED | Live `pg_policies` for GroupMembership/ModuleGrant/PasswordResetToken/TenderMatch | | 6 | SearchProvider/TenderRssFeedSource: 4 command-separated rules, tenant half unchanged | ✓ VERIFIED | Live `pg_policies`, per-command qual/with_check inspected | | 7 | 34 call sites in 8 files, three-arg; scheduler/admin paths stay two-arg | ✓ VERIFIED | Anchored grep counts match 6/9/5/3/2/2/4/3=34; two-arg gate is 0 in all 8 files | | 8 | No app-side ownership check removed | ✓ VERIFIED | `provider.userId !== userId` / `where: { userId }` style checks still present in reviewed files (spot-checked favorites.service.ts, calendar.service.ts headers) | | 9 | Scratch tool measures all ten tables through the generated client, cut (not typed) from the new migration | ✓ VERIFIED | 203/203 fresh run; 13 extraction sites read from `readRlsUserDimensionMigrationSql()`; 0 stale-source calls | | 10 | Six hole-asserting checks inverted into twelve, no old identifier survives, no old defect re-asserted | ✓ VERIFIED | Anchored grep: 0 old identifiers as keys; 12 new identifiers present and passing | | 11 | Documentation coherence — 10 Nachtraege, Regelschluss b1–b5, Ledger #34, "Erledigt" note | ✓ VERIFIED | Grepped every claimed location | | 12 | Switch stays OFF, inert under BYPASSRLS, no schema/compose/env change | ✓ VERIFIED | `rolbypassrls=t`; diff excludes schema.prisma/compose/.env | | 13 | Three commits, pushed, clean working tree (phase scope) | ✓ VERIFIED | commits match; `git log origin/main..HEAD` empty | **Score:** 13/13 truths verified ## Human Verification Required 1 item — see frontmatter `human_verification` block above (item 8's coverage judgment). This is an awareness item tied to an already-accepted, already-documented risk (WINDOWS #34, threat T-NKE-02), not a failing truth — it does not change the `passed` status but is worth a human glance before Etappe 4 (Scharfschalten) is decided. ## Gaps Summary None. Every must-have from the plan's frontmatter and every point in the orchestrator's nine-item verification list was independently re-measured against the live database and working tree and confirmed. The one item flagged above is an explicitly pre-accepted and pre-documented residual risk (not a gap introduced or concealed by this phase), surfaced here only because the phase itself flags it as something to revisit before Etappe 4. --- _Verified: 2026-09-11_ _Verifier: Claude (gsd-verifier)_