# Kadi-v2 Platform Conventions — QAF-Differences Feature Reference

> Written: 2026-06-25
> Source: read-only analysis of /root/projekte/Kadi-v2
> Purpose: blueprint for adding a new top-level navigation page "QAF-Differences" with new DB tables

---

## 1. Navigation / AppShell

### Where nav items live

All navigation is defined in one file:

**`/root/projekte/Kadi-v2/components/layout/app-shell.tsx`** (client component, `'use client'`)

There are three arrays of `NavItem` objects:

```ts
interface NavItem {
  href: string
  label: string
  icon: React.ElementType   // from lucide-react
  matchPrefixes: string[]
}
```

#### BASE_ITEMS (lines 56–67)
All items rendered unconditionally for any authenticated user:
```ts
const BASE_ITEMS: NavItem[] = [
  { href: '/intake/board',    label: t('nav.intake'),        icon: Inbox,       matchPrefixes: ['/intake'] },
  { href: '/projektanlage',   label: t('nav.projektanlage'), icon: FolderKanban, matchPrefixes: ['/projektanlage', '/project'] },
  { href: '/kalender',        label: t('nav.kalender'),      icon: CalendarDays, matchPrefixes: ['/kalender'] },
  { href: '/lsc-workshop',    label: t('nav.lscWorkshop'),   icon: Wrench,       matchPrefixes: ['/lsc-workshop'] },
  { href: '/fabrikanalyse',   label: t('nav.fabrikanalyse'), icon: ClipboardCheck, matchPrefixes: ['/fabrikanalyse'] },
  { href: '/oee',             label: t('nav.oeeAnalyse'),    icon: Gauge,        matchPrefixes: ['/oee'] },
  { href: '/wertstrom',       label: t('nav.wertstrom'),     icon: GitBranch,    matchPrefixes: ['/wertstrom'] },
  { href: '/repository',      label: t('nav.datenablage'),   icon: FolderArchive, matchPrefixes: ['/repository'] },
  { href: '/pmo',             label: t('nav.pmo'),           icon: Briefcase,    matchPrefixes: ['/pmo'] },
  { href: '/notes',           label: t('nav.notizen'),       icon: NotebookPen,  matchPrefixes: ['/notes'] },
]
```

#### REPORTING_ITEM (lines 74–75)
Role-gated (consultant and above), rendered separately:
```ts
const REPORTING_ITEM: NavItem = { href: '/reporting', label: t('nav.reporting'), icon: BarChart3, matchPrefixes: ['/reporting'] }
// Shown only when: session && isAtLeastRole(session, 'consultant')
```

#### ADMIN_SUB_ITEMS (lines 77–82)
Admin-only collapsible section (users, views, audit, emails).

### Role-gating mechanism

The `session` object comes from `useUserSession()` (client hook reading session context).
Gate expressions used in the file:
- `isAtLeastRole(session, 'consultant')` — for reporting
- `session ? isAtLeastRole(session, 'admin') : false` — for admin section
- `hasPermission(session, 'audit.read')` — for the audit-log sub-item

Imports: `{ isAtLeastRole, hasPermission } from '@/lib/auth/permissions-shared'`

### How to add a QAF-Differences nav item

**Step 1 — Add to BASE_ITEMS (or create a role-gated block like reporting)**

For an unrestricted nav item, add to `BASE_ITEMS` array (around line 67):
```ts
{ href: '/qaf-differences', label: t('nav.qafDifferences'), icon: GitCompare, matchPrefixes: ['/qaf-differences'] },
```
Import the icon: `import { ..., GitCompare } from 'lucide-react'` (or choose any fitting icon).

For a consultant-only gate (same pattern as reporting), add a separate block in the JSX at line ~135:
```tsx
{session && isAtLeastRole(session, 'consultant') && (
  <Link href="/qaf-differences" ... className={linkClass(isActive(['/qaf-differences']))}>
    <GitCompare size={17} className="shrink-0"/>
    {!sidebarCollapsed && t('nav.qafDifferences')}
  </Link>
)}
```
And mirror in the `DRAWER_ITEMS` array for mobile (line ~93) or `MOBILE_TABS`.

