Schema

[Sephsekla]

Current project schema for notifications is as follow:

https://github.com/WordPress/wp-feature-notifications/blob/develop/docs/database-schema.md

Database Schema

wp_notifications table

• id (int) 11 auto_increment ID of the notification.
• sender_key Sender of the notification. As defined by the plugin or theme
• timestamp Timestamp of when the notification was triggered.
• recipient_key Single notification recipient. As defined by the plugin or theme
• title_key Notification title key. As defined by the plugin or theme
• message_key Notification message key. As defined by the plugin or theme (optional)
• action_link Correctly formatted URL to an internal or external action.
• status enum (READ, UNREAD) Notification status.

We also have updated demo schema in #98

[{
  "location": (dashboard|wpadmin),
  "image": "string",
  "icon": "string",
  "title": "string",
  "date": "timestamp / unix format",
  "message": "string",
  "source": "string",
  "severity": "string",
  "dismissible": bool,
  "dismissLabel": "string",
  "unread": bool,
  "acceptMessage": "string",
  "acceptLink": "url"
}]

[johnhooks]

Below is my proposal for the data schema. It gives a lot of flexibility for the notification system to be used for a large variety of purposes. I totally understand it's a departure from the previous schema. I believe for how powerful this system could be and how often it will be queried, it has to be structured in a very performant way. It will be looked up on every page load of the admin area, and from what @erikyo has described to me the goal is to poll the the database through the JSON API.

Database Schema

Key features

• Ability to target individual users and maintain a history of their messages.
• Organization of notifications by channel.
• Subscriptions and snooze functionality. Notifications can become overwhelming if the user isn't provided with options to snooze and/or unsubscribe from channels.
• Table structure optimized for search by user or channel. Minimizing repetitive string data.

Possibilities

Create a messaging system for users inside of WordPress. It could give collaborators the ability to have message threads for a specific posts and/or workflows. This seems like a powerful feature, possibly an even a more important use case than plugin notifications.

Message schema

Messages data is stored separately from channel data and can be linked to many users through the wp_notify_queue table.

wp_notify_messages table

• id: int - The ID of the notification

• channel_id: int - The ID of the channel this message was emitted from.

• created_at: timestamp - The timestamp of when the message was broadcast.

• updated_at: timestamp - The timestamp of when the message was last updated.

  Maybe unnecessary, but it could be good to know if a message has been modify after a user dismissed it. This is probably most import if users are given the ability to post messages to channels. They will want the ability to edit their messages.

• expires_at: timestamp | null - The optional timestamp of when the message expires.

  Allowing message emitters to specify when a message expires would help signal when it is appropriate to automatically dispose of a message. It should be best practice to provide expires_at for any message that isn't high priority.

• priority: int (maybe enum) - The priority of the message, aka. INFO, WARNING, DANGER.

  This should be a predefined list of values.

• title: varchar(256) | null - The title of the notification.

  The title should be a very condensed explanations of the notification. In some context, it will be used to show the notification in a list view.

• content: text - The notification message content

  The content could be generated by a modified block editor. Built specifically to limit the canvas size and block types. This would give message emitters control over how the message can look, but in the context of limits required by the system.

• meta: JSON - data that doesn’t have to be queried, like icon or image information.

  The meta field could be stored in another table, similar to other WordPress schemas. Another possibly is to use a concept of message type. A message could be of a type that has metadata attached to it, like icon.

Channel schema

Core should have a set registered channels.

Plugins can register channels of their own.

wp_ notify_channels table

• id: int - The ID of the channel

• created_at: timestamp - The timestamp of when the channel was created.

• updated_at: timestamp - The timestamp of when the channel was last updated.

• source: varchar(256) - The source of the channel, aka. the name of the plugin.

• name: varchar(256) - The name of the channel.

• description: text - The description of the channel.

• parent_id: int | null The ID of the parent channel.

  This would allow a hierarchal system of grouping channels.

• role: varchar(128) The minimum required role to subscribe to messages from the channel.

• meta: JSON - Data that doesn’t have to be queried, like icon or images

  Another way to add metadata to a channel could be the registration process in PHP and JS. Attaching icons could be done in code when calling something like wp_notify_register_channel.

Ideas

Maybe even user registered channels.

Every user has a channel attached to directly to their profile. Allows for notifications targeted to a single user. How would that be discoverable? An entry in the wp_user_meta table? Or enqueue the message for a specific user? I like the last option best.

Message Queue

Join table used to map messages to specific users. Could periodically be cleared depending on message expires_at and/or how long ago it was dismissed.

The full history of messages for every user is retained and could be easily looked up.

If a message has been orphan it can safely be deleted.

wp_notify_queue table

• message_id: int - The ID of the enqueued message.
• user_id: int - The ID of the user.
• dismissed_at: timestamp | null - The timestamp of when the notification was dismissed.

Subscriptions

Join table used determine for whom to enqueue messages for a specific channel.

Logic to authorize a users to subscribe to a channel would be based on a comparison of the role property of the channel and the user.

wp_ notify_subscriptions

• user_id: int - The ID of the user subscribed to the channel.
• channel_id: int - The ID of the channel subscribed to.
• snoozed_until: timestamp | null - The optional timestamp of when to resume the channel.

[johnhooks]

In sense this a messaging system, and it could be used for channels created for every post, where discussions about a post can be right there in the editor. A post meta could be used to link a channel to a specific location in a document and give inline editor commenting. These are just some ideas this system could support.

EDIT: I'm not suggesting that should be the goal of this project, but if the system is built to be a flexible messaging system, it could be use for other purposes.

[Sephsekla]

There are some really great points here!

One thing I'd like to revisit is the idea of title and content vs title_key and description_key. These were originally envisioned to allow easier updates and translation to strings - I'd be very much in favour of keeping this concept rather than having the exact strings in the database.

[johnhooks]

@Sephsekla that sounds great, I actually do not know much about how the internalization and translation works. I didnt understand what the key of the previous schema meant. Could you explain or link some documentation to help me grasp the concept?

[erikyo]

@johnhooks you can find some of the initial schema here:
#16

[johnhooks]

Are any of those people still involved with the project?