# 32 — AI Blog Engine with SEO Optimization

**Version:** 1.0 | **Last Updated:** March 29, 2026 | **Status:** Planning Phase | **Dependency:** Instruction 03 (Self-Hosted AI), Instruction 31 (Autonomous AI Agents), Instruction 11 (Social Media)

---

## OVERVIEW

Build a full-featured blog CMS with AI-powered content generation, SEO optimization, and multi-author support. The Blog Agent (instruction 31) generates hyper-local, brand-voice-aligned posts 2-3x weekly. Admins review and publish. Every published post auto-distributes to social media and email newsletter.

**Key Features:**
- **Rich CMS:** Create, edit, schedule, publish posts. Categories and tags. Featured images. Author attribution.
- **AI Generation:** Blog Agent (instruction 31) generates posts from market data, listing events, seasonal topics, neighbor profiles. Pulls context from properties.php, transactions.php, market indicators.
- **SEO Optimization:** Auto-generate title tags, meta descriptions, header structure, schema markup. Analyze posts for SEO score. Suggest improvements (keyword usage, readability, internal linking). Track keyword rankings.
- **Content Calendar:** Visual calendar of planned/published posts. Blog Agent suggests topics based on market activity and seasonal calendar.
- **Multi-Author:** Brokerage blog + per-agent blogs. Content syndication across sites.
- **Distribution:** Auto-post to social media (ties to social.php). Email newsletter compilation (ties to drips.php). RSS feed.
- **Analytics:** Page views, time on page, bounce rate, scroll depth, lead captures. Tied to Google Analytics if connected.
- **White-Label:** Blog design inherits tenant brand colors. Custom domain support. Template system.
- **Comment Moderation:** Optional reader comments (moderated). Ties to engagement.

**Architecture:** Separate blog.php module (like admin.php, email.php). Standard API endpoints. Integrates with AI inference (instruction 03), social distribution (instruction 11), and email/drips (email.php, drips.php).

---

## DATABASE SCHEMA

### Table: `blog_posts`
All blog content. Status workflow: draft → scheduled → published.

```sql
CREATE TABLE blog_posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT DEFAULT NULL,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL,  -- URL-friendly: "carmel-market-trends-march-2026"
  excerpt VARCHAR(500),  -- Short summary for listings/email
  content LONGTEXT,  -- Full post content (HTML)
  featured_image_url VARCHAR(500),
  featured_image_alt VARCHAR(255),  -- SEO alt text

  author_id INT NOT NULL,  -- admin.id
  is_featured BOOLEAN DEFAULT false,  -- Pinned to homepage

  -- Publishing & Scheduling
  status ENUM('draft', 'scheduled', 'published', 'archived') DEFAULT 'draft',
  published_at TIMESTAMP NULL,
  scheduled_for TIMESTAMP NULL,  -- If status='scheduled'
  unpublished_at TIMESTAMP NULL,  -- When archived

  -- Content Type
  post_type ENUM('standard', 'market_update', 'listing_feature', 'agent_profile', 'guest') DEFAULT 'standard',

  -- SEO Metadata
  meta_title VARCHAR(60),  -- Overrides auto-generated title tag
  meta_description VARCHAR(160),  -- For meta tag
  canonical_url VARCHAR(500),  -- For duplicate content avoidance
  focus_keyword VARCHAR(100),  -- Primary target keyword
  seo_score INT,  -- 0-100 (calculated, not stored, but cached here)
  seo_last_checked TIMESTAMP NULL,

  -- Schema Markup
  schema_markup JSON,  -- Structured data (NewsArticle, BlogPosting, etc.)

  -- Statistics
  view_count INT DEFAULT 0,
  unique_view_count INT DEFAULT 0,
  avg_time_on_page INT DEFAULT 0,  -- seconds
  bounce_rate DECIMAL(3,2) DEFAULT 0,  -- 0-1.0
  scroll_depth DECIMAL(3,2) DEFAULT 0,  -- % of page scrolled
  lead_captures INT DEFAULT 0,  -- Forms submitted on page
  social_shares INT DEFAULT 0,
  comments_count INT DEFAULT 0,

  -- Content Management
  allow_comments BOOLEAN DEFAULT true,
  is_indexed BOOLEAN DEFAULT true,  -- robots=index/noindex meta tag
  word_count INT,  -- Auto-calculated
  reading_time_minutes INT,  -- Auto-calculated (200 words/min)

  -- Syndication
  syndicate_to_agents BOOLEAN DEFAULT false,  -- Republish on agent blogs
  syndicated_at TIMESTAMP NULL,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (author_id) REFERENCES admins(id),
  INDEX idx_tenant_published (tenant_id, status, published_at DESC),
  INDEX idx_slug (tenant_id, slug),
  INDEX idx_focus_keyword (focus_keyword),
  UNIQUE KEY uq_slug (tenant_id, slug)
);
```

