Source: data_layer/docs/REORGANIZATION_VISUAL.md

Database Reorganization - Visual Guide

🎯 Goal: Simplify and Clarify Schema Organization

Current Structure (Confusing!)

database/
├── schemas/
│   ├── domain/
│   │   └── v1/
│   │       ├── league_payload_schema.json        ← JSON Schema here
│   │       ├── team_schema.json
│   │       ├── player_schema.json
│   │       ├── api/
│   │       │   ├── league-master.schema.json     ← More JSON here?
│   │       │   └── league-extracted.schema.json
│   │       └── combat/                           ← Sport-specific?
│   │           └── fighting/
│   │
│   ├── infrastructure/
│   │   └── prisma/                               ← Should this exist?
│   │       └── schema.prisma
│   │
│   └── generated/                                ← Auto-generated (OK)
│       ├── adapters/
│       └── models/
│
└── sql/                                          ← ⚠️ CONFUSING!
    ├── neo4j_comprehensive_schema.cypher         ← Neo4j in "sql"?
    ├── neo4j_league_mvp_schema.cypher            ← Which Neo4j file?
    ├── optimized.neo4j.schema.cypher             ← Which is current?
    ├── core-schema.sql                           ← PostgreSQL
    ├── supabase_*.sql                            ← More PostgreSQL
    └── unified.postgresql.schema.sql             ← Even more!

Problems:
❌ Neo4j (Cypher) mixed with PostgreSQL (SQL) in "sql/" directory
❌ Multiple Neo4j files - unclear which to use
❌ JSON schemas scattered across domain/v1/
❌ No clear home for Prisma/Drizzle

Proposed Structure (Clear!)

database/
├── schemas/                          # 🟢 ALL SCHEMAS HERE
│   │
│   ├── domain/                       # 🟢 BUSINESS DOMAIN MODELS
│   │   └── v1/                       #    (Source of truth)
│   │       │
│   │       ├── json/                 # JSON Schema (validation)
│   │       │   ├── league.schema.json
│   │       │   ├── team.schema.json
│   │       │   ├── player.schema.json
│   │       │   ├── contract.schema.json
│   │       │   └── README.md
│   │       │
│   │       ├── neo4j/                # Neo4j Cypher (graph)
│   │       │   ├── constraints.cypher     # CREATE CONSTRAINT
│   │       │   ├── indexes.cypher         # CREATE INDEX
│   │       │   ├── nodes.cypher           # Node definitions
│   │       │   ├── relationships.cypher   # Relationship patterns
│   │       │   └── README.md
│   │       │
│   │       └── README.md             # Domain v1 overview
│   │
│   ├── infrastructure/               # 🟢 INFRASTRUCTURE/ORM SCHEMAS
│   │   │                             #    (Implementation layer)
│   │   ├── postgresql/               # Raw PostgreSQL DDL
│   │   │   ├── core-schema.sql
│   │   │   ├── supabase/
│   │   │   │   ├── prospective_leagues.sql
│   │   │   │   ├── migrations/
│   │   │   │   │   └── 001_initial.sql
│   │   │   │   └── README.md
│   │   │   ├── migrations/
│   │   │   └── README.md
│   │   │
│   │   ├── prisma/                   # Prisma ORM
│   │   │   ├── schema.prisma
│   │   │   ├── migrations/
│   │   │   └── README.md
│   │   │
│   │   └── drizzle/                  # Drizzle ORM (future)
│   │       ├── schema.ts
│   │       ├── migrations/
│   │       └── README.md
│   │
│   └── generated/                    # 🟢 AUTO-GENERATED (do not edit!)
│       ├── adapters/
│       │   ├── python/v1/            # Pydantic models
│       │   ├── typescript/v1/        # TS + Zod
│       │   └── drizzle/v1/           # Drizzle tables
│       └── models/                   # Legacy (deprecated)
│
├── docs/                             # 🟢 DOCUMENTATION
│   ├── SCHEMA_MAPPING.md
│   ├── SCHEMA_WORKFLOW.md
│   └── ADR-001-SCHEMA-ARCHITECTURE.md
│
├── scripts/                          # 🟢 UTILITIES
│   ├── validate_schema_sync.py
│   └── regenerate_adapters.sh
│
└── CLAUDE.md

Benefits:
✅ Neo4j schemas grouped with JSON schemas (both domain models)
✅ Clear separation: domain vs infrastructure
✅ Split Neo4j file = easy to find constraints vs indexes
✅ Clear home for Prisma/Drizzle
✅ Version folders (v1, v2) group related schemas

Side-by-Side Comparison

Finding League Schema

