📓 Reports

📖 Overview

The Reports service manages service delivery reports that document work performed, results achieved, and performance metrics. These reports are key deliverables provided to clients on a recurring basis (monthly, weekly, etc.) and serve as proof of value and transparency.

Source Files:

• Service: internal/api/v1/projects/services/reports.service.js
• Controller: internal/api/v1/projects/controllers/reports.controller.js

Key Capabilities:

• Retrieve paginated reports for specific sub-accounts
• Filter reports by active subscription status
• Sort reports by various fields (created date, name, type)
• Enrich reports with order and subscription context
• Support client dashboard access with subscription validation

🗄️ Collections Used

📚 Full Schema: See Database Collections Documentation

projects.reports

• Operations: Read (primary operations)
• Model: shared/models/projects-reports.js
• Usage Context: Stores report metadata, files, and delivery information

Key Fields:

{
  _id: ObjectId,
  sub_account: ObjectId,            // Client account
  order_id: ObjectId,               // Related order
  report_name: String,              // Report title
  report_type: String,              // 'monthly', 'weekly', 'quarterly'
  message: String,                  // Report summary/notes
  timeframe: Object,                // Date range covered
  files: Array,                     // Report file URLs
  link: String,                     // External report link
  createdAt: Date,
  updatedAt: Date
}

_store.orders

• Operations: Read (order context)
• Usage Context: Provides product/subscription information for reports

_store.subscriptions

• Operations: Read (subscription validation)
• Usage Context: Filters reports by active subscription status

🔄 Data Flow

Report Listing Flow

flowchart TD
    A[🎯 API Request: GET /reports] --> B[Filter by sub_account]
    B --> C[Lookup Order Details]
    C --> D[Lookup Subscription Status]
    D --> E{Subscription Active?}
    E -->|No| F[Exclude Report]
    E -->|Yes| G[Include Report]
    F --> H{More Reports?}
    G --> H
    H -->|Yes| C
    H -->|No| I[Apply Sorting]
    I --> J[Apply Pagination]
    J --> K[Enrich with Product Info]
    K --> L[📤 Return Report List]

    style A fill:#e1f5ff
    style L fill:#e1ffe1

🔧 Business Logic & Functions

Service Layer

---

getReports(options)

Purpose: Retrieves a paginated list of reports for a specific sub-account, filtered by active subscription status and enriched with product information.

Source: services/reports.service.js

Parameters:

• accountId (ObjectId, required) - Target sub-account ID
• skip (Number, required) - Pagination offset
• limit (Number, required) - Max reports to return
• sort (String, optional) - Sort direction: 'asc' or 'desc' (default: 'desc')
• sortby (String, optional) - Sort field (default: 'createdAt')
  • Possible values: 'createdAt', 'updatedAt', 'report_name', 'report_type'
• dashboardPreferences (Object, optional) - Client dashboard settings (for future use)

Returns: Promise<Object>

{
  data: [
    {
      _id: ObjectId,
      sub_account: ObjectId,
      order_id: ObjectId,
      report_name: String,
      report_type: String,
      message: String,
      timeframe: Object,
      files: Array,
      link: String,
      createdAt: Date,
      updatedAt: Date,
      order_details: {             // Enriched order context
        product_name: String,
        price_name: String,
        product_id: ObjectId,
        price_id: ObjectId,
        product_type: String,
        image: String
      }
    }
  ],
  total: Number                    // Total count of matching reports
}

Business Logic Flow:

1. Sort Configuration

   const sortField = sortby || 'createdAt';
   const sortOrder = sort === 'asc' ? 1 : -1;

   Defaults to newest reports first (createdAt: -1).

2. Base Query Construction

   const options = {
     sub_account: new mongoose.Types.ObjectId(accountId),
   };

   Scopes reports to specific sub-account only.