### Table: `blog_categories`
Taxonomy. Each post can belong to multiple categories.

```sql
CREATE TABLE blog_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT DEFAULT NULL,
  name VARCHAR(100) NOT NULL,  -- "Market Updates", "Luxury Lifestyle", "Carmel News"
  slug VARCHAR(100) NOT NULL,
  description TEXT,
  featured_image_url VARCHAR(500),
  post_count INT DEFAULT 0,  -- Cached count

  display_order INT DEFAULT 0,  -- For category sidebar ordering
  is_active BOOLEAN DEFAULT true,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  INDEX idx_tenant_active (tenant_id, is_active),
  UNIQUE KEY uq_slug (tenant_id, slug)
);
```

### Table: `blog_post_categories`
Junction table. Many-to-many posts to categories.

```sql
CREATE TABLE blog_post_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT DEFAULT NULL,
  post_id INT NOT NULL,
  category_id INT NOT NULL,

  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (post_id) REFERENCES blog_posts(id) ON DELETE CASCADE,
  FOREIGN KEY (category_id) REFERENCES blog_categories(id) ON DELETE CASCADE,
  UNIQUE KEY uq_post_category (post_id, category_id)
);
```

### Table: `blog_tags`
Lightweight taxonomy. Posts can have many tags.

```sql
CREATE TABLE blog_tags (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT DEFAULT NULL,
  name VARCHAR(50) NOT NULL,  -- "luxury", "carmel", "pricing", "spring-market"
  slug VARCHAR(50) NOT NULL,
  post_count INT DEFAULT 0,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  INDEX idx_tenant_slug (tenant_id, slug),
  UNIQUE KEY uq_slug (tenant_id, slug)
);
```

### Table: `blog_post_tags`
Junction table. Many-to-many posts to tags.

```sql
CREATE TABLE blog_post_tags (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT DEFAULT NULL,
  post_id INT NOT NULL,
  tag_id INT NOT NULL,

  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (post_id) REFERENCES blog_posts(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id) REFERENCES blog_tags(id) ON DELETE CASCADE,
  UNIQUE KEY uq_post_tag (post_id, tag_id)
);
```

### Table: `blog_comments`
Reader comments (optional, moderated). Ties to engagement.

```sql
CREATE TABLE blog_comments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT DEFAULT NULL,
  post_id INT NOT NULL,
  author_name VARCHAR(100) NOT NULL,
  author_email VARCHAR(100) NOT NULL,
  author_url VARCHAR(255),
  content TEXT NOT NULL,
  status ENUM('pending', 'approved', 'spam', 'trash') DEFAULT 'pending',

  ip_address VARCHAR(45),
  user_agent VARCHAR(500),

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  approved_at TIMESTAMP NULL,

  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (post_id) REFERENCES blog_posts(id) ON DELETE CASCADE,
  INDEX idx_post_status (post_id, status),
  INDEX idx_pending (status, created_at)
);
```

### Table: `blog_authors`
Author profiles. Can be agents, team members, guest writers.

```sql
CREATE TABLE blog_authors (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT DEFAULT NULL,
  admin_id INT,  -- FK: admins.id (if internal author)
  display_name VARCHAR(100) NOT NULL,
  bio TEXT,
  photo_url VARCHAR(500),
  email VARCHAR(100),
  website_url VARCHAR(255),
  social_facebook VARCHAR(255),
  social_instagram VARCHAR(255),
  social_linkedin VARCHAR(255),

  post_count INT DEFAULT 0,
  is_active BOOLEAN DEFAULT true,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (admin_id) REFERENCES admins(id),
  INDEX idx_tenant_active (tenant_id, is_active)
);
```

### Table: `blog_analytics`
Aggregated analytics per post per day. Ties to Google Analytics if connected.

