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). 52 migrations have applied at the time of writing; you don’t review them.
• Prisma generates the client at build time. Bundling, compatibility, type-safety — all handled.
• Backups run on a schedule (default daily at 02:00 UTC). The schedule, retention, and optional WebDAV target are set in Admin → Settings → 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

Twenty tables (Prisma models), grouped by purpose:

Core travel data

Table	What it holds
Flight	Every flight you log — departure / arrival, times in canonical UTC, airline, aircraft, seat, class, distance, tags, booking reference, companions, optional notes
Airport	The seeded ~9000-airport reference table from OpenFlights + supplementary sources. IATA, ICAO, name, country, coordinates, timezone
Trip	Higher-level grouping (“Honeymoon 2024”). Flights and bookings reference a trip
Booking	Source records for flights imported via parsers — the original email blob, parser used, raw extracted fields. Useful when you re-parse an old import

User-side state

Table	What it holds
User	Account: username, bcrypt-hashed password, isAdmin flag, language preference, units, theme, must-change-password marker
UserSettings	Personal preferences not on the User row (default tags, dashboard widget order, custom map tile-server URL)
ApiToken	Personal Access Tokens. Plaintext shown once, stored as bcrypt + lookup hash, scoped (read / write / admin), per-token rate-limit bucket
UserAchievement	The “you got this one” rows — links a user to an Achievement with the unlock timestamp and the flight that triggered it
Invitation	Pending invites from Admin → Users → Invite — single-use tokens, accept/expire deadlines

Achievements

Table	What it holds
Achievement	The catalogue itself — 58 awards with name, description, category, tier, criteria. Code-defined, ensured at boot via ensureAchievements(); you don’t insert these by hand

Parser pipeline

Table	What it holds
ParserTemplate	User-recorded templates for airlines without a built-in. Sender domain, subject pattern, field-extraction rules
TrainingData	Examples used to refine the parser — anonymised email bodies and what the parser should have extracted. Powers the prompt-engineering feedback loop
ParseTrainingLog	Audit trail of every parse decision: which template matched, which fallback fired, how confident
PendingFlightUpdate	Async parser results awaiting user review. Created by background email-fetch jobs, drained by the user clicking Save all
PendingUpdateStatistics	Aggregate counters for the pending-updates dashboard

Operations

Table	What it holds
AdminSettings	Instance-level configuration: name, public URL, max users, registration mode, encrypted API keys (AirLabs, Aviationstack, OpenSky, OpenAI, Anthropic), Ollama URL/model, log level
SmtpConfig	SMTP host, port, TLS flag, username, encrypted password, from-address
Backup	Metadata for every pg_dump the scheduler has produced — filename, size, timestamp, checksum, optional WebDAV-sync status
AirportSeedingStatus	One-row table tracking whether the OpenFlights CSV import ran successfully
AnalyticsEvent	Internal usage events (parser hit-rates, login frequency). Local only; never leaves your instance

The full schema lives in backend/prisma/schema.prisma on GitHub — 613 lines, every field commented.

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 (column type changes). 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 "Flight" describes the columns. The casing matters — Prisma quotes table names so they’re case-sensitive.

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;

-- All flights for a user
SELECT id, "flightNumber", "departureIata", "arrivalIata", "departureTime"
FROM "Flight"
WHERE "userId" = '<user-uuid>'
ORDER BY "departureTime" DESC;

-- Reset an admin password to a known bcrypt hash
UPDATE "User"
SET "passwordHash" = '$2b$10$WdN5oW3bI3p5LKDGo6gBb.MR0J2I3Sw6NkN4QrJj0Dp/2nXFqtbQO',
    "mustChangePassword" = true
WHERE "isAdmin" = true;

That last one is the bcrypt of tempchangeme at cost 10 — sign in, change immediately, configure SMTP so you don’t need this trick again.

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. Use it 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 volume is bound to container lifecycle (recreated only if you docker volume rm), and the in-app backup scheduler produces clean pg_dump files 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 _prisma_migrations + indices on empty tables)
Single user, 200 flights	~80 MB
Single user, 2000 flights	~150 MB
Family instance (5 users, 5000 flights total)	~250 MB
Power user, 10000 flights with full historical enrichment	~600 MB

The seeded Airport table dominates the empty install (~9000 rows × ~200 bytes each + GIST indices on coordinates). Everything else grows linearly with your travel.

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.