Re: [Announcement] ​A Bitemporal Solution for PostgreSQL (Beta) - Mailing list pgsql-general

Dear all,

I am gald to share an open-source project I've been developing in my free time. It's a bitemporal solution built entirely within PostgreSQL, and while it's still in its early beta stages, it has the ambition to provide a comprehensive framework for temporal data management. The project is inspired by the need for a simple, yet powerful way to handle historical records and query data as it existed at any point in time.

This solution is designed to be highly configurable and uses views with INSTEAD OF triggers to seamlessly manage inserts, updates, and deletes across _current and _history tables. Key features include:

• Bitemporal Support: Tracks both "valid time" (when a fact is true in the real world) and "transaction time" (when the fact was recorded in the database).

• Automatic Historicization: The system automatically creates and manages historical records, ensuring a complete, immutable audit trail.

• Trigger-based Logic: The core logic is encapsulated in a vrsn.trigger_handler() function, making it easy to apply to any table via a view.

• Configurable Behavior: A set of parameters and state variables (vrsn.tar_state_variables) allows for fine-grained control over versioning, conflict resolution, and other behaviors.

• Adherence to Bitemporal Theory: The framework is designed to be fully compliant with the bitemporal model proposed by Snodgrass.

• Pure SQL & Transparency: The solution is built using pure PostgreSQL functions and types, requiring no external extensions, and it maintains a transparent data structure that does not mask the underlying tables.

This project is a work in progress, and I would love to get your feedback and collaboration to improve it. If the concept interests you and you'd like to dive into the code or offer some guidance, your expertise would be highly valued.

Thank you for your time and consideration.

Bye

--

Domenico L.