GRI-1853: Postgres schema, migrations and the db module (signage #9b) #82

Merged
vivy merged 5 commits from feature/GRI-1853-signage-db-schema into dev 2026-11-09 22:52:16 +00:00
Member

What

The database half of signage #9b: Postgres schema, migrations and a server::db module.

  • One embedded sqlx migration, crates/server/migrations/0001_initial_schema.sql, with the MVP tables: users, sessions, devices, ip_rules, media, playlists, playlist_items, device_playlist_assignments.
  • server::db::connect opens a pool from DatabaseConfig and refuses a weak sslmode (disable/allow) unless database.allow_plaintext_connections = true is set — see plan/server/persistence.md, "TLS to the database".
  • server::db::migrate runs every pending migration; safe to call on every start-up.
  • Integration tests in crates/server/tests/db_integration.rs run against a real Postgres via SIGNAGE_TEST_DATABASE_URL, and skip cleanly (reporting ok, not a failure) when that variable is absent — see plan/operations/testing-strategy.md.
  • CI's test job now runs a postgres:16 service and sets SIGNAGE_TEST_DATABASE_URL, so these integration tests actually run there instead of silently skipping.

Not included: wiring the pool into main.rs. GRI-1838 (the axum skeleton, Voidgazer) has not merged yet, and the task names http.rs and the serve loop in main.rs as off-limits until it does. Once it lands, a follow-up merges origin/dev and wires the pool in.

Unit 2 (#10: login, first-user setup, sessions) is a separate PR on top of this one.

Done when

The server starts against an empty database, runs migrations, and the schema matches the MVP tables named in the issue. Checked: cargo test -p server --test db_integration against a disposable local Postgres, migrating an empty database twice (idempotent) and exercising the cascade/CHECK constraints on sessions, playlist items and device assignments.

Verification

  • ./scripts/local-gates.sh --rust-only — every gate passed (fmt, clippy -D warnings, cargo test --workspace --all-targets, cargo test --workspace --doc, cargo deny check bans licenses sources).
  • cargo test -p server --test db_integration against a disposable local postgres:16 database: 5/5 passed (schema has every expected table; migrate is idempotent; a session cascades with its user; a playlist item is refused the schema's own CHECK when its kind-specific column is missing; a device cannot hold two assignment rows).
  • Confirmed the same suite skips cleanly (reports ok, 0 failed) with SIGNAGE_TEST_DATABASE_URL unset, so a developer with no local Postgres is unaffected.
  • repo-boundary --staged — clean.

Plan

plan/server/persistence.md, plan/server/configuration.md, plan/server/content-model.md, plan/server/users-and-permissions.md, plan/server/enrolment.md

## What The database half of signage #9b: Postgres schema, migrations and a `server::db` module. - One embedded sqlx migration, `crates/server/migrations/0001_initial_schema.sql`, with the MVP tables: `users`, `sessions`, `devices`, `ip_rules`, `media`, `playlists`, `playlist_items`, `device_playlist_assignments`. - `server::db::connect` opens a pool from `DatabaseConfig` and refuses a weak `sslmode` (`disable`/`allow`) unless `database.allow_plaintext_connections = true` is set — see plan/server/persistence.md, "TLS to the database". - `server::db::migrate` runs every pending migration; safe to call on every start-up. - Integration tests in `crates/server/tests/db_integration.rs` run against a real Postgres via `SIGNAGE_TEST_DATABASE_URL`, and skip cleanly (reporting `ok`, not a failure) when that variable is absent — see plan/operations/testing-strategy.md. - CI's `test` job now runs a `postgres:16` service and sets `SIGNAGE_TEST_DATABASE_URL`, so these integration tests actually run there instead of silently skipping. **Not included:** wiring the pool into `main.rs`. GRI-1838 (the axum skeleton, Voidgazer) has not merged yet, and the task names `http.rs` and the serve loop in `main.rs` as off-limits until it does. Once it lands, a follow-up merges `origin/dev` and wires the pool in. Unit 2 (#10: login, first-user setup, sessions) is a separate PR on top of this one. ## Done when The server starts against an empty database, runs migrations, and the schema matches the MVP tables named in the issue. Checked: `cargo test -p server --test db_integration` against a disposable local Postgres, migrating an empty database twice (idempotent) and exercising the cascade/CHECK constraints on sessions, playlist items and device assignments. ## Verification - `./scripts/local-gates.sh --rust-only` — every gate passed (fmt, clippy `-D warnings`, `cargo test --workspace --all-targets`, `cargo test --workspace --doc`, `cargo deny check bans licenses sources`). - `cargo test -p server --test db_integration` against a disposable local `postgres:16` database: 5/5 passed (schema has every expected table; migrate is idempotent; a session cascades with its user; a playlist item is refused the schema's own CHECK when its kind-specific column is missing; a device cannot hold two assignment rows). - Confirmed the same suite skips cleanly (reports `ok`, 0 failed) with `SIGNAGE_TEST_DATABASE_URL` unset, so a developer with no local Postgres is unaffected. - `repo-boundary --staged` — clean. ## Plan plan/server/persistence.md, plan/server/configuration.md, plan/server/content-model.md, plan/server/users-and-permissions.md, plan/server/enrolment.md
GRI-1853: Postgres schema, migrations and the db module
Some checks failed
CI / the pre-push hook (pull_request) Successful in 5s
CI / cargo fmt (pull_request) Successful in 4s
CI / cargo deny (pull_request) Successful in 52s
CI / cargo test (pull_request) Failing after 6m14s
CI / cargo deny advisories (pull_request) Failing after 47s
CI / repository boundary (pull_request) Successful in 14s
CI / cargo clippy (pull_request) Successful in 9m45s
CI / frontend-web (pull_request) Successful in 9m42s
CI / CI (pull_request) Failing after 3s
CI / Notify gate failed (pull_request) Successful in 3s
c94447a7a3
Adds the MVP tables (users, sessions, devices, ip_rules, media,
playlists, playlist_items, device_playlist_assignments) as a single
embedded sqlx migration, plus a server::db module that opens the pool
and runs migrations, refusing a weak sslmode unless the admin opts in.

Not wired into main.rs: GRI-1838 (the axum skeleton) has not merged
yet, and the task names that file as off-limits until it does.

Co-Authored-By: Paperclip <noreply@paperclip.ing>
# Conflicts:
#	Cargo.lock
#	Cargo.toml
#	crates/server/Cargo.toml
#	crates/server/src/lib.rs
# Conflicts:
#	Cargo.lock
#	crates/server/Cargo.toml
Merge remote-tracking branch 'origin/dev' into feature/GRI-1853-signage-db-schema
Some checks failed
CI / the pre-push hook (pull_request) Successful in 6s
CI / cargo fmt (pull_request) Successful in 7s
CI / cargo test (pull_request) Failing after 5m56s
CI / cargo deny (pull_request) Successful in 58s
CI / cargo deny advisories (pull_request) Failing after 58s
CI / repository boundary (pull_request) Successful in 10s
CI / cargo clippy (pull_request) Successful in 9m54s
CI / frontend-web (pull_request) Successful in 9m50s
CI / Notify gate failed (pull_request) Successful in 5s
CI / CI (pull_request) Failing after 5s
5b098857a0
GRI-1853: fix CI test-database wiring and per-test isolation
Some checks failed
CI / cargo fmt (pull_request) Successful in 7s
CI / the pre-push hook (pull_request) Successful in 8s
CI / cargo deny advisories (pull_request) Failing after 1m11s
CI / cargo deny (pull_request) Successful in 1m11s
CI / repository boundary (pull_request) Successful in 6s
CI / cargo clippy (pull_request) Successful in 9m49s
CI / cargo test (pull_request) Successful in 10m14s
CI / frontend-web (pull_request) Successful in 9m40s
CI / Notify gate failed (pull_request) Has been skipped
CI / CI (pull_request) Successful in 3s
6ed8cd3b37
Point SIGNAGE_TEST_DATABASE_URL at the postgres service by hostname
instead of localhost with job.services.postgres.ports, which is empty
inside the Forgejo runner's job container. Give each db_integration
test its own database instead of sharing one and racing
DROP SCHEMA public CASCADE across parallel test threads.
vivy merged commit 536d9e0346 into dev 2026-11-09 22:52:16 +00:00
vivy deleted branch feature/GRI-1853-signage-db-schema 2026-11-09 22:52:16 +00:00
Sign in to join this conversation.
No reviewers
No milestone
No project
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference
Grimoirix/grimoire-of-digital-signage!82
No description provided.