netresearch

database-analyzer

2
0
# Install this skill:
npx skills add netresearch/composer-agent-skill-plugin

Or install specific skill: npx add-skill https://github.com/netresearch/composer-agent-skill-plugin/tree/main/examples/database-analyzer-skill

# Description

Analyze and optimize database schemas, identify performance issues, and suggest improvements. Use when working with database structure, indexes, or query performance.

# SKILL.md


name: database-analyzer
description: Analyze and optimize database schemas, identify performance issues, and suggest improvements. Use when working with database structure, indexes, or query performance.


Database Analyzer Skill

This skill helps you analyze database schemas, identify optimization opportunities, and understand table relationships.

Instructions

  1. Identify the target: Determine which table or schema to analyze
  2. Gather context: Understand the current usage patterns and performance concerns
  3. Analyze structure: Examine table definitions, indexes, and relationships
  4. Identify issues: Look for missing indexes, improper data types, or inefficient structures
  5. Suggest improvements: Provide specific, actionable recommendations

Examples

Example 1: Basic Table Analysis

User request: "Analyze the users table for optimization opportunities"

Approach:
- Check table structure and data types
- Verify indexes on frequently queried columns
- Look for redundant or missing indexes
- Suggest appropriate data types for columns

Analysis Steps:

-- 1. Get table structure
DESCRIBE users;

-- 2. Check existing indexes
SHOW INDEX FROM users;

-- 3. Analyze table statistics
ANALYZE TABLE users;

Common Issues to Check:
- Missing indexes on foreign keys
- Text columns that should be ENUM or SET
- Missing or excessive indexes
- Improper data types (e.g., VARCHAR when INT would suffice)

Example 2: Performance Investigation

User request: "Why are queries on the orders table slow?"

Approach:
- Identify frequently executed queries
- Check for missing indexes on WHERE/JOIN columns
- Analyze table size and growth patterns
- Suggest partitioning if appropriate

Investigation Steps:

