systemprompt_analytics/repository/fingerprint/
mutations.rs

1use anyhow::Result;
2use systemprompt_identifiers::UserId;
3
4use super::FingerprintRepository;
5use crate::models::{FingerprintReputation, FlagReason};
6
7impl FingerprintRepository {
8    pub async fn upsert_fingerprint(
9        &self,
10        fingerprint_hash: &str,
11        ip_address: Option<&str>,
12        user_agent: Option<&str>,
13        user_id: Option<&UserId>,
14    ) -> Result<FingerprintReputation> {
15        let user_ids = user_id.map_or_else(Vec::new, |u| vec![u.as_str().to_string()]);
16
17        let row = sqlx::query_as!(
18            FingerprintReputation,
19            r#"
20            INSERT INTO fingerprint_reputation (
21                fingerprint_hash, last_ip_address, last_user_agent,
22                associated_user_ids, total_session_count
23            )
24            VALUES ($1, $2, $3, $4, 1)
25            ON CONFLICT (fingerprint_hash) DO UPDATE SET
26                last_seen_at = CURRENT_TIMESTAMP,
27                last_ip_address = COALESCE($2, fingerprint_reputation.last_ip_address),
28                last_user_agent = COALESCE($3, fingerprint_reputation.last_user_agent),
29                total_session_count = fingerprint_reputation.total_session_count + 1,
30                associated_user_ids = CASE
31                    WHEN array_length($4, 1) > 0 AND NOT ($4[1] = ANY(fingerprint_reputation.associated_user_ids))
32                    THEN array_cat(fingerprint_reputation.associated_user_ids, $4)
33                    ELSE fingerprint_reputation.associated_user_ids
34                END,
35                updated_at = CURRENT_TIMESTAMP
36            RETURNING
37                fingerprint_hash,
38                first_seen_at,
39                last_seen_at,
40                total_session_count,
41                active_session_count,
42                total_request_count,
43                requests_last_hour,
44                peak_requests_per_minute,
45                sustained_high_velocity_minutes,
46                is_flagged,
47                flag_reason,
48                flagged_at,
49                reputation_score,
50                abuse_incidents,
51                last_abuse_at,
52                last_ip_address,
53                last_user_agent,
54                associated_user_ids,
55                updated_at
56            "#,
57            fingerprint_hash,
58            ip_address,
59            user_agent,
60            &user_ids[..],
61        )
62        .fetch_one(&*self.write_pool)
63        .await?;
64
65        Ok(row)
66    }
67
68    pub async fn flag_fingerprint(
69        &self,
70        fingerprint_hash: &str,
71        reason: FlagReason,
72        new_score: i32,
73    ) -> Result<()> {
74        sqlx::query!(
75            r#"
76            UPDATE fingerprint_reputation
77            SET is_flagged = TRUE,
78                flag_reason = $2,
79                flagged_at = CURRENT_TIMESTAMP,
80                reputation_score = $3,
81                abuse_incidents = abuse_incidents + 1,
82                last_abuse_at = CURRENT_TIMESTAMP,
83                updated_at = CURRENT_TIMESTAMP
84            WHERE fingerprint_hash = $1
85            "#,
86            fingerprint_hash,
87            reason.as_str(),
88            new_score,
89        )
90        .execute(&*self.write_pool)
91        .await?;
92
93        Ok(())
94    }
95
96    pub async fn update_velocity_metrics(
97        &self,
98        fingerprint_hash: &str,
99        requests_last_hour: i32,
100        peak_requests_per_minute: f32,
101        sustained_high_velocity_minutes: i32,
102    ) -> Result<()> {
103        sqlx::query!(
104            r#"
105            UPDATE fingerprint_reputation
106            SET requests_last_hour = $2,
107                peak_requests_per_minute = $3,
108                sustained_high_velocity_minutes = $4,
109                total_request_count = total_request_count + 1,
110                updated_at = CURRENT_TIMESTAMP
111            WHERE fingerprint_hash = $1
112            "#,
113            fingerprint_hash,
114            requests_last_hour,
115            peak_requests_per_minute,
116            sustained_high_velocity_minutes,
117        )
118        .execute(&*self.write_pool)
119        .await?;
120
121        Ok(())
122    }
123
124    pub async fn update_active_session_count(
125        &self,
126        fingerprint_hash: &str,
127        active_count: i32,
128    ) -> Result<()> {
129        sqlx::query!(
130            r#"
131            UPDATE fingerprint_reputation
132            SET active_session_count = $2,
133                updated_at = CURRENT_TIMESTAMP
134            WHERE fingerprint_hash = $1
135            "#,
136            fingerprint_hash,
137            active_count,
138        )
139        .execute(&*self.write_pool)
140        .await?;
141
142        Ok(())
143    }
144
145    pub async fn increment_request_count(&self, fingerprint_hash: &str) -> Result<()> {
146        sqlx::query!(
147            r#"
148            UPDATE fingerprint_reputation
149            SET total_request_count = total_request_count + 1,
150                updated_at = CURRENT_TIMESTAMP
151            WHERE fingerprint_hash = $1
152            "#,
153            fingerprint_hash,
154        )
155        .execute(&*self.write_pool)
156        .await?;
157
158        Ok(())
159    }
160
161    pub async fn clear_flag(&self, fingerprint_hash: &str) -> Result<()> {
162        sqlx::query!(
163            r#"
164            UPDATE fingerprint_reputation
165            SET is_flagged = FALSE,
166                flag_reason = NULL,
167                flagged_at = NULL,
168                updated_at = CURRENT_TIMESTAMP
169            WHERE fingerprint_hash = $1
170            "#,
171            fingerprint_hash,
172        )
173        .execute(&*self.write_pool)
174        .await?;
175
176        Ok(())
177    }
178
179    pub async fn adjust_reputation_score(&self, fingerprint_hash: &str, delta: i32) -> Result<i32> {
180        let row = sqlx::query_scalar!(
181            r#"
182            UPDATE fingerprint_reputation
183            SET reputation_score = GREATEST(0, LEAST(100, reputation_score + $2)),
184                updated_at = CURRENT_TIMESTAMP
185            WHERE fingerprint_hash = $1
186            RETURNING reputation_score as "reputation_score!"
187            "#,
188            fingerprint_hash,
189            delta,
190        )
191        .fetch_one(&*self.write_pool)
192        .await?;
193
194        Ok(row)
195    }
196}
systemprompt_analytics/repository/fingerprint/mutations.rs

systemprompt_analytics/repository/fingerprint/
mutations.rs