**Step 2 — Add translation key**

`/root/projekte/Kadi-v2/lib/i18n/translations/de.json` — add inside `"nav"`:
```json
"qafDifferences": "QAF Vergleiche"
```
Mirror in `en.json`, `es.json`, `zh.json`.

**Step 3 — Active-state matching**

The `isActive(matchPrefixes)` function matches by `pathname === p || pathname.startsWith(p + '/')`.
Always include the top-level path in `matchPrefixes`: `['/qaf-differences']`.

---

## 2. Page + Auth Pattern

### Standard server-component auth guard

**Two variants exist in the codebase:**

#### Variant A — via `getUserSession()` (full session with role + permissions)
Used by `app/reporting/page.tsx`, `app/fabrikanalyse/page.tsx` (indirectly):
```ts
// At top of page.tsx
import { redirect } from 'next/navigation'
import { createClient } from '@/lib/supabase/server'
import { getUserSession, isAtLeastRole } from '@/lib/auth/permissions'

export default async function QafDifferencesPage() {
  const session = await getUserSession()
  if (!session) redirect('/login')
  // Optional role gate:
  if (!isAtLeastRole(session, 'consultant')) redirect('/projektanlage')

  const supabase = await createClient()
  // ... data fetching ...
}
```

#### Variant B — via `supabase.auth.getClaims()` (lighter, no role check)
Used by `app/fabrikanalyse/page.tsx`, `app/notes/page.tsx`:
```ts
const supabase = await createClient()
const { data: claimsData } = await supabase.auth.getClaims()
if (!claimsData?.claims) redirect('/login')
const userId = claimsData.claims.sub
```

**`getUserSession()`** also calls `createClient()` internally, so it is the preferred approach when you need role/permission checks. The lighter `getClaims()` variant is used when only the `user_id` is needed.

### Layout-level auth guard

Each route can have a `layout.tsx` that runs the auth guard before the page renders:
```ts
// app/qaf-differences/layout.tsx
import { redirect } from 'next/navigation'
import { createClient } from '@/lib/supabase/server'
import AppShell from '@/components/layout/app-shell'

export default async function SegmentLayout({ children }: { children: React.ReactNode }) {
  const supabase = await createClient()
  const { data: claimsData } = await supabase.auth.getClaims()
  if (!claimsData?.claims) redirect('/login')
  return <AppShell>{children}</AppShell>
}
```
See: `/root/projekte/Kadi-v2/app/reporting/layout.tsx` — this is the exact pattern.

### Server-side data fetch → client component handoff

The codebase rule (CLAUDE.md): **push data fetching UP (server), interactivity DOWN (client)**.

```ts
// app/qaf-differences/page.tsx (server component)
export default async function QafDifferencesPage() {
  const session = await getUserSession()
  if (!session) redirect('/login')

  const supabase = await createClient()

  const { data: qafDiffs, error } = await supabase
    .from('qaf_differences')
    .select('id, project_id, field_name, old_value, new_value, diff_type, created_at')
    .order('created_at', { ascending: false })
    .limit(100)

  if (error) logger.error('qaf_differences.fetch_failed', error)

  return (
    <>
      <AppHeader title="QAF Vergleiche" backHref="/projektanlage" />
      <main className="flex-1 bg-background px-6 py-6 overflow-y-auto">
        <QafDifferencesClient rows={qafDiffs ?? []} />
      </main>
    </>
  )
}
```

**Never expose the Supabase client to client components** (CLAUDE.md rule). Pass data as props only.

---

## 3. Supabase Access Layers

### Client overview

| File | Import alias | Role | When to use |
|------|-------------|------|-------------|
| `lib/supabase/client.ts` | `createClient` | `anon` / authenticated (browser) | Client components, client-side actions |
| `lib/supabase/server.ts` | `createClient` | authenticated (cookie-backed) | Server components, route handlers, server actions |
| `lib/supabase/admin.ts` | `createAdminClient` | service-role (bypasses RLS) | Admin routes, email queue, audit log writes |
| `lib/supabase/privileged-env.ts` | — | env validation only | Called inside `createAdminClient` |

