Backend Design

Backend Design

Workflow

Follow this systematic design process:

1. Requirements Analysis

• Gather functional requirements (features, operations)
• Define non-functional requirements (performance, scalability, availability)
• Identify constraints (budget, timeline, technology, compliance)

2. Architecture Selection

• Choose architecture pattern (monolith, microservices, serverless)
• Select technology stack based on requirements
• Define service boundaries and responsibilities

3. API Design

• Design RESTful endpoints with proper resource modeling
• Define request/response schemas and contracts
• Plan versioning strategy and documentation
• See api-design-guide.md for REST/GraphQL/gRPC patterns

4. Database Design

• Model entities and relationships
• Design schema with normalization
• Plan indexing and partitioning strategies
• See database-design.md for relational and NoSQL patterns

5. Security Design

• Design authentication flow (OAuth 2.0, JWT)
• Plan authorization model (RBAC, ABAC)
• Define data encryption and protection strategy

6. Scalability & Performance

• Design caching strategy (Redis, CDN)
• Plan load balancing and auto-scaling
• Define asynchronous processing with message queues

7. Documentation

• Create API specifications (OpenAPI/Swagger)
• Document architecture decisions with Mermaid diagrams
• Provide implementation guidelines and roadmap

Output Structure

Present your backend design with these sections:

1. System Overview - High-level architecture, components, technology stack 2. API Specification - Endpoints, schemas, authentication, OpenAPI docs 3. Database Design - ERD, schema, indexes, migration plan 4. Architecture Decisions - Service decomposition, communication patterns, consistency model 5. Security Implementation - Authentication/authorization flows, encryption 6. Scalability Plan - Load balancing, caching, database scaling, auto-scaling 7. Deployment Architecture - Containers, infrastructure, CI/CD, monitoring 8. Implementation Roadmap - Phases, milestones, dependencies, risks

Core Principles

• API-first approach - Design and document APIs before implementation
• Security by design - Build authentication, authorization, and encryption from the start
• Design for scalability - Plan for growth with caching, load balancing, and horizontal scaling
• Plan for failure - Include error handling, retries, circuit breakers, and graceful degradation
• Document thoroughly - Create clear API specs, Mermaid architecture diagrams, and implementation guides

Reference Files

Load additional resources based on specific needs:

• Detailed Design Process: See backend-design-process.md for comprehensive step-by-step workflow with examples for API design, database modeling, authentication flows, and microservices patterns

• API Design Guide: See api-design-guide.md when designing RESTful APIs, GraphQL schemas, or gRPC services - includes resource modeling, status codes, versioning strategies, and documentation

• Database Design: See database-design.md for detailed guidance on relational and NoSQL database design, normalization, indexing, partitioning, and replication strategies

• Best Practices: See best-practices.md for API design, database optimization, security hardening, performance tuning, and reliability patterns

• Common Patterns: See common-patterns.md for code examples of repository pattern, service layer, dependency injection, and other architectural patterns

• Example Projects: See examples.md for complete architecture examples including e-commerce systems, real-time chat applications, and microservices implementations

API Design Best Practices Reference

Comprehensive guide for designing robust, scalable, and maintainable APIs.

REST API Design Principles

1. Resource Naming

Use Nouns, Not Verbs

✅ Good:
GET    /users
POST   /users
GET    /users/123
PUT    /users/123
DELETE /users/123

❌ Bad:
GET    /getUsers
POST   /createUser
GET    /getUserById/123
POST   /updateUser/123
DELETE /removeUser/123

Use Plural Nouns

✅ Good: /users, /products, /orders
❌ Bad: /user, /product, /order

Use Hyphens for Multi-Word Resources

✅ Good: /order-items, /user-preferences
❌ Bad: /orderItems, /order_items, /OrderItems

Avoid Deep Nesting

✅ Good:
GET /users/123/posts
GET /posts?user_id=123

❌ Bad:
GET /users/123/posts/456/comments/789/likes

---

2. HTTP Methods

Standard CRUD Operations

POST   /resources         - Create new resource
GET    /resources         - List resources
GET    /resources/{id}    - Get single resource
PUT    /resources/{id}    - Replace resource (full update)
PATCH  /resources/{id}    - Update resource (partial update)
DELETE /resources/{id}    - Delete resource

Idempotency

• GET, PUT, DELETE are idempotent (same result on multiple calls)
• POST is not idempotent (creates new resource each time)
• PATCH may or may not be idempotent

Safe Methods

• GET, HEAD, OPTIONS are safe (read-only, no side effects)

---

3. HTTP Status Codes

Success Codes (2xx)

200 OK              - Successful GET, PUT, PATCH, or DELETE
201 Created         - Successful POST that creates a resource
202 Accepted        - Request accepted for async processing
204 No Content      - Successful request with no response body (DELETE)
206 Partial Content - Partial GET (range requests)

Redirection (3xx)

301 Moved Permanently   - Resource permanently moved
302 Found              - Temporary redirect
304 Not Modified       - Cached version still valid

Client Errors (4xx)

400 Bad Request              - Invalid request syntax
401 Unauthorized             - Authentication required/failed
403 Forbidden                - Authenticated but not authorized
404 Not Found                - Resource doesn't exist
405 Method Not Allowed       - HTTP method not supported
406 Not Acceptable           - Can't produce requested format
409 Conflict                 - Resource conflict (duplicate, version)
410 Gone                     - Resource permanently deleted
415 Unsupported Media Type   - Invalid Content-Type
422 Unprocessable Entity     - Validation errors
429 Too Many Requests        - Rate limit exceeded

Server Errors (5xx)

500 Internal Server Error - Generic server error
502 Bad Gateway          - Invalid response from upstream
503 Service Unavailable  - Server temporarily unavailable
504 Gateway Timeout      - Upstream server timeout

---

4. Request/Response Format

Consistent JSON Structure

// Single resource
{
  "id": "usr_123",
  "email": "user@example.com",
  "name": "John Doe",
  "created_at": "2026-01-14T10:00:00Z"
}

// Collection
{
  "data": [
    { "id": "usr_123", "name": "John" },
    { "id": "usr_456", "name": "Jane" }
  ],
  "pagination": {
    "page": 1,
    "limit": 20,
    "total": 150,
    "pages": 8
  }
}

// Error response
{
  "error": {
    "code": "VALIDATION_ERROR",
    "message": "Validation failed",
    "details": [
      {
        "field": "email",
        "message": "Invalid email format",
        "code": "INVALID_FORMAT"
      }
    ]
  }
}

Content Negotiation

Request:
Accept: application/json
Content-Type: application/json

