Audit queries

Helpers that turn a history table into a diff or net-delta query. Each helper returns a SQLAlchemy Select and registers nothing on metadata — pipe it into CodegenDatabaseViewMixin to expose as a view, or run it directly against a connection.

What is exposed

Four functions, re-exported at codegen_database.ext.audit and at the top-level codegen_database package:

Layout	Per-transition	Net delta over a window
Append-only (SCD Type 2)	construct_append_only_diff_query()	construct_append_only_changed_between_query()
EAV	construct_eav_diff_query()	construct_eav_changed_between_query()

All four emit typed <col>_before / <col>_after pairs (value_before / value_after for EAV). No JSONB.

Pick the row matching the backing table:

• Append-only (each row is a full version of an entity) — pass the factory’s ctx["attributes"] table, or any hand-rolled SCD Type 2 log.

• EAV (each row is one attribute’s value at one point in time) — pass the factory’s ctx["attribute"] table, or any log with the same shape.

How to use

Expose as a declarative view:

from codegen_database import CodegenDatabaseViewMixin, construct_append_only_diff_query


class ProductDiffs(CodegenDatabaseViewMixin, Base):
    __tablename__ = "product_diffs"
    __query__ = construct_append_only_diff_query(
        ProductVersions.__table__, key_cols=["sku"],
    )

Or run the query directly:

from sqlalchemy import create_engine
from codegen_database import construct_append_only_diff_query

engine = create_engine(DATABASE_URL)
with engine.connect() as conn:
    rows = conn.execute(
        construct_append_only_diff_query(table, key_cols=["sku"])
    ).all()

Append-only

construct_append_only_diff_query — per-transition

LAG() partitioned by key_cols, ordered by ts_col. Emits one row per transition where at least one tracked column changed (IS DISTINCT FROM, so NULL/non-NULL counts as a change). The first row per key is included as a creation event with *_before = NULL.

Default tracked_cols is every column except key_cols, ts_col, and the primary-key columns (so surrogate version IDs don’t leak into the diff).

SELECT sku, changed_at,
       name_before,  name_after,
       price_before, price_after,
       stock_before, stock_after
FROM (
  SELECT sku,
         created_at AS changed_at,
         lag(name)  OVER w AS name_before,  name  AS name_after,
         lag(price) OVER w AS price_before, price AS price_after,
         lag(stock) OVER w AS stock_before, stock AS stock_after
  FROM product_versions
  WINDOW w AS (PARTITION BY sku ORDER BY created_at)
) t
WHERE name_before  IS DISTINCT FROM name_after
   OR price_before IS DISTINCT FROM price_after
   OR stock_before IS DISTINCT FROM stock_after
ORDER BY sku, changed_at;

construct_append_only_changed_between_query — net delta

Two DISTINCT ON (key) snapshots (state-as-of start, state-as-of end, both inclusive), FULL OUTER JOINed on the key. Intermediate versions inside the window are collapsed — only the net delta survives. Entities created inside the window appear with NULL on the before side; entities whose last version is before end appear with NULL on the after side.

SELECT coalesce(after_.sku, before_.sku) AS sku,
       before_.name  AS name_before,  after_.name  AS name_after,
       before_.price AS price_before, after_.price AS price_after,
       before_.stock AS stock_before, after_.stock AS stock_after
FROM (SELECT DISTINCT ON (sku) sku, name, price, stock
      FROM product_versions WHERE created_at <= :start
      ORDER BY sku, created_at DESC) AS before_
FULL OUTER JOIN
     (SELECT DISTINCT ON (sku) sku, name, price, stock
      FROM product_versions WHERE created_at <= :end
      ORDER BY sku, created_at DESC) AS after_
  ON before_.sku = after_.sku
WHERE before_.name  IS DISTINCT FROM after_.name
   OR before_.price IS DISTINCT FROM after_.price
   OR before_.stock IS DISTINCT FROM after_.stock;

EAV

EAV diff output is long, not wide: one row per (entity, attribute) transition, with a single value_before / value_after pair. All typed *_value columns are cast to text and coalesced into one expression — each EAV row has exactly one non-null value (check-constrained), so the coalesce is loss-free. value_cols is auto-detected by a _value name suffix.

construct_eav_diff_query — per-attribute transition

LAG() partitioned by (entity, attribute), ordered by ts_col. First observation per pair comes through with value_before = NULL. Optional attributes=[...] filter restricts to named attributes.

SELECT entity_id, attribute_name, changed_at,
       value_before, value_after
FROM (
  SELECT entity_id, attribute_name, changed_at,
         lag(value) OVER (PARTITION BY entity_id, attribute_name
                          ORDER BY changed_at) AS value_before,
         value AS value_after
  FROM (
    SELECT entity_id, attribute_name,
           created_at AS changed_at,
           coalesce(CAST(string_value  AS TEXT),
                    CAST(integer_value AS TEXT)) AS value
    FROM eav_attribute
    -- optional: WHERE attribute_name IN :attributes
  ) eav_rows
) lagged
WHERE value_before IS DISTINCT FROM value_after
ORDER BY entity_id, attribute_name, changed_at;

construct_eav_changed_between_query — net delta

Same DISTINCT ON + FULL OUTER JOIN pattern as the append-only version, keyed by (entity, attribute).

SELECT coalesce(after_.entity_id,      before_.entity_id)      AS entity_id,
       coalesce(after_.attribute_name, before_.attribute_name) AS attribute_name,
       before_.value AS value_before,
       after_.value  AS value_after
FROM (SELECT DISTINCT ON (entity_id, attribute_name)
             entity_id, attribute_name,
             coalesce(CAST(string_value  AS TEXT),
                      CAST(integer_value AS TEXT)) AS value
      FROM eav_attribute WHERE created_at <= :start
      ORDER BY entity_id, attribute_name, created_at DESC) AS before_
FULL OUTER JOIN
     (SELECT DISTINCT ON (entity_id, attribute_name)
             entity_id, attribute_name,
             coalesce(CAST(string_value  AS TEXT),
                      CAST(integer_value AS TEXT)) AS value
      FROM eav_attribute WHERE created_at <= :end
      ORDER BY entity_id, attribute_name, created_at DESC) AS after_
  ON before_.entity_id      = after_.entity_id
 AND before_.attribute_name = after_.attribute_name
WHERE before_.value IS DISTINCT FROM after_.value;

Full signatures live in the API reference reference.