Skip to content

feat: Add database foundation for end-user self-subscription to status pages#6998

Draft
catsdev wants to merge 2 commits intolouislam:masterfrom
catsdev:feature/subscriptions-db
Draft

feat: Add database foundation for end-user self-subscription to status pages#6998
catsdev wants to merge 2 commits intolouislam:masterfrom
catsdev:feature/subscriptions-db

Conversation

@catsdev
Copy link

@catsdev catsdev commented Feb 20, 2026

Summary

This pull request introduces the following changes:

Three new tables have been added to the schema to support end-user status page subscriptions.

  • subscriber: Stores individual subscriber information (email, etc.).

  • subscriptions: Stores associations between subscribers and specific status pages.

  • notification_queue: Manages and queues email notifications to offload the processing of potentially many of emails.

  • Relates to Email Subscription on Status Page  #916 link

Please follow this checklist to avoid unnecessary back and forth (click to expand)
  • ⚠️ Breaking changes: None. This PR does not alter existing functionality.
  • 🧠 LLM/AI usage disclosed. All generated content was reviewed and reflects my intent.
  • 🔍 UI changes adhere to visual style of this project. No UI changes in this PR.
  • 🛠️ Code has been self-reviewed and tested.
  • 📝 I have commented my code where necessary.
  • 🤖 I added or updated automated tests where appropriate.
  • 📄 Documentation updates are included (if applicable).
  • 🧰 Dependency updates are listed and explained.
  • ⚠️ CI passes and is green.

@github-actions
Copy link
Contributor

Hello and thanks for lending a paw to Uptime Kuma! 🐻👋
As this is your first contribution, please be sure to check out our Pull Request guidelines.
In particular: - Mark your PR as Draft while you’re still making changes - Mark it as Ready for review once it’s fully ready
If you have any design or process questions, feel free to ask them right here in this pull request - unclear documentation is a bug too.

@catsdev catsdev changed the title Add database foundation for end-user self-subscription to status pages feat: Add database foundation for end-user self-subscription to status pages Feb 20, 2026
@louislam louislam marked this pull request as draft February 20, 2026 23:16
@catsdev catsdev force-pushed the feature/subscriptions-db branch from a77d9a6 to eec3e77 Compare February 23, 2026 09:01
Copy link
Collaborator

@CommanderStorm CommanderStorm left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Seems resonable. I seem to have forgot to submit my review.

Hard to review something which is not integrated into the rest of the system at all.

table.increments("id").primary();
table.string("email", 255).notNullable().unique();
table.string("unsubscribe_token", 255).unique();
table.datetime("created_at").notNullable().defaultTo(knex.fn.now());
Copy link
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

(applies in more than one place)

please use knex' timestamp function instead. This way we get created_at and updated_on

Copy link
Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Excellent... didn't know about it.

return (
knex.schema
// Create subscriber table
.createTable("subscriber", (table) => {
Copy link
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

(applies in more than one place)

please name all tables status_page_ to make it clearer for future devs what this table contains

Copy link
Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Good call

// Create subscription table (links subscribers to status pages and components)
.createTable("subscription", (table) => {
table.increments("id").primary();
table.integer("subscriber_id").unsigned().notNullable();
Copy link
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

(applies in more than one place)

please use foreign key constraints + on delete cascade. This way we can be tighter in terms of data modeling.
You currently are using an index which is kind of weird

Copy link
Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Makes sense

table.increments("id").primary();
table.integer("subscriber_id").unsigned().notNullable();
table.integer("status_page_id").unsigned().notNullable();
table.integer("component_id").unsigned(); // NULL means subscribe to all components
Copy link
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

what is a component? please add a knex comment or a foreign key to what this is.

Copy link
Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Good catch. My subscription UI called them components, thus the component id. But, in reality these are the publicMonitorList aka publicGroupList of the status page, and the ids como from the table "group". I'll rename to group_id and reference the group table

// Create notification queue table
.createTable("notification_queue", (table) => {
table.increments("id").primary();
table.integer("subscriber_id").unsigned().notNullable();
Copy link
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

should this not be subscription_id?

Copy link
Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Hmmm, not really, once a determination has been made that the subscriber needs to be notified, there is no need for the subscription that triggered the notification. But the contact details are in the subscriber table.

table.string("notification_type", 50).notNullable(); // 'incident', 'incident_update', 'maintenance', 'status_change'
table.string("subject", 255).notNullable();
table.text("data").notNullable();
table.string("status", 50).defaultTo("pending"); // 'pending', 'sent', 'failed'
Copy link
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

(also applies elsewere)

for things that are an enum, please use knex' .enu(..) functionality

Comment on lines +57 to +58
table.index("status", "notification_queue_status");
table.index("created_at", "notification_queue_created_at");
Copy link
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

these two indexes seem kind of strange. Can you explain this?

toPublicJSON() {
return {
id: this.id,
email: this.email,
Copy link
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I'd like to not leak users emails

Suggested change
email: this.email,

table.boolean("notify_maintenance").defaultTo(true);
table.boolean("notify_status_changes").defaultTo(false);
table.boolean("verified").defaultTo(false);
table.string("verification_token", 255);
Copy link
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

please go over your nullability.

I for example think theat verified being null is kind of weird, same as most of the other collumns.

Copy link
Owner

@louislam louislam left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Don't want to waste your time, but this is a feature that would be very difficult to implement, and the scope is very large. We should discuss it first.

Basically, what you are trying to build is a whole open source email subscription system.

Like this one:
https://github.com/Billionmail/BillionMail

In the real world, if a company needs newsletter subscription function, no one would implement such thing from scratch, because we have Sendgrid, mailchimp etc., to deal with the problem perfectly.

1. Sending mass emails is a big topic

Just google why.

2. Performance is questionable, especially on SQLite

By looking at #7040.

Assume that you have 5000 subscription emails.

When a monitor went down, it add 5000 records to notification_queue table, each record contains full email content (full html code).

It could lock SQLite for a long period to insert them all, because SQLite only have one file writer at the same time.

During the period, other monitors could fail to insert heartbeat records, and go down too because of this.... And them another 5000 emails will be queued to the notification queue.... so on....

3. The UI of email subscription management

Another big part that you have to implement from scratch.

My advice

Drop all tables. Add massmail_provider (or similar).

Let Sendgrid, mailchimp, Billionmail handle it.

When a monitor went down, you just need to send one API call to them.

@catsdev
Copy link
Author

catsdev commented Feb 25, 2026

@louislam I like that direction. From your perspective, there wouldn’t be a need for customers to subscribe directly to the status page. Instead, the status page could be configured by admins to send alerts to a service (e.g., webhook, email list), and customers would subscribe to those services instead. Is that correct?

If so, we would only need to add alert configuration to the status pages (UI and database), along with the logic to trigger alerts based on lifecycle events for groups and maintenance events.

Am I reading you correctly?

@louislam
Copy link
Owner

I like that direction. From your perspective, there wouldn’t be a need for customers to subscribe directly to the status page. Instead, the status page could be configured by admins to send alerts to a service (e.g., webhook, email list), and customers would subscribe to those services instead. Is that correct?

From public users' UI perspective, they still need to input their email address in our status page, in order to subscribe. They won't know which mass mail provider we are using.
I think focusing on email first should be better, allowing webhook for public users is another thing?

Here is pseudo code of my imagination, not correct, but you should get the idea.

MassMailProvider {
     name: string;
     config: {};
     
     subscribe(email: string) {}
     unsubscribe(email: string) {}
     send(msg : string) {}
}


SendGrid extends MassMailProvider {
     name = "SendGrid";
     
     // Get config from db
     config = {
        apiKey, 
        emailListName
     }
     
     subscribe(email: string) {
         // Call API to add `email` to `emailListName`
     }
     
     unsubscribe(email: string) {
          // Call API to remove `email` from `emailListName`
     }
     
     send(msg : string) {
         // Call API to send mass email using `emailListName`
     }
}

Ideally, for example, after an admin added new Incident post, it should call:

const massMailProvider = statusPage.getMassMailProvider()

If it is not null, use it to call:

massMailProvider.send("Oh no! My server is down. I am fixing the issue!")

@louislam
Copy link
Owner

louislam commented Feb 26, 2026

But first thing first, if you have no experience with those mass mail services (SendGrid, mailchimp), you should get familiar with them first. They should have free tier for you to try.

Also just re-read #916, Twist is supporting multiple subscription methods. So maybe it is a many-to-many relation (statusPage vs massMailProvider).

I personally don't need this function, I probably won't go deeper, but I hope my comments help get you guys back on the right track.

You may also need to ask in #916 if it is what they need (also is it what you want to do too?).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

4 participants