Supabase Integration - Ayuntamiento de Zongolica

Overview

The platform uses Supabase as the backend-as-a-service, providing:

PostgreSQL Database with Row-Level Security (RLS)
Authentication via OAuth providers (Google, Facebook) and email/password
Real-time Subscriptions for live data updates
Storage for user-generated content

All database interactions are centralized in src/lib/supabase.ts.

Client Setup

Configuration

// src/lib/supabase.ts
import { createClient } from '@supabase/supabase-js';

const supabaseUrl = import.meta.env.PUBLIC_SUPABASE_URL || '';
const supabaseAnonKey = import.meta.env.PUBLIC_SUPABASE_ANON_KEY || '';

export const supabase = createClient(supabaseUrl, supabaseAnonKey);

Environment Variables

# .env
PUBLIC_SUPABASE_URL=https://xxx.supabase.co
PUBLIC_SUPABASE_ANON_KEY=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

PUBLIC_ prefix means these variables are exposed to the client. Never use service role keys with this prefix.

Database Schema

`user_profiles`

Stores user information and authentication metadata.

Column	Type	Description
`id`	`uuid`	Primary key (matches auth.users.id)
`email`	`text`	User email address
`full_name`	`text`	Display name
`avatar_url`	`text`	Profile picture URL
`provider`	`text`	OAuth provider (google, facebook, email)
`onboarding_completed`	`boolean`	Whether user completed tourism onboarding
`created_at`	`timestamptz`	Account creation time
`updated_at`	`timestamptz`	Last profile update

TypeScript Type:

export interface UserProfile {
  id: string;
  email: string;
  full_name: string;
  avatar_url: string;
  provider: string;
  onboarding_completed: boolean;
  created_at: string;
  updated_at: string;
}

`user_preferences`

Stores tourism preferences collected during onboarding.

Column	Type	Description
`id`	`uuid`	Primary key
`user_id`	`uuid`	Foreign key to user_profiles
`experiencia`	`text[]`	Preferred experiences (aventura, cultura, naturaleza…)
`duracion`	`text`	Trip duration preference (medio-dia, dia-completo…)
`dificultad`	`text`	Difficulty level (facil, moderado, dificil, extremo)
`grupo`	`text`	Travel group type (solo, pareja, familia, amigos)
`intereses`	`text[]`	Specific interests (cascadas, miradores, artesanias…)
`created_at`	`timestamptz`	Preference creation time

TypeScript Type:

export interface UserPreferences {
  id?: string;
  user_id: string;
  experiencia: string[];  // ['aventura', 'naturaleza']
  duracion: string;       // 'dia-completo'
  dificultad: string;     // 'moderado'
  grupo: string;          // 'familia'
  intereses: string[];    // ['cascadas', 'miradores']
  created_at?: string;
}

`user_routes`

Stores generated personalized tourism routes.

Column	Type	Description
`id`	`uuid`	Primary key
`user_id`	`uuid`	Foreign key to user_profiles
`user_name`	`text`	User’s display name (denormalized)
`route_name`	`text`	Generated route name
`atractivos`	`text[]`	Array of attraction IDs/slugs
`ticket_url`	`text`	URL to generated ticket image
`share_code`	`text`	Unique share code (nanoid)
`badges`	`text[]`	Earned badge IDs
`created_at`	`timestamptz`	Route creation time

TypeScript Type:

export interface UserRoute {
  id: string;
  user_id: string;
  user_name?: string;
  route_name: string;
  atractivos: string[];    // ['cascada-texpico', 'mirador-zongolica']
  ticket_url: string;      // '/ruta/xyz123'
  share_code: string;      // 'xyz123'
  badges: string[];        // ['aventurero', 'explorador']
  created_at: string;
}

`user_badges`

Tracks unlocked achievements and badges.

Column	Type	Description
`id`	`uuid`	Primary key
`user_id`	`uuid`	Foreign key to user_profiles
`badge_type`	`text`	Badge identifier (aventurero, explorador…)
`unlocked_at`	`timestamptz`	When badge was earned

TypeScript Type:

export interface UserBadge {
  id: string;
  user_id: string;
  badge_type: string;  // 'aventurero'
  unlocked_at: string;
}

`user_favorites`

Stores user’s saved attractions.

Column	Type	Description
`id`	`uuid`	Primary key
`user_id`	`uuid`	Foreign key to user_profiles
`atractivo_slug`	`text`	Attraction slug
`created_at`	`timestamptz`	When favorited

