Project 02: Create Your Own Online Store 🛍️

// MongoDB schema - flexible for different product types
\{
  name: "Handmade Ceramic Mug",
  slug: "handmade-ceramic-mug",
  price: 25.00,
  images: [
    "https://cloudinary.com/your-store/mug-1.jpg",
    "https://cloudinary.com/your-store/mug-2.jpg"
  ],
  description: "Beautiful handcrafted ceramic mug...",
  
  // Flexible attributes based on product type
  attributes: \{
    color: ["Blue", "Green", "White"],
    size: "350ml",
    material: "Ceramic",
    care: "Dishwasher safe"
  \},
  
  // For digital products
  digital: \{
    downloadUrl: "https://...",
    fileSize: "25MB",
    fileType: "PDF"
  \},
  
  stock: 15,
  featured: true,
  createdAt: new Date()
\}

// PostgreSQL tables for transactional data
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255),
    stripe_customer_id VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    status VARCHAR(50) DEFAULT 'pending',
    total DECIMAL(10,2),
    stripe_payment_intent_id VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    product_id VARCHAR(255), -- MongoDB ObjectId
    product_name VARCHAR(255),
    price DECIMAL(10,2),
    quantity INTEGER,
    subtotal DECIMAL(10,2)
);

// API endpoint for checkout
app.post('/api/checkout', async (req, res) => {
  const { items, email } = req.body;
  
  // Calculate total from MongoDB products
  const productIds = items.map(item => item.productId);
  const products = await db.products.find({ 
    _id: { $in: productIds } 
  });
  
  const total = items.reduce((sum, item) => {
    const product = products.find(p => p._id === item.productId);
    return sum + (product.price * item.quantity);
  }, 0);
  
  // Create Stripe payment intent
  const paymentIntent = await stripe.paymentIntents.create({
    amount: Math.round(total * 100),
    currency: 'usd',
    metadata: { email }
  });
  
  // Save order in PostgreSQL
  const order = await db.query(`
    INSERT INTO orders (customer_email, total, stripe_payment_intent_id)
    VALUES ($1, $2, $3)
    RETURNING id
  `, [email, total, paymentIntent.id]);
  
  res.json({ 
    clientSecret: paymentIntent.client_secret,
    orderId: order.rows[0].id 
  });
});

// Track inventory in PostgreSQL
CREATE TABLE inventory (
    id SERIAL PRIMARY KEY,
    product_id VARCHAR(255) UNIQUE,
    quantity INTEGER DEFAULT 0,
    reserved INTEGER DEFAULT 0,
    low_stock_threshold INTEGER DEFAULT 5,
    updated_at TIMESTAMP DEFAULT NOW()
);

// Trigger to update inventory
CREATE OR REPLACE FUNCTION update_inventory()
RETURNS TRIGGER AS $
BEGIN
    -- When order is paid, reserve inventory
    IF NEW.status = 'paid' AND OLD.status = 'pending' THEN
        UPDATE inventory i
        SET reserved = reserved + oi.quantity
        FROM order_items oi
        WHERE oi.order_id = NEW.id 
        AND i.product_id = oi.product_id;
    END IF;
    
    -- When order is fulfilled, deduct from stock
    IF NEW.status = 'fulfilled' AND OLD.status = 'paid' THEN
        UPDATE inventory i
        SET quantity = quantity - oi.quantity,
            reserved = reserved - oi.quantity
        FROM order_items oi
        WHERE oi.order_id = NEW.id 
        AND i.product_id = oi.product_id;
    END IF;
    
    RETURN NEW;
END;
$ LANGUAGE plpgsql;

// Next.js admin page
export default function AdminDashboard() {
  const { data: stats } = useSWR('/api/admin/stats');
  
  return (
    <div className="grid grid-cols-1 md:grid-cols-3 gap-6">
      <StatCard 
        title="Today's Revenue" 
        value={`${stats?.todayRevenue || 0}`}
        change={stats?.revenueChange}
      />
      <StatCard 
        title="Orders" 
        value={stats?.todayOrders || 0}
        change={stats?.orderChange}
      />
      <StatCard 
        title="Low Stock Items" 
        value={stats?.lowStockCount || 0}
        alert={stats?.lowStockCount > 0}
      />
      
      <RecentOrders />
      <TopProducts />
      <CustomerInsights />
    </div>
  );
}

