Counter Town

\n Beta\n

Medium-scale web analytics powered by Val Town

\n Simple and privacy-focused for personal blogs and community sites\n

\n \n Get Started\n \n \n Browse the demo\n \n

\n\n

Powered by Val Town

\n Counter.town runs entirely on Val Town's infrastructure, utilizing Val Town SQLite (Turso) for data\n storage.\n

Perfect for Small to Medium Sites

Ideal for personal blogs and community websites. Easy upgrade paths available as your needs grow.

Privacy-Focused

No cookies set. You have full control over your data. Retention policies customizable.

Open Source

\n Counter.town is{\" \"}\n \n open source on Val Town\n . Contribute, customize, and make it your own.\n

\n \n ,\n );\n});\n\n// zipped execute\nasync function execute(sql) {\n const results = await sqlite.execute(sql);\n return results.rows.map(row =>\n Object.fromEntries(results.columns.map(\n (column, i) => [column, row[i]],\n ))\n );\n}\n\napp.get(\"/dashboard\", async (c) => {\n const timeRange = \"7 days\";\n\n const stats = await execute(`\n SELECT \n COUNT(*) as views,\n COUNT(DISTINCT url) as visits,\n COUNT(DISTINCT user_agent) as visitors\n FROM counter_town\n WHERE time > datetime('now', '-${timeRange}')\n `);\n\n // Chart data\n const chartData = await execute(`\n SELECT \n date(time) as date,\n COUNT(*) as value\n FROM counter_town\n WHERE time > datetime('now', '-${timeRange}')\n GROUP BY date(time)\n ORDER BY date(time)\n `);\n\n // Top paths\n const topPaths = await execute(`\n WITH parsed_urls AS (\n SELECT \n CASE \n WHEN instr(url, '://') > 0 THEN \n CASE\n WHEN instr(substr(url, instr(url, '://') + 3), '/') > 0 THEN\n '/' || substr(substr(url, instr(url, '://') + 3), instr(substr(url, instr(url, '://') + 3), '/'))\n ELSE '/'\n END\n ELSE\n CASE\n WHEN instr(url, '/') > 0 THEN substr(url, instr(url, '/'))\n ELSE '/'\n END\n END AS path,\n user_agent,\n time\n FROM counter_town\n )\n SELECT \n path,\n COUNT(DISTINCT user_agent) as visitors,\n COUNT(*) as views\n FROM parsed_urls\n WHERE time > datetime('now', '-${timeRange}')\n GROUP BY path\n ORDER BY visitors DESC\n LIMIT 10\n `);\n\n // Top referrers\n const topReferrers = await execute(`\n SELECT \n COALESCE(referrer, '(none)') as referrer,\n COUNT(DISTINCT user_agent) as visitors\n FROM counter_town\n WHERE time > datetime('now', '-${timeRange}')\n GROUP BY referrer\n ORDER BY visitors DESC\n LIMIT 10\n `);\n\n // Top browsers\n const topBrowsers = await execute(`\n SELECT \n COALESCE(browser, '(unknown)') as browser,\n COUNT(DISTINCT user_agent) as visitors\n FROM counter_town\n WHERE time > datetime('now', '-${timeRange}')\n GROUP BY browser\n ORDER BY visitors DESC\n LIMIT 10\n `);\n\n // Top countries\n const topCountries = await execute(`\n SELECT \n COALESCE(country, '(unknown)') as country,\n COUNT(DISTINCT user_agent) as visitors\n FROM counter_town\n WHERE time > datetime('now', '-${timeRange}')\n GROUP BY country\n ORDER BY visitors DESC\n LIMIT 10\n `);\n\n // Top devices\n const topDevices = await execute(`\n SELECT \n COALESCE(device, '(unknown)') as device,\n COUNT(DISTINCT user_agent) as visitors\n FROM counter_town\n WHERE time > datetime('now', '-${timeRange}')\n GROUP BY device\n ORDER BY visitors DESC\n LIMIT 10\n `);\n\n return c.html(\n \n \n \n \n Counter Town\n \n \n \n \n

\n \n

Counter Town

\n \n

\n\n

\n \n

\n \n 📅\n \n

\n\n

Views

{stats.views}

Visits

{stats.visits}

Visitors

{stats.visitors}

\n\n

\n {/* We'll need to implement a server-side chart solution or use a simple HTML table for now */}\n \n \n \n \n \n \n \n \n {chartData.map((data) => (\n \n \n \n \n ))}\n \n

Date	Value
{data.date}	{data.value}

\n\n

Top Paths

\n \n \n \n \n \n \n \n \n \n {topPaths.map((path) => (\n \n \n \n \n \n ))}\n \n

Path	Visitors	Views
{path.path.slice(1)}	{path.visitors}	{path.views}

\n \n ,\n );\n});","deleted","label",[99],"permalink","https://www.val.town/x/stevekrouse/counterTown/code/main.tsx","extension","actionData","errors"]