prompt-analysis

name	prompt-analysis
description	Analyze AI prompting patterns and acceptance rates
argument-hint	[question about prompts]
allowed-tools	["Bash(git-ai:*)","Read","Glob","Grep","Task"]

Prompt Analysis Skill

Analyze AI prompting patterns using the local prompts.db SQLite database.

What is Git AI?

Git AI is a tool that tracks AI-generated code and prompts in git. It stores:

• Every AI conversation (prompts and responses)
• Which lines of code came from AI vs human edits
• Acceptance rates (how much AI code was kept vs modified)
• Associated commits and authors

This skill queries that data to help users understand their AI coding patterns.

Initialization

First, determine scope from the user's question:

User mentions	Flags to use
"my prompts" or nothing specified	(default - current user, current repo)
"team", "everyone", "all authors"	--all-authors
specific person's name	--author "<name>"
specific time range	--since <days> (default: 30)

Discovery is notes-only — git-ai prompts always operates on the current repository (the working directory must be inside a git repo). To analyze multiple repos, run the command separately in each.

Run initialization:

git-ai prompts [flags]

This creates/updates prompts.db in the current directory.

Schema Reference

The prompts table contains:

• seq_id - Auto-increment ID for iteration
• id - Unique prompt identifier
• tool - Tool used (e.g., "claude-code", "cursor")
• model - Model name (e.g., "claude-sonnet-4-20250514")
• human_author - Git user who created the prompt
• commit_sha - Associated commit (if any)
• total_additions, total_deletions - Lines of code changed
• accepted_lines, overridden_lines - Lines kept vs modified by human
• accepted_rate - Ratio: accepted / (accepted + overridden)
• messages - JSON array of the conversation
• start_time, last_time - Unix timestamps

Analysis Approaches

For aggregate questions (metrics, comparisons)

Use direct SQL queries:

git-ai prompts exec "SELECT model, AVG(accepted_rate), COUNT(*) FROM prompts GROUP BY model"

For per-prompt analysis (categorization, content analysis)

When questions require examining each prompt's content (messages JSON), use subagents:

1. Add analysis columns to the schema:

git-ai prompts exec "ALTER TABLE prompts ADD COLUMN work_type TEXT"
git-ai prompts exec "ALTER TABLE prompts ADD COLUMN analysis_notes TEXT"

2. Reset the iteration pointer:

git-ai prompts reset

3. Iterate with subagents - Launch parallel subagents using Task tool with subagent_type: "general-purpose". Each subagent:

   • Runs git-ai prompts next to get one prompt as JSON
   • Analyzes the messages content
   • Updates the database: git-ai prompts exec "UPDATE prompts SET work_type='...' WHERE id='...'"

4. Final synthesis - Query the enriched data:

git-ai prompts exec "SELECT work_type, COUNT(*), AVG(accepted_rate) FROM prompts GROUP BY work_type"

Subagent Pattern for Iteration

When processing prompts individually, spawn multiple subagents in parallel. Each subagent prompt should include:

Run `git-ai prompts next` to get the next prompt.

Analyze the messages JSON to determine: [specific analysis task]

Then update the database:
git-ai prompts exec "UPDATE prompts SET [column]='[value]' WHERE id='[prompt_id]'"

Return your analysis result.

Spawn 3-5 subagents at a time, check results, spawn more until git-ai prompts next returns "No more prompts."

IMPORTANT: The git-ai prompts next command returns ALL the data needed for analysis as JSON, including:

• The full messages array with the complete conversation (human prompts and AI responses)
• Metadata like model, tool, accepted_rate, accepted_lines, etc.

Subagents should NOT run additional commands like git show or git log - everything needed is in the JSON output from git-ai prompts next. Instruct subagents explicitly:

IMPORTANT: All data you need is in the JSON output from `git-ai prompts next`.
Do NOT run git commands. Analyze the `messages` field in the JSON directly.

Iterator Examples

Example 1: Categorize prompts by work type

User asks: "Categorize my prompts by work type (bug fix, feature, refactor, docs)"

Setup:

git-ai prompts exec "ALTER TABLE prompts ADD COLUMN work_type TEXT"
git-ai prompts reset

Subagent prompt:

Run `git-ai prompts next` to get the next prompt as JSON.

IMPORTANT: All data you need is in this JSON output. Do NOT run git commands.
Analyze the `messages` field directly.

Read the messages JSON and categorize this prompt into ONE of:
- "bug_fix" - fixing broken behavior, errors, or regressions
- "feature" - adding new functionality
- "refactor" - restructuring code without changing behavior
- "docs" - documentation, comments, READMEs
- "test" - adding or modifying tests
- "config" - configuration, build, CI/CD changes
- "other" - doesn't fit above categories

Update the database:
git-ai prompts exec "UPDATE prompts SET work_type='<category>' WHERE id='<prompt_id>'"

Return: the prompt id, your categorization, and a one-sentence reason.

Synthesis query:

SELECT work_type, COUNT(*) as count,
       ROUND(AVG(accepted_rate), 3) as avg_acceptance,
       SUM(accepted_lines) as total_lines
