Skip to main content
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

TablePurpose
profilesUser profiles and settings
subjectsAcademic subjects with difficulty ratings
tasksTo-do items with Pomodoro tracking
subtasksChecklist items within tasks
eventsCalendar events (exams, matches, appointments)
weekly_scheduleRecurring weekly commitments
gradesAcademic grades and scores
goalsLong-term objectives
focus_sessionsPomodoro timer sessions
routinesDaily routine plans
routine_blocksTime blocks within routines
daily_feedbackDaily reflection and metrics
chat_historyAI coach conversation history
habitsHabit tracking
habit_logsDaily habit completion logs
achievementsGamification achievements
user_achievementsUnlocked achievements per user
translationsDynamic 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

Build docs developers (and LLMs) love

Get started for freeTalk to us