Decision 001 Application and data design

Status: Accepted for initial implementation. Recorded on 4 October 2026.

This decision turns the initial product design into an implementation design. It preserves the agreed scope and supplies defaults for the remaining technical and workflow choices. Accepted design does not mean implemented behavior; requirements track verified implementation progress separately. External integration compatibility still requires the release checks below.

Application architecture🔗

Use a modular monolith with a Rust service and a Solid 2.0 browser interface built with Vite. Axum handles HTTP, SQLx handles PostgreSQL access, and Utoipa generates OpenAPI 3.1 from the registered routes and Rust DTOs. The API is the shared, language-independent interface for the browser and future mobile/desktop clients. Generate frontend request/response types from that contract rather than sharing server implementation types. Domain commands and future workers remain in the Rust codebase; external format adapters stay behind explicit module boundaries. Axum, SQLx, and Utoipa provide the selected service tools.

This amends the initial TypeScript/Fastify and React selection at the product owner's request. A stable API boundary permits multiple frontends to evolve separately; Rust provides explicit ownership and a compiled service while retaining the small self-hosted deployment. Solid 2.0 is currently a release candidate; the initial implementation pins 2.0.0-rc.13 and its compatible compiler/runtime packages. Prerelease upgrades require the same component, production-build, and browser checks. The frontend remains TypeScript where it helps consume generated API types.

Publish the schema at /api/openapi.json, commit its generated export and browser types, and verify drift in CI. Version incompatible contracts under a new API prefix. Session cookies and canonical-origin mutation protection are the current authentication protocol; native token authentication and cross-origin browser hosting are future extensions, not prerequisites for generating native clients.

PostgreSQL is authoritative for identities, permissions, song revisions, service plans, synchronization state, jobs, and history. Use relational columns and foreign keys for identity and relationships, and schema-versioned JSONB for immutable lyric and layout documents and typed service payloads. Use checked SQL migrations; there is no separate search cluster or message broker initially.

Store binary assets through a common storage interface: a durable filesystem volume for the default self-hosted installation and object storage for managed hosting. Use immutable objects, church-scoped keys, SHA-256 digests, and database metadata. Publish a usable asset record only after its bytes are durable and verified; abandoned uploads remain temporary cleanup candidates. A replacement creates a new asset; it never overwrites a referenced object. Preview, archive, and export jobs use the same storage interface.

Modules are accounts, churches, collection, services, rendering, requests, imports, synchronization, and operations. Domain modules expose commands and queries; adapters translate OpenLyrics, ChurchTools, and FreeShow into those commands. External JSON never becomes the internal data model.

The worker uses a PostgreSQL job table with leases, retries, and idempotency keys. Each mutation commits its audit event and outbox message in the same transaction. A worker publishes outbox events and performs slow work after commit. Long-running jobs do not hold database locks. This avoids losing a synchronization or notification job between a database write and process failure.

Data model🔗

Every church-owned record carries church_id. Foreign keys include church scope so a service cannot reference another church's song or file. Application authorization applies on every command and query, reinforced by PostgreSQL row policies under an application role that cannot bypass them. Public reads use a dedicated projection of allowed fields, not unrestricted song records. PostgreSQL documents row security policies; these enforcement rules are our design choices.

Use UUID identifiers generated at creation. Mutable records have an integer version; timestamps are for display and audit, not conflict precedence. Record actor, origin, and operation ID for each change. Dates use UTC instants plus the church's IANA time zone, defaulting to Europe/Zurich during onboarding and editable by an administrator.

This is a logical schema, not a migration script. Split immutable documents into child tables only where constraints or queries require it. Validate document schemas and referenced IDs before moving a head pointer. Preserve historical references when archiving songs; omit archived songs from current searches and the public catalogue. Do not cascade-delete anything referenced by a snapshot.

Languages and arrangements🔗

A lyric version represents one language and a distinct lyrical edition, such as German original or German simplified. It is not a translation column embedded in an arrangement. An arrangement can be used with several lyric versions and contains its own musical assets. Two unrelated compositions with the same title remain different songs.

Section and line identifiers survive ordinary text edits. A split or merge creates new IDs with predecessor information. A bilingual profile stores alignment units pairing one or more lines from each selected version. This permits translations with unequal line counts. Slide breaks occur between alignment units; they cannot split a pair. Missing alignment or references after an update require review, rather than guessing from line numbers.

