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

systemprompt_analytics/repository/fingerprint/
queries.rs
