Timetracker uses PostgreSQL as its only database. Development commands can provision a disposable local server; deployed installations connect to an operator-managed server. See Deployment for deployment and backup examples and Configuration for connection settings.
Every connection must use:
- PostgreSQL major version 18;
- UTF8 database encoding;
- PostgreSQL's
builtinlocale provider; - the
C.UTF-8builtin locale.
The application validates this contract when it opens the default connection
and refuses to start against an incompatible database. A libc or ICU database
does not satisfy the contract, even if its displayed locale has a similar name.
This keeps comparisons, ordering, and unique constraints independent of the
database host's operating-system locale.
Development uses make ensure-postgres, normally through make init, to
create an ignored loopback-only cluster under .cache/. The server appends its
own output to .cache/postgres/server.log rather than writing to the terminal,
because the daemon outlives make and would otherwise keep holding the
inherited stdout — under a pipeline (make migrate | tail) that is the write
end of the pipe, so the reader waits for an EOF that never comes and a finished
command looks like it hung. A failed start quotes the log lines that attempt
added. Stop the cluster with make stop-postgres; the command waits for
shutdown and succeeds when the managed server is already stopped or absent. It
only targets the current worktree's managed cluster. Set DATABASE_URL to use
an existing server instead; make stop-postgres never stops that external
server. Deployments should provide the URL through DATABASE_URL__FILE so
credentials need not appear in the environment or the Compose configuration.
The managed cluster needs an unprivileged user, because PostgreSQL refuses to
run as root. Containers and cloud sandboxes that log in as root therefore cannot
host it, and make ensure-postgres says so rather than attempting the download;
point DATABASE_URL at an existing server there.
Fresh databases are built from the migration files in games/migrations/.
The existing initial migration is the permanent baseline. Future schema
changes add normal Django migrations; do not rewrite an applied migration.
Run make makemigrations when changing models and make check-migrations to
verify that model state and migration state agree. Deployment startup applies
pending migrations before starting the application processes.
PostgreSQL stores several values calculated from other columns:
Purchase.price_per_gamedivides the converted price, or the original price when no converted price exists, by the number of linked games. A zero game count producesNULLrather than a division error.Session.duration_calculatedis the elapsed time between the end and start timestamps, or zero for an unfinished session.Session.duration_totaladds the manual duration to the calculated duration.PlayEvent.days_to_finishis the date difference, counts a same-day event as one day, and is zero when the required dates are absent.
These are Django GeneratedField values. Application code must not write them
directly and must refresh an instance from the database when it needs a newly
calculated value immediately after a write.
The model expressions and migration expressions must remain equivalent. The
behavioral generated-column tests and make check-migrations are the normal
regression gates.
Nullable values in user-facing lists sort after non-null values in both ascending and descending order. The shared sorting path also appends the primary key as a stable tiebreaker. Queries outside that path must specify an explicit null-ordering policy and a deterministic tiebreaker where result order is observable.
Tests use PostgreSQL databases created by Django from DATABASE_URL.
Pytest-xdist assigns every worker a distinct bounded database name that also
includes the test-run identity, so workers and concurrent runs cannot share a
test database. Django creates, migrates, and removes these disposable databases.
The Makefile chooses the normal worker count for the host. Set
PYTEST_WORKERS=0 only for CI, focused debugging, or an explicit serial run.
No deployment here runs a connection pooler. Connections go straight to
PostgreSQL 18, and make dev-prod and the container both use a direct
DATABASE_URL. This section says what would have to hold if one were adopted.
A pooler in transaction or statement pooling mode gives consecutive statements different backend connections. Anything a statement leaves behind on its connection is gone by the next one.
Cursors. QuerySet.iterator() declares a server-side cursor and then
FETCHes from it. Under transaction pooling the FETCH arrives on a connection
that never saw the DECLARE. No first-party code calls iterator():
tests/test_iterator_guard.py walks the syntax tree of games/, common/,
timetracker/, contrib/ and scripts/ and fails on a new call. Large reads use
keyset_pages() from common/keyset.py, which runs one ordinary query per page
over an index and holds no connection state.
Django opens cursors of its own that cannot be rewritten:
ModelChoiceIterator (one plain <select> here —
LibraryPreferencesForm.default_device), dumpdata (make dumpgames) and the
serializers it uses, and serialize_db_to_string in the test database.
DISABLE_SERVER_SIDE_CURSORS=true turns those off. It is not free: without a
cursor, psycopg receives every row on execute() and
django/db/models/sql/compiler.py materialises the lot, so the process holds the
whole result. chunk_size then sizes fetchmany() calls over rows that already
arrived and bounds nothing.
Temp tables. games/events/rebuild.py creates a temp table per projection
table, and its phases run in separate transactions on the same session —
_require_shadow_tables() already states that dependence in its error text. A
temp table belongs to a session. Under transaction pooling the rebuild is
broken whatever DISABLE_SERVER_SIDE_CURSORS says and whatever the reads do.
Adopting a pooler starts there, not with the setting.