digiserver-v2/docs/03-data-model.md

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

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.

---

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