redshift-comment-mcp

English · 日本語 · 繁體中文

A read-only Model Context Protocol server for Amazon Redshift, plus a Claude Code plugin with 6 slash-command skills built on top. Designed around one assertion: column names lie, comments don't — so the server exposes comments aggressively and the skills compose those tools into the discovery workflows you actually do every day.

"What values does dbt_marts.fct_orders.status really hold?"
   → /redshift-profile dbt_marts.fct_orders status
   → cardinality, top-N, null rate, min/max, existing comment — one round.

Why this exists

If you've ever opened an unfamiliar Redshift table and squinted at column names like f3, legacy_id_v2, or status (which status?), you already know the pain. dbt manifests are too narrow. Web GUIs are too slow. Hand-written SQL is too repetitive.

This plugin's charter is Guided Data Discovery:

• Comments first. Every list / search tool returns the column, table, or schema comment when asked — names are advisory, comments are authoritative.
• Read-only by construction. execute_sql rejects DDL / DML at the parse layer; no skill in this repo can mutate Redshift.
• MCP-composed skills. New workflows are built by stringing together existing tools, not by adding new database connections.
• No persistence. No synthesis layer, no .redshift-wiki/ markdown, no stale tracking. Persistence belongs in a separate plugin.

See implementation_guide.md §1.2 for the full charter.

What you get

MCP tools (13, defined in src/redshift_comment_mcp/)

Group	Tools
List	list_schemas · list_tables · list_columns
Search (hit-count ranked)	search_schemas · search_tables · search_columns
Comment retrieval	get_schema_comment · get_table_comment · get_column_comment · get_all_column_comments
Query	execute_sql (SELECT / WITH only)
Setup (since v0.7.0)	setup_via_dialog — in-band profile bootstrap; OS-native password dialog, never crosses MCP wire; tests connection before declaring success · get_setup_status — read-only configuration check; safe to call at session start; never returns password value

Pagination on every list / search; explicit WARNING strings nudging the LLM to read comments before trusting names.

Slash-command skills (6, defined in skills/)

Skill	One-liner	Since
/redshift-setup	Conversational walk-through to configure a connection profile.	v0.2.0
/redshift-switch-profile	Switch the active profile (no host / user / password re-entry); single-profile users get a friendly bow-out.	v0.4.0
/redshift-profile	Profile a column: cardinality / top-N / null rate / min-max / existing comment, one round.	v0.3.0
/redshift-explore	Three-step interactive wizard (schema → table → column) — pick by reading comments.	v0.3.0
/redshift-lineage-from-stl	Mine STL_QUERY + sqlglot to reconstruct actual table-to-table lineage from query history.	v0.3.0
/redshift-grep-columns	Cross-table column search by keyword across one or all schemas via schema-wide MCP call.	v0.4.0
/redshift-grep-tables	Cross-schema table search by keyword across all schemas via cluster-wide MCP call.	v0.4.0

Each skill has its own tri-lingual README inside its folder (except /redshift-setup and /redshift-switch-profile, which are setup-style internals — see SKILL.md directly).

Quick start

The fastest path is the Claude Code plugin.

# 1. Register the marketplace (one-time)
claude plugin marketplace add kouko/redshift-comment-mcp

# 2. Install the plugin (ships DISABLED — opt-in, since it connects to an external service)
claude plugin install redshift-comment-mcp

# 3. Enable it — this is when the connection dialog appears
claude plugin enable redshift-comment-mcp

When you enable the plugin, Claude Code shows a connection dialog asking for host / port (default 5439) / user / dbname / password. There are two ways to connect — pick one; both store the password in an OS keychain, never in chat or settings.json:

• Quick single connection — fill the dialog. The password field is sensitive, so it goes to the OS keychain; host / port / user / dbname are saved to settings.json. That's all you need for one cluster.
• Profiles / multi-cluster — leave the dialog blank and run /redshift-setup. The fields are optional; leave them blank and the plugin falls back to the profile flow. /redshift-setup is the conversational walk-through that writes a named profile (config.toml + active-profile pointer + keychain), and it's the path for running more than one cluster.

# In a Claude Code chat — the profile path (also the multi-cluster path)
/redshift-setup

/redshift-setup walks you through host / port / user / dbname / password. The password is collected in a system dialog (macOS) or a zenity prompt (Linux desktop) or your own terminal (headless) — never in chat. It lands directly in your OS keychain.

After connecting, just type any of the slash commands above. Multi-cluster? Add a second profile with /redshift-setup <name>, then switch between them with /redshift-switch-profile.

For Claude Desktop / other MCP clients / local development, scroll down to Other install paths.

Other install paths

Two packaged artifacts — install ONE of them:

• Claude Code plugin — gives the 6 slash-command skills plus the MCP server, configured via /redshift-setup. This is the Quick start above and the recommended path for Claude Code users.
• .mcpb Desktop Extension — gives the MCP server plus a GUI config form in Claude Desktop, but NOT the skills. This is the path for Claude Desktop users who want a one-click install and a connection form instead of editing claude_desktop_config.json by hand.

