-- Schema de la base "Kompte" (gestion de budget). -- Import initial via phpMyAdmin (cPanel). Les evolutions ulterieures se font -- via des fichiers dates dans database/migrations/, a appliquer dans l'ordre. -- -- Conventions du projet : -- - utf8mb4 / utf8mb4_unicode_ci partout -- - montants stockes en centimes (INT), jamais en flottant -- - CREATE TABLE IF NOT EXISTS pour rester rejouable sans danger -- - adresses IP stockees en binaire via INET6_ATON (IPv4 + IPv6) SET NAMES utf8mb4; -- --------------------------------------------------------------------------- -- Banques, creees par les admins (nom + logo). Referencees par les -- utilisateurs ; supprimer une banque ne supprime pas les utilisateurs -- (bank_id repasse a NULL). Les logos vivent dans storage/banks/. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS banks ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, logo_filename VARCHAR(100) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_banks_name (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- Categories de depenses, creees par les admins. is_active permet d'archiver -- une categorie sans casser l'historique des operations qui la referencent. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS categories ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, color VARCHAR(7) NOT NULL DEFAULT '#6b7194' COMMENT 'couleur hex de la pastille (#rrggbb)', icon VARCHAR(60) NULL COMMENT 'classes CSS de l''icone (Font Awesome ou Tabler), ex. "fa-solid fa-house"', is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_categories_name (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- Enseignes (commerces/services), creees par les admins. Les logos vivent -- dans storage/merchants/. Les futures operations y feront reference. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS merchants ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, logo_filename VARCHAR(100) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_merchants_name (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- Utilisateurs. Comptes crees uniquement par les admins (ou via l'assistant -- /setup pour le tout premier admin) : aucune inscription publique. -- Double authentification au choix : aucune, application TOTP, ou code par -- e-mail (twofa_method) — configurable depuis le futur profil. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, email VARCHAR(190) NOT NULL, password_hash VARCHAR(255) NOT NULL, role ENUM('user', 'admin') NOT NULL DEFAULT 'user', avatar_filename VARCHAR(100) NULL, bank_id INT UNSIGNED NULL, twofa_method ENUM('none', 'totp', 'email') NOT NULL DEFAULT 'none', totp_secret_encrypted VARBINARY(255) NULL COMMENT 'secret TOTP chiffre (AES-256-GCM, cle derivee de app.key), jamais en clair', is_active TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'un compte desactive ne peut plus se connecter', failed_login_attempts TINYINT UNSIGNED NOT NULL DEFAULT 0, locked_until DATETIME NULL, last_login_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_users_email (email), KEY idx_users_bank_id (bank_id), CONSTRAINT fk_users_bank FOREIGN KEY (bank_id) REFERENCES banks (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- Budgets mensuels par categorie : l'utilisateur renseigne (ou non) un -- montant pour chaque categorie creee par l'admin. Un seul montant par -- couple utilisateur/categorie, applique chaque mois. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS user_category_budgets ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, category_id INT UNSIGNED NOT NULL, amount_cents INT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_user_category_budgets (user_id, category_id), KEY idx_user_category_budgets_category (category_id), CONSTRAINT fk_user_category_budgets_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_user_category_budgets_category FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- Operations (depenses/revenus) saisies par les utilisateurs. Structure -- minimale posee pour les simulateurs de la page Outils ; completee par -- migration quand la page de saisie "Mois en cours" sera developpee. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS operations ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, type ENUM('expense', 'income') NOT NULL DEFAULT 'expense', kind ENUM('unique', 'fixed', 'subscription', 'salary', 'refund', 'family', 'other') NOT NULL DEFAULT 'unique' COMMENT 'depense : unique/fixed/subscription — entree : salary/refund/family/other', label VARCHAR(190) NOT NULL, amount_cents INT UNSIGNED NOT NULL, category_id INT UNSIGNED NULL, merchant_id INT UNSIGNED NULL, operation_date DATE NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_operations_user_date (user_id, operation_date), KEY idx_operations_user_merchant (user_id, merchant_id), KEY idx_operations_category (category_id), CONSTRAINT fk_operations_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_operations_category FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE SET NULL, CONSTRAINT fk_operations_merchant FOREIGN KEY (merchant_id) REFERENCES merchants (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- Defis : ne pas depenser dans une categorie pendant N jours. Les jours -- reussis/rates sont calcules depuis operations (une depense de la categorie -- sur un jour du defi = jour rate). -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS challenges ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, category_id INT UNSIGNED NOT NULL, label VARCHAR(150) NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_challenges_user (user_id, start_date), KEY idx_challenges_category (category_id), CONSTRAINT fk_challenges_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_challenges_category FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- Objectifs d'epargne + versements manuels. Le montant courant d'un objectif -- = somme de ses versements. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS savings_goals ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, label VARCHAR(150) NOT NULL, target_cents INT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_savings_goals_user (user_id), CONSTRAINT fk_savings_goals_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS savings_deposits ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, goal_id INT UNSIGNED NOT NULL, amount_cents INT UNSIGNED NOT NULL, deposit_date DATE NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_savings_deposits_goal_date (goal_id, deposit_date), CONSTRAINT fk_savings_deposits_goal FOREIGN KEY (goal_id) REFERENCES savings_goals (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- Jetons "se souvenir de moi", pattern selecteur/validateur : le cookie -- contient les deux valeurs, seule l'empreinte du validateur est stockee. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS user_remember_tokens ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, selector CHAR(24) NOT NULL COMMENT 'partie publique du cookie, sert a la recherche', validator_hash CHAR(64) NOT NULL COMMENT 'sha256 du validateur secret, jamais le validateur en clair', user_agent VARCHAR(255) NULL, expires_at DATETIME NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_user_remember_tokens_selector (selector), KEY idx_user_remember_tokens_user_id (user_id), CONSTRAINT fk_user_remember_tokens_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- Journal de connexion : alimente l'historique du profil utilisateur et le -- journal du panneau admin. user_id nullable (echec sur e-mail inconnu) et -- conserve en SET NULL si le compte est supprime. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS login_logs ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NULL, email VARCHAR(190) NOT NULL COMMENT 'adresse saisie au moment de la tentative', event ENUM('success', 'failure', 'lockout', 'magic_link') NOT NULL, source_ip VARBINARY(16) NULL, user_agent VARCHAR(255) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_login_logs_user_created (user_id, created_at), KEY idx_login_logs_created (created_at), CONSTRAINT fk_login_logs_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- Liens de connexion magiques : token envoye par e-mail, seule l'empreinte -- est stockee, usage unique, expiration courte (auth.magic_link_ttl_minutes). -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS magic_link_tokens ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, token_hash CHAR(64) NOT NULL, expires_at DATETIME NOT NULL, used_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_magic_link_tokens_hash (token_hash), KEY idx_magic_link_tokens_user (user_id), CONSTRAINT fk_magic_link_tokens_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- Trace de TOUTES les demandes de lien magique (meme sur e-mail inconnu), -- uniquement pour le rate limiting par IP : ne revele rien sur les comptes. -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS magic_link_requests ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, source_ip VARBINARY(16) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_magic_link_requests_ip_created (source_ip, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------------- -- Codes de double authentification envoyes par e-mail (twofa_method = email) : -- code a 6 chiffres stocke hache, usage unique, expiration courte. Les echecs -- de saisie comptent dans le verrouillage du compte (users.locked_until). -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS email_otp_codes ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, code_hash CHAR(64) NOT NULL, expires_at DATETIME NOT NULL, used_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_email_otp_codes_user_created (user_id, created_at), CONSTRAINT fk_email_otp_codes_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;