ske087/digiserver-v2
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
digiserver-v2/docs/03-data-model.md
T
ske087 46602f1933 Sanitize codebase, reorganize docs, and add missing deploy files 
Remove dead code identified in docs/SANITIZATION-REVIEW.md:
- app/blueprints/content_old.py and app/blueprints/playlist.py
- app/models/group.py, app/utils/nginx_config_reader.py
- orphaned templates (content_list, edit_content, upload_content,
  player_page) and the related group/Template references

Result: 6 blueprints, 82 routes, no dead modules or orphan templates.

Add files that deploy.sh and docker-entrypoint.sh already require but
which were never tracked:
- https_manager.py       (referenced by deploy.sh, migrate_network.sh,
                          docker-entrypoint.sh)
- Caddyfile.example      (seeded by deploy.sh; its absence aborts deploy)

Relocate generated Graphify artifacts from graphify-out/ to
docs/graphify-out/ (110 files, no content change) and archive the
superseded docs under docs/.

Ignore hygiene:
- ignore ad-hoc .env backups (.env.bak*) — they contain live secrets
- keep the pre-sanitization snapshots (docs/legacy code/,
  docs/old_code_documentation/) on disk but out of the repo

Fix .env.example: drop a duplicated config block, genericize the
hardcoded host IP, and document HOSTNAME_INTERNAL.
2026-09-11 12:18:34 +03:00

195 lines
7.9 KiB
Markdown
Raw Permalink Blame History

