idea-generator

name	idea-generator
description	Analyze Plugin 1's relevance-scored feed data to generate post ideas. Prioritizes posts scored 3+ (meaningful/strong/bullseye) as inspiration. Generates ideas equal to the user's configured posts_per_week. Rotates ideas across content pillars (40/30/20/10 mix) and suggests from the 10 proven hook formulas in the best-practice guide.
version	3.0.0

Idea Generator Skill

Generates LinkedIn post ideas by mining the highest-relevance posts from Plugin 1's scored feed data. Ideas are distributed across content pillars and paired with proven hook formulas.

CRITICAL: Read the Best-Practice Guide First

Before generating ideas, read ../variation-writer/references/best-practice-guide.md. The guide's Section 1 (Positioning & Content Pillars) and Section 11 (Hook Formulas) directly shape idea generation.

Content Pillar Rotation

Every generation cycle should distribute ideas across the 4 content pillars from the guide:

• 40% — Core Expertise (your authority topics — what you know deeply)
• 30% — Personal Journey (founder stories, lessons learned, behind-the-scenes)
• 20% — Industry Commentary (trends, news, what's changing in the space)
• 10% — Community Engagement (questions, polls, celebrating others, giving shoutouts)

How this maps to posts_per_week:

• 1 post/week → rotate pillar each week (core → personal → industry → community)
• 2 posts/week → 1 core + 1 rotating
• 3 posts/week → 1 core + 1 personal + 1 rotating (industry or community)
• 5 posts/week → 2 core + 1 personal + 1 industry + 1 community

Triggers

• User runs /generate command
• User runs /setup-content-engine (first generation)

Step-by-Step Instructions

When this skill is invoked, Claude must follow these steps exactly:

Step 1: Connect to Database and Verify Data

Run this Python to check Plugin 1 data and scoring status:

import sqlite3, json, os, glob
from datetime import datetime, timedelta

# Find feeds.db
for db_path in glob.glob('/sessions/*/mnt/*/LinkedIn Feed Tracker/feeds.db'):
    break
else:
    raise FileNotFoundError("Cannot find feeds.db")

conn = sqlite3.connect(db_path)
conn.row_factory = sqlite3.Row

# Check scoring columns exist
columns = [r['name'] for r in conn.execute("PRAGMA table_info(posts)").fetchall()]
has_scoring = 'relevance_score' in columns

# Count scored posts
if has_scoring:
    stats = conn.execute("""
        SELECT
            COUNT(*) as total_posts,
            SUM(CASE WHEN relevance_score >= 3 THEN 1 ELSE 0 END) as relevant_posts,
            SUM(CASE WHEN relevance_score >= 5 THEN 1 ELSE 0 END) as bullseye_posts,
            SUM(CASE WHEN scored_at IS NOT NULL THEN 1 ELSE 0 END) as scored_posts
        FROM posts
        WHERE collected_at >= datetime('now', '-7 days')
    """).fetchone()
else:
    stats = conn.execute("SELECT COUNT(*) as total_posts FROM posts WHERE collected_at >= datetime('now', '-7 days')").fetchone()

# Get user config
user_row = conn.execute("SELECT value FROM config WHERE key = 'linkedin_user'").fetchone()
username = user_row[0] if user_row else "User"

# Get posts_per_week from voice config
try:
    ppw_row = conn.execute("SELECT posts_per_week FROM user_voice_config WHERE id = 1").fetchone()
    posts_per_week = ppw_row[0] if ppw_row else 3
except:
    posts_per_week = 3

print(json.dumps({
    'db_path': db_path,
    'has_scoring': has_scoring,
    'total_posts_7d': stats['total_posts'] if stats else 0,
    'relevant_posts': stats['relevant_posts'] if has_scoring and stats else 0,
    'bullseye_posts': stats['bullseye_posts'] if has_scoring and stats else 0,
    'username': username,
    'posts_per_week': posts_per_week
}, indent=2))
conn.close()

If no scoring columns: Tell user to run node src/score-posts.js from Plugin 1 first, or that ideas will be based on engagement only.

If no posts in last 7 days: Tell user to run Plugin 1's /collect-now or wait for nightly collection.

Step 2: Pull High-Relevance Inspiration Posts

Query the TOP posts from the last 7 days, prioritized by relevance score:

conn = sqlite3.connect(db_path)
conn.row_factory = sqlite3.Row

if has_scoring:
    # PRIMARY: Get posts scored 3+ by Plugin 1's scoring engine
    inspiration = conn.execute("""
        SELECT
            id, author_name, author_title, content, content_short,
            likes, comments, post_type, collected_at,
            relevance_score, focus_areas, action_flag
        FROM posts
        WHERE relevance_score >= 3
          AND collected_at >= datetime('now', '-7 days')
        ORDER BY relevance_score DESC, (likes + comments) DESC
        LIMIT 30
    """).fetchall()
else:
    # FALLBACK: No scoring — use engagement as proxy
    inspiration = conn.execute("""
        SELECT
            id, author_name, author_title, content, content_short,
            likes, comments, post_type, collected_at
        FROM posts
        WHERE collected_at >= datetime('now', '-7 days')
          AND (likes + comments) >= 10
        ORDER BY (likes + comments) DESC
        LIMIT 30
    """).fetchall()

inspiration = [dict(r) for r in inspiration]

# Parse focus_areas JSON for scored posts
if has_scoring:
    for post in inspiration:
        try:
            post['focus_areas_parsed'] = json.loads(post.get('focus_areas', '[]'))
        except:
            post['focus_areas_parsed'] = []

# Get user's own posts (last 30 days) to find gaps
own_posts = conn.execute("""
    SELECT content_short, posted_at, likes, comments
    FROM own_posts
    ORDER BY posted_at DESC
    LIMIT 20
""").fetchall()
own_posts = [dict(r) for r in own_posts]

# Get authority topics from voice config
try:
    voice = conn.execute("SELECT authority_topics FROM user_voice_config WHERE id = 1").fetchone()
    authority_topics = json.loads(voice['authority_topics']) if voice and voice['authority_topics'] else []
except:
    authority_topics = []

conn.close()

print(f"Found {len(inspiration)} relevant posts for inspiration")
print(f"User has {len(own_posts)} own posts for gap analysis")
print(f"Authority topics: {authority_topics}")

Step 3: Cluster Inspiration by Focus Area

Group the high-scoring posts by their matched focus areas:

# Group posts by their top focus area
clusters = {}
for post in inspiration:
    if has_scoring and post.get('focus_areas_parsed'):
        top_area = post['focus_areas_parsed'][0]['name']
    else:
        top_area = 'General'

    if top_area not in clusters:
        clusters[top_area] = []
    clusters[top_area].append(post)

# Sort clusters by total relevance score (highest first)
sorted_clusters = sorted(
    clusters.items(),
    key=lambda x: sum(p.get('relevance_score', 0) for p in x[1]),
    reverse=True
)

for area, posts in sorted_clusters:
    avg_score = sum(p.get('relevance_score', 0) for p in posts) / len(posts)
    total_engagement = sum(p['likes'] + p['comments'] for p in posts)
    print(f"  {area}: {len(posts)} posts, avg score {avg_score:.1f}, {total_engagement} total engagement")

Step 4: Generate Ideas (Claude Analysis)

Now Claude reads the clustered inspiration and generates ideas. For each idea, Claude must:

1. Assign a content pillar — Distribute across 40/30/20/10 mix (see Content Pillar Rotation above)
2. Pick a focus area cluster — Start with highest-scoring clusters
3. Identify the angle — What's the conversation happening in the network?
4. Find the gap — What hasn't the user posted about recently?
5. Suggest a hook formula — Pick from the 10 proven formulas (see below)
6. Cite sources — Which specific posts inspired this idea?

10 Hook Formulas (from best-practice guide, Section 11):

1. Curiosity Gap — hint at valuable info without revealing it
2. Quantified Promise — lead with specific number + measurable outcome
3. Contrarian / Hot Take — challenge a widely accepted belief
4. Story Opener — start with a specific, vulnerable moment
5. Problem Spotlight — name a specific pain the audience experiences
6. Bold Claim — make a confident, measurable promise
7. Data Drop — open with a surprising statistic
8. Micro-Confession — brief personal admission before pivoting to expertise
9. Framework Tease — introduce a proprietary system or mental model
10. Pattern Interrupt — break the expected pattern to force attention

Generate exactly posts_per_week ideas. If user configured 3 posts/week, generate 3 ideas.

For each idea, present:

IDEA [N]: "[Specific, compelling topic angle]"
Content Pillar: [Core Expertise | Personal Journey | Industry Commentary | Community Engagement]
Focus Area: [matched focus area from Plugin 1]
Suggested Hook: [one of the 10 formulas above]

Why this topic NOW:
[2-3 sentences explaining what's happening in the network — reference specific authors and engagement]

Inspired by: [N] posts scoring [X-Y] in your feed
- [Author Name]: "[first 60 chars of post]" (score: [N], [X] likes)
- [Author Name]: "[first 60 chars of post]" (score: [N], [X] likes)

Your gap: [What you haven't said about this topic recently]

Step 5: Store Ideas in Database

conn = sqlite3.connect(db_path)
cursor = conn.cursor()

# Create tables if needed
cursor.execute("""CREATE TABLE IF NOT EXISTS content_ideas (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    topic TEXT NOT NULL, hook_type TEXT, content_pillar TEXT,
    context_summary TEXT,
    source_post_ids TEXT, source_scores TEXT,
    status TEXT DEFAULT 'generated', user_rating INTEGER,
    generated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    used_at DATETIME, notes TEXT
)""")

cursor.execute("""CREATE TABLE IF NOT EXISTS generation_runs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ran_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    ideas_generated INTEGER, variations_generated INTEGER,
    top_source_score INTEGER, notes TEXT
)""")

# Insert each idea
for idea in ideas:
    cursor.execute("""
        INSERT INTO content_ideas (topic, hook_type, content_pillar, context_summary, source_post_ids, source_scores)
        VALUES (?, ?, ?, ?, ?, ?)
    """, (
        idea['topic'],
        idea['hook_type'],
        idea['content_pillar'],
        idea['context_summary'],
        json.dumps(idea['source_post_ids']),
        json.dumps(idea['source_scores'])
    ))

# Log run
top_score = max(p.get('relevance_score', 0) for p in inspiration) if inspiration else 0
cursor.execute("""
    INSERT INTO generation_runs (ideas_generated, variations_generated, top_source_score, notes)
    VALUES (?, 0, ?, ?)
""", (len(ideas), top_score, 'scoring-aware generation'))

conn.commit()
conn.close()

Step 6: Present to User

Show each idea and ask:

• "yes" → Queue for variation writing
• "skip" → Leave for later
• "reject" → Archive (won't resurface)
• "note" → Add context for variation writing

After user reviews, hand off selected ideas to the variation-writer skill.

Output Format

After generating, show a summary:

Generated [N] ideas from [X] high-relevance posts (scores 3-5).

Top focus areas driving ideas:
- [Area 1]: [N] inspiration posts, avg score [X]
- [Area 2]: [N] inspiration posts, avg score [X]

Ready to create variations? Say "yes" for all, or pick specific ideas by number.

Error States

Error	Message
No feeds.db	"Install Plugin 1 first and run at least one collection."
No scored posts	"Run node src/score-posts.js in Plugin 1 to score your posts, or I'll use engagement as a fallback."
No posts last 7 days	"No recent posts. Run Plugin 1's /collect-now or wait for tonight's nightly run."
No voice config	"Run /setup-content-engine first to set your voice and posting cadence."