/**
* Extension Setup Prompt
*
* Guide for installing and configuring PostgreSQL extensions.
*/
import type { PromptDefinition, RequestContext } from "../../../types/index.js";
interface ExtensionInfo {
purpose: string;
useCases: string[];
}
const extensionInfo: Record<string, ExtensionInfo> = {
pgvector: {
purpose: "AI-native vector similarity search",
useCases: ["Semantic search", "Recommendation systems", "Image similarity"],
},
postgis: {
purpose: "Geospatial operations and GIS",
useCases: ["Mapping", "Location-based services", "Spatial analysis"],
},
hypopg: {
purpose: "Hypothetical index testing",
useCases: ["Index optimization", "Zero-risk testing", "Performance tuning"],
},
pg_stat_statements: {
purpose: "Query performance tracking",
useCases: [
"Performance monitoring",
"Slow query detection",
"Workload analysis",
],
},
pg_trgm: {
purpose: "Fuzzy text search with trigrams",
useCases: ["Fuzzy matching", "Typo tolerance", "Text similarity"],
},
fuzzystrmatch: {
purpose: "Phonetic matching and edit distance",
useCases: ["Soundex matching", "Levenshtein distance", "Metaphone"],
},
pg_cron: {
purpose: "Job scheduling within PostgreSQL",
useCases: ["Scheduled tasks", "Maintenance automation", "ETL pipelines"],
},
pg_partman: {
purpose: "Automated partition management",
useCases: [
"Time-series partitioning",
"Data retention",
"Large table management",
],
},
pg_stat_kcache: {
purpose: "OS-level CPU and I/O metrics",
useCases: [
"Performance profiling",
"Resource monitoring",
"Query optimization",
],
},
citext: {
purpose: "Case-insensitive text type",
useCases: ["Email storage", "Username handling", "Case-insensitive search"],
},
ltree: {
purpose: "Hierarchical tree-structured data",
useCases: ["Categories", "Org charts", "File paths", "Taxonomies"],
},
pgcrypto: {
purpose: "Cryptographic functions",
useCases: ["Password hashing", "Data encryption", "Secure UUIDs"],
},
};
export function createExtensionSetupPrompt(): PromptDefinition {
return {
name: "pg_extension_setup",
description:
"Guide for installing and configuring PostgreSQL extensions (pgvector, postgis, pg_cron, pg_partman, citext, ltree, pgcrypto, etc.).",
arguments: [
{
name: "extensionName",
description:
"Extension name: pgvector, postgis, pg_cron, pg_partman, pg_stat_kcache, citext, ltree, pgcrypto, hypopg, pg_stat_statements, pg_trgm, fuzzystrmatch",
required: true,
},
],
// eslint-disable-next-line @typescript-eslint/require-await
handler: async (
args: Record<string, string>,
_context: RequestContext,
): Promise<string> => {
const extensionName = args["extensionName"] ?? "pg_stat_statements";
const info = extensionInfo[extensionName] ?? {
purpose: "PostgreSQL extension",
useCases: ["Database operations"],
};
let content = `# Extension Setup Guide - ${extensionName}
**Purpose:** ${info.purpose}
## Use Cases
${info.useCases.map((uc) => `- ${uc}`).join("\n")}
## Setup Steps
### 1. Check Availability
\`\`\`sql
SELECT * FROM pg_available_extensions WHERE name = '${extensionName}';
\`\`\`
If not available, install at system level:
\`\`\`bash
# Ubuntu/Debian
sudo apt-get install postgresql-${extensionName}
# macOS (Homebrew)
brew install ${extensionName}
\`\`\`
### 2. Install Extension
\`\`\`sql
CREATE EXTENSION IF NOT EXISTS ${extensionName};
\`\`\`
Verify: \`SELECT extname, extversion FROM pg_extension WHERE extname = '${extensionName}';\`
### 3. Configuration
`;
if (extensionName === "pg_stat_statements") {
content += `
**postgresql.conf:**
\`\`\`
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
\`\`\`
**Restart PostgreSQL after configuration!**
**Verify:**
\`\`\`sql
SELECT query, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC LIMIT 10;
\`\`\`
`;
} else if (extensionName === "hypopg") {
content += `
No configuration needed - works out of the box!
**Test it:**
\`\`\`sql
SELECT * FROM hypopg_create_index('CREATE INDEX ON users(email)');
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
SELECT hypopg_reset();
\`\`\`
`;
} else if (extensionName === "pgvector") {
content += `
**Create vector column:**
\`\`\`sql
ALTER TABLE documents ADD COLUMN embedding vector(1536);
\`\`\`
**Create HNSW index:**
\`\`\`sql
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);
\`\`\`
Use \`pg_setup_pgvector\` prompt for detailed setup.
`;
} else if (extensionName === "postgis") {
content += `
**Check version:**
\`\`\`sql
SELECT PostGIS_Full_Version();
\`\`\`
**Create spatial column:**
\`\`\`sql
ALTER TABLE locations ADD COLUMN geom GEOGRAPHY(POINT, 4326);
CREATE INDEX ON locations USING GIST (geom);
\`\`\`
Use \`pg_setup_postgis\` prompt for detailed setup.
`;
} else if (extensionName === "pg_cron") {
content += `
**postgresql.conf:**
\`\`\`
shared_preload_libraries = 'pg_cron'
cron.database_name = 'your_database'
\`\`\`
**Restart PostgreSQL after configuration!**
**Schedule a job:**
\`\`\`sql
SELECT cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM ANALYZE');
\`\`\`
Use \`pg_setup_pgcron\` prompt for detailed setup.
`;
} else if (extensionName === "pg_partman") {
content += `
**Create parent table:**
\`\`\`sql
SELECT partman.create_parent(
p_parent_table => 'public.events',
p_control => 'created_at',
p_interval => '1 month'
);
\`\`\`
Use \`pg_setup_partman\` prompt for detailed setup.
`;
} else if (extensionName === "pg_stat_kcache") {
content += `
**postgresql.conf:**
\`\`\`
shared_preload_libraries = 'pg_stat_statements, pg_stat_kcache'
\`\`\`
**Restart PostgreSQL after configuration!**
Use \`pg_setup_kcache\` prompt for detailed setup.
`;
} else if (extensionName === "citext") {
content += `
**Use for case-insensitive columns:**
\`\`\`sql
ALTER TABLE users ALTER COLUMN email TYPE CITEXT;
\`\`\`
Use \`pg_setup_citext\` prompt for detailed setup.
`;
} else if (extensionName === "ltree") {
content += `
**Create hierarchical column:**
\`\`\`sql
ALTER TABLE categories ADD COLUMN path LTREE;
CREATE INDEX ON categories USING GIST (path);
\`\`\`
Use \`pg_setup_ltree\` prompt for detailed setup.
`;
} else if (extensionName === "pgcrypto") {
content += `
**Secure password hashing:**
\`\`\`sql
INSERT INTO users (email, password_hash)
VALUES ('user@example.com', crypt('password', gen_salt('bf', 10)));
\`\`\`
Use \`pg_setup_pgcrypto\` prompt for detailed setup.
`;
} else {
content += `
Extension-specific configuration may vary. Check the official documentation.
`;
}
content += `
### 4. Best Practices
- Always test extensions in development first
- Check compatibility with your PostgreSQL version
- Monitor performance impact after enabling
- Keep extensions updated
### 5. Troubleshooting
**Extension not found:** Verify system-level installation, check pg_config --sharedir
**Permission denied:** Must be superuser to install extensions
**Version mismatch:** Ensure extension compatible with PostgreSQL version
**Pro Tip:** PostgreSQL's extension ecosystem is one of its greatest strengths!`;
return content;
},
};
}
