mirror of
https://github.com/renee-png/acmcc.git
synced 2026-06-21 09:50:01 +00:00
99 lines
4.1 KiB
SQL
99 lines
4.1 KiB
SQL
-- Avria Sign: in-house e-signature system
|
|
|
|
CREATE TABLE public.signature_envelopes (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID REFERENCES public.associations(id) ON DELETE SET NULL,
|
|
document_name TEXT NOT NULL,
|
|
document_url TEXT NOT NULL,
|
|
signed_document_url TEXT,
|
|
email_subject TEXT,
|
|
email_body TEXT,
|
|
status TEXT NOT NULL DEFAULT 'sent',
|
|
created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
sent_at TIMESTAMPTZ DEFAULT now(),
|
|
completed_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE public.signature_recipients (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
envelope_id UUID NOT NULL REFERENCES public.signature_envelopes(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
email TEXT NOT NULL,
|
|
signing_token UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
|
|
signing_order INT NOT NULL DEFAULT 1,
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
signature_data_url TEXT,
|
|
signature_method TEXT,
|
|
signed_at TIMESTAMPTZ,
|
|
signed_ip TEXT,
|
|
signed_user_agent TEXT,
|
|
user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE public.signature_events (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
envelope_id UUID NOT NULL REFERENCES public.signature_envelopes(id) ON DELETE CASCADE,
|
|
recipient_id UUID REFERENCES public.signature_recipients(id) ON DELETE CASCADE,
|
|
event_type TEXT NOT NULL,
|
|
details JSONB,
|
|
ip_address TEXT,
|
|
user_agent TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_signature_envelopes_association ON public.signature_envelopes(association_id);
|
|
CREATE INDEX idx_signature_envelopes_status ON public.signature_envelopes(status);
|
|
CREATE INDEX idx_signature_recipients_envelope ON public.signature_recipients(envelope_id);
|
|
CREATE INDEX idx_signature_recipients_token ON public.signature_recipients(signing_token);
|
|
CREATE INDEX idx_signature_recipients_email ON public.signature_recipients(email);
|
|
CREATE INDEX idx_signature_events_envelope ON public.signature_events(envelope_id);
|
|
|
|
ALTER TABLE public.signature_envelopes ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.signature_recipients ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.signature_events ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Staff (admin/manager) can manage all envelopes
|
|
CREATE POLICY "Staff manage envelopes" ON public.signature_envelopes
|
|
FOR ALL TO authenticated
|
|
USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
|
|
WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));
|
|
|
|
-- Recipients can view envelopes addressed to them (matching email or user_id)
|
|
CREATE POLICY "Recipients view their envelopes" ON public.signature_envelopes
|
|
FOR SELECT TO authenticated
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM public.signature_recipients r
|
|
WHERE r.envelope_id = signature_envelopes.id
|
|
AND (r.user_id = auth.uid() OR r.email = (SELECT email FROM auth.users WHERE id = auth.uid()))
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Staff manage recipients" ON public.signature_recipients
|
|
FOR ALL TO authenticated
|
|
USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'))
|
|
WITH CHECK (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));
|
|
|
|
CREATE POLICY "Recipients view themselves" ON public.signature_recipients
|
|
FOR SELECT TO authenticated
|
|
USING (user_id = auth.uid() OR email = (SELECT email FROM auth.users WHERE id = auth.uid()));
|
|
|
|
CREATE POLICY "Staff view events" ON public.signature_events
|
|
FOR SELECT TO authenticated
|
|
USING (public.has_role(auth.uid(), 'admin') OR public.has_role(auth.uid(), 'manager'));
|
|
|
|
CREATE POLICY "System inserts events" ON public.signature_events
|
|
FOR INSERT TO authenticated
|
|
WITH CHECK (true);
|
|
|
|
-- Trigger updated_at
|
|
CREATE TRIGGER trg_signature_envelopes_updated
|
|
BEFORE UPDATE ON public.signature_envelopes
|
|
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
|
|
|
|
-- Storage bucket for signed documents (re-use 'files' bucket but with prefix)
|
|
-- Use existing 'files' bucket; signed docs go under 'signature-envelopes/{envelope_id}/...'
|