migrations/Version20251006100030.php line 1

Open in your IDE?
  1. <?php
  3. declare(strict_types=1);
  5. namespace DoctrineMigrations;
  7. use Doctrine\DBAL\Schema\Schema;
  8. use Doctrine\Migrations\AbstractMigration;
  10. /**
  11. * Create user_session_log table for tracking user sessions and activity
  12. */
  13. final class Version20251006100030 extends AbstractMigration
  14. {
  15. public function getDescription(): string
  16. {
  17. return 'Create user_session_log table to track user logins, logouts and session activity';
  18. }
  20. public function up(Schema $schema): void
  21. {
  22. $this->addSql('CREATE TABLE perseo.user_session_log (
  23. id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
  24. user_id BIGINT UNSIGNED DEFAULT NULL,
  25. username VARCHAR(255) DEFAULT NULL,
  26. email VARCHAR(255) DEFAULT NULL,
  27. login_at DATETIME NOT NULL,
  28. logout_at DATETIME DEFAULT NULL,
  29. last_activity_at DATETIME DEFAULT NULL,
  30. duration INT DEFAULT NULL COMMENT \'Duration in seconds\',
  31. ip_address VARCHAR(255) DEFAULT NULL,
  32. user_agent VARCHAR(500) DEFAULT NULL,
  33. session_id VARCHAR(255) NOT NULL,
  34. deleted_at DATETIME DEFAULT NULL,
  35. updated_at DATETIME DEFAULT \'2022-01-01 00:00:00\' NOT NULL,
  36. created_at DATETIME DEFAULT \'2022-01-01 00:00:00\' NOT NULL,
  37. INDEX idx_session_user_id (user_id),
  38. INDEX idx_session_username (username),
  39. INDEX idx_session_login_at (login_at),
  40. INDEX idx_session_logout_at (logout_at),
  41. INDEX idx_session_active (logout_at, last_activity_at),
  42. INDEX idx_session_id (session_id),
  43. PRIMARY KEY(id)
  44. ) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');
  46. $this->addSql('ALTER TABLE perseo.user_session_log
  47. ADD CONSTRAINT FK_4DB45FC3A76ED395
  48. FOREIGN KEY (user_id) REFERENCES perseo.user (id)
  49. ON DELETE SET NULL');
  50. }
  52. public function down(Schema $schema): void
  53. {
  54. $this->addSql('DROP TABLE perseo.user_session_log');
  55. }
  56. }