Ganesa.ID

No	Nama Lengkap	NISN	L/P	Gaya Belajar (VAK)	MBTI	Top RIASEC	Penalaran	Resiliensi	Aksi
Silakan pilih Rombongan Belajar (Kelas) terlebih dahulu untuk memuat data.

Nama Guru / Staf	Email Akun	Peran	Kelas Dampingan	Status	Aksi
Memuat daftar akun guru & staf...

Nama Sekolah & Wilayah	Volume Siswa	Guru Terdaftar	Siswa Terpetakan Pamong	Log Konseling	Status Keaktifan
Mengambil data telemetri SaaS...

Logo	Nama Resmi Sekolah	Kota / Wilayah	Status Kemitraan	Status Layanan & Lisensi	Aksi
Memuat data sekolah mitra...

Nama Akun	Email	Role	Sekolah Mitra	Aksi
Memuat daftar akun pengguna...

Guru Pamong / Wali	Email Akun	Total Anak Asuh	Daftar Siswa Binaan	Aksi
Memuat data plotting anak asuh...

Sekolah Tujuan	Rombel (Kelas)	Siswa Terproses	Tanggal Impor	Aksi
Memuat riwayat unggahan...

No	Nama Siswa	L/P	NISN	Kode Peserta / Tes	Status Asesmen	Tanggal Impor	Aksi
🏫 Pilih Sekolah Mitra dan Rombel Silakan tentukan rombongan belajar di atas untuk memuat daftar siswa dan mendeteksi nama ganda.

⭐ GANESA PRO • EXCLUSIVE Paket Standard

🔒 Modul Layanan Eksklusif Ganesa PRO

Fitur ini merupakan bagian dari ekosistem premium terintegrasi Ganesa.ID untuk akselerasi bimbingan konseling dan pendampingan karakter sekolah.

💡

Instansi - saat ini berada pada tingkatan Standard Tier. Untuk membuka akses penuh ke modul berikut, silakan hubungi tim Ganesa.ID.

🌿

2. Perwalian (Guru Wali)

Pamong Asuh Karakter Holistik

✔ Portofolio Anak Asuh: Monitoring dinamika karakter per pamong.
✔ Log Sesi Berkala: Catatan observasi, disiplin & adab harian.
✔ Eskalasi Rujukan BK: Tiket rujukan kasus langsung ke Konselor.

🧭

3. 7 Jurus Layanan BK Hebat

Bimbingan & Konseling Klinis

✔ Deteksi Dini & Krisis: Skrining kerentanan psikologis siswa.
✔ Penjurusan Karir & Vokasi: Pemetaan RIASEC & minat bakat.
✔ Iklim TPPK & Buku Kasus: Penanganan perundungan & log sesi BK.

Ingin Mengaktifkan Lisensi PRO untuk Sekolah Anda?

Dapatkan akses penuh seluruh fitur, modul perwalian asuh, dan pendampingan implementasi oleh Tim Konselor Ganesa.ID.

💬 Hubungi Tim Ganesa.ID untuk Aktivasi Lisensi

🏫 Instansi Sekolah

*Prioritas utama jika kolom 'Nama Sekolah' ada di Excel.

👥 Kelas Fallback

*Digunakan jika kolom 'Kelas' di Excel bernilai '-' atau kosong.

📅 Tahun Ajaran

Ketik Nama Sekolah Baru Secara Manual

Ketik Nama Kelas Baru Secara Manual

Otomatis gunakan kolom Kelas dari Excel jika terisi

📊

Klik untuk memilih file Excel atau seret & lepas ke sini

Mendukung format laporan Ganesa.ID (.xlsx, .xls, .csv)

Gunakan skrip SQL berikut di Supabase SQL Editor untuk memastikan tabel public.user_profiles dan fungsi public.register_school_member dengan flag SECURITY DEFINER terpasang dengan benar:

-- ====================================================================
-- LENTERA.ID - PRODUCTION DATABASE SCHEMA, RBAC, RLS & RPC ENGINE
-- ====================================================================

