Case Study: CRUD Application (Admin Dashboard)

Tổng quan

Xây dựng admin dashboard với CRUD operations sử dụng Supabase REST API.

Yêu cầu

• User authentication (admin role)
• Product management (CRUD)
• Order management (view, update status)
• Dashboard analytics
• Role-based access control

Kết quả mong đợi

• Fully functional admin dashboard
• Protected routes
• Real-time data updates
• Responsive design

---

Kiến trúc

┌─────────────────────────────────────────────────────────────┐
│                   ADMIN DASHBOARD ARCHITECTURE               │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  Admin User                                                  │
│    │                                                         │
│    ▼                                                         │
│  Next.js App (Cloudflare Workers)                           │
│    │                                                         │
│    ├──▶ Server Components (SSR data fetching)               │
│    │    │                                                    │
│    │    └──▶ Supabase REST API (PostgREST)                  │
│    │         ├── GET /rest/v1/products                      │
│    │         ├── POST /rest/v1/products                     │
│    │         ├── PATCH /rest/v1/products?id=eq.xxx          │
│    │         └── DELETE /rest/v1/products?id=eq.xxx         │
│    │                                                         │
│    ├──▶ Client Components (mutations, realtime)             │
│    │                                                         │
│    └──▶ Supabase Auth (admin verification)                  │
│                                                              │
│  Database (PostgreSQL + RLS)                                │
│    ├── products                                             │
│    ├── orders                                               │
│    ├── profiles (with role)                                 │
│    └── RLS policies (admin-only access)                     │
│                                                              │
└─────────────────────────────────────────────────────────────┘

Stack được sử dụng

Component	Technology	Lý do
Frontend	Next.js (SSR/Client)	Admin dashboard needs interactivity
API	Supabase REST (PostgREST)	Auto-generated CRUD, no backend code
Auth	Supabase Auth	Session management
Authorization	RLS Policies	Database-level security
Hosting	Cloudflare Workers	Edge deployment

---

Database Schema

Tables

-- supabase/migrations/001_admin_tables.sql

-- Profiles with role
CREATE TABLE public.profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT NOT NULL,
  full_name TEXT,
  role TEXT DEFAULT 'user' CHECK (role IN ('user', 'admin', 'super_admin')),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Products
CREATE TABLE public.products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  description TEXT,
  price NUMERIC(10,2) NOT NULL CHECK (price >= 0),
  stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
  category TEXT,
  image_url TEXT,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Orders
CREATE TABLE public.orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id),
  status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),
  total NUMERIC(10,2) NOT NULL,
  shipping_address JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Order items
CREATE TABLE public.order_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id UUID REFERENCES public.orders(id) ON DELETE CASCADE,
  product_id UUID REFERENCES public.products(id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price NUMERIC(10,2) NOT NULL
);

-- Indexes
CREATE INDEX idx_products_category ON products(category);
CREATE INDEX idx_products_active ON products(is_active);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_order_items_order ON order_items(order_id);

RLS Policies (Admin Only)

-- Enable RLS
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE order_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- Helper function: Check if user is admin
CREATE OR REPLACE FUNCTION is_admin()
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM profiles
    WHERE id = auth.uid()
    AND role IN ('admin', 'super_admin')
  );
$$ LANGUAGE sql STABLE SECURITY DEFINER;

-- Products: Admin only
CREATE POLICY "Admin can do all on products"
  ON products FOR ALL
  USING (is_admin())
  WITH CHECK (is_admin());

-- Public can view active products
CREATE POLICY "Public can view active products"
  ON products FOR SELECT
  USING (is_active = true);

-- Orders: Admin can manage all
CREATE POLICY "Admin can do all on orders"
  ON orders FOR ALL
  USING (is_admin())
  WITH CHECK (is_admin());

-- Users can view own orders
CREATE POLICY "Users view own orders"
  ON orders FOR SELECT
  USING (user_id = auth.uid());

-- Order items: Through order access
CREATE POLICY "Access order items through order"
  ON order_items FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM orders
      WHERE orders.id = order_items.order_id
      AND (orders.user_id = auth.uid() OR is_admin())
    )
  );

-- Profiles
CREATE POLICY "Users view own profile"
  ON profiles FOR SELECT
  USING (id = auth.uid() OR is_admin());

CREATE POLICY "Admin can update any profile"
  ON profiles FOR UPDATE
  USING (is_admin())
  WITH CHECK (is_admin());

