Zettai-Products/chartboard
5
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
main
chartboard/chartboard.sql
Gowtham M fdccda93e1 Initial commit
2026-03-30 09:51:33 +05:30

625 lines
32 KiB
SQL
Raw Permalink Blame History

-- ============================================================
-- Chart-Board — MySQL Database Schema
-- Version : 1.0.0
-- Engine : InnoDB | Charset: utf8mb4 | Collation: utf8mb4_unicode_ci
-- ============================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = 'NO_ENGINE_SUBSTITUTION';
CREATE DATABASE IF NOT EXISTS `chartboard`
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
USE `chartboard`;
-- ============================================================
-- TABLE: users
-- Stores all registered user accounts
-- ============================================================
CREATE TABLE `users` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(150) NOT NULL,
`email` VARCHAR(255) NOT NULL UNIQUE,
`password` VARCHAR(255) NOT NULL COMMENT 'bcrypt hashed',
`avatar` VARCHAR(500) NULL DEFAULT NULL COMMENT 'URL or file path',
`role` ENUM('superadmin','user') NOT NULL DEFAULT 'user',
`email_verified` TINYINT(1) NOT NULL DEFAULT 0,
`verify_token` VARCHAR(255) NULL DEFAULT NULL,
`reset_token` VARCHAR(255) NULL DEFAULT NULL,
`reset_token_expiry` DATETIME NULL DEFAULT NULL,
`api_token` VARCHAR(255) NULL DEFAULT NULL UNIQUE COMMENT 'Personal API token',
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
`theme_preference` VARCHAR(20) NOT NULL DEFAULT 'light' COMMENT 'UI theme: light, dark, system',
`last_login_at` DATETIME NULL DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` DATETIME NULL DEFAULT NULL COMMENT 'Soft delete',
PRIMARY KEY (`id`),
INDEX `idx_users_email` (`email`),
INDEX `idx_users_api_token` (`api_token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='All registered Chart-Board users';
-- ============================================================
-- TABLE: workspaces
-- Top-level container for data sources, charts, dashboards
-- ============================================================
CREATE TABLE `workspaces` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(150) NOT NULL,
`slug` VARCHAR(160) NOT NULL UNIQUE COMMENT 'URL-friendly identifier',
`description` TEXT NULL DEFAULT NULL,
`logo` VARCHAR(500) NULL DEFAULT NULL,
`timezone` VARCHAR(80) NOT NULL DEFAULT 'UTC',
`default_refresh` INT UNSIGNED NOT NULL DEFAULT 300 COMMENT 'Default refresh in seconds',
`owner_id` INT UNSIGNED NOT NULL,
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` DATETIME NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `idx_workspaces_owner` (`owner_id`),
CONSTRAINT `fk_workspaces_owner`
FOREIGN KEY (`owner_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Workspace — team or project level grouping';
-- ============================================================
-- TABLE: workspace_members
-- Many-to-many: users ↔ workspaces with role
-- ============================================================
CREATE TABLE `workspace_members` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`workspace_id` INT UNSIGNED NOT NULL,
`user_id` INT UNSIGNED NOT NULL,
`role` ENUM('admin','editor','viewer') NOT NULL DEFAULT 'viewer',
`invited_by` INT UNSIGNED NULL DEFAULT NULL,
`joined_at` DATETIME NULL DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_workspace_member` (`workspace_id`, `user_id`),
CONSTRAINT `fk_wm_workspace`
FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_wm_user`
FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_wm_invited_by`
FOREIGN KEY (`invited_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Workspace membership and roles';
-- ============================================================
-- TABLE: workspace_invitations
-- Pending email invitations to join a workspace
-- ============================================================
CREATE TABLE `workspace_invitations` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`workspace_id` INT UNSIGNED NOT NULL,
`invited_by` INT UNSIGNED NOT NULL,
`email` VARCHAR(255) NOT NULL,
`role` ENUM('admin','editor','viewer') NOT NULL DEFAULT 'viewer',
`token` VARCHAR(255) NOT NULL UNIQUE,
`accepted` TINYINT(1) NOT NULL DEFAULT 0,
`expires_at` DATETIME NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_invitations_token` (`token`),
CONSTRAINT `fk_inv_workspace`
FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_inv_invited_by`
FOREIGN KEY (`invited_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Pending workspace invitations via email';
-- ============================================================
-- TABLE: data_sources
-- Connection configurations for databases, APIs, CSV files
-- ============================================================
CREATE TABLE `data_sources` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`workspace_id` INT UNSIGNED NOT NULL,
`name` VARCHAR(150) NOT NULL,
`type` ENUM('mysql','postgresql','mongodb','rest_api','csv') NOT NULL,
`host` VARCHAR(255) NULL DEFAULT NULL,
`port` SMALLINT UNSIGNED NULL DEFAULT NULL,
`database_name` VARCHAR(150) NULL DEFAULT NULL,
`username` VARCHAR(150) NULL DEFAULT NULL,
`password` TEXT NULL DEFAULT NULL COMMENT 'AES-256 encrypted',
`connection_uri` TEXT NULL DEFAULT NULL COMMENT 'For MongoDB or DSN-style',
`ssl_enabled` TINYINT(1) NOT NULL DEFAULT 0,
`ssl_ca` TEXT NULL DEFAULT NULL,
-- REST API fields
`api_base_url` VARCHAR(500) NULL DEFAULT NULL,
`api_method` ENUM('GET','POST') NULL DEFAULT 'GET',
`api_auth_type` ENUM('none','bearer','basic','api_key') NULL DEFAULT 'none',
`api_auth_value` TEXT NULL DEFAULT NULL COMMENT 'Encrypted token/password',
`api_headers` JSON NULL DEFAULT NULL COMMENT 'Static headers as key-value JSON',
-- CSV fields
`csv_file_path` VARCHAR(500) NULL DEFAULT NULL,
`csv_delimiter` CHAR(1) NULL DEFAULT ',',
-- Status
`last_tested_at` DATETIME NULL DEFAULT NULL,
`status` ENUM('untested','connected','failed') NOT NULL DEFAULT 'untested',
`error_message` TEXT NULL DEFAULT NULL,
`created_by` INT UNSIGNED NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` DATETIME NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `idx_ds_workspace` (`workspace_id`),
CONSTRAINT `fk_ds_workspace`
FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_ds_created_by`
FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Data source connection configurations';
-- ============================================================
-- TABLE: saved_queries
-- Reusable queries (SQL or API-mapped) per data source
-- ============================================================
CREATE TABLE `saved_queries` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`workspace_id` INT UNSIGNED NOT NULL,
`data_source_id` INT UNSIGNED NOT NULL,
`name` VARCHAR(150) NOT NULL,
`description` TEXT NULL DEFAULT NULL,
`query_type` ENUM('visual','raw_sql','api') NOT NULL DEFAULT 'raw_sql',
`raw_sql` TEXT NULL DEFAULT NULL,
`visual_config` JSON NULL DEFAULT NULL COMMENT 'Visual builder settings (table, filters, columns)',
`api_endpoint` VARCHAR(500) NULL DEFAULT NULL COMMENT 'Override base URL for specific query',
`api_params` JSON NULL DEFAULT NULL COMMENT 'Query params / body',
`response_path` VARCHAR(255) NULL DEFAULT NULL COMMENT 'JSON path to data array e.g. data.results',
`field_map` JSON NULL DEFAULT NULL COMMENT 'Map response fields to aliases',
`cache_ttl` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Cache result for N seconds (0 = no cache)',
`created_by` INT UNSIGNED NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` DATETIME NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `idx_sq_workspace` (`workspace_id`),
INDEX `idx_sq_datasource` (`data_source_id`),
CONSTRAINT `fk_sq_workspace`
FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_sq_datasource`
FOREIGN KEY (`data_source_id`) REFERENCES `data_sources` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_sq_created_by`
FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Reusable saved queries for charts';
-- ============================================================
-- TABLE: query_variables
-- Variable definitions detected/configured per saved query
-- ============================================================
CREATE TABLE `query_variables` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`saved_query_id` INT UNSIGNED NOT NULL,
`workspace_id` INT UNSIGNED NOT NULL,
`name` VARCHAR(100) NOT NULL COMMENT 'Variable token name without braces',
`label` VARCHAR(120) NULL DEFAULT NULL COMMENT 'Display label for form widgets',
`type` ENUM('text','number','date','date_range','select','multi_select') NOT NULL DEFAULT 'text',
`default_value` TEXT NULL DEFAULT NULL,
`options_json` JSON NULL DEFAULT NULL COMMENT 'Option list for select/multi_select',
`is_required` TINYINT(1) NOT NULL DEFAULT 0,
`sort_order` SMALLINT UNSIGNED NOT NULL DEFAULT 1,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_qv_query_name` (`saved_query_id`, `name`),
INDEX `idx_qv_workspace` (`workspace_id`),
INDEX `idx_qv_query` (`saved_query_id`),
CONSTRAINT `fk_qv_saved_query`
FOREIGN KEY (`saved_query_id`) REFERENCES `saved_queries` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_qv_workspace`
FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Variable definitions for parameterized saved queries';
-- ============================================================
-- TABLE: charts
-- Individual chart definitions
-- ============================================================
CREATE TABLE `charts` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`workspace_id` INT UNSIGNED NOT NULL,
`data_source_id` INT UNSIGNED NOT NULL,
`saved_query_id` INT UNSIGNED NULL DEFAULT NULL,
`name` VARCHAR(200) NOT NULL,
`description` TEXT NULL DEFAULT NULL,
`chart_type` ENUM(
'bar','line','area','pie','donut',
'scatter','table','kpi_card','funnel',
'gauge','heatmap','combo',
'spline','stepline','radar','bubble','polar_area'
) NOT NULL,
`query_type` ENUM('visual','raw_sql','api') NOT NULL DEFAULT 'raw_sql',
`raw_sql` TEXT NULL DEFAULT NULL,
`visual_config` JSON NULL DEFAULT NULL,
`api_endpoint` VARCHAR(500) NULL DEFAULT NULL,
`api_params` JSON NULL DEFAULT NULL,
`response_path` VARCHAR(255) NULL DEFAULT NULL,
`field_map` JSON NULL DEFAULT NULL,
-- Axis & field mapping
`x_field` VARCHAR(150) NULL DEFAULT NULL,
`y_field` VARCHAR(150) NULL DEFAULT NULL,
`group_field` VARCHAR(150) NULL DEFAULT NULL,
`value_field` VARCHAR(150) NULL DEFAULT NULL COMMENT 'Used for KPI/Gauge',
-- Display settings
`display_config` JSON NULL DEFAULT NULL COMMENT 'Colors, labels, legend, formatting options',
`refresh_interval` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Auto-refresh in seconds (0 = manual)',
`cache_ttl` INT UNSIGNED NOT NULL DEFAULT 300 COMMENT 'Cache result TTL in seconds',
`is_public` TINYINT(1) NOT NULL DEFAULT 0,
`public_token` VARCHAR(100) NULL DEFAULT NULL UNIQUE,
`created_by` INT UNSIGNED NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` DATETIME NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `idx_charts_workspace` (`workspace_id`),
INDEX `idx_charts_datasource` (`data_source_id`),
INDEX `idx_charts_public` (`public_token`),
CONSTRAINT `fk_charts_workspace`
FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_charts_datasource`
FOREIGN KEY (`data_source_id`) REFERENCES `data_sources` (`id`) ON DELETE RESTRICT,
CONSTRAINT `fk_charts_query`
FOREIGN KEY (`saved_query_id`) REFERENCES `saved_queries` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_charts_created_by`
FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Chart definitions with query and display configuration';
-- ============================================================
-- TABLE: dashboards
-- Dashboard containers that hold multiple charts
-- ============================================================
CREATE TABLE `dashboards` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`workspace_id` INT UNSIGNED NOT NULL,
`name` VARCHAR(200) NOT NULL,
`description` TEXT NULL DEFAULT NULL,
`slug` VARCHAR(220) NOT NULL,
`layout_config` JSON NULL DEFAULT NULL COMMENT 'Grid layout positions for all widgets',
`filters_config` JSON NULL DEFAULT NULL COMMENT 'Global filter widget definitions',
`refresh_interval` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Dashboard-level refresh (0 = manual)',
`theme` ENUM('light','dark','system') NOT NULL DEFAULT 'system',
`is_public` TINYINT(1) NOT NULL DEFAULT 0,
`public_token` VARCHAR(100) NULL DEFAULT NULL UNIQUE,
`public_password` VARCHAR(255) NULL DEFAULT NULL COMMENT 'Optional bcrypt hashed password for public link',
`public_expires_at` DATETIME NULL DEFAULT NULL,
`is_pinned` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Pinned dashboards appear first',
`created_by` INT UNSIGNED NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` DATETIME NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_dashboard_slug` (`workspace_id`, `slug`),
INDEX `idx_dashboards_workspace` (`workspace_id`),
INDEX `idx_dashboards_public` (`public_token`),
CONSTRAINT `fk_dashboards_workspace`
FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_dashboards_created_by`
FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Dashboard containers';
-- ============================================================
-- TABLE: dashboard_widgets
-- Each widget on a dashboard (chart, text, image, filter)
-- ============================================================
CREATE TABLE `dashboard_widgets` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`dashboard_id` INT UNSIGNED NOT NULL,
`chart_id` INT UNSIGNED NULL DEFAULT NULL COMMENT 'NULL for non-chart widgets',
`widget_type` ENUM('chart','text','image','filter_date','filter_dropdown') NOT NULL DEFAULT 'chart',
`title` VARCHAR(200) NULL DEFAULT NULL COMMENT 'Widget-level title override',
-- Grid position (react-grid-layout style)
`grid_x` TINYINT UNSIGNED NOT NULL DEFAULT 0,
`grid_y` TINYINT UNSIGNED NOT NULL DEFAULT 0,
`grid_w` TINYINT UNSIGNED NOT NULL DEFAULT 2 COMMENT 'Width in grid units',
`grid_h` TINYINT UNSIGNED NOT NULL DEFAULT 2 COMMENT 'Height in grid units',
-- Content for non-chart widgets
`content` TEXT NULL DEFAULT NULL COMMENT 'Markdown for text widgets, URL for image widgets',
`widget_config` JSON NULL DEFAULT NULL COMMENT 'Extra config (filter field bindings, image fit, etc.)',
`sort_order` INT UNSIGNED NOT NULL DEFAULT 0,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_dw_dashboard` (`dashboard_id`),
INDEX `idx_dw_chart` (`chart_id`),
CONSTRAINT `fk_dw_dashboard`
FOREIGN KEY (`dashboard_id`) REFERENCES `dashboards` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_dw_chart`
FOREIGN KEY (`chart_id`) REFERENCES `charts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Widgets placed on a dashboard';
-- ============================================================
-- TABLE: alerts
-- Threshold-based alert definitions on chart metrics
-- ============================================================
CREATE TABLE `alerts` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`workspace_id` INT UNSIGNED NOT NULL,
`chart_id` INT UNSIGNED NOT NULL,
`name` VARCHAR(200) NOT NULL,
`metric_field` VARCHAR(150) NOT NULL COMMENT 'Which data field to watch',
`condition` ENUM('gt','lt','eq','gte','lte') NOT NULL COMMENT 'greater than, less than, etc.',
`threshold` DECIMAL(20,4) NOT NULL,
`check_interval` SMALLINT UNSIGNED NOT NULL DEFAULT 15 COMMENT 'Check every N minutes',
-- Notification
`notify_email` TINYINT(1) NOT NULL DEFAULT 0,
`email_addresses` TEXT NULL DEFAULT NULL COMMENT 'Comma-separated list of emails',
`notify_slack` TINYINT(1) NOT NULL DEFAULT 0,
`slack_webhook` VARCHAR(500) NULL DEFAULT NULL,
-- State
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
`is_muted_until` DATETIME NULL DEFAULT NULL,
`last_checked_at` DATETIME NULL DEFAULT NULL,
`last_triggered_at` DATETIME NULL DEFAULT NULL,
`created_by` INT UNSIGNED NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` DATETIME NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `idx_alerts_workspace` (`workspace_id`),
INDEX `idx_alerts_chart` (`chart_id`),
CONSTRAINT `fk_alerts_workspace`
FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_alerts_chart`
FOREIGN KEY (`chart_id`) REFERENCES `charts` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_alerts_created_by`
FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Alert rules defined on chart metrics';
-- ============================================================
-- TABLE: alert_history
-- Log of every time an alert was triggered
-- ============================================================
CREATE TABLE `alert_history` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`alert_id` INT UNSIGNED NOT NULL,
`triggered_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`metric_value` DECIMAL(20,4) NOT NULL COMMENT 'Value that triggered the alert',
`channels_notified` JSON NULL DEFAULT NULL COMMENT 'Which channels were notified',
`status` ENUM('sent','failed','muted') NOT NULL DEFAULT 'sent',
`error_message` TEXT NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `idx_ah_alert` (`alert_id`),
CONSTRAINT `fk_ah_alert`
FOREIGN KEY (`alert_id`) REFERENCES `alerts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='History of triggered alert events';
-- ============================================================
-- TABLE: chart_exports
-- Log of chart/dashboard exports and downloads
-- ============================================================
CREATE TABLE `chart_exports` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`workspace_id` INT UNSIGNED NOT NULL,
`chart_id` INT UNSIGNED NULL DEFAULT NULL,
`dashboard_id` INT UNSIGNED NULL DEFAULT NULL,
`export_type` ENUM('png','svg','csv','excel','pdf') NOT NULL,
`file_path` VARCHAR(500) NULL DEFAULT NULL,
`file_size` INT UNSIGNED NULL DEFAULT NULL COMMENT 'File size in bytes',
`exported_by` INT UNSIGNED NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_ce_workspace` (`workspace_id`),
CONSTRAINT `fk_ce_workspace`
FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_ce_exported_by`
FOREIGN KEY (`exported_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Log of chart and dashboard export requests';
-- ============================================================
-- TABLE: shared_links
-- Public share links for dashboards and charts
-- ============================================================
CREATE TABLE `shared_links` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`token` VARCHAR(100) NOT NULL UNIQUE,
`type` ENUM('dashboard','chart') NOT NULL,
`resource_id` INT UNSIGNED NOT NULL,
`workspace_id` INT UNSIGNED NOT NULL,
`password_hash` VARCHAR(255) NULL DEFAULT NULL,
`expires_at` DATETIME NULL DEFAULT NULL,
`view_count` INT UNSIGNED NOT NULL DEFAULT 0,
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
`created_by` INT UNSIGNED NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_sl_token` (`token`),
CONSTRAINT `fk_sl_workspace`
FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_sl_created_by`
FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Public sharing links for dashboards and charts';
-- ============================================================
-- TABLE: audit_logs
-- Immutable record of significant user actions
-- ============================================================
CREATE TABLE `audit_logs` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`workspace_id` INT UNSIGNED NULL DEFAULT NULL COMMENT 'NULL for super-admin actions',
`user_id` INT UNSIGNED NULL DEFAULT NULL COMMENT 'NULL for system actions',
`action` VARCHAR(100) NOT NULL COMMENT 'e.g. dashboard.created, user.login',
`resource_type` VARCHAR(50) NULL DEFAULT NULL COMMENT 'e.g. dashboard, chart, data_source',
`resource_id` INT UNSIGNED NULL DEFAULT NULL,
`old_value` JSON NULL DEFAULT NULL COMMENT 'State before the action',
`new_value` JSON NULL DEFAULT NULL COMMENT 'State after the action',
`ip_address` VARCHAR(45) NULL DEFAULT NULL COMMENT 'IPv4 or IPv6',
`user_agent` VARCHAR(500) NULL DEFAULT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_al_workspace` (`workspace_id`),
INDEX `idx_al_user` (`user_id`),
INDEX `idx_al_action` (`action`),
INDEX `idx_al_created_at` (`created_at`),
CONSTRAINT `fk_al_workspace`
FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_al_user`
FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Immutable audit trail of all user actions';
-- ============================================================
-- TABLE: user_sessions
-- Tracks active login sessions (for multi-device management)
-- ============================================================
CREATE TABLE `user_sessions` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` INT UNSIGNED NOT NULL,
`session_token` VARCHAR(255) NOT NULL UNIQUE,
`ip_address` VARCHAR(45) NULL DEFAULT NULL,
`user_agent` VARCHAR(500) NULL DEFAULT NULL,
`last_active` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`expires_at` DATETIME NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_us_user` (`user_id`),
INDEX `idx_us_token` (`session_token`),
CONSTRAINT `fk_us_user`
FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Active user login sessions';
-- ============================================================
-- TABLE: tags
-- Tagging system for charts and dashboards
-- ============================================================
CREATE TABLE `tags` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`workspace_id` INT UNSIGNED NOT NULL,
`name` VARCHAR(80) NOT NULL,
`color` VARCHAR(7) NOT NULL DEFAULT '#6366f1' COMMENT 'Hex color code',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_tag_name` (`workspace_id`, `name`),
CONSTRAINT `fk_tags_workspace`
FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Tags for organizing charts and dashboards';
-- ============================================================
-- TABLE: taggables
-- Polymorphic tag assignments (chart or dashboard)
-- ============================================================
CREATE TABLE `taggables` (
`tag_id` INT UNSIGNED NOT NULL,
`taggable_id` INT UNSIGNED NOT NULL,
`taggable_type` ENUM('chart','dashboard') NOT NULL,
PRIMARY KEY (`tag_id`, `taggable_id`, `taggable_type`),
CONSTRAINT `fk_taggables_tag`
FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Polymorphic join table for tagging';
-- ============================================================
-- TABLE: query_cache
-- Stores cached query results to reduce DB load
-- ============================================================
CREATE TABLE `query_cache` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`cache_key` VARCHAR(255) NOT NULL UNIQUE COMMENT 'MD5 hash of datasource+query',
`data_source_id` INT UNSIGNED NOT NULL,
`result_data` LONGTEXT NOT NULL COMMENT 'JSON encoded query result',
`row_count` INT UNSIGNED NOT NULL DEFAULT 0,
`expires_at` DATETIME NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_qc_cache_key` (`cache_key`),
INDEX `idx_qc_expires_at` (`expires_at`),
CONSTRAINT `fk_qc_datasource`
FOREIGN KEY (`data_source_id`) REFERENCES `data_sources` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Cached query results with TTL';
-- ============================================================
-- TABLE: settings
-- Key-value application settings (per workspace or global)
-- ============================================================
CREATE TABLE `settings` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`workspace_id` INT UNSIGNED NULL DEFAULT NULL COMMENT 'NULL = global/system setting',
`key` VARCHAR(150) NOT NULL,
`value` TEXT NULL DEFAULT NULL,
`type` ENUM('string','integer','boolean','json') NOT NULL DEFAULT 'string',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_setting` (`workspace_id`, `key`),
CONSTRAINT `fk_settings_workspace`
FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Key-value settings store';
-- ============================================================
-- INITIAL SEED DATA
-- ============================================================
-- Default Super Admin
INSERT INTO `users` (`name`, `email`, `password`, `role`, `email_verified`, `is_active`)
VALUES (
'Super Admin',
'admin@chartboard.local',
'$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', -- Admin@1234
'superadmin',
1,
1
);
-- Default Workspace
INSERT INTO `workspaces` (`name`, `slug`, `description`, `owner_id`)
VALUES ('Default Workspace', 'default-workspace', 'The default Chart-Board workspace', 1);
-- Add super admin as workspace admin
INSERT INTO `workspace_members` (`workspace_id`, `user_id`, `role`, `joined_at`)
VALUES (1, 1, 'admin', NOW());
-- Global Settings
INSERT INTO `settings` (`workspace_id`, `key`, `value`, `type`) VALUES
(NULL, 'app_name', 'Chart-Board', 'string'),
(NULL, 'app_version', '1.0.0', 'string'),
(NULL, 'allow_registration', '1', 'boolean'),
(NULL, 'max_workspaces', '10', 'integer'),
(NULL, 'default_theme', 'light', 'string'),
(NULL, 'smtp_configured', '0', 'boolean');
SET FOREIGN_KEY_CHECKS = 1;
-- ============================================================
-- End of chart-board schema
-- ============================================================
Reference in New Issue View Git Blame Copy Permalink