For example, a service item for a song can pin German lyric revision 7, English lyric revision 3, arrangement revision 2, and profile revision 5; choose key D; and sequence verse 1, chorus, verse 2, chorus. Its IDs refer to the primary language's sections, and the profile maps them to the second language. Repeating a chorus repeats a sequence occurrence, not its stored lyrics.

Revisions and collaboration🔗

The editing boundaries are a song's metadata, an individual lyric version, an arrangement, a presentation profile, a service header, an item, and the service order. A successful save appends a revision and advances that boundary's head atomically. Different service items do not share a write version.

Commands that update or delete existing boundaries under /api/v1/churches/{churchId} require If-Match with the relevant version. A stale version returns HTTP 412 with the latest permitted record. The editor keeps its local draft and can compare it with the current record, reload, or reapply its changes against the new version. Reapplication is a fresh version-checked write. There is no blind overwrite button. Create, reorder, and export commands accept idempotency keys to make retries safe.

Item insertion/deletion changes membership and order atomically; editing an existing item changes only its body. Reordering checks the order version and validates that it contains each active item once. Song reference changes validate all pinned revision relationships. A write acquires a short service guard lock to check readiness; it does not hold a lock while the user edits. Entering Ready for Sunday uses the same guard and expected service content generation, closing the race between validation and a simultaneous save.

Use server-sent events for change notifications, with an ordered church cursor and event IDs for reconnect. Allocate that cursor under a short transactional church guard so cursor order matches committed visibility. Events identify changed boundaries and versions; clients refetch authorized data. Delivery is at least once, clients deduplicate, and old cursors trigger a full refresh. Keep replayable notifications for seven days; durable change history is separate. Display a disconnected state and poll every 15 seconds while event transport is unavailable. No dirty draft is replaced by a notification.

All domain commands retain base-version and operation-ID semantics so a future offline write queue can reuse them. No CRDT or distributed text editing engine is introduced.

Stable services and readiness🔗

Service items pin metadata, lyrics, arrangement, profile revisions, and exact asset IDs. Resolve church style defaults when the item is added, storing the effective configuration. Later church default changes appear as explicit available layout updates. Store overrides against that resolved configuration. This prevents a font or default-style change from altering Sunday slides silently.

Accepting an update shows the affected lyrics, alignment, assets, and layout together; validates references; and clears the item's preparation marker. Preparation is recorded against an item content digest, not a boolean. A technician marking an older digest prepared receives a stale-version response. Restoring an older song revision does not silently roll back service selections.

Service preparation states are Draft, Preparing, Ready, and Archived. Resolve the initial lock setting from the church default at service creation and retain it on that service; a later church-default change affects new services only. A per-service override remains editable before readiness. Changing an enabled lock while Ready requires reopening first. For enabled locking, an Editor or Administrator can reopen Ready to Preparing; writes and synchronization cannot reopen it implicitly. With locking disabled, a material change moves Ready to Preparing automatically. Requests can remain open while Ready, but adding a requested song obeys the lock. Archived services are read-only and must be restored before editing.

Ready requires a consistent order, valid revision references, all presentation items prepared against their current content, completed asset processing, complete bilingual alignment, and passing slide validation. Unaccepted available song updates are warnings, not blockers, because pinned versions are deliberate. Pending ChurchTools changes are also warnings and do not silently alter the snapshot. Synchronization outages do not prevent standalone preparation.

Slide rendering🔗

Use a shared layout engine that produces a target-independent slide document: 1920×1080 coordinates, text runs, positioned boxes, media references, and ordered cues. The initial template is white text on black, with a bundled font chosen and verified for the initial German and English glyph set. Reserve a 5 percent safe margin and a footer area for song attribution or Bible reference/translation. Font licensing and actual bundling are implementation checks.

The preview and export resolve the same layout document. Lyrics paginate at section or alignment-unit boundaries. Bible text initially uses pasted paragraph boundaries with editable breaks; announcements support title, body, and optional image. A secondary language defaults to 80 percent of the primary text size. Adjustable sizes and breaks are stored in profiles or service overrides. Never shrink below a configured minimum to hide overflow; show overflow and require a break or formatting correction. Missing fonts, alignment, or media block preparation.

