-- ============================================================================= -- Twosome – database/schema.sql -- MySQL 5.7+ / MariaDB 10.3+ -- -- Import via phpMyAdmin: -- 1. Open phpMyAdmin → select your database → click "Import" tab. -- 2. Choose this file, ensure "utf8mb4" is selected, click "Go". -- 3. Run again anytime: every table uses IF NOT EXISTS and triggers use -- DROP IF EXISTS, so re-importing is safe. -- -- Import via MySQL CLI (SSH or cPanel Terminal): -- mysql -u cpanelusername_api -p cpanelusername_twosome < database/schema.sql -- -- Character set : utf8mb4 / utf8mb4_unicode_ci (full Unicode + emoji) -- Storage engine: InnoDB (transactions, foreign keys, row-level locks) -- ============================================================================= SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- allow forward-references during bulk import SET time_zone = '+00:00'; -- Legacy OTP / SMS tables removal (ensures clean slate on upgrade) DROP TABLE IF EXISTS otp_codes; DROP TABLE IF EXISTS sms_logs; DROP TABLE IF EXISTS phone_verifications; -- ============================================================================= -- 1. RATE LIMITER (needed by middleware, no FK deps) -- ============================================================================= CREATE TABLE IF NOT EXISTS rate_limits ( -- Composite PK: one row per (key, window). UPSERT uses ON DUPLICATE KEY. rl_key VARCHAR(160) NOT NULL, requests INT UNSIGNED NOT NULL DEFAULT 1, window_start INT UNSIGNED NOT NULL, -- unix timestamp of window start PRIMARY KEY (rl_key, window_start) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 2. USERS -- ============================================================================= CREATE TABLE IF NOT EXISTS users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, phone_e164 VARCHAR(20) NOT NULL, -- "+919876543210" email VARCHAR(254) DEFAULT NULL, -- optional recovery email password_hash VARCHAR(255) NOT NULL, -- Argon2id via password_hash() recovery_code_hash VARCHAR(255) NOT NULL, -- hashed 16-char code shown once at signup display_name VARCHAR(64) NOT NULL, birth_year_confirmed YEAR NOT NULL, -- 18+ gate: store year only, never full DOB public_key TEXT DEFAULT NULL, -- libsodium X25519 public key, base64url created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, deleted_at DATETIME DEFAULT NULL, -- soft-delete; app treats this user as gone UNIQUE KEY uq_users_phone (phone_e164), UNIQUE KEY uq_users_email (email) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 3. DEVICES (FCM tokens – one user may have multiple phones) -- ============================================================================= CREATE TABLE IF NOT EXISTS devices ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, fcm_token VARCHAR(512) NOT NULL, platform VARCHAR(16) NOT NULL DEFAULT 'android', last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_devices_fcm (fcm_token), INDEX idx_devices_user (user_id), CONSTRAINT fk_devices_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 4. REFRESH TOKENS (stored hashed; pairs with JWT refresh token) -- ============================================================================= CREATE TABLE IF NOT EXISTS refresh_tokens ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, token_hash VARCHAR(64) NOT NULL, -- SHA-256 hex of the raw 32-byte token expires_at DATETIME NOT NULL, revoked_at DATETIME DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_rt_hash (token_hash), -- Index supports cleanup: DELETE WHERE expires_at < NOW() INDEX idx_rt_user_expiry (user_id, expires_at), CONSTRAINT fk_rt_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 5. COUPLES -- ============================================================================= CREATE TABLE IF NOT EXISTS couples ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Spicy mode: BOTH flags must be 1 for spicy content to unlock. spicy_enabled_by_a TINYINT(1) NOT NULL DEFAULT 0, spicy_enabled_by_b TINYINT(1) NOT NULL DEFAULT 0, -- Disappearing messages: NULL = off, 3600 = 1 h, 86400 = 24 h disappearing_seconds INT UNSIGNED DEFAULT NULL, -- Relationship start date / anniversary (editable by either partner) start_date DATE DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 6. COUPLE MEMBERS -- -- Constraints enforced here: -- A) DB-level: UNIQUE (user_id) → one active couple per user at all times. -- B) Trigger : trg_couple_max_members → max 2 rows per couple_id. -- ============================================================================= CREATE TABLE IF NOT EXISTS couple_members ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, couple_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, role ENUM('creator','partner') NOT NULL DEFAULT 'partner', joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, -- One couple per user (DB-level, no trigger needed for this rule). UNIQUE KEY uq_cm_user (user_id), UNIQUE KEY uq_cm_cp_user (couple_id, user_id), CONSTRAINT fk_cm_couple FOREIGN KEY (couple_id) REFERENCES couples(id) ON DELETE CASCADE, CONSTRAINT fk_cm_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ── Trigger B: cap each couple at 2 members ────────────────────────────────── DROP TRIGGER IF EXISTS trg_couple_max_members; CREATE TRIGGER trg_couple_max_members BEFORE INSERT ON couple_members FOR EACH ROW BEGIN DECLARE member_count INT; SELECT COUNT(*) INTO member_count FROM couple_members WHERE couple_id = NEW.couple_id; IF member_count >= 2 THEN -- SQLSTATE 45000 = generic user-defined error SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'A couple space already has two members.'; END IF; END; -- ============================================================================= -- 7. INVITES (8-char one-time code, 24-h expiry) -- ============================================================================= CREATE TABLE IF NOT EXISTS invites ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, couple_id BIGINT UNSIGNED NOT NULL, code_hash VARCHAR(255) NOT NULL, -- bcrypt hash of the 8-char code expires_at DATETIME NOT NULL, used_at DATETIME DEFAULT NULL, UNIQUE KEY uq_invites_code (code_hash), -- Cleanup: delete WHERE expires_at < NOW() AND used_at IS NULL INDEX idx_invites_expiry (expires_at), CONSTRAINT fk_invites_couple FOREIGN KEY (couple_id) REFERENCES couples(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 8. MESSAGES (server stores ONLY ciphertext – cannot read content) -- ============================================================================= CREATE TABLE IF NOT EXISTS messages ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, couple_id BIGINT UNSIGNED NOT NULL, sender_id BIGINT UNSIGNED NOT NULL, ciphertext MEDIUMTEXT NOT NULL, -- base64url-encoded libsodium box nonce VARCHAR(64) NOT NULL, -- base64url-encoded 24-byte nonce type ENUM('text','image','audio','doodle','system') NOT NULL DEFAULT 'text', reply_to_id BIGINT UNSIGNED DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, delivered_at DATETIME DEFAULT NULL, read_at DATETIME DEFAULT NULL, expires_at DATETIME DEFAULT NULL, -- NULL = permanent -- PRIMARY read pattern: poll new messages for a couple after a known id. -- Using (couple_id, id) not (couple_id, created_at) because id is monotonic -- and avoids duplicate rows on same-second inserts. INDEX idx_messages_couple_poll (couple_id, id), -- Cleanup: delete WHERE expires_at IS NOT NULL AND expires_at < NOW() INDEX idx_messages_expiry (expires_at), CONSTRAINT fk_msg_couple FOREIGN KEY (couple_id) REFERENCES couples(id) ON DELETE CASCADE, CONSTRAINT fk_msg_sender FOREIGN KEY (sender_id) REFERENCES users(id), CONSTRAINT fk_msg_reply FOREIGN KEY (reply_to_id) REFERENCES messages(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 9. REACTIONS -- ============================================================================= CREATE TABLE IF NOT EXISTS reactions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, message_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, emoji VARCHAR(8) NOT NULL, -- single emoji codepoint(s) -- One reaction per user per message (update means replace) UNIQUE KEY uq_reactions_msg_user (message_id, user_id), CONSTRAINT fk_react_msg FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE, CONSTRAINT fk_react_user FOREIGN KEY (user_id) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 10. MEDIA (encrypted file references; actual bytes stored outside public/) -- ============================================================================= CREATE TABLE IF NOT EXISTS media ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, couple_id BIGINT UNSIGNED NOT NULL, owner_id BIGINT UNSIGNED NOT NULL, -- Random filename on disk (e.g. /home/user/twosome_uploads/ab/cd1234.enc) -- Never web-accessible; served through authenticated PHP endpoint. path VARCHAR(512) NOT NULL, nonce VARCHAR(64) NOT NULL, -- libsodium nonce for this file size INT UNSIGNED NOT NULL, -- bytes (compressed + encrypted) created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, expires_at DATETIME DEFAULT NULL, INDEX idx_media_couple (couple_id), INDEX idx_media_expiry (expires_at), -- cleanup query CONSTRAINT fk_media_couple FOREIGN KEY (couple_id) REFERENCES couples(id) ON DELETE CASCADE, CONSTRAINT fk_media_owner FOREIGN KEY (owner_id) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 11. GAME PACKS AND CARDS -- ============================================================================= CREATE TABLE IF NOT EXISTS packs ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, slug VARCHAR(64) NOT NULL, -- machine identifier used in code title VARCHAR(128) NOT NULL, -- display name shown in app is_spicy TINYINT(1) NOT NULL DEFAULT 0, -- 1 = flirty/cute, 2 = naughty/suggestive, 3 = wild (never explicit) level TINYINT NOT NULL DEFAULT 1, locale VARCHAR(8) NOT NULL DEFAULT 'en', UNIQUE KEY uq_packs_slug (slug) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS cards ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, pack_id INT UNSIGNED NOT NULL, type ENUM('truth','dare','wyr','nhie','question') NOT NULL, text TEXT NOT NULL, level TINYINT NOT NULL DEFAULT 1, mode ENUM('together','apart','both') NOT NULL DEFAULT 'apart', -- Comma-separated tags for future filtering (e.g. "funny,outdoor") tags VARCHAR(255) DEFAULT NULL, active TINYINT(1) NOT NULL DEFAULT 1, INDEX idx_cards_pack (pack_id), INDEX idx_cards_mode (mode), CONSTRAINT fk_cards_pack FOREIGN KEY (pack_id) REFERENCES packs(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 12. GAME SESSIONS AND ROUNDS -- ============================================================================= CREATE TABLE IF NOT EXISTS game_sessions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, couple_id BIGINT UNSIGNED NOT NULL, -- 'truth_or_dare' | 'would_you_rather' | 'never_have_i_ever' -- | 'question_cards' | 'how_well_do_you_know_me' game_type VARCHAR(32) NOT NULL, -- Stores turn order, card queue, current index as JSON. -- Opaque to the server; only the app interprets it. state_json TEXT NOT NULL, status ENUM('active','finished','abandoned') NOT NULL DEFAULT 'active', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_gs_couple (couple_id, status), CONSTRAINT fk_gs_couple FOREIGN KEY (couple_id) REFERENCES couples(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS game_rounds ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, session_id BIGINT UNSIGNED NOT NULL, card_id INT UNSIGNED NOT NULL, -- Answers stored as-is (may be encrypted by the app if needed). -- NULL until the partner submits their answer. answer_a TEXT DEFAULT NULL, answer_b TEXT DEFAULT NULL, -- Set once both partners have answered and the reveal animation fires. revealed_at DATETIME DEFAULT NULL, INDEX idx_gr_session (session_id), CONSTRAINT fk_gr_session FOREIGN KEY (session_id) REFERENCES game_sessions(id) ON DELETE CASCADE, CONSTRAINT fk_gr_card FOREIGN KEY (card_id) REFERENCES cards(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 12B. UNIFIED MATCH ENGINE (matches, match_moves, scores, stakes) -- ============================================================================= CREATE TABLE IF NOT EXISTS matches ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, couple_id BIGINT UNSIGNED NOT NULL, game_type VARCHAR(64) NOT NULL, mode ENUM('turn', 'simultaneous', 'realtime') NOT NULL, play_mode ENUM('live', 'async', 'challenge') NOT NULL, relationship_mode ENUM('together', 'apart') NOT NULL DEFAULT 'apart', state_json MEDIUMTEXT NOT NULL, version INT UNSIGNED NOT NULL DEFAULT 1, status ENUM('waiting', 'active', 'finished', 'cancelled', 'expired') NOT NULL DEFAULT 'waiting', winner_id BIGINT UNSIGNED DEFAULT NULL, stake_json TEXT DEFAULT NULL, seed BIGINT UNSIGNED NOT NULL, start_at DATETIME DEFAULT NULL, deadline_at DATETIME DEFAULT NULL, last_reminder_at DATETIME DEFAULT NULL, created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, finished_at DATETIME DEFAULT NULL, INDEX idx_matches_couple_status (couple_id, status), INDEX idx_matches_deadline (status, deadline_at), INDEX idx_matches_created_by (created_by), CONSTRAINT fk_matches_couple FOREIGN KEY (couple_id) REFERENCES couples(id) ON DELETE CASCADE, CONSTRAINT fk_matches_winner FOREIGN KEY (winner_id) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_matches_creator FOREIGN KEY (created_by) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS match_moves ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, match_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, client_move_id VARCHAR(64) NOT NULL, move_json TEXT NOT NULL, seq INT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_mm_client_move (client_move_id), UNIQUE KEY uq_mm_match_seq (match_id, seq), INDEX idx_mm_match_user (match_id, user_id), CONSTRAINT fk_mm_match FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE CASCADE, CONSTRAINT fk_mm_user FOREIGN KEY (user_id) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS scores ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, couple_id BIGINT UNSIGNED NOT NULL, game_type VARCHAR(64) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, wins INT UNSIGNED NOT NULL DEFAULT 0, losses INT UNSIGNED NOT NULL DEFAULT 0, draws INT UNSIGNED NOT NULL DEFAULT 0, streak INT NOT NULL DEFAULT 0, best_streak INT NOT NULL DEFAULT 0, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_scores_user (couple_id, game_type, user_id), INDEX idx_scores_couple (couple_id), CONSTRAINT fk_scores_couple FOREIGN KEY (couple_id) REFERENCES couples(id) ON DELETE CASCADE, CONSTRAINT fk_scores_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 stakes ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(128) NOT NULL, description TEXT NOT NULL, mode ENUM('together', 'apart', 'both') NOT NULL DEFAULT 'apart', is_spicy TINYINT(1) NOT NULL DEFAULT 0, level TINYINT NOT NULL DEFAULT 1, INDEX idx_stakes_mode (mode, is_spicy) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS dares ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(128) NOT NULL, text TEXT NOT NULL, mode ENUM('together', 'apart', 'both') NOT NULL DEFAULT 'apart', is_spicy TINYINT(1) NOT NULL DEFAULT 0, level TINYINT NOT NULL DEFAULT 1, active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_dares_mode (mode, is_spicy) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 13. DAILY QUESTIONS AND ANSWERS -- ============================================================================= CREATE TABLE IF NOT EXISTS daily_questions ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- One question per calendar date; scheduled in advance. date DATE NOT NULL, text TEXT NOT NULL, UNIQUE KEY uq_dq_date (date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS daily_answers ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, couple_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, question_id INT UNSIGNED NOT NULL, -- Answers are hidden from partner until both have answered (app-enforced). answer TEXT NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Prevent double-answering the same question UNIQUE KEY uq_da_couple_user_q (couple_id, user_id, question_id), CONSTRAINT fk_da_couple FOREIGN KEY (couple_id) REFERENCES couples(id) ON DELETE CASCADE, CONSTRAINT fk_da_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_da_question FOREIGN KEY (question_id) REFERENCES daily_questions(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 14. MOOD CHECK-INS -- ============================================================================= CREATE TABLE IF NOT EXISTS moods ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, couple_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, -- 1–5 numeric scale mapped to emoji in the app mood TINYINT UNSIGNED NOT NULL CHECK (mood BETWEEN 1 AND 5), note VARCHAR(255) DEFAULT NULL, -- optional short note created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_moods_couple_time (couple_id, created_at), CONSTRAINT fk_mood_couple FOREIGN KEY (couple_id) REFERENCES couples(id) ON DELETE CASCADE, CONSTRAINT fk_mood_user FOREIGN KEY (user_id) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 15. REPORTS (required by Play Store policy for user-generated content) -- ============================================================================= CREATE TABLE IF NOT EXISTS reports ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, reporter_id BIGINT UNSIGNED NOT NULL, -- Free-text reason; never exposed to the reported user reason VARCHAR(255) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_report_user FOREIGN KEY (reporter_id) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 16. ENCRYPTED KEY BACKUPS (passphrase-derived encrypted private key) -- ============================================================================= CREATE TABLE IF NOT EXISTS key_backups ( user_id BIGINT UNSIGNED PRIMARY KEY, ciphertext TEXT NOT NULL, nonce VARCHAR(64) NOT NULL, salt VARCHAR(64) NOT NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_key_backups_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 17. SHARING SETTINGS (Mutual consent transparency layer) -- Each item requires BOTH partners to enable before active. -- ============================================================================= CREATE TABLE IF NOT EXISTS sharing_settings ( couple_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, item VARCHAR(32) NOT NULL, -- 'mood', 'live_location', 'battery_level', 'status' enabled TINYINT(1) NOT NULL DEFAULT 0, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (couple_id, user_id, item), CONSTRAINT fk_sharing_couple FOREIGN KEY (couple_id) REFERENCES couples(id) ON DELETE CASCADE, CONSTRAINT fk_sharing_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- Re-enable FK checks -- ============================================================================= SET FOREIGN_KEY_CHECKS = 1;