Task	Current (Before)	Proposed (After)
Find League JSON Schema	`schemas/domain/v1/league_payload_schema.json`	`schemas/domain/v1/json/league.schema.json`
	or `schemas/domain/v1/api/league-master.schema.json`?	(Clear location!)
	(Multiple options - confusing!)
Find League Neo4j Schema	`sql/neo4j_comprehensive_schema.cypher`	`schemas/domain/v1/neo4j/nodes.cypher`
	or `sql/neo4j_league_mvp_schema.cypher`?	+ `schemas/domain/v1/neo4j/constraints.cypher`
	or `sql/optimized.neo4j.schema.cypher`?	(Split by purpose!)
	(Which one is current??)
Find League Constraints	Search `sql/neo4j_comprehensive_schema.cypher`	`schemas/domain/v1/neo4j/constraints.cypher`
	(200+ lines to scan)	(Dedicated file!)
Find PostgreSQL Schema	`sql/core-schema.sql`	`schemas/infrastructure/postgresql/core-schema.sql`
	or `sql/main_schema.sql`?	(Clear location!)
	or `sql/unified.postgresql.schema.sql`?

Adding New Entity

Step	Current (Before)	Proposed (After)
1. Create JSON Schema	Add to `schemas/domain/v1/new_entity.schema.json`	Add to `schemas/domain/v1/json/new_entity.schema.json`
2. Add Neo4j Node	Edit `sql/neo4j_comprehensive_schema.cypher`	Add to `schemas/domain/v1/neo4j/nodes.cypher`
	(Append to 200+ line file)	(Small focused file)
3. Add Constraints	Edit same `sql/neo4j_comprehensive_schema.cypher`	Add to `schemas/domain/v1/neo4j/constraints.cypher`
	(Find right section in big file)	(Dedicated file for constraints)
4. Add Indexes	Edit same `sql/neo4j_comprehensive_schema.cypher`	Add to `schemas/domain/v1/neo4j/indexes.cypher`
	(Scroll through file)	(Dedicated file for indexes)
5. Generate Adapters	`./scripts/regenerate_adapters.sh`	`./scripts/regenerate_adapters.sh`
	(Same!)	(Same!)

Neo4j Schema Split Example

Current: One Big File (neo4j_comprehensive_schema.cypher)

// =================================================================
// COMPREHENSIVE NEO4J GRAPH SCHEMA FOR SPORTS BUSINESS ECOSYSTEM
// =================================================================
 
// Enable constraints and indexes for performance
// Run these first to ensure data integrity
 
// =================================================================
// CONSTRAINTS AND INDEXES
// =================================================================
 
// Primary entity constraints
CREATE CONSTRAINT user_id_unique IF NOT EXISTS FOR (u:User) REQUIRE u.id IS UNIQUE;
CREATE CONSTRAINT league_id_unique IF NOT EXISTS FOR (l:League) REQUIRE l.id IS UNIQUE;
CREATE CONSTRAINT team_id_unique IF NOT EXISTS FOR (t:Team) REQUIRE t.id IS UNIQUE;
// ... 20 more constraints ...
 
// Performance indexes
CREATE INDEX user_email_idx IF NOT EXISTS FOR (u:User) ON (u.email);
CREATE INDEX league_name_idx IF NOT EXISTS FOR (l:League) ON (l.name);
// ... 15 more indexes ...
 
// =================================================================
// CORE ENTITIES
// =================================================================
 
// Users (League Owners, Administrators, Stakeholders)
CREATE (user_props {
    id: 'unique_id',
    email: 'user@example.com',
    // ... 50 more lines ...
});
 
// Organizations
CREATE (organization_props {
    // ... 30 more lines ...
});
 
// ... 500+ more lines ...
 
Problems:
❌ Hard to find specific constraint
❌ Hard to find specific index
❌ Constraints + indexes + nodes all mixed
❌ 700+ lines to navigate

Proposed: Split into Focused Files

schemas/domain/v1/neo4j/constraints.cypher (30 lines)

// =================================================================
// NEO4J CONSTRAINTS - Uniqueness and Existence
// =================================================================
// Apply first: cypher-shell < constraints.cypher
 
// Uniqueness constraints
CREATE CONSTRAINT user_id_unique IF NOT EXISTS
  FOR (u:User) REQUIRE u.id IS UNIQUE;
 
CREATE CONSTRAINT league_id_unique IF NOT EXISTS
  FOR (l:League) REQUIRE l.id IS UNIQUE;
 
CREATE CONSTRAINT team_id_unique IF NOT EXISTS
  FOR (t:Team) REQUIRE t.id IS UNIQUE;
 
// ... more constraints ...

schemas/domain/v1/neo4j/indexes.cypher (20 lines)

