Learning Objectives

Design normalized database schemas
Create tables with proper relationships
Use appropriate data types and constraints
Implement audit trails and timestamps
Plan for scalability and data integrity

1. Database Design Principles

Normalization

Normalization is a systematic approach to organizing database tables to reduce redundancy and improve data integrity. Following these normal forms helps ensure your data remains consistent.

Normal Form	Rule
1NF	Each column contains atomic values
2NF	All non-key columns depend on the entire primary key
3NF	No transitive dependencies between non-key columns

Best Practices

These design practices have proven effective for building scalable and maintainable database schemas. Apply them consistently across all your tables.

Use UUID for primary keys (better for distributed systems)
Add timestamps for auditing (created_at, updated_at)
Use soft deletes for important data (deleted_at instead of DELETE)
Define constraints for data integrity
Plan indexes for common queries

2. RizPay Schema Overview

The following diagram shows the relationships between the core tables in our bill payment platform. Arrows indicate foreign key relationships, with the arrow pointing to the referenced table.

┌─────────────┐     ┌─────────────┐     ┌──────────────────┐
│   users     │────<│   wallets   │────<│  transactions    │
└─────────────┘     └─────────────┘     └──────────────────┘
       │                                         │
       │            ┌─────────────┐              │
       │            │  products   │──────────────┘
       │            └─────────────┘
       │                   │
       │            ┌──────────────────┐
       └───────────>│   beneficiaries  │
                    └──────────────────┘

Core Tables

Each table serves a specific purpose in the application:

users - User accounts and profiles
wallets - User wallet with balance
transactions - All transaction records
products - Bill payment products (airtime, data, etc.)
beneficiaries - Saved payment recipients

3. Users Table

The users table stores account information and links to Supabase Auth. It includes fields for profile data, verification status, and KYC compliance. The auth_id foreign key connects to Supabase's built-in auth.users table.

-- Create users table
CREATE TABLE public.users (
  -- Primary key
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Authentication (linked to Supabase Auth)
  auth_id UUID UNIQUE REFERENCES auth.users(id) ON DELETE CASCADE,

  -- Profile information
  email VARCHAR(255) UNIQUE NOT NULL,
  phone VARCHAR(20) UNIQUE,
  full_name VARCHAR(100) NOT NULL,
  avatar_url TEXT,

  -- Account status
  is_verified BOOLEAN DEFAULT false,
  is_active BOOLEAN DEFAULT true,
  verification_token VARCHAR(255),

  -- KYC (Know Your Customer) - for compliance
  bvn VARCHAR(11),              -- Bank Verification Number
  bvn_verified BOOLEAN DEFAULT false,
  kyc_level INTEGER DEFAULT 0,  -- 0: None, 1: Basic, 2: Full

  -- Settings
  notification_preferences JSONB DEFAULT '{"email": true, "push": true, "sms": true}'::jsonb,

  -- Metadata
  last_login_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  deleted_at TIMESTAMPTZ          -- Soft delete
);

-- Indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_phone ON users(phone);
CREATE INDEX idx_users_auth_id ON users(auth_id);
CREATE INDEX idx_users_active ON users(is_active) WHERE is_active = true;

-- Updated at trigger
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

TypeScript Type

Define a corresponding TypeScript interface for type safety in your application code. This ensures consistency between your database schema and application logic.

// types/database.ts
export interface User {
  id: string;
  auth_id: string;
  email: string;
  phone: string | null;
  full_name: string;
  avatar_url: string | null;
  is_verified: boolean;
  is_active: boolean;
  kyc_level: 0 | 1 | 2;
  notification_preferences: {
    email: boolean;
    push: boolean;
    sms: boolean;
  };
  last_login_at: string | null;
  created_at: string;
  updated_at: string;
  deleted_at: string | null;
}

4. Wallets Table

The wallets table manages user balances and transaction limits. Each user can have one wallet per currency. The positive_balance constraint ensures wallets cannot go negative.

