> ## Documentation Index
> Fetch the complete documentation index at: https://docs-kit.lucdidion.lu/llms.txt
> Use this file to discover all available pages before exploring further.

# Schéma de Base de Données

> Documentation complète des 13 tables PostgreSQL avec relations et contraintes

## Vue d'ensemble

Kit'Asso utilise **13 tables PostgreSQL** hébergées sur **Nhost** et exposées via **Hasura GraphQL**. La base de données est organisée en 4 domaines fonctionnels : le catalogue d'outils, les workflows guidés, les packs curés et le système de quiz avec scoring pondéré V2.

**Architecture :**

* **Core Tables** (5) : tools, categories, filters, tool\_features, site\_assets
* **Workflow Tables** (2) : workflows, workflow\_steps
* **Pack System** (2) : tool\_packs, pack\_tools
* **Quiz System** (5) : quizzes, quiz\_questions, quiz\_answers, quiz\_recommendations, quiz\_responses

**Accès aux données :**

* **GraphQL** via Hasura (auto-généré depuis le schéma PostgreSQL)
* **Permissions Hasura** par rôle (public/admin) — pas de RLS PostgreSQL
* **API Layer** centralisée dans `src/api/` avec `apiCall()` / `apiCallVoid()`

***

## Core Tables

### 1. tools

Table principale du catalogue d'outils numériques.

**Colonnes :**

```sql theme={null}
CREATE TABLE tools (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT UNIQUE NOT NULL,
  description TEXT NOT NULL,
  pricing_tier TEXT CHECK (pricing_tier IN ('Gratuit', 'Freemium', 'Payant', 'Entreprise')),
  category_id UUID REFERENCES categories(id) ON DELETE SET NULL,
  logo_url TEXT,
  website_url TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
```

**Type TypeScript :**

```typescript theme={null}
interface Tool {
  id: string;
  name: string;
  description: string;
  pricing_tier: 'Gratuit' | 'Freemium' | 'Payant' | 'Entreprise';
  category_id: string | null;
  logo_url: string | null;
  website_url: string | null;
  created_at: string;
}
```

**Query GraphQL :**

```graphql theme={null}
query {
  tools(order_by: { name: asc }) {
    id name description pricing_tier
    category_id logo_url website_url
  }
}
```

***

### 2. categories

Classification des outils par domaine d'activité.

```sql theme={null}
CREATE TABLE categories (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
```

**Exemples :** Communication, Gestion de projet, Comptabilité, Marketing, Événementiel, Formation

***

### 3. filters

Définitions de fonctionnalités et capacités des outils.

```sql theme={null}
CREATE TABLE filters (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  filter_type TEXT NOT NULL,
  value TEXT NOT NULL,
  feature_type TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
```

***

### 4. tool\_features (Join Table)

Relation many-to-many entre tools et filters.

```sql theme={null}
CREATE TABLE tool_features (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tool_id UUID REFERENCES tools(id) ON DELETE CASCADE NOT NULL,
  filter_id UUID REFERENCES filters(id) ON DELETE CASCADE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(tool_id, filter_id)
);
```

***

### 5. site\_assets

Gestion centralisée des assets du site (logos, images).

```sql theme={null}
CREATE TABLE site_assets (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT UNIQUE NOT NULL,
  url TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
```

**Note :** Les URLs pointent vers Nhost Storage (`https://{subdomain}.storage.{region}.nhost.run/v1/files/{fileId}`).

***

## Workflow Tables

### 6. workflows

Parcours guidés étape par étape pour la transformation numérique.

```sql theme={null}
CREATE TABLE workflows (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  difficulty TEXT CHECK (difficulty IN ('débutant', 'intermédiaire', 'expert')),
  duration TEXT NOT NULL,
  category TEXT NOT NULL,
  icon TEXT NOT NULL,
  status TEXT CHECK (status IN ('active', 'draft')) DEFAULT 'draft',
  steps JSONB NOT NULL DEFAULT '[]',
  display_order INTEGER DEFAULT 0,
  objective TEXT,
  completion_message TEXT,
  next_steps JSONB DEFAULT '[]',
  resources JSONB DEFAULT '[]',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
```

**Query GraphQL (public — uniquement actifs) :**

```graphql theme={null}
query {
  workflows(
    where: { status: { _eq: "active" } }
    order_by: { display_order: asc }
  ) {
    id title description difficulty duration category icon status
    steps display_order
  }
}
```

***

### 7. workflow\_steps

Détails enrichis des étapes de workflow.

```sql theme={null}
CREATE TABLE workflow_steps (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  workflow_id UUID REFERENCES workflows(id) ON DELETE CASCADE NOT NULL,
  step_number INTEGER NOT NULL,
  tool_name TEXT NOT NULL,
  action TEXT NOT NULL,
  tool_url TEXT,
  detailed_instructions JSONB DEFAULT '[]',
  practical_tip TEXT,
  completion_checklist TEXT[],
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
```

***

## Pack System

### 8. tool\_packs

Collections curées d'outils pour des cas d'usage spécifiques.

