Skip to main content

​
User Management Tables

​
rol - User Roles

Defines the role hierarchy for the system.
​
id_rol
SERIAL PRIMARY KEY
Unique role identifier
​
rol_name
TEXT NOT NULL UNIQUE
Role name (Director, Madre Procesadora, Supervisor, Desarrollador)
​
description
TEXT
Role description and permissions summary
Default Roles:
  • id_rol=1: Director - School system administrator
  • id_rol=2: Madre Procesadora - Kitchen and inventory operations
  • id_rol=3: Supervisor - Read-only supervision
  • id_rol=4: Desarrollador - Technical administrator (assigned from DB only)

​
users - User Accounts

Extends Supabase auth.users with application-specific fields.
​
id_user
UUID PRIMARY KEY
User ID (references auth.users.id)Foreign Key: auth.users(id) ON DELETE CASCADE
​
username
TEXT UNIQUE NOT NULL
Unique username for login
​
full_name
TEXT NOT NULL
User’s full name
​
id_rol
INTEGER
User role (references rol.id_rol)Default: 2 (Madre Procesadora)
​
is_active
BOOLEAN
Account active statusDefault: TRUE
​
last_activity
TIMESTAMPTZ
Last activity timestamp
​
last_seen
TIMESTAMPTZ
Last seen (heartbeat) timestamp
​
last_ip
TEXT
Last known IP address
​
created_at
TIMESTAMPTZ
Account creation timestampDefault: NOW()
Triggers:
  • trigger_protect_director - Prevents unauthorized modifications to Director accounts
  • trigger_protect_director_insert - Validates role assignments on creation

​
Inventory Management Tables

​
category - Product Categories

​
id_category
SERIAL PRIMARY KEY
Unique category identifier
​
category_name
TEXT NOT NULL UNIQUE
Category name
​
description
TEXT
Category description
Default Categories:
  • Lacteos (Dairy)
  • Proteinas (Proteins)
  • Carbohidratos (Carbohydrates)
  • Legumbres (Legumes)
  • Vegetales (Vegetables)
  • Frutas (Fruits)
  • Aceites y grasas (Oils and fats)
  • Condimentos (Condiments)
  • Otros (Others)

​
product - Product Catalog

Master product catalog with current stock levels.
​
id_product
SERIAL PRIMARY KEY
Unique product identifier
​
product_name
TEXT NOT NULL
Product name
​
stock
NUMERIC(10,2)
Current stock quantityDefault: 0Constraint: CHECK (stock >= 0)
​
unit_measure
TEXT NOT NULL
Unit of measurement (kg, lt, unidades)
​
description
TEXT
Product description or notes
​
id_category
INTEGER
Product category (references category.id_category)
​
created_at
TIMESTAMPTZ
Record creation timestampDefault: NOW()
​
updated_at
TIMESTAMPTZ
Last update timestampDefault: NOW()
Triggers:
  • update_product_updated_at - Automatically updates updated_at on modifications
  • audit_product - Logs all INSERT/UPDATE/DELETE operations

​
guia_entrada - Delivery Guides

Delivery guides with maker-checker approval workflow.
​
id_guia
SERIAL PRIMARY KEY
Unique guide identifier
​
numero_guia_sunagro
TEXT NOT NULL
SUNAGRO guide number (unique)Constraint: UNIQUE
​
numero_guia_sisecal
TEXT
Optional SISECAL guide number
​
fecha
DATE
Delivery dateDefault: CURRENT_DATE
​
vocera_nombre
TEXT
Community representative name
​
telefono_vocera
TEXT
Representative phone number
​
notas
TEXT
Additional notes
​
estado
TEXT
Approval status: Pendiente, Aprobada, RechazadaDefault: 'Pendiente'Constraint: CHECK (estado IN ('Pendiente', 'Aprobada', 'Rechazada'))
​
aprobado_por
UUID
User who approved/rejected (references users.id_user)
​
fecha_aprobacion
TIMESTAMPTZ
Approval/rejection timestamp
​
comentarios_aprobacion
TEXT
Approval/rejection comments
​
created_by
UUID
User who created the guide (references users.id_user)
​
created_at
TIMESTAMPTZ
Creation timestampDefault: NOW()
Indexes:
  • idx_guia_entrada_estado - Fast filtering by status
Triggers:
  • audit_guia - Logs all operations

​
input - Delivery Guide Line Items

Individual products within delivery guides, with batch/lot tracking.
​
id_input
SERIAL PRIMARY KEY
Unique input identifier
​
id_guia
INTEGER
Parent delivery guide (references guia_entrada.id_guia)Foreign Key: ON DELETE CASCADE
​
id_product
INTEGER
Product (references product.id_product)
​
amount
NUMERIC(10,2)
Quantity receivedConstraint: CHECK (amount > 0)
​
unit_amount
INTEGER
Quantity in bulk units (if applicable)
​
lotes_detalle
JSONB
Batch/lot details with expiration datesFormat: [{"cantidad": 50.00, "fecha_vencimiento": "2026-06-30"}]
​
fecha
DATE
Input dateDefault: CURRENT_DATE
​
created_at
TIMESTAMPTZ
Creation timestampDefault: NOW()
Indexes:
  • idx_input_lotes_detalle - GIN index for JSONB queries

​
output - Stock Withdrawals

Records all inventory withdrawals and stock deductions.
​
id_output
SERIAL PRIMARY KEY
Unique output identifier
​
id_product
INTEGER
Product withdrawn (references product.id_product)
​
amount
NUMERIC(10,2)
Quantity withdrawnConstraint: CHECK (amount > 0)
​
fecha
DATE
Withdrawal dateDefault: CURRENT_DATE
​
motivo
TEXT
Reason for withdrawal
​
id_menu
INTEGER
Related menu (references menu_diario.id_menu) - LEGACY
​
id_registro
INTEGER
Related daily record (references registro_diario.id_registro)
​
created_by
UUID
User who created the withdrawal (references auth.users.id)
​
created_at
TIMESTAMPTZ
Creation timestampDefault: NOW()
Triggers:
  • trigger_update_stock_output - Automatically deducts stock from product table
  • audit_output - Logs all operations

