-- ===================================================================== -- SIMSURAT - SQL INSTALASI AWAL (untuk hosting tanpa SSH) -- Impor ke database KOSONG lewat phpMyAdmin > Import. File ini MENGHAPUS -- tabel bernama sama bila sudah ada. Jangan impor ke database yang berisi data. -- Login awal: admin / GantiSegera#2026 (wajib diganti saat login pertama). -- Trigger append-only (opsional) ada di simsurat_instalasi_trigger.sql. -- Dibangun dari migration: 24 file, 44 tabel berisi data. -- ===================================================================== -- SIMSURAT backup database -- Dibuat: 2026-10-09 15:10:18 -- Impor: phpMyAdmin (tab Import) atau: mysql -u USER -p NAMA_DB < database.sql SET NAMES utf8mb4; SET time_zone = '+00:00'; SET FOREIGN_KEY_CHECKS = 0; SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO'; -- -------------------------------------------------------- -- Tabel `migrations` DROP TABLE IF EXISTS `migrations`; CREATE TABLE `migrations` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `migration` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `batch` int NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('1', '2026_10_02_000001_create_tabel_sistem_framework', '1'), ('2', '2026_10_02_000002_create_tabel_referensi', '1'), ('3', '2026_10_02_000003_create_tabel_pengguna_dan_hak_akses', '1'), ('4', '2026_10_02_000004_create_tabel_surat_masuk', '1'), ('5', '2026_10_02_000005_create_tabel_disposisi', '1'), ('6', '2026_10_02_000006_create_tabel_surat_keluar', '1'), ('7', '2026_10_02_000007_create_tabel_riwayat_audit_notifikasi', '1'), ('8', '2026_10_02_000008_create_trigger_append_only', '1'), ('9', '2026_10_03_000001_koreksi1_referensi_naskah', '1'), ('10', '2026_10_03_000002_koreksi2_aksi_instruksi_per_penerima', '1'), ('11', '2026_10_03_000003_fase7_keamanan_akun', '1'), ('12', '2026_10_03_000004_tampilan_menu_dan_dashboard', '1'), ('13', '2026_10_03_000005_menu_manajemen_role', '1'), ('14', '2026_10_03_000006_naskah_keluar_internal', '1'), ('15', '2026_10_03_000007_agenda_unit_naskah_internal', '1'), ('16', '2026_10_04_000001_hapus_role_auditor', '1'), ('17', '2026_10_04_000002_naskah_keluar_unit', '1'), ('18', '2026_10_04_000003_pengaturan_suara_naskah_masuk', '1'), ('19', '2026_10_05_000001_istilah_unit_kerja', '1'), ('20', '2026_10_06_000001_sekretaris_kirim_naskah_keluar', '1'), ('21', '2026_10_06_000002_bidang_naskah_opsional', '1'), ('22', '2026_10_09_000001_operator_unit_kirim_ke_yayasan', '1'), ('23', '2026_10_10_000001_role_per_unit_kerja', '1'), ('24', '2026_10_10_000002_hapus_jenis_tindak_lanjut', '1'); -- -------------------------------------------------------- -- Tabel `tb_ref_aksi_disposisi` DROP TABLE IF EXISTS `tb_ref_aksi_disposisi`; CREATE TABLE `tb_ref_aksi_disposisi` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `kode_aksi` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL, `nama_aksi` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `urutan` smallint unsigned NOT NULL DEFAULT '0', `aktif` tinyint(1) NOT NULL DEFAULT '1', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `tb_ref_aksi_disposisi_kode_aksi_unique` (`kode_aksi`) ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `tb_ref_aksi_disposisi` (`id`, `kode_aksi`, `nama_aksi`, `urutan`, `aktif`, `created_at`, `updated_at`) VALUES ('1', 'ACC', 'ACC', '1', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('2', 'DISKUSIKAN', 'Diskusikan', '2', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('3', 'SEBAGAI_BAHAN', 'Sebagai Bahan', '3', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('4', 'TINDAK_LANJUTI', 'Tindak Lanjuti', '4', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('5', 'MONITOR', 'Monitor', '5', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('6', 'HADIR', 'Hadir', '6', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('7', 'LAPORKAN', 'Laporkan', '7', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('8', 'SESUAI_DISPOSISI', 'Sesuai Disposisi', '8', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('9', 'WAKILI', 'Wakili', '9', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('10', 'SIAPKAN', 'Siapkan', '10', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('11', 'TANGGAPAN_DAN_SARAN', 'Tanggapan Dan Saran', '11', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('12', 'ARSIP', 'Arsip', '12', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('13', 'KOORDINASIKAN', 'Koordinasikan', '13', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('14', 'UNTUK_DIKETAHUI', 'Untuk Diketahui', '14', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('15', 'SIMPAN_DI_MEJA', 'Simpan Di Meja', '15', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('16', 'AGENDAKAN', 'Agendakan', '16', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('17', 'UNTUK_MENJADI_PERHATIAN', 'Untuk Menjadi Perhatian', '17', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('18', 'SESUAI_KETENTUAN_YANG_BERLAKU', 'Sesuai Ketentuan Yang Berlaku', '18', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'); -- -------------------------------------------------------- -- Tabel `tb_ref_bidang_unit` DROP TABLE IF EXISTS `tb_ref_bidang_unit`; CREATE TABLE `tb_ref_bidang_unit` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `kode_bidang` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL, `nama_bidang` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL, `urutan` smallint unsigned NOT NULL DEFAULT '0', `keterangan` text COLLATE utf8mb4_unicode_ci, `aktif` tinyint(1) NOT NULL DEFAULT '1', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `tb_ref_bidang_unit_kode_bidang_unique` (`kode_bidang`) ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `tb_ref_bidang_unit` (`id`, `kode_bidang`, `nama_bidang`, `urutan`, `keterangan`, `aktif`, `created_at`, `updated_at`) VALUES ('1', 'Set', 'Sekretariat', '1', NULL, '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('2', 'Bend', 'Bendahara', '2', NULL, '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('3', 'Dik', 'Bidang Pendidikan', '3', NULL, '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('4', 'Sos', 'Bidang Sosial', '4', NULL, '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('5', 'Ush', 'Bidang Usaha dan Jasa', '5', NULL, '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('6', 'Hum', 'Bidang Humas', '6', NULL, '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('7', 'Um', 'Umum', '7', NULL, '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'); -- -------------------------------------------------------- -- Tabel `tb_ref_derajat_surat` DROP TABLE IF EXISTS `tb_ref_derajat_surat`; CREATE TABLE `tb_ref_derajat_surat` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `kode_derajat` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL, `nama_derajat` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `keterangan` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `warna` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'secondary', `aktif` tinyint(1) NOT NULL DEFAULT '1', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `tb_ref_derajat_surat_kode_derajat_unique` (`kode_derajat`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `tb_ref_derajat_surat` (`id`, `kode_derajat`, `nama_derajat`, `keterangan`, `warna`, `aktif`, `created_at`, `updated_at`) VALUES ('1', 'B', 'Biasa', 'Tindakan bersifat biasa', 'secondary', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('2', 'S', 'Segera', 'Memerlukan tindakan segera', 'danger', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'); -- -------------------------------------------------------- -- Tabel `tb_ref_format_nomor` DROP TABLE IF EXISTS `tb_ref_format_nomor`; CREATE TABLE `tb_ref_format_nomor` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `kode_format` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL, `nama_format` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL, `format_nomor` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL, `panjang_nomor` tinyint unsigned NOT NULL DEFAULT '3' COMMENT 'Jumlah digit dengan nol di depan', `reset_periode` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'TAHUNAN' COMMENT 'TAHUNAN | BULANAN | TIDAK', `nomor_terakhir` int unsigned NOT NULL DEFAULT '0', `periode_terakhir` varchar(7) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'YYYY atau YYYY-MM', `aktif` tinyint(1) NOT NULL DEFAULT '1', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `tb_ref_format_nomor_kode_format_unique` (`kode_format`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `tb_ref_format_nomor` (`id`, `kode_format`, `nama_format`, `format_nomor`, `panjang_nomor`, `reset_periode`, `nomor_terakhir`, `periode_terakhir`, `aktif`, `created_at`, `updated_at`) VALUES ('1', 'NOMOR_DISPOSISI', 'Nomor Disposisi', 'DSP/{NOMOR}/{TAHUN}', '4', 'TAHUNAN', '0', NULL, '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'); -- -------------------------------------------------------- -- Tabel `tb_ref_jabatan` DROP TABLE IF EXISTS `tb_ref_jabatan`; CREATE TABLE `tb_ref_jabatan` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `kode_jabatan` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL, `nama_jabatan` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `level` tinyint unsigned NOT NULL DEFAULT '9' COMMENT '1 = pimpinan tertinggi', `keterangan` text COLLATE utf8mb4_unicode_ci, `aktif` tinyint(1) NOT NULL DEFAULT '1', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `tb_ref_jabatan_kode_jabatan_unique` (`kode_jabatan`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `tb_ref_jabatan` (`id`, `kode_jabatan`, `nama_jabatan`, `level`, `keterangan`, `aktif`, `created_at`, `updated_at`) VALUES ('1', 'KETUA', 'Ketua Yayasan / Ketua Cabang', '1', NULL, '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('2', 'SEKRETARIS', 'Sekretaris', '2', NULL, '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('3', 'BENDAHARA', 'Bendahara', '2', NULL, '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('4', 'KABID', 'Kepala Bidang', '2', NULL, '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('5', 'KEPSEK', 'Kepala Sekolah', '3', NULL, '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('6', 'SEKRETARIS_SEKOLAH', 'Sekretaris / Tata Usaha Sekolah', '4', NULL, '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('7', 'BENDAHARA_SEKOLAH', 'Bendahara Sekolah', '4', NULL, '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('8', 'STAF', 'Staf', '5', NULL, '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('9', 'AUDITOR', 'Auditor / Pengawas', '9', NULL, '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'); -- -------------------------------------------------------- -- Tabel `tb_ref_jenis_surat` DROP TABLE IF EXISTS `tb_ref_jenis_surat`; CREATE TABLE `tb_ref_jenis_surat` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `kode_jenis` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL, `nama_jenis` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `aktif` tinyint(1) NOT NULL DEFAULT '1', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `tb_ref_jenis_surat_kode_jenis_unique` (`kode_jenis`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `tb_ref_jenis_surat` (`id`, `kode_jenis`, `nama_jenis`, `aktif`, `created_at`, `updated_at`) VALUES ('1', 'SURAT_MASUK', 'Naskah Masuk', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('2', 'SURAT_KELUAR', 'Naskah Keluar', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'); -- -------------------------------------------------------- -- Tabel `tb_ref_jenis_unit` DROP TABLE IF EXISTS `tb_ref_jenis_unit`; CREATE TABLE `tb_ref_jenis_unit` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `kode_jenis_unit` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL, `nama_jenis_unit` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `unit_kerja` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 = unit kerja (pimpinan, sekretaris/TU, buku agenda sendiri); 0 = sub unit kerja', `aktif` tinyint(1) NOT NULL DEFAULT '1', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `tb_ref_jenis_unit_kode_jenis_unit_unique` (`kode_jenis_unit`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `tb_ref_jenis_unit` (`id`, `kode_jenis_unit`, `nama_jenis_unit`, `unit_kerja`, `aktif`, `created_at`, `updated_at`) VALUES ('1', 'YAYASAN', 'Yayasan', '1', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('2', 'SEKRETARIAT', 'Sekretariat', '0', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('3', 'BIDANG', 'Bidang', '0', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('4', 'SEKOLAH', 'Sekolah', '1', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('5', 'PERGURUAN_TINGGI', 'Perguruan Tinggi', '1', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('6', 'UNIT_PENDUKUNG', 'Unit Pendukung', '0', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'); -- -------------------------------------------------------- -- Tabel `tb_ref_kategori_surat` DROP TABLE IF EXISTS `tb_ref_kategori_surat`; CREATE TABLE `tb_ref_kategori_surat` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `kode_kategori` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL, `nama_kategori` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL, `urutan` smallint unsigned NOT NULL DEFAULT '0', `aktif` tinyint(1) NOT NULL DEFAULT '1', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `tb_ref_kategori_surat_kode_kategori_unique` (`kode_kategori`) ) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `tb_ref_kategori_surat` (`id`, `kode_kategori`, `nama_kategori`, `urutan`, `aktif`, `created_at`, `updated_at`) VALUES ('1', 'PERATURAN', 'Peraturan', '1', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('2', 'PERATURAN_BERSAMA', 'Peraturan Bersama', '2', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('3', 'PERINTAH_HARIAN', 'Perintah Harian', '3', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('4', 'INSTRUKSI', 'Instruksi', '4', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('5', 'KEPUTUSAN', 'Keputusan', '5', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('6', 'SURAT_EDARAN', 'Surat Edaran', '6', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('7', 'SURAT_PERINTAH', 'Surat Perintah/Surat Tugas', '7', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('8', 'SURAT', 'Surat', '8', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('9', 'NOTA_DINAS', 'Nota Dinas', '9', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('10', 'TELEGRAM', 'Telegram', '10', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('11', 'SURAT_TELEGRAM', 'Surat Telegram', '11', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('12', 'LAPORAN', 'Laporan', '12', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('13', 'PENGUMUMAN', 'Pengumuman', '13', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('14', 'SURAT_PENGANTAR', 'Surat Pengantar', '14', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('15', 'SURAT_IZIN', 'Surat Izin', '15', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('16', 'SURAT_IZIN_JALAN', 'Surat Izin Jalan', '16', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('17', 'SURAT_JALAN', 'Surat Jalan', '17', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('18', 'SURAT_KETERANGAN', 'Surat Keterangan', '18', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('19', 'SAMBUTAN', 'Sambutan', '19', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('20', 'BERITA_ACARA', 'Berita Acara', '20', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('21', 'SERTIFIKAT', 'Sertifikat', '21', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('22', 'IJAZAH', 'Ijazah/Surat Tanda lulus Khusus', '22', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('23', 'PIAGAM_PENGHARGAAN', 'Piagam Penghargaan', '23', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('24', 'TELAAHAN_STAF', 'Telahaan Staf', '24', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('25', 'PERJANJIAN_KERJA_SAMA', 'Perjanjian Kerja sama', '25', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('26', 'RALAT', 'Ralat, Perubahan, Pencabutan dan Pembatalan', '26', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('27', 'UNDANGAN', 'Undangan', '27', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'); -- -------------------------------------------------------- -- Tabel `tb_ref_klasifikasi_surat` DROP TABLE IF EXISTS `tb_ref_klasifikasi_surat`; CREATE TABLE `tb_ref_klasifikasi_surat` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `kode_klasifikasi` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL, `nama_klasifikasi` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `keterangan` text COLLATE utf8mb4_unicode_ci, `warna` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'secondary', `aktif` tinyint(1) NOT NULL DEFAULT '1', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uq_klasifikasi_naskah_kode` (`kode_klasifikasi`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `tb_ref_klasifikasi_surat` (`id`, `kode_klasifikasi`, `nama_klasifikasi`, `keterangan`, `warna`, `aktif`, `created_at`, `updated_at`) VALUES ('1', 'B', 'Biasa', NULL, 'secondary', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'), ('2', 'R', 'Rahasia', NULL, 'dark', '1', '2026-10-09 08:10:12', '2026-10-09 08:10:18'); -- -------------------------------------------------------- -- Tabel `tb_ref_status_disposisi` DROP TABLE IF EXISTS `tb_ref_status_disposisi`; CREATE TABLE `tb_ref_status_disposisi` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `kode_status` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL, `nama_status` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `warna` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'secondary', `urutan` smallint unsigned NOT NULL DEFAULT '0', `aktif` tinyint(1) NOT NULL DEFAULT '1', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `tb_ref_status_disposisi_kode_status_unique` (`kode_status`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `tb_ref_status_disposisi` (`id`, `kode_status`, `nama_status`, `warna`, `urutan`, `aktif`, `created_at`, `updated_at`) VALUES ('1', 'MENUNGGU_DITERIMA', 'Menunggu Diterima', 'secondary', '1', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('2', 'DITERIMA', 'Diterima', 'info', '2', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('3', 'DIBACA', 'Dibaca', 'primary', '3', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('4', 'DALAM_PROSES', 'Dalam Proses', 'warning', '4', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('5', 'MENUNGGU_VERIFIKASI', 'Menunggu Verifikasi', 'warning', '5', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('6', 'SELESAI', 'Selesai', 'success', '6', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('7', 'DITOLAK', 'Ditolak', 'danger', '7', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('8', 'DIBATALKAN', 'Dibatalkan', 'dark', '8', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'); -- -------------------------------------------------------- -- Tabel `tb_ref_status_distribusi` DROP TABLE IF EXISTS `tb_ref_status_distribusi`; CREATE TABLE `tb_ref_status_distribusi` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `kode_status` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL, `nama_status` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `warna` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'secondary', `urutan` smallint unsigned NOT NULL DEFAULT '0', `aktif` tinyint(1) NOT NULL DEFAULT '1', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `tb_ref_status_distribusi_kode_status_unique` (`kode_status`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `tb_ref_status_distribusi` (`id`, `kode_status`, `nama_status`, `warna`, `urutan`, `aktif`, `created_at`, `updated_at`) VALUES ('1', 'TERKIRIM', 'Terkirim', 'secondary', '1', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('2', 'DIBACA', 'Dibaca', 'info', '2', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('3', 'DIKONFIRMASI', 'Penerimaan Dikonfirmasi', 'success', '3', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('4', 'DIBATALKAN', 'Dibatalkan', 'dark', '4', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'); -- -------------------------------------------------------- -- Tabel `tb_ref_status_surat` DROP TABLE IF EXISTS `tb_ref_status_surat`; CREATE TABLE `tb_ref_status_surat` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `kode_status` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL, `nama_status` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `jenis_transaksi` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'SURAT_MASUK | SURAT_KELUAR', `warna` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'secondary' COMMENT 'Kelas warna badge Bootstrap', `urutan` smallint unsigned NOT NULL DEFAULT '0', `aktif` tinyint(1) NOT NULL DEFAULT '1', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `tb_ref_status_surat_jenis_transaksi_kode_status_unique` (`jenis_transaksi`,`kode_status`) ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `tb_ref_status_surat` (`id`, `kode_status`, `nama_status`, `jenis_transaksi`, `warna`, `urutan`, `aktif`, `created_at`, `updated_at`) VALUES ('1', 'DRAFT', 'Draft', 'SURAT_MASUK', 'secondary', '1', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('2', 'DIAGENDAKAN', 'Diagendakan', 'SURAT_MASUK', 'info', '2', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('3', 'MENUNGGU_KETUA', 'Menunggu Pimpinan', 'SURAT_MASUK', 'warning', '3', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('4', 'DIBACA_KETUA', 'Dibaca Pimpinan', 'SURAT_MASUK', 'primary', '4', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('5', 'DIDISPOSISIKAN', 'Didisposisikan', 'SURAT_MASUK', 'primary', '5', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('6', 'DIDISTRIBUSIKAN', 'Didistribusikan', 'SURAT_MASUK', 'primary', '6', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('7', 'DITERIMA_JAJARAN', 'Diterima Jajaran', 'SURAT_MASUK', 'info', '7', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('8', 'DALAM_TINDAK_LANJUT', 'Dalam Tindak Lanjut', 'SURAT_MASUK', 'warning', '8', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('9', 'MENUNGGU_VERIFIKASI', 'Menunggu Verifikasi', 'SURAT_MASUK', 'warning', '9', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('10', 'SELESAI', 'Selesai', 'SURAT_MASUK', 'success', '10', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('11', 'DIARSIPKAN', 'Diarsipkan', 'SURAT_MASUK', 'dark', '11', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('12', 'DIBATALKAN', 'Dibatalkan', 'SURAT_MASUK', 'danger', '12', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('13', 'DRAFT', 'Draft', 'SURAT_KELUAR', 'secondary', '1', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('14', 'DIAGENDAKAN', 'Diagendakan', 'SURAT_KELUAR', 'info', '2', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('15', 'MENUNGGU_KETUA', 'Menunggu Pimpinan Unit Kerja', 'SURAT_KELUAR', 'warning', '3', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('16', 'DIDISTRIBUSIKAN', 'Didistribusikan', 'SURAT_KELUAR', 'primary', '4', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('17', 'SELESAI', 'Selesai', 'SURAT_KELUAR', 'success', '5', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('18', 'DIARSIPKAN', 'Diarsipkan', 'SURAT_KELUAR', 'dark', '6', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('19', 'DIBATALKAN', 'Dibatalkan', 'SURAT_KELUAR', 'danger', '7', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'); -- -------------------------------------------------------- -- Tabel `tb_ref_status_tindak_lanjut` DROP TABLE IF EXISTS `tb_ref_status_tindak_lanjut`; CREATE TABLE `tb_ref_status_tindak_lanjut` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `kode_status` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL, `nama_status` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `warna` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'secondary', `urutan` smallint unsigned NOT NULL DEFAULT '0', `aktif` tinyint(1) NOT NULL DEFAULT '1', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `tb_ref_status_tindak_lanjut_kode_status_unique` (`kode_status`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `tb_ref_status_tindak_lanjut` (`id`, `kode_status`, `nama_status`, `warna`, `urutan`, `aktif`, `created_at`, `updated_at`) VALUES ('1', 'PROGRES', 'Catatan Progres', 'info', '1', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('2', 'DIAJUKAN', 'Diajukan untuk Verifikasi', 'warning', '2', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('3', 'DISETUJUI', 'Disetujui', 'success', '3', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('4', 'DIKEMBALIKAN', 'Dikembalikan untuk Perbaikan', 'danger', '4', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'); -- -------------------------------------------------------- -- Tabel `tb_ref_unit` DROP TABLE IF EXISTS `tb_ref_unit`; CREATE TABLE `tb_ref_unit` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `kode_unit` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL, `nama_unit` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL, `id_jenis_unit` bigint unsigned NOT NULL, `id_unit_induk` bigint unsigned DEFAULT NULL, `keterangan` text COLLATE utf8mb4_unicode_ci, `aktif` tinyint(1) NOT NULL DEFAULT '1', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `tb_ref_unit_kode_unit_unique` (`kode_unit`), KEY `tb_ref_unit_id_jenis_unit_foreign` (`id_jenis_unit`), KEY `tb_ref_unit_id_unit_induk_foreign` (`id_unit_induk`), KEY `tb_ref_unit_aktif_nama_unit_index` (`aktif`,`nama_unit`), CONSTRAINT `tb_ref_unit_id_jenis_unit_foreign` FOREIGN KEY (`id_jenis_unit`) REFERENCES `tb_ref_jenis_unit` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_ref_unit_id_unit_induk_foreign` FOREIGN KEY (`id_unit_induk`) REFERENCES `tb_ref_unit` (`id`) ON DELETE RESTRICT ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `tb_ref_unit` (`id`, `kode_unit`, `nama_unit`, `id_jenis_unit`, `id_unit_induk`, `keterangan`, `aktif`, `created_at`, `updated_at`) VALUES ('1', 'YYS', 'Yayasan', '1', NULL, NULL, '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('2', 'SEKR', 'Sekretariat Yayasan', '2', '1', NULL, '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('3', 'BEND', 'Bendahara Yayasan', '2', '1', NULL, '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('4', 'BID-PEND', 'Bidang Pendidikan', '3', '1', NULL, '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('5', 'BID-SARPRAS', 'Bidang Sarana Prasarana', '3', '1', NULL, '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'); -- -------------------------------------------------------- -- Tabel `tb_sys_cache` DROP TABLE IF EXISTS `tb_sys_cache`; CREATE TABLE `tb_sys_cache` ( `key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `value` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL, `expiration` bigint NOT NULL, PRIMARY KEY (`key`), KEY `tb_sys_cache_expiration_index` (`expiration`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Tabel `tb_sys_cache_lock` DROP TABLE IF EXISTS `tb_sys_cache_lock`; CREATE TABLE `tb_sys_cache_lock` ( `key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `owner` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `expiration` bigint NOT NULL, PRIMARY KEY (`key`), KEY `tb_sys_cache_lock_expiration_index` (`expiration`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Tabel `tb_sys_failed_jobs` DROP TABLE IF EXISTS `tb_sys_failed_jobs`; CREATE TABLE `tb_sys_failed_jobs` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `uuid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `connection` text COLLATE utf8mb4_unicode_ci NOT NULL, `queue` text COLLATE utf8mb4_unicode_ci NOT NULL, `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL, `exception` longtext COLLATE utf8mb4_unicode_ci NOT NULL, `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `tb_sys_failed_jobs_uuid_unique` (`uuid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Tabel `tb_sys_job_batches` DROP TABLE IF EXISTS `tb_sys_job_batches`; CREATE TABLE `tb_sys_job_batches` ( `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `total_jobs` int NOT NULL, `pending_jobs` int NOT NULL, `failed_jobs` int NOT NULL, `failed_job_ids` longtext COLLATE utf8mb4_unicode_ci NOT NULL, `options` mediumtext COLLATE utf8mb4_unicode_ci, `cancelled_at` int DEFAULT NULL, `created_at` int NOT NULL, `finished_at` int DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Tabel `tb_sys_jobs` DROP TABLE IF EXISTS `tb_sys_jobs`; CREATE TABLE `tb_sys_jobs` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `queue` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL, `attempts` tinyint unsigned NOT NULL, `reserved_at` int unsigned DEFAULT NULL, `available_at` int unsigned NOT NULL, `created_at` int unsigned NOT NULL, PRIMARY KEY (`id`), KEY `tb_sys_jobs_queue_index` (`queue`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Tabel `tb_sys_log_aktivitas` DROP TABLE IF EXISTS `tb_sys_log_aktivitas`; CREATE TABLE `tb_sys_log_aktivitas` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `id_user` bigint unsigned DEFAULT NULL, `aktivitas` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `modul` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `data_id` bigint unsigned DEFAULT NULL, `data_tambahan` json DEFAULT NULL COMMENT 'Nilai lama/baru atau konteks tambahan', `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `user_agent` text COLLATE utf8mb4_unicode_ci, `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `tb_sys_log_aktivitas_modul_data_id_index` (`modul`,`data_id`), KEY `tb_sys_log_aktivitas_id_user_created_at_index` (`id_user`,`created_at`), KEY `tb_sys_log_aktivitas_created_at_index` (`created_at`), CONSTRAINT `tb_sys_log_aktivitas_id_user_foreign` FOREIGN KEY (`id_user`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Tabel `tb_sys_menu` DROP TABLE IF EXISTS `tb_sys_menu`; CREATE TABLE `tb_sys_menu` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `id_induk` bigint unsigned DEFAULT NULL, `jenis` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'header | grup | menu', `judul` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `ikon` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `route` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `urutan` smallint unsigned NOT NULL DEFAULT '0', `aktif` tinyint(1) NOT NULL DEFAULT '1', `terkunci` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Item sistem yang tidak boleh dihapus/disembunyikan dari Super Admin', `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `tb_sys_menu_id_induk_urutan_index` (`id_induk`,`urutan`), CONSTRAINT `tb_sys_menu_id_induk_foreign` FOREIGN KEY (`id_induk`) REFERENCES `tb_sys_menu` (`id`) ON DELETE RESTRICT ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `tb_sys_menu` (`id`, `id_induk`, `jenis`, `judul`, `ikon`, `route`, `urutan`, `aktif`, `terkunci`, `created_at`, `updated_at`) VALUES ('1', NULL, 'menu', 'Dashboard', 'fas fa-gauge-high', 'dashboard', '10', '1', '0', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('2', NULL, 'header', 'PERSURATAN', NULL, NULL, '20', '1', '0', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('3', NULL, 'menu', 'Naskah Masuk', 'fas fa-inbox', 'surat-masuk.index', '30', '1', '0', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('4', NULL, 'menu', 'Disposisi', 'fas fa-share-nodes', 'disposisi.index', '40', '1', '0', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('5', NULL, 'menu', 'Tindak Lanjut', 'fas fa-list-check', 'tindak-lanjut.index', '50', '1', '0', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('6', NULL, 'menu', 'Naskah Keluar', 'fas fa-paper-plane', 'surat-keluar.index', '60', '1', '0', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('7', NULL, 'menu', 'Arsip Digital', 'fas fa-box-archive', 'arsip.index', '70', '1', '0', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('8', NULL, 'menu', 'Laporan', 'fas fa-chart-column', 'laporan.index', '80', '1', '0', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('9', NULL, 'header', 'ADMINISTRASI', NULL, NULL, '90', '1', '0', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('10', NULL, 'menu', 'Manajemen Pengguna', 'fas fa-users-gear', 'pengguna.index', '100', '1', '0', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('11', NULL, 'menu', 'Manajemen Role', 'fas fa-user-shield', 'role.index', '110', '1', '0', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('12', NULL, 'menu', 'Referensi', 'fas fa-database', 'referensi.index', '120', '1', '0', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('13', NULL, 'grup', 'Tampilan', 'fas fa-table-columns', NULL, '130', '1', '0', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('14', '13', 'menu', 'Manajemen Menu', 'fas fa-bars-staggered', 'menu.index', '10', '1', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('15', '13', 'menu', 'Dashboard per Role', 'fas fa-gauge', 'dashboard-role.index', '20', '1', '0', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('16', NULL, 'menu', 'Audit Log', 'fas fa-shield-halved', 'audit.index', '140', '1', '0', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('17', NULL, 'menu', 'Pengaturan', 'fas fa-sliders', 'pengaturan.index', '150', '1', '0', '2026-10-09 08:10:18', '2026-10-09 08:10:18'); -- -------------------------------------------------------- -- Tabel `tb_sys_menu_role` DROP TABLE IF EXISTS `tb_sys_menu_role`; CREATE TABLE `tb_sys_menu_role` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `id_menu` bigint unsigned NOT NULL, `id_role` bigint unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `tb_sys_menu_role_id_menu_id_role_unique` (`id_menu`,`id_role`), KEY `tb_sys_menu_role_id_role_foreign` (`id_role`), CONSTRAINT `tb_sys_menu_role_id_menu_foreign` FOREIGN KEY (`id_menu`) REFERENCES `tb_sys_menu` (`id`) ON DELETE CASCADE, CONSTRAINT `tb_sys_menu_role_id_role_foreign` FOREIGN KEY (`id_role`) REFERENCES `tb_sys_role` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `tb_sys_menu_role` (`id`, `id_menu`, `id_role`) VALUES ('1', '1', '1'), ('2', '1', '2'), ('3', '1', '3'), ('4', '1', '4'), ('5', '3', '1'), ('7', '3', '2'), ('8', '3', '3'), ('6', '3', '4'), ('9', '4', '1'), ('10', '4', '2'), ('11', '4', '3'), ('12', '4', '4'), ('13', '5', '1'), ('14', '5', '2'), ('15', '5', '3'), ('16', '5', '4'), ('17', '6', '1'), ('18', '6', '2'), ('19', '6', '3'), ('20', '7', '1'), ('21', '7', '2'), ('22', '7', '3'), ('23', '8', '1'), ('24', '8', '2'), ('25', '8', '3'), ('26', '10', '1'), ('27', '11', '1'), ('28', '12', '1'), ('29', '14', '1'), ('30', '15', '1'), ('31', '16', '1'), ('32', '17', '1'); -- -------------------------------------------------------- -- Tabel `tb_sys_notifikasi` DROP TABLE IF EXISTS `tb_sys_notifikasi`; CREATE TABLE `tb_sys_notifikasi` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `id_user` bigint unsigned NOT NULL, `judul` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL, `pesan` text COLLATE utf8mb4_unicode_ci NOT NULL, `jenis_notifikasi` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'SURAT_MASUK | DISPOSISI | DISTRIBUSI | TINDAK_LANJUT | PENGINGAT', `url_tujuan` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `dibaca` tinyint(1) NOT NULL DEFAULT '0', `tanggal_dibaca` datetime DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `tb_sys_notifikasi_id_user_dibaca_id_index` (`id_user`,`dibaca`,`id`), CONSTRAINT `tb_sys_notifikasi_id_user_foreign` FOREIGN KEY (`id_user`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Tabel `tb_sys_pengaturan` DROP TABLE IF EXISTS `tb_sys_pengaturan`; CREATE TABLE `tb_sys_pengaturan` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `kunci` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL, `nilai` text COLLATE utf8mb4_unicode_ci, `tipe` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'string' COMMENT 'string | integer | boolean | json', `grup` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'umum', `keterangan` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `tb_sys_pengaturan_kunci_unique` (`kunci`) ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `tb_sys_pengaturan` (`id`, `kunci`, `nilai`, `tipe`, `grup`, `keterangan`, `created_at`, `updated_at`) VALUES ('1', 'aplikasi.nama_yayasan', 'Yayasan Pendidikan', 'string', 'umum', 'Nama yayasan pada kop dan navbar', '2026-10-09 08:10:15', '2026-10-09 08:10:15'), ('2', 'aplikasi.logo', NULL, 'string', 'umum', 'Path logo yayasan', '2026-10-09 08:10:15', '2026-10-09 08:10:15'), ('3', 'alur.kirim_hanya_ke_yayasan', '1', 'boolean', 'alur', 'Naskah keluar unit kerja selain Yayasan hanya boleh dikirim ke Yayasan', '2026-10-09 08:10:15', '2026-10-09 08:10:15'), ('4', 'notifikasi.interval_polling_detik', '15', 'integer', 'notifikasi', 'Interval AJAX polling notifikasi', '2026-10-09 08:10:15', '2026-10-09 08:10:15'), ('5', 'notifikasi.hari_pengingat_batas', '2', 'integer', 'notifikasi', 'Pengingat dikirim H-n sebelum batas waktu disposisi', '2026-10-09 08:10:15', '2026-10-09 08:10:15'), ('6', 'notifikasi.suara_surat_masuk', 'surat-masuk', 'string', 'notifikasi', 'Suara notifikasi naskah masuk', '2026-10-09 08:10:15', '2026-10-09 08:10:15'), ('7', 'upload.ukuran_maks_kb', '10240', 'integer', 'upload', 'Ukuran maksimal file unggahan (KB)', '2026-10-09 08:10:15', '2026-10-09 08:10:15'), ('8', 'upload.ekstensi_diizinkan', 'pdf,jpg,jpeg,png', 'string', 'upload', 'Ekstensi file yang diizinkan', '2026-10-09 08:10:15', '2026-10-09 08:10:15'), ('9', 'arsip.masa_retensi_tahun', '10', 'integer', 'arsip', 'Masa simpan arsip sebelum boleh ditinjau untuk pemusnahan', '2026-10-09 08:10:15', '2026-10-09 08:10:15'), ('10', 'backup.jumlah_disimpan', '7', 'integer', 'backup', 'Jumlah file backup terbaru yang disimpan; yang lebih lama dihapus otomatis', '2026-10-09 08:10:15', '2026-10-09 08:10:15'), ('11', 'backup.sertakan_dokumen', '1', 'boolean', 'backup', 'Sertakan file dokumen (PDF/gambar) di dalam backup', '2026-10-09 08:10:15', '2026-10-09 08:10:15'), ('12', 'backup.otomatis', '1', 'boolean', 'backup', 'Jalankan backup otomatis setiap hari lewat scheduler', '2026-10-09 08:10:15', '2026-10-09 08:10:15'); -- -------------------------------------------------------- -- Tabel `tb_sys_pengguna` DROP TABLE IF EXISTS `tb_sys_pengguna`; CREATE TABLE `tb_sys_pengguna` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `nama` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL, `username` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `email` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL, `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `id_unit` bigint unsigned NOT NULL, `id_jabatan` bigint unsigned DEFAULT NULL, `telepon` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `foto` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `notifikasi_suara` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Preferensi suara notifikasi', `aktif` tinyint(1) NOT NULL DEFAULT '1', `wajib_ganti_password` tinyint(1) NOT NULL DEFAULT '0', `password_diubah_at` timestamp NULL DEFAULT NULL, `terakhir_login` timestamp NULL DEFAULT NULL, `terakhir_login_ip` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `tb_sys_pengguna_username_unique` (`username`), UNIQUE KEY `tb_sys_pengguna_email_unique` (`email`), KEY `tb_sys_pengguna_id_jabatan_foreign` (`id_jabatan`), KEY `tb_sys_pengguna_id_unit_aktif_index` (`id_unit`,`aktif`), CONSTRAINT `tb_sys_pengguna_id_jabatan_foreign` FOREIGN KEY (`id_jabatan`) REFERENCES `tb_ref_jabatan` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_sys_pengguna_id_unit_foreign` FOREIGN KEY (`id_unit`) REFERENCES `tb_ref_unit` (`id`) ON DELETE RESTRICT ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `tb_sys_pengguna` (`id`, `nama`, `username`, `email`, `password`, `id_unit`, `id_jabatan`, `telepon`, `foto`, `notifikasi_suara`, `aktif`, `wajib_ganti_password`, `password_diubah_at`, `terakhir_login`, `terakhir_login_ip`, `remember_token`, `created_at`, `updated_at`, `deleted_at`) VALUES ('1', 'Administrator', 'admin', 'admin@yayasan.local', '$2y$12$0v30X//n3Izd56tZK9RHVub01pUFRBdzSEutisdj.lRMnqlppHLO2', '1', NULL, NULL, NULL, '1', '1', '1', NULL, NULL, NULL, NULL, '2026-10-09 08:10:18', '2026-10-09 08:10:18', NULL); -- -------------------------------------------------------- -- Tabel `tb_sys_pengguna_role` DROP TABLE IF EXISTS `tb_sys_pengguna_role`; CREATE TABLE `tb_sys_pengguna_role` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `id_pengguna` bigint unsigned NOT NULL, `id_role` bigint unsigned NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `tb_sys_pengguna_role_id_pengguna_id_role_unique` (`id_pengguna`,`id_role`), KEY `tb_sys_pengguna_role_id_role_foreign` (`id_role`), CONSTRAINT `tb_sys_pengguna_role_id_pengguna_foreign` FOREIGN KEY (`id_pengguna`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE CASCADE, CONSTRAINT `tb_sys_pengguna_role_id_role_foreign` FOREIGN KEY (`id_role`) REFERENCES `tb_sys_role` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `tb_sys_pengguna_role` (`id`, `id_pengguna`, `id_role`, `created_at`, `updated_at`) VALUES ('1', '1', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'); -- -------------------------------------------------------- -- Tabel `tb_sys_permission` DROP TABLE IF EXISTS `tb_sys_permission`; CREATE TABLE `tb_sys_permission` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `kode_permission` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Format: modul.aksi', `nama_permission` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL, `modul` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `tb_sys_permission_kode_permission_unique` (`kode_permission`), KEY `tb_sys_permission_modul_index` (`modul`) ) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `tb_sys_permission` (`id`, `kode_permission`, `nama_permission`, `modul`, `created_at`, `updated_at`) VALUES ('1', 'dashboard.lihat', 'Melihat dashboard', 'dashboard', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('2', 'surat_masuk.lihat', 'Melihat naskah masuk yang didisposisikan kepadanya', 'surat_masuk', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('3', 'surat_masuk.lihat_semua', 'Melihat seluruh naskah masuk unit kerjanya', 'surat_masuk', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('4', 'surat_masuk.lihat_rahasia', 'Melihat naskah masuk bersifat Rahasia di unit kerjanya meskipun tidak terlibat disposisinya', 'surat_masuk', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('5', 'surat_masuk.tambah', 'Menginput naskah masuk dan upload naskah', 'surat_masuk', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('6', 'surat_masuk.ubah', 'Mengubah data naskah masuk', 'surat_masuk', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('7', 'surat_masuk.agenda', 'Mengagendakan naskah masuk di buku agenda unit kerjanya', 'surat_masuk', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('8', 'surat_masuk.teruskan_ketua', 'Meneruskan naskah masuk kepada pimpinan unit kerjanya', 'surat_masuk', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('9', 'surat_masuk.selesaikan', 'Menyatakan naskah masuk selesai tanpa disposisi (cukup diketahui pimpinan)', 'surat_masuk', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('10', 'surat_masuk.batalkan', 'Membatalkan naskah masuk', 'surat_masuk', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('11', 'surat_masuk.arsipkan', 'Mengarsipkan naskah masuk', 'surat_masuk', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('12', 'surat_masuk.unduh', 'Mengunduh dokumen naskah masuk', 'surat_masuk', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('13', 'surat_masuk.ekspor', 'Ekspor data naskah masuk ke Excel/PDF', 'surat_masuk', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('14', 'disposisi.lihat', 'Melihat disposisi', 'disposisi', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('15', 'disposisi.buat', 'Mendisposisikan naskah masuk kepada sub unit kerja di unit kerjanya', 'disposisi', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('16', 'disposisi.batalkan', 'Membatalkan disposisi', 'disposisi', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('17', 'disposisi.pantau', 'Memantau progres seluruh disposisi unit kerjanya', 'disposisi', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('18', 'tindak_lanjut.lihat', 'Melihat tindak lanjut', 'tindak_lanjut', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('19', 'tindak_lanjut.terima', 'Menerima disposisi', 'tindak_lanjut', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('20', 'tindak_lanjut.input', 'Menginput tindak lanjut dan dokumen pendukung', 'tindak_lanjut', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('21', 'tindak_lanjut.verifikasi', 'Menyatakan selesai disposisi yang diberikannya', 'tindak_lanjut', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('22', 'surat_keluar.lihat', 'Melihat naskah keluar yang didistribusikan kepadanya (distribusi perorangan lama)', 'surat_keluar', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('23', 'surat_keluar.lihat_semua', 'Melihat seluruh naskah keluar unit kerjanya', 'surat_keluar', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('24', 'surat_keluar.tambah', 'Menginput naskah keluar dan upload naskah', 'surat_keluar', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('25', 'surat_keluar.ubah', 'Mengubah data naskah keluar', 'surat_keluar', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('26', 'surat_keluar.agenda', 'Mengagendakan naskah keluar di buku agenda unit kerjanya', 'surat_keluar', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('27', 'surat_keluar.distribusi', 'Mengirim naskah keluar kepada unit kerja lain', 'surat_keluar', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('28', 'surat_keluar.konfirmasi', 'Mengonfirmasi penerimaan naskah keluar (distribusi perorangan lama)', 'surat_keluar', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('29', 'surat_keluar.batalkan', 'Membatalkan naskah keluar', 'surat_keluar', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('30', 'surat_keluar.arsipkan', 'Mengarsipkan naskah keluar', 'surat_keluar', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('31', 'surat_keluar.unduh', 'Mengunduh dokumen naskah keluar', 'surat_keluar', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('32', 'surat_keluar.ekspor', 'Ekspor data naskah keluar ke Excel/PDF', 'surat_keluar', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('33', 'arsip.lihat', 'Melihat arsip digital', 'arsip', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('34', 'arsip.unduh', 'Mengunduh dokumen arsip', 'arsip', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('35', 'laporan.lihat', 'Melihat laporan', 'laporan', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('36', 'laporan.ekspor', 'Ekspor laporan', 'laporan', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('37', 'riwayat.lihat', 'Melihat timeline/riwayat naskah', 'riwayat', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('38', 'sistem.kelola_pengguna', 'Mengelola pengguna', 'sistem', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('39', 'sistem.kelola_role', 'Mengelola role dan permission-nya', 'sistem', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('40', 'sistem.kelola_unit', 'Mengelola unit kerja', 'sistem', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('41', 'sistem.kelola_referensi', 'Mengelola data referensi', 'sistem', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('42', 'sistem.kelola_pengaturan', 'Mengatur konfigurasi sistem', 'sistem', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('43', 'sistem.lihat_audit', 'Melihat audit log', 'sistem', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('44', 'sistem.kelola_tampilan', 'Mengelola menu sidebar dan widget dashboard per role', 'sistem', '2026-10-09 08:10:18', '2026-10-09 08:10:18'); -- -------------------------------------------------------- -- Tabel `tb_sys_reset_password` DROP TABLE IF EXISTS `tb_sys_reset_password`; CREATE TABLE `tb_sys_reset_password` ( `email` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL, `token` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`email`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Tabel `tb_sys_role` DROP TABLE IF EXISTS `tb_sys_role`; CREATE TABLE `tb_sys_role` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `kode_role` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL, `nama_role` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `keterangan` text COLLATE utf8mb4_unicode_ci, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `tb_sys_role_kode_role_unique` (`kode_role`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `tb_sys_role` (`id`, `kode_role`, `nama_role`, `keterangan`, `created_at`, `updated_at`) VALUES ('1', 'SUPER_ADMIN', 'Super Admin', 'Pengelola sistem dengan akses penuh', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('2', 'PIMPINAN', 'Pimpinan Unit Kerja', 'Ketua Yayasan / Kepala Sekolah: memantau naskah unit kerjanya, mendisposisikan ke sub unit kerja, dan mengetahui naskah keluar', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('3', 'SEKRETARIS', 'Sekretaris/TU Unit Kerja', 'Sekretaris Yayasan / TU Sekolah: mengelola naskah masuk dan keluar unit kerjanya', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('4', 'SUB_UNIT', 'Sub Unit Kerja', 'Kabid, Bendahara, guru, staf: menerima disposisi pimpinan dan menindaklanjutinya', '2026-10-09 08:10:18', '2026-10-09 08:10:18'); -- -------------------------------------------------------- -- Tabel `tb_sys_role_permission` DROP TABLE IF EXISTS `tb_sys_role_permission`; CREATE TABLE `tb_sys_role_permission` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `id_role` bigint unsigned NOT NULL, `id_permission` bigint unsigned NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `tb_sys_role_permission_id_role_id_permission_unique` (`id_role`,`id_permission`), KEY `tb_sys_role_permission_id_permission_foreign` (`id_permission`), CONSTRAINT `tb_sys_role_permission_id_permission_foreign` FOREIGN KEY (`id_permission`) REFERENCES `tb_sys_permission` (`id`) ON DELETE CASCADE, CONSTRAINT `tb_sys_role_permission_id_role_foreign` FOREIGN KEY (`id_role`) REFERENCES `tb_sys_role` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=99 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `tb_sys_role_permission` (`id`, `id_role`, `id_permission`, `created_at`, `updated_at`) VALUES ('1', '1', '33', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('2', '1', '34', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('3', '1', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('4', '1', '16', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('5', '1', '15', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('6', '1', '14', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('7', '1', '17', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('8', '1', '36', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('9', '1', '35', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('10', '1', '37', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('11', '1', '42', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('12', '1', '38', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('13', '1', '41', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('14', '1', '39', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('15', '1', '44', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('16', '1', '40', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('17', '1', '43', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('18', '1', '26', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('19', '1', '30', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('20', '1', '29', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('21', '1', '27', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('22', '1', '32', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('23', '1', '28', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('24', '1', '22', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('25', '1', '23', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('26', '1', '24', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('27', '1', '25', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('28', '1', '31', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('29', '1', '7', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('30', '1', '11', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('31', '1', '10', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('32', '1', '13', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('33', '1', '2', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('34', '1', '4', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('35', '1', '3', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('36', '1', '9', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('37', '1', '5', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('38', '1', '8', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('39', '1', '6', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('40', '1', '12', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('41', '1', '20', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('42', '1', '18', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('43', '1', '19', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('44', '1', '21', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('45', '2', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('46', '2', '3', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('47', '2', '4', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('48', '2', '12', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('49', '2', '9', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('50', '2', '14', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('51', '2', '15', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('52', '2', '16', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('53', '2', '17', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('54', '2', '18', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('55', '2', '21', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('56', '2', '23', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('57', '2', '31', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('58', '2', '33', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('59', '2', '34', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('60', '2', '35', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('61', '2', '36', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('62', '2', '37', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('63', '3', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('64', '3', '3', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('65', '3', '4', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('66', '3', '5', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('67', '3', '6', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('68', '3', '7', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('69', '3', '8', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('70', '3', '10', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('71', '3', '11', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('72', '3', '12', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('73', '3', '13', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('74', '3', '14', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('75', '3', '17', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('76', '3', '18', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('77', '3', '23', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('78', '3', '24', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('79', '3', '25', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('80', '3', '26', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('81', '3', '27', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('82', '3', '29', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('83', '3', '30', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('84', '3', '31', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('85', '3', '32', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('86', '3', '33', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('87', '3', '34', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('88', '3', '35', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('89', '3', '36', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('90', '3', '37', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('91', '4', '1', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('92', '4', '2', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('93', '4', '12', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('94', '4', '14', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('95', '4', '18', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('96', '4', '19', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('97', '4', '20', '2026-10-09 08:10:18', '2026-10-09 08:10:18'), ('98', '4', '37', '2026-10-09 08:10:18', '2026-10-09 08:10:18'); -- -------------------------------------------------------- -- Tabel `tb_sys_role_widget` DROP TABLE IF EXISTS `tb_sys_role_widget`; CREATE TABLE `tb_sys_role_widget` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `id_role` bigint unsigned NOT NULL, `kode_widget` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `tb_sys_role_widget_id_role_kode_widget_unique` (`id_role`,`kode_widget`), CONSTRAINT `tb_sys_role_widget_id_role_foreign` FOREIGN KEY (`id_role`) REFERENCES `tb_sys_role` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `tb_sys_role_widget` (`id`, `id_role`, `kode_widget`) VALUES ('36', '1', 'aktivitas_terbaru'), ('19', '1', 'disposisi_untuk_saya'), ('27', '1', 'grafik_bulanan'), ('31', '1', 'grafik_status'), ('21', '1', 'keluar_menunggu_distribusi'), ('23', '1', 'keluar_perlu_konfirmasi'), ('17', '1', 'menunggu_disposisi'), ('35', '1', 'naskah_terbaru'), ('16', '1', 'statistik_belum_dibaca'), ('8', '1', 'statistik_keluar'), ('4', '1', 'statistik_masuk'), ('12', '1', 'statistik_menunggu'), ('37', '2', 'aktivitas_terbaru'), ('24', '2', 'grafik_bulanan'), ('28', '2', 'grafik_status'), ('18', '2', 'menunggu_disposisi'), ('32', '2', 'naskah_terbaru'), ('13', '2', 'statistik_belum_dibaca'), ('5', '2', 'statistik_keluar'), ('1', '2', 'statistik_masuk'), ('9', '2', 'statistik_menunggu'), ('38', '3', 'aktivitas_terbaru'), ('25', '3', 'grafik_bulanan'), ('29', '3', 'grafik_status'), ('22', '3', 'keluar_menunggu_distribusi'), ('33', '3', 'naskah_terbaru'), ('14', '3', 'statistik_belum_dibaca'), ('6', '3', 'statistik_keluar'), ('2', '3', 'statistik_masuk'), ('10', '3', 'statistik_menunggu'), ('39', '4', 'aktivitas_terbaru'), ('20', '4', 'disposisi_untuk_saya'), ('26', '4', 'grafik_bulanan'), ('30', '4', 'grafik_status'), ('34', '4', 'naskah_terbaru'), ('15', '4', 'statistik_belum_dibaca'), ('7', '4', 'statistik_keluar'), ('3', '4', 'statistik_masuk'), ('11', '4', 'statistik_menunggu'); -- -------------------------------------------------------- -- Tabel `tb_sys_sesi` DROP TABLE IF EXISTS `tb_sys_sesi`; CREATE TABLE `tb_sys_sesi` ( `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `user_id` bigint unsigned DEFAULT NULL, `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `user_agent` text COLLATE utf8mb4_unicode_ci, `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL, `last_activity` int NOT NULL, PRIMARY KEY (`id`), KEY `tb_sys_sesi_user_id_index` (`user_id`), KEY `tb_sys_sesi_last_activity_index` (`last_activity`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Tabel `tb_trx_disposisi` DROP TABLE IF EXISTS `tb_trx_disposisi`; CREATE TABLE `tb_trx_disposisi` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `id_surat_masuk` bigint unsigned NOT NULL, `id_penerima_induk` bigint unsigned DEFAULT NULL COMMENT 'Baris penerima yang meneruskan disposisi ini', `nomor_disposisi` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `id_user_pemberi` bigint unsigned NOT NULL, `tanggal_disposisi` datetime NOT NULL, `instruksi` text COLLATE utf8mb4_unicode_ci COMMENT 'Instruksi umum untuk seluruh penerima', `tanggal_batas` date DEFAULT NULL, `id_status` bigint unsigned NOT NULL, `keterangan` text COLLATE utf8mb4_unicode_ci, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `tb_trx_disposisi_nomor_disposisi_unique` (`nomor_disposisi`), KEY `tb_trx_disposisi_id_user_pemberi_foreign` (`id_user_pemberi`), KEY `tb_trx_disposisi_id_status_foreign` (`id_status`), KEY `tb_trx_disposisi_id_surat_masuk_tanggal_disposisi_index` (`id_surat_masuk`,`tanggal_disposisi`), KEY `tb_trx_disposisi_tanggal_batas_index` (`tanggal_batas`), KEY `tb_trx_disposisi_id_penerima_induk_foreign` (`id_penerima_induk`), CONSTRAINT `tb_trx_disposisi_id_penerima_induk_foreign` FOREIGN KEY (`id_penerima_induk`) REFERENCES `tb_trx_disposisi_penerima` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_disposisi_id_status_foreign` FOREIGN KEY (`id_status`) REFERENCES `tb_ref_status_disposisi` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_disposisi_id_surat_masuk_foreign` FOREIGN KEY (`id_surat_masuk`) REFERENCES `tb_trx_surat_masuk` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_disposisi_id_user_pemberi_foreign` FOREIGN KEY (`id_user_pemberi`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Tabel `tb_trx_disposisi_aksi` DROP TABLE IF EXISTS `tb_trx_disposisi_aksi`; CREATE TABLE `tb_trx_disposisi_aksi` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `id_disposisi` bigint unsigned NOT NULL, `id_aksi_disposisi` bigint unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `tb_trx_disposisi_aksi_id_disposisi_id_aksi_disposisi_unique` (`id_disposisi`,`id_aksi_disposisi`), KEY `tb_trx_disposisi_aksi_id_aksi_disposisi_foreign` (`id_aksi_disposisi`), CONSTRAINT `tb_trx_disposisi_aksi_id_aksi_disposisi_foreign` FOREIGN KEY (`id_aksi_disposisi`) REFERENCES `tb_ref_aksi_disposisi` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_disposisi_aksi_id_disposisi_foreign` FOREIGN KEY (`id_disposisi`) REFERENCES `tb_trx_disposisi` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Tabel `tb_trx_disposisi_penerima` DROP TABLE IF EXISTS `tb_trx_disposisi_penerima`; CREATE TABLE `tb_trx_disposisi_penerima` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `id_disposisi` bigint unsigned NOT NULL, `id_user_penerima` bigint unsigned DEFAULT NULL, `id_unit_penerima` bigint unsigned NOT NULL, `id_user_diterima` bigint unsigned DEFAULT NULL, `id_status` bigint unsigned NOT NULL, `instruksi` text COLLATE utf8mb4_unicode_ci COMMENT 'Instruksi khusus penerima; kosong = instruksi umum disposisi', `tanggal_batas` date DEFAULT NULL COMMENT 'Batas waktu khusus penerima ini', `tanggal_diterima` datetime DEFAULT NULL, `tanggal_dibaca` datetime DEFAULT NULL, `tanggal_mulai_proses` datetime DEFAULT NULL, `tanggal_selesai` datetime DEFAULT NULL, `keterangan` text COLLATE utf8mb4_unicode_ci, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `tb_trx_disposisi_penerima_id_disposisi_foreign` (`id_disposisi`), KEY `tb_trx_disposisi_penerima_id_user_diterima_foreign` (`id_user_diterima`), KEY `tb_trx_disposisi_penerima_id_status_foreign` (`id_status`), KEY `tb_trx_disposisi_penerima_id_user_penerima_id_status_index` (`id_user_penerima`,`id_status`), KEY `tb_trx_disposisi_penerima_id_unit_penerima_id_status_index` (`id_unit_penerima`,`id_status`), KEY `tb_trx_disposisi_penerima_tanggal_batas_index` (`tanggal_batas`), CONSTRAINT `tb_trx_disposisi_penerima_id_disposisi_foreign` FOREIGN KEY (`id_disposisi`) REFERENCES `tb_trx_disposisi` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_disposisi_penerima_id_status_foreign` FOREIGN KEY (`id_status`) REFERENCES `tb_ref_status_disposisi` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_disposisi_penerima_id_unit_penerima_foreign` FOREIGN KEY (`id_unit_penerima`) REFERENCES `tb_ref_unit` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_disposisi_penerima_id_user_diterima_foreign` FOREIGN KEY (`id_user_diterima`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_disposisi_penerima_id_user_penerima_foreign` FOREIGN KEY (`id_user_penerima`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Tabel `tb_trx_disposisi_penerima_aksi` DROP TABLE IF EXISTS `tb_trx_disposisi_penerima_aksi`; CREATE TABLE `tb_trx_disposisi_penerima_aksi` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `id_disposisi_penerima` bigint unsigned NOT NULL, `id_aksi_disposisi` bigint unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uq_penerima_aksi` (`id_disposisi_penerima`,`id_aksi_disposisi`), KEY `tb_trx_disposisi_penerima_aksi_id_aksi_disposisi_foreign` (`id_aksi_disposisi`), CONSTRAINT `tb_trx_disposisi_penerima_aksi_id_aksi_disposisi_foreign` FOREIGN KEY (`id_aksi_disposisi`) REFERENCES `tb_ref_aksi_disposisi` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_disposisi_penerima_aksi_id_disposisi_penerima_foreign` FOREIGN KEY (`id_disposisi_penerima`) REFERENCES `tb_trx_disposisi_penerima` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Tabel `tb_trx_distribusi_surat_keluar` DROP TABLE IF EXISTS `tb_trx_distribusi_surat_keluar`; CREATE TABLE `tb_trx_distribusi_surat_keluar` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `id_surat_keluar` bigint unsigned NOT NULL, `id_user_penerima` bigint unsigned DEFAULT NULL, `id_unit_penerima` bigint unsigned NOT NULL, `id_user_pengirim` bigint unsigned NOT NULL, `id_user_diterima` bigint unsigned DEFAULT NULL, `tanggal_distribusi` datetime NOT NULL, `tanggal_dibaca` datetime DEFAULT NULL, `tanggal_diterima` datetime DEFAULT NULL COMMENT 'Konfirmasi penerimaan', `id_status` bigint unsigned NOT NULL, `id_surat_masuk` bigint unsigned DEFAULT NULL COMMENT 'Naskah masuk internal di unit tujuan', `keterangan` text COLLATE utf8mb4_unicode_ci, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `tb_trx_distribusi_surat_keluar_id_user_pengirim_foreign` (`id_user_pengirim`), KEY `tb_trx_distribusi_surat_keluar_id_user_diterima_foreign` (`id_user_diterima`), KEY `tb_trx_distribusi_surat_keluar_id_status_foreign` (`id_status`), KEY `tb_trx_distribusi_surat_keluar_id_user_penerima_id_status_index` (`id_user_penerima`,`id_status`), KEY `tb_trx_distribusi_surat_keluar_id_unit_penerima_id_status_index` (`id_unit_penerima`,`id_status`), KEY `tb_trx_distribusi_surat_keluar_id_surat_keluar_index` (`id_surat_keluar`), KEY `tb_trx_distribusi_surat_keluar_id_surat_masuk_foreign` (`id_surat_masuk`), CONSTRAINT `tb_trx_distribusi_surat_keluar_id_status_foreign` FOREIGN KEY (`id_status`) REFERENCES `tb_ref_status_distribusi` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_distribusi_surat_keluar_id_surat_keluar_foreign` FOREIGN KEY (`id_surat_keluar`) REFERENCES `tb_trx_surat_keluar` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_distribusi_surat_keluar_id_surat_masuk_foreign` FOREIGN KEY (`id_surat_masuk`) REFERENCES `tb_trx_surat_masuk` (`id`) ON DELETE SET NULL, CONSTRAINT `tb_trx_distribusi_surat_keluar_id_unit_penerima_foreign` FOREIGN KEY (`id_unit_penerima`) REFERENCES `tb_ref_unit` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_distribusi_surat_keluar_id_user_diterima_foreign` FOREIGN KEY (`id_user_diterima`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_distribusi_surat_keluar_id_user_penerima_foreign` FOREIGN KEY (`id_user_penerima`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_distribusi_surat_keluar_id_user_pengirim_foreign` FOREIGN KEY (`id_user_pengirim`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Tabel `tb_trx_pengirim` DROP TABLE IF EXISTS `tb_trx_pengirim`; CREATE TABLE `tb_trx_pengirim` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `nama_pengirim` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL, `instansi` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `alamat` text COLLATE utf8mb4_unicode_ci, `email` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `telepon` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `tb_trx_pengirim_nama_pengirim_index` (`nama_pengirim`), KEY `tb_trx_pengirim_instansi_index` (`instansi`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Tabel `tb_trx_riwayat_surat` DROP TABLE IF EXISTS `tb_trx_riwayat_surat`; CREATE TABLE `tb_trx_riwayat_surat` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `jenis_transaksi` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'SURAT_MASUK | SURAT_KELUAR', `id_surat_masuk` bigint unsigned DEFAULT NULL, `id_surat_keluar` bigint unsigned DEFAULT NULL, `status_sebelumnya` bigint unsigned DEFAULT NULL, `status_baru` bigint unsigned DEFAULT NULL, `aktivitas` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Kode aktivitas, mis. DIAGENDAKAN, DISPOSISI_DIBUAT', `keterangan` text COLLATE utf8mb4_unicode_ci, `referensi_tipe` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `referensi_id` bigint unsigned DEFAULT NULL, `id_user` bigint unsigned DEFAULT NULL, `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `tb_trx_riwayat_surat_status_sebelumnya_foreign` (`status_sebelumnya`), KEY `tb_trx_riwayat_surat_status_baru_foreign` (`status_baru`), KEY `tb_trx_riwayat_surat_id_user_foreign` (`id_user`), KEY `tb_trx_riwayat_surat_id_surat_masuk_created_at_index` (`id_surat_masuk`,`created_at`), KEY `tb_trx_riwayat_surat_id_surat_keluar_created_at_index` (`id_surat_keluar`,`created_at`), KEY `tb_trx_riwayat_surat_aktivitas_index` (`aktivitas`), CONSTRAINT `tb_trx_riwayat_surat_id_surat_keluar_foreign` FOREIGN KEY (`id_surat_keluar`) REFERENCES `tb_trx_surat_keluar` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_riwayat_surat_id_surat_masuk_foreign` FOREIGN KEY (`id_surat_masuk`) REFERENCES `tb_trx_surat_masuk` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_riwayat_surat_id_user_foreign` FOREIGN KEY (`id_user`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_riwayat_surat_status_baru_foreign` FOREIGN KEY (`status_baru`) REFERENCES `tb_ref_status_surat` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_riwayat_surat_status_sebelumnya_foreign` FOREIGN KEY (`status_sebelumnya`) REFERENCES `tb_ref_status_surat` (`id`) ON DELETE RESTRICT, CONSTRAINT `chk_riwayat_jenis_transaksi` CHECK ((((`jenis_transaksi` = _utf8mb4'SURAT_MASUK') and (`id_surat_masuk` is not null) and (`id_surat_keluar` is null)) or ((`jenis_transaksi` = _utf8mb4'SURAT_KELUAR') and (`id_surat_keluar` is not null) and (`id_surat_masuk` is null)))) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Tabel `tb_trx_surat_keluar` DROP TABLE IF EXISTS `tb_trx_surat_keluar`; CREATE TABLE `tb_trx_surat_keluar` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `tahun_agenda` smallint unsigned DEFAULT NULL, `urutan_agenda` int unsigned DEFAULT NULL, `nomor_agenda` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `buku_agenda` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'PUSAT' COMMENT 'PUSAT = yayasan; U{id_unit} = buku agenda naskah keluar unit', `nomor_surat` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `id_kategori_surat` bigint unsigned NOT NULL, `tanggal_surat` date NOT NULL, `tanggal_agenda` datetime DEFAULT NULL, `id_tujuan` bigint unsigned NOT NULL, `jenis_tujuan` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'INTERNAL' COMMENT 'INTERNAL = didistribusikan/dikirim lewat aplikasi; EKSTERNAL = pihak luar, hanya arsip', `perihal` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `ringkasan` text COLLATE utf8mb4_unicode_ci, `lampiran` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `id_penandatangan` bigint unsigned NOT NULL, `id_unit_pengirim` bigint unsigned NOT NULL, `id_status` bigint unsigned NOT NULL, `id_user_input` bigint unsigned NOT NULL, `tanggal_selesai` datetime DEFAULT NULL, `tanggal_arsip` datetime DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, `id_klasifikasi` bigint unsigned NOT NULL, `id_bidang_unit` bigint unsigned DEFAULT NULL, `id_derajat` bigint unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uq_surat_keluar_buku_nomor_surat` (`buku_agenda`,`nomor_surat`), UNIQUE KEY `uq_surat_keluar_agenda` (`tahun_agenda`,`urutan_agenda`), UNIQUE KEY `uq_surat_keluar_buku_nomor_agenda` (`buku_agenda`,`nomor_agenda`), KEY `tb_trx_surat_keluar_id_tujuan_foreign` (`id_tujuan`), KEY `tb_trx_surat_keluar_id_penandatangan_foreign` (`id_penandatangan`), KEY `tb_trx_surat_keluar_id_unit_pengirim_foreign` (`id_unit_pengirim`), KEY `tb_trx_surat_keluar_id_user_input_foreign` (`id_user_input`), KEY `tb_trx_surat_keluar_tanggal_surat_index` (`tanggal_surat`), KEY `tb_trx_surat_keluar_id_status_tanggal_surat_index` (`id_status`,`tanggal_surat`), KEY `tb_trx_surat_keluar_perihal_index` (`perihal`), KEY `tb_trx_surat_keluar_id_kategori_surat_foreign` (`id_kategori_surat`), KEY `tb_trx_surat_keluar_id_klasifikasi_foreign` (`id_klasifikasi`), KEY `tb_trx_surat_keluar_id_derajat_foreign` (`id_derajat`), KEY `tb_trx_surat_keluar_id_bidang_unit_foreign` (`id_bidang_unit`), CONSTRAINT `tb_trx_surat_keluar_id_bidang_unit_foreign` FOREIGN KEY (`id_bidang_unit`) REFERENCES `tb_ref_bidang_unit` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_surat_keluar_id_derajat_foreign` FOREIGN KEY (`id_derajat`) REFERENCES `tb_ref_derajat_surat` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_surat_keluar_id_kategori_surat_foreign` FOREIGN KEY (`id_kategori_surat`) REFERENCES `tb_ref_kategori_surat` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_surat_keluar_id_klasifikasi_foreign` FOREIGN KEY (`id_klasifikasi`) REFERENCES `tb_ref_klasifikasi_surat` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_surat_keluar_id_penandatangan_foreign` FOREIGN KEY (`id_penandatangan`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_surat_keluar_id_status_foreign` FOREIGN KEY (`id_status`) REFERENCES `tb_ref_status_surat` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_surat_keluar_id_tujuan_foreign` FOREIGN KEY (`id_tujuan`) REFERENCES `tb_trx_tujuan_surat` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_surat_keluar_id_unit_pengirim_foreign` FOREIGN KEY (`id_unit_pengirim`) REFERENCES `tb_ref_unit` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_surat_keluar_id_user_input_foreign` FOREIGN KEY (`id_user_input`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Tabel `tb_trx_surat_keluar_file` DROP TABLE IF EXISTS `tb_trx_surat_keluar_file`; CREATE TABLE `tb_trx_surat_keluar_file` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `id_surat_keluar` bigint unsigned NOT NULL, `nama_file` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `path_file` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `tipe_file` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `ukuran_file` bigint unsigned NOT NULL, `hash_file` char(64) COLLATE utf8mb4_unicode_ci NOT NULL, `versi` smallint unsigned NOT NULL DEFAULT '1', `keterangan` text COLLATE utf8mb4_unicode_ci, `id_user_upload` bigint unsigned NOT NULL, `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `tb_trx_surat_keluar_file_id_surat_keluar_versi_unique` (`id_surat_keluar`,`versi`), UNIQUE KEY `tb_trx_surat_keluar_file_path_file_unique` (`path_file`), KEY `tb_trx_surat_keluar_file_id_user_upload_foreign` (`id_user_upload`), CONSTRAINT `tb_trx_surat_keluar_file_id_surat_keluar_foreign` FOREIGN KEY (`id_surat_keluar`) REFERENCES `tb_trx_surat_keluar` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_surat_keluar_file_id_user_upload_foreign` FOREIGN KEY (`id_user_upload`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Tabel `tb_trx_surat_masuk` DROP TABLE IF EXISTS `tb_trx_surat_masuk`; CREATE TABLE `tb_trx_surat_masuk` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `tahun_agenda` smallint unsigned DEFAULT NULL, `urutan_agenda` int unsigned DEFAULT NULL, `nomor_agenda` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `buku_agenda` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'PUSAT' COMMENT 'PUSAT = sekretariat yayasan; U{id_unit} = buku agenda unit tujuan', `nomor_surat` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `id_kategori_surat` bigint unsigned NOT NULL, `tanggal_surat` date NOT NULL, `tanggal_terima` date NOT NULL, `id_pengirim` bigint unsigned NOT NULL, `perihal` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `ringkasan` text COLLATE utf8mb4_unicode_ci, `lampiran` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `id_status` bigint unsigned NOT NULL, `id_unit_penerima` bigint unsigned NOT NULL, `id_surat_keluar` bigint unsigned DEFAULT NULL COMMENT 'Diisi bila naskah masuk ini berasal dari naskah keluar internal', `id_user_tujuan` bigint unsigned DEFAULT NULL COMMENT 'Pimpinan unit tujuan naskah internal', `id_user_input` bigint unsigned NOT NULL, `tanggal_agenda` datetime DEFAULT NULL, `tanggal_selesai` datetime DEFAULT NULL, `tanggal_arsip` datetime DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, `deleted_at` timestamp NULL DEFAULT NULL, `id_klasifikasi` bigint unsigned NOT NULL, `id_bidang_unit` bigint unsigned DEFAULT NULL, `id_derajat` bigint unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uq_surat_masuk_pengirim_nomor_buku` (`id_pengirim`,`nomor_surat`,`buku_agenda`), UNIQUE KEY `uq_surat_masuk_agenda` (`tahun_agenda`,`urutan_agenda`), UNIQUE KEY `uq_surat_masuk_buku_nomor_agenda` (`buku_agenda`,`nomor_agenda`), UNIQUE KEY `uq_surat_masuk_keluar_unit` (`id_surat_keluar`,`id_unit_penerima`), KEY `tb_trx_surat_masuk_id_unit_penerima_foreign` (`id_unit_penerima`), KEY `tb_trx_surat_masuk_id_user_input_foreign` (`id_user_input`), KEY `tb_trx_surat_masuk_nomor_surat_index` (`nomor_surat`), KEY `tb_trx_surat_masuk_tanggal_surat_index` (`tanggal_surat`), KEY `tb_trx_surat_masuk_tanggal_terima_index` (`tanggal_terima`), KEY `tb_trx_surat_masuk_id_status_tanggal_terima_index` (`id_status`,`tanggal_terima`), KEY `tb_trx_surat_masuk_perihal_index` (`perihal`), KEY `tb_trx_surat_masuk_id_kategori_surat_foreign` (`id_kategori_surat`), KEY `tb_trx_surat_masuk_id_klasifikasi_foreign` (`id_klasifikasi`), KEY `tb_trx_surat_masuk_id_derajat_foreign` (`id_derajat`), KEY `tb_trx_surat_masuk_id_user_tujuan_foreign` (`id_user_tujuan`), KEY `tb_trx_surat_masuk_id_bidang_unit_foreign` (`id_bidang_unit`), CONSTRAINT `tb_trx_surat_masuk_id_bidang_unit_foreign` FOREIGN KEY (`id_bidang_unit`) REFERENCES `tb_ref_bidang_unit` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_surat_masuk_id_derajat_foreign` FOREIGN KEY (`id_derajat`) REFERENCES `tb_ref_derajat_surat` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_surat_masuk_id_kategori_surat_foreign` FOREIGN KEY (`id_kategori_surat`) REFERENCES `tb_ref_kategori_surat` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_surat_masuk_id_klasifikasi_foreign` FOREIGN KEY (`id_klasifikasi`) REFERENCES `tb_ref_klasifikasi_surat` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_surat_masuk_id_pengirim_foreign` FOREIGN KEY (`id_pengirim`) REFERENCES `tb_trx_pengirim` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_surat_masuk_id_status_foreign` FOREIGN KEY (`id_status`) REFERENCES `tb_ref_status_surat` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_surat_masuk_id_surat_keluar_foreign` FOREIGN KEY (`id_surat_keluar`) REFERENCES `tb_trx_surat_keluar` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_surat_masuk_id_unit_penerima_foreign` FOREIGN KEY (`id_unit_penerima`) REFERENCES `tb_ref_unit` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_surat_masuk_id_user_input_foreign` FOREIGN KEY (`id_user_input`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_surat_masuk_id_user_tujuan_foreign` FOREIGN KEY (`id_user_tujuan`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Tabel `tb_trx_surat_masuk_file` DROP TABLE IF EXISTS `tb_trx_surat_masuk_file`; CREATE TABLE `tb_trx_surat_masuk_file` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `id_surat_masuk` bigint unsigned NOT NULL, `nama_file` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Nama asli file, hanya untuk ditampilkan', `path_file` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Path UUID di storage private', `tipe_file` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `ukuran_file` bigint unsigned NOT NULL COMMENT 'Byte', `hash_file` char(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'SHA-256 untuk verifikasi integritas', `versi` smallint unsigned NOT NULL DEFAULT '1', `keterangan` text COLLATE utf8mb4_unicode_ci, `id_user_upload` bigint unsigned NOT NULL, `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `tb_trx_surat_masuk_file_id_surat_masuk_versi_unique` (`id_surat_masuk`,`versi`), UNIQUE KEY `tb_trx_surat_masuk_file_path_file_unique` (`path_file`), KEY `tb_trx_surat_masuk_file_id_user_upload_foreign` (`id_user_upload`), CONSTRAINT `tb_trx_surat_masuk_file_id_surat_masuk_foreign` FOREIGN KEY (`id_surat_masuk`) REFERENCES `tb_trx_surat_masuk` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_surat_masuk_file_id_user_upload_foreign` FOREIGN KEY (`id_user_upload`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Tabel `tb_trx_tindak_lanjut` DROP TABLE IF EXISTS `tb_trx_tindak_lanjut`; CREATE TABLE `tb_trx_tindak_lanjut` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `id_disposisi_penerima` bigint unsigned NOT NULL, `tanggal_tindak_lanjut` datetime NOT NULL, `uraian_tindak_lanjut` text COLLATE utf8mb4_unicode_ci NOT NULL, `hasil_tindak_lanjut` text COLLATE utf8mb4_unicode_ci, `id_user` bigint unsigned NOT NULL, `id_status` bigint unsigned NOT NULL, `id_user_verifikator` bigint unsigned DEFAULT NULL, `tanggal_verifikasi` datetime DEFAULT NULL, `catatan_verifikasi` text COLLATE utf8mb4_unicode_ci, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `tb_trx_tindak_lanjut_id_user_foreign` (`id_user`), KEY `tb_trx_tindak_lanjut_id_user_verifikator_foreign` (`id_user_verifikator`), KEY `idx_tl_penerima_tanggal` (`id_disposisi_penerima`,`tanggal_tindak_lanjut`), KEY `tb_trx_tindak_lanjut_id_status_index` (`id_status`), CONSTRAINT `tb_trx_tindak_lanjut_id_disposisi_penerima_foreign` FOREIGN KEY (`id_disposisi_penerima`) REFERENCES `tb_trx_disposisi_penerima` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_tindak_lanjut_id_status_foreign` FOREIGN KEY (`id_status`) REFERENCES `tb_ref_status_tindak_lanjut` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_tindak_lanjut_id_user_foreign` FOREIGN KEY (`id_user`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_tindak_lanjut_id_user_verifikator_foreign` FOREIGN KEY (`id_user_verifikator`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Tabel `tb_trx_tindak_lanjut_file` DROP TABLE IF EXISTS `tb_trx_tindak_lanjut_file`; CREATE TABLE `tb_trx_tindak_lanjut_file` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `id_tindak_lanjut` bigint unsigned NOT NULL, `nama_file` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `path_file` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `tipe_file` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `ukuran_file` bigint unsigned NOT NULL, `hash_file` char(64) COLLATE utf8mb4_unicode_ci NOT NULL, `id_user_upload` bigint unsigned NOT NULL, `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `tb_trx_tindak_lanjut_file_path_file_unique` (`path_file`), KEY `tb_trx_tindak_lanjut_file_id_tindak_lanjut_foreign` (`id_tindak_lanjut`), KEY `tb_trx_tindak_lanjut_file_id_user_upload_foreign` (`id_user_upload`), CONSTRAINT `tb_trx_tindak_lanjut_file_id_tindak_lanjut_foreign` FOREIGN KEY (`id_tindak_lanjut`) REFERENCES `tb_trx_tindak_lanjut` (`id`) ON DELETE RESTRICT, CONSTRAINT `tb_trx_tindak_lanjut_file_id_user_upload_foreign` FOREIGN KEY (`id_user_upload`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Tabel `tb_trx_tujuan_surat` DROP TABLE IF EXISTS `tb_trx_tujuan_surat`; CREATE TABLE `tb_trx_tujuan_surat` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `nama_tujuan` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL, `instansi` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `alamat` text COLLATE utf8mb4_unicode_ci, `email` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `telepon` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `tb_trx_tujuan_surat_nama_tujuan_index` (`nama_tujuan`), KEY `tb_trx_tujuan_surat_instansi_index` (`instansi`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; SET FOREIGN_KEY_CHECKS = 1;