Architecture — Schéma de base de données

Une seule table métier (travels), une vue (travels_view), deux fonctions helper. Toute la complexité du modèle est encodée dans raw_data JSONB + des colonnes générées PostgreSQL extraites automatiquement.

Vue d'ensemble

                    ┌─────────────────────────────────────────────┐
                    │  travels (table)                            │
                    │                                             │
                    │  ┌──────────────┐   raw_data JSONB         │
                    │  │ raw_data     │   embedding vector(3584) │
                    │  │ JSONB        │   updated_at TIMESTAMP   │
                    │  │ (sot)        │                          │
                    │  └──────┬───────┘   ─── colonnes générées : │
                    │         │ extrait                           │
                    │         ▼                                   │
                    │   id, name, destination, discount_club,     │
                    │   is_valid, is_seaside, travel_ranges[],    │
                    │   departure_dates[], search_vector          │
                    └──────────────────────┬──────────────────────┘
                                           │
                                           ▼
                    ┌─────────────────────────────────────────────┐
                    │  travels_view (VIEW)                         │
                    │                                              │
                    │  = travels                                   │
                    │     + next_departure (MIN d'occurrences      │
                    │                       futures)                │
                    │  WHERE next_departure > CURRENT_DATE          │
                    │  → filtre automatiquement les voyages morts   │
                    └─────────────────────────────────────────────┘

