Research Portal — Schema & ERD

Entity Relationship Diagram

Foreign Key Relationships

Constraint	Table	Column	References	Referenced Column
`fk_researcher_type_i18n`	`research_researcher_type_i18n`	id	`research_researcher_type`	id
`research_room_manifest_ibfk_1`	`research_room_manifest`	room_id	`research_room`	id
`research_room_participant_ibfk_1`	`research_room_participant`	room_id	`research_room`	id
`researcher_submission_file_ibfk_1`	`researcher_submission_file`	item_id	`researcher_submission_item`	id
`researcher_submission_item_ibfk_2`	`researcher_submission_item`	parent_item_id	`researcher_submission_item`	id
`researcher_submission_item_ibfk_1`	`researcher_submission_item`	submission_id	`researcher_submission`	id
`researcher_submission_review_ibfk_1`	`researcher_submission_review`	submission_id	`researcher_submission`	id

`research_access_decision`

7 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`policy_id`	int	NOT NULL	-
FK	`researcher_id`	int	NOT NULL	-
	`action_requested`	varchar(50)	NOT NULL	-
FK	`decision`	varchar(26)	NOT NULL	-		permitted, denied
	`rationale`	text	NULL	-
	`evaluated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_activity`

31 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`activity_type`	varchar(86)	NOT NULL	-		class, tour, exhibit, loan, conservation, photography, filming, event, meeting, other
	`title`	varchar(255)	NOT NULL	-
	`description`	text	NULL	-
FK	`organizer_id`	int	NULL	-		Researcher ID if registered
	`organizer_name`	varchar(255)	NULL	-
	`organizer_email`	varchar(255)	NULL	-
	`organizer_phone`	varchar(50)	NULL	-
	`organization`	varchar(255)	NULL	-
	`expected_attendees`	int	NULL	-
FK	`reading_room_id`	int	NULL	-
FK	`start_date`	date	NOT NULL	-
	`end_date`	date	NULL	-
	`start_time`	time	NULL	-
	`end_time`	time	NULL	-
	`recurring`	tinyint(1)	NULL	`0`
	`recurrence_pattern`	json	NULL	-
	`setup_requirements`	text	NULL	-
	`av_requirements`	text	NULL	-
	`catering_notes`	text	NULL	-
	`special_instructions`	text	NULL	-
FK	`status`	varchar(71)	NULL	`requested`		requested, tentative, confirmed, in_progress, completed, cancelled
	`confirmed_by`	int	NULL	-
	`confirmed_at`	datetime	NULL	-
	`cancelled_by`	int	NULL	-
	`cancelled_at`	datetime	NULL	-
	`cancellation_reason`	text	NULL	-
	`notes`	text	NULL	-
	`admin_notes`	text	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_activity_log`

12 columns 4 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	bigint	NOT NULL	-	auto_increment
FK	`researcher_id`	int	NOT NULL	-
FK	`project_id`	int	NULL	-
FK	`activity_type`	varchar(348)	NOT NULL	-		view, search, download, cite, annotate, collect, book, request, export, share, login, logout, snapshot_created, snapshot_compared, assertion_created, assertion_verified, assertion_disputed, extraction_queued, extraction_completed, validation_accepted, validation_rejected, hypothesis_created, hypothesis_updated, policy_evaluated, doi_minted, create, clipboard_add
FK	`entity_type`	varchar(50)	NULL	-
	`entity_id`	int	NULL	-
	`entity_title`	varchar(500)	NULL	-
	`details`	json	NULL	-
	`session_id`	varchar(64)	NULL	-
	`ip_address`	varchar(45)	NULL	-
	`user_agent`	varchar(500)	NULL	-
FK	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_activity_material`

18 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`activity_id`	int	NOT NULL	-
FK	`object_id`	int	NOT NULL	-
	`purpose`	text	NULL	-
	`handling_notes`	text	NULL	-
	`display_notes`	text	NULL	-		For exhibits
	`insurance_value`	decimal(15,2)	NULL	-
	`loan_agreement_signed`	tinyint(1)	NULL	`0`
	`condition_before`	text	NULL	-
	`condition_after`	text	NULL	-
FK	`status`	varchar(71)	NULL	`requested`		requested, approved, rejected, retrieved, in_use, returned, damaged
	`approved_by`	int	NULL	-
	`approved_at`	datetime	NULL	-
	`retrieved_at`	datetime	NULL	-
	`returned_at`	datetime	NULL	-
	`notes`	text	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_activity_participant`

17 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`activity_id`	int	NOT NULL	-
FK	`researcher_id`	int	NULL	-		If registered researcher
	`name`	varchar(255)	NULL	-
	`email`	varchar(255)	NULL	-
	`phone`	varchar(50)	NULL	-
	`organization`	varchar(255)	NULL	-
FK	`role`	varchar(78)	NULL	`visitor`		organizer, instructor, presenter, student, visitor, assistant, staff, other
	`dietary_requirements`	text	NULL	-
	`accessibility_needs`	text	NULL	-
FK	`registration_status`	varchar(63)	NULL	`pending`		pending, confirmed, waitlist, cancelled, attended, no_show
	`registered_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`confirmed_at`	datetime	NULL	-
	`checked_in_at`	datetime	NULL	-
	`checked_out_at`	datetime	NULL	-
	`feedback`	text	NULL	-
	`notes`	text	NULL	-

`research_annotation`

19 columns 5 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`researcher_id`	int	NOT NULL	-
	`project_id`	int	NULL	-
FK	`object_id`	int	NULL	-
	`entity_type`	varchar(50)	NULL	`information_object`
FK	`collection_id`	int	NULL	-
	`digital_object_id`	int	NULL	-
	`annotation_type`	varchar(51)	NULL	`note`		note, highlight, bookmark, tag, transcription
FK	`title`	varchar(255)	NULL	-
	`content`	text	NULL	-
	`content_format`	varchar(20)	NULL	`text`		text, html
	`target_selector`	text	NULL	-
	`canvas_id`	varchar(500)	NULL	-
	`iiif_annotation_id`	varchar(255)	NULL	-
	`tags`	varchar(500)	NULL	-
	`is_private`	tinyint(1)	NULL	`1`
	`visibility`	varchar(31)	NULL	`private`		private, shared, public
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_annotation_target`

8 columns 6 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`annotation_id`	int	NOT NULL	-
FK	`source_type`	varchar(50)	NOT NULL	-
	`source_id`	int	NULL	-
FK	`selector_type`	varchar(97)	NULL	-		TextQuoteSelector, FragmentSelector, SvgSelector, PointSelector, RangeSelector, TimeSelector
	`selector_json`	json	NULL	-
	`source_url`	varchar(1000)	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_annotation_v2`

11 columns 6 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`researcher_id`	int	NOT NULL	-
FK	`project_id`	int	NULL	-
FK	`motivation`	varchar(84)	NOT NULL	`commenting`		commenting, describing, classifying, linking, questioning, tagging, highlighting
	`body_json`	json	NULL	-
	`creator_json`	json	NULL	-
	`generated_json`	json	NULL	-
