Database Structure

This document describes where and how the database is defined in the gamuda-technology-jira-tracker project.

Database Type

Firestore (Google Cloud Firestore) - A NoSQL document database

Database Configuration

1. Client-Side Configuration

Location: /apps/web/src/lib/firebase.ts

• Initializes Firebase client SDK
• Configures Firestore for client-side usage
• Uses environment variables for configuration:
  • NEXT_PUBLIC_FIREBASE_API_KEY
  • NEXT_PUBLIC_FIREBASE_AUTH_DOMAIN
  • NEXT_PUBLIC_FIREBASE_PROJECT_ID
  • NEXT_PUBLIC_FIREBASE_STORAGE_BUCKET
  • NEXT_PUBLIC_FIREBASE_MESSAGING_SENDER_ID
  • NEXT_PUBLIC_FIREBASE_APP_ID
  • NEXT_PUBLIC_FIREBASE_MEASUREMENT_ID

Usage: Client-side components and hooks

2. Server-Side Configuration (Admin SDK)

Location: /apps/web/src/lib/firebase-admin.ts

• Initializes Firebase Admin SDK for server-side operations
• Supports multiple authentication methods:
  1. Service account JSON from environment variable (FIREBASE_SERVICE_ACCOUNT_JSON)
  2. Service account file path (FIREBASE_SERVICE_ACCOUNT_PATH)
  3. Default application credentials (on GCP)
• Configures Firestore settings:
  • ignoreUndefinedProperties: true - Allows undefined properties in documents

Usage: API routes, server-side operations, data migrations

3. Firebase Project Configuration

Location: /firebase.json

{
  "firestore": {
    "rules": "firestore.rules",
    "indexes": "firestore.indexes.json"
  }
}

Database Schema Definitions

Type Definitions

The database schema is defined through TypeScript interfaces in multiple locations:

1. Modular Type Definitions (Recommended)

Location: /apps/web/src/types/

Organized by domain:

• common.ts - Shared types and enums
• project.ts - Project entity and related types
• strategy.ts - Strategy entity and related types
• person.ts - Person/TeamMember entity
• jira.ts - Jira entities (Worklog, Issue)
• git.ts - Git entities (Commit, PullRequest)
• report.ts - Report entity
• reminder.ts - Reminder entity
• work-arrangement.ts - Work arrangement types
• config.ts - Configuration types
• api.ts - API response types
• index.ts - Barrel export

2. Legacy Type Definitions

Location: ❌ REMOVED - /apps/web/src/lib/firestore/models.ts

• Legacy file has been removed during cleanup
• All types now use modular definitions in /apps/web/src/types/

Firestore Collections

The following table lists all collections in the database, their document ID structure, types, and how they are updated:

