---
id: "ET-SAAS-017"
title: "Especificacion Tecnica Reports Generation"
type: "TechnicalSpec"
status: "Proposed"
priority: "P2"
module: "reports"
version: "1.0.0"
created_date: "2026-01-24"
updated_date: "2026-01-24"
story_points: 5
---

# ET-SAAS-017: Especificacion Tecnica - Sistema de Reportes

## Metadata
- **Codigo:** ET-SAAS-017
- **Modulo:** Reports
- **Version:** 1.0.0
- **Estado:** Propuesto
- **Fecha:** 2026-01-24
- **Basado en:** PDFKit, ExcelJS best practices

---

## 1. Resumen Ejecutivo

### 1.1 Estado Actual

No existe sistema de reportes implementado.

| Capacidad | Estado | Notas |
|-----------|--------|-------|
| Export PDF | NO | Sin implementacion |
| Export Excel | NO | Sin implementacion |
| Export CSV | NO | Sin implementacion |
| Templates | NO | Sin templates definidos |
| Email delivery | Parcial | Email module existe |

### 1.2 Propuesta v1.0

Sistema de reportes con:

- **3 Formatos**: PDF (formateado), Excel (tabular), CSV (crudo)
- **6 Tipos de reporte**: Users, Billing, Invoices, Audit, Usage, Subscriptions
- **Templates predefinidos**: Diseño profesional con branding
- **Filtros avanzados**: Fecha, status, plan, usuario
- **Delivery**: Descarga directa + envio por email

---

## 2. Tipos de Reportes

### 2.1 Catalogo de Reportes

| ID | Nombre | Formatos | Descripcion |
|----|--------|----------|-------------|
| R01 | Users List | PDF, Excel, CSV | Lista completa de usuarios |
| R02 | Active Users | PDF, Excel | Usuarios activos en periodo |
| R03 | Billing Summary | PDF | Resumen de facturacion |
| R04 | Invoices List | PDF, Excel | Facturas emitidas |
| R05 | Audit Log | CSV | Log de auditoría |
| R06 | Subscriptions | Excel | Estado de suscripciones |
| R07 | Usage Report | Excel | Uso de recursos |
| R08 | Revenue Report | PDF, Excel | Ingresos detallados |

### 2.2 Campos por Reporte

**R01 - Users List**
```
| Name | Email | Role | Plan | Status | Created | Last Login |
```

**R03 - Billing Summary**
```
Period: January 2026
MRR: $45,000
Revenue: $52,000
New Subscriptions: 15
Canceled: 3
Top Plan: Pro (65%)
```

**R05 - Audit Log**
```
| Timestamp | User | Action | Resource | IP | Details |
```

---

## 3. Arquitectura

### 3.1 Diagrama de Componentes

```
+------------------+     +-------------------+     +------------------+
|   Frontend UI    |     | ReportsController |     | ReportsService   |
|  Report Config   |---->| /reports/:type    |---->| (orchestration)  |
+------------------+     +-------------------+     +------------------+
                                                          |
                         +--------------------------------+
                         |                |               |
                         v                v               v
               +-------------+  +-------------+  +-------------+
               | PdfGenerator|  |ExcelGenerator|  |CsvGenerator |
               | (PDFKit)    |  | (ExcelJS)   |  | (manual)    |
               +-------------+  +-------------+  +-------------+
                         |                |               |
                         v                v               v
               +---------------------------------------------------+
               |                    Response                        |
               |  - Stream (download) or Email (attachment)        |
               +---------------------------------------------------+
```

### 3.2 Flujo de Generacion

```
1. Usuario configura reporte (tipo, filtros, formato)
   |
2. Frontend POST /reports/generate
   |
3. ReportsService.generate(config)
   |
4. DataService.fetchData(type, filters)
   |
5. Select generator by format:
   |-- PDF: PdfGeneratorService
   |-- Excel: ExcelGeneratorService
   |-- CSV: CsvGeneratorService
   |
6. Generator produces file buffer
   |
7. Response:
   |-- Download: Stream to client
   |-- Email: Queue email with attachment
   |
8. AuditLog: Register report generation
```

---

## 4. Implementacion Backend

### 4.1 Estructura de Archivos

```
backend/src/modules/reports/
├── reports.module.ts
├── controllers/
│   └── reports.controller.ts
├── services/
│   ├── reports.service.ts
│   ├── pdf-generator.service.ts
│   ├── excel-generator.service.ts
│   └── csv-generator.service.ts
├── templates/
│   ├── pdf/
│   │   ├── users-report.template.ts
│   │   ├── billing-summary.template.ts
│   │   └── invoices-report.template.ts
│   └── excel/
│       ├── users-report.template.ts
│       └── usage-report.template.ts
└── dto/
    ├── generate-report.dto.ts
    └── report-config.dto.ts
```

