Software Design Specification: Data Architecture

Document Type: System-Level (Tier 1) Status: Draft Last Updated: 2026-01-25

---

1. Purpose

This document describes the global data architecture of the PCR Analysis System. It defines the high-level entity model, database technology choices, multi-tenancy strategy, and cross-domain relationships.

This document does not:

• Define domain-internal entity details (see domain design documents)
• Provide full table schemas (see Database Reference)
• Specify business rules for data (see SRS and domain documents)

---

2. Database Technology

2.1 Primary Database

Property	Value
Engine	Amazon Aurora MySQL 8.0
Deployment	Aurora Serverless v2
Connection	Via Laravel/Vapor with connection pooling
Character Set	utf8mb4

Rationale: Aurora Serverless v2 provides auto-scaling for variable workloads, cost optimization during low-usage periods, and managed high availability. MySQL was selected for broad ecosystem support and Laravel compatibility.

2.2 Primary Key Strategy

All entities use UUIDs (ordered UUIDs via Laravel's HasUuid trait) as primary keys:

• Non-sequential for security (prevents enumeration attacks)
• Globally unique across all tables
• 36-character string format stored as CHAR(36)

2.3 Audit Database

Audit records are stored in a separate database connection (database.audit) for isolation and compliance. This prevents audit data from being accidentally modified during application operations.

---

3. Multi-Tenancy Strategy

3.1 Site-Based Data Partitioning

The system implements logical multi-tenancy using site_id foreign keys:

Key characteristics:

• Sites represent physical laboratory locations
• Most configuration and transactional data is scoped by site_id
• Users can have access to multiple sites via user_visible_sites junction table
• Site deletion is soft delete (preserves audit trail)

3.2 Site-Scoped Entities

Entity Type	Examples	Scoping Pattern
Configuration	Mixes, Targets, Roles, Error Codes, LIMS Statuses	Direct site_id FK
Transactional	Runs, Wells, Observations	Via parent (Run → Site)
QC Data	Westgard Limits, Control Range Settings	Direct site_id FK
User Access	Visible Sites	Junction table user_visible_sites

3.3 Query Filtering

Site filtering is implemented via Laravel query scopes (FilterableBySite trait):

// All site-scoped queries automatically filter by user's logged_in_site_id
Well::forSite($siteId)->where(...)

---

4. Global Entity Model

4.1 High-Level ER Diagram

This diagram illustrates cross-domain entity relationships. Domain-internal relationships are documented in respective domain design documents.

4.2 Domain Boundaries

Domain	Owned Entities	Key Relationships
KITCFG	Kit, Mix, Target, Dye, Role	Kit → Mixes → Targets
RUNFILE	Run, Well, Observation, RunMix, RunTarget	Run → Wells → Observations
CONFIGIO	ErrorCode, ResolutionCode, LimsStatus	Referenced by Wells
USERMGMT	User, Site	User ↔ Sites (many-to-many)
AUDIT	Audit	Links to User, Run, Well
QC	WestgardLimit, WestgardEvent, ControlRangeSetting	Links to Target, Role, Lot

---

5. Key Cross-Domain Relationships

5.1 Run Processing Pipeline

Data flow:

1. Run file imported → creates runs record
2. Wells parsed → creates wells records linked to mixes
3. Observations created → links wells to targets
4. Analysis rules execute → may assign error_code_id or lims_status
5. QC evaluation → updates westgard_limits
6. Export → sets export_date on wells

5.2 Kit Configuration Hierarchy

Purpose: Kits bundle mixes, which contain targets (analytes). Each target uses a dye for fluorescence detection. Roles (PC, NC, Sample) map to targets for control validation.

5.3 Well State Composition

Wells do not have a single status field. State is computed from multiple indicators:

Indicator	Column	Meaning
Error state	error_code_id	NULL = no error, FK = has error
LIMS outcome	lims_status	String code (DETECTED, RPT, etc.)
Export state	export_date	NULL = not exported, datetime = exported
Exclusion	exclude	Boolean flag for manual exclusion
Resolution	resolution_codes	Pipe-delimited applied resolution codes

Mutual exclusivity: A well has either an error_code_id OR a lims_status, never both simultaneously after analysis completes.

---

6. Soft Delete Patterns

The system uses Laravel's SoftDeletes trait for entities that require audit trail preservation:

Entity	Soft Delete?	Reason
users	Yes	Preserve audit trail references
sites	Yes	Preserve site-scoped historical data
control_labels	Yes	Preserve well references
westgard_limits	Yes	Preserve QC history
tags	Yes	Preserve run categorization history
runs	No	Never deleted (archival instead)
wells	No	Never deleted (part of run)

Implementation: Soft-deleted records have deleted_at timestamp set. Queries exclude soft-deleted records by default via global scope.

---

7. Audit Trail Approach

7.1 Audit Storage

Audit records are stored in the audits table with:

• Separate database connection - Isolates audit data from application transactions
• Microsecond timestamps - High-precision timing for audit sequence
• Immutable records - No UPDATE or DELETE operations permitted

7.2 Audit Record Structure

Column	Purpose
username	Actor who performed action
area	Functional area (Configuration, User Management, Runfile Report)
change_type	Entity type modified
action	Action performed (Created, Edit, Delete)
change_location	Context (resource identifier)
value_before	Previous value (for edits)
value_after	New value
site_name	Site context (nullable)

7.3 Audit Event Architecture

Audit events are captured via Laravel's event system:

1. Domain code dispatches AuditableEvent implementations
2. LogIntoDatabase listener persists to audit table
3. ~71 distinct event types across all domains

See SDS: Domain Audit for detailed audit domain design.

---

8. Data Integrity Patterns

8.1 Referential Integrity

• Foreign keys enforced at database level for critical relationships
• Cascade rules vary by relationship:
  • Run → Wells: Cascade delete (wells deleted with run)
  • Well → Error Code: SET NULL on error code delete
  • Site → Runs: RESTRICT (cannot delete site with runs)

8.2 Decimal Precision

QC statistics use high-precision decimals to prevent rounding errors:

-- Westgard limits use decimal(32,10) for sd, mean
sd DECIMAL(32, 10) NOT NULL DEFAULT 0,
mean DECIMAL(32, 10) NOT NULL DEFAULT 0

8.3 Enum Storage

Enums are stored as SMALLINT with application-level mapping:

Entity	Field	Values
error_codes	error_type	0=Label, 1=Error, 2=Warning, 3=Info, 4=Associate
roles	type	1=PC, 2=NC, 3=Sample, 4=Ignore, 5=Quant, 6=Quant&PC, 7=Crossover
lims_statuses	type	1=Warning, 2=Exclude, 4=Information
observations	final_cls	0=Neg, 1=Pos, 2=Amb, 3=Neg N/A, 4=Pos N/A, 5=Amb N/A

---

9. Related Documents

Document	Relationship
SDS: Architecture Overview	System-level architecture context
SDS: Database Reference	Full table schemas and column definitions
SDS: Security Architecture	Data access control patterns
SRS: Site Management	Site entity requirements
SRS: Audit Log	Audit trail requirements