./browse/prompts

# Narrative Control Prompt: Exhaustive System Architecture & Feature Reverse-Engineering You are an **Expert Enterprise Architect, Product Director, and Lead Engineer**. Your goal is to thoroughly analyze the provided documentation to architect a full-scale, competitive enterprise application. You must deconstruct the feature described in the content into **extensive, granular technical specifications** across multiple engineering disciplines. **CRITICAL INSTRUCTIONS**: - **DO NOT SUMMARIZE**. Be exhaustive. - For every category below, aim to list **10+ specific items** if possible. - Brainstorm every possible implication, edge case, and requirement derived from or inspired by the text. - If the text mentions a "search" feature, break it down into: Indexing, Query Parsing, UI Widgets, Highlighting, Filtering, Sort Logic, Caching, etc. Please output the analysis in the following Markdown format: # 1. Product Strategy & Scope * **Feature Name**: * **Core Value Proposition**: [Deep dive into why this exists] * **User Personas**: [List as many as applicable: e.g. Admin, Power User, Viewer, Auditor, API Consumer...] * **User Stories**: [Extensive list of 10+ granular user stories e.g. "As a User, I want to..."] * **Competitive Differentiators**: [What makes this specific implementation valuable?] # 2. Design & User Experience (UX/UI) * **Key Interface Components**: [List 10+ atoms/molecules: e.g. Data Grid, Filter Chips, Modals, Tooltips, Empty States, Toasts, Dropdowns...] * **Interaction Patterns**: [List 10+ patterns: e.g. Drag-and-drop, Double-click to edit, Hover states, Keyboard shortcuts, Infinite scroll...] * **Visual States**: [List all states: Loading, Success, Error, Warning, Partial Data, Offline...] * **Accessibility (a11y)**: [List 10+ checks: Contrast, ARIA labels, Focus management, Screen reader support, Resizing...] # 3. Frontend Engineering * **State Management**: [List 10+ state atoms: Upload progress %, Selected ID list, Sort order, Filter criteria, Current user permissions...] * **API Interactions**: [List 10+ potential endpoints: GET/POST/PUT/DELETE for main entities, Lookups, Search, Validation...] * **Component Architecture**: [List 10+ React/Vue components: Container, Presentation, Utility wrappers, HoC...] * **Client-Side Logic**: [Validation rules, Formatting (Dates/Currency), Debouncing, Caching...] # 4. Backend Engineering * **Data Models**: [List 10+ fields/entities: Table structure, Foreign keys, Indexes, JSONB fields, Audit columns...] * **API Specification**: [Detailed endpoint contract: Header requirements, Query params, Body schema, Error codes...] * **Business Logic**: [List 10+ rules: Permission checks, Data transformation, Workflows, Triggers, Notifications...] * **Security & Permissions**: [List 10+ checks: RBAC roles, Field-level security, API Rate limiting, CSRF protection...] # 5. Infrastructure & DevOps * **Storage Requirements**: [S3 buckets, Database types (SQL/NoSQL), Redis for cache, CDNs...] * **Compute Needs**: [Async workers, Scheduled cron jobs, Serverless functions, Container specs...] * **Background Jobs**: [List 10+ potential jobs: Email sending, File conversion, Indexing, Cleanup, Analytics aggregation...] * **Observability**: [Metrics to track: API latency, Error rates, Disk usage, Active users...] # 6. Quality Assurance (QA) * **Test Scenarios**: [List 10+ happy path scenarios] * **Edge Cases**: [List 10+ negative/edge cases: Network fail, Giant files, Concurrent edits, Invalid chars...] * **Performance Metrics**: [Specific SLAs: <200ms API, <1s Page load, 99.9% Uptime...] * **Security Testing**: [Pen-test vectors: XSS injection input, SQL injection, IDOR...] # 7. Documentation & Onboarding * **User Guides Needed**: [List 10+ articles to write based on this feature] * **Contextual Help**: [List 10+ places for Tooltips, Tours, Helper text...] * **API Documentation**: [Swagger/OpenAPI requirements] # 8. Implementation Roadmap * **Phase 1 (MVP)**: [List 10+ must-have tasks] * **Phase 2 (Enhanced)**: [List 10+ nice-to-have features] * **Phase 3 (Scale)**: [Optimization and enterprise hardening] --- **Context**: The content below is raw markdown from a help guide.

# Narrative Control Prompt: Exhaustive System Architecture & Feature Reverse-Engineering You are an **Expert Enterprise Architect, Product Director, and Lead Engineer**. Your goal is to thoroughly analyze the provided documentation to architect a full-scale, competitive enterprise application. You must deconstruct the feature described in the content into **extensive, granular technical specifications** across multiple engineering disciplines. **CRITICAL INSTRUCTIONS**: - **DO NOT SUMMARIZE**. Be exhaustive. - For every category below, aim to list **10+ specific items** if possible. - Brainstorm every possible implication, edge case, and requirement derived from or inspired by the text. - If the text mentions a "search" feature, break it down into: Indexing, Query Parsing, UI Widgets, Highlighting, Filtering, Sort Logic, Caching, etc. Please output the analysis in the following Markdown format: # 1. Product Strategy & Scope * **Feature Name**: * **Core Value Proposition**: [Deep dive into why this exists] * **User Personas**: [List as many as applicable: e.g. Admin, Power User, Viewer, Auditor, API Consumer...] * **User Stories**: [Extensive list of 10+ granular user stories e.g. "As a User, I want to..."] * **Competitive Differentiators**: [What makes this specific implementation valuable?] # 2. Design & User Experience (UX/UI) * **Key Interface Components**: [List 10+ atoms/molecules: e.g. Data Grid, Filter Chips, Modals, Tooltips, Empty States, Toasts, Dropdowns...] * **Interaction Patterns**: [List 10+ patterns: e.g. Drag-and-drop, Double-click to edit, Hover states, Keyboard shortcuts, Infinite scroll...] * **Visual States**: [List all states: Loading, Success, Error, Warning, Partial Data, Offline...] * **Accessibility (a11y)**: [List 10+ checks: Contrast, ARIA labels, Focus management, Screen reader support, Resizing...] # 3. Frontend Engineering * **State Management**: [List 10+ state atoms: Upload progress %, Selected ID list, Sort order, Filter criteria, Current user permissions...] * **API Interactions**: [List 10+ potential endpoints: GET/POST/PUT/DELETE for main entities, Lookups, Search, Validation...] * **Component Architecture**: [List 10+ React/Vue components: Container, Presentation, Utility wrappers, HoC...] * **Client-Side Logic**: [Validation rules, Formatting (Dates/Currency), Debouncing, Caching...] # 4. Backend Engineering * **Data Models**: [List 10+ fields/entities: Table structure, Foreign keys, Indexes, JSONB fields, Audit columns...] * **API Specification**: [Detailed endpoint contract: Header requirements, Query params, Body schema, Error codes...] * **Business Logic**: [List 10+ rules: Permission checks, Data transformation, Workflows, Triggers, Notifications...] * **Security & Permissions**: [List 10+ checks: RBAC roles, Field-level security, API Rate limiting, CSRF protection...] # 5. Infrastructure & DevOps * **Storage Requirements**: [S3 buckets, Database types (SQL/NoSQL), Redis for cache, CDNs...] * **Compute Needs**: [Async workers, Scheduled cron jobs, Serverless functions, Container specs...] * **Background Jobs**: [List 10+ potential jobs: Email sending, File conversion, Indexing, Cleanup, Analytics aggregation...] * **Observability**: [Metrics to track: API latency, Error rates, Disk usage, Active users...] # 6. Quality Assurance (QA) * **Test Scenarios**: [List 10+ happy path scenarios] * **Edge Cases**: [List 10+ negative/edge cases: Network fail, Giant files, Concurrent edits, Invalid chars...] * **Performance Metrics**: [Specific SLAs: <200ms API, <1s Page load, 99.9% Uptime...] * **Security Testing**: [Pen-test vectors: XSS injection input, SQL injection, IDOR...] # 7. Documentation & Onboarding * **User Guides Needed**: [List 10+ articles to write based on this feature] * **Contextual Help**: [List 10+ places for Tooltips, Tours, Helper text...] * **API Documentation**: [Swagger/OpenAPI requirements] # 8. Implementation Roadmap * **Phase 1 (MVP)**: [List 10+ must-have tasks] * **Phase 2 (Enhanced)**: [List 10+ nice-to-have features] * **Phase 3 (Scale)**: [Optimization and enterprise hardening] --- **Context**: The content below is raw markdown from a help guide.

# Parallel Feature Development Strategies for decomposing features into parallel work streams, establishing file ownership boundaries, avoiding conflicts, and integrating results from multiple implementer agents. ## When to Use This Skill - Decomposing a feature for parallel implementation - Establishing file ownership boundaries between agents - Designing interface contracts between parallel work streams - Choosing integration strategies (vertical slice vs horizontal layer) - Managing branch and merge workflows for parallel development ## File Ownership Strategies ### By Directory Assign each implementer ownership of specific directories: ``` implementer-1: src/components/auth/ implementer-2: src/api/auth/ implementer-3: tests/auth/ ``` **Best for**: Well-organized codebases with clear directory boundaries. ### By Module Assign ownership of logical modules (which may span directories): ``` implementer-1: Authentication module (login, register, logout) implementer-2: Authorization module (roles, permissions, guards) ``` **Best for**: Feature-oriented architectures, domain-driven design. ### By Layer Assign ownership of architectural layers: ``` implementer-1: UI layer (components, styles, layouts) implementer-2: Business logic layer (services, validators) implementer-3: Data layer (models, repositories, migrations) ``` **Best for**: Traditional MVC/layered architectures. ## Conflict Avoidance Rules ### The Cardinal Rule **One owner per file.** No file should be assigned to multiple implementers. ### When Files Must Be Shared If a file genuinely needs changes from multiple implementers: 1. **Designate a single owner** — One implementer owns the file 2. **Other implementers request changes** — Message the owner with specific change requests 3. **Owner applies changes sequentially** — Prevents merge conflicts 4. **Alternative: Extract interfaces** — Create a separate interface file that the non-owner can import without modifying ### Interface Contracts When implementers need to coordinate at boundaries: ```typescript // src/types/auth-contract.ts (owned by team-lead, read-only for implementers) export interface AuthResponse { token: string; user: UserProfile; expiresAt: number; } export interface AuthService { login(email: string, password: string): Promise<AuthResponse>; register(data: RegisterData): Promise<AuthResponse>; } ``` Both implementers import from the contract file but neither modifies it. ## Integration Patterns ### Vertical Slice Each implementer builds a complete feature slice (UI + API + tests): ``` implementer-1: Login feature (login form + login API + login tests) implementer-2: Register feature (register form + register API + register tests) ``` **Pros**: Each slice is independently testable, minimal integration needed. **Cons**: May duplicate shared utilities, harder with tightly coupled features. ### Horizontal Layer Each implementer builds one layer across all features: ``` implementer-1: All UI components (login form, register form, profile page) implementer-2: All API endpoints (login, register, profile) implementer-3: All tests (unit, integration, e2e) ``` **Pros**: Consistent patterns within each layer, natural specialization. **Cons**: More integration points, layer 3 depends on layers 1 and 2. ### Hybrid Mix vertical and horizontal based on coupling: ``` implementer-1: Login feature (vertical slice — UI + API + tests) implementer-2: Shared auth infrastructure (horizontal — middleware, JWT utils, types) ``` **Best for**: Most real-world features with some shared infrastructure. ## Branch Management ### Single Branch Strategy All implementers work on the same feature branch: - Simple setup, no merge overhead - Requires strict file ownership to avoid conflicts - Best for: small teams (2-3), well-defined boundaries ### Multi-Branch Strategy Each implementer works on a sub-branch: ``` feature/auth ├── feature/auth-login (implementer-1) ├── feature/auth-register (implementer-2) └── feature/auth-tests (implementer-3) ``` - More isolation, explicit merge points - Higher overhead, merge conflicts still possible in shared files - Best for: larger teams (4+), complex features

# Task Coordination Strategies Strategies for decomposing complex tasks into parallelizable units, designing dependency graphs, writing effective task descriptions, and monitoring workload across agent teams. ## When to Use This Skill - Breaking down a complex task for parallel execution - Designing task dependency relationships (blockedBy/blocks) - Writing task descriptions with clear acceptance criteria - Monitoring and rebalancing workload across teammates - Identifying the critical path in a multi-task workflow ## Task Decomposition Strategies ### By Layer Split work by architectural layer: - Frontend components - Backend API endpoints - Database migrations/models - Test suites **Best for**: Full-stack features, vertical slices ### By Component Split work by functional component: - Authentication module - User profile module - Notification module **Best for**: Microservices, modular architectures ### By Concern Split work by cross-cutting concern: - Security review - Performance review - Architecture review **Best for**: Code reviews, audits ### By File Ownership Split work by file/directory boundaries: - `src/components/` — Implementer 1 - `src/api/` — Implementer 2 - `src/utils/` — Implementer 3 **Best for**: Parallel implementation, conflict avoidance ## Dependency Graph Design ### Principles 1. **Minimize chain depth** — Prefer wide, shallow graphs over deep chains 2. **Identify the critical path** — The longest chain determines minimum completion time 3. **Use blockedBy sparingly** — Only add dependencies that are truly required 4. **Avoid circular dependencies** — Task A blocks B blocks A is a deadlock ### Patterns **Independent (Best parallelism)**: ``` Task A ─┐ Task B ─┼─→ Integration Task C ─┘ ``` **Sequential (Necessary dependencies)**: ``` Task A → Task B → Task C ``` **Diamond (Mixed)**: ``` ┌→ Task B ─┐ Task A ─┤ ├→ Task D └→ Task C ─┘ ``` ### Using blockedBy/blocks ``` TaskCreate: { subject: "Build API endpoints" } → Task #1 TaskCreate: { subject: "Build frontend components" } → Task #2 TaskCreate: { subject: "Integration testing" } → Task #3 TaskUpdate: { taskId: "3", addBlockedBy: ["1", "2"] } → #3 waits for #1 and #2 ``` ## Task Description Best Practices Every task should include: 1. **Objective** — What needs to be accomplished (1-2 sentences) 2. **Owned Files** — Explicit list of files/directories this teammate may modify 3. **Requirements** — Specific deliverables or behaviors expected 4. **Interface Contracts** — How this work connects to other teammates' work 5. **Acceptance Criteria** — How to verify the task is done correctly 6. **Scope Boundaries** — What is explicitly out of scope ### Template ``` ## Objective Build the user authentication API endpoints. ## Owned Files - src/api/auth.ts - src/api/middleware/auth-middleware.ts - src/types/auth.ts (shared — read only, do not modify) ## Requirements - POST /api/login — accepts email/password, returns JWT - POST /api/register — creates new user, returns JWT - GET /api/me — returns current user profile (requires auth) ## Interface Contract - Import User type from src/types/auth.ts (owned by implementer-1) - Export AuthResponse type for frontend consumption ## Acceptance Criteria - All endpoints return proper HTTP status codes - JWT tokens expire after 24 hours - Passwords are hashed with bcrypt ## Out of Scope - OAuth/social login - Password reset flow - Rate limiting ``` ## Workload Monitoring ### Indicators of Imbalance | Signal | Meaning | Action | | -------------------------- | ------------------- | --------------------------- | | Teammate idle, others busy | Uneven distribution | Reassign pending tasks | | Teammate stuck on one task | Possible blocker | Check in, offer help | | All tasks blocked | Dependency issue | Resolve critical path first | | One teammate has 3x others | Overloaded | Split tasks or reassign | ### Rebalancing Steps 1. Call `TaskList` to assess current state 2. Identify idle or overloaded teammates 3. Use `TaskUpdate` to reassign tasks 4. Use `SendMessage` to notify affected teammates 5. Monitor for improved throughput

# FastAPI Project Templates Production-ready FastAPI project structures with async patterns, dependency injection, middleware, and best practices for building high-performance APIs. ## When to Use This Skill - Starting new FastAPI projects from scratch - Implementing async REST APIs with Python - Building high-performance web services and microservices - Creating async applications with PostgreSQL, MongoDB - Setting up API projects with proper structure and testing ## Core Concepts ### 1. Project Structure **Recommended Layout:** ``` app/ ├── api/ # API routes │ ├── v1/ │ │ ├── endpoints/ │ │ │ ├── users.py │ │ │ ├── auth.py │ │ │ └── items.py │ │ └── router.py │ └── dependencies.py # Shared dependencies ├── core/ # Core configuration │ ├── config.py │ ├── security.py │ └── database.py ├── models/ # Database models │ ├── user.py │ └── item.py ├── schemas/ # Pydantic schemas │ ├── user.py │ └── item.py ├── services/ # Business logic │ ├── user_service.py │ └── auth_service.py ├── repositories/ # Data access │ ├── user_repository.py │ └── item_repository.py └── main.py # Application entry ``` ### 2. Dependency Injection FastAPI's built-in DI system using `Depends`: - Database session management - Authentication/authorization - Shared business logic - Configuration injection ### 3. Async Patterns Proper async/await usage: - Async route handlers - Async database operations - Async background tasks - Async middleware ## Implementation Patterns ### Pattern 1: Complete FastAPI Application ```python # main.py from fastapi import FastAPI, Depends from fastapi.middleware.cors import CORSMiddleware from contextlib import asynccontextmanager @asynccontextmanager async def lifespan(app: FastAPI): """Application lifespan events.""" # Startup await database.connect() yield # Shutdown await database.disconnect() app = FastAPI( title="API Template", version="1.0.0", lifespan=lifespan ) # CORS middleware app.add_middleware( CORSMiddleware, allow_origins=["*"], allow_credentials=True, allow_methods=["*"], allow_headers=["*"], ) # Include routers from app.api.v1.router import api_router app.include_router(api_router, prefix="/api/v1") # core/config.py from pydantic_settings import BaseSettings from functools import lru_cache class Settings(BaseSettings): """Application settings.""" DATABASE_URL: str SECRET_KEY: str ACCESS_TOKEN_EXPIRE_MINUTES: int = 30 API_V1_STR: str = "/api/v1" class Config: env_file = ".env" @lru_cache() def get_settings() -> Settings: return Settings() # core/database.py from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker from app.core.config import get_settings settings = get_settings() engine = create_async_engine( settings.DATABASE_URL, echo=True, future=True ) AsyncSessionLocal = sessionmaker( engine, class_=AsyncSession, expire_on_commit=False ) Base = declarative_base() async def get_db() -> AsyncSession: """Dependency for database session.""" async with AsyncSessionLocal() as session: try: yield session await session.commit() except Exception: await session.rollback() raise finally: await session.close() ``` ### Pattern 2: CRUD Repository Pattern ```python # repositories/base_repository.py from typing import Generic, TypeVar, Type, Optional, List from sqlalchemy.ext.asyncio import AsyncSession from sqlalchemy import select from pydantic import BaseModel ModelType = TypeVar("ModelType") CreateSchemaType = TypeVar("CreateSchemaType", bound=BaseModel) UpdateSchemaType = TypeVar("UpdateSchemaType", bound=BaseModel) class BaseRepository(Generic[ModelType, CreateSchemaType, UpdateSchemaType]): """Base repository for CRUD operations.""" def __init__(self, model: Type[ModelType]): self.model = model async def get(self, db: AsyncSession, id: int) -> Optional[ModelType]: """Get by ID.""" result = await db.execute( select(self.model).where(self.model.id == id) ) return result.scalars().first() async def get_multi( self, db: AsyncSession, skip: int = 0, limit: int = 100 ) -> List[ModelType]: """Get multiple records.""" result = await db.execute( select(self.model).offset(skip).limit(limit) ) return result.scalars().all() async def create( self, db: AsyncSession, obj_in: CreateSchemaType ) -> ModelType: """Create new record.""" db_obj = self.model(**obj_in.dict()) db.add(db_obj) await db.flush() await db.refresh(db_obj) return db_obj async def update( self, db: AsyncSession, db_obj: ModelType, obj_in: UpdateSchemaType ) -> ModelType: """Update record.""" update_data = obj_in.dict(exclude_unset=True) for field, value in update_data.items(): setattr(db_obj, field, value) await db.flush() await db.refresh(db_obj) return db_obj async def delete(self, db: AsyncSession, id: int) -> bool: """Delete record.""" obj = await self.get(db, id) if obj: await db.delete(obj) return True return False # repositories/user_repository.py from app.repositories.base_repository import BaseRepository from app.models.user import User from app.schemas.user import UserCreate, UserUpdate class UserRepository(BaseRepository[User, UserCreate, UserUpdate]): """User-specific repository.""" async def get_by_email(self, db: AsyncSession, email: str) -> Optional[User]: """Get user by email.""" result = await db.execute( select(User).where(User.email == email) ) return result.scalars().first() async def is_active(self, db: AsyncSession, user_id: int) -> bool: """Check if user is active.""" user = await self.get(db, user_id) return user.is_active if user else False user_repository = UserRepository(User) ``` ### Pattern 3: Service Layer ```python # services/user_service.py from typing import Optional from sqlalchemy.ext.asyncio import AsyncSession from app.repositories.user_repository import user_repository from app.schemas.user import UserCreate, UserUpdate, User from app.core.security import get_password_hash, verify_password class UserService: """Business logic for users.""" def __init__(self): self.repository = user_repository async def create_user( self, db: AsyncSession, user_in: UserCreate ) -> User: """Create new user with hashed password.""" # Check if email exists existing = await self.repository.get_by_email(db, user_in.email) if existing: raise ValueError("Email already registered") # Hash password user_in_dict = user_in.dict() user_in_dict["hashed_password"] = get_password_hash(user_in_dict.pop("password")) # Create user user = await self.repository.create(db, UserCreate(**user_in_dict)) return user async def authenticate( self, db: AsyncSession, email: str, password: str ) -> Optional[User]: """Authenticate user.""" user = await self.repository.get_by_email(db, email) if not user: return None if not verify_password(password, user.hashed_password): return None return user async def update_user( self, db: AsyncSession, user_id: int, user_in: UserUpdate ) -> Optional[User]: """Update user.""" user = await self.repository.get(db, user_id) if not user: return None if user_in.password: user_in_dict = user_in.dict(exclude_unset=True) user_in_dict["hashed_password"] = get_password_hash( user_in_dict.pop("password") ) user_in = UserUpdate(**user_in_dict) return await self.repository.update(db, user, user_in) user_service = UserService() ``` ### Pattern 4: API Endpoints with Dependencies ```python # api/v1/endpoints/users.py from fastapi import APIRouter, Depends, HTTPException, status from sqlalchemy.ext.asyncio import AsyncSession from typing import List from app.core.database import get_db from app.schemas.user import User, UserCreate, UserUpdate from app.services.user_service import user_service from app.api.dependencies import get_current_user router = APIRouter() @router.post("/", response_model=User, status_code=status.HTTP_201_CREATED) async def create_user( user_in: UserCreate, db: AsyncSession = Depends(get_db) ): """Create new user.""" try: user = await user_service.create_user(db, user_in) return user except ValueError as e: raise HTTPException(status_code=400, detail=str(e)) @router.get("/me", response_model=User) async def read_current_user( current_user: User = Depends(get_current_user) ): """Get current user.""" return current_user @router.get("/{user_id}", response_model=User) async def read_user( user_id: int, db: AsyncSession = Depends(get_db), current_user: User = Depends(get_current_user) ): """Get user by ID.""" user = await user_service.repository.get(db, user_id) if not user: raise HTTPException(status_code=404, detail="User not found") return user @router.patch("/{user_id}", response_model=User) async def update_user( user_id: int, user_in: UserUpdate, db: AsyncSession = Depends(get_db), current_user: User = Depends(get_current_user) ): """Update user.""" if current_user.id != user_id: raise HTTPException(status_code=403, detail="Not authorized") user = await user_service.update_user(db, user_id, user_in) if not user: raise HTTPException(status_code=404, detail="User not found") return user @router.delete("/{user_id}", status_code=status.HTTP_204_NO_CONTENT) async def delete_user( user_id: int, db: AsyncSession = Depends(get_db), current_user: User = Depends(get_current_user) ): """Delete user.""" if current_user.id != user_id: raise HTTPException(status_code=403, detail="Not authorized") deleted = await user_service.repository.delete(db, user_id) if not deleted: raise HTTPException(status_code=404, detail="User not found") ``` ### Pattern 5: Authentication & Authorization ```python # core/security.py from datetime import datetime, timedelta from typing import Optional from jose import JWTError, jwt from passlib.context import CryptContext from app.core.config import get_settings settings = get_settings() pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto") ALGORITHM = "HS256" def create_access_token(data: dict, expires_delta: Optional[timedelta] = None): """Create JWT access token.""" to_encode = data.copy() if expires_delta: expire = datetime.utcnow() + expires_delta else: expire = datetime.utcnow() + timedelta(minutes=15) to_encode.update({"exp": expire}) encoded_jwt = jwt.encode(to_encode, settings.SECRET_KEY, algorithm=ALGORITHM) return encoded_jwt def verify_password(plain_password: str, hashed_password: str) -> bool: """Verify password against hash.""" return pwd_context.verify(plain_password, hashed_password) def get_password_hash(password: str) -> str: """Hash password.""" return pwd_context.hash(password) # api/dependencies.py from fastapi import Depends, HTTPException, status from fastapi.security import OAuth2PasswordBearer from jose import JWTError, jwt from sqlalchemy.ext.asyncio import AsyncSession from app.core.database import get_db from app.core.security import ALGORITHM from app.core.config import get_settings from app.repositories.user_repository import user_repository oauth2_scheme = OAuth2PasswordBearer(tokenUrl=f"{settings.API_V1_STR}/auth/login") async def get_current_user( db: AsyncSession = Depends(get_db), token: str = Depends(oauth2_scheme) ): """Get current authenticated user.""" credentials_exception = HTTPException( status_code=status.HTTP_401_UNAUTHORIZED, detail="Could not validate credentials", headers={"WWW-Authenticate": "Bearer"}, ) try: payload = jwt.decode(token, settings.SECRET_KEY, algorithms=[ALGORITHM]) user_id: int = payload.get("sub") if user_id is None: raise credentials_exception except JWTError: raise credentials_exception user = await user_repository.get(db, user_id) if user is None: raise credentials_exception return user ``` ## Testing ```python # tests/conftest.py import pytest import asyncio from httpx import AsyncClient from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession from sqlalchemy.orm import sessionmaker from app.main import app from app.core.database import get_db, Base TEST_DATABASE_URL = "sqlite+aiosqlite:///:memory:" @pytest.fixture(scope="session") def event_loop(): loop = asyncio.get_event_loop_policy().new_event_loop() yield loop loop.close() @pytest.fixture async def db_session(): engine = create_async_engine(TEST_DATABASE_URL, echo=True) async with engine.begin() as conn: await conn.run_sync(Base.metadata.create_all) AsyncSessionLocal = sessionmaker( engine, class_=AsyncSession, expire_on_commit=False ) async with AsyncSessionLocal() as session: yield session @pytest.fixture async def client(db_session): async def override_get_db(): yield db_session app.dependency_overrides[get_db] = override_get_db async with AsyncClient(app=app, base_url="http://test") as client: yield client # tests/test_users.py import pytest @pytest.mark.asyncio async def test_create_user(client): response = await client.post( "/api/v1/users/", json={ "email": "test@example.com", "password": "testpass123", "name": "Test User" } ) assert response.status_code == 201 data = response.json() assert data["email"] == "test@example.com" assert "id" in data ``` ## Resources - **references/fastapi-architecture.md**: Detailed architecture guide - **references/async-best-practices.md**: Async/await patterns - **references/testing-strategies.md**: Comprehensive testing guide - **assets/project-template/**: Complete FastAPI project - **assets/docker-compose.yml**: Development environment setup ## Best Practices 1. **Async All The Way**: Use async for database, external APIs 2. **Dependency Injection**: Leverage FastAPI's DI system 3. **Repository Pattern**: Separate data access from business logic 4. **Service Layer**: Keep business logic out of routes 5. **Pydantic Schemas**: Strong typing for request/response 6. **Error Handling**: Consistent error responses 7. **Testing**: Test all layers independently ## Common Pitfalls - **Blocking Code in Async**: Using synchronous database drivers - **No Service Layer**: Business logic in route handlers - **Missing Type Hints**: Loses FastAPI's benefits - **Ignoring Sessions**: Not properly managing database sessions - **No Testing**: Skipping integration tests - **Tight Coupling**: Direct database access in routes

