Database Setup - Portfolio Hub API

Portfolio Hub API uses MySQL 8.0+ as its primary database with Flyway for automated schema migrations. This guide covers database setup, schema structure, and migration management.

Database Requirements

MySQL 8.0+

Primary database server

Flyway

Database migration tool (included)

Flyway is automatically included as a Spring Boot dependency. You don’t need to install it separately.

Initial Database Setup

Create Database Schema

Connect to your MySQL server and create the database schema:

CREATE SCHEMA IF NOT EXISTS `studiostkoh.portafolio`;

The schema name studiostkoh.portafolio contains a dot (.). Always use backticks when referencing it in SQL queries.

Create Database User (Optional)

For better security, create a dedicated user for the application:

-- Create user
CREATE USER 'portfolio_user'@'localhost' IDENTIFIED BY 'secure_password';

-- Grant privileges
GRANT ALL PRIVILEGES ON `studiostkoh.portafolio`.* TO 'portfolio_user'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

User Permissions Required

The database user needs the following permissions:

SELECT, INSERT, UPDATE, DELETE - Data operations
CREATE, ALTER, DROP - Schema migrations (Flyway)
INDEX - Index management
REFERENCES - Foreign key constraints

For Flyway to work properly, the user must be able to create and modify tables.

Configure Connection

Set environment variables for database connection:

export MYSQL_HOST=localhost
export MYSQL_PORT=3306
export MYSQL_DATABASE=studiostkoh.portafolio
export MYSQL_USER=portfolio_user
export MYSQL_PASSWORD=secure_password

Flyway Migrations

Portfolio Hub API uses Flyway for version-controlled database migrations. Migrations are automatically executed on application startup.

Migration Configuration

Flyway is configured in application.properties:

# Enable Flyway
spring.flyway.enabled=true

# Migration scripts location
spring.flyway.locations=classpath:db/migration

# Allow Flyway to work with existing databases
spring.flyway.baseline-on-migrate=true

# Hibernate validation (no auto-modification)
spring.jpa.hibernate.ddl-auto=validate

Migration Files

All migration files are located in src/main/resources/db/migration/ and follow the naming pattern:

V{version}__{description}.sql

Current migrations:

V1__init.sql - Create database schema
V2__create_core_tables.sql - Create all core tables
V3__add_collaborator_flag.sql - Add collaborator flag to profiles
V4__create_certificate_table.sql - Add certificate functionality
V5__migration_and_add.sql - Add global skills feature

Flyway migrations are immutable. Once applied, they cannot be modified. Always create a new migration for schema changes.

Database Schema

Schema Overview

The database consists of 11 core tables organized around the central profile entity:

Core Tables

1. app_user - Authentication

Stores user credentials and authentication data:

CREATE TABLE app_user (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    created_at datetime(6) NOT NULL,
    created_by VARCHAR(150),
    updated_at datetime(6),
    updated_by VARCHAR(150),
    version BIGINT,
    email VARCHAR(150) NOT NULL,
    password VARCHAR(255) NOT NULL,
    roles VARCHAR(50) NOT NULL,
    UNIQUE KEY uk_user_email (email)
);

Key Fields:

email - Unique user identifier for login
password - BCrypt hashed password
roles - User roles (e.g., “ROLE_USER”, “ROLE_ADMIN”)

2. profile - Portfolio Data

Central table containing user portfolio information:

CREATE TABLE profile (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    created_at datetime(6) NOT NULL,
    created_by VARCHAR(150),
    updated_at datetime(6),
    updated_by VARCHAR(150),
    version BIGINT,
    user_id BIGINT NOT NULL,
    slug VARCHAR(80) NOT NULL,
    full_name VARCHAR(120) NOT NULL,
    headline VARCHAR(160) NOT NULL,
    bio TEXT NOT NULL,
    contact_email VARCHAR(160) NOT NULL,
    location VARCHAR(100),
    avatar_url VARCHAR(512),
    resume_url VARCHAR(512),
    is_tkoh_collaborator BOOLEAN NOT NULL DEFAULT FALSE,
    UNIQUE KEY uk_profile_slug (slug),
    UNIQUE KEY uk_profile_user_id (user_id),
    CONSTRAINT fk_profile_user
        FOREIGN KEY (user_id) REFERENCES app_user(id)
        ON DELETE CASCADE
);

