Plugin for WordPress to create a basic custom DB notifications table. Includes a bunch of helpers to get all, get unread, read, mark as read, and create. This was needed for a website that needed DB only notifications that would be shown to the user on a custom theme

custom-notifications.php

<?php
/**
 * Plugin Name: Custom Notifications
 * Plugin URI: https://braunson.ca/?ref=wp-custom-notifications
 * Description: This plugin provides custom notifications.
 * Version: 1.0.0
 * Author: Braunson Yager
 * Author URI: https://braunson.ca/?ref=wp-custom-notifications
 * Requires PHP: 7.4
 */
 
if ( !defined( 'ABSPATH' ) ) exit;

class CustomNotificationPlugin
{   
    /**
     * This function inserts a new table into the db when the plugin is activated.
     * 
     * @param None
     * @return void
     */
    public function create_plugin_database_table()
    {
        global $wpdb;
        
        // set the default character set and collation for the table
        //$charset_collate = $wpdb->get_charset_collate();
        $charset_collate = '';

		if ( ! empty( $wpdb->charset ) ) {
			$charset_collate = "DEFAULT CHARACTER SET {$wpdb->charset}";
		}

		if ( ! empty( $wpdb->collate ) ) {
			$charset_collate .= " COLLATE {$wpdb->collate}";
		}
        
        $table = $wpdb->base_prefix.'custom_notifications';
        
        // Check that the table does not already exist before continuing
        $sql = "CREATE TABLE IF NOT EXISTS `{$table}` (
id bigint(50) NOT NULL AUTO_INCREMENT,
user_id bigint(20) unsigned NOT NULL default '0',
message TEXT,
read_at TIMESTAMP NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) $charset_collate;";
        
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        
        dbDelta( $sql );
        $is_error = empty( $wpdb->last_error );
                
        return $is_error;
    }
    
    public static function getAll($user = null)
    {
        global $wpdb;

        $user = (is_null($user) && is_user_logged_in()) ? wp_get_current_user() : $user;     
                
        if (is_null($user)) {
            return false;
        }
        
        $table = $wpdb->base_prefix.'custom_notifications';
        
        return $wpdb->get_results( "SELECT * FROM {$table} WHERE user_id = {$user->ID}", OBJECT );
    }
    
    public static function getUnread($user = null)
    {
        global $wpdb;

        $user = (is_null($user) && is_user_logged_in()) ? wp_get_current_user() : $user;
                
        if (is_null($user)) {
            return false;
        }
        
        $table = $wpdb->base_prefix.'custom_notifications';
        
        return $wpdb->get_results( "SELECT * FROM {$table} WHERE user_id = {$user->ID} AND read_at IS NULL ORDER BY created_at DESC", OBJECT );
    }
    
    
    public static function getRead($user = null)
    {
        global $wpdb;

        $user = (is_null($user) && is_user_logged_in()) ? wp_get_current_user() : $user;
        
        if (is_null($user)) {
            return false;
        }
        
        $table = $wpdb->base_prefix.'custom_notifications';
        
        return $wpdb->get_results( "SELECT * FROM {$table} WHERE user_id = {$user->ID} AND read_at IS NOT NULL ORDER BY created_at DESC", OBJECT );
    }
    
    public static function markAsRead($notification_id)
    {
        global $wpdb;
        
        return $wpdb->update(
            $wpdb->base_prefix.'custom_notifications',
            [ 'read_at' => current_time( 'mysql' ) ],
            [ 'id' => $notification_id ]
        );
    }
    
    public static function create($user, $message)
    {
        global $wpdb;
        
        return $wpdb->query(
           $wpdb->prepare(
              "INSERT INTO ".$wpdb->base_prefix.'custom_notifications'." ( user_id, message ) VALUES ( %d, %s );",
              $user->ID,
              $message
           )
        );
    }
}

register_activation_hook( __FILE__, [ 'CustomNotificationPlugin', 'create_plugin_database_table' ] );