# Architecture Patterns Master proven backend architecture patterns including Clean Architecture, Hexagonal Architecture, and Domain-Driven Design to build maintainable, testable, and scalable systems. ## When to Use This Skill - Designing new backend systems from scratch - Refactoring monolithic applications for better maintainability - Establishing architecture standards for your team - Migrating from tightly coupled to loosely coupled architectures - Implementing domain-driven design principles - Creating testable and mockable codebases - Planning microservices decomposition ## Core Concepts ### 1. Clean Architecture (Uncle Bob) **Layers (dependency flows inward):** - **Entities**: Core business models - **Use Cases**: Application business rules - **Interface Adapters**: Controllers, presenters, gateways - **Frameworks & Drivers**: UI, database, external services **Key Principles:** - Dependencies point inward - Inner layers know nothing about outer layers - Business logic independent of frameworks - Testable without UI, database, or external services ### 2. Hexagonal Architecture (Ports and Adapters) **Components:** - **Domain Core**: Business logic - **Ports**: Interfaces defining interactions - **Adapters**: Implementations of ports (database, REST, message queue) **Benefits:** - Swap implementations easily (mock for testing) - Technology-agnostic core - Clear separation of concerns ### 3. Domain-Driven Design (DDD) **Strategic Patterns:** - **Bounded Contexts**: Separate models for different domains - **Context Mapping**: How contexts relate - **Ubiquitous Language**: Shared terminology **Tactical Patterns:** - **Entities**: Objects with identity - **Value Objects**: Immutable objects defined by attributes - **Aggregates**: Consistency boundaries - **Repositories**: Data access abstraction - **Domain Events**: Things that happened ## Clean Architecture Pattern ### Directory Structure ``` app/ ├── domain/ # Entities & business rules │ ├── entities/ │ │ ├── user.py │ │ └── order.py │ ├── value_objects/ │ │ ├── email.py │ │ └── money.py │ └── interfaces/ # Abstract interfaces │ ├── user_repository.py │ └── payment_gateway.py ├── use_cases/ # Application business rules │ ├── create_user.py │ ├── process_order.py │ └── send_notification.py ├── adapters/ # Interface implementations │ ├── repositories/ │ │ ├── postgres_user_repository.py │ │ └── redis_cache_repository.py │ ├── controllers/ │ │ └── user_controller.py │ └── gateways/ │ ├── stripe_payment_gateway.py │ └── sendgrid_email_gateway.py └── infrastructure/ # Framework & external concerns ├── database.py ├── config.py └── logging.py ``` ### Implementation Example ```python # domain/entities/user.py from dataclasses import dataclass from datetime import datetime from typing import Optional @dataclass class User: """Core user entity - no framework dependencies.""" id: str email: str name: str created_at: datetime is_active: bool = True def deactivate(self): """Business rule: deactivating user.""" self.is_active = False def can_place_order(self) -> bool: """Business rule: active users can order.""" return self.is_active # domain/interfaces/user_repository.py from abc import ABC, abstractmethod from typing import Optional, List from domain.entities.user import User class IUserRepository(ABC): """Port: defines contract, no implementation.""" @abstractmethod async def find_by_id(self, user_id: str) -> Optional[User]: pass @abstractmethod async def find_by_email(self, email: str) -> Optional[User]: pass @abstractmethod async def save(self, user: User) -> User: pass @abstractmethod async def delete(self, user_id: str) -> bool: pass # use_cases/create_user.py from domain.entities.user import User from domain.interfaces.user_repository import IUserRepository from dataclasses import dataclass from datetime import datetime import uuid @dataclass class CreateUserRequest: email: str name: str @dataclass class CreateUserResponse: user: User success: bool error: Optional[str] = None class CreateUserUseCase: """Use case: orchestrates business logic.""" def __init__(self, user_repository: IUserRepository): self.user_repository = user_repository async def execute(self, request: CreateUserRequest) -> CreateUserResponse: # Business validation existing = await self.user_repository.find_by_email(request.email) if existing: return CreateUserResponse( user=None, success=False, error="Email already exists" ) # Create entity user = User( id=str(uuid.uuid4()), email=request.email, name=request.name, created_at=datetime.now(), is_active=True ) # Persist saved_user = await self.user_repository.save(user) return CreateUserResponse( user=saved_user, success=True ) # adapters/repositories/postgres_user_repository.py from domain.interfaces.user_repository import IUserRepository from domain.entities.user import User from typing import Optional import asyncpg class PostgresUserRepository(IUserRepository): """Adapter: PostgreSQL implementation.""" def __init__(self, pool: asyncpg.Pool): self.pool = pool async def find_by_id(self, user_id: str) -> Optional[User]: async with self.pool.acquire() as conn: row = await conn.fetchrow( "SELECT * FROM users WHERE id = $1", user_id ) return self._to_entity(row) if row else None async def find_by_email(self, email: str) -> Optional[User]: async with self.pool.acquire() as conn: row = await conn.fetchrow( "SELECT * FROM users WHERE email = $1", email ) return self._to_entity(row) if row else None async def save(self, user: User) -> User: async with self.pool.acquire() as conn: await conn.execute( """ INSERT INTO users (id, email, name, created_at, is_active) VALUES ($1, $2, $3, $4, $5) ON CONFLICT (id) DO UPDATE SET email = $2, name = $3, is_active = $5 """, user.id, user.email, user.name, user.created_at, user.is_active ) return user async def delete(self, user_id: str) -> bool: async with self.pool.acquire() as conn: result = await conn.execute( "DELETE FROM users WHERE id = $1", user_id ) return result == "DELETE 1" def _to_entity(self, row) -> User: """Map database row to entity.""" return User( id=row["id"], email=row["email"], name=row["name"], created_at=row["created_at"], is_active=row["is_active"] ) # adapters/controllers/user_controller.py from fastapi import APIRouter, Depends, HTTPException from use_cases.create_user import CreateUserUseCase, CreateUserRequest from pydantic import BaseModel router = APIRouter() class CreateUserDTO(BaseModel): email: str name: str @router.post("/users") async def create_user( dto: CreateUserDTO, use_case: CreateUserUseCase = Depends(get_create_user_use_case) ): """Controller: handles HTTP concerns only.""" request = CreateUserRequest(email=dto.email, name=dto.name) response = await use_case.execute(request) if not response.success: raise HTTPException(status_code=400, detail=response.error) return {"user": response.user} ``` ## Hexagonal Architecture Pattern ```python # Core domain (hexagon center) class OrderService: """Domain service - no infrastructure dependencies.""" def __init__( self, order_repository: OrderRepositoryPort, payment_gateway: PaymentGatewayPort, notification_service: NotificationPort ): self.orders = order_repository self.payments = payment_gateway self.notifications = notification_service async def place_order(self, order: Order) -> OrderResult: # Business logic if not order.is_valid(): return OrderResult(success=False, error="Invalid order") # Use ports (interfaces) payment = await self.payments.charge( amount=order.total, customer=order.customer_id ) if not payment.success: return OrderResult(success=False, error="Payment failed") order.mark_as_paid() saved_order = await self.orders.save(order) await self.notifications.send( to=order.customer_email, subject="Order confirmed", body=f"Order {order.id} confirmed" ) return OrderResult(success=True, order=saved_order) # Ports (interfaces) class OrderRepositoryPort(ABC): @abstractmethod async def save(self, order: Order) -> Order: pass class PaymentGatewayPort(ABC): @abstractmethod async def charge(self, amount: Money, customer: str) -> PaymentResult: pass class NotificationPort(ABC): @abstractmethod async def send(self, to: str, subject: str, body: str): pass # Adapters (implementations) class StripePaymentAdapter(PaymentGatewayPort): """Primary adapter: connects to Stripe API.""" def __init__(self, api_key: str): self.stripe = stripe self.stripe.api_key = api_key async def charge(self, amount: Money, customer: str) -> PaymentResult: try: charge = self.stripe.Charge.create( amount=amount.cents, currency=amount.currency, customer=customer ) return PaymentResult(success=True, transaction_id=charge.id) except stripe.error.CardError as e: return PaymentResult(success=False, error=str(e)) class MockPaymentAdapter(PaymentGatewayPort): """Test adapter: no external dependencies.""" async def charge(self, amount: Money, customer: str) -> PaymentResult: return PaymentResult(success=True, transaction_id="mock-123") ``` ## Domain-Driven Design Pattern ```python # Value Objects (immutable) from dataclasses import dataclass from typing import Optional @dataclass(frozen=True) class Email: """Value object: validated email.""" value: str def __post_init__(self): if "@" not in self.value: raise ValueError("Invalid email") @dataclass(frozen=True) class Money: """Value object: amount with currency.""" amount: int # cents currency: str def add(self, other: "Money") -> "Money": if self.currency != other.currency: raise ValueError("Currency mismatch") return Money(self.amount + other.amount, self.currency) # Entities (with identity) class Order: """Entity: has identity, mutable state.""" def __init__(self, id: str, customer: Customer): self.id = id self.customer = customer self.items: List[OrderItem] = [] self.status = OrderStatus.PENDING self._events: List[DomainEvent] = [] def add_item(self, product: Product, quantity: int): """Business logic in entity.""" item = OrderItem(product, quantity) self.items.append(item) self._events.append(ItemAddedEvent(self.id, item)) def total(self) -> Money: """Calculated property.""" return sum(item.subtotal() for item in self.items) def submit(self): """State transition with business rules.""" if not self.items: raise ValueError("Cannot submit empty order") if self.status != OrderStatus.PENDING: raise ValueError("Order already submitted") self.status = OrderStatus.SUBMITTED self._events.append(OrderSubmittedEvent(self.id)) # Aggregates (consistency boundary) class Customer: """Aggregate root: controls access to entities.""" def __init__(self, id: str, email: Email): self.id = id self.email = email self._addresses: List[Address] = [] self._orders: List[str] = [] # Order IDs, not full objects def add_address(self, address: Address): """Aggregate enforces invariants.""" if len(self._addresses) >= 5: raise ValueError("Maximum 5 addresses allowed") self._addresses.append(address) @property def primary_address(self) -> Optional[Address]: return next((a for a in self._addresses if a.is_primary), None) # Domain Events @dataclass class OrderSubmittedEvent: order_id: str occurred_at: datetime = field(default_factory=datetime.now) # Repository (aggregate persistence) class OrderRepository: """Repository: persist/retrieve aggregates.""" async def find_by_id(self, order_id: str) -> Optional[Order]: """Reconstitute aggregate from storage.""" pass async def save(self, order: Order): """Persist aggregate and publish events.""" await self._persist(order) await self._publish_events(order._events) order._events.clear() ``` ## Resources - **references/clean-architecture-guide.md**: Detailed layer breakdown - **references/hexagonal-architecture-guide.md**: Ports and adapters patterns - **references/ddd-tactical-patterns.md**: Entities, value objects, aggregates - **assets/clean-architecture-template/**: Complete project structure - **assets/ddd-examples/**: Domain modeling examples ## Best Practices 1. **Dependency Rule**: Dependencies always point inward 2. **Interface Segregation**: Small, focused interfaces 3. **Business Logic in Domain**: Keep frameworks out of core 4. **Test Independence**: Core testable without infrastructure 5. **Bounded Contexts**: Clear domain boundaries 6. **Ubiquitous Language**: Consistent terminology 7. **Thin Controllers**: Delegate to use cases 8. **Rich Domain Models**: Behavior with data ## Common Pitfalls - **Anemic Domain**: Entities with only data, no behavior - **Framework Coupling**: Business logic depends on frameworks - **Fat Controllers**: Business logic in controllers - **Repository Leakage**: Exposing ORM objects - **Missing Abstractions**: Concrete dependencies in core - **Over-Engineering**: Clean architecture for simple CRUD

# Microservices Patterns Master microservices architecture patterns including service boundaries, inter-service communication, data management, and resilience patterns for building distributed systems. ## When to Use This Skill - Decomposing monoliths into microservices - Designing service boundaries and contracts - Implementing inter-service communication - Managing distributed data and transactions - Building resilient distributed systems - Implementing service discovery and load balancing - Designing event-driven architectures ## Core Concepts ### 1. Service Decomposition Strategies **By Business Capability** - Organize services around business functions - Each service owns its domain - Example: OrderService, PaymentService, InventoryService **By Subdomain (DDD)** - Core domain, supporting subdomains - Bounded contexts map to services - Clear ownership and responsibility **Strangler Fig Pattern** - Gradually extract from monolith - New functionality as microservices - Proxy routes to old/new systems ### 2. Communication Patterns **Synchronous (Request/Response)** - REST APIs - gRPC - GraphQL **Asynchronous (Events/Messages)** - Event streaming (Kafka) - Message queues (RabbitMQ, SQS) - Pub/Sub patterns ### 3. Data Management **Database Per Service** - Each service owns its data - No shared databases - Loose coupling **Saga Pattern** - Distributed transactions - Compensating actions - Eventual consistency ### 4. Resilience Patterns **Circuit Breaker** - Fail fast on repeated errors - Prevent cascade failures **Retry with Backoff** - Transient fault handling - Exponential backoff **Bulkhead** - Isolate resources - Limit impact of failures ## Service Decomposition Patterns ### Pattern 1: By Business Capability ```python # E-commerce example # Order Service class OrderService: """Handles order lifecycle.""" async def create_order(self, order_data: dict) -> Order: order = Order.create(order_data) # Publish event for other services await self.event_bus.publish( OrderCreatedEvent( order_id=order.id, customer_id=order.customer_id, items=order.items, total=order.total ) ) return order # Payment Service (separate service) class PaymentService: """Handles payment processing.""" async def process_payment(self, payment_request: PaymentRequest) -> PaymentResult: # Process payment result = await self.payment_gateway.charge( amount=payment_request.amount, customer=payment_request.customer_id ) if result.success: await self.event_bus.publish( PaymentCompletedEvent( order_id=payment_request.order_id, transaction_id=result.transaction_id ) ) return result # Inventory Service (separate service) class InventoryService: """Handles inventory management.""" async def reserve_items(self, order_id: str, items: List[OrderItem]) -> ReservationResult: # Check availability for item in items: available = await self.inventory_repo.get_available(item.product_id) if available < item.quantity: return ReservationResult( success=False, error=f"Insufficient inventory for {item.product_id}" ) # Reserve items reservation = await self.create_reservation(order_id, items) await self.event_bus.publish( InventoryReservedEvent( order_id=order_id, reservation_id=reservation.id ) ) return ReservationResult(success=True, reservation=reservation) ``` ### Pattern 2: API Gateway ```python from fastapi import FastAPI, HTTPException, Depends import httpx from circuitbreaker import circuit app = FastAPI() class APIGateway: """Central entry point for all client requests.""" def __init__(self): self.order_service_url = "http://order-service:8000" self.payment_service_url = "http://payment-service:8001" self.inventory_service_url = "http://inventory-service:8002" self.http_client = httpx.AsyncClient(timeout=5.0) @circuit(failure_threshold=5, recovery_timeout=30) async def call_order_service(self, path: str, method: str = "GET", **kwargs): """Call order service with circuit breaker.""" response = await self.http_client.request( method, f"{self.order_service_url}{path}", **kwargs ) response.raise_for_status() return response.json() async def create_order_aggregate(self, order_id: str) -> dict: """Aggregate data from multiple services.""" # Parallel requests order, payment, inventory = await asyncio.gather( self.call_order_service(f"/orders/{order_id}"), self.call_payment_service(f"/payments/order/{order_id}"), self.call_inventory_service(f"/reservations/order/{order_id}"), return_exceptions=True ) # Handle partial failures result = {"order": order} if not isinstance(payment, Exception): result["payment"] = payment if not isinstance(inventory, Exception): result["inventory"] = inventory return result @app.post("/api/orders") async def create_order( order_data: dict, gateway: APIGateway = Depends() ): """API Gateway endpoint.""" try: # Route to order service order = await gateway.call_order_service( "/orders", method="POST", json=order_data ) return {"order": order} except httpx.HTTPError as e: raise HTTPException(status_code=503, detail="Order service unavailable") ``` ## Communication Patterns ### Pattern 1: Synchronous REST Communication ```python # Service A calls Service B import httpx from tenacity import retry, stop_after_attempt, wait_exponential class ServiceClient: """HTTP client with retries and timeout.""" def __init__(self, base_url: str): self.base_url = base_url self.client = httpx.AsyncClient( timeout=httpx.Timeout(5.0, connect=2.0), limits=httpx.Limits(max_keepalive_connections=20) ) @retry( stop=stop_after_attempt(3), wait=wait_exponential(multiplier=1, min=2, max=10) ) async def get(self, path: str, **kwargs): """GET with automatic retries.""" response = await self.client.get(f"{self.base_url}{path}", **kwargs) response.raise_for_status() return response.json() async def post(self, path: str, **kwargs): """POST request.""" response = await self.client.post(f"{self.base_url}{path}", **kwargs) response.raise_for_status() return response.json() # Usage payment_client = ServiceClient("http://payment-service:8001") result = await payment_client.post("/payments", json=payment_data) ``` ### Pattern 2: Asynchronous Event-Driven ```python # Event-driven communication with Kafka from aiokafka import AIOKafkaProducer, AIOKafkaConsumer import json from dataclasses import dataclass, asdict from datetime import datetime @dataclass class DomainEvent: event_id: str event_type: str aggregate_id: str occurred_at: datetime data: dict class EventBus: """Event publishing and subscription.""" def __init__(self, bootstrap_servers: List[str]): self.bootstrap_servers = bootstrap_servers self.producer = None async def start(self): self.producer = AIOKafkaProducer( bootstrap_servers=self.bootstrap_servers, value_serializer=lambda v: json.dumps(v).encode() ) await self.producer.start() async def publish(self, event: DomainEvent): """Publish event to Kafka topic.""" topic = event.event_type await self.producer.send_and_wait( topic, value=asdict(event), key=event.aggregate_id.encode() ) async def subscribe(self, topic: str, handler: callable): """Subscribe to events.""" consumer = AIOKafkaConsumer( topic, bootstrap_servers=self.bootstrap_servers, value_deserializer=lambda v: json.loads(v.decode()), group_id="my-service" ) await consumer.start() try: async for message in consumer: event_data = message.value await handler(event_data) finally: await consumer.stop() # Order Service publishes event async def create_order(order_data: dict): order = await save_order(order_data) event = DomainEvent( event_id=str(uuid.uuid4()), event_type="OrderCreated", aggregate_id=order.id, occurred_at=datetime.now(), data={ "order_id": order.id, "customer_id": order.customer_id, "total": order.total } ) await event_bus.publish(event) # Inventory Service listens for OrderCreated async def handle_order_created(event_data: dict): """React to order creation.""" order_id = event_data["data"]["order_id"] items = event_data["data"]["items"] # Reserve inventory await reserve_inventory(order_id, items) ``` ### Pattern 3: Saga Pattern (Distributed Transactions) ```python # Saga orchestration for order fulfillment from enum import Enum from typing import List, Callable class SagaStep: """Single step in saga.""" def __init__( self, name: str, action: Callable, compensation: Callable ): self.name = name self.action = action self.compensation = compensation class SagaStatus(Enum): PENDING = "pending" COMPLETED = "completed" COMPENSATING = "compensating" FAILED = "failed" class OrderFulfillmentSaga: """Orchestrated saga for order fulfillment.""" def __init__(self): self.steps: List[SagaStep] = [ SagaStep( "create_order", action=self.create_order, compensation=self.cancel_order ), SagaStep( "reserve_inventory", action=self.reserve_inventory, compensation=self.release_inventory ), SagaStep( "process_payment", action=self.process_payment, compensation=self.refund_payment ), SagaStep( "confirm_order", action=self.confirm_order, compensation=self.cancel_order_confirmation ) ] async def execute(self, order_data: dict) -> SagaResult: """Execute saga steps.""" completed_steps = [] context = {"order_data": order_data} try: for step in self.steps: # Execute step result = await step.action(context) if not result.success: # Compensate await self.compensate(completed_steps, context) return SagaResult( status=SagaStatus.FAILED, error=result.error ) completed_steps.append(step) context.update(result.data) return SagaResult(status=SagaStatus.COMPLETED, data=context) except Exception as e: # Compensate on error await self.compensate(completed_steps, context) return SagaResult(status=SagaStatus.FAILED, error=str(e)) async def compensate(self, completed_steps: List[SagaStep], context: dict): """Execute compensating actions in reverse order.""" for step in reversed(completed_steps): try: await step.compensation(context) except Exception as e: # Log compensation failure print(f"Compensation failed for {step.name}: {e}") # Step implementations async def create_order(self, context: dict) -> StepResult: order = await order_service.create(context["order_data"]) return StepResult(success=True, data={"order_id": order.id}) async def cancel_order(self, context: dict): await order_service.cancel(context["order_id"]) async def reserve_inventory(self, context: dict) -> StepResult: result = await inventory_service.reserve( context["order_id"], context["order_data"]["items"] ) return StepResult( success=result.success, data={"reservation_id": result.reservation_id} ) async def release_inventory(self, context: dict): await inventory_service.release(context["reservation_id"]) async def process_payment(self, context: dict) -> StepResult: result = await payment_service.charge( context["order_id"], context["order_data"]["total"] ) return StepResult( success=result.success, data={"transaction_id": result.transaction_id}, error=result.error ) async def refund_payment(self, context: dict): await payment_service.refund(context["transaction_id"]) ``` ## Resilience Patterns ### Circuit Breaker Pattern ```python from enum import Enum from datetime import datetime, timedelta from typing import Callable, Any class CircuitState(Enum): CLOSED = "closed" # Normal operation OPEN = "open" # Failing, reject requests HALF_OPEN = "half_open" # Testing if recovered class CircuitBreaker: """Circuit breaker for service calls.""" def __init__( self, failure_threshold: int = 5, recovery_timeout: int = 30, success_threshold: int = 2 ): self.failure_threshold = failure_threshold self.recovery_timeout = recovery_timeout self.success_threshold = success_threshold self.failure_count = 0 self.success_count = 0 self.state = CircuitState.CLOSED self.opened_at = None async def call(self, func: Callable, *args, **kwargs) -> Any: """Execute function with circuit breaker.""" if self.state == CircuitState.OPEN: if self._should_attempt_reset(): self.state = CircuitState.HALF_OPEN else: raise CircuitBreakerOpenError("Circuit breaker is open") try: result = await func(*args, **kwargs) self._on_success() return result except Exception as e: self._on_failure() raise def _on_success(self): """Handle successful call.""" self.failure_count = 0 if self.state == CircuitState.HALF_OPEN: self.success_count += 1 if self.success_count >= self.success_threshold: self.state = CircuitState.CLOSED self.success_count = 0 def _on_failure(self): """Handle failed call.""" self.failure_count += 1 if self.failure_count >= self.failure_threshold: self.state = CircuitState.OPEN self.opened_at = datetime.now() if self.state == CircuitState.HALF_OPEN: self.state = CircuitState.OPEN self.opened_at = datetime.now() def _should_attempt_reset(self) -> bool: """Check if enough time passed to try again.""" return ( datetime.now() - self.opened_at > timedelta(seconds=self.recovery_timeout) ) # Usage breaker = CircuitBreaker(failure_threshold=5, recovery_timeout=30) async def call_payment_service(payment_data: dict): return await breaker.call( payment_client.process_payment, payment_data ) ``` ## Resources - **references/service-decomposition-guide.md**: Breaking down monoliths - **references/communication-patterns.md**: Sync vs async patterns - **references/saga-implementation.md**: Distributed transactions - **assets/circuit-breaker.py**: Production circuit breaker - **assets/event-bus-template.py**: Kafka event bus implementation - **assets/api-gateway-template.py**: Complete API gateway ## Best Practices 1. **Service Boundaries**: Align with business capabilities 2. **Database Per Service**: No shared databases 3. **API Contracts**: Versioned, backward compatible 4. **Async When Possible**: Events over direct calls 5. **Circuit Breakers**: Fail fast on service failures 6. **Distributed Tracing**: Track requests across services 7. **Service Registry**: Dynamic service discovery 8. **Health Checks**: Liveness and readiness probes ## Common Pitfalls - **Distributed Monolith**: Tightly coupled services - **Chatty Services**: Too many inter-service calls - **Shared Databases**: Tight coupling through data - **No Circuit Breakers**: Cascade failures - **Synchronous Everything**: Tight coupling, poor resilience - **Premature Microservices**: Starting with microservices - **Ignoring Network Failures**: Assuming reliable network - **No Compensation Logic**: Can't undo failed transactions

