Database Architecture

Overview

MinistryHub uses MySQL as its relational database with a multi-database architecture to separate concerns and optimize for different data access patterns.

Database Strategy

Multi-Database Separation

MinistryHub splits data into logical databases:

Database	Purpose	Key Tables
Main	User management, authentication, multi-tenancy	`members`, `churches`, `roles`, `permissions`
Music	Worship content and setlists	`songs`, `playlists`, `instruments`, `song_edits`

Benefits:

Performance: Smaller indexes, faster queries
Scalability: Can move databases to separate servers
Security: Isolate sensitive user data from content
Maintenance: Easier backups and migrations

Configuration

backend/config/database.env

# Main Database
DB_HOST=localhost
DB_USER=ministryhub_user
DB_PASS=secure_password
DB_NAME=ministryhub_main
DB_PORT=3306

# Music Database
MUSIC_DB_HOST=localhost
MUSIC_DB_USER=ministryhub_user
MUSIC_DB_PASS=secure_password
MUSIC_DB_NAME=ministryhub_music

# Security
JWT_SECRET=your-256-bit-secret
RECAPTCHA_SECRET_KEY=your-recaptcha-key

Database Connection Management

Singleton Pattern with Multi-Config

backend/src/Database.php

<?php

namespace App;

use PDO;
use PDOException;
use App\Helpers\Logger;

class Database
{
    private static $instances = [];
    private static $cachedEnv = null;
    private $conn;

    private function __construct($configKey = 'main')
    {
        // Cache the .env file to avoid repeated disk reads
        if (self::$cachedEnv === null) {
            $configPath = APP_ROOT . DIRECTORY_SEPARATOR . 'config' . DIRECTORY_SEPARATOR . 'database.env';
            
            if (!file_exists($configPath)) {
                Logger::error("Database Configuration file not found at: " . $configPath);
                throw new \Exception("Database configuration missing.");
            }
            
            self::$cachedEnv = parse_ini_file($configPath);
        }

        $env = self::$cachedEnv;

        // Select configuration based on key
        if ($configKey === 'main') {
            $host = $env['DB_HOST'] ?? '';
            $user = $env['DB_USER'] ?? '';
            $pass = $env['DB_PASS'] ?? '';
            $name = $env['DB_NAME'] ?? '';
            $port = $env['DB_PORT'] ?? '3306';
        } elseif ($configKey === 'music') {
            $host = $env['MUSIC_DB_HOST'] ?? '';
            $user = $env['MUSIC_DB_USER'] ?? '';
            $pass = $env['MUSIC_DB_PASS'] ?? '';
            $name = $env['MUSIC_DB_NAME'] ?? '';
            $port = $env['DB_PORT'] ?? '3306';
        }

        if (empty($host) || empty($user) || empty($name)) {
            Logger::error("Database Configuration is missing required keys for: $configKey");
            throw new \Exception("Database configuration malformed for $configKey.");
        }

        try {
            $dsn = "mysql:host=$host;port=$port;dbname=$name;charset=utf8mb4";
            $this->conn = new PDO($dsn, $user, $pass);
            
            // Security and error handling
            $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            Logger::error("Database Connection Error ($configKey): " . $e->getMessage());
            throw new \Exception("Database connection failed for $configKey.");
        }
    }

    public static function getInstance($configKey = 'main')
    {
        // Create one instance per database
        if (!isset(self::$instances[$configKey])) {
            self::$instances[$configKey] = new self($configKey);
        }
        return self::$instances[$configKey]->conn;
    }
}

Key Features

Singleton Per Database

Each database (main, music) gets exactly one connection per request:

$mainDb = Database::getInstance('main');   // First call: creates connection
$mainDb2 = Database::getInstance('main');  // Reuses same connection
$musicDb = Database::getInstance('music'); // Creates separate connection

Configuration Caching

The .env file is read once and cached in $cachedEnv:

if (self::$cachedEnv === null) {
    self::$cachedEnv = parse_ini_file($configPath);
}

This prevents repeated file I/O on every query.

UTF-8 Support

$dsn = "mysql:host=$host;dbname=$name;charset=utf8mb4";

utf8mb4 supports full Unicode including emojis.

Exception Mode

$this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Errors throw exceptions instead of returning false, enabling try/catch blocks.

Associative Fetch

$this->conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

Results return as ['column' => 'value'] instead of numeric indexes.

Repository Pattern