```sql
CREATE TABLE blog_analytics (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT DEFAULT NULL,
  post_id INT NOT NULL,
  analytics_date DATE,  -- YYYY-MM-DD

  pageviews INT DEFAULT 0,
  unique_visitors INT DEFAULT 0,
  sessions INT DEFAULT 0,
  avg_session_duration INT DEFAULT 0,  -- seconds
  bounce_rate DECIMAL(3,2) DEFAULT 0,  -- 0-1.0
  avg_scroll_depth DECIMAL(3,2) DEFAULT 0,  -- 0-1.0
  clicks INT DEFAULT 0,
  form_submissions INT DEFAULT 0,

  traffic_source ENUM('organic', 'social', 'email', 'direct', 'referral') DEFAULT 'organic',

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (post_id) REFERENCES blog_posts(id) ON DELETE CASCADE,
  INDEX idx_post_date (post_id, analytics_date DESC),
  UNIQUE KEY uq_post_date_source (post_id, analytics_date, traffic_source)
);
```

### Table: `blog_content_calendar`
Planned topics and publishing schedule.

```sql
CREATE TABLE blog_content_calendar (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT DEFAULT NULL,
  topic VARCHAR(255) NOT NULL,  -- "Spring Market Predictions", "Carmel Dining Guide"
  topic_category VARCHAR(100),  -- 'market_update', 'lifestyle', 'news', 'education'
  suggested_keywords JSON,  -- ["spring market", "carmel real estate", "pricing trends"]
  assigned_to INT,  -- admin.id (agent writing or AI agent)
  scheduled_for DATE,  -- Target publish date
  status ENUM('planned', 'in_progress', 'pending_review', 'published', 'cancelled') DEFAULT 'planned',
  related_post_id INT,  -- blog_posts.id (if published)
  notes TEXT,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (assigned_to) REFERENCES admins(id),
  FOREIGN KEY (related_post_id) REFERENCES blog_posts(id),
  INDEX idx_tenant_scheduled (tenant_id, scheduled_for)
);
```

### Table: `blog_seo_audits`
SEO analysis results per post. Updated by SEO Agent (instruction 31).

```sql
CREATE TABLE blog_seo_audits (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT DEFAULT NULL,
  post_id INT NOT NULL,
  audit_date DATE,

  -- Title Optimization
  title_length INT,
  title_has_keyword BOOLEAN,
  title_score INT,  -- 0-100

  -- Meta Description
  meta_desc_length INT,
  meta_desc_has_keyword BOOLEAN,
  meta_desc_score INT,

  -- Header Structure
  h1_count INT,
  h2_count INT,
  h3_count INT,
  header_structure_score INT,

  -- Content
  keyword_density DECIMAL(3,2),  -- % of words matching focus keyword
  keyword_score INT,
  readability_score INT,  -- Flesch-Kincaid or similar
  word_count INT,

  -- Technical
  has_schema_markup BOOLEAN,
  internal_link_count INT,
  external_link_count INT,
  images_count INT,
  images_with_alt BOOLEAN,
  images_score INT,

  -- Overall
  overall_seo_score INT,  -- 0-100
  issues JSON,  -- Array of { severity, issue, suggestion }
  opportunities JSON,  -- Array of optimization suggestions

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (post_id) REFERENCES blog_posts(id) ON DELETE CASCADE,
  INDEX idx_post_date (post_id, audit_date DESC)
);
```

---

## API ENDPOINTS (blog.php)

All endpoints: `/api/blog.php?action=<action>`

### Post Management

**POST createPost**
```
Body: { title, content, excerpt?, featured_image_url?, author_id?,
        post_type?, focus_keyword?, category_ids: [ ], tag_ids: [ ],
        status? ('draft'|'scheduled'), scheduled_for? }
Response: { success: true, data: { post_id, post, slug } }
Rules: Session required. Auto-generate slug from title. Auto-set created_at.
       If status='scheduled', store scheduled_for and set cron to publish.
```

**GET getPost**
```
Query: ?id=<post_id> or ?slug=<slug>
Response: { success: true, data: { post, author, categories, tags, analytics, comments_approved } }
Rules: If not published, only author/owner can view. Otherwise public.
       Increment view_count. Log analytics.
```

