Finance Data Schema

This document describes the data schema requirements for the Finance tab features at the project level.

Overview

The Finance tab tracks:

1. Budget Versions - Multiple versions of budget allocations over time
2. Monthly Allocations - Resource allocations (roles and counts) per month
3. Expenses - Additional expenses (claims, equipment, software, etc.)
4. Actual vs Estimated - Comparison of estimated costs vs actual spending

Current Schema (TypeScript Types)

The schema is already defined in /apps/web/src/types/project.ts:

BudgetVersion

interface BudgetVersion {
  id: string;
  version: string;                    // e.g., "v1.0", "v1.1"
  createdBy: string;                  // Person ID or name
  createdDate: Date;
  isCurrent: boolean;                 // Only one version should be current
  totalEstimated: number;             // Total estimated cost (can be calculated or stored)
  status: 'draft' | 'approved' | 'archived';
  monthlyAllocations: MonthlyAllocation[];
}

MonthlyAllocation

interface MonthlyAllocation {
  month: MonthString;                 // Format: "YYYY-MM" (e.g., "2025-10")
  allocations: RoleAllocation[];
}

interface RoleAllocation {
  role: string;                        // e.g., "senior-engineer", "engineer", "product-owner", "designer"
  count: number;                       // Number of people in this role for this month
}

BudgetExpense

interface BudgetExpense {
  id: string;
  type: 'claim' | 'equipment' | 'software' | 'other';
  description: string;
  amount: number;                     // In MYR
  date: Date;
  submittedBy: string;                // Person ID or name
  status: 'pending' | 'approved' | 'rejected';
}

ProjectBudget

interface ProjectBudget {
  versions: BudgetVersion[];
  expenses: BudgetExpense[];
}

Storage Location

The budget data is stored as part of the Project entity in Firestore:

• Collection: projects
• Document ID: projectKey (e.g., "PROJ")
• Field: budget (optional ProjectBudget object)

Data Flow

1. Budget Version Creation

Source: Excel sheet upload via "Ingest New Version" dialog

Process:

1. User uploads Excel file (.xlsx or .xls)
2. Backend parses Excel file to extract:
   • Monthly allocations (month, role, count)
   • Calculates total estimated cost
3. Creates new BudgetVersion:
   • Sets isCurrent: false for all existing versions
   • Sets isCurrent: true for new version
   • Generates version number (increment from latest)
   • Sets status: 'draft' initially
4. Updates project document: project.budget.versions.push(newVersion)

API Endpoint (to be implemented):

POST /api/projects/[projectKey]/budget/ingest
Content-Type: multipart/form-data
Body: { file: File }

2. Budget Version Management

Actions:

• Download: Export current version to Excel format
• Preview: Show budget allocations in table format
• Set Current: Mark a version as current (archives others)
• Approve: Change status from 'draft' to 'approved'
• Archive: Change status to 'archived'

API Endpoints (to be implemented):

GET    /api/projects/[projectKey]/budget/versions
GET    /api/projects/[projectKey]/budget/versions/[versionId]/download
PUT    /api/projects/[projectKey]/budget/versions/[versionId]
DELETE /api/projects/[projectKey]/budget/versions/[versionId]

3. Expense Management

Create Expense:

• User fills form: type, description, amount, date
• Backend creates BudgetExpense:
  • Generates unique ID
  • Sets submittedBy from authenticated user
  • Sets status: 'pending' initially
• Updates project document: project.budget.expenses.push(newExpense)

Update Expense Status:

• Admin/Manager can approve/reject expenses
• Updates status field

API Endpoints (to be implemented):

POST   /api/projects/[projectKey]/budget/expenses
GET    /api/projects/[projectKey]/budget/expenses
PUT    /api/projects/[projectKey]/budget/expenses/[expenseId]
DELETE /api/projects/[projectKey]/budget/expenses/[expenseId]
PUT    /api/projects/[projectKey]/budget/expenses/[expenseId]/status

4. Actual Cost Calculation

Current Implementation: Placeholder calculation