# Saga Orchestration Patterns for managing distributed transactions and long-running business processes. ## When to Use This Skill - Coordinating multi-service transactions - Implementing compensating transactions - Managing long-running business workflows - Handling failures in distributed systems - Building order fulfillment processes - Implementing approval workflows ## Core Concepts ### 1. Saga Types ``` Choreography Orchestration ┌─────┐ ┌─────┐ ┌─────┐ ┌─────────────┐ │Svc A│─►│Svc B│─►│Svc C│ │ Orchestrator│ └─────┘ └─────┘ └─────┘ └──────┬──────┘ │ │ │ │ ▼ ▼ ▼ ┌─────┼─────┐ Event Event Event ▼ ▼ ▼ ┌────┐┌────┐┌────┐ │Svc1││Svc2││Svc3│ └────┘└────┘└────┘ ``` ### 2. Saga Execution States | State | Description | | ---------------- | ------------------------------ | | **Started** | Saga initiated | | **Pending** | Waiting for step completion | | **Compensating** | Rolling back due to failure | | **Completed** | All steps succeeded | | **Failed** | Saga failed after compensation | ## Templates ### Template 1: Saga Orchestrator Base ```python from abc import ABC, abstractmethod from dataclasses import dataclass, field from enum import Enum from typing import List, Dict, Any, Optional from datetime import datetime import uuid class SagaState(Enum): STARTED = "started" PENDING = "pending" COMPENSATING = "compensating" COMPLETED = "completed" FAILED = "failed" @dataclass class SagaStep: name: str action: str compensation: str status: str = "pending" result: Optional[Dict] = None error: Optional[str] = None executed_at: Optional[datetime] = None compensated_at: Optional[datetime] = None @dataclass class Saga: saga_id: str saga_type: str state: SagaState data: Dict[str, Any] steps: List[SagaStep] current_step: int = 0 created_at: datetime = field(default_factory=datetime.utcnow) updated_at: datetime = field(default_factory=datetime.utcnow) class SagaOrchestrator(ABC): """Base class for saga orchestrators.""" def __init__(self, saga_store, event_publisher): self.saga_store = saga_store self.event_publisher = event_publisher @abstractmethod def define_steps(self, data: Dict) -> List[SagaStep]: """Define the saga steps.""" pass @property @abstractmethod def saga_type(self) -> str: """Unique saga type identifier.""" pass async def start(self, data: Dict) -> Saga: """Start a new saga.""" saga = Saga( saga_id=str(uuid.uuid4()), saga_type=self.saga_type, state=SagaState.STARTED, data=data, steps=self.define_steps(data) ) await self.saga_store.save(saga) await self._execute_next_step(saga) return saga async def handle_step_completed(self, saga_id: str, step_name: str, result: Dict): """Handle successful step completion.""" saga = await self.saga_store.get(saga_id) # Update step for step in saga.steps: if step.name == step_name: step.status = "completed" step.result = result step.executed_at = datetime.utcnow() break saga.current_step += 1 saga.updated_at = datetime.utcnow() # Check if saga is complete if saga.current_step >= len(saga.steps): saga.state = SagaState.COMPLETED await self.saga_store.save(saga) await self._on_saga_completed(saga) else: saga.state = SagaState.PENDING await self.saga_store.save(saga) await self._execute_next_step(saga) async def handle_step_failed(self, saga_id: str, step_name: str, error: str): """Handle step failure - start compensation.""" saga = await self.saga_store.get(saga_id) # Mark step as failed for step in saga.steps: if step.name == step_name: step.status = "failed" step.error = error break saga.state = SagaState.COMPENSATING saga.updated_at = datetime.utcnow() await self.saga_store.save(saga) # Start compensation from current step backwards await self._compensate(saga) async def _execute_next_step(self, saga: Saga): """Execute the next step in the saga.""" if saga.current_step >= len(saga.steps): return step = saga.steps[saga.current_step] step.status = "executing" await self.saga_store.save(saga) # Publish command to execute step await self.event_publisher.publish( step.action, { "saga_id": saga.saga_id, "step_name": step.name, **saga.data } ) async def _compensate(self, saga: Saga): """Execute compensation for completed steps.""" # Compensate in reverse order for i in range(saga.current_step - 1, -1, -1): step = saga.steps[i] if step.status == "completed": step.status = "compensating" await self.saga_store.save(saga) await self.event_publisher.publish( step.compensation, { "saga_id": saga.saga_id, "step_name": step.name, "original_result": step.result, **saga.data } ) async def handle_compensation_completed(self, saga_id: str, step_name: str): """Handle compensation completion.""" saga = await self.saga_store.get(saga_id) for step in saga.steps: if step.name == step_name: step.status = "compensated" step.compensated_at = datetime.utcnow() break # Check if all compensations complete all_compensated = all( s.status in ("compensated", "pending", "failed") for s in saga.steps ) if all_compensated: saga.state = SagaState.FAILED await self._on_saga_failed(saga) await self.saga_store.save(saga) async def _on_saga_completed(self, saga: Saga): """Called when saga completes successfully.""" await self.event_publisher.publish( f"{self.saga_type}Completed", {"saga_id": saga.saga_id, **saga.data} ) async def _on_saga_failed(self, saga: Saga): """Called when saga fails after compensation.""" await self.event_publisher.publish( f"{self.saga_type}Failed", {"saga_id": saga.saga_id, "error": "Saga failed", **saga.data} ) ``` ### Template 2: Order Fulfillment Saga ```python class OrderFulfillmentSaga(SagaOrchestrator): """Orchestrates order fulfillment across services.""" @property def saga_type(self) -> str: return "OrderFulfillment" def define_steps(self, data: Dict) -> List[SagaStep]: return [ SagaStep( name="reserve_inventory", action="InventoryService.ReserveItems", compensation="InventoryService.ReleaseReservation" ), SagaStep( name="process_payment", action="PaymentService.ProcessPayment", compensation="PaymentService.RefundPayment" ), SagaStep( name="create_shipment", action="ShippingService.CreateShipment", compensation="ShippingService.CancelShipment" ), SagaStep( name="send_confirmation", action="NotificationService.SendOrderConfirmation", compensation="NotificationService.SendCancellationNotice" ) ] # Usage async def create_order(order_data: Dict): saga = OrderFulfillmentSaga(saga_store, event_publisher) return await saga.start({ "order_id": order_data["order_id"], "customer_id": order_data["customer_id"], "items": order_data["items"], "payment_method": order_data["payment_method"], "shipping_address": order_data["shipping_address"] }) # Event handlers in each service class InventoryService: async def handle_reserve_items(self, command: Dict): try: # Reserve inventory reservation = await self.reserve( command["items"], command["order_id"] ) # Report success await self.event_publisher.publish( "SagaStepCompleted", { "saga_id": command["saga_id"], "step_name": "reserve_inventory", "result": {"reservation_id": reservation.id} } ) except InsufficientInventoryError as e: await self.event_publisher.publish( "SagaStepFailed", { "saga_id": command["saga_id"], "step_name": "reserve_inventory", "error": str(e) } ) async def handle_release_reservation(self, command: Dict): # Compensating action await self.release_reservation( command["original_result"]["reservation_id"] ) await self.event_publisher.publish( "SagaCompensationCompleted", { "saga_id": command["saga_id"], "step_name": "reserve_inventory" } ) ``` ### Template 3: Choreography-Based Saga ```python from dataclasses import dataclass from typing import Dict, Any import asyncio @dataclass class SagaContext: """Passed through choreographed saga events.""" saga_id: str step: int data: Dict[str, Any] completed_steps: list class OrderChoreographySaga: """Choreography-based saga using events.""" def __init__(self, event_bus): self.event_bus = event_bus self._register_handlers() def _register_handlers(self): self.event_bus.subscribe("OrderCreated", self._on_order_created) self.event_bus.subscribe("InventoryReserved", self._on_inventory_reserved) self.event_bus.subscribe("PaymentProcessed", self._on_payment_processed) self.event_bus.subscribe("ShipmentCreated", self._on_shipment_created) # Compensation handlers self.event_bus.subscribe("PaymentFailed", self._on_payment_failed) self.event_bus.subscribe("ShipmentFailed", self._on_shipment_failed) async def _on_order_created(self, event: Dict): """Step 1: Order created, reserve inventory.""" await self.event_bus.publish("ReserveInventory", { "saga_id": event["order_id"], "order_id": event["order_id"], "items": event["items"] }) async def _on_inventory_reserved(self, event: Dict): """Step 2: Inventory reserved, process payment.""" await self.event_bus.publish("ProcessPayment", { "saga_id": event["saga_id"], "order_id": event["order_id"], "amount": event["total_amount"], "reservation_id": event["reservation_id"] }) async def _on_payment_processed(self, event: Dict): """Step 3: Payment done, create shipment.""" await self.event_bus.publish("CreateShipment", { "saga_id": event["saga_id"], "order_id": event["order_id"], "payment_id": event["payment_id"] }) async def _on_shipment_created(self, event: Dict): """Step 4: Complete - send confirmation.""" await self.event_bus.publish("OrderFulfilled", { "saga_id": event["saga_id"], "order_id": event["order_id"], "tracking_number": event["tracking_number"] }) # Compensation handlers async def _on_payment_failed(self, event: Dict): """Payment failed - release inventory.""" await self.event_bus.publish("ReleaseInventory", { "saga_id": event["saga_id"], "reservation_id": event["reservation_id"] }) await self.event_bus.publish("OrderFailed", { "order_id": event["order_id"], "reason": "Payment failed" }) async def _on_shipment_failed(self, event: Dict): """Shipment failed - refund payment and release inventory.""" await self.event_bus.publish("RefundPayment", { "saga_id": event["saga_id"], "payment_id": event["payment_id"] }) await self.event_bus.publish("ReleaseInventory", { "saga_id": event["saga_id"], "reservation_id": event["reservation_id"] }) ``` ### Template 4: Saga with Timeouts ```python class TimeoutSagaOrchestrator(SagaOrchestrator): """Saga orchestrator with step timeouts.""" def __init__(self, saga_store, event_publisher, scheduler): super().__init__(saga_store, event_publisher) self.scheduler = scheduler async def _execute_next_step(self, saga: Saga): if saga.current_step >= len(saga.steps): return step = saga.steps[saga.current_step] step.status = "executing" step.timeout_at = datetime.utcnow() + timedelta(minutes=5) await self.saga_store.save(saga) # Schedule timeout check await self.scheduler.schedule( f"saga_timeout_{saga.saga_id}_{step.name}", self._check_timeout, {"saga_id": saga.saga_id, "step_name": step.name}, run_at=step.timeout_at ) await self.event_publisher.publish( step.action, {"saga_id": saga.saga_id, "step_name": step.name, **saga.data} ) async def _check_timeout(self, data: Dict): """Check if step has timed out.""" saga = await self.saga_store.get(data["saga_id"]) step = next(s for s in saga.steps if s.name == data["step_name"]) if step.status == "executing": # Step timed out - fail it await self.handle_step_failed( data["saga_id"], data["step_name"], "Step timed out" ) ``` ## Best Practices ### Do's - **Make steps idempotent** - Safe to retry - **Design compensations carefully** - They must work - **Use correlation IDs** - For tracing across services - **Implement timeouts** - Don't wait forever - **Log everything** - For debugging failures ### Don'ts - **Don't assume instant completion** - Sagas take time - **Don't skip compensation testing** - Most critical part - **Don't couple services** - Use async messaging - **Don't ignore partial failures** - Handle gracefully ## Resources - [Saga Pattern](https://microservices.io/patterns/data/saga.html) - [Designing Data-Intensive Applications](https://dataintensive.net/)

# Workflow Orchestration Patterns Master workflow orchestration architecture with Temporal, covering fundamental design decisions, resilience patterns, and best practices for building reliable distributed systems. ## When to Use Workflow Orchestration ### Ideal Use Cases (Source: docs.temporal.io) - **Multi-step processes** spanning machines/services/databases - **Distributed transactions** requiring all-or-nothing semantics - **Long-running workflows** (hours to years) with automatic state persistence - **Failure recovery** that must resume from last successful step - **Business processes**: bookings, orders, campaigns, approvals - **Entity lifecycle management**: inventory tracking, account management, cart workflows - **Infrastructure automation**: CI/CD pipelines, provisioning, deployments - **Human-in-the-loop** systems requiring timeouts and escalations ### When NOT to Use - Simple CRUD operations (use direct API calls) - Pure data processing pipelines (use Airflow, batch processing) - Stateless request/response (use standard APIs) - Real-time streaming (use Kafka, event processors) ## Critical Design Decision: Workflows vs Activities **The Fundamental Rule** (Source: temporal.io/blog/workflow-engine-principles): - **Workflows** = Orchestration logic and decision-making - **Activities** = External interactions (APIs, databases, network calls) ### Workflows (Orchestration) **Characteristics:** - Contain business logic and coordination - **MUST be deterministic** (same inputs → same outputs) - **Cannot** perform direct external calls - State automatically preserved across failures - Can run for years despite infrastructure failures **Example workflow tasks:** - Decide which steps to execute - Handle compensation logic - Manage timeouts and retries - Coordinate child workflows ### Activities (External Interactions) **Characteristics:** - Handle all external system interactions - Can be non-deterministic (API calls, DB writes) - Include built-in timeouts and retry logic - **Must be idempotent** (calling N times = calling once) - Short-lived (seconds to minutes typically) **Example activity tasks:** - Call payment gateway API - Write to database - Send emails or notifications - Query external services ### Design Decision Framework ``` Does it touch external systems? → Activity Is it orchestration/decision logic? → Workflow ``` ## Core Workflow Patterns ### 1. Saga Pattern with Compensation **Purpose**: Implement distributed transactions with rollback capability **Pattern** (Source: temporal.io/blog/compensating-actions-part-of-a-complete-breakfast-with-sagas): ``` For each step: 1. Register compensation BEFORE executing 2. Execute the step (via activity) 3. On failure, run all compensations in reverse order (LIFO) ``` **Example: Payment Workflow** 1. Reserve inventory (compensation: release inventory) 2. Charge payment (compensation: refund payment) 3. Fulfill order (compensation: cancel fulfillment) **Critical Requirements:** - Compensations must be idempotent - Register compensation BEFORE executing step - Run compensations in reverse order - Handle partial failures gracefully ### 2. Entity Workflows (Actor Model) **Purpose**: Long-lived workflow representing single entity instance **Pattern** (Source: docs.temporal.io/evaluate/use-cases-design-patterns): - One workflow execution = one entity (cart, account, inventory item) - Workflow persists for entity lifetime - Receives signals for state changes - Supports queries for current state **Example Use Cases:** - Shopping cart (add items, checkout, expiration) - Bank account (deposits, withdrawals, balance checks) - Product inventory (stock updates, reservations) **Benefits:** - Encapsulates entity behavior - Guarantees consistency per entity - Natural event sourcing ### 3. Fan-Out/Fan-In (Parallel Execution) **Purpose**: Execute multiple tasks in parallel, aggregate results **Pattern:** - Spawn child workflows or parallel activities - Wait for all to complete - Aggregate results - Handle partial failures **Scaling Rule** (Source: temporal.io/blog/workflow-engine-principles): - Don't scale individual workflows - For 1M tasks: spawn 1K child workflows × 1K tasks each - Keep each workflow bounded ### 4. Async Callback Pattern **Purpose**: Wait for external event or human approval **Pattern:** - Workflow sends request and waits for signal - External system processes asynchronously - Sends signal to resume workflow - Workflow continues with response **Use Cases:** - Human approval workflows - Webhook callbacks - Long-running external processes ## State Management and Determinism ### Automatic State Preservation **How Temporal Works** (Source: docs.temporal.io/workflows): - Complete program state preserved automatically - Event History records every command and event - Seamless recovery from crashes - Applications restore pre-failure state ### Determinism Constraints **Workflows Execute as State Machines**: - Replay behavior must be consistent - Same inputs → identical outputs every time **Prohibited in Workflows** (Source: docs.temporal.io/workflows): - ❌ Threading, locks, synchronization primitives - ❌ Random number generation (`random()`) - ❌ Global state or static variables - ❌ System time (`datetime.now()`) - ❌ Direct file I/O or network calls - ❌ Non-deterministic libraries **Allowed in Workflows**: - ✅ `workflow.now()` (deterministic time) - ✅ `workflow.random()` (deterministic random) - ✅ Pure functions and calculations - ✅ Calling activities (non-deterministic operations) ### Versioning Strategies **Challenge**: Changing workflow code while old executions still running **Solutions**: 1. **Versioning API**: Use `workflow.get_version()` for safe changes 2. **New Workflow Type**: Create new workflow, route new executions to it 3. **Backward Compatibility**: Ensure old events replay correctly ## Resilience and Error Handling ### Retry Policies **Default Behavior**: Temporal retries activities forever **Configure Retry**: - Initial retry interval - Backoff coefficient (exponential backoff) - Maximum interval (cap retry delay) - Maximum attempts (eventually fail) **Non-Retryable Errors**: - Invalid input (validation failures) - Business rule violations - Permanent failures (resource not found) ### Idempotency Requirements **Why Critical** (Source: docs.temporal.io/activities): - Activities may execute multiple times - Network failures trigger retries - Duplicate execution must be safe **Implementation Strategies**: - Idempotency keys (deduplication) - Check-then-act with unique constraints - Upsert operations instead of insert - Track processed request IDs ### Activity Heartbeats **Purpose**: Detect stalled long-running activities **Pattern**: - Activity sends periodic heartbeat - Includes progress information - Timeout if no heartbeat received - Enables progress-based retry ## Best Practices ### Workflow Design 1. **Keep workflows focused** - Single responsibility per workflow 2. **Small workflows** - Use child workflows for scalability 3. **Clear boundaries** - Workflow orchestrates, activities execute 4. **Test locally** - Use time-skipping test environment ### Activity Design 1. **Idempotent operations** - Safe to retry 2. **Short-lived** - Seconds to minutes, not hours 3. **Timeout configuration** - Always set timeouts 4. **Heartbeat for long tasks** - Report progress 5. **Error handling** - Distinguish retryable vs non-retryable ### Common Pitfalls **Workflow Violations**: - Using `datetime.now()` instead of `workflow.now()` - Threading or async operations in workflow code - Calling external APIs directly from workflow - Non-deterministic logic in workflows **Activity Mistakes**: - Non-idempotent operations (can't handle retries) - Missing timeouts (activities run forever) - No error classification (retry validation errors) - Ignoring payload limits (2MB per argument) ### Operational Considerations **Monitoring**: - Workflow execution duration - Activity failure rates - Retry attempts and backoff - Pending workflow counts **Scalability**: - Horizontal scaling with workers - Task queue partitioning - Child workflow decomposition - Activity batching when appropriate ## Additional Resources **Official Documentation**: - Temporal Core Concepts: docs.temporal.io/workflows - Workflow Patterns: docs.temporal.io/evaluate/use-cases-design-patterns - Best Practices: docs.temporal.io/develop/best-practices - Saga Pattern: temporal.io/blog/saga-pattern-made-easy **Key Principles**: 1. Workflows = orchestration, Activities = external calls 2. Determinism is non-negotiable for workflows 3. Idempotency is critical for activities 4. State preservation is automatic 5. Design for failure and recovery

