[Feature]: Project & Milestone Schema Design

[SudiptaPaul-31]

🔍 Problem Statement

📖 Description

Design a robust PostgreSQL schema to support core entities of the freelance platform. The schema should cover users, projects, milestones, contracts, and disputes, with indexing strategies for efficient queries.

Expected Behavior

• Users: Store freelancer and client profiles with wallet addresses, authentication details, and role distinctions.
• Projects: Represent client-created projects with metadata (title, description, budget, deadlines).
• Milestones: Track project milestones with title, description, amount, due date, and status.
• Contracts: Link freelancers to projects, including terms, escrow details, and status.
• Disputes: Record disputes raised by clients or freelancers, with reason, evidence, and resolution status.

🛠 Technical Notes

• Schema Design:

  • Normalize tables to avoid redundancy.
  • Use foreign keys to enforce relationships (e.g., milestones → projects, contracts → users/projects).
  • Include timestamps (created_at, updated_at) for auditing.

• Indexing Strategy:

  • Index wallet addresses for fast authentication lookups.
  • Index project IDs and milestone IDs for efficient joins.
  • Consider composite indexes for queries involving user + project or project + milestone.
  • Use partial indexes for filtering active vs. completed contracts.

• Scalability Considerations:

  • Support pagination for large contract/milestone histories.
  • Optimize queries for dashboard views (freelancer and client).
  • Ensure schema supports future extensions (e.g., ratings, reviews, notifications).

✅ Acceptance Criteria

• Schema includes tables for users, projects, milestones, contracts, and disputes.
• Relationships are correctly defined with foreign keys.
• Indexes are applied to critical query paths (wallet, project, milestone, contract).
• Schema passes migration tests and integrates with backend APIs.
• npm run lint and npm run build succeed.
• No regression in authentication, wallet connection, or dashboard features.

📈 Expected Impact

High — Would significantly improve user experience

[drips-wave]

This issue has been added to the Stellar Wave Program and is worth 200 Points.

🧑‍💻 Interested in contributing? Apply to work on this issue on Drips Wave, earn points, and receive rewards.

Warning

Only applications submitted via the apply link above will be considered. Please do not apply by commenting on the issue or opening a PR directly.

🤠 Repo maintainers: You can manage this issue's Points and Complexity on your Maintainer Dashboard here. Please keep an eye on applications and respond quickly 💙

ℹ️ Learn more about Drips Wave

[Tola-byte]

@Tola-byte has applied to work on this issue as part of the Stellar Wave Program's 2nd wave.

I would like to do this.

ℹ️ Repo Maintainers: To accept this application, review their application or assign @Tola-byte to this issue.

[laryhills]

@laryhills has applied to work on this issue as part of the Stellar Wave Program's 2nd wave.

Hi, I’d like to apply for this issue! 🗄️

About Me
I’m a backend engineer with 4+ years of experience designing scalable database schemas for marketplace platforms.

Plan

• Design Users table: id (UUID PK), wallet_address (unique indexed), email, role (freelancer/client), authentication_hash, profile_data (JSONB), created_at, updated_at
• Create Projects table: id (UUID PK), client_id (FK users), title, description, budget, deadline, status (active/completed/cancelled), created_at, updated_at
• Build Milestones table: id (UUID PK), project_id (FK projects), title, description, amount, due_date, status (pending/completed/disputed), created_at, updated_at
• Design Contracts table: id (UUID PK), freelancer_id (FK users), project_id (FK projects), terms (JSONB), escrow_amount, escrow_status (locked/released/refunded), status (active/completed/terminated), created_at, updated_at
• Create Disputes table: id (UUID PK), contract_id (FK contracts), raised_by (FK users), reason, evidence (JSONB), resolution_status (open/resolved/dismissed), created_at, updated_at, resolved_at
• Implement indexing: wallet_address (auth lookup), project_id + status (active projects), milestone_id (efficient joins), contract_id + status (active contracts), user_id + role (dashboard filtering)
• Add composite indexes: (user_id, created_at) for history pagination, (project_id, milestone_id) for project details
• Use partial indexes: WHERE status=‘active’ for contract queries, WHERE status != ‘completed’ for active milestones
• Include audit fields: created_at, updated_at, soft_delete support (deleted_at nullable) if needed
• Optimize dashboard queries: design indices supporting freelancer earnings view, client project overview

ETA
6-7 hours

ℹ️ Repo Maintainers: To accept this application, review their application or assign @laryhills to this issue.

[Oluwatos94]

@Oluwatos94 has applied to work on this issue as part of the Stellar Wave Program's 2nd wave.

Hi, I would like to implement this task, I'm familiar with the codebase.

ℹ️ Repo Maintainers: To accept this application, review their application or assign @Oluwatos94 to this issue.

[Nacho1499]

@Nacho1499 has applied to work on this issue as part of the Stellar Wave Program's 2nd wave.

Hello,

please i like to apply for the issue “Design a robust PostgreSQL schema to support core entities of the freelance platform”. thanks

ℹ️ Repo Maintainers: To accept this application, review their application or assign @Nacho1499 to this issue.