GraphQL SDL Migration Analysis

database/schemas/
├── domain/v1/              # ✅ Single Source of Truth (JSON Schema)
│   ├── classification/
│   ├── forms/
│   ├── match-types/
│   └── types/
│
├── generated/adapters/     # ✅ Multi-target code generation
│   ├── python/v1/          # Pydantic models
│   ├── typescript/v1/      # TS interfaces + Zod
│   ├── drizzle/v1/         # Drizzle ORM
│   ├── neo4j/v1/           # Graph stubs
│   ├── postgresql/v1/      # SQL DDL stubs
│   └── redshift/v1/        # Redshift DDL stubs
│
└── infrastructure/         # ✅ Infrastructure layer separation
    ├── postgresql/         # Raw SQL DDL
    └── prisma/            # Prisma schemas

// Current: tier.schema.json
{
  "properties": {
    "tier_name": {"type": "string"},
    "description": {"type": "string"}
  }
}
 
// Current: league.schema.json
{
  "properties": {
    "name": {"type": "string"},
    "tier": {"type": "string"}  // ❌ Just a string, no relationship metadata
  }
}

# database/schemas/generated/adapters/neo4j/v1/tier.py (current)
"""
Generated Neo4j adapter for tier.schema.json
TODO: Implement actual Cypher generation
"""

// Auto-generated constraints and indexes
CREATE CONSTRAINT tier_name IF NOT EXISTS FOR (t:Tier) REQUIRE t.tier_name IS UNIQUE;
CREATE INDEX tier_description IF NOT EXISTS FOR (t:Tier) ON (t.description);
 
// Auto-generated relationship queries
MATCH (l:League)-[:HAS_TIER]->(t:Tier)
WHERE t.tier_name = $tier_name
RETURN l;

# database/schemas/domain/v1/sdl/league_tier.graphql
 
type Tier @id(field: "tier_name") @unique(fields: ["tier_name"]) {
  tier_name: String! @id
  description: String
  priority_score: Int
 
  # First-class relationship definition
  leagues: [League!]! @relationship(type: "HAS_TIER", direction: IN)
}
 
type League @id(field: "league_id") {
  league_id: ID! @id
  name: String!
  sport: String!
 
  # Relationship with cardinality and direction
  tier: Tier! @relationship(type: "HAS_TIER", direction: OUT)
  teams: [Team!]! @relationship(type: "HAS_TEAM", direction: OUT)
}
 
type Team @id(field: "team_id") {
  team_id: ID! @id
  name: String!
  league: League! @relationship(type: "HAS_TEAM", direction: IN)
}

CREATE TABLE tiers (
  tier_name VARCHAR(100) PRIMARY KEY,
  description TEXT,
  priority_score INT
);
 
CREATE TABLE leagues (
  league_id UUID PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  sport VARCHAR(100) NOT NULL,
  tier_name VARCHAR(100) NOT NULL REFERENCES tiers(tier_name)
);
 
CREATE TABLE teams (
  team_id UUID PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  league_id UUID NOT NULL REFERENCES leagues(league_id)
);

// Constraints
CREATE CONSTRAINT tier_unique IF NOT EXISTS FOR (t:Tier) REQUIRE t.tier_name IS UNIQUE;
CREATE CONSTRAINT league_unique IF NOT EXISTS FOR (l:League) REQUIRE l.league_id IS UNIQUE;
 
// Indexes
CREATE INDEX tier_priority IF NOT EXISTS FOR (t:Tier) ON (t.priority_score);
 
// Sample relationship creation (in application code)
MATCH (l:League {league_id: $league_id})
MATCH (t:Tier {tier_name: $tier_name})
MERGE (l)-[:HAS_TIER]->(t);

model Tier {
  tier_name      String   @id
  description    String?
  priority_score Int?
  leagues        League[] @relation("HAS_TIER")
}
 
model League {
  league_id String @id @default(uuid())
  name      String
  sport     String
  tier_name String
  tier      Tier   @relation("HAS_TIER", fields: [tier_name], references: [tier_name])
  teams     Team[] @relation("HAS_TEAM")
}
 
model Team {
  team_id   String @id @default(uuid())
  name      String
  league_id String
  league    League @relation("HAS_TEAM", fields: [league_id], references: [league_id])
}

from pydantic import BaseModel, Field
from typing import List, Optional
 
class Tier(BaseModel):
    tier_name: str = Field(..., description="Primary identifier")
    description: Optional[str] = None
    priority_score: Optional[int] = None
 
    # Relationship metadata
    class Config:
        relationships = {
            "leagues": {"type": "League", "cardinality": "many", "direction": "in"}
        }
 
class League(BaseModel):
    league_id: str = Field(..., description="Primary identifier")
    name: str
    sport: str
    tier_name: str
 
    class Config:
        relationships = {
            "tier": {"type": "Tier", "cardinality": "one", "direction": "out"},
            "teams": {"type": "Team", "cardinality": "many", "direction": "out"}
        }

import { z } from 'zod';
 
export const TierSchema = z.object({
  tier_name: z.string(),
  description: z.string().optional(),
  priority_score: z.number().optional(),
});
 
export type Tier = z.infer<typeof TierSchema>;
 
