mirror of
https://github.com/renee-png/acmcc.git
synced 2026-06-21 09:50:01 +00:00
48 lines
2.2 KiB
SQL
48 lines
2.2 KiB
SQL
-- Staging table for Buildium imports awaiting review
|
|
CREATE TABLE public.buildium_import_staging (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
batch_id uuid NOT NULL,
|
|
association_id uuid,
|
|
kind text NOT NULL CHECK (kind IN ('unit','owner','gl_account','ledger_entry')),
|
|
action text NOT NULL DEFAULT 'create' CHECK (action IN ('create','update','unchanged')),
|
|
external_id text,
|
|
match_id uuid,
|
|
summary text,
|
|
payload jsonb NOT NULL,
|
|
diff jsonb,
|
|
status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','approved','rejected','applied','failed')),
|
|
apply_error text,
|
|
applied_at timestamptz,
|
|
reviewed_by uuid,
|
|
reviewed_at timestamptz,
|
|
created_by uuid,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_buildium_staging_batch ON public.buildium_import_staging(batch_id);
|
|
CREATE INDEX idx_buildium_staging_status ON public.buildium_import_staging(status);
|
|
CREATE INDEX idx_buildium_staging_assoc ON public.buildium_import_staging(association_id);
|
|
|
|
ALTER TABLE public.buildium_import_staging ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Staff (admin/manager) can view, modify, and apply staged rows.
|
|
CREATE POLICY "Staff can view staged buildium imports"
|
|
ON public.buildium_import_staging FOR SELECT
|
|
USING (public.has_role(auth.uid(), 'admin'::public.app_role) OR public.has_role(auth.uid(), 'manager'::public.app_role));
|
|
|
|
CREATE POLICY "Staff can insert staged buildium imports"
|
|
ON public.buildium_import_staging FOR INSERT
|
|
WITH CHECK (public.has_role(auth.uid(), 'admin'::public.app_role) OR public.has_role(auth.uid(), 'manager'::public.app_role));
|
|
|
|
CREATE POLICY "Staff can update staged buildium imports"
|
|
ON public.buildium_import_staging FOR UPDATE
|
|
USING (public.has_role(auth.uid(), 'admin'::public.app_role) OR public.has_role(auth.uid(), 'manager'::public.app_role));
|
|
|
|
CREATE POLICY "Staff can delete staged buildium imports"
|
|
ON public.buildium_import_staging FOR DELETE
|
|
USING (public.has_role(auth.uid(), 'admin'::public.app_role) OR public.has_role(auth.uid(), 'manager'::public.app_role));
|
|
|
|
CREATE TRIGGER trg_buildium_import_staging_updated_at
|
|
BEFORE UPDATE ON public.buildium_import_staging
|
|
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column(); |