Source: data_layer/docs/DRIZZLE_FIRST_ARCHITECTURE.md

Drizzle-First Code Generation Architecture

🎯 Overview

Single Source of Truth: Drizzle TypeScript Schemas

This architecture uses Drizzle ORM schemas as the canonical source of truth, then generates all other schema formats from them.

📝 Drizzle Schema (TypeScript) - WRITE HERE
    ↓
    ├──> 1. JSON Schema (validation, documentation)
    ├──> 2. Pydantic Models (Python validation & API)
    ├──> 3. SQLAlchemy Models (Python ORM)
    ├──> 4. SQL DDL (PostgreSQL migrations)
    ├──> 5. GraphQL SDL (GraphQL API)
    └──> 6. Neo4j Cypher (Graph database)

🚀 Quick Start

1. Define Your Schema in Drizzle

Create or edit a Drizzle schema in schemas/domain/drizzle/v1/:

// schemas/domain/drizzle/v1/my_feature/users.schema.ts
import { pgTable, text, uuid, timestamp, varchar } from "drizzle-orm/pg-core";
 
/**
 * Users in the system
 */
export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  name: varchar("name", { length: 100 }).notNull(),
  role: varchar("role", { length: 20 }).default("user").notNull(),
  created_at: timestamp("created_at").defaultNow().notNull(),
  updated_at: timestamp("updated_at").defaultNow().notNull(),
});

2. Generate Everything

# Generate all targets
python scripts/generate_from_drizzle.py
 
# Or generate specific target
python scripts/generate_from_drizzle.py --target pydantic
python scripts/generate_from_drizzle.py --target sql
 
# Or process specific schema file
python scripts/generate_from_drizzle.py --schema schemas/domain/drizzle/v1/my_feature/users.schema.ts

3. Use Generated Code

Python Backend:

# Import generated Pydantic model
from schemas.generated.from_drizzle.pydantic.users_model import User, UserCreate, UserUpdate
 
# Use in FastAPI
@app.post("/users", response_model=User)
async def create_user(user: UserCreate):
    # Automatic validation!
    return await db.create_user(user)

SQL Migrations:

# Apply generated SQL
psql -d mydatabase -f schemas/generated/from_drizzle/sql/schema.sql

GraphQL API:

# Generated GraphQL schema
query GetUsers {
  users(limit: 10) {
    id
    email
    name
    role
  }
}

📋 What Gets Generated

1. JSON Schema

Output: schemas/generated/from_drizzle/json/{table_name}.schema.json

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "$id": "https://altsportsdata.ai/schemas/users.v1.schema.json",
  "title": "users",
  "type": "object",
  "properties": {
    "id": {
      "type": "string",
      "format": "uuid"
    },
    "email": {
      "type": "string",
      "maxLength": 255
    },
    "name": {
      "type": "string",
      "maxLength": 100
    }
  },
  "required": ["email", "name"]
}

Use Cases:

API documentation
Client-side validation
OpenAPI specifications
Contract testing

2. Pydantic Models

Output: schemas/generated/from_drizzle/pydantic/{table_name}_model.py

from pydantic import BaseModel
from typing import Optional
from datetime import datetime
from uuid import UUID
 
class Users(BaseModel):
    """Model for users table"""
    id: Optional[UUID] = None
    email: str
    name: str
    role: Optional[str] = "user"
    created_at: Optional[datetime] = None
    updated_at: Optional[datetime] = None
 
    class Config:
        from_attributes = True
 
class UsersCreate(BaseModel):
    """Create model (excludes auto-generated fields)"""
    email: str
    name: str
    role: Optional[str] = None
 
class UsersUpdate(BaseModel):
    """Update model (all fields optional)"""
    email: Optional[str] = None
    name: Optional[str] = None
    role: Optional[str] = None

Use Cases:

FastAPI request/response validation
Python data validation
Type hints for Python code
Automatic OpenAPI docs

3. SQLAlchemy Models

Output: schemas/generated/from_drizzle/sqlalchemy/models.py