# Data Storytelling Transform raw data into compelling narratives that drive decisions and inspire action. ## When to Use This Skill - Presenting analytics to executives - Creating quarterly business reviews - Building investor presentations - Writing data-driven reports - Communicating insights to non-technical audiences - Making recommendations based on data ## Core Concepts ### 1. Story Structure ``` Setup → Conflict → Resolution Setup: Context and baseline Conflict: The problem or opportunity Resolution: Insights and recommendations ``` ### 2. Narrative Arc ``` 1. Hook: Grab attention with surprising insight 2. Context: Establish the baseline 3. Rising Action: Build through data points 4. Climax: The key insight 5. Resolution: Recommendations 6. Call to Action: Next steps ``` ### 3. Three Pillars | Pillar | Purpose | Components | | ------------- | -------- | -------------------------------- | | **Data** | Evidence | Numbers, trends, comparisons | | **Narrative** | Meaning | Context, causation, implications | | **Visuals** | Clarity | Charts, diagrams, highlights | ## Story Frameworks ### Framework 1: The Problem-Solution Story ```markdown # Customer Churn Analysis ## The Hook "We're losing $2.4M annually to preventable churn." ## The Context - Current churn rate: 8.5% (industry average: 5%) - Average customer lifetime value: $4,800 - 500 customers churned last quarter ## The Problem Analysis of churned customers reveals a pattern: - 73% churned within first 90 days - Common factor: < 3 support interactions - Low feature adoption in first month ## The Insight [Show engagement curve visualization] Customers who don't engage in the first 14 days are 4x more likely to churn. ## The Solution 1. Implement 14-day onboarding sequence 2. Proactive outreach at day 7 3. Feature adoption tracking ## Expected Impact - Reduce early churn by 40% - Save $960K annually - Payback period: 3 months ## Call to Action Approve $50K budget for onboarding automation. ``` ### Framework 2: The Trend Story ```markdown # Q4 Performance Analysis ## Where We Started Q3 ended with $1.2M MRR, 15% below target. Team morale was low after missed goals. ## What Changed [Timeline visualization] - Oct: Launched self-serve pricing - Nov: Reduced friction in signup - Dec: Added customer success calls ## The Transformation [Before/after comparison chart] | Metric | Q3 | Q4 | Change | |----------------|--------|--------|--------| | Trial → Paid | 8% | 15% | +87% | | Time to Value | 14 days| 5 days | -64% | | Expansion Rate | 2% | 8% | +300% | ## Key Insight Self-serve + high-touch creates compound growth. Customers who self-serve AND get a success call have 3x higher expansion rate. ## Going Forward Double down on hybrid model. Target: $1.8M MRR by Q2. ``` ### Framework 3: The Comparison Story ```markdown # Market Opportunity Analysis ## The Question Should we expand into EMEA or APAC first? ## The Comparison [Side-by-side market analysis] ### EMEA - Market size: $4.2B - Growth rate: 8% - Competition: High - Regulatory: Complex (GDPR) - Language: Multiple ### APAC - Market size: $3.8B - Growth rate: 15% - Competition: Moderate - Regulatory: Varied - Language: Multiple ## The Analysis [Weighted scoring matrix visualization] | Factor | Weight | EMEA Score | APAC Score | | ----------- | ------ | ---------- | ---------- | | Market Size | 25% | 5 | 4 | | Growth | 30% | 3 | 5 | | Competition | 20% | 2 | 4 | | Ease | 25% | 2 | 3 | | **Total** | | **2.9** | **4.1** | ## The Recommendation APAC first. Higher growth, less competition. Start with Singapore hub (English, business-friendly). Enter EMEA in Year 2 with localization ready. ## Risk Mitigation - Timezone coverage: Hire 24/7 support - Cultural fit: Local partnerships - Payment: Multi-currency from day 1 ``` ## Visualization Techniques ### Technique 1: Progressive Reveal ```markdown Start simple, add layers: Slide 1: "Revenue is growing" [single line chart] Slide 2: "But growth is slowing" [add growth rate overlay] Slide 3: "Driven by one segment" [add segment breakdown] Slide 4: "Which is saturating" [add market share] Slide 5: "We need new segments" [add opportunity zones] ``` ### Technique 2: Contrast and Compare ```markdown Before/After: ┌─────────────────┬─────────────────┐ │ BEFORE │ AFTER │ │ │ │ │ Process: 5 days│ Process: 1 day │ │ Errors: 15% │ Errors: 2% │ │ Cost: $50/unit │ Cost: $20/unit │ └─────────────────┴─────────────────┘ This/That (emphasize difference): ┌─────────────────────────────────────┐ │ CUSTOMER A vs B │ │ ┌──────────┐ ┌──────────┐ │ │ │ ████████ │ │ ██ │ │ │ │ $45,000 │ │ $8,000 │ │ │ │ LTV │ │ LTV │ │ │ └──────────┘ └──────────┘ │ │ Onboarded No onboarding │ └─────────────────────────────────────┘ ``` ### Technique 3: Annotation and Highlight ```python import matplotlib.pyplot as plt import pandas as pd fig, ax = plt.subplots(figsize=(12, 6)) # Plot the main data ax.plot(dates, revenue, linewidth=2, color='#2E86AB') # Add annotation for key events ax.annotate( 'Product Launch\n+32% spike', xy=(launch_date, launch_revenue), xytext=(launch_date, launch_revenue * 1.2), fontsize=10, arrowprops=dict(arrowstyle='->', color='#E63946'), color='#E63946' ) # Highlight a region ax.axvspan(growth_start, growth_end, alpha=0.2, color='green', label='Growth Period') # Add threshold line ax.axhline(y=target, color='gray', linestyle='--', label=f'Target: ${target:,.0f}') ax.set_title('Revenue Growth Story', fontsize=14, fontweight='bold') ax.legend() ``` ## Presentation Templates ### Template 1: Executive Summary Slide ``` ┌─────────────────────────────────────────────────────────────┐ │ KEY INSIGHT │ │ ══════════════════════════════════════════════════════════│ │ │ │ "Customers who complete onboarding in week 1 │ │ have 3x higher lifetime value" │ │ │ ├──────────────────────┬──────────────────────────────────────┤ │ │ │ │ THE DATA │ THE IMPLICATION │ │ │ │ │ Week 1 completers: │ ✓ Prioritize onboarding UX │ │ • LTV: $4,500 │ ✓ Add day-1 success milestones │ │ • Retention: 85% │ ✓ Proactive week-1 outreach │ │ • NPS: 72 │ │ │ │ Investment: $75K │ │ Others: │ Expected ROI: 8x │ │ • LTV: $1,500 │ │ │ • Retention: 45% │ │ │ • NPS: 34 │ │ │ │ │ └──────────────────────┴──────────────────────────────────────┘ ``` ### Template 2: Data Story Flow ``` Slide 1: THE HEADLINE "We can grow 40% faster by fixing onboarding" Slide 2: THE CONTEXT Current state metrics Industry benchmarks Gap analysis Slide 3: THE DISCOVERY What the data revealed Surprising finding Pattern identification Slide 4: THE DEEP DIVE Root cause analysis Segment breakdowns Statistical significance Slide 5: THE RECOMMENDATION Proposed actions Resource requirements Timeline Slide 6: THE IMPACT Expected outcomes ROI calculation Risk assessment Slide 7: THE ASK Specific request Decision needed Next steps ``` ### Template 3: One-Page Dashboard Story ```markdown # Monthly Business Review: January 2024 ## THE HEADLINE Revenue up 15% but CAC increasing faster than LTV ## KEY METRICS AT A GLANCE ┌────────┬────────┬────────┬────────┐ │ MRR │ NRR │ CAC │ LTV │ │ $125K │ 108% │ $450 │ $2,200 │ │ ▲15% │ ▲3% │ ▲22% │ ▲8% │ └────────┴────────┴────────┴────────┘ ## WHAT'S WORKING ✓ Enterprise segment growing 25% MoM ✓ Referral program driving 30% of new logos ✓ Support satisfaction at all-time high (94%) ## WHAT NEEDS ATTENTION ✗ SMB acquisition cost up 40% ✗ Trial conversion down 5 points ✗ Time-to-value increased by 3 days ## ROOT CAUSE [Mini chart showing SMB vs Enterprise CAC trend] SMB paid ads becoming less efficient. CPC up 35% while conversion flat. ## RECOMMENDATION 1. Shift $20K/mo from paid to content 2. Launch SMB self-serve trial 3. A/B test shorter onboarding ## NEXT MONTH'S FOCUS - Launch content marketing pilot - Complete self-serve MVP - Reduce time-to-value to < 7 days ``` ## Writing Techniques ### Headlines That Work ```markdown BAD: "Q4 Sales Analysis" GOOD: "Q4 Sales Beat Target by 23% - Here's Why" BAD: "Customer Churn Report" GOOD: "We're Losing $2.4M to Preventable Churn" BAD: "Marketing Performance" GOOD: "Content Marketing Delivers 4x ROI vs. Paid" Formula: [Specific Number] + [Business Impact] + [Actionable Context] ``` ### Transition Phrases ```markdown Building the narrative: • "This leads us to ask..." • "When we dig deeper..." • "The pattern becomes clear when..." • "Contrast this with..." Introducing insights: • "The data reveals..." • "What surprised us was..." • "The inflection point came when..." • "The key finding is..." Moving to action: • "This insight suggests..." • "Based on this analysis..." • "The implication is clear..." • "Our recommendation is..." ``` ### Handling Uncertainty ```markdown Acknowledge limitations: • "With 95% confidence, we can say..." • "The sample size of 500 shows..." • "While correlation is strong, causation requires..." • "This trend holds for [segment], though [caveat]..." Present ranges: • "Impact estimate: $400K-$600K" • "Confidence interval: 15-20% improvement" • "Best case: X, Conservative: Y" ``` ## Best Practices ### Do's - **Start with the "so what"** - Lead with insight - **Use the rule of three** - Three points, three comparisons - **Show, don't tell** - Let data speak - **Make it personal** - Connect to audience goals - **End with action** - Clear next steps ### Don'ts - **Don't data dump** - Curate ruthlessly - **Don't bury the insight** - Front-load key findings - **Don't use jargon** - Match audience vocabulary - **Don't show methodology first** - Context, then method - **Don't forget the narrative** - Numbers need meaning ## Resources - [Storytelling with Data (Cole Nussbaumer)](https://www.storytellingwithdata.com/) - [The Pyramid Principle (Barbara Minto)](https://www.amazon.com/Pyramid-Principle-Logic-Writing-Thinking/dp/0273710516) - [Resonate (Nancy Duarte)](https://www.duarte.com/resonate/)

# KPI Dashboard Design Comprehensive patterns for designing effective Key Performance Indicator (KPI) dashboards that drive business decisions. ## When to Use This Skill - Designing executive dashboards - Selecting meaningful KPIs - Building real-time monitoring displays - Creating department-specific metrics views - Improving existing dashboard layouts - Establishing metric governance ## Core Concepts ### 1. KPI Framework | Level | Focus | Update Frequency | Audience | | --------------- | ---------------- | ----------------- | ---------- | | **Strategic** | Long-term goals | Monthly/Quarterly | Executives | | **Tactical** | Department goals | Weekly/Monthly | Managers | | **Operational** | Day-to-day | Real-time/Daily | Teams | ### 2. SMART KPIs ``` Specific: Clear definition Measurable: Quantifiable Achievable: Realistic targets Relevant: Aligned to goals Time-bound: Defined period ``` ### 3. Dashboard Hierarchy ``` ├── Executive Summary (1 page) │ ├── 4-6 headline KPIs │ ├── Trend indicators │ └── Key alerts ├── Department Views │ ├── Sales Dashboard │ ├── Marketing Dashboard │ ├── Operations Dashboard │ └── Finance Dashboard └── Detailed Drilldowns ├── Individual metrics └── Root cause analysis ``` ## Common KPIs by Department ### Sales KPIs ```yaml Revenue Metrics: - Monthly Recurring Revenue (MRR) - Annual Recurring Revenue (ARR) - Average Revenue Per User (ARPU) - Revenue Growth Rate Pipeline Metrics: - Sales Pipeline Value - Win Rate - Average Deal Size - Sales Cycle Length Activity Metrics: - Calls/Emails per Rep - Demos Scheduled - Proposals Sent - Close Rate ``` ### Marketing KPIs ```yaml Acquisition: - Cost Per Acquisition (CPA) - Customer Acquisition Cost (CAC) - Lead Volume - Marketing Qualified Leads (MQL) Engagement: - Website Traffic - Conversion Rate - Email Open/Click Rate - Social Engagement ROI: - Marketing ROI - Campaign Performance - Channel Attribution - CAC Payback Period ``` ### Product KPIs ```yaml Usage: - Daily/Monthly Active Users (DAU/MAU) - Session Duration - Feature Adoption Rate - Stickiness (DAU/MAU) Quality: - Net Promoter Score (NPS) - Customer Satisfaction (CSAT) - Bug/Issue Count - Time to Resolution Growth: - User Growth Rate - Activation Rate - Retention Rate - Churn Rate ``` ### Finance KPIs ```yaml Profitability: - Gross Margin - Net Profit Margin - EBITDA - Operating Margin Liquidity: - Current Ratio - Quick Ratio - Cash Flow - Working Capital Efficiency: - Revenue per Employee - Operating Expense Ratio - Days Sales Outstanding - Inventory Turnover ``` ## Dashboard Layout Patterns ### Pattern 1: Executive Summary ``` ┌─────────────────────────────────────────────────────────────┐ │ EXECUTIVE DASHBOARD [Date Range ▼] │ ├─────────────┬─────────────┬─────────────┬─────────────────┤ │ REVENUE │ PROFIT │ CUSTOMERS │ NPS SCORE │ │ $2.4M │ $450K │ 12,450 │ 72 │ │ ▲ 12% │ ▲ 8% │ ▲ 15% │ ▲ 5pts │ ├─────────────┴─────────────┴─────────────┴─────────────────┤ │ │ │ Revenue Trend │ Revenue by Product │ │ ┌───────────────────────┐ │ ┌──────────────────┐ │ │ │ /\ /\ │ │ │ ████████ 45% │ │ │ │ / \ / \ /\ │ │ │ ██████ 32% │ │ │ │ / \/ \ / \ │ │ │ ████ 18% │ │ │ │ / \/ \ │ │ │ ██ 5% │ │ │ └───────────────────────┘ │ └──────────────────┘ │ │ │ ├─────────────────────────────────────────────────────────────┤ │ 🔴 Alert: Churn rate exceeded threshold (>5%) │ │ 🟡 Warning: Support ticket volume 20% above average │ └─────────────────────────────────────────────────────────────┘ ``` ### Pattern 2: SaaS Metrics Dashboard ``` ┌─────────────────────────────────────────────────────────────┐ │ SAAS METRICS Jan 2024 [Monthly ▼] │ ├──────────────────────┬──────────────────────────────────────┤ │ ┌────────────────┐ │ MRR GROWTH │ │ │ MRR │ │ ┌────────────────────────────────┐ │ │ │ $125,000 │ │ │ /── │ │ │ │ ▲ 8% │ │ │ /────/ │ │ │ └────────────────┘ │ │ /────/ │ │ │ ┌────────────────┐ │ │ /────/ │ │ │ │ ARR │ │ │ /────/ │ │ │ │ $1,500,000 │ │ └────────────────────────────────┘ │ │ │ ▲ 15% │ │ J F M A M J J A S O N D │ │ └────────────────┘ │ │ ├──────────────────────┼──────────────────────────────────────┤ │ UNIT ECONOMICS │ COHORT RETENTION │ │ │ │ │ CAC: $450 │ Month 1: ████████████████████ 100% │ │ LTV: $2,700 │ Month 3: █████████████████ 85% │ │ LTV/CAC: 6.0x │ Month 6: ████████████████ 80% │ │ │ Month 12: ██████████████ 72% │ │ Payback: 4 months │ │ ├──────────────────────┴──────────────────────────────────────┤ │ CHURN ANALYSIS │ │ ┌──────────┬──────────┬──────────┬──────────────────────┐ │ │ │ Gross │ Net │ Logo │ Expansion │ │ │ │ 4.2% │ 1.8% │ 3.1% │ 2.4% │ │ │ └──────────┴──────────┴──────────┴──────────────────────┘ │ └─────────────────────────────────────────────────────────────┘ ``` ### Pattern 3: Real-time Operations ``` ┌─────────────────────────────────────────────────────────────┐ │ OPERATIONS CENTER Live ● Last: 10:42:15 │ ├────────────────────────────┬────────────────────────────────┤ │ SYSTEM HEALTH │ SERVICE STATUS │ │ ┌──────────────────────┐ │ │ │ │ CPU MEM DISK │ │ ● API Gateway Healthy │ │ │ 45% 72% 58% │ │ ● User Service Healthy │ │ │ ███ ████ ███ │ │ ● Payment Service Degraded │ │ │ ███ ████ ███ │ │ ● Database Healthy │ │ │ ███ ████ ███ │ │ ● Cache Healthy │ │ └──────────────────────┘ │ │ ├────────────────────────────┼────────────────────────────────┤ │ REQUEST THROUGHPUT │ ERROR RATE │ │ ┌──────────────────────┐ │ ┌──────────────────────────┐ │ │ │ ▁▂▃▄▅▆▇█▇▆▅▄▃▂▁▂▃▄▅ │ │ │ ▁▁▁▁▁▂▁▁▁▁▁▁▁▁▁▁▁▁▁▁ │ │ │ └──────────────────────┘ │ └──────────────────────────┘ │ │ Current: 12,450 req/s │ Current: 0.02% │ │ Peak: 18,200 req/s │ Threshold: 1.0% │ ├────────────────────────────┴────────────────────────────────┤ │ RECENT ALERTS │ │ 10:40 🟡 High latency on payment-service (p99 > 500ms) │ │ 10:35 🟢 Resolved: Database connection pool recovered │ │ 10:22 🔴 Payment service circuit breaker tripped │ └─────────────────────────────────────────────────────────────┘ ``` ## Implementation Patterns ### SQL for KPI Calculations ```sql -- Monthly Recurring Revenue (MRR) WITH mrr_calculation AS ( SELECT DATE_TRUNC('month', billing_date) AS month, SUM( CASE subscription_interval WHEN 'monthly' THEN amount WHEN 'yearly' THEN amount / 12 WHEN 'quarterly' THEN amount / 3 END ) AS mrr FROM subscriptions WHERE status = 'active' GROUP BY DATE_TRUNC('month', billing_date) ) SELECT month, mrr, LAG(mrr) OVER (ORDER BY month) AS prev_mrr, (mrr - LAG(mrr) OVER (ORDER BY month)) / LAG(mrr) OVER (ORDER BY month) * 100 AS growth_pct FROM mrr_calculation; -- Cohort Retention WITH cohorts AS ( SELECT user_id, DATE_TRUNC('month', created_at) AS cohort_month FROM users ), activity AS ( SELECT user_id, DATE_TRUNC('month', event_date) AS activity_month FROM user_events WHERE event_type = 'active_session' ) SELECT c.cohort_month, EXTRACT(MONTH FROM age(a.activity_month, c.cohort_month)) AS months_since_signup, COUNT(DISTINCT a.user_id) AS active_users, COUNT(DISTINCT a.user_id)::FLOAT / COUNT(DISTINCT c.user_id) * 100 AS retention_rate FROM cohorts c LEFT JOIN activity a ON c.user_id = a.user_id AND a.activity_month >= c.cohort_month GROUP BY c.cohort_month, EXTRACT(MONTH FROM age(a.activity_month, c.cohort_month)) ORDER BY c.cohort_month, months_since_signup; -- Customer Acquisition Cost (CAC) SELECT DATE_TRUNC('month', acquired_date) AS month, SUM(marketing_spend) / NULLIF(COUNT(new_customers), 0) AS cac, SUM(marketing_spend) AS total_spend, COUNT(new_customers) AS customers_acquired FROM ( SELECT DATE_TRUNC('month', u.created_at) AS acquired_date, u.id AS new_customers, m.spend AS marketing_spend FROM users u JOIN marketing_spend m ON DATE_TRUNC('month', u.created_at) = m.month WHERE u.source = 'marketing' ) acquisition GROUP BY DATE_TRUNC('month', acquired_date); ``` ### Python Dashboard Code (Streamlit) ```python import streamlit as st import pandas as pd import plotly.express as px import plotly.graph_objects as go st.set_page_config(page_title="KPI Dashboard", layout="wide") # Header with date filter col1, col2 = st.columns([3, 1]) with col1: st.title("Executive Dashboard") with col2: date_range = st.selectbox( "Period", ["Last 7 Days", "Last 30 Days", "Last Quarter", "YTD"] ) # KPI Cards def metric_card(label, value, delta, prefix="", suffix=""): delta_color = "green" if delta >= 0 else "red" delta_arrow = "▲" if delta >= 0 else "▼" st.metric( label=label, value=f"{prefix}{value:,.0f}{suffix}", delta=f"{delta_arrow} {abs(delta):.1f}%" ) col1, col2, col3, col4 = st.columns(4) with col1: metric_card("Revenue", 2400000, 12.5, prefix="$") with col2: metric_card("Customers", 12450, 15.2) with col3: metric_card("NPS Score", 72, 5.0) with col4: metric_card("Churn Rate", 4.2, -0.8, suffix="%") # Charts col1, col2 = st.columns(2) with col1: st.subheader("Revenue Trend") revenue_data = pd.DataFrame({ 'Month': pd.date_range('2024-01-01', periods=12, freq='M'), 'Revenue': [180000, 195000, 210000, 225000, 240000, 255000, 270000, 285000, 300000, 315000, 330000, 345000] }) fig = px.line(revenue_data, x='Month', y='Revenue', line_shape='spline', markers=True) fig.update_layout(height=300) st.plotly_chart(fig, use_container_width=True) with col2: st.subheader("Revenue by Product") product_data = pd.DataFrame({ 'Product': ['Enterprise', 'Professional', 'Starter', 'Other'], 'Revenue': [45, 32, 18, 5] }) fig = px.pie(product_data, values='Revenue', names='Product', hole=0.4) fig.update_layout(height=300) st.plotly_chart(fig, use_container_width=True) # Cohort Heatmap st.subheader("Cohort Retention") cohort_data = pd.DataFrame({ 'Cohort': ['Jan', 'Feb', 'Mar', 'Apr', 'May'], 'M0': [100, 100, 100, 100, 100], 'M1': [85, 87, 84, 86, 88], 'M2': [78, 80, 76, 79, None], 'M3': [72, 74, 70, None, None], 'M4': [68, 70, None, None, None], }) fig = go.Figure(data=go.Heatmap( z=cohort_data.iloc[:, 1:].values, x=['M0', 'M1', 'M2', 'M3', 'M4'], y=cohort_data['Cohort'], colorscale='Blues', text=cohort_data.iloc[:, 1:].values, texttemplate='%{text}%', textfont={"size": 12}, )) fig.update_layout(height=250) st.plotly_chart(fig, use_container_width=True) # Alerts Section st.subheader("Alerts") alerts = [ {"level": "error", "message": "Churn rate exceeded threshold (>5%)"}, {"level": "warning", "message": "Support ticket volume 20% above average"}, ] for alert in alerts: if alert["level"] == "error": st.error(f"🔴 {alert['message']}") elif alert["level"] == "warning": st.warning(f"🟡 {alert['message']}") ``` ## Best Practices ### Do's - **Limit to 5-7 KPIs** - Focus on what matters - **Show context** - Comparisons, trends, targets - **Use consistent colors** - Red=bad, green=good - **Enable drilldown** - From summary to detail - **Update appropriately** - Match metric frequency ### Don'ts - **Don't show vanity metrics** - Focus on actionable data - **Don't overcrowd** - White space aids comprehension - **Don't use 3D charts** - They distort perception - **Don't hide methodology** - Document calculations - **Don't ignore mobile** - Ensure responsive design ## Resources - [Stephen Few's Dashboard Design](https://www.perceptualedge.com/articles/visual_business_intelligence/rules_for_using_color.pdf) - [Edward Tufte's Principles](https://www.edwardtufte.com/tufte/) - [Google Data Studio Gallery](https://datastudio.google.com/gallery)

# Context-Driven Development Guide for implementing and maintaining context as a managed artifact alongside code, enabling consistent AI interactions and team alignment through structured project documentation. ## When to Use This Skill - Setting up new projects with Conductor - Understanding the relationship between context artifacts - Maintaining consistency across AI-assisted development sessions - Onboarding team members to an existing Conductor project - Deciding when to update context documents - Managing greenfield vs brownfield project contexts ## Core Philosophy Context-Driven Development treats project context as a first-class artifact managed alongside code. Instead of relying on ad-hoc prompts or scattered documentation, establish a persistent, structured foundation that informs all AI interactions. Key principles: 1. **Context precedes code**: Define what you're building and how before implementation 2. **Living documentation**: Context artifacts evolve with the project 3. **Single source of truth**: One canonical location for each type of information 4. **AI alignment**: Consistent context produces consistent AI behavior ## The Workflow Follow the **Context → Spec & Plan → Implement** workflow: 1. **Context Phase**: Establish or verify project context artifacts exist and are current 2. **Specification Phase**: Define requirements and acceptance criteria for work units 3. **Planning Phase**: Break specifications into phased, actionable tasks 4. **Implementation Phase**: Execute tasks following established workflow patterns ## Artifact Relationships ### product.md - Defines WHAT and WHY Purpose: Captures product vision, goals, target users, and business context. Contents: - Product name and one-line description - Problem statement and solution approach - Target user personas - Core features and capabilities - Success metrics and KPIs - Product roadmap (high-level) Update when: - Product vision or goals change - New major features are planned - Target audience shifts - Business priorities evolve ### product-guidelines.md - Defines HOW to Communicate Purpose: Establishes brand voice, messaging standards, and communication patterns. Contents: - Brand voice and tone guidelines - Terminology and glossary - Error message conventions - User-facing copy standards - Documentation style Update when: - Brand guidelines change - New terminology is introduced - Communication patterns need refinement ### tech-stack.md - Defines WITH WHAT Purpose: Documents technology choices, dependencies, and architectural decisions. Contents: - Primary languages and frameworks - Key dependencies with versions - Infrastructure and deployment targets - Development tools and environment - Testing frameworks - Code quality tools Update when: - Adding new dependencies - Upgrading major versions - Changing infrastructure - Adopting new tools or patterns ### workflow.md - Defines HOW to Work Purpose: Establishes development practices, quality gates, and team workflows. Contents: - Development methodology (TDD, etc.) - Git workflow and commit conventions - Code review requirements - Testing requirements and coverage targets - Quality assurance gates - Deployment procedures Update when: - Team practices evolve - Quality standards change - New workflow patterns are adopted ### tracks.md - Tracks WHAT'S HAPPENING Purpose: Registry of all work units with status and metadata. Contents: - Active tracks with current status - Completed tracks with completion dates - Track metadata (type, priority, assignee) - Links to individual track directories Update when: - New tracks are created - Track status changes - Tracks are completed or archived See [references/artifact-templates.md](references/artifact-templates.md) for copy-paste starter templates. ## Context Maintenance Principles ### Keep Artifacts Synchronized Ensure changes in one artifact reflect in related documents: - New feature in product.md → Update tech-stack.md if new dependencies needed - Completed track → Update product.md to reflect new capabilities - Workflow change → Update all affected track plans ### Update tech-stack.md When Adding Dependencies Before adding any new dependency: 1. Check if existing dependencies solve the need 2. Document the rationale for new dependencies 3. Add version constraints 4. Note any configuration requirements ### Update product.md When Features Complete After completing a feature track: 1. Move feature from "planned" to "implemented" in product.md 2. Update any affected success metrics 3. Document any scope changes from original plan ### Verify Context Before Implementation Before starting any track: 1. Read all context artifacts 2. Flag any outdated information 3. Propose updates before proceeding 4. Confirm context accuracy with stakeholders ## Greenfield vs Brownfield Handling ### Greenfield Projects (New) For new projects: 1. Run `/conductor:setup` to create all artifacts interactively 2. Answer questions about product vision, tech preferences, and workflow 3. Generate initial style guides for chosen languages 4. Create empty tracks registry Characteristics: - Full control over context structure - Define standards before code exists - Establish patterns early ### Brownfield Projects (Existing) For existing codebases: 1. Run `/conductor:setup` with existing codebase detection 2. System analyzes existing code, configs, and documentation 3. Pre-populate artifacts based on discovered patterns 4. Review and refine generated context Characteristics: - Extract implicit context from existing code - Reconcile existing patterns with desired patterns - Document technical debt and modernization plans - Preserve working patterns while establishing standards ## Benefits ### Team Alignment - New team members onboard faster with explicit context - Consistent terminology and conventions across the team - Shared understanding of product goals and technical decisions ### AI Consistency - AI assistants produce aligned outputs across sessions - Reduced need to re-explain context in each interaction - Predictable behavior based on documented standards ### Institutional Memory - Decisions and rationale are preserved - Context survives team changes - Historical context informs future decisions ### Quality Assurance - Standards are explicit and verifiable - Deviations from context are detectable - Quality gates are documented and enforceable ## Directory Structure ``` conductor/ ├── index.md # Navigation hub linking all artifacts ├── product.md # Product vision and goals ├── product-guidelines.md # Communication standards ├── tech-stack.md # Technology preferences ├── workflow.md # Development practices ├── tracks.md # Work unit registry ├── setup_state.json # Resumable setup state ├── code_styleguides/ # Language-specific conventions │ ├── python.md │ ├── typescript.md │ └── ... └── tracks/ └── <track-id>/ ├── spec.md ├── plan.md ├── metadata.json └── index.md ``` ## Context Lifecycle 1. **Creation**: Initial setup via `/conductor:setup` 2. **Validation**: Verify before each track 3. **Evolution**: Update as project grows 4. **Synchronization**: Keep artifacts aligned 5. **Archival**: Document historical decisions ## Context Validation Checklist Before starting implementation on any track, validate context: ### Product Context - [ ] product.md reflects current product vision - [ ] Target users are accurately described - [ ] Feature list is up to date - [ ] Success metrics are defined ### Technical Context - [ ] tech-stack.md lists all current dependencies - [ ] Version numbers are accurate - [ ] Infrastructure targets are correct - [ ] Development tools are documented ### Workflow Context - [ ] workflow.md describes current practices - [ ] Quality gates are defined - [ ] Coverage targets are specified - [ ] Commit conventions are documented ### Track Context - [ ] tracks.md shows all active work - [ ] No stale or abandoned tracks - [ ] Dependencies between tracks are noted ## Common Anti-Patterns Avoid these context management mistakes: ### Stale Context Problem: Context documents become outdated and misleading. Solution: Update context as part of each track's completion process. ### Context Sprawl Problem: Information scattered across multiple locations. Solution: Use the defined artifact structure; resist creating new document types. ### Implicit Context Problem: Relying on knowledge not captured in artifacts. Solution: If you reference something repeatedly, add it to the appropriate artifact. ### Context Hoarding Problem: One person maintains context without team input. Solution: Review context artifacts in pull requests; make updates collaborative. ### Over-Specification Problem: Context becomes so detailed it's impossible to maintain. Solution: Keep artifacts focused on decisions that affect AI behavior and team alignment. ## Integration with Development Tools ### IDE Integration Configure your IDE to display context files prominently: - Pin conductor/product.md for quick reference - Add tech-stack.md to project notes - Create snippets for common patterns from style guides ### Git Hooks Consider pre-commit hooks that: - Warn when dependencies change without tech-stack.md update - Remind to update product.md when feature branches merge - Validate context artifact syntax ### CI/CD Integration Include context validation in pipelines: - Check tech-stack.md matches actual dependencies - Verify links in context documents resolve - Ensure tracks.md status matches git branch state ## Session Continuity Conductor supports multi-session development through context persistence: ### Starting a New Session 1. Read index.md to orient yourself 2. Check tracks.md for active work 3. Review relevant track's plan.md for current task 4. Verify context artifacts are current ### Ending a Session 1. Update plan.md with current progress 2. Note any blockers or decisions made 3. Commit in-progress work with clear status 4. Update tracks.md if status changed ### Handling Interruptions If interrupted mid-task: 1. Mark task as `[~]` with note about stopping point 2. Commit work-in-progress to feature branch 3. Document any uncommitted decisions in plan.md ## Best Practices 1. **Read context first**: Always read relevant artifacts before starting work 2. **Small updates**: Make incremental context changes, not massive rewrites 3. **Link decisions**: Reference context when making implementation choices 4. **Version context**: Commit context changes alongside code changes 5. **Review context**: Include context artifact reviews in code reviews 6. **Validate regularly**: Run context validation checklist before major work 7. **Communicate changes**: Notify team when context artifacts change significantly 8. **Preserve history**: Use git to track context evolution over time 9. **Question staleness**: If context feels wrong, investigate and update 10. **Keep it actionable**: Every context item should inform a decision or behavior

