byrachel/exploration.md

Exploration: PDF Reports Module

Executive Summary

Design client-side PDF generation for 18 report types across inventory, products, movements, purchases, customers, sales, profits, and analytics. All PDFs generated in browser using pdf-lib (~150KB), dynamically imported on demand. Reusable template pattern for headers, footers, and tables ensures consistency and reduces code duplication.

Current State

Existing Architecture

• Framework: Next.js 16.2.11 App Router (TypeScript strict)
• Database: SQLite via Drizzle ORM (better-sqlite3)
• Services: inventory, sales, purchases, customers, suppliers modules with service functions
• UI: shadcn/ui components, Tailwind CSS, ARS currency (es-AR locale)
• Admin Shell: Sidebar navigation, header, mobile drawer
• Reports Page: Placeholder at /admin/reports (coming soon)

Existing Data Sources

// Inventory
- computeTrafficLight() — stock status (green/yellow/red)
- getMovements() — inventory movement history
- getRestockSuggestions() — low stock variants

// Sales
- getSales(filters) — list sales with pagination
- getSaleById(id) — single sale with items

// Purchases
- getPurchases(filters) — list purchases
- getPurchaseById(id) — single purchase with items
- allocateAdditionalCosts() — cost allocation logic

// Customers
- Customer service with CRUD operations

// Suppliers
- Supplier service with CRUD operations

Missing Components

• No PDF libraries installed (pdf-lib, jsPDF, html2canvas)
• No report generation code
• No export functionality
• No print-optimized CSS

---

Required Reports (18 total)

Inventory Reports (4)

1. Inventory General — All variants with current stock, min stock, location
2. Inventory Critical — Variants below minimum stock (red traffic light)
3. Products to Restock — Variants needing reorder (yellow + red traffic light)
4. Inventory Valuation — Stock value by variant (quantity × cost)

Product Reports (2)

5. Products by Category — Grouped by category with counts and totals
6. Products by Supplier — Grouped by preferred supplier

Movement Reports (1)

7. Movement History — All inventory movements with filters (date range, type, variant)

Purchase Reports (2)

8. Purchases — List of purchases with totals, status, supplier
9. Purchase Detail — Single purchase with items, costs, allocation

Customer Reports (2)

10. Customers — List of customers with contact info, type, total purchases
11. Customer Detail — Single customer with purchase history

Sales Reports (2)

12. Sales — List of sales with totals, status, payment method
13. Sale Detail — Single sale with items, discounts, taxes

Profit Reports (2)

14. Profits — Gross profit by period (sale price - cost)
15. Cost Evolution — Average cost trends over time

Analytics Reports (3)

16. Seasonality Analysis — Sales by month/quarter, identify patterns
17. Best Sellers — Top variants by quantity sold
18. Low Rotation — Variants with low sales velocity

---

Technical Requirements

Client-Side Generation

• No server Puppeteer — all PDFs generated in browser
• Libraries: pdf-lib (~150KB) — already chosen in foundation
• Dynamic Import: Load pdf-lib only when user clicks export
• Bundle Impact: ~150KB added to client bundle (lazy-loaded chunk)

PDF Structure (per report)

┌─────────────────────────────────────┐
│  [Logo]        Report Title         │  ← Header (page 1)
│  Generated: 2026-07-23 14:30        │
│  Period: 2026-01-01 to 2026-06-30   │
│  Filters: Category=Shirts, Status=… │
├─────────────────────────────────────┤
│  Data Table                         │
│  ┌──────┬────────┬───────┬────────┐ │
│  │ SKU  │ Name   │ Stock │ Value  │ │
│  ├──────┼────────┼───────┼────────┤ │
│  │ ...  │ ...    │ ...   │ ...    │ │
│  └──────┴────────┴───────┴────────┘ │
│                                     │
│  [Continues on next page if needed] │
├─────────────────────────────────────┤
│  Totals: $1,234,567                 │  ← Footer
│  Page 1 of 5                        │
│  byrachel — www.byrachel.com        │
└─────────────────────────────────────┘

Format & Layout

• Paper: A4 (210mm × 297mm)
• Orientation: Portrait (default) or Landscape (wide tables)
• Margins: 15mm all sides
• Fonts: Helvetica (built-in pdf-lib font)
• Colors: Black text, gray borders, accent color for headers

Pagination

• Calculate rows per page based on available height
• Split table rows across pages
• Repeat header row on each page
• Page numbers in footer (Page X of Y)

Progress Indicator

• Show loading state during data fetching (server-side)
• Show progress during PDF generation (client-side)
• Use React state + spinner component

