Semantic D1 MCP

Reference implementation of Semantic Intent as Single Source of Truth patterns

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

📚 Table of Contents

• What Makes This Different
• Quick Start
• MCP Tools
• Architecture
• Testing
• Contributing
• Security
• License

🎯 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

1. Clone the repository

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

2. Install dependencies

   npm install
   

3. 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.

4. Build the server

   npm run build
   

5. Start the MCP server

   npm start
   

   Or use the provided shell script:

   ./start-d1-mcp.sh
   

Get Cloudflare API Token

1. Go to Cloudflare Dashboard
2. Navigate to My Profile → API Tokens
3. Click Create Token
4. Use the Edit Cloudflare Workers template
5. Add D1 permissions: D1:Read
6. 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

1. Edit Claude Desktop config - Go to Settings → Developer → Edit Config

2. Add MCP server configuration:

{
  "mcpServers": {
    "semantic-d1": {
      "command": "node",
      "args": [
        "/absolute/path/to/semantic-d1-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"
      }
    }
  }
}

3. Restart Claude Desktop

4. 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:

1. 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
   

2. Intent Preservation

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

3. 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

---

🤝 Contributing

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

How to Contribute

1. Read the guidelines: CONTRIBUTING.md
2. Check refactoring plan: D1_MCP_REFACTORING_PLAN.md
3. Follow the architecture: Maintain layer boundaries and semantic anchoring
4. Add tests: All changes need comprehensive test coverage
5. 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: security@semanticintent.dev

---

🔬 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

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

Related Resources

• Research Paper (coming soon)
• Semantic Anchoring Governance
• semanticintent.dev (coming soon)

---

📊 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. 🏗️