Skill: Invoice Generator Core

# Skill: Invoice Generator Core

## Purpose
Implement invoice CRUD, sequential invoice number generation, total computation, status transitions, client management, and the CSV/line-items schema.

---

## TypeScript Types

```typescript
// api/src/types.ts
export type InvoiceStatus = 'draft' | 'sent' | 'paid';
export type DerivedStatus  = InvoiceStatus | 'overdue';

export interface LineItem {
  id: string;          // UUID v4
  description: string;
  quantity: number;
  rate: number;
  // amount = quantity * rate, not stored
}

export interface Invoice {
  id: number;
  user_id: number;
  client_id: number;
  invoice_number: string;
  line_items: LineItem[];   // parsed from JSON
  currency: string;
  tax_rate: number;
  discount: number;
  notes: string | null;
  status: InvoiceStatus;
  issue_date: string;       // YYYY-MM-DD
  due_date: string;         // YYYY-MM-DD
  paid_at: string | null;
  sent_at: string | null;
  created_at: string;
  updated_at: string;
}

export interface InvoiceWithClient extends Invoice {
  client_name: string;
  client_email: string | null;
  client_address: string | null;
}

export interface Client {
  id: number;
  user_id: number;
  name: string;
  email: string | null;
  address: string | null;
  phone: string | null;
  created_at: string;
}

export interface UserSettings {
  user_id: number;
  invoice_prefix: string;
  next_sequence: number;
  currency: string;
  company_name: string | null;
  company_address: string | null;
  company_email: string | null;
  payment_terms: string | null;
  notes_default: string | null;
}

export interface Totals {
  subtotal: number;
  taxAmount: number;
  discountAmount: number;
  total: number;
}
```

---

## Invoice Number Generation

```typescript
// api/src/lib/invoiceNumber.ts
export function generateInvoiceNumber(prefix: string, sequence: number): string {
  const year = new Date().getFullYear();
  return `${prefix}-${year}-${String(sequence).padStart(4, '0')}`;
}

// Used in createInvoice route with atomic transaction
```

---

## Total Computation

```typescript
// api/src/lib/totals.ts  (same logic in web/src/lib/totals.ts)
import type { LineItem, Totals } from '../types.js';

export function computeTotals(
  lineItems: LineItem[],
  taxRate: number,
  discount: number,
): Totals {
  const subtotal = lineItems.reduce((sum, item) => {
    return sum + Math.round(item.quantity * item.rate * 100) / 100;
  }, 0);
  const taxAmount = Math.round(subtotal * taxRate) / 100;
  const total = Math.max(0, Math.round((subtotal + taxAmount - discount) * 100) / 100);
  return { subtotal, taxAmount, discountAmount: discount, total };
}
```

---

## Derived Status

```typescript
// api/src/lib/invoiceStatus.ts  (same in web/src/lib/invoiceStatus.ts)
import type { Invoice, DerivedStatus } from '../types.js';

export function getStatus(invoice: Pick<Invoice, 'status' | 'due_date'>): DerivedStatus {
  if (invoice.status !== 'sent') return invoice.status;
  const today = new Date().toISOString().slice(0, 10);
  return invoice.due_date < today ? 'overdue' : 'sent';
}
```

---

## Create Invoice Route (atomic sequence increment)