-- 1. TABEL USER PROFILES & CONSTRAINT
CREATE TABLE IF NOT EXISTS public.user_profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT NOT NULL,
  full_name TEXT NOT NULL,
  role TEXT NOT NULL CHECK (role IN ('super_admin', 'admin_sekolah', 'guru_bk', 'admin')),
  school_id UUID REFERENCES public.schools(id) ON DELETE SET NULL,
  assigned_classes JSONB DEFAULT '[]'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE public.user_profiles DROP CONSTRAINT IF EXISTS user_profiles_role_check;
ALTER TABLE public.user_profiles ADD CONSTRAINT user_profiles_role_check CHECK (role IN ('super_admin', 'admin_sekolah', 'guru_bk', 'admin'));

-- 2. FUNGSI HELPER RBAC ANTI-REKURSI RLS (SECURITY DEFINER)
-- Menghindari infinite recursion error (42P17) saat RLS mengevaluasi policy tabel user_profiles
CREATE OR REPLACE FUNCTION public.get_my_role()
RETURNS TEXT
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT COALESCE((SELECT role FROM public.user_profiles WHERE id = auth.uid()), '');
$$;

CREATE OR REPLACE FUNCTION public.get_my_school_id()
RETURNS UUID
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT school_id FROM public.user_profiles WHERE id = auth.uid();
$$;

-- 3. RPC: register_school_member (GOTRUE V2 COMPLIANT: auth.users + auth.identities)
CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA extensions;

CREATE OR REPLACE FUNCTION public.register_school_member(
  target_email TEXT,
  target_password TEXT,
  target_name TEXT,
  target_role TEXT,
  target_school_id UUID DEFAULT NULL
)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, auth, extensions
AS $$
DECLARE
  new_user_id UUID;
BEGIN
  -- 1. Insert ke auth.users
  INSERT INTO auth.users (
    instance_id, id, aud, role, email, encrypted_password,
    email_confirmed_at, raw_app_meta_data, raw_user_meta_data,
    created_at, updated_at
  ) VALUES (
    '00000000-0000-0000-0000-000000000000',
    gen_random_uuid(),
    'authenticated',
    'authenticated',
    target_email,
    extensions.crypt(target_password, extensions.gen_salt('bf', 10)),
    NOW(),
    jsonb_build_object('provider', 'email', 'providers', array['email']),
    jsonb_build_object('full_name', target_name, 'role', target_role),
    NOW(),
    NOW()
  )
  RETURNING id INTO new_user_id;

  -- 2. Insert ke auth.identities
  INSERT INTO auth.identities (
    id, user_id, identity_data, provider, provider_id, last_sign_in_at, created_at, updated_at
  ) VALUES (
    new_user_id,
    new_user_id,
    jsonb_build_object('sub', new_user_id::text, 'email', target_email),
    'email',
    target_email,
    NOW(),
    NOW(),
    NOW()
  );

  -- 3. Insert ke public.user_profiles
  INSERT INTO public.user_profiles (id, email, full_name, role, school_id)
  VALUES (new_user_id, target_email, target_name, target_role, target_school_id)
  ON CONFLICT (id) DO UPDATE SET
    email = EXCLUDED.email,
    full_name = EXCLUDED.full_name,
    role = EXCLUDED.role,
    school_id = EXCLUDED.school_id;

  RETURN jsonb_build_object('success', true, 'user_id', new_user_id);
EXCEPTION WHEN OTHERS THEN
  RETURN jsonb_build_object('success', false, 'error', SQLERRM);
END;
$$;

-- 4. RPC: admin_update_user & admin_delete_user
CREATE OR REPLACE FUNCTION public.admin_update_user(
  target_user_id UUID,
  new_full_name TEXT,
  new_role TEXT,
  new_school_id UUID DEFAULT NULL
)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, auth
AS $$
BEGIN
  IF public.get_my_role() != 'super_admin' THEN
    RAISE EXCEPTION 'Akses ditolak: Hanya Super Admin yang berhak memperbarui akun.';
  END IF;

  UPDATE public.user_profiles
  SET 
    full_name = new_full_name,
    role = new_role,
    school_id = CASE WHEN new_role = 'super_admin' THEN NULL ELSE new_school_id END,
    updated_at = NOW()
  WHERE id = target_user_id;

  UPDATE auth.users
  SET raw_user_meta_data = jsonb_build_object('full_name', new_full_name, 'role', new_role)
  WHERE id = target_user_id;

  RETURN jsonb_build_object('success', true);
