{"server":{"name":"io.github.timescale/pg-aiguide","$schema":"https://static.modelcontextprotocol.io/schemas/2025-10-17/server.schema.json","remotes":[{"url":"https://mcp.tigerdata.com/docs","type":"streamable-http"}],"version":"0.6.1","packages":[{"version":"0.6.1","transport":{"type":"stdio"},"identifier":"@tigerdata/pg-aiguide","registryType":"npm","environmentVariables":[{"name":"OPENAI_API_KEY","format":"string","isSecret":true,"isRequired":true,"description":"Your API key for text embeddings via OpenAI"},{"name":"PGHOST","format":"string","isSecret":true,"isRequired":true,"description":"PostgreSQL host to connect to"},{"name":"PGPORT","format":"number","isSecret":true,"isRequired":true,"description":"PostgreSQL port to connect to"},{"name":"PGUSER","format":"string","isSecret":true,"isRequired":true,"description":"PostgreSQL user to connect as"},{"name":"PGPASSWORD","format":"string","isSecret":true,"isRequired":true,"description":"PostgreSQL password to connect with"},{"name":"PGDATABASE","format":"string","isSecret":true,"isRequired":true,"description":"PostgreSQL database to connect to"},{"name":"DB_SCHEMA","format":"string","isSecret":true,"description":"PostgreSQL database schema to use"}]},{"transport":{"type":"stdio"},"identifier":"ghcr.io/timescale/pg-aiguide:0.6.1","registryType":"oci","environmentVariables":[{"name":"OPENAI_API_KEY","format":"string","isSecret":true,"isRequired":true,"description":"Your API key for text embeddings via OpenAI"},{"name":"PGHOST","format":"string","isSecret":true,"isRequired":true,"description":"PostgreSQL host to connect to"},{"name":"PGPORT","format":"number","isSecret":true,"isRequired":true,"description":"PostgreSQL port to connect to"},{"name":"PGUSER","format":"string","isSecret":true,"isRequired":true,"description":"PostgreSQL user to connect as"},{"name":"PGPASSWORD","format":"string","isSecret":true,"isRequired":true,"description":"PostgreSQL password to connect with"},{"name":"PGDATABASE","format":"string","isSecret":true,"isRequired":true,"description":"PostgreSQL database to connect to"},{"name":"DB_SCHEMA","format":"string","isSecret":true,"description":"PostgreSQL database schema to use"}]}],"repository":{"url":"https://github.com/timescale/pg-aiguide","source":"github"},"description":"Comprehensive PostgreSQL documentation and best practices, including ecosystem tools"},"_meta":{"io.modelcontextprotocol.registry/official":{"status":"active","isLatest":true,"updatedAt":"2026-09-04T19:16:47.690447Z","publishedAt":"2026-09-04T19:16:47.690447Z","statusChangedAt":"2026-09-04T19:16:47.690447Z"},"dev.protogrid/connectability":{"class":"R0","autonomous":true,"human_steps":[],"preferred":{"kind":"remote","idx":0},"remotes":[{"idx":0,"url":"https://mcp.tigerdata.com/docs","transport":"streamable-http","templated":false,"headers":[],"auth":{"type":"none","location":null,"secret_name":null,"oauth":null},"protocol":{"supported_versions":["2025-11-25"],"stateless":true},"health":{"reachable":true,"uptime_30d":1,"latency_ms_p50":147,"consecutive_failures":0,"last_probe_at":"2026-10-09T22:36:21.752Z","last_ok_at":"2026-10-09T22:36:21.752Z"}}],"packages":[{"idx":0,"registry_type":"npm","identifier":"@tigerdata/pg-aiguide","version":"0.6.1","runtime_hint":null,"transport":"stdio","secrets":["OPENAI_API_KEY","PGHOST","PGPORT","PGUSER","PGPASSWORD","PGDATABASE","DB_SCHEMA"],"requires_execution":true},{"idx":1,"registry_type":"oci","identifier":"ghcr.io/timescale/pg-aiguide:0.6.1","version":null,"runtime_hint":null,"transport":"stdio","secrets":["OPENAI_API_KEY","PGHOST","PGPORT","PGUSER","PGPASSWORD","PGDATABASE","DB_SCHEMA"],"requires_execution":true}]},"dev.protogrid/tools":[{"name":"search_docs","title":"Search Documentation","description":"Search documentation with hybrid semantic (vector) and keyword (BM25) search. Use semanticWeight to choose keyword-only (0), semantic-only (1), or a blend; mid values fuse rankings with RRF. Supports Tiger Cloud (TimescaleDB), PostgreSQL, and PostGIS.","annotations":{"title":"Search Documentation","readOnlyHint":true,"idempotentHint":true},"source":"probe","observed_at":"2026-10-09T22:36:21.752Z"},{"name":"view_skill","title":"View Skill","description":"Retrieve detailed skills for TimescaleDB operations and best practices.\n\n## Available Skills\n\n<available_skills>\n[11\t]{name\tdescription}:\n  design-postgis-tables\tComprehensive PostGIS spatial table design reference covering geometry types, coordinate systems, spatial indexing, and performance patterns for location-based applications\n  design-postgres-tables\t\"Use this skill for general PostgreSQL table design.\\n\\n**Trigger when user asks to:**\\n- Design PostgreSQL tables, schemas, or data models when creating new tables and when modifying existing ones.\\n- Choose data types, constraints, or indexes for PostgreSQL\\n- Create user tables, order tables, reference tables, or JSONB schemas\\n- Understand PostgreSQL best practices for normalization, constraints, or indexing\\n- Design update-heavy, upsert-heavy, or OLTP-style tables\\n\\n\\n**Keywords:** PostgreSQL schema, table design, data types, PRIMARY KEY, FOREIGN KEY, indexes, B-tree, GIN, JSONB, constraints, normalization, identity columns, partitioning, row-level security\\n\\nComprehensive reference covering data types, indexing strategies, constraints, JSONB patterns, partitioning, and PostgreSQL-specific best practices.\\n\"\n  find-hypertable-candidates\t\"Use this skill to analyze an existing PostgreSQL database and identify which tables should be converted to Timescale/TimescaleDB hypertables.\\n\\n**Trigger when user asks to:**\\n- Analyze database tables for hypertable conversion potential\\n- Identify time-series or event tables in an existing schema\\n- Evaluate if a table would benefit from Timescale/TimescaleDB\\n- Audit PostgreSQL tables for migration to Timescale/TimescaleDB/TigerData\\n- Score or rank tables for hypertable candidacy\\n\\n\\n**Keywords:** hypertable candidate, table analysis, migration assessment, Timescale, TimescaleDB, time-series detection, insert-heavy tables, event logs, audit tables\\n\\nProvides SQL queries to analyze table statistics, index patterns, and query patterns. Includes scoring criteria (8+ points = good candidate) and pattern recognition for IoT, events, transactions, and sequential data.\\n\"\n  migrate-postgres-tables-to-hypertables\t\"Use this skill to migrate identified PostgreSQL tables to Timescale/TimescaleDB hypertables with optimal configuration and validation.\\n\\n**Trigger when user asks to:**\\n- Migrate or convert PostgreSQL tables to hypertables\\n- Execute hypertable migration with minimal downtime\\n- Plan blue-green migration for large tables\\n- Validate hypertable migration success\\n- Configure compression after migration\\n\\n**Prerequisites:** Tables already identified as candidates (use find-hypertable-candidates first if needed)\\n\\n**Keywords:** migrate to hypertable, convert table, Timescale, TimescaleDB, blue-green migration, in-place conversion, create_hypertable, migration validation, compression setup\\n\\nStep-by-step migration planning including: partition column selection, chunk interval calculation, PK/constraint handling, migration execution (in-place vs blue-green), and performance validation queries.\\n\"\n  pgvector-semantic-search\t\"Use this skill for setting up vector similarity search with pgvector for AI/ML embeddings, RAG applications, or semantic search.\\n\\n**Trigger when user asks to:**\\n- Store or search vector embeddings in PostgreSQL\\n- Set up semantic search, similarity search, or nearest neighbor search\\n- Create HNSW or IVFFlat indexes for vectors\\n- Implement RAG (Retrieval Augmented Generation) with PostgreSQL\\n- Optimize pgvector performance, recall, or memory usage\\n- Use binary quantization for large vector datasets\\n\\n**Keywords:** pgvector, embeddings, semantic search, vector similarity, HNSW, IVFFlat, halfvec, cosine distance, nearest neighbor, RAG, LLM, AI search\\n\\nCovers: halfvec storage, HNSW index configuration (m, ef_construction, ef_search), quantization strategies, filtered search, bulk loading, and performance tuning.\\n\"\n  postgres\t\"Use this skill for any PostgreSQL database work — table design, indexing, data types, constraints, extensions (pgvector, PostGIS, TimescaleDB), search, and migrations.\\n\\n**Trigger when user asks to:**\\n- Explore an existing PostgreSQL database to understand its objects and relationships\\n- Design or modify PostgreSQL tables, schemas, or data models\\n- Choose data types, constraints, indexes, or partitioning strategies\\n- Work with pgvector embeddings, semantic search, or RAG\\n- Set up full-text search, hybrid search, or BM25 ranking\\n- Use PostGIS for spatial/geographic data\\n- Set up TimescaleDB hypertables for time-series data\\n- Migrate tables to hypertables or evaluate migration candidates\\n- Plan or execute safe schema migrations with zero downtime\\n\\n**Keywords:** PostgreSQL, Postgres, SQL, schema, table design, indexes, constraints, pgvector, PostGIS, TimescaleDB, hypertable, semantic search, hybrid search, BM25, time-series, migration\\n\"\n  postgres-database-migration\t\"Use this skill for planning, testing, and safely executing PostgreSQL schema migrations — especially when working with production data or shared databases.\\n\\n**Trigger when user asks to:**\\n- Test a schema migration before applying it to production\\n- Add, remove, or rename columns safely on a live table\\n- Change a column's data type without downtime\\n- Add or drop indexes, constraints, or foreign keys on large tables\\n- Understand which ALTER TABLE operations lock the table\\n- Roll back a failed migration\\n- Plan a zero-downtime migration strategy\\n- Fork a database to test a migration safely\\n\\n**Keywords:** migration, schema change, ALTER TABLE, add column, drop column, rename column, change type, zero downtime, lock, AccessExclusiveLock, concurrent index, forking, rollback, backfill, deploy\\n\\nCovers: lock-level reference for every common DDL operation, safe migration patterns, fork-based testing, zero-downtime column changes, index creation, constraint addition, backfill strategies, pre/post-migration validation, and rollback planning.\\n\"\n  postgres-hybrid-text-search\t\"Use this skill to implement hybrid search combining BM25 keyword search with semantic vector search using Reciprocal Rank Fusion (RRF).\\n\\n**Trigger when user asks to:**\\n- Combine keyword and semantic search\\n- Implement hybrid search or multi-modal retrieval\\n- Use BM25/pg_textsearch with pgvector together\\n- Implement RRF (Reciprocal Rank Fusion) for search\\n- Build search that handles both exact terms and meaning\\n\\n\\n**Keywords:** hybrid search, BM25, pg_textsearch, RRF, reciprocal rank fusion, keyword search, full-text search, reranking, cross-encoder\\n\\nCovers: pg_textsearch BM25 index setup, parallel query patterns, client-side RRF fusion (Python/TypeScript), weighting strategies, and optional ML reranking.\\n\"\n  schema-exploration\t\"Explore an existing PostgreSQL database before answering questions about its data or writing SQL. Use this skill whenever a user asks for a query or a data-backed answer against an unfamiliar schema (counts, missing or failed records, recent changes), asks where a business concept lives, or asks how tables, joins, views, routines, triggers, RLS, or extensions work. Find the relevant objects with read-only pg_catalog queries, then request approval before inspecting data-derived statistics or rows. Not a schema-design or migration guide.\\n\"\n  setup-timescaledb-hypertables\t\"Use this skill when creating database schemas or tables for Timescale, TimescaleDB, TigerData, or Tiger Cloud, especially for time-series, IoT, metrics, events, or log data. Use this to improve the performance of any insert-heavy table.\\n\\n**Trigger when user asks to:**\\n- Create or design SQL schemas/tables AND Timescale/TimescaleDB/TigerData/Tiger Cloud is available\\n- Set up hypertables, compression, retention policies, or continuous aggregates\\n- Configure partition columns, segment_by, order_by, or chunk intervals\\n- Optimize time-series database performance or storage\\n- Create tables for sensors, metrics, telemetry, events, or transaction logs\\n\\n**Keywords:** CREATE TABLE, hypertable, Timescale, TimescaleDB, time-series, IoT, metrics, sensor data, compression policy, continuous aggregates, columnstore, retention policy, chunk interval, segment_by, order_by\\n\\nStep-by-step instructions for hypertable creation, column selection, compression policies, retention, continuous aggregates, and indexes.\\n\"\n  timescaledb-hyperfunctions\t\"Use this skill when writing analytical SQL over time-series data with the TimescaleDB Toolkit (timescaledb_toolkit extension) hyperfunctions: approximate percentiles, statistical summaries, time-weighted averages, counter/gauge rates, uptime/heartbeat tracking, state durations, OHLC candlesticks, approximate distinct counts, top-N, and downsampling.\\n\\n**Trigger when user asks to:**\\n- Compute percentiles/medians/p95/p99 over large or rolled-up time-series data\\n- Compute rates or deltas from monotonic counters (Prometheus-style) or gauges\\n- Compute time-weighted averages or integrals over irregularly sampled data\\n- Track uptime/downtime from heartbeats, or time spent in each state\\n- Build OHLC/candlestick or VWAP data for financial ticks\\n- Store re-aggregatable summaries in continuous aggregates (two-step aggregation, rollup)\\n- Approximate COUNT DISTINCT, find top-N / most frequent values, or downsample for charts\\n\\n**Keywords:** timescaledb_toolkit, hyperfunctions, percentile_agg, uddsketch, tdigest, approx_percentile, stats_agg, time_weight, counter_agg, gauge_agg, heartbeat_agg, state_agg, candlestick_agg, hyperloglog, approx_count_distinct, min_n, max_n, mcv_agg, lttb, asap_smooth, rollup, two-step aggregation\\n\"\n</available_skills>","annotations":{"title":"View Skill","readOnlyHint":true,"idempotentHint":true},"source":"probe","observed_at":"2026-10-09T22:36:21.752Z"}],"dev.protogrid/trust":{"score":90,"components":{"hygiene":95,"liveness":100,"freshness":85,"provenance":80},"drivers":["+repository","+namespace-matches-repo","+reachable","+uptime-100%","~updated-35d-ago","-unpinned-packages(1)"],"flags":[],"blocked":false,"computed_at":"2026-10-10T02:23:34.570Z","disclaimer":"Derived from observable signals (official registry feed and our own credential-free probes); no code audit performed."},"dev.protogrid/quality":{"score":76,"label":"good","components":{"auth":null,"hygiene":79,"protocol":75,"stability":100,"dependencies":50},"checks":[{"id":"protocol.modern","detail":"newest supported version is 2025-11-25; 2026-07-28 not supported, older versions are deprecated until 2027-07-28","status":"warn","category":"protocol"},{"id":"protocol.stateless","detail":"only applies to 2026-07-28 servers","status":"na","category":"protocol"},{"id":"protocol.transport","detail":"streamable HTTP","status":"pass","category":"protocol"},{"id":"protocol.list_ttl","detail":"only applies to 2026-07-28 servers","status":"na","category":"protocol"},{"id":"auth.prm","detail":"no remote uses OAuth","status":"na","category":"auth"},{"id":"auth.as_metadata","detail":"no remote uses OAuth","status":"na","category":"auth"},{"id":"auth.cimd","detail":"no remote uses OAuth","status":"na","category":"auth"},{"id":"auth.secret_in_url","detail":"no templated URL","status":"na","category":"auth"},{"id":"tools.descriptions","detail":"every tool has a description","status":"pass","category":"hygiene"},{"id":"tools.description_length","detail":"1 tools have descriptions over 1,024 characters, which crowds the context","status":"warn","category":"hygiene"},{"id":"tools.schemas","detail":"every tool has an object input schema","status":"pass","category":"hygiene"},{"id":"tools.annotations","detail":"2 of 2 tools declare readOnlyHint or destructiveHint","status":"pass","category":"hygiene"},{"id":"tools.directory_hints","detail":"no tool declares all four of readOnlyHint, destructiveHint, idempotentHint and openWorldHint; missing: search_docs (destructiveHint, openWorldHint); view_skill (destructiveHint, openWorldHint)","status":"fail","category":"hygiene"},{"id":"tools.token_cost","detail":"about 2,915 tokens to load every tool","status":"pass","category":"hygiene"},{"id":"tools.api_dump","detail":"tools are not a one-to-one API dump","status":"pass","category":"hygiene"},{"id":"stability.changes","detail":"2 tool changes in 30 days","status":"pass","category":"stability"},{"id":"stability.rug_pull","detail":"no tool changed its meaning under the same name","status":"pass","category":"stability"},{"id":"deps.known_vulns","detail":"1 advisory of lower severity or without a published fix: GHSA-hp3w-g68c-fv3c in sprintf-js@1.0.3 (indirect, no fix yet)","status":"warn","category":"dependencies"},{"id":"deps.mcp_sdk_version","detail":"@modelcontextprotocol/sdk 1.30.1 has 1 known advisory (GHSA-6qxp-vccf-f47h); update @modelcontextprotocol/sdk to 1.31.0 or later","status":"fail","category":"dependencies"},{"id":"deps.resolvable","detail":"@tigerdata/pg-aiguide@0.6.1 resolved: 325 packages","status":"pass","category":"dependencies"}],"drivers":["-tools.directory_hints","-deps.mcp_sdk_version","~protocol.modern","~tools.description_length","~deps.known_vulns"],"flags":["outdated-mcp-sdk"],"tool_count":2,"token_estimate":2915,"tools_hash":"e7030fb5565a6c6ebd3a763fa4be618c","tools_changed_at":"2026-10-07T23:56:34.227Z","computed_at":"2026-10-10T02:27:51.671Z","disclaimer":"Checks run on what our credential-free, read-only probes observe; tools are never called and no code audit is performed."},"dev.protogrid/identity":{"canonical_id":"io.github.timescale/pg-aiguide","aliases":[],"alias_count":0,"repository_key":"github:timescale/pg-aiguide","owner":{"verified":false,"method":null,"since":null}}},"next_actions":[{"action":"list_tools","description":"Every current tool with input schemas","href":"/v1/servers/io.github.timescale%2Fpg-aiguide/tools","arguments":{"name":"io.github.timescale/pg-aiguide"}},{"action":"get_connection","description":"Minimal connection block for the preferred remote or package","href":"/v1/servers/io.github.timescale%2Fpg-aiguide/connection?target=mcpServers","arguments":{"name":"io.github.timescale/pg-aiguide","target":"mcpServers"}},{"action":"search","description":"Find other servers by intent","href":"/v1/search?q={query}","arguments":{"q":"{query}"}}]}