| | 1 | ### Transactions |
| | 2 | |
| | 3 | The application uses database transactions in several key operations: |
| | 4 | |
| | 5 | - Invoice creation: When creating a new invoice, a transaction ensures that both the invoice and its items are created atomically |
| | 6 | - Status updates: Invoice status changes are handled within transactions to maintain data consistency |
| | 7 | - Client updates: When updating client information, related operations are wrapped in transactions |
| | 8 | |
| | 9 | Transactions are implemented in the following files: |
| | 10 | |
| | 11 | - `src/app/api/invoices/[id]/route.ts`: For updating and deleting invoices |
| | 12 | - `src/app/api/invoices/route.ts`: For creating new invoices |
| | 13 | |
| | 14 | ### Triggers |
| | 15 | |
| | 16 | The system implements a sophisticated trigger system for invoice management: |
| | 17 | |
| | 18 | - `invoice_status_update_trigger`: Automatically fires when an invoice status changes |
| | 19 | - Logs status changes in `InvoiceStatusHistory` |
| | 20 | - Creates notifications in `InvoiceNotifications` |
| | 21 | - Updates client status based on payment history |
| | 22 | - Automatically extends due dates when needed |
| | 23 | - Calculates and updates financial metrics |
| | 24 | |
| | 25 | The trigger is implemented in `sql/01_invoice_status_trigger.sql`. |
| | 26 | |
| | 27 | ### Indexes |
| | 28 | |
| | 29 | Several optimized indexes are implemented for better query performance: |
| | 30 | |
| | 31 | - Client email lookups: |
| | 32 | - `idx_client_email`: Basic email search optimization |
| | 33 | - `idx_client_email_tenant`: Composite index for tenant-specific email searches |
| | 34 | - `idx_client_email_lower`: Case-insensitive email searches |
| | 35 | - Invoice management: |
| | 36 | - `idx_invoice_status_history_invoice_id`: Optimizes status history lookups |
| | 37 | - `idx_invoice_status_history_changed_at`: Improves date-based queries |
| | 38 | - `idx_invoice_notifications_invoice_id`: Speeds up notification retrieval |
| | 39 | - `idx_invoice_notifications_processed`: Partial index for unprocessed notifications |