**GET getPosts**
```
Query: ?status=published&category_id=?&tag_id=?&author_id=?&limit=20&offset=0&sort=date
Response: { success: true, data: [ posts ], total_count }
Rules: Default status='published'. Filter by category/tag/author optional.
       Sort options: date (DESC), views, engagement, trending.
       Return title, excerpt, featured_image_url, author, category, published_at.
```

**PATCH updatePost**
```
Body: { id, title?, content?, excerpt?, featured_image_url?, focus_keyword?,
        category_ids?, tag_ids?, status?, scheduled_for? }
Response: { success: true, data: { post } }
Rules: Only author or owner can edit. If status changes to 'published', set published_at=now.
       Regenerate slug only if title changes. Re-run SEO audit.
```

**DELETE deletePost**
```
Query: ?id=<post_id>
Response: { success: true }
Rules: Only author/owner. Soft-delete: set status='archived', unpublished_at=now.
```

### Publishing & Scheduling

**POST publishPost**
```
Query: ?id=<post_id>
Response: { success: true, data: { post, published_at } }
Rules: Set status='published', published_at=now. Trigger social distribution (social.php).
       Trigger email notification (drips.php — add to newsletter queue).
       Log board_activity.
```

**POST schedulePost**
```
Body: { id, scheduled_for }
Response: { success: true, data: { post, scheduled_for } }
Rules: Set status='scheduled', scheduled_for=provided_datetime.
       Create cron task to publish at scheduled_for.
```

**PATCH unschedulePost**
```
Query: ?id=<post_id>
Response: { success: true, data: { post } }
Rules: Set status='draft'. Remove scheduled cron task.
```

### Content Management

**GET getContentCalendar**
```
Query: ?month=2026-04&status=?
Response: { success: true, data: { calendar: [ items ], suggested_topics } }
Rules: Return calendar items for month. Include planned, in_progress, published.
       Suggest topics based on: seasonal calendar, market data, recent sales.
```

**POST addCalendarItem**
```
Body: { topic, topic_category, suggested_keywords, scheduled_for, assigned_to? }
Response: { success: true, data: { item } }
Rules: Create blog_content_calendar row. If assigned_to is AI agent, trigger generation.
```

**PATCH updateCalendarItem**
```
Body: { id, topic?, status?, assigned_to?, notes? }
Response: { success: true, data: { item } }
Rules: Update calendar item. If status='published', link to related_post_id.
```

### Categories & Tags

**GET getCategories**
```
Query: ?active_only=true
Response: { success: true, data: [ categories ] }
Rules: Return all categories with post_count.
```

**POST createCategory**
```
Body: { name, slug?, description?, featured_image_url? }
Response: { success: true, data: { category } }
Rules: Auto-generate slug if not provided. Validate slug uniqueness.
```

**GET getTags**
```
Query: ?search=?&limit=50
Response: { success: true, data: [ tags ] }
Rules: Return all tags or search by name. Include post_count per tag.
```

**POST addTag**
```
Body: { post_id, tag_name }
Response: { success: true, data: { tag } }
Rules: Create tag if doesn't exist. Add to post via blog_post_tags.
```

### AI Content Generation

**POST generateAIPost**
```
Body: { topic, topic_category, focus_keyword, length? (800|1200|1500),
        include_images?, image_count?, include_internal_links? }
Response: { success: true, data: { ai_output_id, content_preview, title, image_prompts } }
Rules: Tie to instruction 31 (Blog Agent). Route to ai_agents.php.
       Return ai_agent_outputs.id. Queues for approval if require_approval=true.
       Generate featured image prompt (for Collov API instruction 12).
       Suggest 3 internal links to existing posts.
```

**GET getAIOutputs**
```
Query: ?status=pending_approval&limit=20
Response: { success: true, data: [ outputs ] }
Rules: Return AI-generated posts awaiting approval. Include feedback if rejected.
```

**PATCH approveAIPost**
```
Body: { ai_output_id, title?, focus_keyword?, featured_image_url?,
        publish_immediately? }
Response: { success: true, data: { post_id, post } }
Rules: Create blog_posts row from ai_agent_outputs.
       Set author_id to AI agent (create special "AI Agent" author).
       If publish_immediately=true, set status='published'. Else 'draft'.
       Mark ai_agent_outputs as approved.
```

### SEO Optimization

