Dashboard

CREATE TABLE accounts (
	account_id INTEGER NOT NULL, 
	created_at DATETIME DEFAULT now(), 
	PRIMARY KEY (account_id)
)

CREATE TABLE `accounts` (
  `account_id` int NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`account_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

CREATE TABLE exchange_api (
	exchange_api_id INTEGER NOT NULL, 
	account_id INTEGER NOT NULL, 
	exchange VARCHAR(6) NOT NULL, 
	subaccount VARCHAR(30), 
	"apiKey" VARCHAR(100), 
	secret VARCHAR(100), 
	password VARCHAR(100), 
	PRIMARY KEY (exchange_api_id, account_id), 
	FOREIGN KEY(account_id) REFERENCES accounts (account_id)
)

CREATE TABLE `exchange_api` (
  `exchange_api_id` int NOT NULL AUTO_INCREMENT,
  `account_id` int NOT NULL,
  `exchange` set('bitget','','','') COLLATE utf8mb4_general_ci NOT NULL,
  `subaccount` varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `apiKey` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `secret` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `password` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`exchange_api_id`),
  KEY `FK_exchange_api_account_ID` (`account_id`),
  CONSTRAINT `FK_exchange_api_account_ID` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`account_id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

CREATE TABLE roles (
	role_id INTEGER NOT NULL, 
	name TEXT NOT NULL, 
	PRIMARY KEY (role_id)
)

CREATE TABLE `roles` (
  `role_id` int NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

CREATE TABLE strategie_envelope (
	strategie_id INTEGER NOT NULL, 
	account_id INTEGER NOT NULL, 
	symbol VARCHAR(100) NOT NULL, 
	timeframe VARCHAR(5) NOT NULL, 
	margin_mode VARCHAR(8) NOT NULL, 
	balance_fraction INTEGER NOT NULL, 
	leverage INTEGER, 
	average_type VARCHAR(3) NOT NULL, 
	average_period INTEGER NOT NULL, 
	envelopes JSON, 
	stop_loss_pct DECIMAL, 
	price_jump_pct DECIMAL, 
	use_longs BOOLEAN, 
	use_shorts BOOLEAN, 
	PRIMARY KEY (strategie_id), 
	FOREIGN KEY(account_id) REFERENCES accounts (account_id)
)

CREATE TABLE `strategie_envelope` (
  `strategie_id` int NOT NULL,
  `account_id` int NOT NULL,
  `symbol` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `timeframe` varchar(5) COLLATE utf8mb4_general_ci NOT NULL,
  `margin_mode` varchar(8) COLLATE utf8mb4_general_ci NOT NULL,
  `balance_fraction` int NOT NULL,
  `leverage` int DEFAULT NULL,
  `average_type` varchar(3) COLLATE utf8mb4_general_ci NOT NULL,
  `average_period` int NOT NULL,
  `envelopes` json DEFAULT NULL,
  `stop_loss_pct` decimal(4,2) DEFAULT NULL,
  `price_jump_pct` decimal(4,2) DEFAULT NULL,
  `use_longs` tinyint(1) DEFAULT NULL,
  `use_shorts` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`strategie_id`),
  KEY `account_id` (`account_id`),
  CONSTRAINT `strategie_envelope_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

CREATE TABLE users (
	user_id INTEGER NOT NULL, 
	username TEXT, 
	email VARCHAR NOT NULL, 
	password_hash VARCHAR(128) NOT NULL, 
	confirmed BOOLEAN DEFAULT 'false' NOT NULL, 
	created_at DATETIME DEFAULT now() NOT NULL, 
	account_id INTEGER NOT NULL, 
	PRIMARY KEY (user_id), 
	UNIQUE (email), 
	FOREIGN KEY(account_id) REFERENCES accounts (account_id)
)

CREATE TABLE `users` (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `email` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `password_hash` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `confirmed` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `account_id` int NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `email` (`email`),
  KEY `account_id` (`account_id`),
  CONSTRAINT `FK_account_ID` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`account_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

CREATE TABLE users_x_roles (
	user_id INTEGER NOT NULL, 
	role_id INTEGER NOT NULL, 
	assigned_at DATETIME DEFAULT now() NOT NULL, 
	PRIMARY KEY (user_id, role_id), 
	FOREIGN KEY(user_id) REFERENCES users (user_id), 
	FOREIGN KEY(role_id) REFERENCES roles (role_id)
)

CREATE TABLE `users_x_roles` (
  `user_id` int NOT NULL,
  `role_id` int NOT NULL,
  `assigned_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`,`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci