Hugging Face's logo

Spaces:
kamau1
/
swiftops-backend
Sleeping

App Files Community
swiftops-backend / docs /api /reports /EXPORT_ENHANCEMENT.md
kamau1's picture
kamau1
feat: add custom reports with aggregations and export parity
367d57f
preview code
|
raw
history blame contribute delete
9.23 kB

Export System Enhancement - Custom Report Features

Overview

Enhanced the Export API to support the same advanced features as Custom Reports, including calculated fields, aggregations, and grouping.

Date: December 14, 2025
Status: ✅ Implemented

What Changed

Before

Exports were limited to:

  • Fixed column selection from predefined lists
  • Basic filtering only
  • No calculated fields
  • No aggregations
  • Legacy entities only (customers, subscriptions, field_agents, sales_orders)

After

Exports now support:

  • ✅ Flexible column selection
  • ✅ Calculated fields (sla_violated, days_to_complete, is_overdue)
  • ✅ Aggregations (count, sum, avg, min, max)
  • ✅ Grouping by any dimension
  • ✅ All entities (tickets, timesheets, payroll, expenses, users)
  • ✅ Backward compatible with legacy exports

New Features

1. Calculated Fields

Add computed fields to your exports:

{
  "entity": "tickets",
  "columns": ["id", "ticket_name", "status"],
  "calculated_fields": ["sla_violated", "days_to_complete", "is_overdue"],
  "filters": {
    "project_id": "uuid",
    "start_date": "2025-12-01",
    "end_date": "2025-12-31"
  }
}

2. Aggregations

Export summary statistics:

{
  "entity": "tickets",
  "columns": ["status", "priority"],
  "aggregations": [
    { "field": "id", "type": "count", "alias": "ticket_count" },
    { "field": "total_amount", "type": "sum", "alias": "total_value" }
  ],
  "group_by": ["status", "priority"],
  "filters": {
    "project_id": "uuid",
    "start_date": "2025-12-01",
    "end_date": "2025-12-31"
  }
}

3. New Entities

Export from any entity:

  • tickets - Work orders
  • timesheets - Attendance logs
  • payroll - Compensation records
  • expenses - Ticket expenses
  • users - User records

API Changes

Updated Request Schema

File: src/app/schemas/export.py

interface ExportRequest {
  entity: string;
  columns: string[];
  filters: Record<string, any>;
  format: 'csv' | 'excel';
  
  // NEW: Advanced features
  calculated_fields?: string[];
  aggregations?: Array<{
    field: string;
    type: 'count' | 'sum' | 'avg' | 'min' | 'max';
    alias?: string;
  }>;
  group_by?: string[];
}

Updated Service

File: src/app/services/export_service.py

  • Added _export_with_custom_report() method
  • Integrates with ReportService for advanced features
  • Maintains backward compatibility for legacy exports
  • Automatic date range handling

Usage Examples

Example 1: Basic Export (Backward Compatible) 

curl -X POST "http://localhost:7860/api/v1/export/tickets/export" \
  -H "Authorization: Bearer TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "entity": "tickets",
    "columns": ["id", "ticket_name", "status", "priority"],
    "filters": {
      "project_id": "uuid",
      "status": "completed"
    },
    "format": "csv"
  }'

Example 2: Export with Calculated Fields 

curl -X POST "http://localhost:7860/api/v1/export/tickets/export" \
  -H "Authorization: Bearer TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "entity": "tickets",
    "columns": ["id", "ticket_name", "status", "created_at", "completed_at"],
    "calculated_fields": ["sla_violated", "days_to_complete"],
    "filters": {
      "project_id": "uuid",
      "start_date": "2025-12-01",
      "end_date": "2025-12-31"
    },
    "format": "csv"
  }'

Example 3: Export with Aggregations 

curl -X POST "http://localhost:7860/api/v1/export/tickets/export" \
  -H "Authorization: Bearer TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "entity": "tickets",
    "columns": ["status", "priority"],
    "aggregations": [
      {"field": "id", "type": "count", "alias": "count"}
    ],
    "group_by": ["status", "priority"],
    "filters": {
      "project_id": "uuid",
      "start_date": "2025-12-01",
      "end_date": "2025-12-31"
    },
    "format": "csv"
  }'

Example 4: Payroll Summary Export 

