Email Notifications System Documentation

This document explains the current state of the email notification system, what has been changed, and how it aligns with the new user story requirements.

test

Database Schema

The email notification system now uses two main tables:

-- Main email table
CREATE TABLE emails (
  email TEXT PRIMARY KEY,
  adminToken TEXT UNIQUE NOT NULL,
  createdAt TEXT NOT NULL,
  isUnsubscribed INTEGER NOT NULL DEFAULT 0
);

-- Subscriptions to an account
CREATE TABLE email_subscriptions (
      id TEXT NOT NULL, -- Account ID
      email TEXT NOT NULL REFERENCES emails(email), -- Email address that is subscribed
      createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      notifyAllMentions BOOLEAN NOT NULL DEFAULT FALSE,
      notifyAllReplies BOOLEAN NOT NULL DEFAULT FALSE,
      notifyOwnedDocChange BOOLEAN NOT NULL DEFAULT FALSE,
      notifySiteDiscussions BOOLEAN NOT NULL DEFAULT FALSE,
      PRIMARY KEY (id, email) -- Represents a subscription
    ) WITHOUT ROWID;

email_subscriptions table is basically the old accounts table, with a different primary key to represent a subscription.

What Was Changed

1. Notification Type Simplification

The system now supports only 2 notification types:

Before: 4 separate notification types

mention - When someone mentions your account

reply - When someone replies to your comment

discussion - New discussions on your site

change - Document changes on your site

After: 2 unified types

change - Document changes and new documents on the subscribed site

comment - All comment-related activity (replies, mentions, discussions) on the subscribed site

mention notifications had to be removed because there is no link between the mentioned account and a subscribed email address.

2. Notification Preferences Changes

Preferences settings are only accessible from the emails, either the welcome email or any notification. This page is accessed by the email token.

Current User Stories Implementation

Receive Email Notifications please read this document with user stories for email notifications.

Subscribe with an email to all the Site Activity

Status: Working

Users can subscribe to any site using just their email address. All subscribers receive notifications for all site activity

How it works: Visit any site → Click "Subscribe" button in site header → Enter email → Receive welcome email. When subscribed, users get notifications for document changes (new documents or edited documents), and all comment activity.

Unsubscribe my email from a Site

Status: Working

Users can unsubscribe from individual sites or all notifications

How it works: Click unsubscribe link in any email → Toggle off all switches under the site.

Subscribe with an email to the curated content from the Site Owner

Status: Not implemented

Current behavior: All subscribers get all activity, regardless of who created it. No distinction between site owner activity and other activity.

Link my email subscription to a Seed Account

Status: Removed

Current behavior: Email subscriptions exist independently of Seed accounts.

Subscribe to activity related to my Site with my email

Status: Working but changed from previous implementation

How it works: Same as other subscribers, site owners can subscribe to their own sites like any other user, with email address as identity. However, there is no distinction in email content between site owner and subscribers. Site owners will also receive notifications about their activity.

Subscribe to replies to my comments.

Status: Removed

Current behavior: All subscribers get all comment activity (including replies). No way to get notifications only for replies to your specific comments.

Subscribe to comments or documents that mention my account

Status: Removed

Current behavior: All subscribers get all comment activity (including mentions). Document mentions notifications were removed completely as there is no way to determine which email belongs to which account.

Subscribe to receive All Activities: replies, discussions, docs from a Site

Status: Working

How it works: All users can subscribe to sites and receive all activity. When subscribed, users get notifications for all types of site activity, unless some are disabled in the notifications preferences page.

As a Site owner, I want to change my notification preferences

Status: Partially removed

Current behavior: Notification preferences were removed from the account settings. Only accessible in a separate preferences page.

See my subscribers

Status: Not implemented

Site owners cannot see who has subscribed to their sites

Current Limitations

No Personalized Notifications: You can't get notifications only for replies to your comments

No Mention Alerts: You can't get notified when someone mentions you specifically

No Account Benefits: Having an account doesn't give you special notification features

Limited Control: Only 2 types of notifications instead of 4

Email-Only Management: Must use email links to change notification preferences

Future Development

Missing Core Stories

Subscribe to replies to my comments: Restore personalized notifications for replies to your specific comments

Subscribe to mentions of my account: Add back mention alerts when users are specifically mentioned

Subscribe with an email to the curated content from the Site Owner: Implement filtering to show only site owner activity

Link my email subscription to a Seed Account: Allow users to link existing email subscriptions to newly created accounts

Enhancements

See my subscribers: Add subscriber management interface for site owners

Manage my notifications from my account: Restore in-app notification settings

I think we can bring back the accounts table with a flag to determine if the (accountId, email) entry is a subscription or a linked account to email.

-- Add back the accounts table with email linking
CREATE TABLE accounts (
  accountId TEXT NOT NULL,
  email TEXT NOT NULL,
  isAccountLinked BOOLEAN NOT NULL DEFAULT FALSE,
  createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  notifyMentions BOOLEAN NOT NULL DEFAULT FALSE,
  notifyReplies BOOLEAN NOT NULL DEFAULT FALSE,
  notifyDocChanges BOOLEAN NOT NULL DEFAULT FALSE,
  notifySiteDiscussions BOOLEAN NOT NULL DEFAULT FALSE,
  PRIMARY KEY (accountId, email)
);

The only issue is that email preferences are different for own account and subscriptions.

Because of that I would instead propose creating a separate table for linking an account to email.

CREATE TABLE email_account_links (
  email TEXT NOT NULL,
  accountId TEXT NOT NULL,
  createdAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  notifyMentions BOOLEAN NOT NULL DEFAULT FALSE,
  notifyReplies BOOLEAN NOT NULL DEFAULT FALSE,
  notifyDocChanges BOOLEAN NOT NULL DEFAULT FALSE,
  notifySiteDiscussions BOOLEAN NOT NULL DEFAULT FALSE,
  PRIMARY KEY (email, accountId)
);

And we could add creation of linking tokens to allow verifying the email address in the welcome email.

CREATE TABLE email_account_links (
  email TEXT NOT NULL,
  accountId TEXT NOT NULL,
  linkingToken TEXT UNIQUE NOT NULL,
  expiresAt DATETIME NOT NULL,
  isVerified BOOLEAN NOT NULL DEFAULT FALSE,
  verifiedAt DATETIME, -- NULL until verified
  notifyMentions BOOLEAN NOT NULL DEFAULT FALSE,
  notifyReplies BOOLEAN NOT NULL DEFAULT FALSE,
  notifyDocChanges BOOLEAN NOT NULL DEFAULT FALSE,
  notifySiteDiscussions BOOLEAN NOT NULL DEFAULT FALSE,
  PRIMARY KEY (email, accountId)
);

hm://z6MkrbYsRzKb1VABdvhsDSAk6JK8fAszKsyHhcaZigYeWCou hm://z6MkgisVMELvqnsCo3dYmtVpy8PiqPGMVwfAyBWFn84vebq4 hm://z6MkgYR76i3w2Xc2fcvgFJWfYjsop6YwUonDhQx3AdLmQ4BQ hm://z6Mkvz9TgGtv9zsGsdrksfNk1ajbFancgHREJEz3Y2HsAVdk hm://z6Mkq9emq1yUBq4KSeiSH5yzgNBJSnidPVqFnTpzjCdLxB3R hm://z6MkfnCSnmstMXTAb6MBXXCj4zdPwZedtRF6YBgYtE5Q8Sov