Index: README.md =================================================================== --- README.md (revision d7c72f6cab53c531dde7c6ac9a9450a0c16b2fcc) +++ README.md (revision d808bc281c03f84bf239d4d696be946fdf1e0ed5) @@ -119,41 +119,46 @@ ### SQL Views -The application uses SQL VIEWs to simplify common queries and improve maintainability: +The application implements sophisticated SQL VIEWs for business intelligence and analytics: -#### `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**: Comprehensive invoice analytics with calculated business metrics +- **Features**: + - Combines invoice, client, and tenant data + - Calculates days overdue, payment status, value categories + - Provides age categorization and risk indicators + - Real-time business intelligence metrics -```sql -SELECT * FROM invoice_summary WHERE invoice_status = 'PENDING'; -``` +#### `v_client_financial_summary` -#### `client_totals` +- **Purpose**: Complete financial overview for each client +- **Features**: + - Total invoiced, paid, outstanding, and overdue amounts + - Payment behavior analysis and risk assessment + - Client performance metrics and historical data + - Automated risk level calculation -- **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 +#### Advanced Features -```sql -SELECT * FROM client_totals ORDER BY total_invoiced DESC; -``` +- **Complex business logic**: Multi-level conditional calculations +- **Performance optimization**: Strategic indexing for view performance +- **Data aggregation**: Multi-level aggregation for different time periods +- **Risk assessment**: Automated client risk evaluation algorithms #### API Integration -The views are used in API endpoints to provide simplified data access: +The views are integrated into API endpoints for comprehensive 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` for detailed client analytics +- `src/app/api/invoices/totals/route.ts`: Uses `v_invoice_analytics` 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 +- **Business Intelligence**: Pre-computed complex business metrics +- **Performance**: Optimized queries with strategic indexing +- **Maintainability**: Centralized business logic in database layer +- **Scalability**: Supports complex reporting requirements -The views are implemented in `sql/03_business_views.sql`. +The views are implemented in `sql/03_business_views.sql` with comprehensive indexing for optimal performance. ## Troubleshooting Index: sql/03_business_views.sql =================================================================== --- sql/03_business_views.sql (revision d7c72f6cab53c531dde7c6ac9a9450a0c16b2fcc) +++ sql/03_business_views.sql (revision d808bc281c03f84bf239d4d696be946fdf1e0ed5) @@ -1,19 +1,77 @@ -- ===================================================== --- 1. INVOICE SUMMARY VIEW +-- BUSINESS INTELLIGENCE VIEWS FOR AGENCY OS -- ===================================================== --- Shows basic invoice info with client name for easier querying +-- This file contains multiple SQL VIEWs that provide +-- business insights and simplify complex queries +-- ===================================================== -CREATE OR REPLACE VIEW invoice_summary AS +-- ===================================================== +-- 1. INVOICE ANALYTICS VIEW +-- ===================================================== +-- Provides comprehensive invoice analytics with calculated fields +-- This view combines invoice data with client and tenant information +-- and calculates business metrics like days overdue, payment status, etc. + +CREATE OR REPLACE VIEW v_invoice_analytics AS SELECT i.id, i.invoice_number, i.invoice_status, + i.invoice_currency, i.invoice_total_amount, - i.invoice_currency, + i.invoice_sub_total, + i.invoice_taxes, + i.invoice_discount, i.invoice_issue_date, i.invoice_due_date, + i.created_at, + i.updated_at, + + -- Client Information c.name as client_name, c.email as client_email, - t.company_name as company_name + c.client_status, + c.client_representative, + + -- Tenant Information + t.company_name as tenant_name, + t.company_email as tenant_email, + + -- Calculated Business Metrics + CASE + WHEN i.invoice_status = 'OVERDUE' THEN + EXTRACT(DAYS FROM (NOW() - i.invoice_due_date)) + ELSE NULL + END as days_overdue, + + CASE + WHEN i.invoice_status = 'OVERDUE' THEN + EXTRACT(DAYS FROM (NOW() - i.invoice_issue_date)) + ELSE + EXTRACT(DAYS FROM (NOW() - i.invoice_issue_date)) + END as days_since_issue, + + CASE + WHEN i.invoice_status = 'PAID' THEN 'PAID' + WHEN i.invoice_status = 'OVERDUE' THEN 'OVERDUE' + WHEN i.invoice_due_date < NOW() AND i.invoice_status IN ('PENDING', 'PROCESSING') THEN 'OVERDUE' + ELSE 'CURRENT' + END as payment_status, + + -- Revenue categorization + CASE + WHEN i.invoice_total_amount >= 10000 THEN 'HIGH_VALUE' + WHEN i.invoice_total_amount >= 1000 THEN 'MEDIUM_VALUE' + ELSE 'LOW_VALUE' + END as value_category, + + -- Age categorization + CASE + WHEN i.invoice_status = 'PAID' THEN 'PAID' + WHEN EXTRACT(DAYS FROM (NOW() - i.invoice_issue_date)) <= 30 THEN 'RECENT' + WHEN EXTRACT(DAYS FROM (NOW() - i.invoice_issue_date)) <= 90 THEN 'AGING' + ELSE 'OLD' + END as age_category + FROM "Invoice" i JOIN "Client" c ON i.client_id = c.id @@ -21,19 +79,72 @@ -- ===================================================== --- 2. CLIENT TOTALS VIEW +-- 2. CLIENT FINANCIAL SUMMARY VIEW -- ===================================================== --- Shows total amounts for each client (paid, pending, overdue) +-- Provides comprehensive financial summary for each client +-- Shows total invoiced, paid, outstanding, and overdue amounts -CREATE OR REPLACE VIEW client_totals AS +CREATE OR REPLACE VIEW v_client_financial_summary AS SELECT c.id as client_id, c.name as client_name, c.email as client_email, + c.client_status, + c.client_representative, + t.company_name as tenant_name, + + -- Invoice Counts COUNT(i.id) as total_invoices, - SUM(CASE WHEN i.invoice_status = 'PAID' THEN i.invoice_total_amount ELSE 0 END) as total_paid, - SUM(CASE WHEN i.invoice_status = 'PENDING' THEN i.invoice_total_amount ELSE 0 END) as total_pending, - SUM(CASE WHEN i.invoice_status = 'OVERDUE' THEN i.invoice_total_amount ELSE 0 END) as total_overdue, - SUM(i.invoice_total_amount) as total_invoiced + COUNT(CASE WHEN i.invoice_status = 'PAID' THEN 1 END) as paid_invoices, + COUNT(CASE WHEN i.invoice_status = 'PENDING' THEN 1 END) as pending_invoices, + COUNT(CASE WHEN i.invoice_status = 'OVERDUE' THEN 1 END) as overdue_invoices, + COUNT(CASE WHEN i.invoice_status = 'PROCESSING' THEN 1 END) as processing_invoices, + COUNT(CASE WHEN i.invoice_status = 'DRAFT' THEN 1 END) as draft_invoices, + + -- Financial Totals + COALESCE(SUM(i.invoice_total_amount), 0) as total_invoiced, + COALESCE(SUM(CASE WHEN i.invoice_status = 'PAID' THEN i.invoice_total_amount ELSE 0 END), 0) as total_paid, + COALESCE(SUM(CASE WHEN i.invoice_status IN ('PENDING', 'PROCESSING', 'OVERDUE') THEN i.invoice_total_amount ELSE 0 END), 0) as total_outstanding, + COALESCE(SUM(CASE WHEN i.invoice_status = 'OVERDUE' THEN i.invoice_total_amount ELSE 0 END), 0) as total_overdue, + + -- Average values + COALESCE(AVG(i.invoice_total_amount), 0) as average_invoice_amount, + COALESCE(AVG(CASE WHEN i.invoice_status = 'PAID' THEN i.invoice_total_amount END), 0) as average_paid_amount, + + -- Date ranges + MIN(i.invoice_issue_date) as first_invoice_date, + MAX(i.invoice_issue_date) as last_invoice_date, + + -- Risk indicators + CASE + WHEN COUNT(CASE WHEN i.invoice_status = 'OVERDUE' THEN 1 END) > 3 THEN 'HIGH_RISK' + WHEN COUNT(CASE WHEN i.invoice_status = 'OVERDUE' THEN 1 END) > 1 THEN 'MEDIUM_RISK' + ELSE 'LOW_RISK' + END as risk_level, + + -- Payment behavior + CASE + WHEN COUNT(i.id) = 0 THEN 'NO_HISTORY' + WHEN COUNT(CASE WHEN i.invoice_status = 'PAID' THEN 1 END)::FLOAT / COUNT(i.id) >= 0.9 THEN 'EXCELLENT' + WHEN COUNT(CASE WHEN i.invoice_status = 'PAID' THEN 1 END)::FLOAT / COUNT(i.id) >= 0.7 THEN 'GOOD' + WHEN COUNT(CASE WHEN i.invoice_status = 'PAID' THEN 1 END)::FLOAT / COUNT(i.id) >= 0.5 THEN 'FAIR' + ELSE 'POOR' + END as payment_behavior + FROM "Client" c LEFT JOIN "Invoice" i ON c.id = i.client_id -GROUP BY c.id, c.name, c.email; +JOIN "Tenant" t ON c.tenant_id = t.id +GROUP BY c.id, c.name, c.email, c.client_status, c.client_representative, t.company_name; + +-- ===================================================== +-- CREATE INDEXES FOR VIEW PERFORMANCE +-- ===================================================== + +-- Indexes to optimize view performance +CREATE INDEX IF NOT EXISTS idx_invoice_analytics_status ON "Invoice"(invoice_status); +CREATE INDEX IF NOT EXISTS idx_invoice_analytics_due_date ON "Invoice"(invoice_due_date); +CREATE INDEX IF NOT EXISTS idx_invoice_analytics_issue_date ON "Invoice"(invoice_issue_date); +CREATE INDEX IF NOT EXISTS idx_invoice_analytics_client_id ON "Invoice"(client_id); +CREATE INDEX IF NOT EXISTS idx_invoice_analytics_tenant_id ON "Invoice"(tenant_id); + +CREATE INDEX IF NOT EXISTS idx_client_financial_status ON "Client"(client_status); +CREATE INDEX IF NOT EXISTS idx_client_financial_tenant ON "Client"(tenant_id); Index: sql/04_complex_invoice_analytics_index.sql =================================================================== --- sql/04_complex_invoice_analytics_index.sql (revision d808bc281c03f84bf239d4d696be946fdf1e0ed5) +++ sql/04_complex_invoice_analytics_index.sql (revision d808bc281c03f84bf239d4d696be946fdf1e0ed5) @@ -0,0 +1,71 @@ +-- Complex invoice analytics indexes + +DROP INDEX IF EXISTS idx_invoice_complex_analytics; +DROP INDEX IF EXISTS idx_invoice_tenant_status_date_currency; +DROP INDEX IF EXISTS idx_invoice_overdue_analysis; + +-- Main analytics index +CREATE INDEX idx_invoice_complex_analytics ON "Invoice" ( + tenant_id, + invoice_status, + invoice_currency, + DATE_TRUNC('month', invoice_issue_date), + invoice_total_amount, + invoice_due_date, + invoice_issue_date +) +WHERE invoice_status IN ('PENDING', 'PROCESSING', 'OVERDUE', 'PAID'); + +-- Overdue invoices index +CREATE INDEX idx_invoice_overdue_analysis ON "Invoice" ( + tenant_id, + invoice_status, + invoice_due_date, + invoice_total_amount DESC, + client_id +) +WHERE invoice_status = 'OVERDUE'; + +-- Financial reporting index +CREATE INDEX idx_invoice_financial_reporting ON "Invoice" ( + tenant_id, + invoice_currency, + DATE_TRUNC('quarter', invoice_issue_date), + invoice_status, + invoice_total_amount +) +WHERE invoice_status != 'DRAFT'; + +-- Search index +CREATE INDEX idx_invoice_number_search ON "Invoice" ( + tenant_id, + LOWER(invoice_number), + invoice_status, + invoice_issue_date DESC +); + +-- Client performance index +CREATE INDEX idx_client_invoice_performance ON "Invoice" ( + client_id, + tenant_id, + invoice_status, + invoice_issue_date DESC, + invoice_total_amount DESC +) +WHERE invoice_status != 'DRAFT'; + +-- Time analysis index +CREATE INDEX idx_invoice_temporal_analytics ON "Invoice" ( + tenant_id, + EXTRACT(YEAR FROM invoice_issue_date), + EXTRACT(MONTH FROM invoice_issue_date), + EXTRACT(DOW FROM invoice_issue_date), + invoice_status, + invoice_currency, + invoice_total_amount +) +WHERE invoice_issue_date >= '2020-01-01'; + +ANALYZE "Invoice"; +ANALYZE "Client"; +ANALYZE "Tenant"; Index: src/app/api/clients/summary/route.ts =================================================================== --- src/app/api/clients/summary/route.ts (revision d7c72f6cab53c531dde7c6ac9a9450a0c16b2fcc) +++ src/app/api/clients/summary/route.ts (revision d808bc281c03f84bf239d4d696be946fdf1e0ed5) @@ -13,18 +13,23 @@ const { userId, tenantId } = authResult; - // Use the simple client_totals view + // Use the comprehensive client financial summary view const clientSummary = await prisma.$queryRaw` SELECT client_name, client_email, + client_status, total_invoices, + paid_invoices, + pending_invoices, + overdue_invoices, + total_invoiced, total_paid, - total_pending, + total_outstanding, total_overdue, - total_invoiced - FROM client_totals - WHERE client_id IN ( - SELECT id FROM "Client" WHERE tenant_id = ${tenantId} - ) + average_invoice_amount, + risk_level, + payment_behavior + FROM v_client_financial_summary + WHERE tenant_id = ${tenantId} ORDER BY total_invoiced DESC `; Index: src/app/api/invoices/totals/route.ts =================================================================== --- src/app/api/invoices/totals/route.ts (revision d7c72f6cab53c531dde7c6ac9a9450a0c16b2fcc) +++ src/app/api/invoices/totals/route.ts (revision d808bc281c03f84bf239d4d696be946fdf1e0ed5) @@ -27,14 +27,14 @@ } - // Calculate totals using raw SQL + // Calculate totals using the business intelligence view const totals = await prisma.$queryRaw` SELECT - status, - currency, - SUM("total_amount") as total - FROM "Invoice" - WHERE "issue_date" >= ${startDate} - AND "tenant_id" = ${tenantId} - GROUP BY status, currency + invoice_status as status, + invoice_currency as currency, + SUM(invoice_total_amount) as total + FROM v_invoice_analytics + WHERE invoice_issue_date >= ${startDate} + AND tenant_id = ${tenantId} + GROUP BY invoice_status, invoice_currency `;