Stage 4: Projects Foundation

Goal

Introduce Projects as an organizational layer between Organization and Files/Reports. This enables better organization of work and sets the foundation for project-level access control.

Key Design Decisions

1. Projects in sidebar: Users see project list in sidebar, click to switch context
2. All content is project-scoped: Files, AnalysisRuns, MultiAnalyses belong to projects
3. Templates at both levels: Org-wide templates (shared) + project-specific templates
4. Migration: Existing data moves to a “Default Project” per organization

Data Model Changes

New Table: projects

CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,

    name VARCHAR(255) NOT NULL,
    description TEXT,
    color VARCHAR(7),  -- Hex color for UI (e.g., '#3B82F6')
    icon VARCHAR(50),  -- Icon identifier (e.g., 'folder', 'briefcase')

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

    is_default BOOLEAN DEFAULT FALSE,  -- One default project per org

    CONSTRAINT unique_default_per_org UNIQUE (organization_id, is_default) WHERE is_default = TRUE
);

CREATE INDEX idx_projects_org ON projects(organization_id);

Modified Tables

files

ALTER TABLE files ADD COLUMN project_id UUID REFERENCES projects(id) ON DELETE CASCADE;
CREATE INDEX idx_files_project ON files(project_id);

analysis_runs

ALTER TABLE analysis_runs ADD COLUMN project_id UUID REFERENCES projects(id) ON DELETE CASCADE;
CREATE INDEX idx_analysis_runs_project ON analysis_runs(project_id);

multi_analyses

ALTER TABLE multi_analyses ADD COLUMN project_id UUID REFERENCES projects(id) ON DELETE CASCADE;
CREATE INDEX idx_multi_analyses_project ON multi_analyses(project_id);

prompt_templates

-- Already has organization_id, add optional project_id
ALTER TABLE prompt_templates ADD COLUMN project_id UUID REFERENCES projects(id) ON DELETE CASCADE;
CREATE INDEX idx_templates_project ON prompt_templates(project_id);

-- NULL project_id = org-wide template
-- Non-NULL project_id = project-specific template

SQLAlchemy Models

class Project(Base):
    __tablename__ = "projects"

    id = Column(UUID, primary_key=True, default=uuid.uuid4)
    organization_id = Column(UUID, ForeignKey("organizations.id", ondelete="CASCADE"), nullable=False)

    name = Column(String(255), nullable=False)
    description = Column(Text)
    color = Column(String(7))
    icon = Column(String(50))

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

    is_default = Column(Boolean, default=False)

    # Relationships
    organization = relationship("Organization", back_populates="projects")
    created_by = relationship("User")
    files = relationship("File", back_populates="project")
    analysis_runs = relationship("AnalysisRun", back_populates="project")
    multi_analyses = relationship("MultiAnalysis", back_populates="project")
    prompt_templates = relationship("PromptTemplate", back_populates="project")

Update existing models:

class File(Base):
    # ... existing ...
    project_id = Column(UUID, ForeignKey("projects.id", ondelete="CASCADE"))
    project = relationship("Project", back_populates="files")

class AnalysisRun(Base):
    # ... existing ...
    project_id = Column(UUID, ForeignKey("projects.id", ondelete="CASCADE"))
    project = relationship("Project", back_populates="analysis_runs")

class MultiAnalysis(Base):
    # ... existing ...
    project_id = Column(UUID, ForeignKey("projects.id", ondelete="CASCADE"))
    project = relationship("Project", back_populates="multi_analyses")

class PromptTemplate(Base):
    # ... existing ...
    project_id = Column(UUID, ForeignKey("projects.id", ondelete="CASCADE"))  # NULL = org-wide
    project = relationship("Project", back_populates="prompt_templates")

Migration Strategy

Step 1: Add columns (nullable)

ALTER TABLE files ADD COLUMN project_id UUID REFERENCES projects(id);
ALTER TABLE analysis_runs ADD COLUMN project_id UUID REFERENCES projects(id);
ALTER TABLE multi_analyses ADD COLUMN project_id UUID REFERENCES projects(id);
ALTER TABLE prompt_templates ADD COLUMN project_id UUID REFERENCES projects(id);

Step 2: Create default projects

-- Create a default project for each organization
INSERT INTO projects (organization_id, name, description, is_default, created_at)
SELECT id, 'Default Project', 'Automatically created for existing content', TRUE, NOW()
FROM organizations;

Step 3: Migrate existing data

