MIME-Version: 1.0
From: Lorusso Domenico <domenico.l76@gmail.com>
Date: Thu, 11 Sep 2025 17:08:10 +0200
Message-ID: <CAJMpnG5FxZw7dJd+EUu19GoETShT_AUGqx1SG6hsyMoTOH61=w@mail.gmail.com>
Subject: =?UTF-8?Q?=5BAnnouncement=5D_=E2=80=8BA_Bitemporal_Solution_for_PostgreS?=
	=?UTF-8?Q?QL_=28Beta=29?=
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000fced72063e87e92a"
Archived-At: <https://www.postgresql.org/message-id/CAJMpnG5FxZw7dJd%2BEUu19GoETShT_AUGqx1SG6hsyMoTOH61%3Dw%40mail.gmail.com>
Precedence: bulk

--000000000000fced72063e87e92a
Content-Type: text/plain; charset="UTF-8"

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.

--000000000000fced72063e87e92a
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div><div class=3D"gmail_default" style=3D"font-family:gar=
amond,serif;font-size:large"><span style=3D"font-family:Arial,Helvetica,san=
s-serif;font-size:small">Dear all,</span></div><p><span class=3D"gmail-sele=
cted">I am gald to share an open-source project I&#39;ve been developing in=
 my free time. It&#39;s a bitemporal solution built entirely within Postgre=
SQL, and while it&#39;s still in its early beta stages, it has the ambition=
 to provide a comprehensive framework for temporal data management. The pro=
ject is inspired by the need for a simple, yet powerful way to handle histo=
rical records and query data as it existed at any point in time.</span></p>=
<p><span class=3D"gmail-selected">This solution is designed to be highly co=
nfigurable and uses views with </span><code><span class=3D"gmail-selected">=
INSTEAD OF</span></code><span class=3D"gmail-selected"> triggers to seamles=
sly manage inserts, updates, and deletes across </span><code><span class=3D=
"gmail-selected">_current</span></code><span class=3D"gmail-selected"> and =
</span><code><span class=3D"gmail-selected">_history</span></code><span cla=
ss=3D"gmail-selected"> tables. Key features include:</span></p><ul><li><p><=
strong><span class=3D"gmail-selected">Bitemporal Support</span></strong><sp=
an class=3D"gmail-selected">: Tracks both &quot;valid time&quot; (when a fa=
ct is true in the real world) and &quot;transaction time&quot; (when the fa=
ct was recorded in the database).</span></p></li><li><p><strong><span class=
=3D"gmail-selected">Automatic Historicization</span></strong><span class=3D=
"gmail-selected">: The system automatically creates and manages historical =
records, ensuring a complete, immutable audit trail.</span></p></li><li><p>=
<strong><span class=3D"gmail-selected">Trigger-based Logic</span></strong><=
span class=3D"gmail-selected">: The core logic is encapsulated in a </span>=
<code><span class=3D"gmail-selected">vrsn.trigger_handler()</span></code><s=
pan class=3D"gmail-selected"> function, making it easy to apply to any tabl=
e via a view.</span></p></li><li><p><strong><span class=3D"gmail-selected">=
Configurable Behavior</span></strong><span class=3D"gmail-selected">: A set=
 of parameters and state variables (</span><code><span class=3D"gmail-selec=
ted">vrsn.tar_state_variables</span></code><span class=3D"gmail-selected">)=
 allows for fine-grained control over versioning, conflict resolution, and =
other behaviors.</span></p></li><li><p><strong><span class=3D"gmail-selecte=
d">Adherence to Bitemporal Theory</span></strong><span class=3D"gmail-selec=
ted">: The framework is designed to be fully compliant with the bitemporal =
model proposed by Snodgrass.</span></p></li><li><p><strong><span class=3D"g=
mail-selected">Pure SQL &amp; Transparency</span></strong><span class=3D"gm=
ail-selected">: The solution is built using pure PostgreSQL functions and t=
ypes, requiring </span><strong><span class=3D"gmail-selected">no external e=
xtensions</span></strong><span class=3D"gmail-selected">, and it maintains =
a </span><strong><span class=3D"gmail-selected">transparent data structure<=
/span></strong><span class=3D"gmail-selected"> that does not mask the under=
lying tables.</span></p></li></ul><p><span class=3D"gmail-selected">This pr=
oject is a work in progress, and I would love to get your feedback and coll=
aboration to improve it. If the concept interests you and you&#39;d like to=
 dive into the code or offer some guidance, your expertise would be highly =
valued.</span></p><p><span class=3D"gmail-selected">Thank you for your time=
 and consideration.</span></p><p><span class=3D"gmail-selected"><span class=
=3D"gmail_default" style=3D"font-family:garamond,serif;font-size:large">B</=
span><span class=3D"gmail_default" style=3D"font-family:garamond,serif;font=
-size:large">ye</span></span></p><br clear=3D"all"></div><div><br></div><sp=
an class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D=
"gmail_signature" data-smartmail=3D"gmail_signature">Domenico L.<br><br></d=
iv></div>

--000000000000fced72063e87e92a--