Return the MCPg server version, access mode, transport, database connection status, and PostgreSQL wal_level / effective_wal_level (the latter is None on PG ≤ 18 where the GUC doesn't exist; on PG 19+ a divergence between the two indicates a reload is still pending).

Return a high-level summary of what mcpg can do, organised into capability buckets (schema introspection, query execution, vector search, RAG telemetry, audit trail, migrations, time-series, etc.). Call this first when discovering mcpg's surface — it's much more compact than walking the full tool catalogue. Returns an object with headline, version, tool_count, capability_count, and a capabilities list, where each capability has id, name, summary, detail, headline_tools (top 3-6 tools to reach for first), tool_count, and all_tools (the full list in that bucket). Read-only; no database access. Pair with list_tools (MCP-protocol) when you need every tool's full schema.

Return the full registered schema for one MCP tool by name — description, input_schema, output_schema, and which capability bucket it belongs to. Use this when an agent hits a tool error and needs to verify the call shape without re-walking the full describe_self payload (handy when the transport surfaces only tools/call, not tools/list). Returns registered=false plus a did_you_mean suggestion list when the name isn't on this server. Read-only; no database access. Example: describe_tool(name='run_select')

Return the in-process Prometheus-format metrics for this MCPg server. Three series: mcpg_tool_calls_total (counter by tool / status), mcpg_tool_duration_seconds (histogram by tool with sum and count). Useful when the HTTP transport's /metrics endpoint is unreachable (e.g. running over stdio) or to fetch via the MCP protocol itself.

List database schemas, excluding PostgreSQL's own schemas unless include_system is true. Returns a list of objects with name.

Example: list_schemas(include_system=false)

List the tables and views in a schema, flagging partitioned tables and partitions. Returns a list of objects with name, type ('table' or 'view'), partitioned, is_partition.

Example: list_tables(schema='public')

Describe the columns of a table, in ordinal order. Returns a list of objects with name, data_type, nullable, default, and vector_dimension (set only for pgvector vector(N) columns).

Example: describe_table(schema='public', table='users')

List the indexes defined on a table. Returns a list of objects with name, method (btree / gin / gist / brin / hash / spgist / hnsw / ivfflat / …), definition (the CREATE INDEX statement), and partitioned.

Example: list_indexes(schema='public', table='users')

List a table's constraints — primary/foreign keys, unique, check, exclusion. Returns a list of objects with name, type, and definition (the constraint SQL).

Example: list_constraints(schema='public', table='orders')

List foreign keys in a schema, resolved to columns and referenced table. Returns a list of objects with name, from_table, from_columns, to_schema, to_table, to_columns.

Example: list_foreign_keys(schema='public')

List the views and materialized views in a schema, with their definitions. Returns a list of objects with name, materialized (bool), and definition (the SELECT SQL).

List the functions and procedures defined in a schema. Returns a list of objects with name, kind ('function' or 'procedure'), arguments (signature string), returns (return-type string), and language (plpgsql / sql / c / etc.).

List the user-defined triggers on a table. Returns a list of objects with name, function (the called function's qualified name), and definition (the CREATE TRIGGER SQL).

Describe how a table is partitioned (strategy and bounds) and list its partitions. Returns an object with partitioned (bool), strategy ('range' / 'list' / 'hash' or null), and partitions (a list of {name, bounds} for each partition).

List the database roles and their attributes, excluding PostgreSQL's own roles unless include_system is true. Returns a list of objects with name, superuser, create_role, create_db, can_login, replication, bypass_rls, connection_limit, member_of.

List the privileges granted on a table — who may do what to it. Returns a list of objects with grantee, privilege (SELECT / INSERT / UPDATE / DELETE / TRUNCATE / REFERENCES / TRIGGER), grantable (bool — may the grantee regrant), and grantor.

List the Row-Level-Security policies on a table, and whether row security is enabled. Returns an object with rls_enabled (bool) and policies — a list of {name, command (SELECT/INSERT/UPDATE/DELETE/ALL), permissive (bool), roles, using_expression, check_expression}.

List the sequences defined in a schema, with their range, increment, and last value. Returns a list of objects with name, data_type, start_value, min_value, max_value, increment, cycle (bool), last_value.

List the enum types in a schema, with their labels in sort order. Returns a list of objects with name and values (list of label strings, in the order defined).

List the domain types in a schema, with base type, default, and check constraints. Returns a list of objects with name, base_type, nullable, default, and constraints (list of CHECK clauses).

List the standalone composite types in a schema with their attributes. Returns a list of objects with name and attributes (list of {name, data_type} for each field).

List the foreign-data wrappers installed in the database. Returns a list of objects with name, handler (qualified function name), validator, and options (dict of wrapper-specific options).

List the foreign servers defined in the database, with their FDW and options. Returns a list of objects with name, wrapper (foreign-data wrapper name), type, version, and options (dict of server options).

List the foreign tables in a schema, with their server and options. Returns a list of objects with name, server (foreign-server name), and options (dict of per-table options).

List role-to-foreign-server mappings; the catch-all appears as user='public'. Returns a list of objects with user (role name or 'public'), server (foreign-server name), and options (dict of mapping options, e.g. credentials).

List logical-replication publications with the tables and operations they include. Returns a list of objects with name, owner, all_tables (bool), publishes_insert / publishes_update / publishes_delete / publishes_truncate (bools), and tables (list of schema.table strings).

List logical-replication subscriptions; requires superuser to see any rows.

List the extensions installed in the database. Returns a list of objects with name and version.

List every extension available to the database, with whether it is installed. Returns a list of objects with name, default_version, installed_version (null when not installed), and installed (bool).

List every GENERATED ALWAYS AS (...) STORED column in a schema, with its data type, the underlying expression, and whether it's stored or virtual. PostgreSQL today supports only the stored form; the kind field is reported anyway so the response shape is forward-compatible when PG adds virtual columns. Returns a list of objects with schema, table, column, data_type, expression, kind ('stored' today; reserved for 'virtual').

List currently-held and waiting locks, joined with backend state from pg_stat_activity. Ordered by (granted ASC, pid) so waiting locks float to the top. Returns lock type, mode, qualified relation name when applicable, transaction / virtualxid, the application_name + state + wait event of the owning backend, and the first 200 chars of its query. Read-only.

Return (blocked, blocking) backend pairs via pg_blocking_pids. Each row pairs a backend waiting on a Lock with one PID holding the lock that's preventing progress. Cycles are possible (A blocks B, B blocks A); render with care. Read-only.

Walk and reconstruct the lock-wait graph of the database. Detects deadlock cycles, traces linear blocking paths to their root blockers, and renders a Mermaid flowchart representing the lock dependency graph. Read-only. Returns an object with cycles (list of detected cycle PID lists), paths (linear blocking paths as PID lists), roots (root blocker PIDs), nodes (dict keyed by PID with per-backend lock detail), and mermaid (the pre-rendered flowchart string).

Read the pg_stat_io view (PostgreSQL 16+). Reports per (backend_type, object, context) cumulative I/O activity — reads, writes, extends, evictions, hits, fsyncs. Useful for spotting buffer-cache misses and write amplification. On PostgreSQL 14 / 15 the view doesn't exist, so the tool returns available=false and an empty list.

Read a high-level summary of the PostgreSQL shared buffer cache usage. Reports total buffers, free/used buffers, dirty buffers, and average usage count. Requires the pg_buffercache extension. If not installed, returns available=false.

Read the list of database relations taking up the most space in the PostgreSQL shared buffer cache. Reports buffered size, percentage of shared buffers, percent of relation buffered, average usage count, and dirty pages. Allows filtering by schema. Requires the pg_buffercache extension. If not installed, returns available=false.

Read Write-Ahead Log (WAL) records information over a specified LSN range. Requires the pg_walinspect extension. If not installed, returns available=false.

Read Write-Ahead Log (WAL) record statistics over a specified LSN range, grouped by resource manager or record type. Requires the pg_walinspect extension. If not installed, returns available=false.

Report WAL-archiving health from pg_stat_archiver + the archive-mode GUCs — the early-warning signal for a failing archive_command / archive_library (full archive volume, bad object-store credentials, network partition), which otherwise silently accumulates WAL in pg_wal/ until the volume fills. Companion to read_pg_wal_records (which inspects WAL records); this covers the WAL archive. Read-only; never raises. The archive_command string is NOT echoed (it can embed credentials) — only a boolean archive_command_set. Returns an object with available, archiving_enabled, archive_mode, archive_command_set, archived_count, last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time, stats_reset, healthy (bool — false when archiving is on and the latest attempt failed), and detail.

Example: get_wal_archive_status()

Assess whether the cluster is ready for point-in-time recovery (PITR) — the one-call 'could I actually recover to an arbitrary point right now, and if not what's missing?' check. Composes get_wal_archive_status (5.2) with the GUCs that gate PITR: continuous archiving must be healthy, wal_level >= replica, max_wal_senders >= 1 (so pg_basebackup can stream a base backup), and full_page_writes on (torn-page safety during replay). Read-only advisor — changes nothing, emits no secrets. Returns an object with available, ready (bool — true only when all gates pass), wal_level, archiving_healthy, gates (list of objects with name, ok, observed, remediation), remediation (ordered fixes for failing gates), and detail.

Example: check_pitr_readiness()

Return a highly condensed, token-efficient text summary of a schema's tables, columns, primary keys, nullability, and relations to save context window tokens.

Query and summarize historical migrations applied to the database by popular migration frameworks (Alembic, Flyway, Diesel, Django, Prisma, Golang Migrate, Goose, Sequelize). Allows filtering by schema. Returns an object with one optional list per detected framework: alembic, flyway, diesel, django, prisma, golang_migrate, goose, sequelize. Each entry is null when the framework's table isn't present; otherwise it carries the framework-specific row shape (e.g. alembic has {version_num}; flyway has {installed_rank, version, description, type, ...}).

Render a Mermaid ER diagram for a schema. Views and foreign tables are excluded; partitions are excluded by default — pass include_partitions=true to draw each partition as its own entity. Returns the Mermaid erDiagram as a string ready to paste into a Markdown block.

Example: generate_schema_diagram(schema='public', include_partitions=false)

Build a Mermaid graph LR of foreign-key cascade chains in a schema. Each edge runs from the referencing table to the referenced table, labelled with the cascade action(s) on DELETE / UPDATE. By default only FKs with at least one CASCADE / SET NULL / SET DEFAULT action are included — those are the ones that produce a write blast radius. Pass include_all=true to include NO ACTION / RESTRICT FKs too (full FK topology view). Cross-schema FK targets are rendered as separate nodes prefixed with their schema. Returns the Mermaid graph LR diagram as a string.

Generate a detailed Markdown reference of a schema's tables, columns, constraints, indexes, views, foreign tables, and custom enums along with comments / descriptions. Optional include_samples fetches a few distinct, non-null values for each column. Returns a single Markdown document as a string.

Example: generate_schema_docs(schema='public', include_samples=true)

Return the structural diff between two schemas — tables/columns/indexes/constraints/foreign-keys added, removed, or changed. Base tables only; views and custom types are not compared. Renames surface as a paired add + remove.

Example: compare_schemas(left_schema='public', right_schema='staging')

Recommend an ivfflat or hnsw configuration for a pgvector column. Reads the live row count and column dimension, applies the standard pgvector heuristics, and returns the parameters plus a ready-to-run CREATE INDEX statement. Requires the vector extension.

Measure recall@k of an existing pgvector index against a brute-force ground truth (function-form distance, which pgvector documents as non-indexed). Returns the mean overlap over a sample of rows from the table. Requires the vector extension.

Generate a DDL plan that converts a pgvector vector(N) column to halfvec(N) — halving per-element storage (4 → 2 bytes) with typically negligible recall impact at d ≥ 768. Reads the column's current type + dimension from the catalog, finds every index on the column, and emits an ordered migration_sql plan: DROP each affected index, ALTER COLUMN to halfvec(N) via a USING cast, then recreate each index with its halfvec opclass. Also returns a mirror rollback_sql that restores the original vector(N) type plus the original index definitions. Nothing is executed — feed the plan through the shadow-migration workflow (prepare_migration / validate_migration_schema) before applying. Returns already_halfvec=true (and an empty plan) when the column is already halfvec, and refuses any index whose opclass has no halfvec sibling rather than rewriting it incorrectly. Requires the vector extension.

Sweeps ef_search values to measure the latency and recall trade-off curve for a given pgvector query vector against exact brute-force ground truth. Requires the vector extension. Returns a list of objects with ef_search, recall_at_k, mean_latency_ms, and p95_latency_ms — one row per ef_search value tested.

Recommend an hnsw.ef_search value for a target recall@k — the actionable companion to analyze_hnsw_recall. Samples sample_queries rows (default 10) as query vectors, builds an exact brute-force top-k ground truth per query, sweeps ef_values (default 16/32/64/128/256) measuring mean recall@k and p50/p95 latency at each, and recommends the smallest value clearing target_recall (default 0.95). Unlike the single-query curve tool, this VERIFIES an HNSW index actually exists on the column (returns has_hnsw_index=false with guidance otherwise — a sweep without one just measures sequential scans). The query row is excluded from its own results. Requires the vector extension. Returns an object with available, has_hnsw_index, index_name, metric, k, target_recall, sample_queries, recommended_ef_search (int or null), detail, and sweep (list of objects with ef_search, mean_recall_at_k, p50_latency_ms, p95_latency_ms, meets_target).

Example: recommend_hnsw_ef_search(schema='public', table='docs', column='embedding', k=10, target_recall=0.95)

Recommend an ivfflat.probes value for a target recall@k — the IVFFlat analogue of recommend_hnsw_ef_search. Samples sample_queries rows (default 10) as query vectors, builds an exact brute-force top-k ground truth per query, sweeps probe_values (default 1/2/5/10/20/50) measuring mean recall@k and p50/p95 latency at each, and recommends the smallest value clearing target_recall (default 0.95). VERIFIES an IVFFlat index actually exists on the column (returns has_ivfflat_index=false with guidance otherwise — a sweep without one just measures sequential scans). The query row is excluded from its own results. Requires the vector extension. Returns an object with available, has_ivfflat_index, index_name, metric, k, target_recall, sample_queries, recommended_probes (int or null), detail, and sweep (list of objects with probes, mean_recall_at_k, p50_latency_ms, p95_latency_ms, meets_target).

Example: recommend_ivfflat_probes(schema='public', table='docs', column='embedding', k=10, target_recall=0.95)

Recommend a pgvector distance metric (cosine | l2 | inner_product) from the embedding-magnitude distribution. Samples up to sample_size non-NULL rows of schema.table.column, computes each embedding's L2 norm, and applies a small heuristic: pre-normalised (CV < 5% and mean ≈ 1.0) → inner_product; nearly-constant magnitude but not unit-norm → cosine (same ranking as L2, safer default); variable magnitude → cosine (normalises out heterogeneous sources). Returns the metric + a rationale + the underlying distribution stats. Reports available=false if the pgvector extension is not installed.

Find the k rows in target_schema.target_table most similar to a specific row in source_schema.source_table. Locates the source row via source_id_column = source_id_value, reads its embedding from source_embedding_column, then issues a pgvector k-NN query against target_embedding_column. Both columns must be vector(N) of the same N — verified from the catalog up front so a mismatch fails with a clear error rather than a cast error. Useful for entity-resolution / linking across tables whose embeddings come from different models but share a dimension. Returns source_embedding_found=false when no row matches the id value. Reports available=false if pgvector is not installed.

Context-packed k-NN retrieval (a one-shot RAG building block). Runs a pgvector k-NN against schema.table.embedding_column for a caller-supplied query_vector (no embedding model needed), then expands each hit one hop along foreign keys and returns the hit row + its related parent / child records in one object. Parents (when include_parents, default true) are the rows a hit references via outbound FKs; children (when include_children, default true) are rows referencing the hit via inbound FKs, capped at max_related (default 5) per FK. Limitations: 1 hop only; inbound (child) expansion is same-schema only. The embedding column is dropped from every returned row. Requires the vector extension. Returns available, dimension, detail, and hits (each with distance, row, and related — a list of fk_name/direction/related_schema/related_table/rows).

Example: retrieve_with_context(schema='public', table='docs', embedding_column='embedding', query_vector=[0.1, 0.2, 0.3], k=5)

k-means cluster a pgvector column. Samples up to sample_size (default 5000) non-NULL rows of schema.table.embedding_column, runs Lloyd's algorithm with k-means++ seeding (seed for determinism), and returns centroids (one per cluster, with size) + assignments (per-row cluster index + distance). When id_column is set each assignment carries that column's value; otherwise the row's positional sample index. metric='l2' (default — squared Euclidean) or 'cosine' (vectors normalised; centroids re-normalised every iteration). k >= 2 and there must be at least 2k parseable rows. Reports available=false if pgvector is not installed.

Example: cluster_vectors(schema='public', table='docs', embedding_column='embedding', k=8, metric='cosine')

Flag pgvector rows whose embedding sits far from any cluster centroid. Samples up to sample_size (default 5000) non-NULL rows of schema.table.embedding_column, clusters them with k-means (same engine as cluster_vectors), then per cluster computes a z-score on the distance from each row to its centroid and flags rows whose z-score exceeds zscore_threshold (default 3.0). Per-cluster scoring catches rows that are weird-for-their-group rather than weird-overall, which is usually what 'find outliers' should mean. Returns outliers sorted by z-score descending (capped at max_results), total_outliers (the unclipped count), and cluster_stats (per-cluster mean / std of within-cluster distances). When id_column is set each outlier carries that column's value; otherwise the row's positional sample index. k >= 2 and there must be at least 2k parseable rows. Reports available=false if pgvector is not installed.

Compare two time windows of a pgvector column and flag distributional drift. Samples up to sample_size (default 5000) non-NULL embeddings from each window (filtered by timestamp_column), computes the centroid (per-dimension mean vector) and L2-norm distribution of each, then reports the cosine distance between the two centroids (the main drift signal), the relative change in mean / std of the L2-norm distribution, and a boolean drift_detected that flips when cosine distance exceeds drift_threshold (default 0.05). Each window is treated as a half-open [start, end) interval. Useful for ops monitoring of embedding pipelines — an upstream model swap typically shows up as a large centroid cosine distance even if the norm distribution looks stable. insufficient_data is returned distinctly from drift_detected=false when either window is empty. Reports available=false if pgvector is not installed.

Example: monitor_embedding_drift(schema='public', table='docs', embedding_column='embedding', timestamp_column='created_at', baseline_start='2026-01-01', baseline_end='2026-02-01', current_start='2026-02-01', current_end='2026-03-01')

Cross-backend retrieval-quality report for a pgvector or pg_turboquant ANN index. Detects the backend (HNSW / IVFFlat / turboquant), sweeps the matching per-backend knob (ef_search / probes / candidate_limit) across a multiplier curve, computes recall@k vs a brute-force exact baseline, Spearman + Kendall rank correlation, per-query p50/p95 wall-clock latency, and (for turboquant) the page-pruning ratio from tq_last_scan_stats. Emits findings: baseline_recall_low (CRITICAL), rerank_lift_flat / rerank_lift_steep / ranking_degraded / pruning_ineffective (WARNING). Burns sample_size x (1 + len(candidate_multipliers)) queries; ad-hoc diagnostic, not a cron tool. Requires the vector extension; turboquant-arm metrics require pg_turboquant.

Per-query Spearman + Kendall correlation between bi-encoder and cross-encoder ranks, aggregated across queries in the window. Low correlation = the reranker is actively reordering (doing real work); high correlation = the reranker mostly confirms the bi-encoder order. Optional model / retrieval_index filters. Surfaces reranker_idle (WARNING) when the reranker rarely changes ordering. Reads from mcpg_rag.rerank_events; returns a report with zero counts when the table doesn't exist or the window is empty.

Jaccard overlap between top-K-by-bi-rank and top-K-by-cross-rank per query, aggregated. High mean Jaccard means the reranker isn't actually changing the top-K membership. Surfaces topk_stable (WARNING) when the rerank is barely earning its place at this K. Reads from mcpg_rag.rerank_events; returns a report with zero counts when the table doesn't exist or the window is empty.

Equal-width histogram of cross_encoder_score values over the window plus the top-decile share. Surfaces score_clustering (WARNING) when the reranker isn't discriminating (more than half of scores land in the top decile of the range). Reads from mcpg_rag.rerank_events. Returns an object with window_days, event_count, histogram (list of counts), bucket_edges (list of bucket boundaries), top_decile_share, and findings (list of advisory findings).

NDCG@k under bi-encoder ordering vs cross-encoder ordering, averaged across labeled queries (ground_truth_relevance IS NOT NULL). Reports the delta (cross - bi) — positive = the rerank is adding real ranking quality, negative = it's hurting. Surfaces rerank_hurts_ndcg (CRITICAL) or rerank_lifts_ndcg (GOOD evidence). Reads from mcpg_rag.rerank_events; returns zero counts when no labeled rows exist in the window.

Roll-up advisor over the four analytics for one window. Returns a single headline summary + the full list of findings. Built from whichever combination of reranker_idle / topk_stable / score_clustering / rerank_hurts_ndcg / rerank_lifts_ndcg fires. Also feeds the RAG Reranker Pipeline category in audit_database. Reads from mcpg_rag.rerank_events.

Compute corpus-percentile thresholds from accumulated mcpg_rag.efficiency_observations history. Phase E currently adapts three thresholds: baseline_recall_low (p10 of recall_baseline), ranking_degraded_spearman (p10 of spearman), and pruning_ineffective (p10 of pages_pruned_ratio_p50). The remaining four thresholds stay at their hardcoded defaults. Filters by days window + optional backend / metric / k so callers can ask 'what's normal for HNSW+cosine+k=10 in this deployment' vs 'what's normal globally'. Falls back to defaults (with derived_from_corpus=false) when the corpus is smaller than the minimum required. Returns an object with corpus_size, derived_from_corpus (bool), and the threshold fields (baseline_recall_low, baseline_recall_low_adapted, ranking_degraded_spearman, ranking_degraded_spearman_adapted, pruning_ineffective, pruning_ineffective_adapted, rerank_lift_flat_delta, rerank_lift_steep_low, rerank_lift_steep_high, and ranking_degraded_recall).

Read a PostgreSQL schema and emit a valid Prisma .prisma schema string (mirrors prisma db pull). Covers tables, columns, primary/foreign keys, unique constraints, indexes, and enums. Views, foreign tables, partitions, triggers, functions, and policies are out of scope; unmappable types fall back to Unsupported("..."). Returns the rendered schema.prisma source as a single string.

Read a PostgreSQL schema and emit a Drizzle ORM TypeScript schema string (drizzle-orm/pg-core). Covers tables, columns with PG-native types, primary/foreign keys, unique constraints, indexes, defaults, and enums. Single-column FKs emit column-level .references(); composite FKs are a documented v1 gap. Views, foreign tables, partitions, triggers, and functions are out of scope. Returns the rendered TypeScript schema.ts source as a single string.

Read a PostgreSQL schema and emit a Diesel ORM (Rust) schema.rs. One table! macro per table with column SQL types, Nullable<T> for nullable columns, plus joinable! declarations for single-column intra-schema FKs and an allow_tables_to_appear_in_same_query! macro so multi-table joins type-check. Enum types are emitted as Text-backed wrapper enums in a pg_enum module so the output works without diesel_derive_enum. Composite FKs are a documented v1 gap.

Read a PostgreSQL schema and emit a jooq-codegen configuration XML pointing at it. Unlike the other exporters, jOOQ generates Java code itself from a live database — the artefact here is the configuration file the user feeds to mvn jooq-codegen:generate (or the Gradle task). The XML lists every base table explicitly via an regex, excludes MCPg's bookkeeping tables, and emits a for every json / jsonb column so they map to org.jooq.JSON / org.jooq.JSONB out of the box. Default Java package is com.example.jooq; override via the target_package arg.

Read a PostgreSQL schema and emit Ent (Go) Schema struct files — one .go file per table. Each file exports a struct that lists field.X(...) calls for every column, edge.To(...) for single-column intra-schema FKs, and field.Enum().Values() for enum-typed columns. Composite FKs are a documented v1 gap. Returns a JSON object {filename: source} so the agent can write each file.

Read a PostgreSQL schema and emit Ecto (Elixir) schema modules — one .ex file per table, named after the singularised table. Each module uses Ecto.Schema with field declarations, belongs_to for single-column intra-schema FKs, and timestamps() when both inserted_at and updated_at exist. The Elixir top-level module is configurable via app_module (default MyApp). Returns a JSON object {filename: source} so the agent can write each file.

Read a PostgreSQL schema and emit a SQLAlchemy 2.0 declarative models file (DeclarativeBase + Mapped[T] + mapped_column). Covers tables, columns with PG-native types (incl. jsonb via sqlalchemy.dialects.postgresql.JSONB), primary keys, single-column FKs via ForeignKey(), unique constraints (column-level + composite via table_args), defaults, and enums (emitted as Python enum.Enum classes). Composite FKs are a documented v1 gap. Returns the rendered Python models.py source as a single string.

Read a PostgreSQL schema and emit a sqlc-friendly schema.sql (plain DDL). Order: CREATE SCHEMA, CREATE TYPE for each enum, CREATE TABLE statements (columns only), ALTER TABLE ADD CONSTRAINT (PK / unique / check / foreign key in that order), then CREATE INDEX for non-constraint indexes. The file replays cleanly against an empty database so FKs land after all referenced tables exist. In-process — no MCPG_ALLOW_SHELL needed. Returns the rendered schema.sql text as a single string.

Run a set of catalog-driven advisor rules against a schema and return the aggregated findings. Rules cover missing primary keys, unindexed foreign keys, duplicate indexes, and nullable timestamps without time zone. Advisory only — no writes.

Find tables and indexes with zero scans since pg_stat was last reset — a strong signal of dead code, but NOT a verdict. Tables report seq+idx scan counts, write counts, and estimated row count; indexes report size and definition. Excludes PRIMARY KEY and UNIQUE indexes (PG needs those regardless of scans). Run this after the database has been hot for a meaningful period — fresh stats produce false positives. Returns an object with tables (list of candidate tables with their stats) and indexes (list of candidate indexes with size and definition).

Flag columns whose names or types look like they hold sensitive data (passwords, tokens, PII, financial info, health records). Pure heuristic — no row sampling, no value introspection. Categories: credential, financial, contact, identifier, health, government_id, location. Each finding carries a confidence (high / medium / low) so an agent can filter for a first review pass. Treat as a SIGNAL, not a verdict — a column named email_template_id matches the email pattern but isn't itself an email address. Returns an object with findings (list of {schema, table, column, data_type, category, confidence, matched_pattern}) and summary counts by category.

Lint table / column / index naming in a schema. Detects the majority case style (snake_case / camelCase / PascalCase / SCREAMING_SNAKE) per schema and per table, then flags outliers. Also flags indexes whose names do not start with a conventional prefix (idx_, ix_, pk_, uq_, fk_ by default). Findings carry the offender's style and the detected majority — agents can use the style field to filter for renames vs accept-as-is. Pure read. Returns an object with schema_style (detected majority), findings (list of style outliers), and index_prefix_findings (indexes with non-conventional prefixes).

Test what an RLS-bound role can read from a table. Reports whether RLS is enabled on the table, lists the policies that apply to the given role, counts the rows the role can read, and returns up to sample_size rows so the agent can inspect them. Runs as the target role inside a READ ONLY transaction — no writes can leak. Pure read.

Generate synthetic INSERT statements for a table — typed values respecting column type, NOT NULL, and DEFAULT. Returns the SQL as strings; does NOT execute it. Useful for seeding dev / staging environments. The generator is deterministic when a seed is provided. Foreign keys are NOT resolved — the caller must pre-seed referenced rows or drop the FK before applying. Hard cap of 10000 rows per call. Pure read (the actual writes go through run_write under unrestricted mode).

Generate openCypher CREATE/MERGE statements that project a relational schema into an Apache AGE property graph — rows become vertices (one label per table), foreign keys become edges. EMITS the Cypher for review; NEVER executes it (like generate_test_data). With row_limit=0 (default) it returns a schema-level template plan (one CREATE per label, one MERGE per edge type, $prop placeholders) reading only the catalog. With row_limit>0 it also emits concrete per-row statements (values escaped, NULLs omitted, capped at 1000 rows/table). Tables without a primary key still get node CREATEs but their edges are skipped (they can't be reliably MATCHed). NOTE: AGE materialises the data (this is a LOAD, not a virtual view); run the node statements before the edge statements; the projection is 1-hop faithful to the FK graph. Returns an object with available (AGE installed, advisory), schema, graph_name, row_limit, node_labels (list of label, source_table, key_columns, property_columns), edge_types (list of edge_type, from_label, to_label, from_key, to_key, fk_name), cypher_statements (generated, never executed), warnings, and detail.

Example: generate_graph_projection(schema='public', graph_name='g', row_limit=0)

Generate ONE realistic test row for a table — catalogue-aware. Skips identity / generated columns (server fills them in), samples one existing row from each referenced table for FK columns (so the row inserts cleanly), and uses column-name heuristics (*_email → user_N@example.com, *_url → https://example.com/r/N, *_at → recent timestamp, etc.) to make values look like data. Sibling of generate_test_data (bulk) — designed for the shadow-migration workflow where a single realistic row matters more than volume. Returns an object with insert_sql (one ready-to-execute INSERT), columns (per-column ColumnFill with sql_literal + heuristic explanation), schema, table. Does NOT execute the INSERT — caller applies via run_write when ready.

Example: generate_test_row_for(schema='public', table='orders', seed=42)

Surface hot-path inefficiencies from the audit log. Reads mcpg_audit.events over the last lookback_minutes (default 60, capped at 1440) and flags tools called more than hot_threshold times (default 10). Catalogue-listing tools (list_tables / list_schemas / list_indexes / etc.) get a redundant_listing finding pointing at get_compact_schema; other tools get a hot_repeated_call finding suggesting caching. Idle sessions get an idle_session finding. When mcpg_audit.events doesn't exist (audit subsystem off) returns audit_table_present=False with a diagnostic. Returns an object with audit_table_present (bool), events_examined (int), lookback_minutes, findings (list of objects with reason, tool, call_count, suggestion), and detail.

Example: analyze_session_cost(lookback_minutes=30, hot_threshold=15)

Empirically curate describe_self's per-bucket headline_tools from the audit log. Reads mcpg_audit.events over the last lookback_days (default 7, capped at 90), groups successful calls by capability bucket, and reports the top-top_n (default 6) tools per bucket with newcomers (recommended but not in the hand-curated current list) and departures (currently headlined but not in the recommendation). The output is a REVIEWABLE recommendation, not an auto-applied override — operators decide whether to update mcpg.about.CAPABILITIES. Returns audit_table_present=False with a diagnostic when the audit subsystem is off. Returns an object with audit_table_present, lookback_days, top_n, events_examined, detail, and buckets (list of objects with bucket_id, current, recommended, newcomers, departures, call_counts).

Example: recommend_headline_tools(lookback_days=14, top_n=6)

Flag sequences nearing their ceiling — serial / identity / explicit sequences whose last_value / max_value exceeds warning_pct (default 80) or critical_pct (default 95). Sequence overflow is catastrophic and silent until the next nextval() raises 'reached maximum value' — the int4 serial ceiling (2^31-1) is hit far more often than expected. Pure read; available=false on PG < 10 (no pg_sequences). Returns an object with available, total_examined, warning_pct, critical_pct, detail, and sequences (at-risk only, sorted by used_pct desc — each with schema, sequence, last_value, max_value, used_pct, remaining, status).

Example: audit_sequences(warning_pct=80, critical_pct=95)

Sanity-sweep postgresql.conf via pg_settings. Flags dangerous toggles (fsync=off, full_page_writes=off, autovacuum=off, synchronous_commit=off), cross-setting issues (maintenance_work_mem < work_mem, tiny shared_buffers, low checkpoint_completion_target), and — when total_ram_mb is supplied — RAM-relative ratios for shared_buffers / effective_cache_size (PostgreSQL can't see host RAM itself). Pure read. Returns an object with ram_aware (bool), examined_settings (list), detail, and findings (tripped rules only — each with code, setting, current, status, suggestion).

Example: audit_settings(total_ram_mb=16384)

Compute pgtune-style postgresql.conf recommendations. Pure calculator — touches no database. Given total_ram_mb (required), cpu_count (default 4), workload (one of web/oltp/dw/desktop/mixed, default mixed), storage (one of ssd/hdd/san, default ssd), and an optional max_connections override, returns recommended values for shared_buffers, effective_cache_size, work_mem, maintenance_work_mem, wal_buffers, min_wal_size/max_wal_size, checkpoint_completion_target, default_statistics_target, random_page_cost, effective_io_concurrency, and the parallel-worker knobs. Memory fields are postgres-ready strings; settings is the same data as a flat {guc: value} dict for direct rendering. Pair with audit_settings (audit first, then size).

Example: recommend_postgres_conf(total_ram_mb=16384, cpu_count=8, workload='oltp', storage='ssd')

Analyze a SQL query for syntax anti-patterns and performance issues using EXPLAIN plan costs and index scans, returning an optimized version.

Return a one-stop snapshot of a table: columns, primary key, foreign keys, every other constraint, indexes, storage + row-count + last-vacuum/analyze stats, and (optionally) a short sample of rows. Replaces what would otherwise be 4-5 individual tool calls. Set sample_rows=0 on wide / jsonb-heavy tables where the sample isn't useful.

Example: summarize_table(schema='public', table='users', sample_rows=5)

Diagnose why a SQL query might be slow, in one call. Runs EXPLAIN (FORMAT JSON) — does NOT execute the query — walks the plan tree, snapshots concurrent active queries + blocking lock pairs, reads the cluster-wide cache hit ratio, and produces categorised suggestions (plan / contention / cache / maintenance). Read-only; safe to run on a statement the agent doesn't want to materialise yet.

Example: why_is_this_slow(sql='SELECT * FROM orders WHERE customer_id = 42')

Run a read-only SQL query and serialise its rows to CSV or JSON. Reuses the SQL-safety checks of run_select. Truncates at limit rows and flags it in the result so callers can paginate.

Example: export_query(sql='SELECT id, email FROM users', format='csv', limit=10000)

Serialise every row in schema.table (up to limit) to CSV or JSON. Schema and table names must be plain identifiers. Returns an object with format, row_count, truncated (bool — true when the row count hit limit), and content (the serialised payload as a string).

List recent rows from mcpg_audit.events (newest first). Returns an empty list when MCPG_AUDIT_PERSIST has never been turned on (no audit table yet). Optionally filter by tool name.

Verify the HMAC-SHA256 signature chain of persisted audit events in mcpg_audit.events. Returns an object with verified (bool), events_checked (int), the first_event_id and last_event_id covered by the walk, and (on failure) error and first_invalid_id pointing at where the chain broke.

Validate and run a read-only SQL query. Writes, DDL, and other unsafe statements are rejected before execution.

Example: run_select(sql='SELECT id, email FROM users LIMIT 10', max_rows=1000)

Run a read-only SELECT with an elevated, bounded work_mem (and optionally maintenance_work_mem) for THIS statement only. Useful for heavy analytical SELECTs (large sorts / hash joins / GROUP BY) that spill to disk under the default work_mem. The knob is set via SET LOCAL inside the same read-only transaction, so it never leaks back into the pool. Both knobs must match ^\d+(kB|MB|GB)$ and are hard-capped at 2GB (unbounded values are an OOM risk). SQL is validated read-only by the same allowlist as run_select. SET LOCAL only affects transactional statements — non-transactional maintenance (CREATE INDEX CONCURRENTLY, VACUUM) is out of scope.

Example: run_select_tuned(sql='SELECT a, count(*) FROM big GROUP BY a', work_mem='256MB')

Run up to parallel_limit read-only SELECTs concurrently. Each statement is validated by the same safety allowlist as run_select; one bad query does not abort the others — its error is captured in its own outcome slot. Useful for dashboard-style fan-out where round-trip latency dominates (e.g. fetching counters / aggregates from several tables at once). Each outcome includes an index so the caller can correlate results without relying on ordering.

Open a server-side cursor for a SELECT query. The cursor holds the result set on the server side so an agent can page through millions of rows without loading them all. SQL is validated by the same safety allowlist as run_select. Returns the cursor_id; fetch the rows with fetch_cursor and close with close_cursor (or let the 5-minute TTL clean up). Hard cap of 16 concurrent cursors.

Fetch the next batch from an open server-side cursor. exhausted=true means the FETCH returned fewer rows than requested — stop polling. batch_size defaults to 100; hard cap is 10000 per call.

Close a server-side cursor and release its dedicated connection. Idempotent — returns closed=false when the cursor was not open (already closed, expired, or never existed).

List every currently-open server-side cursor with its SQL, rows_returned so far, age in seconds, and the TTL after which it'll be auto-closed.