Database

TravStats stores everything in PostgreSQL with the PostGIS extension enabled. The bundled stack ships postgis/postgis:15-3.4. You will rarely need to touch the database directly — the admin UI exposes everything that’s safe to change — but it helps to know what’s in there.

What you don’t need to know

• Migrations apply themselves automatically on every container start (prisma migrate deploy runs from the entrypoint). You don’t review them; the count grows with every release, and the Updating page says when a release carries many.
• Prisma generates the client at build time. Bundling, compatibility, type-safety — all handled.
• Backups run on a schedule once enabled (off by default; then weekly at 02:00 UTC unless you pick daily or monthly) and carry the upload directories alongside the dump. The schedule, retention, and optional WebDAV target are set in Admin → Backups — see the Backups & Restore page.

If you only want to use TravStats, stop here. The rest is for the homelab type who likes to understand what’s running on their box.

Why PostgreSQL + PostGIS

• PostgreSQL because it’s the boring choice that runs forever. SQLite is too small once you log a few thousand flights with statistics queries; MySQL’s date handling is more pain than it’s worth.
• PostGIS because TravStats does spatial things — great-circle distance, “all flights within X km of point Y”, country-of-airport joins. Doing those in JavaScript on top of plain Postgres works, but PostGIS is purpose-built for it.

The PostGIS extension is required. If you point TravStats at an external Postgres without PostGIS, the entrypoint will fail migrations. Run once on the target database:

CREATE EXTENSION IF NOT EXISTS postgis;

Then restart the app container.

What lives in the database

The schema has grown well past what a table-by-table list could keep honest — this page used to carry one, and it was wrong within two releases. What follows are the families of models and what each holds. The authoritative list is backend/prisma/schema.prisma on GitHub, where every model is commented, and \dt in psql on your own instance.

Travel domains

• Flights — every flight you log: departure / arrival, times in canonical UTC plus the local wall clock and its semantics, airline, aircraft, seat, class, price with currency and an exchange-rate snapshot, tags, booking reference, companions, notes. Duration is a generated column Postgres owns. Two composite indexes serve the shape the statistics ask for.
• Cruises — the voyage, its ordered stops (a matched port, a sea day, or an unresolved port name kept for later), the ship and cabin, the price in its currency, and any hand-corrected leg geometry.
• Lodging — a house (type, chain, coordinates, address in Latin script, ISO country code, photographs) and its stays (dates that may be a month or a year only, check-in and check-out times, room, board, guests, per-category ratings, price with currency and FX source), loyalty programmes and the chains and houses each covers.
• Places — a place with the identity a later import would mint, its visits (each with photographs and a caption), the lists you build, and the curated checklists you subscribe to. Present whether or not the instance’s beta switch shows the domain.
• Trips — the grouping, its stops, journal entries, photos, bookings (PNR, price, currency), and — behind the beta switch — the route sections with their stops, legs and recorded tracks.

Reference catalogues

Airports (seeded from OurAirports, duplicate markers stripped, 34 Antarctic fields included), airlines and aircraft types, ships and ports (both extendable; user-added rows survive a re-seed), the achievement catalogue (270+ entries, ensured at boot), the curated place checklists, currencies and exchange-rate snapshots.

Accounts and security

Users (bcrypt password hash, isAdmin, first and last name, must-change marker, an encrypted TOTP secret and its enablement date), single-use two-factor recovery codes (hashed), registered passkeys (one row per credential, each bound to one rpId), personal access tokens (bcrypt + lookup hash, scope; device tokens carry a device id and platform), pairing claim codes (SHA-256 only, ten-minute expiry, single-use), invitations, per-user settings (language, units, currency, module toggles, country strictness override, home airports, Immich and Dawarich connections).

Evidence and quality

The country-evidence tables behind the passport — country-days reduced nightly from location history, holding a day, a country and a fix count, never a coordinate — and the data-quality flags the inbox shows, each naming both conflicting values and neither as correct. Pending flight updates from the enrichment providers sit beside them.

Parsing and imports

