PostgreSQL Functions Overview

Function Categories

The PAE Inventory System uses PostgreSQL functions for:

Trigger Functions - Automatic data validation and updates
RPC Functions - Backend business logic callable from frontend
Helper Functions - Utility functions for policies and queries

Trigger Functions

`update_updated_at_column()`

Automatically updates the updated_at timestamp on row modifications. Signature:

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER

Used On:

product table (via update_product_updated_at trigger)

Logic:

NEW.updated_at = NOW();
RETURN NEW;

Example:

UPDATE product SET product_name = 'Arroz Blanco' WHERE id_product = 1;
-- updated_at is automatically set to NOW()

`protect_director_users()`

Enforces user role hierarchy and prevents unauthorized modifications. Signature:

CREATE OR REPLACE FUNCTION protect_director_users()
RETURNS TRIGGER
SECURITY DEFINER

Trigger: trigger_protect_director BEFORE UPDATE ON users Protection Rules:

Heartbeat Bypass
Self-Modification
Desarrollador Protection
Director Peer Protection
Role Promotion

Allows updates that only modify activity tracking fields:

last_activity
last_seen
last_ip

All other fields (username, full_name, id_rol, is_active) must remain unchanged.

Blocked: Users cannot modify their own account from user management interface

IF OLD.id_user = auth.uid() THEN
  RAISE EXCEPTION 'No puede modificar su propia cuenta';
END IF;

Blocked: No one can modify Desarrollador accounts (only via direct DB access)

IF OLD.id_rol = 4 THEN
  RAISE EXCEPTION 'No puede modificar la cuenta de un Desarrollador';
END IF;

Blocked: Directors cannot modify other Director accounts

IF v_actor_role = 1 AND OLD.id_rol = 1 THEN
  RAISE EXCEPTION 'Un Director no puede modificar a otro Director';
END IF;

Restricted: Only Desarrollador can promote users to Director

IF NEW.id_rol = 1 AND OLD.id_rol != 1 AND v_actor_role != 4 THEN
  RAISE EXCEPTION 'Solo el Desarrollador puede asignar el rol de Director';
END IF;

`protect_director_insert()`

Validates role assignments when creating new users. Signature:

CREATE OR REPLACE FUNCTION protect_director_insert()
RETURNS TRIGGER
SECURITY DEFINER

Trigger: trigger_protect_director_insert BEFORE INSERT ON users Validation Rules:

Desarrollador role (id=4) can only be assigned from database
Only Desarrollador can create Director accounts

`update_stock_on_output()`

Automatically deducts inventory stock when outputs are created. Signature:

CREATE OR REPLACE FUNCTION update_stock_on_output()
RETURNS TRIGGER

Trigger: trigger_update_stock_output AFTER INSERT ON output Logic:

Lock Product Row

Uses SELECT FOR UPDATE to prevent race conditions:

SELECT stock INTO v_stock_actual
FROM product
WHERE id_product = NEW.id_product
FOR UPDATE;

Validate Stock

Ensures sufficient inventory:

IF v_stock_actual < NEW.amount THEN
    RAISE EXCEPTION 'Stock insuficiente para el producto %';
END IF;

Update Stock

Deducts the withdrawal amount:

UPDATE product
SET stock = stock - NEW.amount
WHERE id_product = NEW.id_product;

This trigger ensures stock never goes negative. Attempted withdrawals exceeding available stock will fail with an exception.

`log_audit()`

Generic audit logging function for all critical tables. Signature:

CREATE OR REPLACE FUNCTION log_audit()
RETURNS TRIGGER
SECURITY DEFINER

Applied To:

product (audit_product trigger)
guia_entrada (audit_guia trigger)
menu_diario (audit_menu trigger)
output (audit_output trigger)

Captured Data:

INSERT
UPDATE
DELETE

{
  "id_user": "uuid",
  "action_type": "INSERT",
  "table_affected": "product",
  "record_id": 123,
  "details": "{\"id_product\": 123, \"product_name\": \"Arroz\", ...}"
}

{
  "id_user": "uuid",
  "action_type": "UPDATE",
  "table_affected": "product",
  "record_id": 123,
  "details": "Antes: {...} | Despues: {...}"
}

{
  "id_user": "uuid",
  "action_type": "DELETE",
  "table_affected": "product",
  "record_id": 123,
  "details": "{\"id_product\": 123, \"product_name\": \"Arroz\", ...}"
}