Response:
Content-Type: application/json; charset=utf-8

---

5. Versioning Strategies

URL Path Versioning (Recommended)

/api/v1/users
/api/v2/users

✅ Pros: Clear, easy to route, cacheable ❌ Cons: Multiple base URLs

Header Versioning

GET /api/users
Accept: application/vnd.myapi.v1+json

✅ Pros: Clean URLs, RESTful ❌ Cons: Less visible, harder to test

Query Parameter Versioning

/api/users?version=1

❌ Not recommended: Caching issues, unclear

When to Version

• Breaking changes (removed fields, changed data types)
• Changed business logic
• Security updates requiring client changes

When NOT to Version

• Adding new optional fields
• Adding new endpoints
• Bug fixes
• Performance improvements

---

6. Filtering, Sorting, Pagination

Filtering

GET /users?status=active
GET /users?role=admin&status=active
GET /users?created_after=2026-01-01
GET /products?min_price=10&max_price=100

Sorting

GET /users?sort=created_at        // Ascending (default)
GET /users?sort=-created_at       // Descending
GET /users?sort=name,-created_at  // Multiple fields

Pagination - Offset/Limit

GET /users?page=2&limit=20
GET /users?offset=40&limit=20

Response:
{
  "data": [...],
  "pagination": {
    "page": 2,
    "limit": 20,
    "total": 150,
    "pages": 8
  },
  "links": {
    "first": "/users?page=1&limit=20",
    "prev": "/users?page=1&limit=20",
    "self": "/users?page=2&limit=20",
    "next": "/users?page=3&limit=20",
    "last": "/users?page=8&limit=20"
  }
}

Pagination - Cursor-Based

GET /users?cursor=eyJpZCI6MTIzfQ&limit=20

Response:
{
  "data": [...],
  "pagination": {
    "next_cursor": "eyJpZCI6MTQzfQ",
    "has_more": true
  }
}

✅ Better for large datasets, consistent results

Sparse Fieldsets

GET /users?fields=id,email,name

Search

GET /products?q=laptop
GET /users?search=john

---

7. HATEOAS (Hypermedia)

Include Related Links

{
  "id": "usr_123",
  "name": "John Doe",
  "email": "john@example.com",
  "_links": {
    "self": { "href": "/users/usr_123" },
    "posts": { "href": "/users/usr_123/posts" },
    "avatar": { "href": "/users/usr_123/avatar" }
  }
}

---

8. Rate Limiting

Response Headers

X-RateLimit-Limit: 1000
X-RateLimit-Remaining: 950
X-RateLimit-Reset: 1705228800
Retry-After: 60

429 Response

{
  "error": {
    "code": "RATE_LIMIT_EXCEEDED",
    "message": "Too many requests. Please try again in 60 seconds.",
    "retry_after": 60
  }
}

Rate Limiting Strategies

• Fixed window: 1000 requests per hour
• Sliding window: More accurate, complex
• Token bucket: Burst handling
• Leaky bucket: Smooth rate

---

9. Authentication & Security

Bearer Token (JWT)

Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

API Key

X-API-Key: your-api-key-here

Basic Auth (Avoid for Production)

Authorization: Basic base64(username:password)

Security Headers

Strict-Transport-Security: max-age=31536000
X-Content-Type-Options: nosniff
X-Frame-Options: DENY
Content-Security-Policy: default-src 'self'

---

10. Caching

Cache-Control Header

Cache-Control: public, max-age=3600          // 1 hour
Cache-Control: private, max-age=300          // 5 minutes, user-specific
Cache-Control: no-cache                      // Validate before use
Cache-Control: no-store                      // Don't cache

ETag (Entity Tag)

Response:
ETag: "33a64df551425fcc55e4d42a148795d9f25f89d4"

Request:
If-None-Match: "33a64df551425fcc55e4d42a148795d9f25f89d4"

Response:
304 Not Modified (if unchanged)

Last-Modified

Response:
Last-Modified: Wed, 14 Jan 2026 10:00:00 GMT

Request:
If-Modified-Since: Wed, 14 Jan 2026 10:00:00 GMT

Response:
304 Not Modified (if unchanged)

---

11. Bulk Operations

Batch Create

POST /users/batch
[
  { "email": "user1@example.com", "name": "User 1" },
  { "email": "user2@example.com", "name": "User 2" }
]

Response: 207 Multi-Status
{
  "results": [
    { "status": 201, "id": "usr_123" },
    { "status": 409, "error": "Email already exists" }
  ]
}

Batch Update

PATCH /users/batch
[
  { "id": "usr_123", "name": "Updated Name" },
  { "id": "usr_456", "status": "active" }
]

Batch Delete

DELETE /users/batch
{ "ids": ["usr_123", "usr_456", "usr_789"] }

---

12. Async Operations

Accepted for Processing

POST /reports/generate
{
  "type": "sales",
  "date_range": "2026-01"
}

Response: 202 Accepted
{
  "job_id": "job_123",
  "status": "pending",
  "status_url": "/jobs/job_123"
}

Check Status

GET /jobs/job_123

Response: 200 OK
{
  "id": "job_123",
  "status": "completed",
  "result_url": "/reports/rep_123"
}

---

13. Error Handling

Consistent Error Format

{
  "error": {
    "code": "ERROR_CODE",
    "message": "Human-readable error message",
    "details": [...],
    "request_id": "req_123",
    "timestamp": "2026-01-14T10:00:00Z"
  }
}

Validation Errors

{
  "error": {
    "code": "VALIDATION_ERROR",
    "message": "Validation failed",
    "details": [
      {
        "field": "email",
        "message": "Invalid email format",
        "code": "INVALID_FORMAT"
      },
      {
        "field": "password",
        "message": "Password must be at least 8 characters",
        "code": "TOO_SHORT"
      }
    ]
  }
}

---

14. Documentation (OpenAPI 3.0)

openapi: 3.0.0
info:
  title: User API
  version: 1.0.0
  description: API for managing users

paths:
  /users:
    get:
      summary: List users
      parameters:
        - name: page
          in: query
          schema:
            type: integer
            default: 1
        - name: limit
          in: query
          schema:
            type: integer
            default: 20
      responses:
        '200':
          description: Successful response
          content:
            application/json:
              schema:
                type: object
                properties:
                  data:
                    type: array
                    items:
                      $ref: '#/components/schemas/User'
    
    post:
      summary: Create user
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/UserInput'
      responses:
        '201':
          description: User created
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/User'

components:
  schemas:
    User:
      type: object
      properties:
        id:
          type: string
        email:
          type: string
          format: email
        name:
          type: string
        created_at:
          type: string
          format: date-time

