Download Script
To download the assets needed for this script, you must access the official Cfx.re portal, where all assets purchased through Tebex are managed.
1
Access the Granted Assets Page
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.
2
Log In with Your Cfx.re Account
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.
3
Download the Housing Assets
In the granted assets list, locate and download:
Smartphone v3These 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.
Download Dependencies
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:
Remove Other Scripts
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.
Server.cfg Placement
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-dispatch
Never place the inventory or smartphone above your framework.
Database Setup
It is important to use MariaDB 10.6 or higher.
Older database versions may cause the SQL file to fail during installation or generate compatibility issues. Using MariaDB 10.6+ also helps improve overall database optimization and server performance.
Avoid using tools like XAMPP or other non-optimized local servers, as they may cause connection errors.
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.
ESX/QB
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_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,
`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_number_scope` (`number`, `scope_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
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,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`scope_id`, `track_id`),
INDEX `idx_music_favorites_scope_created` (`scope_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
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_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;
FiveManage API Setup
Want a discount on FiveManage? Use code
QUASAR10 and save on your next purchase.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.lua
Replace 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.
WebRTC / TURN Server Setup
Please choose only one of the following providers:
Metered or Cloudflare. Do not use both at the same time.
Select the option that best fits your server infrastructure and connectivity needs.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.lua
Metered TURN Setup
You can also use Metered as your TURN provider.
1
Create Your Metered Account
Create your account and generate TURN credentials from the Metered dashboard.
Press Show ICE Servers Array copy the generated ICE server configuration.
2
Configure Your Smartphone
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.
Cloudflare TURN Setup
Quasar Smartphone supports Cloudflare TURN services for WebRTC connections.
1
Create A Cloudflare Account
Create an account on Cloudflare.
Navigate to Realtime → TURN Server and create a new TURN server and save your credentials.
2
Configure Your Smartphone
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.