Apex Scout — market intel

\n \n

\n \n \n \n \n \n \n \n \n \n \n

Player	Growth	Role	Age	Rank	Ceiling	Fog	Salary	Buy now	Time Left

\n\n

\n \n

\n \n \n \n \n \n \n \n \n \n \n \n \n

Player	\n Growth▾ \n \n Growth Class \n \n ★ Prodigy\n Veteran\n Amateur\n Novice\n \n \n \n	\n Age▾ \n \n Filter Age \n \n \n \n \n \n \n	\n Market Rank▾ \n \n Market Rank \n All Clear \n \n \n \n	\n Market Pot▾ \n \n Market Potential \n All Clear \n \n \n \n	\n Fog▾ \n \n Filter Fog % \n \n \n \n \n \n \n	\n Ret Rank▾ \n \n Retired Rank \n All Clear \n \n \n \n	\n Ret Pot▾ \n \n Retired Potential \n All Clear \n \n \n \n	\n Salary▾ \n \n Filter Salary \n \n \n \n \n \n \n	\n Pot Δ▾ \n \n Potential Tier Change \n \n \n \n \n \n \n	Resolved

\n \n

\n\n

Advanced Signal Analysis

\n \n

A+ / S-Tier Potential Hit Rate Matrix

Market grade pairs (45%–85% Fog, Sample ≥ 25) ranked by likelihood of revealing ≥ A+ potential.

\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n

Grade Pair (Rank / Pot)	Sample (N)	Avg Fog	Avg Salary	≥ A+ Hits	≥ A+ Rate	S-Tier (S-/S/S+)	Avg Rev. True	Avg Rev. Pot

\n\n \n

Reached ≥ A- potential, by fog band

Percentage of resolved players in each fog band that reached or exceeded the selected grade threshold.

\n \n \n

Age Cohort vs. Upside Decay

Conversion probability of hitting A- and S- tier ceilings across player age brackets.

\n\n \n

Salary vs Revealed Rating

Market salary vs revealed rating — highlighting hidden S-tier players under 6,000 cr/wk.

\n\n \n

Ceiling Grade Distribution

Displayed potential grades split by player growth archetype.

\n\n \n

Roster Development Tracker

\n\n \n\n

\n \n

History

Time

Village

Lumber

Clay

Iron

Crop

Gold

