# Database Schema

This app creates and maintains its schema at startup through `src/db/index.js`.
The sections below summarize the current tables and their purpose.

Primary keys are `id` unless noted otherwise. Timestamps are stored as `created_at` and `modified_at` when a table supports auditing.

## Admin

- `a_users` - admin user accounts and password hashes.
- `a_roles` - named role definitions.
- `a_permissions` - permission catalog seeded from the application constants.
- `a_role_permissions` - many-to-many mapping between roles and permissions.
- `a_user_roles` - many-to-many mapping between users and roles.
- `a_sessions` - persisted admin session tokens.

### `a_users`

- `id`, `name`, `username`, `password_hash`, `password_salt`, `password_iterations`, `created_at`, `created_by`, `modified_at`, `modified_by`
- `username` is unique.

### `a_roles`

- `id`, `role_key`, `name`, `description`, `created_at`, `created_by`, `modified_at`, `modified_by`
- `role_key` is unique.
- `name` is unique.

### `a_permissions`

- `id`, `permission_key`, `name`, `section_name`, `description`, `created_at`, `modified_at`
- `permission_key` is unique.

### `a_role_permissions`

- `role_id`, `permission_id`, `created_at`, `modified_at`
- Foreign keys:
        - `role_id` -> `a_roles.id`
        - `permission_id` -> `a_permissions.id`
- Composite primary key: `(role_id, permission_id)`

### `a_user_roles`

- `user_id`, `role_id`, `created_at`, `modified_at`
- Foreign keys:
        - `user_id` -> `a_users.id`
        - `role_id` -> `a_roles.id`
- Composite primary key: `(user_id, role_id)`

### `a_sessions`

- `session_hash`, `user_id`, `expires_at`, `created_at`, `created_by`, `last_used_at`, `modified_by`
- `session_hash` is the primary key.
- Foreign key:
        - `user_id` -> `a_users.id`

## Content

- `c_canvas_sizes` - reusable canvas presets for templates.
- `c_playlists` - playlist definitions, playback options, and canvas assignment.
- `c_templates` - slide templates with canvas and background settings.
- `c_template_regions` - template region layout and metadata.
- `c_slides` - slide records with template binding, JSON content, and thumbnail path.
- `c_playlist_slides` - ordered playlist items and timing.
- `c_playlist_slide_schedule_rules` - rule rows attached to playlist slides.

### `c_canvas_sizes`

- `id`, `name`, `width`, `height`, `created_at`, `created_by`, `modified_at`, `modified_by`
- `(width, height)` is unique.

### `c_playlists`

- `id`, `name`, `fade_between_slides`, `skip_unavailable_rtmp`, `canvas_id`, `created_at`, `created_by`, `modified_at`, `modified_by`
- Foreign key:
        - `canvas_id` -> `c_canvas_sizes.id` with `ON DELETE SET NULL`

### `c_templates`

- `id`, `name`, `canvas_size_id`, `background_image_path`, `background_color`, `created_at`, `created_by`, `modified_at`, `modified_by`
- Foreign key:
        - `canvas_size_id` -> `c_canvas_sizes.id` with `ON DELETE SET NULL`

### `c_template_regions`

- `id`, `template_id`, `region_key`, `region_type`, `label`, `font_family`, `lock_ratio`, `animation_json`, `x`, `y`, `width`, `height`, `z_index`, `created_at`, `created_by`, `modified_at`, `modified_by`
- Foreign key:
        - `template_id` -> `c_templates.id` with `ON DELETE CASCADE`

### `c_slides`

- `id`, `title`, `template_id`, `content_json`, `thumbnail_path`, `created_at`, `created_by`, `modified_at`, `modified_by`
- Foreign key:
        - `template_id` -> `c_templates.id` with `ON DELETE SET NULL`

### `c_playlist_slides`

- `id`, `playlist_id`, `slide_id`, `position`, `duration_seconds`, `use_video_duration`, `disable_audio`, `created_at`, `created_by`, `modified_at`, `modified_by`
- Foreign keys:
        - `playlist_id` -> `c_playlists.id` with `ON DELETE CASCADE`
        - `slide_id` -> `c_slides.id` with `ON DELETE CASCADE`

