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

systemprompt_analytics/repository/fingerprint/
queries.rs