**GET optimizeSEO**
```
Query: ?post_id=<id>
Response: { success: true, data: { suggestions: [ { type, current, suggested, impact } ] } }
Rules: Run SEO audit (tie to instruction 31, SEO Agent).
       Analyze: title tag, meta desc, headers, keyword usage, internal links.
       Return specific suggestions with before/after.
```

**POST applySEOSuggestion**
```
Body: { post_id, suggestion_type, new_value }
Response: { success: true, data: { post, audit_results } }
Rules: Apply suggestion (e.g., update meta_title, add internal link).
       Re-run SEO audit. Store in blog_seo_audits.
```

**GET getSEOAudit**
```
Query: ?post_id=<id>
Response: { success: true, data: { audit, score, issues, opportunities } }
Rules: Return latest SEO audit for post. Include overall_seo_score and detailed breakdown.
```

### Analytics

**GET getPostAnalytics**
```
Query: ?post_id=<id>&date_range=7d&breakdown=traffic_source
Response: { success: true, data: { summary: { views, unique, avg_duration },
            by_date: [ ], by_traffic_source: [ ], chart_data } }
Rules: Aggregate blog_analytics. Sum views, uniques, form submissions.
       Optional tie to Google Analytics API for real-time data.
```

**GET getBlogDashboard**
```
Response: { success: true, data: { total_posts, total_views, trending_posts: [ ],
            recent_posts: [ ], upcoming_calendar: [ ], ai_approval_queue_count } }
Rules: Overview dashboard. Show top posts by engagement, upcoming scheduled posts, AI queue.
```

### Comments

**GET getComments**
```
Query: ?post_id=<id>&status=approved&limit=50
Response: { success: true, data: [ comments ] }
Rules: Return comments for post, filtered by status.
```

**PATCH moderateComment**
```
Body: { comment_id, status ('approved'|'spam'|'trash') }
Response: { success: true }
Rules: Update comment status. Log moderation action.
```

### Distribution

**GET getRSSFeed**
```
Response: XML RSS 2.0 feed (standard)
Rules: Include last 20 published posts. Standard RSS structure: title, link, description, pubDate, author.
```

**POST syndicatePost**
```
Query: ?post_id=<id>&target_agents=[ agent_ids ]
Response: { success: true, data: { syndicated_count } }
Rules: Copy post to agent blogs (if syndicate_to_agents=true). Customize intro per agent.
       Set published_at on agent blogs. Add canonical back to original.
```

---

## IMPLEMENTATION STEPS

### Phase 1: Backend Infrastructure (Days 1-2)

1. **Create blog.php**
   - Copy auth/config pattern from admin.php
   - Auto-migrate all 10 tables on first call
   - Implement all CRUD endpoints
   - PDO prepared statements, tenant_id filters
   - Standard JSON response pattern

2. **Add blog routes to admin.php**
   - Route `?action=blog_*` to blog.php

3. **Integration with ai_agents.php**
   - blog.php?action=generateAIPost calls ai_agents.php
   - Pass context: market data, recent sales, focus_keyword
   - Returns ai_agent_outputs.id
   - Queues for approval or auto-saves (per config)

4. **Post Scheduling Cron Job**
   - New cron job: `blog_publish_scheduled` (every 15 minutes)
   - Fetch posts with status='scheduled' and scheduled_for <= NOW()
   - Publish each post (set status='published', published_at=now)
   - Trigger social distribution + email notification

### Phase 2: Frontend UI (Days 3-5)

5. **Add "Blog" tab to fogbreak.html**
   - Between "Documents" and another tab
   - Subtabs: "All Posts", "Content Calendar", "AI Drafts", "Analytics"

6. **Build "All Posts" view**
   - Table: title, author, category, status, published_at, views, engagement
   - Filters: status, category, author, date range
   - Actions: edit, delete (soft), view, promote to featured
   - Quick create button for new post
   - Draft count badge

7. **Build Post Editor**
   - TinyMCE or Quill rich text editor
   - Featured image picker + alt text
   - Title, excerpt, focus_keyword, author
   - Category/tag picker (autocomplete)
   - Meta title/description for SEO
   - Status dropdown: draft, scheduled (with datetime picker), published
   - Preview button (renders as would appear on blog)
   - Save as draft, Publish, Schedule buttons

8. **Build Content Calendar**
   - Month view with colored blocks for planned/in_progress/published
   - Click to expand item: topic, assigned_to, status, notes
   - Add item modal: topic, category, keywords, scheduled_for, assign_to
   - Suggested topics section (generated by Blog Agent)
   - Drag-to-reschedule (update scheduled_for)

