infoscreen/DATABASE_GUIDE.md

Database Initialization and Management Guide

Quick Start

Your database has been successfully initialized! Here's what you need to know:

✅ Current Status

• Database: MariaDB 11.2 running in Docker container infoscreen-db
• Schema: Up to date (check with alembic current in server/)
• Default Data: Admin user and client group created
• Academic Periods: Austrian school years 2024/25 (active), 2025/26, 2026/27

🔐 Default Credentials

• Admin Username: infoscreen_admin
• Admin Password: Check your .env file for DEFAULT_ADMIN_PASSWORD
• Database User: infoscreen_admin
• Database Name: infoscreen_by_taa

Database Management Commands

Initialize/Reinitialize Database

cd /workspace/server
python initialize_database.py

Check Migration Status

cd /workspace/server
alembic current
alembic history --verbose

Run Migrations Manually

cd /workspace/server
alembic upgrade head         # Apply all pending migrations
alembic upgrade +1           # Apply next migration
alembic downgrade -1         # Rollback one migration

Create New Migration

cd /workspace/server
alembic revision --autogenerate -m "Description of changes"

Database Connection Test

cd /workspace/server
python -c "
from database import Session
session = Session()
print('✅ Database connection successful')
session.close()
"

Initialization Scripts

Core Scripts (recommended order):

1. alembic upgrade head - Apply database schema migrations
2. init_defaults.py - Create default user groups and admin user
3. init_academic_periods.py - Set up Austrian school year periods

All-in-One Script:

• initialize_database.py - Complete database initialization (runs all above scripts)

Development/Testing Scripts:

• dummy_clients.py - Creates test client data for development
• dummy_events.py - Creates test event data for development
• sync_existing_clients.py - One-time MQTT sync for existing clients

Database Schema Overview

Main Tables:

• users - User authentication and roles
• clients - Registered client devices
• client_groups - Client organization groups
• events - Scheduled events and presentations
• event_media - Media files for events
• conversions - File conversion jobs (PPT → PDF)
• academic_periods - School year/semester management
• school_holidays - Holiday calendar
• event_exceptions - Overrides and skips for recurring events (per occurrence)
• system_settings - Key–value store for global settings
• alembic_version - Migration tracking

Key details and relationships

• Users (users)

  • Fields: username (unique), password_hash, role (enum: user|editor|admin|superadmin), is_active

• Client groups (client_groups)

  • Fields: name (unique), description, is_active

• Clients (clients)

  • Fields: uuid (PK), network/device metadata, group_id (FK→client_groups, default 1), last_alive (updated on heartbeat), is_active

• Academic periods (academic_periods)

  • Fields: name (unique), optional display_name, start_date, end_date, period_type (enum: schuljahr|semester|trimester), is_active (at most one should be active)
  • Indexes: is_active, dates

• Event media (event_media)

  • Fields: media_type (enum, see below), url, optional file_path, optional message_content, optional academic_period_id
  • Used by events of types: presentation, video, website, message, other

• Events (events)

  • Core: group_id (FK), optional academic_period_id (FK), title, optional description, start, end, event_type (enum), optional event_media_id (FK)
  • Presentation/video extras: autoplay, loop, volume, slideshow_interval, page_progress, auto_progress
  • Recurrence: recurrence_rule (RFC 5545 RRULE), recurrence_end, skip_holidays (bool)
  • Audit/state: created_by (FK→users), updated_by (FK→users), is_active
  • Indexes: start, end, recurrence_rule, recurrence_end
  • Relationships: event_media, academic_period, exceptions (one-to-many to event_exceptions with cascade delete)

• Event exceptions (event_exceptions)

  • Purpose: track per-occurrence skips or overrides for a recurring master event
  • Fields: event_id (FK→events, ondelete CASCADE), exception_date (Date), is_skipped, optional overrides (title, description, start, end)

• School holidays (school_holidays)

  • Unique: (name, start_date, end_date, region)
  • Used in combination with events.skip_holidays

• Conversions (conversions)

  • Purpose: track PPT/PPTX/ODP → PDF processing
  • Fields: source_event_media_id (FK→event_media, ondelete CASCADE), target_format, target_path, status (enum), file_hash, timestamps, error_message
  • Indexes: (source_event_media_id, target_format), (status, target_format)
  • Unique: (source_event_media_id, target_format, file_hash) — idempotency per content

• System settings (system_settings)

  • Key–value store: key (PK), value, optional description, updated_at
  • Notable keys used by the app: presentation_interval, presentation_page_progress, presentation_auto_progress

Enums (reference)

• UserRole: user, editor, admin, superadmin
• AcademicPeriodType: schuljahr, semester, trimester
• EventType: presentation, website, video, message, other, webuntis
• MediaType: pdf, ppt, pptx, odp, mp4, avi, mkv, mov, wmv, flv, webm, mpg, mpeg, ogv, jpg, jpeg, png, gif, bmp, tiff, svg, html, website
• ConversionStatus: pending, processing, ready, failed

Timezones, recurrence, and holidays

• All timestamps are stored/compared as timezone-aware UTC. Any naive datetimes are normalized to UTC before comparisons.
• Recurrence is represented on events via recurrence_rule (RFC 5545 RRULE) and recurrence_end. Do not pre-expand series in the DB.
• Per-occurrence exclusions/overrides are stored in event_exceptions. The API also emits EXDATE tokens matching occurrence start times (UTC) so the frontend can exclude instances natively.
• When skip_holidays is true, occurrences that fall on school holidays are excluded via corresponding event_exceptions.

Environment Variables:

DB_CONN=mysql+pymysql://infoscreen_admin:KqtpM7wmNdM1DamFKs@db/infoscreen_by_taa
DB_USER=infoscreen_admin
DB_PASSWORD=KqtpM7wmNdM1DamFKs
DB_NAME=infoscreen_by_taa
DB_HOST=db

Troubleshooting

Database Connection Issues:

# Check if database container is running
docker ps | grep db

# Check database logs
docker logs infoscreen-db

# Test direct connection
docker exec -it infoscreen-db mysql -u infoscreen_admin -p infoscreen_by_taa

Migration Issues:

# Check current state
cd /workspace/server && alembic current

# Show migration history
cd /workspace/server && alembic history

# Show pending migrations  
cd /workspace/server && alembic show head

Reset Database (⚠️ DESTRUCTIVE):

# Stop services
docker-compose down

# Remove database volume
docker volume rm infoscreen_2025_db-data

# Restart and reinitialize
docker-compose up -d db
cd /workspace/server && python initialize_database.py

Production Deployment

The production setup in docker-compose.prod.yml includes automatic database initialization:

server:
  command: >
    bash -c "alembic -c /app/server/alembic.ini upgrade head &&
             python /app/server/init_defaults.py &&
             exec gunicorn server.wsgi:app --bind 0.0.0.0:8000"

This ensures the database is properly initialized on every deployment.