---
title: 資料庫架構
tags:
- 第一組
---
# 資料庫架構

本資料庫設計用於支援惡意程式樣本收集、分析、標註與存取控制，支援完整的樣本追蹤與特徵管理流程，並可延伸整合自動化分析或威脅情報平台。

## 核心資料表

### 1.樣本

CREATE TABLE "samples" (
    -- sha256
    "sample_id" char(64) NOT NULL,
    "original_name" text,
    "md5_hash" char(32) NOT NULL,
    "sha1_hash" char(40) NOT NULL,
    "sha256_hash" char(64) NOT NULL,
    "file_size" integer NOT NULL,
    "file_type" text NOT NULL,
    "platform_id" bigint,
    "malware_family_id" bigint,
    "malware_type_id" bigint,
    "threat_actor_id" bigint NOT NULL,
    "source_id" bigint NOT NULL,
    "sample_file_path" text NOT NULL,
    "uploaded_by" uuid NOT NULL,
    "uploaded_at" timestamp NOT NULL,
    "notes" text,
    PRIMARY KEY ("sample_id")
);

class SampleBase(SQLModel):
    original_name: str | None = Field(default=None)
    md5_hash: str = Field(max_length=32)
    sha1_hash: str = Field(max_length=40)
    sha256_hash: str = Field(max_length=64)
    file_size: int
    file_type: str
    sample_file_path: str
    notes: str | None = Field(default=None)


class SampleCreate(SQLModel):
    original_name: str | None = Field(default=None)
    sample_file_path: str
    notes: str | None = Field(default=None)

    platform_id: int | None = Field(default=None)
    malware_family_id: int | None = Field(default=None)
    malware_type_id: int | None = Field(default=None)
    threat_actor_id: int | None = Field(default=None)
    source_id: int
    uploaded_by: uuid.UUID


class SampleUpdate(SQLModel):
    original_name: str | None = Field(default=None)
    sample_file_path: str | None = Field(default=None)
    notes: str | None = Field(default=None)

    platform_id: int | None = Field(default=None)
    malware_family_id: int | None = Field(default=None)
    malware_type_id: int | None = Field(default=None)
    threat_actor_id: int | None = Field(default=None)
    source_id: int | None = Field(default=None)
    uploaded_by: uuid.UUID | None = Field(default=None)


class Sample(SampleBase, table=True):
    __tablename__ = "samples"  # type: ignore

    sample_id: str = Field(primary_key=True)  # sha256_hash
    platform_id: int | None = Field(default=None)
    malware_family_id: int | None = Field(default=None)
    malware_type_id: int | None = Field(default=None)
    threat_actor_id: int | None = Field(default=None)
    source_id: int
    uploaded_by: uuid.UUID
    uploaded_at: datetime = Field(default_factory=datetime.now)

`sample_id` 採用 `sha256` 作為主鍵，能保證唯一性，避免重複儲存同一樣本。樣本的分析與描述資訊透過多個 `*_library` 表進行正規化，例如平台、惡意家族、類型、來源等。這樣的設計避免重複資料並提供良好的延展性。

### 2.樣本特徵

CREATE TABLE "features" (
    "feature_id" bigint NOT NULL,
    "sample_id" char(64) NOT NULL,
    "feature_type_id" bigint NOT NULL,
    "rule_name" text NOT NULL,
    "rule_content" text NOT NULL,
    "extra_data" jsonb,
    "created_by" uuid NOT NULL,
    "created_at" timestamp NOT NULL,
    PRIMARY KEY ("feature_id")
);

樣本與特徵都可以加上多個標籤（tags），使用多對多關聯表。

### 3. 使用者

CREATE TABLE "users" (
    "user_id" uuid NOT NULL,
    "name" text NOT NULL,
    "email" text NOT NULL UNIQUE,
    "password_hash" text NOT NULL,
    "created_at" timestamp NOT NULL,
    PRIMARY KEY ("user_id")
);

透過 `roles` 與 `user_roles` 建立基本的 RBAC 權限控制架構。