9. **Build AI Drafts Queue**
   - Pending AI outputs awaiting approval
   - Preview: title, content, image prompts
   - Approve button: opens modal to confirm title, keyword, publish_immediately checkbox
   - Reject button: feedback textarea
   - Request revision button

### Phase 3: SEO & Analytics (Days 6-8)

10. **Build SEO Optimization Panel (in post editor)**
    - Show SEO score (0-100) prominently
    - Breakdown: title (25 pts), meta desc (20), headers (15), keyword (20), links (10), images (10)
    - Green/yellow/red indicators for each category
    - Suggestions list: "Add more internal links", "Title too long (70 chars, target 55-60)"
    - "Optimize Now" button: calls SEO Agent, returns suggestions
    - Apply button per suggestion: updates field, re-calculates score

11. **Build Analytics Dashboard**
    - Traffic chart (line graph): views, unique visitors over time
    - Traffic source breakdown (pie): organic, social, email, direct, referral
    - Top posts by engagement (table): views, time on page, bounce rate, conversions
    - Recent posts (cards): thumbnail, title, views, publish date
    - Filter by date range

12. **SEO Audit Cron Job**
    - New cron job: `blog_seo_audit` (daily, 11 PM)
    - For all published posts, run SEO audit (tie to instruction 31, SEO Agent)
    - Store results in blog_seo_audits
    - Alert admin if post score drops significantly

### Phase 4: AI Integration & Publishing (Days 9-11)

13. **Blog Agent Integration (instruction 31)**
    - Blog Agent runs 2-3x weekly (per cron_expression)
    - Context: recent listings, market trends, seasonal calendar, content calendar
    - Generate post title, content (1200 words), suggested keywords
    - Call Collov API for featured image (instruction 12)
    - Save to ai_agent_outputs, status='pending_approval'
    - Admin reviews in "AI Drafts" queue

14. **Post Publishing & Distribution**
    - On publish: status='published', published_at=now
    - Trigger `blog_distribute_post` cron job:
      - Social distribution: call social.php to create social posts (instruction 11)
      - Email distribution: add to newsletter queue (drips.php)
      - RSS feed: post included in next feed generation
      - Board activity: create interaction record (clients.interactions)
    - Update post analytics: set initial view_count=0

15. **Google Analytics Integration (optional)**
    - If tenant has GA4 property linked, pull daily analytics
    - Cron job: `blog_sync_analytics` (11:30 PM, after traffic settles)
    - Call Google Analytics API for yesterday's data
    - Update blog_analytics per post

16. **Newsletter Compilation (instruction 11 + drips.php)**
    - New drip campaign: "Weekly Blog Digest"
    - Each Friday: collect posts published this week
    - Email template: featured image, title, excerpt, read more link
    - Send to subscribers

### Phase 5: Multi-Author & Syndication (Days 12-14)

17. **Multi-Author Support**
    - Create blog_authors entries for each author (agents, team members, guests)
    - Author profile page: bio, photo, social links, post list
    - In post editor, select author from dropdown
    - Author pages linked from blog post footer

18. **Brokerage Blog vs. Agent Blogs**
    - Tenant can have: main brokerage blog, per-agent blogs
    - Same post can be published to main blog + agent blogs (syndication)
    - If syndicate_to_agents=true, publish copy to each agent's blog
    - Customize intro per agent ("Sharon Matthams shares her insights...")
    - Canonical URL points to original post

19. **RSS Feed**
    - Endpoint: `/api/blog.php?action=getRSSFeed`
    - Returns RSS 2.0 XML for all published posts
    - Include category feeds: `/api/blog.php?action=getRSSFeed&category=market_updates`
    - Auto-discovered in blog page <head>

20. **Syndication Distribution**
    - Cron job: `blog_syndicate_posts` (runs after publish)
    - For each published post with syndicate_to_agents=true:
      - Copy post to each agent's blog (create separate blog_posts row with canonical back to original)
      - Customize: agent's name in intro, use agent's photo (optional)
      - Set published_at to original publish time
      - Email agent notification with link

### Phase 6: Integration Points (Days 15-16)

