home_stock/docs/adr/0003-recherche-full-text-sqlite-fts5.md

ADR-0003 — Recherche full-text avec SQLite FTS5

• Statut : accepted
• Date : 2026-01-27

---

Contexte

HomeStock nécessite une fonctionnalité de recherche rapide et efficace pour retrouver des objets dans l'inventaire. Les utilisateurs doivent pouvoir rechercher par :

• Nom de l'objet (ex: "perceuse")
• Description (ex: "outil électrique sans fil")
• Catégorie (ex: "bricolage")
• Localisation (ex: "garage", "étagère A")

Contraintes identifiées :

• Volume de données : ~1000-5000 items attendus initialement, maximum 10000 items à long terme
• Performance : Résultats de recherche en <200ms souhaités
• Simplicité : Pas de serveur externe supplémentaire à gérer
• Pertinence : Recherche "fuzzy" pas nécessaire, recherche exacte par mots-clés suffit
• Langue : Recherche en français uniquement

Le choix de la solution de recherche impacte directement l'expérience utilisateur (fonctionnalité critique) et l'architecture technique (composant additionnel ou intégré).

---

Décision

Nous utilisons SQLite FTS5 (Full-Text Search 5) pour la recherche full-text avec les caractéristiques suivantes :

Configuration

• Table virtuelle FTS5 : fts_items contenant les champs indexés
• Champs indexés : Item.name, Item.description, Category.name, Location.path
• Tokenizer : unicode61 (support Unicode, casse insensible)
• Synchronisation : Triggers SQLite pour maintenir FTS5 à jour lors des INSERT/UPDATE/DELETE

Exemple de structure

CREATE VIRTUAL TABLE fts_items USING fts5(
    item_id UNINDEXED,
    name,
    description,
    category_name,
    location_path,
    tokenize = 'unicode61'
);

-- Triggers pour synchronisation automatique
CREATE TRIGGER items_after_insert AFTER INSERT ON items BEGIN
    INSERT INTO fts_items(item_id, name, description, category_name, location_path)
    VALUES (
        NEW.id,
        NEW.name,
        NEW.description,
        (SELECT name FROM categories WHERE id = NEW.category_id),
        (SELECT path FROM locations WHERE id = NEW.location_id)
    );
END;

-- Triggers similaires pour UPDATE et DELETE

Requêtes de recherche

-- Recherche simple
SELECT items.* FROM items
JOIN fts_items ON items.id = fts_items.item_id
WHERE fts_items MATCH 'perceuse';

-- Recherche avec boost (priorité sur le nom)
SELECT items.* FROM items
JOIN fts_items ON items.id = fts_items.item_id
WHERE fts_items MATCH '{name}: perceuse OR {description}: perceuse'
ORDER BY rank;

---

Alternatives considérées

1. Elasticsearch

Description : Moteur de recherche distribué dédié, standard de l'industrie

Avantages :

• ✅ Recherche extrêmement performante et flexible
• ✅ Recherche fuzzy, phonétique, synonymes
• ✅ Agrégations et analytics avancées
• ✅ Scalable horizontalement

Inconvénients :

• ❌ Serveur Java lourd (1-2GB RAM minimum)
• ❌ Complexité opérationnelle importante
• ❌ Nécessite synchronisation BDD → Elasticsearch
• ❌ Overhead massif pour 1000-10000 items
• ❌ Configuration complexe pour français

Verdict : ❌ Rejeté - Tuer une mouche avec un bazooka. Elasticsearch est conçu pour des millions de documents et des cas d'usage complexes (facettes, agrégations, etc.) dont HomeStock n'a pas besoin.

2. MeiliSearch

Description : Moteur de recherche moderne, léger, orienté UX

Avantages :

• ✅ Très rapide et pertinent out-of-the-box
• ✅ API REST simple
• ✅ Typo-tolerance native
• ✅ Plus léger qu'Elasticsearch (~50MB RAM)
• ✅ Configuration minimale

Inconvénients :

• ❌ Service externe supplémentaire à déployer
• ❌ Synchronisation BDD → MeiliSearch nécessaire
• ❌ Overhead pour petit volume de données
• ❌ Dépendance additionnelle à maintenir

Verdict : ⚠️ Rejeté mais intéressant - Excellente solution technique mais ajoute de la complexité pour un bénéfice limité sur <10k items. À considérer si passage à >50k items.

3. PostgreSQL Full-Text Search (pg_trgm + GIN)

Description : Recherche full-text native de PostgreSQL

Avantages :

• ✅ Très performant avec index GIN
• ✅ Recherche trigram (similitude)
• ✅ Support langues naturelles (stemming français)
• ✅ Pas de service externe

Inconvénients :

• ❌ Nécessite PostgreSQL (vs SQLite choisi dans ADR-0001)
• ❌ Serveur BDD à gérer
• ❌ Complexité setup (dictionnaires français, configuration)

Verdict : ❌ Rejeté - Excellente solution technique mais nécessite PostgreSQL. Si migration vers PostgreSQL (cas multi-utilisateurs), reconsidérer.

4. LIKE '%keyword%' en SQL

