Database Tables

Overview

CEDIS Pedidos uses PostgreSQL with Supabase, featuring 6 core tables with Row Level Security (RLS) policies. The schema supports multi-branch ordering with granular access control.

Enums

categoria_enum

Material category types:

materia_prima - Raw materials (40 items)
esencia - Fragrances/essences (82 items)
varios - Miscellaneous items (21 items)
envase_vacio - Empty containers (14 items)
color - Colors/pigments (11 items)

rol_enum

User role types:

admin - Administrator with full system access
sucursal - Branch user with restricted access

estado_pedido

Order status workflow:

borrador - Draft (editable by branch)
enviado - Submitted (awaiting approval)
aprobado - Approved (awaiting printing)
impreso - Printed (order fulfilled)

Tables

sucursales

Branch/location information for the organization.

uuid

required

Primary key, auto-generated with gen_random_uuid()

nombre

text

required

Full branch name (e.g., “Pachuca I”, “Guadalajara”)

abreviacion

text

required

Unique short code for the branch (e.g., “PAC1”, “GDL”, “CDMX”)Constraint: UNIQUE

ciudad

text

required

City where the branch is located

activa

boolean

default:"true"

Whether the branch is currently active

RLS Policy: All authenticated users can read all branches (sucursales_select)

Seed Data

INSERT INTO sucursales (nombre, abreviacion, ciudad) VALUES
  ('Pachuca I',   'PAC1', 'Pachuca'),
  ('Guadalajara', 'GDL',  'Guadalajara'),
  ('CDMX Norte',  'CDMX', 'Ciudad de México');

users

User profiles linked to Supabase Auth with role-based access control.

uuid

required

Primary key, references auth.users(id) ON DELETE CASCADE

nombre

text

required

User’s full name

text

required

User’s email addressConstraint: UNIQUE

rol

rol_enum

required

User role: admin or sucursal

sucursal_id

uuid

Foreign key to sucursales(id) ON DELETE SET NULLRequired for sucursal role users, NULL for admins

estado_cuenta

text

default:"activo"

Account status: pendiente, activo, or inactivoConstraint: CHECK IN (‘pendiente’,‘activo’,‘inactivo’)

es_superadmin

boolean

default:"false"

Flag for super administrator privileges

RLS Policies:

Users can read their own profile
Admins can read all profiles
Users can insert/update their own profile

materiales

Catalog of all materials available for ordering (168 total items).

uuid

required

Primary key, auto-generated with gen_random_uuid()

codigo

text

Optional material code (legacy identifier)Constraint: UNIQUE

nombre

text

required

Material name (e.g., “Aceite De Pino”, “Lavanda Francesa”)

categoria

categoria_enum

required

Category: materia_prima, esencia, varios, envase_vacio, or color

unidad_base

text

default:"kgs"

Base unit of measurement (e.g., “kgs”, “Lt”, “Pzas”, “Paquete”)

peso_aproximado

numeric

Approximate weight/quantity per container

envase

text

Container description (e.g., “200lts”, “25kgs”, “Caja”)

orden

integer

required

Display order within category

RLS Policy: All authenticated users can read all materials (materiales_select)

Material Counts by Category

Materias Primas: 40 items
Esencias: 82 items
Varios: 21 items
Envases Vacíos: 14 items
Colores: 11 items
Total: 168 materials

pedidos

Order headers containing delivery date, status, and metadata.

uuid

required

Primary key, auto-generated with gen_random_uuid()

codigo_pedido

text

required

Human-readable order code (e.g., “PAC1-2026-001”)Constraint: UNIQUE

sucursal_id

uuid

required

Foreign key to sucursales(id), identifies which branch placed the order

fecha_entrega

date

required

Requested delivery date

total_kilos

numeric

default:"0"

Total weight/quantity sum from pedido_detalleBusiness rule: Frontend enforces 11,500 kg limit; database function validates < 13,000 kg as absolute maximum

estado

estado_pedido

default:"borrador"

Order status: borrador, enviado, aprobado, or impreso

created_at

timestamptz

default:"now()"

Order creation timestamp

updated_at

timestamptz

default:"now()"

Last update timestamp (auto-updated via trigger)

enviado_at

timestamptz

Timestamp when order was submitted (status changed to enviado)

enviado_por

uuid

Foreign key to users(id), user who submitted the order

Indexes

CREATE INDEX idx_pedidos_sucursal  ON pedidos(sucursal_id);
CREATE INDEX idx_pedidos_estado    ON pedidos(estado);
CREATE INDEX idx_pedidos_fecha     ON pedidos(fecha_entrega);

Triggers

Auto-update updated_at:

CREATE TRIGGER trg_pedidos_updated_at
  BEFORE UPDATE ON pedidos
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

RLS Policies: See Row Level Security

pedido_detalle

Order line items linking orders to materials with quantities.

uuid

required

Primary key, auto-generated with gen_random_uuid()

pedido_id

uuid

required

Foreign key to pedidos(id) ON DELETE CASCADE

material_id

uuid

required

Foreign key to materiales(id)

cantidad_kilos

numeric