21. **Social Distribution (instruction 11)**
    - On post publish, call social.php: createSocialPost()
    - Social posts include: title, excerpt, featured_image_url, link to blog
    - Platforms: Facebook, Instagram (carousel or reel), LinkedIn, TikTok (if applicable)
    - Auto-post or queue for approval (per social agent config)

22. **Email Distribution (drips.php + email.php)**
    - New drip template: "Blog Post Published"
    - Triggered by blog publish event
    - Email includes: featured image, title, excerpt, read more link
    - Send to subscribers (or newsletter list)

23. **CRM Interaction Logging**
    - Every published post creates interaction record for brokerage/agent
    - Log type: 'blog_published'
    - Description: "Published blog post: 'Carmel Market Trends'"
    - Visible in activity feed

24. **White-Label Design**
    - Blog inherits tenant brand colors from config
    - CSS variables: --blog-primary, --blog-accent, --blog-bg, --blog-text
    - Custom logo, header, footer per tenant
    - Optional: custom domain (e.g., blog.matthamsgroup.com)

---

## TESTING CRITERIA

### Unit Tests (PHP)
- [ ] createPost: valid inputs, auto-slug generation, tenant isolation
- [ ] updatePost: only author/owner can edit, slug regeneration if title changes
- [ ] publishPost: sets published_at, triggers distribution, logs activity
- [ ] schedulePost: stores scheduled_for, creates cron task
- [ ] generateAIPost: calls ai_agents.php correctly, returns ai_output_id, queues for approval
- [ ] optimizeSEO: returns suggestions, applies changes, recalculates score
- [ ] All queries: PDO binding, tenant_id filters

### Integration Tests
- [ ] End-to-end publish: draft → publish → social distribution → email sent → analytics tracked
- [ ] AI post generation: Blog Agent generates → queue → approve → publish → live on blog
- [ ] SEO audit: post published → daily cron runs audit → results stored → admin sees score
- [ ] Syndication: post with syndicate_to_agents=true → published → copied to agent blogs with canonical
- [ ] Newsletter compilation: posts published → weekly drip collects them → email sent
- [ ] Multi-tenant isolation: tenant A's posts don't appear in tenant B's blog

### Frontend Tests
- [ ] All Posts view loads, filters work, actions (edit, delete, publish) work
- [ ] Post editor: rich text editing, image upload, metadata input
- [ ] Content Calendar: month view renders, add item modal works, drag-to-reschedule
- [ ] AI Drafts: queue loads, approve/reject buttons work, create post from AI output
- [ ] Analytics: charts render, traffic source breakdown, trending posts
- [ ] SEO panel: score displays, suggestions generate, apply suggestion works

### Security Tests
- [ ] Tenant isolation: user from tenant A cannot view/edit posts from tenant B
- [ ] Permission checks: non-author cannot edit published posts (but can comment if enabled)
- [ ] Content sanitization: HTML in blog_posts escaped before render (prevent XSS)
- [ ] Comment moderation: unapproved comments not visible publicly
- [ ] Fair Housing: all AI-generated content scanned for discrimination language (instruction 29)

### Performance Tests
- [ ] getPosts: loads 100 posts with pagination in < 500ms
- [ ] SEO audit: full audit (analyze title, headers, links, readability) in < 2 seconds
- [ ] Blog page render: home page with 20 post previews loads in < 1 second
- [ ] Analytics sync: daily pull from Google Analytics for 50 posts in < 5 seconds

---

## INTEGRATION POINTS

| System | Connection | Notes |
|--------|-----------|-------|
| **ai_agents.php** (instruction 31) | Blog Agent generates posts | AI-generated posts queue for approval |
| **social.php** (instruction 11) | Social distribution | Auto-post to Facebook, Instagram, LinkedIn, TikTok after publish |
| **drips.php (email.php)** | Newsletter compilation, post notifications | Weekly digest, send to subscribers |
| **ai_inference.php** (instruction 03) | Ollama for content generation, SEO optimization | LLM calls for post generation and SEO suggestions |
| **Collov AI API** (instruction 12) | Featured image generation | Generate featured image for each AI post |
| **properties.php** | Context for content generation | Recent listings, listings featured in posts |
| **transactions.php** | Market data, sold prices | Context for market update posts |
| **admins.php** | Author attribution, approval | author_id, reviewer_id |
| **tenants.php** | Multi-tenant isolation, brand config | Separate blogs per tenant, brand colors |
| **Google Analytics API** (optional) | Traffic analytics | Daily sync of pageviews, users, engagement |
| **Google Search Console API** (optional) | Keyword ranking tracking | SEO Agent tracks ranking changes |
| **clients.php** | CRM interaction logging | Blog publish creates interaction record |
| **cron.php** | Scheduling, publishing, analytics sync | 4 new cron jobs: publish_scheduled, seo_audit, sync_analytics, syndicate |