END;
$$;

CREATE OR REPLACE FUNCTION public.admin_delete_user(
  target_user_id UUID
)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, auth
AS $$
BEGIN
  IF public.get_my_role() != 'super_admin' THEN
    RAISE EXCEPTION 'Akses ditolak: Hanya Super Admin yang berhak menghapus akun.';
  END IF;

  IF auth.uid() = target_user_id THEN
    RAISE EXCEPTION 'Tidak diperbolehkan menghapus akun yang sedang aktif digunakan.';
  END IF;

  DELETE FROM public.user_profiles WHERE id = target_user_id;
  DELETE FROM auth.identities WHERE user_id = target_user_id;
  DELETE FROM auth.users WHERE id = target_user_id;

  RETURN jsonb_build_object('success', true);
END;
$$;

-- 4b. RPC: delete_school_cascade
CREATE OR REPLACE FUNCTION public.delete_school_cascade(
  target_school_id UUID
)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  IF public.get_my_role() != 'super_admin' THEN
    RAISE EXCEPTION 'Akses ditolak: Hanya Super Admin yang berhak menghapus instansi sekolah.';
  END IF;

  -- 1. Hapus riwayat asesmen terkait sekolah
  DELETE FROM public.assessment_records WHERE school_id = target_school_id;
  -- 2. Hapus siswa terkait sekolah
  DELETE FROM public.students WHERE school_id = target_school_id;
  -- 3. Hapus kelas/rombel terkait sekolah
  DELETE FROM public.classes WHERE school_id = target_school_id;
  -- 4. Lepas relasi sekolah dari user_profiles (set school_id = NULL)
  UPDATE public.user_profiles SET school_id = NULL WHERE school_id = target_school_id;
  -- 5. Hapus instansi sekolah
  DELETE FROM public.schools WHERE id = target_school_id;

  RETURN jsonb_build_object('success', true);
EXCEPTION WHEN OTHERS THEN
  RETURN jsonb_build_object('success', false, 'error', SQLERRM);
END;
$$;

-- 5. ROW LEVEL SECURITY (RLS) POLICIES & MULTI-TENANT ISOLATION
ALTER TABLE public.user_profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.schools ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.classes ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.students ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.assessment_records ENABLE ROW LEVEL SECURITY;

-- Policy user_profiles
DROP POLICY IF EXISTS "Users can read own profile or super_admin all" ON public.user_profiles;
CREATE POLICY "Users can read own profile or super_admin all" ON public.user_profiles
  FOR SELECT USING (auth.uid() = id OR public.get_my_role() = 'super_admin');

DROP POLICY IF EXISTS "Super admin can manage user_profiles" ON public.user_profiles;
CREATE POLICY "Super admin can manage user_profiles" ON public.user_profiles
  FOR ALL USING (public.get_my_role() = 'super_admin');

-- Policy schools
DROP POLICY IF EXISTS "Tenant can read assigned school or super_admin all" ON public.schools;
CREATE POLICY "Tenant can read assigned school or super_admin all" ON public.schools
  FOR SELECT USING (public.get_my_role() = 'super_admin' OR id = public.get_my_school_id());

DROP POLICY IF EXISTS "Super admin can manage schools" ON public.schools;
CREATE POLICY "Super admin can manage schools" ON public.schools
  FOR ALL USING (public.get_my_role() = 'super_admin');

-- Policy classes
DROP POLICY IF EXISTS "Tenant can read assigned classes" ON public.classes;
CREATE POLICY "Tenant can read assigned classes" ON public.classes
  FOR SELECT USING (public.get_my_role() = 'super_admin' OR school_id = public.get_my_school_id());

DROP POLICY IF EXISTS "Super admin and school admin can manage classes" ON public.classes;
CREATE POLICY "Super admin and school admin can manage classes" ON public.classes
  FOR ALL USING (public.get_my_role() = 'super_admin' OR (public.get_my_role() = 'admin_sekolah' AND school_id = public.get_my_school_id()));

-- Policy students
DROP POLICY IF EXISTS "Tenant can read assigned students" ON public.students;
CREATE POLICY "Tenant can read assigned students" ON public.students
  FOR SELECT USING (public.get_my_role() = 'super_admin' OR school_id = public.get_my_school_id());

