Persistence

Physical collection schema

Every base collection receives a dedicated physical table with generated internal identifiers. The logical field model is provider-neutral; each engine maps it to its own DDL.

Generated columns

_id          TEXT PRIMARY KEY
_version     INTEGER NOT NULL CHECK (_version > 0)
_created_at  TEXT NOT NULL
_updated_at  TEXT NOT NULL
f_ab12...    <provider field type>

Field columns use stable internal IDs rather than user-provided names. Renaming due_date to due_at can therefore preserve the same physical column.

Type mapping

Logical field	SQLite	PostgreSQL
text, email, url, select, relation, datetime	`TEXT`	`TEXT`
number	`REAL` with a numeric `typeof` check	`DOUBLE PRECISION`
boolean	`INTEGER` with `IN (0,1)` check	`BOOLEAN`
json	`TEXT` with `json_valid` check	`TEXT`

SQLite tables are declared STRICT; PostgreSQL uses native types. Both reject the same incompatible schema changes, so existing metadata and rows survive a failed change on either provider.

Constraints and errors

Required and unique metadata becomes physical NOT NULL and UNIQUE constraints. Application validation provides stable field errors first; the database remains the final integrity boundary under races or malformed clients.

Deletion and recovery

Deleting a collection drops its physical table and metadata in one transaction. This is intentionally destructive. Create a tested backup before deleting production data.

Relations

Relation fields store record IDs and can be checked by application logic and access rules. They do not yet declare a target collection or delete/update action, so Trestle does not currently generate a physical foreign key for them.