Recette SSR

<!DOCTYPE html><html lang="fr"><head><meta charSet="utf-8"/><meta name="viewport" content="width=device-width, initial-scale=1"/><link rel="stylesheet" href="/assets/app-yW1jEKvs.css" data-precedence="default"/><title>Architecture : schéma Supabase et politiques RLS | Démo catalogue fluides</title><meta name="robots" content="noindex"/><meta name="description" content="Pile technique de la démonstration, schéma de tables calqué sur Supabase et politiques RLS équivalentes aux règles d’accès."/><link rel="modulepreload" href="/assets/index-Cbfsqsdq.js"/><link rel="modulepreload" href="/assets/react-DmeiICsC.js"/><link rel="modulepreload" href="/assets/schemas-DIlC3hV3.js"/><link rel="modulepreload" href="/assets/architecture-iGYY26WM.js"/><link rel="canonical" href="https://otnpf6qjgg.demo.wassimzo.com/architecture"/></head><body class="bg-slate-50 font-sans text-slate-900 antialiased"><div class="flex min-h-screen flex-col"><div class="bg-amber-100 px-3 py-1.5 text-center text-xs text-amber-900 sm:text-sm" data-bandeau-demo="true"><strong>Démo – données d’exemple · catalogue et entreprises fictifs</strong><span class="hidden sm:inline"> — aucune donnée, aucun logo, aucun prix ni client réel</span></div><header class="border-b border-slate-200 bg-white"><div class="mx-auto flex max-w-6xl flex-wrap items-center justify-between gap-3 px-4 py-3"><a class="flex items-baseline gap-2" href="/"><span class="text-lg font-semibold tracking-tight text-marque">Fluides Rétrofit</span><span class="pastille bg-slate-100 text-slate-600">démo</span></a><a class="rounded-full border border-slate-200 px-3 py-1 text-xs text-slate-700 hover:border-marque sm:text-sm" title="Changer de profil de démonstration" href="/espace-client"><span>Visiteur anonyme · choisir un profil</span></a></div><nav class="mx-auto flex max-w-6xl gap-1 overflow-x-auto px-2 text-sm sm:px-4" aria-label="Navigation principale"><a class="whitespace-nowrap border-b-2 border-transparent px-3 py-2 text-slate-600 hover:text-marque" href="/">Accueil</a><a class="whitespace-nowrap border-b-2 border-transparent px-3 py-2 text-slate-600 hover:text-marque" href="/catalogue">Catalogue</a><a class="whitespace-nowrap border-b-2 border-transparent px-3 py-2 text-slate-600 hover:text-marque" href="/espace-client">Espace client</a><a class="whitespace-nowrap border-b-2 border-transparent px-3 py-2 text-slate-600 hover:text-marque" href="/panier">Panier de devis</a><a class="whitespace-nowrap border-b-2 border-transparent px-3 py-2 text-slate-600 hover:text-marque" href="/admin">Back-office</a><a class="whitespace-nowrap border-b-2 border-transparent px-3 py-2 text-slate-600 hover:text-marque" href="/recette-ssr">Recette SSR</a><a class="whitespace-nowrap border-b-2 border-transparent px-3 py-2 text-slate-600 hover:text-marque border-marque! text-marque! font-medium" href="/architecture" data-status="active" aria-current="page">Architecture</a></nav></header><main class="mx-auto w-full flex-1 px-4 py-6 max-w-6xl"><!--$--><div class="space-y-8"><div><h1 class="text-2xl font-semibold">Architecture</h1><p class="mt-1 max-w-3xl text-sm text-slate-600">La démonstration utilise la pile imposée. Seule la base change : SQLite remplace Supabase, avec les mêmes tables, et les règles d’accès vivent dans un seul module serveur dont chaque fonction correspond à une politique RLS.</p></div><section class="carte overflow-x-auto"><h2 class="font-semibold">Pile</h2><table class="mt-3 w-full min-w-[520px] text-sm"><tbody class="divide-y divide-slate-100"><tr><td class="py-2 pr-4 align-top font-medium">TanStack Start (React 19, Vite)</td><td class="py-2 text-slate-700">Rendu côté serveur de chaque page, routes fichiers, fonctions serveur pour les chargements.</td></tr><tr><td class="py-2 pr-4 align-top font-medium">TanStack Router + zod</td><td class="py-2 text-slate-700">Filtres du catalogue dans l’URL, validés par validateSearch : un lien partagé ou rechargé redonne la même liste.</td></tr><tr><td class="py-2 pr-4 align-top font-medium">Puck</td><td class="py-2 text-slate-700">Éditeur de blocs du back-office (/admin/pages) ; &lt;Render&gt; dans la route serveur de l’accueil.</td></tr><tr><td class="py-2 pr-4 align-top font-medium">SQLite (démo) → Supabase Postgres (cible)</td><td class="py-2 text-slate-700">Même schéma de tables ; SQLite chargé en mémoire et réinitialisé à chaque redémarrage.</td></tr><tr><td class="py-2 pr-4 align-top font-medium">Module d’accès unique (src/server/acces.ts)</td><td class="py-2 text-slate-700">Toutes les règles d’accès côté serveur, écrites pour correspondre une à une aux politiques RLS ci-dessous.</td></tr><tr><td class="py-2 pr-4 align-top font-medium">Cookie signé (HMAC)</td><td class="py-2 text-slate-700">Profils de démonstration sans inscription ; remplacé par Supabase Auth en production.</td></tr></tbody></table></section><section><h2 class="text-lg font-semibold">Règles d’accès et politiques RLS équivalentes</h2><div class="mt-3 space-y-4"><article class="carte"><p class="font-mono text-xs text-marque">peutLireDocument · documentsVisibles</p><p class="mt-1 text-sm text-slate-800">Un document confidentiel n’est lisible que par un membre de son entreprise : l’admin toujours, les autres membres seulement sur les catégories accordées. Les FDS publiques sont lisibles par tous.</p><pre class="mt-3 overflow-x-auto rounded-lg bg-slate-950 p-4 font-mono text-[11px] leading-relaxed text-slate-200">alter table public.documents enable row level security;

