> ## Documentation Index
> Fetch the complete documentation index at: https://docs.spn.wtf/llms.txt
> Use this file to discover all available pages before exploring further.

# SPN - Database

> Database management and migrations

# Database

SPN uses PostgreSQL with Alembic for migrations.

## Migrations

### Generate Migration

```bash theme={null}
cd backend
PYTHONPATH=. alembic revision --autogenerate -m "description"
```

### Apply Migrations

```bash theme={null}
cd backend
PYTHONPATH=. alembic upgrade head
```

### Rollback

```bash theme={null}
cd backend
PYTHONPATH=. alembic downgrade -1
```

### View History

```bash theme={null}
cd backend
PYTHONPATH=. alembic history --verbose
```

## Schema

### Core Tables

| Table              | Description         |
| ------------------ | ------------------- |
| `companies`        | Company profiles    |
| `users`            | User accounts       |
| `catalog_products` | Product catalog     |
| `contacts`         | Contact information |
| `enquiries`        | Leads/enquiries     |

### Full-Text Search

The `companies` table includes a `search_vector` column for fast search:

```sql theme={null}
SELECT * FROM companies
WHERE search_vector @@ plainto_tsquery('english', 'search term')
ORDER BY ts_rank(search_vector, plainto_tsquery('english', 'search term')) DESC;
```

## Connecting to Database

### From Host

```bash theme={null}
docker exec -it spn_postgres psql -U postgres -d spn_db
```

### From Container

```bash theme={null}
docker exec -it spn_backend-1 psql -U postgres -d spn_db
```

## Common Tasks

### Add a Column

1. Update model in `backend/app/models/domain.py`
2. Generate migration: `alembic revision --autogenerate`
3. Review the migration file
4. Apply: `alembic upgrade head`

### Reset Database

```bash theme={null}
docker-compose down -v
docker-compose up -d
alembic upgrade head
```

> Warning: This deletes all data!