curl -X POST "http://localhost:7860/api/v1/export/payroll/export" \
  -H "Authorization: Bearer TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "entity": "payroll",
    "columns": ["user_id"],
    "aggregations": [
      {"field": "total_amount", "type": "sum", "alias": "total_paid"},
      {"field": "days_worked", "type": "sum", "alias": "total_days"}
    ],
    "group_by": ["user_id"],
    "filters": {
      "project_id": "uuid",
      "start_date": "2025-12-01",
      "end_date": "2025-12-31"
    },
    "format": "csv"
  }'

Implementation Details

How It Works

  1. Basic Export Path (Legacy entities without advanced features)

    • Uses existing column-based export logic
    • Validates columns against EXPORT_COLUMN_REGISTRY
    • Extracts data using entity-specific extractors

  2. Advanced Export Path (When calculated_fields, aggregations, or group_by are present)

    • Delegates to ReportService custom report functionality
    • Builds CustomReportConfig from export request
    • Generates report data with all advanced features
    • Returns CSV using ReportService.generate_csv_export()

Date Range Handling

  • Extracts start_date and end_date from filters
  • Falls back to created_at_gte and created_at_lte
  • Defaults to last 30 days if not specified
  • Passes to ReportService for filtering

Authorization

Same as Reports:

  • ✅ PLATFORM_ADMIN - Full access
  • ✅ PROJECT_MANAGER - Project-scoped
  • ✅ DISPATCHER - Project-scoped
  • ✅ SALES_MANAGER - Project-scoped

Benefits

For Users

  • One API for all export needs (basic + advanced)
  • Backward compatible - existing exports still work
  • More powerful - calculated fields and aggregations
  • Consistent - same features as Reports API

For Developers

  • Code reuse - leverages ReportService
  • Maintainable - single source of truth for custom reports
  • Extensible - easy to add new entities and calculated fields

Comparison: Reports vs Exports

Feature Reports API Exports API
Purpose Business intelligence Data extraction
Output JSON or CSV CSV (Excel planned)
Pre-defined Types Yes (4 types) No
Custom Reports Yes Yes (NEW)
Calculated Fields Yes Yes (NEW)
Aggregations Yes Yes (NEW)
Grouping Yes Yes (NEW)
Column Discovery No Yes
Use Case Dashboards, analytics Excel, external systems

When to Use:

Reports API:

  • Dashboard widgets
  • Business intelligence
  • Pre-defined metrics
  • JSON response for UI

Exports API:

  • Excel/CSV downloads
  • External system integration
  • Data migration
  • Backup/archival

Migration Guide

Existing Exports

No changes required - all existing export requests work exactly as before.

New Exports

To use advanced features, add the new optional fields:

// Before (still works)
{
  entity: "tickets",
  columns: ["id", "status"],
  filters: { project_id: "uuid" }
}

// After (with advanced features)
{
  entity: "tickets",
  columns: ["id", "status"],
  calculated_fields: ["sla_violated"],
  aggregations: [{ field: "id", type: "count" }],
  group_by: ["status"],
  filters: { 
    project_id: "uuid",
    start_date: "2025-12-01",
    end_date: "2025-12-31"
  }
}

Files Modified

  1. src/app/schemas/export.py

    • Added AggregationType enum
    • Added AggregationConfig model
    • Updated ExportRequest with new optional fields

  2. src/app/services/export_service.py

    • Updated export_entity() signature
    • Added _export_with_custom_report() method
    • Integrates with ReportService

  3. src/app/api/v1/export.py

    • Updated endpoint to pass new parameters

  4. docs/api/reports/reports-and-exports.md

    • Updated documentation with new features
    • Added examples for advanced exports

Testing

Test Basic Export (Backward Compatibility) 

curl -X POST "http://localhost:7860/api/v1/export/tickets/export" \
  -H "Authorization: Bearer TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"entity":"tickets","columns":["id","status"],"filters":{},"format":"csv"}'

Test Advanced Export 

curl -X POST "http://localhost:7860/api/v1/export/tickets/export" \
  -H "Authorization: Bearer TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "entity":"tickets",
    "columns":["status"],
    "aggregations":[{"field":"id","type":"count"}],
    "group_by":["status"],
    "filters":{"start_date":"2025-12-01","end_date":"2025-12-31"},
    "format":"csv"
  }'

Future Enhancements

  • Excel format support (currently CSV only)
  • Saved export templates
  • Scheduled exports
  • Email delivery
  • Compression for large exports
  • Streaming for very large datasets

Summary

The Export API now has feature parity with the Custom Reports API, providing a unified, powerful data extraction system. Users can export any data with flexible column selection, calculated fields, and aggregations, all while maintaining backward compatibility with existing exports.