Databázové schema

PostgreSQL 16 databáze s 17 tabulkami. Schema je definováno v sql/init.sql. TypeORM entity jsou v src/entities/.

Přehled tabulek

┌──────────────────────────────────────────────────────────────────┐
│                         DATABÁZOVÉ SCHEMA                        │
├──────────────────┬───────────────────────────────────────────────┤
│ Uživatelé        │ users, super_admins, app_admins               │
│ Aplikace         │ applications                                   │
│ Oprávnění        │ permissions, roles, role_permissions           │
│ Přiřazení        │ user_app_roles, user_resource_permissions      │
│ Profily          │ user_profiles, user_app_preferences           │
│ Notifikace       │ notifications, notification_types,            │
│                  │ notification_templates,                        │
│                  │ user_notification_preferences                  │
│ Push             │ push_subscriptions                             │
│ Brandy           │ brands                                         │
└──────────────────┴───────────────────────────────────────────────┘

---

Tabulka users — registr uživatelů

Centrální registr všech uživatelů. Email je primární klíč.

CREATE TABLE users (
  email      VARCHAR PRIMARY KEY,
  active     BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Sloupec	Typ	Popis
email	PK	Primární identifikátor
active	boolean	Aktivní/deaktivovaný účet
created_at	timestamp	Datum registrace

Uživatelé se vytváří automaticky (lazy) při prvním přiřazení role nebo oprávnění.

---

Tabulka applications

CREATE TABLE applications (
  id          SERIAL PRIMARY KEY,
  code        VARCHAR UNIQUE NOT NULL,
  name        VARCHAR NOT NULL,
  description TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

---

Tabulka permissions — definice oprávnění

CREATE TABLE permissions (
  id                   SERIAL PRIMARY KEY,
  application_id       INT NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
  code                 VARCHAR NOT NULL,
  description          TEXT,
  type                 VARCHAR NOT NULL CHECK (type IN ('binary', 'tiered')),
  parent_permission_id INT REFERENCES permissions(id) ON DELETE SET NULL,
  group_name           VARCHAR,
  created_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (application_id, code)
);

Sloupec	Popis
code	Unikátní kód v rámci aplikace
type	binary nebo tiered
parent_permission_id	Self-reference pro UI tree
group_name	Skupina pro UI groupování

---

Tabulka roles

CREATE TABLE roles (
  id             SERIAL PRIMARY KEY,
  application_id INT NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
  code           VARCHAR NOT NULL,
  description    TEXT,
  mdi_icon       VARCHAR,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (application_id, code)
);

---

Tabulka role_permissions — M:N role ↔ oprávnění

CREATE TABLE role_permissions (
  role_id        INT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  permission_id  INT NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
  access_level   VARCHAR NOT NULL CHECK (access_level IN ('allowed', 'view', 'edit')),
  PRIMARY KEY (role_id, permission_id)
);

Compound PK — každá role může mít každé oprávnění nejvýše jednou.

---

Tabulka user_app_roles — přiřazení rolí uživatelům

CREATE TABLE user_app_roles (
  id             SERIAL PRIMARY KEY,
  email          VARCHAR NOT NULL REFERENCES users(email) ON DELETE CASCADE,
  application_id INT NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
  role_id        INT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (email, application_id, role_id)
);

CREATE INDEX idx_user_app_roles_email ON user_app_roles(email);
CREATE INDEX idx_user_app_roles_app ON user_app_roles(application_id);

---

Tabulka user_resource_permissions — resource-level overrides

CREATE TABLE user_resource_permissions (
  id             SERIAL PRIMARY KEY,
  email          VARCHAR NOT NULL REFERENCES users(email) ON DELETE CASCADE,
  application_id INT NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
  resource_type  VARCHAR NOT NULL,
  resource_id    VARCHAR NOT NULL,
  permission_id  INT NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
  granted        BOOLEAN NOT NULL,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (email, application_id, resource_type, resource_id, permission_id)
);

---

Tabulky app_admins a super_admins

CREATE TABLE super_admins (
  id         SERIAL PRIMARY KEY,
  email      VARCHAR UNIQUE NOT NULL REFERENCES users(email) ON DELETE CASCADE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE app_admins (
  id             SERIAL PRIMARY KEY,
  email          VARCHAR NOT NULL REFERENCES users(email) ON DELETE CASCADE,
  application_id INT NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (email, application_id)
);

---

Tabulka user_profiles

CREATE TABLE user_profiles (
  id           SERIAL PRIMARY KEY,
  email        VARCHAR UNIQUE NOT NULL,
  display_name VARCHAR,
  avatar_url   TEXT,
  locale       VARCHAR NOT NULL DEFAULT 'cs',
  theme        VARCHAR NOT NULL DEFAULT 'light' CHECK (theme IN ('light', 'dark', 'system')),
  brand        VARCHAR NOT NULL DEFAULT 'atrea',
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

---

Tabulka user_app_preferences

CREATE TABLE user_app_preferences (
  id                     SERIAL PRIMARY KEY,
  user_profile_id        INT NOT NULL REFERENCES user_profiles(id) ON DELETE CASCADE,
  application_id         INT NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
  show_help              BOOLEAN NOT NULL DEFAULT TRUE,
  notifications_enabled  BOOLEAN NOT NULL DEFAULT TRUE,
  created_at             TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at             TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (user_profile_id, application_id)
);

---

Tabulky notifikací

notification_types

CREATE TABLE notification_types (
  id               SERIAL PRIMARY KEY,
  application_id   INT NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
  code             VARCHAR NOT NULL,
  name             VARCHAR NOT NULL,
  description      TEXT,
  email_enabled    BOOLEAN NOT NULL DEFAULT TRUE,
  push_enabled     BOOLEAN NOT NULL DEFAULT FALSE,
  user_can_disable BOOLEAN NOT NULL DEFAULT TRUE,
  default_email    BOOLEAN NOT NULL DEFAULT TRUE,
  default_push     BOOLEAN NOT NULL DEFAULT FALSE,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (application_id, code)
);

notification_templates

CREATE TABLE notification_templates (
  id               SERIAL PRIMARY KEY,
  application_id   INT NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
  type             VARCHAR NOT NULL,
  locale           VARCHAR NOT NULL,
  subject_template TEXT NOT NULL,
  body_template    TEXT NOT NULL,
  description      TEXT,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (application_id, type, locale)
);

notifications — audit log

CREATE TABLE notifications (
  id              SERIAL PRIMARY KEY,
  recipient_email VARCHAR NOT NULL,
  application_id  INT REFERENCES applications(id) ON DELETE SET NULL,
  type            VARCHAR NOT NULL,
  subject         VARCHAR,
  status          VARCHAR NOT NULL CHECK (status IN ('sent', 'failed', 'skipped')),
  status_reason   TEXT,
  locale          VARCHAR,
  payload         JSONB,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_notifications_recipient ON notifications(recipient_email, application_id, created_at);

user_notification_preferences

CREATE TABLE user_notification_preferences (
  id                   SERIAL PRIMARY KEY,
  user_profile_id      INT NOT NULL REFERENCES user_profiles(id) ON DELETE CASCADE,
  notification_type_id INT NOT NULL REFERENCES notification_types(id) ON DELETE CASCADE,
  email                BOOLEAN NOT NULL DEFAULT TRUE,
  push                 BOOLEAN NOT NULL DEFAULT FALSE,
  created_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (user_profile_id, notification_type_id)
);

---

Tabulka push_subscriptions

CREATE TABLE push_subscriptions (
  id              SERIAL PRIMARY KEY,
  user_profile_id INT NOT NULL REFERENCES user_profiles(id) ON DELETE CASCADE,
  endpoint        TEXT UNIQUE NOT NULL,
  key_p256dh      TEXT NOT NULL,
  key_auth        TEXT NOT NULL,
  device_label    VARCHAR,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_push_subscriptions_user ON push_subscriptions(user_profile_id);

---

Tabulka brands

CREATE TABLE brands (
  id            SERIAL PRIMARY KEY,
  slug          VARCHAR UNIQUE NOT NULL,
  name          VARCHAR NOT NULL,
  primary_color VARCHAR NOT NULL,
  primary_dark  VARCHAR NOT NULL,
  background    VARCHAR DEFAULT '#FFFFFF',
  font_color    VARCHAR DEFAULT '#212121',
  link_color    VARCHAR NOT NULL,
  logo_url      TEXT NOT NULL,
  footer_text   TEXT,
  email_from    VARCHAR NOT NULL,
  is_default    BOOLEAN NOT NULL DEFAULT FALSE,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Seed data (brands)

INSERT INTO brands (slug, name, primary_color, primary_dark, link_color, logo_url, email_from, is_default)
VALUES
  ('atrea', 'Atrea', '#1976D2', '#1565C0', '#1976D2', '/assets/img/atrea/logo.svg', 'noreply@atrea.eu', TRUE),
  ('vallox', 'Vallox', '#E53935', '#C62828', '#E53935', '/assets/img/vallox/logo.svg', 'noreply@vallox.cz', FALSE),
  ('airflow', 'Airflow', '#2E7D32', '#1B5E20', '#2E7D32', '/assets/img/airflow/logo.svg', 'noreply@airflow.cz', FALSE);

---

ER diagram (zjednodušený)

applications ──────────────────────────────────────────────┐
    │                                                       │
    ├── permissions (application_id FK)                     │
    │       └── role_permissions (permission_id FK)         │
    │                   └── roles (role_id FK) ─────────────┤
    │                                                       │
    ├── user_app_roles (application_id FK)                  │
    │       └── users (email FK) ◄──────────────────────────┤
    │               └── super_admins                        │
    │               └── app_admins (application_id FK) ─────┘
    │               └── user_resource_permissions
    │               └── user_profiles
    │                       └── user_app_preferences
    │                       └── user_notification_preferences
    │                       └── push_subscriptions
    │
    ├── notification_types (application_id FK)
    │       └── notification_templates
    │       └── user_notification_preferences (notification_type_id FK)
    │
    └── notifications (application_id FK)

brands (nezávislá tabulka — odkazována z user_profiles.brand jako slug)

---

Inicializace databáze

# Vytvoření tabulek
psql -h localhost -U app -d atrea-user-db < sql/init.sql

# Seed data (brandy)
psql -h localhost -U app -d atrea-user-db < sql/data.sql

# Reset databáze (POZOR — smaže všechna data!)
psql -h localhost -U app -d atrea-user-db < sql/drop.sql