Strategy Management Module Plan¶

Overview¶

The Strategy Management Module handles updatable strategies and client configurations for the content generation system. This module stores and manages client-specific settings, SEO rules, content preferences, and prompt templates through Supabase, with a Next.js UI for administration.

Key Features¶

Client Strategy Storage: Manage strategies per client (PASCO, agencies, sub-clients)
Template Management: Store and version prompt templates
SEO Rules Configuration: Dynamic SEO strategy updates
Multi-tenancy Support: Hierarchical client structure with row-level security
Version Control: Track strategy changes over time
Real-time Updates: Propagate changes to CPM/IM modules

Architecture¶

Database Schema (Supabase)¶

-- Clients table (hierarchical structure)
CREATE TABLE clients (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    parent_id UUID REFERENCES clients(id),
    name TEXT NOT NULL,
    type TEXT CHECK (type IN ('root', 'agency', 'customer')),
    settings JSONB DEFAULT '{}',
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Strategies table
CREATE TABLE strategies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    client_id UUID REFERENCES clients(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    description TEXT,
    config JSONB NOT NULL,
    version INTEGER DEFAULT 1,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID REFERENCES auth.users(id)
);

-- Strategy history for version control
CREATE TABLE strategy_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    strategy_id UUID REFERENCES strategies(id),
    version INTEGER NOT NULL,
    config JSONB NOT NULL,
    changed_by UUID REFERENCES auth.users(id),
    change_reason TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- SEO rules configuration
CREATE TABLE seo_rules (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    client_id UUID REFERENCES clients(id),
    rule_type TEXT NOT NULL, -- 'keywords', 'snippets', 'local', etc.
    rules JSONB NOT NULL,
    priority INTEGER DEFAULT 0,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Content preferences
CREATE TABLE content_preferences (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    client_id UUID REFERENCES clients(id),
    content_type TEXT NOT NULL, -- 'blog', 'social', 'local'
    preferences JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(client_id, content_type)
);

-- Indexes
CREATE INDEX idx_clients_parent ON clients(parent_id);
CREATE INDEX idx_strategies_client ON strategies(client_id) WHERE active = true;
CREATE INDEX idx_seo_rules_client ON seo_rules(client_id) WHERE active = true;
CREATE INDEX idx_content_prefs_client ON content_preferences(client_id);

-- Row Level Security
ALTER TABLE clients ENABLE ROW LEVEL SECURITY;
ALTER TABLE strategies ENABLE ROW LEVEL SECURITY;
ALTER TABLE seo_rules ENABLE ROW LEVEL SECURITY;
ALTER TABLE content_preferences ENABLE ROW LEVEL SECURITY;

-- RLS Policies (example)
CREATE POLICY "Users can view their organization's data" ON clients
    FOR SELECT USING (
        id IN (
            SELECT client_id FROM user_clients 
            WHERE user_id = auth.uid()
        )
    );

Sample Data Structure¶

-- Insert sample clients
INSERT INTO clients (id, name, type, settings) VALUES
('550e8400-e29b-41d4-a716-446655440001', 'PASCO Scientific', 'root', 
 '{"industry": "education", "focus": "science", "tone": "educational"}'),
('550e8400-e29b-41d4-a716-446655440002', 'Heaviside Digital', 'agency',
 '{"industry": "marketing", "focus": "local_seo", "tone": "professional"}'),
('550e8400-e29b-41d4-a716-446655440003', 'Cincinnati Electrician Co', 'customer',
 '{"location": "Cincinnati, OH", "service": "electrical", "parent_agency": "heaviside"}');

-- Insert strategies
INSERT INTO strategies (client_id, name, config) VALUES
('550e8400-e29b-41d4-a716-446655440001', 'PASCO Science Blog Strategy',
'{
  "content_guidelines": {
    "min_words": 1000,
    "max_words": 2000,
    "required_sections": ["introduction", "explanation", "examples", "conclusion"],
    "citation_required": true,
    "difficulty_level": "high_school"
  },
  "seo_focus": {
    "primary_keywords": ["science education", "STEM learning"],
    "keyword_density": 0.02,
    "meta_description_length": 160
  },
  "tone": {
    "formality": "professional",
    "voice": "authoritative",
    "engagement": "educational"
  }
}');

-- Insert SEO rules
INSERT INTO seo_rules (client_id, rule_type, rules) VALUES
('550e8400-e29b-41d4-a716-446655440001', 'keywords',
'{
  "primary_keywords": {
    "science": {"weight": 1.0, "variations": ["scientific", "sciences"]},
    "education": {"weight": 0.8, "variations": ["educational", "learning"]},
    "experiment": {"weight": 0.7, "variations": ["experimental", "lab"]}
  },
  "lsi_keywords": ["STEM", "classroom", "curriculum", "students"],
  "negative_keywords": ["buy", "cheap", "discount"]
}');

-- Insert content preferences
INSERT INTO content_preferences (client_id, content_type, preferences) VALUES
('550e8400-e29b-41d4-a716-446655440001', 'blog',
'{
  "structure": {
    "use_headers": true,
    "include_toc": true,
    "add_summary": true,
    "include_faqs": true
  },
  "media": {
    "include_images": true,
    "image_alt_pattern": "Scientific concept: {topic}",
    "include_diagrams": true
  },
  "engagement": {
    "add_questions": true,
    "include_examples": true,
    "add_exercises": true
  }
}');

API Design (Supabase Functions or Next.js API Routes)¶

Strategy API Endpoints¶

// /api/strategies/[clientId].ts
import { createClient } from '@supabase/supabase-js'
import type { NextApiRequest, NextApiResponse } from 'next'

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_KEY!
)

export default async function handler(
  req: NextApiRequest,
  res: NextApiResponse
) {
  const { clientId } = req.query

  if (req.method === 'GET') {
    // Get active strategy for client
    const { data, error } = await supabase
      .from('strategies')
      .select(`
        *,
        seo_rules (
          rule_type,
          rules
        ),
        content_preferences (
          content_type,
          preferences
        )
      `)
      .eq('client_id', clientId)
      .eq('active', true)
      .single()

    if (error) return res.status(404).json({ error: 'Strategy not found' })
    return res.status(200).json(data)
  }

  if (req.method === 'PUT') {
    // Update strategy (creates new version)
    const { config, change_reason } = req.body

    // Get current strategy
    const { data: current } = await supabase
      .from('strategies')
      .select('*')
      .eq('client_id', clientId)
      .eq('active', true)
      .single()

    if (!current) return res.status(404).json({ error: 'No active strategy' })

    // Create history record
    await supabase
      .from('strategy_history')
      .insert({
        strategy_id: current.id,
        version: current.version,
        config: current.config,
        changed_by: req.headers['user-id'], // From auth
        change_reason
      })

    // Update strategy
    const { data, error } = await supabase
      .from('strategies')
      .update({
        config,
        version: current.version + 1,
        updated_at: new Date().toISOString()
      })
      .eq('id', current.id)
      .select()

    if (error) return res.status(500).json({ error: error.message })
    return res.status(200).json(data)
  }

  return res.status(405).json({ error: 'Method not allowed' })
}

Template Management API¶

// /api/templates/index.ts
export async function getTemplates(clientId: string, contentType?: string) {
  let query = supabase
    .from('prompt_templates')
    .select('*')
    .eq('client_id', clientId)
    .eq('active', true)

  if (contentType) {
    query = query.eq('content_type', contentType)
  }

  const { data, error } = await query
  return { data, error }
}

export async function updateTemplate(
  templateId: string,
  updates: Partial<PromptTemplate>
) {
  const { data, error } = await supabase
    .from('prompt_templates')
    .update({
      ...updates,
      updated_at: new Date().toISOString()
    })
    .eq('id', templateId)
    .select()

  return { data, error }
}

Next.js UI Components¶

Strategy Editor Component¶

// components/StrategyEditor.tsx
import { useState, useEffect } from 'react'
import { createClient } from '@supabase/supabase-js'
import { Card, Tabs, TabsList, TabsTrigger, TabsContent } from '@/components/ui'

interface StrategyEditorProps {
  clientId: string
}

export function StrategyEditor({ clientId }: StrategyEditorProps) {
  const [strategy, setStrategy] = useState(null)
  const [loading, setLoading] = useState(true)
  const supabase = createClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
  )

  useEffect(() => {
    loadStrategy()
  }, [clientId])

  const loadStrategy = async () => {
    const { data, error } = await supabase
      .from('strategies')
      .select('*')
      .eq('client_id', clientId)
      .eq('active', true)
      .single()

    if (data) setStrategy(data)
    setLoading(false)
  }

  const updateStrategy = async (updates: any) => {
    const response = await fetch(`/api/strategies/${clientId}`, {
      method: 'PUT',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({
        config: { ...strategy.config, ...updates },
        change_reason: 'Manual update via UI'
      })
    })

    if (response.ok) {
      const updated = await response.json()
      setStrategy(updated)
    }
  }

  if (loading) return <div>Loading...</div>

  return (
    <Card className="p-6">
      <h2 className="text-2xl font-bold mb-4">Strategy Configuration</h2>

      <Tabs defaultValue="content">
        <TabsList>
          <TabsTrigger value="content">Content Guidelines</TabsTrigger>
          <TabsTrigger value="seo">SEO Rules</TabsTrigger>
          <TabsTrigger value="tone">Tone & Voice</TabsTrigger>
          <TabsTrigger value="templates">Templates</TabsTrigger>
        </TabsList>

        <TabsContent value="content">
          <ContentGuidelinesEditor 
            guidelines={strategy?.config?.content_guidelines}
            onUpdate={(guidelines) => updateStrategy({ content_guidelines: guidelines })}
          />
        </TabsContent>

        <TabsContent value="seo">
          <SEORulesEditor
            clientId={clientId}
            onUpdate={loadStrategy}
          />
        </TabsContent>

        <TabsContent value="tone">
          <ToneVoiceEditor
            settings={strategy?.config?.tone}
            onUpdate={(tone) => updateStrategy({ tone })}
          />
        </TabsContent>

        <TabsContent value="templates">
          <TemplateManager clientId={clientId} />
        </TabsContent>
      </Tabs>
    </Card>
  )
}