---

GraphQL API Design

Schema Definition

type User {
  id: ID!
  email: String!
  name: String!
  posts: [Post!]!
  createdAt: DateTime!
}

type Post {
  id: ID!
  title: String!
  content: String!
  author: User!
  comments: [Comment!]!
  publishedAt: DateTime
}

type Query {
  user(id: ID!): User
  users(limit: Int, offset: Int): [User!]!
  post(id: ID!): Post
  posts(filter: PostFilter): [Post!]!
}

type Mutation {
  createUser(input: CreateUserInput!): User!
  updateUser(id: ID!, input: UpdateUserInput!): User!
  deleteUser(id: ID!): Boolean!
}

input CreateUserInput {
  email: String!
  name: String!
  password: String!
}

input PostFilter {
  status: PostStatus
  authorId: ID
}

enum PostStatus {
  DRAFT
  PUBLISHED
  ARCHIVED
}

Queries

# Get user with nested data
query {
  user(id: "usr_123") {
    id
    name
    email
    posts(limit: 5) {
      id
      title
      comments {
        id
        content
      }
    }
  }
}

# Pagination
query {
  users(limit: 20, offset: 40) {
    id
    name
  }
}

Mutations

mutation {
  createUser(input: {
    email: "user@example.com"
    name: "John Doe"
    password: "password123"
  }) {
    id
    email
    name
  }
}

---

gRPC API Design

Protocol Buffers Definition

syntax = "proto3";

package user.v1;

service UserService {
  rpc GetUser(GetUserRequest) returns (User);
  rpc ListUsers(ListUsersRequest) returns (ListUsersResponse);
  rpc CreateUser(CreateUserRequest) returns (User);
  rpc UpdateUser(UpdateUserRequest) returns (User);
  rpc DeleteUser(DeleteUserRequest) returns (google.protobuf.Empty);
}

message User {
  string id = 1;
  string email = 2;
  string name = 3;
  google.protobuf.Timestamp created_at = 4;
}

message GetUserRequest {
  string id = 1;
}

message ListUsersRequest {
  int32 page = 1;
  int32 limit = 2;
}

message ListUsersResponse {
  repeated User users = 1;
  int32 total = 2;
}

message CreateUserRequest {
  string email = 1;
  string name = 2;
  string password = 3;
}

---

API Design Checklist

• [ ] Consistent naming conventions (nouns, plural, hyphens)
• [ ] Proper HTTP methods and status codes
• [ ] Versioning strategy implemented
• [ ] Pagination for collections
• [ ] Filtering and sorting support
• [ ] Error handling with consistent format
• [ ] Authentication and authorization
• [ ] Rate limiting
• [ ] Caching headers
• [ ] CORS configuration
• [ ] OpenAPI/Swagger documentation
• [ ] Request/response validation
• [ ] Idempotency for non-GET requests
• [ ] Bulk operation support (if needed)
• [ ] Async operation handling (if needed)
• [ ] Monitoring and logging
• [ ] Security headers
• [ ] HTTPS only

This reference provides comprehensive guidelines for building robust, scalable APIs.

Backend Design Process

Follow this systematic approach when designing backend systems:

Phase 1: Requirements Analysis

1. Functional Requirements

• Define API endpoints and operations
• Identify business entities and relationships
• Map user workflows and data flows
• Define integration points with external systems
• Specify background jobs and scheduled tasks

2. Non-Functional Requirements

• Performance: Response time (p50, p95, p99), throughput (req/sec)
• Scalability: Expected load (users, requests, data volume)
• Availability: Uptime SLA (99.9%, 99.99%)
• Reliability: Error rate targets, data consistency requirements
• Security: Authentication, authorization, data protection
• Compliance: GDPR, HIPAA, PCI-DSS requirements

3. Constraints & Assumptions

• Technology constraints (language, frameworks, cloud provider)
• Team expertise and size
• Budget and timeline
• Existing systems and dependencies
• Data residency and regulatory requirements

Phase 2: API Design

1. RESTful API Design

Resource Modeling

Users:
  GET    /api/v1/users           - List users
  POST   /api/v1/users           - Create user
  GET    /api/v1/users/{id}      - Get user by ID
  PUT    /api/v1/users/{id}      - Update user
  PATCH  /api/v1/users/{id}      - Partial update
  DELETE /api/v1/users/{id}      - Delete user

Nested Resources:
  GET    /api/v1/users/{id}/posts        - Get user's posts
  POST   /api/v1/users/{id}/posts        - Create post for user
  GET    /api/v1/posts/{id}/comments     - Get post comments

HTTP Status Codes

• 200 OK: Successful GET, PUT, PATCH
• 201 Created: Successful POST
• 204 No Content: Successful DELETE
• 400 Bad Request: Invalid input
• 401 Unauthorized: Missing/invalid authentication
• 403 Forbidden: Authenticated but not authorized
• 404 Not Found: Resource doesn't exist
• 409 Conflict: Resource conflict (duplicate)
• 422 Unprocessable Entity: Validation errors
• 429 Too Many Requests: Rate limit exceeded
• 500 Internal Server Error: Server error
• 503 Service Unavailable: Service down

Request/Response Format

// POST /api/v1/users
{
  "email": "user@example.com",
  "name": "John Doe",
  "role": "admin"
}

// Response: 201 Created
{
  "id": "usr_1234567890",
  "email": "user@example.com",
  "name": "John Doe",
  "role": "admin",
  "created_at": "2026-01-14T10:30:00Z",
  "updated_at": "2026-01-14T10:30:00Z"
}

// Error Response: 422 Unprocessable Entity
{
  "error": {
    "code": "VALIDATION_ERROR",
    "message": "Validation failed",
    "details": [
      {
        "field": "email",
        "message": "Invalid email format"
      }
    ]
  }
}

1. API Design Best Practices

• Use nouns for resources, not verbs
• Use plural nouns (/users, not /user)
• Use hyphens for multi-word resources (/order-items)
• Version your API (/api/v1/)
• Support pagination for collections
• Allow filtering, sorting, and searching
• Use consistent naming conventions
• Document with OpenAPI/Swagger
• Implement HATEOAS (optional)

1. Pagination

// Request: GET /api/v1/users?page=2&limit=20

// Response
{
  "data": [...],
  "pagination": {
    "page": 2,
    "limit": 20,
    "total": 150,
    "pages": 8
  },
  "links": {
    "self": "/api/v1/users?page=2&limit=20",
    "first": "/api/v1/users?page=1&limit=20",
    "prev": "/api/v1/users?page=1&limit=20",
    "next": "/api/v1/users?page=3&limit=20",
    "last": "/api/v1/users?page=8&limit=20"
  }
}

