theklab_potal

Tables (14)

approval_approvers

  • id — BIGINT — Primary key — NOT NULL
  • approval_id — VARCHAR(36) — Foreign key → approvals.id — NOT NULL
  • approver_id — VARCHAR(36) — NOT NULL
  • approver_order — TINYINT — NOT NULL
  • comment — TEXT — EXT NUL
  • decided_at — DATETIME(6)

approvals

  • id — VARCHAR(36) — Primary key — NOT NULL — PG vault token / 참조 ID (실 카드정보 저장 금지)
  • org_id — VARCHAR(36) — Foreign key → organizations.id — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • description — TEXT
  • requester_id — VARCHAR(36) — Foreign key → users.id — NOT NULL
  • service_code — VARCHAR(32) — NOT NULL — portone | kg_inicis | nice_pay | ...
  • service_reference — VARCHAR(64) — NULL = 모든 서비스
  • status — ENUM('pending', 'in_progress', 'approved', 'rejected', 'cancelled') — NOT NULL
  • approval_mode — ENUM('sequential', 'parallel', 'quorum') — NOT NULL — NULL = 모든 역할
  • required_quorum — TINYINT — login_success | login_failure | signup | logout | token_issue | token_refresh | token_refresh_reuse_detected | password…
  • amount — INT
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL
  • decided_at — DATETIME(6)

audit_logs

  • id — BIGINT — Primary key — NOT NULL
  • event_type — VARCHAR(64) — NOT NULL
  • org_id — VARCHAR(36)
  • client_id — VARCHAR(64)
  • ip_address — VARCHAR(45)
  • user_agent — TEXT
  • metadata — JSON
  • created_at — DATETIME(6) — NOT NULL

email_verifications

  • id — BIGINT — Primary key — NOT NULL
  • email — VARCHAR(190) — NOT NULL
  • code_hash — VARCHAR(255) — NOT NULL — saleslog | industrybook | intellibuy
  • purpose — ENUM('signup', 'password_reset', 'email_change') — NOT NULL
  • expires_at — DATETIME(6) — NOT NULL — UUIDv7
  • attempts — TINYINT — NOT NULL
  • verified_at — DATETIME(6)
  • ip_address — VARCHAR(45)
  • user_agent — TEXT
  • created_at — DATETIME(6) — NOT NULL
  • =이메일 — 인증

notices

  • id — VARCHAR(36) — Primary key — NOT NULL
  • type — ENUM('update', 'notice', 'event') — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • content — MEDIUMTEXT — NOT NULL
  • target_service — VARCHAR(32)
  • Member) — NULL — NOT NULL
  • expires_at — DATETIME(6)
  • is_pinned — TINYINT(1) — NOT NULL
  • created_by — VARCHAR(36) — Foreign key → users.id
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

