👤 Table users
id UUID PRIMARY KEY
email TEXT UNIQUE NOT NULL
username TEXT UNIQUE
display_name TEXT
avatar_url TEXT
bio TEXT
role ENUM(user,creator,mod,admin,super_admin)
trust_score INT DEFAULT 0
is_verified BOOLEAN DEFAULT false
country_code CHAR(2)
languages TEXT[]
traveler_type TEXT
ai_profile JSONB -- jumeau digital
privacy_settings JSONB
created_at TIMESTAMPTZ
📰 Table posts
id UUID PRIMARY KEY
author_id UUID → users
type ENUM(post,story,reel,itinerary)
content TEXT
media_urls TEXT[]
location_id UUID → places
cost DECIMAL
currency CHAR(3)
ai_generated BOOLEAN DEFAULT false
ai_confidence FLOAT -- détection IA
exif_verified BOOLEAN
remix_of UUID → posts
tags TEXT[]
visibility ENUM(public,followers,private)
likes_count INT DEFAULT 0
status ENUM(active,hidden,deleted)
published_at TIMESTAMPTZ
🗺️ Table places
id UUID PRIMARY KEY
name TEXT NOT NULL
type ENUM(city,country,neighborhood,venue,food)
lat DECIMAL
lng DECIMAL
country_code CHAR(2)
safety_score FLOAT -- 0-100
safety_updated_at TIMESTAMPTZ
avg_cost_usd DECIMAL
cover_photo TEXT
description TEXT
tags TEXT[]
geometry GEOMETRY(Point, 4326)
🛤️ Table itineraries
id UUID PRIMARY KEY
author_id UUID → users
title TEXT
description TEXT
duration_days INT
budget_usd DECIMAL
traveler_type TEXT
days JSONB[] -- [{day, activities[]}]
ai_generated BOOLEAN
avg_safety_score FLOAT
remixed_from UUID → itineraries
is_public BOOLEAN
saves_count INT DEFAULT 0
created_at TIMESTAMPTZ
🛡️ Table safety_alerts
id, place_id, level (1-5),
category (crime/weather/political/health),
title, description,
source_url, is_active,
ai_summary, starts_at, ends_at
💬 Table conversations
id, participants UUID[],
last_message_at,
ai_agent_active BOOLEAN,
ai_responses_count
→ messages(id, conversation_id,
sender_id, content,
is_ai_generated, read_at)
✈️ Table transport_alerts
id, user_id,
origin, destination,
type (flight/train/bus),
price_threshold_usd,
current_price,
is_active, last_checked,
notified_at