Overview The Procurement Calendar data model is built around a central Requisicion entity that references multiple catalog tables. This normalized design ensures data consistency and simplifies maintenance.
Entity Relationship Diagram The data model follows this structure:
┌─────────────┐ │ auth.users │ └──────┬──────┘ │ ├──────────────────┐ │ │ ▼ ▼ ┌─────────────┐ ┌──────────────┐ │ profiles │ │requisiciones │◄───────┐ └─────────────┘ └──────┬───────┘ │ │ │ ┌───────────────────┼────────┐ │ │ │ │ │ ▼ ▼ ▼ │ ┌──────────┐ ┌──────────┐ ┌──────────────────────┐ │productos │ │estatus │ │requisiciones_historial│ └──────────┘ └──────────┘ └──────────────────────┘ ┌──────────────┐ │proveedores │──────────────────┘ └──────────────┘ ┌──────────────┐ │presentaciones│──────────────────┘ └──────────────┘ ┌──────────────┐ │destinos │──────────────────┘ └──────────────┘ ┌──────────────┐ │unidades │──────────────────┘ └──────────────┘
Each requisition references 6 catalog tables plus the user who created it, forming a star schema pattern.
Core Entities Requisicion (Requisition) The main business entity representing a purchase requisition.
export interface Requisicion { id : string fecha_recepcion : string proveedor_id : string producto_id : string presentacion_id : string destino_id : string estatus_id : string cantidad_solicitada : number unidad_cantidad_id : string numero_oc : string | null requisicion_numero : string | null fecha_oc : string | null fecha_solicitada_entrega : string | null fecha_confirmada : string | null fecha_entregado : string | null cantidad_entregada : number | null factura_remision : string | null comentarios : string | null created_by : string created_at : string updated_at : string // Joined relations proveedor ?: Proveedor producto ?: Producto presentacion ?: Presentacion destino ?: Destino estatus ?: Estatus unidad_cantidad ?: Unidad }
Field Type Description idUUID Primary key fecha_recepcionDate Date the requisition was received proveedor_idUUID Foreign key to supplier producto_idUUID Foreign key to product presentacion_idUUID Foreign key to presentation format destino_idUUID Foreign key to destination estatus_idUUID Foreign key to current status cantidad_solicitadaNumeric Requested quantity (must be > 0) unidad_cantidad_idUUID Foreign key to unit of measurement numero_ocText Purchase order number (optional) comentariosText Additional comments (optional) created_byUUID Foreign key to user who created record created_atTimestamp Record creation timestamp updated_atTimestamp Last update timestamp (auto-updated)
The optional relation properties (proveedor?, producto?, etc.) are populated when using Supabase joins in queries.
Requisicion Historial (Audit History) Tracks all changes made to requisitions.
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 }
Field Type Description idUUID Primary key requisicion_idUUID Foreign key to requisition being tracked campo_modificadoText Name of the field that was changed valor_anteriorText Previous value (serialized as text) valor_nuevoText New value (serialized as text) usuario_idUUID User who made the change created_atTimestamp When the change occurred
Catalog Entities All catalog entities share a similar structure with common fields.
Proveedor (Supplier) export interface Proveedor { id : string nombre : string activo : boolean created_at : string }
Producto (Product) export interface Producto { id : string nombre : string descripcion : string | null activo : boolean created_at : string }
Products include an optional descripcion field for additional details like chemical composition.
Presentacion (Presentation) export interface Presentacion { id : string nombre : string activo : boolean created_at : string }
Example values : “Tambor 200 L”, “Saco 25 kg”, “Granel”Destino (Destination) export interface Destino { id : string nombre : string activo : boolean created_at : string }
Example values : “Almacén General”, “Línea de Producción 1”, “Planta Amozoc”Estatus (Status) export interface Estatus { id : string nombre : string color_hex : string activo : boolean created_at : string }
Status Color Hex Description Pendiente #F59E0BRequisition pending review Confirmado #3B82F6Order confirmed with supplier En Tránsito #8B5CF6Shipment in transit Recibido #10B981Product received at destination Cancelado #EF4444Requisition cancelled En Revisión #F97316Under review or audit
Unidad (Unit) export interface Unidad { id : string nombre : string abreviatura : string activo : boolean created_at : string }
Unit Abbreviation Kilogramos kg Litros L Piezas pzs Toneladas ton Cajas caj Sacos sac Tambos tam Gramos g
User Management Profile Extends Supabase authentication with application-specific data.
export interface Profile { id : string nombre_completo : string | null email : string | null rol : UserRole created_at : string updated_at : string }
User Roles export type UserRole = 'admin' | 'coordinadora' | 'laboratorio' | 'cedis' | 'pendiente' | 'consulta'
admin Full system access and user management
coordinadora Create and update requisitions
consulta Read-only access to data
The database schema defines only 'admin', 'coordinadora', and 'consulta' in the enum. Additional roles in TypeScript may be used for future features.
Used when creating or updating requisitions.
export interface RequisicionFormData { fecha_recepcion : string proveedor_id : string producto_id : string presentacion_id : string destino_id : string estatus_id : string cantidad_solicitada : number unidad_cantidad_id : string numero_oc ?: string | null requisicion_numero ?: string | null fecha_oc ?: string | null fecha_solicitada_entrega ?: string | null fecha_confirmada ?: string | null fecha_entregado ?: string | null cantidad_entregada ?: number | null factura_remision ?: string | null comentarios ?: string | null }
Form data omits system-managed fields like id, created_by, created_at, and updated_at.
Requisicion Filters Used for filtering requisitions in list views.
export interface RequisicionFilters { proveedor_id ?: string destino_id ?: string estatus_id ?: string fecha_desde ?: string fecha_hasta ?: string search ?: string }
Calendar Integration Calendar Event Transforms requisition data for FullCalendar display.
export interface CalendarEvent { id : string title : string start : string backgroundColor : string borderColor : string extendedProps : { requisicion : Requisicion proveedor_nombre : string estatus_nombre : string estatus_color : string } }
The backgroundColor and borderColor are derived from the estatus.color_hex field.
Aggregated Types Catalogos Convenience type for passing all catalog data together.
export interface Catalogos { proveedores : Proveedor [] productos : Producto [] presentaciones : Presentacion [] destinos : Destino [] estatus : Estatus [] unidades : Unidad [] }
// Fetch all catalogs at once const catalogos : Catalogos = await fetchAllCatalogs (); // Use in components < ProveedorSelect options = {catalogos. proveedores } /> < EstatusSelect options = {catalogos. estatus } />
Relationships Summary
One-to-Many Each catalog entry can be referenced by many requisitions
Many-to-One Each requisition references exactly one entry from each catalog
Cascade Deletes Deleting a requisition cascades to its history records
Foreign Key Constraints All relationships enforced at database level
Deleting catalog entries that are referenced by requisitions will fail due to foreign key constraints. Mark items as inactive (activo = false) instead.
Next Steps
Database Schema View the complete SQL schema
Row Level Security Learn about access control policies
