systemprompt_analytics/repository/fingerprint/
queries.rs

1//! Fingerprint-reputation read queries for `FingerprintRepository`.
2//!
3//! Looks up a fingerprint by hash, counts and finds reusable active sessions,
4//! and lists recent or high-risk fingerprints for the abuse-analysis job. All
5//! reads go to the read pool.
6//!
7//! Copyright (c) systemprompt.io — Business Source License 1.1.
8//! See <https://systemprompt.io> for licensing details.
9
10use crate::Result;
11
12use super::FingerprintRepository;
13use crate::models::FingerprintReputation;
14use systemprompt_identifiers::SessionId;
15
16impl FingerprintRepository {
17    pub async fn get_by_hash(
18        &self,
19        fingerprint_hash: &str,
20    ) -> Result<Option<FingerprintReputation>> {
21        let row = sqlx::query_as!(
22            FingerprintReputation,
23            r#"
24            SELECT
25                fingerprint_hash,
26                first_seen_at,
27                last_seen_at,
28                total_session_count,
29                active_session_count,
30                total_request_count,
31                requests_last_hour,
32                peak_requests_per_minute,
33                sustained_high_velocity_minutes,
34                is_flagged,
35                flag_reason,
36                flagged_at,
37                reputation_score,
38                abuse_incidents,
39                last_abuse_at,
40                last_ip_address,
41                last_user_agent,
42                associated_user_ids,
43                updated_at
44            FROM fingerprint_reputation
45            WHERE fingerprint_hash = $1
46            "#,
47            fingerprint_hash,
48        )
49        .fetch_optional(&*self.pool)
50        .await?;
51
52        Ok(row)
53    }
54
55    pub async fn count_active_sessions(&self, fingerprint_hash: &str) -> Result<i32> {
56        self.sessions
57            .count_active_fingerprint_sessions(fingerprint_hash)
58            .await
59            .map_err(crate::AnalyticsError::from)
60    }
61
62    pub async fn find_reusable_session(&self, fingerprint_hash: &str) -> Result<Option<SessionId>> {
63        self.sessions
64            .find_reusable_fingerprint_session(fingerprint_hash)
65            .await
66            .map_err(crate::AnalyticsError::from)
67    }
68
69    pub async fn get_fingerprints_for_analysis(&self) -> Result<Vec<FingerprintReputation>> {
70        let rows = sqlx::query_as!(
71            FingerprintReputation,
72            r#"
73            SELECT
74                fingerprint_hash,
75                first_seen_at,
76                last_seen_at,
77                total_session_count,
78                active_session_count,
79                total_request_count,
80                requests_last_hour,
81                peak_requests_per_minute,
82                sustained_high_velocity_minutes,
83                is_flagged,
84                flag_reason,
85                flagged_at,
86                reputation_score,
87                abuse_incidents,
88                last_abuse_at,
89                last_ip_address,
90                last_user_agent,
91                associated_user_ids,
92                updated_at
93            FROM fingerprint_reputation
94            WHERE last_seen_at > CURRENT_TIMESTAMP - INTERVAL '1 hour'
95            ORDER BY total_request_count DESC
96            LIMIT 1000
97            "#,
98        )
99        .fetch_all(&*self.pool)
100        .await?;
101
102        Ok(rows)
103    }
104
105    pub async fn get_high_risk_fingerprints(
106        &self,
107        limit: i64,
108    ) -> Result<Vec<FingerprintReputation>> {
109        let rows = sqlx::query_as!(
110            FingerprintReputation,
111            r#"
112            SELECT
113                fingerprint_hash,
114                first_seen_at,
115                last_seen_at,
116                total_session_count,
117                active_session_count,
118                total_request_count,
119                requests_last_hour,
120                peak_requests_per_minute,
121                sustained_high_velocity_minutes,
122                is_flagged,
123                flag_reason,
124                flagged_at,
125                reputation_score,
126                abuse_incidents,
127                last_abuse_at,
128                last_ip_address,
129                last_user_agent,
130                associated_user_ids,
131                updated_at
132            FROM fingerprint_reputation
133            WHERE is_flagged = TRUE
134               OR reputation_score < 30
135               OR abuse_incidents >= 3
136            ORDER BY reputation_score ASC, abuse_incidents DESC
137            LIMIT $1
138            "#,
139            limit,
140        )
141        .fetch_all(&*self.pool)
142        .await?;
143
144        Ok(rows)
145    }
146}
systemprompt_analytics/repository/fingerprint/queries.rs

systemprompt_analytics/repository/fingerprint/
queries.rs
