Semantic Perch Intelligence MCP

PerchIQX - Deep Insights. Database Intelligence.

A Model Context Protocol (MCP) server for Cloudflare D1 database introspection. Reference implementation of Semantic Intent patterns demonstrating semantic anchoring, observable properties, and domain-driven design for AI-assisted database development.

🎯 What Makes This Different

This isn't just another database introspection tool—it's a reference implementation of proven semantic intent patterns:

✅ Semantic Anchoring: Schema analysis based on meaning (table purpose, relationships), not technical metrics (row counts, sizes)
✅ Observable Properties: Decisions anchored to directly observable schema markers (foreign keys, indexes, constraints)
✅ Intent Preservation: Database semantics maintained through all transformations (development → staging → production)
✅ Domain Boundaries: Clear semantic ownership (Schema Domain ≠ Query Optimization Domain ≠ MCP Protocol Domain)

Built on research from Semantic Intent as Single Source of Truth, this implementation demonstrates how to build maintainable, AI-friendly database tools that preserve intent.

🚀 Quick Start

Prerequisites

Node.js 20.x or higher
Cloudflare account with D1 databases
Cloudflare API token with D1 access

Installation

Clone the repository

git clone https://github.com/semanticintent/semantic-perch-intelligence-mcp.git
cd semantic-perch-intelligence-mcp

Install dependencies
```
npm install
```

Configure environment

Copy the example configuration:

cp .env.example .env

Update .env with your Cloudflare credentials:

# Cloudflare Configuration
CLOUDFLARE_ACCOUNT_ID=your_cloudflare_account_id
CLOUDFLARE_API_TOKEN=your_cloudflare_api_token

# D1 Database Configuration - Development
D1_DEV_DATABASE_ID=your_dev_database_id
D1_DEV_DATABASE_NAME=your_dev_database_name

# D1 Database Configuration - Staging (Optional)
D1_STAGING_DATABASE_ID=your_staging_database_id
D1_STAGING_DATABASE_NAME=your_staging_database_name

# D1 Database Configuration - Production (Optional)
D1_PROD_DATABASE_ID=your_prod_database_id
D1_PROD_DATABASE_NAME=your_prod_database_name

Note: At least one database environment must be configured.

Build the server
```
npm run build
```
Start the MCP server
```
npm start
```
Or use the provided shell script:
```
./start-d1-mcp.sh
```

Get Cloudflare API Token

Go to Cloudflare Dashboard
Navigate to My Profile → API Tokens
Click Create Token
Use the Edit Cloudflare Workers template
Add D1 permissions: D1:Read
Copy the token to your .env file

Get D1 Database IDs

# List all your D1 databases
wrangler d1 list

# Get specific database info
wrangler d1 info <database-name>

Copy the database IDs to your .env file.

🛠️ MCP Tools

This server provides 4 comprehensive MCP tools for D1 database introspection:

1. analyze_database_schema

Analyze complete database schema structure with metadata and optional sample data.

Parameters:

environment (required): "development" | "staging" | "production"
includeSamples (optional, default: true): Include sample data from tables
maxSampleRows (optional, default: 5): Maximum rows per table sample

Returns:

Complete schema analysis
Table structures with columns, types, constraints
Indexes and foreign keys
Sample data from each table (if enabled)
Schema metadata and statistics

Example:

{
  "name": "analyze_database_schema",
  "arguments": {
    "environment": "development",
    "includeSamples": true,
    "maxSampleRows": 5
  }
}

2. get_table_relationships

Extract and analyze foreign key relationships between tables.

Parameters:

environment (required): Database environment
tableName (optional): Filter relationships for specific table

Returns:

Foreign key relationships with cardinality (one-to-many, many-to-one)
Referential integrity rules (CASCADE, SET NULL, etc.)
Relationship metadata and statistics

Example:

{
  "name": "get_table_relationships",
  "arguments": {
    "environment": "production",
    "tableName": "users"
  }
}

3. validate_database_schema

Validate database schema for common issues and anti-patterns.

Parameters:

environment (required): Database environment

Returns:

Schema validation results
Missing primary keys
Foreign keys without indexes
Naming convention violations
Tables without relationships

Example:

{
  "name": "validate_database_schema",
  "arguments": {
    "environment": "production"
  }
}

4. suggest_database_optimizations