from sqlalchemy import Column, String, DateTime, UUID as SQLUUID
from sqlalchemy.ext.declarative import declarative_base
from uuid import uuid4
from datetime import datetime
 
Base = declarative_base()
 
class Users(Base):
    """Model for users"""
    __tablename__ = "users"
    
    id = Column(SQLUUID(as_uuid=True), primary_key=True, default=uuid4)
    email = Column(String(255), nullable=False, unique=True)
    name = Column(String(100), nullable=False)
    role = Column(String(20), nullable=False, default="user")
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, nullable=False)

Use Cases:

Python ORM queries
Complex relationship handling
Transaction management
Database introspection

4. SQL DDL

Output: schemas/generated/from_drizzle/sql/schema.sql

-- Generated from Drizzle schemas
 
CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  role VARCHAR(20) DEFAULT 'user' NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);
 
CREATE INDEX IF NOT EXISTS users_email_idx ON users(email);
CREATE INDEX IF NOT EXISTS users_role_idx ON users(role);

Use Cases:

Database migrations
Schema initialization
Development setup
Documentation

5. GraphQL SDL

Output: schemas/generated/from_drizzle/graphql/schema.graphql

# Generated from Drizzle schemas
 
scalar DateTime
scalar JSON
scalar UUID
 
"""Model for users"""
type Users {
  id: UUID!
  email: String!
  name: String!
  role: String!
  created_at: DateTime!
  updated_at: DateTime!
}
 
type Query {
  users(id: ID!): Users
  userss(limit: Int, offset: Int): [Users!]!
}

Use Cases:

GraphQL API
Frontend type generation (with GraphQL Codegen)
API documentation
Client queries

6. Neo4j Cypher

Output: schemas/generated/from_drizzle/neo4j/schema.cypher

// Generated from Drizzle schemas
 
CREATE CONSTRAINT users_id_unique IF NOT EXISTS
FOR (n:Users) REQUIRE n.id IS UNIQUE;
 
CREATE INDEX users_email_index IF NOT EXISTS
FOR (n:Users) ON (n.email);
 
CREATE INDEX users_role_index IF NOT EXISTS
FOR (n:Users) ON (n.role);

Use Cases:

Graph database setup
Relationship modeling
Constraint enforcement
Index optimization

🏗️ Best Practices

1. Schema Organization

schemas/domain/drizzle/v1/
├── core/                    # Core business entities
│   ├── users.schema.ts
│   ├── organizations.schema.ts
│   └── permissions.schema.ts
├── sports/                  # Sports domain
│   ├── leagues.schema.ts
│   ├── teams.schema.ts
│   └── players.schema.ts
├── combat/                  # Combat sports vertical
│   ├── fighters.schema.ts
│   ├── matches.schema.ts
│   └── events.schema.ts
└── examples/                # Example schemas
    └── league_example.schema.ts

2. Naming Conventions

Tables: snake_case

export const sport_archetypes = pgTable("sport_archetypes", { ... });
export const league_relationships = pgTable("league_relationships", { ... });

Columns: snake_case

{
  league_name: varchar("league_name", { length: 255 }),
  sport_archetype_id: uuid("sport_archetype_id"),
  created_at: timestamp("created_at").defaultNow(),
}

TypeScript Constants: snake_case (matches table name)

export const sport_archetypes = pgTable(...);  // ✅ Good
export const SportArchetypes = pgTable(...);   // ❌ Bad

3. Always Use These Patterns

Primary Keys:

// ✅ Always use UUID with defaultRandom()
id: uuid("id").primaryKey().defaultRandom(),
 
// ❌ Avoid auto-increment integers
id: serial("id").primaryKey(),  // Don't use this

Timestamps:

// ✅ Always include these two
created_at: timestamp("created_at").defaultNow().notNull(),
updated_at: timestamp("updated_at").defaultNow().notNull(),

Foreign Keys:

// ✅ Use references() with onDelete
league_id: uuid("league_id")
  .notNull()
  .references(() => leagues.id, { onDelete: 'cascade' }),
 
// ✅ Add index for FK
leagueIdx: index("documents_league_idx").on(table.league_id),

JSONB Columns:

