Skip to content

solution_constraints.md

Latest commit

 

History

History
105 lines (77 loc) · 5.86 KB

solution_constraints.md

File metadata and controls

105 lines (77 loc) · 5.86 KB
Raw

PostgreSQL Bitemporal Solution: Constraints and Conventions

main - readme

This document outlines the key constraints and conventions essential for effectively utilizing the PostgreSQL bitemporal solution. Adhering to these guidelines ensures proper functionality, data integrity, and seamless integration with the framework's automated processes.

Bitemporal Record (bt_info) Field

All bitemporal tables within this solution are designed to include a specific field of type vrsn.bitemporal_record. This field is crucial as it encapsulates the temporal and audit metadata for each record.

CREATE TYPE vrsn.bitemporal_record AS
(
    user_ts_range tstzrange,
    db_ts_range tstzrange,
    audit_record jsonb
);
  • Standard Field Name: While not strictly enforced by the system, it is highly recommended to name this field bt_info for consistency and clarity across all bitemporal tables.
  • Mandatory Inclusion: Tables intended for bitemporal management should always define bt_info as a NOT NULL column, ideally as the first field in the table definition: 
    CREATE TABLE your_entity_current (
    bt_info vrsn.bitemporal_record NOT NULL,
    -- other primary key fields,
    -- other data fields
);
  • Reserved Names within Type: Although not strictly prohibited, it is discouraged to use user_ts_range, db_ts_range, or audit_record as standalone column names in your bitemporal tables, to avoid ambiguity with the components of the bt_info type itself.

Attribute Management Tables Structure

When attribute management is enabled (enable_history_attributes is TRUE), the system creates specific tables to handle attributes in a key-value fashion. These tables follow a distinct structure:

  • Bitemporal Fields: They contain their own bt_info field for historicization.
  • Parent Primary Key Fields: They include the primary key fields of their associated main entity table, allowing for a clear link to the parent record.
  • Attribute-Specific Fields: 
    CREATE TABLE IF NOT EXISTS vrsn.bitemporal_parent_attribute_table
(
    attribute_id bigint NOT NULL,
    idx text COLLATE pg_catalog."default" NOT NULL DEFAULT ''::text,
    attribute_value text COLLATE pg_catalog."default",
    CONSTRAINT bitemporal_parent_attribute_table_pk PRIMARY KEY (attribute_id, idx)
);
    • attribute_id: A unique identifier for the attribute, linking to vrsn.attribute_lineage.
    • idx: This field is crucial for handling arrays within JSON structures, where it serves as an index to maintain the order and identity of array elements.

Naming Conventions for Tables and Views

The solution relies on a standardized naming convention to automatically derive relationships and manage objects. While deviations are possible (e.g., placing tables and views in different schemas for privilege control), adherence to the suffixes is critical.

  • Default Naming Standard:

    • Current Data Tables: [entity_name]_current
    • Historical Data Tables: [entity_name]_history
    • Current View (Primary DML Interface): [entity_name]
    • Attribute Entity (View for Attributes): [entity_name]_attribute (This view then follows the same standard for its underlying tables if attribute management is enabled).
    • Schema: All related objects (current table, history table, current view, attribute view/table) typically reside within the same schema.

  • [entity_name]: This is the conventional base name for the object, and by default, it corresponds to the name of the current view (the DML entry point).

  • Reserved Suffixes: The following suffixes are utilized by the framework to deduce structures and are strongly advised against being used within custom table or view names, except as per the standard convention:

    • _current
    • _history
    • _attribute
    • _entity (if used as part of a view name from which _current is derived, e.g., my_table_entity_current)
    • _view (though not explicitly used for derivation, it's generally a descriptive term for views)

Autogenerated Fields Behavior

The bitemporal solution modifies the standard PostgreSQL behavior for autogenerated fields (GENERATED ALWAYS AS IDENTITY or GENERATED BY DEFAULT AS IDENTITY).

  • NULL in INSERT: Unlike standard PostgreSQL where passing NULL to an IDENTITY column generated BY DEFAULT inserts a generated value, in this solution, passing NULL for an autogenerated field in an INSERT operation is treated as equivalent to not passing the field at all. This means the field will not be populated with a generated value by default if NULL is explicitly given.

  • ALWAYS Generation: If a field's generation type is set to GENERATED ALWAYS AS IDENTITY, any value provided for that field in an INSERT or UPDATE operation, including NULL, will be ignored, and the system will always generate its own value.

  • Consequence: Therefore, field names designated as autogenerated keys should be considered reserved or subject to these specific behavioral rules when interacting with the bitemporal views.

Summary of Reserved/Discouraged Terms

  • Field Names:
    • bt_info (recommended name for the bitemporal record)
    • user_ts_range (component of vrsn.bitemporal_record)
    • db_ts_range (component of vrsn.bitemporal_record)
    • audit_record (component of vrsn.bitemporal_record)
    • Names of autogenerated fields (their behavior is modified by the framework).
  • Table/View Suffixes (for internal deduction):
    • _current
    • _history
    • _attribute
    • _entity
    • _view (less critical but good practice to avoid as custom part of names)