Documentation
Installation guide — please follow each step carefully and exactly as described to ensure the script works correctly on your server. Skipping or modifying steps may cause the system to not function properly, so follow it step by step.
To download the assets needed for this script, you must access the official Cfx.re portal, where all assets purchased through Tebex are managed.
Open the Cfx.re granted assets page: https://portal.cfx.re/assets/granted-assets.
This page contains all assets linked to your Cfx.re purchases.
Sign in using the same Cfx.re account you used when purchasing the asset.
If you use a different account, the assets will not appear.
In the granted assets list, locate and download:
Smartphone v3
These two packages are required to install the full housing system.
These files include the models, materials, and visual resources required for the proper operation of the housing system.
This script requires some mandatory dependencies to function correctly. Make sure to download and extract them inside your server’s main directory, keeping their original folder structure intact.
Optional:
This script may cause conflicts or errors if you use other phone systems on your server. Common examples include qb-phone, esx_phone or other similar phone scripts.It’s strongly recommended to remove them completely before installation to prevent compatibility or functionality issues.
Always start your framework first (es_extended, qb-core or qbx_core). The inventory must be started directly below the framework, while the smartphone should be started below the inventory. Any scripts depending on them should be placed afterwards.
ensure es_extended
ensure qs-inventory
ensure [smartphone]
ensure qs-shops
ensure qs-dispatchNever place the inventory or smartphone above your framework.
This script includes an essential database required for its operation. You must import it before starting your server, preferably using HeidiSQL or any other manager compatible with MariaDB/MySQL.
CREATE TABLE IF NOT EXISTS `qs_phone_metadata` (
`scope_id` VARCHAR(90) NOT NULL,
`data` JSON NOT NULL,
`updated_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (`scope_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_numbers` (
`phone_number` VARCHAR(32) NOT NULL,
`owner_identifier` VARCHAR(90) NOT NULL,
`scope_id` VARCHAR(90) DEFAULT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`phone_number`),
INDEX `idx_phone_numbers_owner` (`owner_identifier`),
INDEX `idx_phone_numbers_scope` (`scope_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_notes_folders` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`scope_id` VARCHAR(90) NOT NULL,
`name` VARCHAR(100) NOT NULL,
`color` VARCHAR(20) DEFAULT '#ffffff',
`icon` VARCHAR(50) DEFAULT 'folder',
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX `idx_scope_id` (`scope_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_notes` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`scope_id` VARCHAR(90) NOT NULL,
`folder_id` INT DEFAULT NULL,
`title` VARCHAR(255) NOT NULL,
`content` TEXT,
`pinned` TINYINT(1) DEFAULT 0,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX `idx_scope_id` (`scope_id`),
INDEX `idx_folder` (`folder_id`),
INDEX `idx_notes_keyset` (`scope_id`, `pinned`, `updated_at`, `id`),
INDEX `idx_notes_folder_keyset` (`scope_id`, `folder_id`, `pinned`, `updated_at`, `id`),
FOREIGN KEY (`folder_id`) REFERENCES `qs_phone_notes_folders`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `qs_phone_notes` ADD INDEX IF NOT EXISTS `idx_notes_keyset` (`scope_id`, `pinned`, `updated_at`, `id`);
ALTER TABLE `qs_phone_notes` ADD INDEX IF NOT EXISTS `idx_notes_folder_keyset` (`scope_id`, `folder_id`, `pinned`, `updated_at`, `id`);
CREATE TABLE IF NOT EXISTS `qs_phone_gallery` (
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
`scope_id` VARCHAR(90) NOT NULL,
`media_url` VARCHAR(1024) NOT NULL,
`thumbnail_url` VARCHAR(1024) DEFAULT NULL,
`media_type` ENUM('image', 'video') NOT NULL,
`location` VARCHAR(191) DEFAULT NULL,
`album` VARCHAR(64) DEFAULT 'Camera',
`is_favorite` TINYINT(1) NOT NULL DEFAULT 0,
`duration_sec` INT UNSIGNED DEFAULT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`captured_at` TIMESTAMP NULL DEFAULT NULL,
INDEX `idx_gallery_scope_created` (`scope_id`, `created_at` DESC),
INDEX `idx_gallery_scope_type_created` (`scope_id`, `media_type`, `created_at` DESC),
INDEX `idx_gallery_scope_favorite_created` (`scope_id`, `is_favorite`, `created_at` DESC),
INDEX `idx_gallery_scope_album_created` (`scope_id`, `album`, `created_at` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_reminder_lists` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`scope_id` VARCHAR(90) NOT NULL,
`name` VARCHAR(100) NOT NULL,
`color` VARCHAR(20) DEFAULT '#0A84FF',
`icon` VARCHAR(50) DEFAULT 'list.bullet',
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX `idx_scope_id` (`scope_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_reminders` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`scope_id` VARCHAR(90) NOT NULL,
`list_id` INT DEFAULT NULL,
`title` VARCHAR(255) NOT NULL,
`notes` TEXT,
`due_at` TIMESTAMP NULL DEFAULT NULL,
`is_completed` TINYINT(1) DEFAULT 0,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX `idx_scope_id` (`scope_id`),
INDEX `idx_list` (`list_id`),
INDEX `idx_due_at` (`due_at`),
INDEX `idx_is_completed` (`is_completed`),
INDEX `idx_reminders_keyset` (`scope_id`, `is_completed`, `due_at`, `updated_at`, `id`),
FOREIGN KEY (`list_id`) REFERENCES `qs_phone_reminder_lists`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `qs_phone_reminders` ADD INDEX IF NOT EXISTS `idx_reminders_keyset` (`scope_id`, `is_completed`, `due_at`, `updated_at`, `id`);
CREATE TABLE IF NOT EXISTS `qs_phone_health` (
`scope_id` VARCHAR(90) NOT NULL,
`steps` BIGINT UNSIGNED NOT NULL DEFAULT 0,
`notice_settings` JSON NOT NULL,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`scope_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_message_participants` (
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
`phone_number` VARCHAR(32) NOT NULL,
UNIQUE KEY `ux_phone_number` (`phone_number`),
INDEX `idx_phone_number_id` (`phone_number`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_message_threads` (
`id` VARCHAR(64) PRIMARY KEY,
`title` VARCHAR(120) DEFAULT NULL,
`avatar` VARCHAR(512) DEFAULT NULL,
`is_group` TINYINT(1) NOT NULL DEFAULT 0,
`last_message_id` BIGINT DEFAULT NULL,
`last_message_preview` VARCHAR(255) NOT NULL DEFAULT '',
`last_message_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX `idx_last_message_at` (`last_message_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_message_thread_members` (
`thread_id` VARCHAR(64) NOT NULL,
`participant_id` BIGINT NOT NULL,
`last_read_message_id` BIGINT DEFAULT NULL,
`pinned` TINYINT(1) NOT NULL DEFAULT 0,
`muted` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`thread_id`, `participant_id`),
INDEX `idx_participant_flags` (`participant_id`, `pinned`, `muted`),
CONSTRAINT `fk_message_member_thread` FOREIGN KEY (`thread_id`) REFERENCES `qs_phone_message_threads`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_message_member_participant` FOREIGN KEY (`participant_id`) REFERENCES `qs_phone_message_participants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_messages` (
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
`thread_id` VARCHAR(64) NOT NULL,
`sender_participant_id` BIGINT NOT NULL,
`system_sender_label` VARCHAR(64) DEFAULT NULL,
`system_sender_key` VARCHAR(64) DEFAULT NULL,
`message_type` ENUM('text', 'image', 'video', 'payment', 'location', 'gif') NOT NULL,
`text_content` TEXT DEFAULT NULL,
`image_url` VARCHAR(1024) DEFAULT NULL,
`media_thumbnail_url` VARCHAR(1024) DEFAULT NULL,
`location_json` JSON NULL DEFAULT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX `idx_thread_created_at` (`thread_id`, `created_at`),
INDEX `idx_thread_id` (`thread_id`, `id`),
INDEX `idx_messages_thread_sender_id` (`thread_id`, `sender_participant_id`, `id`),
CONSTRAINT `fk_message_thread` FOREIGN KEY (`thread_id`) REFERENCES `qs_phone_message_threads`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_message_sender` FOREIGN KEY (`sender_participant_id`) REFERENCES `qs_phone_message_participants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_tweedle_users` (
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
`owner_identifier` VARCHAR(90) NOT NULL,
`username` VARCHAR(32) NOT NULL,
`password_salt` VARCHAR(64) NOT NULL,
`password_hash` VARCHAR(128) NOT NULL,
`name` VARCHAR(80) NOT NULL,
`avatar` VARCHAR(512) NOT NULL DEFAULT '',
`banner` VARCHAR(512) NOT NULL DEFAULT '',
`bio` VARCHAR(280) NOT NULL DEFAULT '',
`verified` TINYINT(1) NOT NULL DEFAULT 0,
`joined_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY `ux_tweedle_users_username` (`username`),
INDEX `idx_tweedle_users_owner` (`owner_identifier`),
INDEX `idx_tweedle_users_joined` (`joined_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_tweedle_sessions` (
`scope_id` VARCHAR(90) NOT NULL PRIMARY KEY,
`user_id` BIGINT NOT NULL,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX `idx_tweedle_sessions_user` (`user_id`),
CONSTRAINT `fk_tweedle_session_user` FOREIGN KEY (`user_id`) REFERENCES `qs_phone_tweedle_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_tweedle_follows` (
`follower_user_id` BIGINT NOT NULL,
`target_user_id` BIGINT NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`follower_user_id`, `target_user_id`),
INDEX `idx_tweedle_follows_target_created` (`target_user_id`, `created_at`),
INDEX `idx_tweedle_follows_follower_created` (`follower_user_id`, `created_at`),
CONSTRAINT `fk_tweedle_follows_follower` FOREIGN KEY (`follower_user_id`) REFERENCES `qs_phone_tweedle_users`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_tweedle_follows_target` FOREIGN KEY (`target_user_id`) REFERENCES `qs_phone_tweedle_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_tweedle_posts` (
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
`user_id` BIGINT NOT NULL,
`parent_post_id` BIGINT DEFAULT NULL,
`text_content` TEXT NOT NULL,
`media_url` VARCHAR(1024) DEFAULT NULL,
`reply_count` INT UNSIGNED NOT NULL DEFAULT 0,
`like_count` INT UNSIGNED NOT NULL DEFAULT 0,
`repost_count` INT UNSIGNED NOT NULL DEFAULT 0,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX `idx_tweedle_posts_created` (`created_at`, `id`),
INDEX `idx_tweedle_posts_user_created` (`user_id`, `created_at`),
INDEX `idx_tweedle_posts_user_parent_created` (`user_id`, `parent_post_id`, `created_at`),
INDEX `idx_tweedle_posts_parent_created` (`parent_post_id`, `created_at`),
CONSTRAINT `fk_tweedle_posts_user` FOREIGN KEY (`user_id`) REFERENCES `qs_phone_tweedle_users`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_tweedle_posts_parent` FOREIGN KEY (`parent_post_id`) REFERENCES `qs_phone_tweedle_posts`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_tweedle_post_reactions` (
`post_id` BIGINT NOT NULL,
`user_id` BIGINT NOT NULL,
`reaction_type` ENUM('like', 'repost') NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`post_id`, `user_id`, `reaction_type`),
INDEX `idx_tweedle_reactions_user_type_created` (`user_id`, `reaction_type`, `created_at`),
CONSTRAINT `fk_tweedle_reactions_post` FOREIGN KEY (`post_id`) REFERENCES `qs_phone_tweedle_posts`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_tweedle_reactions_user` FOREIGN KEY (`user_id`) REFERENCES `qs_phone_tweedle_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_tweedle_notifications` (
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
`recipient_user_id` BIGINT NOT NULL,
`actor_user_id` BIGINT NOT NULL,
`post_id` BIGINT DEFAULT NULL,
`notification_type` ENUM('like', 'reply', 'follow', 'repost', 'mention') NOT NULL,
`is_read` TINYINT(1) NOT NULL DEFAULT 0,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX `idx_tweedle_notifications_recipient_created` (`recipient_user_id`, `created_at`),
INDEX `idx_tweedle_notifications_recipient_read` (`recipient_user_id`, `is_read`, `created_at`),
CONSTRAINT `fk_tweedle_notifications_recipient` FOREIGN KEY (`recipient_user_id`) REFERENCES `qs_phone_tweedle_users`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_tweedle_notifications_actor` FOREIGN KEY (`actor_user_id`) REFERENCES `qs_phone_tweedle_users`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_tweedle_notifications_post` FOREIGN KEY (`post_id`) REFERENCES `qs_phone_tweedle_posts`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_tweedle_threads` (
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
`title` VARCHAR(120) DEFAULT NULL,
`avatar` VARCHAR(512) DEFAULT NULL,
`is_group` TINYINT(1) NOT NULL DEFAULT 0,
`last_message_id` BIGINT DEFAULT NULL,
`last_message_preview` VARCHAR(255) NOT NULL DEFAULT '',
`last_message_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX `idx_tweedle_threads_last_message_at` (`last_message_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_tweedle_thread_members` (
`thread_id` BIGINT NOT NULL,
`user_id` BIGINT NOT NULL,
`last_read_message_id` BIGINT DEFAULT NULL,
`pinned` TINYINT(1) NOT NULL DEFAULT 0,
`muted` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`thread_id`, `user_id`),
INDEX `idx_tweedle_thread_members_user_flags` (`user_id`, `pinned`, `muted`),
CONSTRAINT `fk_tweedle_thread_members_thread` FOREIGN KEY (`thread_id`) REFERENCES `qs_phone_tweedle_threads`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_tweedle_thread_members_user` FOREIGN KEY (`user_id`) REFERENCES `qs_phone_tweedle_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_tweedle_messages` (
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
`thread_id` BIGINT NOT NULL,
`sender_user_id` BIGINT NOT NULL,
`message_type` ENUM('text', 'image', 'video', 'location', 'gif') NOT NULL,
`text_content` TEXT DEFAULT NULL,
`image_url` VARCHAR(1024) DEFAULT NULL,
`video_url` VARCHAR(1024) DEFAULT NULL,
`media_thumbnail_url` VARCHAR(1024) DEFAULT NULL,
`location_json` JSON NULL DEFAULT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX `idx_tweedle_messages_thread_id` (`thread_id`, `id`),
INDEX `idx_tweedle_messages_thread_created` (`thread_id`, `created_at`),
CONSTRAINT `fk_tweedle_messages_thread` FOREIGN KEY (`thread_id`) REFERENCES `qs_phone_tweedle_threads`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_tweedle_messages_sender` FOREIGN KEY (`sender_user_id`) REFERENCES `qs_phone_tweedle_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE INDEX IF NOT EXISTS idx_tweedle_posts_root_time ON qs_phone_tweedle_posts (parent_post_id, created_at, id);
CREATE INDEX IF NOT EXISTS idx_tweedle_posts_user_root_time ON qs_phone_tweedle_posts (user_id, parent_post_id, created_at, id);
CREATE INDEX IF NOT EXISTS idx_tweedle_posts_parent_time ON qs_phone_tweedle_posts (parent_post_id, created_at, id);
CREATE INDEX IF NOT EXISTS idx_tweedle_notifications_recipient_time ON qs_phone_tweedle_notifications (recipient_user_id, created_at, id);
CREATE INDEX IF NOT EXISTS idx_tweedle_messages_thread_id ON qs_phone_tweedle_messages (thread_id, id);
CREATE INDEX IF NOT EXISTS idx_tweedle_follows_follower ON qs_phone_tweedle_follows (follower_user_id, target_user_id);
CREATE INDEX IF NOT EXISTS idx_tweedle_thread_members_user ON qs_phone_tweedle_thread_members (user_id, pinned, thread_id);
CREATE TABLE IF NOT EXISTS `qs_phone_contacts` (
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
`scope_id` VARCHAR(90) NOT NULL,
`display_name` VARCHAR(120) NOT NULL,
`number` VARCHAR(32) NOT NULL,
`avatar` VARCHAR(512) DEFAULT NULL,
`note` TEXT DEFAULT NULL,
`favorite` TINYINT(1) NOT NULL DEFAULT 0,
`blocked` TINYINT(1) NOT NULL DEFAULT 0,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY `ux_phone_contacts_scope_number` (`scope_id`, `number`),
INDEX `idx_phone_contacts_scope_display` (`scope_id`, `display_name`),
INDEX `idx_phone_contacts_scope_updated` (`scope_id`, `updated_at`),
INDEX `idx_phone_contacts_scope_number_display` (`scope_id`, `number`, `display_name`),
INDEX `idx_phone_contacts_scope_favorite` (`scope_id`, `favorite`),
INDEX `idx_phone_contacts_scope_blocked` (`scope_id`, `blocked`),
INDEX `idx_phone_contacts_number_scope` (`number`, `scope_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `qs_phone_contacts` ADD COLUMN IF NOT EXISTS `blocked` TINYINT(1) NOT NULL DEFAULT 0 AFTER `favorite`;
ALTER TABLE `qs_phone_contacts` ADD INDEX IF NOT EXISTS `idx_phone_contacts_scope_blocked` (`scope_id`, `blocked`);
CREATE TABLE IF NOT EXISTS `qs_phone_call_recents` (
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
`scope_id` VARCHAR(90) NOT NULL,
`number` VARCHAR(32) NOT NULL,
`direction` ENUM('incoming', 'outgoing', 'missed') NOT NULL,
`call_type` ENUM('audio', 'video') NOT NULL DEFAULT 'audio',
`call_timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX `idx_phone_call_recents_scope_timestamp` (`scope_id`, `call_timestamp`),
INDEX `idx_phone_call_recents_scope_number` (`scope_id`, `number`),
INDEX `idx_phone_call_recents_scope_type_ts` (`scope_id`, `call_type`, `call_timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_notifications` (
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
`scope_id` VARCHAR(90) NOT NULL,
`app_id` VARCHAR(64) NOT NULL,
`title` VARCHAR(120) NOT NULL,
`subtitle` VARCHAR(160) DEFAULT NULL,
`text` VARCHAR(512) NOT NULL,
`metadata_json` JSON NULL DEFAULT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`read_at` TIMESTAMP NULL DEFAULT NULL,
INDEX `idx_phone_notifications_scope_created` (`scope_id`, `created_at`),
INDEX `idx_phone_notifications_scope_created_id` (`scope_id`, `created_at`, `id`),
INDEX `idx_phone_notifications_scope_read` (`scope_id`, `read_at`),
INDEX `idx_phone_notifications_scope_app_read` (`scope_id`, `app_id`, `read_at`),
INDEX `idx_phone_notifications_scope_app_created` (`scope_id`, `app_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_music_favorites` (
`scope_id` VARCHAR(90) NOT NULL,
`track_id` VARCHAR(191) NOT NULL,
`title` VARCHAR(255) DEFAULT NULL,
`artist` VARCHAR(255) DEFAULT NULL,
`album` VARCHAR(255) DEFAULT NULL,
`artwork_url` VARCHAR(1024) DEFAULT NULL,
`stream_url` VARCHAR(1024) DEFAULT NULL,
`match_key` VARCHAR(64) DEFAULT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`scope_id`, `track_id`),
INDEX `idx_music_favorites_scope_created` (`scope_id`, `created_at`),
INDEX `idx_music_favorites_scope_match` (`scope_id`, `match_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `qs_phone_music_favorites` ADD COLUMN IF NOT EXISTS `title` VARCHAR(255) DEFAULT NULL;
ALTER TABLE `qs_phone_music_favorites` ADD COLUMN IF NOT EXISTS `artist` VARCHAR(255) DEFAULT NULL;
ALTER TABLE `qs_phone_music_favorites` ADD COLUMN IF NOT EXISTS `album` VARCHAR(255) DEFAULT NULL;
ALTER TABLE `qs_phone_music_favorites` ADD COLUMN IF NOT EXISTS `artwork_url` VARCHAR(1024) DEFAULT NULL;
ALTER TABLE `qs_phone_music_favorites` ADD COLUMN IF NOT EXISTS `stream_url` VARCHAR(1024) DEFAULT NULL;
ALTER TABLE `qs_phone_music_favorites` ADD COLUMN IF NOT EXISTS `match_key` VARCHAR(64) DEFAULT NULL;
ALTER TABLE `qs_phone_music_favorites` ADD INDEX IF NOT EXISTS `idx_music_favorites_scope_match` (`scope_id`, `match_key`);
CREATE TABLE IF NOT EXISTS `qs_phone_music_recently_played` (
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
`scope_id` VARCHAR(90) NOT NULL,
`track_id` VARCHAR(191) NOT NULL,
`played_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX `idx_music_recent_scope_played` (`scope_id`, `played_at`),
INDEX `idx_music_recent_scope_track` (`scope_id`, `track_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_clock_alarms` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`scope_id` VARCHAR(90) NOT NULL,
`label` VARCHAR(48) NOT NULL,
`hour` TINYINT UNSIGNED NOT NULL,
`minute` TINYINT UNSIGNED NOT NULL,
`enabled` TINYINT(1) NOT NULL DEFAULT 1,
`cycle` ENUM('everyday', 'weekdays', 'weekend', 'once') NOT NULL DEFAULT 'once',
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX `idx_clock_scope_time` (`scope_id`, `hour`, `minute`),
INDEX `idx_clock_scope_enabled` (`scope_id`, `enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_store_downloads` (
`scope_id` VARCHAR(90) NOT NULL,
`app_id` VARCHAR(64) NOT NULL,
`size_mb` INT UNSIGNED NOT NULL,
`seconds_per_mb` INT UNSIGNED NOT NULL,
`started_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`ends_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`completed` TINYINT(1) NOT NULL DEFAULT 0,
`installed_at` TIMESTAMP NULL DEFAULT NULL,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`scope_id`, `app_id`),
INDEX `idx_store_downloads_scope` (`scope_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_crypto_wallets` (
`scope_id` VARCHAR(90) NOT NULL,
`fiat` INT(11) NOT NULL DEFAULT 0,
`holdings_json` JSON NOT NULL,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`scope_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_crypto_history` (
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
`scope_id` VARCHAR(90) NOT NULL,
`type` ENUM('buy', 'sell', 'transfer') NOT NULL,
`coin_id` VARCHAR(64) NOT NULL,
`symbol` VARCHAR(32) NOT NULL,
`amount_coin` INT(11) NOT NULL DEFAULT 0,
`amount_fiat` INT(11) NOT NULL DEFAULT 0,
`fee_fiat` INT(11) NOT NULL DEFAULT 0,
`target_phone` VARCHAR(32) DEFAULT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX `idx_crypto_history_scope_created` (`scope_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_weazel_articles` (
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
`category_id` VARCHAR(32) NOT NULL,
`title` VARCHAR(200) NOT NULL,
`subtitle` VARCHAR(400) NOT NULL DEFAULT '',
`body` MEDIUMTEXT NOT NULL,
`image_url` VARCHAR(2000) DEFAULT NULL,
`author_scope_id` VARCHAR(90) NOT NULL,
`author_name` VARCHAR(120) NOT NULL,
`is_breaking` TINYINT(1) NOT NULL DEFAULT 0,
`published_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
INDEX `idx_weazel_published` (`published_at`),
INDEX `idx_weazel_cat_published` (`category_id`, `published_at`),
INDEX `idx_weazel_breaking_published` (`is_breaking`, `published_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_weazel_bookmarks` (
`scope_id` VARCHAR(90) NOT NULL,
`article_id` BIGINT NOT NULL,
PRIMARY KEY (`scope_id`, `article_id`),
INDEX `idx_weazel_bm_scope` (`scope_id`),
CONSTRAINT `fk_weazel_bm_article` FOREIGN KEY (`article_id`) REFERENCES `qs_phone_weazel_articles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_mail_accounts` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`owner_identifier` VARCHAR(90) NOT NULL,
`email` VARCHAR(255) NOT NULL,
`password_salt` VARCHAR(64) NOT NULL,
`password_hash` CHAR(64) NOT NULL,
`auth_version` INT UNSIGNED NOT NULL DEFAULT 0,
`display_name` VARCHAR(120) NOT NULL,
`avatar_url` VARCHAR(512) DEFAULT NULL,
`created_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`updated_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
UNIQUE KEY `ux_mail_email` (`email`),
INDEX `idx_mail_accounts_email` (`email`),
INDEX `idx_mail_accounts_owner` (`owner_identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_app_sessions` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`device_scope_id` VARCHAR(90) NOT NULL,
`app_id` VARCHAR(32) NOT NULL,
`account_key` VARCHAR(128) NOT NULL,
`session_token` VARCHAR(96) NOT NULL,
`expires_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`credential_rev` INT UNSIGNED NOT NULL DEFAULT 0,
`created_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
UNIQUE KEY `ux_app_session_device_app` (`device_scope_id`, `app_id`),
UNIQUE KEY `ux_app_session_token` (`session_token`),
INDEX `idx_app_session_device_exp` (`device_scope_id`, `expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_mail_messages` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`account_id` BIGINT UNSIGNED NOT NULL,
`folder` ENUM('inbox', 'sent', 'drafts', 'trash') NOT NULL,
`subject` VARCHAR(512) NOT NULL DEFAULT '',
`preview` VARCHAR(255) NOT NULL DEFAULT '',
`body` MEDIUMTEXT NOT NULL,
`from_email` VARCHAR(255) NOT NULL,
`from_name` VARCHAR(120) DEFAULT NULL,
`from_avatar_url` VARCHAR(512) DEFAULT NULL,
`recipient_avatar_url` VARCHAR(512) DEFAULT NULL,
`to_emails` JSON NOT NULL,
`cc_emails` JSON NOT NULL,
`is_read` TINYINT(1) NOT NULL DEFAULT 0,
`is_starred` TINYINT(1) NOT NULL DEFAULT 0,
`reply_to_message_id` BIGINT UNSIGNED DEFAULT NULL,
`created_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
INDEX `idx_mail_account_folder_created` (`account_id`, `folder`, `created_at`, `id`),
INDEX `idx_mail_account_folder` (`account_id`, `folder`),
CONSTRAINT `fk_mail_msg_account` FOREIGN KEY (`account_id`) REFERENCES `qs_phone_mail_accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_darkchat_accounts` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`owner_identifier` VARCHAR(90) NOT NULL,
`handle` VARCHAR(24) NOT NULL,
`password_salt` VARCHAR(64) NOT NULL,
`password_hash` CHAR(64) NOT NULL,
`display_name` VARCHAR(120) NOT NULL,
`avatar_url` VARCHAR(2048) DEFAULT NULL,
`created_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`updated_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
UNIQUE KEY `ux_darkchat_handle` (`handle`),
INDEX `idx_darkchat_handle` (`handle`),
INDEX `idx_darkchat_owner` (`owner_identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_darkchat_channels` (
`id` VARCHAR(64) NOT NULL,
`name` VARCHAR(120) NOT NULL,
`description` VARCHAR(500) NOT NULL DEFAULT '',
`owner_account_id` BIGINT UNSIGNED NOT NULL,
`invite_code` VARCHAR(16) NOT NULL,
`last_message_preview` VARCHAR(255) NOT NULL DEFAULT '',
`last_message_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`created_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
UNIQUE KEY `ux_darkchat_invite` (`invite_code`),
INDEX `idx_darkchat_channel_owner` (`owner_account_id`),
INDEX `idx_darkchat_channel_last_at` (`last_message_at` DESC, `id` DESC),
CONSTRAINT `fk_darkchat_channel_owner` FOREIGN KEY (`owner_account_id`) REFERENCES `qs_phone_darkchat_accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_darkchat_channel_members` (
`channel_id` VARCHAR(64) NOT NULL,
`account_id` BIGINT UNSIGNED NOT NULL,
`read_up_to_message_id` BIGINT UNSIGNED DEFAULT NULL,
`joined_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (`channel_id`, `account_id`),
INDEX `idx_darkchat_member_account` (`account_id`, `channel_id`),
CONSTRAINT `fk_darkchat_member_channel` FOREIGN KEY (`channel_id`) REFERENCES `qs_phone_darkchat_channels` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_darkchat_member_account` FOREIGN KEY (`account_id`) REFERENCES `qs_phone_darkchat_accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_darkchat_messages` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`channel_id` VARCHAR(64) NOT NULL,
`sender_account_id` BIGINT UNSIGNED NOT NULL,
`content_type` ENUM('text', 'image', 'video', 'location', 'gif') NOT NULL DEFAULT 'text',
`text` TEXT,
`image_url` VARCHAR(2048) DEFAULT NULL,
`media_thumbnail_url` VARCHAR(2048) DEFAULT NULL,
`location_json` JSON NULL DEFAULT NULL,
`created_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
INDEX `idx_darkchat_msg_channel_id` (`channel_id`, `id` DESC),
INDEX `idx_darkchat_msg_channel_created` (`channel_id`, `created_at` DESC, `id` DESC),
CONSTRAINT `fk_darkchat_msg_channel` FOREIGN KEY (`channel_id`) REFERENCES `qs_phone_darkchat_channels` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_darkchat_msg_sender` FOREIGN KEY (`sender_account_id`) REFERENCES `qs_phone_darkchat_accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_zappeats_accounts` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`owner_identifier` VARCHAR(90) NOT NULL,
`handle` VARCHAR(24) NOT NULL,
`password_salt` VARCHAR(64) NOT NULL,
`password_hash` CHAR(64) NOT NULL,
`display_name` VARCHAR(120) NOT NULL,
`avatar_url` VARCHAR(512) NULL DEFAULT NULL,
`total_deliveries` INT UNSIGNED NOT NULL DEFAULT 0,
`total_stars` INT UNSIGNED NOT NULL DEFAULT 0,
`total_points` INT UNSIGNED NOT NULL DEFAULT 0,
`total_payout` INT UNSIGNED NOT NULL DEFAULT 0,
`on_time_streak` INT UNSIGNED NOT NULL DEFAULT 0,
`created_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`updated_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
UNIQUE KEY `ux_zappeats_handle` (`handle`),
INDEX `idx_zappeats_handle` (`handle`),
INDEX `idx_zappeats_owner` (`owner_identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_zappeats_deliveries` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`account_id` BIGINT UNSIGNED NOT NULL,
`item_key` VARCHAR(64) NOT NULL,
`item_label` VARCHAR(120) NOT NULL,
`payout` INT UNSIGNED NOT NULL,
`stars` TINYINT UNSIGNED NOT NULL,
`points_earned` INT UNSIGNED NOT NULL DEFAULT 0,
`duration_ms` BIGINT UNSIGNED NOT NULL,
`allowed_ms` BIGINT UNSIGNED NOT NULL,
`on_time` TINYINT(1) NOT NULL DEFAULT 0,
`completed_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
INDEX `idx_zappeats_del_account_completed` (`account_id`, `completed_at` DESC),
CONSTRAINT `fk_zappeats_del_account` FOREIGN KEY (`account_id`) REFERENCES `qs_phone_zappeats_accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_zapp_accounts` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`owner_identifier` VARCHAR(90) NOT NULL,
`handle` VARCHAR(24) NOT NULL,
`password_salt` VARCHAR(64) NOT NULL,
`password_hash` CHAR(64) NOT NULL,
`display_name` VARCHAR(120) NOT NULL,
`avatar_url` VARCHAR(512) NULL DEFAULT NULL,
`credential_rev` INT UNSIGNED NOT NULL DEFAULT 0,
`trips_completed_as_customer` INT UNSIGNED NOT NULL DEFAULT 0,
`trips_completed_as_driver` INT UNSIGNED NOT NULL DEFAULT 0,
`rating_driver` DECIMAL(4,2) NOT NULL DEFAULT 5.00,
`created_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`updated_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
UNIQUE KEY `ux_zapp_handle` (`handle`),
INDEX `idx_zapp_handle` (`handle`),
INDEX `idx_zapp_owner` (`owner_identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_zapp_trips` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`public_ride_id` VARCHAR(64) NOT NULL,
`customer_account_id` BIGINT UNSIGNED NOT NULL,
`driver_account_id` BIGINT UNSIGNED NOT NULL,
`customer_display_name` VARCHAR(120) NOT NULL,
`driver_display_name` VARCHAR(120) NOT NULL,
`status` VARCHAR(20) NOT NULL,
`pickup_label` VARCHAR(512) NOT NULL,
`dropoff_label` VARCHAR(512) NOT NULL,
`vehicle_class` VARCHAR(16) NOT NULL,
`note` VARCHAR(512) NULL DEFAULT NULL,
`fare_estimate` INT(11) NOT NULL,
`final_fare` INT(11) NULL DEFAULT NULL,
`vehicle_snapshot_json` JSON NULL,
`completed_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
UNIQUE KEY `ux_zapp_trips_public` (`public_ride_id`),
INDEX `idx_zapp_trips_customer` (`customer_account_id`, `id` DESC),
INDEX `idx_zapp_trips_driver` (`driver_account_id`, `id` DESC),
CONSTRAINT `fk_zapp_trip_customer` FOREIGN KEY (`customer_account_id`) REFERENCES `qs_phone_zapp_accounts` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_zapp_trip_driver` FOREIGN KEY (`driver_account_id`) REFERENCES `qs_phone_zapp_accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_yellowpages_posts` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`owner_identifier` VARCHAR(90) NOT NULL,
`owner_phone` VARCHAR(32) NOT NULL,
`title` VARCHAR(200) NOT NULL,
`description` TEXT NOT NULL,
`image_urls` JSON NOT NULL,
`price` INT(11) DEFAULT NULL,
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
INDEX `idx_yellowpages_created_id` (`created_at` DESC, `id` DESC),
INDEX `idx_yellowpages_owner` (`owner_identifier`),
FULLTEXT INDEX `ft_yellowpages_title_desc` (`title`, `description`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `qs_phone_yellowpages_posts` ADD INDEX IF NOT EXISTS `idx_yellowpages_created_id` (`created_at` DESC, `id` DESC);
ALTER TABLE `qs_phone_yellowpages_posts` ADD FULLTEXT INDEX IF NOT EXISTS `ft_yellowpages_title_desc` (`title`, `description`);
CREATE TABLE IF NOT EXISTS `qs_phone_wallet_transfers` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`sender_scope_id` VARCHAR(90) NOT NULL,
`sender_phone` VARCHAR(32) NOT NULL,
`recipient_phone` VARCHAR(32) NOT NULL,
`amount` INT NOT NULL,
`memo` VARCHAR(200) NULL DEFAULT NULL,
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
INDEX `idx_wallet_sender_scope_created` (`sender_scope_id`, `created_at`),
INDEX `idx_wallet_recipient_phone_created` (`recipient_phone`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_bills` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`identifier` VARCHAR(90) NOT NULL,
`sender` VARCHAR(90) NOT NULL DEFAULT '',
`job` VARCHAR(50) NULL DEFAULT NULL,
`title` VARCHAR(80) NOT NULL DEFAULT '',
`subtitle` VARCHAR(120) NULL DEFAULT NULL,
`price` INT NOT NULL DEFAULT 0,
`avatar` VARCHAR(512) NULL DEFAULT NULL,
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
INDEX `idx_phone_bills_identifier` (`identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_market_accounts` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`owner_identifier` VARCHAR(90) NOT NULL,
`phone` VARCHAR(32) NOT NULL,
`username` VARCHAR(64) NOT NULL,
`display_name` VARCHAR(120) NOT NULL,
`avatar_url` VARCHAR(512) NULL DEFAULT NULL,
`password_salt` VARCHAR(64) NOT NULL,
`password_hash` CHAR(64) NOT NULL,
`credential_rev` INT UNSIGNED NOT NULL DEFAULT 0,
`created_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`updated_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
UNIQUE KEY `ux_market_username` (`username`),
UNIQUE KEY `ux_market_phone` (`phone`),
INDEX `idx_market_owner_identifier` (`owner_identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_market_shops` (
`id` INT NOT NULL,
`name` VARCHAR(120) NOT NULL,
`description` TEXT NOT NULL,
`image_url` VARCHAR(512) NOT NULL,
`coords_json` JSON NOT NULL,
`jobs_json` JSON NOT NULL,
`balance` BIGINT NOT NULL DEFAULT 0,
`updated_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_market_ratings` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`account_id` BIGINT UNSIGNED NOT NULL,
`shop_id` INT NOT NULL,
`rating` TINYINT UNSIGNED NOT NULL,
`created_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
UNIQUE KEY `ux_market_rating_account_shop` (`account_id`, `shop_id`),
INDEX `idx_market_rating_shop` (`shop_id`),
CONSTRAINT `fk_market_rating_account` FOREIGN KEY (`account_id`) REFERENCES `qs_phone_market_accounts` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_market_rating_shop` FOREIGN KEY (`shop_id`) REFERENCES `qs_phone_market_shops` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_market_threads` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`customer_account_id` BIGINT UNSIGNED NOT NULL,
`shop_id` INT NOT NULL,
`last_message_id` BIGINT UNSIGNED NULL DEFAULT NULL,
`last_message_preview` VARCHAR(255) NOT NULL DEFAULT '',
`last_message_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`created_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
UNIQUE KEY `ux_market_thread_customer_shop` (`customer_account_id`, `shop_id`),
INDEX `idx_market_thread_shop_last` (`shop_id`, `last_message_at` DESC),
INDEX `idx_market_thread_customer_last` (`customer_account_id`, `last_message_at` DESC),
CONSTRAINT `fk_market_thread_customer` FOREIGN KEY (`customer_account_id`) REFERENCES `qs_phone_market_accounts` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_market_thread_shop` FOREIGN KEY (`shop_id`) REFERENCES `qs_phone_market_shops` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_market_messages` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`thread_id` BIGINT UNSIGNED NOT NULL,
`sender_is_customer` TINYINT(1) NOT NULL,
`content_type` ENUM('text', 'image', 'video', 'location', 'gif') NOT NULL,
`text_content` TEXT NULL DEFAULT NULL,
`media_url` VARCHAR(1024) NULL DEFAULT NULL,
`thumb_url` VARCHAR(512) NULL DEFAULT NULL,
`location_json` JSON NULL DEFAULT NULL,
`created_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
INDEX `idx_market_msg_thread_created` (`thread_id`, `created_at`),
INDEX `idx_market_msg_thread_id` (`thread_id`, `id`),
CONSTRAINT `fk_market_msg_thread` FOREIGN KEY (`thread_id`) REFERENCES `qs_phone_market_threads` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_market_thread_reads` (
`thread_id` BIGINT UNSIGNED NOT NULL,
`reader_account_id` BIGINT UNSIGNED NOT NULL,
`last_read_message_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
`updated_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (`thread_id`, `reader_account_id`),
CONSTRAINT `fk_market_read_thread` FOREIGN KEY (`thread_id`) REFERENCES `qs_phone_market_threads` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_market_read_reader` FOREIGN KEY (`reader_account_id`) REFERENCES `qs_phone_market_accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_chitchat_accounts` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`owner_identifier` VARCHAR(90) NOT NULL,
`peer_id` VARCHAR(96) NOT NULL,
`phone` VARCHAR(32) NOT NULL,
`display_name` VARCHAR(120) NOT NULL,
`bio` VARCHAR(500) NOT NULL DEFAULT '',
`avatar_url` VARCHAR(1024) NOT NULL,
`read_receipts_enabled` TINYINT(1) NOT NULL DEFAULT 1,
`credential_rev` INT UNSIGNED NOT NULL DEFAULT 0,
`created_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`updated_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
UNIQUE KEY `ux_chitchat_owner` (`owner_identifier`),
UNIQUE KEY `ux_chitchat_peer` (`peer_id`),
UNIQUE KEY `ux_chitchat_phone` (`phone`),
INDEX `idx_chitchat_phone_peer` (`phone`, `peer_id`),
INDEX `idx_chitchat_owner_updated` (`owner_identifier`, `updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_chitchat_verifications` (
`phone` VARCHAR(32) NOT NULL,
`code` VARCHAR(12) NOT NULL,
`expires_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`created_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_chitchat_groups` (
`group_peer_id` VARCHAR(96) NOT NULL,
`owner_identifier` VARCHAR(90) NOT NULL,
`title` VARCHAR(120) NOT NULL,
`bio` VARCHAR(500) NOT NULL DEFAULT '',
`avatar_url` VARCHAR(1024) NOT NULL,
`created_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`updated_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (`group_peer_id`),
INDEX `idx_chitchat_group_owner` (`owner_identifier`, `updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_chitchat_group_members` (
`group_peer_id` VARCHAR(96) NOT NULL,
`member_peer_id` VARCHAR(96) NOT NULL,
`is_admin` TINYINT(1) NOT NULL DEFAULT 0,
`joined_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (`group_peer_id`, `member_peer_id`),
INDEX `idx_chitchat_group_member_peer` (`member_peer_id`),
INDEX `idx_chitchat_grp_members_group_joined` (`group_peer_id`, `joined_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_chitchat_threads` (
`id` VARCHAR(96) NOT NULL,
`owner_identifier` VARCHAR(90) NOT NULL,
`peer_id` VARCHAR(96) NOT NULL,
`counterpart_peer_id` VARCHAR(96) NOT NULL,
`title` VARCHAR(120) NOT NULL,
`avatar_url` VARCHAR(1024) NOT NULL,
`bio` VARCHAR(500) NOT NULL DEFAULT '',
`is_group` TINYINT(1) NOT NULL DEFAULT 0,
`last_message_preview` VARCHAR(255) NOT NULL DEFAULT '',
`last_message_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`last_message_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
`unread_count_cache` INT UNSIGNED NOT NULL DEFAULT 0,
`wallpaper_url` VARCHAR(1024) DEFAULT NULL,
`created_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`updated_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
UNIQUE KEY `ux_chitchat_owner_counterpart` (`owner_identifier`, `counterpart_peer_id`),
INDEX `idx_chitchat_owner_last` (`owner_identifier`, `last_message_at`),
INDEX `idx_chitchat_owner_updated` (`owner_identifier`, `updated_at`),
INDEX `idx_chitchat_threads_counterpart` (`counterpart_peer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_chitchat_thread_members` (
`thread_id` VARCHAR(96) NOT NULL,
`member_peer_id` VARCHAR(96) NOT NULL,
`is_admin` TINYINT(1) NOT NULL DEFAULT 0,
`last_read_message_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`thread_id`, `member_peer_id`),
INDEX `idx_chitchat_thread_member_peer` (`member_peer_id`, `thread_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_chitchat_messages` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`thread_id` VARCHAR(96) NOT NULL,
`sender_peer_id` VARCHAR(96) NOT NULL,
`message_type` ENUM('text', 'image', 'voice', 'video', 'location', 'gif') NOT NULL,
`body` TEXT NOT NULL,
`media_url` MEDIUMTEXT NULL,
`location_json` JSON DEFAULT NULL,
`sent_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
INDEX `idx_chitchat_msg_thread_id` (`thread_id`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_chitchat_statuses` (
`id` VARCHAR(96) NOT NULL,
`owner_identifier` VARCHAR(90) NOT NULL,
`author_peer_id` VARCHAR(96) NOT NULL,
`author_name` VARCHAR(120) NOT NULL,
`author_avatar_url` VARCHAR(1024) NOT NULL,
`preview_text` VARCHAR(512) NOT NULL,
`media_url` VARCHAR(1024) DEFAULT NULL,
`created_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
INDEX `idx_chitchat_status_owner_created` (`owner_identifier`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_finder_accounts` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`owner_identifier` VARCHAR(90) NOT NULL,
`username` VARCHAR(64) NOT NULL,
`display_name` VARCHAR(120) NOT NULL,
`bio` VARCHAR(1024) NOT NULL DEFAULT '',
`birthdate` VARCHAR(16) NOT NULL DEFAULT '',
`gender` ENUM('male', 'female', 'non-binary') NOT NULL DEFAULT 'non-binary',
`interested_in` ENUM('men', 'women', 'everyone') NOT NULL DEFAULT 'everyone',
`photos` JSON NOT NULL,
`password_salt` VARCHAR(64) NOT NULL,
`password_hash` CHAR(64) NOT NULL,
`credential_rev` INT UNSIGNED NOT NULL DEFAULT 0,
`created_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`updated_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
UNIQUE KEY `ux_finder_username` (`username`),
INDEX `idx_finder_owner` (`owner_identifier`),
INDEX `idx_finder_updated` (`updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_finder_swipes` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`from_account_id` BIGINT UNSIGNED NOT NULL,
`to_account_id` BIGINT UNSIGNED NOT NULL,
`action` ENUM('like', 'dislike') NOT NULL,
`created_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
UNIQUE KEY `ux_finder_swipe_pair` (`from_account_id`, `to_account_id`),
INDEX `idx_finder_swipes_to` (`to_account_id`, `action`, `created_at`),
CONSTRAINT `fk_finder_swipe_from` FOREIGN KEY (`from_account_id`) REFERENCES `qs_phone_finder_accounts` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_finder_swipe_to` FOREIGN KEY (`to_account_id`) REFERENCES `qs_phone_finder_accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_finder_threads` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`account_a_id` BIGINT UNSIGNED NOT NULL,
`account_b_id` BIGINT UNSIGNED NOT NULL,
`last_message_id` BIGINT UNSIGNED NULL DEFAULT NULL,
`last_message_preview` VARCHAR(255) NOT NULL DEFAULT '',
`last_message_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
`created_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
UNIQUE KEY `ux_finder_thread_pair` (`account_a_id`, `account_b_id`),
INDEX `idx_finder_thread_a_last` (`account_a_id`, `last_message_at` DESC),
INDEX `idx_finder_thread_b_last` (`account_b_id`, `last_message_at` DESC),
CONSTRAINT `fk_finder_thread_a` FOREIGN KEY (`account_a_id`) REFERENCES `qs_phone_finder_accounts` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_finder_thread_b` FOREIGN KEY (`account_b_id`) REFERENCES `qs_phone_finder_accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_finder_messages` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`thread_id` BIGINT UNSIGNED NOT NULL,
`sender_account_id` BIGINT UNSIGNED NOT NULL,
`content_type` ENUM('text', 'image', 'video', 'location', 'gif') NOT NULL,
`text_content` TEXT NULL DEFAULT NULL,
`location_json` JSON NULL DEFAULT NULL,
`created_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`),
INDEX `idx_finder_msg_thread_created` (`thread_id`, `created_at`),
INDEX `idx_finder_msg_thread_id` (`thread_id`, `id`),
CONSTRAINT `fk_finder_msg_thread` FOREIGN KEY (`thread_id`) REFERENCES `qs_phone_finder_threads` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_finder_msg_sender` FOREIGN KEY (`sender_account_id`) REFERENCES `qs_phone_finder_accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_finder_thread_reads` (
`thread_id` BIGINT UNSIGNED NOT NULL,
`reader_account_id` BIGINT UNSIGNED NOT NULL,
`last_read_message_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
`updated_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (`thread_id`, `reader_account_id`),
INDEX `idx_finder_reads_reader` (`reader_account_id`, `thread_id`),
CONSTRAINT `fk_finder_read_thread` FOREIGN KEY (`thread_id`) REFERENCES `qs_phone_finder_threads` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_finder_read_account` FOREIGN KEY (`reader_account_id`) REFERENCES `qs_phone_finder_accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_beatzy_users` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`owner_identifier` VARCHAR(90) NOT NULL,
`username` VARCHAR(40) NOT NULL,
`password_salt` VARCHAR(64) NOT NULL,
`password_hash` VARCHAR(128) NOT NULL,
`display_name` VARCHAR(80) NOT NULL,
`avatar_url` VARCHAR(1024) NULL DEFAULT NULL,
`bio` VARCHAR(255) NULL DEFAULT NULL,
`verified` TINYINT(1) NOT NULL DEFAULT 0,
`followers_count` INT UNSIGNED NOT NULL DEFAULT 0,
`following_count` INT UNSIGNED NOT NULL DEFAULT 0,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `ux_beatzy_users_username` (`username`),
KEY `idx_beatzy_users_owner` (`owner_identifier`),
KEY `idx_beatzy_users_updated` (`updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_beatzy_videos` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`author_user_id` BIGINT UNSIGNED NOT NULL,
`video_url` TEXT NOT NULL,
`poster_url` TEXT NULL,
`caption` VARCHAR(500) NOT NULL,
`music_name` VARCHAR(120) NOT NULL,
`music_track_id` VARCHAR(120) NULL DEFAULT NULL,
`music_track_url` TEXT NULL,
`like_count` INT UNSIGNED NOT NULL DEFAULT 0,
`comment_count` INT UNSIGNED NOT NULL DEFAULT 0,
`share_count` INT UNSIGNED NOT NULL DEFAULT 0,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_beatzy_videos_author` (`author_user_id`),
KEY `idx_beatzy_videos_created` (`created_at`, `id`),
CONSTRAINT `fk_beatzy_videos_author` FOREIGN KEY (`author_user_id`) REFERENCES `qs_phone_beatzy_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_beatzy_comments` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`video_id` BIGINT UNSIGNED NOT NULL,
`author_user_id` BIGINT UNSIGNED NOT NULL,
`comment_text` VARCHAR(500) NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_beatzy_comments_video` (`video_id`, `id`),
KEY `idx_beatzy_comments_author` (`author_user_id`),
CONSTRAINT `fk_beatzy_comments_video` FOREIGN KEY (`video_id`) REFERENCES `qs_phone_beatzy_videos`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_beatzy_comments_author` FOREIGN KEY (`author_user_id`) REFERENCES `qs_phone_beatzy_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_beatzy_video_likes` (
`video_id` BIGINT UNSIGNED NOT NULL,
`user_id` BIGINT UNSIGNED NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`video_id`, `user_id`),
KEY `idx_beatzy_video_likes_user` (`user_id`),
CONSTRAINT `fk_beatzy_video_likes_video` FOREIGN KEY (`video_id`) REFERENCES `qs_phone_beatzy_videos`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_beatzy_video_likes_user` FOREIGN KEY (`user_id`) REFERENCES `qs_phone_beatzy_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_beatzy_follows` (
`follower_user_id` BIGINT UNSIGNED NOT NULL,
`following_user_id` BIGINT UNSIGNED NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`follower_user_id`, `following_user_id`),
KEY `idx_beatzy_follows_following` (`following_user_id`),
CONSTRAINT `fk_beatzy_follows_follower` FOREIGN KEY (`follower_user_id`) REFERENCES `qs_phone_beatzy_users`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_beatzy_follows_following` FOREIGN KEY (`following_user_id`) REFERENCES `qs_phone_beatzy_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_pictagram_users` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`owner_identifier` VARCHAR(90) NOT NULL,
`username` VARCHAR(40) NOT NULL,
`password_salt` VARCHAR(64) NOT NULL,
`password_hash` VARCHAR(128) NOT NULL,
`display_name` VARCHAR(80) NOT NULL,
`avatar_url` VARCHAR(1024) NULL DEFAULT NULL,
`bio` VARCHAR(255) NULL DEFAULT NULL,
`verified` TINYINT(1) NOT NULL DEFAULT 0,
`followers_count` INT UNSIGNED NOT NULL DEFAULT 0,
`following_count` INT UNSIGNED NOT NULL DEFAULT 0,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `ux_pictagram_users_username` (`username`),
KEY `idx_pictagram_users_owner` (`owner_identifier`),
KEY `idx_pictagram_users_updated` (`updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_pictagram_sessions` (
`scope_id` VARCHAR(90) NOT NULL,
`user_id` BIGINT UNSIGNED NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`scope_id`),
KEY `idx_pictagram_sessions_user` (`user_id`),
CONSTRAINT `fk_pictagram_sessions_user` FOREIGN KEY (`user_id`) REFERENCES `qs_phone_pictagram_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_pictagram_follows` (
`follower_user_id` BIGINT UNSIGNED NOT NULL,
`target_user_id` BIGINT UNSIGNED NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`follower_user_id`, `target_user_id`),
KEY `idx_pictagram_follows_target` (`target_user_id`),
CONSTRAINT `fk_pictagram_follows_follower` FOREIGN KEY (`follower_user_id`) REFERENCES `qs_phone_pictagram_users`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pictagram_follows_target` FOREIGN KEY (`target_user_id`) REFERENCES `qs_phone_pictagram_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_pictagram_posts` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`author_user_id` BIGINT UNSIGNED NOT NULL,
`media_items` JSON NOT NULL,
`caption` VARCHAR(500) NULL DEFAULT NULL,
`location` VARCHAR(140) NULL DEFAULT NULL,
`like_count` INT UNSIGNED NOT NULL DEFAULT 0,
`comment_count` INT UNSIGNED NOT NULL DEFAULT 0,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_pictagram_posts_author_created` (`author_user_id`, `created_at`, `id`),
KEY `idx_pictagram_posts_created` (`created_at`, `id`),
CONSTRAINT `fk_pictagram_posts_author` FOREIGN KEY (`author_user_id`) REFERENCES `qs_phone_pictagram_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_pictagram_post_likes` (
`post_id` BIGINT UNSIGNED NOT NULL,
`user_id` BIGINT UNSIGNED NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`post_id`, `user_id`),
KEY `idx_pictagram_post_likes_user` (`user_id`),
CONSTRAINT `fk_pictagram_post_likes_post` FOREIGN KEY (`post_id`) REFERENCES `qs_phone_pictagram_posts`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pictagram_post_likes_user` FOREIGN KEY (`user_id`) REFERENCES `qs_phone_pictagram_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_pictagram_comments` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`post_id` BIGINT UNSIGNED NOT NULL,
`author_user_id` BIGINT UNSIGNED NOT NULL,
`comment_text` VARCHAR(500) NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_pictagram_comments_post_id` (`post_id`, `id`),
KEY `idx_pictagram_comments_author` (`author_user_id`),
CONSTRAINT `fk_pictagram_comments_post` FOREIGN KEY (`post_id`) REFERENCES `qs_phone_pictagram_posts`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pictagram_comments_author` FOREIGN KEY (`author_user_id`) REFERENCES `qs_phone_pictagram_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_pictagram_story_items` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` BIGINT UNSIGNED NOT NULL,
`media_type` ENUM('image', 'video') NOT NULL,
`media_url` TEXT NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_pictagram_story_items_user_created` (`user_id`, `created_at`, `id`),
KEY `idx_pictagram_story_items_created` (`created_at`, `id`),
CONSTRAINT `fk_pictagram_story_items_user` FOREIGN KEY (`user_id`) REFERENCES `qs_phone_pictagram_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_pictagram_story_views` (
`story_item_id` BIGINT UNSIGNED NOT NULL,
`viewer_user_id` BIGINT UNSIGNED NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`story_item_id`, `viewer_user_id`),
KEY `idx_pictagram_story_views_viewer` (`viewer_user_id`),
CONSTRAINT `fk_pictagram_story_views_item` FOREIGN KEY (`story_item_id`) REFERENCES `qs_phone_pictagram_story_items`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pictagram_story_views_viewer` FOREIGN KEY (`viewer_user_id`) REFERENCES `qs_phone_pictagram_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_pictagram_notifications` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`recipient_user_id` BIGINT UNSIGNED NOT NULL,
`actor_user_id` BIGINT UNSIGNED NOT NULL,
`post_id` BIGINT UNSIGNED NULL,
`notification_type` ENUM('follow', 'like', 'comment') NOT NULL,
`text_content` VARCHAR(255) NOT NULL DEFAULT '',
`is_read` TINYINT(1) NOT NULL DEFAULT 0,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_pictagram_notifications_recipient` (`recipient_user_id`, `created_at`, `id`),
KEY `idx_pictagram_notifications_actor` (`actor_user_id`),
KEY `idx_pictagram_notifications_post` (`post_id`),
CONSTRAINT `fk_pictagram_notifications_recipient` FOREIGN KEY (`recipient_user_id`) REFERENCES `qs_phone_pictagram_users`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pictagram_notifications_actor` FOREIGN KEY (`actor_user_id`) REFERENCES `qs_phone_pictagram_users`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pictagram_notifications_post` FOREIGN KEY (`post_id`) REFERENCES `qs_phone_pictagram_posts`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_pictagram_threads` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`last_message_id` BIGINT UNSIGNED NULL DEFAULT NULL,
`last_message_preview` VARCHAR(255) NOT NULL DEFAULT 'No messages yet',
`last_message_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_pictagram_threads_last_message_at` (`last_message_at`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_pictagram_thread_members` (
`thread_id` BIGINT UNSIGNED NOT NULL,
`user_id` BIGINT UNSIGNED NOT NULL,
`last_read_message_id` BIGINT UNSIGNED NULL DEFAULT NULL,
`pinned` TINYINT(1) NOT NULL DEFAULT 0,
`muted` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`thread_id`, `user_id`),
KEY `idx_pictagram_thread_members_user` (`user_id`, `thread_id`),
CONSTRAINT `fk_pictagram_thread_members_thread` FOREIGN KEY (`thread_id`) REFERENCES `qs_phone_pictagram_threads`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pictagram_thread_members_user` FOREIGN KEY (`user_id`) REFERENCES `qs_phone_pictagram_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_pictagram_messages` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`thread_id` BIGINT UNSIGNED NOT NULL,
`sender_user_id` BIGINT UNSIGNED NOT NULL,
`message_type` ENUM('text', 'image', 'gif', 'video', 'location') NOT NULL DEFAULT 'text',
`text_content` TEXT NULL,
`image_url` VARCHAR(1024) NULL DEFAULT NULL,
`media_thumbnail_url` VARCHAR(1024) NULL DEFAULT NULL,
`location_json` JSON NULL DEFAULT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_pictagram_messages_thread_id` (`thread_id`, `id`),
KEY `idx_pictagram_messages_sender` (`sender_user_id`),
CONSTRAINT `fk_pictagram_messages_thread` FOREIGN KEY (`thread_id`) REFERENCES `qs_phone_pictagram_threads`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pictagram_messages_sender` FOREIGN KEY (`sender_user_id`) REFERENCES `qs_phone_pictagram_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE INDEX IF NOT EXISTS idx_phone_msg_members_participant_thread
ON qs_phone_message_thread_members (participant_id, thread_id);
CREATE INDEX IF NOT EXISTS idx_phone_msg_threads_last_at_id
ON qs_phone_message_threads (last_message_at, id);
CREATE INDEX IF NOT EXISTS idx_phone_msg_messages_thread_sender_id
ON qs_phone_messages (thread_id, sender_participant_id, id);
CREATE TABLE IF NOT EXISTS `qs_phone_backups` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`owner_identifier` VARCHAR(90) NOT NULL,
`phone_scope_id` VARCHAR(90) NOT NULL,
`origin_device_serial` VARCHAR(90) NOT NULL,
`phone_number` VARCHAR(32) NOT NULL,
`owner_name` VARCHAR(120) NOT NULL DEFAULT 'Unknown',
`phone_created_at` BIGINT UNSIGNED NOT NULL DEFAULT 0,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY `ux_owner_scope` (`owner_identifier`, `phone_scope_id`),
INDEX `idx_owner` (`owner_identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_revoked_devices` (
`device_serial` VARCHAR(90) NOT NULL PRIMARY KEY,
`revoked_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`revoked_by_identifier` VARCHAR(90) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_calendar_events` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`scope_id` VARCHAR(90) NOT NULL,
`title` VARCHAR(200) NOT NULL,
`notes` TEXT NULL,
`start_date` DATE NOT NULL,
`end_date` DATE NULL,
`color` VARCHAR(24) NOT NULL DEFAULT 'blue',
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_calendar_scope_range` (`scope_id`, `start_date`, `end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_qchat_accounts` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`owner_identifier` VARCHAR(96) NOT NULL,
`peer_id` VARCHAR(96) NOT NULL,
`phone` VARCHAR(24) NOT NULL,
`display_name` VARCHAR(120) NOT NULL,
`avatar_url` VARCHAR(1024) NULL DEFAULT '',
`username` VARCHAR(64) NULL DEFAULT NULL,
`password_salt` VARCHAR(128) NULL DEFAULT NULL,
`password_hash` VARCHAR(255) NULL DEFAULT NULL,
`auth_rev` INT UNSIGNED NOT NULL DEFAULT 0,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `ux_qchat_account_peer` (`peer_id`),
UNIQUE KEY `ux_qchat_account_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_qchat_devices` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`peer_id` VARCHAR(96) NOT NULL,
`device_id` VARCHAR(128) NOT NULL,
`device_name` VARCHAR(100) NULL DEFAULT NULL,
`platform` ENUM('ios', 'android', 'web') NOT NULL DEFAULT 'web',
`push_token` VARCHAR(255) NULL DEFAULT NULL,
`last_active` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `ux_qchat_device` (`device_id`),
INDEX `idx_qchat_device_peer` (`peer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_qchat_qr_sessions` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`session_id` VARCHAR(64) NOT NULL,
`server_id` VARCHAR(64) NOT NULL,
`peer_id` VARCHAR(96) NULL DEFAULT NULL,
`status` ENUM('pending', 'scanned', 'approved', 'expired') NOT NULL DEFAULT 'pending',
`device_info` JSON NULL,
`expires_at` TIMESTAMP NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `ux_qchat_qr_session_id` (`session_id`),
INDEX `idx_qchat_qr_server_status` (`server_id`, `status`, `expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_qchat_threads` (
`id` VARCHAR(96) NOT NULL,
`peer_id` VARCHAR(96) NOT NULL,
`counterpart_peer_id` VARCHAR(96) NULL DEFAULT NULL,
`title` VARCHAR(120) NOT NULL,
`avatar_url` VARCHAR(1024) NULL DEFAULT '',
`bio` VARCHAR(512) NULL DEFAULT '',
`is_group` TINYINT(1) NOT NULL DEFAULT 0,
`last_message_preview` VARCHAR(255) NULL DEFAULT '',
`last_message_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`last_message_id` BIGINT UNSIGNED NULL DEFAULT NULL,
`unread_count_cache` INT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`id`, `peer_id`),
INDEX `idx_qchat_threads_peer` (`peer_id`, `last_message_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_qchat_messages` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`thread_id` VARCHAR(96) NOT NULL,
`sender_peer_id` VARCHAR(96) NOT NULL,
`message_type` ENUM('text', 'image', 'voice', 'video', 'location', 'gif') NOT NULL DEFAULT 'text',
`body` TEXT NOT NULL,
`media_url` VARCHAR(2048) NULL DEFAULT NULL,
`location_json` JSON NULL,
`sent_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_qchat_messages_thread` (`thread_id`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_qchat_groups` (
`group_peer_id` VARCHAR(96) NOT NULL,
`owner_peer_id` VARCHAR(96) NOT NULL,
`title` VARCHAR(120) NOT NULL,
`bio` VARCHAR(500) NULL DEFAULT '',
`avatar_url` VARCHAR(1024) NULL DEFAULT '',
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`group_peer_id`),
INDEX `idx_qchat_groups_owner_peer` (`owner_peer_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_qchat_group_members` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`group_peer_id` VARCHAR(96) NOT NULL,
`member_peer_id` VARCHAR(96) NOT NULL,
`is_admin` TINYINT(1) NOT NULL DEFAULT 0,
`joined_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `ux_qchat_group_member` (`group_peer_id`, `member_peer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_qchat_statuses` (
`id` VARCHAR(96) NOT NULL,
`owner_peer_id` VARCHAR(96) NOT NULL,
`author_peer_id` VARCHAR(96) NOT NULL,
`author_name` VARCHAR(120) NOT NULL,
`author_avatar_url` VARCHAR(1024) NULL DEFAULT '',
`preview_text` VARCHAR(512) NOT NULL,
`media_url` VARCHAR(2048) NULL DEFAULT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_qchat_status_owner_peer` (`owner_peer_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `qs_phone_qchat_calls` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`call_id` VARCHAR(64) NOT NULL,
`server_id` VARCHAR(64) NOT NULL,
`caller_peer_id` VARCHAR(96) NOT NULL,
`callee_peer_id` VARCHAR(96) NOT NULL,
`call_type` ENUM('voice', 'video') NOT NULL,
`status` ENUM('ringing', 'active', 'ended', 'missed', 'rejected') NOT NULL DEFAULT 'ringing',
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `ux_qchat_call_id` (`call_id`),
INDEX `idx_qchat_calls_server_participants` (`server_id`, `caller_peer_id`, `callee_peer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
To use the camera, voice recorder and video recorder features, you must connect the smartphone with FiveManage:
Create your account, generate an API token from the dashboard and place it inside:
qs-smartphone/server/webhook.luaReplace the token value with your own API key:
_G.fivemanage = {
-- If you want to use camera, voice recorder, and video recorder, you need to set a fivemanage token here.
-- If you have custom server. You can edit the qs-smartphone/web/build/custom-upload.js file to use your own upload function.
token = 'YOUR_API_TOKEN_HERE'
}After saving the changes, restart the smartphone resource or your server.
To improve compatibility and stability for video calls, live streaming and InstaPic Live, it is recommended to configure a TURN server inside the smartphone. All settings are configured inside:
qs-smartphone/config/main.luaYou can also use Metered as your TURN provider.
Create your account and generate TURN credentials from the Metered dashboard.
Press Show ICE Servers Array copy the generated ICE server configuration.
Open qs-smartphone/config/main.lua and replace the Config.PhoneWebRTC section with your credentials:
Config.PhoneWebRTC = {
iceServers = {
{
urls = 'stun:stun.relay.metered.ca:80'
},
{
urls = 'turn:global.relay.metered.ca:80',
username = 'YOUR_USERNAME',
credential = 'YOUR_PASSWORD'
},
{
urls = 'turn:global.relay.metered.ca:80?transport=tcp',
username = 'YOUR_USERNAME',
credential = 'YOUR_PASSWORD'
},
{
urls = 'turn:global.relay.metered.ca:443',
username = 'YOUR_USERNAME',
credential = 'YOUR_PASSWORD'
},
{
urls = 'turns:global.relay.metered.ca:443?transport=tcp',
username = 'YOUR_USERNAME',
credential = 'YOUR_PASSWORD'
},
},
video = {
captureFps = 30,
minCaptureLongEdge = 720,
maxBitrate = 2500000,
minBitrate = 600000,
maxFramerate = 30,
},
}Restart the smartphone resource or your server after applying the configuration.
Quasar Smartphone supports Cloudflare TURN services for WebRTC connections.
Create an account on Cloudflare.
Navigate to Realtime → TURN Server and create a new TURN server and save your credentials.
Open: qs-smartphone/config/main.lua.
Example configuration:
Config.PhoneWebRTC = {
iceServers = {
{
urls = 'stun:stun.cloudflare.com:3478'
},
{
urls = 'turn:your-cloudflare-turn-url',
username = 'YOUR_TOKEN_ID',
credential = 'YOUR_API_TOKEN'
},
},
video = {
captureFps = 30,
minCaptureLongEdge = 720,
maxBitrate = 2500000,
minBitrate = 600000,
maxFramerate = 30,
},
}Restart the smartphone resource or your server after saving changes.