SEO Rules Editor¶

// components/SEORulesEditor.tsx
import { useState } from 'react'
import { Input, Button, Badge } from '@/components/ui'

interface SEORulesEditorProps {
  clientId: string
  onUpdate: () => void
}

export function SEORulesEditor({ clientId, onUpdate }: SEORulesEditorProps) {
  const [keywords, setKeywords] = useState<any>({})
  const [newKeyword, setNewKeyword] = useState('')
  const [newWeight, setNewWeight] = useState(1.0)

  const addKeyword = () => {
    if (newKeyword) {
      setKeywords({
        ...keywords,
        [newKeyword]: { weight: newWeight, variations: [] }
      })
      setNewKeyword('')
      setNewWeight(1.0)
    }
  }

  const saveRules = async () => {
    const response = await fetch(`/api/seo-rules/${clientId}`, {
      method: 'PUT',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({
        rule_type: 'keywords',
        rules: { primary_keywords: keywords }
      })
    })

    if (response.ok) {
      onUpdate()
    }
  }

  return (
    <div className="space-y-4">
      <h3 className="text-lg font-semibold">Keyword Configuration</h3>

      <div className="flex gap-2">
        <Input
          placeholder="Keyword"
          value={newKeyword}
          onChange={(e) => setNewKeyword(e.target.value)}
        />
        <Input
          type="number"
          placeholder="Weight"
          value={newWeight}
          onChange={(e) => setNewWeight(parseFloat(e.target.value))}
          className="w-24"
          step="0.1"
          min="0"
          max="1"
        />
        <Button onClick={addKeyword}>Add</Button>
      </div>

      <div className="space-y-2">
        {Object.entries(keywords).map(([keyword, config]: [string, any]) => (
          <div key={keyword} className="flex items-center gap-2">
            <Badge>{keyword}</Badge>
            <span className="text-sm text-gray-500">Weight: {config.weight}</span>
            <Button 
              size="sm" 
              variant="ghost"
              onClick={() => {
                const { [keyword]: _, ...rest } = keywords
                setKeywords(rest)
              }}
            >
              Remove
            </Button>
          </div>
        ))}
      </div>

      <Button onClick={saveRules} className="w-full">Save SEO Rules</Button>
    </div>
  )
}

