Set up infrastructure for push message subscriptions.

A subscription allows an application server (here, the Pilcrow server) to send web push messages to a user agent.

On the server, Pilcrow records subscriptions verbatim, in the clear. Each subscription has an associated key, which will be used to encrypt messages for the corresponding client, but we store them in the clear, for the same broad reason that we store the VAPID key in the clear. They allow anyone who obtains them to impersonate the server and send push messages to clients, but they're rotated regularly - clients must rotate them whenever the server's VAPID key changes.

On the client, we monitor VAPID key change events to drive automatic subscription management, once the user sets up an initial subscription manually (which we must do as it can involve a user-interaction-only prompt for permission to send notifications). This isn't the final UI, but rather a bare-minimum version to let us move on with testing push notifications.

Merges push-subscribe into push-notify.

1 files changed, 17 insertions, 0 deletions

diff --git a/src/token/repo/token.rs b/src/token/repo/token.rs
index 52a3987..33c33af 100644
--- a/src/token/repo/token.rs
+++ b/src/token/repo/token.rs
@@ -89,6 +89,23 @@ impl Tokens<'_> {
     // Expire and delete all tokens that haven't been used more recently than
     // `expire_at`.
     pub async fn expire(&mut self, expire_at: &DateTime) -> Result<Vec<Id>, sqlx::Error> {
+        // This lives here, rather than in the `push` repository, to ensure that the criteria for
+        // stale tokens don't drift apart between the two queries. That would be a larger risk if
+        // the queries lived in very separate parts of the codebase.
+        sqlx::query!(
+            r#"
+                with stale_tokens as (
+                    select id from token
+                    where last_used_at < $1
+                )
+                delete from push_subscription
+                where token in stale_tokens
+            "#,
+            expire_at,
+        )
+        .execute(&mut *self.0)
+        .await?;
+
         let tokens = sqlx::query_scalar!(
             r#"
                 delete