# GGQ — Itinéraires DB inventory (DB-first) + KG cross-reference

**Date (UTC):** 2026-01-22  
**Evidence posture:** DB is truth; KG is a convenience index / sanity-check (may drift).  
**Status:** DRAFT (publish-safe)

Context (project thread):
https://infrafabric.io/llm/platform/if-blackboard/project-threads-v1/project-thread-infra-guidesgq.md.txt

Public GGQ entrypoints:
https://infrafabric.io/ggq/
https://ww2.guidesgq.com/ggq/

---

## 0) Black/white (required)

**VERIFIED (from DB / exported TSV / KG build):** counts, record IDs, titles/slugs, destination↔establishment links (join tables / exports), and the published artifact files in this folder.

**INTERPRETATION:** what should be called an “Itinéraire”, what belongs in the top-nav, and the proposed IA / data model.

---

## 1) Inputs read

- `docs/ggq-itiniares-perplexity-research.md`

---

## 2) Constraints (non-negotiable)

- Read-only DB queries only.
- Do not paste or print secrets/credentials.
- Public artifacts avoid internal hostnames.
- HTML-first report + raw `.md` fallback.

---

## 3) Output artifacts (this folder)

- Candidate inventories (CSV):
  - `candidates_destinations_itinerary_named.csv`
  - `candidates_destinations_itinerary_named_establishments.csv`
  - `candidates_blog_posts_itinerary_titled.csv`
  - `candidates_blog_posts_itinerary_titled_establishments_summary.csv`
  - `candidates_blog_posts_itinerary_titled_establishments.csv`
  - `candidates_blog_posts_linked_to_itinerary_destinations.csv`
  - `candidates_blog_posts_enriched.csv` (keyword hits; noisy)
  - `candidates_tags.csv`
  - `candidates_pages.csv` (empty result)
  - `candidates_blog_categories.csv` (empty result)
- KG cross-reference summary:
  - `kg_cross_reference_summary.json`

---

## 4) DB inventory (source of truth)

### 4.1 Keyword scan results (required search terms)

**VERIFIED** counts:
- Pages matching itinerary terms: **0** (`candidates_pages.csv`)
- Blog categories matching itinerary terms: **0** (`candidates_blog_categories.csv`)
- Tags matching itinerary terms: **2** (`candidates_tags.csv`)
- Blog posts with keyword hits (title/body/slug/custom_url): **139** (`candidates_blog_posts_enriched.csv`)

Interpretation:
- There is **little/no explicit “Itinéraires” section content** expressed as pages/categories in the DB today.
- Keyword hits in blog post bodies appear **too noisy** to treat as “itinerary content” without additional filtering.

### 4.2 High-signal candidates: destinations already named as routes/circuits

**VERIFIED**: 3 destinations whose *names* already match Route/Circuit patterns:
- `destination:62` — Route des Vins de Brome-Missisquoi (11 linked establishments)
- `destination:234` — Circuit gourmand de l'Outaouais (15 linked establishments)
- `destination:48` — Circuit du Paysan (4 linked establishments)

See:
- `candidates_destinations_itinerary_named.csv`
- `candidates_destinations_itinerary_named_establishments.csv`

### 4.3 Candidate blog posts (title contains Route/Circuit/Itinéraire/Boucle/Parcours)

**VERIFIED**: 7 blog posts with itinerary-ish wording in the FR title (`candidates_blog_posts_itinerary_titled.csv`).

Notes:
- Some titles use “en route”/“parcours” in non-itinerary senses.
- One title candidate has **no destination links** (likely needs manual destination tagging to become useful):
  - `blog_post:217` — Sur la route de la Nouvelle-France (0 linked destinations)

See:
- `candidates_blog_posts_itinerary_titled.csv`
- `candidates_blog_posts_itinerary_titled_establishments_summary.csv`
- `candidates_blog_posts_itinerary_titled_establishments.csv`

### 4.4 Bootstrapping-friendly candidates (linked to itinerary destinations + “Vignobles et Vergers”)

**VERIFIED**: blog posts linked to the most itinerary-relevant destinations (48/62/234) plus `destination:53` (Vignobles et Vergers, referenced in the naming research doc):

