SQL Views

The application uses SQL VIEWs to simplify common queries and improve maintainability.

v_invoice_analytics

Purpose: Shows invoice info with client and company names plus business calculations

What it does: Joins Invoice, Client, and Tenant tables and calculates days overdue, payment status, and value categories

Usage: Makes it easier to see which client each invoice belongs to and get business insights without writing complex queries

SELECT * FROM v_invoice_analytics WHERE invoice_status = 'PENDING';

v_client_financial_summary

Purpose: Shows total amounts for each client with risk analysis

What it does: Groups invoices by client and calculates total paid, pending, overdue amounts plus payment behavior and risk levels

Usage: Provides a complete overview of each client's financial status and payment history

SELECT * FROM v_client_financial_summary ORDER BY total_invoiced DESC;

API Integration

The views are used in API endpoints to provide simplified data access:

src/app/api/clients/summary/route.ts: Uses v_client_financial_summary view for detailed client analytics

src/app/api/invoices/totals/route.ts: Uses v_invoice_analytics view for invoice analytics

Benefits

Simpler queries: No need to write complex JOINs every time

Consistent data: Same calculation logic everywhere

Easier to maintain: Change the view, all queries get updated

Better performance: Database can optimize the view

The views are implemented in: sql/03_business_views.sql

Transactions

The application uses database transactions in several key operations:

• Invoice creation: When creating a new invoice, a transaction ensures that both the invoice and its items are created atomically
• Status updates: Invoice status changes are handled within transactions to maintain data consistency
• Client updates: When updating client information, related operations are wrapped in transactions

Transactions are implemented in the following files:

• src/app/api/invoices/[id]/route.ts: For updating and deleting invoices
• src/app/api/invoices/route.ts: For creating new invoices

Triggers

The system implements a sophisticated trigger system for invoice management:

• invoice_status_update_trigger: Automatically fires when an invoice status changes
  • Logs status changes in InvoiceStatusHistory
  • Creates notifications in InvoiceNotifications
  • Updates client status based on payment history
  • Automatically extends due dates when needed
  • Calculates and updates financial metrics

The trigger is implemented in sql/01_invoice_status_trigger.sql.

Indexes

Several optimized indexes are implemented for better query performance:

• Client email lookups:
  • idx_client_email: Basic email search optimization
  • idx_client_email_tenant: Composite index for tenant-specific email searches
  • idx_client_email_lower: Case-insensitive email searches
• Invoice management:
  • idx_invoice_status_history_invoice_id: Optimizes status history lookups
  • idx_invoice_status_history_changed_at: Improves date-based queries
  • idx_invoice_notifications_invoice_id: Speeds up notification retrieval
  • idx_invoice_notifications_processed: Partial index for unprocessed notifications

Using EXPLAIN on the Client table

• Before creating the index:

EXPLAIN ANALYZE SELECT * FROM "Client" WHERE email = 'client12@example.com';

• After adding index and creating 1000 rows:

  EXPLAIN ANALYZE SELECT * FROM "Client" WHERE email = 'client12@example.com';
  

Index Scan using idx_client_email on "Client"  (cost=0.28..8.29 rows=1 width=303)
  Index Cond: (email = 'client12@example.com'::text)