Supabase Setup

Configure Supabase database for Estudio Three application

Estudio Three uses Supabase as its PostgreSQL database with built-in authentication and real-time features. This guide walks you through setting up your Supabase project.

Prerequisites

• A Supabase account (free tier available)
• The supabase/schema.sql file from the project

Creating a Supabase Project

1. Sign up for Supabase

   Go to supabase.com and create a free account

2. Create new project

   • Click “New Project”
   • Choose your organization
   • Enter a project name (e.g., “estudio-three”)
   • Set a strong database password (save this securely)
   • Select a region close to your users
   • Click “Create new project”
3. Wait for provisioning

   Project setup takes 2-3 minutes. You’ll see a progress indicator.

Running the Database Schema

The schema.sql file creates all necessary tables, enums, policies, and triggers.

1. Navigate to SQL Editor

   In your Supabase dashboard, click SQL Editor in the left sidebar

2. Create new query

   Click New Query button

3. Paste schema content

   Copy the entire contents of supabase/schema.sql and paste into the editor

4. Execute the schema

   Click Run or press Ctrl/Cmd + Enter

   The schema includes safety checks for existing objects. It’s safe to run multiple times.

5. Verify tables created

   Click Table Editor in the sidebar. You should see tables like:

   • profiles
   • subjects
   • tasks
   • events
   • focus_sessions
   • grades
   • And many more…

Database Structure

The schema creates a comprehensive academic optimization system:

Core Tables

Table	Purpose
profiles	User profiles and settings
subjects	Academic subjects with difficulty ratings
tasks	To-do items with Pomodoro tracking
subtasks	Checklist items within tasks
events	Calendar events (exams, matches, appointments)
weekly_schedule	Recurring weekly commitments
grades	Academic grades and scores
goals	Long-term objectives
focus_sessions	Pomodoro timer sessions
routines	Daily routine plans
routine_blocks	Time blocks within routines
daily_feedback	Daily reflection and metrics
chat_history	AI coach conversation history
habits	Habit tracking
habit_logs	Daily habit completion logs
achievements	Gamification achievements
user_achievements	Unlocked achievements per user
translations	Dynamic i18n translations

Enums (Type Safety)

The schema defines several enum types for data integrity:

task_status: 'pending', 'in_progress', 'completed'
task_priority: 'low', 'medium', 'high', 'urgent'
session_mode: 'focus', 'short_break', 'long_break'
routine_block_type: 'FIXED', 'FLEXIBLE', 'BUFFER'
goal_type: 'academic', 'sport', 'personal', 'health'
event_type: 'exam', 'match', 'training', 'class', 'other', 'holiday'
weekday: 'monday', 'tuesday', ..., 'sunday'

Row Level Security (RLS)

All tables have Row Level Security enabled to protect user data.

RLS policies ensure users can only access their own data. Never disable RLS in production!

Example Policy (Tasks Table)

-- Users can only manage their own tasks
create policy "Usuarios gestionan sus tareas" 
  on tasks for all 
  using ( auth.uid() = user_id );

Each user is isolated through policies that check auth.uid() = user_id.

Authentication Setup

1. Navigate to Authentication

   Click Authentication in the Supabase sidebar

2. Configure email authentication

   Email authentication is enabled by default. You can customize:

   • Email templates
   • Confirmation requirements
   • Password policies
3. Enable OAuth providers (optional)

   Go to Authentication → Providers:

   For Google OAuth:

   • Enable Google provider
   • Add your Google OAuth credentials
   • Set authorized redirect URLs to your VITE_APP_URL
4. Configure redirect URLs

   Go to Authentication → URL Configuration:

   Add your site URL:

   http://localhost:5173  (development)
   https://yourdomain.com (production)
   

The schema includes a trigger that automatically creates a profile with default subjects when a user signs up

Automatic User Initialization

When a new user signs up, the handle_new_user() trigger automatically:

1. Creates a profile with user metadata
2. Adds default subjects:
   • Matemáticas (Difficulty: 4)
   • Lengua y Literatura (Difficulty: 3)
   • Historia (Difficulty: 3)
   • Inglés (Difficulty: 3)
   • Ciencias / Física (Difficulty: 5)
3. Creates an initial goal: “Completar mi primera semana de rutina”

Getting API Credentials

1. Open Project Settings

   Click the Settings gear icon → API

2. Copy Project URL

   Find Project URL and copy to VITE_SUPABASE_URL

   https://your-project.supabase.co
   
3. Copy anon/public key

   Find Project API keys → anon public and copy to VITE_SUPABASE_ANON_KEY

   eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...
   
4. Save service_role key (optional)

   The service_role key bypasses RLS. Only use server-side for admin operations.

   Never expose the service_role key in client code!

Seeding Achievement Data

The schema includes an achievements table for gamification. You may want to seed it with initial achievements:

INSERT INTO achievements (id, title, description, xp_reward, icon) VALUES
  ('first_task', 'Primera Tarea', 'Completa tu primera tarea', 50, '✅'),
  ('focus_master', 'Maestro del Enfoque', 'Completa 10 sesiones Pomodoro', 100, '🎯'),
  ('week_streak', 'Racha Semanal', 'Mantén una racha de 7 días', 200, '🔥'),
  ('perfect_routine', 'Rutina Perfecta', 'Completa todos los bloques de un día', 150, '⭐');

Database Functions (RPCs)

The schema includes several PostgreSQL functions:

increment_task_pomodoros()

SELECT increment_task_pomodoros('task-uuid-here');

Safely increments the pomodoros_completed counter without race conditions.

get_weekly_summary()

SELECT * FROM get_weekly_summary();

Returns productivity analytics:

• Total focus minutes
• Session count
• Tasks completed
• Top subject by time spent

Monitoring and Logs

1. View database logs

   Click Database → Logs to see query logs and errors

2. Monitor table activity

   Click Table Editor → Select a table → Logs tab

3. Check authentication logs

   Click Authentication → Logs to see sign-ups and logins

Troubleshooting

”violación de clave foránea” (Foreign Key Violation)

Problem: Trying to create tasks before profile exists

Solution: Ensure the user completes onboarding. Check if profile exists:

SELECT * FROM profiles WHERE id = auth.uid();

“Type event_type does not exist”

Problem: Schema not fully executed

Solution: Re-run the entire schema.sql file in SQL Editor

”RLS policy prevents access”

Problem: Accessing data without authentication

Solution: Ensure user is logged in and auth.uid() returns a valid UUID

Schema updates not applying

Problem: Cached schema or migration conflicts

Solution:

Only use in development! This will delete all data.

-- Uncomment and run the DROP TABLE statements at the top of schema.sql
DROP TABLE IF EXISTS chat_history CASCADE;
DROP TABLE IF EXISTS daily_feedback CASCADE;
-- ... etc

Then re-run the full schema.

Backup and Recovery

1. Enable automatic backups

   Supabase Pro plan includes automatic daily backups

2. Manual backup

   Go to Database → Backups → Create backup

3. Export data

   Use SQL Editor to export data:

   COPY tasks TO '/tmp/tasks.csv' CSV HEADER;
   

Performance Optimization

The schema includes indexes on frequently queried columns:

-- Example indexes
CREATE INDEX tasks_user_id_idx ON tasks(user_id);
CREATE INDEX tasks_status_idx ON tasks(status);
CREATE INDEX events_start_time_idx ON events(start_time);

These improve query performance for common operations.

Next Steps

After setting up Supabase:

1. Configure environment variables
2. Deploy to Vercel or Docker
3. Test authentication and data access

Keep your database password and service_role key in a secure password manager