Generate schema optimization recommendations based on structure analysis.

Parameters:

environment (required): Database environment

Returns:

Prioritized optimization suggestions (high/medium/low)
Missing index recommendations
Primary key suggestions
Schema improvement opportunities
Performance optimization tips

Example:

{
  "name": "suggest_database_optimizations",
  "arguments": {
    "environment": "production"
  }
}

🔌 Connect to Claude Desktop

Connect this MCP server to Claude Desktop for AI-assisted database development.

Configuration

Edit Claude Desktop config - Go to Settings → Developer → Edit Config
Add MCP server configuration:

{
  "mcpServers": {
    "semantic-perch": {
      "command": "node",
      "args": [
        "/absolute/path/to/semantic-perch-intelligence-mcp/dist/index.js"
      ],
      "env": {
        "CLOUDFLARE_ACCOUNT_ID": "your_account_id",
        "CLOUDFLARE_API_TOKEN": "your_api_token",
        "D1_DEV_DATABASE_ID": "your_dev_db_id",
        "D1_DEV_DATABASE_NAME": "your_dev_db_name",
        "D1_STAGING_DATABASE_ID": "your_staging_db_id",
        "D1_STAGING_DATABASE_NAME": "your_staging_db_name",
        "D1_PROD_DATABASE_ID": "your_prod_db_id",
        "D1_PROD_DATABASE_NAME": "your_prod_db_name"
      }
    }
  }
}

Restart Claude Desktop
Verify tools are available - You should see 4 D1 tools in Claude's tool list

Usage Example

In Claude Desktop:

"Analyze my production database schema and suggest optimizations for tables with foreign keys"

Claude will use the analyze_database_schema and suggest_database_optimizations tools automatically.

🏗️ Architecture

This project demonstrates Domain-Driven Hexagonal Architecture with clean separation of concerns:

┌─────────────────────────────────────────────────────────┐
│                   Presentation Layer                     │
│              (MCP Server - Protocol Handling)            │
└────────────────────┬────────────────────────────────────┘
                     │
┌────────────────────▼────────────────────────────────────┐
│                  Application Layer                       │
│        (Use Cases - Schema Analysis Orchestration)      │
└────────────────────┬────────────────────────────────────┘
                     │
┌────────────────────▼────────────────────────────────────┐
│                    Domain Layer                          │
│     (Schema Entities, Relationship Logic, Services)     │
│              Pure Business Logic                         │
└────────────────────┬────────────────────────────────────┘
                     │
┌────────────────────▼────────────────────────────────────┐
│                Infrastructure Layer                      │
│       (Cloudflare D1 REST API, HTTP Client)             │
│           Technical Adapters                             │
└─────────────────────────────────────────────────────────┘

Implementation Status

Status: ✅ Hexagonal architecture refactoring complete

Current Structure:

src/
├── domain/              # Business logic (entities, services)
│   ├── entities/        # DatabaseSchema, TableInfo, Column, etc.
│   ├── services/        # SchemaAnalyzer, RelationshipAnalyzer, etc.
│   ├── repositories/    # Port interfaces
│   └── value-objects/   # Environment enum
├── application/         # Use cases and orchestration
│   ├── use-cases/       # AnalyzeSchema, GetRelationships, etc.
│   └── ports/           # Cache provider interface
├── infrastructure/      # External adapters
│   ├── adapters/        # CloudflareD1Repository, Cache
│   ├── config/          # CloudflareConfig, DatabaseConfig
│   └── http/            # CloudflareAPIClient
├── presentation/        # MCP protocol layer
│   └── mcp/             # D1DatabaseMCPServer
└── index.ts             # Composition root (DI)

See ARCHITECTURE.md for detailed design documentation.

Layer Responsibilities

Domain Layer:

Database schema entities (Schema, Table, Relationship, Index)
Schema analysis business logic
Relationship extraction logic
Optimization recommendation rules

Application Layer:

Orchestrate domain services
Execute use cases (AnalyzeSchema, GetRelationships, etc.)
Coordinate infrastructure adapters

Infrastructure Layer:

Cloudflare D1 REST API integration
HTTP client for API calls
Cache provider (in-memory)

Presentation Layer:

MCP server initialization
Tool registration and routing
Request/response formatting

Semantic Intent Principles