For ChordPro, derive transposition from the verified source key to the selected sounding key, preserving chord quality and transposing slash-bass roots. Keep capo separate from sounding key and chord shapes; display all three where a capo is present. Unknown source keys, unsupported chord notation, or key changes the parser cannot interpret produce a review error, not guessed output. Preview and download use the same parser and source revision. The reference documents key semantics and capo; parser coverage must be tested during implementation.

Export generation and imports🔗

An export captures an immutable ServiceSnapshot in a short consistent database transaction. Its digest covers order, item revisions, resolved styles, sequences, transposition, assets, and renderer version. The worker renders from this snapshot only. The presentation and musician packs use the same snapshot. Later edits mark an artifact Outdated relative to the live service but retain it for deliberate download.

Artifacts are Queued, Building, Complete, or Failed, with freshness tracked separately. Retrying the same snapshot/target/adapter reuses the job and completed result. Do not expose a partially built file as Complete. A manifest records song revisions, key, sequence, adapter and renderer versions, asset checksums, and warnings. Regeneration is explicit and never changes a file already downloaded or imported into FreeShow.

FreeShow export uses a ZIP-based .project with data.json and media files, following the documented project format. SongCollect translates its slide document into explicit boxes and ordered layout cues in the show format. Assign stable IDs within an artifact, namespace file basenames to avoid collisions, and rewrite references to packaged files. Repeated section occurrences remain repeatable cues. Provide a companion manifest outside FreeShow-owned data. Font availability on the technician's machine must be checked; do not promise font embedding without validating support.

Bible passages and announcements export as generated shows. Sermon PDFs, images, and videos are packaged with the service. Default video input is MP4 with H.264 video and AAC audio where present; inspect media during upload and report incompatible files before readiness. Automatic video transcoding is deferred. Exact FreeShow behavior for PDF pages, looping, and audio playback remains part of the adapter fixture checks.

OpenLP compatibility exports OpenLyrics song content and the selected sequence. Bilingual export flattens aligned text into stacked lines; arbitrary positioning, differing font sizes, and song-specific styles are not guaranteed. Include an explicit compatibility report. OpenLP documents OpenLyrics import; this design does not claim a complete native OpenLP service package.

Imports stage parsed candidates before publication, preserve originals and source identifiers, and distinguish extracted searchable text from reviewed structured lyrics. Suggest duplicates using existing external IDs, file checksums, and title/author similarity, but never automatically merge different works. Each candidate can be imported, linked, or skipped. Retrying an import does not create duplicates. Empty or scrambled PDF extraction allows attaching the PDF and entering lyrics manually; it is not silently accepted for slides. Do not parse unrelated files or fetch arbitrary embedded links during import.

ChurchTools synchronization🔗

Contract and mapping🔗

Each connection records the installation URL, supported API/schema version, encrypted integration token, capability matrix, and policy version. Use a dedicated ChurchTools account with required song and agenda permissions. SongCollect login is independent. The official documentation exposes the installation's OpenAPI definition at /system/runtime/swagger/openapi.json and documents token authentication; these provide the implementation verification path. API documentation and authentication.

The intended projection maps songs to songs, musical arrangements to arrangements, services to event agendas, and service items to agenda entries. ChurchTools describes song arrangements and attachments and agenda song/arrangement selection. This supports the domain mapping; writable REST fields and concurrency guarantees have not been verified for a real installation.

Synchronize a whitelist of verified shared fields: service title/date where supported, item title/notes, song and arrangement reference, and order. Keep SongCollect lyric pins, singing sequence, bilingual alignment, layout settings, readiness, and preparation markers local unless a supported representation is established. Preserve remote-only fields when writing; never replace a whole agenda from a reduced local projection. Unsupported non-song items can map to generic titled entries with plain notes if verified, while their exact typed data and attachments remain local. Report unsupported mappings rather than silently dropping material.

An incoming changed song reference selects the latest locally available revision bundle for that new selection and marks it unprepared. It never refreshes the existing selection's lyrics automatically. Unresolved references create an item needing attention and trigger song import; they block readiness. Remote edits to linked song content are recorded as drift, and the SongCollect-owned fields are republished rather than changing SongCollect lyrics. Do not delete ChurchTools song records automatically; song archiving hides them locally while historical external references remain valid.

