Dokumen rencana operasional komprehensif migrasi Sprint 1 untuk semua wave (Wave 1 - Wave 4) menggunakan Duckle (DuckDB engine), pemetaan kolom, audit tabel terkendala, dan resep SQL lengkap.

Rencana Komprehensif Migrasi Sprint 1 (Semua Wave)

Dokumen ini memuat rencana operasional menyeluruh untuk mengeksekusi seluruh 31 modul migrasi pada Sprint 1, yang terbagi ke dalam 4 Gelombang Bertahap (Wave 1 – Wave 4). Migrasi data tabular dijalankan menggunakan Duckle (perangkat data pipeline berbasis core analytical engine DuckDB), berkolaborasi dengan s3-migrator untuk menangani streaming dan enkripsi file storage fisik.

1. Arsitektur Pipeline & Alur Eksekusi Antar Gelombang

Untuk menjamin integritas referensial (Foreign Key Constraints) dan konsistensi relasi data, eksekusi migrasi diatur dalam alur berurutan:

Memuat diagram...

2. Analisis Mendalam: Tabel yang Tertunda / Tidak Bisa Masuk di Sprint 1

Audit langsung pada skema PostgreSQL target menemukan beberapa tabel yang tidak dapat langsung di-insert apa adanya karena kendala struktural:

⚠️ A. Tabel dengan Kolom Sumber yang Hilang / Tanpa Penampung di Target

Tabel Sumber	Tabel Target	Kolom Sumber Tanpa Penampung di Target	Dampak Bisnis	Rekomendasi Solusi Duckle
`p_project_type` (123 baris)	`activity-cms.project_type`	`max_assesse`, `max_assessor`, `is_assessment`, `date_expired`, `is_my_assistium`, `jenis`, `group_id`, `is_virtu`, `is_feedback`, `kategori`, `judul_report`	Target hanya memiliki 9 kolom (nama, logo, deskripsi). Aturan kuota peserta dan assessor akan hilang.	Migrasi Terbatas: Migrasikan kolom identitas (`project_type_id`, `project_type`, `description`), eskalasikan ke tim backend jika kuota dibutuhkan di DB.
`m_master` (66 baris)	`activity-cms.activity_type`	`data_id`, `code`, `order_by`, `is_freetext`, `is_rating`, `is_actionplan`, `is_custom_actionplan`	Di legacy menyimpan flag logika form. Di target disederhanakan murni menjadi master tipe aktivitas.	Mapping Parsial: Ambil kolom `value` sebagai `activity_type`, abaikan flag konfigurasi yang tidak relevan.
`pad_jenis_test_master` (29 baris)	`activity-cms.dev_test_type`	Target mewajibkan 12 flag boolean (`is_form`, `is_module`, `is_self_review`, `is_simulation`, dll.), sedangkan sumber hanya memiliki `jenis_test` dan `is_survey`.	Target membutuhkan nilai boolean default.	Default Injection: Set seluruh 12 flag boolean spesifik menjadi `FALSE` pada query Duckle.

⚠️ B. Tabel dengan Perubahan Normalisasi Desain (Missing Parent Tables)

Tabel Sumber	Tabel Target	Akar Masalah Teknis	Status Sprint 1
`learning_hours_master` & `_child` (22 baris)	`activity-cms.master_learning_hour`	Target mewajibkan foreign key `role_id`, `assignment_type_id`, dan `activity_lh_id`. Tabel induk `master_assignment_type` dan `master_activity_lh` di PostgreSQL staging masih kosong (0 baris).	BLOCKED (Tertunda): Memerlukan pengisian master data referensi oleh tim backend terlebih dahulu.
`t_template_ib_materi_env` (4.454 baris)	`activity-cms.master_instruction_video`	Di file Excel tabel ini dipetakan ke video instruksi. Namun tabel ini berisi data template teks simulasi In-Basket (bukan media video).	EXCLUDED (Dikeluarkan): Hanya tabel `ola_opening_video` (7 baris) yang dimigrasikan ke video instruksi.

⚠️ C. Data yang Sudah Ada di Target (Mencegah Duplikasi PK)

Tabel Sumber	Tabel Target	Baris di Sumber	Baris di Staging	Strategi Eksekusi
`p_competency`	`activity-cms.competency`	475	475 (Lengkap)	Skip: Sudah 100% sinkron, tidak perlu di-insert ulang.
`p_competency_jobtarget`	`activity-cms.competency_label`	3.288	3.285 (Hampir Lengkap)	Diff / Append: Hanya insert selisih ~3 baris yang belum ada di target menggunakan `WHERE NOT EXISTS`.

3. Rencana Operasional Per Wave & Resep Query DuckDB

🟢 Wave 1: Master Standalone (13 Tabel – 786 Baris Total)

Karakteristik: Nol dependensi foreign key. Langsung dapat dieksekusi secara instan.

