Scrum4Me/docs/architecture/data-model.md

---
title: "Data Model & Prisma Schema"
status: active
audience: [maintainer, contributor]
language: nl
last_updated: 2026-05-03
related: [auth-and-sessions.md](./auth-and-sessions.md)
---

## Datamodel

### `users`

| Kolom | Type | Constraints | Noten |
|---|---|---|---|
| id | String (cuid) | PK | Gegenereerd door Prisma |
| username | String | unique, not null, min 3 | Inlognaam |
| password_hash | String | not null | bcrypt hash (cost factor 12) |
| is_demo | Boolean | default false | Demo-gebruiker heeft read-only rechten |
| bio | String | nullable, max 160 | Korte profielomschrijving |
| bio_detail | String | nullable, max 2000 | Uitgebreide profielbeschrijving |
| avatar_data | Bytes | nullable | Profielfoto als WebP bytea (max 700×700) |
| created_at | DateTime | default now() | |
| updated_at | DateTime | auto-update | Gebruikt als cache-buster voor avatar-URL |

**Indexes:** `username` (unique lookup bij inloggen)

---

### `user_roles`

| Kolom | Type | Constraints | Noten |
|---|---|---|---|
| id | String (cuid) | PK | |
| user_id | String | FK → users, not null | |
| role | Enum | PRODUCT_OWNER \| SCRUM_MASTER \| DEVELOPER | |

**Indexes:** `(user_id)` — meerdere rollen per gebruiker
**Constraint:** unique `(user_id, role)`

---

### `api_tokens`

| Kolom | Type | Constraints | Noten |
|---|---|---|---|
| id | String (cuid) | PK | |
| user_id | String | FK → users, not null | |
| token_hash | String | not null | SHA-256 hash van het token |
| label | String | nullable | Bijv. "Claude Code — laptop" |
| created_at | DateTime | default now() | |
| revoked_at | DateTime | nullable | Null = actief |

**Indexes:** `token_hash` (lookup bij elke API-aanroep — moet snel zijn)

---

### `products`

| Kolom | Type | Constraints | Noten |
|---|---|---|---|
| id | String (cuid) | PK | |
| user_id | String | FK → users, not null | |
| name | String | not null, max 200 | Uniek per gebruiker |
| description | String | nullable, max 1000 | |
| repo_url | String | nullable | Gevalideerde URL |
| definition_of_done | String | not null, max 500 | Vaste instelling per product |
| archived | Boolean | default false | |
| created_at | DateTime | default now() | |
| updated_at | DateTime | auto-update | |

**Indexes:** `(user_id, archived)` — standaard query filtert op actieve producten
**Constraint:** unique `(user_id, name)`

---

### `pbis`

| Kolom | Type | Constraints | Noten |
|---|---|---|---|
| id | String (cuid) | PK | |
| product_id | String | FK → products (cascade delete) | |
| code | String | nullable, max 30 | Auto-gegenereerd of handmatig |
| title | String | not null, max 200 | |
| description | String | nullable, max 2000 | |
| priority | Int | 1–4, not null | 1 = Kritiek, 4 = Laag |
| sort_order | Float | not null | Float voor volgorde tussen items zonder renummering |
| status | Enum | READY \| BLOCKED \| DONE, default READY | Auto-promotie naar DONE bij sprint-close (zie hieronder) |
| created_at | DateTime | default now() | |
| updated_at | DateTime | auto-update | |

**Indexes:** `(product_id, priority, sort_order)` — standaard query voor het gesplitste scherm; `(product_id, status)` — voor het statusfilter op de Product Backlog

**Cascade-regel (sprint-close):** wanneer een Sprint wordt afgerond via `completeSprintAction` en alle stories van een PBI eindigen op DONE (na toepassing van de afsluitbeslissingen), zet diezelfde transactie de PBI-status op DONE. Promotie alléén — een PBI op DONE wordt nooit automatisch teruggezet. Stories die niet in deze Sprint zaten worden meegerekend op hun huidige DB-status. Een PBI zonder stories blijft READY.

---

### `stories`

