Create POST /api/tips/refresh-total endpoint to update tip statistics #255
Description
Description
While tip history is fetched from the Horizon API in real-time, the database columns (total_tips_received, total_tips_count) need to be periodically refreshed to stay in sync with the blockchain. We need a POST endpoint that recalculates and updates these totals.
Current State
- Database has
total_tips_received,total_tips_count,last_tip_atcolumns in users table - No mechanism to refresh these totals from blockchain data
- Totals may become stale if tips are sent directly on-chain (outside the app)
What Needs to Happen
- Create a new API route at
app/api/tips/refresh-total/route.ts - Implement POST handler that:
- Accepts request body with
usernameorstellarPublicKey - Fetches ALL tips received for the user from Horizon API (paginate through all results)
- Calculates total XLM received and total tip count
- Updates the user's database record with new totals
- Returns updated statistics
- Accepts request body with
- Implement pagination loop:
- Horizon API returns max 200 records per page
- Loop through all pages using cursor to get complete history
- Sum all tip amounts
- Handle edge cases:
- User not found
- User has no Stellar public key
- No tips received yet (set to 0)
- Horizon API errors
- Add authentication/authorization:
- Only the user themselves (or admins) should be able to refresh their totals
- Verify user session/token before processing
- Add rate limiting:
- Prevent abuse by limiting refreshes (e.g., once per 5 minutes per user)
- Return updated statistics in response
Code Structure
// app/api/tips/refresh-total/route.ts
import { NextResponse } from 'next/server';
import { sql } from '@vercel/postgres';
import { fetchPaymentsReceived } from '@/lib/stellar/horizon';
interface RefreshTotalRequest {
username: string;
}
interface RefreshTotalResponse {
username: string;
totalReceived: string;
totalCount: number;
lastTipAt: string | null;
refreshedAt: string;
}
export async function POST(request: Request) {
try {
const { username } = await request.json();
if (!username) {
return NextResponse.json(
{ error: 'Username is required' },
{ status: 400 }
);
}
// TODO: Add authentication check here
// Verify that the requesting user is the owner or admin
// 1. Fetch user from database
const userResult = await sql`
SELECT id, username, stellar_public_key
FROM users
WHERE username = ${username}
`;
if (userResult.rows.length === 0) {
return NextResponse.json(
{ error: 'User not found' },
{ status: 404 }
);
}
const user = userResult.rows[0];
if (!user.stellar_public_key) {
return NextResponse.json(
{ error: 'User has not configured Stellar wallet' },
{ status: 400 }
);
}
// 2. Fetch all tips from Horizon API
let allTips: any[] = [];
let cursor: string | undefined = undefined;
let hasMore = true;
while (hasMore) {
const { tips, nextCursor } = await fetchPaymentsReceived({
publicKey: user.stellar_public_key,
limit: 200,
cursor
});
allTips = [...allTips, ...tips];
cursor = nextCursor || undefined;
hasMore = !!nextCursor;
}
// 3. Calculate totals
const totalReceived = allTips.reduce((sum, tip) => {
return sum + parseFloat(tip.amount);
}, 0).toFixed(7);
const totalCount = allTips.length;
const lastTipAt = allTips.length > 0 ? allTips[0].timestamp : null;
// 4. Update database
await sql`
UPDATE users
SET
total_tips_received = ${totalReceived},
total_tips_count = ${totalCount},
last_tip_at = ${lastTipAt},
updated_at = CURRENT_TIMESTAMP
WHERE id = ${user.id}
`;
// 5. Return updated statistics
return NextResponse.json({
username: user.username,
totalReceived,
totalCount,
lastTipAt,
refreshedAt: new Date().toISOString()
});
} catch (error) {
console.error('Refresh total error:', error);
return NextResponse.json(
{ error: 'Failed to refresh tip totals' },
{ status: 500 }
);
}
}Files Affected
app/api/tips/refresh-total/route.ts(NEW)
Acceptance Criteria
- POST endpoint created at
/api/tips/refresh-total - Endpoint accepts
usernamein request body - Returns 404 if user not found
- Returns 400 if user has no Stellar public key
- Fetches ALL tips from Horizon API using pagination
- Calculates total XLM received and tip count correctly
- Updates
total_tips_received,total_tips_count,last_tip_atin database - Returns updated statistics in response
- Authentication implemented (only user or admin can refresh)
- Rate limiting implemented (max once per 5 minutes)
- Error handling for all edge cases
- TypeScript types defined for request/response
- Handles users with no tips gracefully (sets to 0)
- Loom video submitted showing endpoint refreshing totals and updating database correctly
Useful Resources
- Next.js Route Handlers: https://nextjs.org/docs/app/building-your-application/routing/route-handlers
- Stellar Horizon API Pagination: https://developers.stellar.org/docs/data/horizon/api-reference/structure/pagination
- NextAuth.js (for authentication): https://next-auth.js.org/
Notes
- This endpoint will be expensive for users with many tips (many Horizon API calls)
- Consider adding a background job/cron to refresh totals periodically instead of on-demand
- For MVP, authentication can be simple (check session), but should be added before production
- Consider adding a
forceparameter to bypass rate limiting for admin users - The refresh should be idempotent - running multiple times should give same result
- Consider caching the last refresh timestamp to show users when data was last updated