// ✅ Use JSONB with default empty object/array
metadata: jsonb("metadata").default({}),
tags: jsonb("tags").default([]),
 
// ❌ Don't use JSON (JSONB is faster)
metadata: json("metadata"),

Indexes:

// ✅ Define indexes in the second argument
export const leagues = pgTable("leagues", {
  status: varchar("status", { length: 20 }),
  tier: varchar("tier", { length: 20 }),
}, (table) => ({
  statusIdx: index("leagues_status_idx").on(table.status),
  tierIdx: index("leagues_tier_idx").on(table.tier),
  
  // Composite index
  statusTierIdx: index("leagues_status_tier_idx").on(
    table.status,
    table.tier
  ),
}));

4. Multi-Database Sync Tracking

If using Neo4j/Pinecone:

export const leagues = pgTable("leagues", {
  // ... other columns ...
  
  // Neo4j sync tracking
  neo4j_node_id: varchar("neo4j_node_id", { length: 255 }),
  neo4j_synced_at: timestamp("neo4j_synced_at"),
  neo4j_sync_status: varchar("neo4j_sync_status", { length: 20 }).default("pending"),
  
  // Pinecone sync tracking
  pinecone_id: varchar("pinecone_id", { length: 255 }),
  pinecone_synced_at: timestamp("pinecone_synced_at"),
  pinecone_sync_status: varchar("pinecone_sync_status", { length: 20 }).default("pending"),
});

5. JSDoc Comments

/**
 * Leagues represent sports organizations and competitions
 * 
 * A league can have multiple documents, relationships to other leagues,
 * and tags for categorization.
 */
export const leagues = pgTable("leagues", {
  // Column definitions...
});

🔄 Development Workflow

Typical Day-to-Day Flow

# 1. Edit Drizzle schema
vim schemas/domain/drizzle/v1/sports/leagues.schema.ts
 
# 2. Generate all targets
python scripts/generate_from_drizzle.py
 
# 3. Apply SQL migration
psql -d mydb -f schemas/generated/from_drizzle/sql/schema.sql
 
# 4. Use generated Python models
# (automatically imported, no manual step needed)
 
# 5. Commit everything
git add schemas/
git commit -m "Add new league fields"

When Adding a New Feature

# 1. Create new schema file
mkdir -p schemas/domain/drizzle/v1/my_feature
vim schemas/domain/drizzle/v1/my_feature/my_table.schema.ts
 
# 2. Generate
python scripts/generate_from_drizzle.py --schema schemas/domain/drizzle/v1/my_feature/my_table.schema.ts
 
# 3. Create migration
cat schemas/generated/from_drizzle/sql/schema.sql > migrations/$(date +%Y%m%d_%H%M%S)_add_my_table.sql
 
# 4. Test Pydantic models
python -c "from schemas.generated.from_drizzle.pydantic.my_table_model import MyTable; print(MyTable.schema_json(indent=2))"
 
# 5. Commit
git add schemas/ migrations/
git commit -m "Add my_feature schema"

🧪 Testing Generated Code

Test Pydantic Models

from schemas.generated.from_drizzle.pydantic.users_model import UserCreate
 
# Valid data
user = UserCreate(email="test@test.com", name="Test User")
assert user.email == "test@test.com"
 
# Invalid data (raises ValidationError)
try:
    UserCreate(email="invalid", name="Test")  # Invalid email
except ValidationError as e:
    print("Validation works!")

Test SQL Schema

-- Run generated SQL
\i schemas/generated/from_drizzle/sql/schema.sql
 
-- Test insertion
INSERT INTO users (email, name, role) VALUES ('test@test.com', 'Test', 'user');
 
-- Verify constraints
SELECT * FROM users WHERE email = 'test@test.com';

Test GraphQL Schema

# Use with GraphQL Codegen
npm install -D @graphql-codegen/cli @graphql-codegen/typescript
 
# Generate TypeScript types from GraphQL
graphql-codegen --schema schemas/generated/from_drizzle/graphql/schema.graphql

🔧 Advanced Usage

Custom Generation

You can modify scripts/generate_from_drizzle.py to add custom generators:

class CustomGenerator:
    """Generate custom format from Drizzle tables"""
    
    def generate(self, tables: List[Table], output_dir: Path) -> None:
        for table in tables:
            # Your custom logic here
            pass

Selective Generation

# Only generate SQL
python scripts/generate_from_drizzle.py --target sql
 
# Only process combat schemas
python scripts/generate_from_drizzle.py --schema "schemas/domain/drizzle/v1/combat/**/*.ts"

Output to Custom Directory

python scripts/generate_from_drizzle.py --output /path/to/output

📊 Architecture Comparison

Before (Multiple Sources of Truth)

❌ JSON Schema → Pydantic ❌
❌ Prisma Schema → TypeScript ❌
❌ SQL Files → Database ❌
❌ GraphQL SDL → API ❌

Problem: Keeping 4+ schemas in sync manually

After (Single Source of Truth)

✅ Drizzle Schema (TypeScript)
    ↓ (automatic generation)
    ├──> JSON Schema
    ├──> Pydantic
    ├──> SQL
    ├──> GraphQL
    ├──> Neo4j
    └──> SQLAlchemy

Solution: Edit once, generate everything

🎓 Why Drizzle as Source of Truth?

Advantages

Type Safety at Schema Level
- TypeScript provides immediate feedback
- IDE autocomplete for column definitions
- Compile-time error checking
Rich Schema Features
- Foreign keys with cascade actions
- Composite indexes
- Unique constraints
- Default values
- JSONB support
Migration Support
- drizzle-kit for migrations
- Schema introspection
- Automatic migration generation
Best DX (Developer Experience)
- Beautiful TypeScript syntax
- Excellent documentation
- Active community
Works with Existing Drizzle Code
- If you use Drizzle in Node.js/TypeScript backend
- Can share schemas across services
- Type-safe queries in TS

🚨 Common Issues

Issue: Parser Doesn't Recognize My Schema

Solution: Make sure you're using the correct Drizzle syntax:

// ✅ Correct
export const my_table = pgTable("my_table", { ... });
 
// ❌ Wrong (default export)
export default pgTable("my_table", { ... });

Issue: Generated Python Imports Don't Work

Solution: Make sure you're importing from the correct path:

# ✅ Correct
from schemas.generated.from_drizzle.pydantic.users_model import Users
 
# ❌ Wrong (old path)
from database.schemas.generated.adapters.python.v1.users import Users

Issue: SQL Migration Fails

Solution: Check for:

Circular foreign key dependencies
Missing tables (run generation for all schemas)
Existing tables with different structure

📚 See Also

🤝 Contributing

When adding new generators:

Add generator class to scripts/generate_from_drizzle.py
Implement generate(tables: List[Table], output_dir: Path) method
Add target to generators dict in main()
Update this README
Add tests

Questions? Check the FAQ or examples.

Deployment Workflow Drizzle-First Schema System

Drizzle-First Code Generation Architecture

🎯 Overview

🚀 Quick Start

1. Define Your Schema in Drizzle

2. Generate Everything

3. Use Generated Code

📋 What Gets Generated

1. JSON Schema

2. Pydantic Models

3. SQLAlchemy Models

4. SQL DDL

5. GraphQL SDL

6. Neo4j Cypher

🏗️ Best Practices

1. Schema Organization

2. Naming Conventions

3. Always Use These Patterns

4. Multi-Database Sync Tracking

5. JSDoc Comments

🔄 Development Workflow

Typical Day-to-Day Flow

When Adding a New Feature

🧪 Testing Generated Code

Test Pydantic Models

Test SQL Schema

Test GraphQL Schema

🔧 Advanced Usage

Custom Generation

Selective Generation

Output to Custom Directory

📊 Architecture Comparison

Before (Multiple Sources of Truth)

After (Single Source of Truth)

🎓 Why Drizzle as Source of Truth?

Advantages

🚨 Common Issues

Issue: Parser Doesn't Recognize My Schema

Issue: Generated Python Imports Don't Work

Issue: SQL Migration Fails

📚 See Also

🤝 Contributing

Platform

Documentation

Community

Support