Merge algorithm🔗

Maintain an acknowledged baseline B for each mapped field, current local value L, and newly read remote value R. The default winner is ChurchTools. Policy is a church setting with a version; changes trigger a full comparison against existing baselines, not a mass reset.

Presence is a mergeable value represented by tombstones. A remote deletion wins a simultaneous local edit under the initial policy; a remote edit wins a simultaneous local deletion. A deletion against an unchanged peer propagates normally. Do not infer deletion from an incomplete page, permission failure, or timeout. New unlinked entries are assigned independent IDs and linked only after confirmed creation; titles are not identities.

Treat order among previously linked items as one field. For concurrent reorder conflicts, choose the winner's linked-item order. Preserve independent new items from either side, placing them after their nearest surviving predecessor; break equal-anchor ties by winner-side first, then stable ID. Remove deleted IDs and validate uniqueness. Record the final result on both sides. This prevents losing a local insertion merely because ChurchTools reordered other entries.

While a service is locked, continue reading remote changes into pending state, but do not apply or publish plan changes or advance acknowledged baselines for them. Reopening invokes the same deterministic comparison. Items changed by synchronization lose their preparation marker. Song publication continues independently of locked plans.

Scheduling and recovery🔗

Poll linked active services every 60 seconds, with jitter, and reconcile mapped songs every five minutes. Sync now requests an immediate pass and coalesces with an existing run. Serialize each connection's writes under a lease; apply local changes using version checks and reread if a user saved meanwhile. Back off transient failures from 30 seconds to 15 minutes; show authentication/permission failures immediately and pause until corrected. Last sync means a successful verified pass, not an attempted request.

Before remote writes, reread affected fields and recompute the merge; use conditional writes if the verified API supports them. Read back after writes and advance baselines only for confirmed results. If an uncertain create cannot be reconciled by remote ID or verified operation metadata, pause that operation instead of blindly retrying. Without remote conditional writes, a last-moment remote edit can still race a write: document this adapter limitation, retain observed states, and reconcile on the next pass. Do not claim cross-system atomicity.

Accounts and public requests🔗

Use email/password accounts with verified email, expiring password-reset tokens, and server-managed sessions in secure HttpOnly cookies. Passwords use an established Argon2id implementation with parameters calibrated on the deployment; resets revoke existing sessions. Membership is by administrator invitation; tokens are hashed, single-use, and expire after seven days. An existing user accepts into their account; a new user verifies the invited address. Default invited role is Viewer. A one-time installation bootstrap creates the first administrator; hosted onboarding uses the same command. Self-hosting supports SMTP, plus administrator-generated invitation links and local administrator password recovery when mail is unavailable. Administrator-issued onboarding links can establish the invited identity where SMTP verification is unavailable; the administrator owns that verification. Do not let self-registration join arbitrary churches or remove the last administrator.

Authorize the entire private API, including live events and files, by church and role. Store integration credentials only on the server, encrypt them with a deployment secret, and keep them out of exports and logs. Apply CSRF protection to cookie-authenticated mutations. Public song/PDF access uses an explicit public projection; upload previews do not make service assets public. Public links are convenience URLs, not secrecy boundaries.

Multiple services may accept requests, but each church has at most one explicit default request service. Opening the first sets the default; a later opening preserves it unless the team changes it. Closing the default clears it and falls back to the general pool. No automatic switch based on date or Ready state. Service request URLs preselect their service while open.

Requests allow an optional display name of up to 80 characters and a message of up to 500 characters, without attachments. Status is New, Added, or Dismissed. Adding creates a normal version-checked service item and links it to the request. If the destination closed before submission, show that change and offer the general pool rather than redirect silently. Validate public song IDs against the church catalogue and rate-limit public submissions. Avoid exposing the request list or requester details publicly.

Search and offline storage🔗

Build search documents from current song metadata, each lyric version, theme tags, book/number pairs, and extracted PDF text. Metadata and reviewed lyrics are authoritative; extracted text is labeled as such in matches. Normalize case and accents while retaining original display strings. Prioritize exact book/number and title matches, then title prefixes, themes, and lyric matches; rank typo matches last. PostgreSQL full-text search and trigram matching supply the selected server indexes. Keep a versioned normalization/ranking specification for matching browser behavior.