1. Filtering & Sorting

GET /api/v1/users?status=active&role=admin
GET /api/v1/users?sort=created_at:desc
GET /api/v1/users?search=john
GET /api/v1/users?fields=id,email,name  (sparse fieldsets)

Phase 3: Database Design

1. Relational Database Design

Normalization

• 1NF: Eliminate repeating groups, atomic values
• 2NF: Remove partial dependencies
• 3NF: Remove transitive dependencies
• BCNF: Every determinant is a candidate key

Example Schema

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  role VARCHAR(50) NOT NULL DEFAULT 'user',
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMP,
  
  INDEX idx_email (email),
  INDEX idx_role (role),
  INDEX idx_created_at (created_at)
);

CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title VARCHAR(500) NOT NULL,
  content TEXT NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'draft',
  published_at TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  
  INDEX idx_user_id (user_id),
  INDEX idx_status (status),
  INDEX idx_published_at (published_at),
  FULLTEXT INDEX idx_fulltext (title, content)
);

CREATE TABLE comments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  
  INDEX idx_post_id (post_id),
  INDEX idx_user_id (user_id),
  INDEX idx_created_at (created_at)
);

Indexing Strategy

• Primary key indexes (automatic)
• Foreign key indexes (for joins)
• Columns used in WHERE clauses
• Columns used in ORDER BY
• Composite indexes for multiple columns
• Partial indexes for subset of rows
• Full-text indexes for search

1. NoSQL Database Design

Document Database (MongoDB)

// User document
{
  _id: ObjectId("..."),
  email: "user@example.com",
  name: "John Doe",
  profile: {
    bio: "Developer",
    avatar_url: "https://...",
    social: {
      twitter: "@johndoe",
      github: "johndoe"
    }
  },
  preferences: {
    theme: "dark",
    notifications: true
  },
  created_at: ISODate("2026-01-14T10:30:00Z"),
  updated_at: ISODate("2026-01-14T10:30:00Z")
}

// Embedding vs. Referencing
// Embed: One-to-few, data accessed together
// Reference: One-to-many, many-to-many, frequently updated

Key-Value Store (Redis)

// Session storage
SET session:usr_123 '{"user_id":"usr_123","role":"admin"}' EX 3600

// Caching
SET cache:user:usr_123 '{"name":"John","email":"..."}' EX 300

// Rate limiting
INCR ratelimit:api:usr_123:2026-01-14-10
EXPIRE ratelimit:api:usr_123:2026-01-14-10 3600

Phase 4: Authentication & Authorization

1. Authentication Strategies

JWT-Based Authentication

// Login endpoint
POST /api/v1/auth/login
{
  "email": "user@example.com",
  "password": "password123"
}

// Response
{
  "access_token": "eyJhbGciOiJIUzI1NiIs...",
  "refresh_token": "eyJhbGciOiJIUzI1NiIs...",
  "expires_in": 3600,
  "token_type": "Bearer"
}

// JWT Payload
{
  "sub": "usr_1234567890",
  "email": "user@example.com",
  "role": "admin",
  "iat": 1705228200,
  "exp": 1705231800
}

// Using token
GET /api/v1/users
Authorization: Bearer eyJhbGciOiJIUzI1NiIs...

OAuth 2.0 Flows

• Authorization Code: Web applications
• PKCE: Mobile and SPA applications
• Client Credentials: Service-to-service
• Refresh Token: Long-lived sessions

1. Authorization Patterns

Role-Based Access Control (RBAC)

// Roles and permissions
const roles = {
  admin: ['users:read', 'users:write', 'posts:write', 'posts:delete'],
  editor: ['posts:write', 'posts:read'],
  user: ['posts:read', 'comments:write']
};

// Middleware
function authorize(permission) {
  return (req, res, next) => {
    const userRole = req.user.role;
    const permissions = roles[userRole] || [];
    
    if (permissions.includes(permission)) {
      next();
    } else {
      res.status(403).json({ error: 'Forbidden' });
    }
  };
}

// Usage
app.delete('/api/v1/posts/:id', 
  authenticate,
  authorize('posts:delete'),
  deletePost
);

Attribute-Based Access Control (ABAC)

// More granular control
function canEditPost(user, post) {
  return user.role === 'admin' || 
         (user.role === 'editor' && post.author_id === user.id);
}

Phase 5: Microservices Architecture

1. Service Decomposition

Domain-Driven Design Approach

User Service:
  - User registration and authentication
  - User profile management
  - User preferences

Product Service:
  - Product catalog
  - Product search
  - Inventory management

Order Service:
  - Order creation and management
  - Order history
  - Order status tracking

Payment Service:
  - Payment processing
  - Refunds
  - Payment methods

Notification Service:
  - Email notifications
  - SMS notifications
  - Push notifications

1. Inter-Service Communication

Synchronous (REST)

// Order Service calls Product Service
const response = await fetch('http://product-service/api/v1/products/123', {
  method: 'GET',
  headers: {
    'Authorization': `Bearer ${serviceToken}`,
    'X-Request-ID': requestId
  }
});

const product = await response.json();

Asynchronous (Message Queue)

// Order Service publishes event
await messageQueue.publish('order.created', {
  order_id: 'ord_123',
  user_id: 'usr_456',
  total: 99.99,
  items: [...]
});

// Notification Service subscribes
messageQueue.subscribe('order.created', async (event) => {
  await sendOrderConfirmationEmail(event.user_id, event.order_id);
});

// Inventory Service subscribes
messageQueue.subscribe('order.created', async (event) => {
  await decrementInventory(event.items);
});

1. Service Discovery

// Service registry (Consul, Eureka)
const productServiceUrl = await serviceRegistry.discover('product-service');

// With load balancing
const instance = await serviceRegistry.getHealthyInstance('product-service');

1. API Gateway Pattern

Client → API Gateway → Services

API Gateway responsibilities:
- Request routing
- Authentication/Authorization
- Rate limiting
- Request/response transformation
- Caching
- Monitoring and logging

Phase 6: Caching Strategy

1. Cache Levels

Application Cache (In-Memory)

// Simple in-memory cache
const cache = new Map();

function getUser(userId) {
  const cacheKey = `user:${userId}`;
  
  if (cache.has(cacheKey)) {
    return cache.get(cacheKey);
  }
  
  const user = await db.users.findById(userId);
  cache.set(cacheKey, user);
  
  return user;
}

Distributed Cache (Redis)

