Skip to content

Open-source platform for 3D AI agents. Turn text or a photo into a rigged, animated GLB avatar, give it an LLM brain, memory and a wallet, and embed it anywhere with one web component. Remote MCP server, x402 payments, Three.js, browser-native.

9.0 KB
Raw Blame
// GET /api/leaderboard/unified — the cross-surface leaderboard (prompt 06 of
// the user-value pack).
//
//   ?metric = creations | remixes_received | launches | followers | walk_distance | ufo_score
//             (default creations)
//   ?limit  = 1..100   (default 50)
//   ?offset = 0..
//
// Six real, queried rankings, every column here is a COUNT/SUM over an
// existing table, never a synthetic score:
//   · creations         — finished forge models (forge_creations, status=done)
//                          + saved worlds (dioramas), per signed-in creator.
//   · remixes_received   — count of OTHER creators' forge_creations rows whose
//                          parent_creation_id points at one of this user's
//                          finished creations. Both sides must be status='done',
//                          and a creator's own refines of their own model never
//                          count (a real derivative count, not a view/like one).
//   · launches:           mainnet coins this user launched, from the
//                          platform's launch records (api/_lib/launch-counts.js).
//   · followers          — user_follows platform-wide follower count.
//   · walk_distance      — total metres walked while signed in (walk_metrics,
//                          all-time, signed-in walkers only — the anonymous
//                          half of walking already has its own board at
//                          /walk-leaderboard).
//
// Two metrics considered and REJECTED for lacking real data (see prompt
// report): "royalties earned" (forge_creations.remix_settlement_ref is a
// single JSONB column overwritten on every settlement, not an append-only
// ledger, so a cumulative sum across multiple remixes of the same source is
// not reliably computable without a schema change) and "/play activity" (no
// per-human-user session/quest-completion table exists — /play's own tables
// track wallet auth and agent-level Agora activity, not human play sessions).
//
// Every row is a real signed-in user (anonymous activity has no cross-surface
// identity to rank). The requester's own row is always resolved and pinned,
// even off-page, mirroring /api/walk/leaderboard's "you're #142" pattern.

import { cors, method, json, wrap, rateLimited, error } from '../_lib/http.js';
import { limits, clientIp } from '../_lib/rate-limit.js';
import { sql } from '../_lib/db.js';
import { getSessionUser, extractBearer, authenticateBearer } from '../_lib/auth.js';
import { thumbnailUrl } from '../_lib/r2.js';
import { getStreak, listBadges } from '../_lib/streaks.js';
import { launchCountsByUser } from '../_lib/launch-counts.js';

export const maxDuration = 10;

const METRICS = new Set(['creations', 'remixes_received', 'launches', 'followers', 'walk_distance', 'ufo_score']);

const METRIC_LABEL = {
	creations: 'Creations',
	remixes_received: 'Remixes Received',
	launches: 'Launches',
	followers: 'Followers',
	walk_distance: 'Walk Distance',
	ufo_score: 'UFO High Score',
};

// Per-metric ranking query — returns { user_id, value } rows, unsorted (the
// caller sorts once after merging in profile data isn't needed for sorting).
async function rankingRows(metric) {
	if (metric === 'creations') {
		return sql`
			with per_user as (
				select user_id, count(*) as n
				from forge_creations
				where user_id is not null and status = 'done' and glb_url is not null
				group by user_id
				union all
				select user_id, count(*) as n
				from dioramas
				where user_id is not null
				group by user_id
			)
			select user_id, sum(n)::bigint as value
			from per_user
			group by user_id
		`;
	}
	if (metric === 'remixes_received') {
		// Only finished derivatives by SOMEONE ELSE count. `parent_creation_id` is
		// also written by the forge's own refine lineage (linkRefineLineage() in
		// api/_lib/forge-store.js), so counting every child would let a creator
		// climb this board by re-refining their own model, and would score failed
		// generations as remixes. `is distinct from` keeps an anonymous remixer
		// (null user_id) counted, which is a genuinely other creator.
		return sql`
			with mine as (
				select id, user_id
				from forge_creations
				where user_id is not null and status = 'done'
			)
			select m.user_id, count(*)::bigint as value
			from mine m
			join forge_creations c on c.parent_creation_id = m.id
			where c.status = 'done'
			  and c.user_id is distinct from m.user_id
			group by m.user_id
		`;
	}
	if (metric === 'launches') return launchCountsByUser();
	if (metric === 'followers') {
		return sql`
			select following_id as user_id, count(*)::bigint as value
			from user_follows
			group by following_id
		`;
	}
	if (metric === 'ufo_score') {
		return sql`
			select user_id, max(score)::bigint as value
			from ufo_runs
			where user_id is not null
			group by user_id
		`;
	}
	// walk_distance
	return sql`
		select user_id, sum(distance_meters)::float8 as value
		from walk_metrics
		where user_id is not null
		group by user_id
		having sum(distance_meters) > 0
	`;
}