Key Fields:

slug - URL-friendly unique identifier (e.g., “john-doe”)
user_id - One-to-one relationship with app_user
avatar_url / resume_url - Google Drive file links
is_tkoh_collaborator - Flag for featured collaborators

3. project - Portfolio Projects

CREATE TABLE project (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    created_at datetime(6) NOT NULL,
    created_by VARCHAR(150),
    updated_at datetime(6),
    updated_by VARCHAR(150),
    version BIGINT,
    profile_id BIGINT NOT NULL,
    title VARCHAR(140) NOT NULL,
    slug VARCHAR(160) NOT NULL,
    summary VARCHAR(280) NOT NULL,
    description TEXT,
    repo_url VARCHAR(512),
    live_url VARCHAR(512),
    cover_image VARCHAR(512),
    start_date DATE,
    end_date DATE,
    featured BOOLEAN NOT NULL DEFAULT FALSE,
    sort_order INT NOT NULL DEFAULT 0,
    UNIQUE KEY uk_project_profile_slug (profile_id, slug),
    INDEX idx_project_featured (profile_id, featured, sort_order),
    CONSTRAINT fk_project_profile
        FOREIGN KEY (profile_id) REFERENCES profile(id)
        ON DELETE CASCADE
);

Key Features:

Unique slug per profile (allows duplicate slugs across profiles)
Featured flag for highlighting important projects
Sort order for custom ordering
Composite index on (profile_id, featured, sort_order) for performance

4. skill_category & skill - Skills Organization

CREATE TABLE skill_category (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    created_at datetime(6) NOT NULL,
    created_by VARCHAR(150),
    updated_at datetime(6),
    updated_by VARCHAR(150),
    version BIGINT,
    profile_id BIGINT NOT NULL,
    name VARCHAR(80) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    CONSTRAINT fk_skillcategory_profile
        FOREIGN KEY (profile_id) REFERENCES profile(id)
        ON DELETE CASCADE
);