---

## DEPLOYMENT CHECKLIST

- [ ] blog.php created, all endpoints tested
- [ ] All 10 tables auto-migrated on first API call
- [ ] "Blog" tab added to fogbreak.html
- [ ] "All Posts" view complete with filters, search, actions
- [ ] Post editor complete with rich text, metadata, status workflow
- [ ] Content Calendar view complete with month view, add item, drag-to-reschedule
- [ ] AI Drafts queue complete with approve/reject/revision
- [ ] SEO optimization panel integrated in post editor
- [ ] Analytics dashboard complete with charts and trending posts
- [ ] Blog Agent (instruction 31) integrated, generating posts 2-3x weekly
- [ ] Post publishing cron job created (`blog_publish_scheduled`)
- [ ] Social distribution cron job created (`blog_distribute_post`)
- [ ] SEO audit cron job created (`blog_seo_audit`)
- [ ] Google Analytics sync cron job created (optional, `blog_sync_analytics`)
- [ ] Newsletter template created (weekly blog digest)
- [ ] Syndication cron job created (`blog_syndicate_posts`)
- [ ] RSS feed endpoint working
- [ ] Multi-author support complete (author profiles, author filter)
- [ ] White-label design applied (brand colors, logo, custom domain support)
- [ ] Fair Housing audit integrated (scan AI-generated copy)
- [ ] Comment moderation system operational
- [ ] Mobile blog design responsive
- [ ] GitHub commit + deploy to Bluehost

---

## NOTES

- **Content Strategy:** Blog Agent (instruction 31) generates topics based on: seasonal calendar (spring selling tips, holiday prep), recent market data (sales, price trends), listing events (new listing featured), agent milestones. Admin can also manually plan topics in content calendar.
- **SEO Best Practices:** All posts include meta_title (55-60 chars), meta_description (150-160), focus_keyword, H1/H2 hierarchy, internal links (3-5 per post), featured image with alt text. SEO Agent audits weekly and suggests improvements.
- **Keyword Strategy:** Each post targets 1 primary keyword (focus_keyword). Blog Agent ensures keyword appears in title, headers, first 100 words, and meta description. Avoid keyword stuffing.
- **Internal Linking:** SEO Agent suggests 3-5 internal links per new post. Links to related posts (same category/topic) and high-authority pages (location guides, mortgage guides).
- **Featured Images:** All posts include featured image (required). Blog Agent generates image prompts for Collov API (instruction 12). Agents can also upload custom images.
- **Readability:** Reading time calculated as word_count / 200 (words per minute). Target 800-1500 words for SEO. Paragraph breaks, sub-headers improve readability (scored by SEO audit).
- **Newsletter:** Blog posts auto-compiled into weekly newsletter. Digest sent Friday evening. Includes: top 3-5 posts from week, featured image, excerpt, read more link.
- **Author Attribution:** Every post has author (agent, team member, or "AI Agent"). Author photo + bio included in post footer. Author pages list all posts by that author.
- **Comments:** Optional reader comments (allow_comments=true/false per post). Moderated before publication (status='pending', admin approves). Helps engagement + feedback.
- **Syndication:** If syndicate_to_agents=true, post copied to all agent blogs. Customizable intro: "Sharon Matthams shares her insights on the Carmel market..." Canonical URL prevents SEO duplicate content penalty.
- **Analytics:** Views tracked via JavaScript (unique visitors per session). Form submissions tied to blog posts. Social shares measured via social platform APIs. Lead captures tracked if CTA form on page.
- **Scheduling:** Posts can be scheduled for future publish. Cron job checks every 15 minutes and publishes when scheduled_for <= NOW(). Good for planning content calendar.
- **Fair Housing Compliance:** All AI-generated copy scanned for protected characteristics (age, race, familial status, disability). Red-flag words: "new", "modern" (age-coded), "established community", "quiet area", "near schools", references to religion/ethnicity. Admin reviews before approval.