Skip to content

Desain Basis Data — Soedirman Digital Repository (SDR)

Field Nilai
ID Dokumen SDR-DATA-001
Pilar Data
Standar Acuan DAMA-DMBOK & ISO/IEC 9075 (PostgreSQL)
Status Aktif / Tersinkronisasi dengan Kode
Terakhir Diperbarui 2026-11-21
Sumber Kebenaran prisma/schema.prisma, prisma/migrations/*/migration.sql, prisma/seed.ts, prisma.config.ts, lib/db/client.ts, scripts/media-cleanup.ts, scripts/repositories-purge.ts

Basis data Soedirman Digital Repository (SDR) dijalankan pada PostgreSQL (pg_trgm aktif) dan dikelola menggunakan Prisma ORM 7.10.1 dengan driver adapter @prisma/adapter-pg (prisma.config.ts). Klien Prisma dikompilasi ke direktori terpisah generated/prisma/ dan diinstansiasi sebagai singleton melalui lib/db/client.ts.

Skema produksi aktual terdiri atas 10 model (tabel) dan 5 tipe enum yang berevolusi melalui 3 berkas migrasi SQL:

Migrasi Ruang Lingkup Perubahan
20261104151540_init Pembentukan 2 enum awal (repository_status, media_kind), 6 tabel inti (admins, categories, repositories, repository_screenshots, media_uploads, auth_rate_limits), ekstensi pg_trgm, serta 5 indeks kustom SQL.
20261109071500_add_admin_roles_and_audit_logs Penambahan admin_role & audit_action (19 nilai awal), penambahan username, role, is_active, last_login_at, created_by_id pada admins (email menjadi opsional), serta pembuatan tabel audit_logs.
20261115120000_add_submissions_shortlinks_deadline Penambahan 6 nilai audit_action baru (CREATE_SHORTLINK hingga SUBMIT_REPOSITORY), enum submission_status, kolom deadline + indeks di repositories, tabel short_links, repository_submissions, system_settings, serta seeding kategori Formulir (formulir).
  1. Konvensi penamaan ganda: Nama tabel dan kolom menggunakan snake_case di PostgreSQL (@map / @@map) dan camelCase pada model TypeScript Prisma.
  2. Kunci utama UUID: Seluruh tabel entitas menggunakan @db.Uuid (@default(uuid())), kecuali auth_rate_limits.key dan system_settings.key yang menggunakan kunci string natural.
  3. Penyimpanan waktu terstandar: Seluruh kolom stempel waktu menggunakan TIMESTAMPTZ(6) (UTC), sementara repositories.release_date menggunakan DATE. Penyajian di antarmuka publik diformat ke zona waktu Asia/Jakarta (id-ID).
  4. Penghapusan lunak (Soft Delete): Tabel repositories menggunakan deleted_at agar slug lama tidak dipakai ulang oleh entri lain dan aset media terlindungi selama masa retensi (REPOSITORY_RETENTION_DAYS, default 30 hari).
  5. Pemisahan data biner: Tidak ada BLOB/Base64 di PostgreSQL; aset visual berada di Object Storage S3-kompatibel atau disk lokal public/uploads/, sementara database menyimpan public_url, storage_key, serta dimensi (width, height) untuk mencegah pergeseran tata letak (CLS).

erDiagram
    ADMINS ||--o{ ADMINS : "created_by (self)"
    ADMINS ||--o{ AUDIT_LOGS : "actor"
    ADMINS ||--o{ MEDIA_UPLOADS : "uploaded_by"
    ADMINS ||--o{ SHORT_LINKS : "created_by"
    ADMINS ||--o{ REPOSITORY_SUBMISSIONS : "reviewed_by"
    CATEGORIES ||--o{ REPOSITORIES : "groups"
    CATEGORIES ||--o{ REPOSITORY_SUBMISSIONS : "categorizes"
    REPOSITORIES ||--o{ REPOSITORY_SCREENSHOTS : "has_gallery"
    REPOSITORIES |o--o{ MEDIA_UPLOADS : "attaches"
    REPOSITORIES |o--o{ REPOSITORY_SUBMISSIONS : "approved_from"

    ADMINS {
        uuid id PK
        varchar_120 name
        varchar_32 username UK
        varchar_255 email "UK nullable"
        text password_hash
        admin_role role "SUPER_ADMIN or ADMIN"
        boolean is_active
        int session_version
        timestamptz last_login_at "nullable"
        uuid created_by_id FK "nullable"
        timestamptz created_at
        timestamptz updated_at
    }

    AUDIT_LOGS {
        uuid id PK
        uuid actor_id FK "nullable"
        varchar_64 actor_username
        varchar_120 actor_display_name
        audit_action action
        varchar_60 entity_type
        varchar_100 entity_id "nullable"
        varchar_255 entity_name "nullable"
        jsonb metadata "nullable"
        varchar_100 ip_address "nullable"
        varchar_500 user_agent "nullable"
        timestamptz created_at
    }

    CATEGORIES {
        uuid id PK
        varchar_100 name "unique lower(name)"
        varchar_120 slug UK
        text description "nullable"
        timestamptz created_at
        timestamptz updated_at
    }

    REPOSITORIES {
        uuid id PK
        varchar_120 title
        varchar_150 slug UK
        varchar_300 short_description
        text overview
        varchar_160 developer_name
        text developer_description "nullable"
        text developer_website "nullable"
        uuid category_id FK
        text cover_url "nullable"
        text cover_storage_key "nullable"
        text banner_url "nullable"
        text banner_storage_key "nullable"
        text product_url "nullable"
        text source_code_url "nullable"
        text demo_url "nullable"
        text_array tech_stack
        text_array tags
        text_array key_features
        date release_date "nullable"
        timestamptz deadline "nullable"
        repository_status status "DRAFT or PUBLISHED"
        boolean is_featured
        timestamptz published_at "nullable"
        text search_text
        timestamptz created_at
        timestamptz updated_at
        timestamptz deleted_at "nullable"
    }

    REPOSITORY_SCREENSHOTS {
        uuid id PK
        uuid repository_id FK
        text image_url
        text storage_key
        varchar_255 alt_text "nullable"
        int sort_order
        timestamptz created_at
    }

    MEDIA_UPLOADS {
        uuid id PK
        text storage_key UK
        text public_url
        media_kind kind "COVER BANNER SCREENSHOT"
        varchar_50 mime_type
        int size_bytes
        int width
        int height
        uuid uploaded_by_id FK
        uuid repository_id FK "nullable"
        timestamptz attached_at "nullable"
        timestamptz created_at
    }

    AUTH_RATE_LIMITS {
        varchar_255 key PK
        int attempts
        timestamptz window_started_at
        timestamptz locked_until "nullable"
        timestamptz updated_at
    }

    SHORT_LINKS {
        uuid id PK
        varchar_160 title
        varchar_64 alias UK
        text original_url
        boolean is_active
        int click_count
        uuid created_by_id FK "nullable"
        timestamptz created_at
        timestamptz updated_at
    }

    REPOSITORY_SUBMISSIONS {
        uuid id PK
        varchar_120 submitter_name
        varchar_160 title
        varchar_500 short_description
        text repository_url
        uuid category_id FK
        text cover_url "nullable"
        text cover_storage_key "nullable"
        timestamptz deadline "nullable"
        text_array tech_stack
        submission_status status "PENDING APPROVED REJECTED"
        uuid reviewed_by_id FK "nullable"
        timestamptz reviewed_at "nullable"
        text rejection_reason "nullable"
        uuid approved_repository_id FK "nullable"
        timestamptz created_at
        timestamptz updated_at
    }

    SYSTEM_SETTINGS {
        varchar_100 key PK
        text value
        timestamptz updated_at
    }

2.1 Ringkasan Relasi & Integritas Referensial (ON DELETE)

Section titled “2.1 Ringkasan Relasi & Integritas Referensial (ON DELETE)”
Relasi Kardinalitas Foreign Key Perilaku ON DELETE Alasan Desain
Admin -> Admin (AdminCreatedBy) 1 : N admins.created_by_id SetNull Jejak pembuat tetap aman meski akun pembuat dihapus/dimodifikasi.
Admin -> AuditLog 1 : N audit_logs.actor_id SetNull Log audit imutabel; snapshot actor_username & actor_display_name tersimpan langsung di baris log.
Admin -> MediaUpload 1 : N media_uploads.uploaded_by_id Restrict Mencegah penghapusan akun admin selama masih tercatat sebagai pemilik unggahan.
Admin -> ShortLink 1 : N short_links.created_by_id SetNull Tautan pendek tetap aktif meskipun pembuatnya berubah.
Admin -> RepositorySubmission 1 : N repository_submissions.reviewed_by_id SetNull Riwayat peninjauan pengajuan tetap utuh.
Category -> Repository 1 : N repositories.category_id Restrict Kategori tidak dapat dihapus selama masih dirujuk oleh karya (diperkuat pengecekan UI/Server Action untuk karya aktif).
Category -> RepositorySubmission 1 : N repository_submissions.category_id Restrict Menjaga validitas kategori pada antrean pengajuan publik.
Repository -> RepositoryScreenshot 1 : N repository_screenshots.repository_id Cascade Galeri terikat siklus hidup penuh pada repositori; ikut terhapus saat hard-purge.
Repository -> MediaUpload 1 : N media_uploads.repository_id SetNull Unggahan dibuat sebelum record repositori disimpan; dilepas (NULL) untuk disapu skrip cleanup bila tidak terpakai.
Repository -> RepositorySubmission 1 : N repository_submissions.approved_repository_id SetNull Menghubungkan pengajuan yang disetujui ke karya terbit tanpa memblokir penghapusan karya di masa depan.

Nama Enum (Prisma) Nama Tipe PostgreSQL Anggota Nilai
RepositoryStatus repository_status DRAFT, PUBLISHED
MediaKind media_kind COVER, BANNER, SCREENSHOT
AdminRole admin_role SUPER_ADMIN, ADMIN
SubmissionStatus submission_status PENDING, APPROVED, REJECTED
AuditAction audit_action (25 nilai) ADMIN_LOGIN_SUCCESS, ADMIN_LOGIN_FAILED, ADMIN_LOGOUT, REPOSITORY_CREATED, REPOSITORY_UPDATED, REPOSITORY_DELETED, REPOSITORY_PUBLISHED, REPOSITORY_UNPUBLISHED, REPOSITORY_FEATURED, REPOSITORY_UNFEATURED, CATEGORY_CREATED, CATEGORY_UPDATED, CATEGORY_DELETED, ADMIN_CREATED, ADMIN_ACTIVATED, ADMIN_DEACTIVATED, ADMIN_PASSWORD_RESET, ADMIN_PASSWORD_CHANGED, ADMIN_PROFILE_UPDATED, CREATE_SHORTLINK, UPDATE_SHORTLINK, DELETE_SHORTLINK, APPROVE_REPOSITORY, REJECT_REPOSITORY, SUBMIT_REPOSITORY

Kolom SQL Field Prisma Tipe & Panjang Constraint & Default Keterangan
id id UUID PK, DEFAULT uuid() Identitas unik administrator
name name VARCHAR(120) NOT NULL Nama lengkap administrator
username username VARCHAR(32) NOT NULL, UNIQUE Username login lowercase ([a-z0-9_-]+)
email email VARCHAR(255) NULL, UNIQUE Alamat email opsional (digunakan saat seeding)
password_hash passwordHash TEXT NOT NULL Hash Argon2id ($argon2id$v=19$m=19456,t=2,p=1...)
role role admin_role NOT NULL, DEFAULT 'ADMIN' Peran otorisasi (SUPER_ADMIN atau ADMIN)
is_active isActive BOOLEAN NOT NULL, DEFAULT true Status aktif akun; false memblokir login
session_version sessionVersion INTEGER NOT NULL, DEFAULT 0 Dinaikkan saat logout, deaktivasi, atau reset sandi untuk mencabut JWT lama
last_login_at lastLoginAt TIMESTAMPTZ(6) NULL Waktu login berhasil terakhir
created_by_id createdById UUID NULL, FK -> admins(id) ID Super Admin yang membuat akun ini
created_at createdAt TIMESTAMPTZ(6) NOT NULL, DEFAULT now() Waktu pembuatan record
updated_at updatedAt TIMESTAMPTZ(6) NOT NULL, @updatedAt Waktu pembaruan terakhir
Kolom SQL Field Prisma Tipe & Panjang Constraint & Default Keterangan
id id UUID PK, DEFAULT gen_random_uuid() Identitas unik catatan audit
actor_id actorId UUID NULL, FK -> admins(id) ID admin pelaku aksi (NULL bila login gagal user tidak dikenal)
actor_username actorUsername VARCHAR(64) NOT NULL Snapshot username pelaku (atau "system")
actor_display_name actorDisplayName VARCHAR(120) NOT NULL Snapshot nama tampil pelaku (atau "Unauthenticated" / "System")
action action audit_action NOT NULL Jenis peristiwa audit
entity_type entityType VARCHAR(60) NOT NULL Tipe target (Repository, Category, Admin, Auth, ShortLink, RepositorySubmission, SystemSetting)
entity_id entityId VARCHAR(100) NULL ID target (UUID atau kunci setting)
entity_name entityName VARCHAR(255) NULL Nama entitas sebelum diubah/dihapus
metadata metadata JSONB NULL Rincian tambahan; key sensitif disensor menjadi "[REDACTED]"
ip_address ipAddress VARCHAR(100) NULL IP klien dari x-forwarded-for atau x-real-ip
user_agent userAgent VARCHAR(500) NULL Header user-agent dipotong maksimal 500 karakter
created_at createdAt TIMESTAMPTZ(6) NOT NULL, DEFAULT now() Waktu pencatatan audit
Kolom SQL Field Prisma Tipe & Panjang Constraint & Default Keterangan
id id UUID PK, DEFAULT uuid() Identitas unik kategori
name name VARCHAR(100) NOT NULL, UNIQUE lower(name) Nama kategori, unik secara case-insensitive di PostgreSQL
slug slug VARCHAR(120) NOT NULL, UNIQUE Slug URL kebab-case (mis. web-application, formulir)
description description TEXT NULL Deskripsi kategori untuk halaman /categories/[slug]
created_at createdAt TIMESTAMPTZ(6) NOT NULL, DEFAULT now() Waktu pembuatan
updated_at updatedAt TIMESTAMPTZ(6) NOT NULL, @updatedAt Waktu pembaruan
Kolom SQL Field Prisma Tipe & Panjang Constraint & Default Keterangan
id id UUID PK, DEFAULT uuid() Identitas unik karya repositori
title title VARCHAR(120) NOT NULL Judul karya (3–120 karakter)
slug slug VARCHAR(150) NOT NULL, UNIQUE Slug URL global unik (kebab-case)
short_description shortDescription VARCHAR(300) NOT NULL, DEFAULT '' Ringkasan kartu katalog (wajib 10–300 karakter saat PUBLISHED)
overview overview TEXT NOT NULL, DEFAULT '' Konten deskripsi lengkap (HTML tersanitasi sanitizeOverview)
developer_name developerName VARCHAR(160) NOT NULL, DEFAULT '' Nama pengembang/tim (wajib >= 2 karakter saat PUBLISHED)
developer_description developerDescription TEXT NULL Keterangan tambahan pengembang
developer_website developerWebsite TEXT NULL Situs pengembang (http/https)
category_id categoryId UUID NOT NULL, FK -> categories(id) Kategori utama karya
cover_url coverUrl TEXT NULL URL publik sampul utama
cover_storage_key coverStorageKey TEXT NULL Kunci penyimpanan objek sampul
banner_url bannerUrl TEXT NULL URL publik gambar spanduk
banner_storage_key bannerStorageKey TEXT NULL Kunci penyimpanan objek spanduk
product_url productUrl TEXT NULL URL aplikasi utama (http/https)
source_code_url sourceCodeUrl TEXT NULL URL repositori kode sumber (http/https)
demo_url demoUrl TEXT NULL URL demo opsional (http/https)
tech_stack techStack TEXT[] NOT NULL, DEFAULT '{}' Daftar teknologi (mis. Next.js, PostgreSQL)
tags tags TEXT[] NOT NULL, DEFAULT '{}' Label pencarian tambahan
key_features keyFeatures TEXT[] NOT NULL, DEFAULT '{}' Daftar fitur utama publikasi
release_date releaseDate DATE NULL Tanggal rilis produk
deadline deadline TIMESTAMPTZ(6) NULL Batas waktu (khusus kategori Formulir & prioritas 72 jam)
status status repository_status NOT NULL, DEFAULT 'DRAFT' Status visibilitas (DRAFT / PUBLISHED)
is_featured isFeatured BOOLEAN NOT NULL, DEFAULT false Penanda karya pilihan pengelola
published_at publishedAt TIMESTAMPTZ(6) NULL Stempel waktu publikasi pertama
search_text searchText TEXT NOT NULL, DEFAULT '' Gabungan lowercase judul, deskripsi, pengembang, tags, dan techStack
created_at createdAt TIMESTAMPTZ(6) NOT NULL, DEFAULT now() Waktu pembuatan
updated_at updatedAt TIMESTAMPTZ(6) NOT NULL, @updatedAt Waktu pembaruan terakhir
deleted_at deletedAt TIMESTAMPTZ(6) NULL Stempel soft-delete; NULL berarti aktif

4.5 repository_screenshots (RepositoryScreenshot)

Section titled “4.5 repository_screenshots (RepositoryScreenshot)”
Kolom SQL Field Prisma Tipe & Panjang Constraint & Default Keterangan
id id UUID PK, DEFAULT uuid() Identitas unik tangkapan layar
repository_id repositoryId UUID NOT NULL, FK -> repositories(id) Induk repositori (ON DELETE CASCADE)
image_url imageUrl TEXT NOT NULL URL publik gambar WebP
storage_key storageKey TEXT NOT NULL Kunci penyimpanan objek
alt_text altText VARCHAR(255) NULL Teks alternatif aksesibilitas
sort_order sortOrder INTEGER NOT NULL Urutan tampilan 0-based (maksimal 10 per karya)
created_at createdAt TIMESTAMPTZ(6) NOT NULL, DEFAULT now() Waktu pembuatan
Kolom SQL Field Prisma Tipe & Panjang Constraint & Default Keterangan
id id UUID PK, DEFAULT uuid() Identitas unik catatan unggahan
storage_key storageKey TEXT NOT NULL, UNIQUE Kunci unik repositories/{yyyy}/{mm}/{kind}-{uuid}.webp
public_url publicUrl TEXT NOT NULL URL S3 publik atau fallback /uploads/{storageKey}
kind kind media_kind NOT NULL Jenis aset (COVER, BANNER, SCREENSHOT)
mime_type mimeType VARCHAR(50) NOT NULL Selalu "image/webp" setelah konversi sharp
size_bytes sizeBytes INTEGER NOT NULL Ukuran berkas hasil WebP dalam byte
width width INTEGER NOT NULL Lebar piksel akhir gambar
height height INTEGER NOT NULL Tinggi piksel akhir gambar
uploaded_by_id uploadedById UUID NOT NULL, FK -> admins(id) Admin pengunggah untuk pemeriksaan kepemilikan
repository_id repositoryId UUID NULL, FK -> repositories(id) NULL jika belum ditautkan ke karya (pending/orphan)
attached_at attachedAt TIMESTAMPTZ(6) NULL Waktu berkas ditautkan pada penyimpanan repositori
created_at createdAt TIMESTAMPTZ(6) NOT NULL, DEFAULT now() Waktu unggah awal
Kolom SQL Field Prisma Tipe & Panjang Constraint & Default Keterangan
key key VARCHAR(255) PK Format kunci login:{normalizedUsername}
attempts attempts INTEGER NOT NULL, DEFAULT 0 Jumlah kegagalan dalam jendela waktu aktif
window_started_at windowStartedAt TIMESTAMPTZ(6) NOT NULL Awal jendela rate limit
locked_until lockedUntil TIMESTAMPTZ(6) NULL Waktu berakhirnya penguncian (15 menit setelah 5 gagal)
updated_at updatedAt TIMESTAMPTZ(6) NOT NULL, @updatedAt Waktu pembaruan terakhir (dipakai cleanup > 24 jam)
Kolom SQL Field Prisma Tipe & Panjang Constraint & Default Keterangan
id id UUID PK, DEFAULT gen_random_uuid() Identitas unik tautan pendek
title title VARCHAR(160) NOT NULL Label deskriptif tautan (validasi form 2–120 karakter)
alias alias VARCHAR(64) NOT NULL, UNIQUE Alias path setelah go.p3mdi.id/ (lowercase, non-reserved)
original_url originalUrl TEXT NOT NULL URL tujuan lengkap (http:// atau https://)
is_active isActive BOOLEAN NOT NULL, DEFAULT true Menentukan apakah tautan melakukan pengalihan aktif
click_count clickCount INTEGER NOT NULL, DEFAULT 0 Jumlah klik kumulatif yang bertambah saat /s/[alias] diakses
created_by_id createdById UUID NULL, FK -> admins(id) Admin pembuat tautan
created_at createdAt TIMESTAMPTZ(6) NOT NULL, DEFAULT now() Waktu pembuatan
updated_at updatedAt TIMESTAMPTZ(6) NOT NULL, @updatedAt Waktu pembaruan

4.9 repository_submissions (RepositorySubmission)

Section titled “4.9 repository_submissions (RepositorySubmission)”
Kolom SQL Field Prisma Tipe & Panjang Constraint & Default Keterangan
id id UUID PK, DEFAULT gen_random_uuid() Identitas unik pengajuan
submitter_name submitterName VARCHAR(120) NOT NULL Nama pengaju atau tim (menjadi developerName saat disetujui)
title title VARCHAR(160) NOT NULL Judul karya yang diajukan
short_description shortDescription VARCHAR(500) NOT NULL Deskripsi karya (10–500 karakter)
repository_url repositoryUrl TEXT NOT NULL URL aplikasi/tautan utama (productUrl saat disetujui)
category_id categoryId UUID NOT NULL, FK -> categories(id) Kategori pilihan pengaju
cover_url coverUrl TEXT NULL URL sampul opsional yang dikirim pengaju
cover_storage_key coverStorageKey TEXT NULL Cadangan storage key sampul (opsional)
deadline deadline TIMESTAMPTZ(6) NULL Batas waktu opsional untuk formulir/survei
tech_stack techStack TEXT[] NOT NULL, DEFAULT '{}' Daftar teknologi yang digunakan
status status submission_status NOT NULL, DEFAULT 'PENDING' Status peninjauan (PENDING, APPROVED, REJECTED)
reviewed_by_id reviewedById UUID NULL, FK -> admins(id) Admin peninjau
reviewed_at reviewedAt TIMESTAMPTZ(6) NULL Waktu peninjauan selesai
rejection_reason rejectionReason TEXT NULL Alasan penolakan opsional untuk arsip
approved_repository_id approvedRepositoryId UUID NULL, FK -> repositories(id) Referensi ke Repository yang otomatis dibuat saat disetujui
created_at createdAt TIMESTAMPTZ(6) NOT NULL, DEFAULT now() Waktu pengajuan masuk
updated_at updatedAt TIMESTAMPTZ(6) NOT NULL, @updatedAt Waktu pembaruan

(Catatan sinkronisasi kode: publicSubmissionSchema juga menerima field input opsional submitterEmail dan sourceCodeUrl untuk kompatibilitas UI /ajukan, namun saat ini kolom yang dipersistensikan ke repository_submissions adalah kolom-kolom pada tabel 4.9 di atas.)

Kolom SQL Field Prisma Tipe & Panjang Constraint & Default Keterangan
key key VARCHAR(100) PK Kunci pengaturan sistem (saat ini: "homepage_display_limit")
value value TEXT NOT NULL Nilai string pengaturan (mis. "5" atau "0" untuk tanpa batas)
updated_at updatedAt TIMESTAMPTZ(6) NOT NULL, @updatedAt Waktu pembaruan terakhir

5. Indeks Standar & Indeks Kustom PostgreSQL

Section titled “5. Indeks Standar & Indeks Kustom PostgreSQL”

Selain indeks unik dan indeks B-tree yang dideklarasikan di prisma/schema.prisma, tiga berkas migrasi menyertakan indeks khusus PostgreSQL yang tidak seluruhnya dapat direpresentasikan oleh sintaks skema Prisma standar:

Tabel Nama Indeks Jenis Indeks Definisi / Fungsi
categories categories_name_lower_key Unique Functional ON categories (lower(name)) — mencegah duplikasi kategori beda kapitalisasi ("Web Application" vs "web application").
repositories repositories_public_published_idx Partial B-tree ON repositories (published_at DESC) WHERE status = 'PUBLISHED' AND deleted_at IS NULL — mengakselerasi beranda & katalog terbaru.
repositories repositories_public_category_idx Partial B-tree ON repositories (category_id, published_at DESC) WHERE status = 'PUBLISHED' AND deleted_at IS NULL — mengakselerasi filter kategori dan karya terkait.
repositories repositories_search_text_trgm_idx GIN (pg_trgm) ON repositories USING gin (search_text gin_trgm_ops) — mengakselerasi pencarian ILIKE '%q%' di /repositories.
repositories repositories_deadline_idx B-tree ON repositories (deadline) — mendukung pemeriksaan batas waktu formulir.
media_uploads media_uploads_orphan_idx Partial B-tree ON media_uploads (created_at) WHERE repository_id IS NULL — mengakselerasi skrip media-cleanup.ts.
admins admins_username_idx, admins_role_idx, admins_is_active_idx B-tree Pencarian login, daftar admin, dan penghitung Super Admin aktif.
audit_logs audit_logs_actor_id_idx, _action_idx, _entity_type_idx, _entity_id_idx, _created_at_idx B-tree Filter & paginasi halaman /admin/audit-logs dan /admin/my-activity.
short_links short_links_alias_idx, short_links_is_active_idx B-tree Resolusi rute /s/[alias].
repository_submissions repository_submissions_status_idx, _category_id_idx, _created_at_idx B-tree Antrean tinjauan di /admin/submissions.

[!WARNING] Saat membuat migrasi baru menggunakan prisma migrate dev, selalu periksa berkas SQL yang dihasilkan agar Prisma tidak mengusulkan penghapusan indeks kustom (categories_name_lower_key, indeks parsial, atau indeks GIN pg_trgm).


stateDiagram-v2
    [*] --> DRAFT: "createRepository(DRAFT) / Simpan draf"
    [*] --> PUBLISHED: "createRepository(PUBLISHED) / approveSubmission"
    DRAFT --> PUBLISHED: "togglePublicationStatus / updateRepository (set published_at jika null)"
    PUBLISHED --> DRAFT: "togglePublicationStatus / updateRepository (published_at dipertahankan)"
    DRAFT --> SoftDeleted: "deleteRepository (deleted_at = now, is_featured = false)"
    PUBLISHED --> SoftDeleted: "deleteRepository (deleted_at = now, is_featured = false)"
    SoftDeleted --> [*]: "repositories-purge.ts --apply (> REPOSITORY_RETENTION_DAYS)"
stateDiagram-v2
    [*] --> PENDING: "submitPublicRepository (/ajukan)"
    PENDING --> APPROVED: "approveSubmission ($transaction buat Repository PUBLISHED)"
    PENDING --> REJECTED: "rejectSubmission (simpan rejection_reason opsional)"
    APPROVED --> [*]
    REJECTED --> [*]

6.3 Siklus Hidup MediaUpload & Pemeliharaan

Section titled “6.3 Siklus Hidup MediaUpload & Pemeliharaan”
stateDiagram-v2
    [*] --> Pending: "POST /api/uploads (repository_id = NULL)"
    Pending --> Attached: "createRepository / updateRepository (set repository_id & attached_at)"
    Pending --> RemovedByUploader: "removeUploadAction (cek uploadedById === adminId)"
    Pending --> Orphaned: "Belum ditautkan > 24 jam"
    Orphaned --> Purged: "scripts/media-cleanup.ts --apply"
    Attached --> RetainedOnSoftDelete: "deleteRepository (soft delete)"
    RetainedOnSoftDelete --> Purged: "scripts/repositories-purge.ts --apply"
    RemovedByUploader --> [*]
    Purged --> [*]

  1. Pembersihan Media Yatim & Rate Limit (scripts/media-cleanup.ts):
    • Mode bawaan adalah dry-run; tambahkan --apply untuk eksekusi permanen.
    • Menghapus record media_uploads dengan repository_id IS NULL dan created_at < now - 24 jam, beserta berkas terkait di S3 maupun public/uploads/.
    • Menghapus record auth_rate_limits dengan updated_at < now - 24 jam.
  2. Pembersihan Permanen Repositori Terhapus (scripts/repositories-purge.ts):
    • Menggunakan variabel lingkungan REPOSITORY_RETENTION_DAYS (default 30 hari).
    • Pada mode --apply, menghapus seluruh objek S3/lokal terkait (coverStorageKey, bannerStorageKey, screenshots, uploads) lalu mengeksekusi prisma.repository.delete (menghapus baris repository_screenshots secara cascade).

Eksekusi npm run db:seed (tsx prisma/seed.ts) bersifat idempoten dan menjalankan tiga tahapan:

  1. Kategori Dasar (seedCategories): Melakukan upsert berdasarkan slug untuk 6 kategori awal: Web Application (web-application), Mobile Application (mobile-application), Software & Tools (software-tools), Information System (information-system), Multimedia (multimedia), dan Others (others). (Kategori ke-7 Formulir dengan slug formulir otomatis ditambahkan oleh migrasi SQL 20261115120000_add_submissions_shortlinks_deadline.)
  2. Provisioning Super Admin (seedAdmin): Membaca ADMIN_SEED_EMAIL, ADMIN_SEED_PASSWORD (wajib minimal 12 karakter), ADMIN_SEED_NAME, dan ADMIN_SEED_USERNAME (default superadmin). Bila admin sudah ada, kata sandi tidak ditimpa kecuali ADMIN_SEED_RESET_PASSWORD="true" (yang juga menaikkan sessionVersion).
  3. Karya Demo Non-Produksi (seedDemoRepositories): Hanya berjalan apabila NODE_ENV !== "production" dan SEED_DEMO_DATA === "true". Menghasilkan 3 aset WebP via sharp ke public/images/seed/ dan meng-upsert 3 karya demo (sistem-informasi-akademik-terpadu-demo, mobile-campus-companion-demo, code-quality-scanner-cli-demo).