-- Move files to default project
UPDATE files f
SET project_id = p.id
FROM projects p
WHERE p.organization_id = f.organization_id AND p.is_default = TRUE;

-- Move analysis_runs to default project
UPDATE analysis_runs ar
SET project_id = p.id
FROM projects p
WHERE p.organization_id = ar.organization_id AND p.is_default = TRUE;

-- Move multi_analyses to default project
UPDATE multi_analyses ma
SET project_id = p.id
FROM projects p
WHERE p.organization_id = ma.organization_id AND p.is_default = TRUE;

-- Keep templates org-wide (project_id = NULL) for now

Step 4: Make project_id required (for files, analyses)

ALTER TABLE files ALTER COLUMN project_id SET NOT NULL;
ALTER TABLE analysis_runs ALTER COLUMN project_id SET NOT NULL;
ALTER TABLE multi_analyses ALTER COLUMN project_id SET NOT NULL;
-- prompt_templates.project_id stays nullable (NULL = org-wide)

API Changes

New Endpoints: Projects

POST   /projects
       - Body: { name, description?, color?, icon? }
       - Creates project in user's organization
       - Requires: admin role

GET    /projects
       - Lists all projects in organization
       - Returns: array with file counts, last activity

GET    /projects/{id}
       - Get project details
       - Returns: project with summary stats

PUT    /projects/{id}
       - Update project metadata
       - Requires: admin role

DELETE /projects/{id}
       - Delete project (cascades to all content)
       - Requires: admin role
       - Cannot delete default project

Modified Endpoints

POST /files

class FileCreate(BaseModel):
    original_filename: str
    project_id: UUID  # NEW: required
    # ...

GET /files

@router.get("/files")
async def list_files(
    project_id: UUID = Query(None),  # NEW: filter by project
    # ...
):
    query = db.query(File).filter(File.organization_id == auth.organization_id)
    if project_id:
        query = query.filter(File.project_id == project_id)
    # ...

POST /analyze/multi

class MultiAnalysisCreate(BaseModel):
    file_ids: List[UUID]
    project_id: UUID  # NEW: required
    # ...

GET /prompts

@router.get("/prompts")
async def list_prompts(
    project_id: UUID = Query(None),  # Filter by project
    include_org_wide: bool = Query(True),  # Include org-level templates
    # ...
):
    query = db.query(PromptTemplate).filter(
        PromptTemplate.organization_id == auth.organization_id
    )

    if project_id and include_org_wide:
        # Project-specific + org-wide
        query = query.filter(
            or_(
                PromptTemplate.project_id == project_id,
                PromptTemplate.project_id.is_(None)
            )
        )
    elif project_id:
        # Project-specific only
        query = query.filter(PromptTemplate.project_id == project_id)
    else:
        # Org-wide only
        query = query.filter(PromptTemplate.project_id.is_(None))
    # ...

Frontend Changes

Sidebar Projects Section

// components/layout/Sidebar.jsx

function Sidebar() {
  const { projects, currentProject, setCurrentProject } = useProjects();

  return (
    <aside className="w-64 border-r h-screen flex flex-col">
      {/* Logo */}
      <div className="p-4 border-b">
        <Logo />
      </div>

      {/* Projects */}
      <div className="p-4 border-b">
        <div className="flex items-center justify-between mb-2">
          <h3 className="text-sm font-medium text-muted-foreground">Projects</h3>
          <Button variant="ghost" size="sm" onClick={() => setShowNewProject(true)}>
            <Plus className="h-4 w-4" />
          </Button>
        </div>
        <div className="space-y-1">
          {projects.map(project => (
            <button
              key={project.id}
              onClick={() => setCurrentProject(project)}
              className={cn(
                "w-full flex items-center gap-2 px-2 py-1.5 rounded text-sm",
                currentProject?.id === project.id
                  ? "bg-primary/10 text-primary"
                  : "hover:bg-muted"
              )}
            >
              <div
                className="w-2 h-2 rounded-full"
                style={{ backgroundColor: project.color || '#6B7280' }}
              />
              <span className="truncate">{project.name}</span>
              <span className="ml-auto text-xs text-muted-foreground">
                {project.file_count}
              </span>
            </button>
          ))}
        </div>
      </div>

      {/* Navigation */}
      <nav className="flex-1 p-4">
        <NavLink to="/dashboard">Dashboard</NavLink>
        <NavLink to="/files">Files</NavLink>
        <NavLink to="/reports">Reports</NavLink>
        <NavLink to="/templates">Templates</NavLink>
      </nav>

      {/* ... */}
    </aside>
  );
}

