Changes between Version 8 and Version 9 of App

Timestamp:

09/08/25 19:00:35 (11 hours ago)

Author:

163080

Comment:

--

App

@@ -1 @@
-**SQL Views**
+= SQL Views =
 
-The application uses SQL VIEWs to simplify common queries and improve maintainability:
+The application uses SQL VIEWs to simplify common queries and improve maintainability.
 
-`invoice_summary`
+== v_invoice_analytics ==
 
-- **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
+'''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 invoice_summary WHERE invoice_status = 'PENDING';
+SELECT * FROM v_invoice_analytics WHERE invoice_status = 'PENDING';
 }}}
 
-`client_totals`
+== v_client_financial_summary ==
 
-- **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
+'''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 client_totals ORDER BY total_invoiced DESC;
+SELECT * FROM v_client_financial_summary ORDER BY total_invoiced DESC;
 }}}
 
-API Integration
+= 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
+src/app/api/clients/summary/route.ts: Uses v_client_financial_summary view for detailed client analytics
 
-Benefits
+src/app/api/invoices/totals/route.ts: Uses v_invoice_analytics view for invoice analytics
 
-- **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
+= Benefits =
 
-The views are implemented in `sql/03_business_views.sql`.
+'''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**