Skip to content

Latest commit

 

History

History
407 lines (330 loc) · 24.1 KB

database.md

File metadata and controls

407 lines (330 loc) · 24.1 KB

PostgreSQL infrastructure

The backend uses SQLAlchemy 2.x asynchronous engines and sessions with psycopg 3. Alembic owns application schema migrations. PostgreSQL 17 is the local reference database.

Local Docker Compose setup

See the environment configuration reference for each .env setting, expected values, secret files and which options are fixed or derived by Compose.

The application services use a read-only image filesystem in both local and production Compose. Persistent course files remain writable under /var/lib/sophikron/documents through the dedicated documents_data volume, including its temporary upload directory. /tmp is a separate 64 MiB memory-backed filesystem for ephemeral work; do not store persistent files there. PostgreSQL retains its writable data volume and standard image initialization behavior. Application containers drop Linux capabilities, prohibit privilege escalation and use an init process. Docker logs rotate at 10 MiB per file with three retained files per container. See the Compose architecture decision.

Install Docker with Linux containers and Compose v2. Copy .env.example to .env at the repository root and set SOPHIKRON_SECRETS_DIRECTORY to an absolute private host directory. No local Python or Node installation is needed to run the application. Leave SOPHIKRON_ORIGIN empty for http://localhost:3000; changing FRONTEND_PORT also changes the default local origin. Explicit origins must match the browser URL exactly, without a trailing slash.

Generate the secrets

Create a directory outside the repository, restrict access to your user, then create these six files:

File Contents
db_admin_password Independent random PostgreSQL administrator password
db_app_password Independent random application-role password
db_migration_password Independent random migration-role password
bootstrap_password Random installation password, used with AUTH_BOOTSTRAP_USERNAME
smtp_password SMTP password, or an empty file when mail is disabled
identity_keys.json Encryption and lookup keys generated by the application CLI

Keep all secrets between restarts. Losing the identity keyring makes encrypted identities and recoverable calendar tokens unreadable. Password files must be UTF-8 single-line values. Use the following platform-specific steps for a new installation. They refuse to overwrite existing password files. Set the same absolute directory in .env.

Windows PowerShell

Choose a new directory outside the repository; replace the example user path. Protect it with NTFS ACLs and allow Docker Desktop to read it. The commands create four independent passwords and the empty SMTP password file without printing their values:

$secretsDirectory = 'C:/Users/yourname/AppData/Local/Sophikron'
New-Item -ItemType Directory -Path $secretsDirectory -ErrorAction Stop
icacls $secretsDirectory /inheritance:r /grant:r "${env:USERNAME}:(OI)(CI)F"
$random = [System.Security.Cryptography.RandomNumberGenerator]::Create()
try {
    foreach ($name in 'db_admin_password','db_app_password','db_migration_password','bootstrap_password') {
        $bytes = New-Object byte[] 32
        $random.GetBytes($bytes)
        $value = [BitConverter]::ToString($bytes).Replace('-', '').ToLowerInvariant()
        $file = [System.IO.File]::Open((Join-Path $secretsDirectory $name), 'CreateNew', 'Write')
        try {
            $encoded = [Text.Encoding]::UTF8.GetBytes($value)
            $file.Write($encoded, 0, $encoded.Length)
        } finally { $file.Dispose() }
    }
    $file = [System.IO.File]::Open((Join-Path $secretsDirectory 'smtp_password'), 'CreateNew', 'Write')
    $file.Dispose()
} finally { $random.Dispose() }

Continue with identity keyring generation.

Linux shell

These commands require OpenSSL and a new directory. The parent is private; individual secret files must be readable by container UID 10001. Do not rerun them against an existing installation.

secrets_dir="$HOME/.local/share/sophikron/secrets"
mkdir -p "$HOME/.local/share/sophikron"
mkdir -m 700 "$secrets_dir" &&
( set -e; set -C; umask 077
  for name in db_admin_password db_app_password db_migration_password bootstrap_password; do
    openssl rand -hex 32 > "$secrets_dir/$name"
  done
  : > "$secrets_dir/smtp_password"
  chmod 0644 "$secrets_dir"/*
)

If directory creation fails because it already exists, stop and inspect that installation; preserve its secrets. Use an absolute path in .env (Compose does not expand ~ into a home directory).

Generate the identity keyring

From the repository root, build the existing backend image; no host Python installation is needed:

docker build -f backend/Dockerfile -t sophikron-api-local .

Windows PowerShell, using the directory created above:

docker run --rm --user 0 --mount "type=bind,source=$secretsDirectory,target=/output" sophikron-api-local python -m app.cli generate-keys --output /output/identity_keys.json

Linux shell, using the directory created above:

docker run --rm --user 0 --mount "type=bind,source=$secrets_dir,target=/output" sophikron-api-local sh -c 'python -m app.cli generate-keys --output /output/identity_keys.json && chmod 0644 /output/identity_keys.json'

The CLI refuses an existing keyring. This one-off container runs as root to create the mounted file; application containers remain non-root. Compose secret uid/mode options do not portably change source-file permissions. Read the bootstrap password privately in its file for the initial login; do not paste it into logs or support reports.

Start and initialize

From the repository root:

docker compose -f compose.yaml -f compose.local.yaml config --quiet
docker compose -f compose.yaml -f compose.local.yaml up -d --build --wait

Alternatively, use ./build.ps1 -Local -Start to embed the Git revision automatically. See build options.

db initializes PostgreSQL and restricted roles on the first empty volume. migrate runs Alembic with its dedicated role, then backend starts, then frontend. The API has no migration credentials. Open http://localhost:3000/login, use setup_operator (or your configured username) and the bootstrap password, then complete /setup to create the permanent administrator. Log in again with the generated permanent username. SMTP is optional: invitations can be delivered manually. API docs are at http://localhost:8000/docs; PostgreSQL listens on 127.0.0.1:5432. All published ports are loopback-only. Use synthetic student data for local trials.

Stop without deleting data:

docker compose -f compose.yaml -f compose.local.yaml down

The postgres_data and documents_data volumes survive. Keep the same Compose project name (default: repository directory name), .env and secrets to reopen the same installation. Changing files does not change passwords already provisioned in PostgreSQL. Initialization scripts do not reprovision existing volumes. Never delete volumes to fix an authentication problem.

Production

Use only compose.yaml, set SOPHIKRON_ORIGIN=https://your-domain before building, and configure HTTPS on the host reverse proxy using the BFF topology. API/database remain unpublished; the frontend is loopback-only and trusts the host proxy's replaced client-address header. Do not expose this listener directly to untrusted clients. Production also needs protected secret storage, database/document/key backups and a tested restore procedure. The two Compose files and secret directory suffice for a local trial, not for all production operations. The repository directory name is arbitrary; keep its project identity stable.

Roles and runtime secrets

The feature revisions also own reversible account archival: authentication defines users.manually_archived; promotions defines promotion_account_blocks, a separate association with composite (user_id, promotion_id) primary key and an index on promotion ID. Causes survive membership changes until the responsible promotion is reactivated. The additive 0006_promotion_groups revision introduces independent half, third and khôlle partitions; existing memberships are preserved.

Role Purpose Secret available to
postgres Initial provisioning and cluster administration PostgreSQL container
sophikron_migrator Owns the sophikron schema and creates/updates its objects PostgreSQL initialization and explicit migration service
sophikron_app Application data access, without schema or role administration PostgreSQL initialization and backend

The runtime and migration roles are neither superusers nor database/role creators. The application cannot assume the migration role. Public database/schema privileges are revoked. Default privileges grant application DML on future migration-owned tables and sequence usage; public execution of future migration-owned functions is revoked. Alembic explicitly revokes application access to its version table in the migration transaction. Restrict grants further when future business models require it. Runtime DELETE on promotions and UPDATE/DELETE on activity events are explicitly revoked, as are writes to role/permission catalogues. Database roles do not replace application authentication or capability checks between users.

The database image extends the official postgres:17 image with reviewed initialization SQL. Authentication over the Docker network uses SCRAM-SHA-256. Compose secrets are mounted files under /run/secrets; local Compose does not encrypt their source files. The backend only loads its own password, once at startup. There is no password or complete connection URL in the Compose environment, image or source configuration. An administrator controlling Docker or the host can access mounted secrets. Restrict Docker access and protect the host.

Configuration

DatabaseSettings reads environment variables only; it does not automatically load .env. These settings apply to processes inside containers. SOPHIKRON_SECRETS_DIRECTORY is a host directory used by Compose, not an application setting. Individual host secret-path overrides are not used.

Variable Default / requirement
DB_HOST Required; db in Compose; libpq multi-host lists can be supplied here
DB_PORT 5432; shared port for hosts in the list
DB_NAME Required; sophikron in Compose
DB_USER Required; application or migration role
DB_PASSWORD_FILE Required; readable runtime secret file
DB_POOL_SIZE 5, at least 1
DB_MAX_OVERFLOW 5, bounded and never unlimited
DB_POOL_TIMEOUT 5 seconds waiting for a pooled connection
DB_POOL_RECYCLE 1800 seconds; checked when acquiring connections
DB_CONNECT_TIMEOUT 5 seconds per host connection attempt; minimum 2
DB_STATEMENT_TIMEOUT_MS 10000; migration service uses 60000
DB_LOCK_TIMEOUT_MS 2000
DB_IDLE_TRANSACTION_TIMEOUT_MS 30000
DB_HEALTH_TIMEOUT 2 seconds for the entire readiness operation
DB_SSLMODE disable for the local Docker network
DB_SSLROOTCERT Optional CA certificate file path
DB_TARGET_SESSION_ATTRS read-write; also accepts primary or any

Size the connection budget for all processes and replicas: each application process can use up to DB_POOL_SIZE + DB_MAX_OVERFLOW connections, plus migration and administration connections. SQLAlchemy owns the only application pool. pool_pre_ping replaces stale connections on checkout; it cannot save an interrupted transaction. Recycling does not interrupt active transactions. Server-side statement/lock/idle timeouts and pool/connection timeouts address different waits. Other endpoints may need their own overall request deadlines as they are introduced.

URL.create handles password characters without manual URL interpolation. SQL values must be bound parameters; never build SQL from user input with string concatenation or f-strings. Dynamic identifiers require trusted SQLAlchemy objects or explicit allowlists. SQLAlchemy hides bound parameters in its errors, but callers must still avoid logging raw exceptions, complete URLs, secrets or personal data. PostgreSQL server logs also require their own privacy review.

Sessions and transactions

The application lifespan owns one Database resource per process and disposes it at shutdown. Each get_db dependency yields a fresh AsyncSession, closes it on completion/error/cancellation and never commits implicitly. Use a separate session per concurrent task. Services define the transaction before performing database operations:

async with session.begin():
    await session.execute(statement, parameters)

The context commits on success and rolls back on exceptions. If a read has already started a transaction through SQLAlchemy's autobegin, finish it before opening a new explicit transaction. Data-access helpers should not independently commit parts of a larger business operation. Do not hold transactions open during unrelated network calls. expire_on_commit=False avoids implicit reload after a commit; load relationships explicitly in asynchronous code.

No automatic write retry is performed. Following a disconnect during commit, its result may be unknown. Future retry policies must consider idempotency and retry the complete transaction only where its business semantics allow it.

Migrations

Add future model imports in app/models/__init__.py so Alembic sees them through Base.metadata. Metadata uses the explicit sophikron schema and stable constraint names. Name check constraints. Connections use search_path=pg_catalog; qualify application tables explicitly in handwritten SQL. Alembic inspects only the application schema and uses a dedicated connection with NullPool. Most autonomous IDs are bigint identities; singleton and token-keyed tables have dedicated keys; associations use composite keys.

The release baseline contains nine feature migrations:

Revision Schema responsibility
0001_authentication Accounts, identities, roles, sessions, rate budgets and activity journal
0002_configuration Public settings, minute/category API monitoring and singleton release-check cache
0003_promotions Promotions, memberships and account blocks
0004_subjects Subjects and teacher assignments
0005_lessons Course content, documents, permissions and statistics
0006_promotion_groups Standard promotion partitions and pupil group assignments
0007_timetable Annual calendars, rooms, availability, compact series, custom audiences, archive policy, encrypted revocable ICS links and lifetime ICS usage totals
0008_kholloscope Kholle requirements, academic label creation order, subject commitments, preparation workflow, room capacity, durable generation/revision jobs and explicit read/write capabilities
0009_curricula Current multi-subject curricula, subject-scoped competency DAGs, optional promotion association, bounded revision-protected undo and staff read/edit/manage grants

The release-check cache belongs to 0002_configuration. The kholloscope revision 0008_kholloscope follows 0007_timetable and adds preparation and durable generation jobs. The final head is 0009_curricula. It creates curricula, curriculum_subjects, competencies, competency_links and curriculum_undo, plus nullable promotions.curriculum_id. curricula.version is the optimistic concurrency counter checked by metadata edits, competency mutations, imports and undo; after_version protects inverse operations. This is separate from the JSON file format version. Both endpoints of competency links use composite foreign keys; cycle prevention and current subject rights are enforced transactionally by services.

Fresh databases and an exact supported 0008 baseline can apply the new head. Databases stamped with incompatible development schemas need an inspected transition; a reused revision ID does not prove schema equivalence. An empty feature catalogue does not make those schemas equivalent. Do not apply a blind stamp or destructive downgrade to resolve the mismatch. A downgrade of 0009_curricula removes all curriculum data/undo and promotion associations while preserving older promotions, subjects and calendars. Tests round-trip this on a disposable database. See curriculum contracts and migration compatibility and ADR 0011.

The kholloscope schema uses the following integrity rules:

  • kholle_wishes: one row per academic year, staff account and subject; sessions between 0 and 1000 in the subject's recorded reference cycle. Account/year deletion cascades, while a referenced subject cannot be deleted. The primary key also supports annual roster reads.
  • kholloscope_workflows: one JSONB preparation document and concurrency revision per academic year.
  • kholloscope_runs: one durable job per ID, a checked status catalogue, a partial unique index allowing only one queued/running job per year, and a composite (school_year, created_at) index for latest-generation and revision-history queries. Terminal history remains available.
  • Room capacity is bounded to 0–100, with a default of one. Zero forbids new kholle placements.
  • Requirements, workflow details and solver payloads remain validated JSONB documents. Real teacher/subject/room references in published events use existing timetable foreign keys.

Retained job snapshots and rollback metadata support stale-source detection, dated revisions and publication rollback. Published events remain in the timetable tables. See ADR 0010.

API monitoring has no foreign keys to pedagogical tables, so it can be created in configuration. ICS usage belongs to the timetable feature. The timetable migration creates the final schema directly, including nullable availability ranges and encrypted token metadata. The cleaned 0007 baseline omits legacy min/max kholle hour bounds; 0008 adds exact subject commitments. Recurrences and exceptions remain compact JSON documents; shared teachers require no separate table.

This baseline is intended for fresh databases (alembic upgrade head), not for upgrading databases that applied the previous history, including databases stamped with the same amended revision IDs. Do not stamp an existing schema to bypass that incompatibility. Published migrations become immutable; subsequent schema changes require new revisions.

Downgrading to 0006_promotion_groups removes timetable data, personal subscriptions, ICS totals and custom audiences while preserving standard groups and API metrics. Downgrading below 0002_configuration also removes API metrics, the release cache and public settings. Reapplying migrations recreates these empty stores.

The migration service defaults to alembic upgrade head. Supply a full command to override it:

docker compose run --rm migrate alembic current
docker compose run --rm migrate alembic check
docker compose run --rm migrate alembic upgrade head

For authoring migration files, run Alembic from backend/ with the migration role's settings and secret file. Container-generated files otherwise disappear when the one-off container is removed. Review generated SQL and never modify an already-applied revision. Run migrations once as a controlled deployment step, not from every API worker. Ensure the secret remains readable even when rendering offline SQL; no URL with credentials is written to alembic.ini.

Verification

For a complete automatic local run, use run_tests in PyCharm or run python run_tests.py from backend/ using the configured interpreter. Docker Desktop must already be running. The launcher creates a unique disposable Compose project, synthetic credentials and dynamically assigned local ports, then runs the whole suite in the local interpreter. Its cleanup removes test volumes after success, failure or normal interruption. It never loads the development .env or its secrets. See PyCharm Run / Debug for the launch configuration.

The launcher uses a unique sophikron-tests-* project, database sophikron_test, generated secrets and random loopback ports. It removes only its disposable resources and never loads the local .env. The integration suite intentionally downgrades/reapplies migrations; never target a valuable database. Tests cover schema/metadata consistency, singleton initialization, SQL grants, transaction rollback, concurrency, authorization, sessions and file access. Unit tests alone skip these PostgreSQL cases. See release verification for commands and BFF/browser options.

Production and future encryption

The base Compose configuration is intended for deployment behind a host HTTPS proxy. For remote PostgreSQL, configure TLS certificate verification (DB_SSLMODE=verify-full, trusted CA and matching hostname) and mount the CA certificate. Multi-host connection selection can support a future primary endpoint, but does not configure replication or failover orchestration. A future PgBouncer deployment needs its own pooling and prepared-statement compatibility review.

Usernames, emails, first names and family names in users are encrypted in the application before storage. Keyed lookup indexes support exact search and uniqueness. Pedagogical resources, integer relationships, roles and security/statistics timestamps remain outside this field-encryption scope. Recoverable calendar subscription tokens reuse the same versioned cipher with a dedicated owner-bound field. See authentication for keys, rotation, restoration, sessions and bootstrap. Keep encryption keys separate from PostgreSQL and its backups. Account passwords use Argon2id hashing, not reversible encryption. Storage encryption and encrypted backups remain deployment responsibilities.

Building images with revision metadata

Run from the repository root using Windows PowerShell 5.1+, or PowerShell 7 (pwsh) on Linux. Git and Docker Compose v2 must be installed. Existing secrets and configuration remain required.

# Inspect metadata without building or starting containers.
./build.ps1 -Local -Preview
# Build local images only (default behavior).
./build.ps1 -Local
# Build and start locally, waiting for service readiness.
./build.ps1 -Local -Start
# Production build uses only compose.yaml.
./build.ps1
# Build and start in production.
./build.ps1 -Start
# Preview both commands without building or starting anything.
./build.ps1 -Local -Start -Preview

On Linux, invoke pwsh -File build.ps1 -Local. The script locates the repository relative to itself, so it can also be called from another directory. It reads HEAD and the working tree, sets SOPHIKRON_BUILD_REVISION only in the build process, and restores the previous environment on success or failure. It never fetches, checks out code or changes Git history. The default builds images only. With -Start, a successful build is followed by docker compose up -d --wait using the same Compose files. Build failure prevents startup; startup failure is reported without automatically removing containers or volumes. -Preview suppresses both operations, including when combined with -Start. Do not store this generated value in .env or .env.example.

Uncommitted tracked changes and non-ignored untracked files add -dirty; the dashboard labels the revision as modified. Ignored files, including secrets, do not trigger this marker. A dirty marker identifies a modified build but does not count changes or uniquely identify their contents. The existing Dockerfiles embed the argument in each image. A direct Compose build without an explicit revision uses unknown. Rebuilding is required to change embedded metadata.

The script does not bypass Windows execution policy. Use an approved PowerShell execution configuration if your machine blocks local scripts. Source archives without Git metadata cannot use automatic revision detection; direct Compose remains available with an unknown revision.