SQL setup & pengamanan (jalankan di Supabase SQL Editor)
-- 1. Tabel
create table if not exists topup_entries (
id uuid primary key default gen_random_uuid(),
tanggal date, jenis text, keterangan text,
agency text, brand text,
currency text default 'IDR', convert_to text, nominal numeric default 0,
rate numeric default 0, fee numeric default 0, fee_type text default 'nominal', bukti text,
deleted boolean default false, deleted_by text, deleted_at timestamptz, input_by text,
created_at timestamptz default now());
create table if not exists spend_entries (
id uuid primary key default gen_random_uuid(),
tanggal date, jenis text, keterangan text,
agency text, brand text,
currency text default 'IDR', convert_to text, nominal numeric default 0,
rate numeric default 0, bukti text,
deleted boolean default false, deleted_by text, deleted_at timestamptz, input_by text,
created_at timestamptz default now());
create table if not exists app_settings (id int primary key default 1, data jsonb);
create table if not exists app_users (
username text primary key, pass_hash text not null,
role text not null default 'staff', perms jsonb, tg_id text,
pass_changed_at timestamptz, created_at timestamptz default now());
create unique index if not exists app_users_tg_id_uniq on app_users (tg_id) where tg_id is not null;
-- untuk database lama: kolom pencatat kapan password terakhir diubah
alter table app_users add column if not exists pass_changed_at timestamptz;
create table if not exists activity_log (
id uuid primary key default gen_random_uuid(),
ts timestamptz default now(), username text, role text,
action text, detail text, snapshot jsonb);
create table if not exists requests (
id uuid primary key default gen_random_uuid(),
type text, kind text, entry_id text, summary text, snapshot jsonb,
requested_by text, requested_role text, status text default 'pending',
decided_by text, created_at timestamptz default now(), decided_at timestamptz,
tg_msgs jsonb, reason text);
create table if not exists backups (
id uuid primary key default gen_random_uuid(),
created_at timestamptz default now(), by text,
topup jsonb, spend jsonb, settings jsonb);
-- 2. PENGAMANAN — hapus kebijakan lama yang mengizinkan siapa pun mengakses database.
-- Setelah ini, database HANYA bisa diakses lewat server (service key).
drop policy if exists "anon all topup" on topup_entries;
drop policy if exists "anon all spend" on spend_entries;
drop policy if exists "anon all settings" on app_settings;
drop policy if exists "anon all users" on app_users;
drop policy if exists "anon all activity" on activity_log;
drop policy if exists "anon all requests" on requests;
drop policy if exists "anon all backups" on backups;
-- RLS aktif tanpa policy = tidak ada akses sama sekali untuk anon/authenticated.
-- Service key yang dipakai server memang mem-bypass RLS, jadi aplikasi tetap jalan.
alter table topup_entries enable row level security;
alter table spend_entries enable row level security;
alter table app_settings enable row level security;
alter table app_users enable row level security;
alter table activity_log enable row level security;
alter table requests enable row level security;
alter table backups enable row level security;
-- 3. Bucket foto bukti: boleh dibaca publik (URL gambar), tulis hanya lewat server.
insert into storage.buckets (id, name, public) values ('bukti','bukti',true)
on conflict (id) do nothing;
drop policy if exists "bukti insert" on storage.objects;
drop policy if exists "bukti update" on storage.objects;
drop policy if exists "bukti delete" on storage.objects;