Changes between Version 5 and Version 6 of App

Timestamp:

09/04/25 23:42:28 (8 weeks ago)

Author:

163080

Comment:

--

App

@@ +1 @@
+**SQL Views**
+
+The application uses SQL VIEWs to simplify common queries and improve maintainability:
+
+#### `invoice_summary`
+
+- **Purpose**: Shows invoice info with client and company names
+- **What it does**: Joins Invoice, Client, and Tenant tables to display invoice details with client name and company name
+- **Usage**: Makes it easier to see which client each invoice belongs to without writing complex JOINs
+
+```sql
+SELECT * FROM invoice_summary WHERE invoice_status = 'PENDING';
+```
+
+#### `client_totals`
+
+- **Purpose**: Shows total amounts for each client
+- **What it does**: Groups invoices by client and calculates total paid, pending, overdue amounts
+- **Usage**: Provides a quick overview of each client's financial status
+
+```sql
+SELECT * FROM client_totals 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 `client_totals` view for client financial summaries
+- `src/app/api/invoices/totals/route.ts`: Uses `invoice_summary` 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**