┌─────────────────────────────────────────────────────────────────────────┐
│                          Pure-Prompt Approach                           │
│                                                                         │
│  User Request ──► Agent loads 4,000-line diff into context (15k tokens) │
│               ──► LLM attempts manual regex / line-by-line linting      │
│               ──► High risk of missed errors, hallucinated line numbers  │
│               ──► High latency, massive token consumption ($$$)         │
└─────────────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────────────┐
│                    Executable Multi-File Skill Approach                 │
│                                                                         │
│  User Request ──► Agent executes `python scripts/extract_git_diff.py`   │
│               ──► Deterministic script extracts, filters, scans secrets │
│               ──► Returns compact 300-token structured JSON summary     │
│               ──► Agent reasons, writes high-level review & advice      │
└─────────────────────────────────────────────────────────────────────────┘

my-executable-skill/
├── SKILL.md               # Primary entrypoint: Frontmatter metadata + procedural steps
├── scripts/               # Deterministic execution helpers (Python, Bash, Node.js)
│   ├── extract_data.py    # Standalone CLI tools with standard argument parsing
│   └── run_checks.sh      # Shell scripts for orchestration or environment setup
├── references/            # Static schemas, API specs, and domain reference guides
│   ├── schema.json        # JSON Schema for validating outputs or configurations
│   └── style_guide.md     # Extended reference docs loaded on-demand
└── assets/                # Boilerplate files, scaffolding templates, or starter kits
    └── template.config.ts # Files copied directly into the user workspace

