Substack Newsletter Discovery MCP Server v1.4.1

🔍 Unofficial Tool for Substack Newsletter Research & Discovery
🧠 NEW: Auto-Schema Loading for Immediate Query Intelligence
🚀 NEW: Semantic Search with Natural Language Understanding

Connect Claude Desktop to explore publicly collected Substack newsletter data through natural language queries. Claude automatically learns your database structure on startup for instant intelligent queries without discovery delays.

⚠️ Important Disclaimers

• 🚫 Not Official: This is an unofficial tool, not affiliated with or endorsed by Substack
• 📊 Public Data: Uses publicly available newsletter information collected from open sources
• 🔬 Research Purpose: Designed for newsletter discovery, research, and ecosystem understanding
• 🤝 Respectful Use: Please use responsibly - data is sourced from public information and should be used to support and discover creators in the Substack ecosystem
• ⚖️ No Commercial Exploitation: Not intended for data manipulation, unauthorized commercial use, or activities that harm creators

🚀 Quick Start

Option A: Environment Variables (Recommended - 30 seconds)

Step 1: Generate API token from your dashboard at https://substackexplorer.com/dashboard

Step 2: Add to your Claude Desktop config (~/.config/Claude Desktop/claude_desktop_config.json):

{
  "mcpServers": {
    "substack-newsletter-mcp": {
      "command": "npx",
      "args": ["substack-newsletter-mcp"],
      "env": {
        "NEWSLETTER_MCP_TOKEN": "your-api-token-here",
        "NEWSLETTER_MCP_URL": "https://substackexplorer.com"
      }
    }
  }
}

💡 You can also copy this from the included claude-desktop-config.json example file

Step 3: Restart Claude Desktop - ready to use immediately with auto-schema loading!

🧠 Auto-Schema Loading: Claude immediately knows your newsletter database structure (newsletters, keywords, relationships) for instant intelligent queries without discovery delays.

Option B: Interactive Setup (Legacy - 2 minutes)

If you prefer the interactive setup wizard:

npx substack-newsletter-mcp

The setup wizard will guide you through token configuration.

🎯 What You Can Discover

🧠 Auto-Schema Loading Active: Claude immediately understands your newsletter database structure and can suggest intelligent queries from the first interaction!

Ask Claude Desktop to help you explore the Substack ecosystem:

Newsletter Discovery & Analytics

• "Show me the top 10 newsletters by subscriber count"
• "Find newsletters with podcasts from the last month"
• "Which keywords are associated with the most newsletters?"
• "What newsletters have the highest tier ratings?"
• "Show me newsletters created in the last 30 days"
• "Find all newsletters by a specific author"

🚀 NEW: Semantic Search (v1.4.0)

• "Find productivity newsletters for entrepreneurs"
• "Show me AI and machine learning content"
• "Discover health and wellness newsletters"
• "Search for newsletters about startup funding"
• "Find content about personal development"
• "Show me newsletters similar to tech topics"

Advanced Queries

• "Compare newsletter performance by tier level"
• "Show me newsletter-keyword relationships with details"
• "Which keywords were refreshed most recently?"
• "Export top newsletters to CSV for analysis"

Data Export

• "Export all newsletter data to CSV"
• "Give me a user analytics report"
• "Create a monthly performance summary"

🔧 Architecture

Claude Desktop ←→ NPX MCP Server (local) ←→ Your Hosted App ←→ Database

• Secure: No direct database access - only through your hosted service
• Native: Uses Claude Desktop's preferred stdio transport
• Zero Setup: No Python, no complex configuration, just NPX

🛠️ Features

✅ Natural Language to SQL: Ask questions in plain English
✅ 🚀 Semantic Search: Find newsletters by meaning, not just keywords
✅ 🧠 Auto-Schema Loading: Instant database structure awareness
✅ Schema Discovery: Automatic database structure detection
✅ Safe Queries: Only SELECT operations allowed
✅ CSV Export: Large datasets automatically exported
✅ Error Handling: Helpful error messages and troubleshooting
✅ Secure Auth: Bearer token authentication

📖 Advanced Usage

Configuration Options

Environment Variables (Recommended):

