Skip to main content

​
Overview

The data model for the Job Manager subsystem is designed to support all functional requirements while maintaining data integrity, scalability, and performance through database sharding.

​
Sharding Strategy

Geo-Population Based Sharding

User and company data is partitioned across database shards using a Geo-Population Based Sharding Strategy. The system uses the Country of the Company as the primary determinant for sharding, but distributes data based on population density to prevent hotspots.

​
Shard Distribution (5 Geographic Shards)

To prevent data hotspots caused by population variance (e.g., India and China in the same Asia shard would overload it), the system uses a custom routing strategy based on world population data:
ShardRegionCoverage
Shard 1AfricaAll African nations
Shard 2EuropeEuropean nations
Shard 3AmericasNorth and South America
Shard 4East AsiaChina and surrounding East Asian countries
Shard 5South/SE Asia & PacificIndia, ASEAN nations, Australia, and Pacific

​
Sharding Logic and Routing

The routing mechanism is implemented via a dynamic DataSourceRouter within the Spring Boot application layer.
The router intercepts every database transaction, extracts the shardId from the Country JSON object to determine which shard to access, and routes to it.
@Component
public class DynamicDataSourceRouter extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        String country = ShardContextHolder.getCurrentCountry();
        return ShardResolver.resolveShardId(country);
    }
}
For new registrations or unauthenticated queries, a static ShardResolver utility maps the input Country to the corresponding Shard ID (1–5) based on the defined grouping logic. This ensures write operations land on the correct shard without requiring a global lookup table.
public class ShardResolver {
    public static int resolveShardId(String country) {
        if (AFRICAN_COUNTRIES.contains(country)) return 1;
        if (EUROPEAN_COUNTRIES.contains(country)) return 2;
        if (AMERICAN_COUNTRIES.contains(country)) return 3;
        if (EAST_ASIAN_COUNTRIES.contains(country)) return 4;
        return 5; // South/SE Asia & Pacific (default)
    }
}
The country attribute is stored as an entity attribute containing the country code and shardId for efficient routing:
{
  "code": "VN",
  "name": "Vietnam",
  "dialCode": "+84",
  "shardId": 5
}

​
Data Migration Logic

Since the Country shard key is mutable, whenever there is an update in the country property, physical migration might be processed to relocate the user’s data.
  • Same Shard Migration
  • Cross-Shard Migration
If the profile updates the country field to another country in the same shard region (e.g., from Vietnam to Singapore, both in Shard 5), the system performs a basic UPDATE query in the database.
UPDATE companies
SET country = '{"code": "SG", "name": "Singapore", "shardId": 5}'
WHERE id = :companyId;
This strategy ensures that the database load is distributed more evenly based on actual user population density rather than geographical landmass, optimizing search performance and system resilience.

​
Core Entities

​
Company Entity

CREATE TABLE companies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255), -- NULL for SSO users
    name VARCHAR(255) NOT NULL,

    -- Contact Information
    phone VARCHAR(20),
    street VARCHAR(255),
    city VARCHAR(100),
    country VARCHAR(100) NOT NULL, -- SHARDING KEY

    -- Authentication
    sso_provider VARCHAR(50), -- GOOGLE, MICROSOFT, FACEBOOK, GITHUB
    sso_user_id VARCHAR(255),
    is_activated BOOLEAN DEFAULT FALSE,
    activation_token VARCHAR(255),

    -- Profile
    about_us TEXT,
    who_we_are_looking_for TEXT,
    logo_url VARCHAR(500),

    -- Premium
    is_premium BOOLEAN DEFAULT FALSE,
    premium_expires_at TIMESTAMP,

    -- Audit
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    last_login_at TIMESTAMP,

    -- Indexes
    INDEX idx_email (email),
    INDEX idx_country (country),
    INDEX idx_premium (is_premium, premium_expires_at),
    INDEX idx_sso (sso_provider, sso_user_id)
);
Relationships:
  • One-to-Many with JobPosts
  • One-to-Many with SearchProfiles
  • One-to-One with Subscription
  • One-to-Many with CompanyMedia
CREATE TABLE company_media (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,

    media_type VARCHAR(20) NOT NULL, -- IMAGE, VIDEO
    media_url VARCHAR(500) NOT NULL,
    title VARCHAR(255),
    display_order INT DEFAULT 0,

    created_at TIMESTAMP DEFAULT NOW(),

    INDEX idx_company (company_id),
    INDEX idx_type (company_id, media_type)
);

​
Job Post Entity

