Database Schema & Core Tables
Relevant source files
The following files were used as context for generating this wiki page:
The IT Management System utilizes a MySQL 8.0+ database to manage IT assets, personnel, and infrastructure across a multi-tenant architecture. The schema is designed around a company-scoped data model where nearly every core table includes a company_id to ensure strict data isolation between different organizations. The primary source of truth for the schema is the database.sql file, which defines approximately 88 tables ranging from asset management to support ticketing and audit logging.
The system relies on the MySQLi extension (without PDO) and emphasizes referential integrity through foreign keys and database triggers. These triggers are essential for automated tasks such as generating audit logs and maintaining synchronization between related infrastructure components like IDF racks and equipment ports.
Sources: README.md:144-148, wiki/Home.md:21-25, scripts/scripts.php:191-196
Multi-Tenant Data Model
The architecture follows a shared-database, shared-schema approach where multi-tenancy is enforced at the row level. Each request is scoped to a company_id stored in the authenticated session, which is then used to filter queries. This design prevents data leaks and ensures that users only interact with assets belonging to their specific tenant.
The following ER diagram illustrates the high-level relationship between the core companies entity and the primary module tables.
The diagram shows how the companies table serves as the root for data isolation across the system.
Sources: wiki/Home.md:73-77, scripts/api.php:127-133, scripts/scripts.php:183-189
Core Infrastructure Tables
The infrastructure management system is centered around the relationship between IT locations, IDF racks, and network equipment. Synchronization between these tables is critical for maintaining accurate port mappings and physical layouts.
IDF and Rack Management
The system tracks physical rack space and connectivity through specialized tables. The idfs table represents a rack location, while idf_positions defines specific rack units (RU). Connectivity is managed via idf_ports and idf_links.
| Table Name | Primary Purpose | Key Relationships |
|---|---|---|
idfs | Represents an Intermediate Distribution Frame or rack. | company_id, it_location_id |
idf_positions | Stores metadata for individual rack units (RU). | idf_id |
idf_ports | Defines physical ports on an IDF device or patch panel. | idf_position_id, switch_port_id |
idf_links | Maps cable connections between two ports. | port_id_a, port_id_b |
switch_ports | Mirrored rows representing ports on a switch asset. | equipment_id, idf_port_id |
Sources: wiki/IDF-Synchronization.md:12-22, README.md:104-105
Synchronization Logic
A "protection zone" exists within the codebase to handle the synchronization of IDF data. When a rack position or port is updated, the system must maintain parity between idf_ports and switch_ports. If synchronization fails, the system is designed to use transactions to roll back changes.
The flow ensures that infrastructure data remains consistent across mirrored tables.
Sources: wiki/IDF-Synchronization.md:24-34
Asset and Personnel Management
The equipment and employees tables are the most frequently accessed entities in the schema, serving as the foundation for the inventory and organizational modules.
Equipment Schema
The equipment table tracks IT assets including servers, workstations, and printers. It links to several lookup tables to categorize assets by manufacturer, status, and physical location.
- Key Fields:
id,company_id,manufacturer_id,equipment_type_id,it_location_id,status_id. - Triggers: Audit triggers capture all mutations to the equipment table to maintain a history of asset movements.
Employee and Org Chart Schema
The employees table supports a self-referencing relationship via the reports_to column, enabling the generation of an interactive organizational chart.
- Visibility Flags:
on_orgchartandon_contactscontrol whether an employee appears in visual modules. - Relationship Logic: Employees are linked to
departmentsandemployee_positionsfor reporting and filtering.
Sources: README.md:111-120, wiki/Modules.md:38-50, scripts/api.php:85-90
Database Schema Tools and Utilities
The repository provides several scripts to visualize, verify, and maintain the database schema.
DBdesign.php
This utility parses database.sql to generate an ER-style diagram using Mermaid.js. It extracts table definitions, column types, primary keys, and foreign key relationships.
// Logic for parsing Foreign Keys in scripts/DBdesign.php
if (preg_match('/(?:CONSTRAINT\s+`[^`]+`\s+)?FOREIGN\s+KEY\s*\(([^)]+)\)\s+REFERENCES\s+`([^`]+)`\s*\(([^)]+)\)/i', $line, $fkMatch)) {
$localColumns = itm_dbdesign_normalize_column_list($fkMatch[1]);
$refTable = trim($fkMatch[2]);
$refColumns = itm_dbdesign_normalize_column_list($fkMatch[3]);
// ... logic to add to relationships array
}
Sources: scripts/DBdesign.php:68-80
Schema Verification Scripts
The system includes multiple CLI tools to ensure the live database matches the intended schema:
verify_database_schema.php: Comparesinformation_schemaagainstdatabase.sqlto detect missing or extra tables.check_database_sql_company_name_uniques.php: Audits the schema to ensure every company-scoped table has proper unique constraints (PRIMARY + scope UNIQUE).analyze_database_health.php: RunsANALYZE TABLEon all base tables to identify corruption or performance issues.
Sources: scripts/scripts.php:191-205, README.md:162-167
Conclusion
The database schema of the IT Management System is built for robustness and multi-tenant security. By centering the data model on the companies entity and utilizing strict foreign key constraints and automated synchronization triggers, the system maintains a high degree of data integrity. The inclusion of comprehensive schema auditing tools allows developers to maintain parity between the SQL definitions and the active database environment, ensuring the system remains stable across updates.
Sources: scripts/scripts.php:177-181, wiki/IDF-Synchronization.md:65-75