GRI-1853: Postgres schema, migrations and the db module (signage #9b) #82
No reviewers
Labels
No labels
Area/Backend
Area/Database
Area/Docs
Area/Frontend
Area/Infra
Area/Security
Area/Tests
Area/Tooling
Compat/Breaking
Kind/Bug
Kind/Documentation
Kind/Enhancement
Kind/Feature
Kind/Security
Kind/Testing
Lang/Python
Lang/Rust
Lang/Shell
Lang/SQL
Lang/TypeScript
Priority
Critical
Priority
High
Priority
Low
Priority
Medium
Reviewed
Confirmed
Reviewed
Duplicate
Reviewed
Invalid
Reviewed
Won't Fix
Routing
Auto
Routing
Hold
Routing
Human
Status
Abandoned
Status
Blocked
Status
Need More Info
No milestone
No project
No assignees
1 participant
Notifications
Due date
No due date set.
Dependencies
No dependencies set
Reference
Grimoirix/grimoire-of-digital-signage!82
Loading…
Reference in a new issue
No description provided.
Delete branch "feature/GRI-1853-signage-db-schema"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
What
The database half of signage #9b: Postgres schema, migrations and a
server::dbmodule.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::connectopens a pool fromDatabaseConfigand refuses a weaksslmode(disable/allow) unlessdatabase.allow_plaintext_connections = trueis set — see plan/server/persistence.md, "TLS to the database".server::db::migrateruns every pending migration; safe to call on every start-up.crates/server/tests/db_integration.rsrun against a real Postgres viaSIGNAGE_TEST_DATABASE_URL, and skip cleanly (reportingok, not a failure) when that variable is absent — see plan/operations/testing-strategy.md.testjob now runs apostgres:16service and setsSIGNAGE_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 nameshttp.rsand the serve loop inmain.rsas off-limits until it does. Once it lands, a follow-up mergesorigin/devand 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_integrationagainst 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_integrationagainst a disposable localpostgres:16database: 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).ok, 0 failed) withSIGNAGE_TEST_DATABASE_URLunset, 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