TypeScript Type:

export interface UserFavorite {
  id: string;
  user_id: string;
  atractivo_slug: string;  // 'cascada-texpico'
  created_at: string;
}

Authentication Functions

Get Current User

export async function getCurrentUser() {
  const { data: { user } } = await supabase.auth.getUser();
  return user;
}

Usage:

const user = await getCurrentUser();
if (!user) {
  // Redirect to login
}

Email/Password Authentication

// Sign in
export async function signInWithEmail(email: string, password: string) {
  const { data, error } = await supabase.auth.signInWithPassword({
    email,
    password
  });
  return { data, error };
}

// Sign up
export async function signUpWithEmail(
  email: string,
  password: string,
  name?: string
) {
  const { data, error } = await supabase.auth.signUp({
    email,
    password,
    options: {
      data: {
        name: name || email.split('@')[0]
      }
    }
  });
  return { data, error };
}

OAuth with Google

export async function signInWithGoogle(
  redirectPath = '/turismo?onboarding=1'
) {
  const { data, error } = await supabase.auth.signInWithOAuth({
    provider: 'google',
    options: {
      redirectTo: `${window.location.origin}/auth/callback?return_url=${encodeURIComponent(redirectPath)}`
    }
  });
  return { data, error };
}

Flow:

User clicks “Sign in with Google”
Redirects to Google OAuth
User authorizes
Redirects to /auth/callback
Callback page sets session
Redirects to return_url

See Authentication for full implementation.

Sign Out

export async function signOut() {
  const { error } = await supabase.auth.signOut();
  return { error };
}

Profile Functions

Get User Profile

export async function getUserProfile(userId: string) {
  const { data, error } = await supabase
    .from('user_profiles')
    .select('*')
    .eq('id', userId)
    .single();
  return { data: data as UserProfile | null, error };
}

Update Profile

export async function upsertUserProfile(
  profile: Partial<UserProfile> & { id: string }
) {
  const { data, error } = await supabase
    .from('user_profiles')
    .upsert([{ ...profile, updated_at: new Date().toISOString() }])
    .select()
    .single();
  return { data, error };
}

Mark Onboarding Complete

export async function markOnboardingCompleted(userId: string) {
  const { data, error } = await supabase
    .from('user_profiles')
    .update({
      onboarding_completed: true,
      updated_at: new Date().toISOString()
    })
    .eq('id', userId)
    .select()
    .single();
  return { data, error };
}

Check Onboarding Status

export async function isOnboardingCompleted(userId: string): Promise<boolean> {
  const { data } = await supabase
    .from('user_profiles')
    .select('onboarding_completed')
    .eq('id', userId)
    .single();
  return data?.onboarding_completed ?? false;
}

Get Full Profile

export async function getFullProfile(userId: string) {
  const [profileRes, prefsRes, badgesRes, routesRes, favoritesRes] = await Promise.all([
    getUserProfile(userId),
    getUserPreferences(userId),
    getUserBadges(userId),
    getUserRoutes(userId),
    getUserFavorites(userId),
  ]);
  return {
    profile: profileRes.data,
    preferences: prefsRes.data,
    badges: badgesRes.data ?? [],
    routes: routesRes.data ?? [],
    favorites: favoritesRes.data ?? [],
  };
}

Preferences Functions

Save Preferences

export async function saveUserPreferences(preferences: UserPreferences) {
  const { data, error } = await supabase
    .from('user_preferences')
    .upsert([preferences], { onConflict: 'user_id' })  // Update if exists
    .select()
    .single();
  return { data, error };
}

Get Preferences

export async function getUserPreferences(userId: string) {
  const { data, error } = await supabase
    .from('user_preferences')
    .select('*')
    .eq('user_id', userId)
    .order('created_at', { ascending: false })
    .limit(1)
    .single();
  return { data, error };
}

Usage Example:

const user = await getCurrentUser();
const { data: prefs } = await getUserPreferences(user.id);

if (prefs?.experiencia.includes('aventura')) {
  // Show adventure content
}

Route Functions

Save Route

export async function saveUserRoute(
  route: Omit<UserRoute, 'id' | 'created_at'>
) {
  const { data, error } = await supabase
    .from('user_routes')
    .insert([route])
    .select()
    .single();
  return { data, error };
}

Usage:

import { nanoid } from 'nanoid';
import { generateRouteName } from '@/lib/recommendations';

