/**
* postgres-mcp - Backup Tool Schemas
*
* Input validation schemas for backup and export operations.
*/
import { z } from "zod";
/**
* Base schema for MCP visibility (shows all parameters in JSON Schema).
* This schema is used for tool registration so MCP clients can see the parameters.
*/
export const CopyExportSchemaBase = z.object({
query: z.string().optional().describe("SELECT query for data to export"),
sql: z.string().optional().describe("Alias for query parameter"),
table: z
.string()
.optional()
.describe(
"Table name to export (auto-generates SELECT *). Supports 'schema.table' format",
),
schema: z
.string()
.optional()
.describe("Schema name when using table (default: public)"),
format: z
.enum(["csv", "text", "binary"])
.optional()
.describe("Output format (default: csv)"),
header: z.boolean().optional().describe("Include header row (default: true)"),
delimiter: z.string().optional().describe("Field delimiter"),
limit: z
.number()
.int()
.nonnegative()
.optional()
.describe(
"Maximum number of rows to export (default: 500 to prevent large payloads). Use 0 for all rows",
),
});
/** Default limit for copyExport when not specified */
const DEFAULT_EXPORT_LIMIT = 500;
/**
* Transformed schema with alias resolution, table shortcut, and schema.table parsing.
*/
export const CopyExportSchema = CopyExportSchemaBase.transform((input) => {
// Apply alias: sql → query
let query = input.query ?? input.sql;
let conflictWarning: string | undefined;
// Check for conflicting parameters
if (
(input.query !== undefined || input.sql !== undefined) &&
input.table !== undefined
) {
conflictWarning =
"Both query and table parameters provided. Using query parameter (table ignored).";
}
// Resolve effective limit:
// - undefined = use DEFAULT_EXPORT_LIMIT (500)
// - 0 = no limit (export all rows)
// - positive number = user-specified limit
const effectiveLimit =
input.limit === undefined
? DEFAULT_EXPORT_LIMIT
: input.limit === 0
? undefined // 0 means no limit
: input.limit;
// Track whether we used the default limit (handler will check actual row count)
let usedDefaultLimit = false;
// Auto-generate query from table if provided
if ((query === undefined || query === "") && input.table !== undefined) {
// Parse schema.table format (e.g., 'public.users' -> schema='public', table='users')
// If table contains a dot, always parse it as schema.table (embedded schema takes priority)
let tableName = input.table;
let schemaName = input.schema ?? "public";
if (input.table.includes(".")) {
const parts = input.table.split(".");
if (parts.length === 2 && parts[0] && parts[1]) {
schemaName = parts[0];
tableName = parts[1];
}
}
// Build query with LIMIT
query = `SELECT * FROM "${schemaName}"."${tableName}"`;
if (effectiveLimit !== undefined) {
query += ` LIMIT ${String(effectiveLimit)}`;
// Track if we're using the default limit (actual truncation determined in handler)
if (input.limit === undefined) {
usedDefaultLimit = true;
}
}
} else if (query !== undefined && effectiveLimit !== undefined) {
// If a custom query is provided and limit is specified, wrap or append LIMIT
// Only append if query doesn't already have LIMIT
if (!/\bLIMIT\s+\d+\s*$/i.test(query)) {
query += ` LIMIT ${String(effectiveLimit)}`;
// Track if we're using the default limit (actual truncation determined in handler)
if (input.limit === undefined) {
usedDefaultLimit = true;
}
}
}
if (query === undefined || query === "") {
throw new Error("Either query/sql or table parameter is required");
}
return {
...input,
query,
conflictWarning,
usedDefaultLimit,
effectiveLimit,
};
});
export const DumpSchemaSchema = z.object({
table: z.string().optional().describe("Table name"),
schema: z.string().optional().describe("Schema name"),
filename: z
.string()
.optional()
.describe("Output filename (default: backup.dump)"),
});
// ============================================================================
// Output Schemas
// ============================================================================
/**
* pg_dump_table output - DDL for table, sequence, or view
*/
export const DumpTableOutputSchema = z
.object({
ddl: z.string().describe("DDL statement (CREATE TABLE/SEQUENCE/VIEW)"),
type: z
.string()
.optional()
.describe(
"Object type: table, sequence, view, materialized_view, partitioned_table",
),
note: z.string().describe("Usage notes"),
insertStatements: z
.string()
.optional()
.describe("INSERT statements when includeData=true"),
warning: z.string().optional().describe("Warning message"),
})
.loose();
/**
* pg_dump_schema output - pg_dump command
*/
export const DumpSchemaOutputSchema = z
.object({
command: z.string().describe("pg_dump command to run"),
warning: z
.string()
.optional()
.describe("Warning about schema+table combination"),
formatWarning: z
.string()
.optional()
.describe("Warning about .sql extension with custom format"),
notes: z.array(z.string()).describe("Usage notes"),
})
.loose();
/**
* pg_copy_export output - exported data
*/
export const CopyExportOutputSchema = z
.object({
data: z.string().describe("Exported data (CSV or text format)"),
rowCount: z.number().describe("Number of rows exported"),
truncated: z
.boolean()
.optional()
.describe("Whether results were truncated"),
limit: z.number().optional().describe("Limit that was applied"),
note: z.string().optional().describe("Message when no rows returned"),
warning: z
.string()
.optional()
.describe("Warning about parameter conflicts"),
})
.loose();
/**
* pg_copy_import output - COPY FROM command
*/
export const CopyImportOutputSchema = z.object({
command: z.string().describe("COPY FROM command"),
stdinCommand: z.string().describe("COPY FROM STDIN command"),
notes: z.string().describe("Usage notes"),
});
/**
* pg_create_backup_plan output - backup strategy
*/
export const CreateBackupPlanOutputSchema = z.object({
strategy: z.object({
fullBackup: z.object({
command: z.string().describe("pg_dump command with timestamp"),
frequency: z.string().describe("Backup frequency"),
cronSchedule: z.string().describe("Cron schedule expression"),
retention: z.string().describe("Retention policy"),
}),
walArchiving: z.object({
note: z.string().describe("WAL archiving recommendation"),
configChanges: z.array(z.string()).describe("PostgreSQL config changes"),
}),
}),
estimates: z
.object({
databaseSize: z.string().describe("Current database size"),
backupSizeEach: z.string().describe("Estimated size per backup"),
backupsPerDay: z
.number()
.optional()
.describe("Backups per day (for hourly/daily)"),
backupsPerWeek: z
.number()
.optional()
.describe("Backups per week (for weekly)"),
totalStorageNeeded: z.string().describe("Total storage needed"),
})
.loose(),
});
/**
* pg_restore_command output - pg_restore command
*/
export const RestoreCommandOutputSchema = z.object({
command: z.string().describe("pg_restore command"),
warnings: z
.array(z.string())
.optional()
.describe("Warnings about missing parameters"),
notes: z.array(z.string()).describe("Usage notes"),
});
/**
* pg_backup_physical output - pg_basebackup command
*/
export const PhysicalBackupOutputSchema = z.object({
command: z.string().describe("pg_basebackup command"),
notes: z.array(z.string()).describe("Usage notes"),
requirements: z.array(z.string()).describe("PostgreSQL requirements"),
});
/**
* pg_restore_validate output - validation steps
*/
export const RestoreValidateOutputSchema = z
.object({
note: z.string().optional().describe("Default type note"),
validationSteps: z.array(
z
.object({
step: z.number().describe("Step number"),
name: z.string().describe("Step name"),
command: z.string().optional().describe("Command to run"),
commands: z
.array(z.string())
.optional()
.describe("Multiple commands"),
note: z.string().optional().describe("Step note"),
})
.loose(),
),
recommendations: z
.array(z.string())
.describe("Best practice recommendations"),
})
.loose();
/**
* pg_backup_schedule_optimize output - schedule analysis
*/
export const BackupScheduleOptimizeOutputSchema = z.object({
analysis: z.object({
databaseSize: z.unknown().describe("Database size"),
totalChanges: z.number().describe("Total DML changes since stats reset"),
changeVelocity: z.number().describe("Change velocity ratio"),
changeVelocityRatio: z.string().describe("Change velocity as percentage"),
activityByHour: z
.array(
z.object({
hour: z.number().describe("Hour of day"),
connection_count: z.number().describe("Connection count"),
}),
)
.optional()
.describe("Connection activity by hour"),
activityNote: z.string().describe("Activity data caveat"),
}),
recommendation: z.object({
strategy: z.string().describe("Recommended strategy"),
fullBackupFrequency: z.string().describe("Full backup frequency"),
incrementalFrequency: z.string().describe("Incremental/WAL frequency"),
bestTimeForBackup: z.string().describe("Recommended backup time"),
retentionPolicy: z.string().describe("Retention policy"),
}),
commands: z.object({
cronSchedule: z.string().describe("Sample cron schedule"),
walArchive: z.string().describe("WAL archive command"),
}),
});