-- Create wallets table
CREATE TABLE public.wallets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Owner
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,

  -- Balance
  balance DECIMAL(15, 2) DEFAULT 0.00 NOT NULL,
  currency VARCHAR(3) DEFAULT 'NGN' NOT NULL,

  -- Limits
  daily_limit DECIMAL(15, 2) DEFAULT 100000.00,
  transaction_limit DECIMAL(15, 2) DEFAULT 50000.00,

  -- Status
  is_active BOOLEAN DEFAULT true,
  is_frozen BOOLEAN DEFAULT false,
  frozen_reason TEXT,

  -- Security
  pin_hash VARCHAR(255),
  pin_attempts INTEGER DEFAULT 0,
  pin_locked_until TIMESTAMPTZ,

  -- Metadata
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,

  -- Constraints
  CONSTRAINT positive_balance CHECK (balance >= 0),
  CONSTRAINT valid_currency CHECK (currency IN ('NGN', 'USD', 'GBP')),
  CONSTRAINT unique_user_currency UNIQUE (user_id, currency)
);

-- Indexes
CREATE INDEX idx_wallets_user_id ON wallets(user_id);
CREATE INDEX idx_wallets_active ON wallets(is_active) WHERE is_active = true;

-- Trigger for updated_at
CREATE TRIGGER update_wallets_updated_at
  BEFORE UPDATE ON wallets
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

TypeScript Type

The Wallet interface mirrors the database table structure. Note the union type for currency which restricts values to valid options.

export interface Wallet {
  id: string;
  user_id: string;
  balance: number;
  currency: "NGN" | "USD" | "GBP";
  daily_limit: number;
  transaction_limit: number;
  is_active: boolean;
  is_frozen: boolean;
  frozen_reason: string | null;
  created_at: string;
  updated_at: string;
}

5. Products Table

The products table stores bill payment service offerings. PostgreSQL enums ensure valid category and status values. The JSONB fields allow flexibility for provider-specific requirements.

-- Product categories enum
CREATE TYPE product_category AS ENUM (
  'airtime',
  'data',
  'electricity',
  'cable',
  'internet',
  'education',
  'betting',
  'other'
);

-- Product status enum
CREATE TYPE product_status AS ENUM (
  'active',
  'inactive',
  'coming_soon'
);

-- Create products table
CREATE TABLE public.products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Basic info
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(100) UNIQUE NOT NULL,
  description TEXT,
  category product_category NOT NULL,

  -- Provider info
  provider VARCHAR(50) NOT NULL,        -- MTN, Airtel, IKEDC, etc.
  provider_code VARCHAR(50),            -- Provider's internal code

  -- Pricing
  min_amount DECIMAL(10, 2) DEFAULT 0,
  max_amount DECIMAL(10, 2) DEFAULT 1000000,
  fixed_amounts DECIMAL(10, 2)[],       -- For fixed denomination products
  commission_rate DECIMAL(5, 4) DEFAULT 0, -- 0.0250 = 2.5%

  -- Display
  icon_url TEXT,
  color VARCHAR(7),                     -- Hex color e.g., #FFCC00
  display_order INTEGER DEFAULT 0,

  -- Status
  status product_status DEFAULT 'active',
  is_featured BOOLEAN DEFAULT false,

  -- Requirements
  required_fields JSONB DEFAULT '[]'::jsonb,  -- ["phone", "meter_number"]

  -- Metadata
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

-- Indexes
CREATE INDEX idx_products_category ON products(category);
CREATE INDEX idx_products_provider ON products(provider);
CREATE INDEX idx_products_status ON products(status);
CREATE INDEX idx_products_featured ON products(is_featured) WHERE is_featured = true;

-- Trigger for updated_at
CREATE TRIGGER update_products_updated_at
  BEFORE UPDATE ON products
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