### 4.2 DTOs

```typescript
export class GenerateReportDto {
  @IsEnum(ReportType)
  type: ReportType;

  @IsEnum(ReportFormat)
  format: ReportFormat;

  @IsOptional()
  @IsDateString()
  startDate?: string;

  @IsOptional()
  @IsDateString()
  endDate?: string;

  @IsOptional()
  @IsObject()
  filters?: Record<string, any>;
}

export class SendReportEmailDto extends GenerateReportDto {
  @IsArray()
  @IsEmail({}, { each: true })
  recipients: string[];

  @IsOptional()
  @IsString()
  subject?: string;

  @IsOptional()
  @IsString()
  message?: string;
}

export enum ReportType {
  USERS = 'users',
  ACTIVE_USERS = 'active_users',
  BILLING_SUMMARY = 'billing_summary',
  INVOICES = 'invoices',
  AUDIT_LOG = 'audit_log',
  SUBSCRIPTIONS = 'subscriptions',
  USAGE = 'usage',
  REVENUE = 'revenue',
}

export enum ReportFormat {
  PDF = 'pdf',
  EXCEL = 'excel',
  CSV = 'csv',
}
```

### 4.3 Service: ReportsService

```typescript
@Injectable()
export class ReportsService {
  constructor(
    private readonly pdfGenerator: PdfGeneratorService,
    private readonly excelGenerator: ExcelGeneratorService,
    private readonly csvGenerator: CsvGeneratorService,
    private readonly dataService: ReportDataService,
    private readonly emailService: EmailService,
    private readonly auditService: AuditService,
  ) {}

  async generate(
    tenantId: string,
    userId: string,
    config: GenerateReportDto,
  ): Promise<ReportResult> {
    // 1. Validate limits
    await this.validateLimits(tenantId, config);

    // 2. Fetch data
    const data = await this.dataService.fetchData(tenantId, config);

    // 3. Generate report
    let buffer: Buffer;
    let mimeType: string;
    let extension: string;

    switch (config.format) {
      case ReportFormat.PDF:
        buffer = await this.pdfGenerator.generate(config.type, data);
        mimeType = 'application/pdf';
        extension = 'pdf';
        break;
      case ReportFormat.EXCEL:
        buffer = await this.excelGenerator.generate(config.type, data);
        mimeType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
        extension = 'xlsx';
        break;
      case ReportFormat.CSV:
        buffer = await this.csvGenerator.generate(config.type, data);
        mimeType = 'text/csv';
        extension = 'csv';
        break;
    }

    // 4. Audit log
    await this.auditService.log({
      tenantId,
      userId,
      action: 'report_generated',
      resource: 'report',
      details: { type: config.type, format: config.format, rows: data.length },
    });

    const filename = this.generateFilename(config.type, extension);

    return { buffer, mimeType, filename };
  }

  async sendByEmail(
    tenantId: string,
    userId: string,
    config: SendReportEmailDto,
  ): Promise<void> {
    const report = await this.generate(tenantId, userId, config);

    await this.emailService.sendWithAttachment({
      to: config.recipients,
      subject: config.subject || `${config.type} Report`,
      body: config.message || 'Please find the attached report.',
      attachment: {
        filename: report.filename,
        content: report.buffer,
        contentType: report.mimeType,
      },
    });

    await this.auditService.log({
      tenantId,
      userId,
      action: 'report_emailed',
      resource: 'report',
      details: { type: config.type, recipients: config.recipients },
    });
  }

  private async validateLimits(
    tenantId: string,
    config: GenerateReportDto,
  ): Promise<void> {
    const count = await this.dataService.countRecords(tenantId, config);

    if (count > 10000) {
      throw new BadRequestException(
        `Report exceeds limit of 10,000 records (found ${count}). Please narrow your filters.`,
      );
    }
  }

  private generateFilename(type: string, extension: string): string {
    const date = new Date().toISOString().split('T')[0];
    return `${type}-report-${date}.${extension}`;
  }
}
```

### 4.4 Service: PdfGeneratorService

