749 lines
16 KiB
Markdown
749 lines
16 KiB
Markdown
# Proposed Database Design
|
|
|
|
## Phase 2 Implementation Status
|
|
|
|
Status: Implemented on 2026-05-28.
|
|
|
|
Implemented in migration:
|
|
|
|
- `user_profiles`
|
|
- `elections`
|
|
- `election_settings`
|
|
- `bahagian_pilihanrayas`
|
|
- `daerah_mengundis`
|
|
- `pusat_mengundis`
|
|
- `saluran_mengundis`
|
|
- `positions`
|
|
- `position_quotas`
|
|
- `applications`
|
|
- `application_documents`
|
|
- `application_status_histories`
|
|
- `staff_assignments`
|
|
- `staff_assignment_histories`
|
|
- `police_escorts`
|
|
- `kkm_representatives`
|
|
- `jkm_representatives`
|
|
- `wheelchair_allocations`
|
|
- `wheelchair_transactions`
|
|
- `attendances`
|
|
- `bank_verifications`
|
|
- `export_logs`
|
|
- `system_notes`
|
|
|
|
Implemented models and relationships are available under `app/Models`.
|
|
|
|
Implementation notes:
|
|
|
|
- Public-facing identifiers use UUID columns.
|
|
- MySQL index names are explicitly shortened where Laravel's generated names would exceed MySQL's identifier limit.
|
|
- Complex business constraints such as "four KP per KTM" are intentionally enforced by future services rather than only by database constraints.
|
|
- A PPM plus KTM dual-role assignment is represented through two active rows in `staff_assignments`.
|
|
|
|
## Export Log Purge Columns (Fasa 15 — 2026-06-02)
|
|
|
|
Added via migration `2026_06_02_000001_add_purged_at_to_export_logs.php`.
|
|
|
|
| Column | Type | Purpose |
|
|
|---|---|---|
|
|
| `purged_at` | nullable timestamp | Set when the export file is deleted by `exports:purge` command |
|
|
| `purged_by_user_id` | nullable FK → users | Reserved for future manual purge UI attribution |
|
|
|
|
Records with `purged_at IS NOT NULL` are skipped on subsequent purge runs.
|
|
|
|
## Performance Indexes (Fasa 13 — 2026-06-02)
|
|
|
|
Added via migration `2026_06_02_000000_add_performance_indexes.php`.
|
|
|
|
### `applications` table
|
|
|
|
| Index name | Columns | Purpose |
|
|
|---|---|---|
|
|
| `app_election_status_idx` | `(election_id, status)` | Dashboard count queries and admin list filtered by election |
|
|
| `app_ktm_position_idx` | `(selected_ktm_assignment_id, requested_position_id)` | `KtmVacancyService` reserved-application count (called per KP/KPDP vacancy check) |
|
|
|
|
### `staff_assignments` table
|
|
|
|
| Index name | Columns | Purpose |
|
|
|---|---|---|
|
|
| `sa_reports_to_pos_status_idx` | `(reports_to_assignment_id, position_id, status)` | `KtmVacancyService` active-assignment count under a KTM (called per vacancy check) |
|
|
|
|
### Existing indexes (Phase 2)
|
|
|
|
| Table | Columns | Name |
|
|
|---|---|---|
|
|
| `applications` | `(election_id, ic_number)` | auto |
|
|
| `applications` | `(election_id, pusat_mengundi_id, status)` | auto |
|
|
| `staff_assignments` | `(election_id, pusat_mengundi_id, position_id)` | `sa_election_pusat_position_idx` |
|
|
| `staff_assignments` | `(election_id, saluran_mengundi_id, position_id)` | `sa_election_saluran_position_idx` |
|
|
| `attendances` | `(election_id, pusat_mengundi_id, status)` | `att_election_pusat_status_idx` |
|
|
| `attendances` | `(election_id, staff_assignment_id)` | `att_election_staff_unique` (unique) |
|
|
|
|
### Why `bank_verifications` was not indexed with `election_id`
|
|
|
|
`bank_verifications` does not have a direct `election_id` column — it joins through `application_id` (unique FK). All finance queries that need election filtering use `whereHas('application', ...)` subqueries. The existing `status` index and unique `application_id` index are sufficient for the current query patterns.
|
|
|
|
## Design Goals
|
|
|
|
- Support one election or multiple election events over time.
|
|
- Keep public identifiers separate from internal database IDs.
|
|
- Support dual-role assignment, especially PPM also acting as KTM.
|
|
- Keep sensitive documents private and access-controlled.
|
|
- Preserve auditability through history tables and activity log.
|
|
- Support exports, finance verification, attendance, wheelchair tracking, and post-closing notes.
|
|
|
|
## Naming Conventions
|
|
|
|
- Table names use Laravel plural snake_case where practical.
|
|
- Existing Malay domain terms are retained for clarity.
|
|
- Public UUID/ULID columns use names such as `public_uuid`.
|
|
- Status fields use lowercase snake_case values.
|
|
- Foreign keys use `foreignId()->constrained()` where possible.
|
|
|
|
## Core Identity Tables
|
|
|
|
### users
|
|
|
|
Laravel default user authentication table with additions as needed.
|
|
|
|
Key fields:
|
|
|
|
- `id`
|
|
- `name`
|
|
- `email`
|
|
- `password`
|
|
- `email_verified_at`
|
|
- `remember_token`
|
|
- timestamps
|
|
- soft deletes if operationally useful
|
|
|
|
Notes:
|
|
|
|
- Roles are assigned through `spatie/laravel-permission`.
|
|
- Not every public applicant must become a login user unless later required.
|
|
|
|
### user_profiles
|
|
|
|
Stores extended identity details for staff/admin users.
|
|
|
|
Key fields:
|
|
|
|
- `id`
|
|
- `user_id`
|
|
- `ic_number`
|
|
- `phone_number`
|
|
- `address`
|
|
- `bank_name`
|
|
- `bank_account_number`
|
|
- timestamps
|
|
|
|
Notes:
|
|
|
|
- Sensitive fields should be masked in views where full values are not required.
|
|
- IC uniqueness at profile level should be evaluated with election-specific application rules.
|
|
|
|
## Election and Settings
|
|
|
|
### elections
|
|
|
|
Represents each election event.
|
|
|
|
Key fields:
|
|
|
|
- `id`
|
|
- `public_uuid`
|
|
- `name`
|
|
- `code`
|
|
- `status`
|
|
- `starts_at`
|
|
- `polling_date`
|
|
- timestamps
|
|
- soft deletes
|
|
|
|
Suggested statuses:
|
|
|
|
- `draft`
|
|
- `active`
|
|
- `closed`
|
|
- `archived`
|
|
|
|
### election_settings
|
|
|
|
Stores event-specific operational settings.
|
|
|
|
Key fields:
|
|
|
|
- `id`
|
|
- `election_id`
|
|
- `registration_start_date`
|
|
- `registration_end_date`
|
|
- `polling_date`
|
|
- `is_registration_open`
|
|
- `is_registration_open_override` nullable
|
|
- `is_attendance_active`
|
|
- timestamps
|
|
|
|
Notes:
|
|
|
|
- Registration open state is computed from dates and `is_registration_open`.
|
|
- `is_registration_open_override` can be used later if the system needs to distinguish manual override from normal date-based availability.
|
|
- Attendance can be toggled separately for rehearsal or polling day.
|
|
|
|
## Election Hierarchy
|
|
|
|
### bahagian_pilihanrayas
|
|
|
|
Key fields:
|
|
|
|
- `id`
|
|
- `election_id`
|
|
- `code`
|
|
- `name`
|
|
- timestamps
|
|
- soft deletes
|
|
|
|
Relationships:
|
|
|
|
- belongs to election.
|
|
- has many daerah mengundi.
|
|
|
|
### daerah_mengundis
|
|
|
|
Key fields:
|
|
|
|
- `id`
|
|
- `election_id`
|
|
- `bahagian_pilihanraya_id`
|
|
- `code`
|
|
- `name`
|
|
- timestamps
|
|
- soft deletes
|
|
|
|
Relationships:
|
|
|
|
- belongs to election.
|
|
- belongs to bahagian pilihanraya.
|
|
- has many pusat mengundi.
|
|
|
|
### pusat_mengundis
|
|
|
|
Key fields:
|
|
|
|
- `id`
|
|
- `election_id`
|
|
- `daerah_mengundi_id`
|
|
- `public_uuid`
|
|
- `code`
|
|
- `name`
|
|
- `address`
|
|
- `registration_url`
|
|
- `qr_code_path`
|
|
- timestamps
|
|
- soft deletes
|
|
|
|
Relationships:
|
|
|
|
- belongs to election.
|
|
- belongs to daerah mengundi.
|
|
- has many saluran mengundi.
|
|
- has many position quotas.
|
|
- has many staff assignments.
|
|
- has one wheelchair allocation.
|
|
|
|
Notes:
|
|
|
|
- `public_uuid` is used by `/pohon/{pusat_mengundi_uuid}`.
|
|
- `registration_url` can be generated dynamically; storing it is optional. If stored, it must be refreshed if domain changes.
|
|
|
|
### saluran_mengundis
|
|
|
|
Key fields:
|
|
|
|
- `id`
|
|
- `election_id`
|
|
- `pusat_mengundi_id`
|
|
- `number`
|
|
- `name`
|
|
- `voter_count`
|
|
- timestamps
|
|
- soft deletes
|
|
|
|
Relationships:
|
|
|
|
- belongs to election.
|
|
- belongs to pusat mengundi.
|
|
- has many staff assignments.
|
|
|
|
## Roles and Permissions
|
|
|
|
Managed by `spatie/laravel-permission`:
|
|
|
|
- `roles`
|
|
- `permissions`
|
|
- `model_has_roles`
|
|
- `model_has_permissions`
|
|
- `role_has_permissions`
|
|
|
|
Application roles:
|
|
|
|
- Admin
|
|
- Admin Kewangan
|
|
- PPM
|
|
- KTM
|
|
- Pemohon
|
|
|
|
## Positions and Quotas
|
|
|
|
### positions
|
|
|
|
Stores operational staff positions.
|
|
|
|
Key fields:
|
|
|
|
- `id`
|
|
- `code`
|
|
- `name`
|
|
- `scope`
|
|
- `is_public_applyable`
|
|
- `is_assignable`
|
|
- `allows_dual_role`
|
|
- timestamps
|
|
- soft deletes
|
|
|
|
Suggested position codes:
|
|
|
|
- `PPM`
|
|
- `KTM`
|
|
- `KP`
|
|
- `KPDP`
|
|
- `PAPM`
|
|
- `POLIS`
|
|
- `KKM`
|
|
- `JKM`
|
|
- `CALON_SIMPANAN`
|
|
|
|
Suggested scopes:
|
|
|
|
- `pusat`
|
|
- `saluran`
|
|
- `external`
|
|
|
|
### position_quotas
|
|
|
|
Defines vacancy requirements by election, pusat, saluran, and role.
|
|
|
|
Key fields:
|
|
|
|
- `id`
|
|
- `election_id`
|
|
- `pusat_mengundi_id`
|
|
- `saluran_mengundi_id` nullable
|
|
- `position_id`
|
|
- `quota`
|
|
- timestamps
|
|
- soft deletes
|
|
|
|
Notes:
|
|
|
|
- PPM, PAPM, KKM, JKM, and wheelchair-related staffing are usually Pusat scope.
|
|
- KTM, KP, KPDP, Police escort, and calon simpanan are usually Saluran scope.
|
|
- Use nullable `saluran_mengundi_id` for Pusat-level quota.
|
|
|
|
## Applications
|
|
|
|
### applications
|
|
|
|
Represents public, KTM-created, or Admin-keyed applicant/staff application record.
|
|
|
|
Key fields:
|
|
|
|
- `id`
|
|
- `election_id`
|
|
- `pusat_mengundi_id`
|
|
- `selected_ktm_assignment_id` nullable
|
|
- `user_id` nullable
|
|
- `public_uuid`
|
|
- `source`
|
|
- `status`
|
|
- `name`
|
|
- `ic_number`
|
|
- `phone_number`
|
|
- `email`
|
|
- `address`
|
|
- `requested_position_id`
|
|
- `approved_position_id` nullable
|
|
- `bank_name`
|
|
- `bank_account_number`
|
|
- `created_by_user_id` nullable
|
|
- `approved_by_user_id` nullable
|
|
- `approved_at` nullable
|
|
- `rejected_by_user_id` nullable
|
|
- `rejected_at` nullable
|
|
- `rejection_reason` nullable
|
|
- timestamps
|
|
- soft deletes
|
|
|
|
Suggested source values:
|
|
|
|
- `public`
|
|
- `created_by_ktm`
|
|
- `admin_manual`
|
|
|
|
Suggested status values are documented in `docs/05-workflow-design.md`.
|
|
|
|
Rules:
|
|
|
|
- IC uniqueness is enforced per election among active/non-cancelled records.
|
|
- Public self-registration is blocked if an active `created_by_ktm` record exists for the same election and IC.
|
|
- Admin may manually enter records after registration closes with required note.
|
|
- Phase 4 public submissions create `source = public`, `status = submitted`, and a public UUID reference.
|
|
- For KP and KPDP public applications, `selected_ktm_assignment_id` stores the selected active KTM assignment if vacancy remains.
|
|
|
|
### application_documents
|
|
|
|
Stores private uploaded documents.
|
|
|
|
Key fields:
|
|
|
|
- `id`
|
|
- `application_id`
|
|
- `document_type`
|
|
- `disk`
|
|
- `path`
|
|
- `original_name`
|
|
- `mime_type`
|
|
- `size`
|
|
- `uploaded_by_user_id` nullable
|
|
- timestamps
|
|
- soft deletes
|
|
|
|
Document types:
|
|
|
|
- `ic_document`
|
|
- `bank_statement`
|
|
- `other`
|
|
|
|
Rules:
|
|
|
|
- IC and bank statement documents are required before PPM approval.
|
|
- Documents are stored on private disk.
|
|
- Phase 4 public uploads store `ic_document` and `bank_statement` under private local storage using the application public UUID path.
|
|
|
|
### application_status_histories
|
|
|
|
Tracks application lifecycle status changes.
|
|
|
|
Key fields:
|
|
|
|
- `id`
|
|
- `application_id`
|
|
- `from_status` nullable
|
|
- `to_status`
|
|
- `changed_by_user_id` nullable
|
|
- `note` nullable
|
|
- `metadata` JSON nullable
|
|
- timestamps
|
|
|
|
## Staff Assignments
|
|
|
|
### staff_assignments
|
|
|
|
Canonical table for approved staff placement.
|
|
|
|
Key fields:
|
|
|
|
- `id`
|
|
- `election_id`
|
|
- `application_id` nullable
|
|
- `user_id` nullable
|
|
- `position_id`
|
|
- `pusat_mengundi_id`
|
|
- `saluran_mengundi_id` nullable
|
|
- `reports_to_assignment_id` nullable
|
|
- `status`
|
|
- `assigned_by_user_id`
|
|
- `assigned_at`
|
|
- `source`
|
|
- timestamps
|
|
- soft deletes
|
|
|
|
Suggested statuses:
|
|
|
|
- `active`
|
|
- `pending`
|
|
- `cancelled`
|
|
- `replaced`
|
|
|
|
Notes:
|
|
|
|
- Allows one person to hold multiple assignments, including PPM and KTM.
|
|
- `reports_to_assignment_id` can link KP/KPDP to the approved KTM assignment.
|
|
- A KTM assignment to Saluran is created by PPM or Admin, not chosen by KTM.
|
|
- Unique constraints should prevent duplicate active position assignment to the same role/scope where business rules require.
|
|
|
|
### staff_assignment_histories
|
|
|
|
Tracks assignment changes.
|
|
|
|
Key fields:
|
|
|
|
- `id`
|
|
- `staff_assignment_id`
|
|
- `from_position_id` nullable
|
|
- `to_position_id` nullable
|
|
- `from_pusat_mengundi_id` nullable
|
|
- `to_pusat_mengundi_id` nullable
|
|
- `from_saluran_mengundi_id` nullable
|
|
- `to_saluran_mengundi_id` nullable
|
|
- `from_reports_to_assignment_id` nullable
|
|
- `to_reports_to_assignment_id` nullable
|
|
- `changed_by_user_id`
|
|
- `note` nullable
|
|
- timestamps
|
|
|
|
## Representatives and External Operational Data
|
|
|
|
### police_escorts
|
|
|
|
Key fields:
|
|
|
|
- `id`
|
|
- `election_id`
|
|
- `pusat_mengundi_id`
|
|
- `saluran_mengundi_id` nullable
|
|
- `name`
|
|
- `ic_number` nullable
|
|
- `phone_number` nullable
|
|
- `rank` nullable
|
|
- `station` nullable
|
|
- `notes` nullable
|
|
- timestamps
|
|
- soft deletes
|
|
|
|
### kkm_representatives
|
|
|
|
Key fields:
|
|
|
|
- `id`
|
|
- `election_id`
|
|
- `pusat_mengundi_id`
|
|
- `name`
|
|
- `ic_number` nullable
|
|
- `phone_number` nullable
|
|
- `agency` nullable
|
|
- `notes` nullable
|
|
- timestamps
|
|
- soft deletes
|
|
|
|
### jkm_representatives
|
|
|
|
Key fields:
|
|
|
|
- `id`
|
|
- `election_id`
|
|
- `pusat_mengundi_id`
|
|
- `name`
|
|
- `ic_number` nullable
|
|
- `phone_number` nullable
|
|
- `agency` nullable
|
|
- `notes` nullable
|
|
- timestamps
|
|
- soft deletes
|
|
|
|
## Wheelchair Management
|
|
|
|
### wheelchair_allocations
|
|
|
|
Key fields:
|
|
|
|
- `id`
|
|
- `election_id`
|
|
- `pusat_mengundi_id`
|
|
- `allocated_quantity`
|
|
- `notes` nullable
|
|
- timestamps
|
|
|
|
### wheelchair_transactions
|
|
|
|
Key fields:
|
|
|
|
- `id`
|
|
- `wheelchair_allocation_id`
|
|
- `transaction_type`
|
|
- `quantity`
|
|
- `taken_at` nullable
|
|
- `taken_by_name` nullable
|
|
- `returned_at` nullable
|
|
- `return_condition` nullable
|
|
- `recorded_by_user_id`
|
|
- `notes` nullable
|
|
- timestamps
|
|
|
|
Suggested transaction types:
|
|
|
|
- `taken`
|
|
- `returned`
|
|
- `adjustment`
|
|
|
|
Return condition values:
|
|
|
|
- `baik`
|
|
- `rosak`
|
|
|
|
## Attendance
|
|
|
|
### attendances
|
|
|
|
Key fields:
|
|
|
|
- `id`
|
|
- `election_id`
|
|
- `staff_assignment_id`
|
|
- `pusat_mengundi_id`
|
|
- `saluran_mengundi_id` nullable
|
|
- `position_id`
|
|
- `status`
|
|
- `check_in_time` nullable
|
|
- `recorded_by_user_id`
|
|
- `note` nullable
|
|
- timestamps
|
|
|
|
Attendance statuses:
|
|
|
|
- `present`
|
|
- `absent`
|
|
- `not_recorded`
|
|
|
|
Notes:
|
|
|
|
- Store denormalized pusat/saluran/position references for simpler reporting.
|
|
- A unique key should prevent multiple active attendance rows for the same election and staff assignment.
|
|
|
|
## Finance
|
|
|
|
### bank_verifications
|
|
|
|
Key fields:
|
|
|
|
- `id`
|
|
- `application_id`
|
|
- `status`
|
|
- `verified_by_user_id` nullable
|
|
- `verified_at` nullable
|
|
- `finance_note` nullable
|
|
- timestamps
|
|
|
|
Statuses:
|
|
|
|
- `pending`
|
|
- `verified`
|
|
- `rejected`
|
|
- `requires_correction`
|
|
|
|
Notes:
|
|
|
|
- Admin Kewangan can update this table only.
|
|
- Assignment and placement edits are outside Admin Kewangan scope.
|
|
- Phase 4 public submissions create an initial `pending` bank verification row.
|
|
|
|
## Exports and Notes
|
|
|
|
### export_logs
|
|
|
|
Key fields:
|
|
|
|
- `id`
|
|
- `election_id` nullable
|
|
- `generated_by_user_id`
|
|
- `generated_at`
|
|
- `report_type`
|
|
- `filter_parameters` JSON nullable
|
|
- `file_name`
|
|
- `disk` nullable
|
|
- `path` nullable
|
|
- timestamps
|
|
|
|
Report types:
|
|
|
|
- `full_staff_list`
|
|
- `staff_list_by_pusat`
|
|
- `staff_list_by_role`
|
|
- `missing_document_list`
|
|
- `finance_verification_list`
|
|
- `attendance_summary`
|
|
- `attendance_detail_by_pusat`
|
|
|
|
### system_notes
|
|
|
|
Stores structured operational notes, especially post-registration changes.
|
|
|
|
Key fields:
|
|
|
|
- `id`
|
|
- `election_id`
|
|
- `noteable_type`
|
|
- `noteable_id`
|
|
- `note_type`
|
|
- `note`
|
|
- `created_by_user_id`
|
|
- timestamps
|
|
|
|
Suggested note types:
|
|
|
|
- `post_registration_change`
|
|
- `admin_correction`
|
|
- `finance_note`
|
|
- `general`
|
|
|
|
## Audit Log
|
|
|
|
### activity_log
|
|
|
|
Managed by `spatie/laravel-activitylog`.
|
|
|
|
Required logged details:
|
|
|
|
- actor
|
|
- subject
|
|
- event name
|
|
- previous values
|
|
- new values
|
|
- timestamp
|
|
- note or reason where available
|
|
|
|
## Key Constraints and Indexes
|
|
|
|
Planned indexes:
|
|
|
|
- `elections.public_uuid`
|
|
- `pusat_mengundis.public_uuid`
|
|
- `applications.public_uuid`
|
|
- `applications.election_id, ic_number`
|
|
- `applications.election_id, pusat_mengundi_id, status`
|
|
- `staff_assignments.election_id, pusat_mengundi_id, position_id`
|
|
- `staff_assignments.election_id, saluran_mengundi_id, position_id`
|
|
- `bank_verifications.status`
|
|
- `attendances.election_id, pusat_mengundi_id, status`
|
|
- `export_logs.generated_by_user_id, generated_at`
|
|
|
|
Planned uniqueness:
|
|
|
|
- Unique active public UUIDs.
|
|
- One active PPM per Pusat Mengundi.
|
|
- One active KTM per Saluran Mengundi.
|
|
- One active KPDP per KTM or Saluran based on final rule implementation.
|
|
- Four active KP per KTM/team enforced by quota service, not only DB constraint.
|
|
- Application IC uniqueness per election for active public/admin records, with special handling for deleted/cancelled KTM-created records.
|
|
|
|
## Documented Trade-Offs
|
|
|
|
- `staff_assignments` is used instead of a single role column because the system must support dual-role assignment.
|
|
- Domain-specific history tables are retained in addition to generic activity log because workflows need reportable status/assignment timelines.
|
|
- Representative tables are separate from applications because Police, KKM, and JKM records may not follow the public applicant workflow.
|
|
- Attendance denormalizes role and location references to make election-day reporting faster and more stable.
|
|
## Kemas kini operasi Pusat Mengundi - 2026-05-31
|
|
|
|
- `saluran_mengundis.name` dikekalkan sebagai nullable untuk keserasian rekod lama, tetapi tidak lagi diminta pada UI.
|
|
- Semasa Pusat Mengundi baharu didaftarkan, Saluran bernombor `1..N` dijana secara automatik.
|
|
- `position_quotas` menyimpan `PPM`, `PAPM`, `JKM` dan `KKM` pada skop Pusat serta `KTM`, `POLIS`, `KP`, `KPDP` dan `CALON_TAMBAHAN` pada skop Saluran.
|
|
- Kod jawatan `CALON_SIMPANAN` dimigrasikan kepada `CALON_TAMBAHAN`.
|