---

Implementation

Project Structure

admin-dashboard/
├── app/
│   ├── layout.tsx
│   ├── page.tsx              # Dashboard home
│   ├── login/
│   │   └── page.tsx
│   ├── products/
│   │   ├── page.tsx          # Product list
│   │   ├── new/
│   │   │   └── page.tsx      # Create product
│   │   └── [id]/
│   │       ├── page.tsx      # View product
│   │       └── edit/
│   │           └── page.tsx  # Edit product
│   └── orders/
│       ├── page.tsx          # Order list
│       └── [id]/
│           └── page.tsx      # Order detail
├── components/
│   ├── layout/
│   │   ├── Sidebar.tsx
│   │   ├── Header.tsx
│   │   └── AdminGuard.tsx
│   ├── products/
│   │   ├── ProductTable.tsx
│   │   ├── ProductForm.tsx
│   │   └── ProductCard.tsx
│   └── orders/
│       ├── OrderTable.tsx
│       └── OrderStatusBadge.tsx
├── lib/
│   ├── supabase/
│   │   ├── server.ts
│   │   └── client.ts
│   └── types.ts
└── hooks/
    ├── use-products.ts
    └── use-orders.ts

Admin Guard Component

// components/layout/AdminGuard.tsx
import { redirect } from 'next/navigation';
import { createClient } from '@/lib/supabase/server';

export async function AdminGuard({ children }: { children: React.ReactNode }) {
  const supabase = await createClient();

  const { data: { user } } = await supabase.auth.getUser();

  if (!user) {
    redirect('/login');
  }

  // Check admin role
  const { data: profile } = await supabase
    .from('profiles')
    .select('role')
    .eq('id', user.id)
    .single();

  if (!profile || !['admin', 'super_admin'].includes(profile.role)) {
    redirect('/unauthorized');
  }

  return <>{children}</>;
}

Product List (Server Component)

// app/products/page.tsx
import { createClient } from '@/lib/supabase/server';
import { ProductTable } from '@/components/products/ProductTable';
import { AdminGuard } from '@/components/layout/AdminGuard';
import Link from 'next/link';

export default async function ProductsPage({
  searchParams
}: {
  searchParams: { page?: string; search?: string }
}) {
  return (
    <AdminGuard>
      <ProductsContent searchParams={searchParams} />
    </AdminGuard>
  );
}

async function ProductsContent({
  searchParams
}: {
  searchParams: { page?: string; search?: string }
}) {
  const supabase = await createClient();
  const page = parseInt(searchParams.page || '1');
  const pageSize = 10;
  const offset = (page - 1) * pageSize;

  // Build query using REST API patterns
  let query = supabase
    .from('products')
    .select('*', { count: 'exact' });

  // Search filter
  if (searchParams.search) {
    query = query.ilike('name', `%${searchParams.search}%`);
  }

  // Pagination
  const { data: products, count, error } = await query
    .order('created_at', { ascending: false })
    .range(offset, offset + pageSize - 1);

  if (error) {
    console.error('Error fetching products:', error);
    return <div>Error loading products</div>;
  }

  const totalPages = Math.ceil((count || 0) / pageSize);

  return (
    <div className="p-6">
      <div className="flex justify-between items-center mb-6">
        <h1 className="text-2xl font-bold">Products</h1>
        <Link
          href="/products/new"
          className="bg-blue-600 text-white px-4 py-2 rounded-lg"
        >
          Add Product
        </Link>
      </div>

      <ProductTable products={products || []} />

      <div className="mt-4 flex justify-center gap-2">
        {Array.from({ length: totalPages }, (_, i) => (
          <Link
            key={i + 1}
            href={`/products?page=${i + 1}`}
            className={`px-3 py-1 rounded ${
              page === i + 1 ? 'bg-blue-600 text-white' : 'bg-gray-200'
            }`}
          >
            {i + 1}
          </Link>
        ))}
      </div>
    </div>
  );
}

Product Form (Client Component)

// components/products/ProductForm.tsx
'use client';

import { useState } from 'react';
import { createClient } from '@/lib/supabase/client';
import { useRouter } from 'next/navigation';

interface ProductFormProps {
  product?: {
    id: string;
    name: string;
    description: string;
    price: number;
    stock: number;
    category: string;
    is_active: boolean;
  };
}

