# Database Setup

EntryTarget uses PostgreSQL (via Amazon RDS) with a master-replica architecture.

## Schema

The database schema is provided as a single SQL file (`schema.sql`). Apply it to a fresh database:

```bash
psql -c "CREATE DATABASE ledger;"
psql ledger < schema.sql
```

There are no migrations — the schema file is the complete, authoritative definition. Apply it once to a new database.

## Architecture

| Pool    | Target           | Purpose                                               |
| ------- | ---------------- | ----------------------------------------------------- |
| Master  | RDS Master       | All write operations                                  |
| Replica | RDS Read Replica | All read operations (GET endpoints, integrity checks) |

### Why a Read Replica?

All GET queries are routed to the read replica, keeping the master fully dedicated to write operations (batch commits). This separation significantly improves throughput under load.

A read replica is recommended but not strictly required for low-volume deployments. For development, both URLs can point to the same database.

## Key Schema Decisions

### No Postgres ENUMs

All policy and operation columns use `varchar(20)` instead of PostgreSQL ENUMs. Validation is performed in the application layer. This avoids driver cast issues and simplifies future changes.

### Timestamps

All `created_at` columns use `timestamp(3)` (millisecond precision) with `DEFAULT NOW()`. The application never sends timestamps — they are always generated by PostgreSQL inside the transaction. This eliminates clock skew issues.

### UUIDs

All IDs are UUID v7 (sequential by timestamp), generated by the application. B-tree indexes on UUID v7 behave nearly as efficiently as `bigserial` indexes, with no fragmentation over time.

### Per-Table Autovacuum

The schema includes per-table autovacuum settings optimized for each table's write pattern. See [Autovacuum Tuning](/docs/maintenance/autovacuum.md) for details.

## Connection Pool

The `LEDGER_DB_POOL_SIZE` variable sets the maximum number of connections per pool. There are two pools (master + replica), so the total connection count is `2 * LEDGER_DB_POOL_SIZE`.

**Recommended settings:**

| Workload      | Pool Size | Total Connections |
| ------------- | --------- | ----------------- |
| Low volume    | 10        | 20                |
| Medium volume | 20        | 40                |
| High volume   | 40        | 80                |

Ensure your RDS instance's `max_connections` parameter is set high enough to accommodate the pool size plus any administrative connections.

## TLS

In production, set `LEDGER_DB_TLS=true`. The RDS CA certificate is included in the Amazon Linux system trust store by default — no additional configuration needed.

For local development, set `LEDGER_DB_TLS=false`.

## RDS Parameter Group

Recommended settings for your RDS Parameter Group:

| Parameter                      | Value   | Notes                          |
| ------------------------------ | ------- | ------------------------------ |
| `autovacuum`                   | `on`    | Default — do not change        |
| `autovacuum_max_workers`       | `3`     | Default, sufficient            |
| `autovacuum_naptime`           | `30`    | Default is 60s, can reduce     |
| `autovacuum_vacuum_cost_delay` | `2`     | Default on RDS, already good   |
| `maintenance_work_mem`         | `256MB` | Default is low, increase helps |


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://entrytarget.gitbook.io/docs/operations/database-setup.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.