```typescript
// api/src/routes/invoices.ts
import { db } from '../db.js';
import { generateInvoiceNumber } from '../lib/invoiceNumber.js';
import type { LineItem, Invoice } from '../types.js';

export function createInvoice(
  userId: number,
  body: {
    client_id: number;
    line_items: LineItem[];
    currency?: string;
    tax_rate?: number;
    discount?: number;
    notes?: string;
    issue_date: string;
    due_date: string;
  }
): Invoice {
  // Atomic: read sequence, generate number, insert, increment - all in one transaction
  const result = db.transaction(() => {
    const settings = db.prepare(
      'SELECT invoice_prefix, next_sequence, currency FROM user_settings WHERE user_id = ?'
    ).get(userId) as { invoice_prefix: string; next_sequence: number; currency: string };

    const invoiceNumber = generateInvoiceNumber(settings.invoice_prefix, settings.next_sequence);

    const stmt = db.prepare(`
      INSERT INTO invoices (user_id, client_id, invoice_number, line_items, currency,
                            tax_rate, discount, notes, issue_date, due_date)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);
    const { lastInsertRowid } = stmt.run(
      userId,
      body.client_id,
      invoiceNumber,
      JSON.stringify(body.line_items),
      body.currency ?? settings.currency,
      body.tax_rate ?? 0,
      body.discount ?? 0,
      body.notes ?? null,
      body.issue_date,
      body.due_date,
    );

    db.prepare('UPDATE user_settings SET next_sequence = next_sequence + 1 WHERE user_id = ?').run(userId);

    return db.prepare('SELECT * FROM invoices WHERE id = ?').get(lastInsertRowid) as Invoice;
  })();

  return { ...result, line_items: JSON.parse(result.line_items as unknown as string) };
}
```

---

## Get Invoices List (with derived status)

```typescript
export function listInvoices(userId: number, statusFilter?: string): InvoiceWithClient[] {
  const today = new Date().toISOString().slice(0, 10);

  let query = `
    SELECT i.*, c.name AS client_name, c.email AS client_email, c.address AS client_address
    FROM invoices i
    JOIN clients c ON i.client_id = c.id
    WHERE i.user_id = ?
  `;
  const params: (string | number)[] = [userId];

  if (statusFilter === 'overdue') {
    query += ` AND i.status = 'sent' AND i.due_date < ?`;
    params.push(today);
  } else if (statusFilter && statusFilter !== 'all') {
    if (statusFilter === 'sent') {
      // sent but not overdue
      query += ` AND i.status = 'sent' AND i.due_date >= ?`;
      params.push(today);
    } else {
      query += ` AND i.status = ?`;
      params.push(statusFilter);
    }
  }

  query += ' ORDER BY i.created_at DESC';

  const rows = db.prepare(query).all(...params) as Array<InvoiceWithClient & { line_items: string }>;

  return rows.map(row => ({
    ...row,
    line_items: JSON.parse(row.line_items),
  }));
}
```

---

## Invoice Status Transitions

```typescript
// POST /api/invoices/:id/send
export function markSent(id: number, userId: number): void {
  const inv = getInvoiceForOwner(id, userId);
  if (inv.status !== 'draft') throw Object.assign(new Error('Only draft invoices can be sent'), { status: 422 });
  db.prepare(`
    UPDATE invoices SET status = 'sent', sent_at = strftime('%Y-%m-%dT%H:%M:%SZ','now'), updated_at = strftime('%Y-%m-%dT%H:%M:%SZ','now')
    WHERE id = ?
  `).run(id);
}

// POST /api/invoices/:id/paid
export function markPaid(id: number, userId: number, paidAt: string): void {
  const inv = getInvoiceForOwner(id, userId);
  if (inv.status !== 'sent') throw Object.assign(new Error('Only sent invoices can be marked paid'), { status: 422 });
  db.prepare(`
    UPDATE invoices SET status = 'paid', paid_at = ?, updated_at = strftime('%Y-%m-%dT%H:%M:%SZ','now')
    WHERE id = ?
  `).run(paidAt, id);
}

// POST /api/invoices/:id/void
export function voidInvoice(id: number, userId: number): void {
  const inv = getInvoiceForOwner(id, userId);
  if (inv.status !== 'sent') throw Object.assign(new Error('Only sent invoices can be voided'), { status: 422 });
  db.prepare(`
    UPDATE invoices SET status = 'draft', sent_at = NULL, updated_at = strftime('%Y-%m-%dT%H:%M:%SZ','now')
    WHERE id = ?
  `).run(id);
}