All database queries are encapsulated in Repository classes to separate data access from business logic.

Example: Song Repository

backend/src/Repositories/SongRepo.php

<?php

namespace App\Repositories;

use App\Database;
use PDO;

class SongRepo
{
    /**
     * Get all songs for a church (includes global songs with church_id=0)
     */
    public static function getAll($churchId = null)
    {
        $db = Database::getInstance('music');

        if ($churchId === null) {
            // Superadmin: get ALL songs
            $sql = "SELECT * FROM songs ORDER BY title ASC";
            $stmt = $db->prepare($sql);
        } else {
            // Regular user: get church-specific + global songs
            $sql = "SELECT * FROM songs 
                    WHERE church_id = :church_id OR church_id = 0 
                    ORDER BY title ASC";
            $stmt = $db->prepare($sql);
            $stmt->bindValue(':church_id', $churchId, PDO::PARAM_INT);
        }

        $stmt->execute();
        return $stmt->fetchAll();
    }

    /**
     * Get a single song by ID
     */
    public static function findById($id)
    {
        $db = Database::getInstance('music');
        $sql = "SELECT * FROM songs WHERE id = :id LIMIT 1";
        $stmt = $db->prepare($sql);
        $stmt->bindValue(':id', $id, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetch();
    }

    /**
     * Create a new song
     */
    public static function create($data)
    {
        $db = Database::getInstance('music');
        $sql = "INSERT INTO songs (title, artist, lyrics, original_key, church_id, created_by) 
                VALUES (:title, :artist, :lyrics, :key, :church_id, :created_by)";
        
        $stmt = $db->prepare($sql);
        $stmt->bindValue(':title', $data['title']);
        $stmt->bindValue(':artist', $data['artist']);
        $stmt->bindValue(':lyrics', $data['lyrics']);
        $stmt->bindValue(':key', $data['key']);
        $stmt->bindValue(':church_id', $data['church_id'], PDO::PARAM_INT);
        $stmt->bindValue(':created_by', $data['created_by'], PDO::PARAM_INT);
        
        $stmt->execute();
        return $db->lastInsertId();
    }

    /**
     * Update an existing song
     */
    public static function update($id, $data)
    {
        $db = Database::getInstance('music');
        $sql = "UPDATE songs 
                SET title = :title, artist = :artist, lyrics = :lyrics, original_key = :key 
                WHERE id = :id";
        
        $stmt = $db->prepare($sql);
        $stmt->bindValue(':id', $id, PDO::PARAM_INT);
        $stmt->bindValue(':title', $data['title']);
        $stmt->bindValue(':artist', $data['artist']);
        $stmt->bindValue(':lyrics', $data['lyrics']);
        $stmt->bindValue(':key', $data['key']);
        
        return $stmt->execute();
    }

    /**
     * Delete a song
     */
    public static function delete($id)
    {
        $db = Database::getInstance('music');
        $sql = "DELETE FROM songs WHERE id = :id";
        $stmt = $db->prepare($sql);
        $stmt->bindValue(':id', $id, PDO::PARAM_INT);
        return $stmt->execute();
    }
}

Repository Benefits

Benefit	Explanation
Reusability	Query logic used by multiple controllers
Testability	Can mock repositories in unit tests
Security	Centralized prepared statements
Maintainability	Schema changes only affect repositories

Security: Prepared Statements

MinistryHub NEVER uses string concatenation for SQL queries. All queries use prepared statements.

SQL Injection Prevention

BAD (Vulnerable):

// ❌ NEVER DO THIS
$sql = "SELECT * FROM songs WHERE id = " . $_GET['id'];
$result = $db->query($sql);

GOOD (Prepared Statement):

// ✅ Always use prepared statements
$sql = "SELECT * FROM songs WHERE id = :id";
$stmt = $db->prepare($sql);
$stmt->bindValue(':id', $_GET['id'], PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetch();

Named Parameters

MinistryHub uses named parameters (:name) instead of positional (?) for clarity:

$sql = "SELECT * FROM members 
        WHERE email = :email AND church_id = :church_id";
        
$stmt = $db->prepare($sql);
$stmt->bindValue(':email', $email);
$stmt->bindValue(':church_id', $churchId, PDO::PARAM_INT);
$stmt->execute();

Type Binding

// Integer binding (prevents type juggling attacks)
$stmt->bindValue(':id', $id, PDO::PARAM_INT);

// String binding (default)
$stmt->bindValue(':name', $name); // Equivalent to PDO::PARAM_STR

// Boolean binding
$stmt->bindValue(':is_active', $isActive, PDO::PARAM_BOOL);

Schema Examples

Main Database Schema

Members Table

CREATE TABLE members (
    id INT AUTO_INCREMENT PRIMARY KEY,
    church_id INT NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(50),
    role_id INT,
    is_active TINYINT(1) DEFAULT 1,
    invite_token VARCHAR(255),
    invite_expires_at DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_church (church_id),
    INDEX idx_email (email),
    INDEX idx_invite (invite_token),
    FOREIGN KEY (church_id) REFERENCES churches(id) ON DELETE CASCADE
);

Churches Table

CREATE TABLE churches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    city VARCHAR(100),
    country VARCHAR(100),
    timezone VARCHAR(50) DEFAULT 'UTC',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Roles & Permissions

CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    church_id INT,
    permissions JSON,  -- Stores permissions as JSON array
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (church_id) REFERENCES churches(id) ON DELETE CASCADE
);

Music Database Schema

Songs Table

CREATE TABLE songs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    church_id INT DEFAULT 0,  -- 0 = global song
    title VARCHAR(255) NOT NULL,
    artist VARCHAR(255),
    lyrics TEXT,
    original_key VARCHAR(10),
    ccli_number VARCHAR(50),
    tags JSON,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_church (church_id),
    INDEX idx_title (title),
    FULLTEXT idx_search (title, artist, lyrics)
);

