feat: remove follower table

3 files changed, 36 insertions, 32 deletions

diff --git a/crates/sellershut/migrations/20250713161354_account.sql b/crates/sellershut/migrations/20250713161354_account.sql
index 1b967b8..b62feb9 100644
--- a/crates/sellershut/migrations/20250713161354_account.sql
+++ b/crates/sellershut/migrations/20250713161354_account.sql
@@ -16,15 +16,6 @@ create table account (
     public_key text not null
 );
 
-create table following (
-    id uuid primary key,
-    follower text references account(ap_id) on delete cascade,
-    followee text references account(ap_id) on delete cascade,
-    created_at timestamptz not null default now(),
-    constraint unique_following unique (follower, followee)
-);
-create index "following_pagination" on "following" ("created_at" asc);
-
 create unique index unique_username_local
     on account (username)
     where local = true;
diff --git a/crates/sellershut/migrations/20250719223814_activity.sql b/crates/sellershut/migrations/20250719223814_activity.sql
index 107a21b..b5105c1 100644
--- a/crates/sellershut/migrations/20250719223814_activity.sql
+++ b/crates/sellershut/migrations/20250719223814_activity.sql
@@ -1,5 +1,5 @@
 create table activity (
-    ap_id text references account(ap_id),
+    actor text references account(ap_id),
     activity jsonb not null,
     activity_id text generated always as (
         activity->>'id'
@@ -7,6 +7,7 @@ create table activity (
     constraint unique_activity_id unique (activity_id),
     created_at timestamptz not null default now()
 );
-
-create index idx_activity_owner_id on activity(ap_id);
-create index idx_activity_created on activity(created_at);
+-- create index activity_data_index on activity using gin (activity);
+create index idx_activity_type on activity using gin ((activity->'type'));
+create index idx_activity_actor on activity using gin ((activity->'actor')); create index idx_activity_object on activity using gin ((activity->'object'));
+create index idx_activity_created on activity(created_at asc);
diff --git a/crates/sellershut/src/server/activities/follow.rs b/crates/sellershut/src/server/activities/follow.rs
index 48d9caa..07d2793 100644
--- a/crates/sellershut/src/server/activities/follow.rs
+++ b/crates/sellershut/src/server/activities/follow.rs
@@ -6,9 +6,8 @@ use activitypub_federation::{
 };
 use async_trait::async_trait;
 use serde::{Deserialize, Serialize};
-use tracing::trace;
+use tracing::{debug, trace, warn};
 use url::Url;
-use uuid::Uuid;
 
 use crate::{
     entity::user::User,
@@ -70,38 +69,51 @@ impl Activity for Follow {
     #[doc = " Should perform validation and possibly write action to the database. In case the activity"]
     #[doc = " has a nested `object` field, must call `object.from_json` handler."]
     async fn receive(self, data: &Data<Self::DataType>) -> Result<(), Self::Error> {
-        let id = Uuid::now_v7();
-
         let mut transaction = data.services.postgres.begin().await?;
 
-        trace!("adding a follower");
-        sqlx::query!(
-            "insert into following (id, follower, followee) values ($1, $2, $3)",
-            id,
-            self.actor.inner().as_str(),
-            self.object.inner().as_str(),
-        )
-        .execute(&mut *transaction)
-        .await?;
-
+        let count =  sqlx::query_scalar!("select count (*) from activity where activity->'type' ? $1 and actor = $2 and activity->'object' ? $3",
+          self.kind.to_string(),
+          self.actor.inner().as_str(),
+          self.object.inner().as_str(),
+        ).fetch_one(&data.services.postgres).await?;
+        trace!(count = count, "executed query");
+
+        if let Some(count) = count
+            && count > 1
+        {
+            warn!("found duplicate follows, cleaning up");
+            sqlx::query!("delete from activity where activity->'type' ? $1 and actor = $2 and activity->'object' ? $3",
+              self.kind.to_string(),
+              self.actor.inner().as_str(),
+              self.object.inner().as_str(),
+            ).execute(&mut *transaction).await?;
+
+            // clean up
+        } else if let Some(count) = count
+            && count == 1
+        {
+            debug!("this user already follows the actor");
+            return Ok(());
+        }
         let follower = self.actor.dereference(data).await?;
+
         let id = generate_object_id(data.domain(), data.environment)?;
 
         let local_user = self.object.dereference(data).await?;
         let accept = Accept::new(self.object.clone(), self.clone(), id.clone());
 
-        local_user
-            .send(accept, vec![follower.shared_inbox_or_inbox()], false, data)
-            .await?;
-
         sqlx::query!(
-            "insert into activity (ap_id, activity) values ($1, $2)",
+            "insert into activity (actor, activity) values ($1, $2)",
             self.actor.inner().as_str(),
             sqlx::types::Json(&self) as _
         )
         .execute(&mut *transaction)
         .await?;
 
+        local_user
+            .send(accept, vec![follower.shared_inbox_or_inbox()], false, data)
+            .await?;
+
         transaction.commit().await?;
 
         Ok(())