Alternative: @media print CSS

• Provide print-optimized CSS for browser's native print
• Fallback for users who prefer Ctrl+P
• Hide navigation, show only report content

---

Approaches

Approach 1: pdf-lib (Programmatic PDF Generation) ✅ RECOMMENDED

Description: Build PDFs from scratch using pdf-lib's low-level API. Manually position text, draw tables, embed images. Create reusable template functions for headers, footers, and tables.

Implementation:

// src/lib/reports/templates/header.ts
import { PDFDocument, PDFFont, rgb } from 'pdf-lib';

export function drawHeader(
  page: PDFPage,
  font: PDFFont,
  options: {
    logo?: Uint8Array;
    title: string;
    subtitle?: string;
    generatedAt: Date;
  }
) {
  const { width, height } = page.getSize();
  
  // Draw logo (if provided)
  if (options.logo) {
    const logoImage = await pdfDoc.embedPng(options.logo);
    page.drawImage(logoImage, { x: 15, y: height - 40, width: 30, height: 30 });
  }
  
  // Draw title
  page.drawText(options.title, {
    x: options.logo ? 50 : 15,
    y: height - 30,
    size: 18,
    font,
    color: rgb(0, 0, 0),
  });
  
  // Draw generation date
  page.drawText(`Generated: ${formatDate(options.generatedAt)}`, {
    x: 15,
    y: height - 50,
    size: 10,
    font,
    color: rgb(0.4, 0.4, 0.4),
  });
}

Pros:

• ✅ Smallest bundle (~150KB vs 500KB+ for alternatives)
• ✅ Modern, TypeScript-first API
• ✅ Full control over layout, pagination, positioning
• ✅ Fast (no DOM rendering overhead)
• ✅ Already chosen in foundation architecture
• ✅ Sufficient for all 18 report types

Cons:

• ❌ Manual layout (no CSS/HTML)
• ❌ More code to write (table drawing, pagination logic)
• ❌ Steeper learning curve for team unfamiliar with pdf-lib

Effort: Medium-High (40-60 hours)

---

Approach 2: jsPDF + html2canvas (HTML-to-PDF)

Description: Render React components to HTML, capture with html2canvas, convert to PDF with jsPDF. Easier styling with CSS but larger bundle and slower performance.

Implementation:

// Client component
import jsPDF from 'jspdf';
import html2canvas from 'html2canvas';

async function exportToPDF(elementId: string) {
  const element = document.getElementById(elementId);
  const canvas = await html2canvas(element);
  const imgData = canvas.toDataURL('image/png');
  
  const pdf = new jsPDF('p', 'mm', 'a4');
  const imgWidth = 210; // A4 width in mm
  const imgHeight = (canvas.height * imgWidth) / canvas.width;
  
  pdf.addImage(imgData, 'PNG', 0, 0, imgWidth, imgHeight);
  pdf.save('report.pdf');
}

Pros:

• ✅ Easier styling (use existing CSS/Tailwind)
• ✅ Less code (no manual table drawing)
• ✅ Familiar workflow (HTML → PDF)

Cons:

• ❌ Large bundle (~500KB+ combined)
• ❌ Slow (DOM rendering + canvas conversion)
• ❌ Inconsistent rendering (browser differences)
• ❌ Pagination issues (hard to split tables across pages)
• ❌ Not suitable for large reports (memory issues)

Effort: Low-Medium (20-30 hours)

---

Approach 3: @react-pdf/renderer (React PDF Components)

Description: Use React components to define PDF layout (similar to React Native). Declarative API with <Document>, <Page>, <View>, <Text> components.

Implementation:

import { Document, Page, Text, View, StyleSheet } from '@react-pdf/renderer';

const styles = StyleSheet.create({
  page: { padding: 15 },
  header: { fontSize: 18, marginBottom: 10 },
  table: { display: 'flex', flexDirection: 'column' },
  row: { flexDirection: 'row', borderBottom: 1 },
  cell: { flex: 1, padding: 5 },
});

function InventoryReport({ data }) {
  return (
    <Document>
      <Page size="A4" style={styles.page}>
        <Text style={styles.header}>Inventory Report</Text>
        <View style={styles.table}>
          {data.map((item) => (
            <View key={item.id} style={styles.row}>
              <Text style={styles.cell}>{item.sku}</Text>
              <Text style={styles.cell}>{item.name}</Text>
              <Text style={styles.cell}>{item.stock}</Text>
            </View>
          ))}
        </View>
      </Page>
    </Document>
  );
}

Pros:

• ✅ React components (familiar API)
• ✅ Declarative layout
• ✅ Built-in pagination
• ✅ Good for complex layouts

Cons:

• ❌ Adds ~200KB to bundle
• ❌ Different rendering engine (not browser DOM)
• ❌ Learning curve (different from web React)
• ❌ Limited CSS support (subset of Flexbox)

Effort: Medium (30-40 hours)

---

Approach 4: Hybrid (pdf-lib + @media print CSS)

Description: Use pdf-lib for complex reports (analytics, multi-page tables), provide @media print CSS for simpler reports (single-page lists). Fallback option for users who prefer browser print.

Pros:

• ✅ Flexibility (choose best tool per report)
• ✅ Fallback option (Ctrl+P always works)
• ✅ Progressive enhancement

Cons:

• ❌ Two code paths to maintain
• ❌ Inconsistent user experience
• ❌ More complex implementation

Effort: High (50-70 hours)

---

Recommendation: Approach 1 — pdf-lib

Rationale

1. Already Decided: Foundation architecture chose pdf-lib for small bundle size and modern API.
2. Bundle Size: ~150KB vs 500KB+ for jsPDF+html2canvas. Critical for Oracle VPS with limited RAM.
3. Performance: No DOM rendering overhead. Faster than html2canvas for large reports.
4. Control: Full control over pagination, headers, footers, table layout. Essential for professional reports.
5. Sufficiency: All 18 report types can be built with pdf-lib. No need for HTML-to-PDF complexity.
6. TypeScript: Modern, type-safe API. Better DX than jsPDF.

Tradeoffs Accepted

• Manual Layout: More code to write, but reusable templates reduce duplication.
• Learning Curve: Team needs to learn pdf-lib API, but documentation is good.
• No CSS: Can't reuse existing Tailwind styles, but consistent branding via templates.

---

Architecture

File Structure

src/lib/reports/
├── templates/
│   ├── header.ts          # Reusable header (logo, title, date, filters)
│   ├── footer.ts          # Reusable footer (page numbers, generation info, branding)
│   ├── table.ts           # Reusable table component (columns, rows, pagination)
│   ├── page.ts            # Page management (new page, margins, orientation)
│   └── types.ts           # Shared types (ReportOptions, TableColumn, etc.)
│
├── generators/
│   ├── inventory.ts       # Inventory reports (general, critical, restock, valuation)
│   ├── products.ts        # Product reports (by category, by supplier)
│   ├── movements.ts       # Movement history report
│   ├── purchases.ts       # Purchase reports (list, detail)
│   ├── customers.ts       # Customer reports (list, detail)
│   ├── sales.ts           # Sales reports (list, detail)
│   ├── profits.ts         # Profit reports (profits, cost evolution)
│   └── analytics.ts       # Analytics reports (seasonality, best sellers, low rotation)
│
├── utils.ts               # PDF utilities (formatting, calculations, helpers)
└── index.ts               # Public API (export all generators)

src/components/reports/
├── export-button.tsx      # Reusable export button with progress indicator
├── report-filters.tsx     # Filter form component (date range, categories, etc.)
├── report-header.tsx      # Client-side report header (for @media print alternative)
└── print-styles.css       # @media print styles (hide nav, show report)

src/app/(admin)/reports/
├── page.tsx               # Reports dashboard (list all 18 reports)
├── inventory/
│   ├── general/page.tsx
│   ├── critical/page.tsx
│   ├── restock/page.tsx
│   └── valuation/page.tsx
├── products/
│   ├── by-category/page.tsx
│   └── by-supplier/page.tsx
├── movements/page.tsx
├── purchases/
│   ├── page.tsx
│   └── [id]/page.tsx
├── customers/
│   ├── page.tsx
│   └── [id]/page.tsx
├── sales/
│   ├── page.tsx
│   └── [id]/page.tsx
├── profits/page.tsx
├── cost-evolution/page.tsx
└── analytics/
    ├── seasonality/page.tsx
    ├── best-sellers/page.tsx
    └── low-rotation/page.tsx

Template Pattern

Header Template (src/lib/reports/templates/header.ts):

export interface HeaderOptions {
  title: string;
  subtitle?: string;
  generatedAt: Date;
  period?: { from: Date; to: Date };
  filters?: Array<{ label: string; value: string }>;
  logo?: Uint8Array; // PNG bytes
}

export function drawHeader(
  pdfDoc: PDFDocument,
  page: PDFPage,
  font: PDFFont,
  options: HeaderOptions
): number {
  // Returns Y position after header (for content placement)
}

Footer Template (src/lib/reports/templates/footer.ts):