// Redis caching
async function getUser(userId) {
  const cacheKey = `user:${userId}`;
  
  // Try cache first
  let user = await redis.get(cacheKey);
  
  if (user) {
    return JSON.parse(user);
  }
  
  // Cache miss - fetch from database
  user = await db.users.findById(userId);
  
  // Store in cache (5 minutes TTL)
  await redis.setex(cacheKey, 300, JSON.stringify(user));
  
  return user;
}

// Cache invalidation
async function updateUser(userId, data) {
  await db.users.update(userId, data);
  
  // Invalidate cache
  await redis.del(`user:${userId}`);
}

HTTP Cache (CDN)

// Set cache headers
app.get('/api/v1/posts/:id', (req, res) => {
  const post = getPost(req.params.id);
  
  res.set({
    'Cache-Control': 'public, max-age=300',  // 5 minutes
    'ETag': generateETag(post),
    'Last-Modified': post.updated_at
  });
  
  res.json(post);
});

1. Cache Patterns

• Cache-Aside: Application manages cache
• Read-Through: Cache fetches from database on miss
• Write-Through: Write to cache and database simultaneously
• Write-Behind: Write to cache first, database asynchronously
• Refresh-Ahead: Automatically refresh before expiration

1. Cache Invalidation Strategies

• TTL (Time-To-Live): Automatic expiration
• Explicit Invalidation: Delete on update
• Event-Based: Invalidate on specific events
• Cache Tags: Group-based invalidation

Phase 7: Asynchronous Processing

1. Message Queue Patterns

Job Queue (Bull, BullMQ)

// Producer
await queue.add('send-email', {
  to: 'user@example.com',
  subject: 'Welcome',
  template: 'welcome'
}, {
  attempts: 3,
  backoff: {
    type: 'exponential',
    delay: 5000
  }
});

// Consumer
queue.process('send-email', async (job) => {
  const { to, subject, template } = job.data;
  await emailService.send(to, subject, template);
});

Event Streaming (Kafka)

// Producer
await producer.send({
  topic: 'user-events',
  messages: [{
    key: userId,
    value: JSON.stringify({
      type: 'USER_REGISTERED',
      user_id: userId,
      email: email,
      timestamp: Date.now()
    })
  }]
});

// Consumer
await consumer.run({
  eachMessage: async ({ topic, partition, message }) => {
    const event = JSON.parse(message.value);
    
    if (event.type === 'USER_REGISTERED') {
      await handleUserRegistration(event);
    }
  }
});

1. Background Jobs

• Image processing (resize, optimization)
• Email sending
• Report generation
• Data import/export
• Scheduled cleanup tasks
• Analytics aggregation

Phase 8: Security Implementation

1. Input Validation

// Using validation library (Joi, Yup)
const schema = Joi.object({
  email: Joi.string().email().required(),
  password: Joi.string().min(8).required(),
  age: Joi.number().integer().min(18).max(120)
});

const { error, value } = schema.validate(req.body);
if (error) {
  return res.status(422).json({ error: error.details });
}

1. SQL Injection Prevention

// Bad: String concatenation
const query = `SELECT * FROM users WHERE email = '${email}'`;

// Good: Parameterized queries
const query = 'SELECT * FROM users WHERE email = ?';
const [users] = await db.execute(query, [email]);

// Good: ORM
const user = await User.findOne({ where: { email } });

1. Password Security

const bcrypt = require('bcrypt');

// Hashing (on registration)
const hashedPassword = await bcrypt.hash(password, 10);

// Verification (on login)
const isValid = await bcrypt.compare(password, user.password_hash);

1. Rate Limiting

const rateLimit = require('express-rate-limit');

const limiter = rateLimit({
  windowMs: 15 * 60 * 1000, // 15 minutes
  max: 100, // 100 requests per window
  message: 'Too many requests, please try again later'
});

app.use('/api/', limiter);

1. CORS Configuration

const cors = require('cors');

app.use(cors({
  origin: ['https://example.com', 'https://app.example.com'],
  methods: ['GET', 'POST', 'PUT', 'DELETE'],
  allowedHeaders: ['Content-Type', 'Authorization'],
  credentials: true,
  maxAge: 86400
}));

Phase 9: Observability

1. Structured Logging

const logger = require('pino')();

logger.info({
  event: 'user_created',
  user_id: 'usr_123',
  email: 'user@example.com',
  ip_address: req.ip,
  timestamp: new Date().toISOString()
}, 'User created successfully');

// Correlation ID for request tracking
app.use((req, res, next) => {
  req.id = req.headers['x-request-id'] || uuid();
  logger.child({ request_id: req.id });
  next();
});

1. Metrics

// Prometheus metrics
const promClient = require('prom-client');

const httpRequestDuration = new promClient.Histogram({
  name: 'http_request_duration_seconds',
  help: 'Duration of HTTP requests in seconds',
  labelNames: ['method', 'route', 'status']
});

// Middleware
app.use((req, res, next) => {
  const start = Date.now();
  
  res.on('finish', () => {
    const duration = (Date.now() - start) / 1000;
    httpRequestDuration.labels(req.method, req.route.path, res.statusCode).observe(duration);
  });
  
  next();
});

1. Distributed Tracing

// OpenTelemetry
const { trace } = require('@opentelemetry/api');

const tracer = trace.getTracer('my-service');

async function processOrder(orderId) {
  const span = tracer.startSpan('process_order');
  span.setAttribute('order_id', orderId);
  
  try {
    // Business logic
    await validateOrder(orderId);
    await chargePayment(orderId);
    await updateInventory(orderId);
    
    span.setStatus({ code: SpanStatusCode.OK });
  } catch (error) {
    span.setStatus({ code: SpanStatusCode.ERROR });
    span.recordException(error);
    throw error;
  } finally {
    span.end();
  }
}

1. Health Checks

app.get('/health', async (req, res) => {
  const health = {
    status: 'healthy',
    timestamp: new Date().toISOString(),
    checks: {
      database: await checkDatabase(),
      redis: await checkRedis(),
      messageQueue: await checkMessageQueue()
    }
  };
  
  const isHealthy = Object.values(health.checks).every(c => c.status === 'up');
  const statusCode = isHealthy ? 200 : 503;
  
  res.status(statusCode).json(health);
});

Best Practices

API Design

1. Use consistent naming conventions 2. Version your APIs from the start 3. Document with OpenAPI/Swagger 4. Implement proper error handling 5. Use appropriate HTTP status codes 6. Support pagination for collections 7. Implement rate limiting 8. Use HTTPS for all endpoints

Database