Playlists (Setlists) Table

CREATE TABLE playlists (
    id INT AUTO_INCREMENT PRIMARY KEY,
    church_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    date DATE,
    songs JSON,  -- Array of song IDs with order
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_church_date (church_id, date),
    FOREIGN KEY (church_id) REFERENCES ministryhub_main.churches(id)
);

Multi-Tenancy Strategy

Church Isolation

Every table includes a church_id column for data isolation:

// Users only see data from their church
$sql = "SELECT * FROM songs WHERE church_id = :church_id";

Global Resources

Some resources are shared across all churches using church_id = 0:

-- Get church-specific + global songs
SELECT * FROM songs 
WHERE church_id = :church_id OR church_id = 0
ORDER BY title;

Superadmin Access

Superadmins can bypass church filtering:

public static function getAll($churchId = null)
{
    if ($churchId === null) {
        // No filter: return ALL data
        $sql = "SELECT * FROM songs";
    } else {
        // Filter by church
        $sql = "SELECT * FROM songs WHERE church_id = :church_id";
    }
}

Query Performance

Indexing Strategy

Primary Keys

All tables have AUTO_INCREMENT primary keys for fast lookups.

Foreign Key Indexes

INDEX idx_church (church_id)

Speeds up multi-tenancy queries.

Unique Indexes

UNIQUE INDEX idx_email (email)

Prevents duplicate users and enables fast login queries.

Composite Indexes

INDEX idx_church_date (church_id, date)

Optimizes queries filtering by both church and date.

Full-Text Search

FULLTEXT idx_search (title, artist, lyrics)

Enables fast song search:

$sql = "SELECT * FROM songs 
        WHERE MATCH(title, artist, lyrics) AGAINST(:query IN NATURAL LANGUAGE MODE)";

Query Optimization

// Fetch only needed columns
$sql = "SELECT id, title, artist FROM songs"; // Not SELECT *

// Use LIMIT for pagination
$sql = "SELECT * FROM songs LIMIT :offset, :limit";

// Count efficiently
$sql = "SELECT COUNT(*) as total FROM members WHERE church_id = :church_id";

Transactions

For operations requiring multiple queries (e.g., creating a user + sending notification):

public static function createMemberWithNotification($memberData, $notificationData)
{
    $db = Database::getInstance('main');
    
    try {
        $db->beginTransaction();
        
        // Insert member
        $sql = "INSERT INTO members (name, email, church_id) VALUES (:name, :email, :church_id)";
        $stmt = $db->prepare($sql);
        $stmt->execute([
            ':name' => $memberData['name'],
            ':email' => $memberData['email'],
            ':church_id' => $memberData['church_id']
        ]);
        $memberId = $db->lastInsertId();
        
        // Insert notification
        $sql = "INSERT INTO notifications (member_id, message) VALUES (:member_id, :message)";
        $stmt = $db->prepare($sql);
        $stmt->execute([
            ':member_id' => $memberId,
            ':message' => $notificationData['message']
        ]);
        
        $db->commit();
        return $memberId;
        
    } catch (\Exception $e) {
        $db->rollBack();
        Logger::error("Transaction failed: " . $e->getMessage());
        throw $e;
    }
}

