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

> Discover how the stats-tracker Deno Edge Function aggregates total cards and user counts from PostgreSQL, persisting them as a public JSON for fast frontend access in TCG Pocket Collection Tracker.

- Repository: [Marcel Panse/tcg-pocket-collection-tracker](https://github.com/marcelpanse/tcg-pocket-collection-tracker)
- Tags: how-to-guide
- Published: 2026-03-06

---

**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](https://github.com/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`](https://github.com/marcelpanse/tcg-pocket-collection-tracker/blob/main/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.

```typescript
// 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.

```typescript
// 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.

```typescript
// 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.

```typescript
// 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`](https://github.com/marcelpanse/tcg-pocket-collection-tracker/blob/main/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.

```typescript
// 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.

```typescript
// 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.

```javascript
// 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`](https://github.com/marcelpanse/tcg-pocket-collection-tracker/blob/main/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`](https://github.com/marcelpanse/tcg-pocket-collection-tracker/blob/main/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.