Stage 6: File Organization (Folders + Visibility)

Goal

Allow files to be organized in folders and have visibility controls (private, project, or organization-wide).

Key Design Decisions

1. Unlimited folder nesting: Folders can contain folders to any depth
2. Folders are project-scoped: Folder structure exists within a project
3. Three visibility levels: Private (creator only), Project (members), Organization (everyone)
4. Default visibility: New files default to “project” visibility

Data Model Changes

New Table: folders

CREATE TABLE folders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    parent_folder_id UUID REFERENCES folders(id) ON DELETE CASCADE,

    name VARCHAR(255) NOT NULL,
    color VARCHAR(7),  -- Optional color coding

    created_by_user_id UUID REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- Computed path for breadcrumbs (denormalized for performance)
    path_names TEXT[],  -- ['Root Folder', 'Subfolder', 'Current']
    depth INTEGER DEFAULT 0,

    CONSTRAINT unique_folder_name_in_parent UNIQUE (project_id, parent_folder_id, name)
);

CREATE INDEX idx_folders_project ON folders(project_id);
CREATE INDEX idx_folders_parent ON folders(parent_folder_id);

Modified: files Table

ALTER TABLE files ADD COLUMN folder_id UUID REFERENCES folders(id) ON DELETE SET NULL;
ALTER TABLE files ADD COLUMN visibility VARCHAR(20) NOT NULL DEFAULT 'project';

ALTER TABLE files ADD CONSTRAINT valid_visibility
    CHECK (visibility IN ('private', 'project', 'organization'));

CREATE INDEX idx_files_folder ON files(folder_id);
CREATE INDEX idx_files_visibility ON files(visibility);

SQLAlchemy Models

class Folder(Base):
    __tablename__ = "folders"

    id = Column(UUID, primary_key=True, default=uuid.uuid4)
    project_id = Column(UUID, ForeignKey("projects.id", ondelete="CASCADE"), nullable=False)
    parent_folder_id = Column(UUID, ForeignKey("folders.id", ondelete="CASCADE"))

    name = Column(String(255), nullable=False)
    color = Column(String(7))

    created_by_user_id = Column(UUID, ForeignKey("users.id"))
    created_at = Column(DateTime, default=datetime.utcnow)

    path_names = Column(ARRAY(String))
    depth = Column(Integer, default=0)

    # Relationships
    project = relationship("Project", back_populates="folders")
    parent = relationship("Folder", remote_side=[id], back_populates="children")
    children = relationship("Folder", back_populates="parent", cascade="all, delete-orphan")
    files = relationship("File", back_populates="folder")
    created_by = relationship("User")


class File(Base):
    # ... existing ...
    folder_id = Column(UUID, ForeignKey("folders.id", ondelete="SET NULL"))
    visibility = Column(String(20), nullable=False, default='project')

    folder = relationship("Folder", back_populates="files")

Visibility Access Logic

# api/src/deps.py

class Visibility(str, Enum):
    PRIVATE = "private"
    PROJECT = "project"
    ORGANIZATION = "organization"


def can_view_file(user: User, file: File, db: Session) -> bool:
    """
    Check if user can view a file based on visibility.

    Visibility rules:
    - private: only creator can view
    - project: project members can view
    - organization: all org members can view
    """
    # Always: must be same organization
    if file.organization_id != user.organization_id:
        return False

    # Org admins can always view
    if user.role in ('owner', 'admin'):
        return True

    # Creator can always view their own files
    if file.created_by_user_id == user.id:
        return True

    match file.visibility:
        case Visibility.PRIVATE:
            return False  # Only creator (checked above)

        case Visibility.PROJECT:
            # Check project membership
            membership = db.query(ProjectMembership).filter(
                ProjectMembership.project_id == file.project_id,
                ProjectMembership.user_id == user.id
            ).first()
            return membership is not None

        case Visibility.ORGANIZATION:
            return True  # All org members

    return False