Database Migrations

Development Workflow

Create Migration SQL

-- migrations/001_add_song_tags.sql
ALTER TABLE songs ADD COLUMN tags JSON;

Apply Manually

mysql -u user -p ministryhub_music < migrations/001_add_song_tags.sql

Track in Version Control
- All SQL files in /User SQL/ and /Music SQL/
- Numbered sequentially for order

Production Deployment

Always backup the database before running migrations in production.

# Backup
mysqldump -u user -p ministryhub_main > backup_main_$(date +%Y%m%d).sql

# Apply migration
mysql -u user -p ministryhub_main < migrations/002_add_permissions.sql

Error Handling

Exception Handling in Repositories

public static function findByEmail($email)
{
    try {
        $db = Database::getInstance('main');
        $sql = "SELECT * FROM members WHERE email = :email AND is_active = 1";
        $stmt = $db->prepare($sql);
        $stmt->bindValue(':email', $email);
        $stmt->execute();
        return $stmt->fetch();
    } catch (PDOException $e) {
        Logger::error("Database error in findByEmail: " . $e->getMessage());
        throw new \Exception("Database query failed");
    }
}

Connection Failure Handling

try {
    $this->conn = new PDO($dsn, $user, $pass);
} catch (PDOException $e) {
    Logger::error("Database Connection Error ($configKey): " . $e->getMessage());
    throw new \Exception("Database connection failed for $configKey.");
}

Errors are logged to backend/logs/app.log and generic messages returned to the client.

Best Practices

Always Use Prepared Statements

Never concatenate user input into SQL queries. Use :named parameters.

Specify Column Types

Use PDO::PARAM_INT for integers to prevent type juggling attacks.

Fetch Only What You Need

Avoid SELECT * in production. Specify columns explicitly.

Index Foreign Keys

Every church_id or member_id column should have an index.

Use Transactions

When multiple queries must succeed together, wrap them in a transaction.

Log All Errors

Database errors should be logged for debugging, never shown to users.

Next Steps

Request Lifecycle

See how database queries fit into the request flow

Security

Learn about authentication and data protection

Technology Stack

Explore the full tech stack

API Reference

View all available API endpoints

Architecture

Frontend

Backend

​Overview

​Database Strategy

​Multi-Database Separation

​Configuration

​Database Connection Management

​Singleton Pattern with Multi-Config

​Key Features

​Repository Pattern

​Example: Song Repository

​Repository Benefits

​Security: Prepared Statements

​SQL Injection Prevention

​Named Parameters

​Type Binding

​Schema Examples

​Main Database Schema

​Members Table

​Churches Table

​Roles & Permissions

​Music Database Schema

​Songs Table

​Playlists (Setlists) Table

​Multi-Tenancy Strategy

​Church Isolation

​Global Resources

​Superadmin Access

​Query Performance

​Indexing Strategy

​Query Optimization

​Transactions

​Database Migrations

​Development Workflow

​Production Deployment

​Error Handling

​Exception Handling in Repositories

​Connection Failure Handling

​Best Practices

Always Use Prepared Statements

Specify Column Types

Fetch Only What You Need

Index Foreign Keys

Use Transactions

Log All Errors

​Next Steps

Request Lifecycle

Security

Technology Stack

API Reference

Build docs developers (and LLMs) love

Overview

Database Strategy

Multi-Database Separation

Configuration

Database Connection Management

Singleton Pattern with Multi-Config

Key Features

Repository Pattern

Example: Song Repository

Repository Benefits

Security: Prepared Statements

SQL Injection Prevention

Named Parameters

Type Binding

Schema Examples

Main Database Schema

Members Table

Churches Table

Roles & Permissions

Music Database Schema

Songs Table

Playlists (Setlists) Table

Multi-Tenancy Strategy

Church Isolation

Global Resources

Superadmin Access

Query Performance

Indexing Strategy

Query Optimization

Transactions

Database Migrations

Development Workflow

Production Deployment

Error Handling

Exception Handling in Repositories

Connection Failure Handling

Best Practices

Next Steps