### `c_playlist_slide_schedule_rules`

- `id`, `playlist_slide_id`, `position`, `start_datetime`, `end_datetime`, `start_time`, `end_time`, `schedule_days_json`, `created_at`, `created_by`, `modified_at`, `modified_by`
- Foreign key:
        - `playlist_slide_id` -> `c_playlist_slides.id` with `ON DELETE CASCADE`
- Index:
        - `(playlist_slide_id, position)`

- Each playlist slide can have zero or more schedule rules.
- Rules are evaluated with OR across rows and with AND across the fields inside a single rule.

## Devices

- `d_players` - player registry and connection metadata.
- `d_screens` - screen records and playlist assignment.
- `d_onboarding_devices` - device-to-screen bindings and onboarded client names.

## Announcements

- `d_announcements` - announcement content and display metadata.
- `d_announcement_screens` - announcement-to-screen assignments.

### `d_players`

- `id`, `identifier`, `public_base_url`, `internal_base_url`, `last_seen_at`, `created_at`, `modified_at`
- `id` is the primary key.
- `identifier` is unique and is the stable player identity used by the app.
        
### `d_screens`

- `id`, `name`, `slug`, `playlist_id`, `created_at`, `created_by`, `modified_at`, `modified_by`
- `slug` is unique.
- Foreign keys:
        - `playlist_id` -> `c_playlists.id` with `ON DELETE SET NULL`

- Screens are no longer tied to a player foreign key directly; player registration and live connection metadata are tracked separately in `d_players`.

### `d_onboarding_devices`

- `device_id`, `client_name`, `screen_id`, `created_at`, `created_by`, `modified_at`, `modified_by`
- `device_id` is the primary key.
- Foreign key:
        - `screen_id` -> `d_screens.id` with `ON DELETE SET NULL`

### `d_announcements`

- `id`, `message`, `short_label`, `announcement_type`, `color_key`, `icon_key`, `duration_seconds`, `expires_at`, `created_at`, `created_by`, `modified_at`, `modified_by`
- `announcement_type` defaults to `lower-third`.

### `d_announcement_screens`

- `announcement_id`, `screen_id`, `created_at`, `created_by`, `modified_at`, `modified_by`
- Foreign keys:
        - `announcement_id` -> `d_announcements.id` with `ON DELETE CASCADE`
        - `screen_id` -> `d_screens.id` with `ON DELETE CASCADE`
- Composite primary key: `(announcement_id, screen_id)`

## Onboarding

- The onboarding flow uses `d_onboarding_devices` to bind a device to a screen and persist the client name.

## Integrations

- `i_rss_feeds` - RSS feed definitions and refresh cadence.
- `i_rss_feed_items` - cached RSS feed items.
- `i_api_sources` - API source definitions and last response snapshot.
- `i_timetable_groups` - grouped timetable definitions used by the timetable region.
- `i_timetable_entries` - dated entries that belong to a timetable group.

### `i_rss_feeds`

- `id`, `name`, `feed_url`, `update_interval_value`, `update_interval_unit`, `item_limit`, `created_at`, `created_by`, `modified_at`, `modified_by`

### `i_rss_feed_items`

- `id`, `rss_feed_id`, `position`, `item_json`, `created_at`, `created_by`, `modified_at`, `modified_by`
- Foreign key:
        - `rss_feed_id` -> `i_rss_feeds.id` with `ON DELETE CASCADE`
- Unique key:
        - `(rss_feed_id, position)`

### `i_api_sources`

- `id`, `name`, `api_url`, `auth_method`, `auth_username`, `auth_password`, `auth_bearer_token`, `auth_header_name`, `auth_header_value`, `items_path`, `update_interval_value`, `update_interval_unit`, `last_pulled_at`, `last_pull_error`, `last_response_status`, `last_response_content_type`, `last_response_json`, `created_at`, `created_by`, `modified_at`, `modified_by`

### `i_timetable_groups`

- `id`, `name`, `short_description`, `timezone`, `created_at`, `created_by`, `modified_at`, `modified_by`
- `timezone` defaults to `Europe/London`.