def filter_files_by_visibility(
    query,
    user: User,
    db: Session
):
    """
    Filter a file query to only include files the user can view.
    """
    # Org admins see everything
    if user.role in ('owner', 'admin'):
        return query

    # Get user's project memberships
    project_ids = db.query(ProjectMembership.project_id).filter(
        ProjectMembership.user_id == user.id
    ).subquery()

    return query.filter(
        or_(
            # Own files (any visibility)
            File.created_by_user_id == user.id,
            # Organization-wide files
            File.visibility == Visibility.ORGANIZATION,
            # Project files where user is a member
            and_(
                File.visibility == Visibility.PROJECT,
                File.project_id.in_(project_ids)
            )
        )
    )

API Changes

New Endpoints: Folders

POST   /projects/{project_id}/folders
       - Body: { name, parent_folder_id?, color? }
       - Creates folder in project
       - Requires: project editor access

GET    /projects/{project_id}/folders
       - Lists folder tree for project
       - Returns: nested structure or flat with parent_id

GET    /projects/{project_id}/folders/{folder_id}
       - Get folder details + immediate contents

PUT    /projects/{project_id}/folders/{folder_id}
       - Update folder (rename, move, recolor)
       - Body: { name?, parent_folder_id?, color? }
       - Requires: project editor access

DELETE /projects/{project_id}/folders/{folder_id}
       - Delete folder
       - Files in folder: moved to parent (or root if no parent)
       - Subfolders: recursively deleted
       - Requires: project editor access

Modified Endpoints

GET /files

@router.get("/files")
async def list_files(
    project_id: UUID = Query(None),
    folder_id: UUID = Query(None),  # NEW: filter by folder
    visibility: Visibility = Query(None),  # NEW: filter by visibility
    include_subfolders: bool = Query(False),  # NEW: recursive
    auth: AuthContext = Depends(get_auth),
    db: Session = Depends(get_db)
):
    query = db.query(File).filter(File.organization_id == auth.organization_id)

    # Apply visibility filter
    user = db.query(User).filter(User.id == auth.user_id).first()
    query = filter_files_by_visibility(query, user, db)

    if project_id:
        query = query.filter(File.project_id == project_id)

    if folder_id:
        if include_subfolders:
            # Get all descendant folder IDs
            folder_ids = get_descendant_folder_ids(folder_id, db)
            folder_ids.append(folder_id)
            query = query.filter(File.folder_id.in_(folder_ids))
        else:
            query = query.filter(File.folder_id == folder_id)
    elif folder_id is None and not include_subfolders:
        # Root level only (no folder)
        query = query.filter(File.folder_id.is_(None))

    if visibility:
        query = query.filter(File.visibility == visibility)

    # ...

POST /files

class FileCreate(BaseModel):
    original_filename: str
    project_id: UUID
    folder_id: Optional[UUID] = None  # NEW
    visibility: Visibility = Visibility.PROJECT  # NEW

PUT /files/{id}

class FileUpdate(BaseModel):
    # ... existing ...
    folder_id: Optional[UUID] = None  # NEW: move to folder
    visibility: Optional[Visibility] = None  # NEW: change visibility

Folder Tree Query

def get_folder_tree(project_id: UUID, db: Session) -> List[dict]:
    """
    Get folder tree using recursive CTE.
    """
    query = text("""
        WITH RECURSIVE folder_tree AS (
            -- Base case: root folders
            SELECT
                id, name, parent_folder_id, color, depth,
                ARRAY[name] as path,
                ARRAY[id] as id_path
            FROM folders
            WHERE project_id = :project_id AND parent_folder_id IS NULL

            UNION ALL

            -- Recursive case: child folders
            SELECT
                f.id, f.name, f.parent_folder_id, f.color, f.depth,
                ft.path || f.name,
                ft.id_path || f.id
            FROM folders f
            JOIN folder_tree ft ON f.parent_folder_id = ft.id
        )
        SELECT * FROM folder_tree ORDER BY path;
    """)

    result = db.execute(query, {"project_id": str(project_id)})
    return [dict(row) for row in result]

