VPN Platform 2

Tables (10)

Asset

  • id — SERIAL — Primary key — NOT NULL
  • asset_code — VARCHAR(40)
  • asset_type — ENUM — NOT NULL
  • model_id — INT — Foreign key → DeviceModel.id
  • serial_number — VARCHAR(100)
  • status — VARCHAR(20) — NOT NULL
  • location — VARCHAR(200)
  • purchase_order_no — VARCHAR(60)
  • purchase_date — DATE
  • is_new — BOOLEAN
  • original_business — VARCHAR(120)
  • note — TEXT
  • os_version — VARCHAR(60)
  • license_expire_date — DATE
  • is_owned — BOOLEAN
  • credential_reference — VARCHAR(300)
  • management_port — INT
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

AssetAssignment

  • id — SERIAL — Primary key — NOT NULL
  • asset_id — INT — Foreign key → Asset.id — NOT NULL
  • project_id — INT — Foreign key → Project.id
  • group_id — INT — Foreign key → AssetGroup.id
  • assigned_at — DATE
  • returned_at — DATE
  • status — VARCHAR(20) — NOT NULL
  • role — VARCHAR(20)
  • installation_location — VARCHAR(200)
  • hostname — VARCHAR(200)
  • public_ip — INET
  • management_ip — INET
  • remote_access_type — VARCHAR(20)
  • site_type — VARCHAR(20)
  • note — TEXT
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

AssetCredential

  • asset_id — INT — Foreign key → Asset.id — NOT NULL
  • access_method — VARCHAR(20) — NOT NULL
  • login_id — VARCHAR(100)
  • password_enc — BYTEA
  • port — INT
  • gui_url — BYTEA
  • gui_user — BYTEA
  • gui_password — BYTEA
  • last_rotated_at — TIMESTAMP
  • updated_at — TIMESTAMP — NOT NULL

AssetGroup

  • id — SERIAL — Primary key — NOT NULL
  • group_name — VARCHAR(120) — NOT NULL
  • match_method — VARCHAR(20) — NOT NULL
  • note — TEXT
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

DeviceModel

  • id — SERIAL — Primary key — NOT NULL
  • vendor_id — INT — Foreign key → Vendor.id — NOT NULL
  • model_name — VARCHAR(120) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL

Project

  • id — SERIAL — Primary key — NOT NULL
  • project_code — VARCHAR(60)
  • project_name — VARCHAR(200)

Rental

  • id — SERIAL — Primary key — NOT NULL
  • project_id — INT — Foreign key → Project.id

RentalDevice

  • id — SERIAL — Primary key — NOT NULL
  • rental_id — INT — Foreign key → Rental.id — NOT NULL
  • asset_id — INT — Foreign key → Asset.id — NOT NULL
  • unit_price — DECIMAL(14,2)
  • note — TEXT
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

Vendor

  • id — SERIAL — Primary key — NOT NULL
  • name — VARCHAR(60) — NOT NULL
  • name_ko — VARCHAR(60)
  • active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP — NOT NULL

WorkLog

  • id — SERIAL — Primary key — NOT NULL
  • work_date — DATE — NOT NULL
  • project_id — INT — Foreign key → Project.id
  • asset_id — INT — Foreign key → Asset.id
  • category — VARCHAR(20) — NOT NULL
  • support_method — VARCHAR(20)
  • operator_id — INT
  • description — TEXT
  • duration_hours — DECIMAL(5,2)
  • issue_note — TEXT
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL