/**
* postgres-mcp - Extension Tool Schemas
*
* Input validation schemas for PostgreSQL extensions:
* - pg_stat_kcache
* - citext
* - ltree
* - pgcrypto
*/
import { z } from 'zod';
// =============================================================================
// pg_stat_kcache Schemas
// =============================================================================
/**
* Schema for querying enhanced statistics with kcache data.
* Joins pg_stat_statements with pg_stat_kcache for full picture.
*/
export const KcacheQueryStatsSchema = z.preprocess(
normalizeOptionalParams,
z.object({
limit: z.number().optional().describe('Maximum number of queries to return (default: 25)'),
orderBy: z.enum(['total_time', 'cpu_time', 'reads', 'writes']).optional()
.describe('Order results by metric (default: total_time)'),
minCalls: z.number().optional().describe('Minimum call count to include')
})
);
/**
* Schema for top resource consumers query.
*/
export const KcacheTopConsumersSchema = z.object({
resource: z.enum(['cpu', 'reads', 'writes', 'page_faults']).describe('Resource type to rank by'),
limit: z.number().optional().describe('Number of top queries to return (default: 10)')
});
/**
* Schema for database-level aggregation.
*/
export const KcacheDatabaseStatsSchema = z.preprocess(
normalizeOptionalParams,
z.object({
database: z.string().optional().describe('Database name (current database if omitted)')
})
);
/**
* Schema for identifying resource-bound queries.
*/
export const KcacheResourceAnalysisSchema = z.preprocess(
normalizeOptionalParams,
z.object({
queryId: z.string().optional().describe('Specific query ID to analyze (all if omitted)'),
threshold: z.number().optional().describe('CPU/IO ratio threshold for classification (default: 0.5)'),
limit: z.number().optional().describe('Maximum number of queries to return (default: 50)')
})
);
// =============================================================================
// citext Schemas
// =============================================================================
/**
* Handle undefined/null params for tools with optional-only parameters
*/
function normalizeOptionalParams(input: unknown): Record<string, unknown> {
if (typeof input !== 'object' || input === null) {
return {};
}
return input as Record<string, unknown>;
}
/**
* Base schema for MCP visibility (shows all parameters including aliases).
*/
export const CitextConvertColumnSchemaBase = z.object({
table: z.string().describe('Table name'),
column: z.string().optional().describe('Text column to convert to citext'),
col: z.string().optional().describe('Alias for column'),
schema: z.string().optional().describe('Schema name (default: public)')
});
/**
* Transformed schema for converting a text column to citext.
* Resolves aliases and validates required fields.
*/
export const CitextConvertColumnSchema = CitextConvertColumnSchemaBase.transform((data) => ({
table: data.table,
column: data.column ?? data.col ?? '',
schema: data.schema
})).refine((data) => data.column !== '', {
message: 'column (or col alias) is required'
});
/**
* Schema for listing citext columns.
*/
export const CitextListColumnsSchema = z.preprocess(
normalizeOptionalParams,
z.object({
schema: z.string().optional().describe('Schema name to filter (all schemas if omitted)')
})
);
/**
* Schema for analyzing candidate columns for citext conversion.
*/
export const CitextAnalyzeCandidatesSchema = z.preprocess(
normalizeOptionalParams,
z.object({
patterns: z.array(z.string()).optional()
.describe('Column name patterns to match (default: email, username, name, etc.)'),
schema: z.string().optional().describe('Schema name to filter')
})
);
/**
* Base schema for MCP visibility (shows all parameters including aliases).
*/
export const CitextSchemaAdvisorSchemaBase = z.object({
table: z.string().optional().describe('Table name to analyze (required)'),
tableName: z.string().optional().describe('Alias for table'),
schema: z.string().optional().describe('Schema name (default: public)')
});
/**
* Transformed schema for citext schema advisor tool.
* Resolves aliases and validates required fields.
*/
export const CitextSchemaAdvisorSchema = z.preprocess(
(input) => (typeof input !== 'object' || input === null) ? {} : input,
CitextSchemaAdvisorSchemaBase
).transform((data) => ({
table: data.table ?? data.tableName ?? '',
schema: data.schema
})).refine((data) => data.table !== '', {
message: 'table (or tableName alias) is required'
});
// =============================================================================
// ltree Schemas
// =============================================================================
/**
* Preprocess ltree table parameters:
* - Alias: tableName/name -> table
* - Alias: col -> column
* - Parse schema.table format
*/
function preprocessLtreeTableParams(input: unknown): unknown {
if (typeof input !== 'object' || input === null) return input;
const obj = input as Record<string, unknown>;
const result = { ...obj };
// Alias: tableName/name -> table
if (result['table'] === undefined) {
if (result['tableName'] !== undefined) result['table'] = result['tableName'];
else if (result['name'] !== undefined) result['table'] = result['name'];
}
// Alias: col -> column
if ((result['col'] !== undefined) && result['column'] === undefined) {
result['column'] = result['col'];
}
// Parse schema.table format
if (typeof result['table'] === 'string' && result['table'].includes('.') && result['schema'] === undefined) {
const parts = result['table'].split('.');
if (parts.length === 2) {
result['schema'] = parts[0];
result['table'] = parts[1];
}
}
return result;
}
// -----------------------------------------------------------------------------
// Base schemas for MCP visibility (simple z.object with all params + aliases)
// -----------------------------------------------------------------------------
/**
* Base schema for MCP visibility - shows all parameters including aliases.
*/
export const LtreeQuerySchemaBase = z.object({
table: z.string().describe('Table name'),
tableName: z.string().optional().describe('Alias for table'),
name: z.string().optional().describe('Alias for table'),
column: z.string().describe('ltree column name'),
col: z.string().optional().describe('Alias for column'),
path: z.string().describe('ltree path to query (e.g., "Top.Science.Astronomy")'),
pattern: z.string().optional().describe('Alias for path'),
mode: z.enum(['ancestors', 'descendants', 'exact']).optional()
.describe('Query mode: ancestors, descendants (default), or exact'),
type: z.enum(['ancestors', 'descendants', 'exact']).optional()
.describe('Alias for mode'),
schema: z.string().optional().describe('Schema name (default: public)'),
limit: z.number().optional().describe('Maximum results')
});
/**
* Base schema for MCP visibility - shows all parameters including aliases.
*/
export const LtreeSubpathSchemaBase = z.object({
path: z.string().describe('ltree path (e.g., "Top.Science.Astronomy.Stars")'),
offset: z.number().optional().describe('Starting position (0-indexed, negative counts from end)'),
start: z.number().optional().describe('Alias for offset'),
from: z.number().optional().describe('Alias for offset'),
length: z.number().optional().describe('Number of labels (omit for rest of path)'),
len: z.number().optional().describe('Alias for length')
});
/**
* Base schema for MCP visibility - shows all parameters including aliases.
*/
export const LtreeMatchSchemaBase = z.object({
table: z.string().describe('Table name'),
tableName: z.string().optional().describe('Alias for table'),
name: z.string().optional().describe('Alias for table'),
column: z.string().describe('ltree column name'),
col: z.string().optional().describe('Alias for column'),
pattern: z.string().describe('lquery pattern (e.g., "*.Science.*" or "Top.*{1,3}.Stars")'),
query: z.string().optional().describe('Alias for pattern'),
lquery: z.string().optional().describe('Alias for pattern'),
schema: z.string().optional().describe('Schema name (default: public)'),
limit: z.number().optional().describe('Maximum results'),
maxResults: z.number().optional().describe('Alias for limit')
});
/**
* Base schema for MCP visibility - shows all parameters including aliases.
*/
export const LtreeConvertColumnSchemaBase = z.object({
table: z.string().describe('Table name'),
tableName: z.string().optional().describe('Alias for table'),
name: z.string().optional().describe('Alias for table'),
column: z.string().describe('Text column to convert to ltree'),
col: z.string().optional().describe('Alias for column'),
schema: z.string().optional().describe('Schema name (default: public)')
});
/**
* Base schema for MCP visibility - shows all parameters including aliases.
*/
export const LtreeIndexSchemaBase = z.object({
table: z.string().describe('Table name'),
tableName: z.string().optional().describe('Alias for table'),
name: z.string().optional().describe('Alias for table'),
column: z.string().describe('ltree column name'),
col: z.string().optional().describe('Alias for column'),
indexName: z.string().optional().describe('Custom index name (auto-generated if omitted)'),
schema: z.string().optional().describe('Schema name (default: public)')
});
// -----------------------------------------------------------------------------
// Transformed schemas for handler validation (with alias resolution)
// -----------------------------------------------------------------------------
/**
* Schema for querying ltree hierarchies (ancestors/descendants).
* Accepts 'pattern' as alias for 'path', 'type' as alias for 'mode', 'col'/'tableName'/'name' aliases.
*/
export const LtreeQuerySchema = z.preprocess((input) => {
const obj = preprocessLtreeTableParams(input);
if (typeof obj !== 'object' || obj === null) return obj;
const result = obj as Record<string, unknown>;
if ('pattern' in result && !('path' in result)) {
result['path'] = result['pattern'];
}
// Alias: type -> mode
if ('type' in result && !('mode' in result)) {
result['mode'] = result['type'];
}
return result;
}, z.object({
table: z.string().describe('Table name'),
column: z.string().describe('ltree column name'),
path: z.string().describe('ltree path to query (e.g., "Top.Science.Astronomy")'),
mode: z.enum(['ancestors', 'descendants', 'exact']).optional()
.describe('Query mode: ancestors (@>), descendants (<@), or exact (default: descendants)'),
schema: z.string().optional().describe('Schema name (default: public)'),
limit: z.number().optional().describe('Maximum results')
}));
/**
* Schema for extracting subpath from ltree.
* Accepts 'start'/'from' as alias for 'offset', 'len'/'end' as alias for 'length'.
*/
export const LtreeSubpathSchema = z.preprocess((input) => {
if (typeof input !== 'object' || input === null) return input;
const obj = input as Record<string, unknown>;
const result = { ...obj };
// Alias: len -> length (PostgreSQL function uses len)
if ('len' in obj && !('length' in obj)) {
result['length'] = obj['len'];
}
// Alias: start/from -> offset
if ('start' in obj && !('offset' in obj)) {
result['offset'] = obj['start'];
} else if ('from' in obj && !('offset' in obj)) {
result['offset'] = obj['from'];
}
// Default offset to 0 if not provided
if (result['offset'] === undefined) {
result['offset'] = 0;
}
// Alias: end -> length (calculate length from start/end if both provided)
if ('end' in obj && !('length' in obj) && !('len' in obj)) {
const start = (result['offset'] ?? 0) as number;
const end = obj['end'] as number;
result['length'] = end - start;
}
return result;
}, z.object({
path: z.string().describe('ltree path (e.g., "Top.Science.Astronomy.Stars")'),
offset: z.number().describe('Starting position (0-indexed, negative counts from end). Default: 0'),
length: z.number().optional().describe('Number of labels (omit for rest of path). Alias: len')
}));
/**
* Schema for finding longest common ancestor.
*/
export const LtreeLcaSchema = z.object({
paths: z.array(z.string()).min(2).describe('Array of ltree paths to find common ancestor')
});
/**
* Schema for pattern matching with lquery.
* Accepts 'query'/'lquery' as aliases for 'pattern', 'maxResults' as alias for 'limit'.
*/
export const LtreeMatchSchema = z.preprocess((input) => {
const obj = preprocessLtreeTableParams(input);
if (typeof obj !== 'object' || obj === null) return obj;
const result = obj as Record<string, unknown>;
// Alias: query/lquery -> pattern
if (result['pattern'] === undefined) {
if (result['query'] !== undefined) result['pattern'] = result['query'];
else if (result['lquery'] !== undefined) result['pattern'] = result['lquery'];
}
// Alias: maxResults -> limit
if ((result['maxResults'] !== undefined) && result['limit'] === undefined) {
result['limit'] = result['maxResults'];
}
return result;
}, z.object({
table: z.string().describe('Table name'),
column: z.string().describe('ltree column name'),
pattern: z.string().describe('lquery pattern (e.g., "*.Science.*" or "Top.*{1,3}.Stars")'),
schema: z.string().optional().describe('Schema name (default: public)'),
limit: z.number().optional().describe('Maximum results')
}));
/**
* Schema for listing ltree columns in the database.
*/
export const LtreeListColumnsSchema = z.preprocess(
normalizeOptionalParams,
z.object({
schema: z.string().optional().describe('Schema name to filter (all schemas if omitted)')
})
);
/**
* Schema for converting a text column to ltree.
* Accepts 'tableName'/'name' as aliases for 'table', 'col' as alias for 'column'.
*/
export const LtreeConvertColumnSchema = z.preprocess(
preprocessLtreeTableParams,
z.object({
table: z.string().describe('Table name'),
column: z.string().describe('Text column to convert to ltree'),
schema: z.string().optional().describe('Schema name (default: public)')
})
);
/**
* Schema for creating a GiST index on ltree column.
* Accepts 'tableName'/'name' as aliases for 'table', 'col' as alias for 'column'.
*/
export const LtreeIndexSchema = z.preprocess(
preprocessLtreeTableParams,
z.object({
table: z.string().describe('Table name'),
column: z.string().describe('ltree column name'),
indexName: z.string().optional().describe('Custom index name (auto-generated if omitted)'),
schema: z.string().optional().describe('Schema name (default: public)')
})
);
// =============================================================================
// pgcrypto Schemas
// =============================================================================
/**
* Schema for hashing data with digest().
*/
export const PgcryptoHashSchema = z.object({
data: z.string().describe('Data to hash'),
algorithm: z.enum(['md5', 'sha1', 'sha224', 'sha256', 'sha384', 'sha512'])
.describe('Hash algorithm'),
encoding: z.enum(['hex', 'base64']).optional()
.describe('Output encoding (default: hex)')
});
/**
* Schema for HMAC authentication.
*/
export const PgcryptoHmacSchema = z.object({
data: z.string().describe('Data to authenticate'),
key: z.string().describe('Secret key for HMAC'),
algorithm: z.enum(['md5', 'sha1', 'sha224', 'sha256', 'sha384', 'sha512'])
.describe('Hash algorithm'),
encoding: z.enum(['hex', 'base64']).optional()
.describe('Output encoding (default: hex)')
});
/**
* Schema for PGP symmetric encryption.
* Accepts 'key' as alias for 'password'.
*
* Uses base schema for MCP exposure and transform schema for validation.
*/
export const PgcryptoEncryptSchemaBase = z.object({
data: z.string().describe('Data to encrypt'),
password: z.string().optional().describe('Encryption password'),
key: z.string().optional().describe('Alias for password'),
options: z.string().optional()
.describe('PGP options (e.g., "compress-algo=1, cipher-algo=aes256")')
});
export const PgcryptoEncryptSchema = PgcryptoEncryptSchemaBase.transform((data) => {
// Handle alias: key -> password
const resolvedPassword = data.password ?? data.key;
return {
...data,
password: resolvedPassword
};
}).refine((data) => data.password !== undefined, {
message: 'password (or key alias) is required'
});
/**
* Schema for PGP symmetric decryption.
* Accepts 'data' as alias for 'encryptedData', 'key' as alias for 'password'.
*
* Uses base schema for MCP exposure and transform schema for validation.
*/
export const PgcryptoDecryptSchemaBase = z.object({
encryptedData: z.string().optional().describe('Encrypted data (base64 from encrypt)'),
data: z.string().optional().describe('Alias for encryptedData'),
password: z.string().optional().describe('Decryption password'),
key: z.string().optional().describe('Alias for password')
});
export const PgcryptoDecryptSchema = PgcryptoDecryptSchemaBase.transform((data) => {
// Handle aliases
const resolvedEncryptedData = data.encryptedData ?? data.data;
const resolvedPassword = data.password ?? data.key;
return {
encryptedData: resolvedEncryptedData,
password: resolvedPassword
};
}).refine((data) => data.encryptedData !== undefined, {
message: 'encryptedData (or data alias) is required'
}).refine((data) => data.password !== undefined, {
message: 'password (or key alias) is required'
});
/**
* Schema for generating random bytes.
*/
export const PgcryptoRandomBytesSchema = z.object({
length: z.number().min(1).max(1024)
.describe('Number of random bytes to generate (1-1024)'),
encoding: z.enum(['hex', 'base64']).optional()
.describe('Output encoding (default: hex)')
});
/**
* Schema for generating password salt.
*/
export const PgcryptoGenSaltSchema = z.object({
type: z.enum(['bf', 'md5', 'xdes', 'des'])
.describe('Salt type: bf (bcrypt, recommended), md5, xdes, or des'),
iterations: z.number().optional()
.describe('Iteration count (for bf: 4-31, for xdes: odd 1-16777215)')
});
/**
* Schema for password hashing with crypt().
*/
export const PgcryptoCryptSchema = z.object({
password: z.string().describe('Password to hash or verify'),
salt: z.string().describe('Salt from gen_salt() or stored hash for verification')
});
