1444 lines
49 KiB
Markdown
1444 lines
49 KiB
Markdown
# Odoo Experts Directory System
|
||
## Final Technical Specification for Claude Code
|
||
|
||
---
|
||
|
||
# EXECUTIVE SUMMARY
|
||
|
||
## What We're Building
|
||
A scalable directory system that generates static XML/HTML pages for odoo-expertos.com, deployed via Git/Vercel. The system scrapes Odoo partners globally, enriches data, generates 800+ word unique content, and exports ready-to-publish static files.
|
||
|
||
## Key Decisions
|
||
| Aspect | Decision |
|
||
|--------|----------|
|
||
| Frontend | Integrate into odoo-expertos.com |
|
||
| Deployment | Static XML/HTML → Git → Vercel |
|
||
| Images | Logos + 3-5 scraped images per company |
|
||
| Review | Auto-publish, no human review |
|
||
| Refresh | One-time generation per company |
|
||
| Execution | Build system first, execute per-country on demand |
|
||
|
||
## Workflow
|
||
```
|
||
BUILD SYSTEM (once)
|
||
│
|
||
▼
|
||
PLAN COUNTRY (generate execution plan)
|
||
│
|
||
▼
|
||
EXECUTE COUNTRY (scrape → enrich → generate → export)
|
||
│
|
||
▼
|
||
GIT PUSH → VERCEL DEPLOYS
|
||
```
|
||
|
||
---
|
||
|
||
# PART 1: SYSTEM ARCHITECTURE
|
||
|
||
## 1.1 Component Overview
|
||
|
||
```
|
||
┌──────────────────────────────────────────────────────────────────┐
|
||
│ CLAUDE CODE ORCHESTRATOR │
|
||
│ │
|
||
│ Commands: │
|
||
│ • setup - Initialize Supabase, seed location data │
|
||
│ • plan [country] - Generate execution plan for a country │
|
||
│ • execute [plan] - Run full pipeline for planned country │
|
||
│ • export - Generate static XML/HTML files │
|
||
│ • push - Commit and push to Git │
|
||
└──────────────────────────────────────────────────────────────────┘
|
||
│
|
||
┌────────────────────┼────────────────────┐
|
||
│ │ │
|
||
▼ ▼ ▼
|
||
┌──────────┐ ┌──────────┐ ┌──────────┐
|
||
│ APIFY │ │FIRECRAWL │ │ HAIKU │
|
||
│ │ │ │ │ │
|
||
│ Google │ │ Website │ │ Content │
|
||
│ Maps │ │ Scraper │ │ Agent │
|
||
└──────────┘ └──────────┘ └──────────┘
|
||
│ │ │
|
||
└────────────────────┼────────────────────┘
|
||
│
|
||
▼
|
||
┌──────────────────┐
|
||
│ SUPABASE │
|
||
│ │
|
||
│ Central Data │
|
||
│ Store │
|
||
└──────────────────┘
|
||
│
|
||
▼
|
||
┌──────────────────┐
|
||
│ STATIC EXPORT │
|
||
│ │
|
||
│ XML/HTML files │
|
||
│ → Git → Vercel │
|
||
└──────────────────┘
|
||
```
|
||
|
||
## 1.2 Data Flow Per Country
|
||
|
||
```
|
||
PHASE 1: PLAN
|
||
─────────────
|
||
Input: Country code (e.g., "DE")
|
||
Output: Execution plan in Supabase
|
||
• List of cities to scrape
|
||
• Estimated company count
|
||
• Search queries per city
|
||
• Priority order
|
||
|
||
PHASE 2: SCRAPE
|
||
───────────────
|
||
For each city in plan:
|
||
│
|
||
├─► Apify Google Maps
|
||
│ • Company name, website, phone, rating, address
|
||
│
|
||
└─► Save to `companies` table (status: 'scraped')
|
||
|
||
PHASE 3: ENRICH
|
||
───────────────
|
||
For each scraped company:
|
||
│
|
||
├─► Firecrawl website
|
||
│ • Full markdown content
|
||
│ • Extracted: logo, images, about, services
|
||
│
|
||
├─► Download & store images (3-5 per company)
|
||
│ • Logo (required)
|
||
│ • Hero/banner image
|
||
│ • Team/office photos
|
||
│ • Project screenshots
|
||
│
|
||
├─► Haiku: Extract tags
|
||
│ • Services, industries, modules
|
||
│ • Partner level, team size
|
||
│
|
||
└─► Save enriched data (status: 'enriched')
|
||
|
||
PHASE 4: GENERATE CONTENT
|
||
─────────────────────────
|
||
For each enriched company:
|
||
│
|
||
├─► Haiku: Generate 800+ words
|
||
│ • Overview, expertise, services
|
||
│ • Industries, local value, FAQ
|
||
│
|
||
└─► Save content (status: 'content_ready')
|
||
|
||
For each city with ready companies:
|
||
│
|
||
└─► Haiku: Generate city hub content (1000+ words)
|
||
|
||
For country:
|
||
│
|
||
└─► Haiku: Generate country hub content (1200+ words)
|
||
|
||
PHASE 5: EXPORT
|
||
───────────────
|
||
Generate static files:
|
||
│
|
||
├─► Company XML pages
|
||
├─► City hub XML pages
|
||
├─► Country hub XML page
|
||
├─► Sitemap XML
|
||
└─► Image assets (copied to static folder)
|
||
|
||
PHASE 6: DEPLOY
|
||
───────────────
|
||
│
|
||
├─► Git commit all generated files
|
||
├─► Git push to remote
|
||
└─► Vercel auto-deploys
|
||
```
|
||
|
||
---
|
||
|
||
# PART 2: SUPABASE SCHEMA
|
||
|
||
```sql
|
||
-- =============================================
|
||
-- ENABLE EXTENSIONS
|
||
-- =============================================
|
||
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
||
|
||
-- =============================================
|
||
-- LOCATION HIERARCHY
|
||
-- =============================================
|
||
|
||
CREATE TABLE regions (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
slug VARCHAR(50) UNIQUE NOT NULL,
|
||
name VARCHAR(100) NOT NULL,
|
||
name_localized JSONB DEFAULT '{}',
|
||
|
||
-- Stats (auto-updated via triggers)
|
||
country_count INTEGER DEFAULT 0,
|
||
company_count INTEGER DEFAULT 0,
|
||
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
CREATE TABLE countries (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
region_id UUID REFERENCES regions(id),
|
||
|
||
code CHAR(2) UNIQUE NOT NULL,
|
||
slug VARCHAR(50) UNIQUE NOT NULL,
|
||
name VARCHAR(100) NOT NULL,
|
||
name_local VARCHAR(100),
|
||
|
||
-- Language for content generation
|
||
content_language VARCHAR(10) NOT NULL DEFAULT 'en',
|
||
|
||
-- Generated content (filled during execution)
|
||
meta_title VARCHAR(70),
|
||
meta_description VARCHAR(160),
|
||
content_intro TEXT,
|
||
content_market_overview TEXT,
|
||
content_faq JSONB,
|
||
|
||
-- Stats
|
||
city_count INTEGER DEFAULT 0,
|
||
company_count INTEGER DEFAULT 0,
|
||
|
||
-- Execution status
|
||
status VARCHAR(30) DEFAULT 'pending',
|
||
-- pending → planned → scraping → enriching → generating → exported → published
|
||
planned_at TIMESTAMPTZ,
|
||
executed_at TIMESTAMPTZ,
|
||
exported_at TIMESTAMPTZ,
|
||
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
CREATE TABLE cities (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
country_id UUID REFERENCES countries(id),
|
||
|
||
slug VARCHAR(100) NOT NULL,
|
||
name VARCHAR(100) NOT NULL,
|
||
name_ascii VARCHAR(100), -- Alternative ASCII name for URLs
|
||
|
||
-- Search configuration
|
||
search_queries JSONB, -- Customized search queries for this city
|
||
|
||
-- Generated content
|
||
meta_title VARCHAR(70),
|
||
meta_description VARCHAR(160),
|
||
h1_title VARCHAR(100),
|
||
content_intro TEXT,
|
||
content_services TEXT,
|
||
content_how_to_choose TEXT,
|
||
content_faq JSONB,
|
||
|
||
-- Stats
|
||
company_count INTEGER DEFAULT 0,
|
||
avg_rating DECIMAL(2,1),
|
||
|
||
-- Execution status
|
||
status VARCHAR(30) DEFAULT 'pending',
|
||
-- pending → scraping → scraped → enriching → content_generating → ready
|
||
last_scraped_at TIMESTAMPTZ,
|
||
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
||
UNIQUE(country_id, slug)
|
||
);
|
||
|
||
-- =============================================
|
||
-- COMPANIES
|
||
-- =============================================
|
||
|
||
CREATE TABLE companies (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
city_id UUID REFERENCES cities(id),
|
||
|
||
-- Identifiers
|
||
slug VARCHAR(255) UNIQUE NOT NULL,
|
||
google_place_id VARCHAR(255) UNIQUE,
|
||
|
||
-- Basic Info (from Google Maps)
|
||
name VARCHAR(255) NOT NULL,
|
||
website VARCHAR(500),
|
||
phone VARCHAR(50),
|
||
address TEXT,
|
||
latitude DECIMAL(10, 8),
|
||
longitude DECIMAL(11, 8),
|
||
google_rating DECIMAL(2, 1),
|
||
google_reviews_count INTEGER,
|
||
google_url TEXT,
|
||
|
||
-- Contact (extracted from website)
|
||
email VARCHAR(255),
|
||
|
||
-- Odoo-Specific (extracted)
|
||
odoo_partner_level VARCHAR(50),
|
||
founded_year INTEGER,
|
||
team_size_range VARCHAR(50),
|
||
|
||
-- Raw scraped data (for debugging/reprocessing)
|
||
raw_google_data JSONB,
|
||
raw_website_markdown TEXT,
|
||
raw_firecrawl_extract JSONB,
|
||
|
||
-- Processing status
|
||
status VARCHAR(30) DEFAULT 'scraped',
|
||
-- scraped → enriching → enriched → generating → content_ready → exported
|
||
processing_error TEXT,
|
||
|
||
-- Timestamps
|
||
scraped_at TIMESTAMPTZ DEFAULT NOW(),
|
||
enriched_at TIMESTAMPTZ,
|
||
content_generated_at TIMESTAMPTZ,
|
||
exported_at TIMESTAMPTZ,
|
||
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
-- =============================================
|
||
-- IMAGES
|
||
-- =============================================
|
||
|
||
CREATE TABLE company_images (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
company_id UUID REFERENCES companies(id) ON DELETE CASCADE,
|
||
|
||
-- Image type
|
||
type VARCHAR(50) NOT NULL, -- 'logo', 'hero', 'team', 'office', 'project', 'screenshot'
|
||
|
||
-- URLs
|
||
source_url TEXT NOT NULL, -- Original URL from website
|
||
stored_path VARCHAR(500), -- Path in our static folder (e.g., /images/companies/{slug}/logo.webp)
|
||
|
||
-- Metadata
|
||
alt_text VARCHAR(255),
|
||
width INTEGER,
|
||
height INTEGER,
|
||
file_size INTEGER,
|
||
|
||
-- Processing
|
||
is_processed BOOLEAN DEFAULT FALSE,
|
||
processing_error TEXT,
|
||
|
||
-- Order (for display)
|
||
display_order INTEGER DEFAULT 0,
|
||
|
||
created_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
-- =============================================
|
||
-- TAGGING SYSTEM
|
||
-- =============================================
|
||
|
||
CREATE TABLE tags (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
type VARCHAR(50) NOT NULL,
|
||
slug VARCHAR(100) NOT NULL,
|
||
name VARCHAR(100) NOT NULL,
|
||
name_localized JSONB DEFAULT '{}',
|
||
|
||
UNIQUE(type, slug)
|
||
);
|
||
|
||
CREATE TABLE company_tags (
|
||
company_id UUID REFERENCES companies(id) ON DELETE CASCADE,
|
||
tag_id UUID REFERENCES tags(id) ON DELETE CASCADE,
|
||
confidence DECIMAL(3,2) DEFAULT 1.0,
|
||
source VARCHAR(50) DEFAULT 'extracted',
|
||
|
||
PRIMARY KEY (company_id, tag_id)
|
||
);
|
||
|
||
-- =============================================
|
||
-- GENERATED CONTENT
|
||
-- =============================================
|
||
|
||
CREATE TABLE company_content (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
company_id UUID REFERENCES companies(id) ON DELETE CASCADE UNIQUE,
|
||
|
||
-- Content sections
|
||
content_overview TEXT, -- 200-250 words
|
||
content_expertise TEXT, -- 200-250 words
|
||
content_services TEXT, -- 150-200 words
|
||
content_industries TEXT, -- 150-200 words
|
||
content_local_value TEXT, -- 100-150 words
|
||
content_ideal_client TEXT, -- 80-120 words
|
||
content_faq JSONB, -- [{question, answer}, ...]
|
||
|
||
-- SEO
|
||
meta_title VARCHAR(70),
|
||
meta_description VARCHAR(160),
|
||
|
||
-- Metrics
|
||
total_word_count INTEGER,
|
||
|
||
-- Generation metadata
|
||
model_used VARCHAR(50),
|
||
prompt_version VARCHAR(20),
|
||
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ DEFAULT NOW()
|
||
);
|
||
|
||
-- =============================================
|
||
-- EXECUTION PLANS
|
||
-- =============================================
|
||
|
||
CREATE TABLE execution_plans (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
country_id UUID REFERENCES countries(id),
|
||
|
||
-- Plan details
|
||
plan_name VARCHAR(100), -- e.g., "Germany Full Execution"
|
||
cities_planned JSONB, -- [{city_id, name, priority, estimated_companies}, ...]
|
||
total_cities INTEGER,
|
||
estimated_companies INTEGER,
|
||
|
||
-- Status
|
||
status VARCHAR(30) DEFAULT 'planned',
|
||
-- planned → executing → completed → failed
|
||
|
||
-- Progress tracking
|
||
cities_completed INTEGER DEFAULT 0,
|
||
companies_scraped INTEGER DEFAULT 0,
|
||
companies_enriched INTEGER DEFAULT 0,
|
||
companies_content_ready INTEGER DEFAULT 0,
|
||
|
||
-- Timing
|
||
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
started_at TIMESTAMPTZ,
|
||
completed_at TIMESTAMPTZ,
|
||
|
||
-- Errors
|
||
errors JSONB DEFAULT '[]'
|
||
);
|
||
|
||
-- =============================================
|
||
-- INDEXES
|
||
-- =============================================
|
||
|
||
CREATE INDEX idx_companies_city ON companies(city_id);
|
||
CREATE INDEX idx_companies_status ON companies(status);
|
||
CREATE INDEX idx_cities_country ON cities(country_id);
|
||
CREATE INDEX idx_cities_status ON cities(status);
|
||
CREATE INDEX idx_countries_status ON countries(status);
|
||
CREATE INDEX idx_company_images_company ON company_images(company_id);
|
||
CREATE INDEX idx_company_tags_company ON company_tags(company_id);
|
||
|
||
-- =============================================
|
||
-- SEED DATA: TAGS
|
||
-- =============================================
|
||
|
||
INSERT INTO tags (type, slug, name, name_localized) VALUES
|
||
-- Services
|
||
('service', 'implementation', 'Implementation', '{"de": "Implementierung", "es": "Implementación", "pt": "Implementação"}'),
|
||
('service', 'customization', 'Customization', '{"de": "Anpassung", "es": "Personalización", "pt": "Personalização"}'),
|
||
('service', 'migration', 'Migration', '{"de": "Migration", "es": "Migración", "pt": "Migração"}'),
|
||
('service', 'training', 'Training', '{"de": "Schulung", "es": "Capacitación", "pt": "Treinamento"}'),
|
||
('service', 'support', 'Support & Maintenance', '{"de": "Support & Wartung", "es": "Soporte y Mantenimiento", "pt": "Suporte e Manutenção"}'),
|
||
('service', 'hosting', 'Hosting', '{"de": "Hosting", "es": "Hosting", "pt": "Hospedagem"}'),
|
||
('service', 'consulting', 'Consulting', '{"de": "Beratung", "es": "Consultoría", "pt": "Consultoria"}'),
|
||
('service', 'development', 'Custom Development', '{"de": "Entwicklung", "es": "Desarrollo", "pt": "Desenvolvimento"}'),
|
||
('service', 'integration', 'Integration', '{"de": "Integration", "es": "Integración", "pt": "Integração"}'),
|
||
|
||
-- Industries
|
||
('industry', 'manufacturing', 'Manufacturing', '{"de": "Fertigung", "es": "Manufactura", "pt": "Manufatura"}'),
|
||
('industry', 'retail', 'Retail', '{"de": "Einzelhandel", "es": "Retail", "pt": "Varejo"}'),
|
||
('industry', 'ecommerce', 'E-Commerce', '{"de": "E-Commerce", "es": "E-Commerce", "pt": "E-Commerce"}'),
|
||
('industry', 'wholesale', 'Wholesale & Distribution', '{"de": "Großhandel", "es": "Mayorista", "pt": "Atacado"}'),
|
||
('industry', 'healthcare', 'Healthcare', '{"de": "Gesundheitswesen", "es": "Salud", "pt": "Saúde"}'),
|
||
('industry', 'logistics', 'Logistics & Transport', '{"de": "Logistik", "es": "Logística", "pt": "Logística"}'),
|
||
('industry', 'services', 'Professional Services', '{"de": "Dienstleistungen", "es": "Servicios", "pt": "Serviços"}'),
|
||
('industry', 'construction', 'Construction', '{"de": "Bauwesen", "es": "Construcción", "pt": "Construção"}'),
|
||
('industry', 'food', 'Food & Beverage', '{"de": "Lebensmittel", "es": "Alimentos", "pt": "Alimentos"}'),
|
||
('industry', 'automotive', 'Automotive', '{"de": "Automobil", "es": "Automotriz", "pt": "Automotivo"}'),
|
||
('industry', 'technology', 'Technology', '{"de": "Technologie", "es": "Tecnología", "pt": "Tecnologia"}'),
|
||
('industry', 'education', 'Education', '{"de": "Bildung", "es": "Educación", "pt": "Educação"}'),
|
||
|
||
-- Odoo Modules
|
||
('module', 'accounting', 'Accounting', '{"de": "Buchhaltung", "es": "Contabilidad", "pt": "Contabilidade"}'),
|
||
('module', 'inventory', 'Inventory', '{"de": "Lagerverwaltung", "es": "Inventario", "pt": "Inventário"}'),
|
||
('module', 'crm', 'CRM', NULL),
|
||
('module', 'sales', 'Sales', '{"de": "Vertrieb", "es": "Ventas", "pt": "Vendas"}'),
|
||
('module', 'purchase', 'Purchase', '{"de": "Einkauf", "es": "Compras", "pt": "Compras"}'),
|
||
('module', 'mrp', 'Manufacturing (MRP)', '{"de": "Fertigung", "es": "Fabricación", "pt": "Manufatura"}'),
|
||
('module', 'hr', 'Human Resources', '{"de": "Personal", "es": "RRHH", "pt": "RH"}'),
|
||
('module', 'project', 'Project Management', '{"de": "Projekt", "es": "Proyectos", "pt": "Projetos"}'),
|
||
('module', 'website', 'Website & eCommerce', NULL),
|
||
('module', 'pos', 'Point of Sale', '{"de": "Kasse", "es": "TPV", "pt": "PDV"}'),
|
||
('module', 'helpdesk', 'Helpdesk', NULL),
|
||
('module', 'fsm', 'Field Service', NULL),
|
||
|
||
-- Odoo Versions
|
||
('version', '15', 'Odoo 15', NULL),
|
||
('version', '16', 'Odoo 16', NULL),
|
||
('version', '17', 'Odoo 17', NULL),
|
||
('version', '18', 'Odoo 18', NULL),
|
||
|
||
-- Partner Levels
|
||
('partner-level', 'learning', 'Learning Partner', NULL),
|
||
('partner-level', 'ready', 'Ready Partner', NULL),
|
||
('partner-level', 'silver', 'Silver Partner', NULL),
|
||
('partner-level', 'gold', 'Gold Partner', NULL),
|
||
|
||
-- Languages
|
||
('language', 'de', 'German', '{"de": "Deutsch"}'),
|
||
('language', 'en', 'English', '{"de": "Englisch"}'),
|
||
('language', 'es', 'Spanish', '{"de": "Spanisch"}'),
|
||
('language', 'pt', 'Portuguese', '{"de": "Portugiesisch"}'),
|
||
('language', 'fr', 'French', '{"de": "Französisch"}')
|
||
ON CONFLICT (type, slug) DO NOTHING;
|
||
|
||
-- =============================================
|
||
-- SEED DATA: REGIONS
|
||
-- =============================================
|
||
|
||
INSERT INTO regions (slug, name, name_localized) VALUES
|
||
('dach', 'DACH', '{"de": "DACH", "en": "DACH (Germany, Austria, Switzerland)"}'),
|
||
('latam', 'Latin America', '{"es": "Latinoamérica", "en": "Latin America"}'),
|
||
('brazil', 'Brazil & Portugal', '{"pt": "Brasil e Portugal", "en": "Brazil & Portugal"}'),
|
||
('mena', 'Middle East', '{"en": "Middle East & North Africa", "ar": "الشرق الأوسط"}')
|
||
ON CONFLICT (slug) DO NOTHING;
|
||
|
||
-- =============================================
|
||
-- SEED DATA: COUNTRIES
|
||
-- =============================================
|
||
|
||
INSERT INTO countries (code, slug, name, name_local, content_language, region_id) VALUES
|
||
-- DACH
|
||
('DE', 'germany', 'Germany', 'Deutschland', 'de', (SELECT id FROM regions WHERE slug = 'dach')),
|
||
('AT', 'austria', 'Austria', 'Österreich', 'de', (SELECT id FROM regions WHERE slug = 'dach')),
|
||
('CH', 'switzerland', 'Switzerland', 'Schweiz', 'de', (SELECT id FROM regions WHERE slug = 'dach')),
|
||
|
||
-- LATAM
|
||
('ES', 'spain', 'Spain', 'España', 'es', (SELECT id FROM regions WHERE slug = 'latam')),
|
||
('MX', 'mexico', 'Mexico', 'México', 'es', (SELECT id FROM regions WHERE slug = 'latam')),
|
||
('CO', 'colombia', 'Colombia', 'Colombia', 'es', (SELECT id FROM regions WHERE slug = 'latam')),
|
||
('AR', 'argentina', 'Argentina', 'Argentina', 'es', (SELECT id FROM regions WHERE slug = 'latam')),
|
||
('CL', 'chile', 'Chile', 'Chile', 'es', (SELECT id FROM regions WHERE slug = 'latam')),
|
||
('PE', 'peru', 'Peru', 'Perú', 'es', (SELECT id FROM regions WHERE slug = 'latam')),
|
||
('EC', 'ecuador', 'Ecuador', 'Ecuador', 'es', (SELECT id FROM regions WHERE slug = 'latam')),
|
||
('VE', 'venezuela', 'Venezuela', 'Venezuela', 'es', (SELECT id FROM regions WHERE slug = 'latam')),
|
||
('UY', 'uruguay', 'Uruguay', 'Uruguay', 'es', (SELECT id FROM regions WHERE slug = 'latam')),
|
||
('PY', 'paraguay', 'Paraguay', 'Paraguay', 'es', (SELECT id FROM regions WHERE slug = 'latam')),
|
||
('BO', 'bolivia', 'Bolivia', 'Bolivia', 'es', (SELECT id FROM regions WHERE slug = 'latam')),
|
||
('CR', 'costa-rica', 'Costa Rica', 'Costa Rica', 'es', (SELECT id FROM regions WHERE slug = 'latam')),
|
||
('PA', 'panama', 'Panama', 'Panamá', 'es', (SELECT id FROM regions WHERE slug = 'latam')),
|
||
('DO', 'dominican-republic', 'Dominican Republic', 'República Dominicana', 'es', (SELECT id FROM regions WHERE slug = 'latam')),
|
||
('GT', 'guatemala', 'Guatemala', 'Guatemala', 'es', (SELECT id FROM regions WHERE slug = 'latam')),
|
||
|
||
-- Brazil
|
||
('BR', 'brazil', 'Brazil', 'Brasil', 'pt', (SELECT id FROM regions WHERE slug = 'brazil')),
|
||
('PT', 'portugal', 'Portugal', 'Portugal', 'pt', (SELECT id FROM regions WHERE slug = 'brazil')),
|
||
|
||
-- MENA
|
||
('AE', 'uae', 'United Arab Emirates', 'الإمارات', 'en', (SELECT id FROM regions WHERE slug = 'mena')),
|
||
('SA', 'saudi-arabia', 'Saudi Arabia', 'السعودية', 'en', (SELECT id FROM regions WHERE slug = 'mena')),
|
||
('EG', 'egypt', 'Egypt', 'مصر', 'en', (SELECT id FROM regions WHERE slug = 'mena')),
|
||
('MA', 'morocco', 'Morocco', 'المغرب', 'fr', (SELECT id FROM regions WHERE slug = 'mena')),
|
||
('JO', 'jordan', 'Jordan', 'الأردن', 'en', (SELECT id FROM regions WHERE slug = 'mena')),
|
||
('LB', 'lebanon', 'Lebanon', 'لبنان', 'en', (SELECT id FROM regions WHERE slug = 'mena')),
|
||
('KW', 'kuwait', 'Kuwait', 'الكويت', 'en', (SELECT id FROM regions WHERE slug = 'mena')),
|
||
('QA', 'qatar', 'Qatar', 'قطر', 'en', (SELECT id FROM regions WHERE slug = 'mena')),
|
||
('BH', 'bahrain', 'Bahrain', 'البحرين', 'en', (SELECT id FROM regions WHERE slug = 'mena')),
|
||
('OM', 'oman', 'Oman', 'عمان', 'en', (SELECT id FROM regions WHERE slug = 'mena'))
|
||
ON CONFLICT (code) DO NOTHING;
|
||
|
||
-- =============================================
|
||
-- SEED DATA: CITIES
|
||
-- =============================================
|
||
|
||
-- Germany
|
||
INSERT INTO cities (country_id, slug, name, name_ascii) VALUES
|
||
((SELECT id FROM countries WHERE code = 'DE'), 'berlin', 'Berlin', NULL),
|
||
((SELECT id FROM countries WHERE code = 'DE'), 'hamburg', 'Hamburg', NULL),
|
||
((SELECT id FROM countries WHERE code = 'DE'), 'muenchen', 'München', 'Munich'),
|
||
((SELECT id FROM countries WHERE code = 'DE'), 'koeln', 'Köln', 'Cologne'),
|
||
((SELECT id FROM countries WHERE code = 'DE'), 'frankfurt', 'Frankfurt am Main', 'Frankfurt'),
|
||
((SELECT id FROM countries WHERE code = 'DE'), 'stuttgart', 'Stuttgart', NULL),
|
||
((SELECT id FROM countries WHERE code = 'DE'), 'duesseldorf', 'Düsseldorf', 'Dusseldorf'),
|
||
((SELECT id FROM countries WHERE code = 'DE'), 'leipzig', 'Leipzig', NULL),
|
||
((SELECT id FROM countries WHERE code = 'DE'), 'dortmund', 'Dortmund', NULL),
|
||
((SELECT id FROM countries WHERE code = 'DE'), 'essen', 'Essen', NULL),
|
||
((SELECT id FROM countries WHERE code = 'DE'), 'bremen', 'Bremen', NULL),
|
||
((SELECT id FROM countries WHERE code = 'DE'), 'dresden', 'Dresden', NULL),
|
||
((SELECT id FROM countries WHERE code = 'DE'), 'hannover', 'Hannover', 'Hanover'),
|
||
((SELECT id FROM countries WHERE code = 'DE'), 'nuernberg', 'Nürnberg', 'Nuremberg'),
|
||
((SELECT id FROM countries WHERE code = 'DE'), 'duisburg', 'Duisburg', NULL),
|
||
((SELECT id FROM countries WHERE code = 'DE'), 'bochum', 'Bochum', NULL),
|
||
((SELECT id FROM countries WHERE code = 'DE'), 'wuppertal', 'Wuppertal', NULL),
|
||
((SELECT id FROM countries WHERE code = 'DE'), 'bielefeld', 'Bielefeld', NULL),
|
||
((SELECT id FROM countries WHERE code = 'DE'), 'bonn', 'Bonn', NULL),
|
||
((SELECT id FROM countries WHERE code = 'DE'), 'muenster', 'Münster', 'Munster')
|
||
ON CONFLICT (country_id, slug) DO NOTHING;
|
||
|
||
-- Austria
|
||
INSERT INTO cities (country_id, slug, name, name_ascii) VALUES
|
||
((SELECT id FROM countries WHERE code = 'AT'), 'wien', 'Wien', 'Vienna'),
|
||
((SELECT id FROM countries WHERE code = 'AT'), 'graz', 'Graz', NULL),
|
||
((SELECT id FROM countries WHERE code = 'AT'), 'linz', 'Linz', NULL),
|
||
((SELECT id FROM countries WHERE code = 'AT'), 'salzburg', 'Salzburg', NULL),
|
||
((SELECT id FROM countries WHERE code = 'AT'), 'innsbruck', 'Innsbruck', NULL),
|
||
((SELECT id FROM countries WHERE code = 'AT'), 'klagenfurt', 'Klagenfurt', NULL)
|
||
ON CONFLICT (country_id, slug) DO NOTHING;
|
||
|
||
-- Switzerland
|
||
INSERT INTO cities (country_id, slug, name, name_ascii) VALUES
|
||
((SELECT id FROM countries WHERE code = 'CH'), 'zuerich', 'Zürich', 'Zurich'),
|
||
((SELECT id FROM countries WHERE code = 'CH'), 'genf', 'Genf', 'Geneva'),
|
||
((SELECT id FROM countries WHERE code = 'CH'), 'basel', 'Basel', NULL),
|
||
((SELECT id FROM countries WHERE code = 'CH'), 'bern', 'Bern', NULL),
|
||
((SELECT id FROM countries WHERE code = 'CH'), 'lausanne', 'Lausanne', NULL)
|
||
ON CONFLICT (country_id, slug) DO NOTHING;
|
||
|
||
-- Spain
|
||
INSERT INTO cities (country_id, slug, name) VALUES
|
||
((SELECT id FROM countries WHERE code = 'ES'), 'madrid', 'Madrid'),
|
||
((SELECT id FROM countries WHERE code = 'ES'), 'barcelona', 'Barcelona'),
|
||
((SELECT id FROM countries WHERE code = 'ES'), 'valencia', 'Valencia'),
|
||
((SELECT id FROM countries WHERE code = 'ES'), 'sevilla', 'Sevilla'),
|
||
((SELECT id FROM countries WHERE code = 'ES'), 'bilbao', 'Bilbao'),
|
||
((SELECT id FROM countries WHERE code = 'ES'), 'malaga', 'Málaga'),
|
||
((SELECT id FROM countries WHERE code = 'ES'), 'zaragoza', 'Zaragoza')
|
||
ON CONFLICT (country_id, slug) DO NOTHING;
|
||
|
||
-- Mexico
|
||
INSERT INTO cities (country_id, slug, name, name_ascii) VALUES
|
||
((SELECT id FROM countries WHERE code = 'MX'), 'cdmx', 'Ciudad de México', 'Mexico City'),
|
||
((SELECT id FROM countries WHERE code = 'MX'), 'guadalajara', 'Guadalajara', NULL),
|
||
((SELECT id FROM countries WHERE code = 'MX'), 'monterrey', 'Monterrey', NULL),
|
||
((SELECT id FROM countries WHERE code = 'MX'), 'puebla', 'Puebla', NULL),
|
||
((SELECT id FROM countries WHERE code = 'MX'), 'tijuana', 'Tijuana', NULL),
|
||
((SELECT id FROM countries WHERE code = 'MX'), 'leon', 'León', 'Leon'),
|
||
((SELECT id FROM countries WHERE code = 'MX'), 'queretaro', 'Querétaro', 'Queretaro')
|
||
ON CONFLICT (country_id, slug) DO NOTHING;
|
||
|
||
-- Colombia
|
||
INSERT INTO cities (country_id, slug, name) VALUES
|
||
((SELECT id FROM countries WHERE code = 'CO'), 'bogota', 'Bogotá'),
|
||
((SELECT id FROM countries WHERE code = 'CO'), 'medellin', 'Medellín'),
|
||
((SELECT id FROM countries WHERE code = 'CO'), 'cali', 'Cali'),
|
||
((SELECT id FROM countries WHERE code = 'CO'), 'barranquilla', 'Barranquilla'),
|
||
((SELECT id FROM countries WHERE code = 'CO'), 'cartagena', 'Cartagena')
|
||
ON CONFLICT (country_id, slug) DO NOTHING;
|
||
|
||
-- Argentina
|
||
INSERT INTO cities (country_id, slug, name) VALUES
|
||
((SELECT id FROM countries WHERE code = 'AR'), 'buenos-aires', 'Buenos Aires'),
|
||
((SELECT id FROM countries WHERE code = 'AR'), 'cordoba', 'Córdoba'),
|
||
((SELECT id FROM countries WHERE code = 'AR'), 'rosario', 'Rosario'),
|
||
((SELECT id FROM countries WHERE code = 'AR'), 'mendoza', 'Mendoza')
|
||
ON CONFLICT (country_id, slug) DO NOTHING;
|
||
|
||
-- Brazil
|
||
INSERT INTO cities (country_id, slug, name, name_ascii) VALUES
|
||
((SELECT id FROM countries WHERE code = 'BR'), 'sao-paulo', 'São Paulo', 'Sao Paulo'),
|
||
((SELECT id FROM countries WHERE code = 'BR'), 'rio-de-janeiro', 'Rio de Janeiro', NULL),
|
||
((SELECT id FROM countries WHERE code = 'BR'), 'brasilia', 'Brasília', 'Brasilia'),
|
||
((SELECT id FROM countries WHERE code = 'BR'), 'salvador', 'Salvador', NULL),
|
||
((SELECT id FROM countries WHERE code = 'BR'), 'belo-horizonte', 'Belo Horizonte', NULL),
|
||
((SELECT id FROM countries WHERE code = 'BR'), 'fortaleza', 'Fortaleza', NULL),
|
||
((SELECT id FROM countries WHERE code = 'BR'), 'curitiba', 'Curitiba', NULL),
|
||
((SELECT id FROM countries WHERE code = 'BR'), 'recife', 'Recife', NULL),
|
||
((SELECT id FROM countries WHERE code = 'BR'), 'porto-alegre', 'Porto Alegre', NULL)
|
||
ON CONFLICT (country_id, slug) DO NOTHING;
|
||
|
||
-- Portugal
|
||
INSERT INTO cities (country_id, slug, name) VALUES
|
||
((SELECT id FROM countries WHERE code = 'PT'), 'lisboa', 'Lisboa'),
|
||
((SELECT id FROM countries WHERE code = 'PT'), 'porto', 'Porto'),
|
||
((SELECT id FROM countries WHERE code = 'PT'), 'braga', 'Braga')
|
||
ON CONFLICT (country_id, slug) DO NOTHING;
|
||
|
||
-- UAE
|
||
INSERT INTO cities (country_id, slug, name) VALUES
|
||
((SELECT id FROM countries WHERE code = 'AE'), 'dubai', 'Dubai'),
|
||
((SELECT id FROM countries WHERE code = 'AE'), 'abu-dhabi', 'Abu Dhabi'),
|
||
((SELECT id FROM countries WHERE code = 'AE'), 'sharjah', 'Sharjah')
|
||
ON CONFLICT (country_id, slug) DO NOTHING;
|
||
|
||
-- Saudi Arabia
|
||
INSERT INTO cities (country_id, slug, name) VALUES
|
||
((SELECT id FROM countries WHERE code = 'SA'), 'riyadh', 'Riyadh'),
|
||
((SELECT id FROM countries WHERE code = 'SA'), 'jeddah', 'Jeddah'),
|
||
((SELECT id FROM countries WHERE code = 'SA'), 'dammam', 'Dammam')
|
||
ON CONFLICT (country_id, slug) DO NOTHING;
|
||
|
||
-- Egypt
|
||
INSERT INTO cities (country_id, slug, name) VALUES
|
||
((SELECT id FROM countries WHERE code = 'EG'), 'cairo', 'Cairo'),
|
||
((SELECT id FROM countries WHERE code = 'EG'), 'alexandria', 'Alexandria'),
|
||
((SELECT id FROM countries WHERE code = 'EG'), 'giza', 'Giza')
|
||
ON CONFLICT (country_id, slug) DO NOTHING;
|
||
|
||
-- Chile
|
||
INSERT INTO cities (country_id, slug, name) VALUES
|
||
((SELECT id FROM countries WHERE code = 'CL'), 'santiago', 'Santiago'),
|
||
((SELECT id FROM countries WHERE code = 'CL'), 'valparaiso', 'Valparaíso'),
|
||
((SELECT id FROM countries WHERE code = 'CL'), 'concepcion', 'Concepción')
|
||
ON CONFLICT (country_id, slug) DO NOTHING;
|
||
|
||
-- Peru
|
||
INSERT INTO cities (country_id, slug, name) VALUES
|
||
((SELECT id FROM countries WHERE code = 'PE'), 'lima', 'Lima'),
|
||
((SELECT id FROM countries WHERE code = 'PE'), 'arequipa', 'Arequipa'),
|
||
((SELECT id FROM countries WHERE code = 'PE'), 'trujillo', 'Trujillo')
|
||
ON CONFLICT (country_id, slug) DO NOTHING;
|
||
```
|
||
|
||
---
|
||
|
||
# PART 3: CONTENT GENERATION
|
||
|
||
## 3.1 Company Content (800+ words total)
|
||
|
||
### Section Breakdown
|
||
| Section | Words | Purpose |
|
||
|---------|-------|---------|
|
||
| Overview | 200-250 | Who they are, approach, differentiators |
|
||
| Odoo Expertise | 200-250 | Partner status, modules, versions, tech |
|
||
| Services | 150-200 | What they offer |
|
||
| Industries | 150-200 | Who they serve |
|
||
| Local Value | 100-150 | Why local partner matters |
|
||
| Ideal Client | 80-120 | Best fit description |
|
||
| FAQ | 150-200 | 3-5 Q&As (40-60 words each) |
|
||
| **TOTAL** | **830-1170** | |
|
||
|
||
### Haiku Content Generation Prompt
|
||
```
|
||
SYSTEM:
|
||
You are an expert content writer for an Odoo ERP partner directory.
|
||
Write unique, informative, SEO-optimized content.
|
||
Guidelines:
|
||
- Professional but accessible tone
|
||
- Specific and factual - no filler
|
||
- Natural keyword integration
|
||
- Every sentence adds value
|
||
- Adapt to target language: {language}
|
||
- Do NOT invent information not in the data
|
||
- Do NOT use clichés like "Welcome to" or "In today's world"
|
||
|
||
USER:
|
||
Generate company profile content for this Odoo partner.
|
||
|
||
COMPANY DATA:
|
||
```json
|
||
{company_data}
|
||
```
|
||
|
||
SCRAPED WEBSITE CONTENT:
|
||
{scraped_markdown}
|
||
|
||
EXTRACTED TAGS:
|
||
{tags}
|
||
|
||
IMAGES AVAILABLE:
|
||
{images_list}
|
||
|
||
Generate content for each section following word counts.
|
||
Total must be 800+ words.
|
||
|
||
Output JSON:
|
||
{
|
||
"content_overview": "200-250 words...",
|
||
"content_expertise": "200-250 words...",
|
||
"content_services": "150-200 words...",
|
||
"content_industries": "150-200 words...",
|
||
"content_local_value": "100-150 words about why {city} location matters...",
|
||
"content_ideal_client": "80-120 words...",
|
||
"content_faq": [
|
||
{"question": "...", "answer": "40-60 words..."},
|
||
{"question": "...", "answer": "40-60 words..."},
|
||
{"question": "...", "answer": "40-60 words..."}
|
||
],
|
||
"meta_title": "max 60 chars including {company_name}",
|
||
"meta_description": "max 155 chars"
|
||
}
|
||
|
||
Write in {language}.
|
||
```
|
||
|
||
## 3.2 City Hub Content (1000+ words)
|
||
|
||
### Section Breakdown
|
||
| Section | Words |
|
||
|---------|-------|
|
||
| Intro | 250-350 |
|
||
| Services Overview | 200-250 |
|
||
| How to Choose | 200-300 |
|
||
| FAQ | 200-300 |
|
||
| **TOTAL** | **850-1200** |
|
||
|
||
### Haiku City Content Prompt
|
||
```
|
||
Generate content for the "{city}" Odoo partners directory page.
|
||
|
||
CITY: {city}
|
||
COUNTRY: {country}
|
||
LANGUAGE: {language}
|
||
TOTAL PARTNERS: {company_count}
|
||
PARTNER LEVELS: {partner_breakdown}
|
||
TOP INDUSTRIES: {top_industries}
|
||
TOP SERVICES: {top_services}
|
||
AVG RATING: {avg_rating}
|
||
|
||
Generate content for each section.
|
||
|
||
Output JSON:
|
||
{
|
||
"h1_title": "{city} Odoo Partner - X Experten",
|
||
"meta_title": "Odoo Partner {city} - X zertifizierte Experten",
|
||
"meta_description": "155 chars max",
|
||
"content_intro": "250-350 words about Odoo ecosystem in {city}...",
|
||
"content_services": "200-250 words about services available...",
|
||
"content_how_to_choose": "200-300 words with practical advice...",
|
||
"content_faq": [
|
||
{"question": "Wie viele Odoo Partner gibt es in {city}?", "answer": "..."},
|
||
{"question": "Was kostet eine Odoo Implementierung in {city}?", "answer": "..."},
|
||
{"question": "Wie finde ich den richtigen Odoo Partner?", "answer": "..."},
|
||
{"question": "...", "answer": "..."}
|
||
]
|
||
}
|
||
|
||
Write in {language}.
|
||
```
|
||
|
||
## 3.3 Country Hub Content (1200+ words)
|
||
|
||
Similar structure to city but broader scope:
|
||
- National Odoo market overview
|
||
- Regional differences within country
|
||
- All cities listed with links
|
||
- Country-specific considerations
|
||
|
||
---
|
||
|
||
# PART 4: IMAGE HANDLING
|
||
|
||
## 4.1 Image Requirements Per Company
|
||
|
||
| Type | Required | Notes |
|
||
|------|----------|-------|
|
||
| Logo | Yes | Primary branding, used in listings |
|
||
| Hero | No | Main visual for profile page |
|
||
| Team/Office | No | Shows company culture |
|
||
| Project/Screenshot | No | Shows their work |
|
||
|
||
**Target: 3-5 images per company**
|
||
|
||
## 4.2 Image Extraction from Firecrawl
|
||
|
||
```python
|
||
FIRECRAWL_IMAGE_EXTRACTION = {
|
||
"extract": {
|
||
"schema": {
|
||
"logo_url": {"type": "string"},
|
||
"images": {
|
||
"type": "array",
|
||
"items": {
|
||
"type": "object",
|
||
"properties": {
|
||
"url": {"type": "string"},
|
||
"alt": {"type": "string"},
|
||
"context": {"type": "string"} # where on page
|
||
}
|
||
}
|
||
}
|
||
},
|
||
"systemPrompt": """
|
||
Extract all relevant images from this Odoo partner's website:
|
||
1. Logo (required) - usually in header or about page
|
||
2. Team photos - people, office, company culture
|
||
3. Project screenshots - Odoo implementations, dashboards
|
||
4. Hero/banner images - main visuals
|
||
|
||
For each image, note its context (header, about section, portfolio, etc.)
|
||
Skip: icons, social media buttons, generic stock photos, ads
|
||
"""
|
||
}
|
||
}
|
||
```
|
||
|
||
## 4.3 Image Processing Pipeline
|
||
|
||
```
|
||
1. EXTRACT from Firecrawl response
|
||
└── Get list of image URLs + metadata
|
||
|
||
2. FILTER
|
||
├── Skip images < 100px (icons)
|
||
├── Skip known stock photo domains
|
||
└── Skip common filenames (favicon, icon-*, button-*)
|
||
|
||
3. DOWNLOAD
|
||
├── Fetch image
|
||
├── Verify it's a valid image
|
||
└── Check minimum dimensions (200x200)
|
||
|
||
4. PROCESS
|
||
├── Convert to WebP (for performance)
|
||
├── Resize to standard sizes:
|
||
│ ├── Logo: 400x400 max, preserve aspect
|
||
│ ├── Hero: 1200x630 (OG image size)
|
||
│ └── Gallery: 800x600 max
|
||
└── Compress (80% quality)
|
||
|
||
5. STORE
|
||
├── Save to: /static/images/companies/{company_slug}/
|
||
│ ├── logo.webp
|
||
│ ├── hero.webp
|
||
│ ├── gallery-1.webp
|
||
│ ├── gallery-2.webp
|
||
│ └── gallery-3.webp
|
||
└── Update company_images table
|
||
```
|
||
|
||
## 4.4 Image Storage Path Structure
|
||
|
||
```
|
||
/static/
|
||
└── images/
|
||
└── companies/
|
||
└── {company-slug}/
|
||
├── logo.webp
|
||
├── hero.webp
|
||
├── gallery-1.webp
|
||
├── gallery-2.webp
|
||
└── gallery-3.webp
|
||
```
|
||
|
||
---
|
||
|
||
# PART 5: STATIC EXPORT
|
||
|
||
## 5.1 Export Structure
|
||
|
||
```
|
||
/export/
|
||
├── sitemap.xml
|
||
├── sitemap-companies.xml
|
||
├── sitemap-cities.xml
|
||
├── sitemap-countries.xml
|
||
│
|
||
├── de/ # Germany
|
||
│ ├── index.xml # Country hub
|
||
│ ├── berlin/
|
||
│ │ ├── index.xml # City hub
|
||
│ │ ├── company-a/
|
||
│ │ │ └── index.xml # Company page
|
||
│ │ └── company-b/
|
||
│ │ └── index.xml
|
||
│ └── muenchen/
|
||
│ └── ...
|
||
│
|
||
├── at/ # Austria
|
||
│ └── ...
|
||
│
|
||
├── mx/ # Mexico
|
||
│ └── ...
|
||
│
|
||
└── static/
|
||
└── images/
|
||
└── companies/
|
||
└── ...
|
||
```
|
||
|
||
## 5.2 XML Template Structure
|
||
|
||
### Company Page XML
|
||
```xml
|
||
<?xml version="1.0" encoding="UTF-8"?>
|
||
<company>
|
||
<meta>
|
||
<title>{meta_title}</title>
|
||
<description>{meta_description}</description>
|
||
<canonical>https://odoo-expertos.com/{country}/{city}/{slug}</canonical>
|
||
<language>{language}</language>
|
||
</meta>
|
||
|
||
<header>
|
||
<name>{company_name}</name>
|
||
<logo>{logo_url}</logo>
|
||
<location>
|
||
<city>{city_name}</city>
|
||
<country>{country_name}</country>
|
||
</location>
|
||
<rating>{google_rating}</rating>
|
||
<reviews>{google_reviews_count}</reviews>
|
||
<partner_level>{odoo_partner_level}</partner_level>
|
||
</header>
|
||
|
||
<images>
|
||
<image type="hero">{hero_url}</image>
|
||
<image type="gallery">{gallery_1_url}</image>
|
||
<image type="gallery">{gallery_2_url}</image>
|
||
</images>
|
||
|
||
<content>
|
||
<section id="overview">{content_overview}</section>
|
||
<section id="expertise">{content_expertise}</section>
|
||
<section id="services">{content_services}</section>
|
||
<section id="industries">{content_industries}</section>
|
||
<section id="local">{content_local_value}</section>
|
||
<section id="ideal-client">{content_ideal_client}</section>
|
||
</content>
|
||
|
||
<faq>
|
||
<item>
|
||
<question>{faq_1_question}</question>
|
||
<answer>{faq_1_answer}</answer>
|
||
</item>
|
||
<!-- ... -->
|
||
</faq>
|
||
|
||
<tags>
|
||
<services>{service_tags}</services>
|
||
<industries>{industry_tags}</industries>
|
||
<modules>{module_tags}</modules>
|
||
</tags>
|
||
|
||
<contact>
|
||
<website>{website}</website>
|
||
<phone>{phone}</phone>
|
||
<address>{address}</address>
|
||
</contact>
|
||
|
||
<related>
|
||
<company slug="{related_1_slug}">{related_1_name}</company>
|
||
<company slug="{related_2_slug}">{related_2_name}</company>
|
||
<company slug="{related_3_slug}">{related_3_name}</company>
|
||
</related>
|
||
|
||
<breadcrumbs>
|
||
<item url="/">Home</item>
|
||
<item url="/{country}/">{country_name}</item>
|
||
<item url="/{country}/{city}/">{city_name}</item>
|
||
<item url="/{country}/{city}/{slug}/">{company_name}</item>
|
||
</breadcrumbs>
|
||
</company>
|
||
```
|
||
|
||
### City Hub XML
|
||
```xml
|
||
<?xml version="1.0" encoding="UTF-8"?>
|
||
<city-hub>
|
||
<meta>
|
||
<title>{meta_title}</title>
|
||
<description>{meta_description}</description>
|
||
<canonical>https://odoo-expertos.com/{country}/{city}</canonical>
|
||
</meta>
|
||
|
||
<header>
|
||
<h1>{h1_title}</h1>
|
||
<company_count>{company_count}</company_count>
|
||
<avg_rating>{avg_rating}</avg_rating>
|
||
</header>
|
||
|
||
<content>
|
||
<section id="intro">{content_intro}</section>
|
||
<section id="services">{content_services}</section>
|
||
<section id="how-to-choose">{content_how_to_choose}</section>
|
||
</content>
|
||
|
||
<faq>
|
||
<!-- FAQ items -->
|
||
</faq>
|
||
|
||
<filters>
|
||
<services>{available_service_tags}</services>
|
||
<industries>{available_industry_tags}</industries>
|
||
<partner_levels>{available_partner_levels}</partner_levels>
|
||
</filters>
|
||
|
||
<companies>
|
||
<company featured="true">
|
||
<slug>{slug}</slug>
|
||
<name>{name}</name>
|
||
<logo>{logo}</logo>
|
||
<rating>{rating}</rating>
|
||
<partner_level>{level}</partner_level>
|
||
<excerpt>{short_description}</excerpt>
|
||
<tags>{tags}</tags>
|
||
</company>
|
||
<!-- ... all companies ... -->
|
||
</companies>
|
||
|
||
<related_cities>
|
||
<city slug="{slug}" count="{count}">{name}</city>
|
||
<!-- other cities in country -->
|
||
</related_cities>
|
||
|
||
<breadcrumbs>
|
||
<item url="/">Home</item>
|
||
<item url="/{country}/">{country_name}</item>
|
||
<item url="/{country}/{city}/">{city_name}</item>
|
||
</breadcrumbs>
|
||
</city-hub>
|
||
```
|
||
|
||
---
|
||
|
||
# PART 6: CLI COMMANDS
|
||
|
||
## 6.1 Command Reference
|
||
|
||
```bash
|
||
# ==========================================
|
||
# SETUP
|
||
# ==========================================
|
||
python odoo_directory.py setup
|
||
# - Creates Supabase tables
|
||
# - Seeds regions, countries, cities
|
||
# - Seeds tags
|
||
|
||
# ==========================================
|
||
# PLANNING
|
||
# ==========================================
|
||
python odoo_directory.py plan --country DE
|
||
# - Creates execution plan for Germany
|
||
# - Lists all cities to scrape
|
||
# - Estimates company counts
|
||
# - Saves plan to execution_plans table
|
||
|
||
python odoo_directory.py plan --country DE --cities berlin,muenchen
|
||
# - Plan only specific cities
|
||
|
||
python odoo_directory.py plan --region dach
|
||
# - Plan entire DACH region (DE + AT + CH)
|
||
|
||
python odoo_directory.py plans
|
||
# - List all created plans
|
||
|
||
python odoo_directory.py plan-status --plan {plan_id}
|
||
# - Show progress of a plan
|
||
|
||
# ==========================================
|
||
# EXECUTION
|
||
# ==========================================
|
||
python odoo_directory.py execute --plan {plan_id}
|
||
# - Runs full pipeline for plan:
|
||
# 1. Scrape all cities (Apify)
|
||
# 2. Enrich all companies (Firecrawl + Haiku)
|
||
# 3. Generate content (Haiku)
|
||
# 4. Generate hub pages
|
||
# 5. Export static files
|
||
|
||
python odoo_directory.py execute --plan {plan_id} --step scrape
|
||
# - Run only scraping step
|
||
|
||
python odoo_directory.py execute --plan {plan_id} --step enrich
|
||
# - Run only enrichment step
|
||
|
||
python odoo_directory.py execute --plan {plan_id} --step content
|
||
# - Run only content generation
|
||
|
||
python odoo_directory.py execute --plan {plan_id} --step hubs
|
||
# - Generate city + country hub content
|
||
|
||
# ==========================================
|
||
# EXPORT
|
||
# ==========================================
|
||
python odoo_directory.py export --country DE
|
||
# - Export all Germany data to static XML
|
||
|
||
python odoo_directory.py export --country DE --format html
|
||
# - Export as HTML instead of XML
|
||
|
||
python odoo_directory.py export --all
|
||
# - Export all countries with content_ready status
|
||
|
||
# ==========================================
|
||
# DEPLOY
|
||
# ==========================================
|
||
python odoo_directory.py deploy
|
||
# - Git add, commit, push all exports
|
||
# - Triggers Vercel deployment
|
||
|
||
# ==========================================
|
||
# UTILITIES
|
||
# ==========================================
|
||
python odoo_directory.py status
|
||
# - Show overall system status
|
||
|
||
python odoo_directory.py status --country DE
|
||
# - Show Germany-specific status
|
||
|
||
python odoo_directory.py retry --company {company_id}
|
||
# - Retry failed company processing
|
||
|
||
python odoo_directory.py reprocess --city berlin
|
||
# - Reprocess all companies in a city
|
||
```
|
||
|
||
## 6.2 Execution Flow Example
|
||
|
||
```bash
|
||
# Step 1: Setup (once)
|
||
python odoo_directory.py setup
|
||
|
||
# Step 2: Create plan for Germany
|
||
python odoo_directory.py plan --country DE
|
||
# Output: Created plan abc123 with 20 cities, ~400 estimated companies
|
||
|
||
# Step 3: Execute the plan
|
||
python odoo_directory.py execute --plan abc123
|
||
# This runs:
|
||
# - Scraping: 20 cities × ~20 companies = ~400 companies (30 min)
|
||
# - Enrichment: 400 companies × Firecrawl + Haiku (60 min)
|
||
# - Content: 400 companies × Haiku (40 min)
|
||
# - Hubs: 20 city pages + 1 country page (10 min)
|
||
# Total: ~2.5 hours
|
||
|
||
# Step 4: Export to static files
|
||
python odoo_directory.py export --country DE
|
||
# Creates /export/de/ with all XML files
|
||
|
||
# Step 5: Deploy
|
||
python odoo_directory.py deploy
|
||
# Commits and pushes to Git, Vercel auto-deploys
|
||
```
|
||
|
||
---
|
||
|
||
# PART 7: API CONFIGURATIONS
|
||
|
||
## 7.1 Environment Variables
|
||
|
||
```bash
|
||
# Supabase
|
||
SUPABASE_URL=https://xxx.supabase.co
|
||
SUPABASE_KEY=eyJ...
|
||
|
||
# Apify
|
||
APIFY_TOKEN=apify_api_xxx
|
||
|
||
# Firecrawl
|
||
FIRECRAWL_API_KEY=fc-xxx
|
||
|
||
# Anthropic (for Haiku)
|
||
ANTHROPIC_API_KEY=sk-ant-xxx
|
||
|
||
# Git (for deploy)
|
||
GIT_REPO_PATH=/path/to/odoo-expertos
|
||
GIT_BRANCH=main
|
||
```
|
||
|
||
## 7.2 Apify Configuration
|
||
|
||
```python
|
||
APIFY_CONFIG = {
|
||
"actor_id": "apify/google-maps-scraper",
|
||
"default_input": {
|
||
"maxCrawledPlacesPerSearch": 50,
|
||
"skipClosedPlaces": True,
|
||
"scrapeDirectories": False,
|
||
"maxImages": 0,
|
||
"maxReviews": 0,
|
||
},
|
||
"search_queries_by_language": {
|
||
"de": [
|
||
"Odoo Partner {city}",
|
||
"Odoo Implementierung {city}",
|
||
"Odoo Beratung {city}",
|
||
"Odoo ERP {city}",
|
||
"ERP Berater {city}",
|
||
],
|
||
"es": [
|
||
"Partner Odoo {city}",
|
||
"Implementador Odoo {city}",
|
||
"Consultor Odoo {city}",
|
||
"Odoo ERP {city}",
|
||
],
|
||
"pt": [
|
||
"Parceiro Odoo {city}",
|
||
"Implementador Odoo {city}",
|
||
"Consultor Odoo {city}",
|
||
],
|
||
"en": [
|
||
"Odoo Partner {city}",
|
||
"Odoo Implementation {city}",
|
||
"Odoo Consultant {city}",
|
||
],
|
||
}
|
||
}
|
||
```
|
||
|
||
## 7.3 Firecrawl Configuration
|
||
|
||
```python
|
||
FIRECRAWL_CONFIG = {
|
||
"formats": ["markdown", "extract"],
|
||
"extract": {
|
||
"schema": {
|
||
"company_name": {"type": "string"},
|
||
"logo_url": {"type": "string"},
|
||
"tagline": {"type": "string"},
|
||
"about_text": {"type": "string"},
|
||
"services": {"type": "array", "items": {"type": "string"}},
|
||
"industries": {"type": "array", "items": {"type": "string"}},
|
||
"team_info": {"type": "string"},
|
||
"contact_email": {"type": "string"},
|
||
"phone": {"type": "string"},
|
||
"address": {"type": "string"},
|
||
"social_linkedin": {"type": "string"},
|
||
"founded_year": {"type": "integer"},
|
||
"team_size": {"type": "string"},
|
||
"images": {
|
||
"type": "array",
|
||
"items": {
|
||
"type": "object",
|
||
"properties": {
|
||
"url": {"type": "string"},
|
||
"alt": {"type": "string"},
|
||
"type": {"type": "string"}
|
||
}
|
||
}
|
||
},
|
||
"case_studies": {
|
||
"type": "array",
|
||
"items": {
|
||
"type": "object",
|
||
"properties": {
|
||
"title": {"type": "string"},
|
||
"industry": {"type": "string"},
|
||
"summary": {"type": "string"}
|
||
}
|
||
}
|
||
},
|
||
"odoo_partner_info": {"type": "string"},
|
||
"certifications": {"type": "array", "items": {"type": "string"}}
|
||
},
|
||
"systemPrompt": """
|
||
Extract detailed information about this Odoo consulting company.
|
||
|
||
Focus on:
|
||
1. Company basics: name, tagline, about text
|
||
2. Odoo-specific: partner level, certifications, modules mentioned
|
||
3. Services offered (implementation, customization, training, etc.)
|
||
4. Industries served
|
||
5. Team information (size, key people)
|
||
6. Contact details
|
||
7. Images: logo, team photos, office, project screenshots
|
||
- Mark image type: 'logo', 'hero', 'team', 'office', 'project'
|
||
- Skip icons, social buttons, stock photos
|
||
8. Case studies or project references
|
||
|
||
Only extract information clearly present on the page.
|
||
For images, only include those that appear professional and relevant.
|
||
"""
|
||
},
|
||
"includeTags": ["main", "article", "section", "div", "img"],
|
||
"excludeTags": ["nav", "footer", "aside", "script", "style"],
|
||
"waitFor": 3000,
|
||
"timeout": 30000
|
||
}
|
||
```
|
||
|
||
## 7.4 Haiku Configuration
|
||
|
||
```python
|
||
HAIKU_CONFIG = {
|
||
"model": "claude-3-5-haiku-20241022",
|
||
"max_tokens": 4000,
|
||
"temperature": 0.7,
|
||
}
|
||
|
||
HAIKU_EXTRACTION_CONFIG = {
|
||
"model": "claude-3-5-haiku-20241022",
|
||
"max_tokens": 2000,
|
||
"temperature": 0.2,
|
||
}
|
||
```
|
||
|
||
---
|
||
|
||
# PART 8: ERROR HANDLING
|
||
|
||
## 8.1 Retry Configuration
|
||
|
||
```python
|
||
RETRY_CONFIG = {
|
||
"apify": {"max_attempts": 3, "backoff": [60, 300, 900]},
|
||
"firecrawl": {"max_attempts": 3, "backoff": [30, 120, 300]},
|
||
"haiku": {"max_attempts": 2, "backoff": [10, 30]},
|
||
"supabase": {"max_attempts": 3, "backoff": [5, 15, 30]},
|
||
"image_download": {"max_attempts": 2, "backoff": [5, 15]},
|
||
}
|
||
```
|
||
|
||
## 8.2 Failure Handling
|
||
|
||
| Failure | Action |
|
||
|---------|--------|
|
||
| Apify: No results | Mark city as "low_data", continue |
|
||
| Apify: Rate limited | Wait and retry |
|
||
| Firecrawl: Website down | Mark company, skip |
|
||
| Firecrawl: No content | Mark as "minimal_data", use basic generation |
|
||
| Haiku: JSON parse error | Retry with stricter prompt |
|
||
| Haiku: Insufficient content | Retry once, then mark for review |
|
||
| Image: Download failed | Skip image, continue with others |
|
||
| Supabase: Connection error | Retry with backoff |
|
||
|
||
## 8.3 Minimum Viable Company
|
||
|
||
A company must have:
|
||
- Name ✓
|
||
- Website ✓
|
||
- City association ✓
|
||
- At least 500 words of generated content
|
||
- Logo OR at least 1 image
|
||
|
||
If these minimums aren't met after retries, skip the company.
|
||
|
||
---
|
||
|
||
# PART 9: SUCCESS METRICS
|
||
|
||
## 9.1 Per-Execution Metrics
|
||
|
||
Track for each plan execution:
|
||
- Cities scraped
|
||
- Companies found
|
||
- Companies enriched (success rate)
|
||
- Content generated (word counts)
|
||
- Images downloaded
|
||
- Export success
|
||
- Total time
|
||
|
||
## 9.2 Quality Checks
|
||
|
||
Before marking content as ready:
|
||
- [ ] Total word count ≥ 800
|
||
- [ ] All required sections present
|
||
- [ ] Meta title ≤ 60 chars
|
||
- [ ] Meta description ≤ 155 chars
|
||
- [ ] At least 1 image (logo or other)
|
||
- [ ] FAQ has ≥ 3 items
|
||
|
||
---
|
||
|
||
# SUMMARY
|
||
|
||
This system:
|
||
|
||
1. **Plans** country-level executions with city lists
|
||
2. **Scrapes** Google Maps via Apify
|
||
3. **Enriches** with Firecrawl (content + images)
|
||
4. **Tags** using Haiku extraction
|
||
5. **Generates** 800+ word content via Haiku
|
||
6. **Creates** hub pages for cities/countries
|
||
7. **Exports** static XML for Vercel
|
||
8. **Deploys** via Git push
|
||
|
||
All orchestrated by Claude Code, running on demand per country.
|