FK	`status`	varchar(33)	NULL	`active`		active, archived, deleted
FK	`visibility`	varchar(31)	NULL	`private`		private, shared, public
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_api_key`

11 columns 0 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int	NOT NULL	-	auto_increment
FK	`researcher_id`	int	NOT NULL	-
	`name`	varchar(100)	NOT NULL	-
UQ	`api_key`	varchar(64)	NOT NULL	-
	`permissions`	json	NULL	-
	`rate_limit`	int	NULL	`1000`
	`last_used_at`	datetime	NULL	-
	`request_count`	int	NULL	`0`
	`expires_at`	datetime	NULL	-
FK	`is_active`	tinyint(1)	NULL	`1`
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_api_log`

11 columns 0 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	bigint	NOT NULL	-	auto_increment
FK	`api_key_id`	int	NULL	-
FK	`researcher_id`	int	NULL	-
FK	`endpoint`	varchar(255)	NOT NULL	-
	`method`	varchar(10)	NOT NULL	-
	`request_params`	json	NULL	-
	`response_code`	int	NULL	-
	`response_time_ms`	int	NULL	-
	`ip_address`	varchar(45)	NULL	-
	`user_agent`	varchar(500)	NULL	-
FK	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_assertion`

17 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`researcher_id`	int	NOT NULL	-
FK	`project_id`	int	NULL	-
FK	`subject_type`	varchar(50)	NOT NULL	-
	`subject_id`	int	NOT NULL	-
	`subject_label`	varchar(500)	NULL	-
FK	`predicate`	varchar(255)	NOT NULL	-
	`object_value`	text	NULL	-
	`object_type`	varchar(50)	NULL	-
	`object_id`	int	NULL	-
	`object_label`	varchar(500)	NULL	-
FK	`assertion_type`	varchar(67)	NOT NULL	-		biographical, chronological, spatial, relational, attributive
FK	`status`	varchar(46)	NULL	`proposed`		proposed, verified, disputed, retracted
	`confidence`	decimal(5,2)	NULL	-
	`version`	int	NULL	`1`
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_assertion_evidence`

9 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`assertion_id`	int	NOT NULL	-
FK	`source_type`	varchar(50)	NOT NULL	-
	`source_id`	int	NOT NULL	-
	`selector_json`	json	NULL	-
FK	`relationship`	varchar(26)	NOT NULL	-		supports, refutes
	`note`	text	NULL	-
	`added_by`	int	NOT NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_bibliography`

10 columns 2 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int	NOT NULL	-	auto_increment
FK	`researcher_id`	int	NOT NULL	-
FK	`project_id`	int	NULL	-
	`name`	varchar(255)	NOT NULL	-
	`description`	text	NULL	-
	`citation_style`	varchar(50)	NULL	`chicago`
	`is_public`	tinyint(1)	NULL	`0`
FK	`share_token`	varchar(64)	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_bibliography_entry`

25 columns 3 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`bibliography_id`	int	NOT NULL	-
FK	`object_id`	int	NULL	-
FK	`entry_type`	varchar(60)	NULL	`archival`		archival, book, article, chapter, thesis, website, other
	`csl_data`	json	NULL	-
	`title`	varchar(500)	NULL	-
	`authors`	text	NULL	-
	`date`	varchar(50)	NULL	-
	`publisher`	varchar(255)	NULL	-
	`container_title`	varchar(500)	NULL	-
	`volume`	varchar(50)	NULL	-
	`issue`	varchar(50)	NULL	-
	`pages`	varchar(50)	NULL	-
	`doi`	varchar(255)	NULL	-
	`url`	varchar(1000)	NULL	-
	`accessed_date`	date	NULL	-
	`archive_name`	varchar(255)	NULL	-
	`archive_location`	varchar(255)	NULL	-
	`collection_title`	varchar(500)	NULL	-
	`box`	varchar(50)	NULL	-
	`folder`	varchar(50)	NULL	-
	`notes`	text	NULL	-
	`sort_order`	int	NULL	`0`
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_booking`

22 columns 8 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`researcher_id`	int	NOT NULL	-
FK	`reading_room_id`	int	NOT NULL	-
	`project_id`	int	NULL	-
FK	`booking_date`	date	NOT NULL	-
	`start_time`	time	NOT NULL	-
	`end_time`	time	NOT NULL	-
	`purpose`	text	NULL	-
FK	`status`	varchar(55)	NULL	`pending`		pending, confirmed, cancelled, completed, no_show
	`confirmed_by`	int	NULL	-
	`confirmed_at`	datetime	NULL	-
	`cancelled_at`	datetime	NULL	-
	`cancellation_reason`	text	NULL	-
	`checked_in_at`	datetime	NULL	-
	`checked_out_at`	datetime	NULL	-
	`notes`	text	NULL	-
	`is_walk_in`	tinyint(1)	NULL	`0`
	`rules_acknowledged`	tinyint(1)	NULL	`0`
	`rules_acknowledged_at`	datetime	NULL	-
FK	`seat_id`	int	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_citation_log`

6 columns 11,112 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int	NOT NULL	-	auto_increment
FK	`researcher_id`	int	NULL	-
FK	`object_id`	int	NOT NULL	-
	`citation_style`	varchar(50)	NOT NULL	-
	`citation_text`	text	NOT NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_clipboard_project`

7 columns 4 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int	NOT NULL	-	auto_increment
FK	`researcher_id`	int	NOT NULL	-
FK	`project_id`	int	NOT NULL	-
	`object_id`	int	NOT NULL	-
	`is_pinned`	tinyint(1)	NULL	`0`
	`notes`	text	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_collection`

9 columns 12 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int	NOT NULL	-	auto_increment
FK	`researcher_id`	int	NOT NULL	-
	`project_id`	int	NULL	-
	`name`	varchar(255)	NOT NULL	-
	`description`	text	NULL	-
	`is_public`	tinyint(1)	NULL	`0`
FK	`share_token`	varchar(64)	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_collection_item`

11 columns 40 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int	NOT NULL	-	auto_increment
FK	`collection_id`	int	NOT NULL	-
FK	`object_id`	int	NOT NULL	-
	`object_type`	varchar(50)	NULL	`information_object`
	`culture`	varchar(10)	NULL	-
	`external_uri`	varchar(1000)	NULL	-
	`tags`	varchar(500)	NULL	-
	`reference_code`	varchar(255)	NULL	-
	`notes`	text	NULL	-
	`sort_order`	int	NULL	`0`
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_comment`

11 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`researcher_id`	int	NOT NULL	-
FK	`entity_type`	varchar(67)	NOT NULL	-		report, report_section, annotation, journal_entry, collection
	`entity_id`	int	NOT NULL	-