```typescript
@Injectable()
export class PdfGeneratorService {
  async generate(type: ReportType, data: any[]): Promise<Buffer> {
    return new Promise((resolve, reject) => {
      const doc = new PDFDocument({ margin: 50 });
      const chunks: Buffer[] = [];

      doc.on('data', (chunk) => chunks.push(chunk));
      doc.on('end', () => resolve(Buffer.concat(chunks)));
      doc.on('error', reject);

      // Header
      this.addHeader(doc, type);

      // Content based on type
      switch (type) {
        case ReportType.USERS:
          this.generateUsersReport(doc, data);
          break;
        case ReportType.BILLING_SUMMARY:
          this.generateBillingSummary(doc, data);
          break;
        case ReportType.INVOICES:
          this.generateInvoicesReport(doc, data);
          break;
        default:
          this.generateGenericTable(doc, data);
      }

      // Footer
      this.addFooter(doc);

      doc.end();
    });
  }

  private addHeader(doc: PDFKit.PDFDocument, type: ReportType): void {
    doc.fontSize(20).text(this.getTitle(type), { align: 'center' });
    doc.moveDown(0.5);
    doc.fontSize(10).fillColor('#666').text(`Generated: ${new Date().toLocaleString()}`, { align: 'center' });
    doc.moveDown(2);
    doc.fillColor('#000');
  }

  private generateUsersReport(doc: PDFKit.PDFDocument, users: any[]): void {
    // Summary
    doc.fontSize(14).text('Summary');
    doc.fontSize(12).text(`Total Users: ${users.length}`);
    doc.text(`Active: ${users.filter((u) => u.status === 'active').length}`);
    doc.moveDown(2);

    // Table
    const headers = ['Name', 'Email', 'Role', 'Plan', 'Status', 'Created'];
    const rows = users.map((u) => [
      u.name,
      u.email,
      u.role,
      u.plan,
      u.status,
      new Date(u.createdAt).toLocaleDateString(),
    ]);

    this.drawTable(doc, headers, rows);
  }

  private generateBillingSummary(doc: PDFKit.PDFDocument, data: any): void {
    doc.fontSize(14).text('Billing Summary');
    doc.moveDown();

    const metrics = [
      ['MRR', `$${data.mrr.toLocaleString()}`],
      ['ARR', `$${data.arr.toLocaleString()}`],
      ['Revenue (Period)', `$${data.revenue.toLocaleString()}`],
      ['Active Subscriptions', data.subscriptionsActive.toString()],
      ['New Subscriptions', data.subscriptionsNew.toString()],
      ['Churned', data.subscriptionsChurned.toString()],
    ];

    metrics.forEach(([label, value]) => {
      doc.fontSize(12).text(`${label}: `, { continued: true });
      doc.fontSize(12).fillColor('#3B82F6').text(value);
      doc.fillColor('#000');
    });
  }

  private drawTable(
    doc: PDFKit.PDFDocument,
    headers: string[],
    rows: string[][],
  ): void {
    const startX = 50;
    const startY = doc.y;
    const colWidth = (doc.page.width - 100) / headers.length;
    const rowHeight = 20;

    // Headers
    doc.font('Helvetica-Bold').fontSize(10);
    headers.forEach((header, i) => {
      doc.text(header, startX + i * colWidth, startY, { width: colWidth });
    });

    // Rows
    doc.font('Helvetica').fontSize(9);
    rows.forEach((row, rowIndex) => {
      const y = startY + (rowIndex + 1) * rowHeight;

      if (y > doc.page.height - 100) {
        doc.addPage();
      }

      row.forEach((cell, colIndex) => {
        doc.text(cell || '-', startX + colIndex * colWidth, y, {
          width: colWidth,
          ellipsis: true,
        });
      });
    });
  }

  private addFooter(doc: PDFKit.PDFDocument): void {
    const pages = doc.bufferedPageRange();
    for (let i = 0; i < pages.count; i++) {
      doc.switchToPage(i);
      doc.fontSize(8).fillColor('#999').text(
        `Page ${i + 1} of ${pages.count}`,
        50,
        doc.page.height - 50,
        { align: 'center' },
      );
    }
  }

  private getTitle(type: ReportType): string {
    const titles: Record<ReportType, string> = {
      [ReportType.USERS]: 'Users Report',
      [ReportType.ACTIVE_USERS]: 'Active Users Report',
      [ReportType.BILLING_SUMMARY]: 'Billing Summary',
      [ReportType.INVOICES]: 'Invoices Report',
      [ReportType.AUDIT_LOG]: 'Audit Log',
      [ReportType.SUBSCRIPTIONS]: 'Subscriptions Report',
      [ReportType.USAGE]: 'Usage Report',
      [ReportType.REVENUE]: 'Revenue Report',
    };
    return titles[type] || 'Report';
  }
}
```

### 4.5 Service: ExcelGeneratorService