-- 1. Check table size
SELECT
    table_name,
    round(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.TABLES
WHERE table_name = 'orders';

-- 2. Identify slow queries
SHOW PROCESSLIST;

-- 3. Check query execution plan
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

Optimization Recommendations:
- Add composite indexes for common query patterns
- Consider partitioning by date for large historical tables
- Archive old data to separate tables
- Optimize data types to reduce row size

Example 3: Index Optimization

User request: "Review indexes on the products table"

Approach:
- List all current indexes
- Identify unused or redundant indexes
- Check for missing indexes on query patterns
- Calculate index selectivity

Review Process:

-- 1. Show all indexes
SHOW INDEX FROM products;

-- 2. Check index usage (MySQL 5.6+)
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'your_database'
  AND object_name = 'products';

-- 3. Analyze query patterns
SELECT DISTINCT column_name
FROM information_schema.statistics
WHERE table_name = 'products';

Requirements

  • Access to database schema information
  • Understanding of SQL and database design principles
  • Ability to read EXPLAIN query plans (if available)
  • Knowledge of the application's query patterns

Best Practices

  • Always explain the reasoning behind suggestions
  • Consider both read and write performance impacts
  • Account for data volume and growth patterns
  • Suggest incremental improvements when possible
  • Document assumptions made during analysis
  • Provide migration scripts for proposed changes
  • Test recommendations in a non-production environment first

Common Patterns

Pattern 1: E-commerce Database

  • Heavy read operations on product catalog
  • Frequent JOIN operations between products, categories, and prices
  • Date-based queries for orders
  • Key optimizations: Composite indexes, query caching, read replicas

Pattern 2: User Management System

  • Frequent lookups by email or username
  • Session management with expiration
  • Role-based access control queries
  • Key optimizations: Unique indexes, covering indexes, denormalization

Pattern 3: Analytics Database

  • Large aggregation queries
  • Time-series data
  • Reporting queries with multiple JOINs
  • Key optimizations: Partitioning, summary tables, columnstore indexes

Troubleshooting

No Slow Queries Detected

  • Check slow query log settings
  • Verify logging is enabled
  • Look for queries with high execution count (not just slow time)

Index Not Being Used

  • Check index selectivity (should be high)
  • Verify query uses indexed columns in WHERE clause
  • Consider forcing index with USE INDEX hint for testing
  • Check for implicit type conversions preventing index use

Table Lock Contention

  • Identify long-running transactions
  • Consider using InnoDB over MyISAM for row-level locking
  • Optimize batch operations to reduce lock time

Resources

Bundled resources in this skill package:
- references/schema-patterns.sql - Common schema patterns
- scripts/analyze-table.php - Automated analysis script
- assets/optimization-checklist.md - Comprehensive checklist

Use base directory from composer read-skill output to locate these files.

Notes

  • Always backup before making schema changes
  • Test in development environment first
  • Monitor performance before and after changes
  • Document all modifications for team awareness

# README.md

Composer AI Agent Skill Plugin

A Composer plugin that enables universal AI agent skill distribution and management for PHP projects. Automatically discovers, registers, and manages AI agent skills from Composer packages, providing a standardized way for the PHP ecosystem to share agent capabilities.

CI
Tests
PHPStan
PHP Version
Symfony
License

πŸ”Œ Compatibility

This is an Agent Skill following the open standard originally developed by Anthropic and released for cross-platform use.

Supported Platforms:
- βœ… Claude Code (Anthropic)
- βœ… Cursor
- βœ… GitHub Copilot
- βœ… Other skills-compatible AI agents

Skills are portable packages of procedural knowledge that work across any AI agent supporting the Agent Skills specification.

Features

  • Automatic Discovery: Finds all packages with type ai-agent-skill
  • AGENTS.md Generation: Creates XML skill index compatible with openskills
  • CLI Commands: composer list-skills and composer read-skill for skill inspection
  • Convention Over Configuration: Works out of the box with zero configuration
  • Progressive Disclosure: Lightweight index, full details on demand
  • Security First: Rejects absolute paths, validates all skill metadata
  • Multiple Skills Per Package: Support for both single and multi-skill packages

Installation

composer require netresearch/composer-agent-skill-plugin

During installation, Composer will prompt you to authorize the plugin:

netresearch/composer-agent-skill-plugin contains a Composer plugin which is currently not in your allow-plugins config.
Do you trust "netresearch/composer-agent-skill-plugin" to execute code and wish to enable it now? [y,n,d,?]

Choose y to allow the plugin to activate.

Non-Interactive Installation

For CI/CD or non-interactive environments, pre-authorize the plugin in your composer.json:

{
    "config": {
        "allow-plugins": {
            "netresearch/composer-agent-skill-plugin": true
        }
    }
}

Quick Start

1. Install Skill Packages

Install any package with type ai-agent-skill:

composer require vendor/database-analyzer-skill

2. Skills Auto-Register

The plugin automatically:
- Discovers skill packages during composer install or composer update
- Generates/updates AGENTS.md in your project root
- Registers skills in XML format for AI agent consumption

3. Use Skills

AI agents automatically discover skills via AGENTS.md:

# List all available skills
composer list-skills

# Read a specific skill
composer read-skill database-analyzer

Usage

List Available Skills

$ composer list-skills

Available AI Agent Skills:

  database-analyzer        vendor/db-skill               1.2.0
  oro-bundle-helper        vendor/oro-skill              1.0.0
  symfony-security         vendor/symfony-security       2.1.0

3 skills available. Use 'composer read-skill <name>' for details.

Read Skill Details

$ composer read-skill database-analyzer

Reading: database-analyzer
Package: vendor/db-skill v1.2.0
Base Directory: vendor/vendor/db-skill

---
name: database-analyzer
description: Analyze and optimize database schemas and relationships
---

# Database Analyzer Skill

[Full SKILL.md content with instructions and examples]

Skill read: database-analyzer

The Base Directory is the directory containing SKILL.md, used as the root for resolving bundled resources like references/, scripts/, and assets/.

Creating Skill Packages

Basic Skill Package

1. Create composer.json:

{
  "name": "vendor/my-skill",
  "description": "My awesome AI agent skill",
  "type": "ai-agent-skill",
  "license": "MIT",
  "require": {
    "php": "^8.2"
  }
}

2. Create SKILL.md in package root:

---
name: my-skill
description: Brief description of what this skill does and when to use it
---

# My Skill

## Instructions

Step-by-step guidance for using this skill...

## Examples

Example 1: How to use feature X
Example 2: How to handle scenario Y

## Requirements

- PHP 8.2+
- Any other dependencies

3. Publish to Packagist:

git tag 1.0.0
git push --tags

Multi-Skill Package

For packages containing multiple skills, configure paths in extra.ai-agent-skill:

{
  "name": "vendor/database-tools",
  "type": "ai-agent-skill",
  "require": {
    "netresearch/composer-agent-skill-plugin": "*"
  },
  "extra": {
    "ai-agent-skill": [
      "skills/analyzer/SKILL.md",
      "skills/optimizer/SKILL.md",
      "skills/validator/SKILL.md"
    ]
  }
}

Custom Skill Path

For a single skill in a non-standard location:

{
  "name": "vendor/custom-skill",
  "type": "ai-agent-skill",
  "require": {
    "netresearch/composer-agent-skill-plugin": "*"
  },
  "extra": {
    "ai-agent-skill": "docs/agent-skill.md"
  }
}

Security Note: Only relative paths from package root are allowed. Absolute paths are rejected.

SKILL.md Schema

Skills must follow the Claude Code SKILL.md specification:

Required Frontmatter

---
name: skill-name          # lowercase, numbers, hyphens only (max 64 chars)
description: Clear description of functionality and triggers (max 1024 chars)
---

Optional Frontmatter

---
name: my-skill
description: What it does and when to use it
allowed-tools: [Read, Grep, Glob]  # Claude Code only
---

Validation Rules

  • Name format: ^[a-z0-9-]{1,64}$ (lowercase alphanumeric and hyphens)
  • Name length: Maximum 64 characters
  • Description length: Maximum 1024 characters
  • YAML syntax: Valid YAML with proper delimiters (---)

Configuration Options

Default (Convention)

No configuration needed. Plugin looks for SKILL.md in package root:

vendor/my-skill/
β”œβ”€β”€ composer.json
β”œβ”€β”€ SKILL.md          ← Auto-discovered
└── src/

Custom Single Skill

{
  "extra": {
    "ai-agent-skill": "custom/path/skill.md"
  }
}

Multiple Skills

{
  "extra": {
    "ai-agent-skill": [
      "skills/skill-one.md",
      "skills/skill-two.md",
      "docs/skill-three.md"
    ]
  }
}

Troubleshooting

No Skills Found

[WARNING] No AI agent skills found in installed packages.

Solution: Install packages with "type": "ai-agent-skill" in their composer.json.

Duplicate Skill Names

[vendor/tools-b] Duplicate skill name 'database-analyzer' (already defined by vendor/tools-a).
                 Using skill from vendor/tools-b (last one wins).

Behavior: Last package wins. Consider renaming skills to avoid conflicts.

Invalid Frontmatter

[vendor/broken-skill] Invalid frontmatter in 'SKILL.md': Missing required field: 'description'

Solution: Ensure SKILL.md has both name and description fields in valid YAML format.

Malformed YAML

[vendor/broken-yaml] Malformed YAML in 'SKILL.md':
                     A colon cannot be used in an unquoted mapping value at line 3

Solution: Fix YAML syntax. Use spaces (not tabs), quote values with colons.

Missing SKILL.md

[vendor/missing-skill] SKILL.md not found at 'SKILL.md'.
                       Expected SKILL.md in package root (convention).

Solution: Create SKILL.md in package root or configure extra.ai-agent-skill path.

Absolute Path Rejected

[vendor/unsafe-config] Absolute paths not allowed in 'extra.ai-agent-skill'.
                       Use relative paths from package root.

Solution: Use relative paths like "skills/analyzer.md" instead of /absolute/path.

How It Works

Discovery Process

  1. Plugin hooks into composer install and composer update events
  2. Finds all packages with type ai-agent-skill
  3. Reads each package's composer.json for skill paths
  4. Parses SKILL.md files and validates frontmatter
  5. Generates XML skill registry in AGENTS.md

AGENTS.md Structure

<skills_system priority="1">

## Available Skills

<!-- SKILLS_TABLE_START -->
<usage>
When users ask you to perform tasks, check if any of the available skills below can help complete the task more effectively.

How to use skills:
- Invoke: Bash("composer read-skill <skill-name>")
- The skill content will load with detailed instructions
- Base directory provided in output for resolving bundled resources
</usage>

<available_skills>

<skill>
<name>database-analyzer</name>
<description>Analyze and optimize database schemas</description>
<location>vendor/vendor/db-skill</location>
</skill>

</available_skills>
<!-- SKILLS_TABLE_END -->

</skills_system>

Progressive Disclosure

  • AGENTS.md: Lightweight index with skill names and descriptions
  • read-skill: Full SKILL.md content loaded on demand
  • Benefits: Fast discovery, reduced context size, on-demand details

Requirements

  • PHP 8.2 or higher
  • Composer 2.1 or higher
  • Symfony YAML Component 5.4+, 6.0+, or 7.0+
  • Symfony Console Component 5.4+, 6.0+, or 7.0+

Development

Running Tests

# Run all tests
./vendor/bin/phpunit

# Run with coverage
./vendor/bin/phpunit --coverage-text

# Run specific test
./vendor/bin/phpunit tests/Unit/SkillDiscoveryTest.php

Code Quality

# PHPStan static analysis (level 8)
./vendor/bin/phpstan analyse

# PHP CS Fixer (PSR-12)
./vendor/bin/php-cs-fixer fix --allow-risky=yes

# Check without fixing
./vendor/bin/php-cs-fixer fix --dry-run --allow-risky=yes

Project Structure

src/
β”œβ”€β”€ Commands/
β”‚   β”œβ”€β”€ ListSkillsCommand.php    # composer list-skills
β”‚   └── ReadSkillCommand.php     # composer read-skill
β”œβ”€β”€ Exceptions/
β”‚   └── InvalidSkillException.php
β”œβ”€β”€ AgentsMdGenerator.php         # AGENTS.md generation
β”œβ”€β”€ CommandCapability.php         # Command registration
β”œβ”€β”€ SkillDiscovery.php            # Package discovery
└── SkillPlugin.php               # Main plugin class

tests/
β”œβ”€β”€ Unit/                         # Unit tests
β”œβ”€β”€ Integration/                  # Integration tests
└── Fixtures/                     # Test fixtures

Contributing

Contributions are welcome! Please:

  1. Fork the repository
  2. Create a feature branch (git checkout -b feature/amazing-feature)
  3. Write tests for your changes
  4. Ensure all tests pass (./vendor/bin/phpunit)
  5. Run static analysis (./vendor/bin/phpstan analyse)
  6. Fix code style (./vendor/bin/php-cs-fixer fix --allow-risky=yes)
  7. Commit your changes (git commit -m 'Add amazing feature')
  8. Push to the branch (git push origin feature/amazing-feature)
  9. Open a Pull Request

Code Standards

  • PSR-12 coding style
  • PHPStan level 8
  • 100% type coverage
  • Comprehensive tests (>80% coverage)

License

This project is licensed under the MIT License - see the LICENSE file for details.

Inspiration

Inspired by openskills - Universal AI Agent Skills for standardized skill distribution across development environments.

Support


Made with ❀️ by Netresearch

# Supported AI Coding Agents

This skill is compatible with the SKILL.md standard and works with all major AI coding agents:

Learn more about the SKILL.md standard and how to use these skills with your preferred AI coding agent.