# Track Management Guide for creating, managing, and completing Conductor tracks - the logical work units that organize features, bugs, and refactors through specification, planning, and implementation phases. ## When to Use This Skill - Creating new feature, bug, or refactor tracks - Writing or reviewing spec.md files - Creating or updating plan.md files - Managing track lifecycle from creation to completion - Understanding track status markers and conventions - Working with the tracks.md registry - Interpreting or updating track metadata ## Track Concept A track is a logical work unit that encapsulates a complete piece of work. Each track has: - A unique identifier - A specification defining requirements - A phased plan breaking work into tasks - Metadata tracking status and progress Tracks provide semantic organization for work, enabling: - Clear scope boundaries - Progress tracking - Git-aware operations (revert by track) - Team coordination ## Track Types ### feature New functionality or capabilities. Use for: - New user-facing features - New API endpoints - New integrations - Significant enhancements ### bug Defect fixes. Use for: - Incorrect behavior - Error conditions - Performance regressions - Security vulnerabilities ### chore Maintenance and housekeeping. Use for: - Dependency updates - Configuration changes - Documentation updates - Cleanup tasks ### refactor Code improvement without behavior change. Use for: - Code restructuring - Pattern adoption - Technical debt reduction - Performance optimization (same behavior, better performance) ## Track ID Format Track IDs follow the pattern: `{shortname}_{YYYYMMDD}` - **shortname**: 2-4 word kebab-case description (e.g., `user-auth`, `api-rate-limit`) - **YYYYMMDD**: Creation date in ISO format Examples: - `user-auth_20250115` - `fix-login-error_20250115` - `upgrade-deps_20250115` - `refactor-api-client_20250115` ## Track Lifecycle ### 1. Creation (newTrack) **Define Requirements** 1. Gather requirements through interactive Q&A 2. Identify acceptance criteria 3. Determine scope boundaries 4. Identify dependencies **Generate Specification** 1. Create `spec.md` with structured requirements 2. Document functional and non-functional requirements 3. Define acceptance criteria 4. List dependencies and constraints **Generate Plan** 1. Create `plan.md` with phased task breakdown 2. Organize tasks into logical phases 3. Add verification tasks after phases 4. Estimate effort and complexity **Register Track** 1. Add entry to `tracks.md` registry 2. Create track directory structure 3. Generate `metadata.json` 4. Create track `index.md` ### 2. Implementation **Execute Tasks** 1. Select next pending task from plan 2. Mark task as in-progress 3. Implement following workflow (TDD) 4. Mark task complete with commit SHA **Update Status** 1. Update task markers in plan.md 2. Record commit SHAs for traceability 3. Update phase progress 4. Update track status in tracks.md **Verify Progress** 1. Complete verification tasks 2. Wait for checkpoint approval 3. Record checkpoint commits ### 3. Completion **Sync Documentation** 1. Update product.md if features added 2. Update tech-stack.md if dependencies changed 3. Verify all acceptance criteria met **Archive or Delete** 1. Mark track as completed in tracks.md 2. Record completion date 3. Archive or retain track directory ## Specification (spec.md) Structure ```markdown # {Track Title} ## Overview Brief description of what this track accomplishes and why. ## Functional Requirements ### FR-1: {Requirement Name} Description of the functional requirement. - Acceptance: How to verify this requirement is met ### FR-2: {Requirement Name} ... ## Non-Functional Requirements ### NFR-1: {Requirement Name} Description of the non-functional requirement (performance, security, etc.) - Target: Specific measurable target - Verification: How to test ## Acceptance Criteria - [ ] Criterion 1: Specific, testable condition - [ ] Criterion 2: Specific, testable condition - [ ] Criterion 3: Specific, testable condition ## Scope ### In Scope - Explicitly included items - Features to implement - Components to modify ### Out of Scope - Explicitly excluded items - Future considerations - Related but separate work ## Dependencies ### Internal - Other tracks or components this depends on - Required context artifacts ### External - Third-party services or APIs - External dependencies ## Risks and Mitigations | Risk | Impact | Mitigation | | ---------------- | --------------- | ------------------- | | Risk description | High/Medium/Low | Mitigation strategy | ## Open Questions - [ ] Question that needs resolution - [x] Resolved question - Answer ``` ## Plan (plan.md) Structure ```markdown # Implementation Plan: {Track Title} Track ID: `{track-id}` Created: YYYY-MM-DD Status: pending | in-progress | completed ## Overview Brief description of implementation approach. ## Phase 1: {Phase Name} ### Tasks - [ ] **Task 1.1**: Task description - Sub-task or detail - Sub-task or detail - [ ] **Task 1.2**: Task description - [ ] **Task 1.3**: Task description ### Verification - [ ] **Verify 1.1**: Verification step for phase ## Phase 2: {Phase Name} ### Tasks - [ ] **Task 2.1**: Task description - [ ] **Task 2.2**: Task description ### Verification - [ ] **Verify 2.1**: Verification step for phase ## Phase 3: Finalization ### Tasks - [ ] **Task 3.1**: Update documentation - [ ] **Task 3.2**: Final integration test ### Verification - [ ] **Verify 3.1**: All acceptance criteria met ## Checkpoints | Phase | Checkpoint SHA | Date | Status | | ------- | -------------- | ---- | ------- | | Phase 1 | | | pending | | Phase 2 | | | pending | | Phase 3 | | | pending | ``` ## Status Marker Conventions Use consistent markers in plan.md: | Marker | Meaning | Usage | | ------ | ----------- | --------------------------- | | `[ ]` | Pending | Task not started | | `[~]` | In Progress | Currently being worked | | `[x]` | Complete | Task finished (include SHA) | | `[-]` | Skipped | Intentionally not done | | `[!]` | Blocked | Waiting on dependency | Example: ```markdown - [x] **Task 1.1**: Set up database schema `abc1234` - [~] **Task 1.2**: Implement user model - [ ] **Task 1.3**: Add validation logic - [!] **Task 1.4**: Integrate auth service (blocked: waiting for API key) - [-] **Task 1.5**: Legacy migration (skipped: not needed) ``` ## Track Registry (tracks.md) Format ```markdown # Track Registry ## Active Tracks | Track ID | Type | Status | Phase | Started | Assignee | | ------------------------------------------------ | ------- | ----------- | ----- | ---------- | ---------- | | [user-auth_20250115](tracks/user-auth_20250115/) | feature | in-progress | 2/3 | 2025-01-15 | @developer | | [fix-login_20250114](tracks/fix-login_20250114/) | bug | pending | 0/2 | 2025-01-14 | - | ## Completed Tracks | Track ID | Type | Completed | Duration | | ---------------------------------------------- | ----- | ---------- | -------- | | [setup-ci_20250110](tracks/setup-ci_20250110/) | chore | 2025-01-12 | 2 days | ## Archived Tracks | Track ID | Reason | Archived | | ---------------------------------------------------- | ---------- | ---------- | | [old-feature_20241201](tracks/old-feature_20241201/) | Superseded | 2025-01-05 | ``` ## Metadata (metadata.json) Fields ```json { "id": "user-auth_20250115", "title": "User Authentication System", "type": "feature", "status": "in-progress", "priority": "high", "created": "2025-01-15T10:30:00Z", "updated": "2025-01-15T14:45:00Z", "started": "2025-01-15T11:00:00Z", "completed": null, "assignee": "@developer", "phases": { "total": 3, "current": 2, "completed": 1 }, "tasks": { "total": 12, "completed": 5, "in_progress": 1, "pending": 6 }, "checkpoints": [ { "phase": 1, "sha": "abc1234", "date": "2025-01-15T13:00:00Z" } ], "dependencies": [], "tags": ["auth", "security"] } ``` ## Track Operations ### Creating a Track 1. Run `/conductor:new-track` 2. Answer interactive questions 3. Review generated spec.md 4. Review generated plan.md 5. Confirm track creation ### Starting Implementation 1. Read spec.md and plan.md 2. Verify context artifacts are current 3. Mark first task as `[~]` 4. Begin TDD workflow ### Completing a Phase 1. Ensure all phase tasks are `[x]` 2. Complete verification tasks 3. Wait for checkpoint approval 4. Record checkpoint SHA 5. Proceed to next phase ### Completing a Track 1. Verify all phases complete 2. Verify all acceptance criteria met 3. Update product.md if needed 4. Mark track completed in tracks.md 5. Update metadata.json ### Reverting a Track 1. Run `/conductor:revert` 2. Select track to revert 3. Choose granularity (track/phase/task) 4. Confirm revert operation 5. Update status markers ## Handling Track Dependencies ### Identifying Dependencies During track creation, identify: - **Hard dependencies**: Must complete before this track can start - **Soft dependencies**: Can proceed in parallel but may affect integration - **External dependencies**: Third-party services, APIs, or team decisions ### Documenting Dependencies In spec.md, list dependencies with: - Dependency type (hard/soft/external) - Current status (available/pending/blocked) - Resolution path (what needs to happen) ### Managing Blocked Tracks When a track is blocked: 1. Mark blocked tasks with `[!]` and reason 2. Update tracks.md status 3. Document blocker in metadata.json 4. Consider creating dependency track if needed ## Track Sizing Guidelines ### Right-Sized Tracks Aim for tracks that: - Complete in 1-5 days of work - Have 2-4 phases - Contain 8-20 tasks total - Deliver a coherent, testable unit ### Too Large Signs a track is too large: - More than 5 phases - More than 25 tasks - Multiple unrelated features - Estimated duration > 1 week Solution: Split into multiple tracks with clear boundaries. ### Too Small Signs a track is too small: - Single phase with 1-2 tasks - No meaningful verification needed - Could be a sub-task of another track - Less than a few hours of work Solution: Combine with related work or handle as part of existing track. ## Specification Quality Checklist Before finalizing spec.md, verify: ### Requirements Quality - [ ] Each requirement has clear acceptance criteria - [ ] Requirements are testable - [ ] Requirements are independent (can verify separately) - [ ] No ambiguous language ("should be fast" → "response < 200ms") ### Scope Clarity - [ ] In-scope items are specific - [ ] Out-of-scope items prevent scope creep - [ ] Boundaries are clear to implementer ### Dependencies Identified - [ ] All internal dependencies listed - [ ] External dependencies have owners/contacts - [ ] Dependency status is current ### Risks Addressed - [ ] Major risks identified - [ ] Impact assessment realistic - [ ] Mitigations are actionable ## Plan Quality Checklist Before starting implementation, verify plan.md: ### Task Quality - [ ] Tasks are atomic (one logical action) - [ ] Tasks are independently verifiable - [ ] Task descriptions are clear - [ ] Sub-tasks provide helpful detail ### Phase Organization - [ ] Phases group related tasks - [ ] Each phase delivers something testable - [ ] Verification tasks after each phase - [ ] Phases build on each other logically ### Completeness - [ ] All spec requirements have corresponding tasks - [ ] Documentation tasks included - [ ] Testing tasks included - [ ] Integration tasks included ## Common Track Patterns ### Feature Track Pattern ``` Phase 1: Foundation - Data models - Database migrations - Basic API structure Phase 2: Core Logic - Business logic implementation - Input validation - Error handling Phase 3: Integration - UI integration - API documentation - End-to-end tests ``` ### Bug Fix Track Pattern ``` Phase 1: Reproduction - Write failing test capturing bug - Document reproduction steps Phase 2: Fix - Implement fix - Verify test passes - Check for regressions Phase 3: Verification - Manual verification - Update documentation if needed ``` ### Refactor Track Pattern ``` Phase 1: Preparation - Add characterization tests - Document current behavior Phase 2: Refactoring - Apply changes incrementally - Maintain green tests throughout Phase 3: Cleanup - Remove dead code - Update documentation ``` ## Best Practices 1. **One track, one concern**: Keep tracks focused on a single logical change 2. **Small phases**: Break work into phases of 3-5 tasks maximum 3. **Verification after phases**: Always include verification tasks 4. **Update markers immediately**: Mark task status as you work 5. **Record SHAs**: Always note commit SHAs for completed tasks 6. **Review specs before planning**: Ensure spec is complete before creating plan 7. **Link dependencies**: Explicitly note track dependencies 8. **Archive, don't delete**: Preserve completed tracks for reference 9. **Size appropriately**: Keep tracks between 1-5 days of work 10. **Clear acceptance criteria**: Every requirement must be testable

# Data Quality Frameworks Production patterns for implementing data quality with Great Expectations, dbt tests, and data contracts to ensure reliable data pipelines. ## When to Use This Skill - Implementing data quality checks in pipelines - Setting up Great Expectations validation - Building comprehensive dbt test suites - Establishing data contracts between teams - Monitoring data quality metrics - Automating data validation in CI/CD ## Core Concepts ### 1. Data Quality Dimensions | Dimension | Description | Example Check | | ---------------- | ------------------------ | -------------------------------------------------- | | **Completeness** | No missing values | `expect_column_values_to_not_be_null` | | **Uniqueness** | No duplicates | `expect_column_values_to_be_unique` | | **Validity** | Values in expected range | `expect_column_values_to_be_in_set` | | **Accuracy** | Data matches reality | Cross-reference validation | | **Consistency** | No contradictions | `expect_column_pair_values_A_to_be_greater_than_B` | | **Timeliness** | Data is recent | `expect_column_max_to_be_between` | ### 2. Testing Pyramid for Data ``` /\ / \ Integration Tests (cross-table) /────\ / \ Unit Tests (single column) /────────\ / \ Schema Tests (structure) /────────────\ ``` ## Quick Start ### Great Expectations Setup ```bash # Install pip install great_expectations # Initialize project great_expectations init # Create datasource great_expectations datasource new ``` ```python # great_expectations/checkpoints/daily_validation.yml import great_expectations as gx # Create context context = gx.get_context() # Create expectation suite suite = context.add_expectation_suite("orders_suite") # Add expectations suite.add_expectation( gx.expectations.ExpectColumnValuesToNotBeNull(column="order_id") ) suite.add_expectation( gx.expectations.ExpectColumnValuesToBeUnique(column="order_id") ) # Validate results = context.run_checkpoint(checkpoint_name="daily_orders") ``` ## Patterns ### Pattern 1: Great Expectations Suite ```python # expectations/orders_suite.py import great_expectations as gx from great_expectations.core import ExpectationSuite from great_expectations.core.expectation_configuration import ExpectationConfiguration def build_orders_suite() -> ExpectationSuite: """Build comprehensive orders expectation suite""" suite = ExpectationSuite(expectation_suite_name="orders_suite") # Schema expectations suite.add_expectation(ExpectationConfiguration( expectation_type="expect_table_columns_to_match_set", kwargs={ "column_set": ["order_id", "customer_id", "amount", "status", "created_at"], "exact_match": False # Allow additional columns } )) # Primary key suite.add_expectation(ExpectationConfiguration( expectation_type="expect_column_values_to_not_be_null", kwargs={"column": "order_id"} )) suite.add_expectation(ExpectationConfiguration( expectation_type="expect_column_values_to_be_unique", kwargs={"column": "order_id"} )) # Foreign key suite.add_expectation(ExpectationConfiguration( expectation_type="expect_column_values_to_not_be_null", kwargs={"column": "customer_id"} )) # Categorical values suite.add_expectation(ExpectationConfiguration( expectation_type="expect_column_values_to_be_in_set", kwargs={ "column": "status", "value_set": ["pending", "processing", "shipped", "delivered", "cancelled"] } )) # Numeric ranges suite.add_expectation(ExpectationConfiguration( expectation_type="expect_column_values_to_be_between", kwargs={ "column": "amount", "min_value": 0, "max_value": 100000, "strict_min": True # amount > 0 } )) # Date validity suite.add_expectation(ExpectationConfiguration( expectation_type="expect_column_values_to_be_dateutil_parseable", kwargs={"column": "created_at"} )) # Freshness - data should be recent suite.add_expectation(ExpectationConfiguration( expectation_type="expect_column_max_to_be_between", kwargs={ "column": "created_at", "min_value": {"$PARAMETER": "now - timedelta(days=1)"}, "max_value": {"$PARAMETER": "now"} } )) # Row count sanity suite.add_expectation(ExpectationConfiguration( expectation_type="expect_table_row_count_to_be_between", kwargs={ "min_value": 1000, # Expect at least 1000 rows "max_value": 10000000 } )) # Statistical expectations suite.add_expectation(ExpectationConfiguration( expectation_type="expect_column_mean_to_be_between", kwargs={ "column": "amount", "min_value": 50, "max_value": 500 } )) return suite ``` ### Pattern 2: Great Expectations Checkpoint ```yaml # great_expectations/checkpoints/orders_checkpoint.yml name: orders_checkpoint config_version: 1.0 class_name: Checkpoint run_name_template: "%Y%m%d-%H%M%S-orders-validation" validations: - batch_request: datasource_name: warehouse data_connector_name: default_inferred_data_connector_name data_asset_name: orders data_connector_query: index: -1 # Latest batch expectation_suite_name: orders_suite action_list: - name: store_validation_result action: class_name: StoreValidationResultAction - name: store_evaluation_parameters action: class_name: StoreEvaluationParametersAction - name: update_data_docs action: class_name: UpdateDataDocsAction # Slack notification on failure - name: send_slack_notification action: class_name: SlackNotificationAction slack_webhook: ${SLACK_WEBHOOK} notify_on: failure renderer: module_name: great_expectations.render.renderer.slack_renderer class_name: SlackRenderer ``` ```python # Run checkpoint import great_expectations as gx context = gx.get_context() result = context.run_checkpoint(checkpoint_name="orders_checkpoint") if not result.success: failed_expectations = [ r for r in result.run_results.values() if not r.success ] raise ValueError(f"Data quality check failed: {failed_expectations}") ``` ### Pattern 3: dbt Data Tests ```yaml # models/marts/core/_core__models.yml version: 2 models: - name: fct_orders description: Order fact table tests: # Table-level tests - dbt_utils.recency: datepart: day field: created_at interval: 1 - dbt_utils.at_least_one - dbt_utils.expression_is_true: expression: "total_amount >= 0" columns: - name: order_id description: Primary key tests: - unique - not_null - name: customer_id description: Foreign key to dim_customers tests: - not_null - relationships: to: ref('dim_customers') field: customer_id - name: order_status tests: - accepted_values: values: ["pending", "processing", "shipped", "delivered", "cancelled"] - name: total_amount tests: - not_null - dbt_utils.expression_is_true: expression: ">= 0" - name: created_at tests: - not_null - dbt_utils.expression_is_true: expression: "<= current_timestamp" - name: dim_customers columns: - name: customer_id tests: - unique - not_null - name: email tests: - unique - not_null # Custom regex test - dbt_utils.expression_is_true: expression: "email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'" ``` ### Pattern 4: Custom dbt Tests ```sql -- tests/generic/test_row_count_in_range.sql {% test row_count_in_range(model, min_count, max_count) %} with row_count as ( select count(*) as cnt from {{ model }} ) select cnt from row_count where cnt < {{ min_count }} or cnt > {{ max_count }} {% endtest %} -- Usage in schema.yml: -- tests: -- - row_count_in_range: -- min_count: 1000 -- max_count: 10000000 ``` ```sql -- tests/generic/test_sequential_values.sql {% test sequential_values(model, column_name, interval=1) %} with lagged as ( select {{ column_name }}, lag({{ column_name }}) over (order by {{ column_name }}) as prev_value from {{ model }} ) select * from lagged where {{ column_name }} - prev_value != {{ interval }} and prev_value is not null {% endtest %} ``` ```sql -- tests/singular/assert_orders_customers_match.sql -- Singular test: specific business rule with orders_customers as ( select distinct customer_id from {{ ref('fct_orders') }} ), dim_customers as ( select customer_id from {{ ref('dim_customers') }} ), orphaned_orders as ( select o.customer_id from orders_customers o left join dim_customers c using (customer_id) where c.customer_id is null ) select * from orphaned_orders -- Test passes if this returns 0 rows ``` ### Pattern 5: Data Contracts ```yaml # contracts/orders_contract.yaml apiVersion: datacontract.com/v1.0.0 kind: DataContract metadata: name: orders version: 1.0.0 owner: data-platform-team contact: data-team@company.com info: title: Orders Data Contract description: Contract for order event data from the ecommerce platform purpose: Analytics, reporting, and ML features servers: production: type: snowflake account: company.us-east-1 database: ANALYTICS schema: CORE terms: usage: Internal analytics only limitations: PII must not be exposed in downstream marts billing: Charged per query TB scanned schema: type: object properties: order_id: type: string format: uuid description: Unique order identifier required: true unique: true pii: false customer_id: type: string format: uuid description: Customer identifier required: true pii: true piiClassification: indirect total_amount: type: number minimum: 0 maximum: 100000 description: Order total in USD created_at: type: string format: date-time description: Order creation timestamp required: true status: type: string enum: [pending, processing, shipped, delivered, cancelled] description: Current order status quality: type: SodaCL specification: checks for orders: - row_count > 0 - missing_count(order_id) = 0 - duplicate_count(order_id) = 0 - invalid_count(status) = 0: valid values: [pending, processing, shipped, delivered, cancelled] - freshness(created_at) < 24h sla: availability: 99.9% freshness: 1 hour latency: 5 minutes ``` ### Pattern 6: Automated Quality Pipeline ```python # quality_pipeline.py from dataclasses import dataclass from typing import List, Dict, Any import great_expectations as gx from datetime import datetime @dataclass class QualityResult: table: str passed: bool total_expectations: int failed_expectations: int details: List[Dict[str, Any]] timestamp: datetime class DataQualityPipeline: """Orchestrate data quality checks across tables""" def __init__(self, context: gx.DataContext): self.context = context self.results: List[QualityResult] = [] def validate_table(self, table: str, suite: str) -> QualityResult: """Validate a single table against expectation suite""" checkpoint_config = { "name": f"{table}_validation", "config_version": 1.0, "class_name": "Checkpoint", "validations": [{ "batch_request": { "datasource_name": "warehouse", "data_asset_name": table, }, "expectation_suite_name": suite, }], } result = self.context.run_checkpoint(**checkpoint_config) # Parse results validation_result = list(result.run_results.values())[0] results = validation_result.results failed = [r for r in results if not r.success] return QualityResult( table=table, passed=result.success, total_expectations=len(results), failed_expectations=len(failed), details=[{ "expectation": r.expectation_config.expectation_type, "success": r.success, "observed_value": r.result.get("observed_value"), } for r in results], timestamp=datetime.now() ) def run_all(self, tables: Dict[str, str]) -> Dict[str, QualityResult]: """Run validation for all tables""" results = {} for table, suite in tables.items(): print(f"Validating {table}...") results[table] = self.validate_table(table, suite) return results def generate_report(self, results: Dict[str, QualityResult]) -> str: """Generate quality report""" report = ["# Data Quality Report", f"Generated: {datetime.now()}", ""] total_passed = sum(1 for r in results.values() if r.passed) total_tables = len(results) report.append(f"## Summary: {total_passed}/{total_tables} tables passed") report.append("") for table, result in results.items(): status = "✅" if result.passed else "❌" report.append(f"### {status} {table}") report.append(f"- Expectations: {result.total_expectations}") report.append(f"- Failed: {result.failed_expectations}") if not result.passed: report.append("- Failed checks:") for detail in result.details: if not detail["success"]: report.append(f" - {detail['expectation']}: {detail['observed_value']}") report.append("") return "\n".join(report) # Usage context = gx.get_context() pipeline = DataQualityPipeline(context) tables_to_validate = { "orders": "orders_suite", "customers": "customers_suite", "products": "products_suite", } results = pipeline.run_all(tables_to_validate) report = pipeline.generate_report(results) # Fail pipeline if any table failed if not all(r.passed for r in results.values()): print(report) raise ValueError("Data quality checks failed!") ``` ## Best Practices ### Do's - **Test early** - Validate source data before transformations - **Test incrementally** - Add tests as you find issues - **Document expectations** - Clear descriptions for each test - **Alert on failures** - Integrate with monitoring - **Version contracts** - Track schema changes ### Don'ts - **Don't test everything** - Focus on critical columns - **Don't ignore warnings** - They often precede failures - **Don't skip freshness** - Stale data is bad data - **Don't hardcode thresholds** - Use dynamic baselines - **Don't test in isolation** - Test relationships too ## Resources - [Great Expectations Documentation](https://docs.greatexpectations.io/) - [dbt Testing Documentation](https://docs.getdbt.com/docs/build/tests) - [Data Contract Specification](https://datacontract.com/) - [Soda Core](https://docs.soda.io/soda-core/overview.html)