# 03 · Data Model
DigiServer v2 uses **SQLAlchemy 2.0** with **SQLite** (production: `instance/dashboard.db`, dev: `instance/dev.db`, tests: in-memory). All models live in `app/models/` and are re-exported from `app/models/__init__.py`.
---
## 1. Entity-Relationship Overview
```mermaid
erDiagram
user ||--o{ server_log : "writes"
player ||--o{ player_feedback : "sends (cascade)"
player ||--o{ player_edit : "edits (cascade)"
player }o--o| playlist : "assigned_to"
content ||--o{ player_edit : "edited (cascade)"
content ||--o{ player_feedback : "playing"
playlist ||--o{ content : "playlist_content M2M (position,duration,muted,edit_on_player_enabled)"
player_user ||--o{ player_edit : "user_code"
https_config ||--o| https_config : "singleton row"
```
---
## 2. Tables & Columns
### `user` — human accounts
| Column | Type | Notes |
|---|---|---|
| `id` | Integer | PK |
| `username` | String(80) | unique, NOT NULL, indexed |
| `password` | String(120) | bcrypt hash, NOT NULL |
| `role` | String(20) | default `'user'`; also `'admin'`, `'viewer'`; indexed |
| `theme` | String(20) | default `'light'` |
| `created_at` | DateTime | NOT NULL |
| `last_login` | DateTime | nullable |
Key methods: `is_admin` (property), `update_last_login()`. Mixin: `UserMixin` (Flask-Login).
### `player` — signage devices
| Column | Type | Notes |
|---|---|---|
| `id` | Integer | PK |
| `name` | String(255) | NOT NULL |
| `hostname` | String(255) | unique, NOT NULL, indexed |
| `location` | String(255) | nullable |
| `auth_code` | String(255) | unique, NOT NULL, indexed (legacy Bearer auth) |
| `password_hash` | String(255) | NOT NULL (bcrypt) |
| `quickconnect_code` | String(255) | nullable, bcrypt-hashed |
| `orientation` | String(16) | default `'Landscape'` |
| `status` | String(50) | default `'offline'`, indexed |
| `last_seen` | DateTime | indexed |
| `last_heartbeat` | DateTime | indexed |
| `created_at` | DateTime | NOT NULL |
| `playlist_id` | Integer | FK → `playlist.id` ON DELETE SET NULL, indexed |
| `deployment_status` | String(50) | default `'pending'`; pending/deploying/deployed/failed |
| `last_deployment_at` | DateTime | nullable |
| `last_deployment_status` | String(50) | nullable |
| `last_deployment_message` | Text | nullable |
Relationships: `playlist`, `feedback` (→ PlayerFeedback, cascade delete-orphan).
Methods: `is_online` (5-min window), `update_status()`, `set_password()/check_password()`, `set_quickconnect_code()/check_quickconnect_code()`, static `authenticate(hostname, password, quickconnect_code)`.
### `content` — media items (also weblinks)
| Column | Type | Notes |
|---|---|---|
| `id` | Integer | PK |
| `filename` | String(255) | unique, NOT NULL, indexed; may point to `edited_media/<id>/...` after a player edit |
| `original_filename` | String(255) | nullable, indexed — pristine upload name |
| `content_type` | String(50) | NOT NULL, indexed; image/video/pdf/pptx/weblink/other |
| `duration` | Integer | default 10, nullable |
| `file_size` | BigInteger | nullable |
| `url` | String(2048) | nullable — target URL for `weblink` content |
| `description` | Text | nullable |
| `uploaded_at` | DateTime | NOT NULL, indexed |
Relationships: `playlists` (M2M via `playlist_content`).
Properties/methods: `file_size_mb`, `original_display_name`, `original_media_path`, `current_media_path`, `is_image()/is_video()/is_pdf()/is_weblink()`, `has_player_edits`.
### `playlist` — ordered collections of content
| Column | Type | Notes |
|---|---|---|
| `id` | Integer | PK |
| `name` | String(100) | unique, NOT NULL, indexed |
| `description` | Text | nullable |
| `orientation` | String(20) | default `'Landscape'` |
| `version` | Integer | default 1, NOT NULL — **sync detection** |
| `is_active` | Boolean | default True |
| `created_at` | DateTime | NOT NULL |
| `updated_at` | DateTime | NOT NULL, onupdate |
Methods: `player_count`, `content_count`, `total_duration` (properties), `increment_version()`, `get_content_ordered()`.
### `playlist_content` — association table (M2M playlist ↔ content)
| Column | Type | Notes |
|---|---|---|
| `playlist_id` | Integer | FK → `playlist.id` CASCADE, composite PK |
| `content_id` | Integer | FK → `content.id` CASCADE, composite PK |
| `position` | Integer | default 0 — ordering |
| `duration` | Integer | default 10 — per-playlist override |
| `muted` | Boolean | default True |
| `edit_on_player_enabled` | Boolean | default False — whether player-side editing allowed |
### `player_edit` — on-player media edits
| Column | Type | Notes |
|---|---|---|
| `id` | Integer | PK |
| `player_id` | Integer | FK → `player.id` CASCADE, indexed |
| `content_id` | Integer | FK → `content.id` CASCADE, indexed |
| `original_name` | String(255) | NOT NULL |
| `new_name` | String(255) | NOT NULL |
| `version` | Integer | default 1 |
| `user` | String(255) | nullable (user code) |
| `time_of_modification` | DateTime | nullable |
| `metadata_path` | String(512) | nullable |
| `edited_file_path` | String(512) | NOT NULL |
| `created_at` | DateTime | NOT NULL, indexed |
Relationships: `player`, `content`. Method: `to_dict()`.
### `player_feedback` — status messages from players
| Column | Type | Notes |
|---|---|---|
| `id` | Integer | PK |
| `player_id` | Integer | FK → `player.id`, NOT NULL, indexed |
| `status` | String(50) | default `'unknown'` |
| `current_content_id` | Integer | FK → `content.id`, nullable |
| `message` | Text | nullable |
| `error` | Text | nullable |
| `timestamp` | DateTime | NOT NULL, indexed |
Properties/classmethod: `is_error`, `age_seconds`, `get_latest_for_player()`.
### `player_user` — mapping of player edit user codes
| Column | Type | Notes |
|---|---|---|
| `id` | Integer | PK |
| `user_code` | String(255) | **globally unique**, NOT NULL, indexed |
| `user_name` | String(255) | nullable |
| `created_at` | DateTime | NOT NULL |
| `updated_at` | DateTime | NOT NULL, onupdate |
Method: `to_dict()`. *(Migrated from a per-player table — see `migrate_player_user_global.py`.)*
### `server_log` — DB-backed audit log
| Column | Type | Notes |
|---|---|---|
| `id` | Integer | PK |
| `level` | String(20) | NOT NULL, indexed, default `'info'` |
| `message` | Text | NOT NULL |
| `timestamp` | DateTime | NOT NULL, indexed |
Classmethods: `log_info`, `log_warning`, `log_error`.
### `https_config` — HTTPS settings (singleton row)
| Column | Type | Notes |
|---|---|---|
| `id` | Integer | PK |
| `https_enabled` | Boolean | default False |
| `hostname` | String(255) | nullable |
| `domain` | String(255) | nullable |
| `ip_address` | String(45) | nullable (IPv6-capable) |
| `email` | String(255) | nullable (Let's Encrypt contact) |
| `port` | Integer | default 443 |
| `created_at` | DateTime | NOT NULL |
| `updated_at` | DateTime | NOT NULL, onupdate |
| `updated_by` | String(255) | nullable |
Classmethods: `get_config()` (first row), `create_or_update(...)`. Method: `to_dict()`.
### `group` + `group_content` — **REMOVED**
The `Group` model, the `group_content` association table, `Content.groups` /
`Content.group_count`, and the group-management utility functions were **deleted**
during the code sanitization pass (the feature was archived and the table had no
rows). `Player` never had a `group_id` column. See
[SANITIZATION-REVIEW.md](SANITIZATION-REVIEW.md).
---
## 3. Relationship Summary
| Relationship | Cardinality | FK / Mechanism |
|---|---|---|
| `playlist` → `content` | M:N | `playlist_content` (positioned, with extras) |
| `player` → `playlist` | N:1 | `player.playlist_id` (ON DELETE SET NULL) |
| `player` → `player_feedback` | 1:N | `player_feedback.player_id` (cascade) |
| `player` → `player_edit` | 1:N | `player_edit.player_id` (cascade) |
| `content` → `player_edit` | 1:N | `player_edit.content_id` (cascade) |
| `player_user` → `player_edit` | 1:N | `player_edit.user` = `player_user.user_code` (logical) |
| `https_config` | 1 row | singleton via `get_config()` |
---
> Next: [04 · Application Core](04-application-core.md)
Reference in New Issue View Git Blame Copy Permalink