performance-optimization

Original source / Raw SKILL.md

---
name: performance-optimization
description: Optimize application performance for speed, efficiency, and scalability. Use when improving page load times, reducing bundle size, optimizing database queries, or fixing performance bottlenecks. Handles React optimization, lazy loading, caching, code splitting, and profiling.
metadata:
  tags: performance, optimization, React, lazy-loading, caching, profiling, web-vitals
  platforms: Claude, ChatGPT, Gemini
---


# Performance Optimization


## When to use this skill

- **Slow page loads**: low Lighthouse score
- **Slow rendering**: delayed user interactions
- **Large bundle size**: increased download time
- **Slow queries**: database bottlenecks

## Instructions

### Step 1: Measure performance

**Lighthouse (Chrome DevTools)**:
```bash
# CLI
npm install -g lighthouse
lighthouse https://example.com --view

# Automate in CI
lighthouse https://example.com --output=json --output-path=./report.json
```

**Measure Web Vitals** (React):
```typescript
import { getCLS, getFID, getFCP, getLCP, getTTFB } from 'web-vitals';

function sendToAnalytics(metric: any) {
  // Send to Google Analytics, Datadog, etc.
  console.log(metric);
}

getCLS(sendToAnalytics);
getFID(sendToAnalytics);
getFCP(sendToAnalytics);
getLCP(sendToAnalytics);
getTTFB(sendToAnalytics);
```

### Step 2: Optimize React

**React.memo (prevent unnecessary re-renders)**:
```tsx
// ❌ Bad: child re-renders whenever the parent re-renders
function ExpensiveComponent({ data }: { data: Data }) {
  return <div>{/* complex rendering */}</div>;
}

// ✅ Good: re-render only when props change
const ExpensiveComponent = React.memo(({ data }: { data: Data }) => {
  return <div>{/* complex rendering */}</div>;
});
```

**useMemo & useCallback**:
```tsx
function ProductList({ products, category }: Props) {
  // ✅ Memoize filtered results
  const filteredProducts = useMemo(() => {
    return products.filter(p => p.category === category);
  }, [products, category]);

  // ✅ Memoize callback
  const handleAddToCart = useCallback((id: string) => {
    addToCart(id);
  }, []);

  return (
    <div>
      {filteredProducts.map(product => (
        <ProductCard key={product.id} product={product} onAdd={handleAddToCart} />
      ))}
    </div>
  );
}
```

**Lazy Loading & Code Splitting**:
```tsx
import { lazy, Suspense } from 'react';

// ✅ Route-based code splitting
const Dashboard = lazy(() => import('./pages/Dashboard'));
const Profile = lazy(() => import('./pages/Profile'));
const Settings = lazy(() => import('./pages/Settings'));

function App() {
  return (
    <Suspense fallback={<div>Loading...</div>}>
      <Routes>
        <Route path="/dashboard" element={<Dashboard />} />
        <Route path="/profile" element={<Profile />} />
        <Route path="/settings" element={<Settings />} />
      </Routes>
    </Suspense>
  );
}

// ✅ Component-based lazy loading
const HeavyChart = lazy(() => import('./components/HeavyChart'));

function Dashboard() {
  return (
    <div>
      <h1>Dashboard</h1>
      <Suspense fallback={<Skeleton />}>
        <HeavyChart data={data} />
      </Suspense>
    </div>
  );
}
```

### Step 3: Optimize bundle size

**Webpack Bundle Analyzer**:
```bash
npm install --save-dev webpack-bundle-analyzer

# package.json
{
  "scripts": {
    "analyze": "webpack-bundle-analyzer build/stats.json"
  }
}
```

**Tree Shaking (remove unused code)**:
```typescript
// ❌ Bad: import entire library
import _ from 'lodash';

// ✅ Good: import only what you need
import debounce from 'lodash/debounce';
```

**Dynamic Imports**:
```typescript
// ✅ Load only when needed
button.addEventListener('click', async () => {
  const { default: Chart } = await import('chart.js');
  new Chart(ctx, config);
});
```

### Step 4: Optimize images

**Next.js Image component**:
```tsx
import Image from 'next/image';

function ProductImage() {
  return (
    <Image
      src="/product.jpg"
      alt="Product"
      width={500}
      height={500}
      priority  // for the LCP image
      placeholder="blur"  // blur placeholder
      sizes="(max-width: 768px) 100vw, 50vw"
    />
  );
}
```

**Use WebP format**:
```html
<picture>
  <source srcset="image.webp" type="image/webp">
  <source srcset="image.jpg" type="image/jpeg">
  <img src="image.jpg" alt="Fallback">
</picture>
```

### Step 5: Optimize database queries