// Reviews collection
{
  productId: ObjectId("..."),
  customerId: 123,
  rating: 5,
  title: "Amazing quality!",
  comment: "This mug is even better than expected...",
  images: ["review-photo-1.jpg"],
  verified: true, // Purchased the product
  helpful: 12,
  createdAt: new Date()
}

// Aggregate ratings
db.reviews.aggregate([
  { $match: { productId: productId } },
  { $group: {
    _id: "$productId",
    avgRating: { $avg: "$rating" },
    count: { $sum: 1 },
    distribution: {
      $push: "$rating"
    }
  }}
]);

// Find products frequently bought together
WITH purchase_pairs AS (
  SELECT 
    oi1.product_id as product_a,
    oi2.product_id as product_b,
    COUNT(*) as times_bought_together
  FROM order_items oi1
  JOIN order_items oi2 ON oi1.order_id = oi2.order_id
  WHERE oi1.product_id != oi2.product_id
  GROUP BY oi1.product_id, oi2.product_id
  HAVING COUNT(*) >= 3
)
SELECT product_b, times_bought_together
FROM purchase_pairs
WHERE product_a = $1
ORDER BY times_bought_together DESC
LIMIT 5;

// Real-time analytics with SQL views
CREATE VIEW daily_revenue AS
SELECT 
  DATE(created_at) as date,
  COUNT(*) as orders,
  SUM(total) as revenue,
  AVG(total) as avg_order_value
FROM orders
WHERE status IN ('paid', 'fulfilled')
GROUP BY DATE(created_at);

CREATE VIEW product_performance AS
SELECT 
  oi.product_id,
  oi.product_name,
  COUNT(DISTINCT oi.order_id) as times_ordered,
  SUM(oi.quantity) as units_sold,
  SUM(oi.subtotal) as revenue
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
WHERE o.status IN ('paid', 'fulfilled')
GROUP BY oi.product_id, oi.product_name;

// Abandoned cart recovery
const findAbandonedCarts = async () => \{
  const carts = await db.query(`
    SELECT DISTINCT 
      c.email,
      c.name,
      array_agg(json_build_object(
        'product_name', oi.product_name,
        'price', oi.price,
        'quantity', oi.quantity
      )) as items
    FROM orders o
    JOIN customers c ON o.customer_id = c.id
    JOIN order_items oi ON o.id = oi.order_id
    WHERE o.status = 'pending'
    AND o.created_at < NOW() - INTERVAL '1 hour'
    AND o.created_at > NOW() - INTERVAL '24 hours'
    GROUP BY c.email, c.name
  `);
  
  // Send recovery emails
  for (const cart of carts.rows) \{
    await sendAbandonedCartEmail(cart);
  \}
\};

# Clone the starter template
git clone https://github.com/telebort/ecommerce-starter
cd ecommerce-starter

# Install dependencies
npm install

# Set up environment variables
cp .env.example .env.local
# Add your Stripe, Supabase, MongoDB Atlas keys

# Run development server
npm run dev

your-store/
├── pages/
│   ├── index.js          # Homepage with featured products
│   ├── products/
│   │   └── [slug].js     # Product detail pages
│   ├── cart.js           # Shopping cart
│   ├── checkout.js       # Stripe checkout
│   └── admin/
│       ├── dashboard.js   # Admin analytics
│       ├── products.js    # Product management
│       └── orders.js      # Order fulfillment
├── lib/
│   ├── mongodb.js        # MongoDB connection
│   ├── postgres.js       # PostgreSQL queries
│   └── stripe.js         # Payment processing
└── components/
    ├── ProductCard.js    # Product display
    ├── Cart.js           # Cart sidebar
    └── CheckoutForm.js   # Stripe Elements