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, modified_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

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 SET NULL

Onboarding

• d_onboarding_devices - device-to-screen bindings and onboarded client names.

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

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

        I_RSS_FEEDS ||--o{ I_RSS_FEED_ITEMS : caches
        O_BACKGROUND_TASKS {
                BIGINT id
        }