speech2text/DATABASE_DESIGN.md

DATABASE_DESIGN.md — Speech2Text MBIP

Senarai Jadual

1. users
2. departments
3. transcription_projects
4. project_collaborators
5. transcript_versions
6. project_comments
7. audit_logs
8. sessions
9. jobs / failed_jobs
10. cache

---

Skema Jadual

1. users

CREATE TABLE users (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(255) NOT NULL,
    email           VARCHAR(255) NOT NULL UNIQUE,
    password        VARCHAR(255) NOT NULL,
    role            ENUM('admin', 'user') NOT NULL DEFAULT 'user',
    department_id   BIGINT UNSIGNED NULL,
    is_active       TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at   TIMESTAMP NULL,
    email_verified_at TIMESTAMP NULL,
    remember_token  VARCHAR(100) NULL,
    created_at      TIMESTAMP NULL,
    updated_at      TIMESTAMP NULL,
    deleted_at      TIMESTAMP NULL,  -- soft delete

    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL
);

Catatan:

• deleted_at hanya digunakan jika pengguna belum pernah guna aplikasi (tiada projek, tiada audit).
• Jika pernah guna, hanya is_active = 0 (deactivate), jangan hard delete.
• role enum mudah diurus; boleh upgrade ke spatie/laravel-permission kemudian jika perlu.

---

2. departments

CREATE TABLE departments (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    code        VARCHAR(50) NULL UNIQUE,
    is_active   TINYINT(1) NOT NULL DEFAULT 1,
    created_at  TIMESTAMP NULL,
    updated_at  TIMESTAMP NULL
);

---

3. transcription_projects

CREATE TABLE transcription_projects (
    id                      BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid                    CHAR(36) NOT NULL UNIQUE,   -- digunakan dalam URL
    title                   VARCHAR(255) NOT NULL,
    description             TEXT NULL,
    owner_user_id           BIGINT UNSIGNED NOT NULL,
    original_filename       VARCHAR(500) NOT NULL,
    stored_audio_path       VARCHAR(1000) NOT NULL,     -- path relatif dalam private disk
    mime_type               VARCHAR(100) NOT NULL,
    file_size               BIGINT UNSIGNED NOT NULL,   -- bytes
    duration_seconds        INT UNSIGNED NULL,
    language                VARCHAR(10) NOT NULL DEFAULT 'ms',
    transcription_status    ENUM('pending','processing','completed','failed') NOT NULL DEFAULT 'pending',
    transcription_engine    VARCHAR(50) NULL,           -- e.g. 'faster-whisper'
    transcript_text         LONGTEXT NULL,              -- kandungan sensitif
    transcript_confidence   DECIMAL(5,4) NULL,          -- 0.0000 - 1.0000
    error_message           TEXT NULL,
    processed_at            TIMESTAMP NULL,
    created_at              TIMESTAMP NULL,
    updated_at              TIMESTAMP NULL,
    deleted_at              TIMESTAMP NULL,             -- soft delete

    FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE RESTRICT
);

Catatan keselamatan:

• stored_audio_path adalah path dalam private storage, bukan URL awam.
• transcript_text disimpan dalam database. Untuk keselamatan lanjut, boleh encrypt menggunakan Laravel encrypted cast.
• Admin tidak boleh SELECT transcript_text, stored_audio_path melalui policy/query scope.

---

4. project_collaborators

