acmcc/supabase/migrations/20260401030806_ffac7778-fb1f-48fa-85f3-3e1b4ccee959.sql

-- Company bank transactions table for the management company operating account
CREATE TABLE public.company_bank_transactions (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  date DATE NOT NULL DEFAULT CURRENT_DATE,
  transaction_type TEXT NOT NULL DEFAULT 'payment',
  description TEXT,
  reference_number TEXT,
  debit NUMERIC NOT NULL DEFAULT 0,
  credit NUMERIC NOT NULL DEFAULT 0,
  is_cleared BOOLEAN NOT NULL DEFAULT false,
  cleared_date DATE,
  category TEXT,
  qbo_id TEXT,
  qbo_sync_status TEXT DEFAULT 'pending',
  created_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE public.company_bank_transactions ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Admins can manage company bank transactions"
ON public.company_bank_transactions
FOR ALL
TO authenticated
USING (public.has_role(auth.uid(), 'admin'))
WITH CHECK (public.has_role(auth.uid(), 'admin'));

-- QBO sync log table
CREATE TABLE public.qbo_sync_log (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  sync_type TEXT NOT NULL,
  direction TEXT NOT NULL DEFAULT 'push',
  entity_type TEXT NOT NULL,
  entity_id TEXT,
  qbo_id TEXT,
  status TEXT NOT NULL DEFAULT 'pending',
  error_message TEXT,
  payload JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE public.qbo_sync_log ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Admins can view sync logs"
ON public.qbo_sync_log
FOR ALL
TO authenticated
USING (public.has_role(auth.uid(), 'admin'))
WITH CHECK (public.has_role(auth.uid(), 'admin'));