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:

order_events = CodegenDatabaseLedger(
    tablename="order_events",
    schemaname="ops",
    metadata=metadata,
    schema_items=[
        Column("order_id", String, nullable=False),
        Column("status", String, nullable=False),
    ],
)

CodegenDatabasePlainView(
    name="order_events_latest",
    schema="ops",
    metadata=metadata,
    query=construct_ledger_latest_query(order_events, dimensions=["order_id"]),
)

Usage:

-- Operations go through the ops.order_events view.
-- Only INSERT is allowed; UPDATE and DELETE are rejected.

-- Log a status change:
INSERT INTO ops.order_events (value, order_id, status)
VALUES (1, 'ORD-001', 'placed');

-- Log multiple events at once:
INSERT INTO ops.order_events (value, order_id, status)
VALUES
(1, 'ORD-001', 'confirmed'),
(1, 'ORD-002', 'placed');

-- Current status per order (most recent event):
SELECT * FROM ops.order_events_latest;

Latest view¶

Use construct_ledger_latest_query() to build a query showing the most recent row per dimension group, then register it as a declarative view with CodegenDatabaseViewMixin. This is useful for status-tracking ledgers where you care about current state rather than historical sums:

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:

-- Current status per order:
SELECT * FROM ops.order_events_latest;

Balance views¶

Use construct_ledger_balance_query() to build a SUM(value) GROUP BY query, then register it as a declarative view with CodegenDatabaseViewMixin. Best for ledgers where the running total is meaningful (inventory, resource quotas, point systems):

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:

SELECT warehouse, sku, balance
FROM inventory.stock_movements_balances;

Balance constraints¶

Use 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:

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:

-- 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:

# 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:

DoubleEntryPlugin – adds the direction column to the table.
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:

accounts = CodegenDatabaseSimple(
    tablename="accounts",
    schemaname="finance",
    metadata=metadata,
    schema_items=[
        Column("name", String, nullable=False),
        Column("category", String, nullable=False),
    ],
)

journal = CodegenDatabaseLedger(
    tablename="journal",
    schemaname="finance",
    metadata=metadata,
    schema_items=[
        Column(
            "account_id",
            Integer,
            CodegenDatabaseForeignKey("accounts.id"),
            nullable=False,
        ),
    ],
    extra_plugins=[
        DoubleEntryPlugin(),
        DoubleEntryTriggerPlugin(),
    ],
)

Usage:

-- Double-entry ledger: every entry_id must balance.
-- The constraint trigger validates debits = credits per entry_id.

-- Balanced entry (succeeds):
INSERT INTO finance.journal (entry_id, value, direction, account_id)
VALUES
('cccccccc-0001-4000-8000-000000000001', 100, 'debit', 1),
('cccccccc-0001-4000-8000-000000000001', 100, 'credit', 2);

-- Unbalanced entry (rejected by the constraint trigger):
-- INSERT INTO finance.journal (entry_id, value, direction, account_id)
-- VALUES
--     ('dddddddd-0001-4000-8000-000000000001', 100, 'debit', 1),
--     ('dddddddd-0001-4000-8000-000000000001', 50, 'credit', 2);
-- ERROR: double-entry violation for entry_id ...

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:

All rows in a single INSERT statement are visible to the trigger.
The trigger groups by entry_id and checks that SUM(value) where direction = 'debit' equals SUM(value) where direction = 'credit'.
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 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 LedgerTablePlugin via the internal plugin override mechanism:

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 SerialPKPlugin for UUIDV4PKPlugin to use a UUIDv4 primary key with gen_random_uuid() as the server default:

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 Ledger events 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 Ledger events 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 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 CodegenDatabasePlainView or CodegenDatabaseViewMixin:

construct_ledger_running_balance_query() – row-level running balance via SUM(value) OVER (PARTITION BY dims ORDER BY created_at ROWS UNBOUNDED PRECEDING).
construct_ledger_period_rollup_query() – per-period SUM(value) grouped by date_trunc + dimensions.
construct_ledger_period_running_balance_query() – combines the two: per-period delta plus a cumulative balance window.
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)).
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:

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.

construct_ledger_chart_function() – parameterised period running balance with bracketing starting_balance / ending_balance.
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 <dim> IS NULL / <dim> IS NOT NULL.

Both take the same shape:

<name>(p_period interval DEFAULT '<period_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,
    <dim1> <type>, ...,
    starting_balance  numeric,
    delta             numeric,
    [<split_value>    numeric, ...,]        -- only with split_by
    ending_balance    numeric
)

construct_ledger_rollup_chart_function returns:

TABLE (
    period              tstzrange,
    <dim1> <type>, ...,
    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 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:

-- 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 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 Chart extension 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.

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:

-- 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=("<column>", [<value>, ...]) to 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          <type>,
    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¶

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          <type>,
    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 API reference 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".

codegen_database

Navigation

Related Topics

Ledger tables¶

Basic ledger¶

Latest view¶

Balance views¶

Balance constraints¶

Double-entry ledger¶

How the constraint trigger works¶

Customising the value type¶

Using a UUID primary key¶

Ledger events¶

Chart helpers¶

Layer 1 – query builders¶

Layer 2 – function builders¶

Output schema¶

Filtering the period column¶

The codegen_database_date_bin polyfill¶

The `split_by` kwarg¶

The double-entry chart function¶

Plugin reference¶

Ledger tables¶

Basic ledger¶

Latest view¶

Balance views¶

Balance constraints¶

Double-entry ledger¶

How the constraint trigger works¶

Customising the value type¶

Using a UUID primary key¶

Ledger events¶

Chart helpers¶

Layer 1 – query builders¶

Layer 2 – function builders¶

Output schema¶

Filtering the period column¶

The codegen_database_date_bin polyfill¶

The split_by kwarg¶

The double-entry chart function¶

Plugin reference¶

The `split_by` kwarg¶