Future Implementation:

• Calculate actual resource costs from worklogs collection:
  • Filter worklogs by project
  • Group by month
  • Calculate hours per role
  • Multiply by role rates
• Combine with approved expenses
• Compare with estimated costs

Data Sources:

• worklogs collection: Time tracking data
• project.budget.expenses: Additional expenses
• project.budget.versions[].monthlyAllocations: Estimated costs

Role Rates Configuration

Current: Hardcoded in component (ROLE_RATES constant)

Future: Should be stored in:

• Option 1: Configuration collection (singleton document)
• Option 2: Per-project configuration
• Option 3: Global settings with project overrides

Recommended Structure:

interface RoleRate {
  role: string;
  label: string;
  rate: number;              // Monthly rate in MYR
  effectiveDate?: Date;     // When this rate becomes effective
}

Calculations

Total Estimated Cost

totalEstimated = sum(monthlyAllocations.map(month => 
  sum(month.allocations.map(alloc => 
    ROLE_RATES[alloc.role].rate * alloc.count
  ))
))

Actual Spent

actualResourceCost = calculateFromWorklogs(projectKey, months)
approvedExpenses = sum(expenses.filter(e => e.status === 'approved').map(e => e.amount))
totalActualSpent = actualResourceCost + approvedExpenses

Budget Utilization

budgetUtilization = (totalActualSpent / totalEstimated) * 100
isOverBudget = totalActualSpent > totalEstimated
variance = totalActualSpent - totalEstimated

Monthly Variance

monthlyEstimated = calculateMonthlyTotal(month)
monthlyActual = calculateFromWorklogs(projectKey, month.month)
monthlyVariance = monthlyActual - monthlyEstimated

Data Validation

BudgetVersion

• version must be unique within project
• Only one version can have isCurrent: true
• monthlyAllocations must have valid MonthString format
• monthlyAllocations[].allocations[].count must be >= 0

BudgetExpense

• amount must be > 0
• date must be valid date
• type must be one of: 'claim', 'equipment', 'software', 'other'
• status must be one of: 'pending', 'approved', 'rejected'

Migration Considerations

Existing Projects

• Projects without budget field should initialize with empty ProjectBudget:

  budget: {
    versions: [],
    expenses: []
  }

Version History

• Keep all versions (don't delete archived versions)
• Use isCurrent flag to identify active version
• Use status to filter/display versions

Security & Permissions

Read Access

• All authenticated users can view budget and expenses

Write Access

• Budget Versions: Admin, Manager, Project Owner
• Expenses: All authenticated users (can create their own)
• Expense Status: Admin, Manager (can approve/reject)

Firestore Rules

match /projects/{projectKey} {
  // Budget read access
  allow read: if request.auth != null;
  
  // Budget version write access
  allow write: if request.auth != null && 
    (get(/databases/$(database)/documents/people/$(request.auth.uid)).data.role in ['admin', 'manager']);
  
  // Expense create access
  allow create: if request.auth != null;
  
  // Expense update (status change) access
  allow update: if request.auth != null && 
    (get(/databases/$(database)/documents/people/$(request.auth.uid)).data.role in ['admin', 'manager']);
}

Future Enhancements

1. Budget Templates: Pre-defined budget templates for common project types
2. Budget Approval Workflow: Multi-step approval process
3. Budget Alerts: Notifications when approaching budget limits
4. Budget Forecasting: Predict future spending based on trends
5. Currency Support: Multi-currency budgets
6. Budget Categories: Categorize expenses (e.g., personnel, infrastructure, tools)
7. Integration with Accounting Systems: Sync with external accounting software
8. Budget Reports: Generate PDF/Excel reports with charts and analysis

Related Files

• Type Definitions: /apps/web/src/types/project.ts
• UI Component: /apps/web/src/app/projects/[projectKey]/components/finance-tab.tsx
• API Routes: (to be implemented in /apps/web/src/app/api/projects/[projectKey]/budget/)
• Database structure: Database structure
• Data overview: Data overview