migrations

Database Migrations

🎯 Semantic Intent

This directory contains versioned database migrations following semantic anchoring principles:

• Migration files express TEMPORAL INTENT (numbered sequence)
• Schema changes preserve SEMANTIC CONTRACTS (documented intent)
• Migration commands follow OBSERVABLE PATTERNS (clear wrangler commands)

Migration Files

Migration	Description	Status
0001_initial_schema.sql	Initial context snapshots table with semantic indexes	✅ Applied

Running Migrations

Production Environment

# Apply a specific migration
wrangler d1 execute mcp-context --file=./migrations/0001_initial_schema.sql

# Verify migration success
wrangler d1 execute mcp-context --command="SELECT name FROM sqlite_master WHERE type='table'"

Local Development

# Apply migration locally
wrangler d1 execute mcp-context --local --file=./migrations/0001_initial_schema.sql

# Verify local schema
wrangler d1 execute mcp-context --local --command="SELECT name FROM sqlite_master WHERE type='table'"

Migration Best Practices

🎯 Semantic Anchoring Principles

1. Number Sequentially: 0001_, 0002_, etc. (temporal semantic order)
2. Descriptive Names: Express WHAT the migration does
3. Document Intent: Explain WHY each change preserves semantic meaning
4. Preserve Contracts: Never break existing semantic interfaces
5. Idempotent: Use IF NOT EXISTS and IF EXISTS clauses

Creating New Migrations

When adding a new migration:

1. Create numbered file:

   migrations/0002_add_feature_name.sql
   

2. Document semantic intent:

   -- ========================================
   -- 🎯 MIGRATION: 0002_add_feature_name
   -- ========================================
   -- SEMANTIC INTENT: [Explain WHY this change preserves/extends meaning]
   -- CREATED: YYYY-MM-DD
   -- DESCRIPTION: [WHAT semantic capability does this add?]

3. Make changes idempotent:

   ALTER TABLE context_snapshots ADD COLUMN IF NOT EXISTS new_field TEXT;

4. Update this README:

   • Add entry to migration files table
   • Document new semantic capabilities

5. Update types.ts:

   • Ensure TypeScript interfaces match new schema
   • Maintain semantic type safety

Rollback Strategy

D1 doesn't support automatic rollbacks. For safe migrations:

1. Test locally first: Always test with --local flag
2. Backup data: Export critical data before production migrations
3. Write reverse migrations: Document how to undo changes if needed

Example Rollback Pattern

-- Forward migration (0002_add_field.sql)
ALTER TABLE context_snapshots ADD COLUMN IF NOT EXISTS new_field TEXT;

-- Reverse migration (0002_add_field_rollback.sql)
-- Note: SQLite doesn't support DROP COLUMN easily
-- Alternative: Create new table without column, copy data, rename

Schema Inspection

View Current Schema

# Production
wrangler d1 execute mcp-context --command="SELECT sql FROM sqlite_master WHERE type='table' AND name='context_snapshots'"

# Local
wrangler d1 execute mcp-context --local --command="SELECT sql FROM sqlite_master WHERE type='table' AND name='context_snapshots'"

View Indexes

wrangler d1 execute mcp-context --command="SELECT name, sql FROM sqlite_master WHERE type='index'"

Check Data

# Count records
wrangler d1 execute mcp-context --command="SELECT COUNT(*) as total FROM context_snapshots"

# Sample recent contexts
wrangler d1 execute mcp-context --command="SELECT project, summary, timestamp FROM context_snapshots ORDER BY timestamp DESC LIMIT 5"

Semantic Contract Enforcement

Migration Validation Checklist

Before applying a migration, verify:

• Does this preserve semantic intent of existing data?
• Are semantic anchors (project, timestamp) maintained?
• Do TypeScript types in src/types.ts match new schema?
• Are indexes optimized for semantic query patterns?
• Is the migration idempotent (safe to run multiple times)?
• Does documentation explain semantic purpose?

Type Safety Alignment

After schema changes:

1. Update src/types.ts interfaces
2. Update src/index.ts if semantic contracts changed
3. Run npm run type-check to verify type safety
4. Test with sample data to verify semantic preservation

Migration History Tracking

Track applied migrations manually:

# Create a simple migration tracking approach
# Option 1: Git tags
git tag -a db-0001 -m "Applied migration 0001_initial_schema"

# Option 2: Store in D1 (future enhancement)
# Could create migrations_history table to track applied migrations

Future Enhancements

Potential improvements to migration system:

• Automated migration tracking table
• Migration status verification script
• TypeScript migration generator
• Automatic type file updates from schema
• Schema diff tool for semantic contract validation

---

🏗️ Semantic Architecture

This migration system follows Semantic Intent as Single Source of Truth principles:

• Observable Properties: Database schema defines directly observable semantic fields
• Intent Preservation: Migrations maintain semantic meaning through transformations
• Domain Boundaries: Clear separation between storage (D1) and runtime (TypeScript)
• Type Safety: Compile-time validation of semantic contracts via TypeScript

See SEMANTIC_ANCHORING_GOVERNANCE.md for broader architectural principles.