# PostgreSQL Database Setup

OnCallAlert requires a dedicated PostgreSQL database instance (version 16 or newer; PostgreSQL 17+ recommended). PostgreSQL stores user profiles, escalation policies, rotations, incident history, audit logs, and encrypted secrets.

## Database Extensions

OnCallAlert requires the `pgcrypto` extension for password hashing, token generation, and cryptographic operations.

On initial startup, OnCallAlert attempts to create the extension automatically if database privileges allow:

```sql
CREATE EXTENSION IF NOT EXISTS pgcrypto;
```

If connecting with a restricted non-superuser role, have your database administrator create the extension beforehand.

## Connection String Configuration

Configure the database connection via `ONCALLALERT_DB_URL`:

```bash
export ONCALLALERT_DB_URL="postgres://user:password@your-postgres:5432/oncallalert?sslmode=require"
```

### Connection String Parameters

- `sslmode`: Set to `require`, `verify-ca`, or `verify-full` in production environments.
- `pool_max_conns`: Maximum connection pool size (defaults to sensible pool limits).
- `connect_timeout`: Timeout in seconds for establishing database connections.

## Secret Encryption at Rest

Set `ONCALLALERT_DATA_ENCRYPTION_KEY` to a high-entropy passphrase or random secret (such as the output of `openssl rand -hex 32`):

```bash
export ONCALLALERT_DATA_ENCRYPTION_KEY="a-long-random-secret"
```

This key encrypts third-party integration tokens, AI provider API keys, and notification channel secrets. **Always back up this key.** If lost, encrypted integration secrets cannot be recovered.

## Online Database Switchover (Zero Downtime)

OnCallAlert features a built-in switchover engine (`swo`) for migrating between PostgreSQL instances without service interruption:

1. Set `ONCALLALERT_DB_URL_NEXT` to the connection URI of the new database instance.
2. The engine replicates schema and table contents, mirrors real-time transactions, and validates data consistency.
3. Once synchronized, the server seamlessly shifts read and write queries to the new database without dropping in-flight alerts.
4. Remove `ONCALLALERT_DB_URL_NEXT` and point `ONCALLALERT_DB_URL` to the new instance.

## Operating System & Memory Tuning

When running PostgreSQL on Linux hosts (e.g. Debian, Ubuntu), review Linux virtual memory overcommit (`vm.overcommit_memory`) to prevent the Linux OOM killer from terminating PostgreSQL worker processes under memory pressure.