oauth_clients

  • client_id — VARCHAR(64) — Primary key — NOT NULL
  • client_secret_hash — VARCHAR(255) — NOT NULL — saleslog | industrybook | intellibuy
  • name — VARCHAR(128) — NOT NULL
  • service_code — VARCHAR(32) — NOT NULL — JSON array of URIs
  • redirect_uris — JSON — NOT NULL — "authorization_code","refresh_token"
  • post_logout_redirect_uris — JSON
  • allowed_grants — JSON — NOT NULL — "openid","profile","email","offline_access"
  • allowed_scopes — JSON — NOT NULL — 1=S256 강제
  • token_endpoint_auth_method — VARCHAR(32) — NOT NULL
  • pkce_required — TINYINT(1) — NOT NULL — UUIDv7 권장
  • access_token_format — ENUM('jwt', 'opaque') — NOT NULL
  • is_active — TINYINT(1) — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL
  • =OIDC — 클라이언트(서비스

oidc_payloads

  • model — VARCHAR(48) — Primary key — NOT NULL
  • id — VARCHAR(128) — Primary key — NOT NULL
  • payload — JSON — NOT NULL
  • grant_id — VARCHAR(128) — 서비스 내부 ID (예: PO-2401)
  • user_code — VARCHAR(64)
  • uid — VARCHAR(128)
  • expires_at — DATETIME(6)
  • consumed_at — DATETIME(6) — 시안 확정 후 결정 필요 — 일단 sequential 기본
  • created_at — DATETIME(6) — NOT NULL
  • =node-oidc-provider — Adapter

organizations

  • id — VARCHAR(36) — Primary key — NOT NULL — ISO 3166-1 alpha-2
  • business_number — VARCHAR(20) — NOT NULL — 대표 ID, ^a-zA-Z{4,10} , business_number_type ENUM('business', 'tax') NOT NULL DEFAULT 'business
  • company_name — VARCHAR(128) — NOT NULL
  • country — CHAR(2) — NOT NULL — FK -> users.id (signup TX 마지막에 UPDATE)
  • domain_id — VARCHAR(16) — NOT NULL — UUIDv7, ID Token sub claim
  • admin_user_id — VARCHAR(36) — Foreign key → users.id — argon2id recommended
  • join_policy — ENUM('approval', 'auto') — NOT NULL
  • plan_code — VARCHAR(32) — Foreign key → plans.code
  • status — ENUM('active', 'suspended', 'deleted') — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL
  • deleted_at — DATETIME(6)

payments

  • id — VARCHAR(36) — Primary key — NOT NULL
  • subscription_id — VARCHAR(36) — Foreign key → subscriptions.id
  • org_id — VARCHAR(36) — Foreign key → organizations.id — NOT NULL
  • amount — INT — NOT NULL
  • currency — CHAR(3) — NOT NULL
  • item_description — VARCHAR(255) — NOT NULL
  • status — ENUM('pending', 'completed', 'failed', 'refunded', 'cancelled') — NOT NULL
  • pg_provider — VARCHAR(32)
  • invoice_url — VARCHAR(512)
  • paid_at — DATETIME(6)
  • failed_reason — VARCHAR(255)
  • refunded_at — DATETIME(6)
  • refunded_amount — INT
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

plans

  • code — VARCHAR(32) — Primary key — NOT NULL — starter | professional | enterprise
  • name — VARCHAR(64) — NOT NULL
  • price_monthly — INT — NULL = 문의 (Enterprise)
  • currency — CHAR(3) — NOT NULL
  • max_users — INT — NULL = unlimited
  • storage_gb — INT — NULL = unlimited
  • features — JSON — NOT NULL — JSON array of feature labels
  • is_active — TINYINT(1) — NOT NULL
  • sort_order — INT — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

subscriptions

  • id — VARCHAR(36) — Primary key — NOT NULL
  • org_id — VARCHAR(36) — Foreign key → organizations.id — NOT NULL
  • plan_code — VARCHAR(32) — Foreign key → plans.code — NOT NULL
  • started_at — DATETIME(6) — NOT NULL
  • expires_at — DATETIME(6)
  • next_billing_date — DATE
  • billing_cycle — ENUM('monthly', 'yearly') — NOT NULL
  • status — ENUM('active', 'cancelled', 'expired', 'grace', 'trial') — NOT NULL
  • payment_method_ref — VARCHAR(255)
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

user_notices

  • user_id — VARCHAR(36) — Foreign key → users.id — NOT NULL
  • notice_id — VARCHAR(36) — Foreign key → notices.id — NOT NULL
  • read_at — DATETIME(6) — NOT NULL

user_service_access

  • id — BIGINT — Primary key — NOT NULL
  • user_id — VARCHAR(36) — Foreign key → users.id — NOT NULL
  • service_code — VARCHAR(32) — NOT NULL — 결재 금액 (선택)
  • granted_at — DATETIME(6) — NOT NULL
  • granted_by — VARCHAR(36) — Foreign key → users.id
  • revoked_at — DATETIME(6)
  • revoked_by — VARCHAR(36)
  • =사용자별 — 서비스

users

  • id — VARCHAR(36) — Primary key — NOT NULL — argon2id/bcrypt of 6-digit code (단순 sha256 금지 — 6자리는 엔트로피 ~20bit로 무력)
  • org_id — VARCHAR(36) — Foreign key → organizations.id — NOT NULL
  • email — VARCHAR(190) — NOT NULL
  • email_verified_at — DATETIME(6)
  • password_hash — VARCHAR(255) — NOT NULL — 기본 TTL 180초
  • name — VARCHAR(64) — NOT NULL
  • avatar_url — VARCHAR(255)
  • role — ENUM('Admin', 'Manager', 'Member') — NOT NULL
  • status — ENUM('pending', 'active', 'suspended', 'deleted') — NOT NULL
  • last_login_at — DATETIME(6)
  • failed_login_count — INT — NOT NULL
  • locked_until — DATETIME(6) — 동일 grant의 토큰들을 묶기 위함 (revokeByGrantId용)
  • password_changed_at — DATETIME(6)
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL
  • deleted_at — DATETIME(6)