Description : Recherche basique avec opérateur LIKE

Avantages :

• ✅ Extrêmement simple
• ✅ Aucune dépendance

Inconvénients :

• ❌ Performance catastrophique (full table scan)
• ❌ Pas de pertinence/ranking
• ❌ Impossible de rechercher sur plusieurs champs efficacement
• ❌ Pas d'opérateurs (AND, OR, NOT)

Verdict : ❌ Rejeté - Inacceptable même pour 1000 items, expérience utilisateur dégradée

5. SQLite FTS5 (notre choix)

Description : Module de recherche full-text intégré à SQLite

Avantages :

• ✅ Intégré à SQLite, pas de service externe
• ✅ Performance excellente pour <100k documents
• ✅ Support opérateurs (AND, OR, NOT, NEAR, phrases)
• ✅ Ranking par pertinence (BM25)
• ✅ Triggers pour synchronisation automatique
• ✅ Tokenizer Unicode (support français)
• ✅ Footprint mémoire minimal

Inconvénients :

• ⚠️ Pas de recherche fuzzy native (typos)
• ⚠️ Pas de stemming français parfait
• ⚠️ Limitations si >100k documents (acceptable pour notre cas)

Verdict : ✅ Choisi - Solution optimale pour notre contexte : performance suffisante, simplicité maximale, pas de dépendance externe

---

Conséquences

Positives

1. Simplicité architecture : Pas de service externe, tout dans SQLite
2. Performance suffisante : FTS5 recherche <50ms sur 10k items
3. Synchronisation automatique : Triggers maintiennent l'index à jour
4. Zéro configuration : FTS5 inclus dans SQLite depuis version 3.9.0 (2015)
5. Backup simplifié : Index FTS5 dans le même fichier .db que les données
6. Opérateurs avancés : Support AND, OR, NOT, recherche par phrase
7. Ranking pertinent : Algorithme BM25 pour trier par pertinence

Négatives

1. Pas de typo-tolerance : "perceuze" ne trouvera pas "perceuse"
2. Stemming limité : "perçant" et "percer" non reliés automatiquement
3. Performance dégradée >100k items : Limite théorique (très au-delà de notre besoin)
4. Pas d'agrégations : Impossible de faire des facettes de recherche complexes
5. Recherche française limitée : Pas de dictionnaire français sophistiqué

Mitigations

• Typo-tolerance : Implémenter suggestions de correction côté application si besoin (Levenshtein distance)
• Stemming : Ajouter mots-clés/tags aux items pour améliorer découvrabilité
• Performance : Si >100k items, migrer vers PostgreSQL pg_trgm ou MeiliSearch
• Agrégations : Implémenter filtres côté application (par catégorie, localisation, etc.)

---

Impacts techniques

Développement

• Migration Alembic : Créer table virtuelle FTS5 + triggers lors du setup initial
• Service de recherche : search_service.py encapsule la logique FTS5
• API endpoint : GET /api/v1/search?q=keyword retourne items matchant

Performance attendue

• <1000 items : <10ms
• 1000-5000 items : <50ms
• 5000-10000 items : <150ms
• >10000 items : Dégrédation possible, monitoring nécessaire

Structure de code

# services/search_service.py
class SearchService:
    async def search_items(self, query: str, limit: int = 50):
        """Recherche full-text avec FTS5"""
        # Sanitize query (échapper caractères spéciaux)
        safe_query = self._escape_fts5_query(query)

        # Query FTS5 avec ranking
        sql = """
            SELECT items.*,
                   rank AS relevance
            FROM items
            JOIN fts_items ON items.id = fts_items.item_id
            WHERE fts_items MATCH :query
            ORDER BY rank
            LIMIT :limit
        """

        return await self.db.execute(sql, {"query": safe_query, "limit": limit})

Évolution future

Si les limitations deviennent bloquantes :

1. Migration PostgreSQL : Activer pg_trgm pour recherche similitude
2. Ajout MeiliSearch : Service séparé pour recherche avancée (garder SQLite pour données)
3. Hybrid search : Combiner FTS5 (rapide) + recherche sémantique externe (typos, synonymes)

---

Notes

SQLite FTS5 est utilisé par de nombreux projets à succès pour des cas d'usage similaires :

• Firefox : Recherche dans l'historique et favoris
• Apple Mail : Indexation locale des emails
• VS Code : Recherche dans l'historique de commandes

Pour HomeStock avec <10k items attendus, FTS5 est largement suffisant et offre le meilleur ratio performance/simplicité.

La décision de ne pas utiliser Elasticsearch ou MeiliSearch n'est pas dogmatique : si le projet évolue vers >50k items ou nécessite recherche fuzzy sophistiquée, migration possible sans refonte majeure (API de recherche reste identique, seule l'implémentation change).

Principe appliqué : "Use the simplest tool that works" - Ne pas sur-architecturer pour des besoins hypothétiques futurs.

---

Contributeurs : Gilles (décideur) + Claude Code (architecte)

Références :

• SQLite FTS5 documentation : https://www.sqlite.org/fts5.html
• BM25 ranking algorithm : https://en.wikipedia.org/wiki/Okapi_BM25