-- Sample data
INSERT INTO products (name, slug, category, provider, min_amount, max_amount, icon_url, color) VALUES
  ('MTN Airtime', 'mtn-airtime', 'airtime', 'MTN', 50, 50000, '/icons/mtn.png', '#FFCC00'),
  ('Airtel Airtime', 'airtel-airtime', 'airtime', 'Airtel', 50, 50000, '/icons/airtel.png', '#FF0000'),
  ('Glo Airtime', 'glo-airtime', 'airtime', 'Glo', 50, 50000, '/icons/glo.png', '#00FF00'),
  ('9Mobile Airtime', '9mobile-airtime', 'airtime', '9Mobile', 50, 50000, '/icons/9mobile.png', '#006400'),
  ('MTN Data', 'mtn-data', 'data', 'MTN', 100, 100000, '/icons/mtn.png', '#FFCC00'),
  ('IKEDC Prepaid', 'ikedc-prepaid', 'electricity', 'IKEDC', 1000, 500000, '/icons/ikedc.png', '#1E40AF'),
  ('DSTV', 'dstv', 'cable', 'Multichoice', 1000, 50000, '/icons/dstv.png', '#003399');

TypeScript Type

Union types for category and status provide compile-time validation, ensuring only valid values are used throughout the application.

export type ProductCategory =
  | "airtime"
  | "data"
  | "electricity"
  | "cable"
  | "internet"
  | "education"
  | "betting"
  | "other";

export type ProductStatus = "active" | "inactive" | "coming_soon";

export interface Product {
  id: string;
  name: string;
  slug: string;
  description: string | null;
  category: ProductCategory;
  provider: string;
  provider_code: string | null;
  min_amount: number;
  max_amount: number;
  fixed_amounts: number[] | null;
  commission_rate: number;
  icon_url: string | null;
  color: string | null;
  display_order: number;
  status: ProductStatus;
  is_featured: boolean;
  required_fields: string[];
  metadata: Record<string, unknown>;
  created_at: string;
  updated_at: string;
}

6. Transactions Table

The transactions table is the heart of the bill payment system. It records every financial operation with comprehensive audit data including balance snapshots, provider responses, and timestamps.

-- Transaction type enum
CREATE TYPE transaction_type AS ENUM (
  'credit',      -- Money in
  'debit',       -- Money out
  'transfer',    -- Between wallets
  'reversal'     -- Refund
);

-- Transaction status enum
CREATE TYPE transaction_status AS ENUM (
  'pending',
  'processing',
  'completed',
  'failed',
  'reversed'
);

-- Create transactions table
CREATE TABLE public.transactions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Reference
  reference VARCHAR(50) UNIQUE NOT NULL,  -- Unique transaction reference

  -- Parties
  wallet_id UUID NOT NULL REFERENCES wallets(id),
  user_id UUID NOT NULL REFERENCES users(id),

  -- Transaction details
  type transaction_type NOT NULL,
  status transaction_status DEFAULT 'pending' NOT NULL,

  -- Amounts
  amount DECIMAL(15, 2) NOT NULL,
  fee DECIMAL(10, 2) DEFAULT 0,
  total_amount DECIMAL(15, 2) NOT NULL,  -- amount + fee

  -- Balance tracking
  balance_before DECIMAL(15, 2) NOT NULL,
  balance_after DECIMAL(15, 2) NOT NULL,

  -- Description
  description TEXT,
  narration VARCHAR(255),

  -- Product/Service info (for bill payments)
  product_id UUID REFERENCES products(id),
  beneficiary_id UUID,

  -- External reference (from payment provider)
  provider_reference VARCHAR(100),
  provider_response JSONB,

  -- Bill payment specific
  bill_account VARCHAR(50),      -- Phone number, meter number, etc.
  bill_data JSONB,               -- Additional bill data

  -- Metadata
  metadata JSONB DEFAULT '{}'::jsonb,
  ip_address INET,
  user_agent TEXT,

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  completed_at TIMESTAMPTZ,

  -- Constraints
  CONSTRAINT positive_amount CHECK (amount > 0),
  CONSTRAINT valid_balance CHECK (balance_after >= 0)
);

