Supabase Setup

Overview

Quality Hub GINEZ uses Supabase as its Backend-as-a-Service (BaaS) provider, leveraging:

PostgreSQL 15 for data storage
Row Level Security (RLS) for access control
Supabase Auth for user authentication
Realtime subscriptions for live updates

This guide walks through the complete database setup process.

Prerequisites

Create Supabase Account

Create New Project

Click “New Project” from your Supabase dashboard
Enter project name: quality-hub-ginez
Set a strong database password (save this securely)
Choose a region close to your users
Wait for project to initialize (~2 minutes)

Get API Credentials

Navigate to Settings → API and note:

Project URL
anon public key
service_role key (keep secure)

Add these to your .env.local file (see Environment Variables).

Database Schema

1. Production Log Table

The main table for storing production batch records and quality measurements.

CREATE TABLE bitacora_produccion (
  id BIGSERIAL PRIMARY KEY,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  lote_producto TEXT NOT NULL,
  codigo_producto TEXT NOT NULL,
  sucursal TEXT NOT NULL,
  familia_producto TEXT,
  categoria_producto TEXT,
  fecha_fabricacion DATE NOT NULL,
  tamano_lote NUMERIC,
  ph NUMERIC,
  solidos_medicion_1 NUMERIC,
  solidos_medicion_2 NUMERIC,
  apariencia TEXT,
  color TEXT,
  aroma TEXT,
  nombre_preparador TEXT,
  user_id UUID REFERENCES auth.users(id)
);

-- Create indexes for better query performance
CREATE INDEX idx_bitacora_user_id ON bitacora_produccion(user_id);
CREATE INDEX idx_bitacora_fecha ON bitacora_produccion(fecha_fabricacion);
CREATE INDEX idx_bitacora_sucursal ON bitacora_produccion(sucursal);
CREATE INDEX idx_bitacora_producto ON bitacora_produccion(codigo_producto);
CREATE INDEX idx_bitacora_created ON bitacora_produccion(created_at);

-- Enable Row Level Security
ALTER TABLE bitacora_produccion ENABLE ROW LEVEL SECURITY;

The table stores both quality measurements (pH, solids) and organoleptic properties (appearance, color, aroma) for comprehensive quality control.

2. User Profiles Table

Extends Supabase Auth with additional user metadata and role management.

CREATE TABLE profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT,
  nombre TEXT,
  area TEXT,
  puesto TEXT,
  is_admin BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create indexes
CREATE INDEX idx_profiles_email ON profiles(email);
CREATE INDEX idx_profiles_is_admin ON profiles(is_admin);

-- Enable Row Level Security
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

Profiles are automatically created when users sign up. The trigger ensures every authenticated user has a corresponding profile entry.

Row Level Security (RLS) Policies

RLS ensures users can only access data they’re authorized to see.

Policies for `bitacora_produccion`

-- Users can view their own records
CREATE POLICY "Users can view own records"
ON bitacora_produccion FOR SELECT
USING (auth.uid() = user_id);

-- Admins can view all records
CREATE POLICY "Admins can view all records"
ON bitacora_produccion FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM profiles
    WHERE profiles.id = auth.uid()
    AND profiles.is_admin = true
  )
);

Policies for `profiles`

-- Users can view their own profile
CREATE POLICY "Users can view own profile"
ON profiles FOR SELECT
USING (auth.uid() = id);

-- Admins can view all profiles
CREATE POLICY "Admins can view all profiles"
ON profiles FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM profiles AS p
    WHERE p.id = auth.uid()
    AND p.is_admin = true
  )
);

The is_admin check in user update policy prevents privilege escalation. Regular users cannot promote themselves to admin.

Complete Setup Script

Run this complete SQL script in the Supabase SQL Editor:

-- ================================================
-- Quality Hub GINEZ - Complete Database Setup
-- ================================================