CREATE TABLE project_collaborators (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id  BIGINT UNSIGNED NOT NULL,
    user_id     BIGINT UNSIGNED NOT NULL,
    role        ENUM('editor', 'viewer') NOT NULL DEFAULT 'editor',
    added_by    BIGINT UNSIGNED NOT NULL,
    created_at  TIMESTAMP NULL,
    updated_at  TIMESTAMP NULL,

    UNIQUE KEY unique_project_user (project_id, user_id),
    FOREIGN KEY (project_id) REFERENCES transcription_projects(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (added_by) REFERENCES users(id) ON DELETE RESTRICT
);

---

5. transcript_versions

CREATE TABLE transcript_versions (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id      BIGINT UNSIGNED NOT NULL,
    edited_by       BIGINT UNSIGNED NOT NULL,
    version_number  INT UNSIGNED NOT NULL,
    old_text        LONGTEXT NULL,
    new_text        LONGTEXT NOT NULL,
    change_summary  VARCHAR(500) NULL,
    created_at      TIMESTAMP NULL,

    FOREIGN KEY (project_id) REFERENCES transcription_projects(id) ON DELETE CASCADE,
    FOREIGN KEY (edited_by) REFERENCES users(id) ON DELETE RESTRICT
);

Catatan:

• old_text dan new_text adalah snapshot penuh, bukan diff, untuk kemudahan restore.
• Jangan masukkan transcript_text dalam audit_logs; gunakan jadual ini sebagai ganti.
• Admin tidak boleh akses jadual ini.

---

6. project_comments

CREATE TABLE project_comments (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id  BIGINT UNSIGNED NOT NULL,
    user_id     BIGINT UNSIGNED NOT NULL,
    message     TEXT NOT NULL,
    created_at  TIMESTAMP NULL,
    updated_at  TIMESTAMP NULL,
    deleted_at  TIMESTAMP NULL,  -- soft delete

    FOREIGN KEY (project_id) REFERENCES transcription_projects(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
);

---

7. audit_logs

CREATE TABLE audit_logs (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    actor_user_id   BIGINT UNSIGNED NULL,        -- NULL jika sistem/background job
    actor_role      VARCHAR(50) NULL,
    action          VARCHAR(100) NOT NULL,        -- e.g. 'user_deactivated'
    subject_type    VARCHAR(100) NULL,            -- e.g. 'App\Models\User'
    subject_id      BIGINT UNSIGNED NULL,
    target_user_id  BIGINT UNSIGNED NULL,
    project_id      BIGINT UNSIGNED NULL,
    old_values      JSON NULL,                   -- JANGAN masukkan transcript content
    new_values      JSON NULL,                   -- JANGAN masukkan transcript content
    justification   TEXT NULL,
    ip_address      VARCHAR(45) NULL,            -- support IPv6
    user_agent      TEXT NULL,
    created_at      TIMESTAMP NULL,

    INDEX idx_actor (actor_user_id),
    INDEX idx_action (action),
    INDEX idx_project (project_id),
    INDEX idx_created (created_at)
);

Tindakan yang diaudit:

action	Penerangan
user_created	Admin daftar pengguna baru
user_deactivated	Admin deactivate pengguna
user_reactivated	Admin aktifkan semula pengguna
user_deleted	Admin delete pengguna (hanya jika belum guna)
user_email_changed	Admin tukar emel pengguna
project_created	Pengguna cipta projek
audio_uploaded	Pengguna muat naik audio
transcription_started	Queue worker mula proses
transcription_completed	Queue worker selesai
transcription_failed	Queue worker gagal
transcript_updated	Owner/collaborator edit teks
transcript_version_restored	Restore versi lama
collaborator_added	Owner tambah collaborator
collaborator_removed	Owner buang collaborator
comment_created	Pengguna buat komen
project_deleted	Owner delete projek
project_owner_transferred	Admin transfer ownership

---

Hubungan Model (Eloquent Relationships)

User
  ├── hasMany: TranscriptionProject (as owner)
  ├── belongsToMany: TranscriptionProject (through ProjectCollaborator)
  ├── hasMany: TranscriptVersion (as editor)
  ├── hasMany: ProjectComment
  ├── belongsTo: Department
  └── hasMany: AuditLog (as actor)

TranscriptionProject
  ├── belongsTo: User (owner)
  ├── hasMany: ProjectCollaborator
  ├── hasMany: TranscriptVersion
  ├── hasMany: ProjectComment
  └── belongsToMany: User (collaborators)

Department
  └── hasMany: User

---

Indeks Penting

-- Cari projek mengikut status (untuk admin dashboard)
ALTER TABLE transcription_projects ADD INDEX idx_status (transcription_status);

-- Cari projek mengikut owner
ALTER TABLE transcription_projects ADD INDEX idx_owner (owner_user_id);

-- Cari versi mengikut projek (timeline)
ALTER TABLE transcript_versions ADD INDEX idx_project_version (project_id, version_number);

-- Audit log search
ALTER TABLE audit_logs ADD INDEX idx_target_user (target_user_id);
ALTER TABLE audit_logs ADD INDEX idx_subject (subject_type, subject_id);

---

Nota Keselamatan Data

1. transcript_text — Kolum sensitif. Boleh encrypt menggunakan Laravel cast encrypted:

   protected $casts = [
       'transcript_text' => 'encrypted',
   ];
   

   Ini encrypt menggunakan APP_KEY. Pastikan APP_KEY disimpan dengan selamat.

2. stored_audio_path — Simpan path relatif sahaja, bukan absolute path. Contoh: transcriptions/abc-uuid/audio/recording.mp3.

3. Audit log — Jangan masukkan transcript_text dalam old_values atau new_values. Gunakan transcript_versions untuk simpan snapshot teks.

4. Soft delete — transcription_projects dan project_comments menggunakan soft delete. Fail audio fizikal dikekalkan dalam private storage sehingga admin jalankan retention cleanup.