Database Schema
Interactive ER Diagram - Click to edit, add, or delete
Protocols
ptd_protocols
id
uuid
protocol_number
varchar(100)
protocol_title
text
sponsor_name
varchar(200)
indication
varchar(500)
phase
varchar(50)
file_name
varchar(500)
file_size
integer
status
varchar(50)
version_number
varchar(20)
full_text
text
created_at
timestamptz
updated_at
timestamptz
Protocol documents with metadata
Events
ptd_events
id
uuid
protocol_id
uuid
event_name
varchar(500)
event_type
varchar(100)
event_order
integer
day_nominal
integer
day_min
integer
day_max
integer
window_before
integer
window_after
integer
is_required
boolean
source_reference
text
created_at
timestamptz
Study visits/timepoints from SoA
Tasks
ptd_tasks
id
uuid
event_id
uuid
task_name
varchar(500)
task_order
integer
form_name
varchar(200)
form_code
varchar(20)
domain_code
varchar(10)
is_required
boolean
instructions
text
source_reference
text
created_at
timestamptz
Forms/CRFs mapped to CDASH domains
Actions
ptd_actions
id
uuid
task_id
uuid
action_name
varchar(500)
action_order
integer
variable_name
varchar(50)
variable_label
varchar(200)
data_type
varchar(50)
display_type
varchar(50)
is_required
boolean
is_key
boolean
units
varchar(100)
min_value
decimal
max_value
decimal
validation_rules
jsonb
confidence_score
decimal(3,2)
review_status
varchar(50)
cdash_variable_id
uuid
is_standard_variable
boolean
sdtm_target
varchar(50)
sdtm_mapping_type
varchar(50)
ct_codelist
varchar(100)
ct_compliant
boolean
compliance_issues
jsonb
status
text
modified_by
text
modified_at
timestamptz
original_value
jsonb
created_at
timestamptz
updated_at
timestamptz
Variables/fields with CDASH compliance
Code Lists
ptd_code_lists
id
uuid
action_id
uuid
code_value
varchar(200)
code_label
varchar(500)
code_order
integer
is_default
boolean
ct_submission_value
varchar(200)
ct_compliant
boolean
ct_suggested_value
varchar(200)
created_at
timestamptz
Dropdown options with CT compliance
Data Items
ptd_data_items
id
uuid
action_id
uuid
item_name
varchar(200)
item_value
text
item_type
varchar(50)
created_at
timestamptz
Additional field specifications
Criteria
ptd_criteria
id
uuid
protocol_id
uuid
criteria_type
varchar(50)
criteria_text
text
criteria_order
integer
is_critical
boolean
source_reference
text
created_at
timestamptz
Inclusion/exclusion criteria
Extraction Sessions
ptd_extraction_sessions
id
uuid
protocol_id
uuid
extraction_mode
varchar(50)
started_at
timestamptz
completed_at
timestamptz
status
varchar(50)
total_pages
integer
pages_processed
integer
events_extracted
integer
tasks_extracted
integer
actions_extracted
integer
error_message
text
extraction_metadata
jsonb
created_at
timestamptz
Extraction run metadata
Extraction Progress
ptd_extraction_progress
id
uuid
extraction_session_id
uuid
stage
varchar(50)
message
text
percent
integer
details
jsonb
created_at
timestamptz
Real-time extraction updates
Compliance Scores
ptd_compliance_scores
id
uuid
protocol_id
uuid
extraction_session_id
uuid
overall_score
decimal(5,2)
cdash_score
decimal(5,2)
ct_score
decimal(5,2)
sdtm_score
decimal(5,2)
completeness_score
decimal(5,2)
score_breakdown
jsonb
calculated_at
timestamptz
created_at
timestamptz
Multi-dimensional scoring (CDASH/CT/SDTM)
Domain Compliance
ptd_domain_compliance
id
uuid
compliance_score_id
uuid
domain_code
varchar(10)
domain_score
decimal(5,2)
total_variables
integer
standard_variables
integer
required_present
integer
required_missing
integer
ct_compliant
integer
ct_non_compliant
integer
issues_count
integer
created_at
timestamptz
Per-domain compliance metrics
Compliance Issues
ptd_compliance_issues
id
uuid
compliance_score_id
uuid
action_id
uuid
domain_code
varchar(10)
issue_type
varchar(50)
severity
varchar(20)
variable_name
varchar(100)
message
text
suggestion
text
standard_value
varchar(200)
actual_value
varchar(200)
auto_fixable
boolean
is_resolved
boolean
resolved_at
timestamptz
created_at
timestamptz
Violations with auto-fix suggestions
CDASH Embeddings
ptd_cdash_embeddings
id
uuid
domain_code
varchar(10)
variable_name
varchar(50)
variable_label
varchar(200)
definition
text
combined_text
text
embedding
vector(768)
created_at
timestamptz
CDASH variable vectors for semantic match
Protocol Embeddings
ptd_protocol_embeddings
id
uuid
protocol_id
uuid
chunk_index
integer
chunk_text
text
page_number
integer
section
varchar(200)
embedding
vector(768)
created_at
timestamptz
Protocol text chunks for RAG
CT Embeddings
ptd_ct_embeddings
id
uuid
codelist_code
varchar(50)
term_value
varchar(200)
preferred_term
varchar(200)
synonyms_text
text
combined_text
text
embedding
vector(768)
created_at
timestamptz
Controlled Terminology vectors
Press enter or space to select a node. You can then use the arrow keys to move the node around. Press delete to remove it and escape to cancel.
Press enter or space to select an edge. You can then press delete to remove it or escape to cancel.