3. MongoDB Aggregation Pipeline

   Executes a sophisticated 3-stage aggregation within a facet:

   {
     $facet: {
       data: [
         // Stage 1: Lookup order with nested subscription lookup
         {
           $lookup: {
             from: '_store.orders',
             let: { orderId: '$order_id' },
             pipeline: [
               { $match: { $expr: { $eq: ['$_id', '$$orderId'] } } },
               // Nested subscription lookup
               {
                 $lookup: {
                   from: '_store.subscriptions',
                   let: { subscriptionId: '$subscription' },
                   pipeline: [
                     {
                       $match: {
                         $expr: { $eq: ['$_id', '$$subscriptionId'] },
                         status: { $in: ['active', 'past_due', 'trial'] }
                       }
                     },
                     { $project: { id: '$_id', status: 1, _id: 0 } }
                   ],
                   as: 'subscription'
                 }
               },
               // Filter: Only orders with active subscriptions
               { $match: { 'subscription.0': { $exists: true } } }
             ],
             as: 'order'
           }
         },
         // Stage 2: Unwind order (filter out reports without active orders)
         { $unwind: { path: '$order' } },
         // Stage 3: Apply pagination
         { $skip: skip },
         { $limit: limit },
         // Stage 4: Shape response
         {
           $set: {
             order_details: {
               product_name: '$order.metadata.product_name',
               price_name: '$order.metadata.price_name',
               product_id: '$order.product',
               price_id: '$order.price',
               product_type: '$order.metadata.product_type',
               image: { $first: '$order.metadata.images' }
             },
             order: '$$REMOVE'  // Remove order object, keep only order_details
           }
         }
       ],
       total: [{ $count: 'total' }]
     }
   }

   Key Features:

   • Nested Lookup: Order lookup contains subscription lookup for validation
   • Active Subscription Filter: Only reports for active/past_due/trial subscriptions
   • Automatic Filtering: Reports without orders or inactive subscriptions are excluded
   • Data Enrichment: Adds product information from order metadata
   • Simultaneous Count: Uses $facet to return data and count in one query

4. Response Extraction

   return { data: reports[0]?.data, total: reports[0]?.total?.[0]?.total };

   Extracts data and total from facet results.

Key Business Rules:

• ✅ Active Subscriptions Only: Reports only shown for active/past_due/trial subscriptions
• ✅ Sub-Account Scoping: Always scoped to specific sub-account (no cross-account access)
• ✅ Order Validation: Reports without valid orders are automatically excluded
• ✅ Product Context: Always includes product information for UI display

Error Handling:

• Returns empty results on database errors (graceful degradation)
• Returns { data: undefined, total: undefined } if aggregation fails

Performance Notes:

• Nested Lookup: Order + subscription lookups can be expensive

• Recommended Indexes:

  projects.reports: { sub_account: 1, createdAt: -1 }
  projects.reports: { order_id: 1 }
  _store.orders: { _id: 1, subscription: 1 }
  _store.subscriptions: { _id: 1, status: 1 }

• Subscription Filter Impact: Reduces result set significantly

Example Usage:

const reports = await getReports({
  accountId: subAccountId,
  skip: 0,
  limit: 10,
  sort: 'desc',
  sortby: 'createdAt',
  dashboardPreferences: null,
});

console.log(reports.data.length); // Up to 10 reports
console.log(reports.total); // Total matching reports
console.log(reports.data[0].order_details.product_name); // "SEO Management"

Side Effects:

• 📖 Read-only: No data modifications
• ⚡ Performance Impact: 50-200ms depending on data volume

---

Controller Layer

---

getReports(req, res)

Purpose: HTTP endpoint handler for report listing. Parses query parameters, calls service layer, and formats paginated response.

Source: controllers/reports.controller.js

Route: GET /api/v1/projects/accounts/:account_id/reports

Request:

• URL Parameters:
  • account_id (ObjectId) - Target sub-account ID
• Query Parameters:
  • page (Number, optional) - Page number (1-indexed)
  • limit (Number, required) - Reports per page
  • sort (String, optional) - Sort direction: 'asc' | 'desc'
  • sortby (String, optional) - Sort field name

Response:

• Success (200):

  {
    success: true,
    message: 'SUCCESS',
    data: [...],            // Array of report objects
    pagination: {
      total: Number,
      page: Number,
      limit: Number,
      totalPages: Number
    }
  }