This codebase follows strict semantic anchoring rules:

Semantic Over Structural

// ✅ SEMANTIC: Based on observable schema properties
const needsIndex = table.hasForeignKey() && !table.hasIndexOnForeignKey()

// ❌ STRUCTURAL: Based on technical metrics
const needsIndex = table.rowCount > 10000 && table.queryCount > 100

Intent Preservation

// ✅ Environment semantics preserved through transformations
const schema = await fetchSchema(Environment.PRODUCTION)
// Schema analysis preserves "production" intent - no overrides

Observable Anchoring

// ✅ Based on directly observable properties
const relationships = extractForeignKeys(sqliteMaster)

// ❌ Based on inferred behavior
const relationships = inferFromQueryPatterns(logs)

See SEMANTIC_ANCHORING_GOVERNANCE.md for complete governance rules.

🧪 Testing

Status: ✅ Comprehensive test suite with 398 tests passing

Test Coverage

✅ Domain Layer: 212 tests (entities, services, validation)
✅ Infrastructure Layer: 64 tests (D1 adapter, API client, config)
✅ Application Layer: 35 tests (use cases, orchestration)
✅ Presentation Layer: 13 tests (MCP server, tool routing)
✅ Integration: 15 tests (end-to-end flows)
✅ Value Objects: 59 tests (Environment, immutability)

Total: 398 tests (all passing ✅)

Running Tests

# Run all tests
npm test

# Watch mode
npm run test:watch

# With UI
npm run test:ui

# Coverage report
npm run test:coverage

Test Framework

Vitest: Fast unit testing framework
@vitest/coverage-v8: Code coverage reports
Mock Strategy: Mock Cloudflare D1 API responses via interface implementations

📖 Learning from This Implementation

This codebase serves as a reference implementation for semantic intent patterns in database tooling.

Key Files to Study

Hexagonal Architecture Implementation:

src/index.ts - Composition root with dependency injection
src/domain/entities/ - Domain entities with semantic validation
src/domain/services/ - Pure business logic services
src/application/use-cases/ - Orchestration layer
src/infrastructure/adapters/ - External adapters
src/presentation/mcp/ - MCP protocol layer

Reference Documentation:

D1_MCP_REFACTORING_PLAN.md - Complete refactoring plan
SEMANTIC_ANCHORING_GOVERNANCE.md - Governance rules
ARCHITECTURE.md - Architecture details

Related Projects

semantic-context-mcp - Sibling reference implementation for context management
semantic-chirp-intelligence-mcp - Fantasy hockey intelligence MCP (ChirpIQX)

🤝 Contributing

We welcome contributions! This is a reference implementation, so contributions should maintain semantic intent principles.

How to Contribute

Read the guidelines: CONTRIBUTING.md
Check refactoring plan: D1_MCP_REFACTORING_PLAN.md
Follow the architecture: Maintain layer boundaries and semantic anchoring
Add tests: All changes need comprehensive test coverage
Document intent: Explain WHY, not just WHAT

Contribution Standards

✅ Follow semantic intent patterns
✅ Maintain hexagonal architecture (post-refactoring)
✅ Add comprehensive tests (90%+ coverage target)
✅ Include semantic documentation
✅ Pass all CI checks

Quick Links:

Contributing Guide - Detailed guidelines
Code of Conduct - Community standards
Architecture Guide - Design principles
Security Policy - Report vulnerabilities

Community

💬 Discussions - Ask questions
🐛 Issues - Report bugs
🔒 Security - Report vulnerabilities privately

🔒 Security

Security is a top priority. Please review our Security Policy for:

API token management best practices
What to commit / what to exclude
Reporting security vulnerabilities
Security checklist for deployment

Found a vulnerability? Email: [email protected]

🔬 Research Foundation

This implementation is based on the research paper "Semantic Intent as Single Source of Truth: Immutable Governance for AI-Assisted Development".

Core Principles Applied

Semantic Over Structural - Schema analysis based on meaning, not metrics
Intent Preservation - Environment semantics maintained through transformations
Observable Anchoring - Decisions based on directly observable schema properties
Immutable Governance - Protect semantic integrity at runtime

Related Resources

📊 Project Roadmap

✅ Phase 0: Initial Implementation (Complete)

Monolithic MCP server with 6 tools
D1 REST API integration
Basic schema analysis