| Kolom | Type | Constraints | Noten |
|---|---|---|---|
| id | String (cuid) | PK | |
| pbi_id | String | FK → pbis (cascade delete) | |
| product_id | String | FK → products | Denormalisatie voor snellere queries |
| sprint_id | String | FK → sprints, nullable | Null = in Product Backlog |
| title | String | not null, max 200 | |
| description | String | nullable, max 2000 | |
| acceptance_criteria | String | nullable, max 2000 | |
| priority | Int | 1–4, not null | |
| sort_order | Float | not null | |
| status | Enum | OPEN \| IN_SPRINT \| DONE | |
| created_at | DateTime | default now() | |
| updated_at | DateTime | auto-update | |

**Indexes:** `(pbi_id, priority, sort_order)`, `(sprint_id, sort_order)`, `(product_id, status)`

**Auto-promotie/demotie via task-status:** zodra alle tasks van een story op `DONE` staan en de huidige story-status nog niet `DONE` is, promoot dezelfde transactie de story naar `DONE`. Wordt een task van een `DONE`-story uit `DONE` getrokken (heropening), dan demoot de story terug naar `IN_SPRINT` — niet naar `OPEN`, want `OPEN` betekent "terug in productbacklog" en is een sprint-management-actie. De logica zit in [lib/tasks-status-update.ts](../../lib/tasks-status-update.ts) en wordt aangeroepen door alle drie de task-status-write-paden (`updateTaskStatusAction`, `saveTask` edit-mode, REST `PATCH /api/tasks/[id]`).

---

### `story_logs`

| Kolom | Type | Constraints | Noten |
|---|---|---|---|
| id | String (cuid) | PK | |
| story_id | String | FK → stories (cascade delete) | |
| type | Enum | IMPLEMENTATION_PLAN \| TEST_RESULT \| COMMIT | |
| content | String | not null | Tekst van plan of testuitvoer |
| status | Enum | PASSED \| FAILED, nullable | Alleen bij type TEST_RESULT |
| commit_hash | String | nullable | Alleen bij type COMMIT |
| commit_message | String | nullable | Alleen bij type COMMIT |
| created_at | DateTime | default now() | |

**Indexes:** `(story_id, created_at)` — chronologische weergave in de UI

---

### `sprints`

| Kolom | Type | Constraints | Noten |
|---|---|---|---|
| id | String (cuid) | PK | |
| product_id | String | FK → products (cascade delete) | |
| sprint_goal | String | not null, max 500 | |
| status | Enum | ACTIVE \| COMPLETED | |
| created_at | DateTime | default now() | |
| completed_at | DateTime | nullable | |

**Indexes:** `(product_id, status)` — query voor actieve Sprint per product
**Constraint:** Max. 1 actieve Sprint per product (gehandhaafd in applicatielaag)

---

### `tasks`

| Kolom | Type | Constraints | Noten |
|---|---|---|---|
| id | String (cuid) | PK | |
| story_id | String | FK → stories (cascade delete) | |
| sprint_id | String | FK → sprints, nullable | Denormalisatie voor snellere queries |
| title | String | not null, max 200 | |
| description | String | nullable, max 1000 | |
| implementation_plan | String | nullable | Opgeslagen door Claude Code MCP via `PATCH /api/tasks/:id` |
| priority | Int | 1–4, not null | |
| sort_order | Float | not null | |
| status | Enum | TO_DO \| IN_PROGRESS \| REVIEW \| DONE | |
| created_at | DateTime | default now() | |
| updated_at | DateTime | auto-update | |

**Indexes:** `(story_id, priority, sort_order)`, `(sprint_id, status)`

---

### `todos`

| Kolom | Type | Constraints | Noten |
|---|---|---|---|
| id | String (cuid) | PK | |
| user_id | String | FK → users, not null | |
| product_id | String? | FK → products, nullable | Optioneel in UI; SetNull bij verwijderen product |
| title | String | not null | |
| done | Boolean | default false | |
| archived | Boolean | default false | |
| created_at | DateTime | default now() | |
| updated_at | DateTime | auto-update | |

**Indexes:** `(user_id, done, archived)` — standaard weergave filtert op actieve todo's; `(user_id, product_id)` — filteren per product