Logic:

1. Parameter Parsing

   let { page, limit, sort, sortby } = req.query;
   const { account_id: accountId } = req.params;
   const dashboardPreferences = req.auth.dashboard_preferences;
   
   limit = parseInt(limit);
   page = page ? parseInt(page) : 0;
   const skip = Math.max(0, (page - 1) * limit);

2. Service Call

   const { data, total } = await reportsService.getReports({
     accountId,
     skip,
     limit,
     sort,
     sortby,
     dashboardPreferences,
   });

3. Pagination Generation

   res.json({
     success: true,
     message: 'SUCCESS',
     data,
     pagination: generatePagination(limit, page, total || 0),
   });

Example Request:

GET /api/v1/projects/accounts/507f1f77bcf86cd799439011/reports?page=1&limit=10&sort=desc&sortby=createdAt
Authorization: Bearer <jwt_token>

Example Response:

{
  "success": true,
  "message": "SUCCESS",
  "data": [
    {
      "_id": "507f1f77bcf86cd799439012",
      "sub_account": "507f1f77bcf86cd799439011",
      "order_id": "507f1f77bcf86cd799439013",
      "report_name": "September 2025 SEO Performance",
      "report_type": "monthly",
      "message": "Strong improvement in organic traffic this month.",
      "timeframe": {
        "start": "2025-09-01T00:00:00Z",
        "end": "2025-09-30T23:59:59Z"
      },
      "files": ["https://reports.dashclicks.com/seo-sept-2025.pdf"],
      "link": "https://analytics.google.com/...",
      "createdAt": "2025-10-05T10:00:00Z",
      "updatedAt": "2025-10-05T10:00:00Z",
      "order_details": {
        "product_name": "SEO Management",
        "price_name": "Plus",
        "product_type": "seo",
        "image": "https://..."
      }
    }
  ],
  "pagination": {
    "total": 24,
    "page": 1,
    "limit": 10,
    "totalPages": 3
  }
}

---

🔀 Integration Points

Internal Dependencies

• generatePagination() (utilities/index.js) - Pagination helper
• catchAsync() (utilities/catch-async.js) - Error handling wrapper
• Store Module - Order and subscription data
• Activity Module - Report uploads logged as activities

External Services

None - Pure internal data operations

---

🧪 Edge Cases & Special Handling

Case: Report with Cancelled Subscription

Condition: Report exists but subscription is cancelled

Handling:

{
  $match: {
    status: {
      $in: ['active', 'past_due', 'trial'];
    }
  }
}

Report is excluded from results automatically via subscription filter.

Case: Report with Deleted Order

Condition: Report references non-existent order

Handling:

{
  $unwind: {
    path: '$order';
  }
}

Report is excluded (unwind on empty array removes document from results).

Case: No Reports for Account

Condition: Sub-account has no reports yet

Handling:

Returns empty array:

{
  data: [],
  total: 0
}

Controller generates pagination with 0 total pages.

Case: Invalid Sort Field

Condition: User provides invalid sortby value

Handling:

const sortField = sortby || 'createdAt'; // Falls back to default

No validation - MongoDB will handle invalid field gracefully (no sorting).

---

⚠️ Important Notes

• 📊 Active Subscriptions Only: Reports automatically filtered by subscription status - no historical reports for cancelled services
• 🔍 Automatic Filtering: Order and subscription validation happens in aggregation - no explicit checks needed
• 📁 File URLs: Report files array contains fully-qualified URLs ready for download
• 🔗 External Links: link field may contain external reporting dashboard URLs (e.g., Google Analytics)
• 📅 Timeframe Structure: timeframe object contains start and end dates for report coverage period
• 💡 Product Context: Always includes product details for UI display (tier, type, image)

---

🔗 Related Documentation

• Activity - Report uploads logged in activity timeline
• Subscriptions - Subscription status affects report visibility
• Projects Module Overview - Parent module architecture

---

Last Updated: 2025-10-08 Service Files: services/reports.service.js, controllers/reports.controller.js > Primary Functions: 1 service function, 1 controller endpoint