Frontend Changes

Folder Tree Component

// components/FolderTree.jsx

function FolderTree({ projectId, selectedFolderId, onSelectFolder }) {
  const [folders, setFolders] = useState([]);
  const [expanded, setExpanded] = useState(new Set());

  useEffect(() => {
    loadFolders();
  }, [projectId]);

  const loadFolders = async () => {
    const data = await api.get(`/projects/${projectId}/folders`);
    setFolders(buildTree(data));
  };

  const buildTree = (flatFolders) => {
    const map = new Map();
    const roots = [];

    flatFolders.forEach(f => map.set(f.id, { ...f, children: [] }));
    flatFolders.forEach(f => {
      if (f.parent_folder_id) {
        map.get(f.parent_folder_id)?.children.push(map.get(f.id));
      } else {
        roots.push(map.get(f.id));
      }
    });

    return roots;
  };

  const toggleExpand = (folderId) => {
    const next = new Set(expanded);
    if (next.has(folderId)) {
      next.delete(folderId);
    } else {
      next.add(folderId);
    }
    setExpanded(next);
  };

  const renderFolder = (folder, depth = 0) => (
    <div key={folder.id}>
      <button
        onClick={() => onSelectFolder(folder.id)}
        className={cn(
          "w-full flex items-center gap-2 px-2 py-1 rounded text-sm hover:bg-muted",
          selectedFolderId === folder.id && "bg-primary/10"
        )}
        style={{ paddingLeft: `${depth * 16 + 8}px` }}
      >
        {folder.children.length > 0 && (
          <button onClick={(e) => { e.stopPropagation(); toggleExpand(folder.id); }}>
            {expanded.has(folder.id) ? <ChevronDown className="h-4 w-4" /> : <ChevronRight className="h-4 w-4" />}
          </button>
        )}
        <Folder className="h-4 w-4" style={{ color: folder.color || '#6B7280' }} />
        <span className="truncate">{folder.name}</span>
      </button>
      {expanded.has(folder.id) && folder.children.map(child => renderFolder(child, depth + 1))}
    </div>
  );

  return (
    <div className="space-y-1">
      <button
        onClick={() => onSelectFolder(null)}
        className={cn(
          "w-full flex items-center gap-2 px-2 py-1 rounded text-sm hover:bg-muted",
          selectedFolderId === null && "bg-primary/10"
        )}
      >
        <Home className="h-4 w-4" />
        <span>All Files</span>
      </button>
      {folders.map(folder => renderFolder(folder))}
    </div>
  );
}

Files Page with Folders

// pages/Files.jsx

function Files() {
  const { currentProject } = useProjects();
  const [selectedFolder, setSelectedFolder] = useState(null);
  const [files, setFiles] = useState([]);

  return (
    <div className="flex h-full">
      {/* Folder sidebar */}
      <div className="w-64 border-r p-4">
        <div className="flex items-center justify-between mb-4">
          <h3 className="font-medium">Folders</h3>
          <Button variant="ghost" size="sm" onClick={() => setShowNewFolder(true)}>
            <FolderPlus className="h-4 w-4" />
          </Button>
        </div>
        <FolderTree
          projectId={currentProject?.id}
          selectedFolderId={selectedFolder}
          onSelectFolder={setSelectedFolder}
        />
      </div>

      {/* File list */}
      <div className="flex-1 p-4">
        <Breadcrumbs folderId={selectedFolder} />
        <FileList
          projectId={currentProject?.id}
          folderId={selectedFolder}
        />
      </div>
    </div>
  );
}

Visibility Selector

// components/VisibilitySelector.jsx