• NEWSLETTER_MCP_TOKEN: Your API token from the dashboard
• NEWSLETTER_MCP_URL: Server URL (default: https://substackexplorer.com)

Local Config File (Legacy): Configuration stored in ~/.newsletter-mcp/config.json

Management Commands

Reset Configuration:

npx substack-newsletter-mcp --reset

Debug Mode:

DEBUG=substack-newsletter-mcp* npx substack-newsletter-mcp

Token Management

Update Environment Variable Token:

1. Generate new token from dashboard
2. Update NEWSLETTER_MCP_TOKEN in your Claude Desktop config
3. Restart Claude Desktop

Update Local Config Token:

npx substack-newsletter-mcp --reset

🔒 Security

• No Direct Database Access: All queries go through your hosted service
• Token-Based Auth: Secure Bearer token authentication
• Local Storage: Configuration stored securely in ~/.newsletter-mcp/
• Read-Only: Only SELECT queries allowed, no data modification

📁 Configuration

Configuration is stored in ~/.newsletter-mcp/config.json:

{
  "serverUrl": "https://substackexplorer.com",
  "apiToken": "your-secure-token-here",
  "setupDate": "2024-01-15T10:30:00.000Z"
}

🐛 Troubleshooting

Connection Issues

• Check your internet connection
• Verify your hosted app is running
• Ensure your API token is valid

Authentication Errors

• Generate a new token from your dashboard
• Run npx substack-newsletter-mcp --reset to reconfigure

Claude Desktop Not Connecting

• Make sure Claude Desktop is updated
• Check that no other MCP servers are running on stdio
• Restart Claude Desktop after starting the MCP server

🚀 How It Works

1. NPX Command: Runs the local MCP server
2. Stdio Transport: Claude Desktop connects via stdin/stdout
3. API Proxy: All MCP requests forwarded to your hosted service
4. Hosted Processing: Your service handles database queries safely
5. Response: Results formatted and returned to Claude Desktop

📊 Example Queries & Results

Query: "Show me newsletter growth trends"

Result:

📊 Newsletter Growth Analysis

| Month    | New Newsletters | Total Subscribers | Growth Rate |
|----------|----------------|------------------|-------------|
| Jan 2024 | 45             | 125,430          | +12.3%      |
| Feb 2024 | 52             | 139,820          | +11.5%      |
| Mar 2024 | 38             | 148,950          | +6.5%       |

🔗 CSV exported with 247 detailed records for further analysis.

📝 Changelog

v1.4.1 (Latest) - Critical Bug Fix

🔧 Bug Fix Release

• FIXED: Critical semantic search bug where results weren't displaying in Claude Desktop
• FIXED: Incorrect property access (result.results → result.rawResults)
• IMPROVED: Semantic search now returns actual results instead of "Found: 0 newsletters"

v1.4.0 - Semantic Search Release

🚀 Major Feature Release

• NEW: Semantic search with natural language understanding
• NEW: Query expansion with synonym mapping
• NEW: Full-text search with PostgreSQL tsvector
• NEW: Relevance scoring and highlighting
• ENHANCED: Database now includes 153,916+ newsletters
• IMPROVED: User data restoration and keyword associations
• OPTIMIZED: Search performance with indexed vectors

v1.3.0 - Auto-Schema Loading

🧠 Intelligence Update

• NEW: Auto-schema loading for immediate query awareness
• IMPROVED: Faster query response times
• ENHANCED: Better error messages and troubleshooting

v1.2.0 - Foundation Release

📊 Core Features

• Newsletter database analytics
• Natural language to SQL conversion
• CSV export capabilities
• Secure token authentication

🤝 Contributing

Found a bug or have a feature request?

1. Check existing issues
2. Create a new issue with details
3. Submit a pull request

⚖️ Legal & Ethical Use

Data Sources & Attribution

• Public Data: All newsletter information is collected from publicly available sources and APIs
• Substack Credit: All data originated from the Substack platform - this tool supports newsletter discovery within their ecosystem
• No Data Theft: No private or proprietary data is accessed, stored, or distributed
• Creator Support: This tool is designed to help users discover and support newsletter creators

Acceptable Use

✅ Encouraged Uses:

• Discovering new newsletters to subscribe to
• Academic research on newsletter ecosystems
• Market research for understanding content trends
• Finding newsletters in your areas of interest

❌ Prohibited Uses:

• Bulk harvesting of creator contact information
• Automated spam or unsolicited outreach to creators
• Commercial data reselling or redistribution
• Any activity that harms or exploits newsletter creators

Disclaimer

This is an independent research tool created to support newsletter discovery and ecosystem understanding. It is not affiliated with, endorsed by, or connected to Substack, Inc. All trademarks and platform names are property of their respective owners.

By using this tool, you agree to respect creator privacy, support the newsletter ecosystem, and use the data responsibly for its intended discovery and research purposes.

📄 License

MIT License - see LICENSE file for details.

Built with ❤️ for the Claude Desktop community to discover amazing newsletters on Substack

Supporting creators through intelligent discovery

Need help? Contact support through the dashboard.