-- 1. Create bitacora_produccion table
CREATE TABLE bitacora_produccion (
  id BIGSERIAL PRIMARY KEY,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  lote_producto TEXT NOT NULL,
  codigo_producto TEXT NOT NULL,
  sucursal TEXT NOT NULL,
  familia_producto TEXT,
  categoria_producto TEXT,
  fecha_fabricacion DATE NOT NULL,
  tamano_lote NUMERIC,
  ph NUMERIC,
  solidos_medicion_1 NUMERIC,
  solidos_medicion_2 NUMERIC,
  apariencia TEXT,
  color TEXT,
  aroma TEXT,
  nombre_preparador TEXT,
  user_id UUID REFERENCES auth.users(id)
);

-- Create indexes
CREATE INDEX idx_bitacora_user_id ON bitacora_produccion(user_id);
CREATE INDEX idx_bitacora_fecha ON bitacora_produccion(fecha_fabricacion);
CREATE INDEX idx_bitacora_sucursal ON bitacora_produccion(sucursal);
CREATE INDEX idx_bitacora_producto ON bitacora_produccion(codigo_producto);
CREATE INDEX idx_bitacora_created ON bitacora_produccion(created_at);

-- Enable RLS
ALTER TABLE bitacora_produccion ENABLE ROW LEVEL SECURITY;

-- 2. Create profiles table
CREATE TABLE profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT,
  nombre TEXT,
  area TEXT,
  puesto TEXT,
  is_admin BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_profiles_email ON profiles(email);