Scenario	How
Claude Code (recommended)	claude plugin install redshift-comment-mcp (above), then enable it — Claude Code pops a connection dialog (host / port / user / dbname / password; password → OS keychain). Fill it for one quick connection, or leave it blank and run /redshift-setup instead.
Claude Desktop (one-click)	Download the .mcpb from the project's GitHub Releases page, then install it in Claude Desktop (Settings → Extensions, or drag-and-drop the file). Claude Desktop shows a connection form — fill in host / port / user / dbname / password (the password is stored in the OS keychain). Requires uv (see prerequisite below). Ships the server + form, not the skills.
Claude Desktop / generic MCP client (manual)	pip install redshift-comment-mcp then point your client at uvx redshift-comment-mcp (or --profile <name> to override the pointer file)
Local development	git clone … && pip install -e ".[dev]" then python -m redshift_comment_mcp.server
Multi-cluster	/redshift-setup <name> per cluster + /redshift-switch-profile to switch

.mcpb prerequisite — uv. The Desktop Extension runs on the uv runtime, so uv must be available on your machine before you install the .mcpb (e.g. brew install uv on macOS). The Claude Code plugin path does not need this step — Claude Code provides its own uv.

The plugin runs from the cloned repo source via uv run --project ${CLAUDE_PLUGIN_ROOT} — PyPI release is NOT a prerequisite for plugin updates.

Setting up with uvx — Claude Desktop / generic MCP client

The Claude Code plugin's uv run --project ${CLAUDE_PLUGIN_ROOT} form is specific to Claude Code. For any other MCP client (Claude Desktop, generic stdio MCP clients), the equivalent launch is uvx redshift-comment-mcp against the PyPI release.

Step 1 — set up a profile via the CLI. redshift-comment-mcp ships the same Q&A flow that the Claude Code plugin's /redshift-setup uses, exposed as subcommands:

# interactive Q&A — writes config.toml + stores password in OS keychain
uvx redshift-comment-mcp setup

# or a named profile (for multi-cluster setups)
uvx redshift-comment-mcp setup --profile prod

# verify
uvx redshift-comment-mcp test-connection --profile prod
uvx redshift-comment-mcp list-profiles

The files it writes — ~/.config/redshift-comment-mcp/config.toml + OS keychain entry under service redshift-comment-mcp — are per-user, not per-client. Run setup once and every MCP client that launches uvx redshift-comment-mcp afterwards reads the same profile data. If you already have Claude Code with the plugin, /redshift-setup writes the exact same files; no duplicate setup needed.

Other useful subcommands: set-password, delete-profile. See uvx redshift-comment-mcp --help.

For code-agent bootstrap (any MCP client, since v0.7.0): the preferred path is the in-band MCP tool setup_via_dialog — no Bash tool, no MCP client restart, password stays out of chat:

agent calls any DB tool          → {"error": "not_configured", "next_step": "Call setup_via_dialog..."}
agent asks user for host/user/dbname  (these are NOT secrets)
agent calls MCP tool setup_via_dialog(host=..., user=..., dbname=...)
                                 → server-side spawns OS dialog (macOS osascript / Linux zenity)
                                 → user types password directly into dialog
                                 → server writes config.toml + keychain
                                 → {"status": "configured", ...}
agent retries the DB tool        → works (lazy resolve; no restart needed)

The server boots in degraded mode even when no profile exists — DB tools return a structured not_configured error pointing at setup_via_dialog, so the agent sees the recovery path in its own tool-call result (no need to read MCP client log files). After setup, lazy resolution picks up the new profile on the next tool call.

Fallback for headless / non-GUI hosts (no osascript / no zenity): drop to the Bash + CLI path which uses --stdin instead of the dialog:

uvx redshift-comment-mcp set-fields --profile default \
    --host H --port P --user U --dbname D
echo "$PASSWORD" | uvx redshift-comment-mcp set-password \
    --profile default --stdin

