Temporal subjects

A temporal-table library stores database row states over system time. AuditLog stores the actor and intent behind a business action. Keep those facts separate and link an event to one exact row version.

AuditLog has no compile-time dependency on a temporal-table package. A compatible Ecto schema has a sys_period field containing a DateTime lower boundary. AuditLog.Subject.from_schema/2 reads that boundary from a structurally compatible %{from: %DateTime{}} map or a native %Postgrex.Range{lower: %DateTime{}}.

Insert

Build the subject from the struct returned by the repository write. The temporal integration must load sys_period after writes so the returned struct contains the new version's lower boundary.

Repo.transaction(fn ->
  shareholder =
    %Shareholder{}
    |> Shareholder.changeset(attrs)
    |> Repo.insert!()

  with {:ok, subject} <-
         AuditLog.Subject.from_schema(shareholder, type: "shareholder"),
       {:ok, _event} <-
         AuditLog.record(Repo, "shareholder.created", context, subject) do
    shareholder
  else
    {:error, error} -> Repo.rollback(error)
  end
end)

Update

The updated struct contains the post-change version boundary:

Repo.transaction(fn ->
  shareholder =
    shareholder
    |> Shareholder.changeset(attrs)
    |> Repo.update!()

  with {:ok, subject} <-
         AuditLog.Subject.from_schema(shareholder, type: "shareholder"),
       {:ok, _event} <-
         AuditLog.record(
           Repo,
           "shareholder.address_corrected",
           context,
           subject,
           reason: reason
         ) do
    shareholder
  else
    {:error, error} -> Repo.rollback(error)
  end
end)

Delete

Build the subject from the deleted struct. With a compatible temporal integration, it retains the outgoing version's lower boundary, which identifies the row in history:

Repo.transaction(fn ->
  shareholder = Repo.delete!(shareholder)

  with {:ok, subject} <-
         AuditLog.Subject.from_schema(shareholder, type: "shareholder"),
       {:ok, _event} <-
         AuditLog.record(
           Repo,
           "shareholder.deleted",
           context,
           subject,
           reason: reason
         ) do
    shareholder
  else
    {:error, error} -> Repo.rollback(error)
  end
end)

Load the exact row version

AuditLog stores subject IDs as text. Cast the ID through the application's schema before querying the temporal history:

{:ok, shareholder_id} =
  AuditLog.Subject.cast_id(Shareholder, event.subject_id)

# Query the temporal store with shareholder_id and event.subject_version_at.

Use cast_id/2 for integer and custom Ecto primary-key types. Ecto's UUID and string types accept the stored text directly, but the helper also keeps the query correct if the primary-key type changes.

occurred_at does not identify a row version

subject_version_at is the lower boundary of one exact temporal version. occurred_at is the audit event transaction's start time. Timestamp proximity does not make them interchangeable:

• An update boundary resolves to the post-change version.
• A deleted row's outgoing boundary resolves to its history version.
• transaction_timestamp() records transaction start, not commit.

Half-open temporal periods mean that looking up a deleted row at the audit event's occurrence time can return no row or the wrong row. When the event has a version boundary, use event.subject_version_at.

Each event identifies one subject. When a business action changes several subjects, record one event for each subject and give the events one correlation ID. AuditLog has no event-subject join table.