FK	`parent_id`	int	NULL	-
	`content`	text	NOT NULL	-
	`is_resolved`	tinyint(1)	NULL	`0`
	`resolved_by`	int	NULL	-
	`resolved_at`	datetime	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_custody_handoff`

17 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`material_request_id`	int	NOT NULL	-
FK	`handoff_type`	varchar(50)	NOT NULL	-		checkout\|checkin\|transfer\|return_to_storage\|condition_check
	`from_handler_id`	int	NULL	-		Staff/researcher releasing
	`to_handler_id`	int	NULL	-		Staff/researcher receiving
	`from_location`	varchar(255)	NULL	-
	`to_location`	varchar(255)	NULL	-
	`condition_at_handoff`	varchar(50)	NULL	-		excellent\|good\|fair\|poor\|critical
	`condition_notes`	text	NULL	-
	`signature_confirmed`	tinyint(1)	NOT NULL	`0`
	`confirmed_at`	datetime	NULL	-
	`confirmed_by`	int	NULL	-
	`barcode_scanned`	varchar(100)	NULL	-
FK	`spectrum_movement_id`	int	NULL	-		FK to spectrum_movement if created
	`notes`	text	NULL	-
FK	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`created_by`	int	NULL	-

`research_discussion`

13 columns 2 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int	NOT NULL	-	auto_increment
FK	`workspace_id`	int	NULL	-
FK	`project_id`	int	NULL	-
FK	`parent_id`	int	NULL	-
FK	`researcher_id`	int	NOT NULL	-
	`subject`	varchar(500)	NULL	-
	`content`	text	NOT NULL	-
	`is_pinned`	tinyint(1)	NULL	`0`
	`is_resolved`	tinyint(1)	NULL	`0`
	`resolved_by`	int	NULL	-
	`resolved_at`	datetime	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_document_template`

8 columns 0 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int	NOT NULL	-	auto_increment
	`name`	varchar(255)	NOT NULL	-
FK	`document_type`	varchar(100)	NOT NULL	-
	`description`	text	NULL	-
	`fields_json`	json	NULL	-
FK	`created_by`	int	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_entity_resolution`

15 columns 2 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`entity_a_type`	varchar(50)	NOT NULL	-
	`entity_a_id`	int	NOT NULL	-
FK	`entity_b_type`	varchar(50)	NOT NULL	-
	`entity_b_id`	int	NOT NULL	-
FK	`confidence`	decimal(5,4)	NULL	-
	`match_method`	varchar(100)	NULL	-
FK	`status`	varchar(36)	NULL	`proposed`		proposed, accepted, rejected
	`resolver_id`	int	NULL	-
	`resolved_at`	datetime	NULL	-
	`notes`	text	NULL	-
	`evidence_json`	json	NULL	-
	`relationship_type`	varchar(50)	NULL	`sameAs`
	`proposer_id`	int	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_equipment`

20 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`reading_room_id`	int	NOT NULL	-
	`name`	varchar(100)	NOT NULL	-
UQ	`code`	varchar(50)	NULL	-
FK	`equipment_type`	varchar(127)	NOT NULL	-		microfilm_reader, microfiche_reader, scanner, computer, magnifier, book_cradle, light_box, camera_stand, gloves, weights, other
	`brand`	varchar(100)	NULL	-
	`model`	varchar(100)	NULL	-
	`serial_number`	varchar(100)	NULL	-
	`description`	text	NULL	-
	`location`	varchar(255)	NULL	-		Where in the reading room
	`requires_training`	tinyint(1)	NULL	`0`
	`max_booking_hours`	int	NULL	`4`
	`booking_increment_minutes`	int	NULL	`30`
	`condition_status`	varchar(57)	NULL	`good`		excellent, good, fair, needs_repair, out_of_service
	`last_maintenance_date`	date	NULL	-
	`next_maintenance_date`	date	NULL	-
FK	`is_available`	tinyint(1)	NULL	`1`
	`notes`	text	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_equipment_booking`

18 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`booking_id`	int	NULL	-		Linked reading room booking
FK	`researcher_id`	int	NOT NULL	-
FK	`equipment_id`	int	NOT NULL	-
FK	`booking_date`	date	NOT NULL	-
	`start_time`	time	NOT NULL	-
	`end_time`	time	NOT NULL	-
	`purpose`	text	NULL	-
FK	`status`	varchar(52)	NULL	`reserved`		reserved, in_use, returned, cancelled, no_show
	`checked_out_at`	datetime	NULL	-
	`checked_out_by`	int	NULL	-
	`returned_at`	datetime	NULL	-
	`returned_by`	int	NULL	-
	`condition_on_return`	varchar(37)	NULL	-		excellent, good, fair, damaged
	`return_notes`	text	NULL	-
	`notes`	text	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_external_collaborator`

10 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`share_id`	int	NOT NULL	-
	`name`	varchar(255)	NOT NULL	-
FK	`email`	varchar(255)	NOT NULL	-
	`institution`	varchar(500)	NULL	-
	`orcid_id`	varchar(50)	NULL	-
UQ	`access_token`	varchar(64)	NOT NULL	-
	`role`	varchar(28)	NULL	`viewer`		viewer, contributor
	`last_accessed_at`	datetime	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_extraction_job`

13 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`project_id`	int	NOT NULL	-
FK	`collection_id`	int	NULL	-
FK	`researcher_id`	int	NOT NULL	-
FK	`extraction_type`	varchar(79)	NOT NULL	-		ocr, ner, summarize, translate, spellcheck, face_detection, form_extraction
	`parameters_json`	json	NULL	-
FK	`status`	varchar(41)	NULL	`queued`		queued, running, completed, failed
	`progress`	int	NULL	`0`
	`total_items`	int	NULL	`0`
	`processed_items`	int	NULL	`0`
	`error_log`	text	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`completed_at`	datetime	NULL	-

`research_extraction_result`

9 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`job_id`	int	NOT NULL	-
FK	`object_id`	int	NOT NULL	-
FK	`result_type`	varchar(66)	NOT NULL	-		entity, summary, translation, transcription, form_field, face
	`data_json`	json	NULL	-
FK	`confidence`	decimal(5,4)	NULL	-
	`model_version`	varchar(100)	NULL	-
	`input_hash`	varchar(64)	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_hypothesis`

9 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`project_id`	int	NOT NULL	-
FK	`researcher_id`	int	NOT NULL	-
	`statement`	text	NOT NULL	-
FK	`status`	varchar(44)	NULL	`proposed`		proposed, testing, supported, refuted
	`evidence_count`	int	NULL	`0`
	`tags`	varchar(500)	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_hypothesis_evidence`

9 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`hypothesis_id`	int	NOT NULL	-
FK	`source_type`	varchar(50)	NOT NULL	-
	`source_id`	int	NOT NULL	-
FK	`relationship`	varchar(34)	NOT NULL	-		supports, refutes, neutral
	`confidence`	decimal(5,2)	NULL	-
	`note`	text	NULL	-
	`added_by`	int	NOT NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_institution`

11 columns 0 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int	NOT NULL	-	auto_increment
	`name`	varchar(500)	NOT NULL	-
UQ	`code`	varchar(100)	NOT NULL	-
	`description`	text	NULL	-
	`url`	varchar(1000)	NULL	-
	`contact_name`	varchar(255)	NULL	-
	`contact_email`	varchar(255)	NULL	-
	`logo_path`	varchar(500)	NULL	-
	`is_active`	tinyint(1)	NULL	`1`
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_institutional_share`

13 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`project_id`	int	NOT NULL	-
FK	`institution_id`	int	NULL	-
UQ	`share_token`	varchar(64)	NOT NULL	-
	`share_type`	varchar(30)	NULL	`view`		view, contribute, full
	`shared_by`	int	NOT NULL	-
	`accepted_by`	int	NULL	-
FK	`status`	varchar(40)	NULL	`pending`		pending, active, revoked, expired
	`message`	text	NULL	-
	`permissions`	json	NULL	-
	`expires_at`	datetime	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_journal_entry`

