Changes between Version 12 and Version 13 of App

Timestamp:

09/08/25 23:47:08 (11 hours ago)

Author:

163080

Comment:

--

App

@@ -87 +87 @@
   - `idx_invoice_notifications_invoice_id`: Speeds up notification retrieval
   - `idx_invoice_notifications_processed`: Partial index for unprocessed notifications
-
+- Complex invoice analytics: (/agencyos/sql/04_complex_invoice_analytics_index.sql
+  - `idx_invoice_complex_analytics`: Multi-column index for tenant-specific analytics with status, currency, monthly grouping, and amount filtering
+  - `idx_invoice_overdue_analysis`: Optimized for overdue invoice queries with tenant, status, due date, and amount sorting
+  - `idx_invoice_financial_reporting`: Quarterly financial reporting with currency, status, and amount categorization
+  - `idx_invoice_number_search`: Case-insensitive invoice number search with tenant isolation
+  - `idx_client_invoice_performance`: Client performance metrics with status-based priority ordering
+  - `idx_invoice_temporal_analytics`: Time-series analysis with year, month, and day-of-week grouping
 
 Using `EXPLAIN` on the `Client` table
@@ -107 +113 @@
 }}}
 
+Using `EXPLAIN` on complex invoice analytics
+
+  - Multi-tenant analytics query:
+{{{
+EXPLAIN ANALYZE
+SELECT 
+    invoice_status,
+    invoice_currency,
+    DATE_TRUNC('month', invoice_issue_date) as month,
+    SUM(invoice_total_amount) as total_amount,
+    COUNT(*) as invoice_count
+FROM "Invoice" 
+WHERE tenant_id = 'tenant_123'
+  AND invoice_status IN ('PENDING', 'PROCESSING', 'OVERDUE', 'PAID')
+  AND invoice_issue_date >= '2024-01-01'
+  AND invoice_currency = 'EUR'
+GROUP BY invoice_status, invoice_currency, DATE_TRUNC('month', invoice_issue_date)
+ORDER BY month DESC, total_amount DESC;
+}}}
+
+  - Result with complex index:
+{{{
+Index Only Scan using idx_invoice_complex_analytics on "Invoice"  (cost=0.14..12.49 rows=11 width=24)
+  Index Cond: ((tenant_id = 'tenant_123') AND (invoice_currency = 'EUR'::"Currency") AND (invoice_issue_date >= '2024-01-01 00:00:00'::timestamp without time zone))
+  Heap Fetches: 11
+}}}
+
 [[Image(https://develop.finki.ukim.mk/projects/agency-os/raw-attachment/wiki/App/index.jpg)]]