Database Reference
Document Type: Reference (Tier 3) Status: Draft Last Updated: 2026-01-25
1. Purpose
This document provides a lookup reference for the PCR Analysis System database schema. It serves as authoritative documentation for:
- Table dictionary with purpose and owner domain
- Key columns (primary keys, foreign keys, important fields)
- Index documentation
- Foreign key relationships
- Soft delete conventions
2. Database Overview
| Property | Value |
|---|---|
| Database Platform | Aurora MySQL 8.0 (Serverless v2) |
| Primary Key Strategy | Ordered UUIDs (string, 36 characters) |
| Timestamp Precision | Microseconds (datetime(6)) |
| Decimal Precision | decimal(32,10) for QC statistics |
| Soft Delete Column | deleted_at (nullable datetime) |
3. Table Dictionary by Domain
3.1 Core Data Tables (RUNFILE Domain)
| Table | Purpose | Owner Domain | Soft Delete |
|---|---|---|---|
runs | Root entity for imported thermocycler run files | RUNFILE | No |
wells | Sample position (A1-H12) within a run | RUNFILE | No |
observations | Per-target measurement data for each well | RUNFILE | No |
run_mixes | Mix composition per run (junction) | RUNFILE | No |
run_targets | Per-target metrics for a run | RUNFILE | No |
runs
| Column | Type | Description | FK |
|---|---|---|---|
id | UUID | Primary key | - |
run_name | VARCHAR | Imported file name | - |
uploaded_user_id | UUID | User who uploaded | users.id |
modified_user_id | UUID | User who last modified | users.id |
modifying_user_id | UUID | User currently modifying | users.id |
thermocycler_id | UUID | Instrument used | thermocyclers.id |
site_id | UUID | Site ownership | sites.id |
run_status | SMALLINT | Computed status (1-4) | - |
calibration_status | SMALLINT | Calibration state (0-2) | - |
num_label_errors | INT | Count of label errors | - |
num_pending_resolutions | INT | Count of pending resolutions | - |
resolution_status | VARCHAR | Resolution workflow state | - |
file_hash | VARCHAR | Deduplication hash | - |
runfile_created_at | DATETIME | Original file creation | - |
has_calibrated | BOOLEAN | Calibration completed | - |
has_exported_wells | BOOLEAN | Any wells exported | - |
bson_readings_file_path | VARCHAR | S3 path to readings | - |
custom_details | JSON | Additional metadata | - |
Run Status Values:
| Value | Constant | Meaning |
|---|---|---|
| 1 | ALL_WELLS_EXPORTED | All wells exported |
| 2 | ALL_WELLS_READY_FOR_EXPORT | Ready, none exported |
| 3 | NO_EXPORT_ERRORS_TO_RESOLVE | Has errors, no LIMS status |
| 4 | SOME_WELLS_READY_FOR_EXPORT_WITH_ERRORS_TO_RESOLVE | Mixed state |
wells
| Column | Type | Description | FK |
|---|---|---|---|
id | UUID | Primary key | - |
run_id | UUID | Parent run | runs.id |
run_mix_id | UUID | Mix assignment | run_mixes.id |
mix_id | UUID | Direct mix reference | mixes.id |
specimen_id | UUID | Sample type (nullable) | specimens.id |
extraction_instrument_id | UUID | Extraction device (nullable) | extraction_instruments.id |
error_code_id | UUID | Single error code (nullable) | error_codes.id |
lims_status | VARCHAR | LIMS status code (string, not FK) | - |
resolution_codes | TEXT | Pipe-delimited codes applied | - |
proposed_resolution_code_id | UUID | Pending resolution | resolution_codes.id |
resolution_code_proposed_user_id | UUID | User who proposed | users.id |
well_number | VARCHAR | Position (A1-H12) | - |
sample_label | VARCHAR | Sample identifier | - |
sample_name | VARCHAR | Sample name | - |
batch_number | VARCHAR | Batch reference | - |
accession | VARCHAR | Lab accession number | - |
role_id | UUID | Role classification | roles.id |
role_alias | VARCHAR | Role alias string | - |
control_label_id | UUID | Control label mapping | control_labels.id |
export_date | DATETIME | When exported (null = not exported) | - |
exclude | BOOLEAN | Manual exclusion flag | - |
is_flagged | BOOLEAN | Flagged for review | - |
is_crossover | BOOLEAN | Crossover sample flag | - |
site_id | UUID | Site ownership | sites.id |
extraction_date | DATETIME | Sample extraction date | - |
tissue_weight | DECIMAL | Sample weight | - |
quantity_multiplier | DECIMAL | Quantification multiplier | - |
concentration_factor | DECIMAL | Concentration factor | - |
reporting_quantity | DECIMAL | Calculated quantity | - |
associate_well_ids_for_combined_outcome | JSON | Related wells for combined outcome | - |
archive_dependent_well_ids | JSON | Well IDs from other runs used during analysis (v3.0.1) | - |
missing_mixes_ids | JSON | Missing mix references | - |
invalid_raw_data | JSON | Data validation issues | - |
problems | JSON | Analysis problems | - |
Key Design Decision: Wells have a single error_code_id FK (not a junction table). One error code maximum per well.
LIMS Status Pattern: wells.lims_status stores a code string (e.g., "DETECTED", "RPT"). Lookup via lims_statuses.code, not FK constraint.
observations
| Column | Type | Description | FK |
|---|---|---|---|
id | UUID | Primary key | - |
well_id | UUID | Parent well | wells.id |
target_id | UUID | Target measured | targets.id |
role_id | UUID | Role classification | roles.id |
dye_id | UUID | Fluorophore used | dyes.id |
lot_id | UUID | Reagent lot (nullable) | lots.id |
role_to_target_mapping_id | UUID | Configuration mapping | role_to_target_mappings.id |
machine_cls | SMALLINT | Thermocycler classification (0-5) | - |
dxai_cls | SMALLINT | DXAI classification (0-5) | - |
final_cls | SMALLINT | Final classification (0-5) | - |
manual_cls | SMALLINT | Manual override classification | - |
proposed_cls | SMALLINT | Proposed classification | - |
machine_ct | DECIMAL | Thermocycler CT value | - |
dxai_ct | DECIMAL | DXAI CT value | - |
final_ct | DECIMAL | Final CT value | - |
manual_ct | DECIMAL | Manual override CT | - |
quantity | DECIMAL | Calculated quantity | - |
sd_from_mean | DECIMAL | Standard deviations from mean | - |
readings | JSON | Raw fluorescence readings array | - |
problems | JSON | Analysis problems | - |
target_threshold | DECIMAL | Target threshold used | - |
baseline_start | INT | Baseline calculation start | - |
baseline_end | INT | Baseline calculation end | - |
proposed_ct_provider | SMALLINT | Proposed CT source (1=MACHINE, 2=DXAI) | - |
manual_ct_provider | SMALLINT | Manual CT source | - |
Classification Values:
| Value | Constant | Meaning |
|---|---|---|
| 0 | Neg | Negative |
| 1 | Pos | Positive |
| 2 | Amb | Ambiguous |
| 3 | Neg *N/A | Negative, not applicable |
| 4 | Pos *N/A | Positive, not applicable |
| 5 | Amb *N/A | Ambiguous, not applicable |
3.2 Configuration Tables (KITCFG Domain)
| Table | Purpose | Owner Domain | Soft Delete |
|---|---|---|---|
mixes | PCR reaction mix definitions | KITCFG | No |
targets | Analyte/gene definitions | KITCFG | No |
dyes | Fluorophore catalog | KITCFG | No |
roles | Sample classification types | KITCFG | No |
role_to_target_mappings | Role-mix-target junction | KITCFG | No |
control_labels | Enhanced role mapping (v3.0.0+) | KITCFG | Yes |
specimens | Sample type catalog | KITCFG | No |
kits | Kit groupings | KITCFG | No |
kit_mix | Kit-mix junction | KITCFG | No |
mixes
| Column | Type | Description | FK |
|---|---|---|---|
id | UUID | Primary key | - |
mix_name | VARCHAR | Display name | - |
use_passive_dye | BOOLEAN | Uses passive reference | - |
is_disabled | BOOLEAN | Soft disable (global scope excludes) | - |
is_quantitative | BOOLEAN | Quantitative assay | - |
extraction_instrument_id | UUID | Default instrument | extraction_instruments.id |
site_id | UUID | Site ownership | sites.id |
targets
| Column | Type | Description | FK |
|---|---|---|---|
id | UUID | Primary key | - |
mix_id | UUID | Parent mix | mixes.id |
dye_id | UUID | Detection dye | dyes.id |
target_name | VARCHAR | Display name | - |
export_name | VARCHAR | LIMS export name | - |
type | SMALLINT | 1=IC, 2=EC (nullable) | - |
is_passive | BOOLEAN | Passive reference target | - |
expected_threshold | DECIMAL | Expected threshold | - |
preferred_result_provider | SMALLINT | 1=MACHINE, 2=DXAI | - |
should_check_cls_discrepancy | BOOLEAN | Enable discrepancy check | - |
max_ct_for_cls_discrepancy | DECIMAL | CT threshold for check | - |
ct_discrepancy_delta | DECIMAL | Allowed CT difference | - |
max_ct_for_ct_discrepancy | DECIMAL | CT limit for discrepancy | - |
ct_inhibition_delta | DECIMAL | Inhibition detection delta | - |
maximum_fl | DECIMAL | Maximum fluorescence | - |
minimum_fluorescence | DECIMAL | Minimum fluorescence | - |
minimum_fl_to_be_pos | DECIMAL | Minimum FL for positive | - |
min_df | DECIMAL | Minimum delta fluorescence | - |
max_df | DECIMAL | Maximum delta fluorescence | - |
calibration_file_path | VARCHAR | S3 path to calibration | - |
calibration_uploaded_at | DATETIME | Calibration upload date | - |
order | INT | Display order | - |
site_id | UUID | Site ownership | sites.id |
control_labels
| Column | Type | Description | FK |
|---|---|---|---|
id | UUID | Primary key | - |
role_id | UUID | Role reference | roles.id |
mix_id | UUID | Mix reference | mixes.id |
role_alias | VARCHAR | Role alias string | - |
is_strict | BOOLEAN | Strict matching mode | - |
backup_mixes_ids | JSON | Backup mix UUIDs | - |
site_id | UUID | Site ownership | sites.id |
deleted_at | DATETIME | Soft delete timestamp | - |
3.3 Error & Resolution Tables (ERRORCODES Domain)
| Table | Purpose | Owner Domain | Soft Delete |
|---|---|---|---|
error_codes | Error code definitions | ERRORCODES | Yes |
resolution_codes | Error-to-resolution mappings | ERRORCODES | No |
resolution_messages | Resolution message templates | ERRORCODES | No |
lims_statuses | LIMS status definitions | ERRORCODES | No |
well_resolutions | Applied resolution history | ERRORCODES | No |
well_lims_statuses | Computed LIMS status cache | ERRORCODES | No |
error_codes
| Column | Type | Description | FK |
|---|---|---|---|
id | UUID | Primary key | - |
error_code | VARCHAR | Code string (e.g., "CTRL_FAIL") | - |
error_message | VARCHAR | Display message template | - |
error_level | SMALLINT | 1=Well, 2=Mix, 3=Target | - |
error_type | SMALLINT | 0-4 (see below) | - |
lims_status | VARCHAR | Associated LIMS status | - |
does_prevent_analyse | BOOLEAN | Blocks further analysis | - |
is_inhibited | BOOLEAN | Inhibition-related error | - |
causes_missing_mixes | BOOLEAN | Triggers missing mix logic | - |
site_id | UUID | Site ownership | sites.id |
deleted_at | DATETIME | Soft delete timestamp | - |
Error Type Values:
| Value | Constant | Outcome Type | Blocks Export |
|---|---|---|---|
| 0 | Label Error | Error | Yes |
| 1 | Error | Error | Yes |
| 2 | Warning | Warning | No |
| 3 | Information | Information | No |
| 4 | Associate Control Error | Error | Yes |
Error Level Values:
| Value | Constant | Description |
|---|---|---|
| 1 | Well | Well-level error |
| 2 | Mix | Mix-level error |
| 3 | Target | Target-level error |
lims_statuses
| Column | Type | Description | FK |
|---|---|---|---|
id | UUID | Primary key | - |
code | VARCHAR | Status code (e.g., "DETECTED") | - |
message | VARCHAR | Message template | - |
type | SMALLINT | 1=Warning, 2=Exclude, 4=Information | - |
result | SMALLINT | 1=DETECTED, 2=NOT_DETECTED (nullable) | - |
site_id | UUID | Site ownership | sites.id |
LIMS Status Type Values:
| Value | Constant | Description |
|---|---|---|
| 1 | Warning | Re-test recommended (RPT, RXT) |
| 2 | Exclude | Well excluded from export |
| 4 | Information | Normal result |
3.4 Quality Control Tables (QC Domain)
| Table | Purpose | Owner Domain | Soft Delete |
|---|---|---|---|
westgard_limits | Westgard QC thresholds | QC | Yes |
westgard_events | Westgard violation catalog | QC | No |
control_range_settings | Control acceptance limits | QC | No |
westgard_limits
| Column | Type | Description | FK |
|---|---|---|---|
id | UUID | Primary key | - |
extraction_instrument_id | UUID | Instrument reference | extraction_instruments.id |
lot_id | UUID | Reagent lot | lots.id |
target_id | UUID | Target reference | targets.id |
role_id | UUID | Role reference | roles.id |
sd | DECIMAL(32,10) | Standard deviation | - |
mean | DECIMAL(32,10) | Mean value | - |
historic_cv | DECIMAL(32,10) | Historical CV | - |
quant_or_ct | SMALLINT | 1=Quant, 2=CT | - |
in_error | BOOLEAN | Currently in error state | - |
is_duplicate | BOOLEAN | Duplicate entry marker | - |
is_strict | BOOLEAN | Strict matching mode | - |
role_alias | VARCHAR | Role alias for matching | - |
new_role_map_status | VARCHAR | New role mapping status | - |
westgard_event_id | UUID | Current violation | westgard_events.id |
user_id | UUID | Last modifier | users.id |
caused_well_id | UUID | Well that caused violation | wells.id |
site_id | UUID | Site ownership | sites.id |
deleted_at | DATETIME | Soft delete timestamp | - |
control_range_settings
| Column | Type | Description | FK |
|---|---|---|---|
id | UUID | Primary key | - |
role_to_target_mapping_id | UUID | Configuration mapping | role_to_target_mappings.id |
lot_id | UUID | Reagent lot | lots.id |
low_bound | DECIMAL | Lower acceptance limit | - |
up_bound | DECIMAL | Upper acceptance limit | - |
quant_or_ct | SMALLINT | 1=Quant, 2=CT | - |
site_id | UUID | Site ownership | sites.id |
3.5 User & Site Tables (USERMGMT/SITE Domain)
| Table | Purpose | Owner Domain | Soft Delete |
|---|---|---|---|
users | User accounts | USERMGMT | Yes |
sites | Physical lab locations | SITE | Yes |
user_visible_sites | User-site access junction | USERMGMT | No |
users
| Column | Type | Description | FK |
|---|---|---|---|
id | UUID | Primary key | - |
username | VARCHAR | Login username | - |
email | VARCHAR | Email address | - |
display_name | VARCHAR | Display name | - |
user_type | SMALLINT | 1-5 (see below) | - |
email_verified | BOOLEAN | Email verification status | - |
blocked | BOOLEAN | Account blocked | - |
mfa_enabled | BOOLEAN | MFA status | - |
settings | JSON | User preferences | - |
logged_in_site_id | UUID | Current site | sites.id |
cognito_group | VARCHAR | Cognito group membership | - |
deleted_at | DATETIME | Soft delete timestamp | - |
User Type Values:
| Value | Constant | Description |
|---|---|---|
| 1 | JUNIOR | Junior user |
| 2 | SENIOR | Senior user |
| 3 | CLIENT_ADMIN | Client administrator |
| 4 | SUPER_ADMIN | Super administrator |
| 5 | MANAGER | Manager |
sites
| Column | Type | Description | FK |
|---|---|---|---|
id | UUID | Primary key | - |
name | VARCHAR | Site name | - |
code | VARCHAR | Site code | - |
custom_s3_folder_name | VARCHAR | Custom S3 folder | - |
deleted_at | DATETIME | Soft delete timestamp | - |
3.6 Instrument Tables (INSTRUMENTS Domain)
| Table | Purpose | Owner Domain | Soft Delete |
|---|---|---|---|
thermocyclers | Thermocycler registry | INSTRUMENTS | No |
thermocycler_models | Thermocycler model catalog | INSTRUMENTS | No |
extraction_instruments | Extraction device instances | INSTRUMENTS | No |
extraction_models | Extraction model catalog | INSTRUMENTS | No |
lots | Reagent lot tracking | INSTRUMENTS | No |
thermocyclers
| Column | Type | Description | FK |
|---|---|---|---|
id | UUID | Primary key | - |
thermocycler_type | VARCHAR | Device type | - |
thermocycler_serial_number | VARCHAR | Serial number | - |
thermocycler_plate_size | INT | Plate capacity (96, 384) | - |
thermocycler_model_id | UUID | Model reference | thermocycler_models.id |
site_id | UUID | Site ownership | sites.id |
extraction_instruments
| Column | Type | Description | FK |
|---|---|---|---|
id | UUID | Primary key | - |
extraction_instrument_name | VARCHAR | Device name | - |
extraction_model_id | UUID | Model reference | extraction_models.id |
site_id | UUID | Site ownership | sites.id |
3.7 Rules Engine Tables (RULES Domain)
| Table | Purpose | Owner Domain | Soft Delete |
|---|---|---|---|
rules | Rule definitions | RULES | No |
rule_mappings | Rule-target mappings | RULES | No |
rules
| Column | Type | Description | FK |
|---|---|---|---|
id | UUID | Primary key | - |
rule_name | VARCHAR | Display name | - |
programmatic_rule_name | VARCHAR | PHP class mapping | - |
precedence | INT | Execution order | - |
is_enabled | BOOLEAN | Rule enabled | - |
site_id | UUID | Site ownership | sites.id |
Programmatic Rule Mapping: programmatic_rule_name maps to App\Analyzer\Rules\{StudlyCase}Rule.
3.8 Workflow Tables (WORKFLOW Domain)
| Table | Purpose | Owner Domain | Soft Delete |
|---|---|---|---|
tags | Run categorization tags | WORKFLOW | Yes |
run_tag | Run-tag junction | WORKFLOW | No |
comments | Polymorphic threaded comments | WORKFLOW | No |
notifications | User notifications | WORKFLOW | No |
comments
| Column | Type | Description | FK |
|---|---|---|---|
id | UUID | Primary key | - |
user_id | UUID | Author | users.id |
parent_id | UUID | Parent comment (nullable) | comments.id |
commentable_id | UUID | Polymorphic target ID | - |
commentable_type | VARCHAR | Polymorphic type (Run, Well) | - |
text | TEXT | Comment content | - |
tags
| Column | Type | Description | FK |
|---|---|---|---|
id | UUID | Primary key | - |
name | VARCHAR | Tag name | - |
archive | BOOLEAN | Archive tag | - |
site_id | UUID | Site ownership | sites.id |
deleted_at | DATETIME | Soft delete timestamp | - |
3.9 Audit Tables (AUDIT Domain)
| Table | Purpose | Owner Domain | Soft Delete |
|---|---|---|---|
audits | Immutable audit trail | AUDIT | No |
audits
| Column | Type | Description | FK |
|---|---|---|---|
id | UUID | Primary key | - |
username | VARCHAR | Actor username | - |
area | VARCHAR | Functional area | - |
change_type | VARCHAR | Type of change | - |
action | VARCHAR | Specific action | - |
change_location | VARCHAR | Context/resource | - |
value_before | TEXT | Previous value | - |
value_after | TEXT | New value | - |
site_name | VARCHAR | Site context (nullable) | - |
created_at | DATETIME(6) | Microsecond timestamp | - |
Audit records are immutable. No updates or deletes permitted.
3.10 System Tables
| Table | Purpose | Owner Domain | Soft Delete |
|---|---|---|---|
client_configurations | Key-value application settings | SETTINGS | No |
global_mappings | Cross-site configuration sync | SETTINGS | No |
features | Feature flags | SETTINGS | No |
help_items | Help documentation | SYSTEM | No |
help_videos | Help video links | SYSTEM | No |
jobs | Laravel queue jobs | SYSTEM | No |
telescope_entries | Debugging/monitoring (dev) | SYSTEM | No |
client_configurations
| Column | Type | Description | FK |
|---|---|---|---|
id | UUID | Primary key | - |
name | VARCHAR | Configuration key | - |
value | TEXT | Configuration value | - |
type | VARCHAR | Value type hint | - |
site_id | UUID | Site ownership (nullable) | sites.id |
4. Foreign Key Relationships
4.1 Core Hierarchy
sites
└── runs (site_id)
└── wells (run_id)
└── observations (well_id)
4.2 Configuration Hierarchy
mixes
└── targets (mix_id)
└── observations (target_id)
roles
└── role_to_target_mappings (role_id)
└── control_labels (role_id)
└── westgard_limits (role_id)
4.3 User-Site Relationship
users ←→ user_visible_sites ←→ sites
└── logged_in_site_id → sites
4.4 Error-Resolution Relationship
error_codes
└── resolution_codes (error_code_id)
└── resolution_messages (resolution_message_id)
wells.error_code_id → error_codes.id (single FK)
wells.lims_status → lims_statuses.code (string lookup)
5. Index Documentation
5.1 Primary Indexes
All tables use UUID primary keys with clustered index.
5.2 Notable Secondary Indexes
| Table | Index Columns | Purpose |
|---|---|---|
runs | site_id, runfile_created_at | Site-scoped listing |
wells | run_id | Well lookup by run |
wells | site_id, export_date | Export status queries |
observations | well_id | Observation lookup |
audits | username, created_at | Audit trail queries |
westgard_limits | extraction_instrument_id, target_id, role_id, lot_id | QC lookups |
6. Soft Delete Conventions
6.1 Tables Using Soft Delete
| Table | Reason |
|---|---|
users | Audit trail preservation |
sites | Historical reference |
error_codes | Historical wells reference |
control_labels | Configuration history |
westgard_limits | QC history |
tags | Run categorization history |
6.2 Soft Delete Behavior
- Column:
deleted_at(nullable datetime) - Default scope excludes soft-deleted records
withTrashed()scope includes soft-deletedonlyTrashed()scope returns only soft-deleted- Soft-deleted records preserved for audit/historical joins
7. Related Documents
| Document | Relevance |
|---|---|
| SDS: Data Architecture | Entity relationships and data flow |
| SDS: Configuration Reference | Configuration key documentation |
| SDD: Architecture | Original schema documentation |
| SRS: KITCFG | Kit configuration requirements |
| SRS: ERRORCODES | Error code requirements |