/**
* Capabilities Resource
*
* Server version, tool categories, extension status, and recommendations.
*/
import type { PostgresAdapter } from '../PostgresAdapter.js';
import type { ResourceDefinition, RequestContext } from '../../../types/index.js';
interface ExtensionStatus {
installed: boolean;
purpose: string;
whyCritical: string;
installNote: string;
requiredFor: string[];
}
interface ToolCategory {
count: number;
description: string;
examples: string[];
}
export function createCapabilitiesResource(adapter: PostgresAdapter): ResourceDefinition {
return {
uri: 'postgres://capabilities',
name: 'Server Capabilities',
description: 'PostgreSQL version, installed extensions, tool categories, and recommendations',
mimeType: 'application/json',
handler: async (_uri: string, _context: RequestContext) => {
// Get PostgreSQL version
const versionResult = await adapter.executeQuery('SELECT version()');
const pgVersion = versionResult.rows?.[0]?.['version'] ?? 'Unknown';
// Get installed extensions
const extResult = await adapter.executeQuery(`
SELECT extname, extversion
FROM pg_extension
ORDER BY extname
`);
const extensions = extResult.rows ?? [];
const installedNames = extensions.map((e: Record<string, unknown>) => e['extname'] as string);
// Check critical extensions
const hasPgStat = installedNames.includes('pg_stat_statements');
const hasHypopg = installedNames.includes('hypopg');
const hasPgvector = installedNames.includes('vector');
const hasPostgis = installedNames.includes('postgis');
// Tool categories with examples
const toolCategories: Record<string, ToolCategory> = {
'Core': { count: 13, description: 'CRUD, schema, tables, indexes, health analysis', examples: ['pg_query', 'pg_insert', 'pg_create_table'] },
'Transactions': { count: 7, description: 'BEGIN, COMMIT, ROLLBACK, savepoints', examples: ['pg_begin', 'pg_commit', 'pg_savepoint'] },
'JSONB': { count: 19, description: 'jsonb_set, jsonb_extract, path queries, merge, diff', examples: ['pg_jsonb_get', 'pg_jsonb_set', 'pg_jsonb_merge'] },
'Text': { count: 11, description: 'Full-text search, trigram, fuzzy matching', examples: ['pg_fts_search', 'pg_trigram_search', 'pg_fuzzy_match'] },
'Stats': { count: 8, description: 'Descriptive stats, percentiles, correlation, regression', examples: ['pg_stats_summary', 'pg_percentile', 'pg_correlation'] },
'Performance': { count: 16, description: 'EXPLAIN ANALYZE, plan compare, baseline', examples: ['pg_explain_analyze', 'pg_plan_compare', 'pg_query_baseline'] },
'Admin': { count: 10, description: 'VACUUM, ANALYZE, REINDEX, configuration', examples: ['pg_vacuum', 'pg_analyze', 'pg_reindex'] },
'Monitoring': { count: 11, description: 'Database sizes, connections, replication', examples: ['pg_database_size', 'pg_active_connections', 'pg_replication_status'] },
'Backup': { count: 9, description: 'pg_dump, COPY, physical backup, restore validation', examples: ['pg_dump_schema', 'pg_copy_export', 'pg_restore_command'] },
'Schema': { count: 10, description: 'Schemas, sequences, views, functions, triggers', examples: ['pg_create_schema', 'pg_create_view', 'pg_list_functions'] },
'Vector': { count: 14, description: 'pgvector - similarity search, clustering', examples: ['pg_vector_search', 'pg_vector_add_column', 'pg_vector_create_index'] },
'PostGIS': { count: 12, description: 'Geospatial operations, spatial indexes', examples: ['pg_distance', 'pg_intersection', 'pg_spatial_index'] },
'Partitioning': { count: 6, description: 'Range/list/hash partitioning management', examples: ['pg_create_partitioned_table', 'pg_create_partition', 'pg_attach_partition'] }
};
// Critical extension status with explanations
const criticalExtensions: Record<string, ExtensionStatus> = {
pg_stat_statements: {
installed: hasPgStat,
purpose: 'Query performance tracking',
whyCritical: 'Essential for identifying slow queries, optimization opportunities, and workload analysis. Without it, performance tuning is guesswork.',
installNote: 'CREATE EXTENSION pg_stat_statements; -- Also add to shared_preload_libraries in postgresql.conf and restart',
requiredFor: ['pg_top_queries', 'pg_slow_queries', 'pg_query_stats']
},
hypopg: {
installed: hasHypopg,
purpose: 'Hypothetical index testing (zero-risk)',
whyCritical: 'Allows testing index effectiveness without actually creating them. Prevents creating unused indexes.',
installNote: 'CREATE EXTENSION hypopg; -- No restart required',
requiredFor: ['pg_explain_analyze with hypothetical indexes']
},
pgvector: {
installed: hasPgvector,
purpose: 'Vector similarity search',
whyCritical: 'Required for AI/ML embeddings, semantic search, and recommendation systems.',
installNote: 'CREATE EXTENSION vector; -- May need to install from packages first',
requiredFor: ['pg_vector_search', 'pg_vector_create_index', 'all pg_vector_* tools']
},
postgis: {
installed: hasPostgis,
purpose: 'Geospatial operations',
whyCritical: 'Required for location-based queries, mapping, and geographic analysis.',
installNote: 'CREATE EXTENSION postgis; -- May need to install from packages first',
requiredFor: ['pg_distance', 'pg_intersection', 'all pg_geo_* tools']
}
};
// Generate recommendations
const recommendations: { priority: string; extension: string; sql: string; reason: string }[] = [];
if (!hasPgStat) {
recommendations.push({
priority: 'HIGH',
extension: 'pg_stat_statements',
sql: 'CREATE EXTENSION IF NOT EXISTS pg_stat_statements;',
reason: 'Critical for performance monitoring'
});
}
if (!hasHypopg) {
recommendations.push({
priority: 'MEDIUM',
extension: 'hypopg',
sql: 'CREATE EXTENSION IF NOT EXISTS hypopg;',
reason: 'Enables risk-free index testing'
});
}
return {
serverVersion: '0.3.0',
postgresqlVersion: pgVersion,
totalTools: 146,
totalResources: 15,
totalPrompts: 7,
toolCategories,
installedExtensions: extensions,
criticalExtensions,
recommendations
};
}
};
}
