Skema Database

Architecture Dua Database

Yomu menggunakan dua database PostgreSQL yang sepenuhnya terpisah — tanpa shared state antar layanan.

Database	Layanan	Tujuan
Core_DB	Java Spring Boot	Manajemen pengguna, CRUD konten, outbox event
Engine_DB	Rust Axum	Data gamifikasi, liga/klan, achievement

Jangan pernah melakukan SQL join antar database. Selalu gunakan panggilan API atau sinkronisasi event untuk operasi lintas context.

Skema Core_DB (Layanan Java)

Tabel Users

Melacak semua pengguna terdaftar dengan informasi autentikasi dan profil.

CREATE TABLE users (
    user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(50) NOT NULL UNIQUE,
    display_name VARCHAR(100),
    email VARCHAR(255) NOT NULL UNIQUE,
    phone_number VARCHAR(20),
    password_hash VARCHAR(255),
    role VARCHAR(20) DEFAULT 'USER' CHECK (role IN ('ADMIN', 'USER', 'GUEST')),
    google_sub VARCHAR(255) UNIQUE,
    deleted_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_google_sub ON users(google_sub) WHERE google_sub IS NOT NULL;
CREATE INDEX idx_users_username ON users(username);
CREATE TRIGGER users_updated_at BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

Tabel Articles

Menyimpan artikel konten edukasi.

CREATE TABLE articles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    category VARCHAR(50),
    author_id UUID REFERENCES users(user_id),
    status VARCHAR(20) DEFAULT 'DRAFT' CHECK (status IN ('DRAFT', 'PUBLISHED', 'ARCHIVED')),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_articles_category ON articles(category);
CREATE INDEX idx_articles_author ON articles(author_id);
CREATE INDEX idx_articles_status ON articles(status);

Tabel Quizzes

Mengaitkan kuis dengan artikel.

CREATE TABLE quizzes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    article_id UUID NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    question TEXT NOT NULL,
    options JSONB NOT NULL,
    answer JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_quizzes_article ON quizzes(article_id);

Tabel User Attempts

Melacak status penyelesaian kuis pengguna.

CREATE TABLE user_attempts (
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    kuis_id UUID NOT NULL REFERENCES quizzes(id) ON DELETE CASCADE,
    score DECIMAL(5,2),
    completed_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (user_id, kuis_id)
);

CREATE INDEX idx_user_attempts_quiz ON user_attempts(kuis_id);
CREATE INDEX idx_user_attempts_user ON user_attempts(user_id);

Tabel Comments

Mendukung thread komentar hierarkis dengan fungsionalitas balasan.

CREATE TABLE comments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    article_id UUID NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    parent_comment_id UUID REFERENCES comments(id) ON DELETE SET NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    is_deleted BOOLEAN DEFAULT false,
    deleted_at TIMESTAMPTZ
);

CREATE INDEX idx_comments_article ON comments(article_id);
CREATE INDEX idx_comments_user ON comments(user_id);
CREATE INDEX idx_comments_parent ON comments(parent_comment_id) WHERE parent_comment_id IS NOT NULL;
CREATE INDEX idx_comments_created ON comments(created_at);

Tabel Comment Reactions

Melacak reaksi upvote/downvote pada komentar.

CREATE TABLE comment_reactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    comment_id UUID NOT NULL REFERENCES comments(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    reaction_type VARCHAR(10) NOT NULL CHECK (reaction_type IN ('UPVOTE', 'DOWNVOTE')),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (comment_id, user_id, reaction_type)
);

CREATE INDEX idx_comment_reactions_comment ON comment_reactions(comment_id);
CREATE INDEX idx_comment_reactions_user ON comment_reactions(user_id);

Tabel Failed Sync Events

Implementasi outbox pattern untuk pengiriman event yang andal ke layanan Rust.

CREATE TYPE event_type AS ENUM (
    'USER_CREATED',
    'USER_UPDATED',
    'USER_DELETED',
    'PROGRESS_UPDATE'
);

CREATE TYPE event_status AS ENUM ('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED');

