Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /**
- * Database Schema for [Your Application's Name]
- *
- * This script defines the schema for a series of tables critical to the application's functionality. It includes:
- * - `api_key`: Manages API keys with associated usage credits and types.
- * - `candidates`: Tracks candidates sourced for recruitment, including various metadata and status indicators.
- * - `client`: Stores client information, vital for managing client interactions and data.
- * - `requests`: Details requests made by or for clients, encompassing job details and assignment tracking.
- * - `user`: Manages user accounts, roles, and authentication within the application.
- *
- * Each table is designed with a focus on extensibility, security, and robust data management, ensuring that the application
- * can scale and adapt to future requirements.
- *
- * Author: RapidMod
- * Website: https://rapidmod.io/
- *
- * Note: Before executing this script, ensure that your MySQL/MariaDB server is running and accessible. Modify the script as necessary
- * to align with your specific application logic and database naming conventions.
- */
- | api_key | CREATE TABLE `api_key` (
- `id` bigint NOT NULL AUTO_INCREMENT,
- `guid` varchar(96) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
- `active` tinyint DEFAULT NULL,
- `credits` int DEFAULT NULL,
- `type` varchar(96) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'api_key',
- `public_key` varchar(248) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
- `private_key` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
- `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
- `updated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
- `valid_until` datetime DEFAULT NULL,
- `extra_data` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
- PRIMARY KEY (`id`),
- UNIQUE KEY `guid` (`guid`),
- KEY `type` (`type`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
- | candidates | CREATE TABLE `candidates` (
- `id` bigint NOT NULL AUTO_INCREMENT,
- `guid` varchar(96) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
- `sourced_by` bigint DEFAULT NULL,
- `recruiter_id` bigint DEFAULT NULL,
- `request_id` bigint DEFAULT NULL,
- `has_dripify` tinyint DEFAULT NULL,
- `has_rf` tinyint DEFAULT NULL,
- `date_loaded` datetime DEFAULT NULL,
- `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
- `updated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
- `linkedin_profile_link_status` varchar(48) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
- `linkedin_profile_link` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
- `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
- `recruiter_notes` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
- `sourcer_notes` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
- PRIMARY KEY (`id`),
- UNIQUE KEY `guid` (`guid`),
- KEY `sourced_by` (`sourced_by`),
- KEY `recruiter_id` (`recruiter_id`),
- KEY `request_id` (`request_id`),
- CONSTRAINT `candidates_ibfk_1` FOREIGN KEY (`recruiter_id`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
- CONSTRAINT `candidates_ibfk_2` FOREIGN KEY (`sourced_by`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
- CONSTRAINT `candidates_ibfk_3` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
- ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
- | client | CREATE TABLE `client` (
- `id` bigint NOT NULL AUTO_INCREMENT,
- `guid` varchar(96) COLLATE utf8mb4_unicode_ci NOT NULL,
- `name` varchar(96) COLLATE utf8mb4_unicode_ci NOT NULL,
- `phone` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
- `email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
- `updated` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
- `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
- PRIMARY KEY (`id`),
- UNIQUE KEY `guid` (`guid`)
- ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
- | requests | CREATE TABLE `requests` (
- `id` bigint NOT NULL AUTO_INCREMENT,
- `guid` varchar(96) COLLATE utf8mb4_unicode_ci NOT NULL,
- `job_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
- `job_city` varchar(96) COLLATE utf8mb4_unicode_ci NOT NULL,
- `search_area` varchar(96) COLLATE utf8mb4_unicode_ci NOT NULL,
- `search_priority` int NOT NULL,
- `client_id` bigint DEFAULT NULL,
- `submitted_by` bigint DEFAULT NULL,
- `assigned_to` bigint DEFAULT NULL,
- `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
- `updated` datetime NOT NULL,
- PRIMARY KEY (`id`),
- UNIQUE KEY `guid` (`guid`),
- KEY `assigned_to` (`assigned_to`),
- KEY `submitted_by` (`submitted_by`),
- KEY `client_id` (`client_id`),
- CONSTRAINT `requests_ibfk_1` FOREIGN KEY (`assigned_to`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
- CONSTRAINT `requests_ibfk_2` FOREIGN KEY (`submitted_by`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
- CONSTRAINT `requests_ibfk_3` FOREIGN KEY (`client_id`) REFERENCES `client` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
- ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
- | user | CREATE TABLE `user` (
- `id` bigint NOT NULL AUTO_INCREMENT,
- `api_key_id` bigint DEFAULT NULL,
- `contact_id` bigint DEFAULT NULL,
- `role` varchar(48) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'client',
- `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'this should be email address',
- `email_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
- `guid` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
- `auth_type` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
- `password` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
- `domain` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'local',
- `status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
- `last_login` datetime DEFAULT NULL,
- `updated` datetime DEFAULT NULL,
- `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
- PRIMARY KEY (`id`),
- KEY `contact_id` (`contact_id`),
- KEY `guid` (`guid`),
- KEY `api_key_id` (`api_key_id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPACT |
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement