# PostgreSQL

Windshift supports PostgreSQL as an alternative to SQLite for teams that need higher write concurrency or integration with existing database infrastructure.

## Connection String

Pass a PostgreSQL connection string via CLI flag or environment variable:

```bash
# CLI flag
./windshift --postgres-connection-string "postgres://user:password@localhost:5432/windshift?sslmode=disable"

# Short flag
./windshift --pg-conn "postgres://user:password@localhost:5432/windshift?sslmode=disable"

# Environment variable
export POSTGRES_CONNECTION_STRING="postgres://user:password@localhost:5432/windshift?sslmode=disable"
./windshift
```

When a PostgreSQL connection string is provided, Windshift ignores the `--db` flag and uses PostgreSQL instead of SQLite.

## Connection Pool

Windshift configures the PostgreSQL connection pool with:

| Setting | Default |
|---------|---------|
| Max open connections | 200 |
| Max idle connections | 100 |

These defaults handle most production workloads. The pool is managed by Go's `database/sql` package.

## Docker Compose Setup

```yaml
services:
  windshift:
    image: ghcr.io/windshiftapp/windshift:latest
    restart: unless-stopped
    ports:
      - "8080:8080"
    environment:
      - BASE_URL=https://windshift.example.com
      - SSO_SECRET=${SSO_SECRET}
      - POSTGRES_CONNECTION_STRING=postgres://windshift:${POSTGRES_PASSWORD}@postgres:5432/windshift?sslmode=disable
      - ATTACHMENT_PATH=/data/attachments
    volumes:
      - windshift-data:/data
    depends_on:
      postgres:
        condition: service_healthy

  postgres:
    image: postgres:17-alpine
    restart: unless-stopped
    environment:
      - POSTGRES_USER=windshift
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
      - POSTGRES_DB=windshift
    volumes:
      - postgres-data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U windshift"]
      interval: 5s
      timeout: 5s
      retries: 5

volumes:
  windshift-data:
  postgres-data:
```

## Schema Management

Windshift manages database schemas automatically. On startup it runs embedded migration files for all tables (items, users, tests, workflows, portal, notifications, permissions, etc.). SQL placeholders are automatically converted between SQLite (`?`) and PostgreSQL (`$1, $2, ...`) syntax.

No manual schema setup or migration commands are required.

## SSL/TLS

For production PostgreSQL connections, use `sslmode` in the connection string:

```bash
# Require SSL
--pg-conn "postgres://user:pass@db.example.com:5432/windshift?sslmode=require"

# Verify server certificate
--pg-conn "postgres://user:pass@db.example.com:5432/windshift?sslmode=verify-full&sslrootcert=/path/to/ca.pem"
```

## Backups

Use standard PostgreSQL backup tools:

```bash
# Logical backup
pg_dump -U windshift windshift > backup.sql

# Compressed backup
pg_dump -U windshift -Fc windshift > backup.dump

# Restore
pg_restore -U windshift -d windshift backup.dump
```

## Migrating from SQLite

To migrate an existing SQLite database to PostgreSQL:

1. Set up a fresh PostgreSQL database
2. Start Windshift with the PostgreSQL connection string - it will create all tables
3. Export data from SQLite and import into PostgreSQL using standard tooling

There is no built-in migration command at this time.