CREATE TABLE failed_sync_events (
    event_id SERIAL PRIMARY KEY,
    event_type event_type NOT NULL,
    payload_json JSONB NOT NULL,
    status event_status DEFAULT 'PENDING',
    retry_count INTEGER DEFAULT 0,
    last_error TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_events_status ON failed_sync_events(status);
CREATE INDEX idx_events_created ON failed_sync_events(created_at);
CREATE INDEX idx_events_type ON failed_sync_events(event_type);
CREATE INDEX idx_events_pending ON failed_sync_events(event_id) WHERE status = 'PENDING';

CREATE FUNCTION process_failed_events() RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER failed_events_updated
    BEFORE UPDATE ON failed_sync_events
    FOR EACH ROW EXECUTE FUNCTION process_failed_events();

Skema Engine_DB (Layanan Rust)

Tabel Engine Users (Shadow Users)

Mencerminkan data pengguna dari Core_DB dengan field khusus gamifikasi.

CREATE TABLE engine_users (
    user_id UUID PRIMARY KEY,
    total_score INTEGER DEFAULT 0,
    level INTEGER DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_engine_users_level ON engine_users(level);
CREATE INDEX idx_engine_users_score ON engine_users(total_score);

Tabel Clans

Melacak grup pengguna (kelas/tim) dengan pelacakan skor.

CREATE TABLE clans (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL,
    description TEXT,
    leader_id UUID NOT NULL REFERENCES engine_users(user_id),
    tier VARCHAR(20) DEFAULT 'BRONZE' CHECK (tier IN ('BRONZE', 'SILVER', 'GOLD', 'PLATINUM', 'DIAMOND')),
    total_score INTEGER DEFAULT 0,
    member_count INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_clans_name ON clans(name);

Tabel Clan Members

Relasi many-to-many antara pengguna dan klan.

CREATE TABLE clan_members (
    clan_id UUID NOT NULL REFERENCES clans(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES engine_users(user_id) ON DELETE CASCADE,
    joined_at TIMESTAMPTZ DEFAULT NOW(),
    role VARCHAR(20) DEFAULT 'MEMBER' CHECK (role IN ('LEADER', 'OFFICER', 'MEMBER')),
    PRIMARY KEY (clan_id, user_id)
);

CREATE INDEX idx_clan_members_user ON clan_members(user_id);
CREATE INDEX idx_clan_members_role ON clan_members(role);

Tabel Clan Buffs

Buff sementara yang meningkatkan akumulasi skor klan.

CREATE TABLE clan_buffs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    clan_id UUID NOT NULL REFERENCES clans(id) ON DELETE CASCADE,
    buff_name VARCHAR(50) NOT NULL,
    multiplier DECIMAL(5,2) NOT NULL CHECK (multiplier > 0),
    is_active BOOLEAN DEFAULT true,
    expires_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_clan_buffs_clan ON clan_buffs(clan_id);
CREATE INDEX idx_clan_buffs_active ON clan_buffs(clan_id, is_active);

Tabel Achievements

Milestone gamifikasi yang dapat dibuka oleh pengguna.

CREATE TYPE achievement_type AS ENUM ('READ_ARTICLE', 'COMPLETE_QUIZ', 'CLIMB_LEADERBOARD', 'JOIN_CLAN');

CREATE TABLE achievements (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL,
    description TEXT NOT NULL,
    milestone_target INTEGER NOT NULL,
    achievement_type achievement_type NOT NULL,
    reward_points INTEGER NOT NULL,
    icon_url TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_achievements_name ON achievements(name);
CREATE INDEX idx_achievements_type ON achievements(achievement_type);

Tabel User Achievements

Melacak progres individu pengguna menuju achievement.

CREATE TABLE user_achievements (
    user_id UUID NOT NULL REFERENCES engine_users(user_id) ON DELETE CASCADE,
    achievement_id UUID NOT NULL REFERENCES achievements(id) ON DELETE CASCADE,
    current_progress INTEGER DEFAULT 0,
    is_completed BOOLEAN DEFAULT false,
    is_shown_on_profile BOOLEAN DEFAULT false,
    completed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (user_id, achievement_id)
);

CREATE INDEX idx_user_achievements_user ON user_achievements(user_id);
CREATE INDEX idx_user_achievements_achievement ON user_achievements(achievement_id);
CREATE INDEX idx_user_achievements_completed ON user_achievements(user_id) WHERE is_completed = true;

Tabel Daily Missions

Tugas gamifikasi harian dengan target yang berubah.

CREATE TABLE daily_missions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    description TEXT NOT NULL,
    target_count INTEGER NOT NULL,
    current_count INTEGER DEFAULT 0,
    date DATE NOT NULL,
    reward_points INTEGER NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    expires_at TIMESTAMPTZ NOT NULL
);

CREATE UNIQUE INDEX idx_daily_missions_date ON daily_missions(date);
CREATE INDEX idx_daily_missions_expires ON daily_missions(expires_at);

Tabel User Missions

Melacak progres misi individu.

CREATE TABLE user_missions (
    user_id UUID NOT NULL REFERENCES engine_users(user_id) ON DELETE CASCADE,
    mission_id UUID NOT NULL REFERENCES daily_missions(id) ON DELETE CASCADE,
    current_progress INTEGER DEFAULT 0,
    is_claimed BOOLEAN DEFAULT false,
    claimed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (user_id, mission_id)
);

CREATE INDEX idx_user_missions_user ON user_missions(user_id);
CREATE INDEX idx_user_missions_mission ON user_missions(mission_id);
CREATE INDEX idx_user_missions_unclaimed ON user_missions(user_id) WHERE is_claimed = false;

Tabel Quiz History

Menyimpan upaya kuis yang telah selesai untuk penilaian gamifikasi.

CREATE TABLE quiz_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES engine_users(user_id) ON DELETE CASCADE,
    article_id UUID NOT NULL,
    score DECIMAL(5,2) NOT NULL,
    accuracy DECIMAL(5,2) NOT NULL,
    completed_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_quiz_history_user ON quiz_history(user_id);
CREATE INDEX idx_quiz_history_date ON quiz_history(completed_at);
CREATE INDEX idx_quiz_history_user_date ON quiz_history(user_id, completed_at DESC);

Diagram Relasi Database

Sequences dan Indexes

pattern Index Umum

-- Composite indexes untuk pattern query umum
CREATE INDEX idx_user_attempts_user_quiz ON user_attempts(user_id, kuis_id);
CREATE INDEX idx_comment_reactions_comment_user ON comment_reactions(comment_id, user_id);
CREATE INDEX idx_clan_members_clan_user ON clan_members(clan_id, user_id);

-- Index berbasis waktu untuk analytics
CREATE INDEX idx_quiz_history_user_time ON quiz_history(user_id, completed_at DESC);
CREATE INDEX idx_user_missions_user_date ON user_missions(user_id, created_at DESC);

Trigger untuk Konsistensi Data

-- Perbarui total skor klan saat skor anggota berubah
CREATE OR REPLACE FUNCTION update_clan_score()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE clans SET total_score = (
        SELECT COALESCE(SUM(eu.total_score), 0)
        FROM engine_users eu
        INNER JOIN clan_members cm ON eu.user_id = cm.user_id
        WHERE cm.clan_id = NEW.clan_id
    ) WHERE id = NEW.clan_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Perbarui level pengguna berdasarkan threshold skor
CREATE OR REPLACE FUNCTION update_user_level()
RETURNS TRIGGER AS $$
BEGIN
    NEW.level := GREATEST(1, FLOOR(NEW.total_score / 1000) + 1);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Strategi Migrasi Skema

Layanan Java: Migrasi Flyway atau Liquibase di src/main/resources/db/migration/
Layanan Rust: Migrasi Diesel atau script SQL manual di migrations/
Tanpa migrasi lintas database — setiap layanan mengelola skemanya sendiri

Skema Database

On this page