Database Setup

Capyshop requires PostgreSQL 17+ with the 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:

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:

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

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:

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:

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