앱 스토어 서버 DB

테이블 (15)

app_versions

  • app_version_id — BIGSERIAL
  • app_id — BIGINT — 외래 키 → apps.app_id — NOT NULL
  • version_name — VARCHAR(50) — NOT NULL
  • version_code — BIGINT — NOT NULL
  • channel — VARCHAR(20) — NOT NULL
  • file_path — TEXT — NOT NULL
  • file_sha256 — CHAR(64) — NOT NULL
  • file_size_bytes — BIGINT — NOT NULL
  • uploaded_by — BIGINT — 외래 키 → users.user_id
  • uploaded_at — TIMESTAMPTZ — NOT NULL
  • UNIQUE(app_id, — version_code,

apps

  • app_id — BIGSERIAL
  • package_name — VARCHAR(200) — NOT NULL
  • display_name — VARCHAR(200) — NOT NULL
  • category — VARCHAR(100)
  • description — TEXT
  • created_by — BIGINT — 외래 키 → users.user_id
  • created_at — TIMESTAMPTZ — NOT NULL

audit_logs

  • audit_id — BIGSERIAL
  • actor_user_id — BIGINT — 외래 키 → users.user_id
  • action — VARCHAR(120) — NOT NULL
  • target_type — VARCHAR(60)
  • target_id — VARCHAR(120)
  • ip — VARCHAR(60)
  • user_agent — TEXT
  • created_at — TIMESTAMPTZ — NOT NULL

departments

  • dept_id — BIGSERIAL
  • parent_dept_id — BIGINT — 외래 키 → departments.dept_id
  • name — VARCHAR(100) — NOT NULL
  • code — VARCHAR(50)
  • created_at — TIMESTAMPTZ — NOT NULL

deployment_target_departments

  • deployment_id — BIGINT — 외래 키 → deployments.deployment_id — NOT NULL
  • dept_id — BIGINT — 외래 키 → departments.dept_id — NOT NULL

deployment_target_users

  • deployment_id — BIGINT — 외래 키 → deployments.deployment_id — NOT NULL
  • user_id — BIGINT — 외래 키 → users.user_id — NOT NULL

deployments

  • deployment_id — BIGSERIAL
  • app_version_id — BIGINT — 외래 키 → app_versions.app_version_id — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • start_at — TIMESTAMPTZ
  • end_at — TIMESTAMPTZ
  • created_by — BIGINT — 외래 키 → users.user_id
  • created_at — TIMESTAMPTZ — NOT NULL

device_security_status

  • device_id — BIGINT — 외래 키 → devices.device_id
  • is_rooted — BOOLEAN — NOT NULL
  • tamper_detected — BOOLEAN — NOT NULL
  • last_checked_at — TIMESTAMPTZ

devices

  • device_id — BIGSERIAL
  • device_uid — VARCHAR(200) — NOT NULL
  • user_id — BIGINT — 외래 키 → users.user_id
  • model — VARCHAR(120)
  • os_version — VARCHAR(50)
  • last_seen_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL

notices

  • notice_id — BIGSERIAL
  • title — VARCHAR(200) — NOT NULL
  • body — TEXT — NOT NULL
  • is_published — BOOLEAN — NOT NULL
  • published_at — TIMESTAMPTZ
  • created_by — BIGINT — 외래 키 → users.user_id
  • created_at — TIMESTAMPTZ — NOT NULL

permissions

  • perm_id — BIGSERIAL
  • code — VARCHAR(120) — NOT NULL
  • description — TEXT

role_permissions

  • role_id — BIGINT — 외래 키 → roles.role_id — NOT NULL
  • perm_id — BIGINT — 외래 키 → permissions.perm_id — NOT NULL

roles

  • role_id — BIGSERIAL
  • name — VARCHAR(80) — NOT NULL
  • description — TEXT
  • created_at — TIMESTAMPTZ — NOT NULL

user_roles

  • user_id — BIGINT — 외래 키 → users.user_id — NOT NULL
  • role_id — BIGINT — 외래 키 → roles.role_id — NOT NULL

users

  • user_id — BIGSERIAL
  • dept_id — BIGINT — 외래 키 → departments.dept_id
  • username — VARCHAR(80) — NOT NULL
  • display_name — VARCHAR(120) — NOT NULL
  • password_hash — TEXT — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • is_admin — BOOLEAN — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL