Keyword Research → Content Generation → Publish → Track Rankings → Optimize
       ↓                   ↓               ↓            ↓              ↓
  High-intent        AI + Human        Verticals    Daily scrape   Update content
  long-tail          editorial          sites       SERP APIs      based on data

-- From SCHEMA.md
CREATE TABLE publisher_posts (
  id uuid PRIMARY KEY,
  vertical_id uuid REFERENCES publisher_verticals(id),
  
  title text NOT NULL,
  slug text NOT NULL,
  content text NOT NULL,
  
  -- SEO fields
  meta_title text,           -- 60 char max
  meta_description text,     -- 155 char max
  featured_image_url text,
  
  -- Target keyword tracking
  -- Stored in content_analysis.extracted_facts
  
  published_at timestamptz,
  
  UNIQUE(vertical_id, slug)
);

CREATE TABLE publisher_post_analytics (
  id uuid PRIMARY KEY,
  post_id uuid REFERENCES publisher_posts(id) UNIQUE,
  
  -- Traffic from organic search
  views integer DEFAULT 0,
  unique_visitors integer DEFAULT 0,
  
  -- Engagement (affects rankings)
  avg_time_on_page integer DEFAULT 0,  -- seconds
  bounce_rate numeric DEFAULT 0,       -- percentage
  scroll_depth_avg numeric,            -- percentage
  
  -- Conversions
  email_signups integer DEFAULT 0,
  conversions integer DEFAULT 0,
  revenue_generated numeric DEFAULT 0
);

-- Proposed: publisher_serp_tracking
CREATE TABLE publisher_serp_tracking (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  post_id uuid REFERENCES publisher_posts(id),
  
  -- Target keyword
  keyword text NOT NULL,
  search_volume integer,        -- Monthly searches
  keyword_difficulty integer,   -- 0-100
  
  -- Current ranking
  position integer,             -- 1-100, null if not ranking
  url_ranking text,             -- Which URL is ranking
  
  -- SERP features
  has_featured_snippet boolean DEFAULT false,
  has_people_also_ask boolean DEFAULT false,
  has_local_pack boolean DEFAULT false,
  
  -- Competition
  top_competitor_url text,
  top_competitor_da integer,
  
  -- Tracking
  last_checked_at timestamptz,
  position_history jsonb DEFAULT '[]',  -- [{date, position}]
  
  created_at timestamptz DEFAULT now()
);

CREATE INDEX idx_serp_post ON publisher_serp_tracking(post_id);
CREATE INDEX idx_serp_keyword ON publisher_serp_tracking(keyword);
CREATE INDEX idx_serp_position ON publisher_serp_tracking(position);

SELECT DISTINCT unnest(topics) as topic, COUNT(*) as frequency
FROM content_analysis ca
JOIN raw_content rc ON ca.content_id = rc.id
JOIN content_sources cs ON rc.source_id = cs.id
WHERE cs.vertical_id = 'golf-vertical-uuid'
GROUP BY topic
ORDER BY frequency DESC
LIMIT 50;

// Pseudocode for keyword expansion
async function expandKeywords(seedKeywords, vertical) {
  const expanded = [];
  
  for (const seed of seedKeywords) {
    // Get related keywords
    const related = await seoApi.getRelatedKeywords(seed);
    
    // Filter by criteria
    const filtered = related.filter(kw => 
      kw.volume >= 100 &&           // Minimum volume
      kw.difficulty <= 40 &&        // Achievable difficulty
      kw.cpc >= 0.50               // Commercial intent
    );
    
    expanded.push(...filtered);
  }
  
  return expanded;
}

keyword_priority = (search_volume × 0.3)
                 + (cpc × 20)
                 + ((100 - difficulty) × 0.3)
                 + (intent_score × 0.2)

-- In content_analysis.extracted_facts
{
  "seo": {
    "target_keyword": "best golf drivers 2025",
    "secondary_keywords": ["golf driver reviews", "top golf clubs"],
    "title_tag": "Best Golf Drivers 2025: Expert Reviews & Buying Guide",
    "meta_description": "Discover the best golf drivers for 2025...",
    "word_count": 2847,
    "reading_time_minutes": 12,
    "internal_link_opportunities": ["golf-swing-tips", "golf-equipment-guide"]
  }
}

