Add Implicit Relations Configuration Options

[peter7775]

Add Implicit Relations Configuration Options

Problem Description

Currently, the system only displays graph data based on explicitly defined transformation rules. However, there's often valuable implicit relationship information that can be derived directly from SQL database structure (foreign keys, junction tables, etc.) that users might want to visualize either alongside or instead of explicitly configured data.

Proposed Solution

Add two new configuration options to enable implicit SQL relationship discovery and display:

Configuration Options

1. implicit-relations - Display implicit SQL relationships alongside explicitly defined transformation rules
2. only-implicit-relations - Display only implicit SQL relationships (ignore transformation rules)

Example Configuration

mysql:
  host: localhost
  port: 3306
  user: username
  password: password
  database: dbname

neo4j:
  uri: bolt://localhost:7687
  user: neo4j
  password: password

# New implicit relations configuration
implicit_relations:
  mode: "implicit-relations"  # Options: "implicit-relations", "only-implicit-relations", "disabled" (default)
  
  # Optional: Configure which types of implicit relationships to discover
  discovery:
    foreign_keys: true      # Create relationships from foreign key constraints
    junction_tables: true   # Detect and convert many-to-many junction tables
    naming_patterns: true   # Use naming conventions (e.g., user_id -> User relationship)
  
  # Optional: Customize implicit relationship properties  
  relationship_naming:
    foreign_key_pattern: "REFERENCES"     # Default relationship type for foreign keys
    junction_table_pattern: "RELATED_TO"  # Default for junction tables

Technical Implementation

1. Configuration Schema Extension

• File: internal/domain/models/config.go
• Add ImplicitRelationsConfig struct
• Extend main Config struct to include implicit relations settings

2. SQL Schema Analysis

• New Service: internal/application/services/schema/
• Create SchemaAnalyzer to:
  • Query INFORMATION_SCHEMA tables for foreign key relationships
  • Detect junction tables (tables with only foreign key columns)
  • Apply naming pattern matching for implicit relationships

3. Graph Generation Logic

• File: internal/application/services/transform/
• Extend transformation service to:
  • Generate nodes from all tables when using implicit relations
  • Create relationships based on discovered schema patterns
  • Merge implicit and explicit data when using implicit-relations mode

4. GraphQL Integration

• Files: internal/interfaces/graphql/schema.resolvers.go
• Update existing resolvers to handle implicit relation queries
• No schema changes needed - existing graph, nodesByType, etc. will work

Acceptance Criteria

Core Functionality

• Configuration parser recognizes new implicit_relations section
• mode: "implicit-relations" displays both implicit and explicit relationships
• mode: "only-implicit-relations" displays only discovered SQL relationships
• mode: "disabled" maintains current behavior (default)

Schema Discovery

• Foreign key relationships are correctly identified and converted
• Junction tables are detected and converted to relationships
• Naming pattern matching works for common conventions (user_id → User)

Data Quality

• Implicit nodes contain relevant table column data as properties
• Relationship directions are correctly inferred from foreign keys
• No duplicate relationships when combining implicit and explicit data

Testing

• Unit tests for schema analysis functionality
• Integration tests with sample database containing foreign keys
• GraphQL query tests for both modes
• Configuration validation tests

Documentation

• Update README with implicit relations examples
• Add configuration documentation
• Create example databases showcasing the feature

Technical Considerations

Database Compatibility

• MySQL INFORMATION_SCHEMA.KEY_COLUMN_USAGE for foreign keys
• Handle different MySQL versions and configurations
• Consider future PostgreSQL compatibility

Performance

• Schema analysis should be cached to avoid repeated queries
• Large databases with many tables should be handled efficiently
• Option to limit schema discovery to specific tables/patterns

Naming Conventions

• Support common patterns: user_id → User, customer_id → Customer
• Allow custom naming pattern configuration
• Handle edge cases and naming conflicts gracefully

Error Handling

• Graceful fallback when schema analysis fails
• Clear error messages for configuration issues
• Validation of implicit relation settings

Examples

Use Case 1: E-commerce Database

With implicit relations enabled, automatically discover:

• orders.customer_id → customers (REFERENCES relationship)
• order_items junction table → orders and products relationships
• product_categories.category_id → categories

Use Case 2: Blog System

Automatic discovery of:

• posts.author_id → users (AUTHOR relationship)
• comments.post_id → posts (COMMENT_ON relationship)
• post_tags junction → many-to-many posts ↔ tags

Files to Modify/Create

internal/
├── domain/
│   └── models/
│       └── config.go                    # Add ImplicitRelationsConfig
├── application/
│   └── services/
│       ├── schema/                      # New: Schema analysis service
│       │   ├── analyzer.go
│       │   └── analyzer_test.go
│       └── transform/
│           └── service.go               # Update transformation logic
├── interfaces/
│   └── graphql/
│       └── schema.resolvers.go          # Handle implicit data in resolvers
└── config/
    └── config.yml                       # Add example implicit config

docs/
└── examples/
    └── implicit-relations/              # New: Examples and documentation
        ├── README.md
        ├── ecommerce-implicit.yml
        └── sample-schema.sql

Estimated Effort

Time Estimate: 3-4 days

Breakdown:

• Schema analysis service: 1.5 days
• Configuration integration: 0.5 days
• Transformation logic updates: 1 day
• Testing and documentation: 1 day

Priority

Medium-High - This feature significantly enhances usability by reducing configuration overhead for users who want to quickly visualize existing database relationships.