The database¶

One Postgres per site, two schemas in it, and one command that moves it forward. This page is the model a contributor writes against; Back up and restore is the host admin's side.

derived    rebuildable from corpus commits; `nerthus derive` may drop and rebuild it
authored   the site's own facts; backed up nightly, never dropped by a derive

The bundled server¶

The package carries PostgreSQL 17 from the postgresql-binaries wheel, pinned exactly in pyproject.toml, and runs it; packages/nerthus/db/bundle.py unpacks it into the instance's data directory. The cluster uses PostgreSQL's builtin C.UTF-8 locale provider, so two hosts sort identically without agreeing about system locales. database.bind takes literal addresses and interface names; an interface named there and absent on the host is an error, and nothing is bound that the configuration did not ask for.

nerthus db init          # unpack the bundled server and create this instance's cluster
nerthus db start
nerthus db upgrade       # create the two schemas and run every migration to head
nerthus db current       # the database's revision, and the one the package ships

upgrade is idempotent: a database at head runs no script and exits zero. A rollout runs it once, before the first instance restarts. nerthus db current --check exits non-zero unless the database is at the package's head and prints the command that fixes it; it is the check a health probe and the migrate role use.

database.url points a site at a Postgres it does not run: init, start and stop then refuse and say so, and upgrade, downgrade and current work as they do against the bundled one.

Going back¶

nerthus db downgrade base       # every migrated table gone; the schemas and btree_gist stay
nerthus db downgrade 0001

There is no default target. A contraction is a deliberate act in the next minor version, and a downgrade that guessed one step back is how a table somebody still reads gets dropped.

What every table carries¶

authored rows travel between sites as events, so each carries:

Column	Why
`id`	UUIDv7, minted by the writer; it sorts by the instant it was minted, so a reader pages the stream by that key rather than by a clock it would have to trust
`site_id`	which site owns the fact; never null
`created_at`	the server's instant in UTC, so a host with a skewed clock cannot back-date one

Every authored table is REPLICA IDENTITY FULL and none is unlogged. tests/test_db_ddl.py asserts all of that against a live server, with a deliberately bad table as its positive control.

derived tables mix in ProvenanceMixin and carry corpus, source_path, blob_sha and commit_sha. The blob makes a load idempotent; the commit makes a rebuild verifiable. A derived row that cannot say where it came from cannot be invalidated when its source moves.

Time¶

UTC everywhere. A lore date is the whole of that day as Europe/Warsaw counted it, converted to a half-open UTC range by nerthus.common.time.day_range: half-open, so two consecutive days abut instead of overlapping, and a temporal exclusion constraint refuses a real conflict without refusing an ordinary sequence. The two days a year that are not 24 hours long are handled.

nerthus.db.base.temporal_exclusion("entity_id") is that constraint: one entity may not carry two values of one fact over the same instant. It needs btree_gist, which the first migration installs.

The two locks¶

Exactly one instance of a site may commit to a corpus, and exactly one may load one. Both are Postgres session-level advisory locks rather than a row with a lease: a lease has to be renewed and timed out, and a timeout is a guess about how long a slow load may take, while a session lock is released by the server the moment the connection goes.

# This instance is the site's writer for the body of the block.
async with locks.writer(settings):
    ...

# LockUnavailableError when somebody else holds it.
async with locks.loader(settings, wait=False):
    ...

There is no "is it held" call: any answer stops being true before the caller can act on it.

The canonical dump¶

The same corpus commits and the same Platform version give the same derived, byte for byte. nerthus derive --hash checks that: one deterministic serialisation of schema derived, streamed into one SHA-256.

$ nerthus derive --hash
canonical format 1, schema derived
tables: 0, rows: 0
sha256: 77d2e768f75fd8ce026270af5702a6b87632fa3d346719058fef17c5d8713b53

Every table gets its own line with its row count and hash, so a moved schema hash can be narrowed to a table before anything is diffed, and "nothing was there" never reads like "everything matched".

Every derived table declares the columns that order it; a table that does not is refused rather than dumped:

Table(
    "entity_tags",
    metadata,
    Column("entity_id", Uuid(as_uuid=True), nullable=False),
    ...,
    info=sorted_by("entity_id", "valid"),
)

The key has to identify a row uniquely. Nothing can prove that from a declaration, so two rows that tie stop the dump and name their key. The rest of the dump's guarantees are in the module docstring of nerthus/common/canonical.py: text ordered under COLLATE "C", column order taken from the model, values framed by length and tagged by type, the row count inside the hash, and no clock in the output.