PostgreSQL system schema
Trestle maps one logical 13-version schema to reviewed PostgreSQL DDL while preserving the external HTTP contract.
Type mapping
| Logical value | SQLite | PostgreSQL |
|---|---|---|
| Stable IDs and timestamps | TEXT | TEXT |
| Hashes and ciphertext | BLOB | BYTEA |
| Boolean metadata | Checked integer | BOOLEAN |
| Event/audit sequence | Integer autoincrement | BIGSERIAL |
| File sizes | Integer | BIGINT |
Migration ownership
A PostgreSQL advisory lock serializes migration startup. Trestle reads the validated _trestle_schema_migrations history, refuses a future version and applies each remaining version in its own transaction. Failed DDL and its migration record roll back together.
On both providers the migration history table is authoritative. SQLite's PRAGMA user_version is only a compatibility mirror: a valid history may restore an absent mirror, but history is never reconstructed from a nonzero marker, and disagreement or damaged/non-contiguous history fails closed.
Readiness
The service does not become ready until the connection succeeds, migrations finish and the database identity marker matches postgres. Connection failures are bounded by the configured whole-second startup timeout, enforced at the driver level for every pool connection.
Supported versions
This page documents the Trestle system schema on PostgreSQL. PG05-PG11 added collection, record, query, access-rule, file, events, jobs, backup/restore and cross-provider migration parity, so dynamic collection tables, typed records, automation and recovery are exercised against PostgreSQL. The supported-version window is PostgreSQL 16, 17 and 18, proven by the CI matrix; the migration history remains authoritative on both providers.