**Key env vars:**
- `NEXT_PUBLIC_SUPABASE_URL` + `NEXT_PUBLIC_SUPABASE_PUBLISHABLE_KEY` — browser/server (anon key)
- `SUPABASE_SERVICE_ROLE_KEY` — service-role; read only via `privileged-env.ts`

### Service-role intent register rule

Documented in `/root/projekte/Kadi-v2/docs/foundation/service-role-intent-register.md`.

**The rule:** Every call site of `createAdminClient()` must be catalogued in that register with:
- the file path
- the privileged DB operation performed
- the business purpose
- why normal user scope is insufficient
- Azure replacement recommendation
- audit requirement
- risk rating (Critical / High / Medium / Low)

**For QAF-Differences:** If the page only reads data owned by the user (via RLS), use `createClient()` (server variant). Only use `createAdminClient()` if cross-user reads are needed (e.g. admin viewing all users' diffs), and if so, add a row to the intent register.

`npm run check:secrets` enforces that `SUPABASE_SERVICE_ROLE_KEY` is only read in `privileged-env.ts`. Any new code that tries to read it directly will be blocked.

---

## 4. RLS Conventions

### Access model: single-tenant, user ownership + admin override

The app is **single-tenant** (multi-tenant removed 2026-05-26). There are no `tenant_id` columns on data tables.

Ownership chain: `auth.users` → `user_profiles` (via `auth_user_id`) → `projects` (via `user_id = auth.uid()`) → child tables (via `project_id`).

### Three RLS patterns in use

**Pattern 1 — Direct ownership (top-level tables)**
Used on `projects`:
```sql
CREATE POLICY "Users see own projects" ON projects
  FOR ALL USING (auth.uid() = user_id);
```

**Pattern 2 — Project-chain ownership (child tables, established KADi-Norm)**
Used on `workshop_actions`, `qaf_uploads`, `process_steps`, `cycle_measurements`:
```sql
-- Named convention: <table>_own
CREATE POLICY "workshop_actions_own" ON workshop_actions
  FOR ALL TO authenticated
  USING      (project_id IN (SELECT id FROM projects WHERE user_id = auth.uid()))
  WITH CHECK (project_id IN (SELECT id FROM projects WHERE user_id = auth.uid()));
```

**Pattern 3 — Owner-OR-admin dual policy (hardened tables, post-2026-04 RLS audit)**
Used on `project_suppliers`, `project_type_assignments`, `documents`:
```sql
-- Policy 1: owner via project chain
CREATE POLICY "<table>_own" ON <table>
  FOR ALL TO authenticated
  USING      (project_id IN (SELECT id FROM projects WHERE user_id = auth.uid()))
  WITH CHECK (project_id IN (SELECT id FROM projects WHERE user_id = auth.uid()));

-- Policy 2: admin override via SECURITY DEFINER helper
CREATE POLICY "<table>_admin" ON <table>
  FOR ALL TO authenticated
  USING      (current_user_role() = ANY (ARRAY['admin'::text, 'masteradmin'::text]))
  WITH CHECK (current_user_role() = ANY (ARRAY['admin'::text, 'masteradmin'::text]));
```

`current_user_role()` is a `SECURITY DEFINER` SQL function defined in `MO-26/user-permissions.sql`:
```sql
CREATE OR REPLACE FUNCTION public.current_user_role()
RETURNS text LANGUAGE sql SECURITY DEFINER STABLE AS $$
  SELECT r.code
  FROM public.user_profiles up
  JOIN public.roles r ON r.id = up.role_id
  WHERE up.auth_user_id = auth.uid()
    AND up.deleted_at IS NULL
  LIMIT 1
$$;
```

### Demo shared-read pattern

Commit `07e2e5e` (feat/demo-shared-read-rls): tables with `is_demo = true` rows allow SELECT for any authenticated user, but writes remain owner-scoped. This is used in `fabrikanalyse/page.tsx` — demo projects owned by another user are shown in a separate "other" list. The RLS policy allows `SELECT USING (true)` for demo-flagged rows; or the page fetches all rows via the anon/user client and filters in application code (the current pattern).

### Standard RLS policy template for a new QAF-related table

```sql
-- ============================================================
-- RLS for qaf_differences (child of projects, per KADi-Norm)
-- ============================================================

BEGIN;

ALTER TABLE public.qaf_differences ENABLE ROW LEVEL SECURITY;

-- Policy 1: project-owner CRUD
CREATE POLICY "qaf_differences_own"
  ON public.qaf_differences
  FOR ALL
  TO authenticated
  USING (
    project_id IN (SELECT id FROM public.projects WHERE user_id = auth.uid())
  )
  WITH CHECK (
    project_id IN (SELECT id FROM public.projects WHERE user_id = auth.uid())
  );

-- Policy 2: admin/masteradmin override
CREATE POLICY "qaf_differences_admin"
  ON public.qaf_differences
  FOR ALL
  TO authenticated
  USING      (public.current_user_role() = ANY (ARRAY['admin'::text, 'masteradmin'::text]))
  WITH CHECK (public.current_user_role() = ANY (ARRAY['admin'::text, 'masteradmin'::text]));

COMMIT;
```

If the table has a standalone `user_id` column (not project-child), use direct ownership:
```sql
CREATE POLICY "qaf_differences_own"
  ON public.qaf_differences
  FOR ALL TO authenticated
  USING      (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());
```

---

## 5. Schema Source of Truth + Migration Convention

### Source of truth

- **`supabase/bootstrap/supabase-schema.sql`** — original schema; old tables are created here with `CREATE TABLE IF NOT EXISTS`
- **`supabase/bootstrap/supabase-bootstrap-from-prod.sql`** — recommended one-shot bootstrap (pg_dump snapshot); supersedes incremental history for new DB setups
- **`MIGRATIONS.md`** — catalog of every SQL file, apply order, class, RLS effect, notes

### Migration file naming convention

```
supabase/migrations/supabase-migration-<feature-slug>.sql
supabase/migrations/supabase-migration-<feature-slug>-rollback.sql
```

For RLS-only files:
```
supabase/rls/supabase-rls-<table>-enable.sql
supabase/rls/supabase-rls-closure-<table>-ownership.sql
```

### Migration file structure

```sql
-- ============================================================================
-- <Ticket> — <Short description>
-- ============================================================================
-- Context:   <Why this migration is needed>
-- Rollback:  supabase-migration-<feature>-rollback.sql
-- ============================================================================

BEGIN;

-- actual DDL / DML / POLICY statements

COMMIT;
```

Rules extracted from codebase examples:
1. **Always wrapped in `BEGIN; ... COMMIT;`** (transactional; rollback on error)
2. **Idempotent** — use `CREATE TABLE IF NOT EXISTS`, `ALTER TABLE ... ADD COLUMN IF NOT EXISTS`, `DROP POLICY IF EXISTS`, `CREATE OR REPLACE FUNCTION`
3. **Rollback file** always exists as a sibling with `-rollback` suffix. The rollback SQL is either a separate file or commented out inside a block like `/* ... */` at the end
4. **`ENABLE ROW LEVEL SECURITY`** is always its own step, often in a dedicated enable-only migration
5. **`DROP POLICY IF EXISTS`** before `CREATE POLICY` (idempotent policy replacement)
6. **Verification snapshot** at end (SELECT from pg_policies) — optional but present in closure scripts
7. **MIGRATIONS.md must be updated** after each new migration file: add a row with #, file, class, depends-on, RLS effect, notes

### MIGRATIONS.md catalog update

When adding a new migration for QAF-Differences, append a row to the appropriate section:

| # | File | Class | Depends on | RLS effect | Notes |
|---|------|-------|-----------|-----------|-------|
| N | `supabase/migrations/supabase-migration-qaf-differences.sql` | module-schema | 1 (supabase-schema.sql) | enables | Adds qaf_differences + qaf_diff_fields tables. RLS: project-ownership chain + admin override. |

---

## 6. Upload / Storage Pattern

### Buckets used

| Bucket name | Used by | Access |
|-------------|---------|--------|
| `documents` | Repository (project docs, templates), Notes PDF export | Signed URLs (60s for download, 3600s for display) |
| `note-attachments` (or similar) | `app/notes/actions.ts` — image attachments on notes | Signed URLs (3600s) |
| `avatars` | `lib/avatar/avatar-url.ts`, `components/account/account-form.tsx` | Signed URLs |

### Upload pattern (client component, `documents` bucket)

From `components/repository/upload-dialog.tsx`:

```ts
// 1. Build a deterministic storage key
const uniqueId = crypto.randomUUID()
const prefix = projectId ?? 'global'
const safeFilename = sanitizeFilename(selectedFile.name)
const storageKey = `documents/${prefix}/${uniqueId}_${safeFilename}`

// 2. Upload to storage bucket
const { error: storageError } = await supabase.storage
  .from('documents')
  .upload(storageKey, selectedFile, {
    contentType: selectedFile.type || undefined,
    upsert: false,
  })

// 3. Insert metadata row (row RLS controls access)
await supabase.from('documents').insert({
  storage_key: storageKey,
  original_filename: safeFilename,
  project_id: projectId ?? null,
  uploaded_by: user?.id ?? null,
  // ...
})
```

**Rollback pattern (storage-DB atomicity):**
```ts
// If DB insert fails after storage upload, clean up orphaned object:
await supabase.storage.from(ATTACHMENT_BUCKET).remove([key])
```
See `app/notes/actions.ts` lines 118–121.

### Download pattern (signed URL)

```ts
// Client component — short-lived signed URL (60s)
const { data } = await supabase.storage.from('documents').createSignedUrl(storageKey, 60)
if (data?.signedUrl) window.open(data.signedUrl)

// Server component — longer-lived signed URL (3600s for display)
const { data } = await supabase.storage.from(ATTACHMENT_BUCKET).createSignedUrls(keys, 3600)
```

### For QAF-Differences specifically

If QAF files need to be stored (the existing `qaf_uploads` table already exists in the schema with `file_path TEXT`), the existing `documents` bucket and `qaf_uploads` table should be reused. The `qaf_uploads` table has RLS via `qaf_uploads_own` (project-chain ownership). New "difference" data (computed diffs between QAF versions) should live in a new `qaf_differences` table referencing `project_id`, not as file storage.

---

## Summary: Exact Code Changes for QAF-Differences

### (a) Adding a nav item

**File:** `/root/projekte/Kadi-v2/components/layout/app-shell.tsx`

Add to `BASE_ITEMS` array (after line 66, before closing `]`):
```ts
{ href: '/qaf-differences', label: t('nav.qafDifferences'), icon: FileDiff, matchPrefixes: ['/qaf-differences'] },
```
Add `FileDiff` to the lucide-react import on line 6.

**File:** `/root/projekte/Kadi-v2/lib/i18n/translations/de.json`
```json
"nav": {
  ...
  "qafDifferences": "QAF Vergleiche"
}
```
Mirror same key in `en.json`, `es.json`, `zh.json`.

### (b) Auth-guard boilerplate (new `app/qaf-differences/layout.tsx`)

```ts
// tdd-guard:skip — layout composition file; no isolated unit test worthwhile
import { redirect } from 'next/navigation'
import { createClient } from '@/lib/supabase/server'
import AppShell from '@/components/layout/app-shell'

export default async function SegmentLayout({ children }: { children: React.ReactNode }) {
  const supabase = await createClient()
  const { data: claimsData } = await supabase.auth.getClaims()
  if (!claimsData?.claims) redirect('/login')
  return <AppShell>{children}</AppShell>
}
```

And `app/qaf-differences/page.tsx` (role-gated example):
```ts
import { redirect } from 'next/navigation'
import { getUserSession, isAtLeastRole } from '@/lib/auth/permissions'
import { createClient } from '@/lib/supabase/server'
import { logger } from '@/lib/logger'
import AppHeader from '@/components/layout/app-header'
import QafDifferencesClient from '@/components/qaf-differences/qaf-differences-client'

export default async function QafDifferencesPage() {
  const session = await getUserSession()
  if (!session) redirect('/login')
  // Remove this gate if the page should be open to all authenticated users:
  if (!isAtLeastRole(session, 'consultant')) redirect('/projektanlage')

  const supabase = await createClient()
  const { data: rows, error } = await supabase
    .from('qaf_differences')
    .select('id, project_id, field_name, old_value, new_value, diff_type, created_at')
    .order('created_at', { ascending: false })
    .limit(200)

  if (error) logger.error('qaf_differences.fetch_failed', error)

  return (
    <>
      <AppHeader title="QAF Vergleiche" backHref="/projektanlage" />
      <main className="flex-1 bg-background px-6 py-6 overflow-y-auto">
        <QafDifferencesClient rows={rows ?? []} />
      </main>
    </>
  )
}
```

### (c) New-table RLS policy template (project-child, dual ownership+admin)

```sql
-- ============================================================================
-- QAF-Differences: schema + RLS
-- ============================================================================

BEGIN;

CREATE TABLE IF NOT EXISTS public.qaf_differences (
  id           uuid        DEFAULT gen_random_uuid() PRIMARY KEY,
  project_id   uuid        NOT NULL REFERENCES public.projects(id) ON DELETE CASCADE,
  field_name   text        NOT NULL,
  old_value    text,
  new_value    text,
  diff_type    text        CHECK (diff_type IN ('added', 'removed', 'changed')),
  created_at   timestamptz DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_qaf_differences_project ON public.qaf_differences(project_id);

ALTER TABLE public.qaf_differences ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "qaf_differences_own"  ON public.qaf_differences;
DROP POLICY IF EXISTS "qaf_differences_admin" ON public.qaf_differences;

-- Project-owner CRUD (KADi-Norm: _own pattern for 1:n project-child tables)
CREATE POLICY "qaf_differences_own"
  ON public.qaf_differences
  FOR ALL
  TO authenticated
  USING (
    project_id IN (SELECT id FROM public.projects WHERE user_id = auth.uid())
  )
  WITH CHECK (
    project_id IN (SELECT id FROM public.projects WHERE user_id = auth.uid())
  );

-- Admin override via SECURITY DEFINER helper (MO-26/user-permissions.sql)
CREATE POLICY "qaf_differences_admin"
  ON public.qaf_differences
  FOR ALL
  TO authenticated
  USING      (public.current_user_role() = ANY (ARRAY['admin'::text, 'masteradmin'::text]))
  WITH CHECK (public.current_user_role() = ANY (ARRAY['admin'::text, 'masteradmin'::text]));

-- Verification snapshot
SELECT policyname, cmd, permissive, roles
FROM pg_policies
WHERE schemaname = 'public' AND tablename = 'qaf_differences'
ORDER BY policyname;

COMMIT;
```

---

## Checklist before pushing a QAF-Differences PR

- [ ] Nav item added to `BASE_ITEMS` in `app-shell.tsx` (+ icon imported)
- [ ] Translation keys added to all 4 locale files (`de`, `en`, `es`, `zh`)
- [ ] `app/qaf-differences/layout.tsx` with AppShell + auth guard created
- [ ] `app/qaf-differences/page.tsx` with server auth guard + server data fetch created
- [ ] Client component `components/qaf-differences/qaf-differences-client.tsx` (receives data as props, `'use client'`)
- [ ] `app/qaf-differences/loading.tsx` created (matches pattern of other routes)
- [ ] Migration file `supabase/migrations/supabase-migration-qaf-differences.sql` written (BEGIN/COMMIT, idempotent, RLS enabled)
- [ ] Rollback file `supabase-migration-qaf-differences-rollback.sql` written
- [ ] `MIGRATIONS.md` updated with new catalog row
- [ ] `PRODUCT_SPEC.md` + `UI_FLOWS.md` updated (CLAUDE.md rule: iOS parity)
- [ ] `API_SPEC.md` updated if any new API route is added
- [ ] If `createAdminClient()` is used: add row to `docs/foundation/service-role-intent-register.md`
- [ ] `CHECK_FORBIDDEN_LEVEL=error npm run check:portability` passes
- [ ] `npm run build` passes locally (required per CLAUDE.md)
- [ ] **DO NOT apply migration to production** without operator approval; flag in PR that migration needs operator-applied SQL