How the stats-tracker Deno Edge Function Calculates Collection Summary Insights in TCG Pocket Collection Tracker

The stats-tracker Deno Edge Function aggregates total cards owned and registered user counts from PostgreSQL, then persists the results as a public JSON file in Supabase Storage for fast frontend consumption.

The stats-tracker function in the marcelpanse/tcg-pocket-collection-tracker repository is a Supabase Edge Function written in Deno. It periodically gathers high-level statistics about the TCG Pocket Collection Tracker and stores them in Supabase Storage, allowing the frontend to display real-time collection insights without querying the database directly.

Core Aggregation Workflow

The function follows a strict pipeline: establish database connectivity, execute aggregation queries, and format results for public consumption.

Database Connection Pooling

In supabase/functions/stats-tracker/index.ts, the function initializes a PostgreSQL connection pool using environment variables. It reads SUPABASE_DB_URL and creates a pool with three lazy connections to minimize connection churn and improve latency for repeated invocations.

// From supabase/functions/stats-tracker/index.ts
const pool = new postgres.Pool(Deno.env.get("SUPABASE_DB_URL")!, 3, true);

Querying Collection Totals

The function acquires a connection from the pool and executes a SQL aggregation to sum the amount_owned column across every user's collection. This calculates the total number of physical cards tracked in the system.

// Aggregation query executed by the function
const { rows: collectionRows } = await connection.queryObject<{ count: number }>
  `SELECT SUM(amount_owned) as count FROM collection;`;

The result is formatted with toLocaleString() for readability before being packaged into the final JSON payload.

Counting Registered Users

Similarly, the function queries the auth.users table to determine the total registered user base. This metric provides context for average collection sizes and platform growth.

// User count query
const { rows: userRows } = await connection.queryObject<{ count: number }>
  `SELECT count(*) as count FROM auth.users;`;

Data Persistence Strategy

After aggregating the statistics, the function persists the data to Supabase Storage rather than returning it directly, creating a cached public endpoint that frontend applications can poll without database load.

Service-Role Client Initialization

The function initializes a Supabase client using the SUPABASE_SERVICE_ROLE_KEY, which bypasses Row Level Security (RLS) policies and grants privileged access to write directly to the stats storage bucket.

// Service-role client initialization
const supabase = createClient(
  Deno.env.get("SUPABASE_URL")!,
  Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!
);

Storage Upload with Cache Control

The function packages the aggregated data into a JSON object containing collectionCount and usersCount, then uploads it to stats/stats.json with specific cache headers. The cacheControl: '60' directive instructs browsers and CDNs to cache the file for 60 seconds, balancing freshness with reduced storage write operations.

// Storage upload with cache control
const body = JSON.stringify({ collectionCount, usersCount }, null, 2);
const { error } = await supabase.storage.from('stats').update('stats.json', body, {
  cacheControl: '60',
  upsert: true,
  contentType: 'application/json'
});

Error Handling and Resource Management

The function implements robust error handling and connection management to prevent resource leaks in the edge runtime environment.

The database connection is acquired inside Deno.serve and explicitly released back to the pool in a finally block, ensuring cleanup occurs even if queries throw exceptions. Any uncaught errors return a generic HTTP 500 response to the caller while logging the specific error details for debugging.

// Resource management pattern
try {
  const connection = await pool.connect();
  try {
    // ... query execution ...
  } finally {
    connection.release();
  }
} catch (error) {
  console.error(error);
  return new Response(JSON.stringify({ error: 'Internal Server Error' }), {
    status: 500,
    headers: { 'Content-Type': 'application/json' }
  });
}

Frontend Consumption Pattern

Once persisted, the statistics become publicly accessible via Supabase Storage's CDN. The frontend fetches the static JSON file directly, eliminating the need for complex database queries or authentication for public statistics.

// Frontend consumption example
async function loadStats() {
  const res = await fetch(
    'https://<project>.supabase.co/storage/v1/object/public/stats/stats.json'
  );
  if (!res.ok) throw new Error('Failed to load stats');
  const { collectionCount, usersCount } = await res.json();
  console.log(`Total cards owned: ${collectionCount}`);
  console.log(`Registered users: ${usersCount}`);
}

Summary

• The stats-tracker Deno Edge Function aggregates collection statistics by querying the collection and auth.users tables in PostgreSQL.
• It calculates total cards owned via SUM(amount_owned) and registered users via COUNT(*).
• Results are persisted to Supabase Storage at stats/stats.json with a 60-second cache control header.
• The function uses a connection pool of size 3 and implements strict resource cleanup via finally blocks.
• Frontend applications consume the static JSON directly from the Storage CDN without database authentication.

Frequently Asked Questions

How often does the stats-tracker function run?

The function executes based on a scheduled trigger or manual invocation configured in Supabase. While the code itself does not specify a cron schedule, the 60-second cacheControl header suggests the intended refresh interval aligns with frequent updates, typically invoked via Supabase's cron extension or external scheduling.

Why does the function use a service-role key instead of the anon key?

The service-role key bypasses Row Level Security (RLS) policies, granting the function privileged write access to the stats storage bucket. The anon key would be restricted by RLS rules and cannot write to public storage buckets without explicit policies, making the service-role key necessary for automated backend operations.

What happens if the database connection fails during execution?

If the PostgreSQL connection fails, the function catches the exception in the outer try-catch block, logs the error to the console, and returns an HTTP 500 response with a JSON error payload. The connection pool automatically handles reconnection on subsequent invocations, and the finally block ensures any acquired connection is released back to the pool even if queries fail.

Can the frontend modify the stats.json file directly?

No, the frontend cannot modify stats.json directly. While the file is publicly readable via the Storage CDN, write operations require the service-role key possessed only by the Edge Function. This architecture ensures data integrity by preventing client-side manipulation of aggregated statistics.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}