| 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). |
- Konvensi penamaan ganda: Nama tabel dan kolom menggunakan
snake_case di PostgreSQL (@map / @@map) dan camelCase pada model TypeScript Prisma.
- 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.
- 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).
- 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).
- 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
}
| 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 |
| 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 |
| 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 |
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 --> [*]
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 --> [*]
- 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.
- 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:
- 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.)
- 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).
- 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).