A very performant and light (2mb in memory) link shortener and tracker. Written in Rust and React and uses Postgres/SQLite.
nekomimi.pet
1use crate::auth::AuthenticatedUser;
2use crate::{
3 error::AppError,
4 models::{
5 AuthResponse, Claims, ClickStats, CreateLink, DatabasePool, Link, LoginRequest,
6 RegisterRequest, SourceStats, User, UserResponse,
7 },
8 AppState,
9};
10use actix_web::{web, HttpRequest, HttpResponse, Responder};
11use argon2::{
12 password_hash::{rand_core::OsRng, SaltString},
13 PasswordVerifier,
14};
15use argon2::{Argon2, PasswordHash, PasswordHasher};
16use jsonwebtoken::{encode, EncodingKey, Header};
17use lazy_static::lazy_static;
18use regex::Regex;
19use serde_json::json;
20use sqlx::{Postgres, Sqlite};
21
22lazy_static! {
23 static ref VALID_CODE_REGEX: Regex = Regex::new(r"^[a-zA-Z0-9_-]{1,32}$").unwrap();
24}
25
26pub async fn create_short_url(
27 state: web::Data<AppState>,
28 user: AuthenticatedUser,
29 payload: web::Json<CreateLink>,
30) -> Result<impl Responder, AppError> {
31 tracing::debug!("Creating short URL with user_id: {}", user.user_id);
32 validate_url(&payload.url)?;
33
34 let short_code = if let Some(ref custom_code) = payload.custom_code {
35 validate_custom_code(custom_code)?;
36
37 // Check if code exists using match on pool type
38 let exists = match &state.db {
39 DatabasePool::Postgres(pool) => {
40 sqlx::query_as::<_, Link>("SELECT * FROM links WHERE short_code = $1")
41 .bind(custom_code)
42 .fetch_optional(pool)
43 .await?
44 }
45 DatabasePool::Sqlite(pool) => {
46 sqlx::query_as::<_, Link>("SELECT * FROM links WHERE short_code = ?1")
47 .bind(custom_code)
48 .fetch_optional(pool)
49 .await?
50 }
51 };
52
53 if exists.is_some() {
54 return Err(AppError::InvalidInput(
55 "Custom code already taken".to_string(),
56 ));
57 }
58 custom_code.clone()
59 } else {
60 generate_short_code()
61 };
62
63 // Start transaction based on pool type
64 let result = match &state.db {
65 DatabasePool::Postgres(pool) => {
66 let mut tx = pool.begin().await?;
67
68 let link = sqlx::query_as::<_, Link>(
69 "INSERT INTO links (original_url, short_code, user_id) VALUES ($1, $2, $3) RETURNING *"
70 )
71 .bind(&payload.url)
72 .bind(&short_code)
73 .bind(user.user_id)
74 .fetch_one(&mut *tx)
75 .await?;
76
77 if let Some(ref source) = payload.source {
78 sqlx::query("INSERT INTO clicks (link_id, source) VALUES ($1, $2)")
79 .bind(link.id)
80 .bind(source)
81 .execute(&mut *tx)
82 .await?;
83 }
84
85 tx.commit().await?;
86 link
87 }
88 DatabasePool::Sqlite(pool) => {
89 let mut tx = pool.begin().await?;
90
91 let link = sqlx::query_as::<_, Link>(
92 "INSERT INTO links (original_url, short_code, user_id) VALUES (?1, ?2, ?3) RETURNING *"
93 )
94 .bind(&payload.url)
95 .bind(&short_code)
96 .bind(user.user_id)
97 .fetch_one(&mut *tx)
98 .await?;
99
100 if let Some(ref source) = payload.source {
101 sqlx::query("INSERT INTO clicks (link_id, source) VALUES (?1, ?2)")
102 .bind(link.id)
103 .bind(source)
104 .execute(&mut *tx)
105 .await?;
106 }
107
108 tx.commit().await?;
109 link
110 }
111 };
112
113 Ok(HttpResponse::Created().json(result))
114}
115
116fn validate_custom_code(code: &str) -> Result<(), AppError> {
117 if !VALID_CODE_REGEX.is_match(code) {
118 return Err(AppError::InvalidInput(
119 "Custom code must be 1-32 characters long and contain only letters, numbers, underscores, and hyphens".to_string()
120 ));
121 }
122
123 // Add reserved words check
124 let reserved_words = ["api", "health", "admin", "static", "assets"];
125 if reserved_words.contains(&code.to_lowercase().as_str()) {
126 return Err(AppError::InvalidInput(
127 "This code is reserved and cannot be used".to_string(),
128 ));
129 }
130
131 Ok(())
132}
133
134fn validate_url(url: &str) -> Result<(), AppError> {
135 if url.is_empty() {
136 return Err(AppError::InvalidInput("URL cannot be empty".to_string()));
137 }
138 if !url.starts_with("http://") && !url.starts_with("https://") {
139 return Err(AppError::InvalidInput(
140 "URL must start with http:// or https://".to_string(),
141 ));
142 }
143 Ok(())
144}
145
146pub async fn redirect_to_url(
147 state: web::Data<AppState>,
148 path: web::Path<String>,
149 req: HttpRequest,
150) -> Result<impl Responder, AppError> {
151 let short_code = path.into_inner();
152
153 // Extract query source if present
154 let query_source = req
155 .uri()
156 .query()
157 .and_then(|q| web::Query::<std::collections::HashMap<String, String>>::from_query(q).ok())
158 .and_then(|params| params.get("source").cloned());
159
160 let link = match &state.db {
161 DatabasePool::Postgres(pool) => {
162 let mut tx = pool.begin().await?;
163 let link = sqlx::query_as::<_, Link>(
164 "UPDATE links SET clicks = clicks + 1 WHERE short_code = $1 RETURNING *",
165 )
166 .bind(&short_code)
167 .fetch_optional(&mut *tx)
168 .await?;
169 tx.commit().await?;
170 link
171 }
172 DatabasePool::Sqlite(pool) => {
173 let mut tx = pool.begin().await?;
174 let link = sqlx::query_as::<_, Link>(
175 "UPDATE links SET clicks = clicks + 1 WHERE short_code = ?1 RETURNING *",
176 )
177 .bind(&short_code)
178 .fetch_optional(&mut *tx)
179 .await?;
180 tx.commit().await?;
181 link
182 }
183 };
184
185 match link {
186 Some(link) => {
187 // Handle click recording based on database type
188 match &state.db {
189 DatabasePool::Postgres(pool) => {
190 let mut tx = pool.begin().await?;
191 let user_agent = req
192 .headers()
193 .get("user-agent")
194 .and_then(|h| h.to_str().ok())
195 .unwrap_or("unknown")
196 .to_string();
197
198 sqlx::query(
199 "INSERT INTO clicks (link_id, source, query_source) VALUES ($1, $2, $3)",
200 )
201 .bind(link.id)
202 .bind(user_agent)
203 .bind(query_source)
204 .execute(&mut *tx)
205 .await?;
206
207 tx.commit().await?;
208 }
209 DatabasePool::Sqlite(pool) => {
210 let mut tx = pool.begin().await?;
211 let user_agent = req
212 .headers()
213 .get("user-agent")
214 .and_then(|h| h.to_str().ok())
215 .unwrap_or("unknown")
216 .to_string();
217
218 sqlx::query(
219 "INSERT INTO clicks (link_id, source, query_source) VALUES (?1, ?2, ?3)",
220 )
221 .bind(link.id)
222 .bind(user_agent)
223 .bind(query_source)
224 .execute(&mut *tx)
225 .await?;
226
227 tx.commit().await?;
228 }
229 };
230
231 Ok(HttpResponse::TemporaryRedirect()
232 .append_header(("Location", link.original_url))
233 .finish())
234 }
235 None => Err(AppError::NotFound),
236 }
237}
238
239pub async fn get_all_links(
240 state: web::Data<AppState>,
241 user: AuthenticatedUser,
242) -> Result<impl Responder, AppError> {
243 let links = match &state.db {
244 DatabasePool::Postgres(pool) => {
245 sqlx::query_as::<_, Link>(
246 "SELECT * FROM links WHERE user_id = $1 ORDER BY created_at DESC",
247 )
248 .bind(user.user_id)
249 .fetch_all(pool)
250 .await?
251 }
252 DatabasePool::Sqlite(pool) => {
253 sqlx::query_as::<_, Link>(
254 "SELECT * FROM links WHERE user_id = ?1 ORDER BY created_at DESC",
255 )
256 .bind(user.user_id)
257 .fetch_all(pool)
258 .await?
259 }
260 };
261
262 Ok(HttpResponse::Ok().json(links))
263}
264
265pub async fn health_check(state: web::Data<AppState>) -> impl Responder {
266 let is_healthy = match &state.db {
267 DatabasePool::Postgres(pool) => sqlx::query("SELECT 1").execute(pool).await.is_ok(),
268 DatabasePool::Sqlite(pool) => sqlx::query("SELECT 1").execute(pool).await.is_ok(),
269 };
270
271 if is_healthy {
272 HttpResponse::Ok().json("Healthy")
273 } else {
274 HttpResponse::ServiceUnavailable().json("Database unavailable")
275 }
276}
277
278fn generate_short_code() -> String {
279 use base62::encode;
280 use uuid::Uuid;
281
282 let uuid = Uuid::new_v4();
283 encode(uuid.as_u128() as u64).chars().take(32).collect()
284}
285
286pub async fn register(
287 state: web::Data<AppState>,
288 payload: web::Json<RegisterRequest>,
289) -> Result<impl Responder, AppError> {
290 // Check if any users exist
291 let user_count = match &state.db {
292 DatabasePool::Postgres(pool) => {
293 let mut tx = pool.begin().await?;
294 let count = sqlx::query_as::<Postgres, (i64,)>("SELECT COUNT(*)::bigint FROM users")
295 .fetch_one(&mut *tx)
296 .await?
297 .0;
298 tx.commit().await?;
299 count
300 }
301 DatabasePool::Sqlite(pool) => {
302 let mut tx = pool.begin().await?;
303 let count = sqlx::query_as::<Sqlite, (i64,)>("SELECT COUNT(*) FROM users")
304 .fetch_one(&mut *tx)
305 .await?
306 .0;
307 tx.commit().await?;
308 count
309 }
310 };
311
312 // If users exist, registration is closed - no exceptions
313 if user_count > 0 {
314 return Err(AppError::Auth("Registration is closed".to_string()));
315 }
316
317 // Verify admin token for first user
318 match (&state.admin_token, &payload.admin_token) {
319 (Some(stored_token), Some(provided_token)) if stored_token == provided_token => {
320 // Token matches, proceed with registration
321 }
322 _ => return Err(AppError::Auth("Invalid admin setup token".to_string())),
323 }
324
325 // Check if email already exists
326 let exists = match &state.db {
327 DatabasePool::Postgres(pool) => {
328 let mut tx = pool.begin().await?;
329 let exists =
330 sqlx::query_as::<Postgres, (i32,)>("SELECT id FROM users WHERE email = $1")
331 .bind(&payload.email)
332 .fetch_optional(&mut *tx)
333 .await?;
334 tx.commit().await?;
335 exists
336 }
337 DatabasePool::Sqlite(pool) => {
338 let mut tx = pool.begin().await?;
339 let exists = sqlx::query_as::<Sqlite, (i32,)>("SELECT id FROM users WHERE email = ?")
340 .bind(&payload.email)
341 .fetch_optional(&mut *tx)
342 .await?;
343 tx.commit().await?;
344 exists
345 }
346 };
347
348 if exists.is_some() {
349 return Err(AppError::Auth("Email already registered".to_string()));
350 }
351
352 let salt = SaltString::generate(&mut OsRng);
353 let argon2 = Argon2::default();
354 let password_hash = argon2
355 .hash_password(payload.password.as_bytes(), &salt)
356 .map_err(|e| AppError::Auth(e.to_string()))?
357 .to_string();
358
359 // Insert new user
360 let user = match &state.db {
361 DatabasePool::Postgres(pool) => {
362 let mut tx = pool.begin().await?;
363 let user = sqlx::query_as::<Postgres, User>(
364 "INSERT INTO users (email, password_hash) VALUES ($1, $2) RETURNING *",
365 )
366 .bind(&payload.email)
367 .bind(&password_hash)
368 .fetch_one(&mut *tx)
369 .await?;
370 tx.commit().await?;
371 user
372 }
373 DatabasePool::Sqlite(pool) => {
374 let mut tx = pool.begin().await?;
375 let user = sqlx::query_as::<Sqlite, User>(
376 "INSERT INTO users (email, password_hash) VALUES (?, ?) RETURNING *",
377 )
378 .bind(&payload.email)
379 .bind(&password_hash)
380 .fetch_one(&mut *tx)
381 .await?;
382 tx.commit().await?;
383 user
384 }
385 };
386
387 let claims = Claims::new(user.id);
388 let secret = std::env::var("JWT_SECRET").unwrap_or_else(|_| "default_secret".to_string());
389 let token = encode(
390 &Header::default(),
391 &claims,
392 &EncodingKey::from_secret(secret.as_bytes()),
393 )
394 .map_err(|e| AppError::Auth(e.to_string()))?;
395
396 Ok(HttpResponse::Ok().json(AuthResponse {
397 token,
398 user: UserResponse {
399 id: user.id,
400 email: user.email,
401 },
402 }))
403}
404
405pub async fn login(
406 state: web::Data<AppState>,
407 payload: web::Json<LoginRequest>,
408) -> Result<impl Responder, AppError> {
409 let user = match &state.db {
410 DatabasePool::Postgres(pool) => {
411 let mut tx = pool.begin().await?;
412 let user = sqlx::query_as::<Postgres, User>("SELECT * FROM users WHERE email = $1")
413 .bind(&payload.email)
414 .fetch_optional(&mut *tx)
415 .await?;
416 tx.commit().await?;
417 user
418 }
419 DatabasePool::Sqlite(pool) => {
420 let mut tx = pool.begin().await?;
421 let user = sqlx::query_as::<Sqlite, User>("SELECT * FROM users WHERE email = ?")
422 .bind(&payload.email)
423 .fetch_optional(&mut *tx)
424 .await?;
425 tx.commit().await?;
426 user
427 }
428 }
429 .ok_or_else(|| AppError::Auth("Invalid credentials".to_string()))?;
430
431 let argon2 = Argon2::default();
432 let parsed_hash =
433 PasswordHash::new(&user.password_hash).map_err(|e| AppError::Auth(e.to_string()))?;
434
435 if argon2
436 .verify_password(payload.password.as_bytes(), &parsed_hash)
437 .is_err()
438 {
439 return Err(AppError::Auth("Invalid credentials".to_string()));
440 }
441
442 let claims = Claims::new(user.id);
443 let secret = std::env::var("JWT_SECRET").unwrap_or_else(|_| "default_secret".to_string());
444 let token = encode(
445 &Header::default(),
446 &claims,
447 &EncodingKey::from_secret(secret.as_bytes()),
448 )
449 .map_err(|e| AppError::Auth(e.to_string()))?;
450
451 Ok(HttpResponse::Ok().json(AuthResponse {
452 token,
453 user: UserResponse {
454 id: user.id,
455 email: user.email,
456 },
457 }))
458}
459
460pub async fn edit_link(
461 state: web::Data<AppState>,
462 user: AuthenticatedUser,
463 path: web::Path<i32>,
464 payload: web::Json<CreateLink>,
465) -> Result<impl Responder, AppError> {
466 let link_id: i32 = path.into_inner();
467
468 // Validate the new URL if provided
469 validate_url(&payload.url)?;
470
471 // Validate custom code if provided
472 if let Some(ref custom_code) = payload.custom_code {
473 validate_custom_code(custom_code)?;
474
475 // Check if the custom code is already taken by another link
476 let existing_link = match &state.db {
477 DatabasePool::Postgres(pool) => {
478 sqlx::query_as::<_, Link>("SELECT * FROM links WHERE short_code = $1 AND id != $2")
479 .bind(custom_code)
480 .bind(link_id)
481 .fetch_optional(pool)
482 .await?
483 }
484 DatabasePool::Sqlite(pool) => {
485 sqlx::query_as::<_, Link>("SELECT * FROM links WHERE short_code = ?1 AND id != ?2")
486 .bind(custom_code)
487 .bind(link_id)
488 .fetch_optional(pool)
489 .await?
490 }
491 };
492
493 if existing_link.is_some() {
494 return Err(AppError::InvalidInput(
495 "Custom code already taken".to_string(),
496 ));
497 }
498 }
499
500 // Update the link
501 let updated_link = match &state.db {
502 DatabasePool::Postgres(pool) => {
503 let mut tx = pool.begin().await?;
504
505 // First verify the link belongs to the user
506 let link =
507 sqlx::query_as::<_, Link>("SELECT * FROM links WHERE id = $1 AND user_id = $2")
508 .bind(link_id)
509 .bind(user.user_id)
510 .fetch_optional(&mut *tx)
511 .await?;
512
513 if link.is_none() {
514 return Err(AppError::NotFound);
515 }
516
517 // Update the link
518 let updated = sqlx::query_as::<_, Link>(
519 r#"
520 UPDATE links
521 SET
522 original_url = $1,
523 short_code = COALESCE($2, short_code)
524 WHERE id = $3 AND user_id = $4
525 RETURNING *
526 "#,
527 )
528 .bind(&payload.url)
529 .bind(&payload.custom_code)
530 .bind(link_id)
531 .bind(user.user_id)
532 .fetch_one(&mut *tx)
533 .await?;
534
535 // If source is provided, add a click record
536 if let Some(ref source) = payload.source {
537 sqlx::query("INSERT INTO clicks (link_id, source) VALUES ($1, $2)")
538 .bind(link_id)
539 .bind(source)
540 .execute(&mut *tx)
541 .await?;
542 }
543
544 tx.commit().await?;
545 updated
546 }
547 DatabasePool::Sqlite(pool) => {
548 let mut tx = pool.begin().await?;
549
550 // First verify the link belongs to the user
551 let link =
552 sqlx::query_as::<_, Link>("SELECT * FROM links WHERE id = ?1 AND user_id = ?2")
553 .bind(link_id)
554 .bind(user.user_id)
555 .fetch_optional(&mut *tx)
556 .await?;
557
558 if link.is_none() {
559 return Err(AppError::NotFound);
560 }
561
562 // Update the link
563 let updated = sqlx::query_as::<_, Link>(
564 r#"
565 UPDATE links
566 SET
567 original_url = ?1,
568 short_code = COALESCE(?2, short_code)
569 WHERE id = ?3 AND user_id = ?4
570 RETURNING *
571 "#,
572 )
573 .bind(&payload.url)
574 .bind(&payload.custom_code)
575 .bind(link_id)
576 .bind(user.user_id)
577 .fetch_one(&mut *tx)
578 .await?;
579
580 // If source is provided, add a click record
581 if let Some(ref source) = payload.source {
582 sqlx::query("INSERT INTO clicks (link_id, source) VALUES (?1, ?2)")
583 .bind(link_id)
584 .bind(source)
585 .execute(&mut *tx)
586 .await?;
587 }
588
589 tx.commit().await?;
590 updated
591 }
592 };
593
594 Ok(HttpResponse::Ok().json(updated_link))
595}
596
597pub async fn delete_link(
598 state: web::Data<AppState>,
599 user: AuthenticatedUser,
600 path: web::Path<i32>,
601) -> Result<impl Responder, AppError> {
602 let link_id: i32 = path.into_inner();
603
604 match &state.db {
605 DatabasePool::Postgres(pool) => {
606 let mut tx = pool.begin().await?;
607
608 // Verify the link belongs to the user
609 let link = sqlx::query_as::<Postgres, (i32,)>(
610 "SELECT id FROM links WHERE id = $1 AND user_id = $2",
611 )
612 .bind(link_id)
613 .bind(user.user_id)
614 .fetch_optional(&mut *tx)
615 .await?;
616
617 if link.is_none() {
618 return Err(AppError::NotFound);
619 }
620
621 // Delete associated clicks first due to foreign key constraint
622 sqlx::query("DELETE FROM clicks WHERE link_id = $1")
623 .bind(link_id)
624 .execute(&mut *tx)
625 .await?;
626
627 // Delete the link
628 sqlx::query("DELETE FROM links WHERE id = $1")
629 .bind(link_id)
630 .execute(&mut *tx)
631 .await?;
632
633 tx.commit().await?;
634 }
635 DatabasePool::Sqlite(pool) => {
636 let mut tx = pool.begin().await?;
637
638 // Verify the link belongs to the user
639 let link = sqlx::query_as::<Sqlite, (i32,)>(
640 "SELECT id FROM links WHERE id = ? AND user_id = ?",
641 )
642 .bind(link_id)
643 .bind(user.user_id)
644 .fetch_optional(&mut *tx)
645 .await?;
646
647 if link.is_none() {
648 return Err(AppError::NotFound);
649 }
650
651 // Delete associated clicks first due to foreign key constraint
652 sqlx::query("DELETE FROM clicks WHERE link_id = ?")
653 .bind(link_id)
654 .execute(&mut *tx)
655 .await?;
656
657 // Delete the link
658 sqlx::query("DELETE FROM links WHERE id = ?")
659 .bind(link_id)
660 .execute(&mut *tx)
661 .await?;
662
663 tx.commit().await?;
664 }
665 }
666
667 Ok(HttpResponse::NoContent().finish())
668}
669
670pub async fn get_link_clicks(
671 state: web::Data<AppState>,
672 user: AuthenticatedUser,
673 path: web::Path<i32>,
674) -> Result<impl Responder, AppError> {
675 let link_id = path.into_inner();
676
677 // First verify the link belongs to the user
678 let link = match &state.db {
679 DatabasePool::Postgres(pool) => {
680 sqlx::query_as::<_, (i32,)>("SELECT id FROM links WHERE id = $1 AND user_id = $2")
681 .bind(link_id)
682 .bind(user.user_id)
683 .fetch_optional(pool)
684 .await?
685 }
686 DatabasePool::Sqlite(pool) => {
687 sqlx::query_as::<_, (i32,)>("SELECT id FROM links WHERE id = ? AND user_id = ?")
688 .bind(link_id)
689 .bind(user.user_id)
690 .fetch_optional(pool)
691 .await?
692 }
693 };
694
695 if link.is_none() {
696 return Err(AppError::NotFound);
697 }
698
699 let clicks = match &state.db {
700 DatabasePool::Postgres(pool) => {
701 sqlx::query_as::<_, ClickStats>(
702 r#"
703 SELECT
704 DATE(created_at)::text as date,
705 COUNT(*)::bigint as clicks
706 FROM clicks
707 WHERE link_id = $1
708 GROUP BY DATE(created_at)
709 ORDER BY DATE(created_at) ASC
710 "#,
711 )
712 .bind(link_id)
713 .fetch_all(pool)
714 .await?
715 }
716 DatabasePool::Sqlite(pool) => {
717 sqlx::query_as::<_, ClickStats>(
718 r#"
719 SELECT
720 DATE(created_at) as date,
721 COUNT(*) as clicks
722 FROM clicks
723 WHERE link_id = ?
724 GROUP BY DATE(created_at)
725 ORDER BY DATE(created_at) ASC
726 "#,
727 )
728 .bind(link_id)
729 .fetch_all(pool)
730 .await?
731 }
732 };
733
734 Ok(HttpResponse::Ok().json(clicks))
735}
736
737pub async fn get_link_sources(
738 state: web::Data<AppState>,
739 user: AuthenticatedUser,
740 path: web::Path<i32>,
741) -> Result<impl Responder, AppError> {
742 let link_id = path.into_inner();
743
744 // Verify the link belongs to the user
745 let link = match &state.db {
746 DatabasePool::Postgres(pool) => {
747 let mut tx = pool.begin().await?;
748 let link = sqlx::query_as::<Postgres, (i32,)>(
749 "SELECT id FROM links WHERE id = $1 AND user_id = $2",
750 )
751 .bind(link_id)
752 .bind(user.user_id)
753 .fetch_optional(&mut *tx)
754 .await?;
755 tx.commit().await?;
756 link
757 }
758 DatabasePool::Sqlite(pool) => {
759 let mut tx = pool.begin().await?;
760 let link = sqlx::query_as::<Sqlite, (i32,)>(
761 "SELECT id FROM links WHERE id = ? AND user_id = ?",
762 )
763 .bind(link_id)
764 .bind(user.user_id)
765 .fetch_optional(&mut *tx)
766 .await?;
767 tx.commit().await?;
768 link
769 }
770 };
771
772 if link.is_none() {
773 return Err(AppError::NotFound);
774 }
775
776 let sources = match &state.db {
777 DatabasePool::Postgres(pool) => {
778 sqlx::query_as::<_, SourceStats>(
779 r#"
780 SELECT
781 DATE(created_at)::text as date,
782 query_source as source,
783 COUNT(*)::bigint as count
784 FROM clicks
785 WHERE link_id = $1
786 AND query_source IS NOT NULL
787 AND query_source != ''
788 GROUP BY DATE(created_at), query_source
789 ORDER BY DATE(created_at) ASC, COUNT(*) DESC
790 "#,
791 )
792 .bind(link_id)
793 .fetch_all(pool)
794 .await?
795 }
796 DatabasePool::Sqlite(pool) => {
797 sqlx::query_as::<_, SourceStats>(
798 r#"
799 SELECT
800 DATE(created_at) as date,
801 query_source as source,
802 COUNT(*) as count
803 FROM clicks
804 WHERE link_id = ?
805 AND query_source IS NOT NULL
806 AND query_source != ''
807 GROUP BY DATE(created_at), query_source
808 ORDER BY DATE(created_at) ASC, COUNT(*) DESC
809 "#,
810 )
811 .bind(link_id)
812 .fetch_all(pool)
813 .await?
814 }
815 };
816
817 Ok(HttpResponse::Ok().json(sources))
818}
819
820pub async fn check_first_user(state: web::Data<AppState>) -> Result<impl Responder, AppError> {
821 let user_count = match &state.db {
822 DatabasePool::Postgres(pool) => {
823 sqlx::query_as::<Postgres, (i64,)>("SELECT COUNT(*)::bigint FROM users")
824 .fetch_one(pool)
825 .await?
826 .0
827 }
828 DatabasePool::Sqlite(pool) => {
829 sqlx::query_as::<Sqlite, (i64,)>("SELECT COUNT(*) FROM users")
830 .fetch_one(pool)
831 .await?
832 .0
833 }
834 };
835
836 Ok(HttpResponse::Ok().json(json!({
837 "isFirstUser": user_count == 0
838 })))
839}