# dbt Transformation Patterns Production-ready patterns for dbt (data build tool) including model organization, testing strategies, documentation, and incremental processing. ## When to Use This Skill - Building data transformation pipelines with dbt - Organizing models into staging, intermediate, and marts layers - Implementing data quality tests - Creating incremental models for large datasets - Documenting data models and lineage - Setting up dbt project structure ## Core Concepts ### 1. Model Layers (Medallion Architecture) ``` sources/ Raw data definitions ↓ staging/ 1:1 with source, light cleaning ↓ intermediate/ Business logic, joins, aggregations ↓ marts/ Final analytics tables ``` ### 2. Naming Conventions | Layer | Prefix | Example | | ------------ | -------------- | ----------------------------- | | Staging | `stg_` | `stg_stripe__payments` | | Intermediate | `int_` | `int_payments_pivoted` | | Marts | `dim_`, `fct_` | `dim_customers`, `fct_orders` | ## Quick Start ```yaml # dbt_project.yml name: "analytics" version: "1.0.0" profile: "analytics" model-paths: ["models"] analysis-paths: ["analyses"] test-paths: ["tests"] seed-paths: ["seeds"] macro-paths: ["macros"] vars: start_date: "2020-01-01" models: analytics: staging: +materialized: view +schema: staging intermediate: +materialized: ephemeral marts: +materialized: table +schema: analytics ``` ``` # Project structure models/ ├── staging/ │ ├── stripe/ │ │ ├── _stripe__sources.yml │ │ ├── _stripe__models.yml │ │ ├── stg_stripe__customers.sql │ │ └── stg_stripe__payments.sql │ └── shopify/ │ ├── _shopify__sources.yml │ └── stg_shopify__orders.sql ├── intermediate/ │ └── finance/ │ └── int_payments_pivoted.sql └── marts/ ├── core/ │ ├── _core__models.yml │ ├── dim_customers.sql │ └── fct_orders.sql └── finance/ └── fct_revenue.sql ``` ## Patterns ### Pattern 1: Source Definitions ```yaml # models/staging/stripe/_stripe__sources.yml version: 2 sources: - name: stripe description: Raw Stripe data loaded via Fivetran database: raw schema: stripe loader: fivetran loaded_at_field: _fivetran_synced freshness: warn_after: { count: 12, period: hour } error_after: { count: 24, period: hour } tables: - name: customers description: Stripe customer records columns: - name: id description: Primary key tests: - unique - not_null - name: email description: Customer email - name: created description: Account creation timestamp - name: payments description: Stripe payment transactions columns: - name: id tests: - unique - not_null - name: customer_id tests: - not_null - relationships: to: source('stripe', 'customers') field: id ``` ### Pattern 2: Staging Models ```sql -- models/staging/stripe/stg_stripe__customers.sql with source as ( select * from {{ source('stripe', 'customers') }} ), renamed as ( select -- ids id as customer_id, -- strings lower(email) as email, name as customer_name, -- timestamps created as created_at, -- metadata _fivetran_synced as _loaded_at from source ) select * from renamed ``` ```sql -- models/staging/stripe/stg_stripe__payments.sql {{ config( materialized='incremental', unique_key='payment_id', on_schema_change='append_new_columns' ) }} with source as ( select * from {{ source('stripe', 'payments') }} {% if is_incremental() %} where _fivetran_synced > (select max(_loaded_at) from {{ this }}) {% endif %} ), renamed as ( select -- ids id as payment_id, customer_id, invoice_id, -- amounts (convert cents to dollars) amount / 100.0 as amount, amount_refunded / 100.0 as amount_refunded, -- status status as payment_status, -- timestamps created as created_at, -- metadata _fivetran_synced as _loaded_at from source ) select * from renamed ``` ### Pattern 3: Intermediate Models ```sql -- models/intermediate/finance/int_payments_pivoted_to_customer.sql with payments as ( select * from {{ ref('stg_stripe__payments') }} ), customers as ( select * from {{ ref('stg_stripe__customers') }} ), payment_summary as ( select customer_id, count(*) as total_payments, count(case when payment_status = 'succeeded' then 1 end) as successful_payments, sum(case when payment_status = 'succeeded' then amount else 0 end) as total_amount_paid, min(created_at) as first_payment_at, max(created_at) as last_payment_at from payments group by customer_id ) select customers.customer_id, customers.email, customers.created_at as customer_created_at, coalesce(payment_summary.total_payments, 0) as total_payments, coalesce(payment_summary.successful_payments, 0) as successful_payments, coalesce(payment_summary.total_amount_paid, 0) as lifetime_value, payment_summary.first_payment_at, payment_summary.last_payment_at from customers left join payment_summary using (customer_id) ``` ### Pattern 4: Mart Models (Dimensions and Facts) ```sql -- models/marts/core/dim_customers.sql {{ config( materialized='table', unique_key='customer_id' ) }} with customers as ( select * from {{ ref('int_payments_pivoted_to_customer') }} ), orders as ( select * from {{ ref('stg_shopify__orders') }} ), order_summary as ( select customer_id, count(*) as total_orders, sum(total_price) as total_order_value, min(created_at) as first_order_at, max(created_at) as last_order_at from orders group by customer_id ), final as ( select -- surrogate key {{ dbt_utils.generate_surrogate_key(['customers.customer_id']) }} as customer_key, -- natural key customers.customer_id, -- attributes customers.email, customers.customer_created_at, -- payment metrics customers.total_payments, customers.successful_payments, customers.lifetime_value, customers.first_payment_at, customers.last_payment_at, -- order metrics coalesce(order_summary.total_orders, 0) as total_orders, coalesce(order_summary.total_order_value, 0) as total_order_value, order_summary.first_order_at, order_summary.last_order_at, -- calculated fields case when customers.lifetime_value >= 1000 then 'high' when customers.lifetime_value >= 100 then 'medium' else 'low' end as customer_tier, -- timestamps current_timestamp as _loaded_at from customers left join order_summary using (customer_id) ) select * from final ``` ```sql -- models/marts/core/fct_orders.sql {{ config( materialized='incremental', unique_key='order_id', incremental_strategy='merge' ) }} with orders as ( select * from {{ ref('stg_shopify__orders') }} {% if is_incremental() %} where updated_at > (select max(updated_at) from {{ this }}) {% endif %} ), customers as ( select * from {{ ref('dim_customers') }} ), final as ( select -- keys orders.order_id, customers.customer_key, orders.customer_id, -- dimensions orders.order_status, orders.fulfillment_status, orders.payment_status, -- measures orders.subtotal, orders.tax, orders.shipping, orders.total_price, orders.total_discount, orders.item_count, -- timestamps orders.created_at, orders.updated_at, orders.fulfilled_at, -- metadata current_timestamp as _loaded_at from orders left join customers on orders.customer_id = customers.customer_id ) select * from final ``` ### Pattern 5: Testing and Documentation ```yaml # models/marts/core/_core__models.yml version: 2 models: - name: dim_customers description: Customer dimension with payment and order metrics columns: - name: customer_key description: Surrogate key for the customer dimension tests: - unique - not_null - name: customer_id description: Natural key from source system tests: - unique - not_null - name: email description: Customer email address tests: - not_null - name: customer_tier description: Customer value tier based on lifetime value tests: - accepted_values: values: ["high", "medium", "low"] - name: lifetime_value description: Total amount paid by customer tests: - dbt_utils.expression_is_true: expression: ">= 0" - name: fct_orders description: Order fact table with all order transactions tests: - dbt_utils.recency: datepart: day field: created_at interval: 1 columns: - name: order_id tests: - unique - not_null - name: customer_key tests: - not_null - relationships: to: ref('dim_customers') field: customer_key ``` ### Pattern 6: Macros and DRY Code ```sql -- macros/cents_to_dollars.sql {% macro cents_to_dollars(column_name, precision=2) %} round({{ column_name }} / 100.0, {{ precision }}) {% endmacro %} -- macros/generate_schema_name.sql {% macro generate_schema_name(custom_schema_name, node) %} {%- set default_schema = target.schema -%} {%- if custom_schema_name is none -%} {{ default_schema }} {%- else -%} {{ default_schema }}_{{ custom_schema_name }} {%- endif -%} {% endmacro %} -- macros/limit_data_in_dev.sql {% macro limit_data_in_dev(column_name, days=3) %} {% if target.name == 'dev' %} where {{ column_name }} >= dateadd(day, -{{ days }}, current_date) {% endif %} {% endmacro %} -- Usage in model select * from {{ ref('stg_orders') }} {{ limit_data_in_dev('created_at') }} ``` ### Pattern 7: Incremental Strategies ```sql -- Delete+Insert (default for most warehouses) {{ config( materialized='incremental', unique_key='id', incremental_strategy='delete+insert' ) }} -- Merge (best for late-arriving data) {{ config( materialized='incremental', unique_key='id', incremental_strategy='merge', merge_update_columns=['status', 'amount', 'updated_at'] ) }} -- Insert Overwrite (partition-based) {{ config( materialized='incremental', incremental_strategy='insert_overwrite', partition_by={ "field": "created_date", "data_type": "date", "granularity": "day" } ) }} select *, date(created_at) as created_date from {{ ref('stg_events') }} {% if is_incremental() %} where created_date >= dateadd(day, -3, current_date) {% endif %} ``` ## dbt Commands ```bash # Development dbt run # Run all models dbt run --select staging # Run staging models only dbt run --select +fct_orders # Run fct_orders and its upstream dbt run --select fct_orders+ # Run fct_orders and its downstream dbt run --full-refresh # Rebuild incremental models # Testing dbt test # Run all tests dbt test --select stg_stripe # Test specific models dbt build # Run + test in DAG order # Documentation dbt docs generate # Generate docs dbt docs serve # Serve docs locally # Debugging dbt compile # Compile SQL without running dbt debug # Test connection dbt ls --select tag:critical # List models by tag ``` ## Best Practices ### Do's - **Use staging layer** - Clean data once, use everywhere - **Test aggressively** - Not null, unique, relationships - **Document everything** - Column descriptions, model descriptions - **Use incremental** - For tables > 1M rows - **Version control** - dbt project in Git ### Don'ts - **Don't skip staging** - Raw → mart is tech debt - **Don't hardcode dates** - Use `{{ var('start_date') }}` - **Don't repeat logic** - Extract to macros - **Don't test in prod** - Use dev target - **Don't ignore freshness** - Monitor source data ## Resources - [dbt Documentation](https://docs.getdbt.com/) - [dbt Best Practices](https://docs.getdbt.com/guides/best-practices) - [dbt-utils Package](https://hub.getdbt.com/dbt-labs/dbt_utils/latest/) - [dbt Discourse](https://discourse.getdbt.com/)

# PostgreSQL Table Design ## Core Rules - Define a **PRIMARY KEY** for reference tables (users, orders, etc.). Not always needed for time-series/event/log data. When used, prefer `BIGINT GENERATED ALWAYS AS IDENTITY`; use `UUID` only when global uniqueness/opacity is needed. - **Normalize first (to 3NF)** to eliminate data redundancy and update anomalies; denormalize **only** for measured, high-ROI reads where join performance is proven problematic. Premature denormalization creates maintenance burden. - Add **NOT NULL** everywhere it’s semantically required; use **DEFAULT**s for common values. - Create **indexes for access paths you actually query**: PK/unique (auto), **FK columns (manual!)**, frequent filters/sorts, and join keys. - Prefer **TIMESTAMPTZ** for event time; **NUMERIC** for money; **TEXT** for strings; **BIGINT** for integer values, **DOUBLE PRECISION** for floats (or `NUMERIC` for exact decimal arithmetic). ## PostgreSQL “Gotchas” - **Identifiers**: unquoted → lowercased. Avoid quoted/mixed-case names. Convention: use `snake_case` for table/column names. - **Unique + NULLs**: UNIQUE allows multiple NULLs. Use `UNIQUE (...) NULLS NOT DISTINCT` (PG15+) to restrict to one NULL. - **FK indexes**: PostgreSQL **does not** auto-index FK columns. Add them. - **No silent coercions**: length/precision overflows error out (no truncation). Example: inserting 999 into `NUMERIC(2,0)` fails with error, unlike some databases that silently truncate or round. - **Sequences/identity have gaps** (normal; don't "fix"). Rollbacks, crashes, and concurrent transactions create gaps in ID sequences (1, 2, 5, 6...). This is expected behavior—don't try to make IDs consecutive. - **Heap storage**: no clustered PK by default (unlike SQL Server/MySQL InnoDB); `CLUSTER` is one-off reorganization, not maintained on subsequent inserts. Row order on disk is insertion order unless explicitly clustered. - **MVCC**: updates/deletes leave dead tuples; vacuum handles them—design to avoid hot wide-row churn. ## Data Types - **IDs**: `BIGINT GENERATED ALWAYS AS IDENTITY` preferred (`GENERATED BY DEFAULT` also fine); `UUID` when merging/federating/used in a distributed system or for opaque IDs. Generate with `uuidv7()` (preferred if using PG18+) or `gen_random_uuid()` (if using an older PG version). - **Integers**: prefer `BIGINT` unless storage space is critical; `INTEGER` for smaller ranges; avoid `SMALLINT` unless constrained. - **Floats**: prefer `DOUBLE PRECISION` over `REAL` unless storage space is critical. Use `NUMERIC` for exact decimal arithmetic. - **Strings**: prefer `TEXT`; if length limits needed, use `CHECK (LENGTH(col) <= n)` instead of `VARCHAR(n)`; avoid `CHAR(n)`. Use `BYTEA` for binary data. Large strings/binary (>2KB default threshold) automatically stored in TOAST with compression. TOAST storage: `PLAIN` (no TOAST), `EXTENDED` (compress + out-of-line), `EXTERNAL` (out-of-line, no compress), `MAIN` (compress, keep in-line if possible). Default `EXTENDED` usually optimal. Control with `ALTER TABLE tbl ALTER COLUMN col SET STORAGE strategy` and `ALTER TABLE tbl SET (toast_tuple_target = 4096)` for threshold. Case-insensitive: for locale/accent handling use non-deterministic collations; for plain ASCII use expression indexes on `LOWER(col)` (preferred unless column needs case-insensitive PK/FK/UNIQUE) or `CITEXT`. - **Money**: `NUMERIC(p,s)` (never float). - **Time**: `TIMESTAMPTZ` for timestamps; `DATE` for date-only; `INTERVAL` for durations. Avoid `TIMESTAMP` (without timezone). Use `now()` for transaction start time, `clock_timestamp()` for current wall-clock time. - **Booleans**: `BOOLEAN` with `NOT NULL` constraint unless tri-state values are required. - **Enums**: `CREATE TYPE ... AS ENUM` for small, stable sets (e.g. US states, days of week). For business-logic-driven and evolving values (e.g. order statuses) → use TEXT (or INT) + CHECK or lookup table. - **Arrays**: `TEXT[]`, `INTEGER[]`, etc. Use for ordered lists where you query elements. Index with **GIN** for containment (`@>`, `<@`) and overlap (`&&`) queries. Access: `arr[1]` (1-indexed), `arr[1:3]` (slicing). Good for tags, categories; avoid for relations—use junction tables instead. Literal syntax: `'{val1,val2}'` or `ARRAY[val1,val2]`. - **Range types**: `daterange`, `numrange`, `tstzrange` for intervals. Support overlap (`&&`), containment (`@>`), operators. Index with **GiST**. Good for scheduling, versioning, numeric ranges. Pick a bounds scheme and use it consistently; prefer `[)` (inclusive/exclusive) by default. - **Network types**: `INET` for IP addresses, `CIDR` for network ranges, `MACADDR` for MAC addresses. Support network operators (`<<`, `>>`, `&&`). - **Geometric types**: `POINT`, `LINE`, `POLYGON`, `CIRCLE` for 2D spatial data. Index with **GiST**. Consider **PostGIS** for advanced spatial features. - **Text search**: `TSVECTOR` for full-text search documents, `TSQUERY` for search queries. Index `tsvector` with **GIN**. Always specify language: `to_tsvector('english', col)` and `to_tsquery('english', 'query')`. Never use single-argument versions. This applies to both index expressions and queries. - **Domain types**: `CREATE DOMAIN email AS TEXT CHECK (VALUE ~ '^[^@]+@[^@]+$')` for reusable custom types with validation. Enforces constraints across tables. - **Composite types**: `CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT)` for structured data within columns. Access with `(col).field` syntax. - **JSONB**: preferred over JSON; index with **GIN**. Use only for optional/semi-structured attrs. ONLY use JSON if the original ordering of the contents MUST be preserved. - **Vector types**: `vector` type by `pgvector` for vector similarity search for embeddings. ### Do not use the following data types - DO NOT use `timestamp` (without time zone); DO use `timestamptz` instead. - DO NOT use `char(n)` or `varchar(n)`; DO use `text` instead. - DO NOT use `money` type; DO use `numeric` instead. - DO NOT use `timetz` type; DO use `timestamptz` instead. - DO NOT use `timestamptz(0)` or any other precision specification; DO use `timestamptz` instead - DO NOT use `serial` type; DO use `generated always as identity` instead. ## Table Types - **Regular**: default; fully durable, logged. - **TEMPORARY**: session-scoped, auto-dropped, not logged. Faster for scratch work. - **UNLOGGED**: persistent but not crash-safe. Faster writes; good for caches/staging. ## Row-Level Security Enable with `ALTER TABLE tbl ENABLE ROW LEVEL SECURITY`. Create policies: `CREATE POLICY user_access ON orders FOR SELECT TO app_users USING (user_id = current_user_id())`. Built-in user-based access control at the row level. ## Constraints - **PK**: implicit UNIQUE + NOT NULL; creates a B-tree index. - **FK**: specify `ON DELETE/UPDATE` action (`CASCADE`, `RESTRICT`, `SET NULL`, `SET DEFAULT`). Add explicit index on referencing column—speeds up joins and prevents locking issues on parent deletes/updates. Use `DEFERRABLE INITIALLY DEFERRED` for circular FK dependencies checked at transaction end. - **UNIQUE**: creates a B-tree index; allows multiple NULLs unless `NULLS NOT DISTINCT` (PG15+). Standard behavior: `(1, NULL)` and `(1, NULL)` are allowed. With `NULLS NOT DISTINCT`: only one `(1, NULL)` allowed. Prefer `NULLS NOT DISTINCT` unless you specifically need duplicate NULLs. - **CHECK**: row-local constraints; NULL values pass the check (three-valued logic). Example: `CHECK (price > 0)` allows NULL prices. Combine with `NOT NULL` to enforce: `price NUMERIC NOT NULL CHECK (price > 0)`. - **EXCLUDE**: prevents overlapping values using operators. `EXCLUDE USING gist (room_id WITH =, booking_period WITH &&)` prevents double-booking rooms. Requires appropriate index type (often GiST). ## Indexing - **B-tree**: default for equality/range queries (`=`, `<`, `>`, `BETWEEN`, `ORDER BY`) - **Composite**: order matters—index used if equality on leftmost prefix (`WHERE a = ? AND b > ?` uses index on `(a,b)`, but `WHERE b = ?` does not). Put most selective/frequently filtered columns first. - **Covering**: `CREATE INDEX ON tbl (id) INCLUDE (name, email)` - includes non-key columns for index-only scans without visiting table. - **Partial**: for hot subsets (`WHERE status = 'active'` → `CREATE INDEX ON tbl (user_id) WHERE status = 'active'`). Any query with `status = 'active'` can use this index. - **Expression**: for computed search keys (`CREATE INDEX ON tbl (LOWER(email))`). Expression must match exactly in WHERE clause: `WHERE LOWER(email) = 'user@example.com'`. - **GIN**: JSONB containment/existence, arrays (`@>`, `?`), full-text search (`@@`) - **GiST**: ranges, geometry, exclusion constraints - **BRIN**: very large, naturally ordered data (time-series)—minimal storage overhead. Effective when row order on disk correlates with indexed column (insertion order or after `CLUSTER`). ## Partitioning - Use for very large tables (>100M rows) where queries consistently filter on partition key (often time/date). - Alternate use: use for tables where data maintenance tasks dictates e.g. data pruned or bulk replaced periodically - **RANGE**: common for time-series (`PARTITION BY RANGE (created_at)`). Create partitions: `CREATE TABLE logs_2024_01 PARTITION OF logs FOR VALUES FROM ('2024-01-01') TO ('2024-02-01')`. **TimescaleDB** automates time-based or ID-based partitioning with retention policies and compression. - **LIST**: for discrete values (`PARTITION BY LIST (region)`). Example: `FOR VALUES IN ('us-east', 'us-west')`. - **HASH**: for even distribution when no natural key (`PARTITION BY HASH (user_id)`). Creates N partitions with modulus. - **Constraint exclusion**: requires `CHECK` constraints on partitions for query planner to prune. Auto-created for declarative partitioning (PG10+). - Prefer declarative partitioning or hypertables. Do NOT use table inheritance. - **Limitations**: no global UNIQUE constraints—include partition key in PK/UNIQUE. FKs from partitioned tables not supported; use triggers. ## Special Considerations ### Update-Heavy Tables - **Separate hot/cold columns**—put frequently updated columns in separate table to minimize bloat. - **Use `fillfactor=90`** to leave space for HOT updates that avoid index maintenance. - **Avoid updating indexed columns**—prevents beneficial HOT updates. - **Partition by update patterns**—separate frequently updated rows in a different partition from stable data. ### Insert-Heavy Workloads - **Minimize indexes**—only create what you query; every index slows inserts. - **Use `COPY` or multi-row `INSERT`** instead of single-row inserts. - **UNLOGGED tables** for rebuildable staging data—much faster writes. - **Defer index creation** for bulk loads—>drop index, load data, recreate indexes. - **Partition by time/hash** to distribute load. **TimescaleDB** automates partitioning and compression of insert-heavy data. - **Use a natural key for primary key** such as a (timestamp, device_id) if enforcing global uniqueness is important many insert-heavy tables don't need a primary key at all. - If you do need a surrogate key, **Prefer `BIGINT GENERATED ALWAYS AS IDENTITY` over `UUID`**. ### Upsert-Friendly Design - **Requires UNIQUE index** on conflict target columns—`ON CONFLICT (col1, col2)` needs exact matching unique index (partial indexes don't work). - **Use `EXCLUDED.column`** to reference would-be-inserted values; only update columns that actually changed to reduce write overhead. - **`DO NOTHING` faster** than `DO UPDATE` when no actual update needed. ### Safe Schema Evolution - **Transactional DDL**: most DDL operations can run in transactions and be rolled back—`BEGIN; ALTER TABLE...; ROLLBACK;` for safe testing. - **Concurrent index creation**: `CREATE INDEX CONCURRENTLY` avoids blocking writes but can't run in transactions. - **Volatile defaults cause rewrites**: adding `NOT NULL` columns with volatile defaults (e.g., `now()`, `gen_random_uuid()`) rewrites entire table. Non-volatile defaults are fast. - **Drop constraints before columns**: `ALTER TABLE DROP CONSTRAINT` then `DROP COLUMN` to avoid dependency issues. - **Function signature changes**: `CREATE OR REPLACE` with different arguments creates overloads, not replacements. DROP old version if no overload desired. ## Generated Columns - `... GENERATED ALWAYS AS (<expr>) STORED` for computed, indexable fields. PG18+ adds `VIRTUAL` columns (computed on read, not stored). ## Extensions - **`pgcrypto`**: `crypt()` for password hashing. - **`uuid-ossp`**: alternative UUID functions; prefer `pgcrypto` for new projects. - **`pg_trgm`**: fuzzy text search with `%` operator, `similarity()` function. Index with GIN for `LIKE '%pattern%'` acceleration. - **`citext`**: case-insensitive text type. Prefer expression indexes on `LOWER(col)` unless you need case-insensitive constraints. - **`btree_gin`/`btree_gist`**: enable mixed-type indexes (e.g., GIN index on both JSONB and text columns). - **`hstore`**: key-value pairs; mostly superseded by JSONB but useful for simple string mappings. - **`timescaledb`**: essential for time-series—automated partitioning, retention, compression, continuous aggregates. - **`postgis`**: comprehensive geospatial support beyond basic geometric types—essential for location-based applications. - **`pgvector`**: vector similarity search for embeddings. - **`pgaudit`**: audit logging for all database activity. ## JSONB Guidance - Prefer `JSONB` with **GIN** index. - Default: `CREATE INDEX ON tbl USING GIN (jsonb_col);` → accelerates: - **Containment** `jsonb_col @> '{"k":"v"}'` - **Key existence** `jsonb_col ? 'k'`, **any/all keys** `?\|`, `?&` - **Path containment** on nested docs - **Disjunction** `jsonb_col @> ANY(ARRAY['{"status":"active"}', '{"status":"pending"}'])` - Heavy `@>` workloads: consider opclass `jsonb_path_ops` for smaller/faster containment-only indexes: - `CREATE INDEX ON tbl USING GIN (jsonb_col jsonb_path_ops);` - **Trade-off**: loses support for key existence (`?`, `?|`, `?&`) queries—only supports containment (`@>`) - Equality/range on a specific scalar field: extract and index with B-tree (generated column or expression): - `ALTER TABLE tbl ADD COLUMN price INT GENERATED ALWAYS AS ((jsonb_col->>'price')::INT) STORED;` - `CREATE INDEX ON tbl (price);` - Prefer queries like `WHERE price BETWEEN 100 AND 500` (uses B-tree) over `WHERE (jsonb_col->>'price')::INT BETWEEN 100 AND 500` without index. - Arrays inside JSONB: use GIN + `@>` for containment (e.g., tags). Consider `jsonb_path_ops` if only doing containment. - Keep core relations in tables; use JSONB for optional/variable attributes. - Use constraints to limit allowed JSONB values in a column e.g. `config JSONB NOT NULL CHECK(jsonb_typeof(config) = 'object')` ## Examples ### Users ```sql CREATE TABLE users ( user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email TEXT NOT NULL UNIQUE, name TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE UNIQUE INDEX ON users (LOWER(email)); CREATE INDEX ON users (created_at); ``` ### Orders ```sql CREATE TABLE orders ( order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES users(user_id), status TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','PAID','CANCELED')), total NUMERIC(10,2) NOT NULL CHECK (total > 0), created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX ON orders (user_id); CREATE INDEX ON orders (created_at); ``` ### JSONB ```sql CREATE TABLE profiles ( user_id BIGINT PRIMARY KEY REFERENCES users(user_id), attrs JSONB NOT NULL DEFAULT '{}', theme TEXT GENERATED ALWAYS AS (attrs->>'theme') STORED ); CREATE INDEX profiles_attrs_gin ON profiles USING GIN (attrs); ```

