systemprompt_analytics/repository/fingerprint/
queries.rs

1use anyhow::Result;
2
3use super::FingerprintRepository;
4use crate::models::FingerprintReputation;
5
6impl FingerprintRepository {
7    pub async fn get_by_hash(
8        &self,
9        fingerprint_hash: &str,
10    ) -> Result<Option<FingerprintReputation>> {
11        let row = sqlx::query_as!(
12            FingerprintReputation,
13            r#"
14            SELECT
15                fingerprint_hash,
16                first_seen_at,
17                last_seen_at,
18                total_session_count,
19                active_session_count,
20                total_request_count,
21                requests_last_hour,
22                peak_requests_per_minute,
23                sustained_high_velocity_minutes,
24                is_flagged,
25                flag_reason,
26                flagged_at,
27                reputation_score,
28                abuse_incidents,
29                last_abuse_at,
30                last_ip_address,
31                last_user_agent,
32                associated_user_ids,
33                updated_at
34            FROM fingerprint_reputation
35            WHERE fingerprint_hash = $1
36            "#,
37            fingerprint_hash,
38        )
39        .fetch_optional(&*self.pool)
40        .await?;
41
42        Ok(row)
43    }
44
45    pub async fn count_active_sessions(&self, fingerprint_hash: &str) -> Result<i32> {
46        let row = sqlx::query_scalar!(
47            r#"
48            SELECT COUNT(*)::INT as "count!"
49            FROM user_sessions
50            WHERE fingerprint_hash = $1
51              AND ended_at IS NULL
52              AND last_activity_at > CURRENT_TIMESTAMP - INTERVAL '7 days'
53            "#,
54            fingerprint_hash,
55        )
56        .fetch_one(&*self.pool)
57        .await?;
58
59        Ok(row)
60    }
61
62    pub async fn find_reusable_session(&self, fingerprint_hash: &str) -> Result<Option<String>> {
63        let row = sqlx::query_scalar!(
64            r#"
65            SELECT session_id as "session_id!"
66            FROM user_sessions
67            WHERE fingerprint_hash = $1
68              AND ended_at IS NULL
69              AND last_activity_at > CURRENT_TIMESTAMP - INTERVAL '7 days'
70            ORDER BY last_activity_at ASC
71            LIMIT 1
72            "#,
73            fingerprint_hash,
74        )
75        .fetch_optional(&*self.pool)
76        .await?;
77
78        Ok(row)
79    }
80
81    pub async fn get_fingerprints_for_analysis(&self) -> Result<Vec<FingerprintReputation>> {
82        let rows = sqlx::query_as!(
83            FingerprintReputation,
84            r#"
85            SELECT
86                fingerprint_hash,
87                first_seen_at,
88                last_seen_at,
89                total_session_count,
90                active_session_count,
91                total_request_count,
92                requests_last_hour,
93                peak_requests_per_minute,
94                sustained_high_velocity_minutes,
95                is_flagged,
96                flag_reason,
97                flagged_at,
98                reputation_score,
99                abuse_incidents,
100                last_abuse_at,
101                last_ip_address,
102                last_user_agent,
103                associated_user_ids,
104                updated_at
105            FROM fingerprint_reputation
106            WHERE last_seen_at > CURRENT_TIMESTAMP - INTERVAL '1 hour'
107            ORDER BY total_request_count DESC
108            LIMIT 1000
109            "#,
110        )
111        .fetch_all(&*self.pool)
112        .await?;
113
114        Ok(rows)
115    }
116
117    pub async fn get_high_risk_fingerprints(
118        &self,
119        limit: i64,
120    ) -> Result<Vec<FingerprintReputation>> {
121        let rows = sqlx::query_as!(
122            FingerprintReputation,
123            r#"
124            SELECT
125                fingerprint_hash,
126                first_seen_at,
127                last_seen_at,
128                total_session_count,
129                active_session_count,
130                total_request_count,
131                requests_last_hour,
132                peak_requests_per_minute,
133                sustained_high_velocity_minutes,
134                is_flagged,
135                flag_reason,
136                flagged_at,
137                reputation_score,
138                abuse_incidents,
139                last_abuse_at,
140                last_ip_address,
141                last_user_agent,
142                associated_user_ids,
143                updated_at
144            FROM fingerprint_reputation
145            WHERE is_flagged = TRUE
146               OR reputation_score < 30
147               OR abuse_incidents >= 3
148            ORDER BY reputation_score ASC, abuse_incidents DESC
149            LIMIT $1
150            "#,
151            limit,
152        )
153        .fetch_all(&*self.pool)
154        .await?;
155
156        Ok(rows)
157    }
158}
systemprompt_analytics/repository/fingerprint/queries.rs

systemprompt_analytics/repository/fingerprint/
queries.rs
