1use serde_json::{Value, json};
7
8use crate::registry::{ToolName, ToolProfile};
9
10#[derive(Debug, Clone)]
11pub struct ToolSpec {
12 pub name: ToolName,
13 pub description: &'static str,
14 pub input_schema: Value,
15}
16
17pub fn tools_for_profile(profile: ToolProfile) -> Vec<ToolSpec> {
19 let names = ToolName::for_profile(profile);
20 active_tools()
21 .into_iter()
22 .filter(|spec| names.contains(&spec.name))
23 .collect()
24}
25
26pub fn generate_mermaid_erd_for_object(obj: &serde_json::Map<String, Value>) -> Option<String> {
28 let ref_name = obj.get("ref").and_then(|v| v.as_str()).unwrap_or("table");
29 let safe_table_name = ref_name.replace(['.', '-'], "_");
30 let mut diagram = String::from("erDiagram\n");
31 diagram.push_str(&format!(" {safe_table_name} {{\n"));
32 if let Some(columns) = obj.get("columns").and_then(|v| v.as_array()) {
33 for col in columns {
34 let name = col.get("name").and_then(|v| v.as_str()).unwrap_or("col");
35 let data_type = col.get("type").and_then(|v| v.as_str()).unwrap_or("string");
36 let pk = col.get("is_pk").and_then(|v| v.as_bool()).unwrap_or(false);
37 let fk = col.get("is_fk").and_then(|v| v.as_bool()).unwrap_or(false);
38 let key_str = match (pk, fk) {
39 (true, true) => " PK,FK",
40 (true, false) => " PK",
41 (false, true) => " FK",
42 _ => "",
43 };
44 diagram.push_str(&format!(
45 " {} {}{}\n",
46 data_type.replace(' ', "_"),
47 name,
48 key_str
49 ));
50 }
51 }
52 diagram.push_str(" }\n");
53 Some(diagram)
54}
55
56pub fn generate_mermaid_diagram_for_path(path_val: &Value) -> Option<String> {
58 let edges = path_val
59 .as_array()
60 .or_else(|| path_val.get("path").and_then(|v| v.as_array()))?;
61 if edges.is_empty() {
62 return None;
63 }
64 let mut diagram = String::from("erDiagram\n");
65 for edge in edges {
66 let from = edge
67 .get("from")
68 .and_then(|v| v.as_str())
69 .unwrap_or("A")
70 .replace(['.', '-'], "_");
71 let to = edge
72 .get("to")
73 .and_then(|v| v.as_str())
74 .unwrap_or("B")
75 .replace(['.', '-'], "_");
76 let from_col = edge.get("from_col").and_then(|v| v.as_str()).unwrap_or("");
77 let to_col = edge.get("to_col").and_then(|v| v.as_str()).unwrap_or("");
78 diagram.push_str(&format!(
79 " {from} }}|--|| {to} : \"{from_col} -> {to_col}\"\n"
80 ));
81 }
82 Some(diagram)
83}
84
85pub fn phase2_catalog_tools() -> Vec<ToolSpec> {
87 vec![
88 ToolSpec {
89 name: ToolName::ListConnections,
90 description: "List configured connection profiles (never includes passwords).",
91 input_schema: object_schema(&[]),
92 },
93 ToolSpec {
94 name: ToolName::ListDatabases,
95 description: "List databases available for a connection profile.",
96 input_schema: object_schema(&[(
97 "connectionId",
98 "string",
99 true,
100 "Name of a configured connection profile, as returned by list_connections.",
101 )]),
102 },
103 ToolSpec {
104 name: ToolName::ListSchemas,
105 description: "List non-system schemas in the currently selected database.",
106 input_schema: object_schema(&[]),
107 },
108 ToolSpec {
109 name: ToolName::ListObjects,
110 description: "List objects (tables, views, …) in a schema.",
111 input_schema: object_schema(&[
112 (
113 "schema",
114 "string",
115 false,
116 "Schema name to list, e.g. \"public\". Defaults to all non-system schemas.",
117 ),
118 (
119 "kind",
120 "string",
121 false,
122 "Filter by object kind: \"table\", \"view\", or \"materialized_view\".",
123 ),
124 (
125 "include_partitions",
126 "boolean",
127 false,
128 "When true, list child partition tables individually. Default false groups partitioned parents.",
129 ),
130 (
131 "connectionId",
132 "string",
133 false,
134 "Optional connection profile override — does not change session context.",
135 ),
136 (
137 "database",
138 "string",
139 false,
140 "Database on connectionId. Requires connectionId when set.",
141 ),
142 ]),
143 },
144 ToolSpec {
145 name: ToolName::GetCurrentContext,
146 description: "Return the active profile, database, and access mode.",
147 input_schema: object_schema(&[]),
148 },
149 ToolSpec {
150 name: ToolName::SwitchConnection,
151 description: "Switch the session to another connection profile / database.",
152 input_schema: object_schema(&[
153 (
154 "connectionId",
155 "string",
156 true,
157 "Name of a configured connection profile, as returned by list_connections.",
158 ),
159 (
160 "database",
161 "string",
162 false,
163 "Database name on that connection. Defaults to the profile's configured database.",
164 ),
165 ]),
166 },
167 ToolSpec {
168 name: ToolName::RunSelect,
169 description: "Run a read-only SELECT or WITH query. DML/DDL are rejected. Supports parameterized execution via `params` ($1, $2, …). Results default to 50 rows with total_count and has_more pagination metadata.",
170 input_schema: object_schema(&[
171 (
172 "sql",
173 "string",
174 true,
175 "A single SELECT or WITH statement, schema-qualify table names where possible.",
176 ),
177 (
178 "params",
179 "array",
180 false,
181 "Bound parameters for $1, $2, … in `sql`. JSON values: string, number, boolean, or null.",
182 ),
183 (
184 "limit",
185 "number",
186 false,
187 "Maximum rows to return. Defaults to 50; capped by profile max_rows.",
188 ),
189 (
190 "format",
191 "string",
192 false,
193 "Output format: \"compact\" (default columnar), \"json\", \"markdown\", or \"csv\".",
194 ),
195 (
196 "connectionId",
197 "string",
198 false,
199 "Optional connection profile override — does not change session context.",
200 ),
201 (
202 "database",
203 "string",
204 false,
205 "Database on connectionId. Requires connectionId when set.",
206 ),
207 (
208 "resolve_fks",
209 "boolean",
210 false,
211 "When true, add __resolved suffix columns for foreign-key IDs in the result.",
212 ),
213 (
214 "timeout_ms",
215 "number",
216 false,
217 "Per-query statement timeout in milliseconds; capped by profile statement_timeout_ms.",
218 ),
219 ]),
220 },
221 ToolSpec {
222 name: ToolName::ExplainQuery,
223 description: "Run EXPLAIN (no ANALYZE execute) for a SELECT/WITH query.",
224 input_schema: object_schema(&[
225 (
226 "sql",
227 "string",
228 true,
229 "A single SELECT or WITH statement to explain (not executed).",
230 ),
231 (
232 "connectionId",
233 "string",
234 false,
235 "Optional connection profile override — does not change session context.",
236 ),
237 (
238 "database",
239 "string",
240 false,
241 "Database on connectionId. Requires connectionId when set.",
242 ),
243 ]),
244 },
245 ToolSpec {
246 name: ToolName::DiscoverTools,
247 description: "Dynamically discover and inspect specialized MCP database tools by keyword query (e.g., 'locks', 'bloat', 'index') or category ('query', 'dba', 'write'). Use this when you need specialized tools beyond the core surface.",
248 input_schema: object_schema(&[
249 (
250 "query",
251 "string",
252 false,
253 "Free-text keyword to search tool names/descriptions, e.g. \"locks\" or \"bloat\".",
254 ),
255 (
256 "category",
257 "string",
258 false,
259 "Restrict to a tool category: \"query\", \"dba\", \"meta\", or \"write\".",
260 ),
261 ]),
262 },
263 ToolSpec {
264 name: ToolName::RunDoctor,
265 description: "Run diagnostic health checks on active database connection, permissions, session guards, and index status.",
266 input_schema: object_schema(&[]),
267 },
268 ToolSpec {
269 name: ToolName::SetupConnection,
270 description: "Automatically detect or configure a database connection. Scans environment variables, workspace files, and local settings, eliciting missing credentials when supported.",
271 input_schema: object_schema(&[
272 ("name", "string", false, "Profile name to assign/detect."),
273 (
274 "url",
275 "string",
276 false,
277 "Full postgres:// connection URL; if given, host/port/dbname/user/password are ignored.",
278 ),
279 ("host", "string", false, "Database server hostname."),
280 (
281 "port",
282 "number",
283 false,
284 "Database server port (default 5432).",
285 ),
286 ("dbname", "string", false, "Database name to connect to."),
287 ("user", "string", false, "Database role/username."),
288 (
289 "password",
290 "string",
291 false,
292 "Database role password. Never written to disk in plaintext — stored in the OS keyring, or the call fails with the password_command/password_file alternative.",
293 ),
294 (
295 "sslmode",
296 "string",
297 false,
298 "libpq sslmode value, e.g. \"disable\", \"require\", \"verify-full\".",
299 ),
300 (
301 "interactive",
302 "boolean",
303 false,
304 "Prompt (elicit) for missing credentials instead of failing. Default false.",
305 ),
306 ]),
307 },
308 ToolSpec {
309 name: ToolName::SaveProfile,
310 description: "Save or update a database connection profile in user configuration with atomic backup and dynamic session reload.",
311 input_schema: object_schema(&[
312 ("name", "string", true, "Profile name to save under."),
313 (
314 "url",
315 "string",
316 false,
317 "Full postgres:// connection URL; if given, host/port/dbname/user/password are ignored.",
318 ),
319 ("host", "string", false, "Database server hostname."),
320 (
321 "port",
322 "number",
323 false,
324 "Database server port (default 5432).",
325 ),
326 ("dbname", "string", false, "Database name to connect to."),
327 ("user", "string", false, "Database role/username."),
328 (
329 "password",
330 "string",
331 false,
332 "Database role password. Never written to disk in plaintext — stored in the OS keyring, or the call fails with the password_command/password_file alternative.",
333 ),
334 (
335 "sslmode",
336 "string",
337 false,
338 "libpq sslmode value, e.g. \"disable\", \"require\", \"verify-full\".",
339 ),
340 (
341 "access_mode",
342 "string",
343 false,
344 "Session access mode: \"read\", \"write\", or \"admin\". Setting \"write\" or \"admin\" requires confirm_elevated_access: true, or the call is rejected.",
345 ),
346 (
347 "confirm_elevated_access",
348 "boolean",
349 false,
350 "Required (must be true) when access_mode is \"write\" or \"admin\" — explicit opt-in for a privilege escalation. No effect when access_mode is omitted or \"read\".",
351 ),
352 (
353 "max_rows",
354 "number",
355 false,
356 "Row cap applied to run_select/export_query for this profile.",
357 ),
358 ]),
359 },
360 ToolSpec {
361 name: ToolName::TestProfile,
362 description: "Test a database connection profile or inline parameters and return server version, superuser status, and round-trip latency.",
363 input_schema: object_schema(&[
364 (
365 "name",
366 "string",
367 false,
368 "Existing profile name to test. Omit to test inline parameters instead.",
369 ),
370 (
371 "url",
372 "string",
373 false,
374 "Full postgres:// connection URL to test inline (alternative to name).",
375 ),
376 (
377 "host",
378 "string",
379 false,
380 "Database server hostname (inline test).",
381 ),
382 (
383 "port",
384 "number",
385 false,
386 "Database server port (inline test).",
387 ),
388 ("dbname", "string", false, "Database name (inline test)."),
389 (
390 "user",
391 "string",
392 false,
393 "Database role/username (inline test).",
394 ),
395 (
396 "password",
397 "string",
398 false,
399 "Database role password (inline test).",
400 ),
401 (
402 "sslmode",
403 "string",
404 false,
405 "libpq sslmode value (inline test), e.g. \"require\".",
406 ),
407 ]),
408 },
409 ToolSpec {
410 name: ToolName::ExportProfile,
411 description: "Export a secret-sanitized TOML configuration for team sharing (.nexql/config.toml) with all passwords and credentials stripped.",
412 input_schema: object_schema(&[(
413 "format",
414 "string",
415 false,
416 "Output format, currently only \"toml\" is supported (default).",
417 )]),
418 },
419 ToolSpec {
420 name: ToolName::ImportProfile,
421 description: "Import a team configuration file (.nexql/config.toml) or TOML content into local user configuration.",
422 input_schema: object_schema(&[
423 (
424 "content",
425 "string",
426 false,
427 "Raw TOML content to import. Provide this or `path`, not both.",
428 ),
429 (
430 "path",
431 "string",
432 false,
433 "Filesystem path to a .nexql/config.toml file to import.",
434 ),
435 ]),
436 },
437 ]
438}
439
440pub fn phase3_index_tools() -> Vec<ToolSpec> {
442 vec![
443 ToolSpec {
444 name: ToolName::ResolveTarget,
445 description: "Autonomously find which connection/database matches a user's hint (a database name, environment, host fragment) and/or an object hint (a table/view name), searching across ALL configured connections and their indexed schemas. Call this FIRST whenever the request references a database, environment, or object that is not the current session context — before search_schema, before list_connections. When the match is unambiguous it switches the session context automatically and returns the resolved connection/database; only returns `ambiguous: true` with a candidate list when multiple equally-plausible matches exist, in which case surface those candidates to the user rather than guessing.",
446 input_schema: object_schema(&[
447 (
448 "hint",
449 "string",
450 false,
451 "Free-text hint about the target connection: database name, environment, or host fragment.",
452 ),
453 (
454 "objectHint",
455 "string",
456 false,
457 "Free-text hint about a table/view name expected to live in the target database.",
458 ),
459 ]),
460 },
461 ToolSpec {
462 name: ToolName::Orient,
463 description: "One-call schema bootstrap digest: tables (columns, PK, row estimate), FK join edges (declared vs inferred), enum-like low-cardinality text columns, and degradation notes. Call this FIRST on an unfamiliar database — before search_schema, describe_object, or get_join_path — to build context in a single low-token round trip instead of many.",
464 input_schema: object_schema(&[(
465 "focus",
466 "string",
467 false,
468 "Substring to filter tables/joins by ref (e.g. \"orders\"). Omit to summarize the whole indexed schema.",
469 )]),
470 },
471 ToolSpec {
472 name: ToolName::InspectOrSearch,
473 description: "Composite schema discovery: search by keywords and return matching objects with columns, keys, and row estimates in one call — replaces search_schema → describe_object chains.",
474 input_schema: object_schema(&[
475 (
476 "query",
477 "string",
478 true,
479 "Natural-language or keyword search, e.g. \"cash card\".",
480 ),
481 (
482 "include_columns",
483 "boolean",
484 false,
485 "Include per-column definitions in each match. Default true.",
486 ),
487 (
488 "limit_objects",
489 "number",
490 false,
491 "Maximum matching objects to return. Default 3.",
492 ),
493 ]),
494 },
495 ToolSpec {
496 name: ToolName::SearchAllDatabases,
497 description: "Cross-database schema search: find which connection/database owns an entity across all configured profiles and indexed databases.",
498 input_schema: object_schema(&[
499 (
500 "query",
501 "string",
502 true,
503 "Table/view name or keyword to search across all connections and databases.",
504 ),
505 (
506 "limit_per_database",
507 "number",
508 false,
509 "Max hits per connection/database pair. Default 3.",
510 ),
511 (
512 "limit_connections",
513 "number",
514 false,
515 "Max connection/database pairs to search. Default 20.",
516 ),
517 ]),
518 },
519 ToolSpec {
520 name: ToolName::SearchSchema,
521 description: "Search the live, auto-indexed database schema using natural language or keywords to find tables, views, materialized views, and functions matching the query. Call this FIRST before writing any SQL — do not assume a table exists without finding it here.",
522 input_schema: object_schema(&[(
523 "query",
524 "string",
525 true,
526 "Natural-language or keyword search, e.g. \"customer email\".",
527 )]),
528 },
529 ToolSpec {
530 name: ToolName::DescribeObject,
531 description: "Get structural details of a specific database object (table, view, or materialized view) including columns, data types, constraints, and indexes.",
532 input_schema: object_schema(&[
533 (
534 "ref",
535 "string",
536 true,
537 "Object reference. Prefer schema-qualified form \"schema.name\" (e.g. \"public.customers\"); a bare name is resolved if unambiguous.",
538 ),
539 (
540 "resolve_refs",
541 "boolean",
542 false,
543 "When true, attach enum values and resolved FK label samples on columns.",
544 ),
545 (
546 "resolve_refs_limit",
547 "number",
548 false,
549 "Max distinct FK values to resolve per column when resolve_refs is true. Default 20.",
550 ),
551 (
552 "connectionId",
553 "string",
554 false,
555 "Optional connection profile override — does not change session context.",
556 ),
557 (
558 "database",
559 "string",
560 false,
561 "Database on connectionId. Requires connectionId when set.",
562 ),
563 ]),
564 },
565 ToolSpec {
566 name: ToolName::GetJoinPath,
567 description: "Find the shortest path of join relationships and foreign keys between two database tables.",
568 input_schema: object_schema(&[
569 (
570 "a",
571 "string",
572 true,
573 "Source table reference. Prefer schema-qualified form \"schema.name\" (e.g. \"public.orders\"); a bare name is resolved if unambiguous.",
574 ),
575 (
576 "b",
577 "string",
578 true,
579 "Target table reference. Prefer schema-qualified form \"schema.name\" (e.g. \"public.customers\"); a bare name is resolved if unambiguous.",
580 ),
581 ]),
582 },
583 ToolSpec {
584 name: ToolName::SampleValues,
585 description: "Retrieve a list of sample values from a specific table column to inspect its contents. Only works on read-only SELECT queries.",
586 input_schema: object_schema(&[
587 (
588 "ref",
589 "string",
590 true,
591 "Table/view reference. Prefer schema-qualified form \"schema.name\" (e.g. \"public.orders\"); a bare name is resolved if unambiguous.",
592 ),
593 (
594 "col",
595 "string",
596 true,
597 "Column name within `ref` to sample values from.",
598 ),
599 ]),
600 },
601 ToolSpec {
602 name: ToolName::RebuildIndex,
603 description: "Rebuild the schema index for the active database connection.",
604 input_schema: object_schema(&[(
605 "depth",
606 "string",
607 false,
608 "Index scope: \"shallow\" (structure only) or \"full\" (structure + sample values). Default \"full\".",
609 )]),
610 },
611 ToolSpec {
612 name: ToolName::RefreshIndex,
613 description: "Refresh the schema index for the active database connection using previous build scope.",
614 input_schema: object_schema(&[]),
615 },
616 ]
617}
618
619pub fn phase4_tools() -> Vec<ToolSpec> {
621 vec![
622 ToolSpec {
623 name: ToolName::GetDdl,
624 description: "Get the DDL / definition of a database object. Views, materialized views, functions, and indexes return their CREATE statement; tables return structured DDL (columns, constraints, indexes).",
625 input_schema: object_schema(&[
626 (
627 "ref",
628 "string",
629 true,
630 "Object reference. Prefer schema-qualified form \"schema.name\" (e.g. \"public.orders\"); a bare name is resolved if unambiguous.",
631 ),
632 (
633 "kind",
634 "string",
635 false,
636 "Object kind hint: \"table\", \"view\", \"materialized_view\", \"function\", or \"index\". Auto-detected if omitted.",
637 ),
638 (
639 "connectionId",
640 "string",
641 false,
642 "Optional connection profile override — does not change session context.",
643 ),
644 (
645 "database",
646 "string",
647 false,
648 "Database on connectionId. Requires connectionId when set.",
649 ),
650 ]),
651 },
652 ToolSpec {
653 name: ToolName::TableStats,
654 description: "Get size, row-count, activity (scans, inserts/updates/deletes, dead tuples, vacuum/analyze times) and per-column statistics for a specific table.",
655 input_schema: object_schema(&[
656 (
657 "ref",
658 "string",
659 true,
660 "Table reference. Prefer schema-qualified form \"schema.name\" (e.g. \"public.orders\"); a bare name is resolved if unambiguous.",
661 ),
662 (
663 "connectionId",
664 "string",
665 false,
666 "Optional connection profile override — does not change session context.",
667 ),
668 (
669 "database",
670 "string",
671 false,
672 "Database on connectionId. Requires connectionId when set.",
673 ),
674 ]),
675 },
676 ToolSpec {
677 name: ToolName::IndexUsage,
678 description: "Get index usage statistics (scan counts, size, definition, type) for a specific table's indexes. Useful for finding unused or missing indexes.",
679 input_schema: object_schema(&[(
680 "ref",
681 "string",
682 true,
683 "Table reference. Prefer schema-qualified form \"schema.name\" (e.g. \"public.orders\"); a bare name is resolved if unambiguous.",
684 )]),
685 },
686 ToolSpec {
687 name: ToolName::ListRunningQueries,
688 description: "List currently executing (non-idle) queries in the connected database with pid, user, state, wait events, and duration.",
689 input_schema: object_schema(&[]),
690 },
691 ToolSpec {
692 name: ToolName::FindBlockingLocks,
693 description: "Find lock contention: which queries are blocked waiting on locks and which pids/queries are blocking them.",
694 input_schema: object_schema(&[]),
695 },
696 ToolSpec {
697 name: ToolName::SlowQueries,
698 description: "List the slowest statements by mean execution time from pg_stat_statements (requires the extension; returns a hint if not installed).",
699 input_schema: object_schema(&[(
700 "limit",
701 "number",
702 false,
703 "Maximum number of statements to return. Default 10.",
704 )]),
705 },
706 ToolSpec {
707 name: ToolName::DbHealthCheck,
708 description: "Run a database health overview: size/connection stats, cache hit ratio, tables with dead tuples needing vacuum, active connections, and blocking-lock count. Sections that fail are reported individually; partial results are still returned.",
709 input_schema: object_schema(&[]),
710 },
711 ToolSpec {
712 name: ToolName::GetIndexStatus,
713 description: "Return schema-index status for the active connection/database: indexed_at, fingerprint, object counts, and optional live fingerprint drift. Returns status:\"missing\" (not an error) if no index has been built yet.",
714 input_schema: object_schema(&[]),
715 },
716 ToolSpec {
717 name: ToolName::ListExtensions,
718 description: "List installed PostgreSQL extensions (name, version, schema).",
719 input_schema: object_schema(&[]),
720 },
721 ToolSpec {
722 name: ToolName::ServerSettings,
723 description: "Return key PostgreSQL server settings from pg_settings (memory, connections, timeouts, autovacuum, version).",
724 input_schema: object_schema(&[]),
725 },
726 ToolSpec {
727 name: ToolName::SuggestIndexes,
728 description: "Suggest indexes from high sequential-scan tables, unindexed FK columns, and optional pg_stat_statements / EXPLAIN plan heuristics. Pass sql to analyze a specific query plan.",
729 input_schema: object_schema(&[
730 (
731 "limit",
732 "number",
733 false,
734 "Maximum number of suggestions to return. Default 10.",
735 ),
736 (
737 "sql",
738 "string",
739 false,
740 "Optional SELECT/WITH statement whose plan should inform the suggestions.",
741 ),
742 ]),
743 },
744 ToolSpec {
745 name: ToolName::FindUnusedIndexes,
746 description: "List indexes with idx_scan = 0 (never used since stats reset), excluding primary keys, unique indexes, and constraint-backed indexes.",
747 input_schema: object_schema(&[(
748 "limit",
749 "number",
750 false,
751 "Maximum number of indexes to return. Default 10.",
752 )]),
753 },
754 ToolSpec {
755 name: ToolName::BloatReport,
756 description: "Approximate table bloat via dead-tuple ratio from pg_stat_user_tables (simplified estimate — not physical page bloat). Tables with >1000 dead tuples, ordered by bloat %.",
757 input_schema: object_schema(&[(
758 "limit",
759 "number",
760 false,
761 "Maximum number of tables to return. Default 10.",
762 )]),
763 },
764 ToolSpec {
765 name: ToolName::FindMissingFks,
766 description: "Find likely missing foreign keys: prefers schema-index join-graph inferred edges; falls back to catalog naming (*_id columns without an FK matching a PK).",
767 input_schema: object_schema(&[(
768 "limit",
769 "number",
770 false,
771 "Maximum number of candidates to return. Default 20.",
772 )]),
773 },
774 ]
775}
776
777pub fn phase4b_tools() -> Vec<ToolSpec> {
779 vec![
780 ToolSpec {
781 name: ToolName::ExportQuery,
782 description: "Run a read-only SELECT/WITH and format results as CSV, JSON, or SQL INSERT statements. Honors max-row / max-char caps. For sqlinsert, pass table as schema.name.",
783 input_schema: object_schema(&[
784 (
785 "sql",
786 "string",
787 true,
788 "A single SELECT or WITH statement to run and export.",
789 ),
790 (
791 "format",
792 "string",
793 false,
794 "Output format: \"csv\", \"json\", or \"sqlinsert\". Default \"csv\".",
795 ),
796 (
797 "table",
798 "string",
799 false,
800 "Target table as \"schema.name\", required when format=\"sqlinsert\".",
801 ),
802 ]),
803 },
804 ToolSpec {
805 name: ToolName::ListRoles,
806 description: "List PostgreSQL roles (attributes). Pass role to get memberships and table privileges for one role.",
807 input_schema: object_schema(&[(
808 "role",
809 "string",
810 false,
811 "Specific role name to inspect memberships/privileges for. Omit to list all roles.",
812 )]),
813 },
814 ToolSpec {
815 name: ToolName::DbDashboard,
816 description: "One-shot live metrics bundle: DB size/owner, connection-state breakdown, top tables by size, object counts, active queries, and blocking locks. Soft-fails per section.",
817 input_schema: object_schema(&[]),
818 },
819 ToolSpec {
820 name: ToolName::DeepPlanAnalysis,
821 description: "Run EXPLAIN (ANALYZE by default) and return parsed plan metrics (scan counts, bottlenecks, buffer stats) plus severity-graded findings: estimate skew, expensive function/CTE/subquery nodes, and recommendations. Set analyze=false for plan-only (no execution). The single query-plan-analysis tool — covers what separate explain_analyze/analyze_query_plan tools used to.",
822 input_schema: object_schema(&[
823 (
824 "sql",
825 "string",
826 true,
827 "A single SELECT or WITH statement to analyze.",
828 ),
829 (
830 "analyze",
831 "boolean",
832 false,
833 "If false, use plan-only estimates without executing the query. Default true.",
834 ),
835 ]),
836 },
837 ToolSpec {
838 name: ToolName::SchemaDiff,
839 description: "Compare two schemas in the current database (or sourceSchema vs targetSchema). Returns structured table/column/constraint/index diffs. Read-only — does not apply changes.",
840 input_schema: object_schema(&[
841 (
842 "sourceSchema",
843 "string",
844 true,
845 "Name of the schema to treat as the baseline, e.g. \"public\".",
846 ),
847 (
848 "targetSchema",
849 "string",
850 true,
851 "Name of the schema to diff against the baseline, e.g. \"staging\".",
852 ),
853 ]),
854 },
855 ToolSpec {
856 name: ToolName::GenerateMigration,
857 description: "Emit migration SQL to evolve sourceSchema toward targetSchema (from a live schema_diff). Read-only — returns SQL text, never executes it. Destructive drops are commented out.",
858 input_schema: object_schema(&[
859 (
860 "sourceSchema",
861 "string",
862 true,
863 "Name of the schema to migrate from, e.g. \"public\".",
864 ),
865 (
866 "targetSchema",
867 "string",
868 true,
869 "Name of the schema to migrate towards, e.g. \"staging\".",
870 ),
871 ]),
872 },
873 ToolSpec {
874 name: ToolName::AutoTuneQuery,
875 description: "Autonomous query tuner: executes EXPLAIN ANALYZE, checks table statistics, evaluates missing indexes, and outputs step-by-step performance tuning recommendations.",
876 input_schema: object_schema(&[(
877 "sql",
878 "string",
879 true,
880 "A single SELECT or WITH statement to tune. It executes for real.",
881 )]),
882 },
883 ToolSpec {
884 name: ToolName::CheckDdlSafety,
885 description: "Safety guard for migration DDL: inspects SQL for dangerous exclusive locks (e.g. non-concurrent index builds, column drops, table rewrites) and outputs risk scores and safe zero-downtime alternatives.",
886 input_schema: object_schema(&[(
887 "ddl",
888 "string",
889 true,
890 "One or more DDL statements to inspect for locking risk. Not executed.",
891 )]),
892 },
893 ]
894}
895
896pub fn phase9_write_tools() -> Vec<ToolSpec> {
898 vec![
899 ToolSpec {
900 name: ToolName::ExecuteSql,
901 description: "Execute DML (and DDL in admin mode) inside an explicit transaction. Set dry_run=true to roll back after execution. Errors always roll back.",
902 input_schema: object_schema(&[
903 (
904 "sql",
905 "string",
906 true,
907 "A single DML statement (INSERT/UPDATE/DELETE), or DDL if the session is in admin mode.",
908 ),
909 (
910 "dry_run",
911 "boolean",
912 false,
913 "If true, execute then roll back so no change persists. Default false.",
914 ),
915 (
916 "include_diff",
917 "boolean",
918 false,
919 "When true (default when dry_run), capture before/after row snapshots for UPDATE/DELETE.",
920 ),
921 ]),
922 },
923 ToolSpec {
924 name: ToolName::EditRow,
925 description: "Structured insert, update, or delete by primary key. The server builds parameterized SQL — pass table (schema.name), action, values, and pk for update/delete.",
926 input_schema: object_schema(&[
927 ("table", "string", true, "Target table as \"schema.name\"."),
928 (
929 "action",
930 "string",
931 true,
932 "Operation to perform: \"insert\", \"update\", or \"delete\".",
933 ),
934 (
935 "values",
936 "object",
937 false,
938 "Column name/value pairs to insert or update. Required for insert/update.",
939 ),
940 (
941 "pk",
942 "object",
943 false,
944 "Primary-key column name/value pairs identifying the row. Required for update/delete.",
945 ),
946 (
947 "dry_run",
948 "boolean",
949 false,
950 "If true, execute then roll back so no change persists. Default false.",
951 ),
952 (
953 "include_diff",
954 "boolean",
955 false,
956 "When true (default when dry_run), capture before/after row snapshots for update/delete.",
957 ),
958 ]),
959 },
960 ToolSpec {
961 name: ToolName::ImportData,
962 description: "Batch INSERT rows from a JSON array of objects into a table. Optional columns array fixes column order; otherwise keys from the first row are used.",
963 input_schema: object_schema(&[
964 ("table", "string", true, "Target table as \"schema.name\"."),
965 (
966 "rows",
967 "array",
968 true,
969 "Array of row objects, each mapping column name to value.",
970 ),
971 (
972 "columns",
973 "array",
974 false,
975 "Explicit column order to insert with. Defaults to the keys of the first row.",
976 ),
977 ]),
978 },
979 ToolSpec {
980 name: ToolName::ApplyDdl,
981 description: "Apply a DDL statement (CREATE, ALTER, DROP, TRUNCATE, …) in admin mode inside a transaction. Set dry_run=true to roll back.",
982 input_schema: object_schema(&[
983 ("sql", "string", true, "A single DDL statement to apply."),
984 (
985 "dry_run",
986 "boolean",
987 false,
988 "If true, execute then roll back so no change persists. Default false.",
989 ),
990 ]),
991 },
992 ToolSpec {
993 name: ToolName::CreateIndexConcurrently,
994 description: "Run CREATE INDEX CONCURRENTLY outside a transaction (non-blocking index build). Admin mode only.",
995 input_schema: object_schema(&[(
996 "sql",
997 "string",
998 true,
999 "A single CREATE INDEX CONCURRENTLY statement.",
1000 )]),
1001 },
1002 ToolSpec {
1003 name: ToolName::RunMaintenance,
1004 description: "Run VACUUM, ANALYZE, or REINDEX outside a transaction. Admin mode only. Optional table (schema.name); vacuum supports full=true.",
1005 input_schema: object_schema(&[
1006 (
1007 "action",
1008 "string",
1009 true,
1010 "Maintenance action: \"vacuum\", \"analyze\", or \"reindex\".",
1011 ),
1012 (
1013 "table",
1014 "string",
1015 false,
1016 "Target table as \"schema.name\". Omit to run against the whole database where supported.",
1017 ),
1018 (
1019 "full",
1020 "boolean",
1021 false,
1022 "For action=\"vacuum\", run VACUUM FULL (rewrites the table, takes an exclusive lock). Default false.",
1023 ),
1024 ]),
1025 },
1026 ToolSpec {
1027 name: ToolName::TerminateQuery,
1028 description: "Cancel (pg_cancel_backend) or force-terminate (pg_terminate_backend) a backend by pid. Admin mode only. Refuses superuser targets and the current session.",
1029 input_schema: object_schema(&[
1030 (
1031 "pid",
1032 "number",
1033 true,
1034 "Backend process id to cancel/terminate.",
1035 ),
1036 (
1037 "force",
1038 "boolean",
1039 false,
1040 "If true, force-terminate the backend (pg_terminate_backend) instead of a soft cancel. Default false.",
1041 ),
1042 ]),
1043 },
1044 ]
1045}
1046
1047pub fn active_tools() -> Vec<ToolSpec> {
1049 let mut specs = phase2_catalog_tools();
1050 specs.extend(phase3_index_tools());
1051 specs.extend(phase4_tools());
1052 specs.extend(phase4b_tools());
1053 specs.extend(phase9_write_tools());
1054 specs
1055}
1056
1057fn array_items_schema(prop_name: &str) -> Value {
1060 match prop_name {
1061 "columns" => json!({ "type": "string" }),
1062 "rows" => json!({
1063 "type": "object",
1064 "additionalProperties": true
1065 }),
1066 "params" => json!({
1067 "oneOf": [
1068 { "type": "string" },
1069 { "type": "number" },
1070 { "type": "boolean" },
1071 { "type": "null" }
1072 ]
1073 }),
1074 _ => json!({ "type": "string" }),
1076 }
1077}
1078
1079fn object_schema(props: &[(&str, &str, bool, &str)]) -> Value {
1084 let mut properties = serde_json::Map::new();
1085 let mut required = Vec::new();
1086 for (name, ty, req, description) in props {
1087 let mut prop_val = match *ty {
1088 "array" => json!({
1089 "type": "array",
1090 "items": array_items_schema(name),
1091 }),
1092 _ => json!({ "type": *ty }),
1093 };
1094 prop_val["description"] = json!(*description);
1095 properties.insert((*name).into(), prop_val);
1096 if *req {
1097 required.push(json!(*name));
1098 }
1099 }
1100 json!({
1101 "type": "object",
1102 "properties": properties,
1103 "required": required,
1104 "additionalProperties": false
1105 })
1106}
1107
1108#[cfg(test)]
1109mod tests {
1110 use super::*;
1111 use crate::registry::ToolName;
1112
1113 #[test]
1114 fn active_tools_lists_fifty_four() {
1115 let specs = active_tools();
1116 assert_eq!(specs.len(), 54);
1117 assert_eq!(specs.len(), ToolName::ACTIVE.len());
1118 for (spec, name) in specs.iter().zip(ToolName::ACTIVE.iter()) {
1119 assert_eq!(spec.name, *name);
1120 }
1121 }
1122
1123 #[test]
1124 fn phase9_write_tools_count() {
1125 assert_eq!(phase9_write_tools().len(), ToolName::PHASE9.len());
1126 }
1127
1128 #[test]
1129 fn array_properties_have_items() {
1130 fn items_schema_is_valid(items: &Value) -> bool {
1131 items.get("type").is_some()
1132 || items.get("oneOf").is_some()
1133 || items.get("anyOf").is_some()
1134 || items.get("allOf").is_some()
1135 }
1136
1137 for tool in active_tools() {
1138 if let Some(props) = tool
1139 .input_schema
1140 .get("properties")
1141 .and_then(|p| p.as_object())
1142 {
1143 for (prop_name, prop_val) in props {
1144 if prop_val.get("type").and_then(|t| t.as_str()) == Some("array") {
1145 let items = prop_val.get("items").unwrap_or_else(|| {
1146 panic!(
1147 "Tool '{}' parameter '{}' is array type but missing 'items'",
1148 tool.name.as_str(),
1149 prop_name
1150 )
1151 });
1152 assert!(
1153 items_schema_is_valid(items),
1154 "Tool '{}' parameter '{}' has array items without a concrete schema",
1155 tool.name.as_str(),
1156 prop_name
1157 );
1158 }
1159 }
1160 }
1161 }
1162 }
1163
1164 #[test]
1165 fn import_data_rows_and_columns_have_typed_items() {
1166 let spec = active_tools()
1167 .into_iter()
1168 .find(|t| t.name == ToolName::ImportData)
1169 .expect("import_data tool");
1170 let props = spec
1171 .input_schema
1172 .get("properties")
1173 .and_then(|p| p.as_object())
1174 .expect("import_data properties");
1175 let rows = &props["rows"];
1176 assert_eq!(rows["type"], "array");
1177 assert_eq!(rows["items"]["type"], "object");
1178 assert_eq!(rows["items"]["additionalProperties"], true);
1179 let columns = &props["columns"];
1180 assert_eq!(columns["type"], "array");
1181 assert_eq!(columns["items"]["type"], "string");
1182 }
1183
1184 #[test]
1185 fn profile_tools_filtering() {
1186 let query_specs = tools_for_profile(ToolProfile::Query);
1187 assert_eq!(query_specs.len(), 21);
1188
1189 let dba_specs = tools_for_profile(ToolProfile::Dba);
1190 assert_eq!(dba_specs.len(), 26);
1191
1192 let meta_specs = tools_for_profile(ToolProfile::Meta);
1193 assert_eq!(meta_specs.len(), 13);
1194
1195 let full_specs = tools_for_profile(ToolProfile::Full);
1196 assert_eq!(full_specs.len(), 54);
1197 }
1198
1199 #[test]
1202 fn all_tools_have_descriptions_and_reject_unknown_properties() {
1203 for tool in active_tools() {
1204 assert_eq!(
1205 tool.input_schema.get("additionalProperties"),
1206 Some(&json!(false)),
1207 "Tool '{}' input_schema must set additionalProperties: false",
1208 tool.name.as_str()
1209 );
1210 if let Some(props) = tool
1211 .input_schema
1212 .get("properties")
1213 .and_then(|p| p.as_object())
1214 {
1215 for (prop_name, prop_val) in props {
1216 let desc = prop_val.get("description").and_then(|d| d.as_str());
1217 assert!(
1218 desc.is_some_and(|d| !d.is_empty()),
1219 "Tool '{}' parameter '{}' is missing a non-empty description",
1220 tool.name.as_str(),
1221 prop_name
1222 );
1223 }
1224 }
1225 }
1226 }
1227
1228 #[test]
1229 fn generate_mermaid_erd_test() {
1230 let obj = json!({
1231 "ref": "public.users",
1232 "columns": [
1233 { "name": "id", "type": "uuid", "is_pk": true, "is_fk": false },
1234 { "name": "email", "type": "varchar", "is_pk": false, "is_fk": false },
1235 { "name": "org_id", "type": "uuid", "is_pk": false, "is_fk": true }
1236 ]
1237 });
1238 let diagram = generate_mermaid_erd_for_object(obj.as_object().unwrap()).unwrap();
1239 assert!(diagram.contains("erDiagram"));
1240 assert!(diagram.contains("public_users"));
1241 assert!(diagram.contains("uuid id PK"));
1242 assert!(diagram.contains("uuid org_id FK"));
1243 }
1244
1245 #[test]
1246 fn generate_mermaid_diagram_for_path_test() {
1247 let path = json!([
1248 { "from": "public.orders", "to": "public.users", "from_col": "user_id", "to_col": "id" }
1249 ]);
1250 let diagram = generate_mermaid_diagram_for_path(&path).unwrap();
1251 assert!(diagram.contains("erDiagram"));
1252 assert!(diagram.contains("public_orders }|--|| public_users : \"user_id -> id\""));
1253 }
1254}