Skip to main content

​
Overview

Quality Hub GINEZ uses Supabase (PostgreSQL) as its database. This guide covers how to safely modify the database schema and manage migrations.

​
Database Schema

​
Core Tables

The application uses two main tables:

​
1. bitacora_produccion

Stores all production batch records with 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)
);

​
2. profiles

Stores user profile information and admin status: 
CREATE TABLE profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id),
  email TEXT,
  nombre TEXT,
  area TEXT,
  puesto TEXT,
  is_admin BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

​
Creating Migrations

​
Using Supabase Dashboard

The simplest way to create migrations is through the Supabase dashboard:
  1. Go to your Supabase project
  2. Navigate to SQL Editor
  3. Write your migration SQL
  4. Click Run to execute

​
Example: Adding a New Column

-- Add a new column for batch notes
ALTER TABLE bitacora_produccion 
ADD COLUMN notas TEXT;

​
Example: Creating Indexes

-- Index for faster searches by product code
CREATE INDEX idx_codigo_producto 
ON bitacora_produccion(codigo_producto);

​
Row Level Security (RLS)

​
Understanding RLS

RLS ensures users can only access data they’re authorized to see. Quality Hub uses RLS extensively for security.

​
User Access Policies

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

​
Admin Access Policies

-- 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
  )
);

​
Migration Best Practices

​
1. Always Backup First

Before running migrations: 
# Use Supabase CLI to backup
supabase db dump -f backup.sql
Or use the Supabase dashboard:
  • Go to Database → Backups
  • Create a manual backup before major changes

​
2. Test Migrations in Development

Never run migrations directly in production:
  1. Create a separate Supabase project for development
  2. Test migrations there first
  3. Verify application functionality
  4. Then apply to production

​
3. Use Transactions

BEGIN;

-- Your migration statements
ALTER TABLE bitacora_produccion ADD COLUMN nueva_columna TEXT;
CREATE INDEX idx_nueva_columna ON bitacora_produccion(nueva_columna);

-- Only commit if everything succeeds
COMMIT;

​
4. Make Migrations Reversible

Always know how to undo a migration: 
-- Add column
ALTER TABLE bitacora_produccion 
ADD COLUMN temperatura NUMERIC;

​
Common Migration Scenarios

​
Adding a New Quality Parameter

-- Add viscosity measurement
ALTER TABLE bitacora_produccion 
ADD COLUMN viscosidad NUMERIC;

-- Update application code to handle new field
-- Update production-constants.ts with standards
Then update lib/production-constants.ts: 
export const VISCOSITY_STANDARDS: Record<string, { min: number, max: number }> = {
  "LIMLIM": { min: 50, max: 100 },
  "LIMVIO": { min: 45, max: 95 },
  // ... add more products
};

​
Creating an Audit Log Table

CREATE TABLE audit_log (
  id BIGSERIAL PRIMARY KEY,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  user_id UUID REFERENCES auth.users(id),
  action TEXT NOT NULL,
  table_name TEXT NOT NULL,
  record_id BIGINT,
  old_values JSONB,
  new_values JSONB
);

-- Index for querying by user and date
CREATE INDEX idx_audit_user_date 
ON audit_log(user_id, created_at DESC);

​
Adding Full-Text Search

-- Add tsvector column for search
ALTER TABLE bitacora_produccion 
ADD COLUMN search_vector tsvector;

-- Update search vector
UPDATE bitacora_produccion 
SET search_vector = 
  to_tsvector('spanish', 
    coalesce(lote_producto, '') || ' ' ||
    coalesce(codigo_producto, '') || ' ' ||
    coalesce(nombre_preparador, '')
  );

-- Create GIN index for fast search
CREATE INDEX idx_search 
ON bitacora_produccion USING GIN(search_vector);

​
Modifying Existing Columns

​
Changing Column Type

-- Change column type (safe if data compatible)
ALTER TABLE bitacora_produccion 
ALTER COLUMN tamano_lote TYPE DECIMAL(10,2);

​
Adding NOT NULL Constraint

-- First, set default for existing nulls
UPDATE bitacora_produccion 
SET apariencia = 'NO ESPECIFICADO' 
WHERE apariencia IS NULL;

-- Then add NOT NULL constraint
ALTER TABLE bitacora_produccion 
ALTER COLUMN apariencia SET NOT NULL;

​
Monitoring and Maintenance

​
Check Table Size

SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

​
Vacuum and Analyze

-- Reclaim space and update statistics
VACUUM ANALYZE bitacora_produccion;

​
Emergency Rollback

If a migration causes issues:
  1. Restore from backup (Supabase Dashboard → Backups)
  2. Or manually revert the changes:
-- Drop the problematic column
ALTER TABLE bitacora_produccion DROP COLUMN nueva_columna;

-- Drop the problematic index
DROP INDEX IF EXISTS idx_nueva_columna;

​
Next Steps

Adding Products

Learn how to add new products and standards

Customization

Customize the application functionality

Build docs developers (and LLMs) love

Get started for freeTalk to us