**Fix the N+1 query problem**:
```typescript
// ❌ Bad: N+1 queries
const posts = await db.post.findMany();
for (const post of posts) {
  const author = await db.user.findUnique({ where: { id: post.authorId } });
  // 101 queries (1 + 100)
}

// ✅ Good: JOIN or include
const posts = await db.post.findMany({
  include: {
    author: true
  }
});
// 1 query
```

**Add indexes**:
```sql
-- Identify slow queries
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

-- Add index
CREATE INDEX idx_users_email ON users(email);

-- Composite index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
```

**Caching (Redis)**:
```typescript
async function getUserProfile(userId: string) {
  // 1. Check cache
  const cached = await redis.get(`user:${userId}`);
  if (cached) {
    return JSON.parse(cached);
  }

  // 2. Query DB
  const user = await db.user.findUnique({ where: { id: userId } });

  // 3. Store in cache (1 hour)
  await redis.setex(`user:${userId}`, 3600, JSON.stringify(user));

  return user;
}
```

## Output format

### Performance optimization checklist

```markdown
## Frontend
- [ ] Prevent unnecessary re-renders with React.memo
- [ ] Use useMemo/useCallback appropriately
- [ ] Lazy loading & Code splitting
- [ ] Optimize images (WebP, lazy loading)
- [ ] Analyze and reduce bundle size

## Backend
- [ ] Remove N+1 queries
- [ ] Add database indexes
- [ ] Redis caching
- [ ] Compress API responses (gzip)
- [ ] Use a CDN

## Measurement
- [ ] Lighthouse score 90+
- [ ] LCP < 2.5s
- [ ] FID < 100ms
- [ ] CLS < 0.1
```

## Constraints

### Required rules (MUST)

1. **Measure first**: profile, don't guess
2. **Incremental improvements**: optimize one thing at a time
3. **Performance monitoring**: track continuously

### Prohibited items (MUST NOT)

1. **Premature optimization**: don't optimize when there is no bottleneck
2. **Sacrificing readability**: don't make code complex for performance

## Best practices

1. **80/20 rule**: 80% improvement with 20% effort
2. **User-centered**: focus on improving real user experience
3. **Automation**: performance regression tests in CI

## References

