Compliance module entity-relationship diagram validation fails when adding new document type

QMS Qualio
, , , , , , , ,
1

I’m working on extending our compliance document schema in Qualio 2022.2 and running into ERD validation errors that are blocking progress. We need to add a new document type entity for regulatory submissions that links to our existing audit and approval workflows.

The ERD editor throws validation errors when I try to establish foreign key constraints between the new SubmissionDocument entity and the existing ComplianceAudit table. The error indicates a circular dependency issue:


ERROR: Circular reference detected in entity relationship
Entity: SubmissionDocument -> ComplianceAudit -> ApprovalWorkflow -> SubmissionDocument
Constraint validation failed at foreign key FK_submission_audit

Our requirement is that submission documents must reference audit records (for traceability), but audits also need to reference the documents they’re reviewing. This creates the circular dependency the validator is rejecting.

Has anyone successfully modeled similar bidirectional relationships in Qualio’s compliance module? The integration with audit and approval flows is critical for our regulatory requirements, so we can’t simplify the model without losing essential functionality.

2

The validator doesn’t have configuration options to bypass circular dependency checks - that’s by design for data integrity. However, you can break the cycle at the ApprovalWorkflow level. Instead of having ApprovalWorkflow maintain a hard FK back to SubmissionDocument, store the document reference as metadata (string ID or JSON attribute). The workflow can still access the document through API calls, but you eliminate the circular FK constraint that’s failing validation.

3

I’ve encountered similar ERD validation challenges when extending compliance schemas. The circular dependency error you’re seeing is actually Qualio’s validator protecting you from potential infinite loops in the relationship graph.

The key is understanding that bidirectional relationships don’t require bidirectional foreign keys at the database level. You can model this using a junction table or by making one direction navigable only through queries rather than hard constraints. Have you considered using an association table like AuditDocumentLink that references both entities without creating the circular FK dependency?

4

Great question about audit trail integrity - this is where understanding Qualio’s compliance module architecture really matters. The solution requires breaking the circular FK dependency while maintaining full traceability for regulatory requirements.

Entity-Relationship Diagram Validation: The ERD validator in Qualio 2022.2 performs deep transitive dependency analysis specifically to prevent circular references that could cause cascade delete issues or infinite loops in relationship traversal. Your error indicates a four-entity cycle that must be broken at one point.

Recommended Schema Design:


SubmissionDocument (PK: doc_id)
  ├─> ComplianceAudit (FK: audit_id, document_id)
      ├─> ApprovalWorkflow (FK: workflow_id, audit_id)
          └─> workflow_metadata (JSON: {"source_document_id": "..."})

Foreign Key Constraints: Implement hard FKs for SubmissionDocument → ComplianceAudit and ComplianceAudit → ApprovalWorkflow. For the reverse reference from ApprovalWorkflow to SubmissionDocument, use a metadata field instead of a FK. This passes ERD validation while maintaining the relationship.

Compliance Document Schema: In your SubmissionDocument entity, add a related_workflows computed field that queries ApprovalWorkflow records. Qualio’s ORM will handle this efficiently:


SELECT w.* FROM ApprovalWorkflow w
JOIN ComplianceAudit a ON w.audit_id = a.audit_id
WHERE a.document_id = [current_doc_id]

Integration with Audit and Approval Flows: Qualio’s audit logging operates at the entity change level, not the FK traversal level. Whether you navigate via FK or query doesn’t affect audit trail completeness. Every entity modification is logged with full context including the user action, timestamp, and changed fields. The audit trail will show:

  • Document creation/modification events
  • Audit record linkage (via the hard FK)
  • Workflow state changes (with document_id in metadata)
  • Approval decisions tied to specific audits

The key insight is that audit trail integrity comes from entity-level change tracking, not relationship navigation method. Your compliance reports and regulatory submissions will have complete traceability because all entity changes are logged regardless of how the relationships are queried.

Implementation Steps:

  1. Remove the FK from ApprovalWorkflow back to SubmissionDocument
  2. Add source_document_id as a regular varchar field (not FK) in ApprovalWorkflow
  3. Create a database view or computed field for reverse navigation
  4. Update your application layer to populate source_document_id when creating workflows
  5. Re-run ERD validation - it should pass

This design pattern is standard in Qualio compliance implementations where bidirectional navigation is required but circular FKs would violate validation rules. You maintain full functionality and audit integrity while satisfying the ERD validator.

5

Adding to the previous response - I’d recommend checking your compliance document schema design against Qualio’s reference models. In our implementation, we solved a similar issue by separating the audit relationship into two phases: document-to-audit (hard FK) and audit-findings-to-document (soft reference using document ID in a JSON field). This maintains traceability without violating ERD constraints.

6

The unidirectional approach with reverse queries makes sense. Before I refactor, can you clarify how this impacts audit trail integrity? Will Qualio’s built-in audit logging still capture the full relationship chain if we’re using queries instead of FK navigation for one direction?

7

I’ve worked extensively with Qualio’s compliance module ERD constraints. One pattern that works well is implementing a unidirectional FK chain with reverse lookups handled at the application layer. Your schema would be: SubmissionDocument → ComplianceAudit → ApprovalWorkflow, with no FK from ApprovalWorkflow back to SubmissionDocument. When the workflow needs document data, it queries using the audit’s document_id field rather than traversing a FK relationship. This satisfies the validator while maintaining full bidirectional navigation capability.

8

Thanks for the suggestions. I tried the junction table approach but I’m still getting validation errors. The ERD editor seems to be checking transitive dependencies - even with AuditDocumentLink as an intermediary, it detects that SubmissionDocument → AuditDocumentLink → ComplianceAudit → ApprovalWorkflow → SubmissionDocument still forms a cycle.

Is there a way to configure the validator to allow certain circular patterns? Our approval workflow genuinely needs to reference back to the originating document.