-- Noteable complete fresh-install schema aligned with the current local Drift database -- and Node.js backend: email verification, note revisions, metadata, note types, -- and independent Kanban synchronization. -- -- IMPORTANT: -- * Run this only against a NEW, EMPTY database. -- * Do not run older incremental migrations after importing this complete schema. -- * Back up any existing database before making schema changes. -- -- A physical device can be registered to multiple accounts, with one registration per -- (device_uid, user_id) pair. -- CREATE TABLE users ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, email VARCHAR(255) NOT NULL, password_hash VARCHAR(255) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_users_email (email) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- One-time codes for registration verification and password resets. -- Only the SHA-256 hash of each code is stored. CREATE TABLE auth_email_verifications ( email VARCHAR(254) NOT NULL, purpose ENUM('registration', 'password_reset') NOT NULL, password_hash VARCHAR(255) NULL, code_hash CHAR(64) NOT NULL, expires_at DATETIME NOT NULL, attempts TINYINT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (email, purpose), KEY idx_auth_email_verifications_expiry (expires_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE devices ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, device_uid CHAR(36) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, device_name VARCHAR(255) NULL, platform VARCHAR(50) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, last_seen_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_devices_uid_user (device_uid, user_id), KEY idx_devices_user_id (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; CREATE TABLE notes ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, note_uid CHAR(36) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, title VARCHAR(500) NULL, is_title_default TINYINT(1) NOT NULL DEFAULT 0, description LONGTEXT NOT NULL, current_revision BIGINT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_notes_uid (note_uid), UNIQUE KEY uq_notes_uid_user (note_uid, user_id), KEY idx_notes_user_id (user_id), KEY idx_notes_updated_at (user_id, updated_at), CONSTRAINT fk_notes_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Normalized current classifications for each note. CREATE TABLE note_types ( note_uid CHAR(36) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, type VARCHAR(64) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (user_id, note_uid, type), KEY idx_note_types_note (note_uid, user_id), KEY idx_note_types_type (user_id, type, note_uid), CONSTRAINT fk_note_types_note FOREIGN KEY (note_uid, user_id) REFERENCES notes(note_uid, user_id) ON DELETE CASCADE, CONSTRAINT fk_note_types_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE note_revisions ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, revision_uid CHAR(36) NOT NULL, note_uid CHAR(36) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, device_uid CHAR(36) NOT NULL, revision_number BIGINT UNSIGNED NOT NULL, parent_revision BIGINT UNSIGNED NULL, title VARCHAR(500) NULL, description LONGTEXT NOT NULL, operation ENUM('create', 'update', 'delete') NOT NULL, metadata_json JSON NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_note_revisions_uid (revision_uid), UNIQUE KEY uq_note_revision_number (note_uid, revision_number), KEY idx_note_revisions_note (note_uid), KEY idx_note_revisions_user (user_id), KEY idx_note_revisions_device (device_uid), KEY idx_note_revisions_device_user (device_uid, user_id), CONSTRAINT fk_note_revisions_note FOREIGN KEY (note_uid) REFERENCES notes(note_uid) ON DELETE CASCADE, CONSTRAINT fk_note_revisions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_note_revisions_device FOREIGN KEY (device_uid, user_id) REFERENCES devices(device_uid, user_id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE sync_versions ( version BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (version) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE sync_changes ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, server_version BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, entity_type VARCHAR(50) NOT NULL, entity_uid CHAR(36) NOT NULL, operation ENUM('create', 'update', 'delete') NOT NULL, revision_uid CHAR(36) NULL, payload_json JSON NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_sync_changes_version (server_version), KEY idx_sync_changes_user_version (user_id, server_version), KEY idx_sync_changes_entity (entity_type, entity_uid), KEY idx_sync_changes_entity_user_version (user_id, entity_type, server_version), CONSTRAINT fk_sync_changes_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_sync_changes_revision FOREIGN KEY (revision_uid) REFERENCES note_revisions(revision_uid) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Optimistic-concurrency state for whole-note classification snapshots. CREATE TABLE note_type_sync_state ( note_uid CHAR(36) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, server_revision BIGINT UNSIGNED NOT NULL DEFAULT 0, server_version BIGINT UNSIGNED NOT NULL DEFAULT 0, types_json JSON NOT NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (note_uid, user_id), KEY idx_note_type_sync_state_user (user_id), CONSTRAINT fk_note_type_sync_state_note FOREIGN KEY (note_uid, user_id) REFERENCES notes(note_uid, user_id) ON DELETE CASCADE, CONSTRAINT fk_note_type_sync_state_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Idempotency ledger for note-type outbox changes. CREATE TABLE note_type_sync_revisions ( change_uid CHAR(36) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, note_uid CHAR(36) NOT NULL, server_revision BIGINT UNSIGNED NOT NULL, server_version BIGINT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (change_uid), KEY idx_note_type_sync_revisions_note (user_id, note_uid, server_revision), CONSTRAINT fk_note_type_sync_revision_note FOREIGN KEY (note_uid, user_id) REFERENCES notes(note_uid, user_id) ON DELETE CASCADE, CONSTRAINT fk_note_type_sync_revision_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Current related-note metadata snapshots (checklists, reminders, and NoteRelations). CREATE TABLE note_metadata_state ( note_uid CHAR(36) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, payload_json JSON NOT NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (note_uid, user_id), KEY idx_note_metadata_state_user (user_id), CONSTRAINT fk_note_metadata_state_note FOREIGN KEY (note_uid) REFERENCES notes(note_uid) ON DELETE CASCADE, CONSTRAINT fk_note_metadata_state_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Kanban tables use independent board, column, and card synchronization. -- Kanban boards are independent of note classifications; no legacy tag identity table. CREATE TABLE IF NOT EXISTS boards ( board_uid CHAR(36) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, name VARCHAR(255) NOT NULL, description TEXT NULL, position INT NOT NULL DEFAULT 0, current_revision BIGINT UNSIGNED NOT NULL DEFAULT 0, deleted_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (board_uid), UNIQUE KEY uq_boards_user_uid (user_id, board_uid), KEY idx_boards_user_order (user_id, deleted_at, position), CONSTRAINT fk_boards_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 board_columns ( column_uid CHAR(36) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, board_uid CHAR(36) NOT NULL, title VARCHAR(255) NOT NULL, color_value BIGINT NULL, workflow_tag_uid CHAR(36) NULL, position INT NOT NULL DEFAULT 0, current_revision BIGINT UNSIGNED NOT NULL DEFAULT 0, deleted_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (column_uid), UNIQUE KEY uq_board_columns_user_uid (user_id, column_uid), KEY idx_board_columns_order (user_id, board_uid, deleted_at, position), KEY idx_board_columns_workflow_note (user_id, workflow_tag_uid), CONSTRAINT fk_board_columns_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_board_columns_board FOREIGN KEY (board_uid) REFERENCES boards(board_uid) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS board_cards ( card_uid CHAR(36) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, board_uid CHAR(36) NOT NULL, column_uid CHAR(36) NOT NULL, note_uid CHAR(36) NOT NULL, position INT NOT NULL DEFAULT 0, current_revision BIGINT UNSIGNED NOT NULL DEFAULT 0, deleted_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (card_uid), UNIQUE KEY uq_board_cards_user_uid (user_id, card_uid), UNIQUE KEY uq_board_cards_board_note (board_uid, note_uid), KEY idx_board_cards_order (user_id, board_uid, column_uid, deleted_at, position), KEY idx_board_cards_note (user_id, note_uid), CONSTRAINT fk_board_cards_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_board_cards_board FOREIGN KEY (board_uid) REFERENCES boards(board_uid) ON DELETE CASCADE, CONSTRAINT fk_board_cards_column FOREIGN KEY (column_uid) REFERENCES board_columns(column_uid) ON DELETE CASCADE, CONSTRAINT fk_board_cards_note FOREIGN KEY (note_uid) REFERENCES notes(note_uid) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Idempotency ledger for non-note entity changes; note revisions remain in note_revisions. CREATE TABLE IF NOT EXISTS board_sync_revisions ( change_uid CHAR(36) NOT NULL, user_id BIGINT UNSIGNED NOT NULL, entity_type ENUM('board', 'board_column', 'board_card') NOT NULL, entity_uid CHAR(36) NOT NULL, server_revision BIGINT UNSIGNED NOT NULL, server_version BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (change_uid), KEY idx_board_sync_revisions_entity (user_id, entity_type, entity_uid, server_revision), CONSTRAINT fk_board_sync_revision_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;