export function ProductForm({ product }: ProductFormProps) {
  const [loading, setLoading] = useState(false);
  const [error, setError] = useState('');
  const router = useRouter();
  const supabase = createClient();

  const isEditing = !!product;

  const handleSubmit = async (e: React.FormEvent<HTMLFormElement>) => {
    e.preventDefault();
    setLoading(true);
    setError('');

    const formData = new FormData(e.currentTarget);
    const data = {
      name: formData.get('name') as string,
      description: formData.get('description') as string,
      price: parseFloat(formData.get('price') as string),
      stock: parseInt(formData.get('stock') as string),
      category: formData.get('category') as string,
      is_active: formData.get('is_active') === 'on',
    };

    try {
      if (isEditing) {
        // UPDATE via REST API
        const { error } = await supabase
          .from('products')
          .update(data)
          .eq('id', product.id);

        if (error) throw error;
      } else {
        // INSERT via REST API
        const { error } = await supabase
          .from('products')
          .insert(data);

        if (error) throw error;
      }

      router.push('/products');
      router.refresh();
    } catch (err: any) {
      setError(err.message);
    } finally {
      setLoading(false);
    }
  };

  const handleDelete = async () => {
    if (!product || !confirm('Are you sure?')) return;

    setLoading(true);
    try {
      // DELETE via REST API
      const { error } = await supabase
        .from('products')
        .delete()
        .eq('id', product.id);

      if (error) throw error;

      router.push('/products');
      router.refresh();
    } catch (err: any) {
      setError(err.message);
    } finally {
      setLoading(false);
    }
  };

  return (
    <form onSubmit={handleSubmit} className="max-w-lg space-y-4">
      {error && (
        <div className="bg-red-100 text-red-700 p-3 rounded">
          {error}
        </div>
      )}

      <div>
        <label className="block text-sm font-medium mb-1">Name</label>
        <input
          name="name"
          defaultValue={product?.name}
          required
          className="w-full px-3 py-2 border rounded-lg"
        />
      </div>

      <div>
        <label className="block text-sm font-medium mb-1">Description</label>
        <textarea
          name="description"
          defaultValue={product?.description}
          rows={3}
          className="w-full px-3 py-2 border rounded-lg"
        />
      </div>

      <div className="grid grid-cols-2 gap-4">
        <div>
          <label className="block text-sm font-medium mb-1">Price</label>
          <input
            name="price"
            type="number"
            step="0.01"
            defaultValue={product?.price}
            required
            className="w-full px-3 py-2 border rounded-lg"
          />
        </div>

        <div>
          <label className="block text-sm font-medium mb-1">Stock</label>
          <input
            name="stock"
            type="number"
            defaultValue={product?.stock || 0}
            required
            className="w-full px-3 py-2 border rounded-lg"
          />
        </div>
      </div>

      <div>
        <label className="block text-sm font-medium mb-1">Category</label>
        <select
          name="category"
          defaultValue={product?.category}
          className="w-full px-3 py-2 border rounded-lg"
        >
          <option value="">Select category</option>
          <option value="electronics">Electronics</option>
          <option value="clothing">Clothing</option>
          <option value="books">Books</option>
        </select>
      </div>

      <div className="flex items-center gap-2">
        <input
          name="is_active"
          type="checkbox"
          defaultChecked={product?.is_active ?? true}
          className="w-4 h-4"
        />
        <label className="text-sm">Active</label>
      </div>

      <div className="flex gap-4">
        <button
          type="submit"
          disabled={loading}
          className="bg-blue-600 text-white px-6 py-2 rounded-lg disabled:opacity-50"
        >
          {loading ? 'Saving...' : isEditing ? 'Update' : 'Create'}
        </button>

        {isEditing && (
          <button
            type="button"
            onClick={handleDelete}
            disabled={loading}
            className="bg-red-600 text-white px-6 py-2 rounded-lg disabled:opacity-50"
          >
            Delete
          </button>
        )}
      </div>
    </form>
  );
}

Dashboard Analytics

// app/page.tsx (Dashboard)
import { createClient } from '@/lib/supabase/server';
import { AdminGuard } from '@/components/layout/AdminGuard';

export default async function DashboardPage() {
  return (
    <AdminGuard>
      <DashboardContent />
    </AdminGuard>
  );
}

