The database

Joripo uses PostgreSQL. Most of what is in it is an index over the storage root, rebuilt from the files when needed; some of it exists nowhere else.

What only the database holds

People and their password hashes, plans and limits, phones and their keys, API tokens, quotas, the reviews of quality findings, field-monitoring set-up, the audit log, and server settings. Back the database up as well as the files: see Backups and restore.

Upgrades

Every start brings the database up to date before anything is served. The work is a list of named steps, each in its own transaction and recorded in the database with when it ran, what it found and which release ran it. A step that runs once (a data migration) never runs again once recorded; the others check first and change nothing when nothing is missing.

A step that fails stops the start: the log says which step failed and why, nothing after it runs, and the next start tries it again. The server never serves a half-upgraded database. Server health › Schema shows the record, and turns amber when this release knows a step that never ran here, or the database holds a step only a newer release knows.

Not yet proven on PostgreSQL. The upgrade steps are written to work on both PostgreSQL and the test database (SQLite) and are verified on the latter. A test of them on PostgreSQL 16 is part of continuous integration but has not yet been seen passing, and their first run on a production PostgreSQL server is still to be watched. Watch Server health › Schema after upgrading.

Rebuilding the index

If the database is lost but the storage root is not, start the engine on an empty database (it creates the tables), then run the index rebuild from the engine's folder. It recreates every organisation and workspace from the files, with the same identifiers, and indexes every submission with its review:

python scripts\rebuild_index_from_tree.py --apply

Add --owner "<organisation folder>=<email>" for each organisation that needs a first owner again; it prints a one-time password. Without --apply it only says what it would do.

People, passwords, plans, phones and keys are not in the files, so they come back only from a database backup; without one, owners are created again as above and phones sign in again.

The engine also runs a lighter re-index at every start, indexing any submission on disk the database does not know, so a submission whose indexing once failed is never left invisible.