// Scheduled job: Check rankings daily
async function checkRankings() {
  const keywords = await getTrackedKeywords();
  
  for (const kw of keywords) {
    const result = await serpApi.check(kw.keyword);
    
    // Find our URL in results
    const ourPosition = result.organic.findIndex(
      r => r.link.includes('trendingsociety.com')
    ) + 1;
    
    // Update tracking
    await updateSerpTracking(kw.id, {
      position: ourPosition || null,
      url_ranking: ourPosition ? result.organic[ourPosition - 1].link : null,
      has_featured_snippet: result.featured_snippet?.exists,
      position_history: [...kw.position_history, {
        date: new Date().toISOString(),
        position: ourPosition
      }]
    });
  }
}

-- Find significant position changes (for alerting)
SELECT 
  pst.keyword,
  pp.title,
  pst.position as current_position,
  (pst.position_history->-2->>'position')::int as previous_position,
  pst.position - (pst.position_history->-2->>'position')::int as change
FROM publisher_serp_tracking pst
JOIN publisher_posts pp ON pst.post_id = pp.id
WHERE pst.last_checked_at > now() - interval '1 day'
  AND ABS(pst.position - (pst.position_history->-2->>'position')::int) >= 5
ORDER BY ABS(change) DESC;

SELECT 
  pst.keyword,
  pst.position,
  pst.search_volume,
  pp.title,
  ppa.views,
  ppa.revenue_generated
FROM publisher_serp_tracking pst
JOIN publisher_posts pp ON pst.post_id = pp.id
JOIN publisher_post_analytics ppa ON pp.id = ppa.post_id
WHERE pst.position <= 10  -- Page 1
ORDER BY pst.search_volume DESC;

-- Posts ranking 11-20 (page 2) with potential
SELECT 
  pp.title,
  pst.keyword,
  pst.position,
  pst.search_volume,
  ppa.avg_time_on_page,
  ppa.bounce_rate
FROM publisher_serp_tracking pst
JOIN publisher_posts pp ON pst.post_id = pp.id
JOIN publisher_post_analytics ppa ON pp.id = ppa.post_id
WHERE pst.position BETWEEN 11 AND 20
  AND pst.search_volume >= 500
ORDER BY pst.search_volume DESC;

-- High-volume keywords we don't target yet
SELECT 
  ca.topics,
  COUNT(*) as content_pieces
FROM content_analysis ca
JOIN raw_content rc ON ca.content_id = rc.id
WHERE NOT EXISTS (
  SELECT 1 FROM publisher_serp_tracking pst
  WHERE pst.keyword = ANY(ca.topics)
)
GROUP BY ca.topics
ORDER BY COUNT(*) DESC;

<!-- Auto-generated per vertical -->
<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
  <url>
    <loc>https://golf.trendingsociety.com/best-golf-drivers-2025</loc>
    <lastmod>2025-12-17</lastmod>
    <changefreq>weekly</changefreq>
    <priority>0.8</priority>
  </url>
  <!-- ... more URLs -->
</urlset>

{
  "@context": "https://schema.org",
  "@type": "Article",
  "headline": "Best Golf Drivers 2025: Expert Reviews",
  "author": {
    "@type": "Organization",
    "name": "Golf Insider"
  },
  "publisher": {
    "@type": "Organization",
    "name": "Trending Society",
    "logo": {
      "@type": "ImageObject",
      "url": "https://trendingsociety.com/logo.png"
    }
  },
  "datePublished": "2025-12-17",
  "dateModified": "2025-12-17"
}

User-agent: *
Allow: /

Sitemap: https://golf.trendingsociety.com/sitemap.xml
Sitemap: https://travel.trendingsociety.com/sitemap.xml
# ... per vertical

1. Keyword Research (Weekly)
   └── DataForSEO API → keyword_opportunities table

2. Content Prioritization (Daily)
   └── Score keywords → Create content briefs

3. Content Generation (On-demand)
   └── AI generates SEO-optimized content

4. Publishing (Scheduled)
   └── Auto-publish with proper meta tags

5. Ranking Check (Daily)
   └── SERP API → publisher_serp_tracking

6. Optimization (Weekly)
   └── Identify underperformers → Update content

7. Reporting (Weekly)
   └── Rankings, traffic, revenue by keyword