function getInvoiceForOwner(id: number, userId: number): Invoice {
  const inv = db.prepare('SELECT * FROM invoices WHERE id = ? AND user_id = ?').get(id, userId) as Invoice | undefined;
  if (!inv) throw Object.assign(new Error('Not found'), { status: 404 });
  return { ...inv, line_items: JSON.parse(inv.line_items as unknown as string) };
}
```

---

## Dashboard Stats Query

```typescript
export function getDashboardStats(userId: number): {
  outstanding: number;
  paidThisMonth: number;
  overdueCount: number;
  clientCount: number;
} {
  const today = new Date().toISOString().slice(0, 10);
  const monthStart = today.slice(0, 7) + '-01';

  // outstanding = sum of totals for sent invoices (including overdue)
  // We compute total from line_items JSON + tax_rate + discount
  // Easier: store total as a derived column or compute in app layer from all sent invoices
  const sentRows = db.prepare(`
    SELECT line_items, tax_rate, discount FROM invoices WHERE user_id = ? AND status = 'sent'
  `).all(userId) as Array<{ line_items: string; tax_rate: number; discount: number }>;

  const outstanding = sentRows.reduce((sum, row) => {
    const items: LineItem[] = JSON.parse(row.line_items);
    const { total } = computeTotals(items, row.tax_rate, row.discount);
    return sum + total;
  }, 0);

  const overdueCount = (db.prepare(`
    SELECT COUNT(*) AS c FROM invoices WHERE user_id = ? AND status = 'sent' AND due_date < ?
  `).get(userId, today) as { c: number }).c;

  const paidRows = db.prepare(`
    SELECT line_items, tax_rate, discount FROM invoices
    WHERE user_id = ? AND status = 'paid' AND paid_at >= ?
  `).all(userId, monthStart) as Array<{ line_items: string; tax_rate: number; discount: number }>;

  const paidThisMonth = paidRows.reduce((sum, row) => {
    const items: LineItem[] = JSON.parse(row.line_items);
    return sum + computeTotals(items, row.tax_rate, row.discount).total;
  }, 0);

  const { clientCount } = db.prepare(
    'SELECT COUNT(*) AS clientCount FROM clients WHERE user_id = ?'
  ).get(userId) as { clientCount: number };

  return { outstanding, paidThisMonth, overdueCount, clientCount };
}
```

---

## Duplicate Invoice

```typescript
// POST /api/invoices/:id/duplicate
export function duplicateInvoice(id: number, userId: number): Invoice {
  const orig = getInvoiceForOwner(id, userId);
  const today = new Date().toISOString().slice(0, 10);
  const due = new Date(Date.now() + 30 * 24 * 60 * 60 * 1000).toISOString().slice(0, 10);

  return createInvoice(userId, {
    client_id: orig.client_id,
    line_items: orig.line_items,
    currency: orig.currency,
    tax_rate: orig.tax_rate,
    discount: orig.discount,
    notes: orig.notes ?? undefined,
    issue_date: today,
    due_date: due,
  });
}
```

---

## Currency Formatting

```typescript
// web/src/lib/currency.ts
export const CURRENCIES = [
  { code: 'USD', label: 'US Dollar',       symbol: '$'  },
  { code: 'EUR', label: 'Euro',            symbol: '\u20AC' },
  { code: 'GBP', label: 'British Pound',   symbol: '\u00A3' },
  { code: 'CAD', label: 'Canadian Dollar', symbol: '$'  },
  { code: 'AUD', label: 'Australian Dollar', symbol: '$' },
  { code: 'JPY', label: 'Japanese Yen',    symbol: '\u00A5' },
  { code: 'CHF', label: 'Swiss Franc',     symbol: 'CHF' },
  { code: 'INR', label: 'Indian Rupee',    symbol: '\u20B9' },
] as const;

export function formatCurrency(amount: number, currency: string): string {
  return new Intl.NumberFormat('en-US', {
    style: 'currency',
    currency,
    minimumFractionDigits: 2,
    maximumFractionDigits: 2,
  }).format(amount);
}
```

---

## Vitest Tests

```typescript
// api/src/lib/__tests__/invoiceNumber.test.ts
import { describe, it, expect } from 'vitest';
import { generateInvoiceNumber } from '../invoiceNumber.js';

describe('generateInvoiceNumber', () => {
  it('pads sequence to 4 digits', () => {
    expect(generateInvoiceNumber('INV', 1)).toMatch(/^INV-\d{4}-0001$/);
  });

  it('includes current year', () => {
    const year = new Date().getFullYear();
    expect(generateInvoiceNumber('INV', 5)).toContain(String(year));
  });

  it('uses custom prefix', () => {
    expect(generateInvoiceNumber('ACME', 1)).toMatch(/^ACME-\d{4}-0001$/);
  });
});

// api/src/lib/__tests__/totals.test.ts
import { describe, it, expect } from 'vitest';
import { computeTotals } from '../totals.js';

describe('computeTotals', () => {
  it('computes subtotal as sum of qty * rate', () => {
    const items = [
      { id: '1', description: 'A', quantity: 2, rate: 100 },
      { id: '2', description: 'B', quantity: 1, rate: 50 },
    ];
    const { subtotal } = computeTotals(items, 0, 0);
    expect(subtotal).toBe(250);
  });

  it('computes tax correctly', () => {
    const items = [{ id: '1', description: 'A', quantity: 1, rate: 1000 }];
    const { taxAmount } = computeTotals(items, 20, 0);
    expect(taxAmount).toBe(200);
  });

  it('applies discount', () => {
    const items = [{ id: '1', description: 'A', quantity: 1, rate: 1000 }];
    const { total } = computeTotals(items, 0, 100);
    expect(total).toBe(900);
  });

  it('total is never negative', () => {
    const items = [{ id: '1', description: 'A', quantity: 1, rate: 50 }];
    const { total } = computeTotals(items, 0, 1000);
    expect(total).toBe(0);
  });
});
```

---

## pnpm Commands

```
pnpm --filter api dev         # API with tsx watch
pnpm --filter web dev         # Vite dev server
pnpm --filter api test        # Vitest
pnpm --filter web test        # Vitest
pnpm --filter api build       # TypeScript compile
pnpm --filter web build       # Vite production build
pnpm --filter api db:migrate  # run SQL migrations
```