create or replace function public.a_acces_categorie(p_entreprise uuid, p_categorie text)
returns boolean
language sql stable security definer set search_path = public
as $$
  select exists (
    select 1 from memberships m
    where m.profile_id = auth.uid()
      and m.company_id = p_entreprise
      and (
        m.role = &#x27;admin&#x27;
        or exists (
          select 1 from permissions p
          where p.profile_id = auth.uid()
            and p.company_id = p_entreprise
            and p.category_id = p_categorie
        )
      )
  );
$$;

create policy documents_lecture on public.documents
for select to anon, authenticated
using (
  (company_id is null and exists (
    select 1 from document_categories c where c.id = category_id and c.is_public
  ))
  or public.a_acces_categorie(company_id, category_id)
);</pre></article><article class="carte"><p class="font-mono text-xs text-marque">route /documents/$id (fichier PDF)</p><p class="mt-1 text-sm text-slate-800">Le fichier suit la même règle que sa ligne : sans ligne visible, Storage refuse le téléchargement (403).</p><pre class="mt-3 overflow-x-auto rounded-lg bg-slate-950 p-4 font-mono text-[11px] leading-relaxed text-slate-200">create policy documents_fichiers_lecture on storage.objects
for select to anon, authenticated
using (
  bucket_id = &#x27;documents&#x27;
  and exists (select 1 from public.documents d where d.storage_path = name)
);</pre></article><article class="carte"><p class="font-mono text-xs text-marque">peutGererDroits · modifierDroit</p><p class="mt-1 text-sm text-slate-800">Seul l’admin de l’entreprise accorde ou retire un droit, et seulement à un membre non admin de sa propre entreprise.</p><pre class="mt-3 overflow-x-auto rounded-lg bg-slate-950 p-4 font-mono text-[11px] leading-relaxed text-slate-200">alter table public.permissions enable row level security;