Project Context Provider

// contexts/ProjectContext.jsx

const ProjectContext = createContext();

export function ProjectProvider({ children }) {
  const [projects, setProjects] = useState([]);
  const [currentProject, setCurrentProject] = useState(null);

  // Load projects on mount
  useEffect(() => {
    loadProjects();
  }, []);

  // Persist selected project in localStorage
  useEffect(() => {
    if (currentProject) {
      localStorage.setItem('currentProjectId', currentProject.id);
    }
  }, [currentProject]);

  // Restore selected project on load
  useEffect(() => {
    const savedId = localStorage.getItem('currentProjectId');
    if (savedId && projects.length > 0) {
      const saved = projects.find(p => p.id === savedId);
      setCurrentProject(saved || projects.find(p => p.is_default));
    } else if (projects.length > 0) {
      setCurrentProject(projects.find(p => p.is_default) || projects[0]);
    }
  }, [projects]);

  const loadProjects = async () => {
    const data = await api.get('/projects');
    setProjects(data);
  };

  return (
    <ProjectContext.Provider value={{
      projects,
      currentProject,
      setCurrentProject,
      refreshProjects: loadProjects
    }}>
      {children}
    </ProjectContext.Provider>
  );
}

export const useProjects = () => useContext(ProjectContext);

Update File Upload

// pages/Upload.jsx

function Upload() {
  const { currentProject } = useProjects();

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

  // ...
}

Update File List

// pages/Files.jsx

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

  useEffect(() => {
    if (currentProject) {
      loadFiles();
    }
  }, [currentProject]);

  const loadFiles = async () => {
    const data = await api.get(`/files?project_id=${currentProject.id}`);
    setFiles(data);
  };

  // ...
}

Templates with Project Scope

// pages/PromptTemplates.jsx

function PromptTemplates() {
  const { currentProject } = useProjects();
  const [templates, setTemplates] = useState([]);
  const [showOrgWide, setShowOrgWide] = useState(true);

  const loadTemplates = async () => {
    const params = new URLSearchParams({
      project_id: currentProject.id,
      include_org_wide: showOrgWide
    });
    const data = await api.get(`/prompts?${params}`);
    setTemplates(data);
  };

  return (
    <div>
      <div className="flex items-center gap-4 mb-4">
        <h1>Prompt Templates</h1>
        <label className="flex items-center gap-2 text-sm">
          <input
            type="checkbox"
            checked={showOrgWide}
            onChange={(e) => setShowOrgWide(e.target.checked)}
          />
          Include organization-wide templates
        </label>
      </div>

      <div className="space-y-2">
        {templates.map(template => (
          <TemplateCard
            key={template.id}
            template={template}
            isOrgWide={!template.project_id}
          />
        ))}
      </div>
    </div>
  );
}

Project Response Schema

class ProjectResponse(BaseModel):
    id: UUID
    name: str
    description: Optional[str]
    color: Optional[str]
    icon: Optional[str]
    is_default: bool
    created_at: datetime

    # Summary stats
    file_count: int
    analysis_count: int
    multi_analysis_count: int
    last_activity: Optional[datetime]

Files to Create/Modify

Backend

• api/src/shared/db_models.py - Add Project model, update relationships
• api/migrations/versions/xxx_add_projects.py - Migration with data migration
• api/src/routers/projects.py - New router
• api/src/routers/files.py - Add project_id parameter
• api/src/routers/analyses.py - Add project_id parameter
• api/src/routers/prompts.py - Add project_id filtering
• api/src/shared/models.py - Add Pydantic schemas

Frontend

• client/src/contexts/ProjectContext.jsx - New
• client/src/components/layout/Sidebar.jsx - Add projects section
• client/src/pages/Upload.jsx - Use current project
• client/src/pages/Files.jsx - Filter by project
• client/src/pages/PromptTemplates.jsx - Filter by project
• client/src/pages/MultiAnalysis.jsx - Use current project
• client/src/App.jsx - Wrap with ProjectProvider

Demo Criteria

1. User logs in, sees sidebar with “Projects” section
2. Default project is selected, showing existing files
3. User clicks ”+” to create new project “Q1 Interviews”
4. User clicks on “Q1 Interviews” to switch context
5. Files list is empty (new project)
6. User uploads a file - it appears in “Q1 Interviews”
7. User switches back to “Default Project” - original files are there, new file is not
8. User goes to Templates, sees org-wide templates + option to create project-specific ones
9. Refresh page - selected project persists