AI & NER

Named Entity Recognition, translation, summarization, spellcheck, face detection, LLM suggestions.

ahgAIPlugin
All Schemas
0
ahg_ai_auto_trigger_log
0
ahg_ai_batch
8
ahg_ai_condition_assessment
3
ahg_ai_condition_damage
6
ahg_ai_condition_history
0
ahg_ai_job
0
ahg_ai_job_log
0
ahg_ai_pending_extraction
1
ahg_ai_service_client
0
ahg_ai_service_usage
96
ahg_ai_settings
0
ahg_ai_training_contribution
0
ahg_ai_usage
0
ahg_description_suggestion
3
ahg_llm_config
0
ahg_ner_authority_stub
1,898
ahg_ner_entity
0
ahg_ner_entity_link
190
ahg_ner_extraction
32
ahg_ner_settings
0
ahg_ner_usage
3
ahg_prompt_template
0
ahg_spellcheck_result
17
ahg_translation_draft
101
ahg_translation_log
0
ahg_translation_queue
3
ahg_translation_settings
0
ai_iiif_extraction
Entity Relationship Diagram
Open Full Size
AI & NER ERD
Foreign Key Relationships
Constraint Table Column References Referenced Column
fk_acd_assessment ahg_ai_condition_damage assessment_id ahg_ai_condition_assessment id
fk_ach_assessment ahg_ai_condition_history assessment_id ahg_ai_condition_assessment id
ahg_ai_job_ibfk_1 ahg_ai_job batch_id ahg_ai_batch id
fk_asu_client ahg_ai_service_usage client_id ahg_ai_service_client id
ahg_description_suggestion_ibfk_2 ahg_description_suggestion llm_config_id ahg_llm_config id
ahg_description_suggestion_ibfk_1 ahg_description_suggestion prompt_template_id ahg_prompt_template id
ahg_ner_entity_ibfk_1 ahg_ner_entity extraction_id ahg_ner_extraction id
ahg_ner_entity_link_ibfk_1 ahg_ner_entity_link entity_id ahg_ner_entity id
ahg_ai_auto_trigger_log
6 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK object_id int NOT NULL -
digital_object_id int NULL -
task_type varchar(50) NOT NULL -
status varchar(50) NOT NULL -
FK created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
ahg_ai_batch
21 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
name varchar(255) NOT NULL -
description text NULL -
task_types json NOT NULL -
FK status varchar(59) NULL pending pending, running, paused, completed, failed, cancelled
FK priority tinyint NULL 5
total_items int NULL 0
completed_items int NULL 0
failed_items int NULL 0
progress_percent decimal(5,2) NULL 0.00
max_concurrent int NULL 5
delay_between_ms int NULL 1000
max_retries int NULL 3
FK scheduled_at timestamp NULL -
started_at timestamp NULL -
completed_at timestamp NULL -
estimated_completion timestamp NULL -
options json NULL -
FK created_by int NULL -
created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
updated_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED on update CURRENT_TIMESTAMP
ahg_ai_condition_assessment
21 columns 8 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK information_object_id int NULL -
FK condition_report_id int NULL - FK to condition_report if linked
digital_object_id int NULL -
image_path varchar(1024) NULL - Path to analyzed image
overlay_path varchar(1024) NULL - Path to annotated overlay image
FK overall_score decimal(5,2) NULL - 0-100 condition score
FK condition_grade varchar(50) NULL - Dropdown: condition_grade
damage_count int NULL 0
recommendations text NULL -
model_version varchar(50) NULL -
processing_time_ms int NULL -
confidence_threshold decimal(3,2) NULL 0.25
FK source varchar(50) NULL manual manual, bulk, auto, api
FK is_confirmed tinyint(1) NULL 0 Human reviewed and confirmed
confirmed_by int NULL -
confirmed_at datetime NULL -
api_client_id bigint unsigned NULL - If submitted via SaaS API
created_by int NULL -
FK created_at datetime NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
updated_at datetime NULL - on update CURRENT_TIMESTAMP
ahg_ai_condition_damage
14 columns 3 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK assessment_id bigint unsigned NOT NULL -
FK damage_type varchar(50) NOT NULL - Dropdown: damage_type
FK severity varchar(50) NULL - Dropdown: damage_severity
confidence decimal(4,3) NOT NULL - 0.000-1.000
bbox_x int NULL - Bounding box top-left X (pixels)
bbox_y int NULL - Bounding box top-left Y (pixels)
bbox_w int NULL - Bounding box width (pixels)
bbox_h int NULL - Bounding box height (pixels)
area_percent decimal(5,2) NULL - Damage area as % of total image
location_zone varchar(50) NULL - Dropdown: condition location_zone
description text NULL -
score_deduction decimal(5,2) NULL - Points deducted from score
created_at datetime NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
ahg_ai_condition_history
7 columns 6 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK information_object_id int NOT NULL -
FK assessment_id bigint unsigned NOT NULL -
score decimal(5,2) NOT NULL -
condition_grade varchar(50) NOT NULL -
damage_count int NULL 0
FK assessed_at datetime NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
ahg_ai_job
18 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK batch_id bigint unsigned NOT NULL -
FK object_id int NOT NULL -
FK task_type varchar(50) NOT NULL -
FK status varchar(57) NULL pending pending, queued, running, completed, failed, skipped
priority tinyint NULL 5
gearman_handle varchar(255) NULL -
worker_id varchar(100) NULL -
attempt_count int NULL 0
result_data json NULL -
error_message text NULL -
error_code varchar(50) NULL -
queued_at timestamp NULL -
started_at timestamp NULL -
completed_at timestamp NULL -
processing_time_ms int NULL -
created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
updated_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED on update CURRENT_TIMESTAMP
ahg_ai_job_log
7 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK batch_id bigint unsigned NULL -
FK job_id bigint unsigned NULL -
FK event_type varchar(50) NOT NULL -
message text NULL -
details json NULL -
FK created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
ahg_ai_pending_extraction
9 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK object_id int NOT NULL -
digital_object_id int NULL -
task_type varchar(50) NOT NULL ner
FK status varchar(45) NULL pending pending, processing, completed, failed
attempt_count int NULL 0
error_message text NULL -
FK created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
processed_at timestamp NULL -
ahg_ai_service_client
16 columns 1 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
name varchar(255) NOT NULL -
organization varchar(255) NULL -
email varchar(255) NOT NULL -
UQ api_key varchar(64) NOT NULL -
tier varchar(50) NULL free Dropdown: ai_service_tier
monthly_limit int NULL 50
can_contribute_training tinyint(1) NULL 0 Client has opted in to contribute training data
FK is_active tinyint(1) NULL 1
last_used_at datetime NULL -
created_at datetime NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
updated_at datetime NULL - on update CURRENT_TIMESTAMP
training_approved tinyint(1) NULL 0 Admin approved client data for training
training_approved_at datetime NULL -
training_approved_by int NULL -
training_approval_doc varchar(1024) NULL - Path to uploaded consent document
ahg_ai_service_usage
7 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK client_id bigint unsigned NOT NULL -
year_month varchar(7) NOT NULL - YYYY-MM
scans_used int NULL 0
last_scan_at datetime NULL -
created_at datetime NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
updated_at datetime NULL - on update CURRENT_TIMESTAMP
ahg_ai_settings
5 columns 96 rows
Column Type Nullable Default Extra Comment
PK id int NOT NULL - auto_increment
FK feature varchar(50) NOT NULL general
setting_key varchar(100) NOT NULL -
setting_value text NULL -
updated_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED on update CURRENT_TIMESTAMP
ahg_ai_training_contribution
11 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK source varchar(50) NOT NULL -
FK object_id int NULL -
contributor varchar(255) NULL -
FK client_id bigint unsigned NULL -
image_filename varchar(255) NOT NULL -
annotation_filename varchar(255) NOT NULL -
damage_types json NULL -
FK status varchar(20) NOT NULL pending
FK created_at datetime NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
updated_at datetime NULL - on update CURRENT_TIMESTAMP
ahg_ai_usage
10 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK feature varchar(50) NOT NULL -
FK user_id int NULL -
api_key varchar(100) NULL -
endpoint varchar(100) NOT NULL -
request_size int NULL 0
response_time_ms int NULL -
status_code int NULL 200
ip_address varchar(45) NULL -
FK created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
ahg_description_suggestion
18 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK object_id int NOT NULL -
suggested_text text NOT NULL -
existing_text text NULL -
FK prompt_template_id int unsigned NULL -
FK llm_config_id int unsigned NULL -
source_data json NULL -
FK status varchar(42) NULL pending pending, approved, rejected, edited
edited_text text NULL -
reviewed_by int NULL -
reviewed_at timestamp NULL -
review_notes text NULL -
generation_time_ms int NULL -
tokens_used int NULL -
model_used varchar(100) NULL -
created_by int NULL -
FK created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
expires_at timestamp NULL -
ahg_llm_config
13 columns 3 rows
Column Type Nullable Default Extra Comment
PK id int unsigned NOT NULL - auto_increment
FK provider varchar(50) NOT NULL -
UQ name varchar(100) NOT NULL -
FK is_active tinyint(1) NULL 1
FK is_default tinyint(1) NULL 0
endpoint_url varchar(500) NULL -
api_key_encrypted text NULL -
model varchar(100) NOT NULL -
max_tokens int NULL 2000
temperature decimal(3,2) NULL 0.70
timeout_seconds int NULL 120
created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
updated_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED on update CURRENT_TIMESTAMP
ahg_ner_authority_stub
11 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK ner_entity_id bigint unsigned NOT NULL -
FK actor_id int NOT NULL - Created stub actor
source_object_id int NOT NULL - IO where NER found the entity
entity_type varchar(50) NOT NULL - PERSON, ORG, GPE
entity_value varchar(500) NOT NULL -
confidence decimal(5,4) NOT NULL 1.0000
FK status varchar(20) NOT NULL stub stub, promoted, rejected
promoted_by int NULL -
promoted_at datetime NULL -
created_at datetime NOT NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
ahg_ner_entity
15 columns 1,898 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK extraction_id bigint unsigned NULL -
FK object_id int NOT NULL -
FK entity_type varchar(50) NOT NULL -
entity_value varchar(500) NOT NULL -
original_value varchar(500) NULL -
original_type varchar(50) NULL -
FK correction_type varchar(20) NOT NULL none none, value_edit, type_change, both, rejected, approved
FK training_exported tinyint(1) NULL 0
confidence decimal(5,4) NULL 1.0000
FK status varchar(50) NULL pending
linked_actor_id int NULL -
reviewed_by int NULL -
reviewed_at timestamp NULL -
created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
ahg_ner_entity_link
7 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK entity_id bigint unsigned NOT NULL -
FK actor_id int NOT NULL -
link_type varchar(28) NULL manual exact, fuzzy, manual
confidence decimal(5,4) NULL 1.0000
created_by int NULL -
created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
ahg_ner_extraction
6 columns 190 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK object_id int NOT NULL -
backend_used varchar(50) NULL local
FK status varchar(50) NULL pending
entity_count int NULL 0
extracted_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
ahg_ner_settings
4 columns 32 rows
Column Type Nullable Default Extra Comment
PK id int NOT NULL - auto_increment
UQ setting_key varchar(100) NOT NULL -
setting_value text NULL -
updated_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED on update CURRENT_TIMESTAMP
ahg_ner_usage
9 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK user_id int NULL -
api_key varchar(100) NULL -
endpoint varchar(100) NOT NULL -
request_size int NULL 0
response_time_ms int NULL -
status_code int NULL 200
ip_address varchar(45) NULL -
FK created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
ahg_prompt_template
13 columns 3 rows
Column Type Nullable Default Extra Comment
PK id int unsigned NOT NULL - auto_increment
name varchar(100) NOT NULL -
UQ slug varchar(100) NOT NULL -
system_prompt text NOT NULL -
user_prompt_template text NOT NULL -
FK level_of_description varchar(50) NULL -
FK repository_id int NULL -
FK is_default tinyint(1) NULL 0
FK is_active tinyint(1) NULL 1
include_ocr tinyint(1) NULL 1
max_ocr_chars int NULL 8000
created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
updated_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED on update CURRENT_TIMESTAMP
ahg_spellcheck_result
8 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK object_id int NOT NULL -
errors_json json NULL -
error_count int NULL 0
FK status varchar(34) NULL pending pending, reviewed, ignored
reviewed_by int NULL -
reviewed_at timestamp NULL -
created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
ahg_translation_draft
13 columns 17 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK object_id bigint unsigned NOT NULL -
entity_type varchar(64) NOT NULL information_object
field_name varchar(64) NOT NULL -
source_culture varchar(8) NOT NULL -
target_culture varchar(8) NOT NULL en
source_hash char(64) NOT NULL -
source_text longtext NOT NULL -
translated_text longtext NOT NULL -
FK status varchar(32) NOT NULL draft draft, applied, rejected
created_by_user_id bigint unsigned NULL -
created_at datetime NOT NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
applied_at datetime NULL -
ahg_translation_log
11 columns 101 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK object_id bigint unsigned NULL -
field_name varchar(64) NULL -
source_culture varchar(8) NULL -
target_culture varchar(8) NULL -
endpoint varchar(255) NULL -
http_status int NULL -
FK ok tinyint(1) NOT NULL 0
error text NULL -
elapsed_ms int NULL -
created_at datetime NOT NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
ahg_translation_queue
10 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK object_id int NOT NULL -
source_culture varchar(10) NOT NULL -
target_culture varchar(10) NOT NULL -
fields text NOT NULL - JSON array of fields to translate
FK status varchar(45) NULL pending pending, processing, completed, failed
error_message text NULL -
created_by int NULL -
created_at timestamp NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
processed_at timestamp NULL -
ahg_translation_settings
4 columns 3 rows
Column Type Nullable Default Extra Comment
PK id int unsigned NOT NULL - auto_increment
UQ setting_key varchar(128) NOT NULL -
setting_value text NULL -
updated_at datetime NOT NULL CURRENT_TIMESTAMP DEFAULT_GENERATED on update CURRENT_TIMESTAMP
ai_iiif_extraction
12 columns 0 rows
Column Type Nullable Default Extra Comment
PK id bigint unsigned NOT NULL - auto_increment
FK information_object_id int NOT NULL - FK to information_object.id
FK iiif_canvas_id bigint unsigned NULL - FK to iiif_canvas.id (optional)
FK extraction_type varchar(50) NOT NULL - ocr, ner, translate, summarize, face
FK status varchar(20) NOT NULL pending pending, processing, completed, failed
input_source varchar(500) NULL - Cantaloupe IIIF URL or local path
output_text longtext NULL - Extracted/processed text
output_json json NULL - Structured extraction results
error_message text NULL -
processing_time_ms int unsigned NULL -
FK created_at datetime NULL CURRENT_TIMESTAMP DEFAULT_GENERATED
updated_at datetime NULL CURRENT_TIMESTAMP DEFAULT_GENERATED on update CURRENT_TIMESTAMP
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.