const shareCode = nanoid(10);
const routeName = generateRouteName(preferences);

await saveUserRoute({
  user_id: user.id,
  user_name: user.full_name,
  route_name: routeName,
  atractivos: ['cascada-texpico', 'mirador-zongolica'],
  ticket_url: `/ruta/${shareCode}`,
  share_code: shareCode,
  badges: ['aventurero']
});

export async function getUserRoute(shareCode: string) {
  const { data, error } = await supabase
    .from('user_routes')
    .select('*, user:user_id(*)')  // Join with user profile
    .eq('share_code', shareCode)
    .single();
  return { data, error };
}

Get All User Routes

export async function getUserRoutes(userId: string) {
  const { data, error } = await supabase
    .from('user_routes')
    .select('*')
    .eq('user_id', userId)
    .order('created_at', { ascending: false });
  return { data, error };
}

Get Latest Route

export async function getLatestUserRoute(userId: string) {
  const { data, error } = await supabase
    .from('user_routes')
    .select('*')
    .eq('user_id', userId)
    .order('created_at', { ascending: false })
    .limit(1)
    .single();
  return data;
}

Badge Functions

Unlock Badge

export async function unlockBadge(userId: string, badgeType: string) {
  const { data, error } = await supabase
    .from('user_badges')
    .insert([{ user_id: userId, badge_type: badgeType }])
    .select()
    .single();
  return { data, error };
}

Get User Badges

export async function getUserBadges(userId: string) {
  const { data, error } = await supabase
    .from('user_badges')
    .select('*')
    .eq('user_id', userId)
    .order('unlocked_at', { ascending: false });
  return { data, error };
}

Check Badge Ownership

export async function hasBadge(
  userId: string,
  badgeType: string
): Promise<boolean> {
  const { data } = await supabase
    .from('user_badges')
    .select('id')
    .eq('user_id', userId)
    .eq('badge_type', badgeType)
    .single();
  return !!data;
}

Usage:

if (await hasBadge(user.id, 'aventurero')) {
  // Show special content
}

Favorites Functions

Add Favorite

export async function addFavorite(userId: string, atractivoSlug: string) {
  const { data, error } = await supabase
    .from('user_favorites')
    .insert([{ user_id: userId, atractivo_slug: atractivoSlug }])
    .select()
    .single();
  return { data, error };
}

Remove Favorite

export async function removeFavorite(userId: string, atractivoSlug: string) {
  const { error } = await supabase
    .from('user_favorites')
    .delete()
    .eq('user_id', userId)
    .eq('atractivo_slug', atractivoSlug);
  return { error };
}

Toggle Favorite

export async function toggleFavorite(
  userId: string,
  atractivoSlug: string
): Promise<boolean> {
  const isFav = await isFavorite(userId, atractivoSlug);
  if (isFav) {
    await removeFavorite(userId, atractivoSlug);
    return false;
  } else {
    await addFavorite(userId, atractivoSlug);
    return true;
  }
}

Check Favorite Status

export async function isFavorite(
  userId: string,
  atractivoSlug: string
): Promise<boolean> {
  const { data } = await supabase
    .from('user_favorites')
    .select('id')
    .eq('user_id', userId)
    .eq('atractivo_slug', atractivoSlug)
    .single();
  return !!data;
}

Get All Favorites

export async function getUserFavorites(userId: string) {
  const { data, error } = await supabase
    .from('user_favorites')
    .select('*')
    .eq('user_id', userId)
    .order('created_at', { ascending: false });
  return { data: data as UserFavorite[] | null, error };
}

Get Favorites Count

export async function getFavoritesCount(userId: string): Promise<number> {
  const { count } = await supabase
    .from('user_favorites')
    .select('*', { count: 'exact', head: true })
    .eq('user_id', userId);
  return count ?? 0;
}

Real-time Subscriptions

Listen to Auth Changes

const { data: { subscription } } = supabase.auth.onAuthStateChange(
  (event, session) => {
    if (event === 'SIGNED_IN') {
      console.log('User signed in:', session.user);
    }
    if (event === 'SIGNED_OUT') {
      console.log('User signed out');
    }
  }
);

// Cleanup
subscription.unsubscribe();

Listen to Database Changes

const channel = supabase
  .channel('user_routes')
  .on(
    'postgres_changes',
    {
      event: 'INSERT',
      schema: 'public',
      table: 'user_routes',
      filter: `user_id=eq.${userId}`
    },
    (payload) => {
      console.log('New route created:', payload.new);
    }
  )
  .subscribe();

