systemprompt_analytics/repository/fingerprint/
mutations.rs

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

systemprompt_analytics/repository/fingerprint/
mutations.rs