// =================================================================
// NEO4J INDEXES - Performance Optimization
// =================================================================
// Apply after constraints: cypher-shell < indexes.cypher
 
CREATE INDEX user_email_idx IF NOT EXISTS
  FOR (u:User) ON (u.email);
 
CREATE INDEX league_name_idx IF NOT EXISTS
  FOR (l:League) ON (l.name);
 
// ... more indexes ...

schemas/domain/v1/neo4j/nodes.cypher (200 lines)

// =================================================================
// NEO4J NODE DEFINITIONS - Property Templates
// =================================================================
// Reference only - shows expected properties
 
// User node template
// Properties: id, email, name, role, created_at
CREATE (user:User {
  id: 'unique_id',
  email: 'user@example.com',
  name: 'Full Name',
  role: 'league_owner'
});
 
// ... more node definitions ...

schemas/domain/v1/neo4j/relationships.cypher (100 lines)

// =================================================================
// NEO4J RELATIONSHIPS - Connection Patterns
// =================================================================
 
// League ← Team
CREATE (team:Team)-[:COMPETES_IN {
  season: '2025',
  division: 'Eastern'
}]->(league:League);
 
// ... more relationships ...

Benefits: ✅ Easy to find: "Where are constraints?" → constraints.cypher ✅ Small files (30-200 lines vs 700+ lines) ✅ Apply incrementally (constraints first, then indexes) ✅ Clear separation of concerns

Directory Tree: Before vs After

Before (Current)

database/
├── schemas/domain/v1/
│   ├── league_payload_schema.json
│   ├── team_schema.json
│   ├── api/
│   │   ├── league-master.schema.json
│   │   └── league-extracted.schema.json
│   └── combat/fighting/...
│
└── sql/
    ├── neo4j_comprehensive_schema.cypher   ← Neo4j (700 lines)
    ├── neo4j_league_mvp_schema.cypher      ← Another Neo4j?
    ├── optimized.neo4j.schema.cypher       ← Which one?
    ├── core-schema.sql                     ← PostgreSQL
    ├── main_schema.sql                     ← More PostgreSQL?
    ├── supabase_tier1_schema.sql           ← More?
    └── unified.postgresql.schema.sql       ← Which is current?

After (Proposed)

database/
└── schemas/
    ├── domain/v1/                          ← Domain models
    │   ├── json/                           ← JSON Schema (validation)
    │   │   ├── league.schema.json
    │   │   ├── team.schema.json
    │   │   ├── player.schema.json
    │   │   └── contract.schema.json
    │   │
    │   └── neo4j/                          ← Neo4j (graph)
    │       ├── constraints.cypher          ← 30 lines
    │       ├── indexes.cypher              ← 20 lines
    │       ├── nodes.cypher                ← 200 lines
    │       └── relationships.cypher        ← 100 lines
    │
    └── infrastructure/                     ← Infrastructure layer
        ├── postgresql/                     ← Raw PostgreSQL
        │   ├── core-schema.sql
        │   └── supabase/
        │       └── prospective_leagues.sql
        │
        └── prisma/                         ← Prisma ORM
            └── schema.prisma

Mental Model

Current (Confusing)

"Where's the Neo4j schema?"
→ Look in sql/ (but why? Cypher isn't SQL!)
→ Find 3 Neo4j files (which one is current??)
→ Open 700-line file
→ Search for specific constraint...

"Where's the League JSON Schema?"
→ Look in schemas/domain/v1/
→ Find league_payload_schema.json
→ Wait, there's also api/league-master.schema.json?
→ Which one should I use??

Proposed (Clear)

"Where's the Neo4j schema?"
→ Look in schemas/domain/v1/neo4j/
→ constraints.cypher for constraints
→ indexes.cypher for indexes
→ nodes.cypher for node definitions
→ Clear and focused!

"Where's the League JSON Schema?"
→ schemas/domain/v1/json/league.schema.json
→ One file, clear location!

Summary

Aspect	Before	After	Improvement
Neo4j location	`sql/` (confusing!)	`schemas/domain/v1/neo4j/`	✅ Logical grouping
Neo4j files	3 files, unclear which is current	4 focused files (split by purpose)	✅ Clear organization
JSON schemas	Scattered across v1/	Grouped in `json/`	✅ Easy to find
Infrastructure	Mixed in `sql/`	`schemas/infrastructure/`	✅ Clear separation
Prisma/Drizzle	No clear home	`schemas/infrastructure/`	✅ Logical location
Versioning	Files at v1 level	Entire v1/ versioned together	✅ Easier v2 migration

Next Step: Review REORGANIZATION_PROPOSAL.md for migration plan

Database Reorganization - COMPLETE ✅Deployment Workflow