CREATE TABLE job_posts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,

    -- Basic Information
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    location VARCHAR(255) NOT NULL,

    -- Employment
    is_full_time BOOLEAN DEFAULT FALSE,
    is_part_time BOOLEAN DEFAULT FALSE,
    is_internship BOOLEAN DEFAULT FALSE,
    is_contract BOOLEAN DEFAULT FALSE,

    -- Salary
    salary_type VARCHAR(20) NOT NULL, -- RANGE, ESTIMATION, NEGOTIABLE
    salary_min DECIMAL(10,2),
    salary_max DECIMAL(10,2),
    salary_currency VARCHAR(3) DEFAULT 'USD',
    salary_text VARCHAR(255), -- "About 1000", "Up to 2000"

    -- Dates
    posted_date TIMESTAMP DEFAULT NOW(),
    expiry_date TIMESTAMP,

    -- Status
    is_public BOOLEAN DEFAULT FALSE,

    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    INDEX idx_company (company_id),
    INDEX idx_public (is_public, posted_date DESC),
    INDEX idx_expiry (expiry_date),
    FULLTEXT idx_search (title, description)
);

CREATE TABLE job_skills (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    job_post_id UUID NOT NULL REFERENCES job_posts(id) ON DELETE CASCADE,
    skill_name VARCHAR(100) NOT NULL,

    created_at TIMESTAMP DEFAULT NOW(),

    INDEX idx_job (job_post_id),
    INDEX idx_skill (skill_name),
    INDEX idx_job_skill (job_post_id, skill_name),

    UNIQUE(job_post_id, skill_name)
);

​
Application Tracking

Application data is primarily managed by Job Applicant subsystem. Job Manager maintains references and status.
CREATE TABLE application_references (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    job_post_id UUID NOT NULL REFERENCES job_posts(id) ON DELETE CASCADE,
    applicant_id UUID NOT NULL, -- Reference to JA subsystem

    -- Status tracking
    status VARCHAR(20) DEFAULT 'PENDING', -- PENDING, ARCHIVED

    -- Company notes (internal only)
    notes TEXT,

    -- Marking
    is_favorite BOOLEAN DEFAULT FALSE,
    is_warning BOOLEAN DEFAULT FALSE,

    applied_at TIMESTAMP,
    archived_at TIMESTAMP,

    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    INDEX idx_job (job_post_id, status),
    INDEX idx_applicant (applicant_id),
    INDEX idx_status (status),
    INDEX idx_favorite (is_favorite),
    INDEX idx_warning (is_warning),

    UNIQUE(job_post_id, applicant_id)
);

​
Subscription & Premium

CREATE TABLE subscriptions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,

    -- Subscription details
    status VARCHAR(20) NOT NULL, -- ACTIVE, EXPIRED, CANCELLED
    start_date TIMESTAMP NOT NULL,
    end_date TIMESTAMP NOT NULL,

    -- Payment reference
    payment_transaction_id UUID,

    -- Notifications
    seven_day_warning_sent BOOLEAN DEFAULT FALSE,
    expiry_notice_sent BOOLEAN DEFAULT FALSE,

    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    INDEX idx_company (company_id),
    INDEX idx_status (status),
    INDEX idx_expiry (end_date),
    INDEX idx_notifications (end_date, seven_day_warning_sent, expiry_notice_sent)
);

CREATE TABLE search_profiles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,

    profile_name VARCHAR(255) NOT NULL,

    -- Search criteria
    country VARCHAR(100),
    min_salary DECIMAL(10,2),
    max_salary DECIMAL(10,2),

    -- Employment preferences (JSON array)
    employment_types JSONB, -- ["FULL_TIME", "INTERNSHIP"]

    -- Education requirements (JSON array)
    education_levels JSONB, -- ["BACHELOR", "MASTER"]

    is_active BOOLEAN DEFAULT TRUE,

    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    INDEX idx_company (company_id),
    INDEX idx_active (is_active),
    INDEX idx_country (country)
);

CREATE TABLE search_profile_skills (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    search_profile_id UUID NOT NULL REFERENCES search_profiles(id) ON DELETE CASCADE,
    skill_name VARCHAR(100) NOT NULL,

    created_at TIMESTAMP DEFAULT NOW(),

    INDEX idx_profile (search_profile_id),
    INDEX idx_skill (skill_name),

    UNIQUE(search_profile_id, skill_name)
);

​
Payment Transactions

