Shovel a pile of database-esque code together

2 files changed, 111 insertions, 0 deletions

diff --git a/migrations/20250724_subscription.sql b/migrations/20250724_subscription.sql
new file mode 100644
index 0000000..368557a
--- /dev/null
+++ b/migrations/20250724_subscription.sql
@@ -0,0 +1,17 @@
+create table subscription (
+	id text
+		not null
+		primary key,
+	user text
+		not null
+		references user (id),
+    endpoint text
+		unique
+		not null,
+	key_p256dh text
+		not null,
+	key_auth text
+		not null,
+    expiration_time text,
+);
+
diff --git a/src/push/repo.rs b/src/push/repo.rs
new file mode 100644
index 0000000..2d492ea
--- /dev/null
+++ b/src/push/repo.rs
@@ -0,0 +1,94 @@
+use sqlx::{SqliteConnection, Transaction, sqlite::Sqlite};
+
+use super::{Subscription, Id};
+use crate::{
+    user::{self, User},
+}
+
+pub trait Provider {
+    fn subscriptions(&mut self) -> Subscriptions;
+}
+
+impl Provider for Transaction<'_, Sqlite> {
+    fn subscriptions(&mut self) -> Subscriptions {
+        Subscriptions(self)
+    }
+}
+
+pub struct Subscriptions<'t>(&'t mut SqliteConnection);
+
+impl Subscriptions<'_> {
+    pub async fn create(
+        &mut self,
+        user: &User,
+        endpoint: &String,
+        key_p256dh: &String,
+        key_auth: &String,
+        expiration_time: &String,
+    ) -> Result<Subscription, sqlx::Error> {
+        let id = Id::generate();
+
+        let subscription = sqlx::query!(
+            r#"
+                insert into subscription
+                    (id, user, endpoint, key_p256dh, key_auth, expiration_time)
+                values ($1, $2, $3, $4, $5, $6)
+                returning
+                    id as "id: Id",
+                    user as "user: user::Id",
+                    endpoint as "endpoint: String",
+                    key_p256dh as "key_p256dh: String",
+                    key_auth as "key_auth: String",
+                    expiration_time as "expiration_time: String"
+            "#,
+            id,
+            user.id,
+            endpoint,
+            key_p256dh,
+            key_auth,
+            expiration_time,
+        )
+        .fetch_one(&mut *self.0)
+        .await?;
+
+        Ok(subscription)
+    }
+
+    pub async fn for_user(&mut self, user: &User) -> Result<vec<Subscription>, sqlx::Error> {
+        let subscriptions = sqlx::query!(
+            r#"
+                select
+                    id as "id: Id",
+                    user as "user: user::Id",
+                    endpoint as "endpoint: String",
+                    key_p256dh as "key_p256dh: String",
+                    key_auth as "key_auth: String",
+                from subscription
+                where user = $1
+            "#,
+            user.id,
+        )
+        .fetch_all(&mut *self.0)
+        .await?;
+
+        Ok(subscriptions)
+    }
+
+    pub async fn delete(
+        &mut self,
+        subscription: &Subscription,
+        deleted: &Instant,
+    ) -> Result<(), sqlx::Error> {
+        let id = subscription.id();
+
+        sqlx::query!(
+            r#"
+                delete from subscription where id = $1
+            "#,
+            id,
+        )
+        .execute(&mut *self.0)
+        .await?;
+        Ok(())
+    }
+}