Set the initial acceptance target at 1,000 songs per church with 25 active sessions on a self-hosted reference machine of 2 CPU cores and 4 GiB RAM. Under a documented representative dataset and 10 searches/second, warm search API responses shall have p95 at most 300 ms; local offline search p95 shall be at most 150 ms on the recorded reference desktop. Measure query dispatch to response/results, excluding the 150 ms typing debounce and external network latency. Saved changes should reach connected observers within two seconds p95 under that load. These are targets to test, not measured results.

Use a service worker for the application shell, IndexedDB for structured records and search data, and Cache Storage for downloaded assets. Data is partitioned by deployment, account, church, and public/private audience. Populate a staging generation, validate it, and switch the active pointer atomically only when complete. Versioned manifests and deletion tombstones support incremental refresh; an expired cursor requests a full generation. Last-updated time means completed refresh.

Offer explicit Enable offline for each private workspace; download its metadata and lyrics, then selected or all attachments with a size estimate. Show cached, downloading, failed, or unavailable per file. Public users can save the public collection, without private service or account information. Browser storage can be evicted and quota differs across browsers; request persistence where available, detect missing files, and display actual availability. Browser storage quotas explain why downloaded export files remain the dependable offline presentation path.

Provide remove-downloads and clear-workspace controls. Sign-out clears private cached content on that device; next authenticated refresh removes data for revoked memberships. An offline device cannot receive immediate revocation, so offline copies are explicitly local copies, not remotely erasable records. Never cache credentials or authenticated API responses indiscriminately. Disable editing and submission offline; requests require reconnecting and explicit submission.

Deployment and recovery🔗

Ship versioned application/worker container images and a Docker Compose reference deployment with PostgreSQL, durable asset volumes, migrations, and reverse-proxy configuration. Managed hosting uses the same images and migrations with managed PostgreSQL, object storage, and platform secrets. A local self-hosted installation needs no managed account or hosted control plane. HTTPS is required for deployed browser offline support; SMTP supports the account workflow.

Separate liveness from readiness: readiness checks schema compatibility and required storage access; a worker heartbeat and failed-job counters report import/export/sync health. Log operation IDs, church IDs, outcome, and duration, excluding lyrics, tokens, request messages, and passwords. Version migrations explicitly, take a backup before upgrades, and stop incompatible instances rather than silently running against a newer schema.

Backup is a portable bundle containing a consistent PostgreSQL dump, all referenced immutable objects, checksum inventory, schema/application versions, and separately encrypted required secrets. Encrypt the bundle using an operator-held recovery key stored separately from live deployment secrets; verify that the recovery key can recover integration encryption material. Include all object references present in the dump; suspend physical garbage collection while capturing the inventory and copying assets. PostgreSQL describes consistent SQL dumps; coordinating assets and secrets is an application responsibility.

Schedule nightly backups with 30-day retention for both deployment modes, to storage separate from the live volumes. Target RPO is 24 hours and RTO is four hours for the reference workload; operators must measure restoration. Managed operators own the schedule and restore drills; self-hosted administrators configure the destination and see backup age/failure. Keep historical revision assets; delete only unreferenced temporary/failed artifacts after a seven-day grace period. Per-church data export is distinct from a full deployment backup.

Restore into an isolated installation, verify the schema, asset checksums, memberships, song search, pinned service snapshots, and an export, then reopen access. Pause all integrations and rotate sessions after restoration so an old sync baseline cannot overwrite newer ChurchTools data. Resume each connection only after a fresh comparison. Verify a full restore before first release and quarterly thereafter.

Consequences and release checks🔗

The design keeps deployment small and gives concurrent work, revision pins, and synchronization explicit transactional boundaries. It adds storage for immutable history and rendering inputs, and requires careful adapter testing. Offline editing can later reuse commands and versions, but will still need queue reconciliation and a conflict interface; this decision does not make that feature free.

No test ChurchTools installation or real church credentials are available in this repository. The application foundation now covers revisioned song metadata; the integration and service workflows remain planned. These are implementation validation gates, not missing product decisions. Update the adapter capability matrix and pin the supported external versions when the tests are performed. Additional library choices can be made within this architecture without reopening settled product scope.