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