async function resolveViewerId(req) {
	try {
		const bearer = extractBearer(req);
		const user = bearer ? await authenticateBearer(bearer) : await getSessionUser(req);
		return user?.userId || user?.id || user?.sub || null;
	} catch {
		return null;
	}
}

export default wrap(async (req, res) => {
	if (cors(req, res, { origins: '*', methods: 'GET,OPTIONS' })) return;
	if (!method(req, res, ['GET'])) return;

	const rl = await limits.publicIp(clientIp(req));
	if (!rl.success) return rateLimited(res, rl);

	const url = new URL(req.url, 'http://x');
	const metric = url.searchParams.get('metric') || 'creations';
	if (!METRICS.has(metric)) {
		return error(res, 400, 'bad_metric', 'metric must be one of: ' + [...METRICS].join(', '));
	}
	const limit = Math.min(Math.max(Number(url.searchParams.get('limit')) || 50, 1), 100);
	const offset = Math.max(Number(url.searchParams.get('offset')) || 0, 0);

	const rows = await rankingRows(metric);

	const ranked = rows
		.map((r) => ({ userId: r.user_id, value: Number(r.value) || 0 }))
		.filter((r) => r.value > 0)
		.sort((a, b) => (b.value !== a.value ? b.value - a.value : a.userId < b.userId ? -1 : 1));

	const total = ranked.length;
	const rankByUser = new Map();
	ranked.forEach((r, i) => rankByUser.set(r.userId, i + 1));

	const page = ranked.slice(offset, offset + limit);

	const viewerId = await resolveViewerId(req);
	const idsNeeded = new Set(page.map((r) => r.userId));
	if (viewerId) idsNeeded.add(viewerId);

	const profiles = new Map();
	const idList = [...idsNeeded];
	if (idList.length) {
		const profRows = await sql`
			select u.id, u.username, u.display_name,
			       (select thumbnail_key from avatars
			          where owner_id = u.id and deleted_at is null and thumbnail_key is not null
			          order by created_at desc limit 1) as thumbnail_key
			from users u
			where u.id = any(${idList}) and u.deleted_at is null
		`;
		for (const p of profRows) profiles.set(p.id, p);
	}

	function toRow(r) {
		const prof = profiles.get(r.userId);
		const handle = prof?.username ? `@${prof.username}` : prof?.display_name || 'three.ws creator';
		return {
			rank: rankByUser.get(r.userId),
			userId: r.userId,
			username: prof?.username || null,
			handle,
			profileUrl: prof?.username ? `/u/${prof.username}` : null,
			avatar: thumbnailUrl(prof?.thumbnail_key),
			value: metric === 'walk_distance' ? Math.round(r.value * 100) / 100 : Math.round(r.value),
		};
	}

	const pageRows = page.map(toRow);

	let me = null;
	if (viewerId) {
		if (rankByUser.has(viewerId)) {
			const entry = ranked.find((r) => r.userId === viewerId);
			me = toRow(entry);
			me.onPage = pageRows.some((r) => r.userId === viewerId);
		} else {
			const prof = profiles.get(viewerId);
			me = {
				rank: null,
				userId: viewerId,
				username: prof?.username || null,
				handle: prof?.username ? `@${prof.username}` : prof?.display_name || 'you',
				profileUrl: prof?.username ? `/u/${prof.username}` : null,
				avatar: thumbnailUrl(prof?.thumbnail_key),
				value: 0,
				onPage: false,
				unranked: true,
			};
		}
	}

	// The `me`, `streak`, and `badges` blocks are per-viewer, so a signed-in
	// response must never reach a shared cache: `public` + `s-maxage` here would
	// let the CDN hand one creator's rank and badge list to the next visitor.
	// Anonymous responses are identical for everyone and stay edge-cacheable.
	res.setHeader(
		'cache-control',
		viewerId
			? 'private, max-age=15, stale-while-revalidate=60'
			: 'public, max-age=15, s-maxage=30, stale-while-revalidate=60',
	);

	// Streak and badges ride along with the board so the page resolves the whole
	// signed-in view in one request. Reading them from a username-keyed profile
	// endpoint instead used to leave every user who has not picked a username
	// (the default for a fresh account) with no streak card at all.
	const [streak, badges] = viewerId
		? await Promise.all([getStreak(viewerId), listBadges(viewerId)])
		: [null, []];

	return json(res, 200, {
		metric,
		metricLabel: METRIC_LABEL[metric],
		total,
		limit,
		offset,
		hasMore: offset + limit < total,
		rows: pageRows,
		me,
		streak,
		badges,
	});
});