async function DashboardContent() {
  const supabase = await createClient();

  // Fetch stats in parallel
  const [
    { count: productCount },
    { count: orderCount },
    { data: recentOrders },
    { data: orderStats }
  ] = await Promise.all([
    supabase.from('products').select('*', { count: 'exact', head: true }),
    supabase.from('orders').select('*', { count: 'exact', head: true }),
    supabase
      .from('orders')
      .select('id, status, total, created_at')
      .order('created_at', { ascending: false })
      .limit(5),
    supabase.rpc('get_order_stats'),
  ]);

  return (
    <div className="p-6">
      <h1 className="text-2xl font-bold mb-6">Dashboard</h1>

      {/* Stats Grid */}
      <div className="grid grid-cols-4 gap-4 mb-8">
        <StatCard title="Products" value={productCount || 0} />
        <StatCard title="Total Orders" value={orderCount || 0} />
        <StatCard title="Revenue" value={`$${orderStats?.total_revenue || 0}`} />
        <StatCard title="Pending Orders" value={orderStats?.pending_count || 0} />
      </div>

      {/* Recent Orders */}
      <div className="bg-white rounded-lg shadow p-4">
        <h2 className="text-lg font-semibold mb-4">Recent Orders</h2>
        <table className="w-full">
          <thead>
            <tr className="border-b">
              <th className="text-left py-2">Order ID</th>
              <th className="text-left py-2">Status</th>
              <th className="text-left py-2">Total</th>
              <th className="text-left py-2">Date</th>
            </tr>
          </thead>
          <tbody>
            {recentOrders?.map((order) => (
              <tr key={order.id} className="border-b">
                <td className="py-2">{order.id.slice(0, 8)}...</td>
                <td className="py-2">
                  <span className={`px-2 py-1 rounded text-xs ${
                    order.status === 'delivered' ? 'bg-green-100 text-green-700' :
                    order.status === 'pending' ? 'bg-yellow-100 text-yellow-700' :
                    'bg-gray-100'
                  }`}>
                    {order.status}
                  </span>
                </td>
                <td className="py-2">${order.total}</td>
                <td className="py-2">
                  {new Date(order.created_at).toLocaleDateString()}
                </td>
              </tr>
            ))}
          </tbody>
        </table>
      </div>
    </div>
  );
}

function StatCard({ title, value }: { title: string; value: string | number }) {
  return (
    <div className="bg-white rounded-lg shadow p-4">
      <p className="text-gray-500 text-sm">{title}</p>
      <p className="text-2xl font-bold">{value}</p>
    </div>
  );
}

---

REST API Patterns Used

CRUD Operations

// CREATE
const { data, error } = await supabase
  .from('products')
  .insert({ name: 'New Product', price: 99.99 })
  .select()
  .single();

// READ (list with filters)
const { data, error } = await supabase
  .from('products')
  .select('*')
  .eq('is_active', true)
  .ilike('name', '%search%')
  .order('created_at', { ascending: false })
  .range(0, 9);

// READ (single)
const { data, error } = await supabase
  .from('products')
  .select('*')
  .eq('id', productId)
  .single();

// UPDATE
const { error } = await supabase
  .from('products')
  .update({ price: 149.99, stock: 50 })
  .eq('id', productId);

// DELETE
const { error } = await supabase
  .from('products')
  .delete()
  .eq('id', productId);

Aggregations via RPC

-- Database function for stats
CREATE OR REPLACE FUNCTION get_order_stats()
RETURNS TABLE (
  total_revenue NUMERIC,
  pending_count BIGINT,
  processing_count BIGINT,
  delivered_count BIGINT
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    COALESCE(SUM(total), 0),
    COUNT(*) FILTER (WHERE status = 'pending'),
    COUNT(*) FILTER (WHERE status = 'processing'),
    COUNT(*) FILTER (WHERE status = 'delivered')
  FROM orders;
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;

---

Kết quả

Features hoàn thành

• Admin authentication + authorization
• Product CRUD với search & pagination
• Order management với status updates
• Dashboard với real-time stats
• Responsive design

Chi phí

• Supabase Pro: $25/month (for production)
• Cloudflare: Free tier
• Total: $25/month

---

Lessons Learned

What Worked

• PostgREST auto-generate API = zero backend code
• RLS = security at database level
• Server Components = fast initial load
• Parallel queries = better performance

Challenges

• RLS policy debugging requires care
• Complex queries may need RPC functions
• Client-side vs Server-side data fetching decision

---

Tài liệu tham khảo

• Supabase PostgREST
• RLS Policies
• Next.js Data Fetching