Database tables
MySQL 8.0, as this commit's migrations leave it: mysqldump --no-data after migration:run, not a snapshot of dev. Raw: db-schema.sql.
Tables (101)
DATABASECHANGELOG
| Column | Type | Null | Default | Notes |
|---|
ID | varchar(255) | no | | |
AUTHOR | varchar(255) | no | | |
FILENAME | varchar(255) | no | | |
DATEEXECUTED | datetime | no | | |
ORDEREXECUTED | int | no | | |
EXECTYPE | varchar(10) | no | | |
MD5SUM | varchar(35) | yes | NULL | |
DESCRIPTION | varchar(255) | yes | NULL | |
COMMENTS | varchar(255) | yes | NULL | |
TAG | varchar(255) | yes | NULL | |
LIQUIBASE | varchar(20) | yes | NULL | |
CONTEXTS | varchar(255) | yes | NULL | |
LABELS | varchar(255) | yes | NULL | |
DEPLOYMENT_ID | varchar(10) | yes | NULL | |
DATABASECHANGELOGLOCK
| Column | Type | Null | Default | Notes |
|---|
ID | int | no | | |
LOCKED | bit(1) | no | | |
LOCKGRANTED | datetime | yes | NULL | |
LOCKEDBY | varchar(255) | yes | NULL | |
PRIMARY KEY (`ID`)
access_group_members
| Column | Type | Null | Default | Notes |
|---|
group_id | varchar(36) | no | | |
user_id | bigint | no | | |
PRIMARY KEY (`group_id`,`user_id`)
KEY `fk_agm_user` (`user_id`)
CONSTRAINT `fk_agm_group` FOREIGN KEY (`group_id`) REFERENCES `access_groups` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
CONSTRAINT `fk_agm_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
access_groups
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
group_name | varchar(255) | no | | |
is_active | bit(1) | yes | b'1' | |
no_of_users | int | yes | '0' | |
created_at | timestamp | yes | CURRENT_TIMESTAMP | |
modified_at | timestamp | yes | CURRENT_TIMESTAMP | |
public_id | bigint | no | | auto increment |
PRIMARY KEY (`id`)
UNIQUE KEY `group_name` (`group_name`)
UNIQUE KEY `uk_access_groups_public_id` (`public_id`)
address
| Column | Type | Null | Default | Notes |
|---|
id | int unsigned | no | | auto increment |
city | varchar(255) | no | | |
street | varchar(512) | yes | NULL | |
postal_code | varchar(45) | yes | NULL | |
street_number | varchar(45) | yes | NULL | |
country | varchar(255) | yes | NULL | |
PRIMARY KEY (`id`)
administrative_level
| Column | Type | Null | Default | Notes |
|---|
id | int unsigned | no | | auto increment |
level | varchar(50) | no | | |
creation_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
UNIQUE KEY `id` (`id`)
amenity
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
type | varchar(255) | no | | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
amenity_condition
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
value | varchar(255) | no | | |
PRIMARY KEY (`id`)
amenity_quality
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
value | varchar(255) | no | | |
PRIMARY KEY (`id`)
app_settings
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
setting_key | varchar(255) | no | | |
setting_value | text | yes | | |
description | text | yes | | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
UNIQUE KEY `id` (`id`)
asset
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
portfolio_id | varchar(36) | no | | |
address_id | varchar(36) | no | | |
type_id | varchar(36) | no | | |
dossier_ph_id | varchar(50) | yes | NULL | |
hidden | bit(1) | yes | b'0' | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
highlight_section_title | json | yes | NULL | |
highlight_section | json | yes | NULL | |
hidden_asset_usages | bit(1) | yes | NULL | |
title | text | yes | | |
use_area_adjusted_valuation | bit(1) | yes | b'0' | |
property_description_text | json | yes | NULL | |
vdr_root_node_id | varchar(36) | yes | NULL | |
building_year | varchar(255) | yes | NULL | |
net_living_area | double | yes | NULL | |
renovation_year | int | yes | NULL | |
land_area | double | yes | NULL | |
energy_level_id | varchar(36) | yes | NULL | |
number_of_floors | int | yes | NULL | |
number_of_rooms | double | yes | NULL | |
number_of_bathrooms | int | yes | NULL | |
balcony_terrace | double | yes | NULL | |
garden | double | yes | NULL | |
garage_spaces | int | yes | NULL | |
outdoor_parking_spaces | int | yes | NULL | |
new_building | bit(1) | yes | NULL | |
rented | bit(1) | yes | NULL | |
PRIMARY KEY (`id`)
KEY `fk_asset_v2_address_idx` (`address_id`)
KEY `fk_asset_v2_type_idx` (`type_id`)
KEY `fk_asset_v2_portfolio_v2_id_idx` (`portfolio_id`)
KEY `fk_asset_energy_level` (`energy_level_id`)
CONSTRAINT `fk_asset_energy_level` FOREIGN KEY (`energy_level_id`) REFERENCES `energy_level` (`id`)
CONSTRAINT `fk_asset_v2_address_idx` FOREIGN KEY (`address_id`) REFERENCES `asset_address` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
CONSTRAINT `fk_asset_v2_portfolio_v2_id_idx` FOREIGN KEY (`portfolio_id`) REFERENCES `portfolio` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
CONSTRAINT `fk_asset_v2_type_idx` FOREIGN KEY (`type_id`) REFERENCES `asset_type` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
asset_address
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
city | varchar(255) | no | | |
street | varchar(512) | yes | NULL | |
postal_code | varchar(45) | yes | NULL | |
street_number | varchar(45) | yes | NULL | |
lat | double | no | | |
lng | double | no | | |
country_code | varchar(2) | no | 'DE' | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
asset_apartment
| Column | Type | Null | Default | Notes |
|---|
asset_id | varchar(36) | no | | |
asset_apartment_sub_type_id | varchar(36) | yes | NULL | |
floor_number | int | yes | NULL | |
has_lift | bit(1) | yes | NULL | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`asset_id`)
KEY `fk_asset_apartment_v2_asset_v2_id_idx` (`asset_id`)
KEY `fk_asset_apartment_v2_sub_type_idx` (`asset_apartment_sub_type_id`)
CONSTRAINT `fk_asset_apartment_v2_asset_v2_id_idx` FOREIGN KEY (`asset_id`) REFERENCES `asset` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
CONSTRAINT `fk_asset_apartment_v2_sub_type_idx` FOREIGN KEY (`asset_apartment_sub_type_id`) REFERENCES `asset_apartment_sub_type` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
asset_apartment_amenity
| Column | Type | Null | Default | Notes |
|---|
asset_apartment_id | varchar(36) | no | | |
amenity_id | varchar(36) | no | | |
amenity_condition_id | varchar(36) | yes | NULL | |
amenity_quality_id | varchar(36) | yes | NULL | |
PRIMARY KEY (`asset_apartment_id`,`amenity_id`)
KEY `fk_asset_apartment_v2_amenity_asset_apartment_v2_idx` (`asset_apartment_id`)
KEY `fk_asset_apartment_v2_amenity_amenity_idx` (`amenity_id`)
KEY `fk_amenity_condition_id` (`amenity_condition_id`)
KEY `fk_amenity_quality_id` (`amenity_quality_id`)
CONSTRAINT `fk_amenity_condition_id` FOREIGN KEY (`amenity_condition_id`) REFERENCES `amenity_condition` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
CONSTRAINT `fk_amenity_quality_id` FOREIGN KEY (`amenity_quality_id`) REFERENCES `amenity_quality` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
CONSTRAINT `fk_asset_apartment_v2_amenity_amenity_idx` FOREIGN KEY (`amenity_id`) REFERENCES `amenity` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
CONSTRAINT `fk_asset_apartment_v2_amenity_asset_apartment_v2_idx` FOREIGN KEY (`asset_apartment_id`) REFERENCES `asset_apartment` (`asset_id`) ON DELETE CASCADE ON UPDATE RESTRICT
asset_apartment_sub_type
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
subtype | varchar(50) | yes | NULL | |
ph_subtype | varchar(50) | yes | NULL | |
creation_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
asset_custom_chart
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
chart_type_id | varchar(36) | yes | NULL | |
custom_tab_id | varchar(36) | yes | NULL | |
chart_data | json | yes | NULL | |
creation_date | timestamp | yes | NULL | |
modified_date | timestamp | yes | NULL | |
PRIMARY KEY (`id`)
KEY `idx_asset_chart_type` (`chart_type_id`)
KEY `idx_asset_chart_custom_tab` (`custom_tab_id`)
CONSTRAINT `fk_asset_chart_section` FOREIGN KEY (`custom_tab_id`) REFERENCES `asset_investment_summary_section` (`id`) ON DELETE CASCADE
CONSTRAINT `fk_asset_chart_type` FOREIGN KEY (`chart_type_id`) REFERENCES `chart_type` (`id`)
asset_document
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
path | varchar(255) | no | | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
asset_id | varchar(36) | no | | |
asset_document_type_id | varchar(36) | no | | |
PRIMARY KEY (`id`)
UNIQUE KEY `id` (`id`)
KEY `asset_v2_document_asset_idx` (`asset_id`)
KEY `asset_v2_document_document_type1_idx` (`asset_document_type_id`)
CONSTRAINT `asset_v2_document_asset_idx` FOREIGN KEY (`asset_id`) REFERENCES `asset` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
CONSTRAINT `asset_v2_document_document_type1_idx` FOREIGN KEY (`asset_document_type_id`) REFERENCES `asset_document_type` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
asset_document_type
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
type | varchar(50) | no | | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
UNIQUE KEY `id` (`id`)
| Column | Type | Null | Default | Notes |
|---|
field_id | varchar(36) | no | | |
asset_id | varchar(255) | no | | |
field_name | varchar(255) | no | | |
field_value | json | yes | NULL | |
custom_field | bit(1) | yes | NULL | |
field_visible | bit(1) | yes | NULL | |
field_disabled | bit(1) | yes | NULL | |
field_content_type | varchar(255) | yes | NULL | |
allowed_null | bit(1) | yes | NULL | |
i18n_label | json | yes | NULL | |
field_validators | varchar(255) | yes | NULL | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
order_number | int | no | | |
PRIMARY KEY (`field_id`)
UNIQUE KEY `field_id` (`field_id`)
KEY `asset_v2_id_idx` (`asset_id`)
KEY `ix_asset_fields_metadata_order` (`asset_id`,`order_number`)
CONSTRAINT `fk_asset_v2_custom_fields_asset_v2_id` FOREIGN KEY (`asset_id`) REFERENCES `asset` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
asset_gav
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
asset_id | varchar(36) | no | | |
gav | double | no | | |
month_of_year | int | yes | NULL | |
year | int | yes | NULL | |
date_of_valuation | date | yes | NULL | |
source_of_valuation | text | yes | | |
date_of_submission | date | yes | NULL | |
submitted_by | varchar(255) | yes | NULL | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
KEY `fk_gav_v2_asset_v2_id_idx` (`asset_id`)
CONSTRAINT `fk_gav_v2_asset_v2_id_idx` FOREIGN KEY (`asset_id`) REFERENCES `asset` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
asset_highlights
| Column | Type | Null | Default | Notes |
|---|
description | json | yes | NULL | |
order_number | int unsigned | yes | NULL | |
asset_id | varchar(36) | no | | |
highlights_id | int unsigned | no | | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`asset_id`,`highlights_id`)
KEY `fk_asset_v2_highlights_portfolio1_idx` (`asset_id`)
KEY `fk_asset_v2_highlights_highlights1_idx` (`highlights_id`)
CONSTRAINT `fk_asset_v2_highlights_highlights1_idx` FOREIGN KEY (`highlights_id`) REFERENCES `highlights` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
CONSTRAINT `fk_asset_v2_highlights_portfolio1_idx` FOREIGN KEY (`asset_id`) REFERENCES `asset` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
asset_house
| Column | Type | Null | Default | Notes |
|---|
asset_id | varchar(36) | no | | |
asset_house_sub_type_id | varchar(36) | yes | NULL | |
has_pool | bit(1) | yes | NULL | |
has_sauna | bit(1) | yes | NULL | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`asset_id`)
KEY `fk_asset_house_v2_sub_type_idx` (`asset_house_sub_type_id`)
CONSTRAINT `fk_asset_house_v2_sub_type_idx` FOREIGN KEY (`asset_house_sub_type_id`) REFERENCES `asset_house_sub_type` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
asset_house_amenity
| Column | Type | Null | Default | Notes |
|---|
asset_house_id | varchar(36) | no | | |
amenity_id | varchar(36) | no | | |
amenity_condition_id | varchar(36) | yes | NULL | |
amenity_quality_id | varchar(36) | yes | NULL | |
PRIMARY KEY (`asset_house_id`,`amenity_id`)
KEY `fk_asset_house_v2_id_amenity_asset_house_v2_idx` (`asset_house_id`)
KEY `fk_asset_house_v2_amenity_amenity_idx` (`amenity_id`)
KEY `fk_asset_house_v2_amenity_condition_id` (`amenity_condition_id`)
KEY `fk_asset_house_v2_amenity_quality_id` (`amenity_quality_id`)
CONSTRAINT `fk_asset_house_v2_amenity_amenity_idx` FOREIGN KEY (`amenity_id`) REFERENCES `amenity` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
CONSTRAINT `fk_asset_house_v2_amenity_condition_id` FOREIGN KEY (`amenity_condition_id`) REFERENCES `amenity_condition` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
CONSTRAINT `fk_asset_house_v2_amenity_quality_id` FOREIGN KEY (`amenity_quality_id`) REFERENCES `amenity_quality` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
CONSTRAINT `fk_asset_house_v2_id_amenity_asset_house_v2_idx` FOREIGN KEY (`asset_house_id`) REFERENCES `asset_house` (`asset_id`) ON DELETE CASCADE ON UPDATE RESTRICT
asset_house_sub_type
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
subtype | varchar(50) | no | | |
ph_subtype | varchar(50) | no | | |
creation_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
asset_image
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
asset_id | varchar(36) | yes | NULL | |
path | varchar(255) | no | | |
order_number | int unsigned | no | | |
creation_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
KEY `fk_asset_v2_image_1idx` (`asset_id`)
CONSTRAINT `fk_asset_v2_image_1` FOREIGN KEY (`asset_id`) REFERENCES `asset` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
asset_investment_summary_custom_tab
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
mapping_id | varchar(36) | yes | NULL | |
title | json | yes | NULL | |
content | json | yes | NULL | |
created_at | timestamp | yes | NULL | |
modified_at | timestamp | yes | NULL | |
PRIMARY KEY (`id`)
KEY `idx_asset_custom_tab_mapping` (`mapping_id`)
CONSTRAINT `fk_asset_custom_tab_mapping` FOREIGN KEY (`mapping_id`) REFERENCES `asset_investment_summary_section` (`id`) ON DELETE CASCADE
asset_investment_summary_section
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
asset_id | varchar(36) | no | | |
section_id | varchar(36) | no | | |
hidden | bit(1) | no | | |
creation_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
order_number | int | yes | NULL | |
PRIMARY KEY (`id`)
KEY `fk_asset_id_asset_v2_investment_summary_section` (`asset_id`)
KEY `fk_section_id_asset_v2_investment_summary_section` (`section_id`)
CONSTRAINT `fk_asset_id_asset_v2_investment_summary_section` FOREIGN KEY (`asset_id`) REFERENCES `asset` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
CONSTRAINT `fk_section_id_asset_v2_investment_summary_section` FOREIGN KEY (`section_id`) REFERENCES `investment_summary_section` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
asset_location_marker
| Column | Type | Null | Default | Notes |
|---|
uuid | varchar(36) | no | | |
asset_id | varchar(36) | no | | |
google_place_id | varchar(255) | yes | NULL | |
lat | double | yes | NULL | |
lng | double | yes | NULL | |
PRIMARY KEY (`uuid`)
KEY `fk_asset_location_marker_asset_v21_idx` (`asset_id`)
asset_manually_valued
| Column | Type | Null | Default | Notes |
|---|
asset_id | varchar(36) | no | | |
asset_usage | json | yes | NULL | |
ownership | json | yes | NULL | |
net_rental_income_pa | double | yes | NULL | |
net_rental_income_per_m2_pa | double | yes | NULL | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`asset_id`)
KEY `fk_asset_v2_custom_asset_v2_id_idx` (`asset_id`)
CONSTRAINT `fk_asset_v2_custom_asset_v2_id_idx` FOREIGN KEY (`asset_id`) REFERENCES `asset` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
asset_multi_family_house
| Column | Type | Null | Default | Notes |
|---|
asset_id | varchar(36) | no | | |
number_of_units | int | yes | NULL | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`asset_id`)
asset_property_description
| Column | Type | Null | Default | Notes |
|---|
field_id | varchar(36) | no | | |
asset_id | varchar(255) | no | | |
field_name | varchar(255) | no | | |
field_value | json | yes | NULL | |
custom_field | bit(1) | yes | NULL | |
field_visible | bit(1) | yes | NULL | |
field_content_type | varchar(255) | yes | NULL | |
allowed_null | bit(1) | yes | NULL | |
i18n_label | json | yes | NULL | |
field_validators | varchar(255) | yes | NULL | |
field_disabled | bit(1) | yes | NULL | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
order_number | int | no | | |
PRIMARY KEY (`field_id`)
UNIQUE KEY `field_id` (`field_id`)
KEY `asset_v2_ii_portfolio_idx` (`asset_id`)
CONSTRAINT `asset_v2_property_description_portfolio_id` FOREIGN KEY (`asset_id`) REFERENCES `asset` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
asset_type
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
value | varchar(255) | yes | NULL | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
asset_usage
| Column | Type | Null | Default | Notes |
|---|
usage_id | int unsigned | no | | |
asset_id | varchar(36) | no | | |
area_breakdown_value | double | no | | |
rent_breakdown_value | double | yes | NULL | |
PRIMARY KEY (`usage_id`,`asset_id`)
KEY `fk_asset_v2_usage_asset1_idx` (`asset_id`)
KEY `fk_asset_v2_usage_usage1_idx` (`usage_id`)
CONSTRAINT `fk_asset_v2_usage_asset1_idx` FOREIGN KEY (`asset_id`) REFERENCES `asset` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
CONSTRAINT `fk_asset_v2_usage_usage1_idx` FOREIGN KEY (`usage_id`) REFERENCES `usages` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
asset_valuation_adjustment
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
asset_id | varchar(36) | no | | |
actual_residential_living_area | decimal(10,4) | yes | NULL | |
residential_percentage | decimal(10,4) | yes | NULL | |
residential_percentage_hidden | bit(1) | yes | b'0' | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
UNIQUE KEY `id` (`id`)
UNIQUE KEY `asset_v2_valuation_customization_asset_id_unique` (`asset_id`)
KEY `fk_asset_v2_valuation_customization_asset_id_idx` (`asset_id`)
CONSTRAINT `fk_asset_v2_valuation_customization_asset_id_idx` FOREIGN KEY (`asset_id`) REFERENCES `asset` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
chart_type
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
type | varchar(255) | no | | |
creation_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
UNIQUE KEY `id` (`id`)
country_city_zipcode
| Column | Type | Null | Default | Notes |
|---|
id | int unsigned | no | | auto increment |
country_code | varchar(5) | no | | |
postal_code | int | no | | |
city | varchar(255) | no | | |
region | json | yes | NULL | |
district | varchar(255) | yes | NULL | |
latitude | double | yes | NULL | |
longitude | double | yes | NULL | |
PRIMARY KEY (`id`)
document
| Column | Type | Null | Default | Notes |
|---|
id | bigint | no | | auto increment |
creation_date | date | yes | NULL | |
modified_date | date | yes | NULL | |
path | varchar(255) | yes | NULL | |
user_id | bigint | yes | NULL | |
PRIMARY KEY (`id`)
KEY `FKjhdxdv9sijhujiynqbb5jc010` (`user_id`)
CONSTRAINT `FKjhdxdv9sijhujiynqbb5jc010` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
energy_level
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
level | varchar(50) | yes | NULL | |
ph_level | varchar(50) | yes | NULL | |
creation_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
feature_flag
| Column | Type | Null | Default | Notes |
|---|
flag_key | varchar(100) | no | | |
enabled | tinyint(1) | no | '0' | |
audience_json | json | yes | NULL | |
updated_by | varchar(255) | yes | NULL | |
updated_at | datetime | yes | NULL | |
PRIMARY KEY (`flag_key`)
highlights
| Column | Type | Null | Default | Notes |
|---|
id | int unsigned | no | | auto increment |
picture_name | text | no | | |
PRIMARY KEY (`id`)
investment_summary_section
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
value | varchar(255) | yes | NULL | |
creation_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
location_level_type
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
level_type | varchar(50) | no | | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
UNIQUE KEY `id` (`id`)
message_data
| Column | Type | Null | Default | Notes |
|---|
uuid | varchar(36) | no | | |
sender | bigint | yes | NULL | |
recipient | bigint | no | | |
subject | text | yes | | |
message | text | yes | | |
message_type | varchar(255) | yes | NULL | |
thread_id | text | yes | | |
seen | bit(1) | yes | b'0' | |
creation_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`uuid`)
KEY `fk_sender_message_data1` (`sender`)
KEY `fk_recipient_message_data1` (`recipient`)
CONSTRAINT `fk_recipient_message_data1` FOREIGN KEY (`recipient`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
CONSTRAINT `fk_sender_message_data1` FOREIGN KEY (`sender`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
ph_access_token
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
token | varchar(255) | no | | |
expiry_date | datetime | no | | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
UNIQUE KEY `id` (`id`)
ph_asset
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
asset_id | varchar(36) | no | | |
price | double | yes | NULL | |
min_value | double | yes | NULL | |
max_value | double | yes | NULL | |
net_rent | double | yes | NULL | |
sale_price_forecast_next_year | int | yes | NULL | |
rent_price_forecast_next_year | int | yes | NULL | |
sqm_price | double | yes | NULL | |
sqm_rent_price | double | yes | NULL | |
rent_income | double | yes | NULL | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
gross_initial_yield | double | yes | NULL | |
variant | varchar(32) | no | 'STANDARD' | |
PRIMARY KEY (`id`)
UNIQUE KEY `id` (`id`)
UNIQUE KEY `uq_ph_asset_variant` (`asset_id`,`variant`)
KEY `asset_v2_id_idx` (`asset_id`)
CONSTRAINT `fk_asset_v2_id` FOREIGN KEY (`asset_id`) REFERENCES `asset` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
ph_divisions_level
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
official_id | varchar(100) | no | | |
name | varchar(100) | no | | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
UNIQUE KEY `id` (`id`)
ph_location
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
location_level_type_id | varchar(36) | no | | |
google_place_id | varchar(255) | no | | |
lat | double | yes | NULL | |
lng | double | yes | NULL | |
search_location | varchar(512) | yes | NULL | |
country_code | varchar(2) | no | 'DE' | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
ph_division_level_id | varchar(36) | no | | |
PRIMARY KEY (`id`)
UNIQUE KEY `id` (`id`)
UNIQUE KEY `google_place_id` (`google_place_id`)
KEY `fk_location_level_type2` (`location_level_type_id`)
KEY `fk_ph_division_level_1` (`ph_division_level_id`)
CONSTRAINT `fk_location_level_type2` FOREIGN KEY (`location_level_type_id`) REFERENCES `location_level_type` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
CONSTRAINT `fk_ph_division_level_1` FOREIGN KEY (`ph_division_level_id`) REFERENCES `ph_divisions_level` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
ph_portfolio
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
portfolio_id | varchar(36) | no | | |
evaluated_value | int | yes | NULL | |
min_value | int | yes | NULL | |
max_value | int | yes | NULL | |
average_sqm_price | double | yes | NULL | |
average_sqm_rent_price | double | yes | NULL | |
total_rent_income | double | yes | NULL | |
average_rent | double | yes | NULL | |
nr_of_single_assets | int | yes | NULL | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
gross_initial_yield | double | yes | NULL | |
rent_multiple_1 | double | yes | NULL | |
rent_multiple_2 | double | yes | NULL | |
PRIMARY KEY (`id`)
UNIQUE KEY `id` (`id`)
KEY `portfolio_v2_id_idx` (`portfolio_id`)
CONSTRAINT `fk_portfolio_v2_id` FOREIGN KEY (`portfolio_id`) REFERENCES `portfolio` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
ph_social_economics
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
ph_location_id | varchar(36) | no | | |
metric | varchar(64) | no | | |
bucket_id | varchar(36) | yes | NULL | |
official_id | varchar(100) | no | | |
year | int | yes | NULL | |
absolute_value | double | no | | |
relative_value | double | no | | |
created_date | timestamp | yes | NULL | |
modified_date | timestamp | yes | NULL | |
PRIMARY KEY (`id`)
KEY `ix_ph_se_location_metric_year` (`ph_location_id`,`metric`,`year`)
KEY `ix_ph_se_metric` (`metric`)
KEY `fk_ph_se_bucket` (`bucket_id`)
CONSTRAINT `fk_ph_se_bucket` FOREIGN KEY (`bucket_id`) REFERENCES `ph_social_economics_bucket` (`id`)
CONSTRAINT `fk_ph_se_location` FOREIGN KEY (`ph_location_id`) REFERENCES `ph_location` (`id`)
ph_social_economics_bucket
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
metric | varchar(64) | no | | |
code | varchar(100) | no | | |
created_date | timestamp | yes | NULL | |
modified_date | timestamp | yes | NULL | |
PRIMARY KEY (`id`)
UNIQUE KEY `uq_ph_bucket_metric_code` (`metric`,`code`)
KEY `ix_ph_bucket_metric` (`metric`)
ph_valuation
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
asset_id | varchar(36) | yes | NULL | |
portfolio_id | varchar(36) | yes | NULL | |
variant | varchar(32) | no | | |
value | double | yes | NULL | |
year | int | no | | |
month | int | no | | |
date_of_valuation | date | yes | NULL | |
source_of_valuation | text | yes | | |
is_stale | bit(1) | yes | NULL | |
valuation_type | varchar(50) | yes | NULL | |
computed_with_partial_data | bit(1) | yes | NULL | |
created_date | timestamp | yes | NULL | |
modified_date | timestamp | yes | NULL | |
PRIMARY KEY (`id`)
KEY `ix_ph_valuation_asset` (`asset_id`,`variant`,`year`,`month`)
KEY `ix_ph_valuation_portfolio` (`portfolio_id`,`year`,`month`)
CONSTRAINT `fk_ph_valuation_asset` FOREIGN KEY (`asset_id`) REFERENCES `asset` (`id`)
CONSTRAINT `fk_ph_valuation_portfolio` FOREIGN KEY (`portfolio_id`) REFERENCES `portfolio` (`id`)
CONSTRAINT `ck_ph_valuation_one_subject` CHECK ((((`asset_id` is not null) and (`portfolio_id` is null)) or ((`asset_id` is null) and (`portfolio_id` is not null))))
portfolio
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
name | varchar(255) | yes | NULL | |
asset_owner | varchar(255) | yes | NULL | |
asset_manager | varchar(255) | yes | NULL | |
location | varchar(255) | no | | |
description | json | yes | NULL | |
ownership | json | yes | NULL | |
gross_initial_yield | double | yes | NULL | |
portfolio_usage | json | yes | NULL | |
plot_size | double | yes | NULL | |
lettable_area | double | yes | NULL | |
total_living_area | double | yes | NULL | |
net_rental_income_pa | double | yes | NULL | |
net_rental_income_residential_pa | double | yes | NULL | |
average_lettable_area | double | yes | NULL | |
average_total_living_area | double | yes | NULL | |
number_of_parking_slots | varchar(45) | yes | NULL | |
average_net_rental_income | double | yes | NULL | |
walt | json | yes | NULL | |
portfolio_token_id | json | yes | NULL | |
occupancy_rate | double | yes | NULL | |
total_gross_asset_value | double | yes | NULL | |
total_debt_outstanding | double | yes | NULL | |
debt_service | double | yes | NULL | |
highlight_section_title | json | yes | NULL | |
highlight_section | json | yes | NULL | |
nr_of_assets | int | yes | NULL | |
created_date | timestamp | yes | NULL | |
modified_date | timestamp | yes | NULL | |
t0 | int | yes | NULL | |
property_description_text | json | yes | NULL | |
term_sheet_title | json | yes | NULL | |
subscription_note_title | json | yes | NULL | |
property_description_title | json | yes | NULL | |
portfolio_category_id | varchar(36) | no | | |
download_asset_list_text | json | yes | NULL | |
download_rent_roll_text | json | yes | NULL | |
nr_of_securities | int | yes | NULL | |
portfolio_email_id | varchar(255) | yes | NULL | |
vdr_watermark_enabled | tinyint(1) | no | '0' | |
vdr_root_node_id | varchar(36) | yes | NULL | |
debt_breakdown | text | yes | | |
lease_expiry_data | text | yes | | |
tenant_top5_value | double | yes | NULL | |
tenant_others_value | double | yes | NULL | |
residential_percentage | decimal(10,4) | yes | NULL | |
construction_year | varchar(45) | yes | NULL | |
renovation_year | varchar(45) | yes | NULL | |
PRIMARY KEY (`id`)
UNIQUE KEY `portfolio_v2_name_unique` (`name`)
KEY `fk_portfoliov2_construction_year_idx` (`id`)
KEY `fk_portfoliov2_last_refurbishment_idx` (`id`)
KEY `fk_portfolio_portfolio_v2_category_idx` (`portfolio_category_id`)
CONSTRAINT `fk_portfolio_portfolio_v2_category_id1` FOREIGN KEY (`portfolio_category_id`) REFERENCES `portfolio_category` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
portfolio_access
| Column | Type | Null | Default | Notes |
|---|
id | char(36) | no | | |
user_id | bigint | no | | |
portfolio_id | char(36) | no | | |
group_id | char(36) | yes | NULL | |
access_level | varchar(32) | no | | |
source | varchar(32) | no | | |
granted_by | bigint | yes | NULL | |
created_at | timestamp | yes | NULL | |
modified_at | timestamp | yes | NULL | |
PRIMARY KEY (`id`)
KEY `portfolio_v2_access_portfolio_v2_id_portfolio_v2_id_fk` (`portfolio_id`)
KEY `portfolio_v2_access_group_id_access_groups_id_fk` (`group_id`)
KEY `portfolio_v2_access_granted_by_user_id_fk` (`granted_by`)
KEY `idx_portfolio_v2_access_user_portfolio` (`user_id`,`portfolio_id`)
CONSTRAINT `portfolio_v2_access_granted_by_user_id_fk` FOREIGN KEY (`granted_by`) REFERENCES `user` (`id`)
CONSTRAINT `portfolio_v2_access_group_id_access_groups_id_fk` FOREIGN KEY (`group_id`) REFERENCES `access_groups` (`id`)
CONSTRAINT `portfolio_v2_access_portfolio_v2_id_portfolio_v2_id_fk` FOREIGN KEY (`portfolio_id`) REFERENCES `portfolio` (`id`)
CONSTRAINT `portfolio_v2_access_user_id_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
CONSTRAINT `chk_pa_access_level` CHECK ((`access_level` in (_utf8mb3'LEVEL2',_utf8mb3'LEVEL3',_utf8mb3'PORTFOLIO_MANAGER')))
CONSTRAINT `chk_pa_source` CHECK ((`source` in (_utf8mb3'DIRECT',_utf8mb3'GROUP',_utf8mb3'NDA')))
portfolio_access_requests
| Column | Type | Null | Default | Notes |
|---|
id | char(36) | no | | |
user_id | bigint | no | | |
portfolio_id | char(36) | no | | |
reviewed_by | bigint | yes | NULL | |
status | varchar(32) | no | | |
requested_at | timestamp | yes | NULL | |
reviewed_at | timestamp | yes | NULL | |
reason | varchar(1000) | yes | NULL | Why the request was refused. Null unless status = REJECTED. |
PRIMARY KEY (`id`)
KEY `portfolio_v2_access_requests_user_id_user_id_fk` (`user_id`)
KEY `portfolio_v2_access_requests_portfolio_v2_id_portfolio_v2_id_fk` (`portfolio_id`)
KEY `portfolio_v2_access_requests_reviewed_by_user_id_fk` (`reviewed_by`)
CONSTRAINT `portfolio_v2_access_requests_portfolio_v2_id_portfolio_v2_id_fk` FOREIGN KEY (`portfolio_id`) REFERENCES `portfolio` (`id`)
CONSTRAINT `portfolio_v2_access_requests_reviewed_by_user_id_fk` FOREIGN KEY (`reviewed_by`) REFERENCES `user` (`id`)
CONSTRAINT `portfolio_v2_access_requests_user_id_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
CONSTRAINT `chk_par_status` CHECK ((`status` in (_utf8mb3'PENDING',_utf8mb3'APPROVED',_utf8mb3'REJECTED')))
portfolio_card_values
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
portfolio_id | varchar(36) | no | | |
blurred | bit(1) | no | b'0' | |
location | json | yes | NULL | |
usages | json | yes | NULL | |
portfolio_token_id | json | yes | NULL | |
gross_initial_yield | double | yes | NULL | |
button_text | json | yes | NULL | |
creation_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
show_instrument_yield | bit(1) | no | b'1' | |
PRIMARY KEY (`id`)
KEY `fk_portfolio_v2_card_values_portfolio_idx` (`portfolio_id`)
CONSTRAINT `fk_portfolio_v2_card_values_portfolio_id1` FOREIGN KEY (`portfolio_id`) REFERENCES `portfolio` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
portfolio_category
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
name | varchar(255) | no | | |
creation_date | date | yes | NULL | |
modified_date | date | yes | NULL | |
PRIMARY KEY (`id`)
portfolio_category_order
| Column | Type | Null | Default | Notes |
|---|
portfolio_id | varchar(36) | no | | |
portfolio_category_id | varchar(36) | no | | |
order_number | int unsigned | yes | NULL | |
KEY `fk_portfolio_order_portfolio_v2_category_idx` (`portfolio_category_id`)
KEY `fk_portfolio_order_portfolio_idx` (`portfolio_id`)
CONSTRAINT `fk_portfolio_order_portfolio_id1` FOREIGN KEY (`portfolio_id`) REFERENCES `portfolio` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
CONSTRAINT `fk_portfolio_order_portfolio_v2_category_id1` FOREIGN KEY (`portfolio_category_id`) REFERENCES `portfolio_category` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
portfolio_custom_chart
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
chart_type_id | varchar(36) | no | | |
custom_tab_id | varchar(36) | yes | NULL | |
chart_data | json | yes | NULL | |
creation_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
UNIQUE KEY `id` (`id`)
KEY `fk_portfolio_v2_custom_chart_chart_type_idx` (`chart_type_id`)
KEY `fk_portfolio_v2_custom_chart_investment_summary_section_idx` (`custom_tab_id`)
CONSTRAINT `fk_portfolio_v2_custom_chart_chart_type_id1` FOREIGN KEY (`chart_type_id`) REFERENCES `chart_type` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
CONSTRAINT `fk_portfolio_v2_custom_chart_investment_summary_section_id1` FOREIGN KEY (`custom_tab_id`) REFERENCES `portfolio_investment_summary_section` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
portfolio_debt_outstanding
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
portfolio_id | varchar(36) | no | | |
value | double | yes | NULL | |
date_of_calculation | date | yes | NULL | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
KEY `fk_portfolio_v2_debt_outstanding_portfolio_v2_id_idx` (`portfolio_id`)
CONSTRAINT `fk_portfolio_v2_debt_outstanding_portfolio_v2_id_idx` FOREIGN KEY (`portfolio_id`) REFERENCES `portfolio` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
portfolio_document
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
path | varchar(255) | no | | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
portfolio_id | varchar(36) | no | | |
portfolio_document_type_id | varchar(36) | no | | |
PRIMARY KEY (`id`)
UNIQUE KEY `id` (`id`)
KEY `portfolio_v2_document_portfolio_idx` (`portfolio_id`)
KEY `portfolio_v2_document_asset_document_type1_idx` (`portfolio_document_type_id`)
CONSTRAINT `portfolio_v2_document_asset_document_type1_idx` FOREIGN KEY (`portfolio_document_type_id`) REFERENCES `portfolio_document_type` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
CONSTRAINT `portfolio_v2_document_portfolio_idx` FOREIGN KEY (`portfolio_id`) REFERENCES `portfolio` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
portfolio_document_type
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
type | varchar(50) | no | | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
UNIQUE KEY `id` (`id`)
portfolio_fields
| Column | Type | Null | Default | Notes |
|---|
field_id | varchar(36) | no | | |
portfolio_id | varchar(255) | no | | |
field_name | varchar(255) | no | | |
field_value | json | yes | NULL | |
custom_field | bit(1) | yes | NULL | |
field_visible | bit(1) | yes | NULL | |
field_content_type | varchar(255) | yes | NULL | |
allowed_null | bit(1) | yes | NULL | |
i18n_label | json | yes | NULL | |
field_validators | varchar(255) | yes | NULL | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
field_disabled | bit(1) | yes | NULL | |
order_number | int | no | | |
PRIMARY KEY (`field_id`)
UNIQUE KEY `field_id` (`field_id`)
KEY `portfolio_v2_id_idx` (`portfolio_id`)
KEY `ix_portfolio_fields_order` (`portfolio_id`,`order_number`)
CONSTRAINT `fk_portfolio_v2_fields_portfolio_v2_id` FOREIGN KEY (`portfolio_id`) REFERENCES `portfolio` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
portfolio_financing_request
| Column | Type | Null | Default | Notes |
|---|
field_id | varchar(36) | no | | |
portfolio_id | varchar(36) | no | | |
field_name | varchar(255) | no | | |
field_value | json | yes | NULL | |
custom_field | bit(1) | yes | NULL | |
field_visible | bit(1) | yes | NULL | |
field_disabled | bit(1) | yes | NULL | |
field_content_type | varchar(255) | yes | NULL | |
allowed_null | bit(1) | yes | NULL | |
i18n_label | json | yes | NULL | |
field_validators | varchar(255) | yes | NULL | |
created_date | timestamp | yes | NULL | |
modified_date | timestamp | yes | NULL | |
order_number | int | no | | |
PRIMARY KEY (`field_id`)
UNIQUE KEY `field_id` (`field_id`)
KEY `fk_portfolio_v2_financing_request_portfolio_idx` (`portfolio_id`)
KEY `ix_portfolio_financing_request_order` (`portfolio_id`,`order_number`)
CONSTRAINT `fk_portfolio_v2_financing_request_portfolio_id` FOREIGN KEY (`portfolio_id`) REFERENCES `portfolio` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
portfolio_gav
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
portfolio_id | varchar(36) | no | | |
gav | double | yes | NULL | |
month_of_year | int | yes | NULL | |
year | int | yes | NULL | |
date_of_valuation | date | yes | NULL | |
source_of_valuation | text | yes | | |
date_of_submission | date | yes | NULL | |
submitted_by | varchar(255) | yes | NULL | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
KEY `fk_portfolio_v2_gav_portfolio_v2_id_idx` (`portfolio_id`)
CONSTRAINT `fk_portfolio_v2_gav_portfolio_v2_id_idx` FOREIGN KEY (`portfolio_id`) REFERENCES `portfolio` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
portfolio_highlights
| Column | Type | Null | Default | Notes |
|---|
description | json | yes | NULL | |
order_number | int unsigned | yes | NULL | |
portfolio_id | varchar(36) | no | | |
highlights_id | int unsigned | no | | |
PRIMARY KEY (`portfolio_id`,`highlights_id`)
KEY `fk_portfolio_v2_highlights_portfolio1_idx` (`portfolio_id`)
KEY `fk_portfolio_v2_highlights_highlights1_idx` (`highlights_id`)
CONSTRAINT `fk_portfolio_v2_highlights_highlights1_idx` FOREIGN KEY (`highlights_id`) REFERENCES `highlights` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
CONSTRAINT `fk_portfolio_v2_highlights_portfolio1_idx` FOREIGN KEY (`portfolio_id`) REFERENCES `portfolio` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
portfolio_image
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
portfolio_id | varchar(36) | yes | NULL | |
path | varchar(255) | no | | |
order_number | int unsigned | no | | |
creation_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
KEY `fk_portfolio_v2_image_1idx` (`portfolio_id`)
CONSTRAINT `fk_portfolio_v2_image_1` FOREIGN KEY (`portfolio_id`) REFERENCES `portfolio` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
portfolio_instrument_highlights
| Column | Type | Null | Default | Notes |
|---|
field_id | varchar(36) | no | | |
portfolio_id | varchar(255) | no | | |
field_name | varchar(255) | no | | |
field_value | json | yes | NULL | |
custom_field | bit(1) | yes | NULL | |
field_visible | bit(1) | yes | NULL | |
field_content_type | varchar(255) | yes | NULL | |
allowed_null | bit(1) | yes | NULL | |
i18n_label | json | yes | NULL | |
field_validators | varchar(255) | yes | NULL | |
field_disabled | bit(1) | yes | NULL | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
order_number | int | no | | |
PRIMARY KEY (`field_id`)
UNIQUE KEY `field_id` (`field_id`)
KEY `portfolio_v2_ih_portfolio_idx` (`portfolio_id`)
KEY `ix_portfolio_instrument_highlights_order` (`portfolio_id`,`order_number`)
CONSTRAINT `portfolio_v2_instrument_highlights_portfolio_id` FOREIGN KEY (`portfolio_id`) REFERENCES `portfolio` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
| Column | Type | Null | Default | Notes |
|---|
field_id | varchar(36) | no | | |
portfolio_id | varchar(255) | no | | |
field_name | varchar(255) | no | | |
field_value | json | yes | NULL | |
custom_field | bit(1) | yes | NULL | |
field_visible | bit(1) | yes | NULL | |
field_content_type | varchar(255) | yes | NULL | |
allowed_null | bit(1) | yes | NULL | |
i18n_label | json | yes | NULL | |
field_validators | varchar(255) | yes | NULL | |
field_disabled | bit(1) | yes | NULL | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
order_number | int | no | | |
PRIMARY KEY (`field_id`)
UNIQUE KEY `field_id` (`field_id`)
KEY `portfolio_v2_ii_portfolio_idx` (`portfolio_id`)
KEY `ix_portfolio_instrument_information_order` (`portfolio_id`,`order_number`)
CONSTRAINT `portfolio_v2_instrument_information_portfolio_id` FOREIGN KEY (`portfolio_id`) REFERENCES `portfolio` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
portfolio_investment_summary_custom_tab
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
mapping_id | varchar(36) | yes | NULL | |
title | json | yes | NULL | |
created_at | timestamp | yes | NULL | |
modified_at | timestamp | yes | NULL | |
content | json | yes | NULL | |
PRIMARY KEY (`id`)
KEY `idx_portfolio_custom_tab_mapping` (`mapping_id`)
CONSTRAINT `fk_portfolio_custom_tab_mapping` FOREIGN KEY (`mapping_id`) REFERENCES `portfolio_investment_summary_section` (`id`) ON DELETE CASCADE
portfolio_investment_summary_section
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
portfolio_id | varchar(36) | no | | |
section_id | varchar(36) | no | | |
hidden | bit(1) | no | | |
creation_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
order_number | int | yes | NULL | |
PRIMARY KEY (`id`)
KEY `fk_portfolio_id_portfolio_v2_investment_summary_section` (`portfolio_id`)
KEY `fk_section_id_portfolio_v2_investment_summary_section` (`section_id`)
CONSTRAINT `fk_portfolio_id_portfolio_v2_investment_summary_section` FOREIGN KEY (`portfolio_id`) REFERENCES `portfolio` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
CONSTRAINT `fk_section_id_portfolio_v2_investment_summary_section` FOREIGN KEY (`section_id`) REFERENCES `investment_summary_section` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
portfolio_pledges
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
portfolio_id | varchar(36) | no | | |
amount_euro | decimal(20,2) | yes | NULL | |
creation_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
user_id | varchar(36) | no | | |
contact_number | varchar(20) | yes | NULL | |
optional_text | varchar(255) | yes | NULL | |
PRIMARY KEY (`id`)
KEY `fk_portfolio_v2_pledges_idx` (`portfolio_id`)
CONSTRAINT `fk_portfolio_v2_pledges_id1` FOREIGN KEY (`portfolio_id`) REFERENCES `portfolio` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
portfolio_property_description
| Column | Type | Null | Default | Notes |
|---|
field_id | varchar(36) | no | | |
portfolio_id | varchar(255) | no | | |
field_name | varchar(255) | no | | |
field_value | json | yes | NULL | |
custom_field | bit(1) | yes | NULL | |
field_visible | bit(1) | yes | NULL | |
field_content_type | varchar(255) | yes | NULL | |
allowed_null | bit(1) | yes | NULL | |
i18n_label | json | yes | NULL | |
field_validators | varchar(255) | yes | NULL | |
field_disabled | bit(1) | yes | NULL | |
created_date | timestamp | yes | CURRENT_TIMESTAMP | |
modified_date | timestamp | yes | CURRENT_TIMESTAMP | |
order_number | int | no | | |
PRIMARY KEY (`field_id`)
UNIQUE KEY `field_id` (`field_id`)
KEY `portfolio_v2_ii_portfolio_idx` (`portfolio_id`)
KEY `ix_portfolio_property_description_order` (`portfolio_id`,`order_number`)
CONSTRAINT `portfolio_v2_property_description_portfolio_id` FOREIGN KEY (`portfolio_id`) REFERENCES `portfolio` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
portfolio_tenant
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
portfolio_id | varchar(36) | no | | |
tenant_name | varchar(512) | no | | |
order_value | int | yes | NULL | |
PRIMARY KEY (`id`)
UNIQUE KEY `id` (`id`)
KEY `fk_portfolio_v2_tenant_idx` (`portfolio_id`)
CONSTRAINT `fk_portfolio_v2_id_tenant` FOREIGN KEY (`portfolio_id`) REFERENCES `portfolio` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
portfolio_usage
| Column | Type | Null | Default | Notes |
|---|
usage_id | int unsigned | no | | |
portfolio_id | varchar(36) | no | | |
area_breakdown_value | double | no | | |
rent_breakdown_value | double | yes | NULL | |
PRIMARY KEY (`usage_id`,`portfolio_id`)
KEY `fk_portfolio_v2_usage_portfolio1_idx` (`portfolio_id`)
KEY `fk_portfolio_v2_usage_usage1_idx` (`usage_id`)
CONSTRAINT `fk_portfolio_v2_usage_portfolio1_idx` FOREIGN KEY (`portfolio_id`) REFERENCES `portfolio` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
CONSTRAINT `fk_portfolio_v2_usage_usage1_idx` FOREIGN KEY (`usage_id`) REFERENCES `usages` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
portfolio_visibility
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
portfolio_id | varchar(36) | no | | |
element | varchar(64) | no | | |
hidden | bit(1) | no | b'0' | |
created_date | timestamp | yes | NULL | |
modified_date | timestamp | yes | NULL | |
PRIMARY KEY (`id`)
UNIQUE KEY `uq_portfolio_visibility` (`portfolio_id`,`element`)
CONSTRAINT `fk_portfolio_visibility_portfolio` FOREIGN KEY (`portfolio_id`) REFERENCES `portfolio` (`id`)
privilege
| Column | Type | Null | Default | Notes |
|---|
id | bigint | no | | auto increment |
creation_date | date | yes | NULL | |
modified_date | date | yes | NULL | |
name | varchar(255) | yes | NULL | |
PRIMARY KEY (`id`)
qa_activity_log
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
question_id | varchar(36) | no | | |
activity_type | varchar(50) | no | | |
description | text | yes | | |
old_value | varchar(255) | yes | NULL | |
new_value | varchar(255) | yes | NULL | |
performed_by | bigint | no | | |
performed_at | timestamp | no | CURRENT_TIMESTAMP | |
metadata | text | yes | | |
PRIMARY KEY (`id`)
UNIQUE KEY `id` (`id`)
KEY `fk_qa_activity_log_performed_by` (`performed_by`)
KEY `idx_qa_activity_log_question_performed` (`question_id`,`performed_at`)
CONSTRAINT `fk_qa_activity_log_performed_by` FOREIGN KEY (`performed_by`) REFERENCES `user` (`id`) ON DELETE CASCADE
CONSTRAINT `fk_qa_activity_log_question_id` FOREIGN KEY (`question_id`) REFERENCES `qa_questions` (`id`) ON DELETE CASCADE
CONSTRAINT `chk_qa_activity_log_type` CHECK ((`activity_type` in (_utf8mb4'created',_utf8mb4'assigned',_utf8mb4'replied',_utf8mb4'status_changed',_utf8mb4'participant_added',_utf8mb4'participant_removed',_utf8mb4'attachment_added',_utf8mb4'priority_changed',_utf8mb4'closed',_utf8mb4'reopened')))
qa_attachments
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
question_id | varchar(36) | yes | NULL | |
reply_id | varchar(36) | yes | NULL | |
original_filename | varchar(512) | no | | |
s3_stored_filename | varchar(512) | no | | |
s3_file_path | varchar(512) | no | | |
file_size | bigint | no | | |
mime_type | varchar(128) | yes | NULL | |
uploaded_by | bigint | no | | |
uploaded_at | timestamp | no | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
UNIQUE KEY `id` (`id`)
KEY `fk_qa_attachments_uploaded_by` (`uploaded_by`)
KEY `idx_qa_attachments_question_id` (`question_id`)
KEY `idx_qa_attachments_reply_id` (`reply_id`)
CONSTRAINT `fk_qa_attachments_question_id` FOREIGN KEY (`question_id`) REFERENCES `qa_questions` (`id`) ON DELETE CASCADE
CONSTRAINT `fk_qa_attachments_reply_id` FOREIGN KEY (`reply_id`) REFERENCES `qa_replies` (`id`) ON DELETE CASCADE
CONSTRAINT `fk_qa_attachments_uploaded_by` FOREIGN KEY (`uploaded_by`) REFERENCES `user` (`id`) ON DELETE CASCADE
qa_notifications
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
user_id | bigint | no | | |
question_id | varchar(36) | no | | |
notification_type | varchar(30) | no | | |
title | varchar(255) | no | | |
message | text | no | | |
is_read | bit(1) | no | b'0' | |
read_at | timestamp | yes | NULL | |
created_at | timestamp | no | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
UNIQUE KEY `id` (`id`)
KEY `fk_qa_notifications_question_id` (`question_id`)
KEY `idx_qa_notifications_user_read_created` (`user_id`,`is_read`,`created_at`)
CONSTRAINT `fk_qa_notifications_question_id` FOREIGN KEY (`question_id`) REFERENCES `qa_questions` (`id`) ON DELETE CASCADE
CONSTRAINT `fk_qa_notifications_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
CONSTRAINT `chk_qa_notifications_type` CHECK ((`notification_type` in (_utf8mb4'question_assigned',_utf8mb4'question_submitted',_utf8mb4'new_reply',_utf8mb4'status_changed')))
qa_participants
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
question_id | varchar(36) | no | | |
user_id | bigint | no | | |
access_level | varchar(20) | no | 'questioner' | |
granted_by | bigint | no | | |
granted_at | timestamp | no | CURRENT_TIMESTAMP | |
is_active | bit(1) | no | b'1' | |
revoked_at | timestamp | yes | NULL | |
revoked_by | bigint | yes | NULL | |
PRIMARY KEY (`id`)
UNIQUE KEY `id` (`id`)
KEY `fk_qa_participants_granted_by` (`granted_by`)
KEY `fk_qa_participants_revoked_by` (`revoked_by`)
KEY `idx_qa_participants_question_id` (`question_id`)
KEY `idx_qa_participants_user_id` (`user_id`)
KEY `idx_qa_participants_user_active` (`user_id`,`is_active`)
CONSTRAINT `fk_qa_participants_granted_by` FOREIGN KEY (`granted_by`) REFERENCES `user` (`id`) ON DELETE CASCADE
CONSTRAINT `fk_qa_participants_question_id` FOREIGN KEY (`question_id`) REFERENCES `qa_questions` (`id`) ON DELETE CASCADE
CONSTRAINT `fk_qa_participants_revoked_by` FOREIGN KEY (`revoked_by`) REFERENCES `user` (`id`) ON DELETE SET NULL
CONSTRAINT `fk_qa_participants_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
CONSTRAINT `chk_qa_participants_access_level` CHECK ((`access_level` in (_utf8mb3'questioner',_utf8mb3'responder')))
qa_question_assignments
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
question_id | varchar(36) | no | | |
assigned_to | bigint | no | | |
assigned_by | bigint | no | | |
assigned_at | timestamp | no | CURRENT_TIMESTAMP | |
assignment_status | varchar(20) | no | 'active' | |
completed_at | timestamp | yes | NULL | |
PRIMARY KEY (`id`)
KEY `fk_qa_assignments_assigned_by` (`assigned_by`)
KEY `idx_qa_question_assignments_question_id` (`question_id`)
KEY `idx_qa_question_assignments_assigned_to` (`assigned_to`)
KEY `idx_qa_assignments_assigned_to_status` (`assigned_to`,`assignment_status`)
CONSTRAINT `fk_qa_assignments_assigned_by` FOREIGN KEY (`assigned_by`) REFERENCES `user` (`id`) ON DELETE CASCADE
CONSTRAINT `fk_qa_assignments_assigned_to` FOREIGN KEY (`assigned_to`) REFERENCES `user` (`id`) ON DELETE CASCADE
CONSTRAINT `fk_qa_assignments_question_id` FOREIGN KEY (`question_id`) REFERENCES `qa_questions` (`id`) ON DELETE CASCADE
CONSTRAINT `chk_qa_assignments_status` CHECK ((`assignment_status` in (_utf8mb4'active',_utf8mb4'completed',_utf8mb4'reassigned')))
qa_questions
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
question_number | varchar(20) | no | | |
subject | text | no | | |
question_text | text | no | | |
submitted_by | bigint | no | | |
submitted_at | timestamp | no | CURRENT_TIMESTAMP | |
portfolio_id | varchar(36) | yes | NULL | |
is_public | bit(1) | no | b'1' | |
status | varchar(20) | yes | 'in_progress' | |
priority | varchar(10) | no | 'medium' | |
assigned_by | bigint | yes | NULL | |
assigned_at | timestamp | yes | NULL | |
closed_at | timestamp | yes | NULL | |
closed_by | bigint | yes | NULL | |
created_at | timestamp | no | CURRENT_TIMESTAMP | |
updated_at | timestamp | no | CURRENT_TIMESTAMP | |
vdr_node_id | varchar(36) | yes | NULL | |
PRIMARY KEY (`id`)
UNIQUE KEY `id` (`id`)
UNIQUE KEY `question_number` (`question_number`)
KEY `fk_qa_questions_assigned_by` (`assigned_by`)
KEY `fk_qa_questions_closed_by` (`closed_by`)
KEY `idx_qa_questions_portfolio_status` (`portfolio_id`,`status`)
KEY `idx_qa_questions_submitted_by` (`submitted_by`)
KEY `idx_qa_questions_portfolio_id` (`portfolio_id`)
KEY `idx_qa_questions_status` (`status`)
KEY `idx_qa_questions_submitted_at` (`submitted_at`)
KEY `idx_qa_questions_question_number` (`question_number`)
CONSTRAINT `fk_qa_questions_assigned_by` FOREIGN KEY (`assigned_by`) REFERENCES `user` (`id`) ON DELETE SET NULL
CONSTRAINT `fk_qa_questions_closed_by` FOREIGN KEY (`closed_by`) REFERENCES `user` (`id`) ON DELETE SET NULL
CONSTRAINT `fk_qa_questions_portfolio_id` FOREIGN KEY (`portfolio_id`) REFERENCES `portfolio` (`id`) ON DELETE CASCADE
CONSTRAINT `fk_qa_questions_submitted_by` FOREIGN KEY (`submitted_by`) REFERENCES `user` (`id`) ON DELETE CASCADE
CONSTRAINT `chk_qa_questions_priority` CHECK ((`priority` in (_utf8mb3'low',_utf8mb3'medium',_utf8mb3'high',_utf8mb3'urgent')))
CONSTRAINT `chk_qa_questions_status` CHECK ((`status` in (_utf8mb3'in_progress',_utf8mb3'closed',_utf8mb3'reopened')))
qa_replies
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
question_id | varchar(36) | no | | |
parent_reply_id | varchar(36) | yes | NULL | |
reply_text | text | no | | |
replied_by | bigint | no | | |
replied_at | timestamp | no | CURRENT_TIMESTAMP | |
created_at | timestamp | no | CURRENT_TIMESTAMP | |
updated_at | timestamp | no | CURRENT_TIMESTAMP | |
status | varchar(20) | no | 'pending_approval' | |
recipient_id | bigint | yes | NULL | |
reviewed_by | bigint | yes | NULL | |
reviewed_at | datetime | yes | NULL | |
requires_approval | bit(1) | no | b'1' | |
PRIMARY KEY (`id`)
KEY `idx_qa_replies_question_id` (`question_id`)
KEY `idx_qa_replies_parent_reply_id` (`parent_reply_id`)
KEY `idx_qa_replies_replied_by` (`replied_by`)
KEY `idx_qa_replies_question_created` (`question_id`,`created_at`)
KEY `fk_qa_replies_approved_by` (`reviewed_by`)
KEY `idx_qa_replies_status` (`status`)
KEY `idx_qa_replies_recipient_id` (`recipient_id`)
CONSTRAINT `fk_qa_replies_approved_by` FOREIGN KEY (`reviewed_by`) REFERENCES `user` (`id`) ON DELETE SET NULL
CONSTRAINT `fk_qa_replies_parent_reply_id` FOREIGN KEY (`parent_reply_id`) REFERENCES `qa_replies` (`id`) ON DELETE CASCADE
CONSTRAINT `fk_qa_replies_question_id` FOREIGN KEY (`question_id`) REFERENCES `qa_questions` (`id`) ON DELETE CASCADE
CONSTRAINT `fk_qa_replies_recipient_id` FOREIGN KEY (`recipient_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
CONSTRAINT `fk_qa_replies_replied_by` FOREIGN KEY (`replied_by`) REFERENCES `user` (`id`) ON DELETE CASCADE
CONSTRAINT `chk_qa_replies_status` CHECK ((`status` in (_utf8mb3'pending_approval',_utf8mb3'approved',_utf8mb3'rejected')))
rag_conversation
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
session_id | varchar(255) | yes | NULL | |
title | varchar(255) | yes | NULL | |
user_id | varchar(36) | yes | NULL | |
created_at | datetime(6) | no | CURRENT_TIMESTAMP(6) | |
updated_at | datetime(6) | no | CURRENT_TIMESTAMP(6) | |
PRIMARY KEY (`id`)
rag_document_index_state
| Column | Type | Null | Default | Notes |
|---|
vdr_node_id | varchar(36) | no | | |
portfolio_id | varchar(36) | no | | |
state | varchar(20) | no | | |
last_submitted_at | datetime(6) | yes | NULL | |
last_indexed_at | datetime(6) | yes | NULL | |
last_error | mediumtext | yes | | |
PRIMARY KEY (`vdr_node_id`)
KEY `idx_rdis_portfolio_state` (`portfolio_id`,`state`)
KEY `idx_rdis_state_submitted` (`state`,`last_submitted_at`)
rag_message
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
conversation_id | varchar(36) | no | | |
role | varchar(16) | no | | |
content | mediumtext | no | | |
citations_json | mediumtext | yes | | |
created_at | datetime(6) | no | CURRENT_TIMESTAMP(6) | |
PRIMARY KEY (`id`)
KEY `idx_rag_message_conversation_created` (`conversation_id`,`created_at`)
CONSTRAINT `fk_rag_message_conversation` FOREIGN KEY (`conversation_id`) REFERENCES `rag_conversation` (`id`)
registration_status
| Column | Type | Null | Default | Notes |
|---|
status | varchar(255) | no | | |
creation_date | date | yes | NULL | |
modified_date | date | yes | NULL | |
PRIMARY KEY (`status`)
role
| Column | Type | Null | Default | Notes |
|---|
id | bigint | no | | auto increment |
creation_date | date | yes | NULL | |
modified_date | date | yes | NULL | |
name | varchar(255) | yes | NULL | |
PRIMARY KEY (`id`)
roles_privileges
| Column | Type | Null | Default | Notes |
|---|
role_id | bigint | no | | |
privilege_id | bigint | no | | |
KEY `FK5yjwxw2gvfyu76j3rgqwo685u` (`privilege_id`)
KEY `FK9h2vewsqh8luhfq71xokh4who` (`role_id`)
CONSTRAINT `FK5yjwxw2gvfyu76j3rgqwo685u` FOREIGN KEY (`privilege_id`) REFERENCES `privilege` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
CONSTRAINT `FK9h2vewsqh8luhfq71xokh4who` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
typeorm_migrations
| Column | Type | Null | Default | Notes |
|---|
id | int | no | | auto increment |
timestamp | bigint | no | | |
name | varchar(255) | no | | |
PRIMARY KEY (`id`)
usages
| Column | Type | Null | Default | Notes |
|---|
id | int unsigned | no | | auto increment |
description | text | no | | |
PRIMARY KEY (`id`)
user
| Column | Type | Null | Default | Notes |
|---|
id | bigint | no | | auto increment |
creation_date | date | yes | NULL | |
email | varchar(255) | no | | |
enabled | bit(1) | no | | |
last_name | varchar(255) | no | | |
lei | varchar(255) | yes | NULL | |
modified_date | date | yes | NULL | |
professional_investor | bit(1) | yes | b'0' | |
name | varchar(255) | no | | |
password | varchar(255) | yes | NULL | |
phone_number | varchar(255) | yes | NULL | |
token_expired | bit(1) | no | | |
username | varchar(255) | yes | NULL | |
registration_status | varchar(255) | yes | NULL | |
auth0_id | varchar(255) | yes | NULL | |
external | bit(1) | yes | b'0' | |
company_name | varchar(255) | no | 'Company name' | |
company_address_id | int unsigned | yes | NULL | |
role | varchar(255) | yes | NULL | |
language_preference | varchar(10) | yes | NULL | |
terms_accepted | bit(1) | no | b'0' | |
terms_accepted_at | datetime | yes | NULL | |
PRIMARY KEY (`id`)
UNIQUE KEY `email` (`email`)
UNIQUE KEY `username` (`username`)
KEY `FKdl9a80x56scyl0esdeh0pv7q5` (`registration_status`)
KEY `fk_user_company_address1` (`company_address_id`)
CONSTRAINT `fk_user_company_address1` FOREIGN KEY (`company_address_id`) REFERENCES `address` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
CONSTRAINT `FKdl9a80x56scyl0esdeh0pv7q5` FOREIGN KEY (`registration_status`) REFERENCES `registration_status` (`status`) ON DELETE RESTRICT ON UPDATE RESTRICT
user_audit_log
| Column | Type | Null | Default | Notes |
|---|
id | bigint | no | | auto increment |
user_id | bigint | no | | |
action | varchar(20) | no | | |
actor_email | varchar(255) | no | | |
reason | text | yes | | |
created_at | datetime | no | | |
PRIMARY KEY (`id`)
KEY `idx_user_audit_log_user_id` (`user_id`)
CONSTRAINT `FK_user_audit_log_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
user_invite
| Column | Type | Null | Default | Notes |
|---|
id | bigint | no | | auto increment |
email | varchar(255) | no | | |
name | varchar(255) | yes | NULL | |
last_name | varchar(255) | yes | NULL | |
company_name | varchar(255) | yes | NULL | |
role | varchar(255) | yes | NULL | |
language_preference | varchar(10) | yes | NULL | |
personal_note | text | yes | | |
invited_by_email | varchar(255) | no | | |
auth0_id | varchar(255) | no | | |
invited_at | datetime | no | | |
last_sent_at | datetime | no | | |
phone_number | varchar(50) | yes | NULL | |
company_address_street | varchar(255) | yes | NULL | |
company_address_number | varchar(50) | yes | NULL | |
company_address_postal_code | varchar(20) | yes | NULL | |
company_address_city | varchar(255) | yes | NULL | |
company_address_country | varchar(255) | yes | NULL | |
accepted_at | datetime | yes | NULL | |
lei | varchar(20) | yes | NULL | |
PRIMARY KEY (`id`)
UNIQUE KEY `email` (`email`)
vdr_audit_log
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
action | varchar(20) | no | | |
success | bit(1) | no | | |
error_message | text | yes | | |
node_id | varchar(36) | yes | NULL | |
node_type | varchar(10) | yes | NULL | |
node_name | varchar(512) | yes | NULL | |
node_path | text | yes | | |
user_id | bigint | no | | |
user_email | varchar(255) | yes | NULL | |
ip_address | varchar(45) | yes | NULL | |
user_agent | varchar(512) | yes | NULL | |
file_size | bigint | yes | NULL | |
checksum | varchar(64) | yes | NULL | |
watermark_applied | tinyint(1) | yes | NULL | |
duration_ms | bigint | yes | NULL | |
replaced_node_id | varchar(36) | yes | NULL | |
replaced_node_name | varchar(512) | yes | NULL | |
created_at | timestamp | no | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
vdr_download_job
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
portfolio_id | varchar(36) | no | | |
user_id | bigint | no | | |
user_email | varchar(255) | no | | |
node_ids | text | no | | |
status | enum('PENDING','IN_PROGRESS','COMPLETE','FAILED') | no | 'PENDING' | |
output_s3_key | varchar(1024) | yes | NULL | |
error_message | text | yes | | |
total_files | int | yes | NULL | |
processed_files | int | no | '0' | |
created_at | datetime | no | | |
updated_at | datetime | no | | |
expires_at | datetime | no | | |
PRIMARY KEY (`id`)
KEY `idx_vdr_download_job_user` (`user_id`,`created_at` DESC)
KEY `idx_vdr_download_job_expires` (`expires_at`)
vdr_node
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
parent_id | varchar(36) | yes | NULL | |
portfolio_id | varchar(36) | no | | |
node_type | varchar(10) | no | | |
name | varchar(512) | no | | |
path | text | no | | |
depth | int | no | '0' | |
s3_key | varchar(1024) | yes | NULL | |
file_size | bigint | yes | NULL | |
content_type | varchar(128) | yes | NULL | |
checksum_sha256 | varchar(64) | yes | NULL | |
s3_etag | varchar(64) | yes | NULL | |
s3_version_id | varchar(128) | yes | NULL | |
status | varchar(20) | no | 'COMPLETED' | |
status_message | varchar(512) | yes | NULL | |
access_type | varchar(20) | no | 'STANDARD' | |
created_by | bigint | no | | |
created_at | timestamp | no | CURRENT_TIMESTAMP | |
deleted_at | timestamp | yes | NULL | |
deleted_by | bigint | yes | NULL | |
active_flag | tinyint | yes | | |
PRIMARY KEY (`id`)
UNIQUE KEY `idx_vdr_node_parent_name_active` (`parent_id`,`name`,`active_flag`)
KEY `idx_vdr_node_portfolio_name` (`portfolio_id`,`name`)
KEY `idx_vdr_node_portfolio` (`portfolio_id`)
KEY `idx_vdr_node_status` (`status`)
KEY `idx_vdr_node_path` (`path`(255))
KEY `idx_vdr_node_s3_key` (`s3_key`(255))
KEY `idx_vdr_node_deleted` (`deleted_at`)
CONSTRAINT `fk_vdr_node_parent` FOREIGN KEY (`parent_id`) REFERENCES `vdr_node` (`id`) ON DELETE CASCADE
vdr_node_access
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
node_id | varchar(36) | no | | |
user_id | bigint | yes | NULL | |
user_group_id | bigint | yes | NULL | |
granted_by | bigint | no | | |
granted_at | timestamp | no | CURRENT_TIMESTAMP | |
PRIMARY KEY (`id`)
UNIQUE KEY `idx_vdr_access_node_user` (`node_id`,`user_id`)
UNIQUE KEY `idx_vdr_access_node_group` (`node_id`,`user_group_id`)
KEY `idx_vdr_access_group` (`user_group_id`)
CONSTRAINT `fk_vdr_access_node` FOREIGN KEY (`node_id`) REFERENCES `vdr_node` (`id`) ON DELETE CASCADE
CONSTRAINT `chk_user_or_group` CHECK (((`user_id` is not null) or (`user_group_id` is not null)))
vdr_sqs_message
| Column | Type | Null | Default | Notes |
|---|
message_id | varchar(128) | no | | |
queue_name | varchar(128) | no | | |
processed_at | timestamp | no | CURRENT_TIMESTAMP | |
PRIMARY KEY (`message_id`)
vdr_upload_session
| Column | Type | Null | Default | Notes |
|---|
id | varchar(36) | no | | |
batch_id | varchar(36) | yes | NULL | |
node_id | varchar(36) | no | | |
user_id | bigint | no | | |
s3_upload_id | varchar(256) | yes | NULL | |
s3_key | varchar(1024) | no | | |
file_size | bigint | no | | |
uploaded_bytes | bigint | no | '0' | |
parts_total | int | yes | NULL | |
parts_completed | int | no | '0' | |
status | varchar(20) | no | 'INITIATED' | |
error_message | varchar(512) | yes | NULL | |
created_at | timestamp | no | CURRENT_TIMESTAMP | |
updated_at | timestamp | no | CURRENT_TIMESTAMP | |
expires_at | timestamp | no | | |
PRIMARY KEY (`id`)
verification_token
| Column | Type | Null | Default | Notes |
|---|
id | bigint | no | | auto increment |
creation_date | date | yes | NULL | |
expiry_date | date | yes | NULL | |
modified_date | date | yes | NULL | |
token | varchar(255) | yes | NULL | |
user_id | bigint | no | | |
PRIMARY KEY (`id`)
KEY `FKrdn0mss276m9jdobfhhn2qogw` (`user_id`)
CONSTRAINT `FKrdn0mss276m9jdobfhhn2qogw` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT