Building Ripple: A Twitter Style Social Platform in PHP and MySQL — Part 1: Project Overview and Database Schema

 

Introduction

This is the first post in a series where I'll walk you through building Ripple, a Twitter/X style social media platform, from the ground up using PHP and MySQL. By the end of the series you'll understand how to structure a real social app: authentication, a feed, replies, likes, direct messaging, notifications and profile management, all built with plain PHP and MySQLi rather than a heavy framework.

You can try the live version right now and see everything this series will teach you how to build:

🔗 Live Demo: http://ripple.naijatalks.com.ng

Feel free to register an account, post something, follow other users, and send a message. If you run into anything odd or have feedback, I'd genuinely love to hear it in the comments. Real user feedback is what shapes the next post in this series.

Why Build a Social Platform From Scratch

Social apps look simple on the surface, but they touch almost every core web development skill at once: relational data design, authentication, file uploads, real-time, feel-based interactions, and performance-conscious queries. Building one from scratch, without relying on a big framework to hide the details, is one of the fastest ways to genuinely understand backend development.

Ripple is built with:

• PHP for the server-side logic
• MySQL / MySQLi for the database layer, using prepared statements throughout
• Vanilla JavaScript for interactive elements like the image cropper and live UI updates
• A self-healing schema pattern, where each page checks and creates any tables or columns it needs on load, so the app can evolve without manual migrations

This series will follow that same architecture, so what you build maps directly to how Ripple actually works.

Project Structure

Before touching the database, it helps to see the overall folder layout we'll be building toward across the series.

ripple/
├── config/
│   └── db.php
├── includes/
│   ├── auth.php
│   └── functions.php
├── assets/
│   ├── css/
│   └── js/
├── uploads/
│   ├── avatars/
│   └── covers/
├── auth/
│   ├── register.php
│   ├── login.php
│   └── logout.php
├── feed.php
├── profile.php
├── messages.php
├── notifications.php
└── index.php

Each of these pieces will get its own dedicated post later in the series. For now, let's lay the foundation everything else depends on: the database.

Designing the Database Schema

A social platform's schema needs to support a handful of core relationships: users follow other users, users create posts, posts can be liked and replied to, and users can message each other privately. Getting this right early saves a lot of pain later.

Users Table

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    display_name VARCHAR(100),
    bio TEXT,
    avatar_url VARCHAR(255) DEFAULT NULL,
    cover_url VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Posts Table

Posts support optional reply threading through a self referencing parent_id column, which is how we'll build reply chains later in the series.

CREATE TABLE IF NOT EXISTS posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    parent_id INT DEFAULT NULL,
    content TEXT NOT NULL,
    image_url VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES posts(id) ON DELETE CASCADE
);

Follows Table

A simple many to many relationship between users, using a composite unique key so a user can't follow the same person twice.

CREATE TABLE IF NOT EXISTS follows (
    id INT AUTO_INCREMENT PRIMARY KEY,
    follower_id INT NOT NULL,
    following_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_follow (follower_id, following_id),
    FOREIGN KEY (follower_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (following_id) REFERENCES users(id) ON DELETE CASCADE
);

Likes Table

CREATE TABLE IF NOT EXISTS likes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    post_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_like (user_id, post_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
);

Direct Messages Table

CREATE TABLE IF NOT EXISTS messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sender_id INT NOT NULL,
    receiver_id INT NOT NULL,
    body TEXT NOT NULL,
    is_read TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (receiver_id) REFERENCES users(id) ON DELETE CASCADE
);

Notifications Table

CREATE TABLE IF NOT EXISTS notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    actor_id INT NOT NULL,
    type ENUM('like', 'reply', 'follow') NOT NULL,
    post_id INT DEFAULT NULL,
    is_read TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (actor_id) REFERENCES users(id) ON DELETE CASCADE
);

The Self Healing Schema Pattern

Instead of writing separate migration files, Ripple uses a lightweight pattern where the database connection file ensures every required table and column exists on each request. This keeps local development fast and forgiving while you're actively adding features.

<?php
function ensure_column($conn, $table, $column, $definition) {
    $check = mysqli_query($conn, "SHOW COLUMNS FROM `$table` LIKE '$column'");
    if (mysqli_num_rows($check) === 0) {
        mysqli_query($conn, "ALTER TABLE `$table` ADD COLUMN `$column` $definition");
    }
}

// Example usage after connecting to the database
ensure_column($conn, 'users', 'bio', 'TEXT DEFAULT NULL');
ensure_column($conn, 'posts', 'image_url', "VARCHAR(255) DEFAULT NULL");
?>

This is not a replacement for proper migrations in a large production team environment, but for a solo builder or small team iterating quickly, it removes a lot of friction.

What's Next

With the schema in place, Part 2 of this series will cover building the authentication system: registration, secure password hashing, login sessions, and the logic behind the auth.php include that protects the rest of the app.

In the meantime, go explore the live demo, create a post, follow a few accounts, and get a feel for how these tables translate into the actual product:

🔗 Live Demo: http://ripple.naijatalks.com.ng

If something breaks or feels off, that feedback genuinely helps shape what gets covered next.