# Debugging Strategies Transform debugging from frustrating guesswork into systematic problem-solving with proven strategies, powerful tools, and methodical approaches. ## When to Use This Skill - Tracking down elusive bugs - Investigating performance issues - Understanding unfamiliar codebases - Debugging production issues - Analyzing crash dumps and stack traces - Profiling application performance - Investigating memory leaks - Debugging distributed systems ## Core Principles ### 1. The Scientific Method **1. Observe**: What's the actual behavior? **2. Hypothesize**: What could be causing it? **3. Experiment**: Test your hypothesis **4. Analyze**: Did it prove/disprove your theory? **5. Repeat**: Until you find the root cause ### 2. Debugging Mindset **Don't Assume:** - "It can't be X" - Yes it can - "I didn't change Y" - Check anyway - "It works on my machine" - Find out why **Do:** - Reproduce consistently - Isolate the problem - Keep detailed notes - Question everything - Take breaks when stuck ### 3. Rubber Duck Debugging Explain your code and problem out loud (to a rubber duck, colleague, or yourself). Often reveals the issue. ## Systematic Debugging Process ### Phase 1: Reproduce ```markdown ## Reproduction Checklist 1. **Can you reproduce it?** - Always? Sometimes? Randomly? - Specific conditions needed? - Can others reproduce it? 2. **Create minimal reproduction** - Simplify to smallest example - Remove unrelated code - Isolate the problem 3. **Document steps** - Write down exact steps - Note environment details - Capture error messages ``` ### Phase 2: Gather Information ```markdown ## Information Collection 1. **Error Messages** - Full stack trace - Error codes - Console/log output 2. **Environment** - OS version - Language/runtime version - Dependencies versions - Environment variables 3. **Recent Changes** - Git history - Deployment timeline - Configuration changes 4. **Scope** - Affects all users or specific ones? - All browsers or specific ones? - Production only or also dev? ``` ### Phase 3: Form Hypothesis ```markdown ## Hypothesis Formation Based on gathered info, ask: 1. **What changed?** - Recent code changes - Dependency updates - Infrastructure changes 2. **What's different?** - Working vs broken environment - Working vs broken user - Before vs after 3. **Where could this fail?** - Input validation - Business logic - Data layer - External services ``` ### Phase 4: Test & Verify ```markdown ## Testing Strategies 1. **Binary Search** - Comment out half the code - Narrow down problematic section - Repeat until found 2. **Add Logging** - Strategic console.log/print - Track variable values - Trace execution flow 3. **Isolate Components** - Test each piece separately - Mock dependencies - Remove complexity 4. **Compare Working vs Broken** - Diff configurations - Diff environments - Diff data ``` ## Debugging Tools ### JavaScript/TypeScript Debugging ```typescript // Chrome DevTools Debugger function processOrder(order: Order) { debugger; // Execution pauses here const total = calculateTotal(order); console.log("Total:", total); // Conditional breakpoint if (order.items.length > 10) { debugger; // Only breaks if condition true } return total; } // Console debugging techniques console.log("Value:", value); // Basic console.table(arrayOfObjects); // Table format console.time("operation"); /* code */ console.timeEnd("operation"); // Timing console.trace(); // Stack trace console.assert(value > 0, "Value must be positive"); // Assertion // Performance profiling performance.mark("start-operation"); // ... operation code performance.mark("end-operation"); performance.measure("operation", "start-operation", "end-operation"); console.log(performance.getEntriesByType("measure")); ``` **VS Code Debugger Configuration:** ```json // .vscode/launch.json { "version": "0.2.0", "configurations": [ { "type": "node", "request": "launch", "name": "Debug Program", "program": "${workspaceFolder}/src/index.ts", "preLaunchTask": "tsc: build - tsconfig.json", "outFiles": ["${workspaceFolder}/dist/**/*.js"], "skipFiles": ["<node_internals>/**"] }, { "type": "node", "request": "launch", "name": "Debug Tests", "program": "${workspaceFolder}/node_modules/jest/bin/jest", "args": ["--runInBand", "--no-cache"], "console": "integratedTerminal" } ] } ``` ### Python Debugging ```python # Built-in debugger (pdb) import pdb def calculate_total(items): total = 0 pdb.set_trace() # Debugger starts here for item in items: total += item.price * item.quantity return total # Breakpoint (Python 3.7+) def process_order(order): breakpoint() # More convenient than pdb.set_trace() # ... code # Post-mortem debugging try: risky_operation() except Exception: import pdb pdb.post_mortem() # Debug at exception point # IPython debugging (ipdb) from ipdb import set_trace set_trace() # Better interface than pdb # Logging for debugging import logging logging.basicConfig(level=logging.DEBUG) logger = logging.getLogger(__name__) def fetch_user(user_id): logger.debug(f'Fetching user: {user_id}') user = db.query(User).get(user_id) logger.debug(f'Found user: {user}') return user # Profile performance import cProfile import pstats cProfile.run('slow_function()', 'profile_stats') stats = pstats.Stats('profile_stats') stats.sort_stats('cumulative') stats.print_stats(10) # Top 10 slowest ``` ### Go Debugging ```go // Delve debugger // Install: go install github.com/go-delve/delve/cmd/dlv@latest // Run: dlv debug main.go import ( "fmt" "runtime" "runtime/debug" ) // Print stack trace func debugStack() { debug.PrintStack() } // Panic recovery with debugging func processRequest() { defer func() { if r := recover(); r != nil { fmt.Println("Panic:", r) debug.PrintStack() } }() // ... code that might panic } // Memory profiling import _ "net/http/pprof" // Visit http://localhost:6060/debug/pprof/ // CPU profiling import ( "os" "runtime/pprof" ) f, _ := os.Create("cpu.prof") pprof.StartCPUProfile(f) defer pprof.StopCPUProfile() // ... code to profile ``` ## Advanced Debugging Techniques ### Technique 1: Binary Search Debugging ```bash # Git bisect for finding regression git bisect start git bisect bad # Current commit is bad git bisect good v1.0.0 # v1.0.0 was good # Git checks out middle commit # Test it, then: git bisect good # if it works git bisect bad # if it's broken # Continue until bug found git bisect reset # when done ``` ### Technique 2: Differential Debugging Compare working vs broken: ```markdown ## What's Different? | Aspect | Working | Broken | | ------------ | ----------- | -------------- | | Environment | Development | Production | | Node version | 18.16.0 | 18.15.0 | | Data | Empty DB | 1M records | | User | Admin | Regular user | | Browser | Chrome | Safari | | Time | During day | After midnight | Hypothesis: Time-based issue? Check timezone handling. ``` ### Technique 3: Trace Debugging ```typescript // Function call tracing function trace( target: any, propertyKey: string, descriptor: PropertyDescriptor, ) { const originalMethod = descriptor.value; descriptor.value = function (...args: any[]) { console.log(`Calling ${propertyKey} with args:`, args); const result = originalMethod.apply(this, args); console.log(`${propertyKey} returned:`, result); return result; }; return descriptor; } class OrderService { @trace calculateTotal(items: Item[]): number { return items.reduce((sum, item) => sum + item.price, 0); } } ``` ### Technique 4: Memory Leak Detection ```typescript // Chrome DevTools Memory Profiler // 1. Take heap snapshot // 2. Perform action // 3. Take another snapshot // 4. Compare snapshots // Node.js memory debugging if (process.memoryUsage().heapUsed > 500 * 1024 * 1024) { console.warn("High memory usage:", process.memoryUsage()); // Generate heap dump require("v8").writeHeapSnapshot(); } // Find memory leaks in tests let beforeMemory: number; beforeEach(() => { beforeMemory = process.memoryUsage().heapUsed; }); afterEach(() => { const afterMemory = process.memoryUsage().heapUsed; const diff = afterMemory - beforeMemory; if (diff > 10 * 1024 * 1024) { // 10MB threshold console.warn(`Possible memory leak: ${diff / 1024 / 1024}MB`); } }); ``` ## Debugging Patterns by Issue Type ### Pattern 1: Intermittent Bugs ```markdown ## Strategies for Flaky Bugs 1. **Add extensive logging** - Log timing information - Log all state transitions - Log external interactions 2. **Look for race conditions** - Concurrent access to shared state - Async operations completing out of order - Missing synchronization 3. **Check timing dependencies** - setTimeout/setInterval - Promise resolution order - Animation frame timing 4. **Stress test** - Run many times - Vary timing - Simulate load ``` ### Pattern 2: Performance Issues ```markdown ## Performance Debugging 1. **Profile first** - Don't optimize blindly - Measure before and after - Find bottlenecks 2. **Common culprits** - N+1 queries - Unnecessary re-renders - Large data processing - Synchronous I/O 3. **Tools** - Browser DevTools Performance tab - Lighthouse - Python: cProfile, line_profiler - Node: clinic.js, 0x ``` ### Pattern 3: Production Bugs ```markdown ## Production Debugging 1. **Gather evidence** - Error tracking (Sentry, Bugsnag) - Application logs - User reports - Metrics/monitoring 2. **Reproduce locally** - Use production data (anonymized) - Match environment - Follow exact steps 3. **Safe investigation** - Don't change production - Use feature flags - Add monitoring/logging - Test fixes in staging ``` ## Best Practices 1. **Reproduce First**: Can't fix what you can't reproduce 2. **Isolate the Problem**: Remove complexity until minimal case 3. **Read Error Messages**: They're usually helpful 4. **Check Recent Changes**: Most bugs are recent 5. **Use Version Control**: Git bisect, blame, history 6. **Take Breaks**: Fresh eyes see better 7. **Document Findings**: Help future you 8. **Fix Root Cause**: Not just symptoms ## Common Debugging Mistakes - **Making Multiple Changes**: Change one thing at a time - **Not Reading Error Messages**: Read the full stack trace - **Assuming It's Complex**: Often it's simple - **Debug Logging in Prod**: Remove before shipping - **Not Using Debugger**: console.log isn't always best - **Giving Up Too Soon**: Persistence pays off - **Not Testing the Fix**: Verify it actually works ## Quick Debugging Checklist ```markdown ## When Stuck, Check: - [ ] Spelling errors (typos in variable names) - [ ] Case sensitivity (fileName vs filename) - [ ] Null/undefined values - [ ] Array index off-by-one - [ ] Async timing (race conditions) - [ ] Scope issues (closure, hoisting) - [ ] Type mismatches - [ ] Missing dependencies - [ ] Environment variables - [ ] File paths (absolute vs relative) - [ ] Cache issues (clear cache) - [ ] Stale data (refresh database) ``` ## Resources - **references/debugging-tools-guide.md**: Comprehensive tool documentation - **references/performance-profiling.md**: Performance debugging guide - **references/production-debugging.md**: Debugging live systems - **assets/debugging-checklist.md**: Quick reference checklist - **assets/common-bugs.md**: Common bug patterns - **scripts/debug-helper.ts**: Debugging utility functions

# Nx Workspace Patterns Production patterns for Nx monorepo management. ## When to Use This Skill - Setting up new Nx workspaces - Configuring project boundaries - Optimizing CI with affected commands - Implementing remote caching - Managing dependencies between projects - Migrating to Nx ## Core Concepts ### 1. Nx Architecture ``` workspace/ ├── apps/ # Deployable applications │ ├── web/ │ └── api/ ├── libs/ # Shared libraries │ ├── shared/ │ │ ├── ui/ │ │ └── utils/ │ └── feature/ │ ├── auth/ │ └── dashboard/ ├── tools/ # Custom executors/generators ├── nx.json # Nx configuration └── workspace.json # Project configuration ``` ### 2. Library Types | Type | Purpose | Example | | --------------- | -------------------------------- | ------------------- | | **feature** | Smart components, business logic | `feature-auth` | | **ui** | Presentational components | `ui-buttons` | | **data-access** | API calls, state management | `data-access-users` | | **util** | Pure functions, helpers | `util-formatting` | | **shell** | App bootstrapping | `shell-web` | ## Templates ### Template 1: nx.json Configuration ```json { "$schema": "./node_modules/nx/schemas/nx-schema.json", "npmScope": "myorg", "affected": { "defaultBase": "main" }, "tasksRunnerOptions": { "default": { "runner": "nx/tasks-runners/default", "options": { "cacheableOperations": [ "build", "lint", "test", "e2e", "build-storybook" ], "parallel": 3 } } }, "targetDefaults": { "build": { "dependsOn": ["^build"], "inputs": ["production", "^production"], "cache": true }, "test": { "inputs": ["default", "^production", "{workspaceRoot}/jest.preset.js"], "cache": true }, "lint": { "inputs": ["default", "{workspaceRoot}/.eslintrc.json"], "cache": true }, "e2e": { "inputs": ["default", "^production"], "cache": true } }, "namedInputs": { "default": ["{projectRoot}/**/*", "sharedGlobals"], "production": [ "default", "!{projectRoot}/**/?(*.)+(spec|test).[jt]s?(x)?(.snap)", "!{projectRoot}/tsconfig.spec.json", "!{projectRoot}/jest.config.[jt]s", "!{projectRoot}/.eslintrc.json" ], "sharedGlobals": [ "{workspaceRoot}/babel.config.json", "{workspaceRoot}/tsconfig.base.json" ] }, "generators": { "@nx/react": { "application": { "style": "css", "linter": "eslint", "bundler": "webpack" }, "library": { "style": "css", "linter": "eslint" }, "component": { "style": "css" } } } } ``` ### Template 2: Project Configuration ```json // apps/web/project.json { "name": "web", "$schema": "../../node_modules/nx/schemas/project-schema.json", "sourceRoot": "apps/web/src", "projectType": "application", "tags": ["type:app", "scope:web"], "targets": { "build": { "executor": "@nx/webpack:webpack", "outputs": ["{options.outputPath}"], "defaultConfiguration": "production", "options": { "compiler": "babel", "outputPath": "dist/apps/web", "index": "apps/web/src/index.html", "main": "apps/web/src/main.tsx", "tsConfig": "apps/web/tsconfig.app.json", "assets": ["apps/web/src/assets"], "styles": ["apps/web/src/styles.css"] }, "configurations": { "development": { "extractLicenses": false, "optimization": false, "sourceMap": true }, "production": { "optimization": true, "outputHashing": "all", "sourceMap": false, "extractLicenses": true } } }, "serve": { "executor": "@nx/webpack:dev-server", "defaultConfiguration": "development", "options": { "buildTarget": "web:build" }, "configurations": { "development": { "buildTarget": "web:build:development" }, "production": { "buildTarget": "web:build:production" } } }, "test": { "executor": "@nx/jest:jest", "outputs": ["{workspaceRoot}/coverage/{projectRoot}"], "options": { "jestConfig": "apps/web/jest.config.ts", "passWithNoTests": true } }, "lint": { "executor": "@nx/eslint:lint", "outputs": ["{options.outputFile}"], "options": { "lintFilePatterns": ["apps/web/**/*.{ts,tsx,js,jsx}"] } } } } ``` ### Template 3: Module Boundary Rules ```json // .eslintrc.json { "root": true, "ignorePatterns": ["**/*"], "plugins": ["@nx"], "overrides": [ { "files": ["*.ts", "*.tsx", "*.js", "*.jsx"], "rules": { "@nx/enforce-module-boundaries": [ "error", { "enforceBuildableLibDependency": true, "allow": [], "depConstraints": [ { "sourceTag": "type:app", "onlyDependOnLibsWithTags": [ "type:feature", "type:ui", "type:data-access", "type:util" ] }, { "sourceTag": "type:feature", "onlyDependOnLibsWithTags": [ "type:ui", "type:data-access", "type:util" ] }, { "sourceTag": "type:ui", "onlyDependOnLibsWithTags": ["type:ui", "type:util"] }, { "sourceTag": "type:data-access", "onlyDependOnLibsWithTags": ["type:data-access", "type:util"] }, { "sourceTag": "type:util", "onlyDependOnLibsWithTags": ["type:util"] }, { "sourceTag": "scope:web", "onlyDependOnLibsWithTags": ["scope:web", "scope:shared"] }, { "sourceTag": "scope:api", "onlyDependOnLibsWithTags": ["scope:api", "scope:shared"] }, { "sourceTag": "scope:shared", "onlyDependOnLibsWithTags": ["scope:shared"] } ] } ] } } ] } ``` ### Template 4: Custom Generator ```typescript // tools/generators/feature-lib/index.ts import { Tree, formatFiles, generateFiles, joinPathFragments, names, readProjectConfiguration, } from "@nx/devkit"; import { libraryGenerator } from "@nx/react"; interface FeatureLibraryGeneratorSchema { name: string; scope: string; directory?: string; } export default async function featureLibraryGenerator( tree: Tree, options: FeatureLibraryGeneratorSchema, ) { const { name, scope, directory } = options; const projectDirectory = directory ? `${directory}/${name}` : `libs/${scope}/feature-${name}`; // Generate base library await libraryGenerator(tree, { name: `feature-${name}`, directory: projectDirectory, tags: `type:feature,scope:${scope}`, style: "css", skipTsConfig: false, skipFormat: true, unitTestRunner: "jest", linter: "eslint", }); // Add custom files const projectConfig = readProjectConfiguration( tree, `${scope}-feature-${name}`, ); const projectNames = names(name); generateFiles( tree, joinPathFragments(__dirname, "files"), projectConfig.sourceRoot, { ...projectNames, scope, tmpl: "", }, ); await formatFiles(tree); } ``` ### Template 5: CI Configuration with Affected ```yaml # .github/workflows/ci.yml name: CI on: push: branches: [main] pull_request: branches: [main] env: NX_CLOUD_ACCESS_TOKEN: ${{ secrets.NX_CLOUD_ACCESS_TOKEN }} jobs: main: runs-on: ubuntu-latest steps: - uses: actions/checkout@v4 with: fetch-depth: 0 - uses: actions/setup-node@v4 with: node-version: 20 cache: "npm" - name: Install dependencies run: npm ci - name: Derive SHAs for affected commands uses: nrwl/nx-set-shas@v4 - name: Run affected lint run: npx nx affected -t lint --parallel=3 - name: Run affected test run: npx nx affected -t test --parallel=3 --configuration=ci - name: Run affected build run: npx nx affected -t build --parallel=3 - name: Run affected e2e run: npx nx affected -t e2e --parallel=1 ``` ### Template 6: Remote Caching Setup ```typescript // nx.json with Nx Cloud { "tasksRunnerOptions": { "default": { "runner": "nx-cloud", "options": { "cacheableOperations": ["build", "lint", "test", "e2e"], "accessToken": "your-nx-cloud-token", "parallel": 3, "cacheDirectory": ".nx/cache" } } }, "nxCloudAccessToken": "your-nx-cloud-token" } // Self-hosted cache with S3 { "tasksRunnerOptions": { "default": { "runner": "@nx-aws-cache/nx-aws-cache", "options": { "cacheableOperations": ["build", "lint", "test"], "awsRegion": "us-east-1", "awsBucket": "my-nx-cache-bucket", "awsProfile": "default" } } } } ``` ## Common Commands ```bash # Generate new library nx g @nx/react:lib feature-auth --directory=libs/web --tags=type:feature,scope:web # Run affected tests nx affected -t test --base=main # View dependency graph nx graph # Run specific project nx build web --configuration=production # Reset cache nx reset # Run migrations nx migrate latest nx migrate --run-migrations ``` ## Best Practices ### Do's - **Use tags consistently** - Enforce with module boundaries - **Enable caching early** - Significant CI savings - **Keep libs focused** - Single responsibility - **Use generators** - Ensure consistency - **Document boundaries** - Help new developers ### Don'ts - **Don't create circular deps** - Graph should be acyclic - **Don't skip affected** - Test only what changed - **Don't ignore boundaries** - Tech debt accumulates - **Don't over-granularize** - Balance lib count ## Resources - [Nx Documentation](https://nx.dev/getting-started/intro) - [Module Boundaries](https://nx.dev/core-features/enforce-module-boundaries) - [Nx Cloud](https://nx.app/)

