101 lines
3.4 KiB
PL/PgSQL
101 lines
3.4 KiB
PL/PgSQL
-- Monaco USA Portal 2026 - Document Folders
|
|
-- Adds hierarchical folder support for document organization
|
|
|
|
-- ============================================
|
|
-- DOCUMENT FOLDERS TABLE
|
|
-- ============================================
|
|
CREATE TABLE public.document_folders (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name TEXT NOT NULL,
|
|
parent_id UUID REFERENCES public.document_folders(id) ON DELETE CASCADE,
|
|
path TEXT, -- Full path for breadcrumb support
|
|
visibility TEXT NOT NULL DEFAULT 'members'
|
|
CHECK (visibility IN ('public', 'members', 'board', 'admin')),
|
|
created_by UUID REFERENCES public.members(id),
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
|
|
-- Ensure unique folder names within same parent
|
|
UNIQUE(name, parent_id)
|
|
);
|
|
|
|
-- Add updated_at trigger
|
|
CREATE TRIGGER document_folders_updated_at
|
|
BEFORE UPDATE ON public.document_folders
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at();
|
|
|
|
-- ============================================
|
|
-- ADD FOLDER_ID TO DOCUMENTS TABLE
|
|
-- ============================================
|
|
ALTER TABLE public.documents
|
|
ADD COLUMN folder_id UUID REFERENCES public.document_folders(id) ON DELETE SET NULL;
|
|
|
|
-- ============================================
|
|
-- PATH UPDATE TRIGGER
|
|
-- ============================================
|
|
CREATE OR REPLACE FUNCTION update_folder_path()
|
|
RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
parent_path TEXT;
|
|
BEGIN
|
|
IF NEW.parent_id IS NULL THEN
|
|
NEW.path = NEW.name;
|
|
ELSE
|
|
SELECT path INTO parent_path
|
|
FROM public.document_folders
|
|
WHERE id = NEW.parent_id;
|
|
|
|
NEW.path = parent_path || '/' || NEW.name;
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER folder_path_trigger
|
|
BEFORE INSERT OR UPDATE ON public.document_folders
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_folder_path();
|
|
|
|
-- ============================================
|
|
-- RLS POLICIES FOR FOLDERS
|
|
-- ============================================
|
|
ALTER TABLE public.document_folders ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Everyone can view folders based on visibility
|
|
CREATE POLICY "Folders visible based on visibility" ON public.document_folders
|
|
FOR SELECT USING (
|
|
visibility = 'public' OR
|
|
(visibility = 'members' AND auth.uid() IS NOT NULL) OR
|
|
(visibility = 'board' AND EXISTS (
|
|
SELECT 1 FROM public.members WHERE id = auth.uid() AND role IN ('board', 'admin')
|
|
)) OR
|
|
(visibility = 'admin' AND EXISTS (
|
|
SELECT 1 FROM public.members WHERE id = auth.uid() AND role = 'admin'
|
|
))
|
|
);
|
|
|
|
-- Board and admin can create folders
|
|
CREATE POLICY "Board/admin can create folders" ON public.document_folders
|
|
FOR INSERT WITH CHECK (
|
|
EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role IN ('board', 'admin'))
|
|
);
|
|
|
|
-- Board and admin can update folders
|
|
CREATE POLICY "Board/admin can update folders" ON public.document_folders
|
|
FOR UPDATE USING (
|
|
EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role IN ('board', 'admin'))
|
|
);
|
|
|
|
-- Only admin can delete folders
|
|
CREATE POLICY "Admin can delete folders" ON public.document_folders
|
|
FOR DELETE USING (
|
|
EXISTS (SELECT 1 FROM public.members WHERE id = auth.uid() AND role = 'admin')
|
|
);
|
|
|
|
-- ============================================
|
|
-- INDEX FOR FOLDER QUERIES
|
|
-- ============================================
|
|
CREATE INDEX idx_document_folders_parent ON public.document_folders(parent_id);
|
|
CREATE INDEX idx_documents_folder ON public.documents(folder_id);
|