15 columns 6 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`researcher_id`	int	NOT NULL	-
FK	`project_id`	int	NULL	-
FK	`entry_date`	date	NOT NULL	-
FK	`title`	varchar(500)	NULL	-
	`content`	text	NOT NULL	-
	`content_format`	varchar(20)	NULL	`html`		text, html
	`entry_type`	varchar(108)	NULL	`manual`		manual, auto_booking, auto_material, auto_annotation, auto_search, auto_collection, reflection, milestone
	`time_spent_minutes`	int	NULL	-
	`tags`	varchar(500)	NULL	-
	`is_private`	tinyint(1)	NULL	`1`
	`related_entity_type`	varchar(50)	NULL	-
	`related_entity_id`	int	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_map_point`

13 columns 0 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int	NOT NULL	-	auto_increment
FK	`project_id`	int	NOT NULL	-
FK	`researcher_id`	int	NOT NULL	-
	`label`	varchar(500)	NOT NULL	-
	`description`	text	NULL	-
FK	`latitude`	decimal(10,7)	NOT NULL	-
	`longitude`	decimal(10,7)	NOT NULL	-
	`place_name`	varchar(500)	NULL	-
	`date_valid_from`	date	NULL	-
	`date_valid_to`	date	NULL	-
FK	`source_type`	varchar(50)	NULL	-
	`source_id`	int	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_material_request`

38 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`booking_id`	int	NOT NULL	-
FK	`object_id`	int	NOT NULL	-
	`quantity`	int	NULL	`1`
	`notes`	text	NULL	-
	`request_type`	varchar(54)	NULL	`reading_room`		reading_room, reproduction, loan, remote_access
	`priority`	varchar(26)	NULL	`normal`		normal, high, rush
	`handling_instructions`	text	NULL	-
	`location_code`	varchar(100)	NULL	-
	`shelf_location`	varchar(255)	NULL	-
	`location_current`	varchar(255)	NULL	-		Current location: storage/transit/reading_room/returned
FK	`retrieval_scheduled_for`	datetime	NULL	-
FK	`queue_id`	int	NULL	-
	`box_number`	varchar(50)	NULL	-
	`folder_number`	varchar(50)	NULL	-
	`curatorial_approval_required`	tinyint(1)	NULL	`0`
	`curatorial_approved_by`	int	NULL	-
	`curatorial_approved_at`	datetime	NULL	-
	`paging_slip_printed`	tinyint(1)	NULL	`0`
	`call_slip_printed_at`	datetime	NULL	-
	`call_slip_printed_by`	int	NULL	-
	`status`	varchar(67)	NULL	`requested`		requested, retrieved, delivered, in_use, returned, unavailable
	`retrieved_by`	int	NULL	-
	`retrieved_at`	datetime	NULL	-
	`returned_at`	datetime	NULL	-
	`condition_notes`	text	NULL	-
	`sla_due_date`	date	NULL	-
	`assigned_to`	int	NULL	-
	`triage_status`	varchar(50)	NULL	-
	`triage_by`	int	NULL	-
	`checkout_confirmed_at`	datetime	NULL	-
	`checkout_confirmed_by`	int	NULL	-
	`return_condition`	varchar(50)	NULL	-
	`return_verified_by`	int	NULL	-
	`return_verified_at`	datetime	NULL	-
	`triage_at`	datetime	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_notification`

11 columns 2 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`researcher_id`	int	NOT NULL	-
	`type`	varchar(70)	NOT NULL	-		alert, invitation, comment, reply, system, reminder, collaboration
	`title`	varchar(500)	NOT NULL	-
	`message`	text	NULL	-
	`link`	varchar(1000)	NULL	-
	`related_entity_type`	varchar(50)	NULL	-
	`related_entity_id`	int	NULL	-
FK	`is_read`	tinyint(1)	NULL	`0`
	`read_at`	datetime	NULL	-
FK	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_notification_preference`

6 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`researcher_id`	int	NOT NULL	-
	`notification_type`	varchar(50)	NOT NULL	-
	`email_enabled`	tinyint(1)	NULL	`1`
	`in_app_enabled`	tinyint(1)	NULL	`1`
	`digest_frequency`	varchar(37)	NULL	`immediate`		immediate, daily, weekly, none

`research_password_reset`

5 columns 0 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int	NOT NULL	-	auto_increment
FK	`user_id`	int	NOT NULL	-
FK	`token`	varchar(64)	NOT NULL	-
FK	`expires_at`	datetime	NOT NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_peer_review`

9 columns 2 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`report_id`	int	NOT NULL	-
	`requested_by`	int	NOT NULL	-
FK	`reviewer_id`	int	NOT NULL	-
FK	`status`	varchar(48)	NULL	`pending`		pending, in_progress, completed, declined
	`feedback`	text	NULL	-
	`rating`	int	NULL	-
	`requested_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`completed_at`	datetime	NULL	-

`research_print_template`

20 columns 2 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
	`name`	varchar(100)	NOT NULL	-
UQ	`code`	varchar(50)	NOT NULL	-
FK	`template_type`	varchar(65)	NOT NULL	-		call_slip, paging_slip, receipt, badge, label, report, letter
	`description`	text	NULL	-
	`template_html`	longtext	NOT NULL	-
	`css_styles`	text	NULL	-
	`page_size`	varchar(55)	NULL	`a4`		a4, a5, letter, label_4x6, label_2x4, badge, custom
	`orientation`	varchar(28)	NULL	`portrait`		portrait, landscape
	`margin_top`	int	NULL	`10`
	`margin_right`	int	NULL	`10`
	`margin_bottom`	int	NULL	`10`
	`margin_left`	int	NULL	`10`
	`copies_default`	int	NULL	`1`
	`variables`	json	NULL	-		Available template variables
	`is_default`	tinyint(1)	NULL	`0`
FK	`is_active`	tinyint(1)	NULL	`1`
	`created_by`	int	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_project`

19 columns 3 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`owner_id`	int	NOT NULL	-
	`title`	varchar(500)	NOT NULL	-
	`description`	text	NULL	-
FK	`project_type`	varchar(103)	NULL	`personal`		thesis, dissertation, publication, exhibition, documentary, genealogy, institutional, personal, other
	`institution`	varchar(255)	NULL	-
	`supervisor`	varchar(255)	NULL	-
	`funding_source`	varchar(255)	NULL	-
	`grant_number`	varchar(100)	NULL	-
	`ethics_approval`	varchar(100)	NULL	-
	`start_date`	date	NULL	-
	`expected_end_date`	date	NULL	-
	`actual_end_date`	date	NULL	-
FK	`status`	varchar(52)	NULL	`planning`		planning, active, on_hold, completed, archived
	`visibility`	varchar(38)	NULL	`private`		private, collaborators, public
FK	`share_token`	varchar(64)	NULL	-
	`metadata`	json	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_project_collaborator`