- [web.dev/vitals](https://web.dev/vitals/)
- [React Optimization](https://react.dev/learn/render-and-commit#optimizing-performance)
- [Webpack Bundle Analyzer](https://github.com/webpack-contrib/webpack-bundle-analyzer)

## Metadata

### Version
- **Current version**: 1.0.0
- **Last updated**: 2025-01-01
- **Compatible platforms**: Claude, ChatGPT, Gemini

### Related skills
- [database-schema-design](../database-schema-design/SKILL.md)
- [ui-components](../ui-component-patterns/SKILL.md)

### Tags
`#performance` `#optimization` `#React` `#caching` `#lazy-loading` `#web-vitals` `#code-quality`

## Examples

### Example 1: Basic usage
<!-- Add example content here -->

### Example 2: Advanced usage
<!-- Add advanced example content here -->


---

## Referenced Files

> The following files are referenced in this skill and included for context.

### ../database-schema-design/SKILL.md

```markdown
---
name: database-schema-design
description: Design and optimize database schemas for SQL and NoSQL databases. Use when creating new databases, designing tables, defining relationships, indexing strategies, or database migrations. Handles PostgreSQL, MySQL, MongoDB, normalization, and performance optimization.
metadata:
  tags: database, schema, SQL, NoSQL, PostgreSQL, MySQL, MongoDB, migration
  platforms: Claude, ChatGPT, Gemini
---


# Database Schema Design


## When to use this skill

Lists specific situations where this skill should be triggered:

- **New Project**: Database schema design for a new application
- **Schema Refactoring**: Redesigning an existing schema for performance or scalability
- **Relationship Definition**: Implementing 1:1, 1:N, N:M relationships between tables
- **Migration**: Safely applying schema changes
- **Performance Issues**: Index and schema optimization to resolve slow queries

## Input Format

The required and optional input information to collect from the user:

### Required Information
- **Database Type**: PostgreSQL, MySQL, MongoDB, SQLite, etc.
- **Domain Description**: What data will be stored (e.g., e-commerce, blog, social media)
- **Key Entities**: Core data objects (e.g., User, Product, Order)

### Optional Information
- **Expected Data Volume**: Small (<10K rows), Medium (10K-1M), Large (>1M) (default: Medium)
- **Read/Write Ratio**: Read-heavy, Write-heavy, Balanced (default: Balanced)
- **Transaction Requirements**: Whether ACID is required (default: true)
- **Sharding/Partitioning**: Whether large data distribution is needed (default: false)

### Input Example

```
Design a database for an e-commerce platform:
- DB: PostgreSQL
- Entities: User, Product, Order, Review
- Relationships:
  - A User can have multiple Orders
  - An Order contains multiple Products (N:M)
  - A Review is linked to a User and a Product
- Expected data: 100,000 users, 10,000 products
- Read-heavy (frequent product lookups)
```

## Instructions

Specifies the step-by-step task sequence to follow precisely.

### Step 1: Define Entities and Attributes

Identify core data objects and their attributes.

**Tasks**:
- Extract nouns from business requirements → entities
- List each entity's attributes (columns)
- Determine data types (VARCHAR, INTEGER, TIMESTAMP, JSON, etc.)
- Designate Primary Keys (UUID vs Auto-increment ID)

**Example** (E-commerce):
```
Users
- id: UUID PRIMARY KEY
- email: VARCHAR(255) UNIQUE NOT NULL
- username: VARCHAR(50) UNIQUE NOT NULL
- password_hash: VARCHAR(255) NOT NULL
- created_at: TIMESTAMP DEFAULT NOW()
- updated_at: TIMESTAMP DEFAULT NOW()

Products
- id: UUID PRIMARY KEY
- name: VARCHAR(255) NOT NULL
- description: TEXT
- price: DECIMAL(10, 2) NOT NULL
- stock: INTEGER DEFAULT 0
- category_id: UUID REFERENCES Categories(id)
- created_at: TIMESTAMP DEFAULT NOW()

Orders
- id: UUID PRIMARY KEY
- user_id: UUID REFERENCES Users(id)
- total_amount: DECIMAL(10, 2) NOT NULL
- status: VARCHAR(20) DEFAULT 'pending'
- created_at: TIMESTAMP DEFAULT NOW()

OrderItems (Junction table)
- id: UUID PRIMARY KEY
- order_id: UUID REFERENCES Orders(id) ON DELETE CASCADE
- product_id: UUID REFERENCES Products(id)
- quantity: INTEGER NOT NULL
- price: DECIMAL(10, 2) NOT NULL
```

### Step 2: Design Relationships and Normalization

Define relationships between tables and apply normalization.

**Tasks**:
- 1:1 relationship: Foreign Key + UNIQUE constraint
- 1:N relationship: Foreign Key
- N:M relationship: Create junction table
- Determine normalization level (1NF ~ 3NF)

**Decision Criteria**:
- OLTP systems → normalize to 3NF (data integrity)
- OLAP/analytics systems → denormalization allowed (query performance)
- Read-heavy → minimize JOINs with partial denormalization
- Write-heavy → full normalization to eliminate redundancy

**Example** (ERD Mermaid):
```mermaid
erDiagram
    Users ||--o{ Orders : places
    Orders ||--|{ OrderItems : contains
    Products ||--o{ OrderItems : "ordered in"
    Categories ||--o{ Products : categorizes
    Users ||--o{ Reviews : writes
    Products ||--o{ Reviews : "reviewed by"

    Users {
        uuid id PK
        string email UK
        string username UK
        string password_hash
        timestamp created_at
    }

    Products {
        uuid id PK
        string name
        decimal price
        int stock
        uuid category_id FK
    }

    Orders {
        uuid id PK
        uuid user_id FK
        decimal total_amount
        string status
        timestamp created_at
    }

    OrderItems {
        uuid id PK
        uuid order_id FK
        uuid product_id FK
        int quantity
        decimal price
    }
```

### Step 3: Establish Indexing Strategy

Design indexes for query performance.

**Tasks**:
- Primary Keys automatically create indexes
- Columns frequently used in WHERE clauses → add indexes
- Foreign Keys used in JOINs → indexes
- Consider composite indexes (WHERE col1 = ? AND col2 = ?)
- UNIQUE indexes (email, username, etc.)

**Checklist**:
- [x] Indexes on frequently queried columns
- [x] Indexes on Foreign Key columns
- [x] Composite index order optimized (high selectivity columns first)
- [x] Avoid excessive indexes (degrades INSERT/UPDATE performance)

**Example** (PostgreSQL):
```sql
-- Primary Keys (auto-indexed)
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,  -- UNIQUE = auto-indexed
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Foreign Keys + explicit indexes
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- Composite index (status and created_at frequently queried together)
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

-- Products table
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    category_id UUID REFERENCES categories(id),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_price ON products(price);  -- price range search
CREATE INDEX idx_products_name ON products(name);    -- product name search

-- Full-text search (PostgreSQL)
CREATE INDEX idx_products_name_fts ON products USING GIN(to_tsvector('english', name));
CREATE INDEX idx_products_description_fts ON products USING GIN(to_tsvector('english', description));
```

### Step 4: Set Up Constraints and Triggers

Add constraints to ensure data integrity.

**Tasks**:
- NOT NULL: required columns
- UNIQUE: columns that must be unique
- CHECK: value range constraints (e.g., price >= 0)
- Foreign Key + CASCADE option
- Set default values

**Example**:
```sql
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    discount_percent INTEGER CHECK (discount_percent >= 0 AND discount_percent <= 100),
    category_id UUID REFERENCES categories(id) ON DELETE SET NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Trigger: auto-update updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
```

### Step 5: Write Migration Scripts

Write migrations that safely apply schema changes.

**Tasks**:
- UP migration: apply changes
- DOWN migration: rollback
- Wrap in transactions
- Prevent data loss (use ALTER TABLE carefully)

**Example** (SQL migration):
```sql
-- migrations/001_create_initial_schema.up.sql
BEGIN;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE categories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) UNIQUE NOT NULL,
    parent_id UUID REFERENCES categories(id)
);

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    category_id UUID REFERENCES categories(id),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_price ON products(price);

COMMIT;

-- migrations/001_create_initial_schema.down.sql
BEGIN;

DROP TABLE IF EXISTS products CASCADE;
DROP TABLE IF EXISTS categories CASCADE;
DROP TABLE IF EXISTS users CASCADE;

COMMIT;
```

## Output format

Defines the exact format that deliverables should follow.

### Basic Structure

```
project/
├── database/
│   ├── schema.sql                    # full schema
│   ├── migrations/
│   │   ├── 001_create_users.up.sql
│   │   ├── 001_create_users.down.sql
│   │   ├── 002_create_products.up.sql
│   │   └── 002_create_products.down.sql
│   ├── seeds/
│   │   └── sample_data.sql           # test data
│   └── docs/
│       ├── ERD.md                     # Mermaid ERD diagram
│       └── SCHEMA.md                  # schema documentation
└── README.md
```

### ERD Diagram (Mermaid Format)

```markdown
# Database Schema

## Entity Relationship Diagram

\`\`\`mermaid
erDiagram
    Users ||--o{ Orders : places
    Orders ||--|{ OrderItems : contains
    Products ||--o{ OrderItems : "ordered in"

    Users {
        uuid id PK
        string email UK
        string username UK
    }

    Products {
        uuid id PK
        string name
        decimal price
    }
\`\`\`

## Table Descriptions

### users
- **Purpose**: Store user account information
- **Indexes**: email, username
- **Estimated rows**: 100,000

### products
- **Purpose**: Product catalog
- **Indexes**: category_id, price, name
- **Estimated rows**: 10,000
```

## Constraints

Specifies mandatory rules and prohibited actions.

### Mandatory Rules (MUST)

1. **Primary Key Required**: Define a Primary Key on every table
   - Unique record identification
   - Ensures referential integrity

2. **Explicit Foreign Keys**: Tables with relationships must define Foreign Keys
   - Specify ON DELETE CASCADE/SET NULL options
   - Prevent orphan records

3. **Use NOT NULL Appropriately**: Required columns must be NOT NULL
   - Clearly specify nullable vs. non-nullable
   - Providing defaults is recommended

### Prohibited Actions (MUST NOT)

1. **Avoid EAV Pattern Abuse**: Use the Entity-Attribute-Value pattern only in special cases
   - Query complexity increases dramatically
   - Performance degradation

2. **Excessive Denormalization**: Be careful when denormalizing for performance
   - Data consistency issues
   - Risk of update anomalies

3. **No Plaintext Storage of Sensitive Data**: Never store passwords, card numbers, etc. in plaintext
   - Hashing/encryption is mandatory
   - Legal liability issues

### Security Rules

- **Principle of Least Privilege**: Grant only the necessary permissions to application DB accounts
- **SQL Injection Prevention**: Use Prepared Statements / Parameterized Queries
- **Encrypt Sensitive Columns**: Consider encrypting personally identifiable information at rest

## Examples

Demonstrates how to apply the skill through real-world use cases.

### Example 1: Blog Platform Schema

**Situation**: Database design for a Medium-style blog platform

**User Request**:
```
Design a PostgreSQL schema for a blog platform:
- Users can write multiple posts
- Posts can have multiple tags (N:M)
- Users can like and bookmark posts
- Comment feature (with nested replies)
```

**Final Result**:
```sql
-- Users
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    bio TEXT,
    avatar_url VARCHAR(500),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Posts
CREATE TABLE posts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    author_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    content TEXT NOT NULL,
    published_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_posts_author ON posts(author_id);
CREATE INDEX idx_posts_published ON posts(published_at);
CREATE INDEX idx_posts_slug ON posts(slug);

-- Tags
CREATE TABLE tags (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(50) UNIQUE NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL
);

-- Post-Tag relationship (N:M)
CREATE TABLE post_tags (
    post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
    tag_id UUID REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

CREATE INDEX idx_post_tags_post ON post_tags(post_id);
CREATE INDEX idx_post_tags_tag ON post_tags(tag_id);

-- Likes
CREATE TABLE post_likes (
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (user_id, post_id)
);

-- Bookmarks
CREATE TABLE post_bookmarks (
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (user_id, post_id)
);

-- Comments (self-referencing for nested comments)
CREATE TABLE comments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    author_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    parent_comment_id UUID REFERENCES comments(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_comments_post ON comments(post_id);
CREATE INDEX idx_comments_author ON comments(author_id);
CREATE INDEX idx_comments_parent ON comments(parent_comment_id);
```

### Example 2: MongoDB Schema (NoSQL)

**Situation**: MongoDB schema for a real-time chat app

**User Request**:
```
Design a MongoDB schema for a real-time chat app.
Reads are very frequent, and message history needs to be retrieved quickly.
```

**Final Result**:
```javascript
// users collection
{
  _id: ObjectId,
  username: String,  // indexed, unique
  email: String,     // indexed, unique
  avatar_url: String,
  status: String,    // 'online', 'offline', 'away'
  last_seen: Date,
  created_at: Date
}

// conversations collection (denormalized - read-optimized)
{
  _id: ObjectId,
  participants: [    // indexed
    {
      user_id: ObjectId,
      username: String,
      avatar_url: String
    }
  ],
  last_message: {    // denormalized for fast recent-message retrieval
    content: String,
    sender_id: ObjectId,
    sent_at: Date
  },
  unread_counts: {   // unread message count per participant
    "user_id_1": 5,
    "user_id_2": 0
  },
  created_at: Date,
  updated_at: Date
}

// messages collection
{
  _id: ObjectId,
  conversation_id: ObjectId,  // indexed
  sender_id: ObjectId,
  content: String,
  attachments: [
    {
      type: String,  // 'image', 'file', 'video'
      url: String,
      filename: String
    }
  ],
  read_by: [ObjectId],  // array of user IDs who have read the message
  sent_at: Date,        // indexed
  edited_at: Date
}

// Indexes
db.users.createIndex({ username: 1 }, { unique: true });
db.users.createIndex({ email: 1 }, { unique: true });

db.conversations.createIndex({ "participants.user_id": 1 });
db.conversations.createIndex({ updated_at: -1 });

db.messages.createIndex({ conversation_id: 1, sent_at: -1 });
db.messages.createIndex({ sender_id: 1 });
```

**Design Highlights**:
- Denormalization for read optimization (embedding last_message)
- Indexes on frequently accessed fields
- Using array fields (participants, read_by)

## Best practices

### Quality Improvement

1. **Naming Convention Consistency**: Use snake_case for table/column names
   - users, post_tags, created_at
   - Be consistent with plurals/singulars (tables plural, columns singular, etc.)

2. **Consider Soft Delete**: Use logical deletion instead of physical deletion for important data
   - deleted_at TIMESTAMP (NULL = active, NOT NULL = deleted)
   - Allows recovery of accidentally deleted data
   - Audit trail

3. **Timestamps Required**: Include created_at and updated_at in most tables
   - Data tracking and debugging
   - Time-series analysis

### Efficiency Improvements

- **Partial Indexes**: Minimize index size with conditional indexes
  ```sql
  CREATE INDEX idx_posts_published ON posts(published_at) WHERE published_at IS NOT NULL;
  ```
- **Materialized Views**: Cache complex aggregate queries as Materialized Views
- **Partitioning**: Partition large tables by date/range

## Common Issues

### Issue 1: N+1 Query Problem

**Symptom**: Multiple DB calls when a single query would suffice

**Cause**: Individual lookups in a loop without JOINs

**Solution**:
```sql
-- ❌ Bad example: N+1 queries
SELECT * FROM posts;  -- 1 time
-- for each post
SELECT * FROM users WHERE id = ?;  -- N times

-- ✅ Good example: 1 query
SELECT posts.*, users.username, users.avatar_url
FROM posts
JOIN users ON posts.author_id = users.id;
```

### Issue 2: Slow JOINs Due to Unindexed Foreign Keys

**Symptom**: JOIN queries are very slow

**Cause**: Missing index on Foreign Key column

**Solution**:
```sql
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
```

### Issue 3: UUID vs Auto-increment Performance

**Symptom**: Insert performance degradation when using UUID Primary Keys

**Cause**: UUIDs are random, causing index fragmentation

**Solution**:
- PostgreSQL: Use `uuid_generate_v7()` (time-ordered UUID)
- MySQL: Use `UUID_TO_BIN(UUID(), 1)`
- Or consider using Auto-increment BIGINT

## References

### Official Documentation
- [PostgreSQL Documentation](https://www.postgresql.org/docs/)
- [MySQL Documentation](https://dev.mysql.com/doc/)
- [MongoDB Schema Design Best Practices](https://www.mongodb.com/docs/manual/core/data-modeling-introduction/)

### Tools
- [dbdiagram.io](https://dbdiagram.io/) - ERD diagram creation
- [PgModeler](https://pgmodeler.io/) - PostgreSQL modeling tool
- [Prisma](https://www.prisma.io/) - ORM + migrations

### Learning Resources
- [Database Design Course (freecodecamp)](https://www.youtube.com/watch?v=ztHopE5Wnpc)
- [Use The Index, Luke](https://use-the-index-luke.com/) - SQL indexing guide

## Metadata

### Version
- **Current Version**: 1.0.0
- **Last Updated**: 2025-01-01
- **Compatible Platforms**: Claude, ChatGPT, Gemini

### Related Skills
- [api-design](../api-design/SKILL.md): Schema design alongside API design
- [performance-optimization](../../code-quality/performance-optimization/SKILL.md): Query performance optimization

### Tags
`#database` `#schema` `#PostgreSQL` `#MySQL` `#MongoDB` `#SQL` `#NoSQL` `#migration` `#ERD`

```

### ../ui-component-patterns/SKILL.md

```markdown
---
name: ui-component-patterns
description: Build reusable, maintainable UI components following modern design patterns. Use when creating component libraries, implementing design systems, or building scalable frontend architectures. Handles React patterns, composition, prop design, TypeScript, and component best practices.
metadata:
  tags: UI-components, React, design-patterns, composition, TypeScript, reusable
  platforms: Claude, ChatGPT, Gemini
---


# UI Component Patterns


## When to use this skill

- **Building Component Libraries**: Creating reusable UI components
- **Implementing Design Systems**: Applying consistent UI patterns
- **Complex UI**: Components requiring multiple variants (Button, Modal, Dropdown)
- **Refactoring**: Extracting duplicate code into components

## Instructions

### Step 1: Props API Design

Design Props that are easy to use and extensible.

**Principles**:
- Clear names
- Reasonable defaults
- Type definitions with TypeScript
- Optional Props use optional marker (?)

**Example** (Button):
```tsx
interface ButtonProps {
  // Required
  children: React.ReactNode;

  // Optional (with defaults)
  variant?: 'primary' | 'secondary' | 'outline' | 'ghost';
  size?: 'sm' | 'md' | 'lg';
  disabled?: boolean;
  isLoading?: boolean;

  // Event handlers
  onClick?: (event: React.MouseEvent<HTMLButtonElement>) => void;

  // HTML attribute inheritance
  type?: 'button' | 'submit' | 'reset';
  className?: string;
}

function Button({
  children,
  variant = 'primary',
  size = 'md',
  disabled = false,
  isLoading = false,
  onClick,
  type = 'button',
  className = '',
  ...rest
}: ButtonProps) {
  const baseClasses = 'btn';
  const variantClasses = `btn-${variant}`;
  const sizeClasses = `btn-${size}`;
  const classes = `${baseClasses} ${variantClasses} ${sizeClasses} ${className}`;

  return (
    <button
      type={type}
      className={classes}
      disabled={disabled || isLoading}
      onClick={onClick}
      {...rest}
    >
      {isLoading ? <Spinner /> : children}
    </button>
  );
}

// Usage example
<Button variant="primary" size="lg" onClick={() => alert('Clicked!')}>
  Click Me
</Button>
```

### Step 2: Composition Pattern

Combine small components to build complex UI.

**Example** (Card):
```tsx
// Card component (Container)
interface CardProps {
  children: React.ReactNode;
  className?: string;
}

function Card({ children, className = '' }: CardProps) {
  return <div className={`card ${className}`}>{children}</div>;
}

// Card.Header
function CardHeader({ children }: { children: React.ReactNode }) {
  return <div className="card-header">{children}</div>;
}

// Card.Body
function CardBody({ children }: { children: React.ReactNode }) {
  return <div className="card-body">{children}</div>;
}

// Card.Footer
function CardFooter({ children }: { children: React.ReactNode }) {
  return <div className="card-footer">{children}</div>;
}

// Compound Component pattern
Card.Header = CardHeader;
Card.Body = CardBody;
Card.Footer = CardFooter;

export default Card;

// Usage
import Card from './Card';

function ProductCard() {
  return (
    <Card>
      <Card.Header>
        <h3>Product Name</h3>
      </Card.Header>
      <Card.Body>
        <img src="..." alt="Product" />
        <p>Product description here...</p>
      </Card.Body>
      <Card.Footer>
        <button>Add to Cart</button>
      </Card.Footer>
    </Card>
  );
}
```

### Step 3: Render Props / Children as Function

A pattern for flexible customization.

**Example** (Dropdown):
```tsx
interface DropdownProps<T> {
  items: T[];
  renderItem: (item: T, index: number) => React.ReactNode;
  onSelect: (item: T) => void;
  placeholder?: string;
}

function Dropdown<T>({ items, renderItem, onSelect, placeholder }: DropdownProps<T>) {
  const [isOpen, setIsOpen] = useState(false);
  const [selected, setSelected] = useState<T | null>(null);

  const handleSelect = (item: T) => {
    setSelected(item);
    onSelect(item);
    setIsOpen(false);
  };

  return (
    <div className="dropdown">
      <button onClick={() => setIsOpen(!isOpen)}>
        {selected ? renderItem(selected, -1) : placeholder || 'Select...'}
      </button>

      {isOpen && (
        <ul className="dropdown-menu">
          {items.map((item, index) => (
            <li key={index} onClick={() => handleSelect(item)}>
              {renderItem(item, index)}
            </li>
          ))}
        </ul>
      )}
    </div>
  );
}

// Usage
interface User {
  id: string;
  name: string;
  avatar: string;
}

function UserDropdown() {
  const users: User[] = [...];

  return (
    <Dropdown
      items={users}
      placeholder="Select a user"
      renderItem={(user) => (
        <div className="user-item">
          <img src={user.avatar} alt={user.name} />
          <span>{user.name}</span>
        </div>
      )}
      onSelect={(user) => console.log('Selected:', user)}
    />
  );
}
```

### Step 4: Separating Logic with Custom Hooks

Separate UI from business logic.

**Example** (Modal):
```tsx
// hooks/useModal.ts
function useModal(initialOpen = false) {
  const [isOpen, setIsOpen] = useState(initialOpen);

  const open = useCallback(() => setIsOpen(true), []);
  const close = useCallback(() => setIsOpen(false), []);
  const toggle = useCallback(() => setIsOpen(prev => !prev), []);

  return { isOpen, open, close, toggle };
}

// components/Modal.tsx
interface ModalProps {
  isOpen: boolean;
  onClose: () => void;
  title: string;
  children: React.ReactNode;
}

function Modal({ isOpen, onClose, title, children }: ModalProps) {
  if (!isOpen) return null;

  return (
    <div className="modal-overlay" onClick={onClose}>
      <div className="modal-content" onClick={(e) => e.stopPropagation()}>
        <div className="modal-header">
          <h2>{title}</h2>
          <button onClick={onClose} aria-label="Close">×</button>
        </div>
        <div className="modal-body">{children}</div>
      </div>
    </div>
  );
}

// Usage
function App() {
  const { isOpen, open, close } = useModal();

  return (
    <>
      <button onClick={open}>Open Modal</button>
      <Modal isOpen={isOpen} onClose={close} title="My Modal">
        <p>Modal content here...</p>
      </Modal>
    </>
  );
}
```

### Step 5: Performance Optimization

Prevent unnecessary re-renders.

**React.memo**:
```tsx
// ❌ Bad: child re-renders every time parent re-renders
function ExpensiveComponent({ data }) {
  console.log('Rendering...');
  return <div>{/* Complex UI */}</div>;
}

// ✅ Good: re-renders only when props change
const ExpensiveComponent = React.memo(({ data }) => {
  console.log('Rendering...');
  return <div>{/* Complex UI */}</div>;
});
```

**useMemo & useCallback**:
```tsx
function ProductList({ products, category }: { products: Product[]; category: string }) {
  // ✅ Memoize filtered results
  const filteredProducts = useMemo(() => {
    return products.filter(p => p.category === category);
  }, [products, category]);

  // ✅ Memoize callback
  const handleAddToCart = useCallback((productId: string) => {
    // Add to cart
    console.log('Adding:', productId);
  }, []);

  return (
    <div>
      {filteredProducts.map(product => (
        <ProductCard
          key={product.id}
          product={product}
          onAddToCart={handleAddToCart}
        />
      ))}
    </div>
  );
}

const ProductCard = React.memo(({ product, onAddToCart }) => {
  return (
    <div>
      <h3>{product.name}</h3>
      <button onClick={() => onAddToCart(product.id)}>Add to Cart</button>
    </div>
  );
});
```

## Output format

### Component File Structure

```
components/
├── Button/
│   ├── Button.tsx           # Main component
│   ├── Button.test.tsx      # Tests
│   ├── Button.stories.tsx   # Storybook
│   ├── Button.module.css    # Styles
│   └── index.ts             # Export
├── Card/
│   ├── Card.tsx
│   ├── CardHeader.tsx
│   ├── CardBody.tsx
│   ├── CardFooter.tsx
│   └── index.ts
└── Modal/
    ├── Modal.tsx
    ├── useModal.ts          # Custom hook
    └── index.ts
```

### Component Template

```tsx
import React from 'react';

export interface ComponentProps {
  // Props definition
  children: React.ReactNode;
  className?: string;
}

/**
 * Component description
 *
 * @example
 * ```tsx
 * <Component>Hello</Component>
 * ```
 */
export const Component = React.forwardRef<HTMLDivElement, ComponentProps>(
  ({ children, className = '', ...rest }, ref) => {
    return (
      <div ref={ref} className={`component ${className}`} {...rest}>
        {children}
      </div>
    );
  }
);

Component.displayName = 'Component';

export default Component;
```

## Constraints

### Required Rules (MUST)

1. **Single Responsibility Principle**: One component has one role only
   - Button handles buttons only, Form handles forms only

2. **Props Type Definition**: TypeScript interface required
   - Enables auto-completion
   - Type safety

3. **Accessibility**: aria-*, role, tabindex, etc.

### Prohibited Rules (MUST NOT)

1. **Excessive props drilling**: Prohibited when 5+ levels deep
   - Use Context or Composition

2. **No Business Logic**: Prohibit API calls and complex calculations in UI components
   - Separate into custom hooks

3. **Inline objects/functions**: Performance degradation
   ```tsx
   // ❌ Bad example
   <Component style={{ color: 'red' }} onClick={() => handleClick()} />

   // ✅ Good example
   const style = { color: 'red' };
   const handleClick = useCallback(() => {...}, []);
   <Component style={style} onClick={handleClick} />
   ```

## Examples

### Example 1: Accordion (Compound Component)

```tsx
import React, { createContext, useContext, useState } from 'react';

// Share state with Context
const AccordionContext = createContext<{
  activeIndex: number | null;
  setActiveIndex: (index: number | null) => void;
} | null>(null);

function Accordion({ children }: { children: React.ReactNode }) {
  const [activeIndex, setActiveIndex] = useState<number | null>(null);

  return (
    <AccordionContext.Provider value={{ activeIndex, setActiveIndex }}>
      <div className="accordion">{children}</div>
    </AccordionContext.Provider>
  );
}

function AccordionItem({ index, title, children }: {
  index: number;
  title: string;
  children: React.ReactNode;
}) {
  const context = useContext(AccordionContext);
  if (!context) throw new Error('AccordionItem must be used within Accordion');

  const { activeIndex, setActiveIndex } = context;
  const isActive = activeIndex === index;

  return (
    <div className="accordion-item">
      <button
        className="accordion-header"
        onClick={() => setActiveIndex(isActive ? null : index)}
        aria-expanded={isActive}
      >
        {title}
      </button>
      {isActive && <div className="accordion-body">{children}</div>}
    </div>
  );
}

Accordion.Item = AccordionItem;
export default Accordion;

// Usage
<Accordion>
  <Accordion.Item index={0} title="Section 1">
    Content for section 1
  </Accordion.Item>
  <Accordion.Item index={1} title="Section 2">
    Content for section 2
  </Accordion.Item>
</Accordion>
```

### Example 2: Polymorphic Component (as prop)

```tsx
type PolymorphicComponentProps<C extends React.ElementType> = {
  as?: C;
  children: React.ReactNode;
} & React.ComponentPropsWithoutRef<C>;

function Text<C extends React.ElementType = 'span'>({
  as,
  children,
  ...rest
}: PolymorphicComponentProps<C>) {
  const Component = as || 'span';
  return <Component {...rest}>{children}</Component>;
}

// Usage
<Text>Default span</Text>
<Text as="h1">Heading 1</Text>
<Text as="p" style={{ color: 'blue' }}>Paragraph</Text>
<Text as={Link} href="/about">Link</Text>
```

## Best practices

1. **Composition over Props**: Leverage children instead of many props
2. **Controlled vs Uncontrolled**: Choose based on situation
3. **Default Props**: Provide reasonable defaults
4. **Storybook**: Component documentation and development

## References

- [React Patterns](https://reactpatterns.com/)
- [Compound Components](https://kentcdodds.com/blog/compound-components-with-react-hooks)
- [Radix UI](https://www.radix-ui.com/) - Accessible components
- [Chakra UI](https://chakra-ui.com/) - Component library
- [shadcn/ui](https://ui.shadcn.com/) - Copy-paste components

## Metadata

### Version
- **Current Version**: 1.0.0
- **Last Updated**: 2025-01-01
- **Compatible Platforms**: Claude, ChatGPT, Gemini

### Related Skills
- [web-accessibility](../web-accessibility/SKILL.md): Accessible components
- [state-management](../state-management/SKILL.md): Component state management

### Tags
`#UI-components` `#React` `#design-patterns` `#composition` `#TypeScript` `#frontend`

```