Client Hierarchy Manager¶

// components/ClientHierarchy.tsx
import { useState, useEffect } from 'react'
import { ChevronRight, ChevronDown, Building, Store, User } from 'lucide-react'

interface Client {
  id: string
  name: string
  type: 'root' | 'agency' | 'customer'
  children?: Client[]
}

export function ClientHierarchy({ onSelectClient }: { onSelectClient: (id: string) => void }) {
  const [clients, setClients] = useState<Client[]>([])
  const [expanded, setExpanded] = useState<Set<string>>(new Set())

  useEffect(() => {
    loadClients()
  }, [])

  const loadClients = async () => {
    // Load hierarchical client structure
    const { data } = await supabase
      .from('clients')
      .select('*')
      .order('type', { ascending: true })

    // Build tree structure
    const tree = buildClientTree(data)
    setClients(tree)
  }

  const buildClientTree = (clients: any[]): Client[] => {
    const map = new Map()
    const roots: Client[] = []

    // First pass: create all nodes
    clients.forEach(client => {
      map.set(client.id, { ...client, children: [] })
    })

    // Second pass: build tree
    clients.forEach(client => {
      if (client.parent_id) {
        const parent = map.get(client.parent_id)
        if (parent) {
          parent.children.push(map.get(client.id))
        }
      } else {
        roots.push(map.get(client.id))
      }
    })

    return roots
  }

  const toggleExpand = (clientId: string) => {
    const newExpanded = new Set(expanded)
    if (newExpanded.has(clientId)) {
      newExpanded.delete(clientId)
    } else {
      newExpanded.add(clientId)
    }
    setExpanded(newExpanded)
  }

  const renderClient = (client: Client, level: number = 0) => {
    const hasChildren = client.children && client.children.length > 0
    const isExpanded = expanded.has(client.id)

    const Icon = client.type === 'root' ? Building : 
                 client.type === 'agency' ? Store : User

    return (
      <div key={client.id}>
        <div 
          className={`flex items-center gap-2 p-2 hover:bg-gray-100 cursor-pointer`}
          style={{ paddingLeft: `${level * 20 + 8}px` }}
          onClick={() => onSelectClient(client.id)}
        >
          {hasChildren && (
            <button
              onClick={(e) => {
                e.stopPropagation()
                toggleExpand(client.id)
              }}
              className="p-1"
            >
              {isExpanded ? <ChevronDown size={16} /> : <ChevronRight size={16} />}
            </button>
          )}
          {!hasChildren && <div className="w-6" />}
          <Icon size={16} />
          <span className="font-medium">{client.name}</span>
          <Badge variant="outline" className="ml-auto text-xs">
            {client.type}
          </Badge>
        </div>
        {isExpanded && client.children?.map(child => 
          renderClient(child, level + 1)
        )}
      </div>
    )
  }

  return (
    <div className="border rounded-lg">
      {clients.map(client => renderClient(client))}
    </div>
  )
}