-- Indexes for common queries
CREATE INDEX idx_transactions_wallet_id ON transactions(wallet_id);
CREATE INDEX idx_transactions_user_id ON transactions(user_id);
CREATE INDEX idx_transactions_reference ON transactions(reference);
CREATE INDEX idx_transactions_status ON transactions(status);
CREATE INDEX idx_transactions_type ON transactions(type);
CREATE INDEX idx_transactions_created_at ON transactions(created_at DESC);
CREATE INDEX idx_transactions_product_id ON transactions(product_id);

-- Composite index for user transaction history
CREATE INDEX idx_transactions_user_history
  ON transactions(user_id, created_at DESC);

-- Trigger for updated_at
CREATE TRIGGER update_transactions_updated_at
  BEFORE UPDATE ON transactions
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

-- Function to generate transaction reference
CREATE OR REPLACE FUNCTION generate_transaction_reference()
RETURNS VARCHAR(50) AS $$
DECLARE
  ref VARCHAR(50);
BEGIN
  ref := 'TXN' || TO_CHAR(NOW(), 'YYYYMMDD') || UPPER(SUBSTRING(gen_random_uuid()::text, 1, 8));
  RETURN ref;
END;
$$ LANGUAGE plpgsql;

TypeScript Type

The Transaction interface captures all the data needed for auditing and reconciliation. Balance before/after fields enable transaction history reconstruction.

export type TransactionType = "credit" | "debit" | "transfer" | "reversal";
export type TransactionStatus =
  | "pending"
  | "processing"
  | "completed"
  | "failed"
  | "reversed";

export interface Transaction {
  id: string;
  reference: string;
  wallet_id: string;
  user_id: string;
  type: TransactionType;
  status: TransactionStatus;
  amount: number;
  fee: number;
  total_amount: number;
  balance_before: number;
  balance_after: number;
  description: string | null;
  narration: string | null;
  product_id: string | null;
  beneficiary_id: string | null;
  provider_reference: string | null;
  provider_response: Record<string, unknown> | null;
  bill_account: string | null;
  bill_data: Record<string, unknown> | null;
  metadata: Record<string, unknown>;
  ip_address: string | null;
  created_at: string;
  updated_at: string;
  completed_at: string | null;
}

7. Beneficiaries Table

The beneficiaries table stores frequently used payment recipients. Users can save their favorite phone numbers, meter numbers, and other account details for quick reuse in future transactions.

-- Create beneficiaries table (saved recipients)
CREATE TABLE public.beneficiaries (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Owner
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,

  -- Beneficiary info
  name VARCHAR(100) NOT NULL,            -- Display name
  account_number VARCHAR(50) NOT NULL,   -- Phone, meter, card number, etc.

  -- Product/Service
  product_id UUID REFERENCES products(id),
  category product_category NOT NULL,
  provider VARCHAR(50),

  -- Usage tracking
  is_favorite BOOLEAN DEFAULT false,
  usage_count INTEGER DEFAULT 0,
  last_used_at TIMESTAMPTZ,

  -- Metadata
  metadata JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,

  -- Unique constraint per user
  CONSTRAINT unique_user_beneficiary UNIQUE (user_id, account_number, category)
);

-- Indexes
CREATE INDEX idx_beneficiaries_user_id ON beneficiaries(user_id);
CREATE INDEX idx_beneficiaries_category ON beneficiaries(category);
CREATE INDEX idx_beneficiaries_favorite ON beneficiaries(user_id, is_favorite) WHERE is_favorite = true;

-- Trigger for updated_at
CREATE TRIGGER update_beneficiaries_updated_at
  BEFORE UPDATE ON beneficiaries
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

TypeScript Type

The Beneficiary interface includes usage tracking fields that help surface frequently used recipients in the user interface.