​
Daily Operations Tables

​
registro_diario - Daily Meal Records

Unified daily meal operations (replaces legacy attendance + menu tables).
​
id_registro
SERIAL PRIMARY KEY
Unique record identifier
​
fecha
DATE NOT NULL
Operation date
​
turno
TEXT NOT NULL
Meal shift: Desayuno, Almuerzo, MeriendaConstraint: CHECK (turno IN ('Desayuno', 'Almuerzo', 'Merienda'))
​
asistencia_total
INTEGER NOT NULL
Total student attendanceConstraint: CHECK (asistencia_total >= 0)
​
notas
TEXT
Additional notes
​
created_by
UUID
User who created the record (references auth.users.id)
​
created_at
TIMESTAMPTZ
Creation timestampDefault: NOW()
Constraints:
  • UNIQUE(fecha, turno) - One record per date/shift combination

​
receta_porcion - Product Yield Configuration

Defines portion yield per unit for meal planning calculations.
​
id_porcion
SERIAL PRIMARY KEY
Unique portion configuration identifier
​
id_product
INTEGER UNIQUE
Product (references product.id_product)Constraint: One configuration per product
​
rendimiento_por_unidad
NUMERIC(10,2)
Portions per unit (e.g., 1 kg rice = 12 portions)Default: 1.0
​
unit_measure
TEXT NOT NULL
Unit of measurement
​
notas
TEXT
Additional notes or preparation instructions
​
created_at
TIMESTAMPTZ
Creation timestampDefault: NOW()
Example:
  • 1 kg arroz → 12 porciones
  • 1 kg leche en polvo → 40 porciones
  • 1 kg pollo → 4 porciones

​
Legacy Tables

These tables are maintained for backward compatibility but have been replaced by registro_diario. Use the new unified table for all new development.

​
asistencia_diaria - Student Attendance (LEGACY)

​
id_asistencia
SERIAL PRIMARY KEY
Unique attendance record identifier
​
fecha
DATE UNIQUE NOT NULL
Attendance date
​
total_alumnos
INTEGER NOT NULL
Total students presentConstraint: CHECK (total_alumnos >= 0)
​
notas
TEXT
Notes
​
created_by
UUID
User who created the record (references auth.users.id)
​
created_at
TIMESTAMPTZ
Creation timestampDefault: NOW()
​
id_menu
SERIAL PRIMARY KEY
Unique menu identifier
​
fecha
DATE UNIQUE NOT NULL
Menu date
​
id_asistencia
INTEGER
Related attendance (references asistencia_diaria.id_asistencia)
​
notas
TEXT
Menu notes
​
confirmado
BOOLEAN
Menu confirmation statusDefault: FALSE
​
created_by
UUID
User who created the menu (references auth.users.id)
​
created_at
TIMESTAMPTZ
Creation timestampDefault: NOW()
Triggers:
  • audit_menu - Logs all operations
​
id_detalle
SERIAL PRIMARY KEY
Unique detail identifier
​
id_menu
INTEGER
Parent menu (references menu_diario.id_menu)Foreign Key: ON DELETE CASCADE
​
id_product
INTEGER
Product (references product.id_product)
​
cantidad_planificada
NUMERIC(10,2)
Planned quantity
​
cantidad_real_usada
NUMERIC(10,2)
Actually used quantity
​
created_at
TIMESTAMPTZ
Creation timestampDefault: NOW()

​
Audit & Logging

​
audit_log - System Audit Trail

Comprehensive audit trail for all critical operations.
​
id_log
SERIAL PRIMARY KEY
Unique log entry identifier
​
id_user
UUID
User who performed the action (references auth.users.id)
​
action_type
TEXT NOT NULL
Action type: INSERT, UPDATE, DELETE, APPROVE, REJECT
​
table_affected
TEXT
Table name where action occurred
​
record_id
INTEGER
ID of affected record
​
details
TEXT
JSON details of the operation (old/new values)
​
timestamp
TIMESTAMPTZ
Operation timestampDefault: NOW()
​
ip_address
TEXT
User IP address
Automatically Populated By:
  • log_audit() trigger function
  • RPC functions (aprobar_guia, rechazar_guia, etc.)

​
Entity Relationships

​
Key Foreign Key Relationships

users
  ├─ id_rol → rol.id_rol
  └─ id_user → auth.users.id (CASCADE)

product
  └─ id_category → category.id_category

guia_entrada
  ├─ created_by → users.id_user
  └─ aprobado_por → users.id_user

input
  ├─ id_guia → guia_entrada.id_guia (CASCADE)
  └─ id_product → product.id_product

output
  ├─ id_product → product.id_product
  ├─ id_menu → menu_diario.id_menu (LEGACY)
  ├─ id_registro → registro_diario.id_registro
  └─ created_by → auth.users.id

registro_diario
  └─ created_by → auth.users.id

receta_porcion
  └─ id_product → product.id_product (UNIQUE)

​
Sample Data

The schema includes default seed data:
  • 4 roles: Director, Madre Procesadora, Supervisor, Desarrollador
  • 9 categories: Lacteos, Proteinas, Carbohidratos, etc.
  • 8 sample products: Arroz, Aceite, Leche, Pollo, etc.
  • 5 portion yields: Configured for common products
See supabase_schema.sql lines 870-888 for complete seed data.

Build docs developers (and LLMs) love

Get started for freeTalk to us