Connectez le Site

Add a blog to my website. Articles are delivered via webhook from AutoSEO (https://getautoseo.com) and stored in Supabase.

CRITICAL: Do NOT drop, delete, alter, or modify any existing database tables, columns, data, or RLS policies. Only CREATE new tables and functions. Never use DROP TABLE, TRUNCATE, or DELETE on existing tables. This project has existing data that must be preserved.

REQUIREMENTS:

1. SUPABASE DATABASE TABLE
   - Create a "blog_posts" table ONLY IF IT DOES NOT ALREADY EXIST (use CREATE TABLE IF NOT EXISTS)
   - Table columns:
     * id (integer, primary key) — the article id from AutoSEO
     * title (text, not null)
     * slug (text, not null, unique)
     * content_html (text) — the full article HTML, ready to render
     * content_markdown (text) — the article in Markdown format
     * hero_image_url (text) — hero/featured image URL
     * hero_image_alt (text) — alt text for hero image (for accessibility and SEO)
     * infographic_url (text) — infographic image URL (may be null)
     * meta_description (text) — SEO meta description
     * meta_keywords (text) — comma-separated SEO keywords
     * tags (text) — comma-separated tags
     * faq_schema (jsonb) — array of {question, answer} objects for FAQ section
     * language (text, default 'en')
     * reading_time (integer) — estimated minutes to read
     * published_at (timestamptz)
     * updated_at (timestamptz)
     * created_at (timestamptz)
     * received_at (timestamptz, default now()) — when the webhook was received
   - Enable Row Level Security (RLS) on the table
   - Add an RLS policy allowing anonymous SELECT access — the blog is public
   - Add an RLS policy allowing the service_role to INSERT, UPDATE, and DELETE
   - Create an index on slug for fast lookups
   - Create an index on published_at for sorting

2. WEBHOOK RECEIVER EDGE FUNCTION
   - Create a Supabase Edge Function named "receive-article" that:
     a. Accepts POST requests with JSON body
     b. Verifies the Authorization header: extract the Bearer token and compare it to this exact value: "aseo_wh_2e5a43307f68fb094354b92455aff530". If the token doesn't match or is missing, return HTTP 401 with { "error": "Unauthorized" }
     c. Reads the JSON body with these fields:
        - event (string): "article.published" or "test"
        - id (integer): unique article ID
        - title (string): article title
        - slug (string): URL-friendly slug
        - metaDescription (string): SEO meta description
        - content_html (string): full article as HTML
        - content_markdown (string): full article as Markdown
        - heroImageUrl (string|null): featured hero image URL
        - heroImageAlt (string|null): alt text for the hero image (use for img alt attribute)
        - infographicImageUrl (string|null): infographic image URL
        - keywords (array): target keywords
        - metaKeywords (string|null): comma-separated SEO keywords
        - wordpressTags (string|null): comma-separated tags
        - faqSchema (array|null): array of {question, answer} objects
        - languageCode (string): ISO language code for this article (e.g. "en", "de")
        - sourceArticleId (integer|null): for translated articles, the ID of the original source article
        - status (string): "published"
        - publishedAt (string): ISO 8601 timestamp
        - updatedAt (string): ISO 8601 timestamp
        - createdAt (string): ISO 8601 timestamp
     d. If event is "test": return HTTP 200 with { "url": "https://YOUR_DOMAIN/blog/test", "status": "ok" }
     e. Calculate reading_time: strip HTML tags from content_html, count words, divide by 200, round up
     f. UPSERT the article into blog_posts (on conflict on id, update all fields + set received_at = now())
     g. Return HTTP 200 with JSON: { "url": "https://YOUR_DOMAIN/blog/SLUG" }
        IMPORTANT: Replace YOUR_DOMAIN with your actual deployed site domain. AutoSEO reads the "url" field from the response to track where the article was published. This enables the "View Live" link in your AutoSEO dashboard.
     h. On error, return HTTP 500 with { "error": "message" } — AutoSEO will retry up to 3 times
   - The function must use the Supabase service_role key to bypass RLS for writes
   - Set CORS headers to allow POST from any origin (AutoSEO's server sends webhooks)

3. BLOG LISTING PAGE at /blog
   - Query blog_posts from Supabase, ordered by published_at DESC
   - Display each article as a card: hero image (with fallback — hide container if no image or image fails to load), title, meta_description (truncated to ~160 chars), published date (formatted as "Month Day, Year"), reading time
   - Link each card to /blog/[slug]
   - Paginate: show 12 articles initially with a "Load More" button (use Supabase range queries)
   - LOADING STATE: show skeleton card placeholders (3-6 cards) with pulsing animation
   - EMPTY STATE: show "Articles are on the way! Check back soon." with a friendly illustration
   - ERROR STATE: if Supabase query fails, show "Couldn't load articles. Please try again." with a Retry button

4. INDIVIDUAL BLOG POST PAGE at /blog/[slug]
   - Query blog_posts WHERE slug = [slug], single row
   - ARTICLE NOT FOUND: styled 404 with link back to /blog
   - Hero image: responsive container (max-height: 400px, object-fit: cover, full width). Use hero_image_alt as the img alt attribute. On load error, hide the image container — no broken image icons
   - Infographic: if infographic_url is set, display below the article content as a full-width image with a subtle border and rounded corners. On load error, hide it
   - Title: render as h1
   - Meta info row: formatted published date, reading time (e.g. "5 min read")
   - Content: render content_html using dangerouslySetInnerHTML inside a "prose" styled container with these typography rules:
     * h2: 1.5rem, bold, margin-top 1.75rem
     * h3: 1.25rem, bold, margin-top 1.5rem
     * p: 1.1rem line-height, margin-bottom 1rem, color slightly muted (gray-800)
     * ul/ol: proper indentation, visible bullets/numbers, margin-bottom 1rem, li margin-bottom 0.5rem
     * blockquote: left border (4px, primary color), italic, padding-left 1rem, muted text
     * code (inline): background gray-100, padding 2px 6px, rounded, monospace font
     * pre > code: background gray-900, text gray-100, padding 1rem, rounded-lg, overflow-x auto
     * table: full width, bordered cells, alternating row backgrounds, horizontal scroll wrapper on mobile
     * img inside content: max-width 100%, height auto, rounded-lg, centered. Add onerror handler to hide broken images
     * a: colored link (primary color or blue-600), underline on hover; preserve each link's target and rel attributes from content_html (do not override them)
   - FAQ SECTION: if faq_schema exists and is not empty, render below the article content:
     * Heading: "Frequently Asked Questions"
     * Each Q&A as a collapsible accordion (question = clickable header, answer = expandable body)
     * First item expanded by default, rest collapsed
     * Smooth expand/collapse animation
     * Inject FAQPage JSON-LD structured data (schema.org)
   - RELATED ARTICLES: below FAQ (or below content if no FAQs), show up to 3 other recent articles excluding the current one. Display as small horizontal cards (image, title, date)
   - "← Back to all articles" link at the top

5. SEO & META TAGS
   - Blog listing: title "Blog | [Site Name]", meta description "Read our latest articles and insights"
   - Article page: title "[Article Title] | [Site Name]"
   - Per-article meta tags (react-helmet-async or equivalent):
     * meta description from meta_description column (fall back to first 160 chars of content)
     * meta keywords from meta_keywords column
     * og:title, og:description, og:image (hero_image_url), og:type="article"
     * og:url = https://[your-domain]/blog/[slug]
     * twitter:card="summary_large_image", twitter:title, twitter:description, twitter:image
     * canonical link: https://[your-domain]/blog/[slug]
   - Article JSON-LD (schema.org Article): headline, image, datePublished, dateModified, description, publisher
   - FAQPage JSON-LD if faq_schema exists

6. SITEMAP at /sitemap.xml
   - Create a Supabase Edge Function that reads from blog_posts and returns valid XML sitemap
   - Configure a rewrite so /sitemap.xml hits this Edge Function
   - List every blog post as https://[your-domain]/blog/[slug]
   - Include lastmod from updated_at, changefreq "weekly", priority "0.7"
   - Include /blog listing with priority "0.8"
   - Include homepage with priority "1.0"

7. SPA ROUTING & DEPLOYMENT
   - Ensure /blog and /blog/[slug] routes work with direct navigation
   - Add redirect/rewrite config: public/_redirects for Netlify or vercel.json for Vercel

8. NAVIGATION
   - Add a "Blog" link to the main navigation linking to /blog
   - Highlight the Blog nav item when on /blog/*

9. DESIGN & RESPONSIVENESS
   - Clean, modern layout matching my existing site style
   - Blog listing: responsive grid — 1 col mobile, 2 tablet, 3 desktop
   - Article cards: subtle hover effect (lift + shadow), smooth transitions, rounded image corners
   - Article page: max-width 720px centered for comfortable reading
   - Fully responsive, mobile-first
   - All images responsive with max-width: 100%
   - All interactive elements have cursor: pointer