Project 04: Online Store Database Architecture 🛒

Products ←→ Categories (Many-to-Many)
Products ←→ Inventory (One-to-Many)
Users → Orders → OrderItems ← Products
Users → Cart → CartItems ← Products
Orders → Payments → Transactions
Products → Reviews ← Users

-- ACID transaction for order placement
BEGIN TRANSACTION;

-- 1. Lock inventory (pessimistic locking)
SELECT quantity FROM inventory 
WHERE product_id = ? FOR UPDATE;

-- 2. Create order record
INSERT INTO orders (user_id, total, status)
VALUES (?, ?, 'pending')
RETURNING order_id;

-- 3. Add order items
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (?, ?, ?, ?);

-- 4. Decrement inventory
UPDATE inventory 
SET quantity = quantity - ?,
    reserved = reserved - ?
WHERE product_id = ? AND quantity >= ?;

-- 5. Record payment attempt
INSERT INTO payments (order_id, amount, method, status)
VALUES (?, ?, ?, 'processing');

-- If all succeeds:
COMMIT;
-- If any fails:
ROLLBACK;

// When to use MongoDB for e-commerce
const mongoDBStrengths = {
  productCatalog: {
    reason: "Flexible schema for diverse product types",
    example: "Electronics have specs, clothing has sizes/colors"
  },
  recommendations: {
    reason: "Document model suits user preferences",
    example: "Embedded arrays of viewed/purchased items"
  },
  sessionData: {
    reason: "TTL indexes for automatic cleanup",
    example: "Shopping cart expiration"
  }
};

// When to use PostgreSQL for e-commerce
const postgreSQLStrengths = {
  financialData: {
    reason: "ACID transactions for payment integrity",
    example: "Order processing with inventory updates"
  },
  inventory: {
    reason: "Strong consistency for stock levels",
    example: "Preventing overselling with locks"
  },
  reporting: {
    reason: "Complex SQL queries for analytics",
    example: "Revenue reports with multiple JOINs"
  }
};

// Discovery Challenge: Product Catalog Architecture
// Research Question: Why might MongoDB excel for product catalogs?

// Consider this product diversity
const products = [
  {
    type: "laptop",
    specs: { cpu: "i7", ram: "16GB", storage: "512GB SSD" },
    variants: null
  },
  {
    type: "tshirt",
    specs: null,
    variants: [
      { size: "S", color: "Red", sku: "TSH-S-RED" },
      { size: "M", color: "Blue", sku: "TSH-M-BLUE" }
    ]
  },
  {
    type: "subscription",
    specs: { duration: "monthly", features: ["Feature A", "Feature B"] },
    variants: null
  }
];

// TODO: Model this in both databases
// TODO: Compare query complexity for filtering
// TODO: Analyze schema evolution scenarios
// TODO: Document your decision rationale

-- PostgreSQL approach: Normalized tables
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    type VARCHAR(50),
    base_price DECIMAL(10,2)
);

CREATE TABLE product_attributes (
    product_id INT REFERENCES products(product_id),
    attribute_name VARCHAR(50),
    attribute_value TEXT,
    PRIMARY KEY (product_id, attribute_name)
);

-- Research: How many JOINs for product display?
-- Challenge: Add new product type with unique attributes

// MongoDB approach: Flexible documents
db.products.insertOne({
  name: "Gaming Laptop",
  type: "electronics",
  price: 1299.99,
  // Flexible attributes without schema changes
  specs: {
    processor: "Intel i7",
    graphics: "RTX 3060",
    display: { size: "15.6\"", resolution: "1920x1080" }
  },
  reviews: [/* embedded reviews */]
});

// Research: How to maintain consistency?
// Challenge: Query products by nested attributes

// Discovery Challenge: Distributed Transaction Management
// Problem: Order in PostgreSQL, Product details in MongoDB

class HybridOrderService {
  async placeOrder(customerId, items) {
    // Research: Two-Phase Commit pattern
    // TODO: Implement compensation pattern
    // TODO: Handle partial failures
    
    // Step 1: Reserve inventory (PostgreSQL)
    const pgTransaction = await pgClient.query('BEGIN');
    
    try {
      // Step 2: Validate products (MongoDB)
      const products = await mongoClient
        .collection('products')
        .find({ _id: { $in: items.map(i => i.productId) } })
        .toArray();
      
      // Step 3: Create order (PostgreSQL)
      const order = await pgClient.query(
        'INSERT INTO orders ... RETURNING order_id'
      );
      
      // Step 4: Update inventory (PostgreSQL)
      await pgClient.query(
        'UPDATE inventory SET quantity = quantity - ? ...'
      );
      
      // Step 5: Log to MongoDB for analytics
      await mongoClient.collection('order_analytics').insertOne({
        orderId: order.order_id,
        products: products,
        timestamp: new Date()
      });
      
      await pgClient.query('COMMIT');
    } catch (error) {
      await pgClient.query('ROLLBACK');
      // TODO: Implement compensation logic
      // Research: Saga pattern for distributed transactions
    }
  }
}

// Critical Questions:
// 1. What if MongoDB is down but PostgreSQL is up?
// 2. How do you handle consistency delays?
// 3. When is eventual consistency acceptable?

-- Discovery: Optimistic vs Pessimistic Locking
-- Research which approach for which scenario

-- Pessimistic Locking (PostgreSQL)
CREATE FUNCTION reserve_inventory(
  p_product_id INT,
  p_quantity INT
) RETURNS BOOLEAN AS $
DECLARE
  v_available INT;
