Learning Objectives

Design proper database relationships
Implement foreign key constraints
Write complex JOIN queries
Use Supabase's nested select syntax
Optimize queries for performance

1. Types of Relationships

One-to-One (1:1)

Each record in Table A relates to exactly one record in Table B.

In a one-to-one relationship, you can either use the same primary key for both tables or use a UNIQUE constraint on the foreign key column. Here are two approaches:

-- Example: User has one profile
CREATE TABLE profiles (
  id UUID PRIMARY KEY REFERENCES users(id),  -- Same ID as user
  bio TEXT,
  website TEXT,
  -- ...
);

-- Or with separate ID
CREATE TABLE user_settings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID UNIQUE REFERENCES users(id),  -- UNIQUE enforces 1:1
  theme VARCHAR(20),
  -- ...
);

One-to-Many (1:N)

Each record in Table A can relate to many records in Table B.

In a one-to-many relationship, the "many" side contains a foreign key pointing to the "one" side. This is the most common relationship type in databases:

-- Example: User has many transactions
-- users (1) → transactions (N)
CREATE TABLE transactions (
  id UUID PRIMARY KEY,
  user_id UUID REFERENCES users(id),  -- Many transactions per user
  amount DECIMAL,
  -- ...
);

Many-to-Many (M:N)

Records in Table A can relate to many in Table B, and vice versa.

Many-to-many relationships require a junction table (also called a bridge or join table) that contains foreign keys to both related tables. The composite primary key ensures uniqueness:

-- Example: Users can have many favorite products
-- Products can be favorited by many users
CREATE TABLE user_favorite_products (
  user_id UUID REFERENCES users(id),
  product_id UUID REFERENCES products(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  PRIMARY KEY (user_id, product_id)  -- Composite primary key
);

2. Foreign Key Constraints

Basic Foreign Key

Foreign keys establish relationships between tables and ensure referential integrity. You can define them inline with the column or as a separate constraint with a custom name:

-- Create child table with foreign key
CREATE TABLE wallets (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users(id),
  balance DECIMAL
);

-- Explicit constraint name
CREATE TABLE transactions (
  id UUID PRIMARY KEY,
  wallet_id UUID NOT NULL,
  CONSTRAINT fk_wallet
    FOREIGN KEY (wallet_id)
    REFERENCES wallets(id)
);

Referential Actions

Referential actions define what happens when a referenced row is deleted or updated. Choose the appropriate action based on your data model and business requirements:

-- ON DELETE options
CREATE TABLE transactions (
  id UUID PRIMARY KEY,
  wallet_id UUID REFERENCES wallets(id)
    ON DELETE CASCADE,      -- Delete transactions when wallet deleted
    -- ON DELETE SET NULL,  -- Set wallet_id to NULL
    -- ON DELETE RESTRICT,  -- Prevent deletion if transactions exist
    -- ON DELETE NO ACTION, -- Same as RESTRICT (default)

  product_id UUID REFERENCES products(id)
    ON DELETE SET NULL      -- Keep transaction, clear product reference
);

-- ON UPDATE options
CREATE TABLE beneficiaries (
  id UUID PRIMARY KEY,
  user_id UUID REFERENCES users(id)
    ON UPDATE CASCADE       -- Update if user ID changes
);

Foreign Key Best Practices

Always create indexes on foreign key columns to improve JOIN performance. Without indexes, the database must scan the entire table for each JOIN operation:

-- Always index foreign keys for JOIN performance
CREATE INDEX idx_transactions_wallet_id ON transactions(wallet_id);
CREATE INDEX idx_transactions_user_id ON transactions(user_id);
CREATE INDEX idx_wallets_user_id ON wallets(user_id);

3. RizPay Relationships Diagram

This diagram shows all the table relationships in our bill payment platform. Arrows indicate foreign key relationships, with the arrow pointing from the child table to the parent table:

                    ┌─────────────┐
                    │   users     │
                    │─────────────│
                    │ id (PK)     │
                    │ auth_id     │
                    │ email       │
                    └──────┬──────┘
                           │
           ┌───────────────┼───────────────┐
           │               │               │
           ▼               ▼               ▼
    ┌─────────────┐ ┌─────────────┐ ┌──────────────┐
    │   wallets   │ │beneficiaries│ │  (future)    │
    │─────────────│ │─────────────│ │   KYC docs   │
    │ id (PK)     │ │ id (PK)     │ └──────────────┘
    │ user_id (FK)│ │ user_id (FK)│
    │ balance     │ │ account_num │
    └──────┬──────┘ │ product_id  │──────┐
           │        └─────────────┘      │
           │                             │
           ▼                             ▼
    ┌─────────────┐               ┌─────────────┐
    │transactions │               │  products   │
    │─────────────│               │─────────────│
    │ id (PK)     │               │ id (PK)     │
    │ wallet_id   │───────────────│ name        │
    │ user_id (FK)│               │ category    │
    │ product_id  │───────────────│ provider    │
    │ amount      │               └─────────────┘
    └─────────────┘

4. JOIN Queries

INNER JOIN

Returns only matching rows from both tables.

The INNER JOIN retrieves transactions along with user information, but only where a matching user exists. This query also filters by status and orders results by date:

-- Get transactions with user info
SELECT
  t.id,
  t.reference,
  t.amount,
  t.status,
  t.created_at,
  u.full_name,
  u.email
FROM transactions t
INNER JOIN users u ON t.user_id = u.id
WHERE t.status = 'completed'
ORDER BY t.created_at DESC;

LEFT JOIN

Returns all rows from left table, matching from right.

LEFT JOIN is useful when you want to include all records from the left table, even if there are no matching records in the right table. COALESCE provides a default value when the join produces NULL:

-- Get all users with their wallet balance (including users without wallets)
SELECT
  u.id,
  u.full_name,
  u.email,
  COALESCE(w.balance, 0) as balance,
  w.currency
FROM users u
LEFT JOIN wallets w ON u.id = w.user_id
ORDER BY u.created_at;

-- Get products with transaction counts
SELECT
  p.id,
  p.name,
  p.provider,
  COUNT(t.id) as transaction_count,
  COALESCE(SUM(t.amount), 0) as total_amount
FROM products p
LEFT JOIN transactions t ON p.id = t.product_id
GROUP BY p.id, p.name, p.provider
ORDER BY transaction_count DESC;

Multiple JOINs

You can chain multiple JOINs to fetch data from several related tables in a single query. This example retrieves a comprehensive transaction history with user, wallet, and product information:

-- Get detailed transaction history
SELECT
  t.id,
  t.reference,
  t.amount,
  t.status,
  t.bill_account,
  t.created_at,
  u.full_name as user_name,
  w.balance as current_balance,
  p.name as product_name,
  p.provider,
  p.category
FROM transactions t
INNER JOIN users u ON t.user_id = u.id
INNER JOIN wallets w ON t.wallet_id = w.id
LEFT JOIN products p ON t.product_id = p.id
WHERE t.user_id = 'user-uuid-here'
ORDER BY t.created_at DESC
LIMIT 20;

Self JOIN

A self JOIN joins a table with itself, useful for hierarchical data or when rows reference other rows in the same table. This example finds the referrer for each transaction's user:

-- Get transactions with referral info (if user was referred)
SELECT
  t.*,
  referrer.full_name as referred_by
FROM transactions t
INNER JOIN users u ON t.user_id = u.id
LEFT JOIN users referrer ON u.referred_by = referrer.id;

5. Supabase Nested Selects

Supabase provides a convenient syntax for fetching related data.

Basic Nested Select

Instead of writing complex JOINs, Supabase allows you to fetch related data using a nested syntax that mirrors your data structure. The related table name in parentheses fetches the associated records:

// Fetch user with wallet
const { data: user } = await supabase
  .from('users')
  .select(`
    id,
    full_name,
    email,
    wallets (
      id,
      balance,
      currency
    )
  `)
  .eq('auth_id', authId)
  .single()

// Result:
// {
//   id: 'user-uuid',
//   full_name: 'John Doe',
//   email: 'john@example.com',
//   wallets: [{ id: 'wallet-uuid', balance: 5000, currency: 'NGN' }]
// }

Multiple Relations

You can fetch multiple related tables in a single query. Using a colon alias (e.g., user:users) gives the result a more readable property name:

// Fetch transaction with all related data
const { data: transactions } = await supabase
  .from('transactions')
  .select(`
    id,
    reference,
    amount,
    status,
    created_at,
    user:users (
      id,
      full_name,
      email
    ),
    wallet:wallets (
      id,
      balance
    ),
    product:products (
      id,
      name,
      provider,
      category
    )
  `)
  .order('created_at', { ascending: false })
  .limit(10)

Filtering on Relations

The !inner modifier converts a LEFT JOIN to an INNER JOIN, excluding parent records that don't have matching related records. You can also filter based on related table columns:

// Get users who have wallets with balance > 10000
const { data } = await supabase
  .from('users')
  .select(`
    *,
    wallets!inner (
      balance,
      currency
    )
  `)
  .gt('wallets.balance', 10000)

// !inner makes it an INNER JOIN (excludes users without matching wallets)

Nested Filtering

You can filter related data without affecting the parent query. This returns the user but only includes their completed transactions in the nested result:

// Get user with only completed transactions
const { data: user } = await supabase
  .from('users')
  .select(`
    id,
    full_name,
    transactions (
      id,
      amount,
      status
    )
  `)
  .eq('id', userId)
  .eq('transactions.status', 'completed')
  .single()

6. Complex Query Patterns

Transaction Summary by Product

This aggregation query groups transactions by product and calculates various statistics. LEFT JOIN ensures all products appear even without transactions, and NULLS LAST puts products with no transactions at the end:

-- Get transaction totals grouped by product
SELECT
  p.name as product_name,
  p.provider,
  p.category,
  COUNT(t.id) as transaction_count,
  SUM(t.amount) as total_amount,
  AVG(t.amount) as average_amount,
  MAX(t.amount) as max_amount,
  MIN(t.amount) as min_amount
FROM products p
LEFT JOIN transactions t ON p.id = t.product_id
  AND t.status = 'completed'
GROUP BY p.id, p.name, p.provider, p.category
ORDER BY total_amount DESC NULLS LAST;

User Transaction History with Running Balance

Window functions allow calculations across a set of related rows. This query calculates a running total using SUM with OVER, partitioned by user and ordered by date:

-- Calculate running balance
SELECT
  t.id,
  t.reference,
  t.type,
  t.amount,
  t.balance_after,
  t.created_at,
  SUM(
    CASE
      WHEN t.type = 'credit' THEN t.amount
      ELSE -t.amount
    END
  ) OVER (
    PARTITION BY t.user_id
    ORDER BY t.created_at
  ) as running_total
FROM transactions t
WHERE t.user_id = 'user-uuid'
  AND t.status = 'completed'
ORDER BY t.created_at;

Monthly Transaction Report

DATE_TRUNC groups timestamps by a specified interval (month, week, day, etc.). The FILTER clause provides an elegant way to calculate conditional aggregates within the same query:

-- Monthly summary
SELECT
  DATE_TRUNC('month', t.created_at) as month,
  COUNT(*) as transaction_count,
  COUNT(*) FILTER (WHERE t.type = 'credit') as credits,
  COUNT(*) FILTER (WHERE t.type = 'debit') as debits,
  SUM(CASE WHEN t.type = 'credit' THEN t.amount ELSE 0 END) as total_credits,
  SUM(CASE WHEN t.type = 'debit' THEN t.amount ELSE 0 END) as total_debits
FROM transactions t
WHERE t.user_id = 'user-uuid'
  AND t.status = 'completed'
  AND t.created_at >= NOW() - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', t.created_at)
ORDER BY month DESC;

7. Implementing in TypeScript

Dashboard Data Fetching

This server component fetches all the data needed for a dashboard page. It uses parallel queries where possible and nested selects to efficiently load user, wallet, transactions, and statistics:

// app/(dashboard)/page.tsx
import { createClient } from '@/lib/supabase/server'

interface DashboardData {
  user: {
    id: string
    full_name: string
    email: string
  }
  wallet: {
    balance: number
    currency: string
  }
  recentTransactions: Array<{
    id: string
    amount: number
    type: string
    status: string
    created_at: string
    product: {
      name: string
      provider: string
    } | null
  }>
  stats: {
    totalTransactions: number
    monthlySpending: number
  }
}

export default async function DashboardPage() {
  const supabase = await createClient()

  // Get authenticated user
  const { data: { user: authUser } } = await supabase.auth.getUser()

  if (!authUser) {
    redirect('/login')
  }

  // Fetch user with wallet
  const { data: userData } = await supabase
    .from('users')
    .select(`
      id,
      full_name,
      email,
      wallets (
        id,
        balance,
        currency
      )
    `)
    .eq('auth_id', authUser.id)
    .single()

  // Fetch recent transactions
  const { data: transactions } = await supabase
    .from('transactions')
    .select(`
      id,
      amount,
      type,
      status,
      created_at,
      product:products (
        name,
        provider
      )
    `)
    .eq('user_id', userData?.id)
    .order('created_at', { ascending: false })
    .limit(5)

  // Fetch monthly stats
  const startOfMonth = new Date()
  startOfMonth.setDate(1)
  startOfMonth.setHours(0, 0, 0, 0)

  const { data: monthlyStats } = await supabase
    .from('transactions')
    .select('amount')
    .eq('user_id', userData?.id)
    .eq('type', 'debit')
    .eq('status', 'completed')
    .gte('created_at', startOfMonth.toISOString())

  const monthlySpending = monthlyStats?.reduce(
    (sum, t) => sum + t.amount, 0
  ) || 0

  return (
    <div>
      <WalletCard wallet={userData?.wallets?.[0]} />
      <TransactionList transactions={transactions} />
      <StatsCard
        totalTransactions={transactions?.length || 0}
        monthlySpending={monthlySpending}
      />
    </div>
  )
}

Transaction Details Page

Dynamic routes in Next.js allow you to fetch a specific record by its ID. The nested select retrieves the transaction along with all related data in a single query:

// app/(dashboard)/transactions/[id]/page.tsx
import { createClient } from '@/lib/supabase/server'
import { notFound } from 'next/navigation'

interface PageProps {
  params: Promise<{ id: string }>
}

export default async function TransactionDetailPage({ params }: PageProps) {
  const { id } = await params
  const supabase = await createClient()

  const { data: transaction, error } = await supabase
    .from('transactions')
    .select(`
      *,
      user:users (
        id,
        full_name,
        email,
        phone
      ),
      wallet:wallets (
        id,
        currency
      ),
      product:products (
        id,
        name,
        provider,
        category,
        icon_url
      )
    `)
    .eq('id', id)
    .single()

  if (error || !transaction) {
    notFound()
  }

  return (
    <div className="max-w-2xl mx-auto">
      <TransactionReceipt transaction={transaction} />
    </div>
  )
}

8. Database Functions for Complex Operations

Create Transaction with Relations

This PostgreSQL function encapsulates a complex operation that spans multiple tables. It validates inputs, updates balances, creates records, and automatically saves beneficiaries, all in a single atomic transaction:

CREATE OR REPLACE FUNCTION create_bill_payment(
  p_product_id UUID,
  p_amount DECIMAL,
  p_bill_account TEXT,
  p_metadata JSONB DEFAULT '{}'
)
RETURNS TABLE (
  transaction_id UUID,
  reference TEXT,
  new_balance DECIMAL
) AS $$
DECLARE
  v_user_id UUID;
  v_wallet_id UUID;
  v_balance DECIMAL;
  v_tx_id UUID;
  v_reference TEXT;
  v_product products%ROWTYPE;
BEGIN
  -- Get user and wallet
  SELECT u.id, w.id, w.balance
  INTO v_user_id, v_wallet_id, v_balance
  FROM users u
  JOIN wallets w ON w.user_id = u.id AND w.is_active = true
  WHERE u.auth_id = auth.uid();

  IF v_wallet_id IS NULL THEN
    RAISE EXCEPTION 'Active wallet not found';
  END IF;

  -- Get product
  SELECT * INTO v_product FROM products WHERE id = p_product_id;

  IF v_product IS NULL THEN
    RAISE EXCEPTION 'Product not found';
  END IF;

  -- Validate amount
  IF p_amount < v_product.min_amount OR p_amount > v_product.max_amount THEN
    RAISE EXCEPTION 'Amount out of range';
  END IF;

  IF v_balance < p_amount THEN
    RAISE EXCEPTION 'Insufficient balance';
  END IF;

  -- Generate reference
  v_reference := 'TXN' || TO_CHAR(NOW(), 'YYYYMMDD') ||
                 UPPER(SUBSTRING(gen_random_uuid()::text, 1, 8));

  -- Deduct balance
  UPDATE wallets
  SET balance = balance - p_amount, updated_at = NOW()
  WHERE id = v_wallet_id;

  -- Create transaction
  INSERT INTO transactions (
    reference, wallet_id, user_id, type, status,
    amount, fee, total_amount,
    balance_before, balance_after,
    product_id, bill_account, metadata
  ) VALUES (
    v_reference, v_wallet_id, v_user_id, 'debit', 'pending',
    p_amount, 0, p_amount,
    v_balance, v_balance - p_amount,
    p_product_id, p_bill_account, p_metadata
  )
  RETURNING id INTO v_tx_id;

  -- Auto-save beneficiary
  INSERT INTO beneficiaries (user_id, name, account_number, product_id, category, provider)
  VALUES (v_user_id, p_bill_account, p_bill_account, p_product_id, v_product.category, v_product.provider)
  ON CONFLICT (user_id, account_number, category) DO UPDATE
  SET usage_count = beneficiaries.usage_count + 1,
      last_used_at = NOW();

  RETURN QUERY
  SELECT v_tx_id, v_reference, v_balance - p_amount;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Call from TypeScript

Server Actions in Next.js provide a clean way to call database functions. The rpc method invokes the PostgreSQL function and handles the response:

// app/actions/transactions.ts
'use server'

import { createClient } from '@/lib/supabase/server'

export async function createBillPayment(
  productId: string,
  amount: number,
  billAccount: string,
  metadata?: Record<string, unknown>
) {
  const supabase = await createClient()

  const { data, error } = await supabase
    .rpc('create_bill_payment', {
      p_product_id: productId,
      p_amount: amount,
      p_bill_account: billAccount,
      p_metadata: metadata || {},
    })

  if (error) {
    return { error: error.message }
  }

  return { data: data[0] }
}

9. Query Optimization

Use EXPLAIN ANALYZE

EXPLAIN ANALYZE shows the actual execution plan and timing for a query. This is essential for identifying performance bottlenecks and understanding how PostgreSQL processes your queries:

-- Analyze query performance
EXPLAIN ANALYZE
SELECT t.*, p.name
FROM transactions t
LEFT JOIN products p ON t.product_id = p.id
WHERE t.user_id = 'user-uuid'
ORDER BY t.created_at DESC
LIMIT 10;

Optimize with Indexes

Strategic indexes dramatically improve query performance. Create composite indexes for queries that filter on multiple columns, and match the index order to your query patterns:

-- Create indexes for common query patterns
CREATE INDEX idx_transactions_user_created
  ON transactions(user_id, created_at DESC);

CREATE INDEX idx_transactions_wallet_status
  ON transactions(wallet_id, status);

CREATE INDEX idx_beneficiaries_user_category
  ON beneficiaries(user_id, category);

Pagination Best Practices

Cursor-based pagination is more efficient than offset-based pagination, especially for large datasets. Using a timestamp or ID as a cursor avoids the performance degradation of large OFFSET values:

// Cursor-based pagination (more efficient than offset)
const { data } = await supabase
  .from('transactions')
  .select('*')
  .eq('user_id', userId)
  .lt('created_at', lastSeenTimestamp)  // Cursor
  .order('created_at', { ascending: false })
  .limit(10)

// Avoid large offsets
// Bad: .range(10000, 10010)
// Good: Use cursor-based pagination

10. Common Patterns

Eager Loading Related Data

Fetching all related data in a single query prevents the N+1 query problem, where you would otherwise make a separate query for each related record:

// Load all related data in one query
const { data: user } = await supabase
  .from('users')
  .select(`
    *,
    wallets (*),
    beneficiaries (
      *,
      product:products (name, provider)
    ),
    transactions (
      *,
      product:products (name, category)
    )
  `)
  .eq('id', userId)
  .single()

Counting Relations

Supabase can return counts of related records alongside your data. The count option in the select returns the total number of matching records:

// Get count of related records
const { data, count } = await supabase
  .from('users')
  .select('*, transactions(count)', { count: 'exact' })
  .single()

// Result includes transaction count

Soft Deletes with Relations

Instead of permanently deleting records, soft deletes set a timestamp to mark records as deleted. This preserves data integrity and allows for recovery:

-- When soft-deleting, cascade to related queries
UPDATE beneficiaries
SET deleted_at = NOW()
WHERE user_id = 'user-uuid';

Assignment

Task: Build RizPay Data Access Layer

Implement complex queries for the platform:

Dashboard Queries:
- User with wallet balance
- Last 5 transactions with product info
- Monthly spending total
Transaction Queries:
- Full transaction history with pagination
- Filter by date range, status, category
- Transaction detail with all relations
Beneficiaries:
- List with product info
- Most used beneficiaries
- Auto-complete search
Analytics (SQL Functions):
- Monthly spending by category
- Transaction trends
- Top products used
Optimization:
- Create necessary indexes
- Test query performance
- Implement cursor pagination

Deliverables

Database functions in Supabase
TypeScript data fetching utilities
Optimized indexes
Query performance report

Additional Resources

Next Lesson: Supabase Auth Setup