tessera-ctl/.planning/quick/260911-nke-mandantentrennung-etappe-3b-benutzerdime/260911-nke-VERIFICATION.md

---
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 `<verify>` 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*'<name>',\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 (`<slug>-ohne-benutzer-sieht-beide-nutzer-desselben-mandanten`, `<slug>-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, '<T>')` = 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 `<verify>` 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)_