Quantity in kilograms (used for materials measured in kgs)

cantidad_solicitada

numeric

Requested quantity (used for piece-based materials)

peso_total

numeric

Total weight for this line item

lote

text

Batch/lot number (filled by admin during fulfillment)

peso

numeric

Actual weight (filled by admin during fulfillment)

Constraint: UNIQUE(pedido_id, material_id) - one row per material per order

Indexes

CREATE INDEX idx_detalle_pedido    ON pedido_detalle(pedido_id);
CREATE INDEX idx_detalle_material  ON pedido_detalle(material_id);

RLS Policies: Access follows parent pedidos table rules. See Row Level Security

solicitudes_acceso

Access requests for new user registration (approval workflow).

uuid

required

Primary key, auto-generated with gen_random_uuid()

user_id

uuid

Foreign key to users(id) ON DELETE CASCADE (set after account creation)

nombre

text

required

Requester’s full name

text

required

Requester’s email address

sucursal_id

uuid

Foreign key to sucursales(id), requested branch assignment

mensaje

text

Optional message from requester

estado

text

default:"pendiente"

Request status: pendiente, aprobado, or rechazadoConstraint: CHECK IN (‘pendiente’,‘aprobado’,‘rechazado’)

revisado_por

uuid

Foreign key to users(id), admin who reviewed the request

revisado_at

timestamptz

Timestamp when request was reviewed

created_at

timestamptz

default:"now()"

Request creation timestamp

RLS Policies:

Any authenticated user can insert
Users can read their own requests
Admins can read and update all requests

Database Functions

validate_pedido_limit(p_pedido_id uuid)

Validates that an order’s total weight is under 13,000 kg (absolute database maximum). Note: The frontend enforces a stricter 11,500 kg limit for operational safety. Returns: boolean Usage:

SELECT validate_pedido_limit('order-uuid-here');

Implementation:

CREATE OR REPLACE FUNCTION validate_pedido_limit(p_pedido_id uuid)
RETURNS boolean LANGUAGE sql SECURITY DEFINER AS $$
  SELECT COALESCE(total_kilos, 0) < 13000
  FROM pedidos WHERE id = p_pedido_id;
$$;

Relationships

Key Relationships

sucursales → users: One branch has many users (ON DELETE SET NULL)
sucursales → pedidos: One branch has many orders
pedidos → pedido_detalle: One order has many line items (ON DELETE CASCADE)
materiales → pedido_detalle: One material appears in many line items
auth.users → users: One-to-one with CASCADE delete
users → solicitudes_acceso: User can have multiple access requests

TypeScript Interfaces

See /workspace/source/src/lib/types.ts for complete type definitions:

export interface Sucursal {
    id: string
    nombre: string
    abreviacion: string
    ciudad: string
    activa: boolean
}

export interface UserProfile {
    id: string
    nombre: string
    email: string
    rol: Rol
    sucursal_id: string | null
    sucursal?: Sucursal
    estado_cuenta: EstadoCuenta
    es_superadmin: boolean
}

export interface Material {
    id: string
    codigo: string | null
    nombre: string
    categoria: Categoria
    unidad_base: string
    peso_aproximado: number | null
    envase: string | null
    orden: number
    activo: boolean
}

export interface Pedido {
    id: string
    codigo_pedido: string
    sucursal_id: string
    fecha_entrega: string
    tipo_entrega: TipoEntrega | null
    total_kilos: number
    estado: EstadoPedido
    created_at: string
    updated_at: string
    enviado_at: string | null
    enviado_por: string | null
    sucursal?: Sucursal
}

export interface PedidoDetalle {
    id: string
    pedido_id: string
    material_id: string
    cantidad_kilos: number | null
    cantidad_solicitada: number | null
    peso_total: number | null
    lote: string | null
    peso: number | null
    material?: Material
}

Architecture

Database

Deployment

Overview

Enums

categoria_enum

rol_enum

estado_pedido

Tables

sucursales

Seed Data

users

materiales

Material Counts by Category

pedidos

Indexes

Triggers

pedido_detalle

Indexes

solicitudes_acceso

Database Functions

validate_pedido_limit(p_pedido_id uuid)

Relationships

Key Relationships

TypeScript Interfaces

Build docs developers (and LLMs) love

Architecture

Database

Deployment

​Overview

​Enums

​categoria_enum

​rol_enum

​estado_pedido

​Tables

​sucursales

​Seed Data

​users

​materiales

​Material Counts by Category

​pedidos

​Indexes

​Triggers

​pedido_detalle

​Indexes

​solicitudes_acceso

​Database Functions

​validate_pedido_limit(p_pedido_id uuid)

​Relationships

​Key Relationships

​TypeScript Interfaces

Build docs developers (and LLMs) love

Overview

Enums

categoria_enum

rol_enum

estado_pedido

Tables

sucursales

Seed Data

users

materiales

Material Counts by Category

pedidos

Indexes

Triggers

pedido_detalle

Indexes

solicitudes_acceso

Database Functions

validate_pedido_limit(p_pedido_id uuid)

Relationships

Key Relationships

TypeScript Interfaces