RPC Functions

`aprobar_guia()`

Transactionally approves a delivery guide and updates inventory. Signature:

CREATE OR REPLACE FUNCTION aprobar_guia(
  p_id_guia INTEGER,
  p_comentarios TEXT DEFAULT NULL
)
RETURNS JSON
SECURITY DEFINER

Parameters:

p_id_guia

INTEGER

required

ID of the delivery guide to approve

p_comentarios

TEXT

Optional approval comments

Returns:

{
  "success": true,
  "id_guia": 42,
  "productos_procesados": 5,
  "mensaje": "Guia ABC-123 aprobada. 5 productos actualizados."
}

Authorization:

Only users with id_rol IN (1, 4) (Director or Desarrollador) can approve

Business Logic:

Validate Permissions

Verifies user has Director or Desarrollador role

Validate Guide Status

Ensures guide is in ‘Pendiente’ state (not already processed)

Update Guide Status

Sets estado = 'Aprobada', records approver and timestamp

Update Inventory

Loops through all input records and adds quantities to product.stock

Log Audit

Creates audit trail with action=‘APPROVE’

Example Usage:

const { data, error } = await supabase.rpc('aprobar_guia', {
  p_id_guia: 42,
  p_comentarios: 'Todos los productos verificados y en buen estado'
});

`rechazar_guia()`

Rejects a delivery guide with a required reason. Signature:

CREATE OR REPLACE FUNCTION rechazar_guia(
  p_id_guia INTEGER,
  p_motivo TEXT
)
RETURNS JSON
SECURITY DEFINER

Parameters:

p_id_guia

INTEGER

required

ID of the delivery guide to reject

p_motivo

TEXT

required

Rejection reason (cannot be empty)

Returns:

{
  "success": true,
  "id_guia": 42,
  "mensaje": "Guia ABC-123 rechazada."
}

Authorization:

Only users with id_rol IN (1, 4) can reject

Validation:

Reason (p_motivo) must not be null or empty
Guide must be in ‘Pendiente’ state

Rejected guides do NOT update inventory. Stock levels remain unchanged.

`get_lotes_por_vencer()`

Queries batches nearing expiration date (FIFO-aware). Signature:

CREATE OR REPLACE FUNCTION get_lotes_por_vencer(
  p_dias INTEGER DEFAULT 30
)
RETURNS TABLE(...)
SECURITY DEFINER

Parameters:

p_dias

INTEGER

default:"30"

Number of days ahead to check for expiring batches

Returns:

id_product

INTEGER

Product ID

product_name

TEXT

Product name

stock

NUMERIC(10,2)

Current total stock

unit_measure

TEXT

Unit of measurement

category_name

TEXT

Product category

fecha_vencimiento

DATE

Batch expiration date

cantidad_lote

NUMERIC(10,2)

Quantity in this specific batch

dias_restantes

INTEGER

Days until expiration

Example Usage:

// Get batches expiring in next 15 days
const { data, error } = await supabase.rpc('get_lotes_por_vencer', {
  p_dias: 15
});

Query Logic:

Extracts batch details from JSONB lotes_detalle field
Only includes batches from approved guides
Orders by expiration date (oldest first)

`procesar_operacion_diaria()`

Processes daily meal operations with FIFO batch consumption. Signature:

CREATE OR REPLACE FUNCTION procesar_operacion_diaria(
  p_fecha DATE,
  p_turno TEXT,
  p_asistencia INTEGER,
  p_rubros INTEGER[]
)
RETURNS JSON
SECURITY DEFINER

Parameters:

p_fecha

DATE

required

Operation date

p_turno

TEXT

required

Meal shift: ‘Desayuno’, ‘Almuerzo’, or ‘Merienda’

p_asistencia

INTEGER

required

Total student attendance

p_rubros

INTEGER[]

required

Array of product IDs to consume

Returns:

{
  "success": true,
  "id_registro": 123,
  "rubros_procesados": 5,
  "mensaje": "Operacion registrada: 5 rubros procesados para 150 alumnos."
}

Authorization:

Users with id_rol IN (1, 2, 4) (Director, Madre Procesadora, or Desarrollador)

Complex FIFO Logic:

Create Daily Record

Inserts into registro_diario with date, shift, and attendance

Calculate Portions

For each product:

cantidad_necesaria = ROUND(p_asistencia / rendimiento_por_unidad, 2)