- `blog_post:236` — La Route des vins de Brome-Missisquoi → `destination:62` (11 linked establishments)
- `blog_post:274` — Côté vignobles et vergers → `destination:53` (9 linked establishments)
- `blog_post:273` — Du paysan à la table → `destination:48` + `destination:53` (12 linked establishments)

See:
- `candidates_blog_posts_linked_to_itinerary_destinations.csv`

---

## 5) Establishment linking (required)

**VERIFIED (DB-first logic):**
- Blog post → destination links come from `refinery_blog_posts_destinations`.
- Destination → establishment links come from `refinery_destinations_destinations_inscriptions` (to `refinery_destinations_inscriptions`).

Per-candidate establishment lists:
- For itinerary-named destinations:
  - `candidates_destinations_itinerary_named_establishments.csv`
- For itinerary-titled blog posts:
  - `candidates_blog_posts_itinerary_titled_establishments.csv`

Interpretation:
- “Linked establishments” here means **establishments associated to the destination(s) tagged on the blog post**, not necessarily explicitly referenced in the article body.
- If the product goal is “curated routes”, a future iteration likely needs **direct blog_post↔establishment** relations or explicit body links.

---

## 6) Knowledge graph cross-reference (required section)

### 6.1 Build command (required verbatim)

`python3 scripts/build_if_knowledge_tabular_graph.py --config docs/data/ggq_tabular_ingest.json --out /root/tmp/ggq_graph.json`

### 6.2 Limitations (required)

**VERIFIED:** the KG is derived from exported TSV snapshots.

**INTERPRETATION:** because it is export-based, it may drift from the live DB; treat it as a convenience index, not truth.

### 6.3 Cross-reference results

**VERIFIED** examples from the KG:
- `blog_post:236` has `about_destination` edges to `destination:58..62` and reaches **25** establishments via `has_listing`.
- `blog_post:217` has **0** destination edges (candidate for missing-link cleanup).
- `destination:234` exists with **15** linked establishments, even though no linked blog post surfaced in this pass.

See:
- `kg_cross_reference_summary.json`

---

## 7) Bootstrapping proposal (INTERPRETATION)

### 7.1 IA suggestion

Top-nav item:
- **Itinéraires**

Landing page should start with:
- “Routes/Circuits” (promote existing destination records like Route des Vins / Circuit du Paysan)
- “Par thème” (gourmand, fleuve, bord de mer, urbain)
- “Par région” (reusing destinations taxonomy)

### 7.2 Minimum viable data model

Two viable paths:

**Option A (new type):** create a dedicated `Itinerary` content type with explicit relations:
- itinerary → destinations (ordered)
- itinerary → establishments (curated subset)
- itinerary → editorial blocks (markdown/html)

**Option B (convention over schema):** treat a subset of `refinery_destinations` as itineraries:
- “Itinerary destinations” = destinations whose names match Route/Circuit/Parcours patterns
- Use the destination’s establishments as default listing, with manual curation later
- Attach 1–N supporting blog posts (existing `blog_post ↔ destination` relation)

---

## 8) Verify (required)

### 8.1 DB inventory commands (include verbatim)

Candidate destinations (route/circuit names):
`psql --no-psqlrc --set ON_ERROR_STOP=1 -c "SELECT d.id, dt.locale, dt.name FROM refinery_destinations d JOIN refinery_destination_translations dt ON dt.refinery_destination_id=d.id AND dt.locale='fr' WHERE dt.name ILIKE '%route%' OR dt.name ILIKE '%circuit%' ORDER BY d.id;"`

Candidate blog posts (FR title contains route/circuit/itinéraire/boucle/parcours):
`psql --no-psqlrc --set ON_ERROR_STOP=1 -c "SELECT bpt.refinery_blog_post_id, bpt.locale, bpt.title, bpt.slug FROM refinery_blog_post_translations bpt WHERE COALESCE(bpt.title,'') ILIKE '%route%' OR COALESCE(bpt.title,'') ILIKE '%circuit%' OR COALESCE(bpt.title,'') ILIKE '%itinér%' OR COALESCE(bpt.title,'') ILIKE '%boucle%' OR COALESCE(bpt.title,'') ILIKE '%parcours%' ORDER BY bpt.refinery_blog_post_id;"`

### 8.2 KG build command (include verbatim)

`python3 scripts/build_if_knowledge_tabular_graph.py --config docs/data/ggq_tabular_ingest.json --out /root/tmp/ggq_graph.json`
