NuestraVoz Campus Supabase Database Setup and Migrations

NuestraVoz delegates its entire persistence layer to Supabase: a managed PostgreSQL database for relational data, the built-in Auth service for identity management, and Storage buckets for binary assets such as activity materials and student submissions. The schema is maintained through a sequential set of SQL migration files in supabase/migrations/, and Row Level Security (RLS) policies control per-role data visibility. The Express API always uses the service_role key which bypasses RLS, but the policies remain correct for any code that runs with a user-scoped JWT.

Create a Supabase project

Go to supabase.com and sign in or create a free account.

Create a new project

Click New project, choose an organisation, enter a project name (e.g. nuestravoz-production), set a strong database password, and select the region closest to your users.

Copy your credentials

Once the project is provisioned, navigate to Project Settings → API. Copy:

Project URL → SUPABASE_URL in apps/api/.env
service_role secret key → SUPABASE_SERVICE_ROLE_KEY in apps/api/.env

The service_role key bypasses all Row Level Security policies. Never expose it in client-side code, public repositories, or build logs.

Migrations overview

All schema changes are versioned in supabase/migrations/. Apply them in order — each file is cumulative and depends on the previous ones.

Migration	Description
`001_initial_schema.sql`	Core tables (`profiles`, `carreras`, `materias`, `inscripciones`, `actividades`, `entregas`, `foro_hilos`, `foro_respuestas`, `foro_reacciones`, `recursos`, `auditoria_logs`), enums, RLS enablement, and storage buckets
`002_fix_rls_policies.sql`	Granular INSERT / UPDATE / DELETE RLS policies for all tables, covering all roles
`003_accesos_rapidos.sql`	Adds the `accesos_rapidos` table for per-subject quick-access links
`004_actividad_materiales.sql`	Adds `actividad_materiales` to attach file resources to specific activities
`005_entregas.sql`	Extends `entregas` with file upload (`storage_path`) and feedback fields
`006_entregas_tardias.sql`	Adds a `tardia` boolean column for late-submission tracking
`007_formularios.sql`	Introduces `formulario_preguntas`, `formulario_opciones`, and `entrega_respuestas` tables for in-platform quizzes
`008_recursos_descripcion.sql`	Adds a `descripcion` column to the `recursos` table
`009_cursos_y_horarios.sql`	Adds the `materias.horarios` JSONB column and `cursos` table with schedule and Zoom link
`010_preceptor_role.sql`	Extends the `user_role` enum with the `preceptor` role for registrar staff
`011_notas_finales.sql`	Creates the `notas_finales` table for final examination grades per student and subject
`012_inscripciones_unique_comision.sql`	Adds a unique constraint on `inscripciones` preventing duplicate enrolments per student per career/subject
`013_remove_comisiones.sql`	Drops the deprecated `comisiones` table
`014_anio_por_carrera.sql`	Adds `anio` column to `carreras` to drive automatic `anio_academico` derivation for final grades
`015_materias_promocional.sql`	Adds `promocional` boolean to `materias` distinguishing promotional subjects
`016_notas_finales_libre.sql`	Adds `libre` boolean to `notas_finales` for the unattended examination modality
`017_notas_finales_regular.sql`	Adds `regular` boolean to `notas_finales` for the regular examination modality
`018_cursos_en_materias.sql`	Adds `curso_id` foreign key to `materias` for standalone course enrolments
`019_preceptor_notas_finales_write.sql`	Grants the `preceptor` role INSERT and UPDATE permission on `notas_finales` via an RLS policy

Run migrations

Via Supabase CLI (recommended)

# Install the CLI
brew install supabase/tap/supabase   # macOS
npm install -g supabase              # any platform

# Link to your remote project
supabase login
supabase link --project-ref <your-project-ref>

# Push all pending migrations
supabase db push

Your project ref is the subdomain in your Supabase URL (e.g. kdiebefyguycbjlsnjsm).

supabase db push is idempotent with respect to already-applied migrations. It is safe to run in a CI/CD pipeline on every deployment.

Via Supabase Dashboard

Open each migration file and paste its contents into SQL Editor → New query in the Supabase Dashboard, then click Run. Execute files in numerical order (001 → 019).

Local development stack

supabase start    # boots Docker containers (requires Docker Desktop)
supabase db reset # drops and recreates local DB, applies all migrations

The local Supabase Studio is available at http://localhost:54323. Update SUPABASE_URL in .env to http://localhost:54321 when working against the local stack.

Row Level Security

Every table has RLS enabled. Policies use a get_user_role() helper function that reads the authenticated user’s role from the profiles table:

CREATE OR REPLACE FUNCTION get_user_role()
RETURNS user_role
LANGUAGE sql STABLE SECURITY DEFINER
SET search_path = public
AS $$
  SELECT rol FROM profiles WHERE id = auth.uid();
$$;

Role	Read access	Write access
`admin`	Everything	Everything
`docente`	All academic data	Announcements, activities, resources, grades for own subjects
`estudiante`	Own enrolments, enrolled subjects, own submissions	Own submissions, forum threads and replies
`preceptor`	All academic data	Final grades (`notas_finales`) and subject metadata

Supabase Storage

Two storage buckets are used by the platform:

avatars

User profile pictures. Upload is scoped to the authenticated user’s own folder. Reads are public.

recursos

Activity materials uploaded by docentes and student submission files. Any authenticated user may upload; reads are public. The recursos and actividad_materiales tables store public URLs.

The maximum single file upload is 20 MiB as enforced by the multer middleware in the Express API (limits: { fileSize: 20 * 1024 * 1024 }).

Seed demo data

The seed script provisions a minimal dataset so you can immediately log in and explore the platform:

pnpm --filter @nuestravoz/api seed

The script (apps/api/src/scripts/seed.ts) is idempotent — it checks for existing records before inserting:

Entity	Details
Admin user	`admin@nuestravoz.edu` / `Admin123!` — full platform access, name: Carlos Mendoza
Career	Ingeniería en Sistemas (code `IS-2024`, type `grado`, anio `2`)
Subject	Arquitectura de Sistemas I — schedule `Lun y Vie 18:30`, demo Zoom link
Audit log	One `Sistema inicializado` entry in `auditoria_logs`

Change the default admin password (Admin123!) immediately after seeding in any internet-accessible environment.

Getting Started

Core Features

User Roles

Configuration & Deployment

Create a Supabase project

Migrations overview

Run migrations

Via Supabase CLI (recommended)

Via Supabase Dashboard

Local development stack

Row Level Security

Supabase Storage

avatars

recursos

Seed demo data

Build docs developers (and LLMs) love

Getting Started

Core Features

User Roles

Configuration & Deployment

Documentation Index

​Create a Supabase project

​Migrations overview

​Run migrations

​Via Supabase CLI (recommended)

​Via Supabase Dashboard

​Local development stack

​Row Level Security

​Supabase Storage

avatars

recursos

​Seed demo data

Build docs developers (and LLMs) love

Create a Supabase project

Migrations overview

Run migrations

Via Supabase CLI (recommended)

Via Supabase Dashboard

Local development stack

Row Level Security

Supabase Storage

Seed demo data