Lock Batch Rows

Uses SELECT FOR UPDATE to prevent race conditions during concurrent operations

FIFO Consumption

Orders batches by fecha_vencimiento ASC (oldest first)
Consumes from each batch sequentially
Updates JSONB lotes_detalle to reduce batch quantities
Stops when full amount is consumed

Validate Sufficiency

Raises exception if batches are insufficient:

'Lotes insuficientes para "Arroz". Faltan 5.50 unidades.'

Create Output

Inserts into output table (triggers automatic stock deduction)

Audit Trail

Logs operation with all parameters

Example Usage:

const { data, error } = await supabase.rpc('procesar_operacion_diaria', {
  p_fecha: '2026-03-15',
  p_turno: 'Almuerzo',
  p_asistencia: 150,
  p_rubros: [1, 3, 4, 5]  // Arroz, Leche, Pollo, Caraotas
});

This function implements First-In-First-Out (FIFO) inventory management, automatically consuming the oldest batches first to minimize waste from expiration.

Helper Functions

`get_user_role()`

Returns the role ID of the currently authenticated user. Signature:

CREATE OR REPLACE FUNCTION get_user_role()
RETURNS INTEGER
SECURITY DEFINER

Usage: Primarily used in Row Level Security (RLS) policies:

CREATE POLICY "Products: Director can delete" 
ON product FOR DELETE
USING (get_user_role() IN (1, 4));

Logic:

SELECT id_rol FROM users WHERE id_user = auth.uid();

Function Permissions

All RPC functions are granted to authenticated role:

GRANT EXECUTE ON FUNCTION aprobar_guia(INTEGER, TEXT) TO authenticated;
GRANT EXECUTE ON FUNCTION rechazar_guia(INTEGER, TEXT) TO authenticated;
GRANT EXECUTE ON FUNCTION get_lotes_por_vencer(INTEGER) TO authenticated;
GRANT EXECUTE ON FUNCTION procesar_operacion_diaria(DATE, TEXT, INTEGER, INTEGER[]) TO authenticated;

All RPC functions implement internal permission checks. Even though they’re granted to all authenticated users, role-based validation occurs within the function logic.

Error Handling

All functions use PostgreSQL’s exception mechanism:

EXCEPTION
  WHEN OTHERS THEN
    RAISE EXCEPTION 'Error al aprobar guia: %', SQLERRM;
END;

Common Error Patterns:

RAISE EXCEPTION 'No tiene permisos para aprobar guias.';

Best Practices

Transaction Safety

All RPC functions run in implicit transactions. If any step fails, all changes are rolled back.

Locking Strategy

Use SELECT FOR UPDATE when reading data that will be modified to prevent race conditions.

Security Definer

Functions marked SECURITY DEFINER run with the permissions of the function owner, not the caller. This allows controlled privilege escalation for specific operations.

Audit Everything

All state-changing operations should create audit log entries for compliance and debugging.

Schema

Reference

Function Categories

Trigger Functions

`update_updated_at_column()`

`protect_director_users()`

`protect_director_insert()`

`update_stock_on_output()`

`log_audit()`

RPC Functions

`aprobar_guia()`

`rechazar_guia()`

`get_lotes_por_vencer()`

`procesar_operacion_diaria()`

Helper Functions

`get_user_role()`

Function Permissions

Error Handling

Best Practices

Transaction Safety

Locking Strategy

Security Definer

Audit Everything

Build docs developers (and LLMs) love

Schema

Reference

​Function Categories

​Trigger Functions

​update_updated_at_column()

​protect_director_users()

​protect_director_insert()

​update_stock_on_output()

​log_audit()

​RPC Functions

​aprobar_guia()

​rechazar_guia()

​get_lotes_por_vencer()

​procesar_operacion_diaria()

​Helper Functions

​get_user_role()

​Function Permissions

​Error Handling

​Best Practices

Transaction Safety

Locking Strategy

Security Definer

Audit Everything

Build docs developers (and LLMs) love

Function Categories

Trigger Functions

`update_updated_at_column()`

`protect_director_users()`

`protect_director_insert()`

`update_stock_on_output()`

`log_audit()`

RPC Functions

`aprobar_guia()`

`rechazar_guia()`

`get_lotes_por_vencer()`

`procesar_operacion_diaria()`

Helper Functions

`get_user_role()`

Function Permissions

Error Handling

Best Practices