an app to share curated trails
sidetrail.app
1import "server-only";
2import { cache } from "react";
3import { notFound } from "next/navigation";
4import { cacheLife } from "next/cache";
5import { getDb, trails, walks, completions, accounts, type TrailRecord } from "@/data/db";
6import { desc, eq, inArray, sql } from "drizzle-orm";
7import { IdResolver } from "@atproto/identity";
8import { AtUri } from "@atproto/syntax";
9import { tagDid, tagHandle, tagAvatar } from "@/cache/tags";
10import { getCurrentDid } from "@/auth";
11
12// ============================================================================
13// Types
14// ============================================================================
15
16export type User = {
17 did: string;
18 handle: string;
19 avatar?: string;
20};
21
22export type ExternalEmbed = {
23 uri: string;
24 title?: string;
25 description?: string;
26 thumb?: string;
27};
28
29export type TrailStop = {
30 tid: string;
31 title: string;
32 content: string;
33 buttonText?: string;
34 external?: ExternalEmbed;
35 walkersHere: Promise<User[]>;
36};
37
38export type TrailCardData = {
39 uri: string;
40 rkey: string;
41 creatorHandle: string;
42 title: string;
43 description: string;
44 accentColor: string;
45 backgroundColor: string;
46 creator: User;
47 stopsCount: number;
48 createdAt: Date;
49};
50
51export type WalkCardData = {
52 walkRkey: string;
53 walkerDid: string;
54 trailRkey: string;
55 trailCreatorHandle: string;
56 title: string;
57 description: string;
58 accentColor: string;
59 backgroundColor: string;
60 stops: Omit<TrailStop, "walkersHere">[];
61 visitedStops: string[];
62 updatedAt: Date;
63};
64
65export type TrailHeaderData = {
66 uri: string;
67 cid: string;
68 rkey: string;
69 title: string;
70 description: string;
71 stopsCount: number;
72 accentColor: string;
73 backgroundColor: string;
74 creator: User;
75 createdAt: Date;
76};
77
78export type TrailDetailData = {
79 header: TrailHeaderData;
80 stops: TrailStop[];
81 yourWalk: {
82 uri: string;
83 visitedStops: string[];
84 createdAt: Date;
85 updatedAt: Date;
86 } | null;
87 walkers: Promise<Array<{ user: User; isYou: boolean; key: string }>>;
88 completions: Promise<
89 Array<{ user: User; timestamp: Date; completionUri: string; isYou: boolean }>
90 >;
91};
92
93export type CompletedTrailData = {
94 uri: string;
95 rkey: string;
96 title: string;
97 description: string;
98 accentColor: string;
99 backgroundColor: string;
100 stopsCount: number;
101 creator: User;
102 completedAt: Date;
103};
104
105// ============================================================================
106// Account Status
107// ============================================================================
108
109async function requireActiveAccount(did: string): Promise<void> {
110 const db = getDb();
111 const [row] = await db
112 .select({ active: accounts.active })
113 .from(accounts)
114 .where(eq(accounts.did, did))
115 .limit(1);
116
117 if (!row) return;
118
119 if (row.active === 0) {
120 notFound();
121 }
122}
123
124// ============================================================================
125// DID Resolution
126// ============================================================================
127
128const idResolver = new IdResolver();
129
130const resolveDidToHandle = cache(async function resolveDidToHandle(did: string): Promise<string> {
131 "use cache: redis";
132 tagDid(did);
133
134 const didDoc = await idResolver.did.resolve(did);
135 for (const aka of didDoc?.alsoKnownAs ?? []) {
136 if (typeof aka === "string" && aka.startsWith("at://")) {
137 return aka.replace("at://", "");
138 }
139 }
140
141 throw Error("Could not resolve did: " + did);
142});
143
144export const resolveHandleToDid = cache(async function resolveHandleToDid(
145 handle: string,
146): Promise<string> {
147 "use cache: redis";
148 tagHandle(handle);
149
150 const did = await idResolver.handle.resolve(handle);
151 if (did) {
152 return did;
153 }
154 throw new Error(`Could not resolve handle: ${handle}`);
155});
156
157async function batchResolveHandles(dids: string[]): Promise<Map<string, string>> {
158 const unique = [...new Set(dids)];
159 const results = await Promise.all(unique.map((did) => resolveDidToHandle(did)));
160 return new Map(unique.map((did, i) => [did, results[i]]));
161}
162
163const fetchBskyAvatar = cache(async function fetchBskyAvatar(
164 did: string,
165): Promise<string | undefined> {
166 "use cache: redis";
167 tagAvatar(did);
168
169 const res = await fetch(
170 `https://public.api.bsky.app/xrpc/app.bsky.actor.getProfile?actor=${encodeURIComponent(did)}`,
171 { signal: AbortSignal.timeout(5000) },
172 );
173 if (!res.ok) return undefined;
174
175 const profile = await res.json();
176 return profile.avatar?.replace("/img/avatar/", "/img/avatar_thumbnail/");
177});
178
179export const tryFetchBskyAvatar = cache(async function tryFetchBskyAvatar(
180 did: string,
181): Promise<string | undefined> {
182 try {
183 return await fetchBskyAvatar(did);
184 } catch {
185 return undefined;
186 }
187});
188
189async function batchResolveUsers(dids: string[]): Promise<Map<string, User>> {
190 const unique = [...new Set(dids)];
191 if (unique.length === 0) return new Map();
192
193 const [handles, avatars] = await Promise.all([
194 Promise.all(unique.map((did) => resolveDidToHandle(did))),
195 Promise.all(unique.map((did) => tryFetchBskyAvatar(did))),
196 ]);
197
198 return new Map(unique.map((did, i) => [did, { did, handle: handles[i], avatar: avatars[i] }]));
199}
200
201function parseStopExternal(
202 ext: TrailRecord["stops"][0]["external"],
203 authorDid: string,
204): ExternalEmbed | undefined {
205 if (!ext) return undefined;
206 return {
207 uri: ext.uri,
208 title: ext.title,
209 description: ext.description,
210 thumb: ext.thumb?.ref?.$link
211 ? `https://cdn.bsky.app/img/feed_thumbnail/plain/${authorDid}/${ext.thumb.ref.$link}@jpeg`
212 : undefined,
213 };
214}
215
216// ============================================================================
217// Current User
218// ============================================================================
219
220export const loadCurrentDid = cache(getCurrentDid);
221
222export const loadCurrentUser = cache(async function loadCurrentUser(): Promise<User | null> {
223 const did = await loadCurrentDid();
224 if (!did) return null;
225
226 const [handle, avatar] = await Promise.all([resolveDidToHandle(did), tryFetchBskyAvatar(did)]);
227 return { did, handle, avatar };
228});
229
230// ============================================================================
231// Trails List (Home Page)
232// ============================================================================
233
234const HALF_LIFE_HOURS = 12;
235const DECAY_RATE = Math.log(2) / HALF_LIFE_HOURS; // ≈ 0.0578
236const RECENT_ACTIVITY_WINDOW_HOURS = 3;
237const GRACE_PERIOD_HOURS = 3; // new trails get exposure
238const MIN_ACTIVITY_FOR_RANKING = 5; // need this many to rank in main tier
239
240export const loadTrails = cache(async function loadTrails(): Promise<TrailCardData[]> {
241 const db = getDb();
242 const trailRows = await db.execute<{
243 uri: string;
244 cid: string;
245 author_did: string;
246 rkey: string;
247 record: TrailRecord;
248 created_at: Date;
249 indexed_at: Date;
250 }>(sql`
251 WITH activity AS (
252 SELECT trail_uri, author_did, created_at FROM walks
253 UNION ALL
254 SELECT trail_uri, author_did, created_at FROM completions
255 )
256 SELECT
257 t.uri, t.cid, t.author_did, t.rkey, t.record, t.created_at, t.indexed_at
258 FROM trails t
259 LEFT JOIN activity a ON a.trail_uri = t.uri AND a.author_did != t.author_did
260 LEFT JOIN accounts acc ON acc.did = t.author_did
261 WHERE COALESCE(acc.active, 1) = 1
262 AND COALESCE(acc.shadowban, 0) = 0
263 GROUP BY t.uri, t.cid, t.author_did, t.rkey, t.record, t.created_at, t.indexed_at
264 ORDER BY
265 -- Tier: 0 = grace period (new), 1 = proven (enough activity), 2 = unproven
266 CASE
267 WHEN EXTRACT(EPOCH FROM (NOW() - t.created_at))/3600 < ${GRACE_PERIOD_HOURS}::float THEN 0
268 WHEN COUNT(a.trail_uri) >= ${MIN_ACTIVITY_FOR_RANKING} THEN 1
269 ELSE 2
270 END,
271 -- Score within tier: (1 + sqrt(recent_activity)) * recency_decay
272 (1 + SQRT(SUM(CASE WHEN EXTRACT(EPOCH FROM (NOW() - a.created_at))/3600 < ${RECENT_ACTIVITY_WINDOW_HOURS}::float THEN 1 ELSE 0 END)))
273 * EXP(${DECAY_RATE}::float * -1.0 * EXTRACT(EPOCH FROM (NOW() - GREATEST(t.created_at, COALESCE(MAX(a.created_at), t.created_at))))/3600)
274 DESC
275 LIMIT 100
276 `);
277
278 if (trailRows.rows.length === 0) return [];
279
280 const authorDids = trailRows.rows.map((t) => t.author_did);
281 const userMap = await batchResolveUsers(authorDids);
282
283 return trailRows.rows.map((row) => {
284 const creator = userMap.get(row.author_did)!;
285 return {
286 uri: row.uri,
287 rkey: row.rkey,
288 creatorHandle: creator.handle,
289 title: row.record.title,
290 description: row.record.description,
291 accentColor: row.record.accentColor,
292 backgroundColor: row.record.backgroundColor,
293 creator,
294 stopsCount: row.record.stops.length,
295 createdAt: row.created_at,
296 };
297 });
298});
299
300export const loadTrailCardByUri = cache(async function loadTrailCardByUri(
301 uri: string,
302): Promise<TrailCardData | null> {
303 const db = getDb();
304 const parsed = new AtUri(uri);
305 const did = parsed.host.startsWith("did:") ? parsed.host : await resolveHandleToDid(parsed.host);
306
307 const trailRows = await db
308 .select()
309 .from(trails)
310 .where(eq(trails.uri, `at://${did}/${parsed.collection}/${parsed.rkey}`))
311 .limit(1);
312
313 if (trailRows.length === 0) return null;
314
315 const row = trailRows[0];
316 const userMap = await batchResolveUsers([row.authorDid]);
317 const creator = userMap.get(row.authorDid);
318 if (!creator) return null;
319
320 return {
321 uri: row.uri,
322 rkey: row.rkey,
323 creatorHandle: creator.handle,
324 title: row.record.title,
325 description: row.record.description,
326 accentColor: row.record.accentColor,
327 backgroundColor: row.record.backgroundColor,
328 creator,
329 stopsCount: row.record.stops.length,
330 createdAt: row.createdAt,
331 };
332});
333
334export const loadTrailActiveWalkers = cache(async function loadTrailActiveWalkers(
335 trailUri: string,
336): Promise<User[]> {
337 const db = getDb();
338
339 const recentWalks = await db.execute<{
340 author_did: string;
341 }>(sql`
342 SELECT author_did FROM (
343 SELECT author_did,
344 ROW_NUMBER() OVER (ORDER BY last_activity DESC) as rn
345 FROM (
346 SELECT DISTINCT ON (w.author_did) w.author_did,
347 COALESCE((w.record->>'updatedAt')::timestamptz, w.created_at) as last_activity
348 FROM walks w
349 LEFT JOIN accounts a ON a.did = w.author_did
350 WHERE w.trail_uri = ${trailUri}
351 AND COALESCE(a.active, 1) = 1
352 ORDER BY w.author_did, COALESCE((w.record->>'updatedAt')::timestamptz, w.created_at) DESC
353 ) unique_walkers
354 ) ranked
355 WHERE rn <= 5
356 `);
357
358 if (recentWalks.rows.length === 0) return [];
359
360 const walkerDids = recentWalks.rows.map((w) => w.author_did);
361 const walkerUserMap = await batchResolveUsers(walkerDids);
362
363 return recentWalks.rows.map((row) => walkerUserMap.get(row.author_did)!).filter(Boolean);
364});
365
366// ============================================================================
367// Trail Detail
368// ============================================================================
369
370async function loadTrailRecord(trailUri: string) {
371 const db = getDb();
372 const [row] = await db.select().from(trails).where(eq(trails.uri, trailUri)).limit(1);
373 if (!row) throw new Error("Trail not found");
374 return row;
375}
376
377async function loadUserWalk(trailUri: string, userDid: string | null) {
378 if (!userDid) return null;
379 const db = getDb();
380 const result = await db.execute<{
381 uri: string;
382 record: typeof walks.$inferSelect.record;
383 created_at: Date;
384 }>(sql`
385 SELECT uri, record, created_at
386 FROM walks
387 WHERE author_did = ${userDid} AND trail_uri = ${trailUri}
388 ORDER BY COALESCE((record->>'updatedAt')::timestamptz, created_at) DESC
389 LIMIT 1
390 `);
391 const row = result.rows[0];
392 if (!row) return null;
393 return {
394 uri: row.uri,
395 visitedStops: row.record.visitedStops,
396 createdAt: new Date(row.created_at),
397 updatedAt: row.record.updatedAt ? new Date(row.record.updatedAt) : new Date(row.created_at),
398 };
399}
400
401type WalkData = {
402 uri: string;
403 cid: string;
404 authorDid: string;
405 rkey: string;
406 trailUri: string;
407 record: typeof walks.$inferSelect.record;
408 createdAt: Date;
409 indexedAt: Date;
410};
411
412type CompletionData = {
413 uri: string;
414 cid: string;
415 authorDid: string;
416 rkey: string;
417 trailUri: string;
418 record: typeof completions.$inferSelect.record;
419 createdAt: Date;
420 indexedAt: Date;
421};
422
423async function loadTrailWalks(
424 trailUri: string,
425): Promise<{ walksData: WalkData[]; walkUserMap: Map<string, User> }> {
426 const db = getDb();
427
428 const trailWalks = await db.execute<{
429 uri: string;
430 cid: string;
431 author_did: string;
432 rkey: string;
433 trail_uri: string;
434 record: typeof walks.$inferSelect.record;
435 created_at: Date;
436 indexed_at: Date;
437 }>(sql`
438 SELECT DISTINCT ON (w.author_did) w.uri, w.cid, w.author_did, w.rkey, w.trail_uri, w.record, w.created_at, w.indexed_at
439 FROM walks w
440 LEFT JOIN accounts a ON a.did = w.author_did
441 WHERE w.trail_uri = ${trailUri}
442 AND COALESCE(a.active, 1) = 1
443 ORDER BY w.author_did, COALESCE((w.record->>'updatedAt')::timestamptz, w.created_at) DESC
444 `);
445
446 const walksData = trailWalks.rows.map((row) => ({
447 uri: row.uri,
448 cid: row.cid,
449 authorDid: row.author_did,
450 rkey: row.rkey,
451 trailUri: row.trail_uri,
452 record: row.record,
453 createdAt: new Date(row.created_at),
454 indexedAt: new Date(row.indexed_at),
455 }));
456
457 const walkDids = walksData.map((w) => w.authorDid);
458 const walkUserMap = await batchResolveUsers(walkDids);
459
460 return { walksData, walkUserMap };
461}
462
463async function loadTrailCompletions(
464 trailUri: string,
465): Promise<{ completionsData: CompletionData[]; completionUserMap: Map<string, User> }> {
466 const db = getDb();
467
468 const trailCompletions = await db.execute<{
469 uri: string;
470 cid: string;
471 author_did: string;
472 rkey: string;
473 trail_uri: string;
474 record: typeof completions.$inferSelect.record;
475 created_at: Date;
476 indexed_at: Date;
477 }>(sql`
478 SELECT c.uri, c.cid, c.author_did, c.rkey, c.trail_uri, c.record, c.created_at, c.indexed_at
479 FROM completions c
480 LEFT JOIN accounts a ON a.did = c.author_did
481 WHERE c.trail_uri = ${trailUri}
482 AND COALESCE(a.active, 1) = 1
483 ORDER BY c.created_at DESC
484 LIMIT 100
485 `);
486
487 const completionsData = trailCompletions.rows.map((row) => ({
488 uri: row.uri,
489 cid: row.cid,
490 authorDid: row.author_did,
491 rkey: row.rkey,
492 trailUri: row.trail_uri,
493 record: row.record,
494 createdAt: new Date(row.created_at),
495 indexedAt: new Date(row.indexed_at),
496 }));
497
498 const completionDids = completionsData.map((c) => c.authorDid);
499 const completionUserMap = await batchResolveUsers(completionDids);
500
501 return { completionsData, completionUserMap };
502}
503
504function buildCompletionsList(
505 completionsData: CompletionData[],
506 userMap: Map<string, User>,
507 currentUserDid: string | null,
508) {
509 return completionsData.map((c) => ({
510 user: userMap.get(c.authorDid)!,
511 timestamp: c.createdAt,
512 completionUri: c.uri,
513 isYou: c.authorDid === currentUserDid,
514 }));
515}
516
517function buildWalkersList(
518 walksData: WalkData[],
519 completionsData: CompletionData[],
520 walkUserMap: Map<string, User>,
521 completionUserMap: Map<string, User>,
522 currentUserDid: string | null,
523) {
524 const activityMap = new Map<
525 string,
526 { user: User; isYou: boolean; key: string; timestamp: Date }
527 >();
528
529 for (const c of completionsData) {
530 activityMap.set(c.authorDid, {
531 user: completionUserMap.get(c.authorDid)!,
532 isYou: c.authorDid === currentUserDid,
533 key: c.uri,
534 timestamp: c.createdAt,
535 });
536 }
537
538 for (const walk of walksData) {
539 const timestamp = walk.record.updatedAt ? new Date(walk.record.updatedAt) : walk.createdAt;
540 const existing = activityMap.get(walk.authorDid);
541 if (!existing || timestamp > existing.timestamp) {
542 activityMap.set(walk.authorDid, {
543 user: walkUserMap.get(walk.authorDid)!,
544 isYou: walk.authorDid === currentUserDid,
545 key: walk.uri,
546 timestamp,
547 });
548 }
549 }
550
551 return Array.from(activityMap.values())
552 .sort((a, b) => b.timestamp.getTime() - a.timestamp.getTime())
553 .map(({ timestamp: _, ...rest }) => rest);
554}
555
556function buildWalkersAtStopMap(walksData: WalkData[], userMap: Map<string, User>) {
557 const stopMap = new Map<string, User[]>();
558 for (const walk of walksData) {
559 const visited = walk.record.visitedStops;
560 if (visited.length > 0) {
561 const currentStop = visited[visited.length - 1];
562 const user = userMap.get(walk.authorDid);
563 if (user) {
564 const list = stopMap.get(currentStop) ?? [];
565 list.push(user);
566 stopMap.set(currentStop, list);
567 }
568 }
569 }
570 return stopMap;
571}
572
573export const loadTrailDetail = cache(async function loadTrailDetail(
574 handle: string,
575 rkey: string,
576): Promise<TrailDetailData> {
577 const currentUserDidPromise = loadCurrentDid();
578 const creatorDidPromise = resolveHandleToDid(handle);
579
580 const creatorDid = await creatorDidPromise;
581
582 await requireActiveAccount(creatorDid);
583
584 const trailUri = `at://${creatorDid}/app.sidetrail.trail/${rkey}`;
585
586 const walksDataPromise = loadTrailWalks(trailUri);
587 const completionsDataPromise = loadTrailCompletions(trailUri);
588
589 const [trail, currentUserDid, creatorAvatar] = await Promise.all([
590 loadTrailRecord(trailUri),
591 currentUserDidPromise,
592 tryFetchBskyAvatar(creatorDid),
593 ]);
594
595 const yourWalkPromise = loadUserWalk(trailUri, currentUserDid);
596
597 const creator: User = { did: creatorDid, handle, avatar: creatorAvatar };
598
599 const walkersAtStopPromise = walksDataPromise.then(({ walksData, walkUserMap }) =>
600 buildWalkersAtStopMap(walksData, walkUserMap),
601 );
602
603 const completionsPromise = completionsDataPromise.then(({ completionsData, completionUserMap }) =>
604 buildCompletionsList(completionsData, completionUserMap, currentUserDid),
605 );
606
607 const walkersPromise = Promise.all([walksDataPromise, completionsDataPromise]).then(
608 ([{ walksData, walkUserMap }, { completionsData, completionUserMap }]) =>
609 buildWalkersList(walksData, completionsData, walkUserMap, completionUserMap, currentUserDid),
610 );
611
612 const stops: TrailStop[] = trail.record.stops.map((stop) => ({
613 tid: stop.tid,
614 title: stop.title,
615 content: stop.content,
616 buttonText: stop.buttonText,
617 external: parseStopExternal(stop.external, creatorDid),
618 walkersHere: walkersAtStopPromise.then((m) => m.get(stop.tid) ?? []),
619 }));
620
621 return {
622 header: {
623 uri: trail.uri,
624 cid: trail.cid,
625 rkey,
626 title: trail.record.title,
627 description: trail.record.description,
628 stopsCount: trail.record.stops.length,
629 accentColor: trail.record.accentColor,
630 backgroundColor: trail.record.backgroundColor,
631 creator,
632 createdAt: trail.createdAt,
633 },
634 stops,
635 yourWalk: await yourWalkPromise,
636 walkers: walkersPromise,
637 completions: completionsPromise,
638 };
639});
640
641export type TrailHeaderForOG = {
642 title: string;
643 description: string;
644 stopsCount: number;
645 accentColor: string;
646 backgroundColor: string;
647 creatorHandle: string;
648};
649
650export const loadTrailDetailForOG = cache(async function loadTrailDetailForOG(
651 handle: string,
652 rkey: string,
653): Promise<TrailHeaderForOG> {
654 "use cache";
655 cacheLife("max");
656
657 const creatorDid = await resolveHandleToDid(handle);
658 const trailUri = `at://${creatorDid}/app.sidetrail.trail/${rkey}`;
659 const trail = await loadTrailRecord(trailUri);
660
661 return {
662 title: trail.record.title,
663 description: trail.record.description,
664 stopsCount: trail.record.stops.length,
665 accentColor: trail.record.accentColor,
666 backgroundColor: trail.record.backgroundColor,
667 creatorHandle: handle,
668 };
669});
670
671// ============================================================================
672// User's Walks
673// ============================================================================
674
675export const loadWalks = cache(async function loadWalks(userDid?: string): Promise<WalkCardData[]> {
676 const did = userDid ?? (await loadCurrentDid());
677 if (!did) return [];
678
679 const db = getDb();
680
681 const walksWithTrails = await db.execute<{
682 walk_uri: string;
683 walk_rkey: string;
684 walk_record: typeof walks.$inferSelect.record;
685 walk_created_at: Date;
686 trail_uri: string;
687 trail_rkey: string;
688 trail_author_did: string;
689 trail_record: TrailRecord;
690 }>(sql`
691 SELECT DISTINCT ON (w.trail_uri)
692 w.uri as walk_uri, w.rkey as walk_rkey, w.record as walk_record, w.created_at as walk_created_at,
693 t.uri as trail_uri, t.rkey as trail_rkey, t.author_did as trail_author_did, t.record as trail_record
694 FROM walks w
695 INNER JOIN trails t ON w.trail_uri = t.uri
696 WHERE w.author_did = ${did}
697 ORDER BY w.trail_uri, COALESCE((w.record->>'updatedAt')::timestamptz, w.created_at) DESC
698 `);
699
700 if (walksWithTrails.rows.length === 0) return [];
701
702 const creatorDids = walksWithTrails.rows.map((r) => r.trail_author_did);
703 const handleMap = await batchResolveHandles(creatorDids);
704
705 const sortedWalks = walksWithTrails.rows.sort((a, b) => {
706 const aUpdated = a.walk_record.updatedAt
707 ? new Date(a.walk_record.updatedAt)
708 : new Date(a.walk_created_at);
709 const bUpdated = b.walk_record.updatedAt
710 ? new Date(b.walk_record.updatedAt)
711 : new Date(b.walk_created_at);
712 return bUpdated.getTime() - aUpdated.getTime();
713 });
714
715 return sortedWalks.map((row) => ({
716 walkRkey: row.walk_rkey,
717 walkerDid: did,
718 trailRkey: row.trail_rkey,
719 trailCreatorHandle: handleMap.get(row.trail_author_did) ?? row.trail_author_did,
720 title: row.trail_record.title,
721 description: row.trail_record.description,
722 accentColor: row.trail_record.accentColor,
723 backgroundColor: row.trail_record.backgroundColor,
724 stops: row.trail_record.stops.map((stop) => ({
725 tid: stop.tid,
726 title: stop.title,
727 content: stop.content,
728 buttonText: stop.buttonText,
729 external: parseStopExternal(stop.external, row.trail_author_did),
730 })),
731 visitedStops: row.walk_record.visitedStops,
732 updatedAt: row.walk_record.updatedAt
733 ? new Date(row.walk_record.updatedAt)
734 : new Date(row.walk_created_at),
735 }));
736});
737
738export const loadWalkingBadges = cache(async function loadWalkingBadges(): Promise<
739 Array<{ accentColor: string; key: string }>
740> {
741 const currentDid = await loadCurrentDid();
742 if (!currentDid) return [];
743
744 const db = getDb();
745
746 const walksWithTrails = await db.execute<{
747 trail_uri: string;
748 accent_color: string;
749 }>(sql`
750 SELECT trail_uri, accent_color FROM (
751 SELECT DISTINCT ON (w.trail_uri)
752 w.trail_uri,
753 t.record->>'accentColor' as accent_color,
754 COALESCE((w.record->>'updatedAt')::timestamptz, w.created_at) as last_activity
755 FROM walks w
756 INNER JOIN trails t ON w.trail_uri = t.uri
757 WHERE w.author_did = ${currentDid}
758 ORDER BY w.trail_uri, COALESCE((w.record->>'updatedAt')::timestamptz, w.created_at) DESC
759 ) unique_walks
760 ORDER BY last_activity DESC
761 LIMIT 3
762 `);
763
764 return walksWithTrails.rows.map((row) => ({
765 key: row.trail_uri,
766 accentColor: row.accent_color,
767 }));
768});
769
770export const loadDraftsBadges = cache(async function loadDraftsBadges(): Promise<
771 Array<{ accentColor: string; key: string }>
772> {
773 return [];
774});
775
776// ============================================================================
777// User Published Trails
778// ============================================================================
779
780export const loadUserPublishedTrails = cache(async function loadUserPublishedTrails(
781 handle: string,
782): Promise<TrailCardData[]> {
783 const userDid = await resolveHandleToDid(handle);
784
785 await requireActiveAccount(userDid);
786
787 const db = getDb();
788
789 const userTrails = await db
790 .select()
791 .from(trails)
792 .where(eq(trails.authorDid, userDid))
793 .orderBy(desc(trails.createdAt))
794 .limit(100);
795
796 const user: User = { did: userDid, handle };
797
798 return userTrails.map((trail) => ({
799 uri: trail.uri,
800 rkey: trail.rkey,
801 creatorHandle: handle,
802 title: trail.record.title,
803 description: trail.record.description,
804 accentColor: trail.record.accentColor,
805 backgroundColor: trail.record.backgroundColor,
806 creator: user,
807 stopsCount: trail.record.stops.length,
808 createdAt: trail.createdAt,
809 }));
810});
811
812// ============================================================================
813// User Completed Trails
814// ============================================================================
815
816export const loadUserCompletedTrails = cache(async function loadUserCompletedTrails(
817 handle: string,
818): Promise<CompletedTrailData[]> {
819 const userDid = await resolveHandleToDid(handle);
820
821 await requireActiveAccount(userDid);
822
823 const db = getDb();
824
825 const userCompletions = await db
826 .select()
827 .from(completions)
828 .where(eq(completions.authorDid, userDid))
829 .orderBy(desc(completions.createdAt))
830 .limit(100);
831
832 const completedTrailUris = [...new Set(userCompletions.map((c) => c.trailUri))];
833 const completedTrailsData =
834 completedTrailUris.length > 0
835 ? await db.select().from(trails).where(inArray(trails.uri, completedTrailUris))
836 : [];
837
838 const trailDataMap = new Map(completedTrailsData.map((t) => [t.uri, t]));
839 const creatorDids = completedTrailsData.map((t) => t.authorDid);
840 const creatorUserMap = await batchResolveUsers(creatorDids);
841
842 const seenUris = new Set<string>();
843 const completedTrails: CompletedTrailData[] = [];
844 for (const c of userCompletions) {
845 if (seenUris.has(c.trailUri)) continue;
846 seenUris.add(c.trailUri);
847
848 const trail = trailDataMap.get(c.trailUri);
849 if (!trail) continue;
850
851 completedTrails.push({
852 uri: trail.uri,
853 rkey: trail.rkey,
854 title: trail.record.title,
855 description: trail.record.description,
856 accentColor: trail.record.accentColor,
857 backgroundColor: trail.record.backgroundColor,
858 stopsCount: trail.record.stops.length,
859 creator: creatorUserMap.get(trail.authorDid) ?? {
860 did: trail.authorDid,
861 handle: trail.authorDid,
862 },
863 completedAt: c.createdAt,
864 });
865 }
866
867 return completedTrails;
868});