1. Design schema with normalization in mind 2. Add indexes for frequently queried columns 3. Use transactions for data consistency 4. Implement soft deletes where appropriate 5. Use UUIDs for distributed systems 6. Plan for data migration from day one 7. Backup regularly and test restoration

Security

1. Never store passwords in plain text 2. Validate and sanitize all inputs 3. Use parameterized queries (prevent SQL injection) 4. Implement rate limiting 5. Use HTTPS/TLS for all communication 6. Keep dependencies updated 7. Follow principle of least privilege 8. Implement proper CORS policies

Performance

1. Cache frequently accessed data 2. Use database indexes strategically 3. Implement pagination for large datasets 4. Optimize N+1 queries 5. Use connection pooling 6. Implement lazy loading 7. Profile and optimize slow queries 8. Use CDN for static assets

Reliability

1. Implement circuit breakers 2. Add retry logic with exponential backoff 3. Design for idempotency 4. Implement graceful degradation 5. Use health checks and readiness probes 6. Plan for disaster recovery 7. Monitor and alert on critical metrics

Common Patterns

Repository Pattern

class UserRepository {
  async findById(id) {
    return await db.users.findOne({ id });
  }
  
  async findByEmail(email) {
    return await db.users.findOne({ email });
  }
  
  async create(userData) {
    return await db.users.insert(userData);
  }
  
  async update(id, data) {
    return await db.users.update({ id }, data);
  }
  
  async delete(id) {
    return await db.users.delete({ id });
  }
}

Service Layer Pattern

class UserService {
  constructor(userRepository, emailService) {
    this.userRepository = userRepository;
    this.emailService = emailService;
  }
  
  async registerUser(userData) {
    // Validate
    this.validateUserData(userData);
    
    // Check existing
    const existing = await this.userRepository.findByEmail(userData.email);
    if (existing) {
      throw new Error('Email already exists');
    }
    
    // Hash password
    userData.password = await bcrypt.hash(userData.password, 10);
    
    // Create user
    const user = await this.userRepository.create(userData);
    
    // Send welcome email
    await this.emailService.sendWelcomeEmail(user.email);
    
    return user;
  }
}

Database Design Reference

Comprehensive guide for designing relational and NoSQL databases.

Relational Database Design

1. Normal Forms

First Normal Form (1NF)

• Eliminate repeating groups
• Each column contains atomic values
• Each row is unique (has primary key)

-- ❌ Violates 1NF (repeating groups)
CREATE TABLE orders (
  id INT PRIMARY KEY,
  product1 VARCHAR(100),
  product2 VARCHAR(100),
  product3 VARCHAR(100)
);

-- ✅ Complies with 1NF
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT
);

CREATE TABLE order_items (
  id INT PRIMARY KEY,
  order_id INT,
  product_id INT,
  FOREIGN KEY (order_id) REFERENCES orders(id)
);

Second Normal Form (2NF)

• Must be in 1NF
• Remove partial dependencies (non-key attributes depend on entire key)

-- ❌ Violates 2NF (product_name depends only on product_id)
CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  product_name VARCHAR(100),
  quantity INT,
  PRIMARY KEY (order_id, product_id)
);

-- ✅ Complies with 2NF
CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  quantity INT,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

Third Normal Form (3NF)

• Must be in 2NF
• Remove transitive dependencies (non-key attributes depend only on primary key)

-- ❌ Violates 3NF (city depends on zip_code, not user_id)
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  zip_code VARCHAR(10),
  city VARCHAR(100)
);

-- ✅ Complies with 3NF
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  zip_code VARCHAR(10),
  FOREIGN KEY (zip_code) REFERENCES zip_codes(code)
);

CREATE TABLE zip_codes (
  code VARCHAR(10) PRIMARY KEY,
  city VARCHAR(100)
);

Boyce-Codd Normal Form (BCNF)

• Must be in 3NF
• Every determinant is a candidate key

---

2. Indexing Strategies

Primary Key Index

-- Automatically indexed
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid()
);

Foreign Key Indexes