```typescript
@Injectable()
export class ExcelGeneratorService {
  async generate(type: ReportType, data: any[]): Promise<Buffer> {
    const workbook = new ExcelJS.Workbook();
    workbook.creator = 'Template SaaS';
    workbook.created = new Date();

    const sheet = workbook.addWorksheet(this.getSheetName(type));

    switch (type) {
      case ReportType.USERS:
        this.generateUsersSheet(sheet, data);
        break;
      case ReportType.INVOICES:
        this.generateInvoicesSheet(sheet, data);
        break;
      case ReportType.USAGE:
        this.generateUsageSheet(sheet, data);
        break;
      default:
        this.generateGenericSheet(sheet, data);
    }

    return workbook.xlsx.writeBuffer() as Promise<Buffer>;
  }

  private generateUsersSheet(sheet: ExcelJS.Worksheet, users: any[]): void {
    sheet.columns = [
      { header: 'Name', key: 'name', width: 25 },
      { header: 'Email', key: 'email', width: 30 },
      { header: 'Role', key: 'role', width: 15 },
      { header: 'Plan', key: 'plan', width: 15 },
      { header: 'Status', key: 'status', width: 12 },
      { header: 'Created', key: 'createdAt', width: 15 },
      { header: 'Last Login', key: 'lastLoginAt', width: 15 },
    ];

    // Style header row
    sheet.getRow(1).font = { bold: true };
    sheet.getRow(1).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FF3B82F6' },
    };
    sheet.getRow(1).font = { bold: true, color: { argb: 'FFFFFFFF' } };

    // Add data
    users.forEach((user) => {
      sheet.addRow({
        name: user.name,
        email: user.email,
        role: user.role,
        plan: user.plan,
        status: user.status,
        createdAt: new Date(user.createdAt),
        lastLoginAt: user.lastLoginAt ? new Date(user.lastLoginAt) : null,
      });
    });

    // Date formatting
    sheet.getColumn('createdAt').numFmt = 'yyyy-mm-dd';
    sheet.getColumn('lastLoginAt').numFmt = 'yyyy-mm-dd';
  }

  private generateUsageSheet(sheet: ExcelJS.Worksheet, data: any): void {
    sheet.columns = [
      { header: 'Resource', key: 'resource', width: 20 },
      { header: 'Usage', key: 'usage', width: 15 },
      { header: 'Unit', key: 'unit', width: 10 },
      { header: 'Limit', key: 'limit', width: 15 },
      { header: '% Used', key: 'percent', width: 10 },
    ];

    sheet.getRow(1).font = { bold: true };

    const resources = [
      { resource: 'API Calls', usage: data.apiCalls, unit: 'calls', limit: data.apiLimit },
      { resource: 'Storage', usage: data.storageGb, unit: 'GB', limit: data.storageLimit },
      { resource: 'AI Tokens', usage: data.aiTokens, unit: 'tokens', limit: data.aiLimit },
    ];

    resources.forEach((r) => {
      sheet.addRow({
        ...r,
        percent: r.limit > 0 ? Math.round((r.usage / r.limit) * 100) : 0,
      });
    });
  }

  private getSheetName(type: ReportType): string {
    return type.replace(/_/g, ' ').replace(/\b\w/g, (l) => l.toUpperCase());
  }
}
```

### 4.6 Controller: ReportsController

```typescript
@Controller('reports')
@ApiTags('Reports')
@UseGuards(JwtAuthGuard, RolesGuard)
@Roles('admin', 'owner')
export class ReportsController {
  constructor(private readonly reportsService: ReportsService) {}

  @Post('generate')
  @ApiOperation({ summary: 'Generate and download report' })
  async generateReport(
    @GetTenant() tenantId: string,
    @GetUser() user: User,
    @Body() dto: GenerateReportDto,
    @Res() res: Response,
  ): Promise<void> {
    const report = await this.reportsService.generate(tenantId, user.id, dto);

    res.setHeader('Content-Type', report.mimeType);
    res.setHeader('Content-Disposition', `attachment; filename="${report.filename}"`);
    res.send(report.buffer);
  }

  @Post('send-email')
  @ApiOperation({ summary: 'Generate and send report by email' })
  async sendReportByEmail(
    @GetTenant() tenantId: string,
    @GetUser() user: User,
    @Body() dto: SendReportEmailDto,
  ): Promise<{ message: string }> {
    await this.reportsService.sendByEmail(tenantId, user.id, dto);
    return { message: 'Report sent successfully' };
  }

  @Get('types')
  @ApiOperation({ summary: 'Get available report types' })
  getReportTypes(): ReportTypeInfo[] {
    return [
      { type: 'users', name: 'Users List', formats: ['pdf', 'excel', 'csv'] },
      { type: 'active_users', name: 'Active Users', formats: ['pdf', 'excel'] },
      { type: 'billing_summary', name: 'Billing Summary', formats: ['pdf'] },
      { type: 'invoices', name: 'Invoices', formats: ['pdf', 'excel'] },
      { type: 'audit_log', name: 'Audit Log', formats: ['csv'] },
      { type: 'subscriptions', name: 'Subscriptions', formats: ['excel'] },
      { type: 'usage', name: 'Usage Report', formats: ['excel'] },
      { type: 'revenue', name: 'Revenue Report', formats: ['pdf', 'excel'] },
    ];
  }
}
```