CREATE TABLE skill (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    created_at datetime(6) NOT NULL,
    created_by VARCHAR(150),
    updated_at datetime(6),
    updated_by VARCHAR(150),
    version BIGINT,
    category_id BIGINT NOT NULL,
    global_skill_id BIGINT NOT NULL,
    name VARCHAR(80) NULL,
    level SMALLINT,
    icon VARCHAR(255) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    CONSTRAINT fk_skill_category
        FOREIGN KEY (category_id) REFERENCES skill_category(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_skill_global
        FOREIGN KEY (global_skill_id) REFERENCES global_skill(id)
        ON DELETE RESTRICT
);

Skills are organized hierarchically:

Categories (e.g., “Frontend”, “Backend”, “DevOps”)
Skills within categories (e.g., “React”, “Spring Boot”)
Skills reference global_skill for standardization

5. global_skill - Global Skill Reference

Added in V5 migration for skill standardization:

CREATE TABLE global_skill (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    created_at datetime(6) NOT NULL,
    created_by VARCHAR(150),
    updated_at datetime(6),
    updated_by VARCHAR(150),
    version BIGINT,
    name VARCHAR(80) NOT NULL,
    icon_url VARCHAR(512),
    UNIQUE KEY uk_global_skill_name (name)
);

Purpose:

Centralized skill definitions across all portfolios
Consistent naming and icons
Enables skill-based portfolio discovery

6. experience - Work History

CREATE TABLE experience (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    created_at datetime(6) NOT NULL,
    created_by VARCHAR(150),
    updated_at datetime(6),
    updated_by VARCHAR(150),
    version BIGINT,
    profile_id BIGINT NOT NULL,
    company VARCHAR(150) NOT NULL,
    role VARCHAR(150) NOT NULL,
    location VARCHAR(100),
    start_date DATE NOT NULL,
    end_date DATE,
    current BOOLEAN NOT NULL DEFAULT FALSE,
    description TEXT,
    CONSTRAINT fk_experience_profile
        FOREIGN KEY (profile_id) REFERENCES profile(id)
        ON DELETE CASCADE
);

Key Features:

current flag for ongoing positions
end_date is NULL for current positions

7. education - Academic Background

CREATE TABLE education (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    created_at datetime(6) NOT NULL,
    created_by VARCHAR(150),
    updated_at datetime(6),
    updated_by VARCHAR(150),
    version BIGINT,
    profile_id BIGINT NOT NULL,
    institution VARCHAR(150) NOT NULL,
    degree VARCHAR(150) NOT NULL,
    field VARCHAR(150),
    start_date DATE NOT NULL,
    end_date DATE,
    description TEXT,
    CONSTRAINT fk_education_profile
        FOREIGN KEY (profile_id) REFERENCES profile(id)
        ON DELETE CASCADE
);

8. certificate - Certifications

CREATE TABLE certificate (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    created_at datetime(6) NOT NULL,
    created_by VARCHAR(150),
    updated_at datetime(6),
    updated_by VARCHAR(150),
    version BIGINT,
    profile_id BIGINT NOT NULL,
    education_id BIGINT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    image_url VARCHAR(512),
    file_id VARCHAR(255),
    CONSTRAINT fk_certificate_profile
        FOREIGN KEY (profile_id) REFERENCES profile(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_certificate_education
        FOREIGN KEY (education_id) REFERENCES education(id)
        ON DELETE SET NULL
);

Key Features:

Optional link to education record
Stores Google Drive file ID for certificate documents

CREATE TABLE social_link (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    created_at datetime(6) NOT NULL,
    created_by VARCHAR(150),
    updated_at datetime(6),
    updated_by VARCHAR(150),
    version BIGINT,
    profile_id BIGINT NOT NULL,
    platform VARCHAR(50) NOT NULL,
    url VARCHAR(512) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    CONSTRAINT fk_social_profile
        FOREIGN KEY (profile_id) REFERENCES profile(id)
        ON DELETE CASCADE
);

10. contact_message - Contact Form Submissions

CREATE TABLE contact_message (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    created_at datetime(6) NOT NULL,
    created_by VARCHAR(150),
    updated_at datetime(6),
    updated_by VARCHAR(150),
    version BIGINT,
    profile_id BIGINT NOT NULL,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(160) NOT NULL,
    message TEXT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'NEW',
    CONSTRAINT fk_contact_profile
        FOREIGN KEY (profile_id) REFERENCES profile(id)
        ON DELETE CASCADE
);

11. project_skill - Many-to-Many Relationship

CREATE TABLE project_skill (
    project_id BIGINT NOT NULL,
    skill_id BIGINT NOT NULL,
    PRIMARY KEY (project_id, skill_id),
    CONSTRAINT fk_projectskill_project
        FOREIGN KEY (project_id) REFERENCES project(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_projectskill_skill
        FOREIGN KEY (skill_id) REFERENCES skill(id)
        ON DELETE CASCADE
);

Links projects to the skills used in them.

Audit Fields

All tables include standard audit fields:

created_at - Timestamp when record was created
created_by - User who created the record
updated_at - Last update timestamp
updated_by - User who last updated the record
version - Optimistic locking version

Migration Management

Automatic Migrations

Migrations run automatically on application startup:

./mvnw spring-boot:run

You’ll see Flyway output in the logs:

Flyway: Migrating schema `studiostkoh.portafolio` to version 1 - init
Flyway: Migrating schema `studiostkoh.portafolio` to version 2 - create core tables
Flyway: Migrating schema `studiostkoh.portafolio` to version 3 - add collaborator flag
Flyway: Migrating schema `studiostkoh.portafolio` to version 4 - create certificate table
Flyway: Migrating schema `studiostkoh.portafolio` to version 5 - migration and add
Flyway: Successfully applied 5 migrations

Checking Migration Status

View applied migrations:

SELECT * FROM `studiostkoh.portafolio`.flyway_schema_history
ORDER BY installed_rank;

Creating New Migrations

When you need to modify the schema:

Create Migration File

Create a new file in src/main/resources/db/migration/:

V6__add_new_feature.sql

Follow the naming convention: V{number}__{description}.sql

Write SQL Changes

USE `studiostkoh.portafolio`;

-- Add your schema changes
ALTER TABLE profile ADD COLUMN new_field VARCHAR(100);

Test Migration

Restart the application to apply:

./mvnw spring-boot:run

Never modify existing migration files after they’ve been applied. Always create a new migration for schema changes.

Database Maintenance

Backup Database

mysqldump -u portfolio_user -p "studiostkoh.portafolio" > backup.sql

Restore Database

mysql -u portfolio_user -p "studiostkoh.portafolio" < backup.sql

Reset Database (Development Only)

This will delete all data. Only use in development!

DROP SCHEMA `studiostkoh.portafolio`;
CREATE SCHEMA `studiostkoh.portafolio`;

Then restart the application to rerun all migrations.

Performance Optimization

Indexes

The schema includes strategic indexes:

Unique indexes on email, slug, and (profile_id, slug) combinations
Composite index on (profile_id, featured, sort_order) for efficient project queries
Foreign key indexes automatically created by MySQL

Connection Pooling

Portfolio Hub API uses HikariCP (default in Spring Boot):

spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=30000

Troubleshooting

Flyway migration failed

Symptoms: Application fails to start with Flyway errorSolutions:

Check database user has CREATE/ALTER permissions
Verify schema exists: SHOW DATABASES LIKE 'studiostkoh.portafolio';
Check migration file syntax
Review flyway_schema_history table for failed migrations

Repair failed migration:

DELETE FROM `studiostkoh.portafolio`.flyway_schema_history 
WHERE success = 0;

Schema validation failed

Symptoms: Hibernate validation error on startupCause: Schema doesn’t match JPA entitiesSolution:

Ensure all Flyway migrations have run successfully
Check for manual schema modifications
Compare schema with entity definitions

Connection refused

Symptoms: Cannot connect to MySQLCheck:

# Is MySQL running?
sudo systemctl status mysql

# Can you connect manually?
mysql -h localhost -u portfolio_user -p

# Check environment variables
echo $MYSQL_HOST
echo $MYSQL_USER

Duplicate entry errors

Symptoms: Unique constraint violationsCheck unique constraints:

Email must be unique in app_user
Slug must be unique in profile
(profile_id, slug) must be unique in project
Global skill name must be unique

Next Steps

API Reference

Explore database operations via REST API

Authentication

Create users and authenticate

Managing Portfolio

Learn about portfolio data management

Configuration

Configure database connection settings

Get Started

Setup

Guides

Deployment

​Database Requirements

MySQL 8.0+

Flyway

​Initial Database Setup

​Create Database Schema

​Create Database User (Optional)

​Configure Connection

​Flyway Migrations

​Migration Configuration

​Migration Files

​Database Schema

​Schema Overview

​Core Tables

​1. app_user - Authentication

​2. profile - Portfolio Data

​3. project - Portfolio Projects

​4. skill_category & skill - Skills Organization

​5. global_skill - Global Skill Reference

​6. experience - Work History

​7. education - Academic Background

​8. certificate - Certifications

​9. social_link - Social Media

​10. contact_message - Contact Form Submissions

​11. project_skill - Many-to-Many Relationship

​Audit Fields

​Migration Management

​Automatic Migrations

​Checking Migration Status

​Creating New Migrations

​Database Maintenance

​Backup Database

​Restore Database

​Reset Database (Development Only)

​Performance Optimization

​Indexes

​Connection Pooling

​Troubleshooting

​Next Steps

API Reference

Authentication

Managing Portfolio

Configuration

Build docs developers (and LLMs) love

Database Requirements

Initial Database Setup

Create Database Schema

Create Database User (Optional)

Configure Connection

Flyway Migrations

Migration Configuration

Migration Files

Database Schema

Schema Overview

Core Tables

1. app_user - Authentication

2. profile - Portfolio Data

3. project - Portfolio Projects

4. skill_category & skill - Skills Organization

5. global_skill - Global Skill Reference

6. experience - Work History

7. education - Academic Background

8. certificate - Certifications

9. social_link - Social Media

10. contact_message - Contact Form Submissions

11. project_skill - Many-to-Many Relationship

Audit Fields

Migration Management

Automatic Migrations

Checking Migration Status

Creating New Migrations

Database Maintenance

Backup Database

Restore Database

Reset Database (Development Only)

Performance Optimization

Indexes

Connection Pooling

Troubleshooting

Next Steps