CREATE TABLE posts (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Add index for foreign key
CREATE INDEX idx_posts_user_id ON posts(user_id);

Single Column Index

-- Index for WHERE clause
CREATE INDEX idx_users_email ON users(email);

-- Query benefits: WHERE email = 'user@example.com'

Composite Index

-- For queries filtering/sorting multiple columns
CREATE INDEX idx_posts_user_status_created 
ON posts(user_id, status, created_at DESC);

-- Benefits queries like:
-- WHERE user_id = ? AND status = ?
-- WHERE user_id = ? AND status = ? ORDER BY created_at DESC

Partial Index

-- Index only subset of rows
CREATE INDEX idx_posts_published 
ON posts(published_at) 
WHERE status = 'published';

Full-Text Index

-- PostgreSQL
CREATE INDEX idx_posts_fulltext 
ON posts USING GIN(to_tsvector('english', title || ' ' || content));

-- Query
SELECT * FROM posts 
WHERE to_tsvector('english', title || ' ' || content) 
@@ to_tsquery('english', 'search & terms');

Index Best Practices

• Index columns used in WHERE, JOIN, ORDER BY
• Don't over-index (slows writes)
• Index foreign keys
• Use composite indexes for multi-column queries
• Monitor index usage and remove unused ones
• Consider index size vs. benefit

---

3. Common Table Patterns

Timestamps

CREATE TABLE base_table (
  id UUID PRIMARY KEY,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Trigger to auto-update updated_at
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON base_table
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

Soft Deletes

CREATE TABLE users (
  id UUID PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  deleted_at TIMESTAMP NULL,
  
  -- Ensure active emails are unique
  CONSTRAINT unique_active_email 
  UNIQUE (email) WHERE deleted_at IS NULL
);

-- Query active users
SELECT * FROM users WHERE deleted_at IS NULL;

Versioning/Audit Trail

CREATE TABLE posts (
  id UUID PRIMARY KEY,
  title VARCHAR(500),
  content TEXT,
  version INT NOT NULL DEFAULT 1,
  created_by UUID REFERENCES users(id),
  updated_by UUID REFERENCES users(id),
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE posts_history (
  id UUID PRIMARY KEY,
  post_id UUID REFERENCES posts(id),
  title VARCHAR(500),
  content TEXT,
  version INT NOT NULL,
  changed_by UUID REFERENCES users(id),
  changed_at TIMESTAMP NOT NULL DEFAULT NOW()
);

Polymorphic Associations

CREATE TABLE comments (
  id UUID PRIMARY KEY,
  commentable_type VARCHAR(50) NOT NULL, -- 'post', 'photo', etc.
  commentable_id UUID NOT NULL,
  content TEXT NOT NULL,
  user_id UUID REFERENCES users(id)
);

CREATE INDEX idx_comments_polymorphic 
ON comments(commentable_type, commentable_id);

Self-Referencing (Tree Structure)

CREATE TABLE categories (
  id UUID PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  parent_id UUID REFERENCES categories(id),
  path VARCHAR(500) -- Materialized path: '/electronics/computers/laptops'
);

-- Get all descendants
WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 1 as depth
  FROM categories
  WHERE id = 'root_category_id'
  
  UNION ALL
  
  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM categories c
  INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

Many-to-Many (Junction Table)

CREATE TABLE users (
  id UUID PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE roles (
  id UUID PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE user_roles (
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  role_id UUID REFERENCES roles(id) ON DELETE CASCADE,
  assigned_at TIMESTAMP NOT NULL DEFAULT NOW(),
  PRIMARY KEY (user_id, role_id)
);

CREATE INDEX idx_user_roles_user ON user_roles(user_id);
CREATE INDEX idx_user_roles_role ON user_roles(role_id);

---

4. Data Types

PostgreSQL Recommended Types

CREATE TABLE data_types_example (
  -- Primary keys
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  -- id SERIAL PRIMARY KEY,  -- Auto-increment integer
  
  -- Text
  short_text VARCHAR(255),
  long_text TEXT,
  fixed_length CHAR(10),
  
  -- Numbers
  small_int SMALLINT,        -- -32768 to 32767
  regular_int INTEGER,       -- -2147483648 to 2147483647
  big_int BIGINT,           -- Very large numbers
  decimal_number DECIMAL(10, 2),  -- Precise (10 digits, 2 decimal)
  float_number REAL,        -- Approximate
  double_number DOUBLE PRECISION,
  
  -- Boolean
  is_active BOOLEAN DEFAULT true,
  
  -- Date/Time
  date_only DATE,
  time_only TIME,
  timestamp_val TIMESTAMP,
  timestamp_tz TIMESTAMPTZ,  -- With timezone (recommended)
  
  -- JSON
  json_data JSON,
  jsonb_data JSONB,  -- Binary JSON (faster, recommended)
  
  -- Arrays
  tags TEXT[],
  numbers INTEGER[],
  
  -- Binary
  file_data BYTEA,
  
  -- Network
  ip_address INET,
  mac_address MACADDR,
  
  -- Other
  uuid_val UUID,
  enum_val status_enum
);

-- Enum type
CREATE TYPE status_enum AS ENUM ('draft', 'published', 'archived');

---

5. Constraints

Primary Key

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid()
);

Foreign Key

CREATE TABLE posts (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE
);

Unique

CREATE TABLE users (
  id UUID PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  username VARCHAR(100) NOT NULL UNIQUE
);

-- Composite unique
CREATE TABLE user_preferences (
  user_id UUID,
  key VARCHAR(100),
  value TEXT,
  UNIQUE (user_id, key)
);

Check

CREATE TABLE products (
  id UUID PRIMARY KEY,
  price DECIMAL(10, 2) CHECK (price >= 0),
  quantity INTEGER CHECK (quantity >= 0),
  status VARCHAR(20) CHECK (status IN ('active', 'inactive', 'discontinued'))
);

Not Null

CREATE TABLE users (
  id UUID PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(100) NOT NULL
);

---

6. Transactions

ACID Properties

• Atomicity: All or nothing
• Consistency: Valid state transitions
• Isolation: Concurrent transactions don't interfere
• Durability: Committed changes persist

Usage Example

BEGIN;

-- Transfer money between accounts
UPDATE accounts SET balance = balance - 100 WHERE id = 'acc_123';
UPDATE accounts SET balance = balance + 100 WHERE id = 'acc_456';

-- Insert transaction record
INSERT INTO transactions (from_account, to_account, amount) 
VALUES ('acc_123', 'acc_456', 100);

COMMIT;
-- or ROLLBACK on error

Isolation Levels

-- Read Uncommitted (lowest isolation, dirty reads possible)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Read Committed (default in PostgreSQL)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Repeatable Read (prevents non-repeatable reads)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Serializable (highest isolation, prevents anomalies)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

---

7. Query Optimization

Explain Query Plan

EXPLAIN ANALYZE
SELECT u.name, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.created_at > '2026-01-01'
GROUP BY u.id, u.name
ORDER BY post_count DESC
LIMIT 10;

Avoid N+1 Queries

-- ❌ Bad (N+1)
SELECT * FROM posts;
-- Then for each post: SELECT * FROM comments WHERE post_id = ?

-- ✅ Good (Single query or join)
SELECT p.*, 
       COALESCE(json_agg(c.*) FILTER (WHERE c.id IS NOT NULL), '[]') as comments
FROM posts p
LEFT JOIN comments c ON p.id = c.post_id
GROUP BY p.id;

Use Covering Indexes

-- Include all columns needed in query
CREATE INDEX idx_posts_covering 
ON posts(user_id, status) 
INCLUDE (title, created_at);

-- This query only needs index (no table access)
SELECT title, created_at 
FROM posts 
WHERE user_id = ? AND status = 'published';

Pagination Optimization

-- ❌ Bad (OFFSET on large datasets)
SELECT * FROM posts 
ORDER BY created_at DESC 
LIMIT 20 OFFSET 10000;

-- ✅ Good (Cursor-based)
SELECT * FROM posts 
WHERE created_at < ?  -- Last seen timestamp
ORDER BY created_at DESC 
LIMIT 20;

---

NoSQL Database Design

1. Document Database (MongoDB)

Schema Design Patterns

Embedding (Denormalization)

// One-to-Few: Embed addresses
{
  _id: ObjectId("..."),
  name: "John Doe",
  email: "john@example.com",
  addresses: [
    {
      type: "home",
      street: "123 Main St",
      city: "New York",
      zip: "10001"
    },
    {
      type: "work",
      street: "456 Work Ave",
      city: "New York",
      zip: "10002"
    }
  ]
}

Referencing (Normalization)

// One-to-Many: Reference posts
// User document
{
  _id: ObjectId("user123"),
  name: "John Doe",
  email: "john@example.com"
}

// Post documents
{
  _id: ObjectId("post456"),
  user_id: ObjectId("user123"),
  title: "My Post",
  content: "..."
}

Two-Way Referencing

// Many-to-Many: Users and Groups
// User document
{
  _id: ObjectId("user123"),
  name: "John Doe",
  group_ids: [ObjectId("group1"), ObjectId("group2")]
}

// Group document
{
  _id: ObjectId("group1"),
  name: "Developers",
  member_ids: [ObjectId("user123"), ObjectId("user456")]
}

Extended Reference (Denormalization)

// Store frequently accessed fields
{
  _id: ObjectId("post456"),
  title: "My Post",
  author: {
    id: ObjectId("user123"),
    name: "John Doe",  // Denormalized
    avatar: "https://..."  // Denormalized
  },
  content: "..."
}

Indexing

// Single field
db.users.createIndex({ email: 1 });

// Compound index
db.posts.createIndex({ user_id: 1, created_at: -1 });

// Text index
db.posts.createIndex({ title: "text", content: "text" });

// Geospatial index
db.stores.createIndex({ location: "2dsphere" });

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

---

2. Key-Value Store (Redis)

Data Structures

Strings

SET user:123:name "John Doe"
GET user:123:name
SETEX session:abc 3600 "session_data"  -- With expiry
INCR page_views:home

Hashes (Objects)

HSET user:123 name "John Doe" email "john@example.com" age 30
HGET user:123 name
HGETALL user:123
HINCRBY user:123 age 1

Lists (Ordered)

LPUSH notifications:user123 "New message"
LRANGE notifications:user123 0 9  -- Get first 10
LTRIM notifications:user123 0 99  -- Keep only latest 100

Sets (Unique Values)

SADD tags:post123 "javascript" "nodejs" "api"
SMEMBERS tags:post123
SINTER tags:post123 tags:post456  -- Common tags

Sorted Sets (Ordered by Score)

ZADD leaderboard 100 "user123" 95 "user456" 88 "user789"
ZRANGE leaderboard 0 9 WITHSCORES  -- Top 10
ZREVRANK leaderboard "user123"  -- User's rank

Common Patterns

Caching

-- Cache user data
SET cache:user:123 '{"name":"John","email":"..."}' EX 300

-- Check cache first in application
user = redis.get('cache:user:123')
if (!user) {
  user = db.users.find(123)
  redis.setex('cache:user:123', 300, JSON.stringify(user))
}

Session Storage

SETEX session:abc123 3600 '{"user_id":"123","role":"admin"}'

Rate Limiting

-- Fixed window
key = "ratelimit:api:" + userId + ":" + currentHour
INCR key
EXPIRE key 3600
count = GET key
if count > limit: reject()

Pub/Sub

-- Publisher
PUBLISH notifications "New message"

-- Subscriber
SUBSCRIBE notifications

---

3. Column-Family (Cassandra)

Data Modeling

-- Query-first design
CREATE TABLE users_by_email (
  email TEXT PRIMARY KEY,
  user_id UUID,
  name TEXT,
  created_at TIMESTAMP
);

CREATE TABLE posts_by_user (
  user_id UUID,
  created_at TIMESTAMP,
  post_id UUID,
  title TEXT,
  content TEXT,
  PRIMARY KEY ((user_id), created_at, post_id)
) WITH CLUSTERING ORDER BY (created_at DESC);

-- Query efficiently
SELECT * FROM posts_by_user 
WHERE user_id = ? 
ORDER BY created_at DESC 
LIMIT 10;

---

4. Graph Database (Neo4j)

Data Model

// Create nodes
CREATE (u:User {id: 'user123', name: 'John Doe'})
CREATE (p:Post {id: 'post456', title: 'My Post'})

// Create relationships
CREATE (u)-[:AUTHORED]->(p)
CREATE (u1:User)-[:FOLLOWS]->(u2:User)
CREATE (u)-[:LIKES]->(p)

// Query relationships
MATCH (u:User {name: 'John Doe'})-[:AUTHORED]->(p:Post)
RETURN p

// Find friends of friends
MATCH (me:User {id: 'user123'})-[:FOLLOWS]->()-[:FOLLOWS]->(fof:User)
WHERE NOT (me)-[:FOLLOWS]->(fof) AND me <> fof
RETURN fof

// Shortest path
MATCH path = shortestPath((u1:User)-[*]-(u2:User))
WHERE u1.id = 'user123' AND u2.id = 'user456'
RETURN path

---

Database Scaling Strategies

1. Read Replicas

Master (Write) ─┬─► Replica 1 (Read)
                ├─► Replica 2 (Read)
                └─► Replica 3 (Read)

2. Sharding (Horizontal Partitioning)

Shard 1: Users A-F
Shard 2: Users G-M
Shard 3: Users N-S
Shard 4: Users T-Z

3. Vertical Partitioning

Users table ─┬─► Basic info (id, email, name)
             └─► Extended profile (bio, preferences)

4. Caching Layer

Application ─► Cache (Redis) ─► Database

---

Database Design Checklist

• [ ] Normalized to 3NF (or denormalized with reason)
• [ ] Primary keys defined
• [ ] Foreign keys with appropriate constraints
• [ ] Indexes on frequently queried columns
• [ ] Timestamps (created_at, updated_at)
• [ ] Soft delete strategy (if needed)
• [ ] Appropriate data types chosen
• [ ] Constraints (NOT NULL, CHECK, UNIQUE)
• [ ] Migration strategy planned
• [ ] Backup and recovery plan
• [ ] Scaling strategy (read replicas, sharding)
• [ ] Query performance tested
• [ ] Connection pooling configured

This reference provides comprehensive patterns for both relational and NoSQL database design.

Examples

Example 1: E-Commerce Backend

Architecture: Microservices with event-driven communication

Services:

• User Service (authentication, profiles)
• Product Service (catalog, search)
• Order Service (cart, checkout, orders)
• Payment Service (Stripe integration)
• Inventory Service (stock management)
• Notification Service (emails, SMS)

Technology Stack:

• Backend: Node.js/Express, Python/FastAPI
• Database: PostgreSQL (relational), MongoDB (product catalog), Redis (cache)
• Message Queue: RabbitMQ
• Search: Elasticsearch
• Infrastructure: Docker, Kubernetes, AWS

Key Patterns:

• API Gateway (Kong)
• Event-driven architecture
• CQRS for orders
• Saga pattern for distributed transactions

Example 2: Real-Time Chat Application

Architecture: Monolithic with WebSocket support

Features:

• User authentication
• One-on-one messaging
• Group chats
• Message history
• Online presence
• Typing indicators
• File sharing

Technology Stack:

• Backend: Node.js with Socket.IO
• Database: PostgreSQL (users, messages), Redis (presence, cache)
• Storage: AWS S3 (files)
• Infrastructure: Docker, AWS ECS

Key Patterns:

• WebSocket for real-time communication
• Redis pub/sub for message distribution
• Message queue for async processing (notifications)