Architecture
Database Directory Reorganization Proposal

Source: data_layer/docs/REORGANIZATION_PROPOSAL.md

Database Directory Reorganization Proposal

Problem

Current structure mixes concerns:

  • Neo4j .cypher files in sql/ (confusing - Cypher isn't SQL)
  • JSON Schema domain models mixed with infrastructure models
  • Unclear where Prisma/Drizzle schemas should live
  • Hard to find the "source of truth" files

Proposed Structure

database/
├── schemas/                         # 🟢 ALL SCHEMA DEFINITIONS
│   ├── domain/                      # Business domain schemas
│   │   ├── v1/                      # Version 1 (current)
│   │   │   ├── json/                # JSON Schema (API validation)
│   │   │   │   ├── league.schema.json
│   │   │   │   ├── team.schema.json
│   │   │   │   ├── player.schema.json
│   │   │   │   └── contract.schema.json
│   │   │   │
│   │   │   ├── neo4j/               # Neo4j Cypher schemas
│   │   │   │   ├── constraints.cypher      # Constraints
│   │   │   │   ├── indexes.cypher          # Indexes
│   │   │   │   ├── nodes.cypher            # Node definitions
│   │   │   │   └── relationships.cypher    # Relationship patterns
│   │   │   │
│   │   │   └── README.md            # Domain schema documentation
│   │   │
│   │   └── v2/                      # Future version
│   │
│   ├── infrastructure/              # Infrastructure/ORM schemas
│   │   ├── postgresql/              # PostgreSQL schemas
│   │   │   ├── core-schema.sql
│   │   │   ├── supabase/
│   │   │   │   ├── prospective_leagues.sql
│   │   │   │   └── migrations/
│   │   │   └── migrations/
│   │   │
│   │   ├── prisma/                  # Prisma ORM schemas
│   │   │   ├── schema.prisma
│   │   │   └── migrations/
│   │   │
│   │   └── drizzle/                 # Drizzle ORM schemas (future)
│   │       ├── schema.ts
│   │       └── migrations/
│   │
│   └── generated/                   # AUTO-GENERATED (do not edit)
│       ├── adapters/
│       │   ├── python/v1/           # Pydantic models
│       │   ├── typescript/v1/       # TS types + Zod
│       │   └── drizzle/v1/          # Drizzle tables
│       │
│       └── models/                  # Legacy (deprecated)
│
├── docs/                            # 🟢 DOCUMENTATION
│   ├── SCHEMA_MAPPING.md            # JSON ↔ Neo4j mapping
│   ├── SCHEMA_WORKFLOW.md           # Daily workflow guide
│   ├── ADR-001-SCHEMA-ARCHITECTURE.md
│   └── DATABASE_ARCHITECTURE.md
│
├── scripts/                         # 🟢 UTILITIES
│   ├── validate_schema_sync.py      # Validation
│   ├── setup_neo4j_light.py
│   └── regenerate_adapters.sh
│
├── prompts/                         # 🟢 AI PROMPTS
├── output-styles/                   # 🟢 TEMPLATES
├── storage/                         # 🟢 EXAMPLES/SEEDS
└── CLAUDE.md

Key Changes

1. Split schemas/domain/v1/ by Database Type

Before:

schemas/domain/v1/*.json             # JSON Schema
sql/*.cypher                         # Neo4j (confusing!)

After:

schemas/domain/v1/json/*.json        # JSON Schema (validation)
schemas/domain/v1/neo4j/*.cypher     # Neo4j (graph)

Benefits:

  • ✅ Neo4j schemas live with domain schemas (same concern level)
  • ✅ Clear separation: json/ vs neo4j/
  • ✅ Version together (v1, v2)
  • ✅ Easier to find matching files

2. Create schemas/infrastructure/ for ORMs

Before:

sql/core-schema.sql                  # PostgreSQL
sql/supabase_*.sql                   # Supabase schemas
(no clear home for Prisma/Drizzle)

After:

schemas/infrastructure/postgresql/   # Raw PostgreSQL
schemas/infrastructure/prisma/       # Prisma ORM
schemas/infrastructure/drizzle/      # Drizzle ORM (future)

Benefits:

  • ✅ Separate "domain models" from "infrastructure"
  • ✅ Clear home for ORM schemas
  • ✅ Infrastructure schemas can reference domain schemas

3. Organize Neo4j Schemas by Purpose

Before:

sql/neo4j_comprehensive_schema.cypher    # Everything mixed
sql/neo4j_league_mvp_schema.cypher       # Subset?
sql/optimized.neo4j.schema.cypher        # Which one?

After:

schemas/domain/v1/neo4j/
├── constraints.cypher       # Uniqueness, existence constraints
├── indexes.cypher           # Performance indexes
├── nodes.cypher             # Node label definitions
├── relationships.cypher     # Relationship type definitions
└── README.md                # Which file to use when

Benefits:

  • ✅ One file per concern
  • ✅ Easier to find specific constraint or index
  • ✅ Can apply incrementally (constraints first, then indexes)
  • ✅ Clearer what each file does

Migration Steps

Phase 1: Reorganize Domain Schemas (Low Risk)

# 1. Create new directories
mkdir -p schemas/domain/v1/json
mkdir -p schemas/domain/v1/neo4j
 
# 2. Move JSON schemas
mv schemas/domain/v1/*.json schemas/domain/v1/json/
 
# 3. Move Neo4j schemas (symlink old location for compatibility)
cp sql/neo4j_comprehensive_schema.cypher schemas/domain/v1/neo4j/comprehensive.cypher
ln -s ../../schemas/domain/v1/neo4j/comprehensive.cypher sql/neo4j_comprehensive_schema.cypher
 
# 4. Update documentation paths
# Edit SCHEMA_MAPPING.md to point to new locations

Phase 2: Split Neo4j Comprehensive Schema (Medium Risk)

# Split comprehensive.cypher into logical files
python scripts/split_neo4j_schema.py \
  schemas/domain/v1/neo4j/comprehensive.cypher \
  --output schemas/domain/v1/neo4j/
 
# Result:
# schemas/domain/v1/neo4j/
#   ├── constraints.cypher
#   ├── indexes.cypher
#   ├── nodes.cypher
#   └── relationships.cypher

Phase 3: Reorganize Infrastructure Schemas (Medium Risk)

# Create infrastructure directories
mkdir -p schemas/infrastructure/postgresql
mkdir -p schemas/infrastructure/prisma
 
# Move PostgreSQL schemas
mv sql/*.sql schemas/infrastructure/postgresql/
 
# Symlink for backward compatibility
ln -s ../schemas/infrastructure/postgresql sql

Phase 4: Update Scripts and Documentation (Low Risk)

# Update paths in:
- scripts/validate_schema_sync.py
- scripts/regenerate_adapters.sh
- docs/SCHEMA_MAPPING.md
- docs/SCHEMA_WORKFLOW.md

Comparison: Before vs After

Finding JSON Schema for League

Before:

database/schemas/domain/v1/league_payload_schema.json  # Is this it?
database/schemas/domain/v1/api/league-master.schema.json  # Or this?

After:

database/schemas/domain/v1/json/league.schema.json  # Clear!

Finding Neo4j Schema

Before:

database/sql/neo4j_comprehensive_schema.cypher  # Why in sql/?
database/sql/neo4j_league_mvp_schema.cypher     # Which one?
database/sql/optimized.neo4j.schema.cypher      # ???

After:

database/schemas/domain/v1/neo4j/constraints.cypher      # Constraints here
database/schemas/domain/v1/neo4j/indexes.cypher          # Indexes here
database/schemas/domain/v1/neo4j/nodes.cypher            # Nodes here
database/schemas/domain/v1/neo4j/relationships.cypher    # Relationships here

Adding New Entity

Before:

1. Add to schemas/domain/v1/new_entity.schema.json
2. Add to sql/neo4j_comprehensive_schema.cypher (confusing location!)
3. Document in SCHEMA_MAPPING.md

After:

1. Add to schemas/domain/v1/json/new_entity.schema.json
2. Add to schemas/domain/v1/neo4j/nodes.cypher (logical location!)
3. Add to schemas/domain/v1/neo4j/constraints.cypher
4. Document in SCHEMA_MAPPING.md (paths clearer!)

Updated Documentation Paths

SCHEMA_MAPPING.md

Before:

**JSON Schema:** `database/schemas/domain/v1/league_payload_schema.json`
**Neo4j Cypher:** `database/sql/neo4j_comprehensive_schema.cypher`

After:

**JSON Schema:** `database/schemas/domain/v1/json/league.schema.json`
**Neo4j Cypher:**
- Constraints: `database/schemas/domain/v1/neo4j/constraints.cypher`
- Indexes: `database/schemas/domain/v1/neo4j/indexes.cypher`
- Nodes: `database/schemas/domain/v1/neo4j/nodes.cypher`

validate_schema_sync.py

Before:

self.json_schema_dir = base_path / "schemas" / "domain" / "v1"
self.neo4j_schema_file = base_path / "sql" / "neo4j_comprehensive_schema.cypher"

After:

self.json_schema_dir = base_path / "schemas" / "domain" / "v1" / "json"
self.neo4j_schema_dir = base_path / "schemas" / "domain" / "v1" / "neo4j"

Benefits Summary

Clarity

  • ✅ Neo4j schemas in schemas/ (not confusingly in sql/)
  • ✅ Domain vs infrastructure separation clear
  • ✅ Version folders group related schemas

Discoverability

  • ✅ Easy to find JSON schema: schemas/domain/v1/json/
  • ✅ Easy to find Neo4j schema: schemas/domain/v1/neo4j/
  • ✅ Easy to find infrastructure: schemas/infrastructure/

Maintainability

  • ✅ Related files versioned together (v1, v2)
  • ✅ Split Neo4j schema = easier to navigate
  • ✅ Clear what to edit vs what's generated

Scalability

  • ✅ Easy to add v2 alongside v1
  • ✅ Easy to add new ORM (Drizzle) in infrastructure/
  • ✅ Validation script can scan json/ and neo4j/ dirs

Risks and Mitigations

Risk: Breaking Existing Scripts

Mitigation:

  • Phase 1: Create symlinks from old paths to new paths
  • Update one script at a time
  • Test after each change

Risk: Team Confusion

Mitigation:

  • Clear documentation (this file)
  • Update CLAUDE.md with new paths
  • Announcement in team chat

Risk: Git History Confusion

Mitigation:

  • Use git mv instead of mv to preserve history
  • Document moves in commit message
  • Keep old symlinks for a transition period

Rollback Plan

If reorganization causes issues:

# Restore old structure (symlinks still point correctly)
# or
git revert <commit-hash>

Next Steps

  1. Review this proposal - Team approval
  2. Create migration script - Automate reorganization
  3. Test in branch - Verify scripts still work
  4. Update documentation - All paths updated
  5. Merge to main - Roll out changes
  6. Remove symlinks - After 1-2 weeks of compatibility

Questions?

Q: Why not keep Neo4j in sql/? A: Cypher is not SQL. Different syntax, different database. Grouping by database type (PostgreSQL vs Neo4j) is clearer than mixing them in sql/.

Q: Why split Neo4j comprehensive schema? A: Easier to find constraints vs indexes. Can apply incrementally. Clearer what each file does.

Q: What about backward compatibility? A: Use symlinks during transition. Update scripts incrementally.

Q: When to remove symlinks? A: After 1-2 weeks, once all scripts and docs are updated.

Status: 📋 Proposal (not implemented yet) Author: Development Team Date: 2025-01-14 Needs Approval: Yes

Data Layer OverviewDatabase Directory Structure

Platform

Data PlatformAI Intelligence

Documentation

Technical SpecsAPI ReferenceData SchemasSystem Catalogs

Community

GitHubPartnership Portal

Support

partnership@altsportsdata.comdev@altsportsleagues.ai

2025 © AltSportsLeagues.ai. Powered by AI-driven sports business intelligence.

🤖 AI-Enhanced•📊 Data-Driven•⚡ Real-Time