export interface Beneficiary {
  id: string;
  user_id: string;
  name: string;
  account_number: string;
  product_id: string | null;
  category: ProductCategory;
  provider: string | null;
  is_favorite: boolean;
  usage_count: number;
  last_used_at: string | null;
  metadata: Record<string, unknown>;
  created_at: string;
  updated_at: string;
}

8. Wallet Funding Table

The wallet_funding table tracks deposit requests and their status. It stores payment gateway references and responses, enabling reconciliation with payment providers.

-- Create wallet_funding table (deposit records)
CREATE TABLE public.wallet_funding (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- References
  wallet_id UUID NOT NULL REFERENCES wallets(id),
  user_id UUID NOT NULL REFERENCES users(id),
  transaction_id UUID REFERENCES transactions(id),

  -- Amount
  amount DECIMAL(15, 2) NOT NULL,
  fee DECIMAL(10, 2) DEFAULT 0,

  -- Payment info
  payment_method VARCHAR(50) NOT NULL,   -- card, bank_transfer, ussd
  payment_provider VARCHAR(50) NOT NULL, -- paystack, flutterwave
  payment_reference VARCHAR(100) UNIQUE,

  -- Status
  status transaction_status DEFAULT 'pending' NOT NULL,

  -- Payment details
  payment_data JSONB DEFAULT '{}'::jsonb,

  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  completed_at TIMESTAMPTZ,

  CONSTRAINT positive_funding_amount CHECK (amount > 0)
);

-- Indexes
CREATE INDEX idx_wallet_funding_wallet_id ON wallet_funding(wallet_id);
CREATE INDEX idx_wallet_funding_user_id ON wallet_funding(user_id);
CREATE INDEX idx_wallet_funding_reference ON wallet_funding(payment_reference);
CREATE INDEX idx_wallet_funding_status ON wallet_funding(status);

9. Complete Schema Migration

This migration script creates all tables, enums, functions, and seed data in the correct order. Run this in the Supabase SQL Editor to set up your complete database schema.

-- Complete migration script: migrations/001_initial_schema.sql

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Create enums
CREATE TYPE product_category AS ENUM (
  'airtime', 'data', 'electricity', 'cable',
  'internet', 'education', 'betting', 'other'
);

CREATE TYPE product_status AS ENUM (
  'active', 'inactive', 'coming_soon'
);

CREATE TYPE transaction_type AS ENUM (
  'credit', 'debit', 'transfer', 'reversal'
);

CREATE TYPE transaction_status AS ENUM (
  'pending', 'processing', 'completed', 'failed', 'reversed'
);

-- Create updated_at trigger function
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ language 'plpgsql';

-- Create tables (in order of dependencies)
-- 1. users
-- 2. wallets
-- 3. products
-- 4. transactions
-- 5. beneficiaries
-- 6. wallet_funding

-- (Include all CREATE TABLE statements from above)

-- Create all indexes and triggers

-- Seed initial data
INSERT INTO products (name, slug, category, provider, min_amount, max_amount, color, status)
VALUES
  ('MTN Airtime', 'mtn-airtime', 'airtime', 'MTN', 50, 50000, '#FFCC00', 'active'),
  ('Airtel Airtime', 'airtel-airtime', 'airtime', 'Airtel', 50, 50000, '#FF0000', 'active'),
  ('Glo Airtime', 'glo-airtime', 'airtime', 'Glo', 50, 50000, '#00FF00', 'active'),
  ('9Mobile Airtime', '9mobile-airtime', 'airtime', '9Mobile', 50, 50000, '#006400', 'active');

10. Schema Validation Queries

After running the migration, use these queries to verify your schema was created correctly. They help identify any issues with table structures, relationships, or indexes.

-- Verify table structure
SELECT
  table_name,
  column_name,
  data_type,
  is_nullable,
  column_default
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;

-- Check foreign key relationships
SELECT
  tc.table_name,
  kcu.column_name,
  ccu.table_name AS foreign_table_name,
  ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
  ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY';

-- Check indexes
SELECT
  tablename,
  indexname,
  indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

Additional Resources

Next Lesson: Supabase Client in Next.js