Apex Scout — market & roster intel

[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_299":300,"_301":302},"routes/_app.x.$handle.$name",{"_99":98,"_33":45,"_108":89,"_28":109,"_13":14,"_110":-5,"_11":111,"_112":113,"_114":115,"_116":117,"_118":119,"_20":-5,"_120":-5,"_121":-5,"_122":27,"_123":124,"_125":25,"_126":-5,"_127":-5,"_128":129},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_43":44,"_33":45,"_38":46,"_47":-5,"_48":27,"_49":50,"_51":50,"_52":53,"_54":-5,"_18":55,"_56":57,"_58":25,"_59":-5,"_60":-5,"_61":62,"_63":64,"_65":66,"_67":68,"_69":70,"_71":-5,"_72":73,"_74":73,"_75":76,"_77":76,"_78":76,"_79":73,"_80":73,"_81":82,"_83":84,"_85":86,"_87":27},"version",100,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":23,"_24":25,"_26":27,"_28":29,"_30":29},"file",{"_31":32,"_33":29,"_34":35,"_36":37,"_38":39,"_40":14,"_41":42,"_24":27},"previous",{"_31":32,"_33":29,"_34":35,"_36":37},"deleted",true,"deletedHere",false,"path","export.ts","latestPath","fileId","01bf7b54-9f0f-11f1-88b1-1607ee4eb77e","name","fileType","script","content","export default async function () {\n const files = [\n \"db.ts\",\n \"collect.ts\",\n \"site.ts\",\n \"page.ts\",\n \"collect_roster.ts\",\n ];\n let allCode = \"\";\n\n for (const f of files) {\n try {\n allCode += `\\n\\n// ================= ${f} =================\\n\\n`;\n allCode += await Deno.readTextFile(`./${f}`);\n } catch (e) {\n // Skip if file doesn't exist\n }\n }\n\n console.log(allCode);\n}","createdAt",["D",1787502237779],"branchVersion","branchId","3439b816-bce6-43b4-8219-2a89c70ea329","id","b4ea5955-0efb-41c6-a963-10779cb76bf7","apexGemTracker",["D",1787146151215],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","Tracks market players to find fog-hidden A-+ gems on retirement","imageUrl","b71e33f8-d99a-4cff-b095-38f58c0a5236","pinnedFileId","f1a1657c-9bd3-11f1-8fbb-1607ee4eb77e","pinPreview","pinMigrated","screenshotVersion","likeCount",0,"orgId","019fd3a0-5b29-72d9-8429-c2783b92627c","tursoName","b4ea59550efb41c6a96310779cb76bf7","tursoHostname","b4ea59550efb41c6a96310779cb76bf7-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789491713377],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_99":98,"_107":103},"org",{"_99":98,"_100":101,"_102":103,"_18":55,"_104":105,"_106":27},"branch",{"_13":88,"_33":89,"_43":42,"_90":44,"_91":-5,"_92":-5,"_93":-5,"_94":-5,"_95":-5,"_96":45,"_97":98},"isOwned",215,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","kubeniz","handle","displayName","Valentino Rodrigues","avatar","https://lh3.googleusercontent.com/a/ACg8ocLUTO6MzdqeXpcs4pmDUIuBGqLrhsGH0xN-uIFC_LP0kPooCA=s96-c","tier","free","canRestrictVals","profileImageUrl","branchName","","adoptOrgId",{"_43":44,"_33":45,"_38":294,"_47":-5,"_48":27,"_49":50,"_51":50,"_52":53,"_54":-5,"_18":55,"_56":57,"_58":25,"_59":-5,"_60":-5,"_61":62,"_63":64,"_65":66,"_67":68,"_69":295,"_71":-5,"_72":73,"_74":73,"_75":76,"_77":76,"_78":76,"_79":73,"_80":73,"_81":296,"_83":297,"_85":298,"_87":27},"tree",[245,246,247,248,249,250,251,252,253,254,255,256],"files",[245,246,247,248,249,250,251,252,253,254,255,256],"counts",{"_235":62,"_236":190,"_237":62,"_238":239,"_240":241},"contents",[130,131,132,133,134,135,136,137,138,139,140,141],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_43":228,"_33":229,"_31":230,"_145":-5,"_41":42,"_40":231,"_147":27,"_38":232,"_13":202,"_34":20,"_36":233,"_24":27,"_151":-5,"_28":234},{"_43":221,"_33":222,"_31":223,"_145":-5,"_41":42,"_40":224,"_147":25,"_38":225,"_13":149,"_34":218,"_36":226,"_24":27,"_151":-5,"_28":227},{"_43":213,"_33":214,"_31":215,"_145":-5,"_41":42,"_40":216,"_147":25,"_38":217,"_13":167,"_34":218,"_36":219,"_24":27,"_151":-5,"_28":220},{"_43":205,"_33":206,"_31":207,"_145":-5,"_41":42,"_40":208,"_147":27,"_38":209,"_13":210,"_34":35,"_36":211,"_24":27,"_151":-5,"_28":212},{"_43":197,"_33":198,"_31":199,"_145":-5,"_41":42,"_40":200,"_147":27,"_38":201,"_13":202,"_34":35,"_36":203,"_24":27,"_151":-5,"_28":204},{"_43":193,"_33":29,"_31":32,"_145":-5,"_41":42,"_40":14,"_147":27,"_38":194,"_13":195,"_34":35,"_36":37,"_24":27,"_151":-5,"_28":196},{"_43":186,"_33":187,"_31":188,"_145":-5,"_41":42,"_40":174,"_147":25,"_38":189,"_13":190,"_34":35,"_36":191,"_24":27,"_151":-5,"_28":192},{"_43":178,"_33":179,"_31":180,"_145":-5,"_41":42,"_40":181,"_147":27,"_38":182,"_13":183,"_34":35,"_36":184,"_24":27,"_151":-5,"_28":185},{"_43":170,"_33":171,"_31":57,"_145":-5,"_41":42,"_40":172,"_147":25,"_38":173,"_13":174,"_34":159,"_36":175,"_24":27,"_151":176,"_28":177},{"_43":162,"_33":163,"_31":164,"_145":-5,"_41":42,"_40":165,"_147":25,"_38":166,"_13":167,"_34":35,"_36":168,"_24":27,"_151":-5,"_28":169},{"_43":153,"_33":154,"_31":155,"_145":-5,"_41":42,"_40":156,"_147":27,"_38":157,"_13":158,"_34":159,"_36":160,"_24":27,"_151":-5,"_28":161},{"_43":142,"_33":143,"_31":144,"_145":-5,"_41":42,"_40":146,"_147":25,"_38":148,"_13":149,"_34":35,"_36":150,"_24":27,"_151":-5,"_28":152},"1899354d-34d7-4a4d-a4e5-51944db29e34","tmp_recheck_active.ts","defa3af6-9cba-11f1-b2f7-1607ee4eb77e","parentId",71,"latest",["D",1787347035385],9,"export default async function (req: Request): Promise {\n const token = Deno.env.get(\"APEX_TOKEN\");\n const resp = await fetch(\"https://apexmanager.rootsxo.studio/api/auctions\", {\n headers: { Authorization: `Bearer ${token}` },\n });\n const data = await resp.json();\n const auctions = Array.isArray(data)\n ? data\n : (data.auctions ?? data.items ?? []);\n\n // Return the first auction object to see all its top-level properties\n const sample = auctions[0] ?? { error: \"No active auctions found\" };\n\n return new Response(JSON.stringify(sample, null, 2), {\n headers: { \"content-type\": \"application/json\" },\n });\n}","label",[143],"b7c93753-141b-4bc1-be9f-144cb4fc18cd","site.ts","ef09917e-9d89-11f1-8a53-1607ee4eb77e",90,["D",1787501289895],17,"http","// site.ts — Apex Scout: live dashboard + JSON API for apexGemTracker.\n// Serves the UI at / and reads the listings/retired/roster tables at /api/*.\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport PAGE from \"./page.ts\";\n\nconst GRADES = [\n \"D-\", \"D\", \"D+\", \"C-\", \"C\", \"C+\", \"B-\", \"B\", \"B+\", \"A-\", \"A\", \"A+\", \"S-\", \"S\", \"S+\"\n];\nconst AMINUS = GRADES.indexOf(\"A-\");\nconst SMINUS = GRADES.indexOf(\"S-\");\nconst ELITE = GRADES.slice(AMINUS).map((g) => `'${g}'`).join(\",\");\nconst SUPER_ELITE = GRADES.slice(SMINUS).map((g) => `'${g}'`).join(\",\");\n\nconst LISTINGS_UNOWNED = `(\n player_json IS NULL \n OR json_extract(player_json, '$.previousTeams') IS NULL \n OR json_array_length(json_extract(player_json, '$.previousTeams')) = 0\n)`;\n\nconst RETIRED_UNOWNED = `NOT EXISTS (\n SELECT 1 FROM listings l_prev \n WHERE l_prev.player_id = r.player_id \n AND l_prev.player_json IS NOT NULL \n AND json_extract(l_prev.player_json, '$.previousTeams') IS NOT NULL \n AND json_array_length(json_extract(l_prev.player_json, '$.previousTeams')) > 0\n)`;\n\nconst gradeCase = (col: string) =>\n \"CASE \" + col + \" \" + GRADES.map((g, i) => `WHEN '${g}' THEN ${i}`).join(\" \") + \" ELSE -1 END\";\n\nasync function ensureSchema() {\n try { await sqlite.execute(`ALTER TABLE listings ADD COLUMN end_at INTEGER`); } catch (_e) {}\n try {\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS roster_players (\n player_id TEXT PRIMARY KEY,\n name TEXT NOT NULL,\n role TEXT,\n growth_class TEXT,\n archetype TEXT,\n status TEXT,\n salary INTEGER,\n first_seen_at INTEGER,\n last_updated_at INTEGER,\n player_json TEXT\n );\n `);\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS roster_clues (\n clue_id TEXT PRIMARY KEY,\n player_id TEXT NOT NULL,\n kind TEXT,\n activity_key TEXT,\n activity_label TEXT,\n text TEXT,\n changes_json TEXT,\n stat_gain_sum INTEGER DEFAULT 0,\n occurred_at INTEGER,\n created_at INTEGER\n );\n `);\n } catch (_e) {}\n}\n\nasync function q(sql: string, args: any[] = []): Promise {\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) ? Object.fromEntries(cols.map((c, i) => [c, row[i]])) : 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`))[0];\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) => GRADES.includes(g));\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(`(${gradeCase(\"r.retired_potential\")} - ${gradeCase(\"COALESCE(r.market_est_potential, l.est_potential)\")}) >= ?`);\n args.push(Number(minDelta));\n }\n if (maxDelta !== null && maxDelta !== \"\" && !isNaN(Number(maxDelta))) {\n where.push(`(${gradeCase(\"r.retired_potential\")} - ${gradeCase(\"COALESCE(r.market_est_potential, l.est_potential)\")}) <= ?`);\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\") orderSql = `l.growth_class ${sortDir}`;\n else if (sortKey === \"age\") orderSql = `COALESCE(r.market_age, l.age) ${sortDir}`;\n else if (sortKey === \"fog\") orderSql = `COALESCE(r.market_fog_pct, l.fog_pct) ${sortDir}`;\n else if (sortKey === \"salary\") orderSql = `COALESCE(r.market_salary, l.salary) ${sortDir}`;\n else if (sortKey === \"m_rank\") orderSql = `${gradeCase(\"COALESCE(r.market_est_rank, l.est_rank)\")} ${sortDir}`;\n else if (sortKey === \"m_pot\") orderSql = `${gradeCase(\"COALESCE(r.market_est_potential, l.est_potential)\")} ${sortDir}`;\n else if (sortKey === \"r_rank\") orderSql = `${gradeCase(\"r.retired_rank\")} ${sortDir}`;\n else if (sortKey === \"r_pot\") orderSql = `${gradeCase(\"r.retired_potential\")} ${sortDir}, r.retired_at DESC`;\n else if (sortKey === \"delta\") orderSql = `(${gradeCase(\"r.retired_potential\")} - ${gradeCase(\"COALESCE(r.market_est_potential, l.est_potential)\")}) ${sortDir}`;\n else if (sortKey === \"retired_at\") orderSql = `r.retired_at ${sortDir}`;\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 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 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) || { band: b, n: 0, t9: 0, t10: 0, t11: 0, t12: 0, t13: 0 }\n );\n\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 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 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 endpoint\nasync function apiRosterIntel(): Promise {\n await ensureSchema();\n const players = await q(`\n SELECT player_id, name, role, growth_class, archetype, status, salary, last_updated_at, player_json\n FROM roster_players\n ORDER BY CASE status WHEN 'active' THEN 1 WHEN 'reserve' THEN 2 WHEN 'bench' THEN 3 ELSE 4 END, name ASC\n `);\n\n const clues = await q(`\n SELECT clue_id, player_id, kind, activity_key, activity_label, text, changes_json, stat_gain_sum, occurred_at\n FROM roster_clues\n ORDER BY occurred_at DESC\n `);\n\n const activityRoi = await q(`\n SELECT \n activity_label,\n COUNT(*) AS total_events,\n SUM(CASE WHEN stat_gain_sum > 0 THEN 1 ELSE 0 END) AS gain_events,\n SUM(stat_gain_sum) AS total_stat_gains,\n ROUND(AVG(stat_gain_sum), 2) AS avg_gain\n FROM roster_clues\n WHERE kind = 'history' AND activity_label IS NOT NULL\n GROUP BY activity_label\n ORDER BY total_stat_gains DESC, total_events DESC\n `);\n\n return json({ players, clues, activityRoi });\n}\n\nexport default async function (req: Request): Promise {\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 if (p === \"/api/roster\") return await apiRosterIntel();\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}",[154],"8ae0225b-4aae-48d6-8744-0c87083beb24","seed_test.ts","6acdd9de-9bd5-11f1-90a0-1607ee4eb77e",20,["D",1787147868661],2,"import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { initDb, isGem, isBelowAMinus } from \"./db.ts\";\n\nawait initDb();\nconst TOKEN = Deno.env.get(\"APEX_TOKEN\");\n\nasync function api(url: string) {\n const r = await fetch(url, { headers: { Authorization: `Bearer ${TOKEN}` } });\n return r.ok ? await r.json() : null;\n}\n\nconst wl = await api(\"https://apexmanager.rootsxo.studio/api/watchlist\");\nconst arr = Array.isArray(wl) ? wl : (wl.items ?? wl.players ?? []);\n\n// Prefer a retired player who displayed below A- on the market but has A-+ potential (a real gem).\nconst candidates = arr.filter(\n (p: any) => (p.isRetired || p.trueRating != null) && p.potentialRating >= 1800 && isBelowAMinus(p.estimatedPotential),\n);\nconst pick = (candidates[0] as any) ?? (arr.find((p: any) => p.isRetired || p.trueRating != null) as any);\nif (!pick) {\n console.log(\"No retired player found to seed.\");\n} else {\n const c = await api(`https://apexmanager.rootsxo.studio/api/players/${pick.id}`);\n if (!c) {\n console.log(\"Could not fetch player card.\");\n } else {\n const intel = c.fogConfidence ?? 0;\n const fogPct = Math.round((1 - intel) * 100);\n const gem = isGem(c.estimatedPotential, c.estimatedPotential); // market sourced from card here\n const pid = \"test_\" + c.id; // prefix marks this as a manual test row\n await sqlite.execute(\n `INSERT INTO retired\n (player_id, name, market_est_rank, market_est_potential, market_intel,\n market_fog_pct, market_salary, true_rating, potential_rating, peak_rating,\n retired_rank, retired_potential, retired_at, checked_at, is_gem, card_url)\n VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)\n ON CONFLICT(player_id) DO UPDATE SET\n market_est_potential=excluded.market_est_potential,\n true_rating=excluded.true_rating,\n potential_rating=excluded.potential_rating,\n retired_rank=excluded.retired_rank,\n retired_potential=excluded.retired_potential,\n is_gem=excluded.is_gem,\n card_url=excluded.card_url`,\n [\n pid, c.name, c.estimatedRank, c.estimatedPotential, intel,\n fogPct, c.salary ?? null, c.trueRating ?? null, c.potentialRating ?? null, c.peakRating ?? null,\n c.estimatedRank, c.estimatedPotential,\n Date.now(), Date.now(), gem ? 1 : 0,\n `https://apexmanager.rootsxo.studio/player/${c.id}`,\n ],\n );\n console.log(`Seeded test row: ${c.name} | market ${c.estimatedRank}/${c.estimatedPotential} (fog ${fogPct}%) -> potential ${c.potentialRating} | gem=${gem}`);\n }\n}\n",[163],"357da92c-a32f-4cf0-ba45-e30a56bf1d6f","report.ts",37,["D",1787175228715],6,"import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { initDb } from \"./db.ts\";\n\n// Turns a header string + array-of-rows into CSV text, quoting values that\n// contain commas, quotes, or newlines (so names/urls with commas stay intact).\nfunction toCsv(header: string, rows: any[][]): string {\n const esc = (v: any) => {\n if (v === null || v === undefined) return \"\";\n const s = String(v);\n return /[\",\\n]/.test(s) ? `\"${s.replace(/\"/g, '\"\"')}\"` : s;\n };\n return [header, ...rows.map((r) => r.map(esc).join(\",\"))].join(\"\\n\");\n}\n\nexport default async function handler(req: Request): Promise {\n await initDb();\n const url = new URL(req.url);\n\n // Resolved players: market display vs revealed grade, gems flagged.\n const retiredRows = await sqlite.execute(\n `SELECT name, market_est_rank, market_est_potential, market_fog_pct, market_age, market_salary,\n retired_rank, retired_potential, true_rating, potential_rating, peak_rating,\n is_gem, card_url, retired_at\n FROM retired\n ORDER BY is_gem DESC, potential_rating DESC`,\n );\n const retired = retiredRows.rows as any[];\n\n // Active market listings currently up for auction (not yet resolved).\n const listingRows = await sqlite.execute(\n `SELECT name, role, age, growth_class, archetype, est_rank, est_potential,\n fog_pct, salary, starting_bid, current_bid, first_seen_at, last_seen_at\n FROM listings\n WHERE left_at IS NULL\n ORDER BY fog_pct DESC, first_seen_at ASC`,\n );\n const listings = listingRows.rows as any[];\n\n // Every listing ever seen, active or not. left_at is NULL while a listing\n // is still active; it gets set once collect.ts hasn't seen it in 45+ min.\n const allListingRows = await sqlite.execute(\n `SELECT name, role, age, growth_class, archetype, est_rank, est_potential,\n fog_pct, salary, starting_bid, current_bid, first_seen_at, last_seen_at, left_at\n FROM listings\n ORDER BY (left_at IS NULL) DESC, first_seen_at DESC`,\n );\n const allListings = allListingRows.rows as any[];\n\n if (url.pathname.endsWith(\"/csv\") || url.pathname.endsWith(\"/csv/gems\")) {\n const header =\n \"name,market_rank,market_potential,market_fog_pct,market_age,market_salary,retired_rank,retired_potential,true_rating,potential_rating,peak_rating,is_gem,card_url,retired_at\";\n const body = toCsv(\n header,\n retired.map((r) => [\n r.name,\n r.market_est_rank,\n r.market_est_potential,\n r.market_fog_pct,\n r.market_age,\n r.market_salary,\n r.retired_rank,\n r.retired_potential,\n r.true_rating,\n r.potential_rating,\n r.peak_rating,\n r.is_gem,\n r.card_url,\n r.retired_at ? new Date(r.retired_at).toISOString() : \"\",\n ]),\n );\n return new Response(body, {\n headers: { \"Content-Type\": \"text/csv; charset=utf-8\" },\n });\n }\n\n if (url.pathname.endsWith(\"/csv/listings/all\")) {\n const header =\n \"name,role,age,growth_class,archetype,market_rank,market_potential,fog_pct,salary,starting_bid,current_bid,status,first_seen_at,last_seen_at,left_at\";\n const body = toCsv(\n header,\n allListings.map((r) => [\n r.name,\n r.role,\n r.age,\n r.growth_class,\n r.archetype,\n r.est_rank,\n r.est_potential,\n r.fog_pct,\n r.salary,\n r.starting_bid,\n r.current_bid,\n r.left_at ? \"left\" : \"active\",\n r.first_seen_at ? new Date(r.first_seen_at).toISOString() : \"\",\n r.last_seen_at ? new Date(r.last_seen_at).toISOString() : \"\",\n r.left_at ? new Date(r.left_at).toISOString() : \"\",\n ]),\n );\n return new Response(body, {\n headers: { \"Content-Type\": \"text/csv; charset=utf-8\" },\n });\n }\n\n if (url.pathname.endsWith(\"/csv/listings\")) {\n const header =\n \"name,role,age,growth_class,archetype,market_rank,market_potential,fog_pct,salary,starting_bid,current_bid,first_seen_at,last_seen_at\";\n const body = toCsv(\n header,\n listings.map((r) => [\n r.name,\n r.role,\n r.age,\n r.growth_class,\n r.archetype,\n r.est_rank,\n r.est_potential,\n r.fog_pct,\n r.salary,\n r.starting_bid,\n r.current_bid,\n r.first_seen_at ? new Date(r.first_seen_at).toISOString() : \"\",\n r.last_seen_at ? new Date(r.last_seen_at).toISOString() : \"\",\n ]),\n );\n return new Response(body, {\n headers: { \"Content-Type\": \"text/csv; charset=utf-8\" },\n });\n }\n\n if (url.pathname.endsWith(\"/listings/all\")) {\n return Response.json(allListings);\n }\n\n if (url.pathname.endsWith(\"/listings\")) {\n return Response.json(listings);\n }\n\n return Response.json(retired);\n}","apex-gem-tracker",[171],"e9701814-9e88-4bd3-abaa-eb85f042731a","page.ts","50d3f082-9d8c-11f1-8b76-1607ee4eb77e",88,["D",1787501041675],14,"export default `\n\n\n\n\nApex Scout — market & roster intel\n\n\n\n\n\n\n\n

APEX SCOUT

\n 🔒 UNLISTED\n \n

\n \n 100%\n \n

\n\n

\n \n

live

\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 \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 \n

Squad Growth & Activity ROI

\n \n \n

Activity Development Efficiency

Actual confirmed stat points gained across your organisation by activity type.

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

Activity	Total Logged Events	Events With Stat Boost	Total Stat Points Gained	Avg Stat Gain / Event

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

\n\n \n

Advanced Signal Analysis

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

Reached ≥ A- potential, by fog band

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

\n \n

Age Cohort vs. Upside Decay

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

\n\n

Salary vs Revealed Rating

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

\n\n

Ceiling Grade Distribution

Displayed potential grades split by player growth archetype.

\n\n\n\n`;",[179],"52f2df96-bed7-44a7-bf25-dbc54e582e4b","main.ts","f693e3b8-9bd1-11f1-bfe0-1607ee4eb77e",["D",1787147006802],1,"import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { initDb } from \"./db.ts\";\n\n// Run this once (or any time) to initialize the schema and see current state.\nawait initDb();\n\nconst l = await sqlite.execute(`SELECT COUNT(*) AS c FROM listings`);\nconst r = await sqlite.execute(`SELECT COUNT(*) AS c FROM retired`);\nconst g = await sqlite.execute(`SELECT COUNT(*) AS c FROM retired WHERE is_gem = 1`);\nconst p = await sqlite.execute(`SELECT COUNT(*) AS c FROM player_checks`);\n\nconsole.log(`apexGemTracker status`);\nconsole.log(` listings: ${l.rows[0]?.c ?? 0}`);\nconsole.log(` retired: ${r.rows[0]?.c ?? 0}`);\nconsole.log(` gems found: ${g.rows[0]?.c ?? 0}`);\nconsole.log(` players checked: ${p.rows[0]?.c ?? 0}`);\n",[187],"895b4aaa-c789-4d5c-aef3-7a7c72d12025",["D",1787502237779],3,[29],"1548e7eb-358e-4951-b108-a3c3394efaa1","db.ts","e798aad6-9bd3-11f1-89a9-1607ee4eb77e",35,["D",1787174487396],5,"import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\n\n// A \"gem\" = a player who displayed BELOW A- potential on the market, but whose\n// revealed (retired) potential grade is A- or higher. Comparison is by letter\n// grade only — the game's numeric true/potential rating does NOT map to a fixed\n// grade scale we can rely on, so we never infer a grade from a number.\nconst A_MINUS_INDEX = 9;\n\nconst GRADE_INDEX: Record = {\n \"D-\": 0,\n \"D\": 1,\n \"D+\": 2,\n \"C-\": 3,\n \"C\": 4,\n \"C+\": 5,\n \"B-\": 6,\n \"B\": 7,\n \"B+\": 8,\n \"A-\": 9,\n \"A\": 10,\n \"A+\": 11,\n \"S-\": 12,\n \"S\": 13,\n \"S+\": 14,\n};\n\nexport function gradeIndex(letter?: string | null): number | null {\n if (!letter) return null;\n return GRADE_INDEX[letter] ?? null;\n}\n\n// Was the displayed potential grade strictly below A- ? (i.e. fog hid an A-+ player)\nexport function isBelowAMinus(letter?: string | null): boolean {\n const i = gradeIndex(letter);\n return i !== null && i < A_MINUS_INDEX;\n}\n\nexport function isGem(\n marketPotential: string | null,\n retiredPotential: string | null,\n): boolean {\n return isBelowAMinus(marketPotential) && !isBelowAMinus(retiredPotential);\n}\n\nexport async function initDb() {\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS listings (\n auction_id TEXT PRIMARY KEY,\n player_id TEXT NOT NULL,\n name TEXT,\n role TEXT,\n age REAL,\n growth_class TEXT,\n archetype TEXT,\n est_rank TEXT,\n est_potential TEXT,\n intel REAL,\n fog_pct INTEGER,\n salary INTEGER,\n starting_bid INTEGER,\n current_bid INTEGER,\n first_seen_at INTEGER,\n last_seen_at INTEGER,\n left_at INTEGER,\n player_json TEXT\n )`,\n );\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS retired (\n player_id TEXT PRIMARY KEY,\n name TEXT,\n market_est_rank TEXT,\n market_est_potential TEXT,\n market_intel REAL,\n market_fog_pct INTEGER,\n market_salary INTEGER,\n true_rating INTEGER,\n potential_rating INTEGER,\n peak_rating INTEGER,\n retired_at INTEGER,\n checked_at INTEGER,\n is_gem INTEGER DEFAULT 0\n )`,\n );\n try {\n await sqlite.execute(`ALTER TABLE retired ADD COLUMN card_url TEXT`);\n } catch { /* already exists */ }\n try {\n await sqlite.execute(`ALTER TABLE retired ADD COLUMN retired_rank TEXT`);\n } catch { /* already exists */ }\n try {\n await sqlite.execute(\n `ALTER TABLE retired ADD COLUMN retired_potential TEXT`,\n );\n } catch { /* already exists */ }\n try {\n await sqlite.execute(`ALTER TABLE retired ADD COLUMN market_age REAL`);\n } catch { /* already exists */ }\n await sqlite.execute(\n `CREATE TABLE IF NOT EXISTS player_checks (\n player_id TEXT PRIMARY KEY,\n name TEXT,\n last_checked_at INTEGER,\n retired INTEGER DEFAULT 0\n )`,\n );\n await sqlite.execute(\n `CREATE INDEX IF NOT EXISTS idx_listings_player ON listings(player_id)`,\n );\n await sqlite.execute(\n `CREATE INDEX IF NOT EXISTS idx_listings_left ON listings(left_at)`,\n );\n await sqlite.execute(\n `CREATE INDEX IF NOT EXISTS idx_listings_first ON listings(first_seen_at)`,\n );\n}",[198],"96f74fdb-b55e-46f7-9947-47257f1a7e45","collect_roster.ts","1b2821fc-9f0c-11f1-a7eb-1607ee4eb77e",92,["D",1787501463976],4,"import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { initDb } from \"./db.ts\";\n\nexport default async function collectRoster(_interval?: Interval) {\n await initDb();\n const token = Deno.env.get(\"APEX_TOKEN\");\n if (!token) throw new Error(\"Missing APEX_TOKEN\");\n\n const headers = { Authorization: `Bearer ${token}` };\n\n // 1. Fetch Roster\n const rosterResp = await fetch(\"https://apexmanager.rootsxo.studio/api/roster\", { headers });\n let rosterData: any = {};\n if (rosterResp.ok) {\n rosterData = await rosterResp.json();\n } else {\n console.log(`Roster fetch status: ${rosterResp.status}`);\n }\n\n // 2. Fetch Knowledge (Check /api/knowledge/status first, fallback to /api/knowledge)\n let knowData: any = {};\n let knowResp = await fetch(\"https://apexmanager.rootsxo.studio/api/knowledge/status\", { headers });\n if (!knowResp.ok) {\n knowResp = await fetch(\"https://apexmanager.rootsxo.studio/api/knowledge\", { headers });\n }\n if (knowResp.ok) {\n knowData = await knowResp.json();\n } else {\n console.log(`Knowledge fetch status: ${knowResp.status}`);\n }\n\n const myManagerId = knowData.viewerManagerId || \"cmpsoddfr0028qvbuk41j443y\";\n const now = Date.now();\n\n // Index active, reserve, and bench players\n const activeList = rosterData.active || [];\n const reserveList = rosterData.reserve || [];\n const benchList = rosterData.bench || [];\n const currentOwnedMap = new Map();\n\n for (const p of activeList) currentOwnedMap.set(p.id, { player: p, slot: \"active\" });\n for (const p of reserveList) currentOwnedMap.set(p.id, { player: p, slot: \"reserve\" });\n for (const p of benchList) currentOwnedMap.set(p.id, { player: p, slot: \"bench\" });\n\n const dossiers = knowData.dossiers || [];\n let savedCount = 0;\n let clueCount = 0;\n\n for (const d of dossiers) {\n const p = d.player;\n if (!p) continue;\n\n const isCurrentOwned = currentOwnedMap.has(p.id);\n const isAlumni = (p.previousTeams || []).some((t: any) => t.managerId === myManagerId);\n\n if (!isCurrentOwned && !isAlumni && p.ownerId !== myManagerId) continue;\n\n const status = isCurrentOwned\n ? currentOwnedMap.get(p.id)!.slot\n : p.ownerId === myManagerId\n ? (p.rosterSlot || \"reserve\")\n : \"alumni\";\n\n await sqlite.execute(`\n INSERT INTO roster_players \n (player_id, name, role, growth_class, archetype, status, salary, first_seen_at, last_updated_at, player_json)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n ON CONFLICT(player_id) DO UPDATE SET\n status = excluded.status,\n salary = excluded.salary,\n last_updated_at = excluded.last_updated_at,\n player_json = excluded.player_json\n `, [\n p.id, p.name, p.role || \"\", p.growthClass || \"\", p.archetype || null,\n status, p.salary || 0, now, now, JSON.stringify(p)\n ]);\n savedCount++;\n\n for (const c of (d.clues || [])) {\n const occurredAtMs = c.occurredAt ? new Date(c.occurredAt).getTime() : now;\n const createdAtMs = c.createdAt ? new Date(c.createdAt).getTime() : now;\n const changes = c.changes || [];\n \n let gainSum = 0;\n for (const ch of changes) {\n const match = String(ch).match(/\\+(\\d+)/);\n if (match) gainSum += parseInt(match[1]);\n }\n\n await sqlite.execute(`\n INSERT OR IGNORE INTO roster_clues\n (clue_id, player_id, kind, activity_key, activity_label, text, changes_json, stat_gain_sum, occurred_at, created_at)\n VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)\n `, [\n c.id, p.id, c.kind || \"history\", c.activityKey || \"other\", c.activityLabel || \"Other\",\n c.text || \"\", JSON.stringify(changes), gainSum, occurredAtMs, createdAtMs\n ]);\n clueCount++;\n }\n }\n\n console.log(`Success: Saved ${savedCount} roster players & ${clueCount} history/lead clues.`);\n}",[206],"f5a7613f-a27f-4556-ab78-9a37cf407e76","collect.ts","f18cf222-9bd3-11f1-8fbb-1607ee4eb77e",80,["D",1787353789910],"interval","import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { initDb } from \"./db.ts\";\n\nconst API = \"https://apexmanager.rootsxo.studio/api/auctions\";\n\nexport default async function collect(_interval: Interval) {\n await initDb();\n\n // Ensure end_at column exists in listings table\n try {\n await sqlite.execute(`ALTER TABLE listings ADD COLUMN end_at INTEGER`);\n } catch (_e) {\n // Column already exists\n }\n\n const token = Deno.env.get(\"APEX_TOKEN\");\n if (!token) throw new Error(\"Missing APEX_TOKEN\");\n\n const resp = await fetch(API, { headers: { Authorization: `Bearer ${token}` } });\n if (!resp.ok) throw new Error(`API error: ${resp.status} ${resp.statusText}`);\n const data = await resp.json();\n const auctions = Array.isArray(data) ? data : (data.auctions ?? data.items ?? []);\n const now = Date.now();\n\n const activeAuctionIds: string[] = [];\n\n for (const a of auctions) {\n if (a.kind !== \"player\" || !a.player) continue;\n activeAuctionIds.push(a.id);\n const p = a.player;\n const intel = p.fogConfidence ?? 0;\n const fogPct = Math.round((1 - intel) * 100);\n const endAtMs = a.endAt ? new Date(a.endAt).getTime() : null;\n\n await sqlite.execute(\n `INSERT INTO listings\n (auction_id, player_id, name, role, age, growth_class, archetype,\n est_rank, est_potential, intel, fog_pct, salary, starting_bid, current_bid,\n first_seen_at, last_seen_at, end_at, left_at, player_json)\n VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NULL,?)\n ON CONFLICT(auction_id) DO UPDATE SET\n last_seen_at = excluded.last_seen_at,\n end_at = COALESCE(excluded.end_at, listings.end_at),\n current_bid = excluded.current_bid,\n left_at = NULL,\n player_json = excluded.player_json`,\n [\n a.id, p.id, p.name, p.role, p.age, p.growthClass, p.archetype ?? null,\n p.estimatedRank, p.estimatedPotential, intel, fogPct, p.salary ?? null,\n a.startingBid ?? null, a.currentBid ?? null,\n now, now, endAtMs, JSON.stringify(p),\n ],\n );\n }\n\n // If a listing wasn't in the active auctions response, mark it as left the market immediately\n if (activeAuctionIds.length > 0) {\n const placeholders = activeAuctionIds.map(() => \"?\").join(\",\");\n await sqlite.execute(\n `UPDATE listings SET left_at = ? WHERE left_at IS NULL AND auction_id NOT IN (${placeholders})`,\n [now, ...activeAuctionIds],\n );\n }\n\n const n = await sqlite.execute(`SELECT COUNT(*) AS c FROM listings`);\n const c = n.rows[0]?.c ?? 0;\n console.log(`Collect: ${auctions.length} auctions, ${c} tracked listings.`);\n}",[214],"1d03028a-9d90-413c-a890-1d9e163eabb4","check.ts","f1983bdc-9bd3-11f1-8fbb-1607ee4eb77e",74,["D",1787347872727],"import { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { initDb, isGem } from \"./db.ts\";\n\nconst API = \"https://apexmanager.rootsxo.studio/api/players/\";\nconst RE_CHECK_MS = 10 * 60 * 1000; // Retry unresolved/active players every 10 minutes\n\nasync function getCard(id: string): Promise {\n const token = Deno.env.get(\"APEX_TOKEN\");\n try {\n const r = await fetch(API + id, {\n headers: { Authorization: `Bearer ${token}` },\n });\n return r.ok ? await r.json() : null;\n } catch {\n return null;\n }\n}\n\nexport default async function check(_interval: Interval) {\n await initDb();\n const token = Deno.env.get(\"APEX_TOKEN\");\n if (!token) throw new Error(\"Missing APEX_TOKEN\");\n\n const now = Date.now();\n\n // Query ONLY players whose listings have definitively left the market (left_at IS NOT NULL)\n // regardless of whether their auction lasted 15 minutes, 6 hours, or 24 hours.\n const rows = await sqlite.execute(\n `SELECT l.player_id, l.name, MAX(l.left_at) AS left_market_at\n FROM listings l\n LEFT JOIN player_checks c ON c.player_id = l.player_id\n WHERE l.left_at IS NOT NULL\n AND (c.player_id IS NULL OR c.retired = 0)\n AND (c.player_id IS NULL OR c.last_checked_at < ?)\n GROUP BY l.player_id\n ORDER BY left_market_at ASC\n LIMIT 100`,\n [now - RE_CHECK_MS],\n );\n\n let checked = 0;\n let retiredFound = 0;\n\n for (const r of rows.rows as any[]) {\n const pid = r.player_id;\n const card = await getCard(pid);\n if (!card) continue;\n\n checked++;\n if (card.isRetired || card.trueRating != null) {\n // Retrieve earliest market display listing for this player\n const m = await sqlite.execute(\n `SELECT est_rank, est_potential, intel, fog_pct, salary, age\n FROM listings \n WHERE player_id = ? \n ORDER BY first_seen_at ASC \n LIMIT 1`,\n [pid],\n );\n const d = m.rows[0] as any;\n const gem = isGem(\n d?.est_potential ?? null,\n card.estimatedPotential ?? null,\n );\n\n await sqlite.execute(\n `INSERT INTO retired\n (player_id, name, market_est_rank, market_est_potential, market_intel,\n market_fog_pct, market_age, market_salary, true_rating, potential_rating, peak_rating,\n retired_rank, retired_potential, retired_at, checked_at, is_gem, card_url)\n VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)\n ON CONFLICT(player_id) DO UPDATE SET\n true_rating = excluded.true_rating,\n potential_rating = excluded.potential_rating,\n peak_rating = excluded.peak_rating,\n retired_rank = excluded.retired_rank,\n retired_potential = excluded.retired_potential,\n checked_at = excluded.checked_at,\n is_gem = excluded.is_gem,\n card_url = excluded.card_url`,\n [\n pid,\n card.name ?? r.name,\n d?.est_rank ?? null,\n d?.est_potential ?? null,\n d?.intel ?? null,\n d?.fog_pct ?? null,\n d?.age ?? null,\n d?.salary ?? null,\n card.trueRating ?? null,\n card.potentialRating ?? null,\n card.peakRating ?? null,\n card.estimatedRank ?? null,\n card.estimatedPotential ?? null,\n now,\n now,\n gem ? 1 : 0,\n `https://apexmanager.rootsxo.studio/player/${pid}`,\n ],\n );\n\n await sqlite.execute(\n `INSERT INTO player_checks (player_id, name, last_checked_at, retired)\n VALUES (?,?,?,1)\n ON CONFLICT(player_id) DO UPDATE SET retired = 1, last_checked_at = excluded.last_checked_at`,\n [pid, card.name ?? r.name, now],\n );\n retiredFound++;\n } else {\n await sqlite.execute(\n `INSERT INTO player_checks (player_id, name, last_checked_at, retired)\n VALUES (?,?,?,0)\n ON CONFLICT(player_id) DO UPDATE SET last_checked_at = excluded.last_checked_at`,\n [pid, card.name ?? r.name, now],\n );\n }\n }\n\n console.log(\n `Check: ${checked} cards checked, ${retiredFound} retired/resolved.`,\n );\n}",[222],"add6332f-fe51-4be6-a98f-addc6e40c5d6","AGENTS.md","85cb48e2-9bd6-11f1-8663-1607ee4eb77e",40,["D",1787246131200],"# apexGemTracker — context for future edits\n\nTracks Apex Manager market listings to find \"hidden gems\": players who displayed\nBELOW A- potential on the market but actually retired with A- or higher\npotential (i.e. fog hid their true quality). Separate from the alert vals — this\nis a data collection + comparison pipeline, no notifications.\n\n## ⚠️ CRITICAL: compare LETTER GRADES, never numeric ratings\n\nThe game's numeric `trueRating` / `potentialRating` do NOT map to a reliable\ngrade scale — do NOT infer a grade from a number. (Lesson learned: Parker has\n`potentialRating 1986` but retired as C/B; flagging him as A- from 1986 was\nWRONG.) The authoritative \"true\" grade is the **revealed letter grade on the\nretired card** (`estimatedRank` / `estimatedPotential` at fogConfidence=1).\n\n**Gem rule (in `db.ts` `isGem`):**\n`is_gem = isBelowAMinus(marketPotential) && !isBelowAMinus(retiredPotential)`\ni.e. market displayed potential < A- AND retired revealed potential >= A-.\n\n## fog semantics (same as the alert vals)\n\n`fogConfidence` (API) = INTEL. Displayed \"Fog %\" = 100 − fogConfidence·100. High\nintel (low fog) = well-scouted/revealed; low intel (high fog) =\nobscured/lottery.\n\n## Data model\n\n- `listings` — every market listing (keyed by auction_id), first-seen snapshot\n of est rank/potential/intel/fog/salary + full player_json. Upserted each\n collect run (no duplicates); `left_at` set when a listing stops appearing.\n- `retired` — resolved players: market display vs retired revealed grade\n (`retired_rank`/`retired_potential`) + market age/salary snapshot\n (`market_age`/`market_salary`) + numeric true/potential/peak ratings +\n `is_gem` + `card_url`.\n- `player_checks` — dedupe/backoff so we don't re-hit a player card too often.\n\n## Jobs\n\n- `collect.ts` (interval, */15): snapshot all `/api/auctions` listings.\n- `check.ts` (interval, hourly): for listings >=1h old, GET `/api/players/{id}`;\n if retired, record true ratings + market-vs-retired comparison; else re-check\n daily. (Unsigned players retire immediately, so the table fills within ~a\n day.)\n- `report.ts` (http, https://apex-gem-tracker.val.run/): JSON at `/`, CSV at\n `/csv`.\n- `main.ts`: init schema + status counts.\n\n## Env\n\n- APEX_TOKEN — Apex Manager API bearer token (required).\n\n## Spreadsheet exports\n\n`report.ts` also serves `/listings` and `/csv/listings` for the active market\n(not yet resolved), alongside the original `/` and `/csv` (aka `/csv/gems`) for\nresolved players — `/csv` now also includes `retired_at`. A Google Sheet pulls\nboth CSV endpoints live via `IMPORTDATA()` so the data is easy to read without\nhitting the raw endpoints directly.",[229],"pulls","branches","forks","envVars",{"_242":190,"_244":62},"connections",{"_242":62,"_243":62},"count","unfilledCount","emptyCount",{"_43":228,"_33":229,"_31":230,"_145":-5,"_41":42,"_40":231,"_147":27,"_38":291,"_13":202,"_34":20,"_36":233,"_24":27,"_151":-5,"_258":292,"_28":293},{"_43":221,"_33":222,"_31":223,"_145":-5,"_41":42,"_40":224,"_147":25,"_38":288,"_13":149,"_34":218,"_36":226,"_24":27,"_151":-5,"_258":289,"_28":290},{"_43":213,"_33":214,"_31":215,"_145":-5,"_41":42,"_40":216,"_147":25,"_38":285,"_13":167,"_34":218,"_36":219,"_24":27,"_151":-5,"_258":286,"_28":287},{"_43":205,"_33":206,"_31":207,"_145":-5,"_41":42,"_40":208,"_147":27,"_38":282,"_13":210,"_34":35,"_36":211,"_24":27,"_151":-5,"_258":283,"_28":284},{"_43":197,"_33":198,"_31":199,"_145":-5,"_41":42,"_40":200,"_147":27,"_38":279,"_13":202,"_34":35,"_36":203,"_24":27,"_151":-5,"_258":280,"_28":281},{"_43":193,"_33":29,"_31":32,"_145":-5,"_41":42,"_40":14,"_147":27,"_38":276,"_13":195,"_34":35,"_36":37,"_24":27,"_151":-5,"_258":277,"_28":278},{"_43":186,"_33":187,"_31":188,"_145":-5,"_41":42,"_40":174,"_147":25,"_38":273,"_13":190,"_34":35,"_36":191,"_24":27,"_151":-5,"_258":274,"_28":275},{"_43":178,"_33":179,"_31":180,"_145":-5,"_41":42,"_40":181,"_147":27,"_38":270,"_13":183,"_34":35,"_36":184,"_24":27,"_151":-5,"_258":271,"_28":272},{"_43":170,"_33":171,"_31":57,"_145":-5,"_41":42,"_40":172,"_147":25,"_38":267,"_13":174,"_34":159,"_36":175,"_24":27,"_151":176,"_258":268,"_28":269},{"_43":162,"_33":163,"_31":164,"_145":-5,"_41":42,"_40":165,"_147":25,"_38":264,"_13":167,"_34":35,"_36":168,"_24":27,"_151":-5,"_258":265,"_28":266},{"_43":153,"_33":154,"_31":155,"_145":-5,"_41":42,"_40":156,"_147":27,"_38":261,"_13":158,"_34":159,"_36":160,"_24":27,"_151":-5,"_258":262,"_28":263},{"_43":142,"_33":143,"_31":144,"_145":-5,"_41":42,"_40":146,"_147":25,"_38":257,"_13":149,"_34":35,"_36":150,"_24":27,"_151":-5,"_258":259,"_28":260},["D",1787347035385],"children",[],[143],["D",1787501289895],[],[154],["D",1787147868661],[],[163],["D",1787175228715],[],[171],["D",1787501041675],[],[179],["D",1787147006802],[],[187],["D",1787502237779],[],[29],["D",1787174487396],[],[198],["D",1787501463976],[],[206],["D",1787353789910],[],[214],["D",1787347872727],[],[222],["D",1787246131200],[],[229],["D",1787146151215],["D",1789491713377],{"_99":98,"_107":103},{"_99":98,"_100":101,"_102":103,"_18":55,"_104":105,"_106":27},{"_13":88,"_33":89,"_43":42,"_90":44,"_91":-5,"_92":-5,"_93":-5,"_94":-5,"_95":-5,"_96":45,"_97":98},"ctx",{"_81":-5,"_127":332,"_333":334,"_335":336,"_337":27,"_338":27,"_339":27,"_340":25,"_341":-5,"_342":-7,"_343":-7,"_344":62,"_345":27,"_346":27,"_347":-5,"_348":27,"_349":27,"_350":27,"_351":-5},"ENV",{"_303":304,"_305":306,"_307":89,"_308":309,"_310":311,"_312":313,"_314":315,"_316":317,"_318":319,"_320":321,"_322":323,"_324":325,"_326":327,"_328":329,"_330":331},"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"]

Diff · v100