Annual Updates - TamborraData

TamborraData features an automatic annual update system that detects when new data is being processed and shows a real-time updating banner to users. This happens every January without requiring downtime or manual intervention.

Overview

The isUpdating system provides:

Zero Downtime

Website stays online during data updates

Real-time Detection

Automatic polling detects when updates finish

User Feedback

Clear banner shows update status

Seamless Transition

Smooth transition when new data is ready

Architecture

System Flow

Components

Database
Backend
Frontend
Private Pipeline

sys_status TableSingle-row table that stores the update status:

CREATE TABLE sys_status (
  id integer PRIMARY KEY DEFAULT 1,
  is_updating boolean DEFAULT FALSE NOT NULL,
  updated_at timestamptz DEFAULT now() NOT NULL,
  notes text
);

This is a singleton table - it only ever contains one row with id = 1.

getSysStatus() FunctionChecks the sys_status table and returns the current state:

export async function getSysStatus(): Promise<boolean | null> {
  const isDev = process.env.NODE_ENV === 'development';
  const now = new Date();
  const month = now.getMonth(); // 0 = January
  const day = now.getDate();

  // Only check during January or February ≤ 20
  if (isDev || month === 0 || (month === 1 && day <= 20)) {
    const { data, error } = await supabaseClient
      .from('sys_status')
      .select('is_updating')
      .eq('id', 1)
      .single();

    if (error || !data) return false;
    return data.is_updating as boolean;
  }

  // Outside January/February → always false
  return false;
}

Date optimization: The function only queries the database during January and early February. The rest of the year, it returns false without a database call.

React Query PollingThe frontend uses conditional polling to detect changes:

export function useStatisticsQuery<T>(year: string) {
  return useQuery({
    queryKey: queryKeys.statistics(year),
    queryFn: ({ signal }) => fetchStatistics<T>(year, signal),
    
    // Infinite cache for historical data
    staleTime: Infinity,
    gcTime: Infinity,
    
    // Conditional polling
    refetchInterval: (query) => {
      const isUpdating = query.state.data?.isUpdating;
      return isUpdating ? 3000 : false;  // Poll every 3s if updating
    },
    
    // Refetch on window focus if updating
    refetchOnWindowFocus: (query) => {
      return query.state.data?.isUpdating === true;
    }
  });
}

Automated Pipeline (Private Repository)The private pipeline runs during January:

def update_tamborrada_data():
    # 1. Activate update mode
    supabase.table('sys_status').update({
        'is_updating': True,
        'updated_at': datetime.now()
    }).eq('id', 1).execute()

    try:
        # 2. Scrape official lists
        participants = scrape_official_lists()

        # 3. Clean and validate data
        clean_data = validate_and_clean(participants)

        # 4. Generate statistics
        statistics = generate_statistics(clean_data)

        # 5. Insert into database
        supabase.table('participants').insert(clean_data).execute()
        supabase.table('statistics').insert(statistics).execute()
        supabase.table('available_years').insert({
            'year': '2025',
            'is_ready': True
        }).execute()

        # 6. Deactivate update mode
        supabase.table('sys_status').update({
            'is_updating': False,
            'updated_at': datetime.now()
        }).eq('id', 1).execute()
        
    except Exception as e:
        # Rollback on error
        supabase.table('sys_status').update({
            'is_updating': False,
            'notes': f'Error: {str(e)}'
        }).eq('id', 1).execute()
        raise

The private pipeline repository is not public because it processes sensitive information about minors (full names, schools).

User Experience

When isUpdating = true, users see an updating page:

app/(frontend)/statistics/components/UpdatingPage.tsx

'use client';
import { ExclamationIcon } from '@/app/(frontend)/icons/icons';

export function UpdatingPage() {
  return (
    <div className="w-full h-screen flex flex-col items-center justify-center gap-4">
      <ExclamationIcon />
      <h4 className="text-base md:text-xl font-bold text-center">
        La página se está actualizando...
      </h4>
      <p className="text-sm text-center text-(--color-text-secondary)">
        Visita la página dentro de un rato para ver las estadísticas actualizadas.
      </p>
    </div>
  );
}

File location: app/(frontend)/statistics/components/UpdatingPage.tsx:5

Conditional Rendering

Components check the isUpdating status:

export function StatisticsContent({ year }) {
  const { data, isLoading } = useStatisticsQuery(year);

  if (isLoading) return <LoadingPage />;
  
  if (data?.isUpdating) return <UpdatingPage />;
  
  return <StatisticsTable data={data.statistics} />;
}

Complete Flow Example

Scenario: User visits during update

User navigates to /statistics/2025
Backend checks sys_status table → is_updating = true
API returns { isUpdating: true }
Frontend receives the status via React Query
UI shows updating banner
Frontend activates polling (every 3 seconds)
3 seconds later, frontend queries again
Backend checks sys_status → still true
API returns { isUpdating: true } again
User continues seeing updating banner
Meanwhile, private pipeline finishes processing
Pipeline updates sys_status → is_updating = false
Next poll (3s), frontend queries again
Backend checks sys_status → now false
API returns full statistics
React Query updates cache with new data
UI transitions to show new statistics
Polling stops automatically

Users don’t need to refresh the page - the transition happens automatically when data is ready.

API Response Format

During Update

{
  "isUpdating": true
}

Normal Operation

{
  "isUpdating": false,
  "year": "2024",
  "statistics": {
    "intro": [...],
    "top_names": [...],
    "top_surnames": [...],
    "top_schools": [...],
    // ... more categories
  }
}

Backend Implementation

API Route

app/(backend)/api/statistics/route.ts