CREATE TABLE payment_transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- User reference
    user_type VARCHAR(20) NOT NULL, -- COMPANY or APPLICANT
    user_id UUID NOT NULL,
    user_email VARCHAR(255) NOT NULL,

    -- Payment details
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',

    -- Status
    status VARCHAR(20) NOT NULL, -- SUCCESS, PENDING, FAILED, REFUNDED, CANCELLED
    payment_method VARCHAR(50) NOT NULL, -- STRIPE, PAYPAL
    payment_provider VARCHAR(50) NOT NULL,

    -- Provider data
    provider_transaction_id VARCHAR(255) UNIQUE,
    provider_customer_id VARCHAR(255),

    -- Subscription dates
    subscription_start_date TIMESTAMP,
    subscription_end_date TIMESTAMP,

    -- Metadata
    metadata JSONB,

    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    INDEX idx_user (user_id, user_type),
    INDEX idx_email (user_email),
    INDEX idx_status (status),
    INDEX idx_provider (provider_transaction_id),
    INDEX idx_created (created_at DESC)
);

​
Authentication

CREATE TABLE token_revocations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL,
    token_jti VARCHAR(255) UNIQUE NOT NULL, -- JWT ID

    revoked_at TIMESTAMP DEFAULT NOW(),
    expires_at TIMESTAMP NOT NULL,

    INDEX idx_jti (token_jti),
    INDEX idx_company (company_id),
    INDEX idx_expires (expires_at)
);

CREATE TABLE login_attempts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL,

    success BOOLEAN NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,

    attempted_at TIMESTAMP DEFAULT NOW(),

    INDEX idx_email_time (email, attempted_at DESC),
    INDEX idx_attempted (attempted_at)
);

​
Entity Relationship Diagram

The complete ERD for the Job Manager system illustrates all entities, relationships, and data types across both the Applicant and Company subsystems.
Entity Relationship Diagram

​
Entity Overview

The diagram shows the following major entity groups:

User Entities

  • Applicant - Job seekers with personal info, biography, and avatar
  • ApplicantAuth - Authentication data (email, password, SSO, role)
  • Company - Employers with company details and branding
  • CompanyAuth - Company authentication credentials

Profile & Experience

  • Education - Academic background with degree type and GPA
  • WorkExperience - Employment history
  • ApplicantMedia / CompanyMedia - Images and videos

Job & Applications

  • JobPost - Job listings with salary, location, and employment type
  • JobApplication - Applications linking applicants to jobs
  • SkillTag - Skills database with junction tables

Premium & Payments

  • Subscription - Premium subscription management
  • Transaction - Payment records with status tracking
  • Company_Applicant_Marker - Favorite/warning markers

​
Enumerations

EnumValues
DegreeTypeBACHELOR, MASTER, DOCTORATE
MediaTypeIMAGE, VIDEO
RoleTypeADMIN, USER
TransactionStatusSUCCESSFUL, FAILED, CANCEL, PENDING
PaymentMethodVISA, EWALLET, CREDIT_CARD
SubscriptionStatusACTIVE, EXPIRED, CANCELED
MarkerFAVORITE, WARNING
ApplicationStatusPENDING, ARCHIVED

​
Search Profiles

The system supports search profiles for both sides:
  • SearchPostProfile - Applicant-created profiles for job searches (salary range, employment type, country)
  • SearchApplicantProfile - Company-created profiles for candidate searches (salary range, degree level, employment type)
Both profile types link to SkillTag through junction tables for skill-based filtering.

​
Data Migration Strategy

​
Shard Migration

When a company changes their country (sharding key):
1

Identify Target Shard

Determine new shard based on new country value
2

Begin Transaction

Start distributed transaction across shards
3

Copy Data

Copy all company data to new shard
4

Verify Integrity

Ensure all data copied correctly
5

Update Routing

Update routing table with new shard mapping
6

Delete Old Data

Remove data from old shard
7

Commit Transaction

Finalize migration

​
Design Justifications

​
Advantages

  • Horizontal sharding by country enables geographic distribution
  • Independent scaling of each shard
  • Reduced query load per database
  • Location-based queries hit only relevant shard
  • Full-text search indexes on job posts
  • Compound indexes for common query patterns
  • Clear entity boundaries
  • Normalized schema reduces redundancy
  • Consistent naming conventions
  • Password hashing for non-SSO users
  • Token revocation tracking
  • Audit trails with timestamps

​
Limitations

Queries spanning multiple countries require querying multiple shards and merging results
Changing country requires complex data migration across shards
Some denormalization (e.g., application references) required for performance