Modele de Donnees

Modèle de données

Statut : 🟡 Brouillon · Màj : 2026-10-14 · Tickets : #164 #383

PostgreSQL 16 avec PostGIS. Schéma géré par Drizzle ORM (apps/api/src/db/schema.ts), migrations versionnées. Identifiants UUID v7 (triables). Horodatages en timestamptz. Clés et valeurs d'énumération en anglais snake_case ; les libellés français vivent dans packages/shared.

Vue d'ensemble

erDiagram
  users ||--o{ routes : creates
  users ||--o{ favorites : has
  users ||--o{ planned_routes : plans
  users ||--o{ place_signals : sends
  users ||--o{ route_reports : sends
  users ||--o{ user_anecdotes : tells
  users ||--o{ user_featured_routes : features
  routes ||--|{ steps : contains
  routes ||--o{ media : has
  routes ||--o{ route_revisions : has
  places ||--o{ steps : used_in
  places ||--o{ place_hours : has
  places ||--o{ media : has
  steps ||--o{ media : has
  places ||--o{ place_signals : receives
  routes ||--o{ route_reports : receives
  cities ||--o{ places : contains
  cities ||--o{ routes : contains
  planned_routes ||--o{ planned_steps : tracks
  routes ||--o{ moderation_actions : receives

Loading

Tables

cities

Colonne	Type	Notes
id	uuid
slug	text unique	paris
name	text
center	geography(Point)
bounds	geography(Polygon)
timezone	text	Europe/Paris
is_active	boolean	seule Paris est active au MVP

Révision du 2026-10-09 (D-085, #340) : is_active ne filtre plus rien ; une ville est créée automatiquement par géocodage inverse au premier lieu qui s'y trouve, et places.city_id, routes.city_id (ville de la première étape) sont remplis par l'API. La recherche se fait par zone, sans ville.

users

Colonne	Type	Notes
id	uuid
firebase_uid	text unique
email	text	nullable (Apple relay)
first_name	text	prénom ou pseudo, affiché sur le profil et les cards
avatar_url	text
role	enum user, editor, moderator, admin
plan	enum free, premium	free ; mis à jour par les webhooks RevenueCat
plan_expires_at	timestamptz
revenuecat_app_user_id	text
is_public	boolean	profil public de créateur visible, défaut true ; interrupteur « Profil public » des Réglages (D-025)
bio	text	150 caractères au plus, facultative, affichée sur le profil (D-023)
neighborhood	text	quartier affiché sur le profil (« Paris 11e »), facultatif
is_certified	boolean	badge Créateur certifié, défaut false, recalculé chaque nuit
certified_override	enum none, granted, revoked	choix de l'équipe dans l'admin, l'emporte sur le calcul ; défaut none
certified_at	timestamptz	date d'obtention du badge, nullable
trust_score	numeric	pondération des signaux, défaut 1.0
notification_prefs	jsonb	{ reminders, next_day, my_routes }, booléens, true par défaut (D-062, E-19) : reminders rappels de la veille et de H-1 ; next_day notification go_closed de 10:00 ; my_routes publication, corrections demandées, report_resolved
locale	text	nullable : fr ou en ; nul = langue du téléphone (D-062). Langue des notifications et des e-mails
created_at, deleted_at	timestamptz	soft delete puis purge

email_changes (D-062)

user_id, new_email, token_hash, expires_at, created_at, confirmed_at, cancelled_at. Une demande en attente par utilisateur au plus (une nouvelle demande remplace l'ancienne) ; l'adresse de users ne change qu'à la confirmation. Durée de validité du lien : même valeur que le lien de connexion de E-10.

support_messages (D-063)

user_id (nullable, sans compte), reply_email, topic (question, problem, data, other), message, app_version, device, locale, created_at, handled_at. Durée de conservation à fixer (3 ans après le dernier échange proposés dans la politique de confidentialité).

places

Colonne	Type	Notes
id	uuid
city_id	uuid
name	text
category	enum	restaurant, cafe, bar, bakery, museum, gallery, monument, park, viewpoint, shop, activity, event_venue, walk, other
location	geography(Point)	index GiST
address	text	formatée
address_components	jsonb	rue, code postal, arrondissement
is_indoor	boolean
price_range	enum free, low, medium, high	indicatif
website_url, phone	text
external_refs	jsonb	ids Google Places, OSM, pour plus tard
verification_status	enum unverified, verified, stale, flagged, closed	unverified par défaut pour un lieu créé par un utilisateur, jusqu'à la modération ou une première vérification ; la règle unverified_place s'appuie dessus (D-049)
always_open	boolean	« Accessible à toute heure » (parc, rue, point de vue) : pas d'horaires, ne lève pas hours_unknown (D-049)
ends_on	date	lieu éphémère (exposition, pop-up), facultatif ; la date passée, le lieu sort de Widoo (D-050)
verified_at	timestamptz
confirmations_since_verified	numeric	pondéré
created_by	uuid
created_at, updated_at	timestamptz

Index : GIST(location), (city_id, category), trigram sur name.

place_hours

Colonne	Type	Notes
place_id	uuid
weekday	smallint	0 = lundi
opens, closes	time	plusieurs lignes par jour pour les coupures
valid_from, valid_to	date	nullable, pour les horaires saisonniers

Absence de ligne pour un jour = fermé ce jour ; absence totale = horaires inconnus.

routes

Colonne	Type	Notes
id	uuid
city_id	uuid
author_id	uuid
is_official	boolean	« Par Widoo »
status	enum draft, private, pending, changes_requested, published, needs_fix, rejected, unpublished
access	enum free, premium	free
sponsored	boolean	false
share_token	text unique	pour les liens privés
title, description	text	titre 60 caractères au plus (D-050)
moods	text[]	clés d'ambiance, ordonnées : la première est la principale (point d'ambiance, ligne « ambiance · quartier ») (D-050)
audiences	text[]
conditions	text[]	saisies + dérivées
transport	enum walk, bike, metro, car	dérivé des trajets : mode qui couvre le plus de distance ; les autres modes servent de valeurs secondaires du filtre (D-048)
suggested_start	time	heure de départ choisie par le créateur ; vide = calculée (D-048, D-050)
tips	text[]	« À prévoir » : lignes du créateur seulement, 3 au plus ; les suggestions sont calculées dans computed (D-048, D-050)
tips_hidden	text[]	clés des suggestions « À prévoir » désactivées par le créateur (D-050)
start_location	geography(Point)	première étape, index GiST
bounds	geography(Polygon)	enveloppe des étapes
computed	jsonb	résultat d'orchestration, sur nos estimations de trajet (D-088) : durée, budget (budget_per_person_eur, somme exacte des coûts par personne, sans fourchette, D-032), distance, marche, effort, filtres dérivés, avertissements, départ calculé, accès (station la plus proche de la première étape, D-050), suggestions « À prévoir » (D-050), computed_at
duration_bucket	enum	dérivé, indexé
budget_bucket	enum	dérivé, indexé
published_at	timestamptz
stats	jsonb	favoris, programmations, go terminés, rating_avg, rating_count (dénormalisé, recalculé)
recommendation	jsonb	composantes stables du score, recalculées la nuit par POST /internal/recommendation : quality, rated (au moins un avis), reliability, freshness, official (entre 0 et 1), moments (tranches de la journée où le parcours convient, déduites de la première étape), computed_at ; la proximité et le contexte sont calculés à la requête (Filtres-et-Recherche › Recommandation, D-066)
created_at, updated_at	timestamptz

Index : GIST(bounds), (city_id, status), GIN(moods), GIN(audiences), GIN(conditions), (duration_bucket), (budget_bucket).

route_revisions

Instantané JSON d'un parcours publié à chaque nouvelle soumission de l'auteur ; la révision en attente est stockée ici jusqu'à validation, la version publiée reste dans routes.

route_translations (décision d'Ilan du 2026-09-28)

route_id, revision_id, locale (en, fr), source_locale, fields jsonb (titre, description, conditions, À prévoir, et par étape : description, le mot, à ne pas manquer, chemin écrit, note de réservation), provider, created_at. Une ligne par révision publiée et par langue cible ; générée par un modèle de langage à la publication et à chaque modification publiée, jamais à la lecture. Les noms de lieux ne sont pas traduits. Les avis sont traduits à la demande et mis en cache (même principe, review_translations, avec source_locale, que l'app affiche : « traduit de l'anglais »). Le choix « Voir l'original » (parcours, avis) est gardé sur le téléphone, jamais sur le serveur ; un parcours lancé garde sur le téléphone sa traduction et son original (D-076, Ecrans › E-05).

steps

Colonne	Type	Notes
id	uuid
route_id	uuid
position	smallint	ordre
place_id	uuid
title	text	nullable, sinon nom du lieu
description	text	600 caractères au plus ; le mot du créateur : « Le mot de {prénom} » dans E-07, conseil dans E-05 ; facultatif
highlights	text[]	« À ne pas manquer » dans E-07, 1 à 3 points, facultatif (D-029)
audio_path, audio_duration_s	text, smallint	piste audio du créateur, colonne gardée mais hors MVP : aucun écran ni lecteur (D-029)
duration_min	smallint
cost_per_person	numeric	estimé, nullable
booking_required	boolean
booking_url	text
booking_provider	enum restaurant, ticketing, activity, other
transition_note	text	« Passer par la rue des Rosiers » ; « Le chemin de {prénom} » dans E-07, facultatif
transition	jsonb	estimation, jamais une réponse de Mapbox (D-088) : distance_m, duration_min, mode, estimated ; mode choisi par le créateur pour chaque trajet, proposé selon la distance (à pied jusqu'à 1,5 km, métro au-delà), modifiable (D-048)
booking_note	text	précision de réservation, 80 caractères (D-048)
booking_is_affiliate	boolean	case « C'est un lien affilié » ; affiche « Lien affilié » près de « Réserver » (D-048)
ends_on	date	étape éphémère, facultative (D-050)

Unique (route_id, position).

direction_cache (désactivée, D-088)

profile, from_lat_e5, from_lng_e5, to_lat_e5, to_lng_e5, distance_m, duration_s, geometry, created_at, expires_at. Cache des réponses de Mapbox Directions livré par #34, vide : MAPBOX_DIRECTIONS_CACHE_DAYS vaut 0, car les conditions de Mapbox interdisent de conserver ces résultats. Il ne s'active qu'avec un accord écrit de Mapbox (option écartée pour l'instant, D-088).

media

Colonne	Type	Notes
id	uuid
owner_type	enum route, place, step	step : photos de l'étape dans E-07, 5 au plus (D-029) ; route : photos du parcours, 6 au plus, préremplies avec la première photo de chaque étape, position 0 = couverture (D-050)
owner_id	uuid
storage_path	text
width, height	int
position	smallint	ordre ; photo principale d'un lieu : le média owner_type = place de plus petite position, à égalité le plus ancien (created_at) ; c'est steps[].photoUrl de la card de recherche (D-071, #219)
uploaded_by	uuid
source	enum upload, commons	photo envoyée ou photo libre de Wikimedia Commons (D-050)
author, license, source_url	text	crédit d'une photo libre, affiché partout où la photo apparaît (D-050)
created_at	timestamptz

favorites

(user_id, route_id) unique, created_at.

planned_routes

Colonne	Type	Notes
id	uuid
user_id, route_id	uuid
route_revision_snapshot	jsonb	ce que l'utilisateur a programmé, figé
starts_at	timestamptz
status	enum planned, in_progress, completed, stopped, cancelled, missed	stopped : « Arrêter le parcours », sans note (D-029) ; « Arrêté » dans les parcours passés, relançable (D-033) ; missed : date passée sans lancement, posé par le job de 4:00 du lendemain, « Pas fait » dans les parcours passés, « Reprogrammer » remet planned avec un nouveau starts_at, ne compte pas comme une sortie terminée ; cancelled : « Annuler la sortie », réversible par « Rétablir » tant que le toast est affiché (D-038)
timeline	jsonb	heures calculées par étape
checklist	jsonb	réservations cochées
completed_at	timestamptz	« Terminer la sortie », ou clôture automatique le lendemain à 4 h
closed_by	enum user, auto	nullable ; auto : sortie restée ouverte, close à 4:00 le lendemain (D-033)

planned_steps

planned_route_id, step_id, arrived_at (« J'y suis », D-029), done_at (« Étape suivante », « Fait à 10:22 » dans la timeline de E-07), skipped_at (« Passer cette étape », D-033 ; une étape passée ne compte pas comme faite), signal_id (nullable, la confirmation ou le signalement fait à cette étape). Sert au suivi Go et à la pondération des confirmations.

route_ratings

Colonne	Type	Notes
id	uuid
route_id, user_id	uuid	unique ensemble
planned_route_id	uuid	la réalisation qui donne le droit de noter
rating	smallint	1 à 5
comment	text	280 caractères max, nullable
status	enum visible, hidden	masquage par un modérateur
created_at, updated_at	timestamptz

user_anecdotes

Colonne	Type	Notes
user_id	uuid
question_key	text	clé de anecdote_questions
answer	text	150 caractères au plus
position	smallint	1 à 3

(user_id, position) et (user_id, question_key) uniques (D-023).

anecdote_questions

key, label, position, is_active. Huit questions au lancement, listées avec leur clé dans Ecrans › E-09 (D-026), gérées côté serveur et servies par /config.

user_featured_routes

user_id, route_id (parcours publié de l'utilisateur), position (1 à 3) ; (user_id, position) et (user_id, route_id) uniques. La ligne disparaît si le parcours n'est plus publié (D-023).

badges, user_badges (préparé, non implémenté)

badges : key, libellé et description dans shared, icon. user_badges : user_id, badge_key, earned_at. Aucune logique d'attribution au MVP.

place_signals

Colonne	Type	Notes
id	uuid
place_id, user_id	uuid
kind	enum confirm, report
reason	enum closed_today, closed_temporarily, closed_permanently, wrong_hours, wrong_address, works, crowded, not_as_described, other	pour report ; valeurs closed_today, works, crowded, not_as_described ajoutées par D-036
comment	text	seulement pour other, 80 caractères (D-036)
weight	numeric	calculé à l'insertion
context_planned_route_id	uuid	obligatoire depuis D-056 : un lieu ne se confirme ou ne se signale que pendant ou après une sortie (E-07, E-15)
status	enum open, accepted, rejected
reviewed_by, reviewed_at
created_at	timestamptz

Unique partiel (place_id, user_id) sur 7 jours glissants appliqué côté API.

route_reports (D-056)

Colonne	Type	Notes
id	uuid
route_id, user_id	uuid
step_id	uuid	nullable ; pour place_changed
reason	enum place_changed, inappropriate, dangerous_place, hidden_ad, copied_content, false_info, other
comment	text	80 caractères ; obligatoire pour other, facultatif pour place_changed
status	enum open, accepted, rejected
reviewed_by, reviewed_at
created_at	timestamptz

Jamais exposé au créateur. Lieux vérifiés du profil : calculés (lieux distincts des place_signals confirm de l'utilisateur), pas de colonne.

Ajout du 2026-10-09 (D-086, D-087) : motif low_rating, créé automatiquement (sans signaleur, reporter_id nul) quand un parcours publié passe sous 2,5 de moyenne avec au moins 5 avis ; un seul ouvert par parcours, rouvert seulement après 5 nouveaux avis.

moderation_actions

route_id, actor_id, action (publish, request_changes, reject, unpublish, edit), reason_code, message, created_at. Journal immuable.

notifications

user_id, kind, payload jsonb, read_at, sent_push_at, sent_email_at. Historique et centre de notifications futur. kind go_closed : notification de 10:00 après une clôture automatique, seulement si la note est possible et pas encore donnée (D-033). kind report_resolved : retour au signaleur quand son signalement de lieu ou de parcours mène à une correction (D-056).

route_unlocks (décision d'Ilan du 2026-09-28)

user_id, route_id, store_transaction_id, created_at. Déblocage définitif d'un parcours Premium acheté à l'unité ; unique par (user_id, route_id). Le pass 7 jours ne crée pas de ligne ici : il passe par users.plan et plan_expires_at (webhook RevenueCat), comme l'abonnement.

settings

Clé/valeur jsonb pour les seuils (buckets de budget et durée, max_pending_routes 3 (révision du 2026-09-28), seuils de vérification, limites de règles, seuils de certification certification.min_verified_routes 3, certification.min_rating 4,5 et certification.min_completed_routes 3 (sorties sur ses parcours, D-091), création gratuite free.max_routes 1, recherche search.cluster_area_km2 40 : surface de zone en km² au-delà de laquelle la recherche répond en clusters, poids de la recommandation recommendation.weights : objet partiel { proximity, quality, reliability, freshness, context, official }, chaque poids entre 0 et 10, les poids du wiki par défaut), lus par l'API au démarrage et rechargeables ; les deux clés de la recherche sont relues chaque minute, une valeur absente ou invalide vaut la valeur par défaut (D-066).

🟡 Brouillon #383 du 2026-10-14, à valider par Ilan, voir Compte-et-Monetisation › Fin de la limite de création : proposé, free.max_routes sort de la liste (création gratuite et sans limite depuis D-073, clé lue par aucun code) ; la seule limite, max_pending_routes, porte sur la soumission.

Admin web (D-085)

Ajouts du cadrage de l'Admin-Web :

• admin_actions : actor_id, action, target_type, target_id, before jsonb, after jsonb, reason, created_at. Journal immuable de toute écriture de l'admin (A-12) ; moderation_actions reste l'historique d'un parcours.
• users : suspended_at, suspension_reason ; plan_source enum store, gift (Premium offert depuis A-06).
• routes.source enum user, team, ai (brouillons de la pipeline IA, A-07).
• route_translations : status enum ok, failed, pending, edited_fields jsonb, edited_by, edited_at (correction manuelle, perdue à la révision publiée suivante).
• affiliate_programs : name, network, commission_note, cookie_days, is_active.
• places : partner_booking_url, partner_program_id (lien partenaire de Widoo, affiché seulement sans lien du créateur, D-048).
• booking_clicks : step_id, user_id (nullable), link_origin enum creator, creator_affiliate, partner, created_at ; écrit par GET /go/:stepId.
• b2b_clients : name, slug unique, type enum hotel, concierge, tourist_office, other, contact_name, contact_email, contact_phone, address, status enum prospect, active, suspended, ended, monthly_price_cents (HT), started_at, notes.
• b2b_client_routes : client_id, route_id, position (3 à 10 par partenaire).
• support_messages : reply_body, replied_by, replied_at en plus de handled_at.

Requête centrale : parcours dans une zone avec filtres

select r.*
from routes r
where r.status = 'published'
  and r.city_id = $city
  and ST_Intersects(r.bounds, ST_MakeEnvelope($west, $south, $east, $north, 4326)::geography)
  and ($audiences::text[] is null or r.audiences && $audiences)
  and ($moods::text[] is null or r.moods && $moods)
  and ($conditions::text[] is null or r.conditions @> $conditions)
  and ($durations::text[] is null or r.duration_bucket = any($durations))
  and ($budgets::text[] is null or r.budget_bucket = any($budgets))
  and ($transports::text[] is null or r.transport = any($transports))
order by
  (select count(*) from steps s join places p on p.id = s.place_id
   where s.route_id = r.id and p.verification_status = 'verified') desc,
  r.is_official desc,
  r.published_at desc
limit 50;

Esquisse d'origine : la recherche livrée sert 50 parcours au plus par page (limit, 20 par défaut) dans l'ordre du tri demandé (Filtres-et-Recherche › Recommandation), et répond en clusters au-delà de search.cluster_area_km2 (D-066).

Note : conditions utilise @> (toutes les conditions demandées) car « intérieur » et « sans réservation » sont des exigences, pas des envies. Public et ambiance utilisent && (au moins une).

Rétention et RGPD

Voir Securite-et-RGPD. Les tables portant des données personnelles : users, user_anecdotes, user_featured_routes, favorites, planned_routes, planned_steps, place_signals, route_reports, route_ratings, notifications, media.uploaded_by, routes.author_id.