pulse-signage/docs/schema.md

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, name, description, created_at, created_by, modified_at, modified_by
• 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 and playback options.
• 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, timing, and schedule rules.

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, created_at, created_by, modified_at, modified_by

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, lock_ratio, 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, schedule_mode, schedule_start_datetime, schedule_end_datetime, schedule_start_time, schedule_end_time, schedule_days_json, 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

Devices

• d_players - player registry and connection metadata.
• d_screens - screen records and playlist/player 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

• device_id, public_base_url, internal_base_url, last_seen_at, created_at, modified_at
• device_id is the primary key.

d_screens

• id, name, slug, playlist_id, player_id, created_at, created_by, modified_at, modified_by

• slug is unique.

• Foreign keys: - playlist_id -> c_playlists.id with ON DELETE SET NULL - player_id -> d_players.id with ON DELETE RESTRICT

• player_id is required and defaults to '1' for the current singleton-player model.

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_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

Operations

• o_background_tasks - queue and history for background jobs.

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.

Notes

• The schema is initialized with CREATE TABLE IF NOT EXISTS, so new installs can start from an empty database.
• src/db/index.js also seeds default permissions and the default administrator role.
• The migration module stays in place for future releases, but this version treats the current schema as the install baseline.

erDiagram
        A_USERS {
                INT id
                VARCHAR name
                VARCHAR username
                CHAR password_hash
                VARCHAR password_salt
                INT password_iterations
                TIMESTAMP created_at
                INT created_by
                TIMESTAMP modified_at
                INT modified_by
        }
        A_ROLES {
                INT id
                VARCHAR role_key
                VARCHAR name
                TEXT description
                TIMESTAMP created_at
                INT created_by
                TIMESTAMP modified_at
                INT modified_by
        }
        A_PERMISSIONS {
                INT id
                VARCHAR permission_key
                VARCHAR name
                VARCHAR section_name
                TEXT description
                TIMESTAMP created_at
                TIMESTAMP modified_at
        }
        A_ROLE_PERMISSIONS {
                INT role_id
                INT permission_id
                TIMESTAMP created_at
                INT created_by
                TIMESTAMP modified_at
                INT modified_by
        }
        A_USER_ROLES {
                INT user_id
                INT role_id
                TIMESTAMP created_at
                INT created_by
                TIMESTAMP modified_at
                INT modified_by
        }
        A_SESSIONS {
                CHAR session_hash
                INT user_id
                DATETIME expires_at
                TIMESTAMP created_at
                INT created_by
                TIMESTAMP last_used_at
                INT modified_by
        }
        C_CANVAS_SIZES {
                INT id
                VARCHAR name
                INT width
                INT height
                TIMESTAMP created_at
                INT created_by
                TIMESTAMP modified_at
                INT modified_by
        }
        C_PLAYLISTS {
                INT id
                VARCHAR name
                TINYINT fade_between_slides
                TINYINT skip_unavailable_rtmp
                TIMESTAMP created_at
                INT created_by
                TIMESTAMP modified_at
                INT modified_by
        }
        C_TEMPLATES {
                INT id
                VARCHAR name
                INT canvas_size_id
                VARCHAR background_image_path
                VARCHAR background_color
                TIMESTAMP created_at
                INT created_by
                TIMESTAMP modified_at
                INT modified_by
        }
        C_TEMPLATE_REGIONS {
                INT id
                INT template_id
                VARCHAR region_key
                VARCHAR region_type
                VARCHAR label
                VARCHAR font_family
                VARCHAR lock_ratio
                INT x
                INT y
                INT width
                INT height
                INT z_index
                TIMESTAMP created_at
                INT created_by
                TIMESTAMP modified_at
                INT modified_by
        }
        C_SLIDES {
                INT id
                VARCHAR title
                INT template_id
                JSON content_json
                VARCHAR thumbnail_path
                TIMESTAMP created_at
                INT created_by
                TIMESTAMP modified_at
                INT modified_by
        }
        C_PLAYLIST_SLIDES {
                INT id
                INT playlist_id
                INT slide_id
                INT position
                DECIMAL duration_seconds
                TINYINT use_video_duration
                VARCHAR schedule_mode
                DATETIME schedule_start_datetime
                DATETIME schedule_end_datetime
                TIME schedule_start_time
                TIME schedule_end_time
                JSON schedule_days_json
                TIMESTAMP created_at
                INT created_by
                TIMESTAMP modified_at
                INT modified_by
        }
        D_PLAYERS {
                INT device_id
                VARCHAR public_base_url
                VARCHAR internal_base_url
                TIMESTAMP last_seen_at
                TIMESTAMP created_at
                TIMESTAMP modified_at
        }
        D_SCREENS {
                INT id
                VARCHAR name
                VARCHAR slug
                INT playlist_id
                INT player_id
                TIMESTAMP created_at
                INT created_by
                TIMESTAMP modified_at
                INT modified_by
        }
        D_ONBOARDING_DEVICES {
                VARCHAR device_id
                VARCHAR client_name
                INT screen_id
                TIMESTAMP created_at
                INT created_by
                TIMESTAMP modified_at
                INT modified_by
        }
        D_ANNOUNCEMENTS {
                INT id
                TEXT message
                VARCHAR short_label
                VARCHAR announcement_type
                VARCHAR color_key
                VARCHAR icon_key
                INT duration_seconds
                TIMESTAMP expires_at
                TIMESTAMP created_at
                INT created_by
                TIMESTAMP modified_at
                INT modified_by
        }
        D_ANNOUNCEMENT_SCREENS {
                INT announcement_id
                INT screen_id
                TIMESTAMP created_at
                INT created_by
                TIMESTAMP modified_at
                INT modified_by
        }
        I_RSS_FEEDS {
                INT id
                VARCHAR name
                VARCHAR feed_url
                INT update_interval_value
                VARCHAR update_interval_unit
                INT item_limit
                TIMESTAMP created_at
                INT created_by
                TIMESTAMP modified_at
                INT modified_by
        }
        I_RSS_FEED_ITEMS {
                INT id
                INT rss_feed_id
                INT position
                MEDIUMTEXT item_json
                TIMESTAMP created_at
                INT created_by
                TIMESTAMP modified_at
                INT modified_by
        }
        I_API_SOURCES {
                INT id
                VARCHAR name
                VARCHAR api_url
                INT update_interval_value
                VARCHAR update_interval_unit
                TIMESTAMP last_pulled_at
                MEDIUMTEXT last_pull_error
                INT last_response_status
                VARCHAR last_response_content_type
                MEDIUMTEXT last_response_json
        }
        O_BACKGROUND_TASKS {
                BIGINT id
                VARCHAR task_key
                VARCHAR task_type
                VARCHAR title
                VARCHAR category
                VARCHAR status
                MEDIUMTEXT payload_json
                MEDIUMTEXT metadata_json
                INT attempts
                TIMESTAMP created_at
                TIMESTAMP started_at
                TIMESTAMP finished_at
                MEDIUMTEXT error_message
        }

        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_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

        D_PLAYERS ||--o{ D_SCREENS : assigned_to
        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