```sql theme={null}
CREATE TABLE tool_packs (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  difficulty TEXT CHECK (difficulty IN ('débutant', 'intermédiaire', 'expert')),
  icon TEXT DEFAULT 'Package',
  color TEXT DEFAULT 'blue',
  display_order INTEGER DEFAULT 0,
  status TEXT CHECK (status IN ('active', 'draft')) DEFAULT 'active',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
```

### 9. pack\_tools (Join Table)

Relation many-to-many entre packs et tools avec ordering.

```sql theme={null}
CREATE TABLE pack_tools (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  pack_id UUID REFERENCES tool_packs(id) ON DELETE CASCADE NOT NULL,
  tool_id UUID REFERENCES tools(id) ON DELETE CASCADE NOT NULL,
  display_order INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(pack_id, tool_id)
);
```

***

## Quiz System

### 10. quizzes

```sql theme={null}
CREATE TABLE quizzes (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  title TEXT NOT NULL,
  description TEXT,
  slug TEXT UNIQUE NOT NULL,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
```

### quiz\_questions

Questions du quiz avec support de branchement conditionnel.

```sql theme={null}
CREATE TABLE quiz_questions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  quiz_id UUID REFERENCES quizzes(id) ON DELETE CASCADE NOT NULL,
  question_text TEXT NOT NULL,
  question_type TEXT CHECK (question_type IN ('single', 'multiple', 'scale')),
  order_index INTEGER NOT NULL,
  is_required BOOLEAN DEFAULT TRUE,
  help_text TEXT,
  next_question_rules JSONB,  -- V2 : règles de branchement conditionnel
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
```

**Branchement conditionnel (`next_question_rules`) :**

```json theme={null}
[
  { "if_answer_value": "aucun", "go_to_question_id": "uuid-question-3" },
  { "if_answer_value": "avance", "go_to_question_id": "uuid-question-5" }
]
```

### quiz\_answers

Options de réponse avec scoring pondéré V2.

```sql theme={null}
CREATE TABLE quiz_answers (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  question_id UUID REFERENCES quiz_questions(id) ON DELETE CASCADE NOT NULL,
  answer_text TEXT NOT NULL,
  answer_value TEXT NOT NULL,
  order_index INTEGER DEFAULT 0,
  scoring_weights JSONB,  -- V2 : pondération pour le scoring
  created_at TIMESTAMPTZ DEFAULT NOW()
);
```

**Scoring weights (`scoring_weights`) :**

```json theme={null}
{
  "tool:uuid-mailchimp": 5,
  "category:communication": 2,
  "pricing:gratuit": 1
}
```

Les clés supportées sont `tool:<id>`, `category:<name>`, et `pricing:<tier>`.

### quiz\_recommendations

Règles de recommandation (système legacy V1, remplacé par le scoring V2).

```sql theme={null}
CREATE TABLE quiz_recommendations (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  quiz_id UUID REFERENCES quizzes(id) ON DELETE CASCADE NOT NULL,
  condition_logic JSONB NOT NULL,
  recommended_pack_ids UUID[],
  recommended_tool_ids UUID[],
  recommendation_text TEXT,
  priority INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
```

### quiz\_responses

Soumissions utilisateurs avec analytics.

```sql theme={null}
CREATE TABLE quiz_responses (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  quiz_id UUID REFERENCES quizzes(id) ON DELETE CASCADE NOT NULL,
  answers JSONB NOT NULL,
  recommended_pack_ids UUID[],
  recommended_tool_ids UUID[],
  email TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
```

**Query GraphQL pour charger un quiz (V2) :**

```graphql theme={null}
query GetQuiz($slug: String!) {
  quizzes(where: { slug: { _eq: $slug }, is_active: { _eq: true } }) {
    id title description slug
    quiz_questions(order_by: { order_index: asc }) {
      id question_text question_type order_index is_required help_text
      next_question_rules
      quiz_answers(order_by: { order_index: asc }) {
        id answer_text answer_value scoring_weights
      }
    }
  }
}
```

***

## Relations et Cardinalités

```
categories (1) ──< (N) tools
tools (N) ──< (N) filters (via tool_features)
tools (N) ──< (N) tool_packs (via pack_tools)
workflows (1) ──< (N) workflow_steps
quizzes (1) ──< (N) quiz_questions
quiz_questions (1) ──< (N) quiz_answers
quizzes (1) ──< (N) quiz_recommendations
quizzes (1) ──< (N) quiz_responses
```

**Cascades :**

* Suppression d'un `tool` → supprime ses `tool_features` et `pack_tools`
* Suppression d'un `workflow` → supprime ses `workflow_steps`
* Suppression d'un `quiz` → supprime `questions`, `answers`, `recommendations`, `responses`
* Suppression d'une `category` → SET NULL sur `tools.category_id`

***

## Ressources

<CardGroup cols={2}>
  <Card title="Permissions Hasura" icon="shield" href="/database/rls">
    Contrôle d'accès par rôle
  </Card>

  <Card title="Storage" icon="folder" href="/database/storage">
    Gestion des logos et assets via Nhost Storage
  </Card>

  <Card title="API Layer" icon="code" href="/development/api-layer">
    Utilisation de l'API pour requêter via GraphQL
  </Card>

  <Card title="Nhost & GraphQL" icon="plug" href="/architecture/nhost-graphql">
    Architecture backend
  </Card>
</CardGroup>