Ledger tables ============= Ledger tables are append-only tables designed for recording immutable events such as status transitions, resource consumption, or financial transactions. Every row has a ``value`` column, an ``entry_id`` UUID for correlating related entries, a ``created_at`` timestamp, and consumer-provided dimension columns. Unlike dimensions, ledger entries are never updated or deleted. Ledger tables only allow ``SELECT`` and ``INSERT``. Choose the variant that matches your data: - **Basic ledger** -- a single append-only table with a value column. Best for event logs, status tracking, or metric observations. - **Double-entry ledger** -- adds a ``direction`` column (``'debit'``/``'credit'``) and a constraint trigger that validates debits equal credits per ``entry_id``. Best for financial journals. Basic ledger ------------ A single append-only table. Insert-only: UPDATE and DELETE raise a PostgreSQL error. **Example configuration:** .. literalinclude:: ../scripts/examples/ledger.py :language: python :start-after: # --- example start --- :end-before: # --- example end --- :dedent: **Usage:** .. literalinclude:: ../scripts/examples/ledger.sql :language: sql .. include:: _generated/dim_ledger.rst Latest view ~~~~~~~~~~~ Use :func:`~codegen_database.ext.ledger.queries.construct_ledger_latest_query` to build a query showing the most recent row per dimension group, then register it as a declarative view with :class:`~codegen_database.declarative.CodegenDatabaseViewMixin`. This is useful for status-tracking ledgers where you care about current state rather than historical sums: .. code-block:: python from sqlalchemy import Column, String from codegen_database import CodegenDatabaseViewMixin from codegen_database.factory import CodegenDatabaseLedger from codegen_database.ext.ledger import construct_ledger_latest_query class OrderEvents(Base): __tablename__ = "order_events" __table_args__ = {"schema": "ops"} __factory__ = CodegenDatabaseLedger order_id = Column(String, nullable=False) status = Column(String, nullable=False) class OrderEventsLatest(CodegenDatabaseViewMixin, Base): __tablename__ = "order_events_latest" __table_args__ = {"schema": "ops"} __query__ = construct_ledger_latest_query( OrderEvents, dimensions=["order_id"] ) This registers an ``order_events_latest`` view using PostgreSQL's ``DISTINCT ON``: .. code-block:: sql -- Current status per order: SELECT * FROM ops.order_events_latest; Balance views ~~~~~~~~~~~~~ Use :func:`~codegen_database.ext.ledger.queries.construct_ledger_balance_query` to build a ``SUM(value) GROUP BY`` query, then register it as a declarative view with :class:`~codegen_database.declarative.CodegenDatabaseViewMixin`. Best for ledgers where the running total is meaningful (inventory, resource quotas, point systems): .. code-block:: python from sqlalchemy import Column, String from codegen_database import CodegenDatabaseViewMixin from codegen_database.factory import CodegenDatabaseLedger from codegen_database.ext.ledger import construct_ledger_balance_query class StockMovements(Base): __tablename__ = "stock_movements" __table_args__ = {"schema": "inventory"} __factory__ = CodegenDatabaseLedger warehouse = Column(String, nullable=False) sku = Column(String, nullable=False) class StockBalances(CodegenDatabaseViewMixin, Base): __tablename__ = "stock_movements_balances" __table_args__ = {"schema": "inventory"} __query__ = construct_ledger_balance_query( StockMovements, dimensions=["warehouse", "sku"] ) This registers a ``stock_movements_balances`` view: .. code-block:: sql SELECT warehouse, sku, balance FROM inventory.stock_movements_balances; Balance constraints ~~~~~~~~~~~~~~~~~~~ Use :class:`~codegen_database.plugins.ledger.LedgerBalanceCheckPlugin` to enforce that ``SUM(value)`` for a dimension group never drops below a threshold. This is useful for preventing negative inventory, overdrafts, or exceeding resource quotas: .. code-block:: python from sqlalchemy import Column, String from codegen_database import CodegenDatabaseViewMixin from codegen_database.factory import CodegenDatabaseLedger from codegen_database.ext.ledger import construct_ledger_balance_query from codegen_database.plugins.ledger import LedgerBalanceCheckPlugin class StockMovements(Base): __tablename__ = "stock_movements" __table_args__ = {"schema": "inventory"} __factory__ = CodegenDatabaseLedger __plugins__ = [ LedgerBalanceCheckPlugin( dimensions=["warehouse", "sku"], min_balance=0, # cannot go negative ), ] warehouse = Column(String, nullable=False) sku = Column(String, nullable=False) class StockBalances(CodegenDatabaseViewMixin, Base): __tablename__ = "stock_movements_balances" __table_args__ = {"schema": "inventory"} __query__ = construct_ledger_balance_query( StockMovements, dimensions=["warehouse", "sku"] ) The trigger fires ``AFTER INSERT FOR EACH STATEMENT`` and checks only the dimension groups affected by the new rows. If any group's balance falls below ``min_balance``, the entire statement is rejected: .. code-block:: sql -- Succeeds (balance stays >= 0): INSERT INTO inventory.stock_movements (value, warehouse, sku) VALUES (100, 'east', 'WIDGET-A'); -- Fails (balance would go to -50): INSERT INTO inventory.stock_movements (value, warehouse, sku) VALUES (-150, 'east', 'WIDGET-A'); -- ERROR: ledger balance violation ... Set ``min_balance`` to a different value for other use cases: .. code-block:: python # Allow overdraft up to -1000: LedgerBalanceCheckPlugin( dimensions=["account"], min_balance=-1000, ) Double-entry ledger ------------------- A double-entry ledger extends the basic ledger with debit/credit semantics. Two additional plugins are required: - :class:`~codegen_database.plugins.ledger.DoubleEntryPlugin` -- adds the ``direction`` column to the table. - :class:`~codegen_database.plugins.ledger.DoubleEntryTriggerPlugin` -- registers an ``AFTER INSERT FOR EACH STATEMENT`` constraint trigger that validates debits equal credits for every ``entry_id`` in the batch. Dimension columns like ``category`` belong on a separate dimension table (e.g. ``accounts``), not on the journal itself. The journal references the dimension via a foreign key: **Example configuration:** .. literalinclude:: ../scripts/examples/double_entry.py :language: python :start-after: # --- example start --- :end-before: # --- example end --- :dedent: **Usage:** .. literalinclude:: ../scripts/examples/double_entry.sql :language: sql .. include:: _generated/dim_double_entry.rst How the constraint trigger works ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The trigger fires ``AFTER INSERT FOR EACH STATEMENT`` using a ``REFERENCING NEW TABLE AS new_entries`` transition table. This means: 1. All rows in a single ``INSERT`` statement are visible to the trigger. 2. The trigger groups by ``entry_id`` and checks that ``SUM(value)`` where ``direction = 'debit'`` equals ``SUM(value)`` where ``direction = 'credit'``. 3. If any ``entry_id`` is unbalanced, the entire statement is rejected. This approach allows multi-row inserts (debit + credit in one ``INSERT``) to succeed, while single-sided inserts are correctly rejected. .. note:: The constraint trigger fires on the **raw backing table** (``{tablename}_raw``). Inserts that flow through a view's INSTEAD OF trigger (the factory view) are processed row-by-row, so each row is a separate statement from the raw table's perspective. To benefit from statement-level batching — where a two-row debit+credit insert is validated as a unit — insert directly into ``{tablename}_raw``. Note that the raw table is protected by :class:`~codegen_database.plugins.protect.RawTableProtectionPlugin` by default; you must omit that plugin (e.g. with a custom ``_INTERNAL_PLUGINS`` override) if direct raw-table access is needed for this use case. Customising the value type -------------------------- The default value type is ``INTEGER``. To use ``NUMERIC`` for decimal precision, pass ``value_type="numeric"`` to :class:`~codegen_database.factory.ledger.LedgerTablePlugin` via the internal plugin override mechanism: .. code-block:: python from sqlalchemy import Column, Integer from codegen_database.factory import CodegenDatabaseLedger class Payments(Base): __tablename__ = "payments" __table_args__ = {"schema": "finance"} __factory__ = CodegenDatabaseLedger account_id = Column(Integer, nullable=False) Using a UUID primary key ------------------------ Swap :class:`~codegen_database.plugins.pk.SerialPKPlugin` for :class:`~codegen_database.plugins.pk.UUIDV4PKPlugin` to use a UUIDv4 primary key with ``gen_random_uuid()`` as the server default: .. code-block:: python from sqlalchemy import Column, String from codegen_database.factory import CodegenDatabaseLedger from codegen_database.plugins.pk import UUIDV4PKPlugin class Events(Base): __tablename__ = "events" __table_args__ = {"schema": "analytics"} __factory__ = CodegenDatabaseLedger __plugins__ = [UUIDV4PKPlugin()] event_type = Column(String, nullable=False) Ledger events ------------- Use :doc:`ledger_actions` to attach named PostgreSQL functions to a ledger. Two modes are provided: - **Diff mode** -- declarative reconciliation from a desired-state snapshot (uses ``desired``, ``existing``, ``diff_keys``). - **Simple mode** -- explicit delta insert (``input`` only). See the :doc:`ledger_actions` page for full documentation. Chart helpers ------------- For time-series charts and dashboards, codegen_database ships a set of query and function builders that compose ``date_trunc``, window functions, ``generate_series``, and ``ROLLUP``. The builders live in :mod:`codegen_database.ext.ledger` and all take a ledger source plus dimension column names. Layer 1 -- query builders ~~~~~~~~~~~~~~~~~~~~~~~~~ Each returns a SQLAlchemy ``Select`` you can drop into :class:`~codegen_database.views.view.CodegenDatabasePlainView` or :class:`~codegen_database.declarative.CodegenDatabaseViewMixin`: - :func:`~codegen_database.ext.ledger.rollup.construct_ledger_running_balance_query` -- row-level running balance via ``SUM(value) OVER (PARTITION BY dims ORDER BY created_at ROWS UNBOUNDED PRECEDING)``. - :func:`~codegen_database.ext.ledger.rollup.construct_ledger_period_rollup_query` -- per-period ``SUM(value)`` grouped by ``date_trunc`` + dimensions. - :func:`~codegen_database.ext.ledger.rollup.construct_ledger_period_running_balance_query` -- combines the two: per-period ``delta`` plus a cumulative ``balance`` window. - :func:`~codegen_database.ext.ledger.rollup.construct_ledger_gap_filled_period_rollup_query` -- left-joins a ``generate_series`` period axis against the ledger so empty periods appear with ``delta = 0``. Bounds can be literal timestamps or ``None`` (auto-detected from ``MIN/MAX(created_at)``). - :func:`~codegen_database.ext.ledger.rollup.construct_ledger_rolling_window_query` -- adds a trailing N-row rolling sum (``ROWS BETWEEN N-1 PRECEDING AND CURRENT ROW``) to the per-period rollup. Example: .. code-block:: python from codegen_database import CodegenDatabaseViewMixin from codegen_database.ext.ledger import ( construct_ledger_period_running_balance_query, ) class StockChart(CodegenDatabaseViewMixin, Base): __tablename__ = "stock_chart" __table_args__ = {"schema": "inventory"} __query__ = construct_ledger_period_running_balance_query( StockMovements, dimensions=["warehouse", "sku"], period="day", ) Produces:: SELECT date_trunc('day', created_at) AS period, warehouse, sku, SUM(value) AS delta, SUM(SUM(value)) OVER ( PARTITION BY warehouse, sku ORDER BY date_trunc('day', created_at) ) AS balance FROM inventory.stock_movements GROUP BY 1, warehouse, sku ORDER BY 1, warehouse, sku; Layer 2 -- function builders ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ When the chart should be driven by runtime parameters (a period selector, a date range from the UI), use the function builders. Both emit ``LANGUAGE sql STABLE`` functions so Postgres inlines them at plan time -- predicates that callers add on top push down through the function parameters, unlike PL/pgSQL ``EXECUTE format()`` which blocks inlining. - :func:`~codegen_database.ext.ledger.chart_functions.construct_ledger_chart_function` -- parameterised period running balance with bracketing ``starting_balance`` / ``ending_balance``. - :func:`~codegen_database.ext.ledger.chart_functions.construct_ledger_rollup_chart_function` -- ``ROLLUP(dims)`` aggregate, one row per ``(period, grouping combination)``. Subtotal rows carry ``NULL`` in the rolled-up dimension columns, so callers filter detail vs. subtotals with `` IS NULL`` / `` IS NOT NULL``. Both take the same shape:: (p_period interval DEFAULT ''::interval, p_start timestamptz DEFAULT NULL, p_end timestamptz DEFAULT NULL) ``p_period`` is a Postgres ``interval`` literal -- arbitrary strides are allowed, including ``'15 minutes'``, ``'3 months'``, ``'1 year'``, because bucketing goes through the ``codegen_database_date_bin`` polyfill (see below). ``p_start`` / ``p_end`` bracket a half-open ``[p_start, p_end)`` window on ``created_at``; either can be ``NULL`` (unbounded). Output schema ^^^^^^^^^^^^^ ``construct_ledger_chart_function`` returns:: TABLE ( period tstzrange, , ..., starting_balance numeric, delta numeric, [ numeric, ...,] -- only with split_by ending_balance numeric ) ``construct_ledger_rollup_chart_function`` returns:: TABLE ( period tstzrange, , ..., delta numeric ) Subtotal rows are signalled by ``NULL`` in the rolled-up dimension columns. This assumes the dimension data itself never contains ``NULL`` (typical for FK / enum dimensions); if it does, subtotal rows cannot be distinguished from NULL-data rows. The ``period`` column is a half-open ``[bucket_start, bucket_end)`` ``tstzrange`` -- the full extent of the bucket, not just its start. ``bucket_end`` is always ``bucket_start + p_period``. .. note:: ``starting_balance`` / ``ending_balance`` are cumulative deltas *within the filtered window*, not absolute ledger balances. Widen ``p_start`` or compose with :func:`~codegen_database.ext.ledger.queries.construct_ledger_balance_query` to anchor against full history. Filtering the period column ^^^^^^^^^^^^^^^^^^^^^^^^^^^ ``tstzrange`` supports the full range operator set, so pick the operator that matches intent: .. code-block:: sql -- All buckets fully contained in Feb 2024: WHERE period <@ tstzrange('2024-02-01', '2024-03-01', '[)') -- All buckets that overlap an instant (e.g. "right now"): WHERE period @> now() -- Exactly the Feb-2024 bucket (only useful when the stride and -- the filter boundary align): WHERE lower(period) = '2024-02-01' Ordering on ``period`` works directly -- Postgres has a total order on ``tstzrange`` -- so ``ORDER BY period`` sorts chronologically. The codegen_database_date_bin polyfill ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Native ``date_bin`` rejects intervals of a month or larger because months have no fixed length; codegen_database ships :func:`~codegen_database.ext.chart.date_bin.construct_date_bin_function` which handles any interval by switching strategies: - Sub-month strides (``'15 minutes'``, ``'1 day'``, ``'1 week'``) delegate to native ``date_bin``. - Month-or-larger strides (``'1 month'``, ``'3 months'``, ``'1 year'``) use calendar arithmetic anchored at ``date_trunc('month', origin)``. - Mixed strides (``'1 month 3 days'``) have no uniform bucketing; the function returns ``NULL`` and the chart functions surface that as a ``NULL`` range rather than the unbounded ``(,)`` that ``tstzrange(NULL, NULL, '[)')`` would otherwise produce. Register :ref:`ext-chart` on your config to install the polyfill once in the ``codegen_database`` schema; the chart function builders find it via ``metadata.info["codegen_database_chart_schema"]`` and qualify calls automatically. Pass ``date_bin_schema`` / ``date_bin_name`` to point at an existing helper elsewhere. .. code-block:: python from codegen_database import CodegenDatabaseFunctionMixin from codegen_database.config import CodegenDatabaseConfig from codegen_database.ext.chart import ChartExtension from codegen_database.ext.ledger import ( construct_ledger_chart_function, construct_ledger_rollup_chart_function, ) config = CodegenDatabaseConfig() config.use(ChartExtension()) metadata.info["codegen_database_config"] = config class StockChart(CodegenDatabaseFunctionMixin, Base): __table_args__ = {"schema": "inventory"} __funcspec__ = construct_ledger_chart_function( StockMovements, name="stock_chart", dimensions=["warehouse", "sku"], period_default="1 day", ) class StockRollupChart(CodegenDatabaseFunctionMixin, Base): __table_args__ = {"schema": "inventory"} __funcspec__ = construct_ledger_rollup_chart_function( StockMovements, name="stock_rollup_chart", dimensions=["warehouse", "sku"], ) Calling the functions: .. code-block:: sql -- Daily running balance for a specific SKU, first half of 2024: SELECT period, starting_balance, delta, ending_balance FROM inventory.stock_chart( p_period => '1 day'::interval, p_start => '2024-01-01', p_end => '2024-07-01' ) WHERE warehouse = 'east' AND sku = 'WIDGET-A' ORDER BY period; -- Detail rows only, monthly, for one month: SELECT period, warehouse, sku, delta FROM inventory.stock_rollup_chart(p_period => '1 month'::interval) WHERE warehouse IS NOT NULL AND sku IS NOT NULL AND period <@ tstzrange('2024-06-01', '2024-07-01', '[)') ORDER BY period, warehouse, sku; -- Warehouse subtotals, weekly: SELECT period, warehouse, delta FROM inventory.stock_rollup_chart(p_period => '1 week'::interval) WHERE warehouse IS NOT NULL AND sku IS NULL; -- Quarterly view (native date_bin can't do this): SELECT period, warehouse, sku, delta FROM inventory.stock_rollup_chart(p_period => '3 months'::interval); The ``split_by`` kwarg ^^^^^^^^^^^^^^^^^^^^^^ Pass ``split_by=("", [, ...])`` to :func:`~codegen_database.ext.ledger.chart_functions.construct_ledger_chart_function` to split ``delta`` into one named ``SUM ... FILTER`` column per listed value. Typical use is a double-entry ledger with a ``direction`` column:: construct_ledger_chart_function( Journal, name="ledger_chart", dimensions=["account"], split_by=("direction", ["debit", "credit"]), ) which emits:: TABLE ( period tstzrange, account , starting_balance numeric, delta numeric, debit numeric, -- SUM(value) FILTER (WHERE direction='debit') credit numeric, -- SUM(value) FILTER (WHERE direction='credit') ending_balance numeric ) The double-entry chart function ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ :func:`~codegen_database.ext.ledger.chart_functions.construct_double_entry_chart_function` is a specialization for double-entry ledgers that joins each row to an accounts dimension table and normalizes the per-bucket change (``delta``) by each account's ``normal_side``: - **Debit-normal** (assets, expenses): ``delta = debits - credits``. - **Credit-normal** (liabilities, equity, revenue): ``delta = credits - debits``. so ``delta`` is always positive when the balance moved in the account's natural direction. Running ``starting_balance`` and ``ending_balance`` are cumulative sums of this signed delta. The accounts table must have ``name`` and ``normal_side`` columns (override via *accounts_key_column* / *normal_side_column*):: class Accounts(Base): __tablename__ = "accounts" __table_args__ = {"schema": "private"} name = Column(String, nullable=False, unique=True) account_type = Column(String, nullable=False) normal_side = Column(String, nullable=False) # 'debit' or 'credit' class LedgerChart(CodegenDatabaseFunctionMixin, Base): __table_args__ = {"schema": "private"} __funcspec__ = construct_double_entry_chart_function( Ledger, name="ledger_chart", accounts_table=Accounts, dimensions=["account"], period_default="1 day", ) emits:: TABLE ( period tstzrange, account , starting_balance numeric, debits numeric, -- SUM(value) FILTER (direction='debit') credits numeric, -- SUM(value) FILTER (direction='credit') delta numeric, -- normalized by normal_side ending_balance numeric ) Unlike the generic ``split_by=("direction", ...)`` form, this builder knows which sign is "up" for each account, so a dashboard can chart ``delta`` directly without looking up accounting conventions per account. Plugin reference ---------------- All ledger plugins are documented in the :doc:`api` reference. The key context keys are: ``SerialPKPlugin`` / ``UUIDV4PKPlugin`` Writes ``"pk_columns"``. ``UUIDEntryIDPlugin`` Writes ``"entry_id_column"`` (a ``Column`` object) and appends the column to ``ctx.injected_columns``. ``CreatedAtPlugin`` Writes ``"created_at_column"`` (the column name string) and appends a ``DateTime`` column to ``ctx.injected_columns``. ``LedgerTablePlugin`` Reads ``"pk_columns"`` and spreads ``ctx.injected_columns`` into the table. Requires ``"entry_id_column"`` and ``"created_at_column"`` for plugin ordering. Writes ``"raw_table"`` (the raw backing table). ``LedgerViewPlugin`` Reads ``"raw_table"``. Writes ``"primary"`` (the factory view proxy) and ``"__root__"``. ``LedgerTriggerPlugin`` Reads ``"raw_table"`` (via ``table_key``), ``"primary"`` (via ``view_key``), ``"entry_id_column"``. ``LedgerBalanceCheckPlugin`` Reads ``"raw_table"`` (via ``table_key``, default ``"raw_table"``). Registers an AFTER INSERT trigger enforcing ``SUM(value) >= min_balance`` per dimension group. ``DoubleEntryPlugin`` Writes ``"double_entry_columns"`` (the direction column name) and appends the direction column to ``ctx.injected_columns``. ``DoubleEntryTriggerPlugin`` Reads ``"raw_table"`` (via ``table_key``, default ``"raw_table"``), ``"double_entry_columns"``, ``"entry_id_column"``.