Integration with CPM/IM¶

Strategy Loader for CPM¶

# strategy_loader.py
from typing import Dict, Optional
import asyncio
from datetime import datetime, timedelta

class StrategyLoader:
    def __init__(self, supabase_client):
        self.supabase = supabase_client
        self.cache = {}
        self.cache_ttl = timedelta(minutes=5)

    async def get_strategy(self, client_id: str) -> Dict:
        """Get active strategy for client with caching"""

        # Check cache
        if client_id in self.cache:
            cached_data, cached_time = self.cache[client_id]
            if datetime.utcnow() - cached_time < self.cache_ttl:
                return cached_data

        # Load from database
        result = self.supabase.table("strategies").select("*").eq(
            "client_id", client_id
        ).eq("active", True).execute()

        if not result.data:
            raise ValueError(f"No active strategy for client {client_id}")

        strategy = result.data[0]

        # Load related data
        seo_rules = self.supabase.table("seo_rules").select("*").eq(
            "client_id", client_id
        ).eq("active", True).execute()

        content_prefs = self.supabase.table("content_preferences").select("*").eq(
            "client_id", client_id
        ).execute()

        # Combine into full strategy
        full_strategy = {
            "config": strategy["config"],
            "seo_rules": {rule["rule_type"]: rule["rules"] for rule in seo_rules.data},
            "preferences": {pref["content_type"]: pref["preferences"] for pref in content_prefs.data}
        }

        # Cache result
        self.cache[client_id] = (full_strategy, datetime.utcnow())

        return full_strategy

    def invalidate_cache(self, client_id: str):
        """Invalidate cache for specific client"""
        if client_id in self.cache:
            del self.cache[client_id]

Real-time Strategy Updates¶

# realtime_updates.py
from typing import Callable
import asyncio

class StrategyUpdateListener:
    def __init__(self, supabase_client, strategy_loader):
        self.supabase = supabase_client
        self.strategy_loader = strategy_loader
        self.callbacks = []

    def on_strategy_change(self, callback: Callable):
        """Register callback for strategy changes"""
        self.callbacks.append(callback)

    async def start_listening(self):
        """Subscribe to strategy changes"""

        def handle_change(payload):
            client_id = payload['new']['client_id']

            # Invalidate cache
            self.strategy_loader.invalidate_cache(client_id)

            # Notify callbacks
            for callback in self.callbacks:
                asyncio.create_task(callback(client_id))

        # Subscribe to changes
        self.supabase.table("strategies").on(
            "UPDATE", 
            handle_change
        ).subscribe()

        self.supabase.table("seo_rules").on(
            "*", 
            handle_change
        ).subscribe()

