br-office-suite/database/migrations/002_core_schema.sql

CREATE TABLE IF NOT EXISTS committee (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  committee_type VARCHAR(50) NOT NULL DEFAULT 'general',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS app_user (
  id BIGSERIAL PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash TEXT NOT NULL DEFAULT '',
  role role_type NOT NULL DEFAULT 'member',
  committee_id BIGINT REFERENCES committee(id),
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  security_classification VARCHAR(30) NOT NULL DEFAULT 'standard',
  force_password_reset BOOLEAN NOT NULL DEFAULT FALSE,
  two_factor_enabled BOOLEAN NOT NULL DEFAULT FALSE,
  last_mfa_at TIMESTAMPTZ,
  last_login_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS refresh_token (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
  token_hash TEXT NOT NULL,
  expires_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS br_case (
  id BIGSERIAL PRIMARY KEY,
  case_number VARCHAR(50) UNIQUE NOT NULL,
  title VARCHAR(255) NOT NULL,
  case_type VARCHAR(50) NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'neu',
  priority VARCHAR(20) NOT NULL DEFAULT 'mittel',
  confidentiality_level conf_level NOT NULL DEFAULT 'intern',
  summary TEXT,
  affected_employee_name VARCHAR(120),
  department VARCHAR(120),
  committee_id BIGINT REFERENCES committee(id),
  owner_user_id BIGINT NOT NULL REFERENCES app_user(id),
  legal_hold BOOLEAN NOT NULL DEFAULT FALSE,
  created_by BIGINT REFERENCES app_user(id),
  updated_by BIGINT REFERENCES app_user(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS deadline (
  id BIGSERIAL PRIMARY KEY,
  case_id BIGINT REFERENCES br_case(id) ON DELETE CASCADE,
  title VARCHAR(255) NOT NULL,
  deadline_type VARCHAR(50) NOT NULL,
  due_date TIMESTAMPTZ NOT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'offen',
  remind_days_before INTEGER NOT NULL DEFAULT 2,
  created_by BIGINT REFERENCES app_user(id),
  updated_by BIGINT REFERENCES app_user(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS meeting (
  id BIGSERIAL PRIMARY KEY,
  meeting_number VARCHAR(50) UNIQUE NOT NULL,
  title VARCHAR(255) NOT NULL,
  meeting_type VARCHAR(50) NOT NULL DEFAULT 'ordentlich',
  meeting_date DATE NOT NULL,
  start_time TIME,
  end_time TIME,
  location VARCHAR(255),
  status VARCHAR(30) NOT NULL DEFAULT 'geplant',
  protocol_text TEXT,
  is_quorate BOOLEAN,
  quorum_recorded_at TIMESTAMPTZ,
  quorum_recorded_by BIGINT REFERENCES app_user(id),
  committee_id BIGINT REFERENCES committee(id),
  chair_user_id BIGINT REFERENCES app_user(id),
  secretary_user_id BIGINT REFERENCES app_user(id),
  created_by BIGINT REFERENCES app_user(id),
  updated_by BIGINT REFERENCES app_user(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS meeting_agenda_item (
  id BIGSERIAL PRIMARY KEY,
  meeting_id BIGINT NOT NULL REFERENCES meeting(id) ON DELETE CASCADE,
  agenda_number VARCHAR(20) NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  duration_minutes INTEGER,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS decision (
  id BIGSERIAL PRIMARY KEY,
  decision_number VARCHAR(50) UNIQUE NOT NULL,
  meeting_id BIGINT REFERENCES meeting(id) ON DELETE SET NULL,
  agenda_item_id BIGINT REFERENCES meeting_agenda_item(id) ON DELETE SET NULL,
  case_id BIGINT REFERENCES br_case(id) ON DELETE SET NULL,
  title VARCHAR(255) NOT NULL,
  decision_text TEXT NOT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'offen',
  votes_yes INTEGER NOT NULL DEFAULT 0,
  votes_no INTEGER NOT NULL DEFAULT 0,
  votes_abstain INTEGER NOT NULL DEFAULT 0,
  responsible_user_id BIGINT REFERENCES app_user(id),
  due_date TIMESTAMPTZ,
  created_by BIGINT REFERENCES app_user(id),
  updated_by BIGINT REFERENCES app_user(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS task (
  id BIGSERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  status VARCHAR(30) NOT NULL DEFAULT 'offen',
  priority VARCHAR(20) NOT NULL DEFAULT 'mittel',
  assigned_user_id BIGINT REFERENCES app_user(id),
  backup_user_id BIGINT REFERENCES app_user(id),
  case_id BIGINT REFERENCES br_case(id) ON DELETE SET NULL,
  meeting_id BIGINT REFERENCES meeting(id) ON DELETE SET NULL,
  decision_id BIGINT REFERENCES decision(id) ON DELETE SET NULL,
  due_date TIMESTAMPTZ,
  confidentiality_level conf_level NOT NULL DEFAULT 'intern',
  created_by BIGINT REFERENCES app_user(id),
  updated_by BIGINT REFERENCES app_user(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS document_item (
  id BIGSERIAL PRIMARY KEY,
  document_number VARCHAR(50) UNIQUE NOT NULL,
  title VARCHAR(255) NOT NULL,
  document_type VARCHAR(50) NOT NULL,
  description TEXT,
  version_label VARCHAR(30) NOT NULL DEFAULT 'v1',
  file_name VARCHAR(255),
  storage_path TEXT,
  storage_provider VARCHAR(20) NOT NULL DEFAULT 'local',
  mime_type VARCHAR(100),
  size_bytes BIGINT,
  sha256_hash VARCHAR(64),
  confidentiality_level conf_level NOT NULL DEFAULT 'intern',
  case_id BIGINT REFERENCES br_case(id) ON DELETE SET NULL,
  meeting_id BIGINT REFERENCES meeting(id) ON DELETE SET NULL,
  decision_id BIGINT REFERENCES decision(id) ON DELETE SET NULL,
  uploaded_by BIGINT REFERENCES app_user(id),
  is_signed BOOLEAN NOT NULL DEFAULT FALSE,
  scan_status scan_status_type NOT NULL DEFAULT 'pending',
  scan_engine VARCHAR(50),
  scan_result TEXT,
  uploaded_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS document_scan_event (
  id BIGSERIAL PRIMARY KEY,
  document_id BIGINT NOT NULL REFERENCES document_item(id) ON DELETE CASCADE,
  scanned_by_user_id BIGINT REFERENCES app_user(id),
  status scan_status_type NOT NULL,
  engine VARCHAR(50) NOT NULL,
  result_text TEXT,
  scanned_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS comment_item (
  id BIGSERIAL PRIMARY KEY,
  entity_type VARCHAR(50) NOT NULL,
  entity_id BIGINT NOT NULL,
  comment_text TEXT NOT NULL,
  visibility VARCHAR(30) NOT NULL DEFAULT 'intern',
  author_user_id BIGINT REFERENCES app_user(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS template_item (
  id BIGSERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  category VARCHAR(50) NOT NULL,
  description TEXT,
  content TEXT NOT NULL,
  version_label VARCHAR(30) NOT NULL DEFAULT 'v1',
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_by BIGINT REFERENCES app_user(id),
  updated_by BIGINT REFERENCES app_user(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS notification_item (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
  notification_type VARCHAR(50) NOT NULL,
  title VARCHAR(255) NOT NULL,
  entity_type VARCHAR(50),
  entity_id BIGINT,
  is_read BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS audit_log (
  id BIGSERIAL PRIMARY KEY,
  actor_user_id BIGINT REFERENCES app_user(id),
  action VARCHAR(100) NOT NULL,
  entity_type VARCHAR(50),
  entity_id BIGINT,
  metadata JSONB,
  ip_address INET,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS auth_challenge (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
  challenge_type VARCHAR(40) NOT NULL,
  challenge_value TEXT NOT NULL,
  metadata JSONB,
  expires_at TIMESTAMPTZ NOT NULL,
  used_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS webauthn_challenge (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
  flow_type VARCHAR(40) NOT NULL,
  challenge TEXT NOT NULL,
  expires_at TIMESTAMPTZ NOT NULL,
  used_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS webauthn_credential (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
  credential_id TEXT NOT NULL UNIQUE,
  public_key TEXT NOT NULL,
  counter BIGINT NOT NULL DEFAULT 0,
  transports JSONB,
  credential_device_type VARCHAR(50),
  credential_backed_up BOOLEAN NOT NULL DEFAULT FALSE,
  nickname VARCHAR(120),
  last_used_at TIMESTAMPTZ,
  revoked_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS user_totp_secret (
  user_id BIGINT PRIMARY KEY REFERENCES app_user(id) ON DELETE CASCADE,
  secret_base32 TEXT NOT NULL,
  is_verified BOOLEAN NOT NULL DEFAULT FALSE,
  verified_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);