User-recorded parser templates, training uploads, the parse log the parser statistics read, and the import batches: every import run as one row that its created rows point back to, which is what makes reverting a run whole possible.

Operations

Instance settings (name, public URL, user cap, registration mode, the beta switch, the country strictness default, the FX fallback switch, geocoder URLs, encrypted API keys, Ollama URL and models, log level), SMTP configuration, backup metadata, seeding status, local analytics events, and the audit log.

Migrations

Every code change that touches the schema ships an additional migration in backend/prisma/migrations/. On container start, the entrypoint runs:

npx prisma migrate deploy

which applies any migrations that haven’t run yet, in order, and records them in the _prisma_migrations table. Idempotent — the second restart is a no-op.

Forward-only by design. Prisma migrations don’t auto-revert on a downgrade. If you roll the image back, the schema stays where the newer version put it. Older code tolerates that for additive migrations (new columns) but not for destructive ones (a generated column, a renamed value). The Updating page covers the safe rollback dance.

Failed migrations

If a migration fails mid-apply (rare — usually disk-full or a manual schema edit that conflicts), the entrypoint detects it on the next boot:

[entrypoint] ⚠️  Found failed migrations in status output, attempting to resolve automatically...
[entrypoint] Resolving failed migration: 20260301120000_add_…

It calls prisma migrate resolve --rolled-back to clear the marker, then re-runs migrate deploy. Most cases self-heal in one restart cycle.

If something stays stuck, the Troubleshooting page covers the manual workflow.

Connecting to the database

You’ll do this for ad-hoc queries, debugging, or one-off bulk edits.

docker exec -it travstats-db psql -U flights -d flights

You’re now in psql against the live database. \dt lists tables, \d flights describes the columns. Table names are snake_case; a few older ones are quoted and case-sensitive, and \dt shows which.

A few queries that come up often:

-- How big is each table?
SELECT relname AS table, pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

-- Which migrations have applied, newest first
SELECT migration_name, finished_at FROM _prisma_migrations
ORDER BY finished_at DESC LIMIT 10;

For a lost admin password, use Admin → Users → Force password change from another admin, or the recovery steps on the first-run page. A user who lost their second factor is reset by an admin from the user table; nothing needs SQL.

Caution

Direct UPDATE against the database bypasses the audit log, backup integrity check, and any business-logic validation TravStats runs on writes through the API. Several values are derived on write (a flight’s duration, a stay’s overall rating, a place’s status) and a hand-written row can leave them stale. Use SQL for emergencies and one-off admin chores; for everything else, the API or admin UI is safer.

Where the data physically lives

In the bundled compose, the database files live in the named volume travstats-db-data:

docker volume inspect travstats-db-data

Default mountpoint is somewhere like /var/lib/docker/volumes/travstats-db-data/_data/ on the host — the actual pg_data directory.

You don’t need to back this up directly. The in-app backup scheduler produces pg_dump files plus the upload directories in /app/data/backups/ that are easier to restore from. See the Backups & Restore page.

Database sizing in practice

For reference, a few rough numbers:

Volume	Size on disk
Empty (just-installed)	~50 MB (mostly the seeded catalogues and indices on empty tables)
Single user, 200 flights	~80 MB
Single user, 2000 flights and a few dozen stays	~150 MB
Family instance (5 users, 5000 flights total)	~250 MB
Power user, 10000 flights with full historical enrichment	~600 MB

The seeded catalogues dominate the empty install. Everything else grows linearly with your travel. Photographs are not in the database — they are files under /app/data/uploads and are what makes a backup large.

When to use an external Postgres

If you already run Postgres in your homelab and would rather TravStats share that instance:

1. Confirm PostGIS is available (SELECT postgis_full_version();).
2. Create a database and a role with CREATEROLE and CREATE on its own schema.
3. Drop the bundled db service from the compose file.
4. Set DATABASE_URL in .env:

   DATABASE_URL=postgresql://travstats:<password>@postgres.lan:5432/travstats

5. Start the app. Migrations apply against the external database on first boot.

The bundled db service is fine for any home-scale install. The main reason to externalise is if you want one Postgres + one backup strategy across all your homelab apps.