j4flmao Report Generation Skill
Purpose
Generate complex reports in multiple formats (PDF, Excel, CSV) from large datasets with async processing, scheduling, and reliable delivery.
Architecture Decision Trees
Format Selection
| Criterion | Excel (XLSX) | CSV | HTML | |
|---|---|---|---|---|
| Human readability | Excellent (fixed layout) | Good (tabular) | Poor (raw data) | Excellent (responsive) |
| Machine parsability | Poor | Good | Excellent | Poor |
| Page size control | Full control | Print layout | N/A | CSS print |
| File size (10K rows) | ~5-10MB (rendered) | ~1-2MB | ~500KB | ~2-3MB |
| Streaming support | No (full render) | Partial (worksheet) | Yes (row-by-row) | Yes |
| Interactive features | No | Formulas, filters | No | Links, JS |
| Accessibility | Poor (screen reader limited) | Moderate | Excellent | Excellent |
| Best for | Invoices, contracts, compliance | Financials, data analysis | Data export, ETL | Dashboards, email |
Decision: PDF for presentation/output. CSV for data export. Excel for analysis. HTML for screen display.
Rendering Strategy
| Criterion | Puppeteer/Chrome | wkhtmltopdf | PDFKit/jsPDF | Server-side lib (iText, TCPDF) |
|---|---|---|---|---|
| CSS support | Full (Chrome) | Limited (WebKit) | None (manual) | Limited |
| JavaScript rendering | Yes | No | No | No |
| Page headers/footers | Full HTML templates | Basic HTML | Manual | Built-in |
| Performance | Slow (~2s/page) | Medium | Fast | Fast |
| Memory | High (~200MB per browser) | Medium | Low | Low |
| Dependency | Chromium binary | wkhtmltopdf binary | Pure JS | Language-specific |
Decision: Puppeteer for complex visual reports. PDFKit for programmatic simple PDFs. wkhtmltopdf only if Puppeteer is too heavy.
Async Processing Decision
| Condition | Sync | Background Queue | Streaming |
|---|---|---|---|
| Rows < 1K | Yes | No | No |
| Rows 1K-100K | No (timeout risk) | Yes | For CSV only |
| Rows > 100K | No | Yes (chunked) | Yes (CSV only) |
| Format = PDF | No (slow) | Yes | No |
| Format = CSV | Possible | Overkill | Yes (preferred) |
| Scheduled reports | No | Yes | No |
Decision: Background queue for PDF/Excel > 1K rows. Stream CSV for any size.
Agent Protocol
Trigger
User mentions PDF generation, report export, Excel export, CSV download, report templates, report scheduling, async reports, Puppeteer, wkhtmltopdf, ExcelJS, Handlebars reports, or large dataset export.
Input Context
- Report format requirements (PDF, Excel, CSV, or all)
- Data sources and expected dataset sizes
- Template requirements and variable injection
- Scheduling needs (one-time, recurring, cron)
- Delivery method (email, download, CDN, webhook)
- Locale/i18n requirements
Output Artifact
SKILL.md adherence document plus implemented report generation code, templates, queue workers, scheduling config, and API endpoints.
Response Format
No preamble. No postamble. No explanations. No filler/hedging/transitions. Compress output — why use many token when few do trick.
Completion Criteria
- Report generation service implemented for all required formats
- HTML templates created with Handlebars/EJS for PDF rendering
- Async generation with queue system (Bull/Sidekiq) configured
- Chunked export for large datasets (>100K rows) implemented
- Report scheduling with cron expressions operational
- Download/storage strategy defined (local/CDN/S3)
- Report API endpoints (generate, status, download, list) implemented
- Error handling and retry logic for failed generations
- Report localization per locale
- Rate limiting on report generation endpoints
Max Response Length
4096 tokens
Workflow
- Format Selection & Setup: Choose libraries per format — Puppeteer for PDF, ExcelJS for XLSX, native streams for CSV.
// Puppeteer PDF generation
import puppeteer from 'puppeteer';
async function generatePdf(html: string): Promise<Buffer> {
const browser = await puppeteer.launch({ headless: 'new' });
const page = await browser.newPage();
await page.setContent(html, { waitUntil: 'networkidle0' });
const pdf = await page.pdf({
format: 'A4',
margin: { top: '20mm', bottom: '20mm', left: '15mm', right: '15mm' },
printBackground: true,
displayHeaderFooter: true,
headerTemplate: '<div style="font-size:8px;margin-left:15mm;">{{title}}</div>',
footerTemplate: '<div style="font-size:8px;text-align:center;width:100%;">Page <span class="pageNumber"></span> of <span class="totalPages"></span></div>',
});
await browser.close();
return pdf;
}
- Template Rendering: Use Handlebars or EJS with report-specific helpers.
<!DOCTYPE html>
<html>
<head>
<style>
body { font-family: 'Inter', Arial, sans-serif; font-size: 12px; }
table { width: 100%; border-collapse: collapse; }
th { background: #1a1a1a; color: white; padding: 8px; text-align: left; }
td { padding: 8px; border-bottom: 1px solid #e0e0e0; }
.header { text-align: center; margin-bottom: 20px; }
.header h1 { font-size: 24px; margin: 0; }
.header p { color: #666; font-size: 14px; }
.summary { display: flex; gap: 16px; margin-bottom: 24px; }
.summary-card { flex: 1; padding: 16px; background: #f8f8f8; border-radius: 8px; text-align: center; }
.summary-card .value { font-size: 28px; font-weight: bold; }
.summary-card .label { font-size: 12px; color: #666; }
.footer { text-align: center; color: #999; font-size: 10px; margin-top: 40px; }
.page-break { page-break-after: always; }
</style>
</head>
<body>
<div class="header">
<h1>{{reportTitle}}</h1>
<p>Generated: {{generatedAt}} | Period: {{startDate}} - {{endDate}}</p>
</div>
<div class="summary">
{{#each summaryCards}}
<div class="summary-card">
<div class="value">{{value}}</div>
<div class="label">{{label}}</div>
</div>
{{/each}}
</div>
<table>
<thead>
<tr>
{{#each columns}}<th>{{this}}</th>{{/each}}
</tr>
</thead>
<tbody>
{{#each rows}}
<tr>
{{#each cells}}<td>{{this}}</td>{{/each}}
</tr>
{{/each}}
</tbody>
</table>
<div class="footer">
<p>{{companyName}} — Confidential</p>
</div>
</body>
</html>
- Excel Export with ExcelJS: Handle multi-sheet workbooks, formatting, formulas.
import ExcelJS from 'exceljs';
async function generateExcel(data: ReportData[]): Promise<Buffer> {
const workbook = new ExcelJS.Workbook();
workbook.creator = 'Report System';
workbook.created = new Date();
const sheet = workbook.addWorksheet('Report Data');
sheet.columns = [
{ header: 'Date', key: 'date', width: 15 },
{ header: 'Revenue', key: 'revenue', width: 15 },
{ header: 'Orders', key: 'orders', width: 12 },
{ header: 'Customers', key: 'customers', width: 15 },
{ header: 'Conversion', key: 'conversion', width: 12 },
];
sheet.getRow(1).font = { bold: true, color: { argb: 'FFFFFFFF' } };
sheet.getRow(1).fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF1a1a1a' } };
data.forEach(row => {
sheet.addRow(row);
});
const totalRow = sheet.addRow({
date: 'TOTAL',
revenue: data.reduce((s, r) => s + r.revenue, 0),
orders: data.reduce((s, r) => s + r.orders, 0),
customers: data.reduce((s, r) => s + r.customers, 0),
conversion: data.reduce((s, r) => s + r.conversion, 0) / data.length,
});
totalRow.font = { bold: true };
return await workbook.xlsx.writeBuffer() as Buffer;
}
- Large CSV Export with Streaming: Process datasets >100K rows without memory overflow.
import { createObjectCsvStringifier } from 'csv-writers';
import { Transform } from 'stream';
async function* fetchDataBatches(batchSize = 1000): AsyncGenerator<Record<string, unknown>[]> {
let offset = 0;
let hasMore = true;
while (hasMore) {
const batch = await db.query(`SELECT * FROM large_table LIMIT $1 OFFSET $2`, [batchSize, offset]);
if (batch.length === 0) hasMore = false;
else {
yield batch;
offset += batchSize;
}
}
}
app.get('/reports/csv', async (req, res) => {
res.setHeader('Content-Type', 'text/csv');
res.setHeader('Content-Disposition', 'attachment; filename="report.csv"');
const csvStringifier = createObjectCsvStringifier({
header: [
{ id: 'id', title: 'ID' },
{ id: 'name', title: 'Name' },
{ id: 'email', title: 'Email' },
{ id: 'created_at', title: 'Created At' },
],
});
res.write(csvStringifier.getHeaderString());
const transform = new Transform({
writableObjectMode: true,
readableObjectMode: true,
transform(chunk, encoding, callback) {
this.push(csvStringifier.stringifyRecords(chunk));
callback();
},
});
const batchIterator = fetchDataBatches();
for await (const batch of batchIterator) {
transform.write(batch);
}
transform.end();
transform.pipe(res);
});
- Async Report Queue: Use Bull or Sidekiq for reports exceeding processing thresholds.
import Bull from 'bull';
const reportQueue = new Bull('report-generation', {
redis: { host: process.env.REDIS_HOST, port: 6379 },
defaultJobOptions: {
attempts: 3,
backoff: { type: 'exponential', delay: 2000 },
removeOnComplete: 100,
removeOnFail: 50,
},
});
reportQueue.process(async (job) => {
const { reportId, format, params } = job.data;
const report = await ReportModel.findById(reportId);
try {
report.status = 'processing';
await report.save();
const data = await fetchReportData(params);
let output: Buffer;
switch (format) {
case 'pdf':
output = await generatePdf(await renderTemplate(report.templateId, data));
break;
case 'xlsx':
output = await generateExcel(data);
break;
case 'csv':
output = await generateCsv(data);
break;
}
const storagePath = await uploadToStorage(reportId, format, output);
report.status = 'completed';
report.storagePath = storagePath;
report.completedAt = new Date();
await report.save();
await notifyUser(report.userId, reportId);
} catch (error) {
report.status = 'failed';
report.error = error.message;
await report.save();
throw error;
}
});
- Report Scheduling: Support one-time and recurring schedules with cron expressions.
interface ReportSchedule {
id: string;
reportType: string;
format: 'pdf' | 'xlsx' | 'csv';
cronExpression: string; // e.g., '0 8 * * 1' = every Monday 8AM
params: Record<string, unknown>;
recipients: string[];
nextRunAt: Date;
lastRunAt?: Date;
}
async function evaluateSchedules(): Promise<void> {
const now = new Date();
const dueSchedules = await ReportSchedule.find({
nextRunAt: { $lte: now },
enabled: true,
});
for (const schedule of dueSchedules) {
const report = await createReportFromSchedule(schedule);
await reportQueue.add({
reportId: report.id,
format: schedule.format,
params: schedule.params,
});
const nextRun = cronParser.parseExpression(schedule.cronExpression);
schedule.nextRunAt = nextRun.next().toDate();
schedule.lastRunAt = now;
await schedule.save();
}
}
Implementation Patterns
Pattern: Puppeteer Pool for PDF Generation
import puppeteer, { Browser, Page } from 'puppeteer';
import { createPool, Pool } from 'generic-pool';
const browserPool: Pool<Browser> = createPool({
create: async () => await puppeteer.launch({
headless: 'new',
args: ['--no-sandbox', '--disable-setuid-sandbox', '--disable-dev-shm-usage', '--disable-gpu'],
}),
destroy: async (browser) => await browser.close(),
}, { max: 3, min: 1, maxWaitingClients: 10 });
async function generatePdfFromUrl(url: string, options: PdfOptions = {}): Promise<Buffer> {
const browser = await browserPool.acquire();
const page = await browser.newPage();
try {
await page.goto(url, { waitUntil: 'networkidle0', timeout: 30000 });
await page.emulateMediaType('print');
return await page.pdf({
format: options.format || 'A4',
margin: { top: '20mm', bottom: '20mm', left: '15mm', right: '15mm' },
printBackground: true,
displayHeaderFooter: true,
headerTemplate: options.headerTemplate,
footerTemplate: options.footerTemplate || '<div style="font-size:8px;text-align:center;width:100%;">Page <span class="pageNumber"></span> of <span class="totalPages"></span></div>',
}) as Buffer;
} finally {
await page.close();
await browserPool.release(browser);
}
}
Pattern: Report Status API
// report-api.ts
interface ReportJob {
id: string;
status: 'queued' | 'processing' | 'completed' | 'failed';
format: string;
progress: { processed: number; total: number; percentage: number };
downloadUrl?: string;
expiresAt?: Date;
error?: string;
createdAt: Date;
}
app.post('/api/reports', async (req, res) => {
const { format, params, schedule } = req.body;
const job = await ReportJobModel.create({
userId: req.user.id,
format,
params,
status: 'queued',
createdAt: new Date(),
});
await reportQueue.add({ jobId: job.id, format, params, userId: req.user.id });
res.status(202).json({ jobId: job.id, status: 'queued' });
});
app.get('/api/reports/:id', async (req, res) => {
const job = await ReportJobModel.findById(req.params.id);
if (!job) return res.status(404).json({ error: 'Report not found' });
if (job.userId !== req.user.id && !req.user.isAdmin) return res.status(403).json({ error: 'Forbidden' });
res.json(formatJobResponse(job));
});
app.get('/api/reports/:id/download', async (req, res) => {
const job = await ReportJobModel.findById(req.params.id);
if (job.status !== 'completed') return res.status(400).json({ error: 'Report not ready' });
if (job.expiresAt && job.expiresAt < new Date()) return res.status(410).json({ error: 'Report expired' });
const stream = await storageService.download(job.storagePath);
res.setHeader('Content-Type', getContentType(job.format));
res.setHeader('Content-Disposition', `attachment; filename="report.${job.format}"`);
stream.pipe(res);
});
Pattern: Chunked Excel with Multiple Sheets
import ExcelJS from 'exceljs';
import { Transform } from 'stream';
async function generateMultiSheetExcel(dataBySheet: Record<string, any[]>, summaryData?: any): Promise<Buffer> {
const workbook = new ExcelJS.Workbook();
workbook.created = new Date();
workbook.modified = new Date();
for (const [sheetName, rows] of Object.entries(dataBySheet)) {
if (rows.length === 0) continue;
const sheet = workbook.addWorksheet(sheetName, {
views: [{ state: 'frozen', ySplit: 1 }],
pageSetup: { orientation: 'landscape', fitToPage: true, paperSize: 9 },
});
const headers = Object.keys(rows[0]);
sheet.columns = headers.map(h => ({ header: h, key: h, width: Math.max(h.length * 2, 12) }));
sheet.getRow(1).font = { bold: true, color: { argb: 'FFFFFFFF' } };
sheet.getRow(1).fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF2D3748' } };
sheet.getRow(1).alignment = { horizontal: 'center', vertical: 'middle' };
sheet.autoFilter = { from: { row: 1, column: 1 }, to: { row: 1, column: headers.length } };
rows.forEach(row => sheet.addRow(row));
}
if (summaryData) {
const summary = workbook.addWorksheet('Summary');
Object.entries(summaryData).forEach(([key, value], i) => {
summary.getCell(`A${i + 1}`).value = key;
summary.getCell(`B${i + 1}`).value = value;
});
}
return await workbook.xlsx.writeBuffer() as Buffer;
}
Pattern: Report Template Versioning
interface ReportTemplate {
id: string;
name: string;
type: 'handlebars' | 'mjml' | 'ejs';
source: string;
version: number;
compiled?: string;
variables: string[];
schema: Record<string, unknown>; // JSON Schema for variable validation
createdAt: Date;
updatedAt: Date;
}
class ReportTemplateManager {
private cache = new Map<string, { compiled: HandlebarsTemplateFunction; version: number }>();
async getCompiler(templateId: string): Promise<HandlebarsTemplateFunction> {
const cached = this.cache.get(templateId);
const template = await ReportTemplate.findById(templateId);
if (cached && cached.version === template.version) return cached.compiled;
const compiled = Handlebars.compile(template.source, { noEscape: true });
this.cache.set(templateId, { compiled, version: template.version });
return compiled;
}
async render(templateId: string, variables: Record<string, unknown>): Promise<string> {
const template = await ReportTemplate.findById(templateId);
const errors = validateAgainstSchema(variables, template.schema);
if (errors.length > 0) throw new Error(`Template variable validation failed: ${errors.join(', ')}`);
const compiler = await this.getCompiler(templateId);
return compiler(variables);
}
}
Production Considerations
Resource Management
- Puppeteer: limit concurrent browsers to 3-5 per node; reuse via pool; kill zombie processes
- Memory: PDF generation is memory-intensive — isolate to dedicated worker with 2-4GB RAM
- Disk: use tmpfs for temporary files; clean up generated files after download or TTL expiry
- CPU: report generation workers should be CPU-optimized instances (compute-optimized AWS EC2)
Storage Strategy
- Short-term (30 days): local disk or S3 with expiring URLs
- Long-term (archive): S3 Glacier or equivalent cold storage
- Never store generated files in database — use blob storage with metadata in DB
- File naming:
{reportType}/{userId}/{date}/{uuid}.{format}
Performance Optimization
- Pre-compile Handlebars/EJS templates at deploy time, not render time
- Use Redis caching for template source (prevent DB load on every render)
- Stream CSV to response — never buffer full file in memory
- For Excel > 100MB, consider splitting into multiple workbook files
- PDF: wait for
networkidle0only for pages with async content; usedomcontentloadedfor faster simple reports
Anti-Patterns
| Anti-Pattern | Why | Fix |
|---|---|---|
| Generating PDF in request handler | Blocks response for seconds; timeout risk | Queue + worker pattern |
| wkhtmltopdf for complex CSS | Limited CSS support; inconsistent rendering | Puppeteer for anything beyond basic tables |
| Single browser for PDF | Cold starts + memory leak per request | Browser pool with max 3-5 instances |
| No query pagination | OOM on datasets > 100K rows | Batch query with offset/keyset pagination |
| Storing files in DB | DB bloat; slow backups | Object storage with metadata pointer |
| Hardcoded page size | A4 and Letter differ; layout breaks | Dynamic page size based on locale/request |
| Not setting PDF timeout | Infinite hang on slow page load | Always set 30s timeout on page.goto and page.pdf |
| Serverless for PDF | Cold start + 15min limit + heavy Chromium | Use long-running worker or dedicated service |
| No progress tracking | Users don't know if report is stuck | Update status via queue → DB → poll endpoint |
Security Considerations
- Never render user-supplied HTML in Puppeteer — use template variables only; sanitize via DOMPurify
- Signed download URLs with short expiry (5-15 minutes) — never expose direct S3 paths
- Validate authorization: user can only access reports they created unless admin
- No internal data in error responses: return generic "Generation failed" with log correlation ID
- Timeout enforcement: kill Puppeteer processes that exceed limit (prevents resource exhaustion DoS)
- Template sandbox: disable
eval,require,processin Handlebars templates - Rate limiting: per-user, per-hour on report generation (e.g., 5/hour, 50/day)
- File size validation: reject requests that would produce files > 100MB before processing
Testing Strategies
import { describe, it, expect } from 'vitest';
import puppeteer from 'puppeteer';
import ExcelJS from 'exceljs';
describe('Report Generation', () => {
it('generates PDF from HTML template', async () => {
const browser = await puppeteer.launch({ headless: 'new' });
const page = await browser.newPage();
await page.setContent('<h1>Test Report</h1><p>Content</p>', { waitUntil: 'networkidle0' });
const pdf = await page.pdf({ format: 'A4' });
expect(pdf.byteLength).toBeGreaterThan(100);
await browser.close();
});
it('generates valid Excel with formulas', async () => {
const workbook = new ExcelJS.Workbook();
const sheet = workbook.addWorksheet('Data');
sheet.addRow(['Item', 'Value']);
sheet.addRow(['A', 10]);
sheet.addRow(['B', 20]);
sheet.getCell('B4').value = { formula: 'SUM(B2:B3)' };
const buf = await workbook.xlsx.writeBuffer();
const wb2 = await new ExcelJS.Workbook().xlsx.readBuffer(buf);
expect(wb2.getWorksheet('Data').getCell('B4').value).toEqual({ formula: 'SUM(B2:B3)', result: 30 });
});
it('streams CSV without buffering full payload', async () => {
const rows = Array.from({ length: 100000 }, (_, i) => ({ id: i, name: `User ${i}` }));
let chunkCount = 0;
for await (const chunk of streamCsv(rows)) {
chunkCount++;
expect(chunk.length).toBeLessThan(65536);
}
expect(chunkCount).toBeGreaterThan(1);
});
it('handles queue job lifecycle', async () => {
const job = await reportQueue.add({ jobId: 'test', format: 'csv', params: {} });
expect(job.id).toBeDefined();
const state = await job.getState();
expect(['waiting', 'active']).toContain(state);
});
it('validates template variables against schema', () => {
const errors = validateAgainstSchema({ name: 'Test' }, { type: 'object', required: ['name', 'email'] });
expect(errors.length).toBe(1);
expect(errors[0]).toContain('email');
});
});
- Use Puppeteer's
screenshotfor visual diff testing of PDF output - Validate PDF accessibility with axe-core in Puppeteer
- Test report generation with empty datasets (edge case: 0 rows)
- Load test: generate 50 PDFs concurrently via queue, measure throughput
- Test file size limits: verify rejection of oversized requests
Rules
- references/excel-csv-export.md — Excel and CSV Export
- references/pdf-generation.md — PDF Generation
- references/report-distribution.md — Report Distribution and Delivery
- references/report-generation-advanced.md — Report Generation Advanced Topics
- references/report-generation-fundamentals.md — Report Generation Fundamentals
- references/report-scheduling.md — Report Scheduling
- references/report-templates.md — Report Templates
- references/report-visualization.md — Report Visualization and Charts
Handoff
backend/bulk-import— CSV/Excel parsing and validation patternsdata/analytics— Report data aggregation and query patternsmanagement/task-queues— Queue management and worker scaling