export interface FooterOptions {
  pageNumber: number;
  totalPages: number;
  totals?: Array<{ label: string; value: string }>;
  branding?: string; // "byrachel — www.byrachel.com"
}

export function drawFooter(
  page: PDFPage,
  font: PDFFont,
  options: FooterOptions
) {
  // Draw page numbers, totals, branding
}

Table Template (src/lib/reports/templates/table.ts):

export interface TableColumn {
  header: string;
  key: string;
  width: number; // percentage (0-1)
  align?: 'left' | 'center' | 'right';
  format?: (value: any) => string;
}

export interface TableOptions {
  columns: TableColumn[];
  rows: any[];
  startY: number;
  fontSize?: number;
  rowHeight?: number;
  headerColor?: RGB;
  alternateRowColor?: RGB;
}

export function drawTable(
  pdfDoc: PDFDocument,
  page: PDFPage,
  font: PDFFont,
  options: TableOptions
): { endY: number; pagesUsed: number } {
  // Handles pagination, row breaks, header repetition
}

Data Fetching Strategy

Server-Side (API Routes / Server Actions):

// src/app/api/reports/inventory/general/route.ts
import { db } from '@/lib/db';
import { variants, products, inventory, categories } from '@/lib/db/schema';
import { eq, and, sql } from 'drizzle-orm';

export async function GET(request: Request) {
  const { searchParams } = new URL(request.url);
  const categoryId = searchParams.get('categoryId');
  const location = searchParams.get('location');
  
  // Fetch data with filters
  const data = await db
    .select({
      sku: variants.sku,
      productName: products.name,
      size: variants.size,
      color: variants.color,
      stock: inventory.quantity,
      minStock: inventory.minStock,
      location: inventory.location,
      cost: variants.averageCost,
      value: sql`${inventory.quantity} * ${variants.averageCost}`,
    })
    .from(variants)
    .innerJoin(products, eq(variants.productId, products.id))
    .innerJoin(inventory, eq(variants.id, inventory.variantId))
    .leftJoin(categories, eq(products.categoryId, categories.id))
    .where(
      and(
        categoryId ? eq(products.categoryId, Number(categoryId)) : undefined,
        location ? eq(inventory.location, location) : undefined,
        eq(products.isActive, true),
        eq(variants.isActive, true)
      )
    );
  
  return Response.json(data);
}

Client-Side (Export Button):

// src/components/reports/export-button.tsx
'use client';

import { useState } from 'react';

export function ExportButton({ reportType, filters }: { reportType: string; filters: any }) {
  const [loading, setLoading] = useState(false);
  const [progress, setProgress] = useState(0);
  
  async function handleExport() {
    setLoading(true);
    setProgress(10);
    
    try {
      // Fetch data from API
      const response = await fetch(`/api/reports/${reportType}?${new URLSearchParams(filters)}`);
      const data = await response.json();
      setProgress(50);
      
      // Dynamically import pdf-lib
      const { PDFDocument } = await import('pdf-lib');
      setProgress(60);
      
      // Generate PDF
      const pdfDoc = await PDFDocument.create();
      const font = await pdfDoc.embedFont('Helvetica');
      
      // Call report generator
      const generator = await import(`@/lib/reports/generators/${reportType}`);
      await generator.default(pdfDoc, font, data);
      setProgress(90);
      
      // Download PDF
      const pdfBytes = await pdfDoc.save();
      const blob = new Blob([pdfBytes], { type: 'application/pdf' });
      const url = URL.createObjectURL(blob);
      const a = document.createElement('a');
      a.href = url;
      a.download = `${reportType}-${Date.now()}.pdf`;
      a.click();
      setProgress(100);
    } finally {
      setLoading(false);
      setTimeout(() => setProgress(0), 1000);
    }
  }
  
  return (
    <button onClick={handleExport} disabled={loading}>
      {loading ? `Exporting... ${progress}%` : 'Export PDF'}
    </button>
  );
}

PDF Generation Flow

User clicks "Export PDF"
         ↓
Client calls API route with filters
         ↓
Server fetches data from DB (with joins, aggregations)
         ↓
Server returns JSON data
         ↓
Client dynamically imports pdf-lib (~150KB)
         ↓
Client creates PDFDocument
         ↓
Client calls report generator (e.g., generateInventoryReport)
         ↓
Generator uses templates (header, footer, table)
         ↓
Generator handles pagination (split rows across pages)
         ↓
Client downloads PDF blob

Pagination Logic