Collection	Document ID	Type	Update Source	Update Method
projects	projectKey (e.g., "PROJ")	Project	Jira Sync + Manual API	Sync: /api/sync/projects or /api/sync/all Manual: PUT /api/projects/[projectKey]
strategies	Auto-generated UUID	Strategy	Manual API	POST /api/strategies PUT /api/strategies/[id] Sub-resources: /api/strategies/[id]/*
people	Email address or custom ID	Person	Jira Sync + Manual API	Sync: /api/sync/users Manual: PUT /api/users/[identifier] CSV Import: /api/sync/users/import-csv
issues	jiraIssueId	Issue	Jira Sync	/api/sync/jira /api/sync/all
worklogs	jiraWorklogId	Worklog	Jira Sync	/api/sync/jira /api/sync/all
commits	commitHash	Commit	Bitbucket Sync	/api/sync/all (includes Bitbucket)
pullRequests	${repoSlug}-${prId}	PullRequest	Bitbucket Sync	/api/sync/all (includes Bitbucket)
reports	Auto-generated UUID	Report	Manual API	POST /api/reports/generate
reminders	Auto-generated UUID	Reminder	System Generated	Auto-generated by system logic Manual: API routes (if implemented)
workArrangements	Auto-generated UUID	WorkArrangement	Slack Integration	Slack bot reactions → /api/slack/events
workArrangementQuotas	engineerId-year-week	WorkArrangementQuota	Slack Integration	Calculated from workArrangements
engineerMetrics	engineerEmail-period	Metrics object	Computed	Auto-calculated during sync via aggregateMetrics()
projectMetrics	projectKey-period	Metrics object	Computed	Auto-calculated during sync via aggregateMetrics()
syncHistory	Auto-generated UUID	Sync record	System Generated	Created after each sync operation
configuration	config (singleton)	Configuration	Manual API	Admin-only configuration updates
syncStatus	sync-status (singleton)	SyncStatus	System Generated	Updated during sync operations
slackConfiguration	slack-config (singleton)	SlackConfiguration	Manual API	/api/slack/config
budgets	projectKey (e.g., "PROJ")	Budget	Manual API	/api/projects/[projectKey]/budget/*

Detailed Collection Information

1. projects

• Document ID: projectKey (e.g., "PROJ", "MOBILE")
• Type: Project (from /apps/web/src/types/project.ts)
• Update Sources:
  • Jira Sync: Fetches project metadata from Jira API
    • Endpoint: /api/sync/projects (projects only)
    • Endpoint: /api/sync/all (comprehensive sync)
    • Preserves active field and Velocity-enhanced fields (uses merge)
  • Manual Updates: Via API
    • Endpoint: PUT /api/projects/[projectKey]
    • Updates Velocity fields: projectType, projectSubtype, status, checklistItems, quickLinks, etc.
    • Does NOT overwrite synced fields (uses merge strategy)
• Fields:
  • Synced from Jira: projectKey, projectName, description, leadAccountId, leadName, lastSynced
  • Manual/Velocity: projectType, projectSubtype, strategyId, owner, status, checklistItems, quickLinks, budget, teamAllocations
• Budget Data Structure (nested in budget field):
  • Type: ProjectBudget (from /apps/web/src/types/project.ts)
  • Structure:

    {
      versions: BudgetVersion[];    // Budget version history
      expenses: BudgetExpense[];     // Additional expenses
    }

  • BudgetVersion fields:
    • id: string - Unique version identifier
    • version: string - Version label (e.g., "v1.0", "v1.1")
    • createdBy: string - Person ID or name who created the version
    • createdDate: Date - When version was created
    • isCurrent: boolean - Only one version can be current
    • totalEstimated: number - Total estimated cost (can be calculated from allocations)
    • status: 'draft' | 'approved' | 'archived'
    • monthlyAllocations: MonthlyAllocation[] - Resource allocations per month
  • MonthlyAllocation structure:
    • month: MonthString - Format "YYYY-MM" (e.g., "2025-10")
    • allocations: RoleAllocation[] - Role-based allocations
      • role: string - Role identifier (e.g., "senior-engineer", "engineer")
      • count: number - Number of people in this role
  • BudgetExpense fields:
    • id: string - Unique expense identifier
    • type: 'claim' | 'equipment' | 'software' | 'other'
    • description: string - Expense description
    • amount: number - Amount in dollars
    • date: Date - Expense date
    • submittedBy: string - Person ID or name who submitted
    • status: 'pending' | 'approved' | 'rejected'
  • Update Sources:
    • Manual API: Created and updated via finance API routes
      • POST /api/projects/[projectKey]/budget/expenses - Create expense
      • PUT /api/projects/[projectKey]/budget/expenses/[expenseId] - Update expense
      • PUT /api/projects/[projectKey]/budget/expenses/[expenseId]/status - Approve/reject expense
      • GET /api/projects/[projectKey]/budget/versions - List versions
      • PUT /api/projects/[projectKey]/budget/versions/[versionId] - Update version
      • POST /api/projects/[projectKey]/budget/ingest - Upload Excel and create version
  • Related documentation: Finance data schema

2. strategies

• Document ID: Auto-generated UUID
• Type: Strategy (from /apps/web/src/types/strategy.ts)
• Update Sources:
  • Manual API Only: Created and updated via API routes
    • POST /api/strategies - Create new strategy
    • GET /api/strategies - List strategies
    • GET /api/strategies/[id] - Get strategy
    • PUT /api/strategies/[id] - Update strategy
    • DELETE /api/strategies/[id] - Delete strategy
  • Sub-resources (nested updates):
    • /api/strategies/[id]/project-slots - Manage project slots
    • /api/strategies/[id]/milestones - Manage milestones
    • /api/strategies/[id]/monthly-returns - Manage monthly returns
    • /api/strategies/[id]/documents - Manage documents
    • /api/strategies/[id]/ad-hoc-references - Manage ad-hoc project references

3. users / engineers

• Document ID: Firebase Auth UID or email
• Type: Person (from /apps/web/src/types/person.ts)
• Update Sources:
  • Jira Sync: Syncs user data from Jira
    • Endpoint: /api/sync/users
    • Maps Jira account IDs to internal user records
  • CSV Import: Bulk import from CSV
    • Endpoint: /api/sync/users/import-csv
  • Manual Updates: Via API
    • PUT /api/users/[identifier]/update - Update user details
    • POST /api/users/[identifier]/toggle-disabled - Enable/disable user

4. issues

• Document ID: jiraIssueId (Jira's internal issue ID)
• Type: Issue (from /apps/web/src/types/jira.ts)
• Update Sources:
  • Jira Sync Only: Fetched from Jira API
    • Endpoint: /api/sync/jira - Sync specific project
    • Endpoint: /api/sync/all - Comprehensive sync
    • Query: project = "PROJ" AND updated >= [date]
    • Fields synced: summary, status, assignee, reporter, created, updated, timespent, etc.
• Note: Issues are read-only from application perspective (only synced, never manually edited)

5. worklogs

• Document ID: jiraWorklogId (Jira's worklog ID)
• Type: Worklog (from /apps/web/src/types/jira.ts)
• Update Sources:
  • Jira Sync Only: Fetched from Jira API
    • Endpoint: /api/sync/jira - Sync specific project
    • Endpoint: /api/sync/all - Comprehensive sync
    • Fetched per issue: GET /rest/api/3/issue/{issueKey}/worklog
    • Filtered by date range
• Note: Worklogs are read-only from application perspective (only synced, never manually edited)

6. commits

• Document ID: commitHash (Git commit hash)
• Type: Commit (from /apps/web/src/types/git.ts)
• Update Sources:
  • Bitbucket Sync Only: Fetched from Bitbucket API
    • Endpoint: /api/sync/all (includes Bitbucket sync)
    • Fetched per repository with date range filter
    • Extracts Jira issue keys from commit messages
    • Optionally fetches diffstat (lines added/deleted) for recent commits
• Note: Commits are read-only from application perspective

7. pullRequests

• Document ID: ${repoSlug}-${prId} (e.g., "my-repo-123")
• Type: PullRequest (from /apps/web/src/types/git.ts)
• Update Sources:
  • Bitbucket Sync Only: Fetched from Bitbucket API
    • Endpoint: /api/sync/all (includes Bitbucket sync)
    • Fetched per repository
    • Extracts Jira issue keys from PR title/description
• Note: Pull requests are read-only from application perspective

8. reports

• Document ID: Auto-generated UUID
• Type: Report (from /apps/web/src/types/report.ts)
• Update Sources:
  • Manual API Only: Generated on-demand
    • Endpoint: POST /api/reports/generate
    • Generates reports with metrics aggregation
    • Status: generating → completed / failed

9. reminders

• Document ID: Auto-generated UUID
• Type: Reminder (from /apps/web/src/types/reminder.ts)
• Update Sources:
  • System Generated: Created by system logic
    • Checks for missing checklist items
    • Detects stalled projects
    • Identifies overdue items
  • Manual Resolution: Users can resolve reminders via UI/API

10. workArrangements

• Document ID: Auto-generated UUID
• Type: WorkArrangement (from /apps/web/src/types/work-arrangement.ts)
• Update Sources:
  • Slack Integration: Created from Slack bot reactions
    • Endpoint: /api/slack/events - Receives Slack events
    • Service: work-arrangement-service.ts
    • Users react to daily Slack messages with emojis (🏠 home, 🏢 office, 🏛️ alternate)
    • Automatically creates work arrangement records

11. workArrangementQuotas

• Document ID: engineerId-year-week (e.g., "user123-2025-47")
• Type: WorkArrangementQuota (from /apps/web/src/types/work-arrangement.ts)
• Update Sources:
  • Computed from workArrangements: Calculated weekly quotas
    • Aggregates work arrangements by engineer, week, and year
    • Tracks quota limits (e.g., 1 home day per week)
    • Updated when new arrangements are created

12. engineerMetrics

• Document ID: engineerEmail-period (e.g., "john@example.com-2025-01-15")
• Type: Metrics object (not a TypeScript interface, computed structure)
• Update Sources:
  • Computed During Sync: Auto-calculated by aggregateMetrics()
    • Called during /api/sync/all
    • Aggregates: hours logged, commits, PRs, issues completed, productivity scores
    • Period: Daily metrics (one document per engineer per day)
    • Excludes disabled users

13. projectMetrics

• Document ID: projectKey-period (e.g., "PROJ-2025-01-15")
• Type: Metrics object (not a TypeScript interface, computed structure)
• Update Sources:
  • Computed During Sync: Auto-calculated by aggregateMetrics()
    • Called during /api/sync/all
    • Aggregates: total hours, issues (open/done), active engineers, health scores
    • Period: Daily metrics (one document per project per day)

14. syncHistory

• Document ID: Auto-generated UUID
• Type: Sync record object
• Update Sources:
  • System Generated: Created after each sync operation
    • Records sync batch ID, type, status, statistics
    • Endpoint: /api/sync/history - Query sync history

15. configuration (Singleton)

• Document ID: config (single document)
• Type: Configuration (from /apps/web/src/types/config.ts)
• Update Sources:
  • Manual API Only: Admin configuration
    • Stores: Jira base URL, project keys, Bitbucket workspace, sync schedule, email mappings, anomaly thresholds

16. syncStatus (Singleton)

• Document ID: sync-status (single document)
• Type: SyncStatus (from /apps/web/src/types/config.ts)
• Update Sources:
  • System Generated: Updated during sync operations
    • Tracks: last sync times, sync status (idle/syncing/error), sync history
    • Endpoint: /api/sync/status - Get current sync status

17. slackConfiguration (Singleton)

• Document ID: slack-config (single document)
• Type: SlackConfiguration (from /apps/web/src/types/config.ts)
• Update Sources:
  • Manual API Only: Slack integration configuration
    • Endpoint: /api/slack/config
    • Stores: bot token, signing secret, channel ID, emoji mappings, quota settings

18. budgets

• Document ID: projectKey (e.g., "PROJ", "MOBILE")
• Type: Budget (from /apps/web/src/types/budget.ts)
• Update Sources:
  • Manual API Only: Created and updated via finance API routes
    • GET /api/projects/[projectKey]/budget - Get budget
    • POST /api/projects/[projectKey]/budget/expenses - Create expense
    • GET /api/projects/[projectKey]/budget/expenses - List expenses
    • PUT /api/projects/[projectKey]/budget/expenses/[expenseId] - Update expense
    • DELETE /api/projects/[projectKey]/budget/expenses/[expenseId] - Delete expense
    • PUT /api/projects/[projectKey]/budget/expenses/[expenseId]/status - Approve/reject expense
    • GET /api/projects/[projectKey]/budget/versions - List versions
    • PUT /api/projects/[projectKey]/budget/versions/[versionId] - Update version
    • DELETE /api/projects/[projectKey]/budget/versions/[versionId] - Delete version
    • POST /api/projects/[projectKey]/budget/ingest - Upload Excel and create version
• Fields:
  • projectKey: string - Reference to project
  • currentVersionId: string | undefined - ID of current budget version
  • versions: BudgetVersion[] - Budget version history
  • expenses: BudgetExpense[] - Additional expenses
  • createdAt: Date - When budget document was created
  • updatedAt: Date - Last update timestamp
• BudgetVersion structure:
  • id: string - Unique version identifier
  • version: string - Version label (e.g., "v1.0")
  • createdBy: string - Person ID or name
  • createdDate: Date - Creation date
  • isCurrent: boolean - Only one version can be current
  • totalEstimated: number - Total estimated cost
  • status: 'draft' | 'approved' | 'archived'
  • monthlyAllocations: MonthlyAllocation[] - Resource allocations per month
  • excelFileUrl: string | undefined - URL to uploaded Excel file
  • excelFileName: string | undefined - Original filename
• BudgetExpense structure:
  • id: string - Unique expense identifier
  • type: 'claim' | 'equipment' | 'software' | 'other'
  • description: string - Expense description
  • amount: number - Amount in dollars
  • date: Date - Expense date
  • submittedBy: string - Person ID or name
  • status: 'pending' | 'approved' | 'rejected'
• Note: Budget documents are automatically created when first expense/version is added
• Related documentation: Finance data schema

Security Rules

Location: /firestore.rules

Defines access control for Firestore collections:

• Authentication requirements
• Role-based access control (admin, manager, engineer, viewer)
• Read/write permissions per collection
• Helper functions for common checks

Database Indexes

Location: /firestore.indexes.json

Defines composite indexes for Firestore queries:

• Required for queries with multiple where clauses
• Required for queries with orderBy on different fields
• Automatically deployed with Firebase CLI

Data Access Patterns

Client-Side

import { db } from '@/lib/firebase';
// Use db for client-side Firestore operations

Server-Side (API Routes)

import { getAdminFirestore } from '@/lib/firebase-admin';
const db = getAdminFirestore();
// Use db for server-side Firestore operations

Schema Evolution

Since Firestore is schemaless:

• Schema is enforced through TypeScript types
• No database migrations needed for schema changes
• Data validation happens at the application layer
• Firestore security rules provide additional validation

Related Files

• /apps/web/src/lib/firestore/services.ts - Firestore service functions
• /apps/web/src/lib/firestore/migrations.ts - Data migration utilities
• /apps/web/src/lib/firestore/project-team.ts - Project team management
• Data overview — Collections at a glance
• Finance data schema — Budget and finance fields

Environment Variables

Required for database connection:

# Firebase Client Config (public)
NEXT_PUBLIC_FIREBASE_API_KEY=
NEXT_PUBLIC_FIREBASE_AUTH_DOMAIN=
NEXT_PUBLIC_FIREBASE_PROJECT_ID=
NEXT_PUBLIC_FIREBASE_STORAGE_BUCKET=
NEXT_PUBLIC_FIREBASE_MESSAGING_SENDER_ID=
NEXT_PUBLIC_FIREBASE_APP_ID=
NEXT_PUBLIC_FIREBASE_MEASUREMENT_ID=

# Firebase Admin Config (server-side only)
FIREBASE_SERVICE_ACCOUNT_JSON=  # JSON string
# OR
FIREBASE_SERVICE_ACCOUNT_PATH=  # Path to service account file

Update Mechanisms Summary

Sync Operations

1. Jira Sync (/api/sync/jira, /api/sync/projects, /api/sync/all)

   • Updates: projects, issues, worklogs
   • Frequency: Manual trigger or scheduled (configurable)
   • Strategy: Merge (preserves manual fields)

2. Bitbucket Sync (/api/sync/all)

   • Updates: commits, pullRequests
   • Frequency: Part of comprehensive sync
   • Strategy: Overwrite (by document ID)

3. Comprehensive Sync (/api/sync/all)

   • Updates: All Jira + Bitbucket data
   • Also triggers: Correlation, metrics aggregation, team member detection
   • Frequency: Manual trigger or scheduled

4. Auto-Sync (/api/sync/cron)

   • Scheduled sync (default: daily at 01:00)
   • Syncs only previous day's data
   • Configurable via /api/sync/auto-sync-config

Manual Updates

1. Projects: PUT /api/projects/[projectKey]

   • Updates Velocity fields only
   • Preserves synced fields

2. Strategies: POST /api/strategies, PUT /api/strategies/[id]

   • Full CRUD operations
   • Sub-resource management via nested routes

3. Users: PUT /api/users/[identifier]/update

   • User profile updates
   • CSV import available

4. Budget & Expenses: Finance API routes (uses budgets collection)

   • GET /api/projects/[projectKey]/budget - Get budget
   • POST /api/projects/[projectKey]/budget/expenses - Create expense
   • GET /api/projects/[projectKey]/budget/expenses - List expenses
   • PUT /api/projects/[projectKey]/budget/expenses/[expenseId] - Update expense
   • DELETE /api/projects/[projectKey]/budget/expenses/[expenseId] - Delete expense
   • PUT /api/projects/[projectKey]/budget/expenses/[expenseId]/status - Approve/reject expense
   • GET /api/projects/[projectKey]/budget/versions - List budget versions
   • PUT /api/projects/[projectKey]/budget/versions/[versionId] - Update version (status, current flag)
   • DELETE /api/projects/[projectKey]/budget/versions/[versionId] - Delete version
   • POST /api/projects/[projectKey]/budget/ingest - Upload Excel and create new version

System-Generated

1. Metrics: Auto-calculated during sync

   • engineerMetrics, projectMetrics
   • Computed by aggregateMetrics() function

2. Reminders: Auto-generated by system logic

   • Checks for missing items, stalled projects, etc.

3. Work Arrangements: Created from Slack reactions

   • Real-time via Slack webhook events

Summary

The database is Firestore (NoSQL document database) and is defined through:

1. Configuration: /apps/web/src/lib/firebase.ts (client) and /apps/web/src/lib/firebase-admin.ts (server)
2. Type Definitions: /apps/web/src/types/ (modular, organized by domain)
3. Security Rules: /firestore.rules
4. Indexes: /firestore.indexes.json
5. Documentation: This site — Data overview and related pages

The schema is enforced through TypeScript types rather than database constraints, providing flexibility while maintaining type safety.

Key Points

• 18 collections total (including singletons)
• Sync-driven: Most data comes from Jira/Bitbucket syncs
• Merge strategy: Manual fields preserved during syncs
• Computed metrics: Auto-calculated during sync operations
• Real-time: Work arrangements updated via Slack integration
• Separate collections: Budgets stored separately from projects for better scalability