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;
237
238export const loadTrails = cache(async function loadTrails(): Promise<TrailCardData[]> {
239 const db = getDb();
240 const trailRows = await db.execute<{
241 uri: string;
242 cid: string;
243 author_did: string;
244 rkey: string;
245 record: TrailRecord;
246 created_at: Date;
247 indexed_at: Date;
248 }>(sql`
249 WITH activity AS (
250 SELECT trail_uri, author_did, created_at FROM walks
251 UNION ALL
252 SELECT trail_uri, author_did, created_at FROM completions
253 )
254 SELECT
255 t.uri, t.cid, t.author_did, t.rkey, t.record, t.created_at, t.indexed_at
256 FROM trails t
257 LEFT JOIN activity a ON a.trail_uri = t.uri AND a.author_did != t.author_did
258 LEFT JOIN accounts acc ON acc.did = t.author_did
259 WHERE COALESCE(acc.active, 1) = 1
260 AND COALESCE(acc.shadowban, 0) = 0
261 GROUP BY t.uri, t.cid, t.author_did, t.rkey, t.record, t.created_at, t.indexed_at
262 ORDER BY
263 -- Score: (1 + sqrt(recent_activity)) * recency_decay.
264 -- Self-calibrating: a brand-new trail scores 1.0, so it outranks anything
265 -- whose last activity is older than ~one half-life, while trails that are
266 -- hot right now stay above it. No tiers or grace windows needed.
267 (1 + SQRT(SUM(CASE WHEN EXTRACT(EPOCH FROM (NOW() - a.created_at))/3600 < ${RECENT_ACTIVITY_WINDOW_HOURS}::float THEN 1 ELSE 0 END)))
268 * EXP(${DECAY_RATE}::float * -1.0 * EXTRACT(EPOCH FROM (NOW() - GREATEST(t.created_at, COALESCE(MAX(a.created_at), t.created_at))))/3600)
269 DESC
270 LIMIT 100
271 `);
272
273 if (trailRows.rows.length === 0) return [];
274
275 const authorDids = trailRows.rows.map((t) => t.author_did);
276 const userMap = await batchResolveUsers(authorDids);
277
278 return trailRows.rows.map((row) => {
279 const creator = userMap.get(row.author_did)!;
280 return {
281 uri: row.uri,
282 rkey: row.rkey,
283 creatorHandle: creator.handle,
284 title: row.record.title,
285 description: row.record.description,
286 accentColor: row.record.accentColor,
287 backgroundColor: row.record.backgroundColor,
288 creator,
289 stopsCount: row.record.stops.length,
290 createdAt: row.created_at,
291 };
292 });
293});
294
295export const loadTrailCardByUri = cache(async function loadTrailCardByUri(
296 uri: string,
297): Promise<TrailCardData | null> {
298 const db = getDb();
299 const parsed = new AtUri(uri);
300 const did = parsed.host.startsWith("did:") ? parsed.host : await resolveHandleToDid(parsed.host);
301
302 const trailRows = await db
303 .select()
304 .from(trails)
305 .where(eq(trails.uri, `at://${did}/${parsed.collection}/${parsed.rkey}`))
306 .limit(1);
307
308 if (trailRows.length === 0) return null;
309
310 const row = trailRows[0];
311 const userMap = await batchResolveUsers([row.authorDid]);
312 const creator = userMap.get(row.authorDid);
313 if (!creator) return null;
314
315 return {
316 uri: row.uri,
317 rkey: row.rkey,
318 creatorHandle: creator.handle,
319 title: row.record.title,
320 description: row.record.description,
321 accentColor: row.record.accentColor,
322 backgroundColor: row.record.backgroundColor,
323 creator,
324 stopsCount: row.record.stops.length,
325 createdAt: row.createdAt,
326 };
327});
328
329export const loadTrailActiveWalkers = cache(async function loadTrailActiveWalkers(
330 trailUri: string,
331): Promise<User[]> {
332 const db = getDb();
333
334 const recentWalks = await db.execute<{
335 author_did: string;
336 }>(sql`
337 SELECT author_did FROM (
338 SELECT author_did,
339 ROW_NUMBER() OVER (ORDER BY last_activity DESC) as rn
340 FROM (
341 SELECT DISTINCT ON (w.author_did) w.author_did,
342 COALESCE((w.record->>'updatedAt')::timestamptz, w.created_at) as last_activity
343 FROM walks w
344 LEFT JOIN accounts a ON a.did = w.author_did
345 WHERE w.trail_uri = ${trailUri}
346 AND COALESCE(a.active, 1) = 1
347 ORDER BY w.author_did, COALESCE((w.record->>'updatedAt')::timestamptz, w.created_at) DESC
348 ) unique_walkers
349 ) ranked
350 WHERE rn <= 5
351 `);
352
353 if (recentWalks.rows.length === 0) return [];
354
355 const walkerDids = recentWalks.rows.map((w) => w.author_did);
356 const walkerUserMap = await batchResolveUsers(walkerDids);
357
358 return recentWalks.rows.map((row) => walkerUserMap.get(row.author_did)!).filter(Boolean);
359});
360
361// ============================================================================
362// Trail Detail
363// ============================================================================
364
365async function loadTrailRecord(trailUri: string) {
366 const db = getDb();
367 const [row] = await db.select().from(trails).where(eq(trails.uri, trailUri)).limit(1);
368 if (!row) throw new Error("Trail not found");
369 return row;
370}
371
372async function loadUserWalk(trailUri: string, userDid: string | null) {
373 if (!userDid) return null;
374 const db = getDb();
375 const result = await db.execute<{
376 uri: string;
377 record: typeof walks.$inferSelect.record;
378 created_at: Date;
379 }>(sql`
380 SELECT uri, record, created_at
381 FROM walks
382 WHERE author_did = ${userDid} AND trail_uri = ${trailUri}
383 ORDER BY COALESCE((record->>'updatedAt')::timestamptz, created_at) DESC
384 LIMIT 1
385 `);
386 const row = result.rows[0];
387 if (!row) return null;
388 return {
389 uri: row.uri,
390 visitedStops: row.record.visitedStops,
391 createdAt: new Date(row.created_at),
392 updatedAt: row.record.updatedAt ? new Date(row.record.updatedAt) : new Date(row.created_at),
393 };
394}
395
396type WalkData = {
397 uri: string;
398 cid: string;
399 authorDid: string;
400 rkey: string;
401 trailUri: string;
402 record: typeof walks.$inferSelect.record;
403 createdAt: Date;
404 indexedAt: Date;
405};
406
407type CompletionData = {
408 uri: string;
409 cid: string;
410 authorDid: string;
411 rkey: string;
412 trailUri: string;
413 record: typeof completions.$inferSelect.record;
414 createdAt: Date;
415 indexedAt: Date;
416};
417
418async function loadTrailWalks(
419 trailUri: string,
420): Promise<{ walksData: WalkData[]; walkUserMap: Map<string, User> }> {
421 const db = getDb();
422
423 const trailWalks = await db.execute<{
424 uri: string;
425 cid: string;
426 author_did: string;
427 rkey: string;
428 trail_uri: string;
429 record: typeof walks.$inferSelect.record;
430 created_at: Date;
431 indexed_at: Date;
432 }>(sql`
433 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
434 FROM walks w
435 LEFT JOIN accounts a ON a.did = w.author_did
436 WHERE w.trail_uri = ${trailUri}
437 AND COALESCE(a.active, 1) = 1
438 ORDER BY w.author_did, COALESCE((w.record->>'updatedAt')::timestamptz, w.created_at) DESC
439 `);
440
441 const walksData = trailWalks.rows.map((row) => ({
442 uri: row.uri,
443 cid: row.cid,
444 authorDid: row.author_did,
445 rkey: row.rkey,
446 trailUri: row.trail_uri,
447 record: row.record,
448 createdAt: new Date(row.created_at),
449 indexedAt: new Date(row.indexed_at),
450 }));
451
452 const walkDids = walksData.map((w) => w.authorDid);
453 const walkUserMap = await batchResolveUsers(walkDids);
454
455 return { walksData, walkUserMap };
456}
457
458async function loadTrailCompletions(
459 trailUri: string,
460): Promise<{ completionsData: CompletionData[]; completionUserMap: Map<string, User> }> {
461 const db = getDb();
462
463 const trailCompletions = await db.execute<{
464 uri: string;
465 cid: string;
466 author_did: string;
467 rkey: string;
468 trail_uri: string;
469 record: typeof completions.$inferSelect.record;
470 created_at: Date;
471 indexed_at: Date;
472 }>(sql`
473 SELECT c.uri, c.cid, c.author_did, c.rkey, c.trail_uri, c.record, c.created_at, c.indexed_at
474 FROM completions c
475 LEFT JOIN accounts a ON a.did = c.author_did
476 WHERE c.trail_uri = ${trailUri}
477 AND COALESCE(a.active, 1) = 1
478 ORDER BY c.created_at DESC
479 LIMIT 100
480 `);
481
482 const completionsData = trailCompletions.rows.map((row) => ({
483 uri: row.uri,
484 cid: row.cid,
485 authorDid: row.author_did,
486 rkey: row.rkey,
487 trailUri: row.trail_uri,
488 record: row.record,
489 createdAt: new Date(row.created_at),
490 indexedAt: new Date(row.indexed_at),
491 }));
492
493 const completionDids = completionsData.map((c) => c.authorDid);
494 const completionUserMap = await batchResolveUsers(completionDids);
495
496 return { completionsData, completionUserMap };
497}
498
499function buildCompletionsList(
500 completionsData: CompletionData[],
501 userMap: Map<string, User>,
502 currentUserDid: string | null,
503) {
504 return completionsData.map((c) => ({
505 user: userMap.get(c.authorDid)!,
506 timestamp: c.createdAt,
507 completionUri: c.uri,
508 isYou: c.authorDid === currentUserDid,
509 }));
510}
511
512function buildWalkersList(
513 walksData: WalkData[],
514 completionsData: CompletionData[],
515 walkUserMap: Map<string, User>,
516 completionUserMap: Map<string, User>,
517 currentUserDid: string | null,
518) {
519 const activityMap = new Map<
520 string,
521 { user: User; isYou: boolean; key: string; timestamp: Date }
522 >();
523
524 for (const c of completionsData) {
525 activityMap.set(c.authorDid, {
526 user: completionUserMap.get(c.authorDid)!,
527 isYou: c.authorDid === currentUserDid,
528 key: c.uri,
529 timestamp: c.createdAt,
530 });
531 }
532
533 for (const walk of walksData) {
534 const timestamp = walk.record.updatedAt ? new Date(walk.record.updatedAt) : walk.createdAt;
535 const existing = activityMap.get(walk.authorDid);
536 if (!existing || timestamp > existing.timestamp) {
537 activityMap.set(walk.authorDid, {
538 user: walkUserMap.get(walk.authorDid)!,
539 isYou: walk.authorDid === currentUserDid,
540 key: walk.uri,
541 timestamp,
542 });
543 }
544 }
545
546 return Array.from(activityMap.values())
547 .sort((a, b) => b.timestamp.getTime() - a.timestamp.getTime())
548 .map(({ timestamp: _, ...rest }) => rest);
549}
550
551function buildWalkersAtStopMap(walksData: WalkData[], userMap: Map<string, User>) {
552 const stopMap = new Map<string, User[]>();
553 for (const walk of walksData) {
554 const visited = walk.record.visitedStops;
555 if (visited.length > 0) {
556 const currentStop = visited[visited.length - 1];
557 const user = userMap.get(walk.authorDid);
558 if (user) {
559 const list = stopMap.get(currentStop) ?? [];
560 list.push(user);
561 stopMap.set(currentStop, list);
562 }
563 }
564 }
565 return stopMap;
566}
567
568export const loadTrailDetail = cache(async function loadTrailDetail(
569 handle: string,
570 rkey: string,
571): Promise<TrailDetailData> {
572 const currentUserDidPromise = loadCurrentDid();
573 const creatorDidPromise = resolveHandleToDid(handle);
574
575 const creatorDid = await creatorDidPromise;
576
577 await requireActiveAccount(creatorDid);
578
579 const trailUri = `at://${creatorDid}/app.sidetrail.trail/${rkey}`;
580
581 const walksDataPromise = loadTrailWalks(trailUri);
582 const completionsDataPromise = loadTrailCompletions(trailUri);
583
584 const [trail, currentUserDid, creatorAvatar] = await Promise.all([
585 loadTrailRecord(trailUri),
586 currentUserDidPromise,
587 tryFetchBskyAvatar(creatorDid),
588 ]);
589
590 const yourWalkPromise = loadUserWalk(trailUri, currentUserDid);
591
592 const creator: User = { did: creatorDid, handle, avatar: creatorAvatar };
593
594 const walkersAtStopPromise = walksDataPromise.then(({ walksData, walkUserMap }) =>
595 buildWalkersAtStopMap(walksData, walkUserMap),
596 );
597
598 const completionsPromise = completionsDataPromise.then(({ completionsData, completionUserMap }) =>
599 buildCompletionsList(completionsData, completionUserMap, currentUserDid),
600 );
601
602 const walkersPromise = Promise.all([walksDataPromise, completionsDataPromise]).then(
603 ([{ walksData, walkUserMap }, { completionsData, completionUserMap }]) =>
604 buildWalkersList(walksData, completionsData, walkUserMap, completionUserMap, currentUserDid),
605 );
606
607 const stops: TrailStop[] = trail.record.stops.map((stop) => ({
608 tid: stop.tid,
609 title: stop.title,
610 content: stop.content,
611 buttonText: stop.buttonText,
612 external: parseStopExternal(stop.external, creatorDid),
613 walkersHere: walkersAtStopPromise.then((m) => m.get(stop.tid) ?? []),
614 }));
615
616 return {
617 header: {
618 uri: trail.uri,
619 cid: trail.cid,
620 rkey,
621 title: trail.record.title,
622 description: trail.record.description,
623 stopsCount: trail.record.stops.length,
624 accentColor: trail.record.accentColor,
625 backgroundColor: trail.record.backgroundColor,
626 creator,
627 createdAt: trail.createdAt,
628 },
629 stops,
630 yourWalk: await yourWalkPromise,
631 walkers: walkersPromise,
632 completions: completionsPromise,
633 };
634});
635
636export type TrailHeaderForOG = {
637 title: string;
638 description: string;
639 stopsCount: number;
640 accentColor: string;
641 backgroundColor: string;
642 creatorHandle: string;
643};
644
645export const loadTrailDetailForOG = cache(async function loadTrailDetailForOG(
646 handle: string,
647 rkey: string,
648): Promise<TrailHeaderForOG> {
649 "use cache";
650 cacheLife("max");
651
652 const creatorDid = await resolveHandleToDid(handle);
653 const trailUri = `at://${creatorDid}/app.sidetrail.trail/${rkey}`;
654 const trail = await loadTrailRecord(trailUri);
655
656 return {
657 title: trail.record.title,
658 description: trail.record.description,
659 stopsCount: trail.record.stops.length,
660 accentColor: trail.record.accentColor,
661 backgroundColor: trail.record.backgroundColor,
662 creatorHandle: handle,
663 };
664});
665
666// ============================================================================
667// User's Walks
668// ============================================================================
669
670export const loadWalks = cache(async function loadWalks(userDid?: string): Promise<WalkCardData[]> {
671 const did = userDid ?? (await loadCurrentDid());
672 if (!did) return [];
673
674 const db = getDb();
675
676 const walksWithTrails = await db.execute<{
677 walk_uri: string;
678 walk_rkey: string;
679 walk_record: typeof walks.$inferSelect.record;
680 walk_created_at: Date;
681 trail_uri: string;
682 trail_rkey: string;
683 trail_author_did: string;
684 trail_record: TrailRecord;
685 }>(sql`
686 SELECT DISTINCT ON (w.trail_uri)
687 w.uri as walk_uri, w.rkey as walk_rkey, w.record as walk_record, w.created_at as walk_created_at,
688 t.uri as trail_uri, t.rkey as trail_rkey, t.author_did as trail_author_did, t.record as trail_record
689 FROM walks w
690 INNER JOIN trails t ON w.trail_uri = t.uri
691 WHERE w.author_did = ${did}
692 ORDER BY w.trail_uri, COALESCE((w.record->>'updatedAt')::timestamptz, w.created_at) DESC
693 `);
694
695 if (walksWithTrails.rows.length === 0) return [];
696
697 const creatorDids = walksWithTrails.rows.map((r) => r.trail_author_did);
698 const handleMap = await batchResolveHandles(creatorDids);
699
700 const sortedWalks = walksWithTrails.rows.sort((a, b) => {
701 const aUpdated = a.walk_record.updatedAt
702 ? new Date(a.walk_record.updatedAt)
703 : new Date(a.walk_created_at);
704 const bUpdated = b.walk_record.updatedAt
705 ? new Date(b.walk_record.updatedAt)
706 : new Date(b.walk_created_at);
707 return bUpdated.getTime() - aUpdated.getTime();
708 });
709
710 return sortedWalks.map((row) => ({
711 walkRkey: row.walk_rkey,
712 walkerDid: did,
713 trailRkey: row.trail_rkey,
714 trailCreatorHandle: handleMap.get(row.trail_author_did) ?? row.trail_author_did,
715 title: row.trail_record.title,
716 description: row.trail_record.description,
717 accentColor: row.trail_record.accentColor,
718 backgroundColor: row.trail_record.backgroundColor,
719 stops: row.trail_record.stops.map((stop) => ({
720 tid: stop.tid,
721 title: stop.title,
722 content: stop.content,
723 buttonText: stop.buttonText,
724 external: parseStopExternal(stop.external, row.trail_author_did),
725 })),
726 visitedStops: row.walk_record.visitedStops,
727 updatedAt: row.walk_record.updatedAt
728 ? new Date(row.walk_record.updatedAt)
729 : new Date(row.walk_created_at),
730 }));
731});
732
733export const loadWalkingBadges = cache(async function loadWalkingBadges(): Promise<
734 Array<{ accentColor: string; key: string }>
735> {
736 const currentDid = await loadCurrentDid();
737 if (!currentDid) return [];
738
739 const db = getDb();
740
741 const walksWithTrails = await db.execute<{
742 trail_uri: string;
743 accent_color: string;
744 }>(sql`
745 SELECT trail_uri, accent_color FROM (
746 SELECT DISTINCT ON (w.trail_uri)
747 w.trail_uri,
748 t.record->>'accentColor' as accent_color,
749 COALESCE((w.record->>'updatedAt')::timestamptz, w.created_at) as last_activity
750 FROM walks w
751 INNER JOIN trails t ON w.trail_uri = t.uri
752 WHERE w.author_did = ${currentDid}
753 ORDER BY w.trail_uri, COALESCE((w.record->>'updatedAt')::timestamptz, w.created_at) DESC
754 ) unique_walks
755 ORDER BY last_activity DESC
756 LIMIT 3
757 `);
758
759 return walksWithTrails.rows.map((row) => ({
760 key: row.trail_uri,
761 accentColor: row.accent_color,
762 }));
763});
764
765export const loadDraftsBadges = cache(async function loadDraftsBadges(): Promise<
766 Array<{ accentColor: string; key: string }>
767> {
768 return [];
769});
770
771// ============================================================================
772// User Published Trails
773// ============================================================================
774
775export const loadUserPublishedTrails = cache(async function loadUserPublishedTrails(
776 handle: string,
777): Promise<TrailCardData[]> {
778 const userDid = await resolveHandleToDid(handle);
779
780 await requireActiveAccount(userDid);
781
782 const db = getDb();
783
784 const userTrails = await db
785 .select()
786 .from(trails)
787 .where(eq(trails.authorDid, userDid))
788 .orderBy(desc(trails.createdAt))
789 .limit(100);
790
791 const user: User = { did: userDid, handle };
792
793 return userTrails.map((trail) => ({
794 uri: trail.uri,
795 rkey: trail.rkey,
796 creatorHandle: handle,
797 title: trail.record.title,
798 description: trail.record.description,
799 accentColor: trail.record.accentColor,
800 backgroundColor: trail.record.backgroundColor,
801 creator: user,
802 stopsCount: trail.record.stops.length,
803 createdAt: trail.createdAt,
804 }));
805});
806
807// ============================================================================
808// User Completed Trails
809// ============================================================================
810
811export const loadUserCompletedTrails = cache(async function loadUserCompletedTrails(
812 handle: string,
813): Promise<CompletedTrailData[]> {
814 const userDid = await resolveHandleToDid(handle);
815
816 await requireActiveAccount(userDid);
817
818 const db = getDb();
819
820 const userCompletions = await db
821 .select()
822 .from(completions)
823 .where(eq(completions.authorDid, userDid))
824 .orderBy(desc(completions.createdAt))
825 .limit(100);
826
827 const completedTrailUris = [...new Set(userCompletions.map((c) => c.trailUri))];
828 const completedTrailsData =
829 completedTrailUris.length > 0
830 ? await db.select().from(trails).where(inArray(trails.uri, completedTrailUris))
831 : [];
832
833 const trailDataMap = new Map(completedTrailsData.map((t) => [t.uri, t]));
834 const creatorDids = completedTrailsData.map((t) => t.authorDid);
835 const creatorUserMap = await batchResolveUsers(creatorDids);
836
837 const seenUris = new Set<string>();
838 const completedTrails: CompletedTrailData[] = [];
839 for (const c of userCompletions) {
840 if (seenUris.has(c.trailUri)) continue;
841 seenUris.add(c.trailUri);
842
843 const trail = trailDataMap.get(c.trailUri);
844 if (!trail) continue;
845
846 completedTrails.push({
847 uri: trail.uri,
848 rkey: trail.rkey,
849 title: trail.record.title,
850 description: trail.record.description,
851 accentColor: trail.record.accentColor,
852 backgroundColor: trail.record.backgroundColor,
853 stopsCount: trail.record.stops.length,
854 creator: creatorUserMap.get(trail.authorDid) ?? {
855 did: trail.authorDid,
856 handle: trail.authorDid,
857 },
858 completedAt: c.createdAt,
859 });
860 }
861
862 return completedTrails;
863});