// src/lib/reports/templates/table.ts
const A4_HEIGHT = 842; // points (297mm)
const MARGIN = 42.5; // 15mm
const FOOTER_HEIGHT = 50;

function calculateRowsPerPage(pageHeight: number, rowHeight: number, headerHeight: number): number {
  const availableHeight = pageHeight - MARGIN * 2 - FOOTER_HEIGHT - headerHeight;
  return Math.floor(availableHeight / rowHeight);
}

function paginateRows(rows: any[], rowsPerPage: number): any[][] {
  const pages: any[][] = [];
  for (let i = 0; i < rows.length; i += rowsPerPage) {
    pages.push(rows.slice(i, i + rowsPerPage));
  }
  return pages;
}

Logo Handling

// src/lib/reports/utils.ts
export async function loadLogo(pdfDoc: PDFDocument): Promise<Uint8Array | undefined> {
  try {
    const response = await fetch('/images/logo.png');
    if (!response.ok) return undefined;
    const arrayBuffer = await response.arrayBuffer();
    return new Uint8Array(arrayBuffer);
  } catch {
    return undefined; // Fallback to text-only header
  }
}

---

Affected Areas

New Files

• src/lib/reports/ — 15+ files (templates, generators, utils)
• src/components/reports/ — 4 files (export button, filters, print styles)
• src/app/(admin)/reports/ — 20+ files (report pages)
• src/app/api/reports/ — 18+ API routes (one per report type)

Modified Files

• package.json — Add pdf-lib dependency
• src/app/(admin)/reports/page.tsx — Replace placeholder with dashboard
• src/app/globals.css — Add @media print styles (optional)

Dependencies

• pdf-lib (~150KB) — PDF generation library
• No other dependencies — use existing Drizzle, React, Tailwind

Performance Impact

• Bundle Size: +150KB (lazy-loaded chunk, only loaded when user clicks export)
• Memory: Minimal (PDF generation is short-lived, garbage collected after download)
• CPU: Low (pdf-lib is fast, no DOM rendering)

---

Risks

1. Manual Layout Complexity

Risk: Writing table drawing, pagination, and positioning code is time-consuming and error-prone.
Mitigation: Create reusable templates (header, footer, table) to reduce duplication. Start with simple reports, iterate to complex ones.
Severity: Medium

2. Large Report Performance

Risk: Reports with thousands of rows (e.g., movement history) may be slow to generate or cause memory issues.
Mitigation:

• Limit default date range (e.g., last 90 days)
• Paginate data (fetch in chunks)
• Show progress indicator
• Test with large datasets early
  Severity: Medium

3. Font Limitations

Risk: pdf-lib's built-in fonts (Helvetica, Times, Courier) may not support special characters (ñ, á, é, í, ó, ú).
Mitigation:

• Use UTF-8 encoding (pdf-lib supports it)
• Test with Spanish text early
• If issues, embed custom font (adds ~50KB)
  Severity: Low

4. Logo Embedding

Risk: Logo may not be available (user hasn't uploaded it) or may be wrong format (JPEG instead of PNG).
Mitigation:

• Fallback to text-only header if logo not found
• Support both PNG and JPEG (pdf-lib has embedPng and embedJpg)
• Provide default logo in public/images/logo.png
  Severity: Low

5. Browser Compatibility

Risk: Dynamic import of pdf-lib may fail in older browsers.
Mitigation:

• pdf-lib supports all modern browsers (Chrome, Firefox, Safari, Edge)
• Provide fallback message for unsupported browsers
• Test on target browsers
  Severity: Low

6. Data Aggregation Complexity

Risk: Some reports (profits, analytics) require complex aggregations (JOINs, GROUP BY, calculations).
Mitigation:

• Create dedicated service functions for complex queries
• Test queries with realistic data volumes
• Use SQL aggregations (faster than in-memory)
  Severity: Medium

7. Print CSS Maintenance

Risk: Maintaining two code paths (pdf-lib + @media print) increases complexity.
Mitigation:

• Make @media print optional (not required for MVP)
• Focus on pdf-lib first, add print CSS later if needed
  Severity: Low

---

Ready for Proposal

Status: ✅ Ready

Next Steps:

1. Create proposal document with scope, approach, and acceptance criteria
2. Define report specifications (columns, filters, sorting for each of 18 reports)
3. Implement template system (header, footer, table)
4. Implement 2-3 sample reports (inventory general, sales list, sale detail)
5. Test with realistic data volumes
6. Iterate on remaining reports

Recommendation: Proceed to proposal phase with pdf-lib, reusable templates, and server-side data fetching. Start with inventory and sales reports as proof of concept.