import { NextResponse } from 'next/server';
import { getSysStatus } from '@/app/(backend)/shared/utils/getSysStatus';
import { getStatistics } from './services/statistics.service';

export async function GET(req: Request) {
  const year = new URL(req.url).searchParams.get('year');

  // Check system status
  const isUpdating = await getSysStatus();

  // If updating, return minimal response
  if (isUpdating) {
    return NextResponse.json({ isUpdating: true }, { status: 200 });
  }

  // Normal case: return full statistics
  const statistics = await getStatistics(year);
  return NextResponse.json({
    isUpdating: false,
    year,
    statistics
  });
}

Date Optimization

The system only checks the database during update season:

Date Range	Behavior
January 1-31	Query database on every request
February 1-20	Query database on every request
Rest of year	Return `false` without DB query

Rationale: Updates only happen in January. No need to query the database the rest of the year.

if (isDev || month === 0 || (month === 1 && day <= 20)) {
  // Check database
} else {
  // Skip database query
  return false;
}

This optimization saves ~10,000 database reads per month outside the update window.

Polling Behavior

When Polling Activates

refetchInterval: (query) => {
  const isUpdating = query.state.data?.isUpdating;
  return isUpdating ? 3000 : false;
}

State	Action
`isUpdating = false`	No polling (infinite cache)
`isUpdating = true`	Poll every 3 seconds
User changes tab	If updating, refetch on return
User navigates away	Stop polling automatically

Window Focus Refetching

refetchOnWindowFocus: (query) => {
  return query.state.data?.isUpdating === true;
}

If the user switches tabs during an update:

Polling continues in background (limited by browser)
When user returns, React Query immediately refetches
If update finished, user sees new data right away

Error Handling

Pipeline Failure

If the pipeline encounters an error:

try:
    update_statistics()
except Exception as e:
    # Rollback: deactivate isUpdating
    supabase.table('sys_status').update({
        'is_updating': False,
        'notes': f'Error: {str(e)}'
    }).eq('id', 1).execute()
    raise

Result: isUpdating returns to false, and the frontend shows data from the previous year.

Database Connection Issues

export async function getSysStatus(): Promise<boolean | null> {
  try {
    const { data, error } = await supabaseClient
      .from('sys_status')
      .select('is_updating')
      .eq('id', 1)
      .single();

    if (error || !data) return false;
    return data.is_updating as boolean;
  } catch (error) {
    console.error('Error fetching sys_status:', error);
    return false;  // Fail open - show content
  }
}

Fail-open strategy: If the database is unreachable, assume isUpdating = false and show existing data.

Performance Considerations

Supabase Reads

Scenario	Database Reads per Month
January (updating)	~10,000 reads
February 1-20	~3,000 reads
Rest of year	0 reads (optimized)

React Query Deduplication

If multiple tabs are open:

Each tab has its own React Query instance
All tabs poll independently
React Query deduplicates requests → only 1 HTTP call

// Tab 1: useStatisticsQuery('2025')
// Tab 2: useStatisticsQuery('2025')
// Tab 3: useStatisticsQuery('2025')
// → Only 1 actual HTTP request to /api/statistics

Row-Level Security

The sys_status table has read-only access for anonymous users:

-- Frontend can only read, not write
CREATE POLICY "Anon read access on sys_status"
ON sys_status
FOR SELECT
TO anon
USING (true);

Only the private pipeline (with service role credentials) can update is_updating.

Testing Locally

To test the updating flow in development:

# 1. Connect to your Supabase database
psql postgres://your-db-url

# 2. Set isUpdating to true
UPDATE sys_status SET is_updating = true WHERE id = 1;

# 3. Visit http://localhost:3000/statistics/2024
# You should see the updating banner

# 4. After testing, reset to false
UPDATE sys_status SET is_updating = false WHERE id = 1;

Why Not WebSockets?

Alternatives considered:

Approach	Pros	Cons	Decision
WebSockets	Real-time, no polling	Complex, expensive, overkill	❌ Not used
Server-Sent Events	Simpler than WebSockets	Not supported on Vercel Edge	❌ Not used
Polling (current)	Simple, reliable, cheap	Slight delay (3s)	✅ Used
Manual refresh	No overhead	Poor UX	❌ Not used

Polling is sufficient because updates only happen once per year and the 3-second delay is acceptable.

React Query

Polling and caching configuration

Statistics Explorer

How users browse statistics

Architecture

System architecture overview

Get Started

Core Features

Architecture

Development

Advanced Topics

​Overview

Zero Downtime

Real-time Detection

User Feedback

Seamless Transition

​Architecture

​System Flow

​Components

​User Experience

​Updating Banner

​Conditional Rendering

​Complete Flow Example

​Scenario: User visits during update

​API Response Format

​During Update

​Normal Operation

​Backend Implementation

​API Route

​Date Optimization

​Polling Behavior

​When Polling Activates

​Window Focus Refetching

​Error Handling

​Pipeline Failure

​Database Connection Issues

​Performance Considerations

​Supabase Reads

​React Query Deduplication

​Row-Level Security

​Testing Locally

​Why Not WebSockets?

​Related Documentation

React Query

Statistics Explorer

Architecture

Build docs developers (and LLMs) love

Overview

Architecture

System Flow

Components

User Experience

Updating Banner

Conditional Rendering

Complete Flow Example

Scenario: User visits during update

API Response Format

During Update

Normal Operation

Backend Implementation

API Route

Date Optimization

Polling Behavior

When Polling Activates

Window Focus Refetching

Error Handling

Pipeline Failure

Database Connection Issues

Performance Considerations

Supabase Reads

React Query Deduplication

Row-Level Security

Testing Locally

Why Not WebSockets?

Related Documentation