Source de vérité unique : migrations/*.sql. Le schéma vivant est obtenu en appliquant toutes les migrations yoyo dans l'ordre.

Table travels

CREATE TABLE travels (
    raw_data JSONB NOT NULL,                    -- Source de vérité (record Horizon)
    embedding vector(3584) NOT NULL,            -- BGE Multilingual Gemma2
    updated_at TIMESTAMP DEFAULT NOW(),         -- Dernier UPSERT (reindex)

    -- Colonnes générées (extraites de raw_data)
    id TEXT GENERATED ALWAYS AS
        (raw_data->>'id') STORED PRIMARY KEY,

    name TEXT GENERATED ALWAYS AS
        (raw_data->>'name') STORED,

    destination VARCHAR(2) GENERATED ALWAYS AS
        (raw_data->'country'->>'code') STORED,

    discount_club BOOLEAN GENERATED ALWAYS AS (
        CASE
            WHEN (raw_data->'minPrice'->>'pricePerPersonWithClubSpecialOffers')::NUMERIC
                 < (raw_data->'minPrice'->>'pricePerPersonWithSpecialOffers')::NUMERIC
            THEN TRUE ELSE FALSE
        END
    ) STORED,

    is_valid BOOLEAN GENERATED ALWAYS AS (
        raw_data @? '$.occurrences[*] ? (@.bookingState == 0 || @.bookingState == 2)'
    ) STORED,

    is_seaside BOOLEAN GENERATED ALWAYS AS
        (COALESCE((raw_data->>'isSeaside')::boolean, false)) STORED,

    travel_ranges TEXT[] GENERATED ALWAYS AS
        (jsonb_to_text_array(raw_data, '$.travelRanges[*].slug')) STORED,

    departure_dates TIMESTAMP[] GENERATED ALWAYS AS
        (jsonb_to_timestamp_array(raw_data, '$.occurrences[*].start')) STORED,

    search_vector tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('french', COALESCE(raw_data->>'name', '')), 'A') ||
        setweight(to_tsvector('french', COALESCE(raw_data->>'subtitle', '')), 'B') ||
        setweight(to_tsvector('french', COALESCE(raw_data->>'description', '')), 'C')
    ) STORED
);

Pourquoi des colonnes générées ?

• Une seule source de vérité : tout est dans raw_data. Pas de risque de désynchronisation entre raw_data->>'name' et name car PostgreSQL recalcule la colonne à chaque INSERT/UPDATE.
• Indexables comme des colonnes normales : GIN, BTREE, etc.
• Reindex idempotent : INSERT … ON CONFLICT DO UPDATE SET raw_data = EXCLUDED.raw_data met automatiquement à jour toutes les colonnes générées.
• Évolution facile : ajouter un filtre = une migration + une colonne générée + un index, sans toucher la pipeline d'ingestion.

Détails sur le « pourquoi pas un schéma normalisé » : architecture/adr/0001-jsonb-source-of-truth.md.

Détails par colonne générée

Colonne	Type	Source dans raw_data	Sert à filtrer/trier
id	TEXT	id	PK
name	TEXT	name	(utilisé via FTS)
destination	VARCHAR(2)	country.code (ex: CH, FR, AT)	?destination=
discount_club	BOOLEAN	minPrice.pricePerPersonWithClubSpecialOffers < pricePerPersonWith…	?discountclub=
is_valid	BOOLEAN	JSONPath: au moins une occurrences[*] a bookingState ∈ {0, 2}	?hide_invalid=
is_seaside	BOOLEAN	isSeaside (injecté par le reindex sur la source /seaside)	?seaside=
travel_ranges	TEXT[]	JSONPath: $.travelRanges[*].slug	?category=
departure_dates	TIMESTAMP[]	JSONPath: $.occurrences[*].start	?dates=
search_vector	tsvector	Concaténation pondérée name (A) + subtitle (B) + description (C)	branche FTS du search

Fonctions helper

Définies dans migrations/0001_baseline.sql :

CREATE FUNCTION jsonb_to_text_array(j JSONB, path TEXT)
RETURNS TEXT[] AS $$
    SELECT ARRAY(
        SELECT elem
        FROM jsonb_array_elements_text(jsonb_path_query_array(j, path::jsonpath)) AS elem
    )
$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION jsonb_to_timestamp_array(j JSONB, path TEXT)
RETURNS TIMESTAMP[] AS $$
    SELECT ARRAY(
        SELECT (elem)::TIMESTAMP
        FROM jsonb_array_elements_text(jsonb_path_query_array(j, path::jsonpath)) AS elem
    )
$$ LANGUAGE SQL IMMUTABLE;

Marquées IMMUTABLE — c'est indispensable pour qu'elles soient utilisables dans une définition GENERATED ALWAYS AS ... STORED. Si vous changez la définition, vous devrez DROP puis recréer toutes les colonnes qui s'en servent (= une migration coûteuse).

Index

Créés dans migrations/0001_baseline.sql (puis 0004 pour is_seaside) :

Index	Type	Colonne(s)	Sert pour
(PK implicite)	btree	id	jointures, lookups
idx_travels_search_vector	GIN	search_vector	recherche FTS
idx_travels_destination	btree	destination	WHERE destination = ?
idx_travels_discount_club	btree	discount_club	WHERE discount_club = ?
idx_travels_is_valid	btree	is_valid	WHERE is_valid = TRUE
idx_travels_is_seaside	btree	is_seaside	WHERE is_seaside = ?
idx_travels_travel_ranges	GIN	travel_ranges	WHERE travel_ranges && ?
idx_travels_departure_dates	GIN	departure_dates	(peu utilisé en pratique)
idx_travels_raw_data	GIN	raw_data	requêtes ad-hoc sur le JSON brut

Pas d'index vectoriel ivfflat/hnsw sur embedding : le dataset est suffisamment petit (~quelques centaines de voyages) pour qu'un seq scan reste rapide. À reconsidérer si la table dépasse ~10k lignes.

Vue travels_view

CREATE VIEW travels_view AS
WITH with_departure_dates AS (
    SELECT
        raw_data, embedding, id, name, destination,
        discount_club, is_valid, is_seaside,
        travel_ranges, departure_dates, search_vector, updated_at,
        (SELECT MIN(d) FROM unnest(departure_dates) d
         WHERE d > CURRENT_DATE) AS next_departure
    FROM travels
)
SELECT * FROM with_departure_dates
WHERE next_departure > CURRENT_DATE;

Deux responsabilités :

1. Exposer next_departure (calculé) : le plus proche départ futur du voyage. Utilisé par le tri ?orderBy=departure et exposé dans la réponse JSON (next_departure → nextDeparture côté infoDensity=card).
2. Filtrer les voyages morts : un voyage dont toutes les departure_dates sont dans le passé n'a pas de next_departure → exclu de la vue.

Conséquence importante : tous les SELECT de recherche utilisent travels_view, pas travels. Un voyage présent dans travels mais sans départ futur est invisible côté API, même avec hide_invalid=false. C'est voulu : éviter d'afficher des voyages passés sans avoir besoin d'un filtre côté reindex.

Le reindex ne supprime PAS les voyages devenus inactifs. Il s'appuie sur deux mécanismes : (a) la vue qui les filtre, (b) le DELETE final qui supprime ceux absents de l'amont.

Historique des migrations

Toutes sous migrations/, appliquées dans l'ordre par yoyo (cf. migrations/0001_baseline.sql ligne -- depends:).

Fichier	Apporte
0001_baseline.sql	Extension vector, helpers JSONB, table travels, indexes, vue v1
0002_add_next_departure_view.sql	Vue v2 — calcule next_departure côté vue plutôt que colonne stored
0003_update_travels_view.sql	Vue v3 — ajoute le WHERE next_departure > CURRENT_DATE
0004_add_seaside_filter.sql	Colonne is_seaside + index + vue v4 incluant is_seaside

Pour les bases existantes pré-yoyo, marquer la baseline appliquée : yoyo mark 0001_baseline --database "$DATABASE_URL".

Voir modules/migrations.md pour le workflow concret d'ajout/exécution de migrations.

Anti-patterns à éviter

• Ne pas ajouter de colonnes non-générées dérivables du JSON. Toute donnée extraite de raw_data doit être recalculée à chaque update — c'est ce que fait GENERATED ALWAYS AS ... STORED. Une colonne « classique » qu'on remplit côté application est un bug futur.
• Ne pas insérer directement dans travels depuis du code de prod (autre que src/reindex.py). Le reindex est le seul writer.
• Ne pas requêter travels directement depuis le path de recherche — toujours travels_view. Sinon vous afficherez des voyages dont aucune date n'est future.
• Ne pas modifier jsonb_to_text_array ou jsonb_to_timestamp_array sans plan de migration : elles sont scellées dans la définition de colonnes générées.

Contributors

No contributors

Changelog

No recent changes