{\n const res: any = await sqlite.execute(args.length ? { sql, args } : sql);\n const cols: string[] = res.columns || [];\n return (res.rows as any[]).map((row) =>\n Array.isArray(row)\n ? Object.fromEntries(cols.map((c, i) => [c, row[i]]))\n : row\n );\n}\n\nconst json = (o: any, status = 200) =>\n new Response(JSON.stringify(o), {\n status,\n headers: { \"content-type\": \"application/json\" },\n });\n\nasync function apiStats(): Promise {\n await ensureSchema();\n const now = Date.now();\n const r = (await q(`SELECT\n (SELECT COUNT(*) FROM listings WHERE ${LISTINGS_UNOWNED}) AS captured,\n (SELECT COUNT(*) FROM listings WHERE left_at IS NULL AND (end_at IS NULL OR end_at > ${now}) AND ${LISTINGS_UNOWNED}) AS live,\n (SELECT COUNT(*) FROM retired r WHERE ${RETIRED_UNOWNED}) AS resolved,\n (SELECT COUNT(*) FROM retired r WHERE r.retired_potential IN (${ELITE}) AND ${RETIRED_UNOWNED}) AS hits,\n (SELECT COUNT(*) FROM retired r WHERE r.retired_potential IN (${SUPER_ELITE}) AND ${RETIRED_UNOWNED}) AS s_hits`))[\n 0\n ];\n const hitRate = r.resolved ? Math.round((100 * r.hits) / r.resolved) : 0;\n const sHitRate = r.resolved ? Math.round((100 * r.s_hits) / r.resolved) : 0;\n return json({\n captured: r.captured,\n live: r.live,\n resolved: r.resolved,\n hitRate,\n sHitRate,\n });\n}\n\nasync function apiLive(): Promise {\n await ensureSchema();\n const now = Date.now();\n const rows = await q(\n `SELECT player_id, name, role, age, est_rank, est_potential, intel, fog_pct,\n salary, starting_bid, current_bid, growth_class, first_seen_at, end_at\n FROM listings \n WHERE left_at IS NULL \n AND (end_at IS NULL OR end_at > ?)\n AND ${LISTINGS_UNOWNED}\n ORDER BY end_at ASC, first_seen_at DESC`,\n [now]\n );\n return json(rows);\n}\n\nasync function apiRetired(url: URL): Promise {\n await ensureSchema();\n const limitParam = parseInt(url.searchParams.get(\"limit\") || \"50\") || 50;\n const pageSize = Math.min(100, Math.max(1, limitParam));\n const page = Math.max(1, parseInt(url.searchParams.get(\"page\") || \"1\") || 1);\n const term = (url.searchParams.get(\"q\") || \"\").trim();\n const sortKey = url.searchParams.get(\"sort\") || \"r_pot\";\n const sortDir = url.searchParams.get(\"dir\") === \"1\" ? \"ASC\" : \"DESC\";\n\n const mRank = (url.searchParams.get(\"m_rank\") || \"\").trim();\n const mPot = (url.searchParams.get(\"m_pot\") || \"\").trim();\n const rRank = (url.searchParams.get(\"r_rank\") || \"\").trim();\n const rPot = (url.searchParams.get(\"r_pot\") || \"\").trim();\n const growthClass = (url.searchParams.get(\"growth_class\") || \"\").trim();\n const minAge = url.searchParams.get(\"min_age\");\n const maxAge = url.searchParams.get(\"max_age\");\n const minFog = url.searchParams.get(\"min_fog\");\n const maxFog = url.searchParams.get(\"max_fog\");\n const minSal = url.searchParams.get(\"min_sal\");\n const maxSal = url.searchParams.get(\"max_sal\");\n const minDelta = url.searchParams.get(\"min_delta\");\n const maxDelta = url.searchParams.get(\"max_delta\");\n\n const where = [\"r.retired_potential IS NOT NULL\", RETIRED_UNOWNED];\n const args: any[] = [];\n\n if (term) {\n where.push(\"r.name LIKE ?\");\n args.push(\"%\" + term + \"%\");\n }\n\n const addGradeFilter = (paramVal: string, colExpr: string) => {\n if (!paramVal || paramVal === \"all\") return;\n const grades = paramVal.split(\",\").map((g) => g.trim()).filter((g) =>\n GRADES.includes(g)\n );\n if (grades.length > 0) {\n const inList = grades.map((g) => `'${g}'`).join(\",\");\n where.push(`${colExpr} IN (${inList})`);\n }\n };\n\n addGradeFilter(mRank, \"COALESCE(r.market_est_rank, l.est_rank)\");\n addGradeFilter(mPot, \"COALESCE(r.market_est_potential, l.est_potential)\");\n addGradeFilter(rRank, \"r.retired_rank\");\n addGradeFilter(rPot, \"r.retired_potential\");\n\n if (growthClass && growthClass !== \"all\") {\n where.push(\"LOWER(l.growth_class) = LOWER(?)\");\n args.push(growthClass);\n }\n\n if (minAge !== null && minAge !== \"\" && !isNaN(Number(minAge))) {\n where.push(\"COALESCE(r.market_age, l.age) >= ?\");\n args.push(Number(minAge));\n }\n if (maxAge !== null && maxAge !== \"\" && !isNaN(Number(maxAge))) {\n where.push(\"COALESCE(r.market_age, l.age) <= ?\");\n args.push(Number(maxAge));\n }\n if (minFog !== null && minFog !== \"\" && !isNaN(Number(minFog))) {\n where.push(\"COALESCE(r.market_fog_pct, l.fog_pct) >= ?\");\n args.push(Number(minFog));\n }\n if (maxFog !== null && maxFog !== \"\" && !isNaN(Number(maxFog))) {\n where.push(\"COALESCE(r.market_fog_pct, l.fog_pct) <= ?\");\n args.push(Number(maxFog));\n }\n if (minSal !== null && minSal !== \"\" && !isNaN(Number(minSal))) {\n where.push(\"COALESCE(r.market_salary, l.salary) >= ?\");\n args.push(Number(minSal));\n }\n if (maxSal !== null && maxSal !== \"\" && !isNaN(Number(maxSal))) {\n where.push(\"COALESCE(r.market_salary, l.salary) <= ?\");\n args.push(Number(maxSal));\n }\n if (minDelta !== null && minDelta !== \"\" && !isNaN(Number(minDelta))) {\n where.push(\n `(${gradeCase(\"r.retired_potential\")} - ${\n gradeCase(\"COALESCE(r.market_est_potential, l.est_potential)\")\n }) >= ?`,\n );\n args.push(Number(minDelta));\n }\n if (maxDelta !== null && maxDelta !== \"\" && !isNaN(Number(maxDelta))) {\n where.push(\n `(${gradeCase(\"r.retired_potential\")} - ${\n gradeCase(\"COALESCE(r.market_est_potential, l.est_potential)\")\n }) <= ?`,\n );\n args.push(Number(maxDelta));\n }\n\n let orderSql = `${gradeCase(\"r.retired_potential\")} DESC, r.retired_at DESC`;\n if (sortKey === \"name\") orderSql = `r.name ${sortDir}`;\n else if (sortKey === \"growth\") {\n orderSql = `l.growth_class ${sortDir}`;\n } else if (sortKey === \"age\") {\n orderSql = `COALESCE(r.market_age, l.age) ${sortDir}`;\n } else if (sortKey === \"fog\") {\n orderSql = `COALESCE(r.market_fog_pct, l.fog_pct) ${sortDir}`;\n } else if (sortKey === \"salary\") {\n orderSql = `COALESCE(r.market_salary, l.salary) ${sortDir}`;\n } else if (sortKey === \"m_rank\") {\n orderSql = `${gradeCase(\"COALESCE(r.market_est_rank, l.est_rank)\")} ${sortDir}`;\n } else if (sortKey === \"m_pot\") {\n orderSql = `${gradeCase(\"COALESCE(r.market_est_potential, l.est_potential)\")} ${sortDir}`;\n } else if (sortKey === \"r_rank\") {\n orderSql = `${gradeCase(\"r.retired_rank\")} ${sortDir}`;\n } else if (sortKey === \"r_pot\") {\n orderSql = `${gradeCase(\"r.retired_potential\")} ${sortDir}, r.retired_at DESC`;\n } else if (sortKey === \"delta\") {\n orderSql = `(${gradeCase(\"r.retired_potential\")} - ${\n gradeCase(\"COALESCE(r.market_est_potential, l.est_potential)\")\n }) ${sortDir}`;\n } else if (sortKey === \"retired_at\") {\n orderSql = `r.retired_at ${sortDir}`;\n }\n\n const fromClause = `\n FROM retired r\n LEFT JOIN listings l ON l.player_id = r.player_id \n AND l.first_seen_at = (SELECT MIN(first_seen_at) FROM listings WHERE player_id = r.player_id)\n `;\n\n const W = \"WHERE \" + where.join(\" AND \");\n const countSql = `SELECT COUNT(*) c ${fromClause} ${W}`;\n const total = (await q(countSql, args))[0].c;\n const pages = Math.max(1, Math.ceil(total / pageSize));\n const p = Math.min(page, pages);\n\n const querySql = `\n SELECT \n r.name, \n l.growth_class,\n COALESCE(r.market_age, l.age) AS age,\n COALESCE(r.market_fog_pct, l.fog_pct) AS fog_pct,\n COALESCE(r.market_salary, l.salary) AS salary,\n COALESCE(r.market_est_rank, l.est_rank) AS market_est_rank,\n COALESCE(r.market_est_potential, l.est_potential) AS market_est_potential,\n r.retired_rank, \n r.retired_potential, \n r.true_rating, \n r.potential_rating, \n r.retired_at, \n r.card_url\n ${fromClause}\n ${W}\n ORDER BY ${orderSql}\n LIMIT ${pageSize} OFFSET ${(p - 1) * pageSize}\n `;\n\n const rows = await q(querySql, args);\n return json({ rows, total, page: p, pages, limit: pageSize });\n}\n\nasync function apiAnalytics(): Promise {\n await ensureSchema();\n const potCase = gradeCase(\"r.retired_potential\");\n const bandCase =\n `CASE WHEN COALESCE(r.market_fog_pct, l.fog_pct)<20 THEN '0-20' \n WHEN COALESCE(r.market_fog_pct, l.fog_pct)<40 THEN '20-40' \n WHEN COALESCE(r.market_fog_pct, l.fog_pct)<60 THEN '40-60' \n WHEN COALESCE(r.market_fog_pct, l.fog_pct)<80 THEN '60-80' \n ELSE '80+' END`;\n\n // 0. A+ / S-Tier Hit Rate Matrix\n const aPlusMatrix = await q(`\n SELECT \n COALESCE(r.market_est_rank, l.est_rank) || ' / ' || COALESCE(r.market_est_potential, l.est_potential) AS grade_pair,\n COUNT(*) AS total_players,\n ROUND(AVG(COALESCE(r.market_fog_pct, l.fog_pct)), 1) AS avg_fog,\n ROUND(AVG(COALESCE(r.market_salary, l.salary)), 0) AS avg_salary,\n SUM(CASE WHEN r.retired_potential IN ('A+', 'S-', 'S', 'S+') THEN 1 ELSE 0 END) AS a_plus_or_better,\n ROUND(100.0 * SUM(CASE WHEN r.retired_potential IN ('A+', 'S-', 'S', 'S+') THEN 1 ELSE 0 END) / COUNT(*), 1) AS a_plus_rate_pct,\n SUM(CASE WHEN r.retired_potential IN ('S-', 'S', 'S+') THEN 1 ELSE 0 END) AS s_tier_count,\n ROUND(AVG(r.true_rating), 1) AS avg_revealed_true,\n ROUND(AVG(r.potential_rating), 1) AS avg_revealed_pot\n FROM retired r\n LEFT JOIN listings l ON l.player_id = r.player_id \n AND l.first_seen_at = (SELECT MIN(first_seen_at) FROM listings WHERE player_id = r.player_id)\n WHERE r.retired_potential IS NOT NULL \n AND COALESCE(r.market_est_potential, l.est_potential) IS NOT NULL\n AND COALESCE(r.market_fog_pct, l.fog_pct) BETWEEN 45 AND 85\n AND ${RETIRED_UNOWNED}\n GROUP BY COALESCE(r.market_est_rank, l.est_rank), COALESCE(r.market_est_potential, l.est_potential)\n HAVING COUNT(*) >= 25\n ORDER BY a_plus_rate_pct DESC\n `);\n\n // 1. Fog Bands\n const fb = await q(`\n SELECT ${bandCase} AS band, COUNT(*) n,\n SUM(CASE WHEN ${potCase}>=9 THEN 1 ELSE 0 END) t9,\n SUM(CASE WHEN ${potCase}>=10 THEN 1 ELSE 0 END) t10,\n SUM(CASE WHEN ${potCase}>=11 THEN 1 ELSE 0 END) t11,\n SUM(CASE WHEN ${potCase}>=12 THEN 1 ELSE 0 END) t12,\n SUM(CASE WHEN ${potCase}>=13 THEN 1 ELSE 0 END) t13\n FROM retired r\n LEFT JOIN listings l ON l.player_id = r.player_id \n AND l.first_seen_at = (SELECT MIN(first_seen_at) FROM listings WHERE player_id = r.player_id)\n WHERE r.retired_potential IS NOT NULL \n AND COALESCE(r.market_fog_pct, l.fog_pct) IS NOT NULL \n AND ${RETIRED_UNOWNED}\n GROUP BY band\n `);\n\n const order = [\"0-20\", \"20-40\", \"40-60\", \"60-80\", \"80+\"];\n const fogbands = order.map((b) =>\n fb.find((x) => x.band === b) ||\n { band: b, n: 0, t9: 0, t10: 0, t11: 0, t12: 0, t13: 0 }\n );\n\n // 2. Salary vs Revealed Potential Rating\n const scatter = await q(`\n SELECT COALESCE(r.market_salary, l.salary) s, r.potential_rating p, r.true_rating r, r.retired_potential g\n FROM retired r\n LEFT JOIN listings l ON l.player_id = r.player_id \n AND l.first_seen_at = (SELECT MIN(first_seen_at) FROM listings WHERE player_id = r.player_id)\n WHERE r.potential_rating IS NOT NULL \n AND COALESCE(r.market_salary, l.salary) IS NOT NULL \n AND ${RETIRED_UNOWNED}\n `);\n\n // 3. Age Decay Curve (% S- or above across age brackets)\n const ageBrackets = await q(`\n SELECT \n CASE \n WHEN COALESCE(r.market_age, l.age) < 20 THEN '<20'\n WHEN COALESCE(r.market_age, l.age) < 24 THEN '20-23'\n WHEN COALESCE(r.market_age, l.age) < 28 THEN '24-27'\n WHEN COALESCE(r.market_age, l.age) < 32 THEN '28-31'\n ELSE '32+' \n END AS bracket,\n COUNT(*) AS total,\n SUM(CASE WHEN ${potCase} >= 12 THEN 1 ELSE 0 END) AS s_count,\n SUM(CASE WHEN ${potCase} >= 9 THEN 1 ELSE 0 END) AS a_count\n FROM retired r\n LEFT JOIN listings l ON l.player_id = r.player_id \n AND l.first_seen_at = (SELECT MIN(first_seen_at) FROM listings WHERE player_id = r.player_id)\n WHERE r.retired_potential IS NOT NULL \n AND COALESCE(r.market_age, l.age) IS NOT NULL \n AND ${RETIRED_UNOWNED}\n GROUP BY bracket\n `);\n\n // 4. Ceiling Breakdown by Growth Class\n const cRows = await q(`\n SELECT est_potential pot, growth_class g, COUNT(*) n \n FROM listings\n WHERE est_potential IS NOT NULL \n AND growth_class IS NOT NULL \n AND ${LISTINGS_UNOWNED}\n GROUP BY est_potential, growth_class\n `);\n\n const labels = Array.from(new Set(cRows.map((r) => r.pot))).sort((a, b) =>\n GRADES.indexOf(a) - GRADES.indexOf(b)\n );\n const matrix: any = { novice: {}, amateur: {}, veteran: {}, prodigy: {} };\n for (const r of cRows) {\n (matrix[r.g] = matrix[r.g] || {})[r.pot] = r.n;\n }\n\n return json({ aPlusMatrix, fogbands, scatter, ageBrackets, ceiling: { labels, matrix } });\n}\n\n/* ---- Roster Development API ---- */\n\nconst STAT_COLS = [\n \"aim\", \"mechanics\", \"clutch\", \"consistency\",\n \"awareness\", \"communication\", \"temperament\", \"cognition\"\n];\n\nasync function apiRosterPlayers(): Promise {\n // Latest snapshot per player + lead counts — exclude departed players\n const rows = await q(`\n SELECT s.*, \n (SELECT COUNT(*) FROM development_leads d WHERE d.player_id = s.player_id) AS lead_count,\n (SELECT COUNT(*) FROM development_leads d WHERE d.player_id = s.player_id AND d.completed = 1) AS leads_done\n FROM roster_snapshots s\n INNER JOIN (\n SELECT player_id, MAX(snapshot_at) AS max_at\n FROM roster_snapshots GROUP BY player_id\n ) latest ON s.player_id = latest.player_id AND s.snapshot_at = latest.max_at\n WHERE s.player_id NOT IN (\n SELECT rc.player_id FROM roster_changes rc\n WHERE rc.change_type = 'departed'\n AND rc.changed_at = (SELECT MAX(rc2.changed_at) FROM roster_changes rc2 WHERE rc2.player_id = rc.player_id AND rc2.change_type = 'departed')\n AND NOT EXISTS (\n SELECT 1 FROM roster_snapshots s2\n WHERE s2.player_id = rc.player_id AND s2.snapshot_at > rc.changed_at\n )\n )\n ORDER BY s.roster_group ASC, s.name ASC\n `);\n\n // For each player, get their previous snapshot to compute trend (stats + fog)\n const withTrend = [];\n for (const row of rows) {\n const prev = await q(`\n SELECT ${STAT_COLS.join(\",\")}, fog_pct FROM roster_snapshots\n WHERE player_id = ? AND snapshot_at < ?\n ORDER BY snapshot_at DESC LIMIT 1\n `, [row.player_id, row.snapshot_at]);\n\n const trend: Record = {};\n if (prev.length) {\n for (const col of [...STAT_COLS, \"fog_pct\"]) {\n if (row[col] != null && prev[0][col] != null) {\n const d = row[col] - prev[0][col];\n if (d !== 0) trend[col] = d;\n }\n }\n }\n withTrend.push({ ...row, trend });\n }\n return json(withTrend);\n}\n\nasync function apiRosterSnapshots(url: URL): Promise {\n const playerId = url.searchParams.get(\"player_id\");\n if (!playerId) return json({ error: \"player_id required\" }, 400);\n const limit = Math.min(500, Math.max(1, parseInt(url.searchParams.get(\"limit\") || \"200\") || 200));\n\n const rows = await q(`\n SELECT * FROM roster_snapshots\n WHERE player_id = ?\n ORDER BY snapshot_at ASC\n LIMIT ?\n `, [playerId, limit]);\n return json(rows);\n}\n\nasync function apiRosterEvents(url: URL): Promise {\n const playerId = url.searchParams.get(\"player_id\");\n const limit = Math.min(500, Math.max(1, parseInt(url.searchParams.get(\"limit\") || \"200\") || 200));\n\n const where = [\"1=1\"];\n const args: any[] = [];\n if (playerId) { where.push(\"player_id = ?\"); args.push(playerId); }\n args.push(limit);\n\n const rows = await q(`\n SELECT * FROM roster_events\n WHERE ${where.join(\" AND \")}\n ORDER BY detected_at DESC\n LIMIT ?\n `, args);\n return json(rows);\n}\n\nasync function apiRosterChanges(): Promise {\n const rows = await q(`\n SELECT * FROM roster_changes\n ORDER BY changed_at DESC\n LIMIT 200\n `);\n return json(rows);\n}\n\nasync function apiRosterLeads(url: URL): Promise {\n const playerId = url.searchParams.get(\"player_id\");\n const where = [\"1=1\"];\n const args: any[] = [];\n if (playerId) { where.push(\"player_id = ?\"); args.push(playerId); }\n\n const rows = await q(`\n SELECT * FROM development_leads\n WHERE ${where.join(\" AND \")}\n ORDER BY completed ASC, first_seen_at DESC\n `, args);\n return json(rows);\n}\n\nasync function apiRosterLeadToggle(req: Request): Promise {\n const body = await req.json();\n const clueId = body.clue_id;\n if (!clueId) return json({ error: \"clue_id required\" }, 400);\n\n // Toggle: flip the completed flag\n const now = Date.now();\n await sqlite.execute({\n sql: `UPDATE development_leads\n SET completed = CASE WHEN completed = 0 THEN 1 ELSE 0 END,\n completed_at = CASE WHEN completed = 0 THEN ? ELSE NULL END\n WHERE clue_id = ?`,\n args: [now, clueId],\n });\n\n const rows = await q(`SELECT * FROM development_leads WHERE clue_id = ?`, [clueId]);\n return json(rows[0] || { error: \"not found\" });\n}\n\nasync function apiRosterHistory(url: URL): Promise {\n // Now serves activity sessions instead of development_history\n const playerId = url.searchParams.get(\"player_id\");\n const where = [\"1=1\"];\n const args: any[] = [];\n if (playerId) { where.push(\"player_id = ?\"); args.push(playerId); }\n\n const rows = await q(`\n SELECT * FROM activity_sessions\n WHERE ${where.join(\" AND \")}\n ORDER BY started_at DESC\n LIMIT 200\n `, args);\n return json(rows);\n}\n\nasync function apiRosterTurningPoints(url: URL): Promise {\n const playerId = url.searchParams.get(\"player_id\");\n const where = [\"1=1\"];\n const args: any[] = [];\n if (playerId) { where.push(\"player_id = ?\"); args.push(playerId); }\n\n const rows = await q(`\n SELECT * FROM turning_points\n WHERE ${where.join(\" AND \")}\n ORDER BY detected_at DESC\n LIMIT 100\n `, args);\n return json(rows);\n}\n\nasync function apiRosterDaily(url: URL): Promise {\n const playerId = url.searchParams.get(\"player_id\");\n if (!playerId) return json({ error: \"player_id required\" }, 400);\n const limit = Math.min(60, Math.max(1, parseInt(url.searchParams.get(\"limit\") || \"30\") || 30));\n\n // Get one snapshot per day (latest snapshot each day) for this player\n const rows = await q(`\n SELECT s.* FROM roster_snapshots s\n INNER JOIN (\n SELECT MAX(id) AS max_id\n FROM roster_snapshots\n WHERE player_id = ?\n GROUP BY date(snapshot_at/1000, 'unixepoch')\n ) daily ON s.id = daily.max_id\n ORDER BY s.snapshot_at DESC\n LIMIT ?\n `, [playerId, limit]);\n\n // Also get the very first snapshot for total progress calculation\n const first = await q(`\n SELECT * FROM roster_snapshots\n WHERE player_id = ?\n ORDER BY snapshot_at ASC\n LIMIT 1\n `, [playerId]);\n\n return json({ snapshots: rows, first: first[0] || null });\n}\n\nasync function apiRosterEffectiveness(): Promise {\n // Aggregate stat gains by activity type\n const rows = await q(`\n SELECT attributed_activity,\n stat_name,\n COUNT(*) AS occurrences,\n SUM(delta) AS total_delta,\n ROUND(AVG(delta), 2) AS avg_delta\n FROM roster_events\n WHERE attributed_activity IS NOT NULL\n GROUP BY attributed_activity, stat_name\n ORDER BY attributed_activity, total_delta DESC\n `);\n\n // Also get totals per activity\n const totals = await q(`\n SELECT attributed_activity,\n COUNT(*) AS total_changes,\n SUM(delta) AS total_points\n FROM roster_events\n WHERE attributed_activity IS NOT NULL\n GROUP BY attributed_activity\n ORDER BY total_points DESC\n `);\n\n return json({ byStatAndActivity: rows, totals });\n}\n\nexport default async function (req: Request): Promise {\n await initDb();\n const url = new URL(req.url);\n const p = url.pathname.replace(/\\/+$/, \"\") || \"/\";\n try {\n if (p === \"/api/stats\") return await apiStats();\n if (p === \"/api/live\") return await apiLive();\n if (p === \"/api/retired\") return await apiRetired(url);\n if (p === \"/api/analytics\") return await apiAnalytics();\n\n // Roster Development endpoints\n if (p === \"/api/roster/players\") return await apiRosterPlayers();\n if (p === \"/api/roster/snapshots\") return await apiRosterSnapshots(url);\n if (p === \"/api/roster/events\") return await apiRosterEvents(url);\n if (p === \"/api/roster/changes\") return await apiRosterChanges();\n if (p === \"/api/roster/leads\") return await apiRosterLeads(url);\n if (p === \"/api/roster/leads/toggle\" && req.method === \"POST\") return await apiRosterLeadToggle(req);\n if (p === \"/api/roster/history\") return await apiRosterHistory(url);\n if (p === \"/api/roster/effectiveness\") return await apiRosterEffectiveness();\n if (p === \"/api/roster/turning-points\") return await apiRosterTurningPoints(url);\n if (p === \"/api/roster/daily\") return await apiRosterDaily(url);\n } catch (e) {\n return json({ error: String(e) }, 500);\n }\n return new Response(PAGE, {\n headers: { \"content-type\": \"text/html; charset=utf-8\" },\n });\n}","label",[136],"e492857d-7f73-4c73-877b-a108ad36929d",["D",1788522570294],[28],"3deb33ba-c4f5-44cf-8a18-ace046e88bcf","db.ts","01a06c3e-3531-7518-8403-a875e7bf0d24",8,["D",1788522524123],"import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\n\nexport async function initDb() {\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS snapshots (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n village_id TEXT,\n village_name TEXT,\n lumber INTEGER,\n clay INTEGER,\n iron INTEGER,\n crop INTEGER,\n lumber_prod INTEGER,\n clay_prod INTEGER,\n iron_prod INTEGER,\n crop_prod INTEGER,\n warehouse_cap INTEGER,\n granary_cap INTEGER,\n gold INTEGER,\n silver INTEGER,\n captured_at INTEGER NOT NULL\n )`);\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_snapshots_time ON snapshots(captured_at)`);\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_snapshots_village ON snapshots(village_id, captured_at)`);\n\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS villages (\n village_id TEXT PRIMARY KEY,\n name TEXT,\n coords TEXT,\n last_seen_at INTEGER\n )`);\n\n // One row per collect.ts run — lets the dashboard show \"last checked\" / \"session expired\"\n // even when a run produces no new snapshot.\n await sqlite.execute(`CREATE TABLE IF NOT EXISTS collect_log (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n status TEXT NOT NULL,\n message TEXT,\n at INTEGER NOT NULL\n )`);\n await sqlite.execute(`CREATE INDEX IF NOT EXISTS idx_collect_log_at ON collect_log(at)`);\n}\n",[150],"da0ca083-331e-4cd0-9745-d44807be570c","collect.ts","01a06c3e-3531-7518-8403-a70bcae040f2",9,["D",1788522546998],"interval","import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { parseHTML } from \"https://esm.sh/linkedom@0.16.11\";\nimport { initDb } from \"./db.ts\";\n\nconst BASE = \"https://b15.playcenturia.com\";\n\nfunction parseNum(s: string | null | undefined): number | null {\n if (!s) return null;\n const cleaned = s.replace(/[^\\d-]/g, \"\");\n return cleaned ? parseInt(cleaned, 10) : null;\n}\n\nasync function log(status: string, message: string) {\n await sqlite.execute(\n `INSERT INTO collect_log (status, message, at) VALUES (?, ?, ?)`,\n [status, message, Date.now()],\n );\n}\n\n// Fetches an authenticated page from b15 using the PHPSESSID cookie stored in\n// CENTURIA_SESSION. Throws \"SESSION_EXPIRED\" if the cookie is dead — the game\n// either 30x-redirects to a login page, or serves a page containing a login form.\nasync function fetchAuthed(path: string, cookie: string): Promise {\n const resp = await fetch(`${BASE}${path}`, {\n headers: {\n Cookie: `PHPSESSID=${cookie}`,\n \"User-Agent\": \"Mozilla/5.0 (compatible; VillageDashboard/1.0; personal use)\",\n },\n redirect: \"manual\",\n });\n if (resp.status >= 300 && resp.status < 400) {\n throw new Error(\"SESSION_EXPIRED\");\n }\n if (!resp.ok) {\n throw new Error(`HTTP_${resp.status}`);\n }\n const html = await resp.text();\n if (/id=[\"']?loginForm[\"']?|name=[\"']?login[\"']?/i.test(html)) {\n throw new Error(\"SESSION_EXPIRED\");\n }\n return html;\n}\n\n// Best-effort DOM scraping of dorf1.php (resource overview). Selectors were\n// confirmed live on 2026-09-04 against Centuria build v0.6.25 — if the game\n// updates its markup, these may need re-checking (compare against a fresh\n// page load in the browser).\nfunction extractSnapshot(html: string) {\n const { document } = parseHTML(html);\n const getText = (id: string) => document.getElementById(id)?.textContent?.trim() ?? null;\n\n const lumber = parseNum(getText(\"l1\"));\n const clay = parseNum(getText(\"l2\"));\n const iron = parseNum(getText(\"l3\"));\n const crop = parseNum(getText(\"l4\"));\n\n const prodRows = Array.from(document.querySelectorAll(\".production tr\"))\n .map((el: any) => (el.textContent ?? \"\").trim());\n const findProd = (name: string) => {\n const row = prodRows.find((r) => r.toLowerCase().startsWith(name.toLowerCase()));\n return row ? parseNum(row.split(\":\")[1] ?? null) : null;\n };\n const lumberProd = findProd(\"Lumber\");\n const clayProd = findProd(\"Clay\");\n const ironProd = findProd(\"Iron\");\n const cropProd = findProd(\"Crop\");\n\n // Warehouse/granary capacity: derived positionally from the resource bar's\n // full text (\" \"). Fragile —\n // flagged for verification against the first few real collect.ts runs.\n const stockBarText = document.getElementById(\"stockBar\")?.textContent?.replace(/\\s+/g, \" \").trim() ?? \"\";\n const nums = (stockBarText.match(/[\\d.,]+/g) ?? []).map((n) => parseNum(n));\n const warehouseCap = nums[0] ?? null;\n const granaryCap = nums[4] ?? null;\n\n const gold = parseNum(\n Array.from(document.querySelectorAll(\".coinGold\"))\n .map((el: any) => el.textContent)\n .find((t: string) => /\\d/.test(t ?? \"\")) ?? null,\n );\n\n const villageName = document.getElementById(\"villageName\")?.textContent?.trim()\n ?? document.getElementById(\"sidebarBoxActiveVillage\")?.textContent?.trim()\n ?? null;\n\n return {\n villageName,\n lumber, clay, iron, crop,\n lumberProd, clayProd, ironProd, cropProd,\n warehouseCap, granaryCap, gold,\n };\n}\n\nexport default async function collect(_interval: Interval) {\n await initDb();\n\n const cookie = Deno.env.get(\"CENTURIA_SESSION\");\n if (!cookie) {\n console.log(\"Missing CENTURIA_SESSION — skipping run. Add it in this val's env vars.\");\n return;\n }\n\n let html: string;\n try {\n html = await fetchAuthed(\"/dorf1.php\", cookie);\n } catch (e) {\n const msg = String((e as Error)?.message ?? e);\n if (msg === \"SESSION_EXPIRED\") {\n await log(\"expired\", \"PHPSESSID cookie is no longer valid — grab a fresh one from Chrome DevTools and update CENTURIA_SESSION.\");\n console.log(\"Session expired — update CENTURIA_SESSION.\");\n } else {\n await log(\"error\", msg);\n console.error(\"Collect fetch failed:\", msg);\n }\n return;\n }\n\n const snap = extractSnapshot(html);\n if (snap.lumber === null && snap.clay === null && snap.iron === null && snap.crop === null) {\n await log(\"error\", \"Fetched dorf1.php but found no resource values — page markup may have changed.\");\n console.error(\"Parsed 0 resource fields — markup likely changed, check selectors in collect.ts.\");\n return;\n }\n\n await sqlite.execute(\n `INSERT INTO snapshots\n (village_id, village_name, lumber, clay, iron, crop,\n lumber_prod, clay_prod, iron_prod, crop_prod,\n warehouse_cap, granary_cap, gold, silver, captured_at)\n VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,\n [\n snap.villageName, snap.villageName,\n snap.lumber, snap.clay, snap.iron, snap.crop,\n snap.lumberProd, snap.clayProd, snap.ironProd, snap.cropProd,\n snap.warehouseCap, snap.granaryCap, snap.gold, null,\n Date.now(),\n ],\n );\n await log(\"ok\", `${snap.villageName ?? \"village\"}: L${snap.lumber} C${snap.clay} I${snap.iron} Cr${snap.crop}`);\n console.log(`Collected snapshot for ${snap.villageName ?? \"(unknown village)\"}`);\n}\n",[157],"c97e2158-3782-4d64-8145-241447a4bc5c","AGENTS.md","01a06c3e-3531-7518-8403-9ebcc109d10f",["D",1788522411148],"# apexGemTracker — Core Architecture & Context\n\nThis Val tracks Apex Manager market listings to find \"hidden gems\": players who displayed BELOW A- potential on the market but actually retired with A- or higher potential (i.e., fog hid their true quality)[cite: 1]. This is strictly a data collection and comparison pipeline with a web dashboard, entirely separate from notification/alert Vals[cite: 1].\n\n## ⚠️ Core Business Logic\n\n1. **The Gem Rule (`db.ts`)**\n `is_gem = isBelowAMinus(marketPotential) && !isBelowAMinus(retiredPotential)`[cite: 1, 4]\n *CRITICAL:* Always compare LETTER GRADES (`estimatedRank` / `estimatedPotential`), never the numeric `trueRating` or `potentialRating`[cite: 1]. The game's numeric ratings do not map to a reliable letter grade scale (e.g., a potential rating of 1986 might retire as a C/B, not an A)[cite: 1]. The authoritative \"true\" grade is the revealed letter grade on the retired card at 100% confidence[cite: 1].\n\n2. **Fog Semantics**\n `fogConfidence` (API) = INTEL[cite: 1]. Displayed \"Fog %\" = 100 - (fogConfidence * 100)[cite: 3]. High intel (low fog) = well-scouted; low intel (high fog) = obscured/lottery[cite: 1].\n\n## Data Model (SQLite)\n\n* **`listings`**: Snapshot of every market listing (keyed by `auction_id`)[cite: 1]. Captures the first-seen state (est rank/potential, intel, fog, salary, full `player_json`)[cite: 1, 3]. Upserted every run[cite: 1]. `left_at` is set when a listing stops appearing in the active auctions API[cite: 1, 3].\n* **`retired`**: Resolved players[cite: 1]. Compares market display vs. retired revealed grade (`retired_rank` / `retired_potential`)[cite: 1]. Stores market age/salary snapshot + true numeric ratings + `is_gem` flag + `card_url`[cite: 1, 2].\n* **`player_checks`**: Dedupe and backoff tracking table (checked every 10 minutes) so we don't spam the API for unresolved players[cite: 1, 2].\n\n## Scheduled Jobs (Cron)\n\n* **`collect.ts` (*/15 mins)**: Snapshots all `/api/auctions` listings[cite: 1]. Inserts new listings and updates `left_at` for expired auctions[cite: 3].\n* **`check.ts` (Hourly)**: Looks at listings that left the market >=1h ago[cite: 1]. Fetches `/api/players/{id}`[cite: 1]. If retired, calculates gem status and moves to `retired` table[cite: 1, 2]. If still active, backs off and checks again later[cite: 1]. \n\n## Web & API Endpoints (HTTP)\n\n* **`site.ts`**: The main frontend UI and API router[cite: 9]. Serves a rich HTML/JS dashboard at the root (`/`) with tabs for Live Market, Retired Players, and Advanced Analytics charts[cite: 6, 9]. Serves JSON endpoints at `/api/stats`, `/api/live`, `/api/retired`, and `/api/analytics`[cite: 9].\n* **`report.ts`**: Spreadsheet export router[cite: 1, 7]. Serves raw JSON and CSVs (e.g., `/csv` for gems, `/csv/listings` for active market)[cite: 1, 7]. Designed to be ingested directly by Google Sheets using the `IMPORTDATA()` function[cite: 1].\n* **`main.ts`**: A utility script to initialize the DB schema and print current table row counts[cite: 1, 5]. \n\n## Environment\n\n* `APEX_TOKEN`: The bearer token required to authenticate against the Apex Manager API[cite: 1]. All scripts will fail if this is missing or expired[cite: 1, 2, 3].",[165],"pulls","branches","forks","envVars",{"_177":61,"_179":61},"connections",{"_177":61,"_178":61},"count","unfilledCount","emptyCount",{"_43":164,"_32":165,"_30":166,"_138":-5,"_41":42,"_40":139,"_140":25,"_38":198,"_13":61,"_33":20,"_35":168,"_24":25,"_144":-5,"_186":199,"_27":200},{"_43":156,"_32":157,"_30":158,"_138":-5,"_41":42,"_40":159,"_140":25,"_38":195,"_13":139,"_33":161,"_35":162,"_24":25,"_144":-5,"_186":196,"_27":197},{"_43":149,"_32":150,"_30":151,"_138":-5,"_41":42,"_40":152,"_140":125,"_38":192,"_13":139,"_33":34,"_35":154,"_24":25,"_144":-5,"_186":193,"_27":194},{"_43":146,"_32":28,"_30":31,"_138":-5,"_41":42,"_40":14,"_140":125,"_38":189,"_13":139,"_33":34,"_35":37,"_24":25,"_144":-5,"_186":190,"_27":191},{"_43":135,"_32":136,"_30":137,"_138":-5,"_41":42,"_40":139,"_140":25,"_38":185,"_13":61,"_33":142,"_35":143,"_24":25,"_144":-5,"_186":187,"_27":188},["D",1788522411148],"children",[],[136],["D",1788522570294],[],[28],["D",1788522524123],[],[150],["D",1788522546998],[],[157],["D",1788522411148],[],[165],["D",1788522411148],["D",1789477862254],{"_98":97,"_106":102},{"_98":97,"_99":100,"_101":102,"_18":55,"_103":104,"_105":25},{"_13":87,"_32":88,"_43":42,"_89":44,"_90":-5,"_91":-5,"_92":-5,"_93":-5,"_94":-5,"_95":45,"_96":97},"ctx",{"_80":-5,"_127":239,"_240":241,"_242":243,"_244":25,"_245":25,"_246":25,"_247":125,"_248":-5,"_249":-7,"_250":-7,"_251":61,"_252":25,"_253":25,"_254":-5,"_255":25,"_256":25,"_257":25,"_258":-5},"ENV",{"_210":211,"_212":213,"_214":88,"_215":216,"_217":218,"_219":220,"_221":222,"_223":224,"_225":226,"_227":228,"_229":230,"_231":232,"_233":234,"_235":236,"_237":238},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","e87eb04e707cf64e7e532c8e8ec395f209e17f15","RENDER_GIT_BRANCH","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]

A+ / S-Tier Potential Hit Rate Matrix

Reached ≥ A- potential, by fog band

Age Cohort vs. Upside Decay

Salary vs Revealed Rating

Ceiling Grade Distribution

🏛️ Centuria b15 — Village Dashboard

History

Diff · v10