So, I am looking into implementing Notifications for Kin 2, and while I want to make certain improvements over Kin 1, it’s important that we also don’t make the same mistakes again.

In Kin 1 we had one very strange bug. Notifications send to a specific User might end up in the Notification Queue of a given Page, and vice versa. It’s been an ongoing issue for a long long time, and both I and other members of the Kin 1 team struggled to understand why that was happening.

Well, I think I figured it out.

But to properly explain I’m going to have to back up a bit.

The very first iteration of Kin lacked a lot of the more advanced stuff we have today. There were no Pages or Groups. We still wanted Notifications for when people interacted with Updates, so Daniel built a subscription model. Basically every single time someone interacted with an Update, a row would be inserted into the subscription table denoting whether or not they had subscribed to that piece of content. The table looked a little something like this: 

CREATE TABLE `subscriptions` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `updateID` int(11) NOT NULL,
  `userID` int(11) NOT NULL,
  `active` binary(1) DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `subscriptions_idx` (`updateID`,`userID`,`active`)
) ENGINE=InnoDB AUTO_INCREMENT=5067 DEFAULT CHARSET=utf8;

The two columns ‘updateID‘ and ‘userID‘ connects this table to the Updates table and the User table. The ‘active‘ column denotes whether or not the subscription is active.

This model had a lot of benefits. For instance, a User had the option of subscribing to an Update without interacting with it, essentially allowing people to lurk in a conversation and still be notified when something new happened.

What would happen is that whenever an interaction took place we would look at the Subscription table to determine who should receive a Notification. By polling on the ‘updateID‘ and ‘active‘ we could get a list of userID’s that were subscribed to notifications on this particular Update.

However, when the application grew, and specifically when we added Pages we added another set of parameters under which an Update could be posted. Essentially, once logged in you could “switch” to any of your characters Pages and then post as that page, with Updates having the pages logo and name in the Update, rather than that of the character.

What we, now obviously, forgot, was to add this functionality to the subscription model. All our tables have Primary Keys which we use to internally reference a bunch of data. But for this, essentially if a Page had Primary Key 11 in the Pages table and a User had Primary Key 11 in the Users table, those could get mixed up in the Subscriptions table, and presto there’s the crossed wires mess.

Now I present our new and improved Subscriptions table which hopefully (knock on wood) fixes this particular issue. 

CREATE TABLE `subscriptions` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `postID` int(11) NOT NULL,
  `subscriberID` int(11) NOT NULL,
  `subscriberType` varchar(10) DEFAULT NULL COMMENT 'Can be either ''user'' or ''page''',
  `active` binary(1) DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `subscriptions_idx` (`postID`,`subscriberID`,`subscriberType`,`active`)
) ENGINE=InnoDB AUTO_INCREMENT=5067 DEFAULT CHARSET=utf8;

Now … onwards and upwards to Notifications. Which is probably going to make me hate life on a whole new level.