# Database Setup

Capyshop requires PostgreSQL 17+ with the [pgvector](https://github.com/pgvector/pgvector) extension installed.

## Prerequisites

- PostgreSQL 17+ server with pgvector installed
- Access to the `postgres` superuser (or equivalent) for initial setup

## 1. Create the Database and User

Connect to your PostgreSQL server as a superuser (e.g. `postgres`) and run:

```sql
CREATE DATABASE your_database;
CREATE USER your_user WITH PASSWORD 'your_secure_password';
```

## 2. Configure Database Permissions

Connect to the newly created database and grant the required permissions:

```sql
-- Connect to the database
\c your_database

-- Grant privileges to the user
GRANT ALL PRIVILEGES ON DATABASE your_database TO your_user;
GRANT ALL ON SCHEMA public TO your_user;

-- Create the pgvector extension (requires superuser)
CREATE EXTENSION IF NOT EXISTS vector;
```

The `vector` extension must be created by a superuser. Once created, the application user can use it without superuser privileges.

## 3. Set the Connection URL

Set the `DATABASE_URL` environment variable with your connection details:

```
DATABASE_URL=postgresql://your_user:your_secure_password@localhost:5432/your_database
```

## Docker Compose Example

If using the custom PostgreSQL image with pgvector, you can automate the setup with an init script.

Create `init-db.sql`:

```sql
GRANT ALL PRIVILEGES ON DATABASE your_database TO your_user;
GRANT ALL ON SCHEMA public TO your_user;
CREATE EXTENSION IF NOT EXISTS vector;
```

Mount it in your `docker-compose.yml`:

```yaml
postgres:
  image: your-pgvector-image:latest
  environment:
    - POSTGRES_USER=postgres
    - POSTGRES_PASSWORD=postgres_password
    - POSTGRES_DB=your_database
  volumes:
    - ./init-db.sql:/docker-entrypoint-initdb.d/init-db.sql
    - postgres_data:/var/lib/postgresql/data
```

Then create the application user manually or via another init script:

```sql
-- In a second init script (e.g. 00-create-user.sql, runs before init-db.sql)
CREATE USER your_user WITH PASSWORD 'your_secure_password';
```

> **Note:** Scripts in `/docker-entrypoint-initdb.d/` only run when the PostgreSQL data directory is empty (first start). If the database already exists, run the SQL commands manually.

## Troubleshooting

| Error                                            | Cause                                            | Fix                                                                             |
| ------------------------------------------------ | ------------------------------------------------ | ------------------------------------------------------------------------------- |
| `permission denied for schema public`            | User lacks schema permissions                    | `GRANT ALL ON SCHEMA public TO your_user;`                                      |
| `permission denied to create extension "vector"` | Extension must be created by superuser           | Run `CREATE EXTENSION IF NOT EXISTS vector;` as superuser                       |
| `type "vector" does not exist`                   | pgvector extension not installed or not created  | Install pgvector in PostgreSQL and run `CREATE EXTENSION IF NOT EXISTS vector;` |
| `P3009 - failed migrations`                      | A previous migration failed and left dirty state | `DROP TABLE IF EXISTS _prisma_migrations CASCADE;` then re-run migrations       |
| `Connection url is empty`                        | `DATABASE_URL` not set                           | Ensure the env variable is set in your deployment environment                   |