---

name: civitai-analyst description: "Generate and execute SQL queries against the civitai_records PostgreSQL database to analyze video performance on Civitai. Use when users ask about: video engagement metrics (likes, hearts, comments), content performance analysis, tag/theme analysis, quality scores, weekly reports, comparing videos, content recommendations, trend analysis, or any Civitai data queries. Triggers: Civitai, video stats, engagement, likes, hearts, comments, weekly report, tag analysis, quality score, content strategy, top performers, SQL query, video comparison, WoW analysis, 数据分析, 视频表现, 周报, 内容分析."

Civitai Analyst

Analyze video performance data on Civitai through natural language queries. Generate SQL, execute against the database, and provide actionable insights.

Capabilities

1. SQL Generation - Convert natural language to optimized PostgreSQL queries
2. Query Execution - Run queries via query_civitai_db
3. Data Analysis - Interpret engagement metrics and find patterns
4. Content Insights - Analyze tags, themes, quality scores from video_analysis
5. Recommendations - Suggest content strategies based on performance data
6. Weekly Reports - Generate JSON/HTML performance summaries

Tool Usage

Execute SQL using the MCP tool:

query_civitai_db(sql="SELECT ...")

Error Handling: If query is rejected, response contains:

{
  "allowed": false,
  "reason": "...",
  "violation_type": "...",
  "suggestions": "..."
}

Fix the SQL based on the error and retry.

Workflow

1. Understand - Parse user's question, identify metrics/filters needed
2. Generate SQL - Use schema.md for tables, query-index.md for templates
3. Execute - Call the SQL tool, handle errors
4. Analyze - Interpret results, find patterns, compare data points
5. Present - Format with links, provide insights and recommendations

Key Parameters

civitai_account

• User-provided account identifier
• Default fallback: 'c29' if not specified

on_behalf_of

• User's first name, inferred from session context
• Used to filter assets/stats by uploader

Date Ranges

• Use calendar weeks (Monday 00:00 to Sunday 23:59 UTC)
• Format: PostgreSQL timestamptz '2025-01-06T00:00:00Z'

Date Calculations:

• "This week" = Current Monday to next Monday
• "Last week" = Previous Monday to current Monday
• "Past 2 weeks" = Monday 2 weeks ago to next Monday

Link Formatting

Assets (videos/images):

https://civitai.com/images/{assets.civitai_id}

Posts:

https://civitai.com/posts/{civitai_posts.civitai_id}

Always include clickable links in results for easy navigation.

Analysis Guidelines

Engagement Metrics

• Positive engagement: likes + hearts + laughs
• Total engagement: all reactions + comments
• Engagement rate: total_engagement / asset_count

Pattern Recognition

• Compare top performers vs average
• Identify common tags in high-engagement videos
• Correlate quality_score with engagement
• Analyze motion_intensity impact

Comparative Analysis

When comparing videos (e.g., "rank 2 vs rank 9"):

• Extract shared tags
• Compare quality scores
• Analyze description/prompt similarities
• Identify differentiating factors

Recommendation Framework

Based on analysis, provide actionable suggestions:

1. Content themes - Which topics/tags drive engagement
2. Quality factors - Optimal quality_score ranges
3. Timing patterns - Best posting times if data shows trends
4. Improvement areas - Underperforming high-quality content

Example insights:

• "Anime + high-motion videos get 2x engagement"
• "Videos with quality_score > 0.85 need better tags for visibility"
• "Comments spike on 'cinematic' tagged content"

Report Generation

For weekly reports, use templates from references/report-templates.md:

• JSON format - Structured data for programmatic use
• HTML format - Visual report with Tailwind CSS styling

Generate reports by:

1. Run weekly-feedback-stats.sql for summary
2. Run top-performing-assets.sql for highlights
3. Run tag-performance.sql for content insights
4. Combine into report template

Language

Respond in the same language as the user's query.

• English query → English response
• Chinese query → Chinese response (中文提问 → 中文回答)

Reference Files

File	When to Read
references/schema.md	Understanding table structures, columns, relationships
references/query-index.md	Finding the right query template for user's request
references/queries/*.sql	Loading specific query when needed
references/report-templates.md	Generating weekly reports