### `i_timetable_entries`

- `id`, `schedule_group_id`, `title`, `short_description`, `start_datetime`, `end_datetime`, `created_at`, `created_by`, `modified_at`, `modified_by`
- Foreign key:
        - `schedule_group_id` -> `i_timetable_groups.id` with `ON DELETE CASCADE`
- Index:
        - `(schedule_group_id, start_datetime)`

## Operations

- `o_background_tasks` - queue and history for background jobs.
- `o_app_state` - generic app state and version markers stored as key/value pairs.

### `o_background_tasks`

- `id`, `task_key`, `task_type`, `title`, `category`, `status`, `payload_json`, `metadata_json`, `attempts`, `created_at`, `created_by`, `started_at`, `finished_at`, `error_message`
- Indexed by `status`, `task_key`, and `task_type`.

### `o_app_state`

- `state_key`, `state_value`, `created_at`, `modified_at`
- `state_key` is the primary key.
- `schema_version` is stored here so startup can detect the previously recorded schema version before deciding whether migrations need to run.

## Notes

- The schema is initialized with `CREATE TABLE IF NOT EXISTS`, so new installs can start from an empty database.
- `src/db/bootstrap.js` seeds the canvas size defaults, default permissions, and the default administrator role.
- `src/db/migrations.js` records the current schema version in `o_app_state` during startup so later launches can tell whether an update is happening.

```mermaid
erDiagram
        A_USERS {
        }
        A_ROLES {
        }
        A_PERMISSIONS {
        }
        A_ROLE_PERMISSIONS {
        }
        A_USER_ROLES {
        }
        A_SESSIONS {
        }
        C_CANVAS_SIZES {
        }
        C_PLAYLISTS {
        }
        C_TEMPLATES {
        }
        C_TEMPLATE_REGIONS {
        }
        C_SLIDES {
        }
        C_PLAYLIST_SLIDES {
        }
        C_PLAYLIST_SLIDE_SCHEDULE_RULES {
        }
        D_PLAYERS {
        }
        D_SCREENS {
        }
        D_ONBOARDING_DEVICES {
        }
        D_ANNOUNCEMENTS {
        }
        D_ANNOUNCEMENT_SCREENS {
        }
        I_RSS_FEEDS {
        }
        I_RSS_FEED_ITEMS {
        }
        I_API_SOURCES {
        }
        I_TIMETABLE_GROUPS {
        }
        I_TIMETABLE_ENTRIES {
        }
        O_APP_STATE {
        }
        O_BACKGROUND_TASKS {
        }

        A_USERS ||--o{ A_USER_ROLES : has
        A_ROLES ||--o{ A_USER_ROLES : assigned_to
        A_ROLES ||--o{ A_ROLE_PERMISSIONS : has
        A_PERMISSIONS ||--o{ A_ROLE_PERMISSIONS : granted_to
        A_USERS ||--o{ A_SESSIONS : owns

        C_CANVAS_SIZES ||--o{ C_TEMPLATES : used_by
        C_CANVAS_SIZES ||--o{ C_PLAYLISTS : used_by
        C_TEMPLATES ||--o{ C_TEMPLATE_REGIONS : contains
        C_TEMPLATES ||--o{ C_SLIDES : used_by
        C_PLAYLISTS ||--o{ C_PLAYLIST_SLIDES : contains
        C_SLIDES ||--o{ C_PLAYLIST_SLIDES : included_in
        C_PLAYLIST_SLIDES ||--o{ C_PLAYLIST_SLIDE_SCHEDULE_RULES : has_rules

        C_PLAYLISTS ||--o{ D_SCREENS : uses
        D_SCREENS ||--o{ D_ONBOARDING_DEVICES : binds
        D_ANNOUNCEMENTS ||--o{ D_ANNOUNCEMENT_SCREENS : targets
        D_SCREENS ||--o{ D_ANNOUNCEMENT_SCREENS : receives

        I_RSS_FEEDS ||--o{ I_RSS_FEED_ITEMS : caches
        I_TIMETABLE_GROUPS ||--o{ I_TIMETABLE_ENTRIES : contains
```