# PostgreSQL Memory Plan

This document defines the first local memory database target for Clawdie.

## Decision

Use a dedicated FreeBSD jail named `db` running:

- PostgreSQL `18`
- later `pgvector`

Canonical starting identity:

- jail: `db`
- hostname: `db.clawdie.si`
- provisioning: `thick`
- networking: `vnet` for the current deployment path, `shared` remains a valid install option

This is the preferred local memory backend over trying to reproduce a full local Supabase stack immediately.

## Why

- native FreeBSD packages
- good fit for ZFS-backed jails
- one database can hold relational memory data and vectors
- lower operational complexity than a Linux VM or multiple specialized services

## Thick vs Thin

Use a thick jail for `db`.

Reason:

- database is a persistent service, not an ephemeral worker
- upgrades and rollback should be self-contained
- host coupling should be minimized
- ZFS snapshots are more useful when the jail shape is stable

## Initial scope

First installation milestone:

1. create or prepare the `db` jail
2. install PostgreSQL 18
3. enable `allow.sysvipc` for the jail
4. initialize and start the service
5. validate local access
6. snapshot

Deferred:

- `pgvector`
- memory schema
- embedding generation
- REST API layer

## Install-time questions

This should become an explicit deployment choice, not a hidden default.

Recommended question set:

1. network profile
   - `shared`
   - `vnet`

2. resource profile
   - `minimal` = `1G RAM / 10G / 1 vCPU`
   - `balanced` = `2G RAM / 15G / 1 vCPU`

3. restore mode
   - `empty`
   - `restore from dump`

Recommended first-install default:

- network: `shared`
- resources: `minimal`
- restore: `empty`

Current chosen deployment path for this host:

- network: `vnet`
- IP: `192.168.100.2`
- bridge: `warden0`
- gateway: `192.168.100.1`

Canonical create command for that path:

```sh
sudo bastille create -T -B -g 192.168.100.1 db 15.0-RELEASE 192.168.100.2/24 warden0
```

If a VNET `db` jail comes up without a `default` route, treat that as a provisioning defect:

- the create path is missing the explicit `-g 192.168.100.1` flag
- fix the create command rather than adding the route manually and forgetting the root cause

If `pkg install` still fails with:

- `Transient resolver failure`

then the likely remaining host-side problems are:

1. missing `pf` NAT for `192.168.100.0/24`
2. no `pass quick on warden0 ... keep state`
3. Bastille copied the host Tailscale resolver file into the jail

Useful real-world signal from the archived Bastille console log:

- `add net default: gateway 51.83.197.1 ... Invalid argument`

Interpretation:

- Bastille tried to use the host uplink gateway
- the correct gateway for the Warden subnet is `192.168.100.1`
- this is evidence of a bad VNET create path, not a PostgreSQL defect

Another required jail-side prerequisite discovered during real bring-up:

```sh
sudo bastille config db set allow.sysvipc 1
sudo bastille restart db
```

Without that, `service postgresql initdb` can fail with shared-memory errors.

## Restore path

The deployment design should support restore from the start.

Target future flow:

1. create `db`
2. install PostgreSQL 18
3. enable `allow.sysvipc`
4. initialize cluster
5. optionally restore from `.sql` or PostgreSQL custom dump
6. validate
7. snapshot

## Baseline resources

- minimal: `1G RAM / 10G / 1 vCPU`
- balanced: `2G RAM / 15G / 1 vCPU`

## Snapshot points

- `@fresh`
- `@postgres18-ready`
- `@pre-schema`
- `@pre-pgvector`

## ZFS note

Two different decisions matter here:

1. `ashift`
2. dataset properties

`ashift` is a pool/vdev decision and cannot be changed later. For modern 4 KiB devices, the expected value is usually `12`.

Dataset-level settings can still be tuned later. Conservative starting settings for PostgreSQL data are:

- `compression=lz4`
- `atime=off`
- `recordsize=16K`

## Current role split

- SQLite: operational state today
- PostgreSQL: future long-term memory backend


## Next snapshot policy step

Once `db` is stable, the next Sanoid target should be `controlplane`, with lighter retention than the database:

- `db`: stronger retention as `critical-data`
- `controlplane`: moderate retention as `persistent-service`

## Validation Note

The PostgreSQL 18 installation path documented here was validated on
`08.03.2026` by Sam & Codex in the `db` jail on the current FreeBSD host.