10 columns 4 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`project_id`	int	NOT NULL	-
FK	`researcher_id`	int	NOT NULL	-
	`role`	varchar(41)	NULL	`contributor`		owner, editor, contributor, viewer
	`permissions`	json	NULL	-
	`invited_by`	int	NULL	-
	`invited_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`accepted_at`	datetime	NULL	-
FK	`status`	varchar(43)	NULL	`pending`		pending, accepted, declined, removed
	`notes`	text	NULL	-

`research_project_milestone`

10 columns 1 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`project_id`	int	NOT NULL	-
	`title`	varchar(255)	NOT NULL	-
	`description`	text	NULL	-
FK	`due_date`	date	NULL	-
	`completed_at`	datetime	NULL	-
	`completed_by`	int	NULL	-
FK	`status`	varchar(49)	NULL	`pending`		pending, in_progress, completed, cancelled
	`sort_order`	int	NULL	`0`
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_project_resource`

15 columns 2 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`project_id`	int	NOT NULL	-
FK	`resource_type`	varchar(92)	NOT NULL	-		collection, saved_search, annotation, bibliography, object, external_link, document, note
	`resource_id`	int	NULL	-
FK	`object_id`	int	NULL	-
	`external_url`	varchar(1000)	NULL	-
	`link_type`	varchar(73)	NULL	-		academic, archive, database, government, website, social_media, other
	`link_metadata`	json	NULL	-
	`title`	varchar(500)	NULL	-
	`description`	text	NULL	-
	`notes`	text	NULL	-
	`tags`	varchar(500)	NULL	-
	`added_by`	int	NULL	-
	`sort_order`	int	NULL	`0`
	`added_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_quality_metric`

7 columns 20 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`object_id`	int	NOT NULL	-
FK	`metric_type`	varchar(78)	NOT NULL	-		ocr_confidence, image_quality, digitisation_completeness, fixity_status
	`metric_value`	decimal(10,4)	NOT NULL	-
	`source_service`	varchar(100)	NULL	-
	`raw_data_json`	json	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_reading_room`

22 columns 2 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
	`name`	varchar(255)	NOT NULL	-
	`code`	varchar(20)	NULL	-
	`description`	text	NULL	-
	`amenities`	text	NULL	-
	`capacity`	int	NULL	`10`
	`has_seat_management`	tinyint(1)	NULL	`0`		Enable individual seat tracking
	`walk_ins_allowed`	tinyint(1)	NULL	`1`
	`walk_in_capacity`	int	NULL	`5`
	`floor_plan_path`	varchar(500)	NULL	-
	`location`	varchar(255)	NULL	-
	`operating_hours`	text	NULL	-
	`rules`	text	NULL	-
	`advance_booking_days`	int	NULL	`14`
	`max_booking_hours`	int	NULL	`4`
	`cancellation_hours`	int	NULL	`24`
	`is_active`	tinyint(1)	NULL	`1`
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP
	`opening_time`	time	NULL	`09:00:00`
	`closing_time`	time	NULL	`17:00:00`
	`days_open`	varchar(50)	NULL	`Mon,Tue,Wed,Thu,Fri`

`research_reading_room_seat`

17 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`reading_room_id`	int	NOT NULL	-
	`seat_number`	varchar(20)	NOT NULL	-
	`seat_label`	varchar(100)	NULL	-		Display name like "Table A - Seat 1"
FK	`seat_type`	varchar(69)	NULL	`standard`		standard, accessible, computer, microfilm, oversize, quiet, group
	`has_power`	tinyint(1)	NULL	`1`
	`has_lamp`	tinyint(1)	NULL	`1`
	`has_computer`	tinyint(1)	NULL	`0`
	`has_magnifier`	tinyint(1)	NULL	`0`
	`position_x`	int	NULL	-		For visual seat map
	`position_y`	int	NULL	-		For visual seat map
FK	`zone`	varchar(50)	NULL	-		Reading room zone/section
	`notes`	text	NULL	-
FK	`is_active`	tinyint(1)	NULL	`1`
	`sort_order`	int	NULL	`0`
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_report`

10 columns 1 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`researcher_id`	int	NOT NULL	-
FK	`project_id`	int	NULL	-
	`title`	varchar(500)	NOT NULL	-
	`template_type`	varchar(85)	NULL	`custom`		research_summary, genealogical, historical, source_analysis, finding_aid, custom
	`description`	text	NULL	-
FK	`status`	varchar(53)	NULL	`draft`		draft, in_progress, review, completed, archived
	`metadata`	json	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_report_section`

12 columns 7 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`report_id`	int	NOT NULL	-
	`section_type`	varchar(98)	NULL	`text`		title_page, toc, heading, text, bibliography, collection_list, annotation_list, timeline, custom
	`title`	varchar(500)	NULL	-
	`content`	text	NULL	-
	`content_format`	varchar(20)	NULL	`html`		text, html
	`bibliography_id`	int	NULL	-
	`collection_id`	int	NULL	-
	`settings`	json	NULL	-
FK	`sort_order`	int	NULL	`0`
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_report_template`

7 columns 6 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int	NOT NULL	-	auto_increment
	`name`	varchar(255)	NOT NULL	-
UQ	`code`	varchar(50)	NOT NULL	-
	`description`	text	NULL	-
	`sections_config`	json	NOT NULL	-
	`is_system`	tinyint(1)	NULL	`0`
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_reproduction_file`

11 columns 0 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int	NOT NULL	-	auto_increment
FK	`item_id`	int	NOT NULL	-
	`file_name`	varchar(255)	NOT NULL	-
	`file_path`	varchar(500)	NOT NULL	-
	`file_size`	bigint	NULL	-
	`mime_type`	varchar(100)	NULL	-
	`checksum`	varchar(64)	NULL	-
	`download_count`	int	NULL	`0`
	`download_expires_at`	datetime	NULL	-
FK	`download_token`	varchar(64)	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_reproduction_item`

17 columns 1 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`request_id`	int	NOT NULL	-
FK	`object_id`	int	NOT NULL	-
	`digital_object_id`	int	NULL	-
	`reproduction_type`	varchar(120)	NULL	`scan`		photocopy, scan, photograph, digital_copy, digital_scan, transcription, certification, certified_copy, microfilm, other
	`format`	varchar(50)	NULL	`PDF`
	`resolution`	varchar(50)	NULL	-
	`color_mode`	varchar(28)	NULL	`grayscale`		color, grayscale, bw
	`quantity`	int	NULL	`1`
	`page_range`	varchar(100)	NULL	-
	`special_instructions`	text	NULL	-
	`unit_price`	decimal(10,2)	NULL	-
	`total_price`	decimal(10,2)	NULL	-
