**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**

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)
}}}

[[Image(https://develop.finki.ukim.mk/projects/agency-os/raw-attachment/wiki/App/index.jpg)]]