Loan Management

Incoming/outgoing loan tracking with item-level condition checks and insurance.

ahgLoanPlugin
All Schemas
3
ahg_loan
0
ahg_loan_condition_image
0
ahg_loan_condition_report
0
ahg_loan_cost
10
ahg_loan_courier
0
ahg_loan_document
0
ahg_loan_extension
0
ahg_loan_facility_image
0
ahg_loan_facility_report
2
ahg_loan_history
0
ahg_loan_notification_log
5
ahg_loan_notification_template
1
ahg_loan_object
0
ahg_loan_shipment
0
ahg_loan_shipment_event
0
ahg_loan_status_history
0
loan
0
loan_document
0
loan_extension
0
loan_object
Entity Relationship Diagram
Open Full Size
Loan Management ERD
Foreign Key Relationships
Constraint Table Column References Referenced Column
ahg_loan_condition_image_ibfk_1 ahg_loan_condition_image condition_report_id ahg_loan_condition_report id
ahg_loan_condition_report_ibfk_1 ahg_loan_condition_report loan_id ahg_loan id
ahg_loan_condition_report_ibfk_2 ahg_loan_condition_report loan_object_id ahg_loan_object id
ahg_loan_cost_ibfk_1 ahg_loan_cost loan_id ahg_loan id
ahg_loan_document_ibfk_1 ahg_loan_document loan_id ahg_loan id
ahg_loan_extension_ibfk_1 ahg_loan_extension loan_id ahg_loan id
ahg_loan_facility_image_ibfk_1 ahg_loan_facility_image facility_report_id ahg_loan_facility_report id
ahg_loan_facility_report_ibfk_1 ahg_loan_facility_report loan_id ahg_loan id
ahg_loan_history_ibfk_1 ahg_loan_history loan_id ahg_loan id
ahg_loan_notification_log_ibfk_1 ahg_loan_notification_log loan_id ahg_loan id
ahg_loan_notification_log_ibfk_2 ahg_loan_notification_log template_id ahg_loan_notification_template id
ahg_loan_object_ibfk_1 ahg_loan_object loan_id ahg_loan id
ahg_loan_shipment_ibfk_2 ahg_loan_shipment courier_id ahg_loan_courier id
ahg_loan_shipment_ibfk_1 ahg_loan_shipment loan_id ahg_loan id
ahg_loan_shipment_event_ibfk_1 ahg_loan_shipment_event shipment_id ahg_loan_shipment id
ahg_loan_status_history_ibfk_1 ahg_loan_status_history loan_id ahg_loan id
loan_document_loan_id_foreign loan_document loan_id loan id
loan_extension_loan_id_foreign loan_extension loan_id loan id
loan_object_loan_id_foreign loan_object loan_id loan id
ahg_loan
34 columns 3 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
UQ loan_number varchar(50) NOT NULL -
FK loan_type varchar(20) NOT NULL - out, in
FK sector varchar(44) NOT NULL museum museum, gallery, archive, library, dam
title varchar(500) NULL -
description text NULL -
purpose varchar(100) NULL exhibition
FK partner_institution varchar(500) NOT NULL -
partner_contact_name varchar(255) NULL -
partner_contact_email varchar(255) NULL -
partner_contact_phone varchar(100) NULL -
partner_address text NULL -
request_date datetime NULL -
FK start_date date NULL -
end_date date NULL -
FK return_date date NULL -
insurance_type varchar(53) NULL borrower borrower, lender, shared, government, self, none
insurance_value decimal(15,2) NULL -
insurance_currency varchar(3) NULL ZAR
insurance_policy_number varchar(100) NULL -
insurance_provider varchar(255) NULL -
loan_fee decimal(12,2) NULL -
loan_fee_currency varchar(3) NULL ZAR
FK status varchar(50) NULL draft
internal_approver_id int NULL -
approved_date datetime NULL -
exhibition_id bigint unsigned NULL -
repository_id int NULL -
sector_data json NULL -
notes text NULL -
created_by int NULL -
updated_by int NULL -
created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
updated_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED on update CURRENT_TIMESTAMP
ahg_loan_condition_image
11 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK condition_report_id bigint unsigned NOT NULL -
file_path varchar(500) NOT NULL -
file_name varchar(255) NULL -
mime_type varchar(100) NULL -
image_type varchar(50) NULL overall
caption text NULL -
annotation_data json NULL -
view_position varchar(50) NULL front
sort_order int NULL 0
created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
ahg_loan_condition_report
36 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK loan_id bigint unsigned NOT NULL -
FK loan_object_id bigint unsigned NULL -
information_object_id int NULL -
FK report_type varchar(48) NOT NULL pre_loan pre_loan, post_loan, in_transit, periodic
examination_date datetime NOT NULL -
examiner_id int NULL -
examiner_name varchar(255) NULL -
location varchar(255) NULL -
overall_condition varchar(43) NOT NULL good excellent, good, fair, poor, critical
condition_stable tinyint(1) NULL 1
structural_condition text NULL -
surface_condition text NULL -
has_damage tinyint(1) NULL 0
damage_description text NULL -
has_previous_repairs tinyint(1) NULL 0
repair_description text NULL -
has_active_deterioration tinyint(1) NULL 0
deterioration_description text NULL -
height_cm decimal(10,2) NULL -
width_cm decimal(10,2) NULL -
depth_cm decimal(10,2) NULL -
weight_kg decimal(10,2) NULL -
handling_requirements text NULL -
mounting_requirements text NULL -
environmental_requirements text NULL -
treatment_recommendations text NULL -
display_recommendations text NULL -
signed_by_lender int NULL -
signed_by_borrower int NULL -
lender_signature_date datetime NULL -
borrower_signature_date datetime NULL -
pdf_generated tinyint(1) NULL 0
pdf_path varchar(500) NULL -
created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
updated_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED on update CURRENT_TIMESTAMP
ahg_loan_cost
16 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK loan_id bigint unsigned NOT NULL -
FK cost_type varchar(50) NOT NULL -
description varchar(500) NULL -
amount decimal(12,2) NOT NULL -
currency varchar(3) NULL ZAR
vendor varchar(255) NULL -
invoice_number varchar(100) NULL -
invoice_date date NULL -
paid tinyint(1) NULL 0
paid_date date NULL -
paid_by varchar(32) NULL borrower lender, borrower, shared
notes text NULL -
created_by int NULL -
created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
updated_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED on update CURRENT_TIMESTAMP
ahg_loan_courier
17 columns 10 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
company_name varchar(255) NOT NULL -
contact_name varchar(255) NULL -
contact_email varchar(255) NULL -
contact_phone varchar(100) NULL -
address text NULL -
website varchar(255) NULL -
is_art_specialist tinyint(1) NULL 0
has_climate_control tinyint(1) NULL 0
has_gps_tracking tinyint(1) NULL 0
insurance_coverage decimal(15,2) NULL -
insurance_currency varchar(3) NULL ZAR
quality_rating decimal(3,2) NULL -
notes text NULL -
FK is_active tinyint(1) NULL 1
created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
updated_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED on update CURRENT_TIMESTAMP
ahg_loan_document
10 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK loan_id bigint unsigned NOT NULL -
FK document_type varchar(50) NOT NULL -
file_path varchar(500) NULL -
file_name varchar(255) NULL -
mime_type varchar(100) NULL -
file_size bigint NULL -
description text NULL -
uploaded_by int NULL -
created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
ahg_loan_extension
7 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK loan_id bigint unsigned NOT NULL -
previous_end_date date NOT NULL -
new_end_date date NOT NULL -
reason text NULL -
approved_by int NULL -
created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
ahg_loan_facility_image
8 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK facility_report_id bigint unsigned NOT NULL -
file_path varchar(500) NOT NULL -
file_name varchar(255) NULL -
mime_type varchar(100) NULL -
caption text NULL -
image_type varchar(50) NULL other
created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
ahg_loan_facility_report
37 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK loan_id bigint unsigned NOT NULL -
venue_name varchar(255) NOT NULL -
venue_address text NULL -
venue_contact_name varchar(255) NULL -
venue_contact_email varchar(255) NULL -
venue_contact_phone varchar(100) NULL -
assessment_date date NULL -
assessed_by int NULL -
has_climate_control tinyint(1) NULL 0
temperature_min decimal(5,2) NULL -
temperature_max decimal(5,2) NULL -
humidity_min decimal(5,2) NULL -
humidity_max decimal(5,2) NULL -
has_uv_filtering tinyint(1) NULL 0
light_levels_lux int NULL -
has_24hr_security tinyint(1) NULL 0
has_cctv tinyint(1) NULL 0
has_alarm_system tinyint(1) NULL 0
has_fire_suppression tinyint(1) NULL 0
fire_suppression_type varchar(100) NULL -
security_notes text NULL -
display_case_type varchar(100) NULL -
mounting_method varchar(100) NULL -
barrier_distance decimal(5,2) NULL -
storage_type varchar(100) NULL -
public_access_hours text NULL -
staff_supervision tinyint(1) NULL 0
photography_allowed tinyint(1) NULL 1
overall_rating varchar(57) NULL acceptable excellent, good, acceptable, marginal, unacceptable
recommendations text NULL -
conditions_required text NULL -
approved tinyint(1) NULL 0
approved_by int NULL -
approved_date datetime NULL -
created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
updated_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED on update CURRENT_TIMESTAMP
ahg_loan_history
6 columns 2 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK loan_id bigint unsigned NOT NULL -
FK action varchar(100) NOT NULL -
details json NULL -
user_id int NULL -
created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
ahg_loan_notification_log
12 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK loan_id bigint unsigned NOT NULL -
FK template_id bigint unsigned NULL -
notification_type varchar(100) NULL -
recipient_email varchar(255) NULL -
recipient_name varchar(255) NULL -
subject varchar(500) NULL -
body text NULL -
FK status varchar(37) NULL pending pending, sent, failed, bounced
sent_at datetime NULL -
error_message text NULL -
created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
ahg_loan_notification_template
12 columns 5 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
UQ code varchar(100) NOT NULL -
name varchar(255) NOT NULL -
description text NULL -
FK sector varchar(50) NULL -
subject_template varchar(500) NULL -
body_template text NULL -
trigger_event varchar(100) NULL -
trigger_days_before int NULL -
is_active tinyint(1) NULL 1
created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
updated_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED on update CURRENT_TIMESTAMP
ahg_loan_object
19 columns 1 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK loan_id bigint unsigned NOT NULL -
FK information_object_id int NULL -
external_object_id varchar(255) NULL -
object_title varchar(500) NULL -
object_identifier varchar(255) NULL -
object_type varchar(100) NULL -
insurance_value decimal(15,2) NULL -
condition_report_id bigint unsigned NULL -
condition_on_departure text NULL -
condition_on_return text NULL -
special_requirements text NULL -
display_requirements text NULL -
FK status varchar(82) NULL pending pending, approved, prepared, dispatched, received, on_display, packed, returned
dispatched_date date NULL -
received_date date NULL -
returned_date date NULL -
created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
updated_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED on update CURRENT_TIMESTAMP
ahg_loan_shipment
25 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK loan_id bigint unsigned NOT NULL -
FK courier_id bigint unsigned NULL -
shipment_type varchar(25) NOT NULL outbound outbound, return
shipment_number varchar(100) NULL -
tracking_number varchar(255) NULL -
waybill_number varchar(255) NULL -
origin_address text NULL -
destination_address text NULL -
scheduled_pickup datetime NULL -
actual_pickup datetime NULL -
scheduled_delivery datetime NULL -
actual_delivery datetime NULL -
FK status varchar(89) NULL planned planned, picked_up, in_transit, customs, out_for_delivery, delivered, failed, returned
handling_instructions text NULL -
special_requirements text NULL -
shipping_cost decimal(12,2) NULL -
insurance_cost decimal(12,2) NULL -
customs_cost decimal(12,2) NULL -
total_cost decimal(12,2) NULL -
cost_currency varchar(3) NULL ZAR
notes text NULL -
created_by int NULL -
created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
updated_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED on update CURRENT_TIMESTAMP
ahg_loan_shipment_event
7 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK shipment_id bigint unsigned NOT NULL -
event_time datetime NOT NULL -
event_type varchar(100) NULL -
location varchar(255) NULL -
description text NULL -
created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
ahg_loan_status_history
7 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK loan_id bigint unsigned NOT NULL -
from_status varchar(50) NULL -
to_status varchar(50) NOT NULL -
changed_by int NULL -
comment text NULL -
FK created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
loan
29 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
UQ loan_number varchar(50) NOT NULL -
FK loan_type varchar(20) NOT NULL - out, in
status varchar(50) NULL pending
purpose varchar(88) NOT NULL exhibition exhibition, research, conservation, photography, education, filming, long_term, other
title varchar(255) NULL -
description text NULL -
FK partner_institution varchar(255) NOT NULL -
partner_contact_name varchar(255) NULL -
partner_contact_email varchar(255) NULL -
partner_contact_phone varchar(100) NULL -
partner_address text NULL -
request_date date NOT NULL -
FK start_date date NULL -
FK end_date date NULL -
FK return_date date NULL -
insurance_type varchar(48) NOT NULL borrower borrower, lender, shared, government, self
insurance_value decimal(15,2) NULL -
insurance_currency varchar(10) NOT NULL ZAR
insurance_policy_number varchar(100) NULL -
insurance_provider varchar(255) NULL -
loan_fee decimal(12,2) NULL -
loan_fee_currency varchar(10) NOT NULL ZAR
internal_approver_id int unsigned NULL -
approved_date date NULL -
notes text NULL -
created_by int unsigned NOT NULL -
created_at timestamp NULL -
updated_at timestamp NULL -
loan_document
10 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK loan_id bigint unsigned NOT NULL -
FK document_type varchar(116) NOT NULL - agreement, facilities_report, condition_report, insurance_certificate, receipt, correspondence, photograph, other
file_path varchar(500) NOT NULL -
file_name varchar(255) NOT NULL -
mime_type varchar(100) NULL -
file_size int unsigned NULL -
description text NULL -
uploaded_by int unsigned NULL -
created_at timestamp NOT NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
loan_extension
7 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK loan_id bigint unsigned NOT NULL -
previous_end_date date NOT NULL -
new_end_date date NOT NULL -
reason text NULL -
approved_by int unsigned NOT NULL -
created_at timestamp NOT NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
loan_object
10 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK loan_id bigint unsigned NOT NULL -
FK information_object_id int unsigned NOT NULL -
object_title varchar(500) NULL -
object_identifier varchar(100) NULL -
insurance_value decimal(15,2) NULL -
condition_report_id bigint unsigned NULL -
special_requirements text NULL -
display_requirements text NULL -
created_at timestamp NOT NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
Notes

Log in to add notes.

Legend
PK Primary Key FK Foreign Key / Index UQ Unique Constraint
Table structures are read live from the database. Row counts reflect current data.