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.

Related MCP server: redshift-utils-mcp

What you get

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

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

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

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. Every field is optional, and what you leave blank decides which of three paths you get. All of them keep the password in an OS keychain, never in chat or settings.json:

• Fill everything — quick single connection. 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.

• Fill host / port / user / dbname, leave password blank — borrow a stored profile's password. The server looks for a profile written by /redshift-setup whose host, port, user and dbname all match what you typed here, and the connection always goes to the target you typed — a profile only ever lends its password, never its own host or port. If none matches, the connection refuses, naming the target you typed and each existing profile's target, so you can create a matching profile with /redshift-setup or fill in the password yourself. If more than one matches, the connection refuses the same way, naming every tied candidate — the shape a password rotation leaves behind (the old and new profile, still recorded for the same target) — so you can delete or rename the stale one.

• Leave every field blank — the profile path. The plugin falls back entirely to /redshift-setup's profile flow. /redshift-setup is the conversational walk-through that writes a named profile (config.toml + active-profile pointer + keychain), and it's also 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.

.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

config.toml is machine-managed — read this before hand-editing it

config.toml is written only by this project's own tools: the setup / set-fields / delete-profile subcommands, the /redshift-setup skill, and the setup_via_dialog MCP tool. (set-password and /redshift-switch-profile don't touch it — they write the OS keychain and the active-profile pointer respectively.) Every one of those writers rewrites the whole file from the profiles it just read, so anything the project doesn't recognise — your comments, your blank-line grouping, extra keys — is dropped on the next write. That's the designed behaviour, not a bug: the file is a store this project owns, not a config file meant to be authored by hand.

Hand-editing still works (it's plain TOML, and the schema below is stable) — just expect only the recognised keys to survive, and keep anything you'd want to remember somewhere else. A write of one profile never disturbs another profile's fields.

[profile.prod]
host = "my-cluster.abc123.us-east-1.redshift.amazonaws.com"
port = 5439
user = "alice"
dbname = "analytics"

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.