create policy droits_gestion_admin on public.permissions
for all to authenticated
using (exists (
  select 1 from memberships m
  where m.profile_id = auth.uid() and m.company_id = permissions.company_id and m.role = &#x27;admin&#x27;
))
with check (
  exists (
    select 1 from memberships m
    where m.profile_id = auth.uid() and m.company_id = permissions.company_id and m.role = &#x27;admin&#x27;
  )
  and exists (
    select 1 from memberships cible
    where cible.profile_id = permissions.profile_id
      and cible.company_id = permissions.company_id
      and cible.role &lt;&gt; &#x27;admin&#x27;
  )
);</pre></article><article class="carte"><p class="font-mono text-xs text-marque">peutEnvoyerDemandeDevis · peutLireDemandesDevis</p><p class="mt-1 text-sm text-slate-800">Un membre crée une demande pour sa propre entreprise ; l’équipe interne lit toutes les demandes, un client seulement celles de son entreprise.</p><pre class="mt-3 overflow-x-auto rounded-lg bg-slate-950 p-4 font-mono text-[11px] leading-relaxed text-slate-200">alter table public.quote_requests enable row level security;

create policy devis_creation_membre on public.quote_requests
for insert to authenticated
with check (
  profile_id = auth.uid()
  and exists (
    select 1 from memberships m
    where m.profile_id = auth.uid() and m.company_id = quote_requests.company_id
  )
);

create policy devis_lecture on public.quote_requests
for select to authenticated
using (
  exists (select 1 from profiles p where p.id = auth.uid() and p.is_staff)
  or exists (
    select 1 from memberships m
    where m.profile_id = auth.uid() and m.company_id = quote_requests.company_id
  )
);</pre></article><article class="carte"><p class="font-mono text-xs text-marque">peutPublierPage</p><p class="mt-1 text-sm text-slate-800">Les pages publiées sont lisibles par tous ; seule l’équipe marketing publie.</p><pre class="mt-3 overflow-x-auto rounded-lg bg-slate-950 p-4 font-mono text-[11px] leading-relaxed text-slate-200">alter table public.pages enable row level security;

create policy pages_lecture on public.pages for select using (true);

create policy pages_publication_marketing on public.pages
for all to authenticated
using (exists (select 1 from profiles p where p.id = auth.uid() and p.is_staff))
with check (exists (select 1 from profiles p where p.id = auth.uid() and p.is_staff));</pre></article></div></section><section class="carte"><h2 class="font-semibold">Schéma cible Supabase (Postgres)</h2><p class="mt-1 text-sm text-slate-600">companies, profiles, memberships, document_categories, documents, permissions, products, quote_requests, quote_lines, pages.</p><pre class="mt-3 max-h-[32rem] overflow-auto rounded-lg bg-slate-950 p-4 font-mono text-[11px] leading-relaxed text-slate-200">create type member_role as enum (&#x27;admin&#x27;, &#x27;acheteur&#x27;, &#x27;technicien&#x27;);

create table public.companies (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  siret text not null,
  city text not null,
  capacity_certificate_number text not null,
  capacity_certificate_valid_until date not null
);

create table public.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  full_name text not null,
  email text not null,
  is_staff boolean not null default false
);

create table public.memberships (
  profile_id uuid not null references public.profiles(id) on delete cascade,
  company_id uuid not null references public.companies(id) on delete cascade,
  role member_role not null,
  primary key (profile_id, company_id)
);

create table public.document_categories (
  id text primary key,
  label text not null,
  is_public boolean not null default false,
  sort_order int not null
);

create table public.documents (
  id uuid primary key default gen_random_uuid(),
  company_id uuid references public.companies(id),
  category_id text not null references public.document_categories(id),
  title text not null,
  storage_path text not null,
  created_at timestamptz not null default now()
);

create table public.permissions (
  company_id uuid not null references public.companies(id) on delete cascade,
  profile_id uuid not null references public.profiles(id) on delete cascade,
  category_id text not null references public.document_categories(id),
  granted_by uuid references public.profiles(id),
  granted_at timestamptz not null default now(),
  primary key (company_id, profile_id, category_id)
);

