Skip to main content

​
Overview

The Procurement Calendar uses PostgreSQL via Supabase with a normalized relational schema. The database consists of catalog tables, a main requisitions table, an audit history table, and user profiles.
All tables use UUID primary keys generated with the uuid-ossp extension.

​
Schema Architecture

The database is organized into several logical groups:

Catalog Tables

Reference data for suppliers, products, presentations, destinations, statuses, and units

Core Tables

Requisitions (main business entity) and user profiles

Audit Tables

Change history tracking for all requisition modifications

Security

Row Level Security policies for role-based access control

​
Catalog Tables

These tables store reference data used throughout the application. All catalog tables share a common structure:

​
Proveedores (Suppliers)

Stores supplier/vendor information. 
CREATE TABLE proveedores (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  nombre      TEXT NOT NULL,
  activo      BOOLEAN NOT NULL DEFAULT TRUE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

export interface Proveedor {
    id: string
    nombre: string
    activo: boolean
    created_at: string
}

​
Productos (Products)

Catalog of products that can be requisitioned. 
CREATE TABLE productos (
  id           UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  nombre       TEXT NOT NULL,
  descripcion  TEXT,
  activo       BOOLEAN NOT NULL DEFAULT TRUE,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

export interface Producto {
    id: string
    nombre: string
    descripcion: string | null
    activo: boolean
    created_at: string
}

​
Presentaciones (Presentations)

Packaging or presentation formats (e.g., “Tambor 200 L”, “Saco 25 kg”). 
CREATE TABLE presentaciones (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  nombre      TEXT NOT NULL,
  activo      BOOLEAN NOT NULL DEFAULT TRUE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

​
Destinos (Destinations)

Warehouse locations or production lines where products are delivered. 
CREATE TABLE destinos (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  nombre      TEXT NOT NULL,
  activo      BOOLEAN NOT NULL DEFAULT TRUE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

​
Estatus (Status)

Requisition status values with color coding for UI display. 
CREATE TABLE estatus (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  nombre      TEXT NOT NULL,
  color_hex   TEXT NOT NULL DEFAULT '#6366F1',
  activo      BOOLEAN NOT NULL DEFAULT TRUE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

INSERT INTO estatus (nombre, color_hex) VALUES
  ('Pendiente',    '#F59E0B'),
  ('Confirmado',   '#3B82F6'),
  ('En Tránsito',  '#8B5CF6'),
  ('Recibido',     '#10B981'),
  ('Cancelado',    '#EF4444'),
  ('En Revisión',  '#F97316');

​
Unidades (Units)

Measurement units for quantities (kg, L, pzs, etc.). 
CREATE TABLE unidades (
  id           UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  nombre       TEXT NOT NULL,
  abreviatura  TEXT NOT NULL,
  activo       BOOLEAN NOT NULL DEFAULT TRUE,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

INSERT INTO unidades (nombre, abreviatura) VALUES
  ('Kilogramos',  'kg'),
  ('Litros',      'L'),
  ('Piezas',      'pzs'),
  ('Toneladas',   'ton'),
  ('Cajas',       'caj'),
  ('Sacos',       'sac'),
  ('Tambos',      'tam'),
  ('Gramos',      'g');

​
User Tables

​
Profiles

Extends Supabase auth.users with application-specific profile data. 
CREATE TABLE profiles (
  id              UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  nombre_completo TEXT,
  rol             user_role NOT NULL DEFAULT 'consulta',
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
Profiles are automatically created when a user signs up via the handle_new_user() trigger function.

​
User Roles Enum

CREATE TYPE user_role AS ENUM ('admin', 'coordinadora', 'consulta');
  • admin: Full system access including user management and all CRUD operations
  • coordinadora: Can create, read, and update requisitions; view history
  • consulta: Read-only access to requisitions and catalogs

​
Auto-Create Profile Trigger

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

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

​
Main Table: Requisiciones

The core table storing all requisition records. 
CREATE TABLE requisiciones (
  id                   UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  fecha_recepcion      DATE NOT NULL,
  proveedor_id         UUID NOT NULL REFERENCES proveedores(id),
  producto_id          UUID NOT NULL REFERENCES productos(id),
  presentacion_id      UUID NOT NULL REFERENCES presentaciones(id),
  destino_id           UUID NOT NULL REFERENCES destinos(id),
  estatus_id           UUID NOT NULL REFERENCES estatus(id),
  cantidad_solicitada  NUMERIC(10, 2) NOT NULL CHECK (cantidad_solicitada > 0),
  unidad_cantidad_id   UUID NOT NULL REFERENCES unidades(id),
  numero_oc            TEXT,
  comentarios          TEXT,
  created_by           UUID NOT NULL REFERENCES auth.users(id),
  created_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at           TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

​
Foreign Key Relationships

proveedor_id

References proveedores(id) - The supplier

producto_id

References productos(id) - The product being requisitioned

presentacion_id

References presentaciones(id) - Package format

destino_id

References destinos(id) - Delivery destination

estatus_id

References estatus(id) - Current status

unidad_cantidad_id

References unidades(id) - Unit of measurement

created_by

References auth.users(id) - User who created the record

​
Performance Indexes

CREATE INDEX idx_requisiciones_fecha ON requisiciones(fecha_recepcion);
CREATE INDEX idx_requisiciones_proveedor ON requisiciones(proveedor_id);
CREATE INDEX idx_requisiciones_estatus ON requisiciones(estatus_id);
CREATE INDEX idx_requisiciones_destino ON requisiciones(destino_id);
Indexes are created on frequently queried columns to optimize filtering and sorting operations.

​
Auto-Update Timestamp

The updated_at field is automatically maintained via trigger: 
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_requisiciones_updated_at
  BEFORE UPDATE ON requisiciones
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

​
Audit Table: Requisiciones Historial

Tracks all changes made to requisitions for audit purposes. 
CREATE TABLE requisiciones_historial (
  id                UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  requisicion_id    UUID NOT NULL REFERENCES requisiciones(id) ON DELETE CASCADE,
  campo_modificado  TEXT NOT NULL,
  valor_anterior    TEXT,
  valor_nuevo       TEXT,
  usuario_id        UUID NOT NULL REFERENCES auth.users(id),
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_historial_requisicion ON requisiciones_historial(requisicion_id);

export interface RequisicionHistorial {
    id: string
    requisicion_id: string
    campo_modificado: string
    valor_anterior: string | null
    valor_nuevo: string | null
    usuario_id: string
    created_at: string
    profiles?: Profile
}
History records are automatically deleted when their parent requisition is deleted due to the ON DELETE CASCADE constraint.

​
Database Extensions

The schema requires the following PostgreSQL extensions: 
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
This extension provides the uuid_generate_v4() function used for generating UUIDs.

​
Next Steps

Data Model

Explore entity relationships and TypeScript types

Row Level Security

Learn about access control policies

Build docs developers (and LLMs) love

Get started for freeTalk to us