Audit queries
=============

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

What is exposed
---------------

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

.. list-table::
   :header-rows: 1
   :widths: 25 38 38

   * - Layout
     - Per-transition
     - Net delta over a window
   * - Append-only (SCD Type 2)
     - :func:`~codegen_database.ext.audit.construct_append_only_diff_query`
     - :func:`~codegen_database.ext.audit.construct_append_only_changed_between_query`
   * - EAV
     - :func:`~codegen_database.ext.audit.construct_eav_diff_query`
     - :func:`~codegen_database.ext.audit.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:

.. code-block:: python

   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:

.. code-block:: python

   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).

.. code-block:: sql

   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 JOIN``\ ed 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.

.. code-block:: sql

   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.

.. code-block:: sql

   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)``.

.. code-block:: sql

   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 :doc:`api` reference.