FROM prompts
WHERE work_type IS NOT NULL
GROUP BY work_type
ORDER BY count DESC

Example 2: Analyze why prompts had low acceptance

User asks: "Why do some of my prompts have low acceptance rates?"

Setup:

git-ai prompts exec "ALTER TABLE prompts ADD COLUMN low_acceptance_reason TEXT"
git-ai prompts exec "UPDATE pointers SET current_seq_id = (SELECT MIN(seq_id) - 1 FROM prompts WHERE accepted_rate < 0.5 AND accepted_rate IS NOT NULL)"

Subagent prompt:

Run `git-ai prompts next` to get the next prompt as JSON.

IMPORTANT: All data you need is in this JSON output. Do NOT run git commands.
Analyze the `messages` field directly.

This prompt had a low acceptance rate (human modified most of the AI's code).
Analyze the messages JSON and identify the likely reason:
- "vague_request" - the prompt was unclear or underspecified
- "wrong_approach" - AI took a fundamentally wrong approach
- "style_mismatch" - code worked but didn't match project conventions
- "partial_solution" - AI only solved part of the problem
- "overengineered" - AI added unnecessary complexity
- "context_missing" - AI lacked necessary context about the codebase
- "other" - explain briefly

Update the database:
git-ai prompts exec "UPDATE prompts SET low_acceptance_reason='<reason>' WHERE id='<prompt_id>'"

Return: prompt id, the reason, and specific evidence from the conversation.

Example 3: Identify prompts that could be turned into reusable patterns

User asks: "Which of my prompts solved problems I might face again?"

Setup:

git-ai prompts exec "ALTER TABLE prompts ADD COLUMN reusable_pattern TEXT"
git-ai prompts exec "ALTER TABLE prompts ADD COLUMN pattern_description TEXT"
git-ai prompts reset

Subagent prompt:

Run `git-ai prompts next` to get the next prompt as JSON.

IMPORTANT: All data you need is in this JSON output. Do NOT run git commands.
Analyze the `messages` field directly.

Analyze whether this prompt represents a reusable pattern worth saving:
- Look for: common coding tasks, useful abstractions, clever solutions
- Skip: one-off fixes, highly context-specific changes, trivial edits

If reusable, set reusable_pattern to a short name (e.g., "api_error_handling", "form_validation", "test_mocking")
and pattern_description to a one-sentence description of what it does.

If not reusable, set both to NULL.

git-ai prompts exec "UPDATE prompts SET reusable_pattern='<name>', pattern_description='<desc>' WHERE id='<prompt_id>'"

Return: prompt id and whether you marked it as reusable (with the pattern name if yes).

Example 4: Score prompt quality/clarity

User asks: "How clear are my prompts? Which ones could I have written better?"

Setup:

git-ai prompts exec "ALTER TABLE prompts ADD COLUMN clarity_score INTEGER"
git-ai prompts exec "ALTER TABLE prompts ADD COLUMN clarity_feedback TEXT"
git-ai prompts reset

Subagent prompt:

Run `git-ai prompts next` to get the next prompt as JSON.

IMPORTANT: All data you need is in this JSON output. Do NOT run git commands.
Analyze the `messages` field directly.

Score the HUMAN's prompt clarity from 1-5:
5 = Crystal clear: specific goal, context provided, constraints stated
4 = Good: clear intent, minor ambiguities
3 = Adequate: understandable but missing helpful context
2 = Vague: required AI to make significant assumptions
1 = Unclear: AI had to guess what was wanted

Also provide brief feedback on how the prompt could be improved.

git-ai prompts exec "UPDATE prompts SET clarity_score=<1-5>, clarity_feedback='<feedback>' WHERE id='<prompt_id>'"

Return: prompt id, score, and your feedback.

Synthesis query:

SELECT clarity_score, COUNT(*) as count,
       ROUND(AVG(accepted_rate), 3) as avg_acceptance
FROM prompts
WHERE clarity_score IS NOT NULL
GROUP BY clarity_score
ORDER BY clarity_score DESC

Example 5: Correlate prompting techniques with acceptance rate

User asks: "Correlate my prompting techniques with acceptance rate"

Setup:

git-ai prompts exec "ALTER TABLE prompts ADD COLUMN technique TEXT"
git-ai prompts exec "ALTER TABLE prompts ADD COLUMN technique_notes TEXT"
git-ai prompts reset

Subagent prompt:

Run `git-ai prompts next` to get the next prompt as JSON.

IMPORTANT: All data you need is in this JSON output. Do NOT run git commands.
Analyze the `messages` field directly.

Analyze the HUMAN's prompting technique in the messages. Identify which techniques were used:
- "example_driven" - provided examples of desired output or behavior
- "step_by_step" - broke down the request into steps or phases
- "context_heavy" - provided extensive background/context about the codebase
- "minimal" - terse, brief request with little context
- "iterative" - built up solution through back-and-forth refinement
- "constraint_focused" - emphasized what NOT to do or specific requirements
- "reference_based" - pointed to existing code/files to follow as patterns
- "multiple" - combined several techniques (list them in notes)

Also note any specific effective or ineffective patterns in technique_notes.

git-ai prompts exec "UPDATE prompts SET technique='<technique>', technique_notes='<notes>' WHERE id='<prompt_id>'"

Return: prompt id, technique identified, the acceptance_rate, and your observations.

Synthesis query:

SELECT technique,
       COUNT(*) as count,
       ROUND(AVG(accepted_rate), 3) as avg_acceptance,
       ROUND(MIN(accepted_rate), 3) as min_acceptance,
       ROUND(MAX(accepted_rate), 3) as max_acceptance
FROM prompts
WHERE technique IS NOT NULL AND accepted_rate IS NOT NULL
GROUP BY technique
ORDER BY avg_acceptance DESC

Example 6: Qualitative analysis of failed prompts

User asks: "Do a qualitative analysis of failed prompts (0 accepted)"

Definition of "failed prompts":

• accepted_lines IS NULL - conversation happened but no code was committed
• accepted_lines = 0 - code was generated but human rejected/rewrote all of it

Setup:

git-ai prompts exec "ALTER TABLE prompts ADD COLUMN failure_analysis TEXT"
git-ai prompts exec "ALTER TABLE prompts ADD COLUMN failure_category TEXT"
git-ai prompts exec "ALTER TABLE prompts ADD COLUMN improvement_suggestion TEXT"
-- Set pointer to iterate only over failed prompts
git-ai prompts exec "DELETE FROM pointers WHERE name='default'"
git-ai prompts exec "INSERT INTO pointers (name, current_seq_id) VALUES ('default', (SELECT MIN(seq_id) - 1 FROM prompts WHERE accepted_lines IS NULL OR accepted_lines = 0))"

Subagent prompt:

Run `git-ai prompts next` to get the next prompt as JSON.

IMPORTANT: All data you need is in this JSON output. Do NOT run git commands.
Analyze the `messages` field directly.

This prompt resulted in 0% acceptance - the human rejected or completely rewrote all AI-generated code.

Perform a qualitative analysis by reading the full conversation in messages JSON:

1. **What was requested?** Summarize the human's goal.
2. **What did the AI produce?** Summarize what code/solution was generated.
3. **Why did it fail?** Categorize into:
   - "misunderstood_intent" - AI solved the wrong problem
   - "poor_code_quality" - bugs, errors, or broken code
   - "wrong_technology" - used wrong framework/library/approach
   - "incomplete" - only partially addressed the request
   - "style_violation" - worked but violated project conventions
   - "overcomplicated" - solution far more complex than needed
   - "security_issue" - introduced vulnerabilities
   - "abandoned" - user changed direction mid-conversation

4. **How could the prompt be improved?** Specific, actionable suggestion.

git-ai prompts exec "UPDATE prompts SET failure_category='<category>', failure_analysis='<analysis>', improvement_suggestion='<suggestion>' WHERE id='<prompt_id>'"

Return a detailed analysis including: prompt id, what was requested, what went wrong, and how to prompt better next time.

Synthesis:

SELECT failure_category,
       COUNT(*) as count,
       GROUP_CONCAT(id, ', ') as prompt_ids
FROM prompts
WHERE failure_category IS NOT NULL
GROUP BY failure_category
ORDER BY count DESC

Then review individual failure_analysis and improvement_suggestion values to compile lessons learned.

Example 7: Grade prompts according to best practices

User asks: "Grade my prompts according to best practices"

Setup:

git-ai prompts exec "ALTER TABLE prompts ADD COLUMN grade TEXT"
git-ai prompts exec "ALTER TABLE prompts ADD COLUMN grade_breakdown TEXT"
git-ai prompts exec "ALTER TABLE prompts ADD COLUMN grade_feedback TEXT"
git-ai prompts reset

Subagent prompt:

Run `git-ai prompts next` to get the next prompt as JSON.

IMPORTANT: All data you need is in this JSON output. Do NOT run git commands.
Analyze the `messages` field directly.

Grade the HUMAN's prompt against these best practices for AI-assisted coding:

**Grading Criteria (each worth 0-2 points):**

1. **Specificity (0-2)**: Does the prompt clearly state what needs to be done?
   - 0: Vague or ambiguous ("fix this", "make it better")
   - 1: General direction but missing details ("add validation")
   - 2: Specific outcome described ("add email validation that checks format and shows inline error")

2. **Context (0-2)**: Does the prompt provide necessary background?
   - 0: No context, assumes AI knows everything
   - 1: Some context but missing key details
   - 2: Relevant files, constraints, or project patterns mentioned

3. **Scope (0-2)**: Is the request appropriately sized?
   - 0: Too broad ("rewrite the app") or impossibly vague
   - 1: Large but manageable, could be broken down
   - 2: Focused, single-responsibility task

4. **Constraints (0-2)**: Are requirements and boundaries specified?
   - 0: No constraints given when they'd be helpful
   - 1: Some constraints but missing important ones
   - 2: Clear about what to do AND what not to do

5. **Testability (0-2)**: Can success be verified?
   - 0: No way to know if the result is correct
   - 1: Implicit success criteria
   - 2: Explicit expected behavior or acceptance criteria

**Calculate total (0-10) and assign grade:**