Database ERD - PyCharter Schema¶

This document provides an Entity Relationship Diagram (ERD) for the PyCharter database schema.

ERD Diagram¶

erDiagram
    %% Core Component Tables
    schemas {
        uuid id PK
        string title
        uuid data_contract_id FK
        string version
        json schema_data
        datetime created_at
        datetime updated_at
    }

    coercion_rules {
        uuid id PK
        string title
        uuid data_contract_id FK
        string description
        string version
        json rules
        uuid schema_id FK
        datetime created_at
        datetime updated_at
    }

    validation_rules {
        uuid id PK
        string title
        uuid data_contract_id FK
        string description
        string version
        json rules
        uuid schema_id FK
        datetime created_at
        datetime updated_at
    }

    metadata_records {
        uuid id PK
        string title
        uuid data_contract_id FK
        string version
        string status
        string type
        text description
        json governance_rules
        datetime created_at
        datetime updated_at
        string created_by
        string updated_by
    }

    owners {
        string id PK
        string name
        string email UK
        string team
        json additional_info
        datetime created_at
        datetime updated_at
    }

    %% Entity Tables
    systems {
        uuid id PK
        string name UK
        string app_id
        text description
        datetime created_at
        datetime updated_at
    }

    domains {
        uuid id PK
        string name UK
        text description
        datetime created_at
        datetime updated_at
    }

    %% Central Join Table
    data_contracts {
        uuid id PK
        string name
        string version
        string status
        text description
        uuid schema_id FK
        uuid coercion_rules_id FK
        uuid validation_rules_id FK
        uuid metadata_record_id FK
        string schema_version
        string coercion_rules_version
        string validation_rules_version
        string metadata_version
        datetime created_at
        datetime updated_at
        string created_by
        string updated_by
    }

    %% Join Tables (Many-to-Many)
    metadata_record_system_pulls {
        uuid id PK
        uuid metadata_record_id FK
        uuid system_id FK
        datetime created_at
    }

    metadata_record_system_pushes {
        uuid id PK
        uuid metadata_record_id FK
        uuid system_id FK
        datetime created_at
    }

    metadata_record_system_sources {
        uuid id PK
        uuid metadata_record_id FK
        uuid system_id FK
        datetime created_at
    }

    metadata_record_domains {
        uuid id PK
        uuid metadata_record_id FK
        uuid domain_id FK
        datetime created_at
    }

    %% Ownership Join Tables (Many-to-Many)
    metadata_record_business_owners {
        uuid id PK
        uuid metadata_record_id FK
        string owner_id FK
        datetime created_at
    }

    metadata_record_bu_sme {
        uuid id PK
        uuid metadata_record_id FK
        string owner_id FK
        datetime created_at
    }

    metadata_record_it_application_owners {
        uuid id PK
        uuid metadata_record_id FK
        string owner_id FK
        datetime created_at
    }

    metadata_record_it_sme {
        uuid id PK
        uuid metadata_record_id FK
        string owner_id FK
        datetime created_at
    }

    metadata_record_support_lead {
        uuid id PK
        uuid metadata_record_id FK
        string owner_id FK
        datetime created_at
    }

    %% Quality Assurance Tables
    quality_metrics {
        uuid id PK
        string schema_id
        string schema_version
        uuid data_contract_id FK
        float overall_score
        float violation_rate
        float completeness
        float accuracy
        integer record_count
        integer valid_count
        integer invalid_count
        integer violation_count
        json field_scores
        json threshold_breaches
        string passed
        datetime check_timestamp
        json additional_metadata
        datetime created_at
        datetime updated_at
        string created_by
    }

    quality_violations {
        uuid id PK
        string schema_id
        string schema_version
        uuid data_contract_id FK
        string record_identifier
        json record_data
        string field_name
        string error_type
        text error_message
        string severity
        string status
        datetime resolved_at
        string resolved_by
        datetime check_timestamp
        json additional_metadata
        datetime created_at
        datetime updated_at
    }

    %% Relationships - Core Components (Many-to-One from components to data_contracts)
    schemas }o--|| data_contracts : "belongs_to"
    coercion_rules }o--|| data_contracts : "belongs_to"
    validation_rules }o--|| data_contracts : "belongs_to"
    metadata_records }o--|| data_contracts : "belongs_to"

    %% Relationships - Schema Components (Many-to-One from rules to schemas)
    coercion_rules }o--o| schemas : "optional_link"
    validation_rules }o--o| schemas : "optional_link"

    %% Relationships - Many-to-Many (via Join Tables)
    metadata_records ||--o{ metadata_record_system_pulls : "pulls_from"
    systems ||--o{ metadata_record_system_pulls : "pulled_by"

    metadata_records ||--o{ metadata_record_system_pushes : "pushes_to"
    systems ||--o{ metadata_record_system_pushes : "pushed_to"

    metadata_records ||--o{ metadata_record_system_sources : "sources_from"
    systems ||--o{ metadata_record_system_sources : "sourced_by"

    metadata_records ||--o{ metadata_record_domains : "belongs_to"
    domains ||--o{ metadata_record_domains : "contains"

    metadata_records ||--o{ metadata_record_business_owners : "has"
    owners ||--o{ metadata_record_business_owners : "owns"

    metadata_records ||--o{ metadata_record_bu_sme : "has"
    owners ||--o{ metadata_record_bu_sme : "is_sme"

    metadata_records ||--o{ metadata_record_it_application_owners : "has"
    owners ||--o{ metadata_record_it_application_owners : "is_it_owner"

    metadata_records ||--o{ metadata_record_it_sme : "has"
    owners ||--o{ metadata_record_it_sme : "is_it_sme"

    metadata_records ||--o{ metadata_record_support_lead : "has"
    owners ||--o{ metadata_record_support_lead : "is_support_lead"

    %% Relationships - Quality Assurance (Many-to-One to data_contracts)
    quality_metrics }o--o| data_contracts : "optional_link"
    quality_violations }o--o| data_contracts : "optional_link"

Table Descriptions¶

Core Component Tables¶

`schemas`¶

Stores JSON Schema definitions that define the structure and validation rules for data.

Model: SchemaModel (file: schema.py)

Key Fields: - id: Primary key (UUID) - title: Schema title - data_contract_id: Foreign key to data_contracts (required) - version: Schema version (e.g., "1.0.0") - schema_data: JSON schema definition

Unique Constraint: (data_contract_id, version)

Relationships: - Many-to-one with data_contracts (required) - One-to-many with coercion_rules (optional) - One-to-many with validation_rules (optional)

`coercion_rules`¶

Defines data type coercion rules for schema fields.

Model: CoercionRuleModel (file: coercion_rule.py)

Key Fields: - id: Primary key (UUID) - title: Rule title - data_contract_id: Foreign key to data_contracts (required) - version: Rules version - rules: JSON object containing coercion rules - schema_id: Optional foreign key to schemas

Unique Constraint: (data_contract_id, version)

Relationships: - Many-to-one with data_contracts (required, via data_contract_id) - Many-to-one with schemas (optional, via schema_id)

`validation_rules`¶

Defines validation rules for schema fields.

Model: ValidationRuleModel (file: validation_rule.py)

Key Fields: - id: Primary key (UUID) - title: Rule title - data_contract_id: Foreign key to data_contracts (required) - version: Rules version - rules: JSON object containing validation rules - schema_id: Optional foreign key to schemas

Unique Constraint: (data_contract_id, version)

Relationships: - Many-to-one with data_contracts (required, via data_contract_id) - Many-to-one with schemas (optional, via schema_id)

`metadata_records`¶

Stores comprehensive metadata including audit information, data lineage, governance rules, and ownership.

Model: MetadataRecordModel (file: metadata_record.py)

Key Fields: - id: Primary key (UUID) - title: Metadata title - data_contract_id: Foreign key to data_contracts (required) - version: Metadata version - status: e.g., "active", "deprecated", "draft" - type: e.g., "object" - description: Text description - governance_rules: JSON object containing governance rules - created_at, updated_at, created_by, updated_by: Audit fields

Unique Constraint: (data_contract_id, version)

Relationships: - Many-to-one with data_contracts (required) - Many-to-many with systems via: - metadata_record_system_pulls (pulls_from) - metadata_record_system_pushes (pushes_to) - metadata_record_system_sources (system_sources) - Many-to-many with domains via metadata_record_domains - Many-to-many with owners via: - metadata_record_business_owners (business_owners) - metadata_record_bu_sme (bu_sme) - metadata_record_it_application_owners (it_application_owners) - metadata_record_it_sme (it_sme) - metadata_record_support_lead (support_lead)

Note: Governance rules are stored as JSON in this table, not as a separate table. Ownership relationships are stored in join tables linking to the owners table.

`owners`¶

Lookup table of owner entities (people/teams) that can be referenced by metadata_records.

Model: OwnerModel (file: owner.py)

Key Fields: - id: Primary key (string, e.g., "lincoln_mak", "operations-team") - name: Display name (optional, defaults to id if not provided) - email: Owner email address (unique if provided) - team: Team name (e.g., "data-engineering") - additional_info: JSON object with additional owner metadata

Unique Constraint: (email) if email is provided

Relationships: - Many-to-many with metadata_records via: - metadata_record_business_owners (business_owners) - metadata_record_bu_sme (bu_sme) - metadata_record_it_application_owners (it_application_owners) - metadata_record_it_sme (it_sme) - metadata_record_support_lead (support_lead)

Note: This table stores owner entities. Ownership relationships are stored in join tables linking metadata_records to owners.

Entity Tables¶

`systems`¶

Represents external systems that metadata records interact with (pulls_from, pushes_to, system_sources).

Model: SystemModel (file: system.py)

Key Fields: - id: Primary key (UUID) - name: Unique system name (e.g., "1811", "ICC", "1846") - app_id: Application ID for the system (optional) - description: System description

Unique Constraint: (name)

Relationships: - Many-to-many with metadata_records via: - metadata_record_system_pulls (pulls_from) - metadata_record_system_pushes (pushes_to) - metadata_record_system_sources (system_sources)

`domains`¶

Represents business domains that metadata records belong to.

Model: DomainModel (file: domain.py)

Key Fields: - id: Primary key (UUID) - name: Unique domain name (e.g., "IOC") - description: Domain description

Unique Constraint: (name)

Relationships: - Many-to-many with metadata_records via metadata_record_domains

Central Join Table¶

`data_contracts`¶

The central table that links all components of a data contract. Each contract represents a versioned contract for a specific dataset.

Model: DataContractModel (file: data_contract.py)

Key Fields: - id: Primary key (UUID) - name: Contract name (e.g., "template_contract") - version: Contract version (e.g., "1.0.0") - status: Contract status (e.g., "active", "deprecated", "draft") - description: Contract description - schema_id: Foreign key to schemas (optional, nullable) - coercion_rules_id: Foreign key to coercion_rules (optional) - validation_rules_id: Foreign key to validation_rules (optional) - metadata_record_id: Foreign key to metadata_records (optional) - schema_version, coercion_rules_version, validation_rules_version, metadata_version: Version tracking - created_at, updated_at, created_by, updated_by: Audit fields

Unique Constraint: (name, version)

Relationships: - One-to-many with schemas (optional, nullable) - One-to-many with coercion_rules (optional) - One-to-many with validation_rules (optional) - One-to-many with metadata_records (optional)

Note: Ownership and governance rules are stored within metadata_records, not as separate foreign keys in data_contracts.

Join Tables (Many-to-Many Relationships)¶

`metadata_record_system_pulls`¶

Links metadata_records to systems they pull data from.

Model: MetadataRecordSystemPull

Key Fields: - id: Primary key (UUID) - metadata_record_id: Foreign key to metadata_records - system_id: Foreign key to systems - created_at: Timestamp

Unique Constraint: (metadata_record_id, system_id)

`metadata_record_system_pushes`¶

Links metadata_records to systems they push data to.

Model: MetadataRecordSystemPush

Key Fields: - id: Primary key (UUID) - metadata_record_id: Foreign key to metadata_records - system_id: Foreign key to systems - created_at: Timestamp

Unique Constraint: (metadata_record_id, system_id)

`metadata_record_system_sources`¶

Links metadata_records to their source systems.

Model: MetadataRecordSystemSource

Key Fields: - id: Primary key (UUID) - metadata_record_id: Foreign key to metadata_records - system_id: Foreign key to systems - created_at: Timestamp

Unique Constraint: (metadata_record_id, system_id)

`metadata_record_domains`¶

Links metadata_records to domains.

Model: MetadataRecordDomain

Key Fields: - id: Primary key (UUID) - metadata_record_id: Foreign key to metadata_records - domain_id: Foreign key to domains - created_at: Timestamp

Unique Constraint: (metadata_record_id, domain_id)

`metadata_record_business_owners`¶

Links metadata_records to owners as business owners.

Model: MetadataRecordBusinessOwner

Key Fields: - id: Primary key (UUID) - metadata_record_id: Foreign key to metadata_records - owner_id: Foreign key to owners (string) - created_at: Timestamp