ERD
Tables (29)
arbitration
- Arbitration_ID — INT
- Case_ID — INT — Foreign key → case_info.Case_ID — NOT NULL
- Arbitration_Date — DATE — NOT NULL
- Deadline — DATE — NOT NULL
- Remarks — TEXT
- Created_At — TIMESTAMP — NOT NULL
- mediator_name — VARCHAR(255)
barangay_officials
- Official_ID — INT
- Name — VARCHAR(100)
- Position — VARCHAR(50)
- Contact_Number — VARCHAR(20)
- password — VARCHAR(255)
- official_username — VARCHAR(100)
- isActive — TINYINT
- email — VARCHAR(255)
blotter_info
- Blotter_ID — INT
- Case_ID — INT — Foreign key → case_info.Case_ID
- Blotter_Description — TEXT
- Reported_By — VARCHAR(100)
- Date_Reported — DATE
- Complaint_ID — INT
brgy_captain
- Captain_ID — INT
- Official_ID — INT — Foreign key → barangay_officials.Official_ID
brgy_secretary
- Secretary_ID — INT
- Official_ID — INT — Foreign key → barangay_officials.Official_ID
case_info
- Case_ID — INT
- case_original_id — VARCHAR(191)
- Complaint_ID — INT
- Case_Status — VARCHAR(50)
- Certificate_Path — VARCHAR(512)
- Date_Opened — DATETIME
- Date_Closed — DATETIME
- Next_Hearing_Date — DATE
- lupon_assign — VARCHAR(255)
- Case_Deadline — DATE
- Deadline_Overdue — TINYINT
- case_number — INT
- Dismiss_Reason — TEXT
case_participants
- Participant_ID — INT
- Case_ID — INT — Foreign key → case_info.Case_ID
- Resident_ID — INT
- Participant_Role — VARCHAR(50)
case_process
- Process_ID — INT
- Case_ID — INT
- Process_Details — TEXT
- Date_Processed — DATE
case_types
- Type_ID — INT
- Case_Type — VARCHAR(191) — NOT NULL
complaint_history
- History_ID — INT
- Complaint_ID — INT — NOT NULL
- Old_Title — VARCHAR(512)
- Old_Details — TEXT
- Edited_By — VARCHAR(191)
- Edited_At — DATETIME — NOT NULL
complaint_info
- Complaint_ID — INT
- Resident_ID — INT
- Complaint_Title — VARCHAR(255)
- Respondent_Name — VARCHAR(255)
- Complaint_Details — TEXT
- Date_Filed — DATE
- Status — VARCHAR(50)
- Respondent_ID — INT
- Complaint_Type — VARCHAR(100)
- Attachment_Path — VARCHAR(255)
- external_complainant_id — INT
- external_complainant_agree — TINYINT
- case_type — VARCHAR(255)
- complainant_agree — TINYINT
- respondent_agree — TINYINT
- Salaysay_Path — VARCHAR(1024)
complaint_info_history
- id — INT
- complaint_id — INT — Foreign key → complaint_info.Complaint_ID — NOT NULL
- version_number — INT — NOT NULL
- details — TEXT — NOT NULL
- updated_by — VARCHAR(100)
- updated_at — DATETIME
complaint_respondents
- id — INT
- complaint_id — INT — NOT NULL
- respondent_id — INT — NOT NULL
- respondent_agree — TINYINT — NOT NULL
conciliation
- Conciliation_ID — INT
- Case_ID — INT — NOT NULL
- Conciliation_Date — DATE — NOT NULL
- Deadline — DATE — NOT NULL
- Remarks — TEXT
- Created_At — DATETIME
- mediator_name — VARCHAR(255)
external_complainant
- External_Complaint_ID — INT
- First_Name — VARCHAR(50)
- Middle_Name — VARCHAR(50)
- Last_Name — VARCHAR(50)
- Address — VARCHAR(255)
- Contact_Number — VARCHAR(20)
- Birthdate — DATE
- isActive — TINYINT — NOT NULL
- email — VARCHAR(60) — NOT NULL
- password — VARCHAR(255) — NOT NULL
- External_Username — VARCHAR(20) — NOT NULL
feedback
- id — INT
- official_id — INT
- official_name — VARCHAR(255)
- message — TEXT — NOT NULL
- created_at — DATETIME
- case_id — INT
mediation_info
- Mediation_ID — INT
- Case_ID — INT
- Mediator_Name — VARCHAR(100)
- Mediation_Date — DATE
- Mediation_Result — TEXT
- Deadline — DATE
meeting_logs
- Log_ID — INT
- Case_ID — INT — NOT NULL
- Hearing_Date — DATE — NOT NULL
- Hearing_Time — TIME — NOT NULL
- Hearing_Details — TEXT — NOT NULL
- Created_At — TIMESTAMP — NOT NULL
- Hearing_End_Time — TIME
- Complainant_Status — VARCHAR(50)
- Respondent_Status — VARCHAR(50)
- Attendance — TEXT
- Reason_Incompliance — TEXT
notifications
- notification_id — INT
- resident_id — INT
- type — VARCHAR(50)
- title — VARCHAR(255)
- message — TEXT
- created_at — DATETIME
- is_read — TINYINT
- related_id — INT
- external_complaint_id — INT
- official_id — INT
- isPriority — TINYINT
- lupon_id — INT
- is_read_captain — TINYINT
- is_read_secretary — TINYINT
notifications_delivered
- id — INT
- notification_id — INT — NOT NULL
- resident_id — INT
- official_id — INT
- lupon_id — INT
- external_complaint_id — INT
- delivered_at — DATETIME — NOT NULL
official_profile
- Official_ID — INT
- address — VARCHAR(255)
official_schedule
- Official_ID — INT
- duty_days — VARCHAR(100)
- time_in — VARCHAR(10)
- time_out — VARCHAR(10)
password_resets
- id — INT
- email — VARCHAR(255) — NOT NULL
- otp_hash — VARCHAR(255) — NOT NULL
- expires_at — DATETIME — NOT NULL
- used — TINYINT — NOT NULL
- created_at — TIMESTAMP — NOT NULL
peace_keeper
- PeaceKeeper_ID — INT
- Official_ID — INT
resident_info
- Resident_ID — INT
- First_Name — VARCHAR(50)
- Middle_Name — VARCHAR(50)
- Last_Name — VARCHAR(50)
- Address — VARCHAR(255)
- Contact_Number — VARCHAR(20)
- Birthdate — DATE
- Resident_Username — VARCHAR(20) — NOT NULL
- Password — VARCHAR(225) — NOT NULL
- isVerify — TINYINT — NOT NULL
- email — VARCHAR(255)
- valid_id_path — VARCHAR(255) — NOT NULL
resolution
- Resolution_ID — INT
- Case_ID — INT
- Mediator_Name — VARCHAR(100)
- Resolution_Details — TEXT
- Resolution_Date — DATE
- Deadline — DATE
schedule_list
- hearingID — INT
- visible — INT — NOT NULL
- selectedCase — INT
- hearingTitle — VARCHAR(100) — NOT NULL
- hearingDateTime — DATETIME — NOT NULL
- place — VARCHAR(100) — NOT NULL
- participant — TEXT — NOT NULL
- remarks — TEXT
- Case_ID — INT — NOT NULL
settlement
- Settlement_ID — INT
- Case_ID — INT
- Mediator_Name — VARCHAR(100)
- Settlement_Details — TEXT
- Date_Agreed — DATE
- Deadline — DATE
site_settings
- setting_key — VARCHAR(100)
- setting_value — TEXT