flowchart TD
    A[Session Start] --> B[Tier 1: Catalog Discovery]
    B -->|Agent scans all skill names & descriptions| C[System Prompt Index ~50 tokens/skill]
    C --> D{User Prompt Matches Intent?}
    D -- No --> E[Skill Remains Inactive / Zero Context Cost]
    D -- Yes --> F[Tier 2: Skill Activation]
    F -->|Agent reads SKILL.md body| G[Inject Procedural Steps & Guidelines]
    G --> H[Tier 3: Execution & Resource Resolution]
    H -->|Agent runs terminal commands| I[Execute scripts/*.py]
    H -->|Agent inspects references| J[Read references/*.json]
    H -->|Agent copies assets| K[Deploy assets/* to Project]

pr-reviewer/
├── SKILL.md
└── scripts/
    └── extract_git_diff.py

#!/usr/bin/env python3
"""
extract_git_diff.py — Deterministic Git Changeset & Security Scanner
Extracts modified files, filters out generated noise, and scans for high-entropy secrets.
"""

import subprocess
import sys
import os
import re
import json
import argparse
from typing import Dict, List, Any

# Ignored binary/lockfile patterns to prevent context bloat
IGNORED_PATTERNS = [
    r"package-lock\.json$",
    r"pnpm-lock\.yaml$",
    r"yarn\.lock$",
    r"poetry\.lock$",
    r"Cargo\.lock$",
    r"dist\/.*",
    r"build\/.*",
    r"\.next\/.*",
    r".*\.min\.(js|css)$",
    r".*\.svg$",
    r".*\.png$",
    r".*\.webp$",
    r".*\.map$"
]

# Regex rules for high-risk hardcoded secrets
SECRET_PATTERNS = {
    "AWS Access Key": r"(?:A3T[A-Z0-9]|AKIA|AGPA|AIDA|AROA|AIPA|ANPA|ANVA|ASIA)[A-Z0-9]{16}",
    "Generic Private Key": r"-----BEGIN (?:RSA |EC |DSA |OPENSSH )?PRIVATE KEY-----",
    "GitHub Token": r"(?:ghp|gho|ghu|ghs|ghr)_[A-Za-z0-9_]{36,255}",
    "Slack Token": r"xox[baprs]-[0-9]{10,13}-[0-9]{10,13}[a-zA-Z0-9-]*",
    "Generic API Key / Secret": r"(?i)(?:api_key|apikey|secret_key|app_secret|auth_token)\s*[:=]\s*['\"][A-Za-z0-9\-_=]{16,}['\"]"
}

def run_command(cmd: List[str]) -> str:
    try:
        res = subprocess.run(cmd, capture_output=True, text=True, check=True)
        return res.stdout.strip()
    except subprocess.CalledProcessError as e:
        sys.stderr.write(f"Command error: {' '.join(cmd)}\n{e.stderr}\n")
        return ""

def is_ignored(filename: str) -> bool:
    for pattern in IGNORED_PATTERNS:
        if re.search(pattern, filename):
            return True
    return False

def scan_for_secrets(diff_text: str) -> List[Dict[str, str]]:
    findings = []
    for line_no, line in enumerate(diff_text.splitlines(), start=1):
        if line.startswith("+") and not line.startswith("+++"):
            for secret_type, regex in SECRET_PATTERNS.items():
                if re.search(regex, line):
                    findings.append({
                        "rule": secret_type,
                        "line_preview": line[:80].strip(),
                        "note": "Potential hardcoded credential in added line"
                    })
    return findings

def get_git_diff_summary(base_branch: str = "main") -> Dict[str, Any]:
    # Check git repo status
    if not os.path.exists(".git"):
        return {"error": "Not a valid git repository root."}

    # Identify changed files
    diff_stat = run_command(["git", "diff", f"{base_branch}...HEAD", "--name-status"])
    if not diff_stat:
        # Fallback to unstaged/staged working tree diff against HEAD
        diff_stat = run_command(["git", "diff", "HEAD", "--name-status"])
        base_ref = "HEAD"
    else:
        base_ref = base_branch

    files_data = []
    all_findings = []
    total_insertions = 0
    total_deletions = 0

    for line in diff_stat.splitlines():
        parts = line.split(maxsplit=1)
        if len(parts) != 2:
            continue
        status, file_path = parts[0], parts[1]

        if is_ignored(file_path):
            files_data.append({
                "path": file_path,
                "status": status,
                "ignored": True,
                "reason": "Binary or generated asset"
            })
            continue

        # Get diff for specific file
        file_diff = run_command(["git", "diff", f"{base_ref}...HEAD", "--", file_path]) if base_ref != "HEAD" else run_command(["git", "diff", "HEAD", "--", file_path])
        
        # Calculate additions/deletions
        additions = sum(1 for l in file_diff.splitlines() if l.startswith("+") and not l.startswith("+++"))
        deletions = sum(1 for l in file_diff.splitlines() if l.startswith("-") and not l.startswith("---"))
        total_insertions += additions
        total_deletions += deletions

        # Scan for security leaks
        secrets = scan_for_secrets(file_diff)
        if secrets:
            all_findings.extend([{"file": file_path, **s} for s in secrets])

        files_data.append({
            "path": file_path,
            "status": status,
            "additions": additions,
            "deletions": deletions,
            "security_alerts": len(secrets),
            "snippet": "\n".join(file_diff.splitlines()[:60]) # First 60 lines max for context
        })

    # Get recent commit messages
    commits_log = run_command(["git", "log", f"{base_ref}..HEAD", "--oneline", "-n", "10"]) if base_ref != "HEAD" else run_command(["git", "log", "-n", "5", "--oneline"])

    return {
        "base_ref": base_ref,
        "total_files_changed": len(files_data),
        "total_insertions": total_insertions,
        "total_deletions": total_deletions,
        "security_findings": all_findings,
        "commits": commits_log.splitlines() if commits_log else [],
        "files": files_data
    }

def main():
    parser = argparse.ArgumentParser(description="Extract and sanitize Git diff summary for AI Agent review.")
    parser.add_argument("--base", default="main", help="Base git branch to compare against (default: main)")
    args = parser.parse_args()

    summary = get_git_diff_summary(base_branch=args.base)
    print(json.dumps(summary, indent=2))

if __name__ == "__main__":
    main()

---
name: git-pr-reviewer
description: Automated Git PR & Changeset Reviewer. Audits staged or branch changes against main, checks for security leaks/secrets, validates commit conventions, and generates a structured code review summary.
---

# Git PR & Changeset Reviewer Skill

Use this skill when the user asks for a PR review, code change audit, pre-commit check, or summary of recent git changes.

## Prerequisites
- Python 3.8+ available in `$PATH`
- Active Git repository

## Procedure

1. **Execute Diff Extraction Script**:
   Run the bundled deterministic extraction script from the skill directory:
   ```bash
   python3 <SKILL_DIR>/scripts/extract_git_diff.py --base main
   ```

2. **Evaluate the JSON Output**:
   - Check `security_findings`: If any secrets (API keys, private keys) are discovered, **HALT** regular review and emit an immediate critical security alert.
   - Review `commits`: Verify adherence to Conventional Commits format (`feat:`, `fix:`, `refactor:`, `chore:`).
   - Inspect `files`: Examine modified code snippets for:
     - Architectural consistency and maintainability
     - Potential regression risks or unhandled boundary conditions
     - Missing test coverage for newly added logic

3. **Format the Pull Request Review**:
   Construct the response with the following markdown structure:
   - **Executive Summary**: Total files changed, net lines added/deleted, and primary purpose.
   - **Security & Integrity Status**: Explicit confirmation that no secrets or lockfile corruptions were found.
   - **File-by-File Findings**: Grouped by architectural layer with line-specific suggestions.
   - **Recommended Next Steps**: Verification commands (e.g., test suites, typecheck).

migration-verifier/
├── SKILL.md
└── scripts/
    └── validate_schema.py

#!/usr/bin/env python3
"""
validate_schema.py — Database Migration Safety & Anti-Pattern Linter
Analyzes raw SQL migrations and schema files for high-risk DDL operations.
"""

import sys
import os
import re
import json
import argparse
from typing import Dict, List, Any

# High-risk SQL patterns that cause table locks or downtime
RISKY_PATTERNS = [
    {
        "id": "DROP_COLUMN",
        "regex": r"(?i)ALTER\s+TABLE\s+[\w\.\"]+\s+DROP\s+COLUMN\s+",
        "severity": "CRITICAL",
        "message": "Dropping columns directly causes immediate data loss and breaks running application instances."
    },
    {
        "id": "DROP_TABLE",
        "regex": r"(?i)DROP\s+TABLE\s+(?:IF\s+EXISTS\s+)?[\w\.\"]+",
        "severity": "CRITICAL",
        "message": "Dropping tables destroys historical records. Ensure an archival backup exists."
    },
    {
        "id": "ADD_NOT_NULL_NO_DEFAULT",
        "regex": r"(?i)ALTER\s+TABLE\s+[\w\.\"]+\s+ADD\s+(?:COLUMN\s+)?[\w\.\"]+\s+[^;\n]+NOT\s+NULL(?!\s+DEFAULT)",
        "severity": "HIGH",
        "message": "Adding a NOT NULL column without a DEFAULT will fail on tables with existing rows."
    },
    {
        "id": "UNINDEXED_FOREIGN_KEY",
        "regex": r"(?i)(?:FOREIGN\s+KEY|REFERENCES)\s+[\w\.\"]+\s*\([^\)]+\)",
        "severity": "MEDIUM",
        "message": "Foreign key created. Ensure a corresponding index exists on the referencing column to prevent table-scan locks during CASCADE/deletions."
    },
    {
        "id": "TABLE_RENAME",
        "regex": r"(?i)ALTER\s+TABLE\s+[\w\.\"]+\s+RENAME\s+TO\s+",
        "severity": "HIGH",
        "message": "Renaming a live table immediately breaks running queries. Use expand/contract pattern."
    }
]

def analyze_sql_content(file_path: str, content: str) -> List[Dict[str, Any]]:
    violations = []
    lines = content.splitlines()
    
    for item in RISKY_PATTERNS:
        for idx, line in enumerate(lines, start=1):
            if re.search(item["regex"], line):
                violations.append({
                    "file": file_path,
                    "line": idx,
                    "code_snippet": line.strip()[:100],
                    "rule_id": item["id"],
                    "severity": item["severity"],
                    "description": item["message"]
                })
    return violations

def find_migration_files(target_dir: str) -> List[str]:
    sql_files = []
    if not os.path.exists(target_dir):
        return []
    for root, _, files in os.walk(target_dir):
        for f in files:
            if f.endswith(".sql") or f.endswith(".prisma") or "migration" in f.lower():
                sql_files.append(os.path.join(root, f))
    return sorted(sql_files)

def main():
    parser = argparse.ArgumentParser(description="Audit database migrations for safety and locking anti-patterns.")
    parser.add_argument("--path", default="./migrations", help="Path to migration directory or SQL file")
    parser.add_argument("--strict", action="store_true", help="Fail if any MEDIUM or HIGH issues found")
    args = parser.parse_args()

    target_path = args.path
    files_to_check = []

    if os.path.isfile(target_path):
        files_to_check = [target_path]
    elif os.path.isdir(target_path):
        files_to_check = find_migration_files(target_path)
    else:
        # Scan common default paths
        for default_loc in ["./migrations", "./prisma/migrations", "./drizzle", "./db/migrations"]:
            if os.path.exists(default_loc):
                files_to_check.extend(find_migration_files(default_loc))

    if not files_to_check:
        print(json.dumps({"status": "no_files_found", "target": target_path, "violations": []}))
        return

    all_violations = []
    for fpath in files_to_check:
        try:
            with open(fpath, "r", encoding="utf-8") as f:
                content = f.read()
                violations = analyze_sql_content(fpath, content)
                all_violations.extend(violations)
        except Exception as e:
            sys.stderr.write(f"Error reading {fpath}: {str(e)}\n")

    summary = {
        "status": "completed",
        "scanned_files_count": len(files_to_check),
        "scanned_files": files_to_check,
        "critical_count": sum(1 for v in all_violations if v["severity"] == "CRITICAL"),
        "high_count": sum(1 for v in all_violations if v["severity"] == "HIGH"),
        "medium_count": sum(1 for v in all_violations if v["severity"] == "MEDIUM"),
        "violations": all_violations
    }

    print(json.dumps(summary, indent=2))

if __name__ == "__main__":
    main()

---
name: database-migration-verifier
description: Database Migration & Schema Verifier. Audits SQL, Prisma, and Drizzle migrations for locking anti-patterns, missing indexes on foreign keys, un-reversible drops, and non-nullable additions.
---

# Database Migration & Schema Verifier Skill

Activate this skill when creating, updating, or reviewing database migrations, DDL scripts, or ORM schema updates.

## Verification Workflow

1. **Run Static DDL Safety Audit**:
   Execute the validation script against the project's migration directory:
   ```bash
   python3 <SKILL_DIR>/scripts/validate_schema.py --path ./migrations
   ```

2. **Analyze Migration Reversibility**:
   - Check if an accompanying rollback / `down` migration exists.
   - For irreversible operations (e.g., `DROP COLUMN`), ensure a multi-phase deprecation strategy (Expand/Contract pattern) is documented.

3. **Check Concurrency & Zero-Downtime Requirements**:
   - In PostgreSQL, ensure indexes on large tables use `CREATE INDEX CONCURRENTLY`.
   - Ensure foreign key constraints are created with `NOT VALID` followed by `VALIDATE CONSTRAINT` in high-throughput production databases.

4. **Report Findings**:
   Provide the user with:
   - Risk matrix summary (Critical / High / Medium).
   - Safe refactored SQL replacement snippets for any flagged anti-patterns.
   - Exact dry-run command recommendation.

seo-frontmatter-linter/
├── SKILL.md
├── references/
│   └── frontmatter-schema.json
└── scripts/
    └── audit_frontmatter.py

{
  "$schema": "http://json-schema.org/draft-07/schema#",
  "title": "FrontmatterMetadata",
  "type": "object",
  "required": ["title", "description", "date", "author", "tags"],
  "properties": {
    "title": {
      "type": "string",
      "minLength": 20,
      "maxLength": 70,
      "description": "SEO page title (optimal 50-60 characters)"
    },
    "description": {
      "type": "string",
      "minLength": 50,
      "maxLength": 160,
      "description": "Search engine meta description (optimal 110-155 characters)"
    },
    "date": {
      "type": "string",
      "pattern": "^\\d{4}-\\d{2}-\\d{2}$"
    },
    "author": {
      "type": "string"
    },
    "image": {
      "type": "string",
      "pattern": "^/.*\\.(webp|png|jpg|jpeg)$"
    },
    "tags": {
      "type": "array",
      "items": { "type": "string" },
      "minItems": 1,
      "maxItems": 10
    }
  }
}

#!/usr/bin/env python3
"""
audit_frontmatter.py — Markdown & MDX Frontmatter SEO Validator
Validates YAML metadata against length limits, date formats, and OpenGraph requirements.
"""

import sys
import os
import re
import json
import argparse
from typing import Dict, List, Any

# Simple regex YAML frontmatter extractor to avoid mandatory external PyYAML dependency
FRONTMATTER_REGEX = re.compile(r"^---\s*\n(.*?)\n---\s*\n", re.DOTALL)

def parse_simple_yaml(yaml_text: str) -> Dict[str, Any]:
    data = {}
    for line in yaml_text.splitlines():
        line = line.strip()
        if not line or line.startswith("#"):
            continue
        if ":" in line:
            key, val = line.split(":", 1)
            key = key.strip()
            val = val.strip()
            # Clean string quotes
            if (val.startswith('"') and val.endswith('"')) or (val.startswith("'") and val.endswith("'")):
                val = val[1:-1]
            # Parse simple JSON-style lists: ["a", "b"]
            if val.startswith("[") and val.endswith("]"):
                try:
                    val = json.loads(val)
                except Exception:
                    val = [item.strip().strip("'\"") for item in val[1:-1].split(",") if item.strip()]
            data[key] = val
    return data

def audit_file(file_path: str) -> List[Dict[str, Any]]:
    errors = []
    try:
        with open(file_path, "r", encoding="utf-8") as f:
            content = f.read()
    except Exception as e:
        return [{"file": file_path, "type": "READ_ERROR", "message": str(e)}]

    match = FRONTMATTER_REGEX.match(content)
    if not match:
        return [{"file": file_path, "type": "MISSING_FRONTMATTER", "message": "No valid --- frontmatter block found at beginning of file."}]

    fm_raw = match.group(1)
    meta = parse_simple_yaml(fm_raw)

    # 1. Title Checks
    title = meta.get("title", "")
    if not title:
        errors.append({"file": file_path, "field": "title", "issue": "Missing required field 'title'"})
    else:
        length = len(title)
        if length < 20 or length > 70:
            errors.append({
                "file": file_path,
                "field": "title",
                "issue": f"Title length ({length} chars) is outside optimal range [20 - 70].",
                "value": title
            })

    # 2. Description Checks
    desc = meta.get("description", "")
    if not desc:
        errors.append({"file": file_path, "field": "description", "issue": "Missing required field 'description'"})
    else:
        length = len(desc)
        if length < 50 or length > 160:
            errors.append({
                "file": file_path,
                "field": "description",
                "issue": f"Description length ({length} chars) is outside optimal SEO range [50 - 160].",
                "value": desc
            })

    # 3. Tags Check
    tags = meta.get("tags")
    if not tags or not isinstance(tags, list) or len(tags) == 0:
        errors.append({"file": file_path, "field": "tags", "issue": "Field 'tags' must be a non-empty array."})

    # 4. Image / OG Check
    image = meta.get("image", "")
    if image and not image.startswith("/"):
        errors.append({"file": file_path, "field": "image", "issue": f"Image path '{image}' should be an absolute path starting with '/'."})

    return errors

def main():
    parser = argparse.ArgumentParser(description="Audit MDX/MD files for Frontmatter SEO compliance.")
    parser.add_argument("--path", required=True, help="Path to markdown file or directory to scan.")
    args = parser.parse_args()

    files_to_check = []
    if os.path.isfile(args.path):
        files_to_check = [args.path]
    elif os.path.isdir(args.path):
        for root, _, files in os.walk(args.path):
            for f in files:
                if f.endswith(".md") or f.endswith(".mdx"):
                    files_to_check.append(os.path.join(root, f))

    all_issues = []
    for fp in sorted(files_to_check):
        issues = audit_file(fp)
        all_issues.extend(issues)

    result = {
        "scanned_files": len(files_to_check),
        "total_issues": len(all_issues),
        "passed": len(all_issues) == 0,
        "issues": all_issues
    }
    print(json.dumps(result, indent=2))

if __name__ == "__main__":
    main()

---
name: seo-frontmatter-linter
description: SEO & Markdown Frontmatter Linter. Audits MDX and Markdown files for SEO title length, description limits, tag arrays, date formatting, and OpenGraph image specifications.
---

# SEO & Markdown Frontmatter Linter

Use this skill whenever creating new blog posts, documentation pages, prompt tutorials, or reviewing content for SEO compliance.

## Execution Steps

1. **Run the Linter Script**:
   ```bash
   python3 <SKILL_DIR>/scripts/audit_frontmatter.py --path contents/
   ```

2. **Inspect the Output**:
   - If `passed: true`, inform the user that all metadata conforms to SEO specifications.
   - If issues are detected, read the offending file and propose automatic frontmatter fixes that preserve meaning while respecting character limits.

3. **Title & Description Guidelines**:
   - **Title**: Keep within **50–60 characters** (maximum 70). Ensure it contains the primary target keyword.
   - **Description**: Keep within **120–155 characters** (maximum 160). Include a clear call-to-action or value proposition.

Standard Session Token Comparison:
Pure-Prompt:        ████████████████████████████████ 18,500 tokens
Executable Skill:   █ 650 tokens

# In SKILL.md, instruct the agent to interpolate the skill directory path:
python3 <SKILL_DIR>/scripts/validate_schema.py --path ./src

import os

SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
SKILL_ROOT = os.path.abspath(os.path.join(SCRIPT_DIR, ".."))
SCHEMA_PATH = os.path.join(SKILL_ROOT, "references", "schema.json")

{
  "status": "failed",
  "summary": "2 critical security issues detected",
  "issues": [
    {
      "file": "config/auth.ts",
      "line": 42,
      "severity": "CRITICAL",
      "rule": "HARDCODED_JWT_SECRET"
    }
  ]
}