BEGIN
  -- Lock the row immediately
  SELECT quantity INTO v_available
  FROM inventory
  WHERE product_id = p_product_id
  FOR UPDATE NOWAIT; -- Fail fast if locked
  
  IF v_available >= p_quantity THEN
    UPDATE inventory 
    SET quantity = quantity - p_quantity,
        reserved = reserved + p_quantity
    WHERE product_id = p_product_id;
    RETURN TRUE;
  END IF;
  
  RETURN FALSE;
END;
$ LANGUAGE plpgsql;

-- Research: Impact on concurrent users
-- TODO: Implement optimistic locking alternative
-- TODO: Compare performance under load

// Discovery Challenge: Cross-Database Query Optimization
// Scenario: Product recommendations need both databases

class RecommendationEngine {
  // Naive approach - multiple round trips
  async getRecommendationsNaive(userId) {
    // Get user purchase history from PostgreSQL
    const purchases = await pgClient.query(
      'SELECT product_id FROM orders WHERE user_id = ?'
    );
    
    // Get product details from MongoDB
    const products = await mongoClient
      .collection('products')
      .find({ _id: { $in: purchases.rows.map(r => r.product_id) } })
      .toArray();
    
    // Get similar products from MongoDB
    const recommendations = await mongoClient
      .collection('products')
      .find({ category: { $in: products.map(p => p.category) } })
      .limit(10)
      .toArray();
    
    return recommendations;
  }
  
  // Optimized approach - minimize round trips
  async getRecommendationsOptimized(userId) {
    // TODO: Implement caching strategy
    // TODO: Use aggregation pipeline
    // TODO: Consider materialized views
    // Research: When to denormalize for performance?
  }
}

// Performance metrics to measure:
// - Query response time
// - Database round trips
// - Memory usage
// - Cache hit rate

// MongoDB text search
db.products.createIndex({ 
  name: "text", 
  description: "text", 
  tags: "text" 
});

db.products.find({ 
  $text: { $search: "wireless headphones" } 
}).explain("executionStats");

// PostgreSQL full-text search
CREATE INDEX idx_products_search ON products 
USING GIN(to_tsvector('english', name || ' ' || description));

SELECT * FROM products 
WHERE to_tsvector('english', name || ' ' || description) 
  @@ to_tsquery('english', 'wireless & headphones');

// Research: Compare search performance
// TODO: Implement faceted search
// TODO: Design search result ranking

// Discovery Challenge: Monitoring Hybrid Systems
// Problem: Different metrics for different databases

const monitoringStrategy = {
  postgresql: {
    metrics: [
      "Connection pool utilization",
      "Transaction duration",
      "Lock wait time",
      "Replication lag"
    ],
    alerts: {
      connectionPoolExhaustion: "connections > 80%",
      slowQueries: "duration > 1000ms",
      deadlocks: "count > 0"
    }
  },
  mongodb: {
    metrics: [
      "Operation latency",
      "Document size",
      "Index usage",
      "Replica set status"
    ],
    alerts: {
      highLatency: "latency > 500ms",
      replicationLag: "lag > 10s",
      indexMiss: "usage < 90%"
    }
  },
  application: {
    metrics: [
      "Cross-database query time",
      "Cache hit rate",
      "Transaction success rate"
    ],
    alerts: {
      consistencyErrors: "mismatch > 0",
      compensationFailures: "failed > 0"
    }
  }
};

// TODO: Design unified monitoring dashboard
// TODO: Implement distributed tracing
// TODO: Create runbook for common issues

# Recovery scenarios to plan for
disaster_recovery:
  scenario_1:
    name: "PostgreSQL down, MongoDB up"
    impact: "No new orders, catalog browsing works"
    recovery_steps:
      - Switch to read-only mode
      - Queue orders for later processing
      - Restore PostgreSQL from backup
      - Process queued orders
      
  scenario_2:
    name: "Data inconsistency detected"
    impact: "Inventory mismatch, overselling risk"
    recovery_steps:
      - Identify inconsistency source
      - Halt affected operations
      - Run reconciliation job
      - Verify data integrity
      
  scenario_3:
    name: "Regional database failure"
    impact: "Regional customers affected"
    recovery_steps:
      - Failover to secondary region
      - Update DNS/routing
      - Sync data when primary recovers

# ADR-001: Hybrid Database Architecture for E-commerce

## Status
Accepted

## Context
Our e-commerce platform needs to handle:
- Flexible product catalog with varied attributes
- Financial transactions with ACID guarantees
- Real-time inventory management
- Analytics and recommendations

Single database solutions have limitations:
- PostgreSQL: Rigid schema for diverse products
- MongoDB: Weak consistency for financial data

## Decision
Implement hybrid architecture:
- MongoDB for product catalog and recommendations
- PostgreSQL for orders, inventory, and payments
- Redis for session data and caching

## Consequences
### Positive
- Optimal database for each use case
- Better performance through specialization
- Easier scaling of independent components

### Negative
- Increased operational complexity
- Cross-database consistency challenges
- Higher infrastructure costs
- Need for specialized expertise

### Mitigations
- Implement robust monitoring
- Use compensation patterns for consistency
- Automate operational tasks
- Comprehensive documentation

## Alternatives Considered
1. PostgreSQL only with JSONB
   - Rejected: Poor performance for document queries
   
2. MongoDB only with transactions
   - Rejected: Limited transaction support
   
3. NewSQL database (CockroachDB)
   - Rejected: Immature ecosystem