---

### `product_members`

| Kolom | Type | Constraints | Noten |
|---|---|---|---|
| id | String (cuid) | PK | |
| product_id | String | FK → products (cascade delete) | |
| user_id | String | FK → users (cascade delete) | |
| created_at | DateTime | default now() | |

**Indexes:** `(user_id)` — opzoeken van producten waarbij een gebruiker lid is
**Constraint:** unique `(product_id, user_id)` — één lidmaatschap per gebruiker per product

Koppelt Developer-gebruikers aan een product backlog. De eigenaar (`products.user_id`) heeft altijd volledige toegang; via `product_members` kunnen aanvullende Developers leesrechten en schrijfrechten op stories, taken en sprints van dat product krijgen. Rollen worden niet opgeslagen in deze tabel — dat doet `user_roles`. Een gebruiker kan alleen worden toegevoegd als hij/zij de rol `DEVELOPER` heeft.

---

## Toegangsmodel en schrijfbeveiliging

Producttoegang is centraal gedefinieerd als:

- eigenaar: `products.user_id === gebruiker.id`
- teamlid: `product_members` bevat `(product_id, user_id)`

Code gebruikt hiervoor `productAccessFilter(userId)` uit `lib/product-access.ts`. Route Handlers en Server Actions mogen geen eigenaar-only filter (`user_id`) gebruiken voor product-scoped resources tenzij het expliciet om eigenaarsbeheer gaat, zoals archiveren of teamleden beheren.

Schrijfoperaties volgen deze invarianten:

- Controleer eerst authenticatie en `session.isDemo`.
- Valideer input met Zod, maar behandel TypeScript types niet als runtime-beveiliging.
- Controleer de parent-resource met `productAccessFilter`.
- Vertrouw bulk-ID's nooit los: haal de records eerst op met `id in (...)` plus de parent-scope (`product_id`, `pbi_id`, `sprint_id` of `story_id`) en weiger de operatie als aantallen niet exact overeenkomen.
- Weiger dubbele IDs in reorder- en beslissingslijsten.
- Leid denormalized foreign keys af van de database-parent (`pbi.product_id`, `sprint.product_id`) en niet van form-data of JSON body.
- Delete of update alleen nadat de resource scoped is gevonden; gebruik scoped `deleteMany`/`updateMany` wanneer een unique `delete` anders onveilig zou zijn.

---

## Prisma Schema (excerpt)

