mirror of
https://github.com/renee-png/acmcc.git
synced 2026-06-21 01:40:01 +00:00
79 lines
2.8 KiB
SQL
79 lines
2.8 KiB
SQL
|
|
-- Public form templates (designed by admins)
|
|
CREATE TABLE public.public_form_templates (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID NOT NULL REFERENCES public.associations(id) ON DELETE CASCADE,
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
fields JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
settings JSONB DEFAULT '{}'::jsonb,
|
|
is_published BOOLEAN DEFAULT false,
|
|
slug TEXT UNIQUE,
|
|
require_auth BOOLEAN DEFAULT false,
|
|
allow_attachments BOOLEAN DEFAULT true,
|
|
created_by UUID,
|
|
created_at TIMESTAMPTZ DEFAULT now(),
|
|
updated_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
-- Public form submissions
|
|
CREATE TABLE public.public_form_submissions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
template_id UUID NOT NULL REFERENCES public.public_form_templates(id) ON DELETE CASCADE,
|
|
association_id UUID NOT NULL REFERENCES public.associations(id) ON DELETE CASCADE,
|
|
submitter_name TEXT,
|
|
submitter_email TEXT,
|
|
form_data JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
attachments JSONB DEFAULT '[]'::jsonb,
|
|
status TEXT DEFAULT 'pending',
|
|
created_at TIMESTAMPTZ DEFAULT now(),
|
|
updated_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
ALTER TABLE public.public_form_templates ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.public_form_submissions ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Staff can manage templates
|
|
CREATE POLICY "Staff can manage form templates"
|
|
ON public.public_form_templates FOR ALL TO authenticated
|
|
USING (true) WITH CHECK (true);
|
|
|
|
-- Staff can view submissions
|
|
CREATE POLICY "Staff can manage form submissions"
|
|
ON public.public_form_submissions FOR ALL TO authenticated
|
|
USING (true) WITH CHECK (true);
|
|
|
|
-- Anonymous users can view published templates
|
|
CREATE POLICY "Anyone can view published templates"
|
|
ON public.public_form_templates FOR SELECT TO anon
|
|
USING (is_published = true);
|
|
|
|
-- Anonymous users can submit forms
|
|
CREATE POLICY "Anyone can submit forms"
|
|
ON public.public_form_submissions FOR INSERT TO anon
|
|
WITH CHECK (true);
|
|
|
|
-- Triggers
|
|
CREATE TRIGGER update_public_form_templates_updated_at
|
|
BEFORE UPDATE ON public.public_form_templates
|
|
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
|
|
|
|
CREATE TRIGGER update_public_form_submissions_updated_at
|
|
BEFORE UPDATE ON public.public_form_submissions
|
|
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
|
|
|
|
-- Storage bucket for public form attachments
|
|
INSERT INTO storage.buckets (id, name, public) VALUES ('public-form-attachments', 'public-form-attachments', true);
|
|
|
|
CREATE POLICY "Anyone can upload form attachments"
|
|
ON storage.objects FOR INSERT TO anon
|
|
WITH CHECK (bucket_id = 'public-form-attachments');
|
|
|
|
CREATE POLICY "Anyone can view form attachments"
|
|
ON storage.objects FOR SELECT TO anon
|
|
USING (bucket_id = 'public-form-attachments');
|
|
|
|
CREATE POLICY "Auth users manage form attachments"
|
|
ON storage.objects FOR ALL TO authenticated
|
|
USING (bucket_id = 'public-form-attachments');
|