FK	`status`	varchar(49)	NULL	`pending`		pending, in_progress, completed, cancelled
	`completed_at`	datetime	NULL	-
	`notes`	text	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_reproduction_request`

33 columns 7 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`researcher_id`	int	NOT NULL	-
UQ	`reference_number`	varchar(50)	NULL	-
	`purpose`	text	NULL	-
	`intended_use`	varchar(255)	NULL	`personal`
	`publication_details`	text	NULL	-
FK	`status`	varchar(87)	NULL	`draft`		draft, submitted, processing, awaiting_payment, in_production, completed, cancelled
	`estimated_cost`	decimal(10,2)	NULL	-
	`final_cost`	decimal(10,2)	NULL	-
	`currency`	varchar(3)	NULL	`ZAR`
	`payment_reference`	varchar(100)	NULL	-
	`payment_date`	date	NULL	-
	`payment_method`	varchar(50)	NULL	-
	`invoice_number`	varchar(50)	NULL	-
	`invoice_date`	date	NULL	-
	`delivery_method`	varchar(69)	NULL	`email`		email, download, post, collect, digital, pickup, courier, physical
	`delivery_address`	text	NULL	-
	`delivery_email`	varchar(255)	NULL	-
	`completed_at`	datetime	NULL	-
	`processed_by`	int	NULL	-
	`notes`	text	NULL	-
	`admin_notes`	text	NULL	-
	`triage_status`	varchar(50)	NULL	-
	`triage_by`	int	NULL	-
	`triage_at`	datetime	NULL	-
	`triage_notes`	text	NULL	-
	`sla_due_date`	date	NULL	-
	`assigned_to`	int	NULL	-
	`closed_at`	datetime	NULL	-
	`closed_by`	int	NULL	-
	`closure_reason`	varchar(100)	NULL	-
FK	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_request_correspondence`

11 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`request_id`	int	NOT NULL	-
	`request_type`	varchar(50)	NOT NULL	`material`		material\|reproduction
	`sender_type`	varchar(50)	NOT NULL	`staff`		staff\|researcher
FK	`sender_id`	int	NULL	-
	`subject`	varchar(255)	NULL	-
	`body`	text	NOT NULL	-
	`is_internal`	tinyint(1)	NOT NULL	`0`		Staff-only note, hidden from researcher
	`attachment_path`	varchar(500)	NULL	-
	`attachment_name`	varchar(255)	NULL	-
FK	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_request_queue`

19 columns 7 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
	`name`	varchar(100)	NOT NULL	-
UQ	`code`	varchar(50)	NOT NULL	-
	`description`	text	NULL	-
FK	`queue_type`	varchar(57)	NULL	`retrieval`		retrieval, paging, return, curatorial, reproduction
	`filter_status`	varchar(100)	NULL	-		Comma-separated statuses to include
	`filter_room_id`	int	NULL	-
	`filter_priority`	varchar(50)	NULL	-
	`sort_field`	varchar(50)	NULL	`created_at`
	`sort_direction`	varchar(20)	NULL	`ASC`		ASC, DESC
	`auto_assign`	tinyint(1)	NULL	`0`
	`assigned_staff_id`	int	NULL	-
	`color`	varchar(7)	NULL	`#3498db`		Queue display color
	`icon`	varchar(50)	NULL	`box`
	`is_default`	tinyint(1)	NULL	`0`
FK	`is_active`	tinyint(1)	NULL	`1`
	`sort_order`	int	NULL	`0`
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_request_status_history`

8 columns 1 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`request_id`	int	NOT NULL	-
	`request_type`	varchar(31)	NOT NULL	`material`		material, reproduction
	`old_status`	varchar(50)	NULL	-
	`new_status`	varchar(50)	NOT NULL	-
	`changed_by`	int	NULL	-
	`notes`	text	NULL	-
FK	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_researcher`

46 columns 20 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`user_id`	int	NOT NULL	-
	`title`	varchar(50)	NULL	-
	`first_name`	varchar(100)	NOT NULL	-
	`last_name`	varchar(100)	NOT NULL	-
FK	`email`	varchar(255)	NOT NULL	-
	`phone`	varchar(50)	NULL	-
	`affiliation_type`	varchar(63)	NULL	`independent`		academic, government, private, independent, student, other
	`institution`	varchar(255)	NULL	-
	`institution_id`	int	NULL	-
	`department`	varchar(255)	NULL	-
	`position`	varchar(255)	NULL	-
	`student_id`	varchar(100)	NULL	-
	`research_interests`	text	NULL	-
	`current_project`	text	NULL	-
	`orcid_id`	varchar(50)	NULL	-
	`orcid_verified`	tinyint(1)	NULL	`0`
	`orcid_access_token`	text	NULL	-
	`orcid_refresh_token`	text	NULL	-
	`orcid_token_expires_at`	datetime	NULL	-
	`researcher_id_wos`	varchar(50)	NULL	-
	`scopus_id`	varchar(50)	NULL	-
	`isni`	varchar(50)	NULL	-
	`researcher_type_id`	int	NULL	-
	`timezone`	varchar(50)	NULL	`Africa/Johannesburg`
	`preferred_language`	varchar(10)	NULL	`en`
	`api_key`	varchar(64)	NULL	-
	`api_key_expires_at`	datetime	NULL	-
	`id_type`	varchar(65)	NULL	-		passport, national_id, drivers_license, student_card, other
	`id_number`	varchar(100)	NULL	-
	`id_verified`	tinyint(1)	NULL	`0`
	`id_verified_by`	int	NULL	-
	`id_verified_at`	datetime	NULL	-
FK	`status`	varchar(53)	NULL	`pending`		pending, approved, suspended, expired, rejected
	`rejection_reason`	text	NULL	-
	`approved_by`	int	NULL	-
	`approved_at`	datetime	NULL	-
	`expires_at`	date	NULL	-
	`renewal_reminder_sent`	tinyint(1)	NULL	`0`
	`notes`	text	NULL	-
	`photo_path`	varchar(500)	NULL	-
	`card_number`	varchar(50)	NULL	-
	`card_barcode`	varchar(100)	NULL	-
	`card_issued_at`	datetime	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_researcher_audit`

23 columns 1 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
	`original_id`	int	NOT NULL	-
FK	`user_id`	int	NOT NULL	-
	`title`	varchar(50)	NULL	-
	`first_name`	varchar(100)	NOT NULL	-
	`last_name`	varchar(100)	NOT NULL	-
FK	`email`	varchar(255)	NOT NULL	-
	`phone`	varchar(50)	NULL	-
	`affiliation_type`	varchar(50)	NULL	-
	`institution`	varchar(255)	NULL	-
	`department`	varchar(255)	NULL	-
	`position`	varchar(255)	NULL	-
	`research_interests`	text	NULL	-
	`current_project`	text	NULL	-
	`orcid_id`	varchar(50)	NULL	-
	`id_type`	varchar(50)	NULL	-
	`id_number`	varchar(100)	NULL	-
	`status`	varchar(20)	NOT NULL	-		Status at time of archival
	`rejection_reason`	text	NULL	-
	`archived_by`	int	NULL	-
	`archived_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`original_created_at`	datetime	NULL	-
	`original_updated_at`	datetime	NULL	-

`research_researcher_type`

18 columns 10 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int	NOT NULL	-	auto_increment
	`name`	varchar(100)	NOT NULL	-