function VisibilitySelector({ value, onChange }) {
  return (
    <Select value={value} onValueChange={onChange}>
      <SelectTrigger className="w-40">
        <SelectValue />
      </SelectTrigger>
      <SelectContent>
        <SelectItem value="private">
          <div className="flex items-center gap-2">
            <Lock className="h-4 w-4" />
            <span>Private</span>
          </div>
        </SelectItem>
        <SelectItem value="project">
          <div className="flex items-center gap-2">
            <Users className="h-4 w-4" />
            <span>Project</span>
          </div>
        </SelectItem>
        <SelectItem value="organization">
          <div className="flex items-center gap-2">
            <Building className="h-4 w-4" />
            <span>Organization</span>
          </div>
        </SelectItem>
      </SelectContent>
    </Select>
  );
}

File Row with Visibility Indicator

// components/FileRow.jsx

function FileRow({ file, showActions }) {
  const visibilityIcons = {
    private: <Lock className="h-3 w-3 text-muted-foreground" title="Private" />,
    project: <Users className="h-3 w-3 text-muted-foreground" title="Project visible" />,
    organization: <Building className="h-3 w-3 text-muted-foreground" title="Organization-wide" />,
  };

  return (
    <div className="flex items-center gap-4 p-3 border rounded hover:bg-muted/50">
      <FileAudio className="h-8 w-8 text-primary" />
      <div className="flex-1">
        <p className="font-medium">{file.original_filename}</p>
        <p className="text-sm text-muted-foreground">
          {formatDuration(file.duration_seconds)} • {formatDate(file.created_at)}
        </p>
      </div>
      {visibilityIcons[file.visibility]}
      {showActions && (
        <DropdownMenu>
          {/* ... actions ... */}
        </DropdownMenu>
      )}
    </div>
  );
}

Upload with Folder & Visibility

// pages/Upload.jsx

function Upload() {
  const { currentProject } = useProjects();
  const [selectedFolder, setSelectedFolder] = useState(null);
  const [visibility, setVisibility] = useState('project');

  const handleUpload = async (file) => {
    const response = await api.post('/files', {
      original_filename: file.name,
      project_id: currentProject.id,
      folder_id: selectedFolder,
      visibility: visibility,
    });
    // ...
  };

  return (
    <div>
      {/* ... file dropzone ... */}

      <div className="grid grid-cols-2 gap-4 mt-4">
        <div>
          <Label>Folder</Label>
          <FolderSelector
            projectId={currentProject?.id}
            value={selectedFolder}
            onChange={setSelectedFolder}
          />
        </div>
        <div>
          <Label>Visibility</Label>
          <VisibilitySelector value={visibility} onChange={setVisibility} />
        </div>
      </div>
    </div>
  );
}

Files to Create/Modify

Backend

• api/src/shared/db_models.py - Add Folder model, update File
• api/migrations/versions/xxx_add_folders_visibility.py - Migration
• api/src/routers/folders.py - New router
• api/src/routers/files.py - Add folder/visibility params
• api/src/deps.py - Add visibility access logic

Frontend

• client/src/components/FolderTree.jsx - New
• client/src/components/FolderSelector.jsx - New (dropdown variant)
• client/src/components/VisibilitySelector.jsx - New
• client/src/components/Breadcrumbs.jsx - New
• client/src/pages/Files.jsx - Add folder sidebar
• client/src/pages/Upload.jsx - Add folder/visibility options
• client/src/pages/FileDetails.jsx - Show/edit visibility

Demo Criteria

1. User opens Files page, sees folder tree in sidebar
2. User creates folder “Week 1 Interviews”
3. User creates subfolder “Day 1” inside “Week 1 Interviews”
4. User uploads a file, selecting “Day 1” as destination and “Project” visibility
5. File appears in the folder tree under Day 1
6. User changes file visibility to “Private”
7. Another project member logs in - they don’t see the private file
8. First user changes visibility to “Organization”
9. Users in other projects can now see the file
10. User drags file to different folder (or uses move action)