create table public.products (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique,
  name text not null,
  kind text not null check (kind in (&#x27;retrofit&#x27;, &#x27;equipement&#x27;, &#x27;historique&#x27;)),
  replaces text[] not null default &#x27;{}&#x27;,
  gwp_ar4 int not null,
  safety_class text not null check (safety_class in (&#x27;A1&#x27;, &#x27;A2L&#x27;, &#x27;A3&#x27;)),
  glide_k numeric(4, 1) not null,
  oils text[] not null,
  applications text[] not null,
  packagings text[] not null,
  composition text not null,
  regulatory_note text not null
);

create table public.quote_requests (
  id bigint generated always as identity primary key,
  reference text not null unique,
  company_id uuid not null references public.companies(id),
  profile_id uuid not null references public.profiles(id),
  company_siret text not null,
  capacity_certificate_number text not null,
  capacity_certificate_valid_until date not null,
  capacity_certificate_expired boolean not null,
  comment text not null default &#x27;&#x27;,
  status text not null default &#x27;reçue&#x27;,
  created_at timestamptz not null default now()
);

create table public.quote_lines (
  id bigint generated always as identity primary key,
  quote_request_id bigint not null references public.quote_requests(id) on delete cascade,
  product_id uuid not null references public.products(id),
  product_name text not null,
  packaging text not null,
  quantity int not null check (quantity between 1 and 500)
);

create table public.pages (
  slug text primary key,
  published_data jsonb not null,
  published_at timestamptz not null default now(),
  published_by uuid references public.profiles(id)
);
</pre></section></div><script>(function(a,f){let l;try{l=JSON.parse(sessionStorage.getItem(a)||"{}")}catch{return}const n=l?.[f||history.state?.__TSR_key];let c=!1;for(const t in n){const e=n[t],o=e?.scrollX,s=e?.scrollY;if(Number.isFinite(o)&&Number.isFinite(s)){if(t==="window")scrollTo(o,s),c=!0;else if(t)try{const r=document.querySelector(t);r&&(r.scrollLeft=o,r.scrollTop=s)}catch{}}}if(c)return;const i=location.hash.slice(1);if(i){const t=history.state?.__hashScrollIntoViewOptions??!0;if(t){const e=document.getElementById(i);e&&e.scrollIntoView(t)}return}scrollTo(0,0)})("tsr-scroll-restoration-v1_3");document.currentScript.remove()</script><!--/$--></main><footer class="border-t border-slate-200 bg-white"><div class="mx-auto flex max-w-6xl flex-col gap-2 px-4 py-4 text-xs text-slate-500 sm:flex-row sm:items-center sm:justify-between"><p>Démonstration technique : entreprises, documents et demandes fictifs, réinitialisés à chaque redémarrage.</p><p class="flex gap-4"><a href="/recette-ssr?page=%2Farchitecture&amp;lancer=true" class="lien">Recette SSR de cette page</a><a class="lien active" href="/architecture" data-status="active" aria-current="page">Architecture</a></p></div></footer></div><script data-tsr-stream-part="">(self.$R=self.$R||{})["tsr"]=[];self.$_TSR={h(){this.hydrated=!0,this.c()},e(){this.streamEnded=!0,this.c()},c(){this.hydrated&&this.streamEnded&&(delete self.$_TSR,delete self.$R.tsr)},p(e){this.initialized?e():this.buffer.push(e)},buffer:[]};$_TSR.router=($R=>$R[0]={manifest:$R[1]={routes:$R[2]={__root__:$R[3]={preloads:$R[4]=["/assets/index-Cbfsqsdq.js","/assets/react-DmeiICsC.js","/assets/schemas-DIlC3hV3.js"],scripts:$R[5]=[$R[6]={attrs:$R[7]={type:"module",async:!0,src:"/assets/index-Cbfsqsdq.js"}}]},"/architecture":$R[8]={preloads:$R[9]=["/assets/architecture-iGYY26WM.js"]}}},matches:$R[10]=[$R[11]={i:"__root__#",u:1791896601349,s:"success",l:$R[12]={utilisateur:null},ssr:!0},$R[13]={i:"#architecture#architecture",u:1791896601349,s:"success",ssr:!0}]})($R["tsr"]);$_TSR.e();{let s=document.currentScript,p;while((p=s.previousElementSibling)&&p.hasAttribute('data-tsr-stream-part'))p.remove();s.remove()}</script><script type="module" async="" src="/assets/index-Cbfsqsdq.js"></script><script>document.currentScript.remove();/*$tsr-stream-boundary*/</script></body></html>