Step 2 — single profile. In claude_desktop_config.json (or your client's equivalent):

{
  "mcpServers": {
    "redshift-comment": {
      "command": "uvx",
      "args": ["redshift-comment-mcp"]
    }
  }
}

The server resolves which profile to use via this chain (most explicit wins): --profile CLI flag > REDSHIFT_COMMENT_PROFILE env var > active-profile pointer file > implicit fallback (lone profile rescue / default).

Step 3 — multi-cluster. One MCP server entry per profile, override the pointer file via --profile:

{
  "mcpServers": {
    "redshift-prod": {
      "command": "uvx",
      "args": ["redshift-comment-mcp", "--profile", "prod"]
    },
    "redshift-stg": {
      "command": "uvx",
      "args": ["redshift-comment-mcp", "--profile", "stg"]
    }
  }
}

Each entry runs as a separate MCP server; tools appear in the client under their respective server names.

Tip — uv tool install for faster startup. uvx fetches and spawns on every invocation (~2s after the first cache warmup). If you'd rather pay the install cost once:

uv tool install redshift-comment-mcp

then point "command" at redshift-comment-mcp directly with no uvx wrapper.

Where things live

.
├── README.md / README.ja.md / README.zh-TW.md     (this file, tri-lingual)
├── implementation_guide.md                         design rationale + charter
├── src/redshift_comment_mcp/                       MCP server source — see its own README
├── skills/                                         5 slash-command skills — see its own README
├── commands/                                       plugin slash command stubs
├── tests/                                          pytest suite
├── pyproject.toml                                  packaging metadata
└── .claude-plugin/                                 plugin manifest + marketplace

The two READMEs to read next:

• skills/README.md — overview of all 5 skills
• src/redshift_comment_mcp/README.md — server internals, module map, charter constraints

Data layout at runtime

Path	Contents	Permissions
~/.config/redshift-comment-mcp/config.toml	Non-secret profile fields	0600
~/.config/redshift-comment-mcp/active-profile	One-line pointer to the active profile name. Absent ↔ server uses default (canonical single-profile state — most users never see this file).	0600
OS keychain (redshift-comment-mcp / <profile>)	Passwords	OS-managed

Recommended DB GRANTs (defense-in-depth)

execute_sql blocks DDL / DML / admin keywords at the parser layer (DROP / DELETE / UPDATE / INSERT / ALTER / CREATE / TRUNCATE / MERGE / GRANT / REVOKE / COPY / UNLOAD), but that's a layer-1 defense. The defense-in-depth move is to give the plugin's connecting Redshift user read-only privileges only, so even if a parser bypass is found, the database itself rejects writes:

-- Create a dedicated read-only user for the plugin
CREATE USER redshift_mcp_reader WITH PASSWORD '...';

-- Grant only what the plugin actually needs
GRANT USAGE ON SCHEMA public, dbt_marts, dbt_staging TO redshift_mcp_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public, dbt_marts, dbt_staging TO redshift_mcp_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public, dbt_marts, dbt_staging
  GRANT SELECT ON TABLES TO redshift_mcp_reader;

-- Do NOT grant: INSERT / UPDATE / DELETE / TRUNCATE / DROP / CREATE / GRANT / superuser

For /redshift-lineage-from-stl, the user additionally needs SYSLOG ACCESS UNRESTRICTED (or admin) to read STL_QUERY / SYS_QUERY_HISTORY. If you're not running that skill, skip this grant.

Known limits

MCP response token cap (~25K tokens default) — Claude Code silently truncates MCP tool results above ~25,000 tokens (no error, no marker; see anthropics/claude-code#2638). For dbt-rich schemas where column comments are long markdown blocks, a single list_columns(include_comments=True) page (50 rows) on a wide table can approach this. Two mitigations the plugin already applies:

• include_comments defaults to False on list_tables / list_columns (only list_schemas defaults True since schema count is small) — agent must opt in to comment-loaded responses.
• MAX_COMMENT_LEN=1000 caps each comment in multi-item responses (with comment_truncated_count + ellipsis marker). Single-item getters (get_table_comment / get_column_comment) never truncate.

If you still need to bump the cap (e.g. to fetch a heavily-documented column set in one shot), set MAX_MCP_OUTPUT_TOKENS=50000 in the environment where Claude Code runs. This affects all MCP servers in that session, not just this one.

Comment-writing tips for your DB

The plugin shines brightest on tables whose owners invest in comments. Concrete tips (Chinese examples — adapt to your team's language):

COMMENT ON SCHEMA   sales        IS '[用途] 線上零售銷售數據 [主要實體] 訂單, 客戶, 產品';
COMMENT ON TABLE    sales.orders IS '[實體] 訂單 [PK] order_id [FK] customer_id → customers.customer_id';
COMMENT ON COLUMN   sales.orders.revenue IS '[定義] 訂單總銷售額 [語意類型] Metric [單位] 新台幣 [計算] 未稅商品總價 + 稅 − 折扣';

A more thorough Semantic Layer guide is in implementation_guide.md Appendix A.

Development

pytest tests/                    # run unit + invariant tests (fast, no live cluster)
REDSHIFT_INTEGRATION=1 \
  pytest tests/integration/      # opt-in: smoke-test against the active Redshift profile
python -m build                  # build sdist + wheel

The tests/integration/ smoke gate exercises every MCP tool against a real Redshift cluster — connection, list / search / get tools, pagination, the SQL-safety guard. It skips cleanly when REDSHIFT_INTEGRATION is unset, when no active profile is configured, or when the keychain entry is missing, so default pytest tests/ is safe in CI.

CI / release flow lives in .github/.

License

MIT

Contributing

Issues and pull requests welcome. New skills should follow the patterns documented in skills/README.md: read-only, MCP-composed, no direct DB connections, no synthesis layer. SKILL.md ≤ 130 lines, tri-lingual README, audited via dev-workflow:skill-judge before commit.