Database Schema

usuarios — System users

Stores all system user accounts. cedula (national ID number) is used as the primary key and login credential.

CREATE TABLE `usuarios` (
  `cedula`               varchar(20)  NOT NULL,
  `password_hash`        varchar(255) NOT NULL,
  `nombres`              varchar(100) NOT NULL,
  `apellidos`            varchar(100) NOT NULL,
  `email`                varchar(100) DEFAULT NULL,
  `rol`                  enum('Administrador','Usuario') NOT NULL,
  `activo`               tinyint(1)   DEFAULT 1,
  `ultimo_acceso`        timestamp    NULL DEFAULT NULL,
  `fecha_creacion`       timestamp    NOT NULL DEFAULT current_timestamp(),
  `fecha_actualizacion`  timestamp    NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`cedula`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Column	Type	Nullable	Default	Description
`cedula`	`varchar(20)`	No	—	National ID number; primary key and login username
`password_hash`	`varchar(255)`	No	—	bcrypt hash generated with `password_hash($clave, PASSWORD_DEFAULT)`
`nombres`	`varchar(100)`	No	—	First name(s)
`apellidos`	`varchar(100)`	No	—	Last name(s)
`email`	`varchar(100)`	Yes	`NULL`	Contact email address
`rol`	`enum`	No	—	Either `Administrador` or `Usuario`
`activo`	`tinyint(1)`	Yes	`1`	Soft-delete flag; `0` disables login
`ultimo_acceso`	`timestamp`	Yes	`NULL`	Timestamp of last successful login (set by application)
`fecha_creacion`	`timestamp`	No	`current_timestamp()`	Record creation time
`fecha_actualizacion`	`timestamp`	No	`current_timestamp()`	Auto-updated on any change

Keys

PRIMARY KEY (cedula)

Seed data — one default admin is inserted with cedula = 12345678, rol = Administrador.

categorias — Asset categories (self-referential)

Holds the classification taxonomy for assets. A category may optionally have a parent category, forming a tree hierarchy (e.g., “Mobiliario” → “Sillas”).

CREATE TABLE `categorias` (
  `id`                   int(11)      NOT NULL AUTO_INCREMENT,
  `nombre`               varchar(100) NOT NULL,
  `codigo`               varchar(20)  DEFAULT NULL,
  `descripcion`          text         DEFAULT NULL,
  `categoria_padre_id`   int(11)      DEFAULT NULL,
  `activo`               tinyint(1)   DEFAULT 1,
  `fecha_creacion`       timestamp    NOT NULL DEFAULT current_timestamp(),
  `fecha_actualizacion`  timestamp    NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_categoria_codigo` (`codigo`),
  KEY `categoria_padre_id` (`categoria_padre_id`),
  CONSTRAINT `categorias_ibfk_1`
    FOREIGN KEY (`categoria_padre_id`) REFERENCES `categorias` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Column	Type	Nullable	Default	Description
`id`	`int(11)`	No	AUTO_INCREMENT	Surrogate primary key
`nombre`	`varchar(100)`	No	—	Category display name
`codigo`	`varchar(20)`	Yes	`NULL`	Short classification code (e.g. `MOB-001`); must be unique
`descripcion`	`text`	Yes	`NULL`	Free-text description
`categoria_padre_id`	`int(11)`	Yes	`NULL`	Self-referential FK; `NULL` means root-level category
`activo`	`tinyint(1)`	Yes	`1`	Soft-delete flag
`fecha_creacion`	`timestamp`	No	`current_timestamp()`	Record creation time
`fecha_actualizacion`	`timestamp`	No	`current_timestamp()`	Auto-updated on any change

Keys

PRIMARY KEY (id)
UNIQUE KEY uk_categoria_codigo (codigo)
FOREIGN KEY (categoria_padre_id) → categorias(id) ON DELETE SET NULL

estatus — Asset statuses

Lookup table for the lifecycle state of each asset. The permite_movimiento flag controls whether an asset in this status can be moved or reassigned.

CREATE TABLE `estatus` (
  `id`                  int(11)      NOT NULL AUTO_INCREMENT,
  `nombre`              varchar(50)  NOT NULL,
  `descripcion`         text         DEFAULT NULL,
  `permite_movimiento`  tinyint(1)   DEFAULT 1,
  `activo`              tinyint(1)   DEFAULT 1,
  `fecha_creacion`      timestamp    NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_estatus_nombre` (`nombre`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Column	Type	Nullable	Default	Description
`id`	`int(11)`	No	AUTO_INCREMENT	Surrogate primary key
`nombre`	`varchar(50)`	No	—	Status label; must be unique
`descripcion`	`text`	Yes	`NULL`	Human-readable explanation
`permite_movimiento`	`tinyint(1)`	Yes	`1`	`1` = asset can be transferred; `0` = movement blocked
`activo`	`tinyint(1)`	Yes	`1`	Soft-delete flag
`fecha_creacion`	`timestamp`	No	`current_timestamp()`	Record creation time

Keys

PRIMARY KEY (id)
UNIQUE KEY uk_estatus_nombre (nombre)

Seed data

id	nombre	permite_movimiento
1	Activo	1
2	En Uso	1
3	En Reparacion	0
4	Desincorporado	0
5	Extraviado	0
6	En Proceso de Desincorporacion	0

dependencias — University departments/offices

Represents the organizational units of UPTAG (rectoría, vice-rectorados, oficinas, PNFs, laboratorios, etc.). Each ubicacion belongs to exactly one dependencia.

CREATE TABLE `dependencias` (
  `id`                   int(11)      NOT NULL AUTO_INCREMENT,
  `nombre`               varchar(150) NOT NULL,
  `codigo`               varchar(20)  DEFAULT NULL,
  `tipo`                 enum('Administrativa','Academica','PNF','Laboratorio','Otra') DEFAULT 'Otra',
  `responsable_nombre`   varchar(150) DEFAULT NULL,
  `responsable_cedula`   varchar(20)  DEFAULT NULL,
  `direccion`            varchar(255) DEFAULT NULL,
  `ciudad`               varchar(100) DEFAULT NULL,
  `estado`               varchar(100) DEFAULT NULL,
  `activo`               tinyint(1)   DEFAULT 1,
  `fecha_creacion`       timestamp    NOT NULL DEFAULT current_timestamp(),
  `fecha_actualizacion`  timestamp    NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_dependencia_codigo` (`codigo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Column	Type	Nullable	Default	Description
`id`	`int(11)`	No	AUTO_INCREMENT	Surrogate primary key
`nombre`	`varchar(150)`	No	—	Full name of the department
`codigo`	`varchar(20)`	Yes	`NULL`	Short code (e.g. `01`); must be unique
`tipo`	`enum`	Yes	`'Otra'`	Classification: `Administrativa`, `Academica`, `PNF`, `Laboratorio`, or `Otra`
`responsable_nombre`	`varchar(150)`	Yes	`NULL`	Name of the department head
`responsable_cedula`	`varchar(20)`	Yes	`NULL`	National ID of the department head
`direccion`	`varchar(255)`	Yes	`NULL`	Physical address
`ciudad`	`varchar(100)`	Yes	`NULL`	City
`estado`	`varchar(100)`	Yes	`NULL`	Venezuelan state
`activo`	`tinyint(1)`	Yes	`1`	Soft-delete flag
`fecha_creacion`	`timestamp`	No	`current_timestamp()`	Record creation time
`fecha_actualizacion`	`timestamp`	No	`current_timestamp()`	Auto-updated on any change

Keys

PRIMARY KEY (id)
UNIQUE KEY uk_dependencia_codigo (codigo)

ubicaciones — Physical locations

Represents specific physical spaces (offices, rooms, labs) within a dependencia. The columns responsable, telefono, and email were added via agregar_columnas_ubicaciones.sql.

CREATE TABLE `ubicaciones` (
  `id`                   int(11)      NOT NULL AUTO_INCREMENT,
  `dependencia_id`       int(11)      NOT NULL,
  `nombre`               varchar(150) NOT NULL,
  `descripcion`          text         DEFAULT NULL,
  `responsable`          varchar(200) DEFAULT NULL,   -- added via ALTER TABLE
  `telefono`             varchar(50)  DEFAULT NULL,   -- added via ALTER TABLE
  `email`                varchar(100) DEFAULT NULL,   -- added via ALTER TABLE
  `activo`               tinyint(1)   DEFAULT 1,
  `fecha_creacion`       timestamp    NOT NULL DEFAULT current_timestamp(),
  `fecha_actualizacion`  timestamp    NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `dependencia_id` (`dependencia_id`),
  CONSTRAINT `ubicaciones_ibfk_1`
    FOREIGN KEY (`dependencia_id`) REFERENCES `dependencias` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Column	Type	Nullable	Default	Description
`id`	`int(11)`	No	AUTO_INCREMENT	Surrogate primary key
`dependencia_id`	`int(11)`	No	—	FK → `dependencias.id`; every location belongs to one department
`nombre`	`varchar(150)`	No	—	Location name (e.g. `OFICINA DEL RECTOR`)
`descripcion`	`text`	Yes	`NULL`	Additional description or internal code
`responsable`	`varchar(200)`	Yes	`NULL`	Name of the person responsible for this location
`telefono`	`varchar(50)`	Yes	`NULL`	Contact phone number
`email`	`varchar(100)`	Yes	`NULL`	Contact email
`activo`	`tinyint(1)`	Yes	`1`	Soft-delete flag
`fecha_creacion`	`timestamp`	No	`current_timestamp()`	Record creation time
`fecha_actualizacion`	`timestamp`	No	`current_timestamp()`	Auto-updated on any change

Keys

PRIMARY KEY (id)
FOREIGN KEY (dependencia_id) → dependencias(id)

The responsable, telefono, and email columns were added after initial creation via agregar_columnas_ubicaciones.sql. They are already included in the main schema when using the latest bd_bienes_nacionales.sql.

bienes — Main assets table (central table)

This is the core table of the system. Every inventoried asset has exactly one row here. It references categorias, ubicaciones, adquisiciones, donaciones, and estatus.

CREATE TABLE `bienes` (
  `id`                         int(11)        NOT NULL AUTO_INCREMENT,
  `codigo_bien_nacional`       varchar(50)    NOT NULL,
  `codigo_anterior`            varchar(50)    DEFAULT NULL,
  `categoria_id`               int(11)        NOT NULL,
  `ubicacion_id`               int(11)        DEFAULT NULL,
  `adquisicion_id`             int(11)        DEFAULT NULL,
  `donacion_id`                int(11)        DEFAULT NULL,
  `descripcion`                text           NOT NULL,
  `marca`                      varchar(100)   DEFAULT NULL,
  `modelo`                     varchar(100)   DEFAULT NULL,
  `serial`                     varchar(100)   DEFAULT NULL,
  `color`                      varchar(50)    DEFAULT NULL,
  `dimensiones`                varchar(100)   DEFAULT NULL,
  `valor_adquisicion`          decimal(18,2)  DEFAULT NULL,
  `valor_actual`               decimal(18,2)  DEFAULT NULL,
  `vida_util_anos`             int(11)        DEFAULT NULL,
  `estatus_id`                 int(11)        NOT NULL,
  `observaciones`              text           DEFAULT NULL,
  `fecha_incorporacion`        date           NOT NULL,
  `fecha_desincorporacion`     date           DEFAULT NULL,
  `motivo_desincorporacion`    text           DEFAULT NULL,
  `documento_desincorporacion` varchar(255)   DEFAULT NULL,
  `activo`                     tinyint(1)     DEFAULT 1,
  `fecha_creacion`             timestamp      NOT NULL DEFAULT current_timestamp(),
  `fecha_actualizacion`        timestamp      NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_codigo_bien` (`codigo_bien_nacional`),
  KEY `categoria_id`    (`categoria_id`),
  KEY `adquisicion_id`  (`adquisicion_id`),
  KEY `donacion_id`     (`donacion_id`),
  KEY `estatus_id`      (`estatus_id`),
  KEY `idx_ubicacion_id`(`ubicacion_id`),
  CONSTRAINT `bienes_ibfk_1` FOREIGN KEY (`categoria_id`)   REFERENCES `categorias`   (`id`),
  CONSTRAINT `bienes_ibfk_2` FOREIGN KEY (`adquisicion_id`) REFERENCES `adquisiciones` (`id`) ON DELETE SET NULL,
  CONSTRAINT `bienes_ibfk_3` FOREIGN KEY (`donacion_id`)    REFERENCES `donaciones`    (`id`) ON DELETE SET NULL,
  CONSTRAINT `bienes_ibfk_4` FOREIGN KEY (`estatus_id`)     REFERENCES `estatus`       (`id`),
  CONSTRAINT `fk_bienes_ubicacion` FOREIGN KEY (`ubicacion_id`) REFERENCES `ubicaciones` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Column	Type	Nullable	Default	Description
`id`	`int(11)`	No	AUTO_INCREMENT	Surrogate primary key
`codigo_bien_nacional`	`varchar(50)`	No	—	Official national asset code; must be unique
`codigo_anterior`	`varchar(50)`	Yes	`NULL`	Legacy code from a prior system
`categoria_id`	`int(11)`	No	—	FK → `categorias.id`; required
`ubicacion_id`	`int(11)`	Yes	`NULL`	FK → `ubicaciones.id`; `NULL` if location unknown
`adquisicion_id`	`int(11)`	Yes	`NULL`	FK → `adquisiciones.id`; set when acquired by purchase or transfer
`donacion_id`	`int(11)`	Yes	`NULL`	FK → `donaciones.id`; set when asset came via donation
`descripcion`	`text`	No	—	Full description of the asset
`marca`	`varchar(100)`	Yes	`NULL`	Manufacturer brand
`modelo`	`varchar(100)`	Yes	`NULL`	Model name or number
`serial`	`varchar(100)`	Yes	`NULL`	Serial number
`color`	`varchar(50)`	Yes	`NULL`	Primary color
`dimensiones`	`varchar(100)`	Yes	`NULL`	Physical dimensions (free text)
`valor_adquisicion`	`decimal(18,2)`	Yes	`NULL`	Original acquisition value in BsF/USD
`valor_actual`	`decimal(18,2)`	Yes	`NULL`	Current estimated value
`vida_util_anos`	`int(11)`	Yes	`NULL`	Expected useful life in years
`estatus_id`	`int(11)`	No	—	FK → `estatus.id`; required
`observaciones`	`text`	Yes	`NULL`	Free-text notes
`fecha_incorporacion`	`date`	No	—	Date the asset was formally incorporated
`fecha_desincorporacion`	`date`	Yes	`NULL`	Date the asset was decommissioned
`motivo_desincorporacion`	`text`	Yes	`NULL`	Reason for decommissioning
`documento_desincorporacion`	`varchar(255)`	Yes	`NULL`	File path or reference number of decommission document
`activo`	`tinyint(1)`	Yes	`1`	Soft-delete flag
`fecha_creacion`	`timestamp`	No	`current_timestamp()`	Record creation time
`fecha_actualizacion`	`timestamp`	No	`current_timestamp()`	Auto-updated on any change

Keys

PRIMARY KEY (id)
UNIQUE KEY uk_codigo_bien (codigo_bien_nacional)
FOREIGN KEY (categoria_id) → categorias(id) — required, no cascade
FOREIGN KEY (estatus_id) → estatus(id) — required, no cascade
FOREIGN KEY (ubicacion_id) → ubicaciones(id) ON DELETE SET NULL
FOREIGN KEY (adquisicion_id) → adquisiciones(id) ON DELETE SET NULL
FOREIGN KEY (donacion_id) → donaciones(id) ON DELETE SET NULL

codigo_bien_nacional has a unique constraint. Attempting to insert a duplicate value will produce a MySQL error 1062 Duplicate entry. Always validate this field before inserting.

movimientos — Asset movement history

Records every movement event for an asset: incorporations, transfers, decommissions, assignments, repairs, and returns. All movements are append-only — no rows are ever updated.

CREATE TABLE `movimientos` (
  `id`                      int(11)  NOT NULL AUTO_INCREMENT,
  `bien_id`                 int(11)  NOT NULL,
  `tipo_movimiento`         enum('Incorporacion','Traslado','Desincorporacion','Asignacion','Reparacion','Devolucion') NOT NULL,
  `ubicacion_origen_id`     int(11)  DEFAULT NULL,
  `ubicacion_destino_id`    int(11)  DEFAULT NULL,
  `responsable_origen_id`   int(11)  DEFAULT NULL,
  `responsable_destino_id`  int(11)  DEFAULT NULL,
  `fecha_movimiento`        date     NOT NULL,
  `razon`                   text     NOT NULL,
  `numero_documento`        varchar(50)  DEFAULT NULL,
  `observaciones`           text     DEFAULT NULL,
  `usuario_registro`        varchar(20)  DEFAULT NULL,
  `fecha_creacion`          timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  CONSTRAINT `movimientos_ibfk_1` FOREIGN KEY (`bien_id`)                REFERENCES `bienes`       (`id`),
  CONSTRAINT `movimientos_ibfk_2` FOREIGN KEY (`ubicacion_origen_id`)    REFERENCES `ubicaciones`  (`id`) ON DELETE SET NULL,
  CONSTRAINT `movimientos_ibfk_3` FOREIGN KEY (`ubicacion_destino_id`)   REFERENCES `ubicaciones`  (`id`) ON DELETE SET NULL,
  CONSTRAINT `movimientos_ibfk_4` FOREIGN KEY (`responsable_origen_id`)  REFERENCES `responsables` (`id`) ON DELETE SET NULL,
  CONSTRAINT `movimientos_ibfk_5` FOREIGN KEY (`responsable_destino_id`) REFERENCES `responsables` (`id`) ON DELETE SET NULL,
  CONSTRAINT `movimientos_ibfk_6` FOREIGN KEY (`usuario_registro`)       REFERENCES `usuarios`     (`cedula`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Column	Type	Nullable	Default	Description
`id`	`int(11)`	No	AUTO_INCREMENT	Surrogate primary key
`bien_id`	`int(11)`	No	—	FK → `bienes.id`; the asset being moved
`tipo_movimiento`	`enum`	No	—	One of: `Incorporacion`, `Traslado`, `Desincorporacion`, `Asignacion`, `Reparacion`, `Devolucion`
`ubicacion_origen_id`	`int(11)`	Yes	`NULL`	FK → `ubicaciones.id`; origin location
`ubicacion_destino_id`	`int(11)`	Yes	`NULL`	FK → `ubicaciones.id`; destination location
`responsable_origen_id`	`int(11)`	Yes	`NULL`	FK → `responsables.id`; person releasing the asset
`responsable_destino_id`	`int(11)`	Yes	`NULL`	FK → `responsables.id`; person receiving the asset
`fecha_movimiento`	`date`	No	—	Date the movement occurred
`razon`	`text`	No	—	Reason or justification for the movement
`numero_documento`	`varchar(50)`	Yes	`NULL`	Supporting document or memo number
`observaciones`	`text`	Yes	`NULL`	Additional notes
`usuario_registro`	`varchar(20)`	Yes	`NULL`	FK → `usuarios.cedula`; who recorded this movement
`fecha_creacion`	`timestamp`	No	`current_timestamp()`	Record creation time

Keys

PRIMARY KEY (id)
FOREIGN KEY (bien_id) → bienes(id) — required, no cascade
FOREIGN KEY (ubicacion_origen_id) → ubicaciones(id) ON DELETE SET NULL
FOREIGN KEY (ubicacion_destino_id) → ubicaciones(id) ON DELETE SET NULL
FOREIGN KEY (responsable_origen_id) → responsables(id) ON DELETE SET NULL
FOREIGN KEY (responsable_destino_id) → responsables(id) ON DELETE SET NULL
FOREIGN KEY (usuario_registro) → usuarios(cedula) ON DELETE SET NULL

auditoria — System audit log

Immutable log of all data-modifying events in the system, including login attempts. Written via prepared statements in application code — never modified after insertion.

CREATE TABLE `auditoria` (
  `id`               int(11)      NOT NULL AUTO_INCREMENT,
  `tabla_afectada`   varchar(50)  NOT NULL,
  `accion`           enum('INSERT','UPDATE','DELETE') NOT NULL,
  `usuario_cedula`   varchar(20)  DEFAULT NULL,
  `datos_anteriores` varchar(2000) DEFAULT NULL,
  `datos_nuevos`     varchar(2000) DEFAULT NULL,
  `ip_address`       varchar(45)  DEFAULT NULL,
  `fecha_accion`     timestamp    NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `usuario_cedula` (`usuario_cedula`),
  CONSTRAINT `auditoria_ibfk_1`
    FOREIGN KEY (`usuario_cedula`) REFERENCES `usuarios` (`cedula`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Column	Type	Nullable	Default	Description
`id`	`int(11)`	No	AUTO_INCREMENT	Surrogate primary key
`tabla_afectada`	`varchar(50)`	No	—	Name of the table the action targeted (e.g. `usuarios`, `bienes`)
`accion`	`enum`	No	—	Type of change: `INSERT`, `UPDATE`, or `DELETE`
`usuario_cedula`	`varchar(20)`	Yes	`NULL`	FK → `usuarios.cedula`; `NULL` if user was deleted
`datos_anteriores`	`varchar(2000)`	Yes	`NULL`	JSON snapshot of data before the change
`datos_nuevos`	`varchar(2000)`	Yes	`NULL`	JSON snapshot of data after the change (also used for login event payloads)
`ip_address`	`varchar(45)`	Yes	`NULL`	IPv4 or IPv6 address of the request; supports up to 45 chars for IPv6
`fecha_accion`	`timestamp`	No	`current_timestamp()`	Timestamp of the event

Keys

PRIMARY KEY (id)
FOREIGN KEY (usuario_cedula) → usuarios(cedula) ON DELETE SET NULL

Login events (successful and failed) are stored with tabla_afectada = 'usuarios' and accion = 'INSERT'. The datos_nuevos column holds a JSON payload such as {"accion": "inicio_sesion_exitoso"} or {"intento": "sesion_fallida", "motivo": "credenciales_incorrectas"}.

proveedores — Suppliers

Stores supplier records used when registering purchases via adquisiciones.

Column	Type	Nullable	Default	Description
`id`	`int(11)`	No	AUTO_INCREMENT	Surrogate primary key
`razon_social`	`varchar(200)`	No	—	Legal name of the supplier
`rif`	`varchar(20)`	Yes	`NULL`	Tax ID (RIF); must be unique
`direccion`	`varchar(255)`	Yes	`NULL`	Address
`telefono`	`varchar(50)`	Yes	`NULL`	Phone number
`email`	`varchar(100)`	Yes	`NULL`	Email
`contacto_nombre`	`varchar(150)`	Yes	`NULL`	Contact person’s name
`tipo_proveedor`	`enum`	No	—	`Oficina de Compras` or `Ingreso por parte de la Universidad`
`activo`	`tinyint(1)`	Yes	`1`	Soft-delete flag
`fecha_creacion`	`timestamp`	No	`current_timestamp()`	Record creation time
`fecha_actualizacion`	`timestamp`	No	`current_timestamp()`	Auto-updated on any change

Keys

PRIMARY KEY (id)
UNIQUE KEY uk_proveedor_rif (rif)

donaciones — Donations

Tracks donation records. A bien can link to a donacion via bienes.donacion_id to record that it was received as a gift.

Column	Type	Nullable	Default	Description
`id`	`int(11)`	No	AUTO_INCREMENT	Surrogate primary key
`donante_nombre`	`varchar(200)`	No	—	Name of the donor
`donante_rif`	`varchar(20)`	Yes	`NULL`	Donor’s RIF (if legal entity)
`donante_cedula`	`varchar(20)`	Yes	`NULL`	Donor’s national ID (if natural person)
`donante_direccion`	`varchar(255)`	Yes	`NULL`	Donor address
`donante_telefono`	`varchar(50)`	Yes	`NULL`	Donor phone
`donante_email`	`varchar(100)`	Yes	`NULL`	Donor email
`fecha_donacion`	`date`	No	—	Date the donation was received
`tipo_donante`	`enum`	No	—	`Persona Natural`, `Persona Juridica`, or `Institucion`
`documento_soporte`	`varchar(255)`	Yes	`NULL`	File path to supporting document
`observaciones`	`text`	Yes	`NULL`	Notes
`fecha_creacion`	`timestamp`	No	`current_timestamp()`	Record creation time
`fecha_actualizacion`	`timestamp`	No	`current_timestamp()`	Auto-updated on any change

Keys

PRIMARY KEY (id)

adquisiciones — Acquisition records

Records the procurement event for an asset (purchase, own-income, or transfer). Linked to proveedores and referenced by bienes.adquisicion_id.

Column	Type	Nullable	Default	Description
`id`	`int(11)`	No	AUTO_INCREMENT	Surrogate primary key
`tipo_adquisicion`	`enum`	No	—	`Compra`, `Ingreso Propio`, or `Traspaso`
`proveedor_id`	`int(11)`	Yes	`NULL`	FK → `proveedores.id`; `NULL` for own-income acquisitions
`numero_factura`	`varchar(50)`	Yes	`NULL`	Invoice number
`numero_orden_compra`	`varchar(50)`	Yes	`NULL`	Purchase order number
`fecha_adquisicion`	`date`	No	—	Acquisition date
`monto_total`	`decimal(18,2)`	Yes	`NULL`	Total amount paid
`observaciones`	`text`	Yes	`NULL`	Notes
`documento_soporte`	`varchar(255)`	Yes	`NULL`	File path to supporting document
`fecha_creacion`	`timestamp`	No	`current_timestamp()`	Record creation time
`fecha_actualizacion`	`timestamp`	No	`current_timestamp()`	Auto-updated on any change

Keys

PRIMARY KEY (id)
FOREIGN KEY (proveedor_id) → proveedores(id) ON DELETE SET NULL

responsables — Asset custodians

Stores people responsible for assets or locations. The ubicacion_id column was added via agregar_fk_responsables_ubicaciones.sql.

Column	Type	Nullable	Default	Description
`id`	`int(11)`	No	AUTO_INCREMENT	Surrogate primary key
`cedula`	`varchar(20)`	No	—	National ID; must be unique
`nombres`	`varchar(100)`	No	—	First name(s)
`apellidos`	`varchar(100)`	No	—	Last name(s)
`cargo`	`varchar(100)`	Yes	`NULL`	Job title or position
`dependencia_id`	`int(11)`	Yes	`NULL`	FK → `dependencias.id`
`ubicacion_id`	`int(11)`	Yes	`NULL`	FK → `ubicaciones.id`; added via migration
`telefono`	`varchar(50)`	Yes	`NULL`	Phone number
`email`	`varchar(100)`	Yes	`NULL`	Email
`activo`	`tinyint(1)`	Yes	`1`	Soft-delete flag
`fecha_creacion`	`timestamp`	No	`current_timestamp()`	Record creation time
`fecha_actualizacion`	`timestamp`	No	`current_timestamp()`	Auto-updated on any change

Keys

PRIMARY KEY (id)
UNIQUE KEY uk_responsable_cedula (cedula)
FOREIGN KEY (dependencia_id) → dependencias(id) ON DELETE SET NULL
FOREIGN KEY (ubicacion_id) → ubicaciones(id) ON DELETE SET NULL (via fk_responsables_ubicacion)

control_perceptivo — Physical verification records

Records periodic physical inspections of assets, capturing their condition and verified location at a point in time.

Column	Type	Nullable	Default	Description
`id`	`int(11)`	No	AUTO_INCREMENT	Surrogate primary key
`bien_id`	`int(11)`	No	—	FK → `bienes.id`; asset being inspected
`fecha_control`	`date`	No	—	Date of the inspection
`ubicacion_fisica_verificada_id`	`int(11)`	Yes	`NULL`	FK → `ubicaciones.id`; where the asset was physically found
`responsable_verificado_id`	`int(11)`	Yes	`NULL`	FK → `responsables.id`; custodian at time of inspection
`condicion`	`enum`	No	—	`Excelente`, `Bueno`, `Regular`, `Malo`, or `No Localizado`
`observaciones`	`text`	Yes	`NULL`	Notes from the inspector
`verificador_nombre`	`varchar(150)`	Yes	`NULL`	Name of the inspector
`verificador_cedula`	`varchar(20)`	Yes	`NULL`	National ID of the inspector
`firma_digital`	`varchar(255)`	Yes	`NULL`	Path or hash for digital signature
`fecha_creacion`	`timestamp`	No	`current_timestamp()`	Record creation time

Keys

PRIMARY KEY (id)
FOREIGN KEY (bien_id) → bienes(id)
FOREIGN KEY (ubicacion_fisica_verificada_id) → ubicaciones(id) ON DELETE SET NULL
FOREIGN KEY (responsable_verificado_id) → responsables(id) ON DELETE SET NULL

Introducción

Primeros Pasos

Gestión de Bienes

Organización

Reportes

Administración

Referencia

Entity Relationship Overview

Tables

Build docs developers (and LLMs) love

Introducción

Primeros Pasos

Gestión de Bienes

Organización

Reportes

Administración

Referencia

​Entity Relationship Overview

​Tables

Build docs developers (and LLMs) love

Entity Relationship Overview

Tables