V2 Enhancements¶

1. A/B Testing Strategies¶

// A/B testing configuration
interface ABTest {
  id: string
  client_id: string
  test_name: string
  variants: {
    control: StrategyConfig
    test: StrategyConfig
  }
  metrics: {
    engagement_rate?: number
    conversion_rate?: number
    quality_score?: number
  }
  status: 'active' | 'completed' | 'paused'
}

2. Machine Learning Optimization¶

# ml_optimizer.py
from sklearn.ensemble import RandomForestRegressor
import numpy as np

class StrategyOptimizer:
    def __init__(self):
        self.model = RandomForestRegressor()

    async def optimize_keywords(self, client_id: str, performance_data: List[Dict]):
        """Use ML to optimize keyword weights based on performance"""

        # Prepare training data
        X = []  # Features: keyword usage, position, etc.
        y = []  # Target: engagement metrics

        for item in performance_data:
            features = self.extract_features(item)
            X.append(features)
            y.append(item['engagement_score'])

        # Train model
        self.model.fit(X, y)

        # Generate optimized weights
        return self.generate_optimized_weights(client_id)

3. Strategy Templates Marketplace¶

-- Strategy templates that can be shared/sold
CREATE TABLE strategy_templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    description TEXT,
    category TEXT NOT NULL, -- 'education', 'marketing', 'ecommerce', etc.
    config JSONB NOT NULL,
    price DECIMAL(10, 2) DEFAULT 0, -- 0 for free templates
    downloads INTEGER DEFAULT 0,
    rating DECIMAL(3, 2),
    created_by UUID REFERENCES auth.users(id),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Success Metrics¶

MVP Goals¶

Strategy load time: <100ms (with caching)
UI responsiveness: <200ms for updates
99% uptime for strategy API
Support 50+ concurrent clients

V2 Goals¶

Real-time strategy propagation: <1s
A/B testing framework operational
ML-optimized strategies showing 20%+ improvement
Strategy marketplace with 100+ templates

Cost Considerations¶

MVP Costs¶

Supabase: Free tier sufficient
Next.js hosting (via Vercel): Free tier
Total: $0/month

V2 Costs¶

Supabase Pro: $25/month
ML compute: ~$10/month
Total: ~$35/month

Performance Optimization & Enhancement Suggestions¶

Task 23: ClientHierarchy API Architecture Enhancement¶

Current PRD Implementation¶

Direct Supabase queries in React component as specified:

const { data } = await supabase
  .from('clients')
  .select('*')
  .order('type', { ascending: true })

Enhancement Suggestions for Future Iterations¶

1. Dedicated Client Hierarchy API Endpoints - /api/clients/hierarchy - Returns pre-built hierarchical tree structure - /api/clients/index - Flat client list with pagination and filtering - Benefits: Better separation of concerns, consistent API architecture, enhanced security, caching opportunities

2. Performance Optimizations - Tree building on server to reduce client-side processing - Response caching for faster subsequent loads - Incremental loading for large hierarchies - Virtual scrolling for performance at scale

3. Enhanced Error Handling - Specific error types for different failure scenarios - Automatic retry mechanisms for transient failures - Graceful degradation when API unavailable

4. Security Enhancements - Server-side authentication and authorization - Input sanitization and validation - Rate limiting to prevent abuse

These enhancements maintain PRD compliance while providing a foundation for scalable, production-ready improvements in future development cycles.

Real-time Strategy Update Listener (Tasks 20 & 28)¶

Future Enhancement for Automatic Cache Invalidation¶

Overview: Implement a Python-based listener that subscribes to Supabase real-time database changes to automatically invalidate the StrategyLoader cache when strategies are updated through the UI.

Components to Implement:

StrategyUpdateListener Class
Initialize Supabase real-time client
Subscribe to changes on strategies, seo_rules_v2, and content_preferences_v2 tables
Handle incoming event payloads
Event Processing
Parse real-time payloads to extract client_id
Trigger cache invalidation in StrategyLoader
Support generic callback registration for extensibility
Background Execution
Run as non-blocking background process
Integrate with CPM/IM service startup
Graceful shutdown handling

Benefits: - Ensures backend services always use latest strategy data - Eliminates need for manual cache invalidation - Reduces cache TTL requirements for better performance - Enables true real-time strategy propagation

Implementation Priority: Low - The current 5-minute cache TTL provides reasonable freshness for most use cases. This enhancement can be added when real-time requirements become critical.