Skip to main content

max / makenotwork

7.9 KB · 231 lines History Blame Raw
1 //! Tip CRUD operations.
2
3 use sqlx::PgPool;
4
5 use super::id_types::{ProjectId, TipId, UserId};
6 use super::models::{DbTip, DbTipWithUser};
7 use super::validated_types::Cents;
8 use crate::error::Result;
9
10 /// Create a pending tip record before redirecting to Stripe Checkout.
11 #[tracing::instrument(skip(pool))]
12 pub async fn create_tip(
13 pool: &PgPool,
14 tipper_id: UserId,
15 recipient_id: UserId,
16 project_id: Option<ProjectId>,
17 amount_cents: i32,
18 message: Option<&str>,
19 stripe_checkout_session_id: &str,
20 ) -> Result<DbTip> {
21 // Defense in depth: the route handler enforces a $1 minimum and the column
22 // carries CHECK (amount_cents > 0), but guard here too so any future caller
23 // gets a clean validation error instead of a raw constraint violation.
24 if amount_cents <= 0 {
25 return Err(crate::error::AppError::validation(
26 "Tip amount must be positive",
27 ));
28 }
29 let tip = sqlx::query_as!(
30 DbTip,
31 r#"
32 INSERT INTO tips (tipper_id, recipient_id, project_id, amount_cents, message, stripe_checkout_session_id)
33 VALUES ($1, $2, $3, $4, $5, $6)
34 RETURNING
35 id AS "id: TipId", tipper_id AS "tipper_id: UserId", recipient_id AS "recipient_id: UserId",
36 project_id AS "project_id: ProjectId", amount_cents AS "amount_cents: Cents", message,
37 status AS "status: super::TransactionStatus", stripe_payment_intent_id, stripe_checkout_session_id,
38 stripe_transfer_group,
39 created_at AS "created_at: chrono::DateTime<chrono::Utc>",
40 completed_at AS "completed_at: chrono::DateTime<chrono::Utc>"
41 "#,
42 tipper_id as UserId,
43 recipient_id as UserId,
44 project_id as Option<ProjectId>,
45 amount_cents,
46 message,
47 stripe_checkout_session_id,
48 )
49 .fetch_one(pool)
50 .await?;
51
52 Ok(tip)
53 }
54
55 /// Mark a tip as completed after Stripe confirms payment.
56 /// Returns `Some(tip)` if updated, `None` if already completed (idempotent).
57 #[tracing::instrument(skip(executor))]
58 pub async fn complete_tip<'e>(
59 executor: impl sqlx::PgExecutor<'e>,
60 stripe_checkout_session_id: &str,
61 stripe_payment_intent_id: Option<&str>,
62 ) -> Result<Option<DbTip>> {
63 let tip = sqlx::query_as!(
64 DbTip,
65 r#"
66 UPDATE tips
67 SET status = 'completed',
68 stripe_payment_intent_id = $2,
69 completed_at = NOW()
70 WHERE stripe_checkout_session_id = $1
71 AND status = 'pending'
72 RETURNING
73 id AS "id: TipId", tipper_id AS "tipper_id: UserId", recipient_id AS "recipient_id: UserId",
74 project_id AS "project_id: ProjectId", amount_cents AS "amount_cents: Cents", message,
75 status AS "status: super::TransactionStatus", stripe_payment_intent_id, stripe_checkout_session_id,
76 stripe_transfer_group,
77 created_at AS "created_at: chrono::DateTime<chrono::Utc>",
78 completed_at AS "completed_at: chrono::DateTime<chrono::Utc>"
79 "#,
80 stripe_checkout_session_id,
81 stripe_payment_intent_id,
82 )
83 .fetch_optional(executor)
84 .await?;
85
86 Ok(tip)
87 }
88
89 /// Fetch a tip by its Stripe checkout session id, regardless of status.
90 ///
91 /// Used for webhook crash recovery: when `complete_tip` returns `None` (the tip
92 /// was already flipped to completed by an earlier delivery), the handler re-reads
93 /// the tip here to re-run the idempotent split write, in case the first delivery
94 /// crashed after completing the tip but before recording splits.
95 #[tracing::instrument(skip(pool))]
96 pub async fn get_tip_by_session(
97 pool: &PgPool,
98 stripe_checkout_session_id: &str,
99 ) -> Result<Option<DbTip>> {
100 let tip = sqlx::query_as!(
101 DbTip,
102 r#"
103 SELECT
104 id AS "id: TipId", tipper_id AS "tipper_id: UserId", recipient_id AS "recipient_id: UserId",
105 project_id AS "project_id: ProjectId", amount_cents AS "amount_cents: Cents", message,
106 status AS "status: super::TransactionStatus", stripe_payment_intent_id, stripe_checkout_session_id,
107 stripe_transfer_group,
108 created_at AS "created_at: chrono::DateTime<chrono::Utc>",
109 completed_at AS "completed_at: chrono::DateTime<chrono::Utc>"
110 FROM tips
111 WHERE stripe_checkout_session_id = $1
112 "#,
113 stripe_checkout_session_id,
114 )
115 .fetch_optional(pool)
116 .await?;
117
118 Ok(tip)
119 }
120
121 /// Get tips received by a creator, most recent first.
122 #[tracing::instrument(skip(pool))]
123 pub async fn get_tips_received(
124 pool: &PgPool,
125 recipient_id: UserId,
126 limit: i64,
127 offset: i64,
128 ) -> Result<Vec<DbTipWithUser>> {
129 let tips = sqlx::query_as!(
130 DbTipWithUser,
131 r#"
132 SELECT t.id AS "id: TipId", t.tipper_id AS "tipper_id: UserId", t.recipient_id AS "recipient_id: UserId",
133 t.project_id AS "project_id: ProjectId", t.amount_cents AS "amount_cents: Cents",
134 t.message, t.status AS "status: super::TransactionStatus",
135 t.created_at AS "created_at: chrono::DateTime<chrono::Utc>",
136 t.completed_at AS "completed_at: chrono::DateTime<chrono::Utc>",
137 u.username AS tipper_username, u.display_name AS tipper_display_name
138 FROM tips t
139 JOIN users u ON u.id = t.tipper_id
140 WHERE t.recipient_id = $1 AND t.status = 'completed'
141 ORDER BY t.created_at DESC
142 LIMIT $2 OFFSET $3
143 "#,
144 recipient_id as UserId,
145 limit,
146 offset,
147 )
148 .fetch_all(pool)
149 .await?;
150
151 Ok(tips)
152 }
153
154 /// Total tip revenue received by a creator (completed tips only).
155 #[tracing::instrument(skip(pool))]
156 pub async fn total_tips_received(pool: &PgPool, recipient_id: UserId) -> Result<i64> {
157 let total = sqlx::query_scalar!(
158 r#"SELECT COALESCE(SUM(amount_cents), 0)::BIGINT AS "total!" FROM tips WHERE recipient_id = $1 AND status = 'completed'"#,
159 recipient_id as UserId,
160 )
161 .fetch_one(pool)
162 .await?;
163
164 Ok(total)
165 }
166
167 /// Count of completed tips received.
168 #[tracing::instrument(skip(pool))]
169 pub async fn count_tips_received(pool: &PgPool, recipient_id: UserId) -> Result<i64> {
170 let count = sqlx::query_scalar!(
171 r#"SELECT COUNT(*) AS "count!" FROM tips WHERE recipient_id = $1 AND status = 'completed'"#,
172 recipient_id as UserId,
173 )
174 .fetch_one(pool)
175 .await?;
176
177 Ok(count)
178 }
179
180 /// Mark a tip as refunded by payment intent ID.
181 /// Returns true if a tip was refunded, false if not found (idempotent).
182 #[tracing::instrument(skip(pool))]
183 pub async fn refund_tip_by_payment_intent(pool: &PgPool, payment_intent_id: &str) -> Result<bool> {
184 let result = sqlx::query!(
185 r#"
186 UPDATE tips
187 SET status = 'refunded'
188 WHERE stripe_payment_intent_id = $1 AND status = 'completed'
189 "#,
190 payment_intent_id,
191 )
192 .execute(pool)
193 .await?;
194
195 Ok(result.rows_affected() > 0)
196 }
197
198 /// Get tips sent by a user, most recent first.
199 #[allow(dead_code)]
200 #[tracing::instrument(skip(pool))]
201 pub async fn get_tips_sent(
202 pool: &PgPool,
203 tipper_id: UserId,
204 limit: i64,
205 offset: i64,
206 ) -> Result<Vec<DbTip>> {
207 let tips = sqlx::query_as!(
208 DbTip,
209 r#"
210 SELECT
211 id AS "id: TipId", tipper_id AS "tipper_id: UserId", recipient_id AS "recipient_id: UserId",
212 project_id AS "project_id: ProjectId", amount_cents AS "amount_cents: Cents", message,
213 status AS "status: super::TransactionStatus", stripe_payment_intent_id, stripe_checkout_session_id,
214 stripe_transfer_group,
215 created_at AS "created_at: chrono::DateTime<chrono::Utc>",
216 completed_at AS "completed_at: chrono::DateTime<chrono::Utc>"
217 FROM tips
218 WHERE tipper_id = $1 AND status = 'completed'
219 ORDER BY created_at DESC
220 LIMIT $2 OFFSET $3
221 "#,
222 tipper_id as UserId,
223 limit,
224 offset,
225 )
226 .fetch_all(pool)
227 .await?;
228
229 Ok(tips)
230 }
231