DROP POLICY IF EXISTS "Super admin and school admin can manage students" ON public.students;
CREATE POLICY "Super admin and school admin can manage students" ON public.students
  FOR ALL USING (public.get_my_role() = 'super_admin' OR (public.get_my_role() = 'admin_sekolah' AND school_id = public.get_my_school_id()));

-- Policy assessment_records
DROP POLICY IF EXISTS "Tenant can read assigned assessment_records" ON public.assessment_records;
CREATE POLICY "Tenant can read assigned assessment_records" ON public.assessment_records
  FOR SELECT USING (public.get_my_role() = 'super_admin' OR school_id = public.get_my_school_id());

DROP POLICY IF EXISTS "Super admin and school admin can manage assessment_records" ON public.assessment_records;
CREATE POLICY "Super admin and school admin can manage assessment_records" ON public.assessment_records
  FOR ALL USING (public.get_my_role() = 'super_admin' OR (public.get_my_role() = 'admin_sekolah' AND school_id = public.get_my_school_id()));

Peserta Didik (Konseli) *

Tanggal Konseling *

Kategori Masalah *

Tingkat Urgensi / Prioritas *

Pokok Masalah / Topik Kasus *

Status Intervensi *

Nama Guru BK / Konselor Pembimbing *

Catatan & Fakta Kasus (Observasi / Keterangan Siswa) *

Kesepakatan & Rencana Tindak Lanjut (Solusi & Komitmen Siswa) *

🏫 Instansi Satuan Pendidikan:

🔒 Berkas Rahasia TPPK

1. Tanggal Kejadian *

2. Lokasi Kejadian *

🛡️ Data Identitas Korban

Pilih Siswa / Masukkan Nama Korban *

Kelas Korban *

Gender

⚠️ Data Terduga Pelaku

Nama Terduga Pelaku *

Kelas / Status Pelaku *

Saksi-Saksi yang Mengetahui (Opsional)

Kategori / Jenis Bentuk Kekerasan *

🥊 Kekerasan Fisik 🗣️ Perundungan Verbal 🧠 Relasional / Psikologis 🌐 Cyberbullying / Daring

Kronologi Kejadian (Fakta & Uraian Peristiwa) *

Tindakan Awal yang Sudah Dilakukan *

Status Alur Penanganan

Nama Petugas / Pelapor

Sudah Menghubungi & Menginformasikan Orang Tua / Wali Murid (SOP Wajib Permendikbud)

Belum Ada Data Siswa

Distribusi Gaya Belajar (VAK)

Distribusi Tipe Kepribadian (MBTI)

Distribusi Suasana Rumah Siswa

Distribusi Relasi Keluarga

Daftar Hasil Asesmen Peserta Didik

Belum Ada Siswa Dipilih

Pusat Pengaturan & Manajemen Terpusat

Profil & Identitas Resmi Sekolah

🏛️ Pratinjau Identitas & Titimangsa Rapor

Tambah Akun Guru & Staf Internal

Daftar Akun Guru & Staf Sekolah Aktif

SaaS Platform Analytics & Telemetry Engine

0

0

0

0

Tabel Utilisasi & Keaktifan Sekolah Mitra (Tenant Health)

Daftar Instansi Sekolah Mitra Terdaftar

Pendaftaran Akun Pengguna

Daftar Akun Pengguna Terdaftar

Distribusi Anak Asuh (Pamong Assignment)

Panel Penugasan Guru BK & Pamong / Wali

📋 Rekapitulasi Plotting Anak Asuh per Guru Pamong

Impor Data Asesmen Baru

📋 Log Riwayat Unggahan Berkas & Rombel

Pembersihan & Reset Data Kelas

Kelola Data Siswa & Deteksi Duplikasi

📄 Format Penandatangan & Teks Narasi Rapor Cetak A4

Pratinjau Format Tanda Tangan & Footer Rapor

🖼️ Kustomisasi Tampilan Halaman Login (Branding Login Page)

Mini Pratinjau Halaman Login (Live Mockup)

Profil & Preferensi Petugas BK

📝 Identitas & Legalitas Petugas

👁️ Pratinjau Tanda Tangan Dokumen Cetak

Ganesa .ID

Welcome back!