Foreign Key Standard

Purpose

The Foreign Key Standard defines relationship rules for future PostgreSQL specifications.

Relationship Types

Relationship	Standard
Intra-module relationship	May use database foreign keys where lifecycle and ownership are inside one module.
Cross-module reference	Store identifier reference; do not rely on direct table ownership.
External system reference	Store provider, external ID, retrieval context, and provenance.
Projection lineage	Store source domain and source ID references for rebuild and audit.

Rules

• Foreign keys are preferred inside a module-owned persistence area when they support integrity.
• Cross-module database-level foreign keys require explicit architecture review because they can block future extraction.
• Cross-module references must not grant write access to the referenced module.
• Deletion behavior must be explicit: restrict, cascade, soft delete, archive, or retain reference.
• Provider references must preserve source system and external identifier.

Cross-Module Reference Pattern

flowchart LR
    Bid[(bid_workspaces)]
    OpportunityId[TenderOpportunityId reference]
    Opportunity[(opportunity tables)]

    Bid --> OpportunityId
    OpportunityId -. reference only .-> Opportunity

Review Questions

• Is the relationship inside one module boundary?
• Would a database foreign key make future service extraction harder?
• Is tenant context preserved on both sides?
• What happens when the referenced record is archived or deleted?