---

## 5. Implementacion Frontend

### 5.1 ReportGenerator Component

```tsx
export const ReportGenerator: React.FC = () => {
  const [reportType, setReportType] = useState<string>('users');
  const [format, setFormat] = useState<string>('pdf');
  const [dateRange, setDateRange] = useState<DateRange>({
    start: subDays(new Date(), 30),
    end: new Date(),
  });
  const [isLoading, setIsLoading] = useState(false);
  const [showEmailModal, setShowEmailModal] = useState(false);

  const { data: reportTypes } = useReportTypes();

  const handleDownload = async () => {
    setIsLoading(true);
    try {
      const blob = await reportsApi.generate({
        type: reportType,
        format,
        startDate: dateRange.start.toISOString(),
        endDate: dateRange.end.toISOString(),
      });

      downloadBlob(blob, `${reportType}-report.${format === 'excel' ? 'xlsx' : format}`);
      toast.success('Report downloaded');
    } catch (error) {
      toast.error('Failed to generate report');
    } finally {
      setIsLoading(false);
    }
  };

  return (
    <div className="space-y-6">
      <h1 className="text-2xl font-bold">Generate Report</h1>

      <Card>
        <CardContent className="space-y-4 pt-6">
          {/* Report Type */}
          <div>
            <Label>Report Type</Label>
            <Select value={reportType} onValueChange={setReportType}>
              <SelectTrigger>
                <SelectValue />
              </SelectTrigger>
              <SelectContent>
                {reportTypes?.map((rt) => (
                  <SelectItem key={rt.type} value={rt.type}>
                    {rt.name}
                  </SelectItem>
                ))}
              </SelectContent>
            </Select>
          </div>

          {/* Format */}
          <div>
            <Label>Format</Label>
            <Select value={format} onValueChange={setFormat}>
              <SelectTrigger>
                <SelectValue />
              </SelectTrigger>
              <SelectContent>
                {reportTypes
                  ?.find((rt) => rt.type === reportType)
                  ?.formats.map((f) => (
                    <SelectItem key={f} value={f}>
                      {f.toUpperCase()}
                    </SelectItem>
                  ))}
              </SelectContent>
            </Select>
          </div>

          {/* Date Range */}
          <div>
            <Label>Date Range</Label>
            <DateRangePicker value={dateRange} onChange={setDateRange} />
          </div>

          {/* Actions */}
          <div className="flex gap-2 pt-4">
            <Button onClick={handleDownload} disabled={isLoading}>
              {isLoading ? <Spinner /> : <DownloadIcon />}
              Download
            </Button>
            <Button variant="outline" onClick={() => setShowEmailModal(true)}>
              <MailIcon />
              Send by Email
            </Button>
          </div>
        </CardContent>
      </Card>

      <ReportEmailModal
        open={showEmailModal}
        onClose={() => setShowEmailModal(false)}
        reportConfig={{ type: reportType, format, dateRange }}
      />
    </div>
  );
};
```

---

## 6. Criterios de Aceptacion

- [ ] Generar PDF de usuarios con formato profesional
- [ ] Generar Excel de usuarios con datos tabulares
- [ ] Generar CSV de audit log
- [ ] Filtros por fecha funcionan correctamente
- [ ] Limite de 10,000 registros se respeta
- [ ] Envio por email funciona con adjunto
- [ ] Solo admins pueden generar reportes
- [ ] Cada generacion se registra en audit log
- [ ] Performance < 5s para reportes de 5000 registros
- [ ] Tests unitarios con cobertura >70%

---

## 7. Dependencias

### NPM Packages

```json
{
  "pdfkit": "^0.15.0",
  "exceljs": "^4.4.0"
}
```

---

*ET-SAAS-017 v1.0.0 - Template SaaS*