export const LeagueSchema = z.object({
  league_id: z.string().uuid(),
  name: z.string(),
  sport: z.string(),
  tier_name: z.string(),
});
 
export type League = z.infer<typeof LeagueSchema>;
 
// Relationship metadata
export const LeagueRelationships = {
  tier: { type: 'Tier', cardinality: 'one' },
  teams: { type: 'Team', cardinality: 'many' },
} as const;

database/schemas/
├── domain/
│   ├── v1/                    # KEEP: JSON Schema for basic types
│   │   ├── types/             # Primitives, enums, simple objects
│   │   └── forms/             # Form definitions (JSON Schema works great here)
│   │
│   └── sdl/                   # NEW: GraphQL SDL for entities + relationships
│       └── v1/
│           ├── league_tier.graphql
│           ├── contracts.graphql
│           ├── combat_sports.graphql
│           └── team_sports.graphql
│
├── generated/adapters/        # ENHANCED: Add relationship metadata
│   ├── python/v1/
│   ├── typescript/v1/
│   └── ...
│
└── infrastructure/            # AUTO-GENERATED: From SDL
    ├── postgresql/
    │   └── generated_from_sdl/  # Auto-generated DDL
    ├── prisma/
    │   └── generated.schema.prisma  # Auto-generated from SDL
    └── neo4j/
        └── generated_constraints.cypher  # Auto-generated from SDL

# Shared base
interface SportEvent {
  event_id: ID!
  date: DateTime!
  venue: String
}
 
# Sport-specific implementations
type CombatBout implements SportEvent {
  event_id: ID!
  date: DateTime!
  venue: String
  fighter_a: Fighter! @relationship(type: "FIGHTS_IN", direction: IN)
  fighter_b: Fighter! @relationship(type: "FIGHTS_IN", direction: IN)
  weight_class: String!
  method_of_victory: String
}
 
type RaceEvent implements SportEvent {
  event_id: ID!
  date: DateTime!
  venue: String
  track_length: Float!
  weather_conditions: String
  participants: [Driver!]! @relationship(type: "COMPETES_IN", direction: IN)
}

# Regenerate all adapters from SDL + JSON Schema
./scripts/regenerate_all_schemas.sh
 
# Internally calls:
python database/setup/generate_from_sdl.py    # SDL → SQL/Prisma/Neo4j/Pydantic
python database/setup/generate_adapters.py    # JSON Schema → Pydantic/TS/Zod

# database/setup/generate_from_sdl.py
 
from pathlib import Path
from typing import List
import graphql
 
SDL_ROOT = Path("database/schemas/domain/sdl/v1")
INFRA_ROOT = Path("database/schemas/infrastructure")
 
def parse_sdl(sdl_path: Path) -> graphql.DocumentNode:
    """Parse GraphQL SDL file."""
    return graphql.parse(sdl_path.read_text())
 
def generate_postgresql(schema: graphql.DocumentNode) -> str:
    """Generate PostgreSQL DDL from SDL."""
    ddl = []
    for definition in schema.definitions:
        if isinstance(definition, graphql.ObjectTypeDefinitionNode):
            ddl.append(generate_table(definition))
    return "\n\n".join(ddl)
 
def generate_table(type_def: graphql.ObjectTypeDefinitionNode) -> str:
    """Generate CREATE TABLE statement."""
    table_name = type_def.name.value.lower() + "s"  # Simple pluralization
    columns = []
 
    for field in type_def.fields:
        # Check for @id directive
        is_id = any(d.name.value == "id" for d in (field.directives or []))
        column = generate_column(field, is_id)
        columns.append(column)
 
    return f"CREATE TABLE {table_name} (\n  " + ",\n  ".join(columns) + "\n);"
 
def generate_column(field: graphql.FieldDefinitionNode, is_id: bool) -> str:
    """Generate column definition."""
    field_name = field.name.value
    field_type = graphql_to_sql_type(field.type)
 
    constraints = []
    if is_id:
        constraints.append("PRIMARY KEY")
    if isinstance(field.type, graphql.NonNullTypeNode):
        constraints.append("NOT NULL")
 
    return f"{field_name} {field_type} " + " ".join(constraints)
 
def graphql_to_sql_type(gql_type) -> str:
    """Map GraphQL type to SQL type."""
    # Unwrap NonNull and List types
    if isinstance(gql_type, graphql.NonNullTypeNode):
        gql_type = gql_type.type
    if isinstance(gql_type, graphql.ListTypeNode):
        return "JSONB"  # Store arrays as JSONB
 
    type_name = gql_type.name.value
 
    mapping = {
        "ID": "UUID",
        "String": "VARCHAR(255)",
        "Int": "INTEGER",
        "Float": "DOUBLE PRECISION",
        "Boolean": "BOOLEAN",
        "DateTime": "TIMESTAMPTZ",
    }
    return mapping.get(type_name, "TEXT")
 
# Similar generators for Prisma, Neo4j, Pydantic...

infrastructure/
├── postgresql/
│   └── generated/
│       └── league_tier.sql          # ✅ Auto-generated
│
├── prisma/
│   └── generated/
│       └── league_tier.prisma       # ✅ Auto-generated
│
└── neo4j/
    └── generated/
        └── league_tier_constraints.cypher  # ✅ Auto-generated