// Cleanup
channel.unsubscribe();

Example: Complete Onboarding Flow

import {
  getCurrentUser,
  saveUserPreferences,
  saveUserRoute,
  unlockBadge,
  markOnboardingCompleted
} from '@/lib/supabase';
import { generateRouteName } from '@/lib/recommendations';
import { nanoid } from 'nanoid';

async function completeOnboarding(preferences: UserPreferences) {
  const user = await getCurrentUser();
  if (!user) throw new Error('Not authenticated');

  // 1. Save preferences
  await saveUserPreferences({
    user_id: user.id,
    ...preferences
  });

  // 2. Generate personalized route
  const routeName = generateRouteName(preferences);
  const shareCode = nanoid(10);
  const atractivos = selectAtractivos(preferences);  // Your logic

  await saveUserRoute({
    user_id: user.id,
    user_name: user.user_metadata.name,
    route_name: routeName,
    atractivos,
    ticket_url: `/ruta/${shareCode}`,
    share_code: shareCode,
    badges: ['explorador']
  });

  // 3. Unlock onboarding badge
  await unlockBadge(user.id, 'explorador');

  // 4. Mark onboarding complete
  await markOnboardingCompleted(user.id);

  return shareCode;
}

Row-Level Security (RLS)

All tables are protected with RLS policies:

-- Users can only read/write their own data
CREATE POLICY "Users can view own profile"
ON user_profiles FOR SELECT
USING (auth.uid() = id);

CREATE POLICY "Users can update own profile"
ON user_profiles FOR UPDATE
USING (auth.uid() = id);

-- Public can view shared routes
CREATE POLICY "Public can view routes by share code"
ON user_routes FOR SELECT
USING (true);

Error Handling

const { data, error } = await saveUserRoute(route);

if (error) {
  console.error('Failed to save route:', error.message);
  // Show user-friendly error
  toast.error('No se pudo guardar la ruta. Inténtalo de nuevo.');
  return;
}

// Success
toast.success('¡Ruta guardada!');

Next Steps

Authentication

Implement authentication flows with AuthButtons

Tourism Components

TurismoOnboarding and route generation

Architecture

Understanding the data flow

Tech Stack

All technologies powering the platform

Architecture

Core Systems

Components

Documentation Index

​Overview

​Client Setup

​Configuration

​Environment Variables

​Database Schema

​user_profiles

​user_preferences

​user_routes

​user_badges

​user_favorites

​Authentication Functions

​Get Current User

​Email/Password Authentication

​OAuth with Google

​Sign Out

​Profile Functions

​Get User Profile

​Update Profile

​Mark Onboarding Complete

​Check Onboarding Status

​Get Full Profile

​Preferences Functions

​Save Preferences

​Get Preferences

​Route Functions

​Save Route

​Get Route by Share Code

​Get All User Routes

​Get Latest Route

​Badge Functions

​Unlock Badge

​Get User Badges

​Check Badge Ownership

​Favorites Functions

​Add Favorite

​Remove Favorite

​Toggle Favorite

​Check Favorite Status

​Get All Favorites

​Get Favorites Count

​Real-time Subscriptions

​Listen to Auth Changes

​Listen to Database Changes

​Example: Complete Onboarding Flow

​Row-Level Security (RLS)

​Error Handling

​Next Steps

Authentication

Tourism Components

Architecture

Tech Stack

Build docs developers (and LLMs) love

Overview

Client Setup

Configuration

Environment Variables

Database Schema

`user_profiles`

`user_preferences`

`user_routes`

`user_badges`

`user_favorites`

Authentication Functions

Get Current User

Email/Password Authentication

OAuth with Google

Sign Out

Profile Functions

Get User Profile

Update Profile

Mark Onboarding Complete

Check Onboarding Status

Get Full Profile

Preferences Functions

Save Preferences

Get Preferences

Route Functions

Save Route

Get Route by Share Code

Get All User Routes

Get Latest Route

Badge Functions

Unlock Badge

Get User Badges

Check Badge Ownership

Favorites Functions

Add Favorite

Remove Favorite

Toggle Favorite

Check Favorite Status

Get All Favorites

Get Favorites Count

Real-time Subscriptions

Listen to Auth Changes

Listen to Database Changes

Example: Complete Onboarding Flow

Row-Level Security (RLS)

Error Handling

Next Steps