1. Overview

This document provides an overview of the database schema, along with the necessary SQL scripts to initialize the database with required tables and seed data for the project.

2. Database Schema

2.1. Tables

Below is a brief description of the database tables and their purposes:

Table Name Description
user_status Stores user status (e.g., active, inactive, suspended
calc_user Contains user account information, such as username and status.
refresh_tokens Stores refresh tokens for user sessions.
bank_account Holds user bank account details, including balance and currency.
operation_type Lists available operations (e.g., Addition, Subtraction).
operation_record Tracks user operations and their results.

3. SQL Scripts

3.1. Table Creation

Run the following SQL script to create the database tables:

Note: Execute the entire script at once using your SQL client’s “Run Script” option (e.g., Ctrl+Shift+Enter in MySQL Workbench or Alt+X in DBeaver) to ensure all statements run in the correct order.

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS balance_record;
DROP TABLE IF EXISTS operation_record;
DROP TABLE IF EXISTS operation_type;
DROP TABLE IF EXISTS bank_account;
DROP TABLE IF EXISTS refresh_tokens;
DROP TABLE IF EXISTS calc_user;
DROP TABLE IF EXISTS user_status;

/* User Status */
CREATE TABLE user_status (
    id INT AUTO_INCREMENT PRIMARY KEY,
    description VARCHAR(80) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

/* User */
CREATE TABLE calc_user (
    id CHAR(36) PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    user_status_id INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_status_id) REFERENCES user_status(id) ON DELETE CASCADE
);

/* Refresh Tokens */
CREATE TABLE refresh_tokens (
    token VARCHAR(255) NOT NULL PRIMARY KEY,
    user_id CHAR(36) NOT NULL,
    expires_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL,
    is_revoked BOOLEAN NOT NULL DEFAULT 0,
    revoked_at DATETIME NULL,
    FOREIGN KEY (user_id) REFERENCES calc_user(id) ON DELETE CASCADE
);

/* Bank Account */
CREATE TABLE bank_account (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36) NOT NULL,
    balance DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    currency CHAR(3) NOT NULL DEFAULT 'USD',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES calc_user(id) ON DELETE CASCADE
);

/* Operation Type */
CREATE TABLE operation_type (
    id CHAR(36) PRIMARY KEY,
    description VARCHAR(80) NOT NULL UNIQUE,
    operator_code VARCHAR(30) NOT NULL UNIQUE,
    cost DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

/* Operation Record */
CREATE TABLE operation_record (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36) NOT NULL,
    cost DECIMAL(10, 2) NOT NULL,
    user_balance DECIMAL(10, 2) NOT NULL,
    result TEXT NOT NULL,
    expression TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (user_id) REFERENCES calc_user(id) ON DELETE CASCADE
);

/* Balance Record */
CREATE TABLE balance_record (
    id CHAR(36) PRIMARY KEY,
    type ENUM('credit', 'debit') NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    account_id CHAR(36) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES bank_account(id) ON DELETE CASCADE
);

SET FOREIGN_KEY_CHECKS = 1;

3.2. Index Creation

Run the following script to create index to tables into the database:

/* =========================================
   Indexes
   Run the entire script at once to ensure
   tables exist before creating indexes
========================================= */

/* Refresh Tokens */
CREATE INDEX idx_refresh_tokens_user_id ON refresh_tokens(user_id);

/* Bank Account */
CREATE INDEX idx_bank_account_user_id ON bank_account(user_id);

/* Operation Record */
CREATE INDEX idx_operation_record_user_id ON operation_record(user_id);
CREATE INDEX idx_operation_record_deleted_at ON operation_record(deleted_at);
CREATE INDEX idx_operation_record_user_id_created_at ON operation_record(user_id, created_at);

/* Balance Record */
CREATE INDEX idx_balance_record_account_id ON balance_record(account_id);

/* Full-text index for result and expression columns in operation_record */
CREATE FULLTEXT INDEX idx_operation_record_result_expression ON operation_record(result, expression);

/* Optionally: Composite index for deleted_at and created_at in operation_record */
CREATE INDEX idx_operation_record_deleted_at_created_at ON operation_record(deleted_at, created_at);

3.3. Seed Data