UQ	`code`	varchar(50)	NOT NULL	-
	`description`	text	NULL	-
	`max_booking_days_advance`	int	NULL	`14`
	`max_booking_hours_per_day`	int	NULL	`4`
	`max_materials_per_booking`	int	NULL	`10`
	`can_remote_access`	tinyint(1)	NULL	`0`
	`can_request_reproductions`	tinyint(1)	NULL	`1`
	`can_export_data`	tinyint(1)	NULL	`1`
	`requires_id_verification`	tinyint(1)	NULL	`1`
	`auto_approve`	tinyint(1)	NULL	`0`
	`expiry_months`	int	NULL	`12`
	`priority_level`	int	NULL	`5`
FK	`is_active`	tinyint(1)	NULL	`1`
FK	`sort_order`	int	NULL	`100`
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_researcher_type_i18n`

4 columns 0 rows

	Column	Type	Nullable	Default
PK	`id`	int	NOT NULL	-
PK	`culture`	varchar(10)	NOT NULL	`en`
	`name`	varchar(100)	NULL	-
	`description`	text	NULL	-

`research_retrieval_schedule`

12 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`reading_room_id`	int	NOT NULL	-
	`name`	varchar(100)	NOT NULL	-		e.g., "Morning Retrieval", "Afternoon Retrieval"
FK	`day_of_week`	tinyint	NULL	-		0=Sunday, 6=Saturday, NULL=all days
FK	`retrieval_time`	time	NOT NULL	-
	`cutoff_minutes_before`	int	NULL	`30`		Minutes before retrieval time to stop accepting requests
	`max_items_per_run`	int	NULL	`50`
	`storage_location`	varchar(255)	NULL	-		Which storage area this schedule serves
FK	`is_active`	tinyint(1)	NULL	`1`
	`notes`	text	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_rights_policy`

10 columns 1 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`target_type`	varchar(50)	NOT NULL	-
	`target_id`	int	NOT NULL	-
FK	`policy_type`	varchar(43)	NOT NULL	-		permission, prohibition, obligation
FK	`action_type`	varchar(57)	NOT NULL	-		use, reproduce, distribute, modify, archive, display
	`constraints_json`	json	NULL	-
	`policy_json`	json	NULL	-
	`created_by`	int	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_room`

9 columns 1 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
FK	`project_id`	bigint unsigned	NOT NULL	-
	`name`	varchar(255)	NOT NULL	-
	`description`	text	NULL	-
FK	`status`	varchar(31)	NULL	`draft`		draft, active, archived
	`created_by`	int	NOT NULL	-
	`max_participants`	int	NULL	`10`
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_room_manifest`

6 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
FK	`room_id`	bigint unsigned	NOT NULL	-
	`object_id`	int	NOT NULL	-
	`manifest_json`	longtext	NULL	-
	`derivative_type`	varchar(31)	NULL	`full`		full, subset, annotated
	`created_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_room_participant`

5 columns 1 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
FK	`room_id`	bigint unsigned	NOT NULL	-
	`user_id`	int	NOT NULL	-
	`role`	varchar(29)	NULL	`viewer`		owner, editor, viewer
	`joined_at`	timestamp	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_saved_search`

21 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`researcher_id`	int	NOT NULL	-
	`project_id`	int	NULL	-
	`name`	varchar(255)	NOT NULL	-
	`description`	text	NULL	-
	`search_query`	text	NOT NULL	-
	`search_filters`	text	NULL	-
	`query_ast_json`	json	NULL	-
	`result_snapshot_json`	json	NULL	-
	`citation_id`	varchar(100)	NULL	-
	`last_result_count`	int	NULL	-
	`search_type`	varchar(50)	NULL	`informationobject`
	`total_results_at_save`	int	NULL	-
	`facets`	json	NULL	-
	`alert_enabled`	tinyint(1)	NULL	`0`
	`alert_frequency`	varchar(30)	NULL	`weekly`		daily, weekly, monthly
	`last_alert_at`	datetime	NULL	-
	`new_results_count`	int	NULL	`0`
	`is_public`	tinyint(1)	NULL	`0`
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_search_alert_log`

9 columns 0 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int	NOT NULL	-	auto_increment
FK	`saved_search_id`	int	NOT NULL	-
FK	`researcher_id`	int	NOT NULL	-
	`previous_count`	int	NULL	-
	`new_count`	int	NULL	-
	`new_items_count`	int	NULL	-
	`notification_sent`	tinyint(1)	NULL	`0`
	`notification_method`	varchar(50)	NULL	-
FK	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_seat_assignment`

9 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`booking_id`	int	NOT NULL	-
FK	`seat_id`	int	NOT NULL	-
FK	`assigned_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`assigned_by`	int	NULL	-
	`released_at`	datetime	NULL	-
	`released_by`	int	NULL	-
FK	`status`	varchar(44)	NULL	`assigned`		assigned, occupied, released, no_show
	`notes`	text	NULL	-

`research_snapshot`

14 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`project_id`	int	NOT NULL	-
FK	`researcher_id`	int	NOT NULL	-
	`title`	varchar(500)	NOT NULL	-
	`description`	text	NULL	-
FK	`hash_sha256`	varchar(64)	NULL	-
	`query_state_json`	json	NULL	-
	`rights_state_json`	json	NULL	-
	`metadata_json`	json	NULL	-
	`item_count`	int	NULL	`0`
FK	`status`	varchar(32)	NULL	`active`		active, frozen, archived
	`frozen_at`	datetime	NULL	-
	`citation_id`	varchar(100)	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_snapshot_item`

10 columns 0 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int	NOT NULL	-	auto_increment
FK	`snapshot_id`	int	NOT NULL	-
FK	`object_id`	int	NOT NULL	-
	`object_type`	varchar(50)	NULL	`information_object`
	`culture`	varchar(10)	NULL	-
	`slug`	varchar(255)	NULL	-
	`metadata_version_json`	json	NULL	-
	`rights_snapshot_json`	json	NULL	-
	`sort_order`	int	NULL	`0`
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_source_assessment`

10 columns 10 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`object_id`	int	NOT NULL	-
FK	`researcher_id`	int	NOT NULL	-
FK	`source_type`	varchar(36)	NOT NULL	-		primary, secondary, tertiary
	`source_form`	varchar(62)	NULL	`original`		born_digital, scan, original, transcription, translation
	`completeness`	varchar(58)	NULL	`complete`		complete, partial, fragment, missing_pages, redacted
FK	`trust_score`	int	NULL	-
	`rationale`	text	NULL	-
	`bias_context`	text	NULL	-
	`assessed_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_statistics_daily`

9 columns 0 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	int	NOT NULL	-	auto_increment
FK	`stat_date`	date	NOT NULL	-
FK	`stat_type`	varchar(50)	NOT NULL	-
	`dimension`	varchar(100)	NULL	-
	`dimension_value`	varchar(255)	NULL	-
	`count_value`	int	NULL	`0`
	`sum_value`	decimal(15,2)	NULL	-
	`metadata`	json	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_timeline_event`

13 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`project_id`	int	NOT NULL	-
FK	`researcher_id`	int	NOT NULL	-
	`label`	varchar(500)	NOT NULL	-
	`description`	text	NULL	-
FK	`date_start`	date	NOT NULL	-
	`date_end`	date	NULL	-
	`date_type`	varchar(46)	NULL	`event`		event, creation, accession, publication
FK	`source_type`	varchar(50)	NULL	-
	`source_id`	int	NULL	-
	`position`	int	NULL	`0`
	`color`	varchar(7)	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_validation_queue`

9 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`result_id`	int	NOT NULL	-
FK	`researcher_id`	int	NOT NULL	-
FK	`status`	varchar(44)	NULL	`pending`		pending, accepted, rejected, modified
	`modified_data_json`	json	NULL	-
FK	`reviewer_id`	int	NULL	-
	`reviewed_at`	datetime	NULL	-
	`notes`	text	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_verification`

15 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`researcher_id`	int	NOT NULL	-
FK	`verification_type`	varchar(113)	NOT NULL	-		id_document, institutional_letter, institutional_email, orcid, staff_approval, professional_membership, other
	`document_type`	varchar(100)	NULL	-
	`document_reference`	varchar(255)	NULL	-
	`document_path`	varchar(500)	NULL	-
	`verification_data`	json	NULL	-
FK	`status`	varchar(43)	NULL	`pending`		pending, verified, rejected, expired
	`verified_by`	int	NULL	-
	`verified_at`	datetime	NULL	-
FK	`expires_at`	date	NULL	-
	`rejection_reason`	text	NULL	-
	`notes`	text	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_walk_in_visitor`

23 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`reading_room_id`	int	NOT NULL	-
FK	`visit_date`	date	NOT NULL	-
	`first_name`	varchar(100)	NOT NULL	-
	`last_name`	varchar(100)	NOT NULL	-
FK	`email`	varchar(255)	NULL	-
	`phone`	varchar(50)	NULL	-
	`id_type`	varchar(65)	NULL	-		passport, national_id, drivers_license, student_card, other
	`id_number`	varchar(100)	NULL	-
	`organization`	varchar(255)	NULL	-
	`purpose`	text	NULL	-
	`research_topic`	text	NULL	-
	`rules_acknowledged`	tinyint(1)	NULL	`0`
	`rules_acknowledged_at`	datetime	NULL	-
	`photo_permission`	tinyint(1)	NULL	`0`
FK	`converted_to_researcher_id`	int	NULL	-		If they registered
	`seat_id`	int	NULL	-
	`check_in_time`	time	NOT NULL	-
	`check_out_time`	time	NULL	-
	`checked_in_by`	int	NULL	-
	`checked_out_by`	int	NULL	-
	`notes`	text	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`research_workspace`

9 columns 2 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`owner_id`	int	NOT NULL	-
	`name`	varchar(255)	NOT NULL	-
	`description`	text	NULL	-
FK	`visibility`	varchar(32)	NULL	`private`		private, members, public
FK	`share_token`	varchar(64)	NULL	-
	`settings`	json	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`research_workspace_member`

8 columns 2 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`workspace_id`	int	NOT NULL	-
FK	`researcher_id`	int	NOT NULL	-
	`role`	varchar(54)	NULL	`viewer`		owner, admin, editor, viewer, member, contributor
	`invited_by`	int	NULL	-
	`invited_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`accepted_at`	datetime	NULL	-
FK	`status`	varchar(43)	NULL	`pending`		pending, accepted, declined, removed

`research_workspace_resource`

10 columns 2 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	int	NOT NULL	-	auto_increment
FK	`workspace_id`	int	NOT NULL	-
FK	`resource_type`	varchar(68)	NOT NULL	-		collection, project, bibliography, saved_search, document, link
	`resource_id`	int	NULL	-
	`external_url`	varchar(1000)	NULL	-
	`title`	varchar(500)	NULL	-
	`description`	text	NULL	-
	`added_by`	int	NULL	-
	`sort_order`	int	NULL	`0`
	`added_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`researcher_submission`

22 columns 1 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
FK	`researcher_id`	int	NULL	-
FK	`user_id`	int	NOT NULL	-
	`title`	varchar(255)	NOT NULL	-
	`description`	text	NULL	-
	`repository_id`	int	NULL	-
	`parent_object_id`	int	NULL	-
FK	`project_id`	int	NULL	-
	`source_type`	varchar(24)	NOT NULL	`online`		online, offline
	`source_file`	varchar(255)	NULL	-
	`include_images`	tinyint(1)	NULL	`1`
FK	`status`	varchar(75)	NOT NULL	`draft`		draft, submitted, under_review, approved, published, returned, rejected
	`workflow_task_id`	int	NULL	-
	`total_items`	int	NULL	`0`
	`total_files`	int	NULL	`0`
	`total_file_size`	bigint unsigned	NULL	`0`
	`return_comment`	text	NULL	-
	`reject_comment`	text	NULL	-
	`published_at`	datetime	NULL	-
	`submitted_at`	datetime	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`researcher_submission_file`

12 columns 0 rows

	Column	Type	Nullable	Default	Extra
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
FK	`item_id`	bigint unsigned	NOT NULL	-
	`original_name`	varchar(255)	NOT NULL	-
	`stored_name`	varchar(255)	NOT NULL	-
	`stored_path`	varchar(1024)	NOT NULL	-
	`mime_type`	varchar(100)	NULL	-
	`file_size`	bigint unsigned	NULL	`0`
	`checksum`	varchar(64)	NULL	-
	`caption`	text	NULL	-
	`sort_order`	int	NULL	`0`
	`published_do_id`	int	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED

`researcher_submission_item`

28 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
FK	`submission_id`	bigint unsigned	NOT NULL	-
FK	`parent_item_id`	bigint unsigned	NULL	-
FK	`item_type`	varchar(45)	NOT NULL	`description`		description, note, repository, creator
	`title`	varchar(255)	NOT NULL	-
	`identifier`	varchar(255)	NULL	-
	`level_of_description`	varchar(50)	NULL	`item`
	`scope_and_content`	text	NULL	-
	`extent_and_medium`	varchar(500)	NULL	-
	`date_display`	varchar(255)	NULL	-
	`date_start`	date	NULL	-
	`date_end`	date	NULL	-
	`creators`	text	NULL	-
	`subjects`	text	NULL	-
	`places`	text	NULL	-
	`genres`	text	NULL	-
	`access_conditions`	text	NULL	-
	`reproduction_conditions`	text	NULL	-
	`notes`	text	NULL	-
	`repository_name`	varchar(500)	NULL	-
	`repository_address`	text	NULL	-
	`repository_contact`	varchar(500)	NULL	-
	`reference_object_id`	int	NULL	-
	`reference_slug`	varchar(255)	NULL	-
	`sort_order`	int	NULL	`0`
	`published_object_id`	int	NULL	-
	`created_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED
	`updated_at`	datetime	NULL	`CURRENT_TIMESTAMP`	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

`researcher_submission_review`

6 columns 0 rows

	Column	Type	Nullable	Default	Extra	Comment
PK	`id`	bigint unsigned	NOT NULL	-	auto_increment
FK	`submission_id`	bigint unsigned	NOT NULL	-
	`reviewer_id`	int	NOT NULL	-
	`action`	varchar(47)	NOT NULL	-		comment, return, approve, reject, publish
	`comment`	text	NULL	-
	`created_at`	datetime	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.