Arquitectura de Base de Datos

Modelo Entidad-Relación

Diagrama ER Principal

erDiagram
    USER ||--o{ USER_PROFILE : has
    USER ||--o{ PROPERTY_USER_RELATION : has
    USER ||--o{ CONTRACT : signs

    PROPERTY ||--o{ PROPERTY_USER_RELATION : belongs_to
    PROPERTY ||--o{ PROPERTY_IMAGE : has
    PROPERTY ||--o{ CONTRACT : involves

    CONTRACT ||--o{ PAYMENT : generates
    CONTRACT ||--o{ SIGNATURE : requires

    PROFILE ||--o{ USER_PROFILE : assigned_to
    PROFILE ||--o{ MENU_PROFILE_RELATION : has_access

    MENU ||--o{ MENU_PROFILE_RELATION : accessible_by

    SUBSCRIPTION ||--o{ USER : belongs_to

---

Tablas Principales

Usuarios y Autenticación

-- Tabla de usuarios (Django User extendido)
TABLE users {
  id: INTEGER PRIMARY KEY
  username: VARCHAR(150) UNIQUE
  email: VARCHAR(254) UNIQUE
  first_name: VARCHAR(150)
  last_name: VARCHAR(150)
  phone: VARCHAR(20)
  document_id: VARCHAR(50)
  address: TEXT
  birth_date: DATE
  profile_image: VARCHAR(500)
  is_verified: BOOLEAN
  created_at: DATETIME
  updated_at: DATETIME
}

-- Perfiles de usuario
TABLE profiles {
  id: INTEGER PRIMARY KEY
  profile_name: VARCHAR(100) UNIQUE
  description: TEXT
  permissions: JSON
  active: BOOLEAN
  created_at: DATETIME
}

-- Relación usuario-perfil
TABLE user_profiles {
  id: INTEGER PRIMARY KEY
  user_id: INTEGER FOREIGN KEY
  profile_id: INTEGER FOREIGN KEY
  assigned_at: DATETIME
  UNIQUE(user_id, profile_id)
}

Propiedades

-- Propiedades principales
TABLE properties {
  id: INTEGER PRIMARY KEY
  title: VARCHAR(200)
  description: TEXT
  property_type: ENUM('house', 'apartment', 'commercial', 'office', 'warehouse')
  status: ENUM('available', 'occupied', 'maintenance', 'inactive')

  -- Location
  address: TEXT
  city: VARCHAR(100)
  state: VARCHAR(100)
  country: VARCHAR(100)
  postal_code: VARCHAR(20)
  latitude: DECIMAL(10,8)
  longitude: DECIMAL(11,8)

  -- Characteristics
  bedrooms: INTEGER DEFAULT 1
  bathrooms: INTEGER DEFAULT 1
  area: DECIMAL(8,2)
  floors: INTEGER DEFAULT 1
  parking_spaces: INTEGER DEFAULT 0
  balcony: BOOLEAN DEFAULT FALSE

  -- Financial
  price: DECIMAL(12,2)
  currency: VARCHAR(3) DEFAULT 'COP'
  deposit: DECIMAL(12,2) DEFAULT 0
  admin_fee: DECIMAL(10,2) DEFAULT 0

  -- Amenities
  amenities: JSON

  -- Dates
  available_from: DATE
  created_at: DATETIME
  updated_at: DATETIME
}

-- Imágenes de propiedades
TABLE property_images {
  id: INTEGER PRIMARY KEY
  property_id: INTEGER FOREIGN KEY
  image_url: VARCHAR(500)
  is_main: BOOLEAN DEFAULT FALSE
  order_index: INTEGER DEFAULT 0
  uploaded_at: DATETIME
}

-- Relación propiedad-usuario
TABLE property_user_relations {
  id: INTEGER PRIMARY KEY
  property_id: INTEGER FOREIGN KEY
  user_id: INTEGER FOREIGN KEY
  relation_type: ENUM('owner', 'tenant', 'manager', 'agent')
  start_date: DATE
  end_date: DATE
  active: BOOLEAN DEFAULT TRUE
  created_at: DATETIME
}

Contratos y Pagos

-- Contratos
TABLE contracts {
  id: INTEGER PRIMARY KEY
  property_id: INTEGER FOREIGN KEY
  landlord_id: INTEGER FOREIGN KEY
  tenant_id: INTEGER FOREIGN KEY
  guarantor_id: INTEGER FOREIGN KEY NULL

  contract_type: ENUM('residential', 'commercial')
  status: ENUM('draft', 'pending', 'active', 'expired', 'terminated')

  -- Dates
  start_date: DATE
  end_date: DATE
  signing_date: DATETIME NULL

  -- Financial Terms
  monthly_rent: DECIMAL(12,2)
  deposit: DECIMAL(12,2)
  payment_day: INTEGER DEFAULT 1
  annual_increase: DECIMAL(5,2) DEFAULT 0

  -- Contract Details
  contract_text: TEXT
  terms_and_conditions: TEXT
  special_clauses: TEXT

  -- Digital Signatures
  landlord_signature_hash: VARCHAR(255)
  tenant_signature_hash: VARCHAR(255)
  guarantor_signature_hash: VARCHAR(255)

  -- Metadata
  contract_hash: VARCHAR(255) UNIQUE
  created_at: DATETIME
  updated_at: DATETIME
}

-- Firmas digitales
TABLE contract_signatures {
  id: INTEGER PRIMARY KEY
  contract_id: INTEGER FOREIGN KEY
  signer_id: INTEGER FOREIGN KEY
  signature_data: TEXT
  signature_hash: VARCHAR(255)
  ip_address: VARCHAR(45)
  user_agent: TEXT
  signed_at: DATETIME
}

-- Pagos
TABLE payments {
  id: INTEGER PRIMARY KEY
  contract_id: INTEGER FOREIGN KEY
  amount: DECIMAL(12,2)
  payment_type: ENUM('rent', 'deposit', 'admin_fee', 'maintenance')
  status: ENUM('pending', 'completed', 'failed', 'refunded')
  payment_method: VARCHAR(50)
  stripe_payment_intent_id: VARCHAR(200)
  due_date: DATE
  paid_at: DATETIME NULL
  created_at: DATETIME
}

Sistema de Menús

-- Menús dinámicos
TABLE menus {
  id: INTEGER PRIMARY KEY
  menu_title: VARCHAR(100)
  icon: VARCHAR(50)
  path: VARCHAR(200)
  parent_menu_id: INTEGER FOREIGN KEY NULL
  permissions_required: VARCHAR(200)
  order_index: INTEGER DEFAULT 0
  active: BOOLEAN DEFAULT TRUE
  created_at: DATETIME
}

Suscripciones

-- Suscripciones Stripe
TABLE subscriptions {
  id: INTEGER PRIMARY KEY
  user_id: INTEGER FOREIGN KEY
  stripe_subscription_id: VARCHAR(200) UNIQUE
  stripe_customer_id: VARCHAR(200)
  status: ENUM('active', 'inactive', 'canceled', 'expired')
  plan_name: VARCHAR(100)
  amount: DECIMAL(10,2)
  currency: VARCHAR(3) DEFAULT 'COP'
  interval: VARCHAR(20)
  current_period_start: DATETIME
  current_period_end: DATETIME
  created_at: DATETIME
  updated_at: DATETIME
}

---

Índices y Performance

Índices Principales

-- Índices para búsquedas frecuentes
CREATE INDEX idx_properties_city ON properties(city);
CREATE INDEX idx_properties_type ON properties(property_type);
CREATE INDEX idx_properties_status ON properties(status);
CREATE INDEX idx_properties_price ON properties(price);

-- Índices para relaciones
CREATE INDEX idx_user_profiles_user ON user_profiles(user_id);
CREATE INDEX idx_property_relations_property ON property_user_relations(property_id);
CREATE INDEX idx_property_relations_user ON property_user_relations(user_id);

-- Índices para contratos
CREATE INDEX idx_contracts_property ON contracts(property_id);
CREATE INDEX idx_contracts_landlord ON contracts(landlord_id);
CREATE INDEX idx_contracts_tenant ON contracts(tenant_id);
CREATE INDEX idx_contracts_status ON contracts(status);

-- Índices compuestos
CREATE INDEX idx_properties_city_type ON properties(city, property_type);
CREATE INDEX idx_contracts_dates ON contracts(start_date, end_date);

Optimizaciones de Consultas

-- Query para propiedades disponibles por ciudad
SELECT p.*, pi.image_url as main_image
FROM properties p
LEFT JOIN property_images pi ON p.id = pi.property_id AND pi.is_main = TRUE
WHERE p.city = 'Medellín'
  AND p.status = 'available'
  AND p.price BETWEEN 500000 AND 2000000
ORDER BY p.price ASC
LIMIT 20;

-- Query para contratos activos de un usuario
SELECT c.*, p.title as property_title, p.address
FROM contracts c
JOIN properties p ON c.property_id = p.id
WHERE (c.landlord_id = ? OR c.tenant_id = ?)
  AND c.status = 'active'
  AND c.end_date > CURDATE()
ORDER BY c.end_date ASC;

---

Particionamiento y Escalabilidad

Particionamiento por Fecha

-- Particionamiento de tabla de pagos por año
CREATE TABLE payments (
    -- campos...
    created_at DATETIME
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Archivado de Datos

-- Tabla de archivos para contratos expirados
CREATE TABLE contracts_archive (
    -- misma estructura que contracts
) ENGINE=InnoDB;

-- Proceso de archivado automático
DELIMITER //
CREATE EVENT archive_expired_contracts
ON SCHEDULE EVERY 1 MONTH
DO
BEGIN
    INSERT INTO contracts_archive
    SELECT * FROM contracts
    WHERE status = 'expired'
      AND end_date < DATE_SUB(NOW(), INTERVAL 2 YEAR);

    DELETE FROM contracts
    WHERE status = 'expired'
      AND end_date < DATE_SUB(NOW(), INTERVAL 2 YEAR);
END //
DELIMITER ;

---

Backup y Recuperación

Estrategia de Backup

# Backup completo diario
mysqldump --single-transaction --routines --triggers \
  -h $DB_HOST -u $DB_USER -p$DB_PASSWORD alojaplus \
  > /backups/alojaplus_$(date +%Y%m%d).sql

# Backup incremental cada 6 horas
mysqlbinlog --start-datetime="$(date -d '6 hours ago' '+%Y-%m-%d %H:%M:%S')" \
  /var/log/mysql/mysql-bin.* > /backups/incremental_$(date +%Y%m%d_%H).sql

Punto de Recuperación

-- Restoration procedure
-- 1. Restore full backup
mysql -h $DB_HOST -u $DB_USER -p$DB_PASSWORD alojaplus < backup_full.sql

-- 2. Apply incremental backups
mysql -h $DB_HOST -u $DB_USER -p$DB_PASSWORD alojaplus < incremental_*.sql

-- 3. Verify data integrity
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM properties;
SELECT COUNT(*) FROM contracts;

---

Seguridad de Base de Datos

Control de Acceso

-- Usuario de aplicación con permisos limitados
CREATE USER 'alojaplus_app'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON alojaplus.* TO 'alojaplus_app'@'%';
GRANT EXECUTE ON alojaplus.* TO 'alojaplus_app'@'%';

-- Usuario de solo lectura para reportes
CREATE USER 'alojaplus_read'@'%' IDENTIFIED BY 'read_password';
GRANT SELECT ON alojaplus.* TO 'alojaplus_read'@'%';

-- Revocar permisos peligrosos
REVOKE CREATE, DROP, ALTER, INDEX ON alojaplus.* FROM 'alojaplus_app'@'%';

Auditoría

-- Tabla de auditoría
CREATE TABLE audit_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(64),
    operation ENUM('INSERT', 'UPDATE', 'DELETE'),
    user_id INTEGER,
    old_values JSON,
    new_values JSON,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_table_time (table_name, timestamp)
);

-- Trigger de auditoría para tabla users
DELIMITER //
CREATE TRIGGER users_audit_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, operation, user_id, old_values, new_values)
    VALUES ('users', 'UPDATE', NEW.id,
            JSON_OBJECT('email', OLD.email, 'phone', OLD.phone),
            JSON_OBJECT('email', NEW.email, 'phone', NEW.phone));
END //
DELIMITER ;

---

Monitoreo y Mantenimiento

Métricas de Performance

-- Query para monitorear performance
SELECT
    SCHEMA_NAME as database_name,
    TABLE_NAME,
    ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) as size_mb,
    TABLE_ROWS as row_count
FROM information_schema.TABLES
WHERE SCHEMA_NAME = 'alojaplus'
ORDER BY size_mb DESC;

-- Queries más lentas
SELECT
    DIGEST_TEXT as query,
    COUNT_STAR as exec_count,
    AVG_TIMER_WAIT/1000000000 as avg_time_seconds
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'alojaplus'
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

Mantenimiento Automatizado

-- Optimización automática de tablas
DELIMITER //
CREATE EVENT optimize_tables
ON SCHEDULE EVERY 1 WEEK
DO
BEGIN
    OPTIMIZE TABLE properties;
    OPTIMIZE TABLE contracts;
    OPTIMIZE TABLE payments;
    OPTIMIZE TABLE audit_log;
END //
DELIMITER ;

-- Limpieza de logs antiguos
DELIMITER //
CREATE EVENT cleanup_old_logs
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
    DELETE FROM audit_log
    WHERE timestamp < DATE_SUB(NOW(), INTERVAL 1 YEAR);
END //
DELIMITER ;