`DATABASE.md`

284 lines
# Database Schema and Models Documentation

## Overview

This project uses PostgreSQL as the database for storing property information and guest inquiries. The database includes proper constraints, indexes, and error handling.

## Database Connection

### Configuration

Database connection is configured through environment variables in `.env` file:

```env
DB_HOST=localhost
DB_PORT=5432
DB_NAME=jill_website
DB_USER=postgres
DB_PASSWORD=your_password_here
```

### Connection Module

Located in `src/config/database.ts`, provides:

- **getPool()**: Returns a singleton PostgreSQL connection pool
- **testConnection()**: Tests database connectivity
- **closePool()**: Closes the connection pool gracefully

### Error Handling

- Connection errors are logged and cause process exit
- Connection timeouts are set to 2 seconds
- Idle connections timeout after 30 seconds
- Pool maintains up to 20 concurrent connections

## Database Schema

### Inquiries Table

Stores guest inquiry information submitted through the contact form.

```sql
CREATE TABLE inquiries (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone VARCHAR(50) NOT NULL,
  check_in_date DATE NOT NULL,
  check_out_date DATE NOT NULL,
  guest_count INTEGER NOT NULL CHECK (guest_count > 0),
  message TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT valid_dates CHECK (check_out_date > check_in_date)
);
```

**Indexes:**
- `idx_inquiries_email`: Fast email lookups
- `idx_inquiries_check_in`: Availability queries
- `idx_inquiries_created_at`: Recent inquiries sorting

**Constraints:**
- Guest count must be positive
- Check-out date must be after check-in date
- All fields are required

### Properties Table

Stores luxury property information including amenities and images.

```sql
CREATE TABLE properties (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE,
  description TEXT NOT NULL,
  amenities JSONB NOT NULL,
  images JSONB NOT NULL,
  airbnb_url VARCHAR(500) NOT NULL,
  capacity INTEGER CHECK (capacity > 0),
  bedrooms INTEGER CHECK (bedrooms >= 0),
  bathrooms NUMERIC(3,1) CHECK (bathrooms >= 0),
  size_sqm NUMERIC(10,2) CHECK (size_sqm >= 0),
  price_per_night NUMERIC(10,2) CHECK (price_per_night >= 0),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```

**Indexes:**
- `idx_properties_name`: Fast property name lookups

**Constraints:**
- Property name must be unique
- Capacity must be positive
- Numeric fields cannot be negative
- Amenities and images stored as JSONB arrays

## Models

### InquiryModel

Located in `src/models/Inquiry.ts`

**Interface:**
```typescript
interface Inquiry {
  id?: number;
  name: string;
  email: string;
  phone: string;
  check_in_date: Date;
  check_out_date: Date;
  guest_count: number;
  message: string;
  created_at?: Date;
  updated_at?: Date;
}
```

**Methods:**
- `create(inquiry)`: Creates new inquiry with validation
- `findById(id)`: Retrieves inquiry by ID
- `findAll(limit, offset)`: Lists all inquiries with pagination
- `update(id, inquiry)`: Updates existing inquiry
- `delete(id)`: Deletes inquiry by ID

**Validation:**
- Name, email, phone, message required
- Email format validation (regex)
- Date validation (check-out after check-in)
- Guest count must be at least 1

### PropertyModel

Located in `src/models/Property.ts`

**Interface:**
```typescript
interface Property {
  id?: number;
  name: string;
  description: string;
  amenities: string[];
  images: string[];
  airbnb_url: string;
  capacity?: number;
  bedrooms?: number;
  bathrooms?: number;
  size_sqm?: number;
  price_per_night?: number;
  created_at?: Date;
  updated_at?: Date;
}
```

**Methods:**
- `create(property)`: Creates new property with validation
- `findById(id)`: Retrieves property by ID
- `findAll(limit, offset)`: Lists all properties with pagination
- `update(id, property)`: Updates existing property
- `delete(id)`: Deletes property by ID

**Validation:**
- Name, description, amenities, images, airbnb_url required
- At least one amenity and image required
- All image URLs must be valid
- Airbnb URL must be valid
- Numeric fields must be non-negative

## Database Migrations

### Running Migrations

```bash
npm run migrate
```

### Migration Files

Located in `src/migrations/`:

- `001_create_tables.sql`: Creates inquiries and properties tables with indexes
- `migrate.ts`: Migration runner script

### Migration Process

1. Tests database connection
2. Reads all `.sql` files in migrations directory
3. Executes files in alphabetical order
4. Logs success/failure for each migration
5. Closes connection pool on completion

## Usage Examples

### Creating an Inquiry

```typescript
import { InquiryModel } from './models/Inquiry';

const inquiryModel = new InquiryModel();

const newInquiry = await inquiryModel.create({
  name: 'John Doe',
  email: 'john@example.com',
  phone: '+1-555-0123',
  check_in_date: new Date('2025-02-01'),
  check_out_date: new Date('2025-02-08'),
  guest_count: 4,
  message: 'Looking to book Casita del Sol for a week'
});
```

### Creating a Property

```typescript
import { PropertyModel } from './models/Property';

const propertyModel = new PropertyModel();

const newProperty = await propertyModel.create({
  name: 'Casita del Sol',
  description: 'First floor oceanfront villa with private entrance',
  amenities: ['Saltwater Pool', 'Beach Access', 'Full Kitchen', 'WiFi'],
  images: [
    'https://example.com/image1.jpg',
    'https://example.com/image2.jpg'
  ],
  airbnb_url: 'https://airbnb.com/rooms/12345',
  capacity: 4,
  bedrooms: 1,
  bathrooms: 1.5,
  size_sqm: 76,
  price_per_night: 250
});
```

### Testing Connection

```typescript
import { testConnection } from './config/database';

try {
  await testConnection();
  console.log('Database connected successfully');
} catch (error) {
  console.error('Database connection failed:', error);
}
```

## Error Handling

All model methods include comprehensive error handling:

- Validation errors throw detailed messages
- Database errors are caught and logged
- Error messages include original error details
- Connection errors trigger automatic reconnection

## Type Safety

All models are fully typed with TypeScript interfaces, providing:

- Compile-time type checking
- IDE autocomplete support
- Clear API documentation
- Prevention of runtime type errors

## Setup Instructions

1. Install PostgreSQL on your system
2. Create database: `createdb jill_website`
3. Copy `.env.example` to `.env` and configure credentials
4. Run migrations: `npm run migrate`
5. Test connection in your application

## Performance Considerations

- Connection pooling (max 20 connections)
- Proper indexes on frequently queried fields
- JSONB for flexible amenities/images storage
- Automatic timestamp updates via triggers
- Query result pagination support