CREATE INDEX idx_profiles_is_admin ON profiles(is_admin);

ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- 3. Create helper functions
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_profiles_updated_at
  BEFORE UPDATE ON profiles
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id, email, nombre, is_admin)
  VALUES (
    NEW.id,
    NEW.email,
    COALESCE(NEW.raw_user_meta_data->>'nombre', NEW.email),
    false
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW
  EXECUTE FUNCTION public.handle_new_user();

-- 4. RLS Policies for bitacora_produccion
CREATE POLICY "Users can view own records" ON bitacora_produccion
  FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Admins can view all records" ON bitacora_produccion
  FOR SELECT USING (
    EXISTS (SELECT 1 FROM profiles WHERE profiles.id = auth.uid() AND profiles.is_admin = true)
  );

CREATE POLICY "Users can insert own records" ON bitacora_produccion
  FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own records" ON bitacora_produccion
  FOR UPDATE USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Admins can update all records" ON bitacora_produccion
  FOR UPDATE USING (
    EXISTS (SELECT 1 FROM profiles WHERE profiles.id = auth.uid() AND profiles.is_admin = true)
  );

CREATE POLICY "Admins can delete records" ON bitacora_produccion
  FOR DELETE USING (
    EXISTS (SELECT 1 FROM profiles WHERE profiles.id = auth.uid() AND profiles.is_admin = true)
  );

-- 5. RLS Policies for profiles
CREATE POLICY "Users can view own profile" ON profiles
  FOR SELECT USING (auth.uid() = id);

CREATE POLICY "Admins can view all profiles" ON profiles
  FOR SELECT USING (
    EXISTS (SELECT 1 FROM profiles AS p WHERE p.id = auth.uid() AND p.is_admin = true)
  );

CREATE POLICY "Users can update own profile" ON profiles
  FOR UPDATE USING (auth.uid() = id)
  WITH CHECK (auth.uid() = id AND is_admin = (SELECT is_admin FROM profiles WHERE id = auth.uid()));

CREATE POLICY "Admins can update all profiles" ON profiles
  FOR UPDATE USING (
    EXISTS (SELECT 1 FROM profiles AS p WHERE p.id = auth.uid() AND p.is_admin = true)
  );

CREATE POLICY "Admins can delete profiles" ON profiles
  FOR DELETE USING (
    EXISTS (SELECT 1 FROM profiles WHERE profiles.id = auth.uid() AND profiles.is_admin = true)
    AND id != auth.uid()
  );

Authentication Configuration

1. Enable Email Authentication

Navigate to Authentication

In Supabase Dashboard, go to Authentication → Providers

Configure Email Provider

Enable “Email” provider
Set “Confirm email” to ON (recommended for production)
Configure email templates if needed

Set Site URL

Go to Authentication → URL Configuration:

Site URL: http://localhost:3000 (dev) or your production URL
Redirect URLs: Add your application URLs

2. Create First Admin User

After setup, manually promote your first user to admin:

-- Find your user ID
SELECT id, email FROM auth.users;

-- Promote to admin (replace with actual user ID)
UPDATE profiles
SET is_admin = true
WHERE id = 'your-user-uuid-here';

After creating your first admin, use the application’s Configuration module to manage other users’ roles.

Verification

Verify your setup is working correctly:

Check Tables

In Supabase Dashboard → Table Editor, confirm:

✅ bitacora_produccion table exists
✅ profiles table exists

Test RLS

In SQL Editor, run:

SELECT * FROM bitacora_produccion; -- Should respect RLS
SELECT * FROM profiles; -- Should respect RLS

Test Authentication

In your application:

Sign up a new user
Check that profile was auto-created
Try creating a production record
Verify record is visible

Troubleshooting

Error: relation does not exist

Cause: Table not created or wrong schemaSolution: Verify tables exist in public schema:

SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public';

Error: permission denied for table

Cause: RLS blocking accessSolution:

Verify RLS policies are created: SELECT * FROM pg_policies;
Check user is authenticated: SELECT auth.uid();
For admins, verify is_admin is true in profiles table

Profile not auto-created on signup

Cause: Trigger not workingSolution:

Verify trigger exists: SELECT * FROM pg_trigger WHERE tgname = 'on_auth_user_created';
Manually create profile for existing users:

INSERT INTO profiles (id, email, nombre, is_admin)
SELECT id, email, email, false
FROM auth.users
WHERE id NOT IN (SELECT id FROM profiles);

Can't delete own profile

Expected behavior: Self-deletion is prevented by policy

AND id != auth.uid() -- Prevents self-deletion

This safety measure prevents admins from accidentally locking themselves out.

Backup and Maintenance

Automatic Backups

Supabase Pro plans include automatic daily backups. Free tier allows manual backups via dashboard.

Manual Backup

# Export schema
pg_dump -h db.xxx.supabase.co -U postgres \
  --schema-only > schema.sql

# Export data
pg_dump -h db.xxx.supabase.co -U postgres \
  --data-only > data.sql

Monitoring

Monitor database performance in Supabase Dashboard → Database → Query Performance

Index Optimization

Review slow queries and add indexes as needed:

CREATE INDEX idx_name ON table_name(column);

Get Started

Core Modules

Quality Management

User Management

Configuration

Overview

Prerequisites

Database Schema

1. Production Log Table

2. User Profiles Table

Row Level Security (RLS) Policies

Policies for `bitacora_produccion`

Policies for `profiles`

Complete Setup Script

Authentication Configuration

1. Enable Email Authentication

2. Create First Admin User

Verification

Troubleshooting

Backup and Maintenance

Automatic Backups

Manual Backup

Monitoring

Index Optimization

Next Steps

Environment Variables

Multi-Location Setup

Build docs developers (and LLMs) love

Get Started

Core Modules

Quality Management

User Management

Configuration

​Overview

​Prerequisites

​Database Schema

​1. Production Log Table

​2. User Profiles Table

​Row Level Security (RLS) Policies

​Policies for bitacora_produccion

​Policies for profiles

​Complete Setup Script

​Authentication Configuration

​1. Enable Email Authentication

​2. Create First Admin User

​Verification

​Troubleshooting

​Backup and Maintenance

Automatic Backups

Manual Backup

Monitoring

Index Optimization

​Next Steps

Environment Variables

Multi-Location Setup

Build docs developers (and LLMs) love

Overview

Prerequisites

Database Schema

1. Production Log Table

2. User Profiles Table

Row Level Security (RLS) Policies

Policies for `bitacora_produccion`

Policies for `profiles`

Complete Setup Script

Authentication Configuration

1. Enable Email Authentication

2. Create First Admin User

Verification

Troubleshooting

Backup and Maintenance

Next Steps