source: README.md@ d7c72f6

# Project Setup Guide

This guide will help you set up and run the project locally.

## Prerequisites

- Node.js (v16.x or v18.x)
- Yarn (recommended) or npm
- Access to project's database credentials
- Google Cloud credentials

## Installation Steps

1. **Install Dependencies**

   ```bash
   yarn install
   # or using npm
   npm install --legacy-peer-deps
   ```

2. **Database Connection**

   Open a terminal and run the following SSH command to establish a connection to the database:

   ```bash
   ssh -L 5432:localhost:5432 t_agency_os@194.149.135.130 -N
   ```

   Keep this terminal window open while working with the application.

3. **Environment Setup**

   Create a `.env` file in the root directory and set the path to your service-account.json file:

   ```env
   GOOGLE_APPLICATION_CREDENTIALS="/path/to/your/service-account.json"
   ```

4. **Generate Prisma Client**

   ```bash
   yarn prisma:generate
   ```

5. **Seed Database (First-time setup)**

   If you're setting up the project for the first time and the database is empty, run:

   ```bash
   yarn db:seed
   ```

   This will create an initial tenant and user in the database.

6. **Start Development Server**
   ```bash
   yarn dev
   ```
   The application should now be running on `http://localhost:3000`

## Authentication

The application uses Firebase Authentication. You can log in with the following default credentials:

- Email: naum@mvpmasters.com
- Password: BnP2025~

## Available Scripts

- `yarn dev` - Start development server
- `yarn prisma:generate` - Generate Prisma client
- `yarn build` - Build for production
- `yarn start` - Start production server
- `yarn db:seed` - Seed the database with initial data

## Database Features

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

### 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`.

## Troubleshooting

If you encounter any issues:

- Ensure the SSH connection to the database is active
- Verify your Google Cloud credentials path is correct
- Check if all environment variables are properly set
- Make sure the database is properly seeded if you're setting up for the first time

## Support

For additional help or questions, please contact the development team.