-- Source: p_city (48 rows) -> Target: "activity-cms".master_city
SELECT 
    city_id::INTEGER AS city_id,
    city_name::VARCHAR AS city_name,
    'SYSTEM_MIGRATION'::VARCHAR AS created_by,
    CURRENT_TIMESTAMP AS created_date,
    NULL::VARCHAR AS last_modified_by,
    NULL::TIMESTAMP AS last_modified_date,
    CASE WHEN is_active = 0 THEN TRUE ELSE FALSE END AS is_deleted
FROM mysql_source.p_city;

🟡 Wave 2: Master Hirarkis & Pohon Kompetensi (11 Modul)

Karakteristik: Memerlukan urutan eksekusi berjenjang untuk menjaga integritas relasional Job Target, Department, serta rantai pohon kompetensi (Competency Label $\rightarrow$ Key Behaviour $\rightarrow$ Key Action).

-- Source: p_jobtarget (544 rows) -> Target: "activity-cms".master_job_target
SELECT 
    jobtarget_id::INTEGER AS job_target_id,
    keljt_id::INTEGER AS job_target_group_id,
    jobtarget_name::VARCHAR AS job_target_name,
    COALESCE(created_by, 'SYSTEM_MIGRATION')::VARCHAR AS created_by,
    COALESCE(created_date, CURRENT_TIMESTAMP) AS created_date,
    NULL::VARCHAR AS last_modified_by,
    NULL::TIMESTAMP AS last_modified_date,
    FALSE AS is_deleted,
    CASE WHEN is_active = 1 THEN TRUE ELSE FALSE END AS job_target_status,
    level_bod::INTEGER AS job_target_level,
    description::TEXT AS job_target_desc,
    company_type::INTEGER AS company_type_id
FROM mysql_source.p_jobtarget;

🔵 Wave 3: Master Data Terkait File S3 / OBS (Integrasi `s3-migrator`)

Karakteristik: File fisik media (logo perusahaan, tanda tangan, video MP4) di-stream, dienkripsi AES-256-CBC, diunggah ke MinIO target, dan Full URL-nya di-inject ke tabel bisnis melalui LEFT JOIN ke tabel "activity-cms".migration_obs_storage.

# 1. Jalankan migrasi file fisik khusus tabel Wave 3
cd "/mnt/data/project/2026/migrasi aci/s3-migrator"
./s3-migrator migrate --table=p_company --workers=5
./s3-migrator migrate --table=p_penandatangan --workers=5
./s3-migrator migrate --table=ola_opening_video --workers=5

# 2. Setelah status SUCCESS terisi di migration_obs_storage, jalankan pipeline Duckle

🟣 Wave 4: User Management & Core Employee (Volume Besar, 40k+ Baris)

Karakteristik: Memigrasikan akun autentikasi pengguna (users) ke skema "user" dan biodata karyawan lengkap (participant) ke skema "activity-cms". Termasuk pemetaan foto profil karyawan dari S3/OBS.

-- Source: users (5.545 rows) -> Target: "user".user
SELECT 
    u.id::INTEGER AS user_id,
    COALESCE(u.id_role_divisi, 2)::INTEGER AS role_id,
    u.username::VARCHAR AS user_name,
    u.email::VARCHAR AS user_email,
    u.password::VARCHAR AS user_password,
    'WEB_ACI'::VARCHAR AS user_client,
    'SYSTEM_MIGRATION'::VARCHAR AS created_by,
    COALESCE(u.create_at, CURRENT_TIMESTAMP) AS created_date,
    NULL::VARCHAR AS last_modified_by,
    NULL::TIMESTAMP AS last_modified_date,
    CASE WHEN u.status = 0 THEN TRUE ELSE FALSE END AS is_deleted,
    NULL::INTEGER AS personel_subarea_id,
    u.username::VARCHAR AS employee_nik,
    NULL::INTEGER AS company_id,
    NULL::INTEGER AS department_id
FROM mysql_source.users u;

4. Script Penyesuaian Auto-Increment Sequence (Post-Migration)

Setelah seluruh data dari Wave 1 hingga Wave 4 selesai di-insert menggunakan ID manual dari MySQL, jalankan script sinkronisasi sequence PostgreSQL berikut agar penambahan data baru di aplikasi target tidak mengalami duplicate key error:

-- Wave 1 Sequences
SELECT setval(pg_get_serial_sequence('"activity-cms".master_city', 'city_id'), COALESCE(MAX(city_id), 1)) FROM "activity-cms".master_city;
SELECT setval(pg_get_serial_sequence('"activity-cms".dev_master_holiday', 'id'), COALESCE(MAX(id), 1)) FROM "activity-cms".dev_master_holiday;
SELECT setval(pg_get_serial_sequence('"activity-cms".master_company_type', 'company_type_id'), COALESCE(MAX(company_type_id), 1)) FROM "activity-cms".master_company_type;
SELECT setval(pg_get_serial_sequence('"activity-cms".master_comp_point_type', 'comp_point_type_id'), COALESCE(MAX(comp_point_type_id), 1)) FROM "activity-cms".master_comp_point_type;
SELECT setval(pg_get_serial_sequence('"activity-cms".dev_test_type', 'id'), COALESCE(MAX(id), 1)) FROM "activity-cms".dev_test_type;
SELECT setval(pg_get_serial_sequence('"activity-cms".master_job_family', 'job_family_id'), COALESCE(MAX(job_family_id), 1)) FROM "activity-cms".master_job_family;
SELECT setval(pg_get_serial_sequence('"activity-cms".hats_final_recom', 'hats_fin_recom_id'), COALESCE(MAX(hats_fin_recom_id), 1)) FROM "activity-cms".hats_final_recom;
SELECT setval(pg_get_serial_sequence('"activity-cms".project_type', 'project_type_id'), COALESCE(MAX(project_type_id), 1)) FROM "activity-cms".project_type;
SELECT setval(pg_get_serial_sequence('"activity-cms".activity_type', 'activity_type_id'), COALESCE(MAX(activity_type_id), 1)) FROM "activity-cms".activity_type;
SELECT setval(pg_get_serial_sequence('"activity-cms".master_rating_cat', 'rating_cat_id'), COALESCE(MAX(rating_cat_id), 1)) FROM "activity-cms".master_rating_cat;
SELECT setval(pg_get_serial_sequence('"activity-cms".master_recommendation', 'recom_id'), COALESCE(MAX(recom_id), 1)) FROM "activity-cms".master_recommendation;
SELECT setval(pg_get_serial_sequence('"activity-cms".master_simulation', 'simulation_id'), COALESCE(MAX(simulation_id), 1)) FROM "activity-cms".master_simulation;
SELECT setval(pg_get_serial_sequence('"activity-cms".master_prompt_ai', 'id'), COALESCE(MAX(id), 1)) FROM "activity-cms".master_prompt_ai;

-- Wave 2 Sequences
SELECT setval(pg_get_serial_sequence('"activity-cms".master_job_target', 'job_target_id'), COALESCE(MAX(job_target_id), 1)) FROM "activity-cms".master_job_target;
SELECT setval(pg_get_serial_sequence('"activity-cms".master_job_recom', 'job_recom_id'), COALESCE(MAX(job_recom_id), 1)) FROM "activity-cms".master_job_recom;
SELECT setval(pg_get_serial_sequence('"activity-cms".master_department', 'department_id'), COALESCE(MAX(department_id), 1)) FROM "activity-cms".master_department;
SELECT setval(pg_get_serial_sequence('"activity-cms".key_behaviour', 'key_behaviour_id'), COALESCE(MAX(key_behaviour_id), 1)) FROM "activity-cms".key_behaviour;
SELECT setval(pg_get_serial_sequence('"activity-cms".key_action', 'key_action_id'), COALESCE(MAX(key_action_id), 1)) FROM "activity-cms".key_action;
SELECT setval(pg_get_serial_sequence('"activity-cms".master_comp_pointers', 'comp_pointers_id'), COALESCE(MAX(comp_pointers_id), 1)) FROM "activity-cms".master_comp_pointers;

-- Wave 3 Sequences
SELECT setval(pg_get_serial_sequence('"activity-cms".master_company', 'company_id'), COALESCE(MAX(company_id), 1)) FROM "activity-cms".master_company;
SELECT setval(pg_get_serial_sequence('"activity-cms".master_signer', 'signer_id'), COALESCE(MAX(signer_id), 1)) FROM "activity-cms".master_signer;
SELECT setval(pg_get_serial_sequence('"activity-cms".master_instruction_video', 'instruction_video_id'), COALESCE(MAX(instruction_video_id), 1)) FROM "activity-cms".master_instruction_video;
SELECT setval(pg_get_serial_sequence('"activity-cms".master_project_manager', 'id'), COALESCE(MAX(id), 1)) FROM "activity-cms".master_project_manager;

-- Wave 4 Sequences
SELECT setval(pg_get_serial_sequence('"user".user', 'user_id'), COALESCE(MAX(user_id), 1)) FROM "user".user;
SELECT setval(pg_get_serial_sequence('"activity-cms".master_employee', 'employee_id'), COALESCE(MAX(employee_id), 1)) FROM "activity-cms".master_employee;

5. Ringkasan Kesiapan Eksekusi Sprint 1

Gelombang	Karakteristik Utama	Jumlah Tabel	Total Baris	Estimasi Waktu Duckle	Rekomendasi Eksekusi
Wave 1	Master Standalone (Zero Dependency)	13	786	< 10 detik	Dapat dieksekusi sekarang juga
Wave 2	Master Hirarkis & Pohon Kompetensi	11	~65.000	~1 - 2 menit	Eksekusi setelah Wave 1 selesai
Wave 3	Master dengan File S3 / OBS Media	4	~1.700	~5 menit (Sync File)	Jalankan `s3-migrator` terlebih dahulu
Wave 4	User Management & Core Employee	4	~40.000	~3 - 5 menit	Eksekusi terakhir (bisa backfill foto)

Plan