# PostgreSQL database

An optional PostgreSQL 15 database and Node.js `pg` client in the website’s private
container. Existing catalog features continue using SQLite; this module adds a
database for custom website functionality.

## Install

Use the verified installer in the website container. It installs dependencies, checks checksums, and refuses to replace customer changes:

```sh
python /opt/runalio/catalog_install.py postgres-database
python /opt/runalio/catalog_install.py --status
```

Review the preview and click Publish to promote backend code and pages. Production data remains separate.

To undo the last installation before publishing: `python /opt/runalio/catalog_install.py --rollback`.

## What the customer needs to do

1. Ask the coding agent to install `postgres-database`.
2. Open **Settings → Website modules → PostgreSQL**. Choose a verified custom
   domain, or the website’s assigned Runalio hostname, and click **Create database**.
3. Setup generates a database and application role with an encrypted password.
   `crumbandbuttercookies.com` becomes `crumbandbuttercookies_com`. Names exceeding
   PostgreSQL’s 63-byte limit have a deterministic hash suffix. The initial name
   remains fixed if the website later changes domain.
4. Readiness is shown separately for preview and live. Preview uses a separate
   `<name>_preview` database in its editing container. Publishing code does not
   copy preview data into production.
5. Review and Publish your pages. Paid production runs in the chosen EC2 region.

## Node.js access

Add custom features with names such as `features/z-customer-records.js`, so they
load after the PostgreSQL feature. Use the supplied pool; never expose its
credentials, database access or unrestricted SQL through a public endpoint.

```js
export default async function install(app) {
  if (!app.postgres) return; // Owner has not completed setup yet.
  const { pool } = app.postgres;
  await pool.query('CREATE TABLE IF NOT EXISTS notes (id bigserial PRIMARY KEY, text text NOT NULL)');
  // Always bind user values, for example:
  // await pool.query('INSERT INTO notes(text) VALUES ($1)', [validatedText]);
}
```

Use `pool.connect()` with BEGIN/COMMIT/ROLLBACK for multi-statement transactions,
and release clients in `finally`. The pool has at most four connections. The app
role owns this website’s database and cannot create roles/databases or become a
superuser. PostgreSQL listens only on container loopback and a private Unix socket.
Managed credentials arrive as RUNALIO_POSTGRES_DATABASE, RUNALIO_POSTGRES_USER and
RUNALIO_POSTGRES_PASSWORD; they are never returned to the portal or stored in source.

## Backups and limits

Database files live in `server/data/postgres/cluster`. Do not modify them directly
or create tablespaces outside that directory. Daily production backups and
pre-publication backups include a PostgreSQL base backup with required WAL when
running, or a complete stopped-cluster copy. Candidate migrations use a disposable
copy; failed publication keeps the previous runtime. Account migrations preserve
the cluster. Restoring backups remains an operator procedure, with 30-day local
retention; off-server replication is separate work.

PostgreSQL shares the account’s CPU, RAM and 5 GB production filesystem allowance.
It does not provision RDS, expose public port 5432 or increase the monthly package.
This module is for modest local-business workloads; it does not add high availability,
automatic PostgreSQL major upgrades or a browser SQL console.