```prisma
// prisma/schema.prisma

generator client {
  provider = "prisma-client-js"
}

// Database wordt bepaald via prisma.config.ts — niet hier

enum Role {
  PRODUCT_OWNER
  SCRUM_MASTER
  DEVELOPER
}

enum StoryStatus {
  OPEN
  IN_SPRINT
  DONE
}

enum PbiStatus {
  READY
  BLOCKED
  DONE
}

enum TaskStatus {
  TO_DO
  IN_PROGRESS
  REVIEW
  DONE
}

enum LogType {
  IMPLEMENTATION_PLAN
  TEST_RESULT
  COMMIT
}

enum TestStatus {
  PASSED
  FAILED
}

enum SprintStatus {
  ACTIVE
  COMPLETED
}

model User {
  id              String          @id @default(cuid())
  username        String          @unique
  password_hash   String
  is_demo         Boolean         @default(false)
  bio             String?         @db.VarChar(160)
  bio_detail      String?         @db.VarChar(2000)
  avatar_data     Bytes?
  created_at      DateTime        @default(now())
  updated_at      DateTime        @updatedAt
  roles           UserRole[]
  api_tokens      ApiToken[]
  products        Product[]
  todos           Todo[]
  product_members ProductMember[]
}

model UserRole {
  id      String @id @default(cuid())
  user    User   @relation(fields: [user_id], references: [id], onDelete: Cascade)
  user_id String
  role    Role

  @@unique([user_id, role])
}

model ApiToken {
  id         String    @id @default(cuid())
  user       User      @relation(fields: [user_id], references: [id], onDelete: Cascade)
  user_id    String
  token_hash String    @unique
  label      String?
  created_at DateTime  @default(now())
  revoked_at DateTime?

  @@index([token_hash])
}

model Product {
  id                 String          @id @default(cuid())
  user               User            @relation(fields: [user_id], references: [id], onDelete: Cascade)
  user_id            String
  name               String
  description        String?
  repo_url           String?
  definition_of_done String
  archived           Boolean         @default(false)
  created_at         DateTime        @default(now())
  updated_at         DateTime        @updatedAt
  pbis               Pbi[]
  sprints            Sprint[]
  stories            Story[]
  todos              Todo[]
  members            ProductMember[]

  @@unique([user_id, name])
  @@index([user_id, archived])
}

model Pbi {
  id          String    @id @default(cuid())
  product     Product   @relation(fields: [product_id], references: [id], onDelete: Cascade)
  product_id  String
  code        String?   @db.VarChar(30)
  title       String
  description String?
  priority    Int
  sort_order  Float
  status      PbiStatus @default(READY)
  created_at  DateTime  @default(now())
  updated_at  DateTime  @updatedAt
  stories     Story[]

  @@unique([product_id, code])
  @@index([product_id, priority, sort_order])
  @@index([product_id, status])
}

model Story {
  id                   String      @id @default(cuid())
  pbi                  Pbi         @relation(fields: [pbi_id], references: [id], onDelete: Cascade)
  pbi_id               String
  product              Product     @relation(fields: [product_id], references: [id])
  product_id           String
  sprint               Sprint?     @relation(fields: [sprint_id], references: [id])
  sprint_id            String?
  title                String
  description          String?
  acceptance_criteria  String?
  priority             Int
  sort_order           Float
  status               StoryStatus @default(OPEN)
  created_at           DateTime    @default(now())
  updated_at           DateTime    @updatedAt
  logs                 StoryLog[]
  tasks                Task[]

  @@index([pbi_id, priority, sort_order])
  @@index([sprint_id, sort_order])
  @@index([product_id, status])
}

model StoryLog {
  id             String      @id @default(cuid())
  story          Story       @relation(fields: [story_id], references: [id], onDelete: Cascade)
  story_id       String
  type           LogType
  content        String
  status         TestStatus?
  commit_hash    String?
  commit_message String?
  created_at     DateTime    @default(now())

  @@index([story_id, created_at])
}

model Sprint {
  id           String       @id @default(cuid())
  product      Product      @relation(fields: [product_id], references: [id], onDelete: Cascade)
  product_id   String
  sprint_goal  String
  status       SprintStatus @default(ACTIVE)
  created_at   DateTime     @default(now())
  completed_at DateTime?
  stories      Story[]
  tasks        Task[]

  @@index([product_id, status])
}

model Task {
  id                  String     @id @default(cuid())
  story               Story      @relation(fields: [story_id], references: [id], onDelete: Cascade)
  story_id            String
  sprint              Sprint?    @relation(fields: [sprint_id], references: [id])
  sprint_id           String?
  title               String
  description         String?
  implementation_plan String?
  priority            Int
  sort_order          Float
  status              TaskStatus @default(TO_DO)
  created_at          DateTime   @default(now())
  updated_at          DateTime   @updatedAt

  @@index([story_id, priority, sort_order])
  @@index([sprint_id, status])
}

model Todo {
  id         String   @id @default(cuid())
  user       User     @relation(fields: [user_id], references: [id], onDelete: Cascade)
  user_id    String
  product    Product? @relation(fields: [product_id], references: [id], onDelete: SetNull)
  product_id String?
  title      String
  done       Boolean  @default(false)
  archived   Boolean  @default(false)
  created_at DateTime @default(now())
  updated_at DateTime @updatedAt

  @@index([user_id, done, archived])
  @@index([user_id, product_id])
}

model ProductMember {
  id         String   @id @default(cuid())
  product    Product  @relation(fields: [product_id], references: [id], onDelete: Cascade)
  product_id String
  user       User     @relation(fields: [user_id], references: [id], onDelete: Cascade)
  user_id    String
  created_at DateTime @default(now())

  @@unique([product_id, user_id])
  @@index([user_id])
  @@map("product_members")
}
```

---