# Architecture Decision Records Comprehensive patterns for creating, maintaining, and managing Architecture Decision Records (ADRs) that capture the context and rationale behind significant technical decisions. ## When to Use This Skill - Making significant architectural decisions - Documenting technology choices - Recording design trade-offs - Onboarding new team members - Reviewing historical decisions - Establishing decision-making processes ## Core Concepts ### 1. What is an ADR? An Architecture Decision Record captures: - **Context**: Why we needed to make a decision - **Decision**: What we decided - **Consequences**: What happens as a result ### 2. When to Write an ADR | Write ADR | Skip ADR | | -------------------------- | ---------------------- | | New framework adoption | Minor version upgrades | | Database technology choice | Bug fixes | | API design patterns | Implementation details | | Security architecture | Routine maintenance | | Integration patterns | Configuration changes | ### 3. ADR Lifecycle ``` Proposed → Accepted → Deprecated → Superseded ↓ Rejected ``` ## Templates ### Template 1: Standard ADR (MADR Format) ```markdown # ADR-0001: Use PostgreSQL as Primary Database ## Status Accepted ## Context We need to select a primary database for our new e-commerce platform. The system will handle: - ~10,000 concurrent users - Complex product catalog with hierarchical categories - Transaction processing for orders and payments - Full-text search for products - Geospatial queries for store locator The team has experience with MySQL, PostgreSQL, and MongoDB. We need ACID compliance for financial transactions. ## Decision Drivers - **Must have ACID compliance** for payment processing - **Must support complex queries** for reporting - **Should support full-text search** to reduce infrastructure complexity - **Should have good JSON support** for flexible product attributes - **Team familiarity** reduces onboarding time ## Considered Options ### Option 1: PostgreSQL - **Pros**: ACID compliant, excellent JSON support (JSONB), built-in full-text search, PostGIS for geospatial, team has experience - **Cons**: Slightly more complex replication setup than MySQL ### Option 2: MySQL - **Pros**: Very familiar to team, simple replication, large community - **Cons**: Weaker JSON support, no built-in full-text search (need Elasticsearch), no geospatial without extensions ### Option 3: MongoDB - **Pros**: Flexible schema, native JSON, horizontal scaling - **Cons**: No ACID for multi-document transactions (at decision time), team has limited experience, requires schema design discipline ## Decision We will use **PostgreSQL 15** as our primary database. ## Rationale PostgreSQL provides the best balance of: 1. **ACID compliance** essential for e-commerce transactions 2. **Built-in capabilities** (full-text search, JSONB, PostGIS) reduce infrastructure complexity 3. **Team familiarity** with SQL databases reduces learning curve 4. **Mature ecosystem** with excellent tooling and community support The slight complexity in replication is outweighed by the reduction in additional services (no separate Elasticsearch needed). ## Consequences ### Positive - Single database handles transactions, search, and geospatial queries - Reduced operational complexity (fewer services to manage) - Strong consistency guarantees for financial data - Team can leverage existing SQL expertise ### Negative - Need to learn PostgreSQL-specific features (JSONB, full-text search syntax) - Vertical scaling limits may require read replicas sooner - Some team members need PostgreSQL-specific training ### Risks - Full-text search may not scale as well as dedicated search engines - Mitigation: Design for potential Elasticsearch addition if needed ## Implementation Notes - Use JSONB for flexible product attributes - Implement connection pooling with PgBouncer - Set up streaming replication for read replicas - Use pg_trgm extension for fuzzy search ## Related Decisions - ADR-0002: Caching Strategy (Redis) - complements database choice - ADR-0005: Search Architecture - may supersede if Elasticsearch needed ## References - [PostgreSQL JSON Documentation](https://www.postgresql.org/docs/current/datatype-json.html) - [PostgreSQL Full Text Search](https://www.postgresql.org/docs/current/textsearch.html) - Internal: Performance benchmarks in `/docs/benchmarks/database-comparison.md` ``` ### Template 2: Lightweight ADR ```markdown # ADR-0012: Adopt TypeScript for Frontend Development **Status**: Accepted **Date**: 2024-01-15 **Deciders**: @alice, @bob, @charlie ## Context Our React codebase has grown to 50+ components with increasing bug reports related to prop type mismatches and undefined errors. PropTypes provide runtime-only checking. ## Decision Adopt TypeScript for all new frontend code. Migrate existing code incrementally. ## Consequences **Good**: Catch type errors at compile time, better IDE support, self-documenting code. **Bad**: Learning curve for team, initial slowdown, build complexity increase. **Mitigations**: TypeScript training sessions, allow gradual adoption with `allowJs: true`. ``` ### Template 3: Y-Statement Format ```markdown # ADR-0015: API Gateway Selection In the context of **building a microservices architecture**, facing **the need for centralized API management, authentication, and rate limiting**, we decided for **Kong Gateway** and against **AWS API Gateway and custom Nginx solution**, to achieve **vendor independence, plugin extensibility, and team familiarity with Lua**, accepting that **we need to manage Kong infrastructure ourselves**. ``` ### Template 4: ADR for Deprecation ```markdown # ADR-0020: Deprecate MongoDB in Favor of PostgreSQL ## Status Accepted (Supersedes ADR-0003) ## Context ADR-0003 (2021) chose MongoDB for user profile storage due to schema flexibility needs. Since then: - MongoDB's multi-document transactions remain problematic for our use case - Our schema has stabilized and rarely changes - We now have PostgreSQL expertise from other services - Maintaining two databases increases operational burden ## Decision Deprecate MongoDB and migrate user profiles to PostgreSQL. ## Migration Plan 1. **Phase 1** (Week 1-2): Create PostgreSQL schema, dual-write enabled 2. **Phase 2** (Week 3-4): Backfill historical data, validate consistency 3. **Phase 3** (Week 5): Switch reads to PostgreSQL, monitor 4. **Phase 4** (Week 6): Remove MongoDB writes, decommission ## Consequences ### Positive - Single database technology reduces operational complexity - ACID transactions for user data - Team can focus PostgreSQL expertise ### Negative - Migration effort (~4 weeks) - Risk of data issues during migration - Lose some schema flexibility ## Lessons Learned Document from ADR-0003 experience: - Schema flexibility benefits were overestimated - Operational cost of multiple databases was underestimated - Consider long-term maintenance in technology decisions ``` ### Template 5: Request for Comments (RFC) Style ```markdown # RFC-0025: Adopt Event Sourcing for Order Management ## Summary Propose adopting event sourcing pattern for the order management domain to improve auditability, enable temporal queries, and support business analytics. ## Motivation Current challenges: 1. Audit requirements need complete order history 2. "What was the order state at time X?" queries are impossible 3. Analytics team needs event stream for real-time dashboards 4. Order state reconstruction for customer support is manual ## Detailed Design ### Event Store ``` OrderCreated { orderId, customerId, items[], timestamp } OrderItemAdded { orderId, item, timestamp } OrderItemRemoved { orderId, itemId, timestamp } PaymentReceived { orderId, amount, paymentId, timestamp } OrderShipped { orderId, trackingNumber, timestamp } ``` ### Projections - **CurrentOrderState**: Materialized view for queries - **OrderHistory**: Complete timeline for audit - **DailyOrderMetrics**: Analytics aggregation ### Technology - Event Store: EventStoreDB (purpose-built, handles projections) - Alternative considered: Kafka + custom projection service ## Drawbacks - Learning curve for team - Increased complexity vs. CRUD - Need to design events carefully (immutable once stored) - Storage growth (events never deleted) ## Alternatives 1. **Audit tables**: Simpler but doesn't enable temporal queries 2. **CDC from existing DB**: Complex, doesn't change data model 3. **Hybrid**: Event source only for order state changes ## Unresolved Questions - [ ] Event schema versioning strategy - [ ] Retention policy for events - [ ] Snapshot frequency for performance ## Implementation Plan 1. Prototype with single order type (2 weeks) 2. Team training on event sourcing (1 week) 3. Full implementation and migration (4 weeks) 4. Monitoring and optimization (ongoing) ## References - [Event Sourcing by Martin Fowler](https://martinfowler.com/eaaDev/EventSourcing.html) - [EventStoreDB Documentation](https://www.eventstore.com/docs) ``` ## ADR Management ### Directory Structure ``` docs/ ├── adr/ │ ├── README.md # Index and guidelines │ ├── template.md # Team's ADR template │ ├── 0001-use-postgresql.md │ ├── 0002-caching-strategy.md │ ├── 0003-mongodb-user-profiles.md # [DEPRECATED] │ └── 0020-deprecate-mongodb.md # Supersedes 0003 ``` ### ADR Index (README.md) ```markdown # Architecture Decision Records This directory contains Architecture Decision Records (ADRs) for [Project Name]. ## Index | ADR | Title | Status | Date | | ------------------------------------- | ---------------------------------- | ---------- | ---------- | | [0001](0001-use-postgresql.md) | Use PostgreSQL as Primary Database | Accepted | 2024-01-10 | | [0002](0002-caching-strategy.md) | Caching Strategy with Redis | Accepted | 2024-01-12 | | [0003](0003-mongodb-user-profiles.md) | MongoDB for User Profiles | Deprecated | 2023-06-15 | | [0020](0020-deprecate-mongodb.md) | Deprecate MongoDB | Accepted | 2024-01-15 | ## Creating a New ADR 1. Copy `template.md` to `NNNN-title-with-dashes.md` 2. Fill in the template 3. Submit PR for review 4. Update this index after approval ## ADR Status - **Proposed**: Under discussion - **Accepted**: Decision made, implementing - **Deprecated**: No longer relevant - **Superseded**: Replaced by another ADR - **Rejected**: Considered but not adopted ``` ### Automation (adr-tools) ```bash # Install adr-tools brew install adr-tools # Initialize ADR directory adr init docs/adr # Create new ADR adr new "Use PostgreSQL as Primary Database" # Supersede an ADR adr new -s 3 "Deprecate MongoDB in Favor of PostgreSQL" # Generate table of contents adr generate toc > docs/adr/README.md # Link related ADRs adr link 2 "Complements" 1 "Is complemented by" ``` ## Review Process ```markdown ## ADR Review Checklist ### Before Submission - [ ] Context clearly explains the problem - [ ] All viable options considered - [ ] Pros/cons balanced and honest - [ ] Consequences (positive and negative) documented - [ ] Related ADRs linked ### During Review - [ ] At least 2 senior engineers reviewed - [ ] Affected teams consulted - [ ] Security implications considered - [ ] Cost implications documented - [ ] Reversibility assessed ### After Acceptance - [ ] ADR index updated - [ ] Team notified - [ ] Implementation tickets created - [ ] Related documentation updated ``` ## Best Practices ### Do's - **Write ADRs early** - Before implementation starts - **Keep them short** - 1-2 pages maximum - **Be honest about trade-offs** - Include real cons - **Link related decisions** - Build decision graph - **Update status** - Deprecate when superseded ### Don'ts - **Don't change accepted ADRs** - Write new ones to supersede - **Don't skip context** - Future readers need background - **Don't hide failures** - Rejected decisions are valuable - **Don't be vague** - Specific decisions, specific consequences - **Don't forget implementation** - ADR without action is waste ## Resources - [Documenting Architecture Decisions (Michael Nygard)](https://cognitect.com/blog/2011/11/15/documenting-architecture-decisions) - [MADR Template](https://adr.github.io/madr/) - [ADR GitHub Organization](https://adr.github.io/) - [adr-tools](https://github.com/npryce/adr-tools)

# .NET Backend Development Patterns Master C#/.NET patterns for building production-grade APIs, MCP servers, and enterprise backends with modern best practices (2024/2025). ## When to Use This Skill - Developing new .NET Web APIs or MCP servers - Reviewing C# code for quality and performance - Designing service architectures with dependency injection - Implementing caching strategies with Redis - Writing unit and integration tests - Optimizing database access with EF Core or Dapper - Configuring applications with IOptions pattern - Handling errors and implementing resilience patterns ## Core Concepts ### 1. Project Structure (Clean Architecture) ``` src/ ├── Domain/ # Core business logic (no dependencies) │ ├── Entities/ │ ├── Interfaces/ │ ├── Exceptions/ │ └── ValueObjects/ ├── Application/ # Use cases, DTOs, validation │ ├── Services/ │ ├── DTOs/ │ ├── Validators/ │ └── Interfaces/ ├── Infrastructure/ # External implementations │ ├── Data/ # EF Core, Dapper repositories │ ├── Caching/ # Redis, Memory cache │ ├── External/ # HTTP clients, third-party APIs │ └── DependencyInjection/ # Service registration └── Api/ # Entry point ├── Controllers/ # Or MinimalAPI endpoints ├── Middleware/ ├── Filters/ └── Program.cs ``` ### 2. Dependency Injection Patterns ```csharp // Service registration by lifetime public static class ServiceCollectionExtensions { public static IServiceCollection AddApplicationServices( this IServiceCollection services, IConfiguration configuration) { // Scoped: One instance per HTTP request services.AddScoped<IProductService, ProductService>(); services.AddScoped<IOrderService, OrderService>(); // Singleton: One instance for app lifetime services.AddSingleton<ICacheService, RedisCacheService>(); services.AddSingleton<IConnectionMultiplexer>(_ => ConnectionMultiplexer.Connect(configuration["Redis:Connection"]!)); // Transient: New instance every time services.AddTransient<IValidator<CreateOrderRequest>, CreateOrderValidator>(); // Options pattern for configuration services.Configure<CatalogOptions>(configuration.GetSection("Catalog")); services.Configure<RedisOptions>(configuration.GetSection("Redis")); // Factory pattern for conditional creation services.AddScoped<IPriceCalculator>(sp => { var options = sp.GetRequiredService<IOptions<PricingOptions>>().Value; return options.UseNewEngine ? sp.GetRequiredService<NewPriceCalculator>() : sp.GetRequiredService<LegacyPriceCalculator>(); }); // Keyed services (.NET 8+) services.AddKeyedScoped<IPaymentProcessor, StripeProcessor>("stripe"); services.AddKeyedScoped<IPaymentProcessor, PayPalProcessor>("paypal"); return services; } } // Usage with keyed services public class CheckoutService { public CheckoutService( [FromKeyedServices("stripe")] IPaymentProcessor stripeProcessor) { _processor = stripeProcessor; } } ``` ### 3. Async/Await Patterns ```csharp // ✅ CORRECT: Async all the way down public async Task<Product> GetProductAsync(string id, CancellationToken ct = default) { return await _repository.GetByIdAsync(id, ct); } // ✅ CORRECT: Parallel execution with WhenAll public async Task<(Stock, Price)> GetStockAndPriceAsync( string productId, CancellationToken ct = default) { var stockTask = _stockService.GetAsync(productId, ct); var priceTask = _priceService.GetAsync(productId, ct); await Task.WhenAll(stockTask, priceTask); return (await stockTask, await priceTask); } // ✅ CORRECT: ConfigureAwait in libraries public async Task<T> LibraryMethodAsync<T>(CancellationToken ct = default) { var result = await _httpClient.GetAsync(url, ct).ConfigureAwait(false); return await result.Content.ReadFromJsonAsync<T>(ct).ConfigureAwait(false); } // ✅ CORRECT: ValueTask for hot paths with caching public ValueTask<Product?> GetCachedProductAsync(string id) { if (_cache.TryGetValue(id, out Product? product)) return ValueTask.FromResult(product); return new ValueTask<Product?>(GetFromDatabaseAsync(id)); } // ❌ WRONG: Blocking on async (deadlock risk) var result = GetProductAsync(id).Result; // NEVER do this var result2 = GetProductAsync(id).GetAwaiter().GetResult(); // Also bad // ❌ WRONG: async void (except event handlers) public async void ProcessOrder() { } // Exceptions are lost // ❌ WRONG: Unnecessary Task.Run for already async code await Task.Run(async () => await GetDataAsync()); // Wastes thread ``` ### 4. Configuration with IOptions ```csharp // Configuration classes public class CatalogOptions { public const string SectionName = "Catalog"; public int DefaultPageSize { get; set; } = 50; public int MaxPageSize { get; set; } = 200; public TimeSpan CacheDuration { get; set; } = TimeSpan.FromMinutes(15); public bool EnableEnrichment { get; set; } = true; } public class RedisOptions { public const string SectionName = "Redis"; public string Connection { get; set; } = "localhost:6379"; public string KeyPrefix { get; set; } = "mcp:"; public int Database { get; set; } = 0; } // appsettings.json { "Catalog": { "DefaultPageSize": 50, "MaxPageSize": 200, "CacheDuration": "00:15:00", "EnableEnrichment": true }, "Redis": { "Connection": "localhost:6379", "KeyPrefix": "mcp:", "Database": 0 } } // Registration services.Configure<CatalogOptions>(configuration.GetSection(CatalogOptions.SectionName)); services.Configure<RedisOptions>(configuration.GetSection(RedisOptions.SectionName)); // Usage with IOptions (singleton, read once at startup) public class CatalogService { private readonly CatalogOptions _options; public CatalogService(IOptions<CatalogOptions> options) { _options = options.Value; } } // Usage with IOptionsSnapshot (scoped, re-reads on each request) public class DynamicService { private readonly CatalogOptions _options; public DynamicService(IOptionsSnapshot<CatalogOptions> options) { _options = options.Value; // Fresh value per request } } // Usage with IOptionsMonitor (singleton, notified on changes) public class MonitoredService { private CatalogOptions _options; public MonitoredService(IOptionsMonitor<CatalogOptions> monitor) { _options = monitor.CurrentValue; monitor.OnChange(newOptions => _options = newOptions); } } ``` ### 5. Result Pattern (Avoiding Exceptions for Flow Control) ```csharp // Generic Result type public class Result<T> { public bool IsSuccess { get; } public T? Value { get; } public string? Error { get; } public string? ErrorCode { get; } private Result(bool isSuccess, T? value, string? error, string? errorCode) { IsSuccess = isSuccess; Value = value; Error = error; ErrorCode = errorCode; } public static Result<T> Success(T value) => new(true, value, null, null); public static Result<T> Failure(string error, string? code = null) => new(false, default, error, code); public Result<TNew> Map<TNew>(Func<T, TNew> mapper) => IsSuccess ? Result<TNew>.Success(mapper(Value!)) : Result<TNew>.Failure(Error!, ErrorCode); public async Task<Result<TNew>> MapAsync<TNew>(Func<T, Task<TNew>> mapper) => IsSuccess ? Result<TNew>.Success(await mapper(Value!)) : Result<TNew>.Failure(Error!, ErrorCode); } // Usage in service public async Task<Result<Order>> CreateOrderAsync(CreateOrderRequest request, CancellationToken ct) { // Validation var validation = await _validator.ValidateAsync(request, ct); if (!validation.IsValid) return Result<Order>.Failure( validation.Errors.First().ErrorMessage, "VALIDATION_ERROR"); // Business rule check var stock = await _stockService.CheckAsync(request.ProductId, request.Quantity, ct); if (!stock.IsAvailable) return Result<Order>.Failure( $"Insufficient stock: {stock.Available} available, {request.Quantity} requested", "INSUFFICIENT_STOCK"); // Create order var order = await _repository.CreateAsync(request.ToEntity(), ct); return Result<Order>.Success(order); } // Usage in controller/endpoint app.MapPost("/orders", async ( CreateOrderRequest request, IOrderService orderService, CancellationToken ct) => { var result = await orderService.CreateOrderAsync(request, ct); return result.IsSuccess ? Results.Created($"/orders/{result.Value!.Id}", result.Value) : Results.BadRequest(new { error = result.Error, code = result.ErrorCode }); }); ``` ## Data Access Patterns ### Entity Framework Core ```csharp // DbContext configuration public class AppDbContext : DbContext { public DbSet<Product> Products => Set<Product>(); public DbSet<Order> Orders => Set<Order>(); protected override void OnModelCreating(ModelBuilder modelBuilder) { // Apply all configurations from assembly modelBuilder.ApplyConfigurationsFromAssembly(typeof(AppDbContext).Assembly); // Global query filters modelBuilder.Entity<Product>().HasQueryFilter(p => !p.IsDeleted); } } // Entity configuration public class ProductConfiguration : IEntityTypeConfiguration<Product> { public void Configure(EntityTypeBuilder<Product> builder) { builder.ToTable("Products"); builder.HasKey(p => p.Id); builder.Property(p => p.Id).HasMaxLength(40); builder.Property(p => p.Name).HasMaxLength(200).IsRequired(); builder.Property(p => p.Price).HasPrecision(18, 2); builder.HasIndex(p => p.Sku).IsUnique(); builder.HasIndex(p => new { p.CategoryId, p.Name }); builder.HasMany(p => p.OrderItems) .WithOne(oi => oi.Product) .HasForeignKey(oi => oi.ProductId); } } // Repository with EF Core public class ProductRepository : IProductRepository { private readonly AppDbContext _context; public async Task<Product?> GetByIdAsync(string id, CancellationToken ct = default) { return await _context.Products .AsNoTracking() .FirstOrDefaultAsync(p => p.Id == id, ct); } public async Task<IReadOnlyList<Product>> SearchAsync( ProductSearchCriteria criteria, CancellationToken ct = default) { var query = _context.Products.AsNoTracking(); if (!string.IsNullOrWhiteSpace(criteria.SearchTerm)) query = query.Where(p => EF.Functions.Like(p.Name, $"%{criteria.SearchTerm}%")); if (criteria.CategoryId.HasValue) query = query.Where(p => p.CategoryId == criteria.CategoryId); if (criteria.MinPrice.HasValue) query = query.Where(p => p.Price >= criteria.MinPrice); if (criteria.MaxPrice.HasValue) query = query.Where(p => p.Price <= criteria.MaxPrice); return await query .OrderBy(p => p.Name) .Skip((criteria.Page - 1) * criteria.PageSize) .Take(criteria.PageSize) .ToListAsync(ct); } } ``` ### Dapper for Performance ```csharp public class DapperProductRepository : IProductRepository { private readonly IDbConnection _connection; public async Task<Product?> GetByIdAsync(string id, CancellationToken ct = default) { const string sql = """ SELECT Id, Name, Sku, Price, CategoryId, Stock, CreatedAt FROM Products WHERE Id = @Id AND IsDeleted = 0 """; return await _connection.QueryFirstOrDefaultAsync<Product>( new CommandDefinition(sql, new { Id = id }, cancellationToken: ct)); } public async Task<IReadOnlyList<Product>> SearchAsync( ProductSearchCriteria criteria, CancellationToken ct = default) { var sql = new StringBuilder(""" SELECT Id, Name, Sku, Price, CategoryId, Stock, CreatedAt FROM Products WHERE IsDeleted = 0 """); var parameters = new DynamicParameters(); if (!string.IsNullOrWhiteSpace(criteria.SearchTerm)) { sql.Append(" AND Name LIKE @SearchTerm"); parameters.Add("SearchTerm", $"%{criteria.SearchTerm}%"); } if (criteria.CategoryId.HasValue) { sql.Append(" AND CategoryId = @CategoryId"); parameters.Add("CategoryId", criteria.CategoryId); } if (criteria.MinPrice.HasValue) { sql.Append(" AND Price >= @MinPrice"); parameters.Add("MinPrice", criteria.MinPrice); } if (criteria.MaxPrice.HasValue) { sql.Append(" AND Price <= @MaxPrice"); parameters.Add("MaxPrice", criteria.MaxPrice); } sql.Append(" ORDER BY Name OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY"); parameters.Add("Offset", (criteria.Page - 1) * criteria.PageSize); parameters.Add("PageSize", criteria.PageSize); var results = await _connection.QueryAsync<Product>( new CommandDefinition(sql.ToString(), parameters, cancellationToken: ct)); return results.ToList(); } // Multi-mapping for related data public async Task<Order?> GetOrderWithItemsAsync(int orderId, CancellationToken ct = default) { const string sql = """ SELECT o.*, oi.*, p.* FROM Orders o LEFT JOIN OrderItems oi ON o.Id = oi.OrderId LEFT JOIN Products p ON oi.ProductId = p.Id WHERE o.Id = @OrderId """; var orderDictionary = new Dictionary<int, Order>(); await _connection.QueryAsync<Order, OrderItem, Product, Order>( new CommandDefinition(sql, new { OrderId = orderId }, cancellationToken: ct), (order, item, product) => { if (!orderDictionary.TryGetValue(order.Id, out var existingOrder)) { existingOrder = order; existingOrder.Items = new List<OrderItem>(); orderDictionary.Add(order.Id, existingOrder); } if (item != null) { item.Product = product; existingOrder.Items.Add(item); } return existingOrder; }, splitOn: "Id,Id"); return orderDictionary.Values.FirstOrDefault(); } } ``` ## Caching Patterns ### Multi-Level Cache with Redis ```csharp public class CachedProductService : IProductService { private readonly IProductRepository _repository; private readonly IMemoryCache _memoryCache; private readonly IDistributedCache _distributedCache; private readonly ILogger<CachedProductService> _logger; private static readonly TimeSpan MemoryCacheDuration = TimeSpan.FromMinutes(1); private static readonly TimeSpan DistributedCacheDuration = TimeSpan.FromMinutes(15); public async Task<Product?> GetByIdAsync(string id, CancellationToken ct = default) { var cacheKey = $"product:{id}"; // L1: Memory cache (in-process, fastest) if (_memoryCache.TryGetValue(cacheKey, out Product? cached)) { _logger.LogDebug("L1 cache hit for {CacheKey}", cacheKey); return cached; } // L2: Distributed cache (Redis) var distributed = await _distributedCache.GetStringAsync(cacheKey, ct); if (distributed != null) { _logger.LogDebug("L2 cache hit for {CacheKey}", cacheKey); var product = JsonSerializer.Deserialize<Product>(distributed); // Populate L1 _memoryCache.Set(cacheKey, product, MemoryCacheDuration); return product; } // L3: Database _logger.LogDebug("Cache miss for {CacheKey}, fetching from database", cacheKey); var fromDb = await _repository.GetByIdAsync(id, ct); if (fromDb != null) { var serialized = JsonSerializer.Serialize(fromDb); // Populate both caches await _distributedCache.SetStringAsync( cacheKey, serialized, new DistributedCacheEntryOptions { AbsoluteExpirationRelativeToNow = DistributedCacheDuration }, ct); _memoryCache.Set(cacheKey, fromDb, MemoryCacheDuration); } return fromDb; } public async Task InvalidateAsync(string id, CancellationToken ct = default) { var cacheKey = $"product:{id}"; _memoryCache.Remove(cacheKey); await _distributedCache.RemoveAsync(cacheKey, ct); _logger.LogInformation("Invalidated cache for {CacheKey}", cacheKey); } } // Stale-while-revalidate pattern public class StaleWhileRevalidateCache<T> { private readonly IDistributedCache _cache; private readonly TimeSpan _freshDuration; private readonly TimeSpan _staleDuration; public async Task<T?> GetOrCreateAsync( string key, Func<CancellationToken, Task<T>> factory, CancellationToken ct = default) { var cached = await _cache.GetStringAsync(key, ct); if (cached != null) { var entry = JsonSerializer.Deserialize<CacheEntry<T>>(cached)!; if (entry.IsStale && !entry.IsExpired) { // Return stale data immediately, refresh in background _ = Task.Run(async () => { var fresh = await factory(CancellationToken.None); await SetAsync(key, fresh, CancellationToken.None); }); } if (!entry.IsExpired) return entry.Value; } // Cache miss or expired var value = await factory(ct); await SetAsync(key, value, ct); return value; } private record CacheEntry<TValue>(TValue Value, DateTime CreatedAt) { public bool IsStale => DateTime.UtcNow - CreatedAt > _freshDuration; public bool IsExpired => DateTime.UtcNow - CreatedAt > _staleDuration; } } ``` ## Testing Patterns ### Unit Tests with xUnit and Moq ```csharp public class OrderServiceTests { private readonly Mock<IOrderRepository> _mockRepository; private readonly Mock<IStockService> _mockStockService; private readonly Mock<IValidator<CreateOrderRequest>> _mockValidator; private readonly OrderService _sut; // System Under Test public OrderServiceTests() { _mockRepository = new Mock<IOrderRepository>(); _mockStockService = new Mock<IStockService>(); _mockValidator = new Mock<IValidator<CreateOrderRequest>>(); // Default: validation passes _mockValidator .Setup(v => v.ValidateAsync(It.IsAny<CreateOrderRequest>(), It.IsAny<