✅ Phase 1: Domain Layer (Complete)

10 domain entities with semantic validation
3 domain services (SchemaAnalyzer, RelationshipAnalyzer, OptimizationService)
212 passing tests

✅ Phase 2: Infrastructure Layer (Complete)

CloudflareD1Repository adapter
CloudflareAPIClient HTTP client
InMemoryCacheProvider
64 passing tests

✅ Phase 3: Application Layer (Complete)

4 use cases (AnalyzeSchema, GetRelationships, ValidateSchema, SuggestOptimizations)
Port interfaces (ICloudflareD1Repository, ICacheProvider)
35 passing tests

✅ Phase 4: Presentation Layer (Complete)

D1DatabaseMCPServer with 4 MCP tools
Request/response DTOs
13 passing tests

✅ Phase 5: Integration & Composition Root (Complete)

Dependency injection in index.ts
Environment configuration
15 integration tests

✅ Phase 6: CI/CD & Documentation (Complete)

TypeScript build verification
README updated
398 total tests passing

🎯 Phase 7: Production Readiness (Planned)

GitHub Actions CI/CD workflow
Dependabot automation
Security scanning
GitHub repository setup

See D1_MCP_REFACTORING_PLAN.md for detailed roadmap.

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

🙏 Acknowledgments

Built on Model Context Protocol by Anthropic
Inspired by Hexagonal Architecture (Alistair Cockburn)
Based on Domain-Driven Design principles (Eric Evans)
Part of the Semantic Intent research initiative

This is a reference implementation demonstrating semantic intent patterns for database introspection. Study the code, learn the patterns, and apply them to your own projects. 🏗️

Name		Name	Last commit message	Last commit date
Latest commit History 24 Commits
.github		.github
dist		dist
src		src
tests/domain		tests/domain
.env.example		.env.example
.gitignore		.gitignore
.npmignore		.npmignore
ARCHITECTURE.md		ARCHITECTURE.md
CHANGELOG.md		CHANGELOG.md
CODE_OF_CONDUCT.md		CODE_OF_CONDUCT.md
CONTRIBUTING.md		CONTRIBUTING.md
D1_MCP_REFACTORING_PLAN.md		D1_MCP_REFACTORING_PLAN.md
LICENSE		LICENSE
README.md		README.md
SECURITY.md		SECURITY.md
SEMANTIC_ANCHORING_GOVERNANCE.md		SEMANTIC_ANCHORING_GOVERNANCE.md
biome.json		biome.json
package-lock.json		package-lock.json
package.json		package.json
start-d1-mcp.sh		start-d1-mcp.sh
tsconfig.json		tsconfig.json
vitest.config.ts		vitest.config.ts

Uh oh!

License

Uh oh!

semanticintent/semantic-perch-intelligence-mcp

Folders and files

Latest commit

History

Repository files navigation

Semantic Perch Intelligence MCP

📚 Table of Contents

🎯 What Makes This Different

🚀 Quick Start

Prerequisites

Installation

Get Cloudflare API Token

Get D1 Database IDs

🛠️ MCP Tools

1. analyze_database_schema

2. get_table_relationships

3. validate_database_schema

4. suggest_database_optimizations

🔌 Connect to Claude Desktop

Configuration

Usage Example

🏗️ Architecture

Implementation Status

Layer Responsibilities

Semantic Intent Principles

🧪 Testing

Test Coverage

Running Tests

Test Framework

📖 Learning from This Implementation

Key Files to Study

Related Projects

🤝 Contributing

How to Contribute

Contribution Standards

Community

🔒 Security

🔬 Research Foundation

Core Principles Applied

Related Resources

📊 Project Roadmap

✅ Phase 0: Initial Implementation (Complete)

✅ Phase 1: Domain Layer (Complete)

✅ Phase 2: Infrastructure Layer (Complete)

✅ Phase 3: Application Layer (Complete)

✅ Phase 4: Presentation Layer (Complete)

✅ Phase 5: Integration & Composition Root (Complete)

✅ Phase 6: CI/CD & Documentation (Complete)

🎯 Phase 7: Production Readiness (Planned)

📄 License

🙏 Acknowledgments

About

Resources

License

Code of conduct

Contributing

Security policy

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Contributors 2

Uh oh!

Languages

Packages