Schema Trace

Schema Trace

Skill Purpose: Database schema alignment and data flow verification patterns

---

Core Skill Pattern

Objective: Establish comprehensive schema tracing to ensure data consistency across database, types, and application layers.

Universal Pattern:

1. Define schema sources and alignment requirements
2. Create schema comparison and verification patterns
3. Set up data flow tracing and validation
4. Establish type generation and synchronization procedures
5. Create schema change impact analysis

Key Decisions (Project-Specific):

• Schema source hierarchy and authority
• Comparison strictness and tolerance levels
• Type generation automation vs manual processes
• Change detection and notification procedures
• Cross-system synchronization requirements

---

Project-Specific Implementation Notes

Customize per project:

• Schema sources based on data architecture complexity
• Comparison strictness based on data criticality
• Automation level based on team expertise and change frequency
• Change notification based on stakeholder needs
• Synchronization depth based on system integration requirements

---

Example Implementation (Database Schema Tracing Pattern)

Note: This is an example pattern for database schema tracing. Adapt schema sources and verification methods based on your specific data architecture and requirements.

Prerequisites (Example)

• Database schema defined and stable
• Type system requirements established
• Data flow patterns documented

---

Example: Database Schema Tracing Implementation

Framework-Specific Example: This demonstrates schema tracing patterns. Adapt for your database system and type generation approach.

1. Schema Source Definition

// Define schema sources
interface SchemaSource {
  name: string;
  type: 'database' | 'typescript' | 'api' | 'documentation';
  location: string;
  lastUpdated: Date;
  version: string;
}

// Example schema sources
const schemaSources: SchemaSource[] = [
  {
    name: 'Supabase Database',
    type: 'database',
    location: 'supabase://project-db',
    lastUpdated: new Date(),
    version: '1.0.0'
  },
  {
    name: 'Generated Types',
    type: 'typescript',
    location: '/​types/​database.ts',
    lastUpdated: new Date(),
    version: '1.0.0'
  }
];

2. Schema Comparison Patterns

// Compare database schema with TypeScript types
interface SchemaDifference {
  type: 'missing' | 'extra' | 'type_mismatch' | 'constraint_diff';
  entity: string;
  field: string;
  expected: any;
  actual: any;
  severity: 'error' | 'warning' | 'info';
}

// Schema comparison logic
function compareSchemas(source1: any, source2: any): SchemaDifference[] {
  const differences: SchemaDifference[] = [];
  
  // Implementation for comparing schemas
  // - Check for missing tables/​fields
  // - Validate type matches
  // - Check constraint differences
  
  return differences;
}

3. Data Flow Verification

// Trace data flow through application
interface DataFlowNode {
  name: string;
  type: 'table' | 'view' | 'function' | 'api' | 'component';
  inputs: string[];
  outputs: string[];
  transformations: string[];
}

// Data flow tracing
function traceDataFlow(source: string, target: string): DataFlowNode[] {
  // Implementation for tracing data flow
  // - Map data dependencies
  // - Identify transformation points
  // - Verify data integrity at each step
  
  return [];
}

4. Type Generation Alignment

// Ensure generated types match database schema
interface TypeAlignment {
  tableName: string;
  databaseColumns: ColumnDef[];
  typeScriptFields: FieldDef[];
  alignmentIssues: AlignmentIssue[];
}

// Type alignment verification
function verifyTypeAlignment(schema: DatabaseSchema): TypeAlignment[] {
  // Implementation for type alignment
  // - Compare table columns with type fields
  // - Check for missing or extra fields
  // - Validate type compatibility
  
  return [];
}

5. Change Impact Analysis

// Analyze impact of schema changes
interface SchemaChange {
  type: 'add' | 'remove' | 'modify';
  entity: string;
  field?: string;
  impact: string[];
  affectedComponents: string[];
}

// Change impact analysis
function analyzeChangeImpact(
  oldSchema: DatabaseSchema, 
  newSchema: DatabaseSchema
): SchemaChange[] {
  // Implementation for impact analysis
  // - Detect schema changes
  // - Map affected components
  // - Assess breaking changes
  
  return [];
}

6. Schema Verification Reports

// Generate schema verification reports
interface SchemaReport {
  summary: {
    totalEntities: number;
    alignedEntities: number;
    issues: number;
    lastVerified: Date;
  };
  issues: SchemaDifference[];
  recommendations: string[];
}

// Report generation
function generateSchemaReport(
  differences: SchemaDifference[]
): SchemaReport {
  // Implementation for report generation
  // - Summarize findings
  // - Categorize issues by severity
  // - Provide actionable recommendations
  
  return {
    summary: {
      totalEntities: 0,
      alignedEntities: 0,
      issues: differences.length,
      lastVerified: new Date()
    },
    issues: differences,
    recommendations: []
  };
}

---

Integration Patterns

Database Integration

• Connect to database schema metadata
• Extract table definitions and constraints
• Monitor schema changes over time

Type System Integration

• Compare with generated TypeScript types
• Validate type definitions match schema
• Ensure type safety across application

API Integration

• Verify API contracts match schema
• Check request/​response type alignment
• Validate data transformation accuracy

---

Stop Conditions

STOP verification and escalate if:

• Schema sources are inconsistent or conflicting
• Critical data flow breaks detected
• Type generation fails repeatedly
• Schema changes impact production systems
• Cross-system synchronization fails

PAUSE verification if:

• Database is in maintenance mode
• Type generation process is running
• Schema migration in progress

---

Quality Metrics

Schema Alignment Metrics:

• Entity alignment percentage
• Field accuracy rate
• Type compatibility score
• Change detection timeliness

Data Flow Metrics:

• Flow completeness percentage
• Transformation accuracy rate
• Integration point validation success

---

Maintenance Procedures

Regular Tasks:

• Daily schema alignment checks
• Weekly type generation verification
• Monthly data flow validation
• Quarterly schema review

Change Procedures:

• Schema change impact assessment
• Type regeneration after schema updates
• Data flow verification after changes
• Documentation updates for schema modifications