Project — Document Library

Features to Build

1. Database Tables

folders Table

-- Unique folder name within same parent and workspace
UNIQUE (workspace_id, parent_id, name)

documents Table

-- Unique filename within same folder and workspace
UNIQUE (workspace_id, folder_id, name)

RLS Policies (both tables)

folders:
  SELECT: workspace members can view all folders in their workspace
  INSERT: workspace members can create folders
  DELETE: uploader or workspace admin/owner can delete

documents:
  SELECT: workspace members can view all documents in their workspace
  INSERT: workspace members can insert document records
  DELETE: uploader (uploaded_by = auth.uid()) OR workspace admin/owner can delete

Run checklist: New Table · RLS Policies

2. Storage Bucket — `workspace-docs`

• Visibility: Private
• Allowed MIME types: image/jpeg, image/png, image/gif, application/pdf, application/msword, application/vnd.openxmlformats-officedocument.wordprocessingml.document, text/plain
• Max file size: 25 MB

File Path Structure

{workspace_id}/{folder_id_or_'root'}/{document_id}/{sanitized_filename}

RLS on storage.objects

SELECT: workspace member (check via workspace_members JOIN on workspace_id from path)
INSERT: workspace member — path must start with a workspace_id they belong to
DELETE: uploader (path contains their user folder) OR workspace admin

Run checklist: Storage Bucket

3. RPC: `create_folder`

CREATE OR REPLACE FUNCTION create_folder(
  p_workspace_id  uuid,
  p_name          text,
  p_parent_id     uuid DEFAULT NULL
)
RETURNS json
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
DECLARE
  v_user_id uuid := auth.uid();
  v_folder  folders%ROWTYPE;
BEGIN
  -- 1. Auth
  IF v_user_id IS NULL THEN
    RAISE EXCEPTION 'AUTH_REQUIRED: authentication required';
  END IF;

  -- 2. Validate name
  IF p_name IS NULL OR trim(p_name) = '' THEN
    RAISE EXCEPTION 'VALIDATION_ERROR: folder name is required';
  END IF;
  IF length(trim(p_name)) > 100 THEN
    RAISE EXCEPTION 'VALIDATION_ERROR: folder name must be 100 characters or fewer';
  END IF;

  -- 3. Workspace membership check
  IF NOT EXISTS (SELECT 1 FROM workspace_members WHERE workspace_id = p_workspace_id AND user_id = v_user_id) THEN
    RAISE EXCEPTION 'FORBIDDEN: you are not a member of this workspace';
  END IF;

  -- 4. Parent folder exists and belongs to same workspace (if provided)
  IF p_parent_id IS NOT NULL THEN
    IF NOT EXISTS (SELECT 1 FROM folders WHERE id = p_parent_id AND workspace_id = p_workspace_id) THEN
      RAISE EXCEPTION 'NOT_FOUND: parent folder not found';
    END IF;
  END IF;

  -- 5. Uniqueness check within same parent
  IF EXISTS (SELECT 1 FROM folders WHERE workspace_id = p_workspace_id AND parent_id IS NOT DISTINCT FROM p_parent_id AND name = trim(p_name)) THEN
    RAISE EXCEPTION 'DUPLICATE: a folder with this name already exists here';
  END IF;

  INSERT INTO folders (workspace_id, name, parent_id, created_by)
  VALUES (p_workspace_id, trim(p_name), p_parent_id, v_user_id)
  RETURNING * INTO v_folder;

  RETURN json_build_object('data', row_to_json(v_folder));
END;
$$;

Run checklist: RPC Function

4. React: Document Library Page

URL: /workspaces/{id}/docs

Layout

LEFT/TOP: Breadcrumb navigation
  Home / Marketing / 2024 Q1

CONTENT AREA: Two sections (in order)
  1. Folders list — each folder shows: icon, name, created by, created date, actions (delete)
  2. Files list — each file shows: file type icon, name, size, uploaded by, date, actions

HEADER ACTIONS:
  "Create Folder" button (all members)
  "Upload File" button (all members)

FOLDER ROW ACTIONS:
  Delete (visible to uploader + admins only)

FILE ROW ACTIONS:
  Download · Delete (visible to uploader + admins only)

States

TypeScript Interfaces

interface Folder {
  id: string;
  name: string;
  parent_id: string | null;
  created_by: string;
  created_at: string;
}

interface Document {
  id: string;
  folder_id: string | null;
  name: string;
  storage_path: string;
  file_size_bytes: number;
  mime_type: string;
  uploaded_by: string;
  created_at: string;
}

Run checklist: React Page Build · Loading States & Skeletons

5. Upload File Modal

FIELDS:
[ ] Drag-and-drop zone (or click to browse)
[ ] Shows: accepted types, max 25 MB

VALIDATION (before upload):
[ ] MIME type checked (not just extension)
[ ] File size checked: > 25 MB shows "File too large" immediately
[ ] Multiple files selectable (optional) — upload sequentially

UPLOAD FLOW:
[ ] Show progress bar per file during upload
[ ] On storage upload success: insert row into documents table
[ ] On success: "File uploaded." toast, file appears in list
[ ] On storage error: show error, do NOT insert document record
[ ] On DB insert error: delete the uploaded file from storage (cleanup)
[ ] Cancel button during upload: cancels the upload

DUPLICATE FILENAME:
[ ] If a file with the same name exists in this folder:
    "A file named '[name]' already exists here. Replace it?" → confirm → overwrite

Run checklist: Modals & Dialogs

6. Create Folder Modal

Fields: Folder Name (text, required, max 100 chars)

Behavior:
[ ] Calls create_folder RPC
[ ] Duplicate name: "A folder with this name already exists here." as field error
[ ] On success: folder appears in list, "Folder created." toast
[ ] Loading state on Create button

7. Download File

8. Delete File / Delete Folder

Delete File

1. Delete document record from database

2. Delete file from Supabase Storage

If storage delete fails: show error, restore DB record (or show warning about orphan)

Delete Folder

Run checklist: Delete & Destructive Actions

9. Breadcrumb Navigation

What You Should NOT Do

× Make the storage bucket public — documents contain workspace data
× Store signed download URLs in the database — generate at request time
× Delete the DB record without deleting the Storage file (orphaned file)
× Upload to Storage without inserting the document record (orphaned storage file)
× Validate file type by extension only — validate the actual MIME type
× Allow deleting a folder that still has content — prevent with a check
× Use sequential IDs in the storage path — use document UUID
× Skip the breadcrumb URL param — folder navigation must be shareable/bookmarkable

Checklists to Run (in order)

Done When