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 = PGCraftLedger(
    tablename="order_events",
    schemaname="ops",
    metadata=metadata,
    schema_items=[
        Column("order_id", String, nullable=False),
        Column("status", String, nullable=False),
    ],
)

PostgRESTView(source=order_events, grants=["select", "insert"])
LatestView(source=order_events, dimensions=["order_id"])

Usage:

-- All operations go through the API view.
-- Only INSERT is allowed; UPDATE and DELETE are rejected.

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

-- Log multiple events at once:
INSERT INTO api.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;

Schema¶

An append-only event log (ops.order_events) records immutable status transitions. Each row carries a value (typically 1), an entry_id UUID, and dimension columns. The ops.order_events_latest view shows the most recent event per order_id. An api.order_events view exposes SELECT and INSERT only.

=# \d ops.order_events
                                                 Table "ops.order_events"
   Column   |           Type           | Collation | Nullable |                          Default
------------+--------------------------+-----------+----------+-----------------------------------------------------------
 id         | integer                  |           | not null | nextval('ops.order_events_id_seq'::regclass)
 entry_id   | uuid                     |           | not null | gen_random_uuid()
 created_at | timestamp with time zone |           |          | '2026-03-15 23:17:26.493447+00'::timestamp with time zone
 value      | integer                  |           | not null |
 order_id   | character varying        |           | not null |
 status     | character varying        |           | not null |
Indexes:
    "pk__order_events__id" PRIMARY KEY, btree (id)

Sample queries¶

All entries are immutable. Once inserted, rows cannot be updated or deleted.

=# SELECT * FROM ops.order_events ORDER BY id;
 id |               entry_id               |       created_at       | value | order_id |  status
----+--------------------------------------+------------------------+-------+----------+-----------
  1 | aaaaaaaa-0001-4000-8000-000000000001 | 2025-01-15 09:00:00+00 |     1 | ORD-001  | placed
  2 | aaaaaaaa-0002-4000-8000-000000000002 | 2025-01-15 09:05:00+00 |     1 | ORD-002  | placed
  3 | aaaaaaaa-0003-4000-8000-000000000003 | 2025-01-15 10:30:00+00 |     1 | ORD-001  | confirmed
  4 | aaaaaaaa-0004-4000-8000-000000000004 | 2025-01-16 14:00:00+00 |     1 | ORD-001  | shipped
  5 | aaaaaaaa-0005-4000-8000-000000000005 | 2025-01-16 15:00:00+00 |     1 | ORD-002  | confirmed
(5 rows)

The latest view shows the most recent event per order.

=# SELECT * FROM ops.order_events_latest ORDER BY order_id;
 id |               entry_id               |       created_at       | value | order_id |  status
----+--------------------------------------+------------------------+-------+----------+-----------
  4 | aaaaaaaa-0004-4000-8000-000000000004 | 2025-01-16 14:00:00+00 |     1 | ORD-001  | shipped
  5 | aaaaaaaa-0005-4000-8000-000000000005 | 2025-01-16 15:00:00+00 |     1 | ORD-002  | confirmed
(2 rows)

Counting events per order shows how many state transitions each order has gone through.

=# SELECT order_id, COUNT(*) AS transitions FROM ops.order_events GROUP BY order_id ORDER BY order_id;
 order_id | transitions
----------+-------------
 ORD-001  |           3
 ORD-002  |           2
(2 rows)

Latest view¶

Use LatestView to create a view that shows the most recent row per dimension group. This is useful for status-tracking ledgers where you care about current state rather than historical sums:

from pgcraft.factory import PGCraftLedger
from pgcraft.views import LatestView

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

LatestView(
    source=order_events,
    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;

The view name follows the naming convention and can be customised via metadata.naming_convention["ledger_latest_view"].

Balance views¶

Use BalanceView to create a view that shows current balances (SUM(value)) per dimension group. Best for ledgers where the running total is meaningful (inventory, resource quotas, point systems):

from pgcraft.factory import PGCraftLedger
from pgcraft.views import BalanceView

stock = PGCraftLedger(
    tablename="stock_movements",
    schemaname="inventory",
    metadata=metadata,
    schema_items=[
        Column(
            "warehouse", String, nullable=False
        ),
        Column(
            "sku", String, nullable=False
        ),
    ],
)

BalanceView(
    source=stock,
    dimensions=["warehouse", "sku"],
)

This registers a stock_movements_balances view:

SELECT warehouse, sku, balance
FROM inventory.stock_movements_balances;

The view name follows the naming convention and can be customised via metadata.naming_convention["ledger_balance_view"].

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 pgcraft.factory import PGCraftLedger
from pgcraft.plugins.ledger import (
    LedgerBalanceCheckPlugin,
)
from pgcraft.views import BalanceView

stock = PGCraftLedger(
    tablename="stock_movements",
    schemaname="inventory",
    metadata=metadata,
    schema_items=[
        Column(
            "warehouse", String, nullable=False
        ),
        Column(
            "sku", String, nullable=False
        ),
    ],
    extra_plugins=[
        LedgerBalanceCheckPlugin(
            dimensions=["warehouse", "sku"],
            min_balance=0,  # cannot go negative
        ),
    ],
)

BalanceView(
    source=stock,
    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 = PGCraftSimple(
    tablename="accounts",
    schemaname="finance",
    metadata=metadata,
    schema_items=[
        Column("name", String, nullable=False),
        Column("category", String, nullable=False),
    ],
)

PostgRESTView(source=accounts)

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

PostgRESTView(
    source=journal,
    grants=["select", "insert"],
)

Usage:

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

-- Balanced entry (succeeds):
INSERT INTO api.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 api.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 ...

Schema¶

A double-entry journal table (finance.journal) references an accounts dimension via FK. The direction column ('debit' or 'credit') is added by DoubleEntryPlugin. An AFTER INSERT constraint trigger validates that debits equal credits for every entry_id in the batch.

=# \d finance.accounts
                                      Table "finance.accounts"
  Column  |       Type        | Collation | Nullable |                   Default
----------+-------------------+-----------+----------+----------------------------------------------
 id       | integer           |           | not null | nextval('finance.accounts_id_seq'::regclass)
 name     | character varying |           | not null |
 category | character varying |           | not null |
Indexes:
    "pk__accounts__id" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "finance.journal" CONSTRAINT "fk__journal__account_id__accounts" FOREIGN KEY (account_id) REFERENCES finance.accounts(id)

=# \d finance.journal
                                                 Table "finance.journal"
   Column   |           Type           | Collation | Nullable |                         Default
------------+--------------------------+-----------+----------+----------------------------------------------------------
 id         | integer                  |           | not null | nextval('finance.journal_id_seq'::regclass)
 direction  | character varying        |           | not null |
 entry_id   | uuid                     |           | not null | gen_random_uuid()
 created_at | timestamp with time zone |           |          | '2026-03-15 23:17:26.64756+00'::timestamp with time zone
 value      | integer                  |           | not null |
 account_id | integer                  |           | not null |
Indexes:
    "pk__journal__id" PRIMARY KEY, btree (id)
Check constraints:
    "ck__journal" CHECK (direction::text = ANY (ARRAY['debit'::character varying, 'credit'::character varying]::text[]))
Foreign-key constraints:
    "fk__journal__account_id__accounts" FOREIGN KEY (account_id) REFERENCES finance.accounts(id)

Sample queries¶

The accounts dimension holds the name and category for each account.

=# SELECT * FROM finance.accounts ORDER BY id;
 id |   name   | category
----+----------+----------
  1 | cash     | asset
  2 | revenue  | income
  3 | supplies | expense
(3 rows)

Each entry_id group must balance: total debits = total credits.

=# SELECT * FROM finance.journal ORDER BY id;
 id | direction |               entry_id               |          created_at          | value | account_id
----+-----------+--------------------------------------+------------------------------+-------+------------
  1 | debit     | aaaaaaaa-0001-4000-8000-000000000001 | 2026-03-15 23:17:26.64756+00 |   500 |          1
  2 | credit    | aaaaaaaa-0001-4000-8000-000000000001 | 2026-03-15 23:17:26.64756+00 |   500 |          2
  3 | debit     | aaaaaaaa-0002-4000-8000-000000000002 | 2026-03-15 23:17:26.64756+00 |   200 |          3
  4 | credit    | aaaaaaaa-0002-4000-8000-000000000002 | 2026-03-15 23:17:26.64756+00 |   200 |          1
(4 rows)

Joining the journal to the accounts dimension shows the T-account breakdown with category.

=# SELECT a.name, a.category, j.direction, SUM(j.value) AS total FROM finance.journal j JOIN finance.accounts a ON a.id = j.account_id GROUP BY a.name, a.category, j.direction ORDER BY a.name, j.direction;
   name   | category | direction | total
----------+----------+-----------+-------
 cash     | asset    | credit    |   200
 cash     | asset    | debit     |   500
 revenue  | income   | credit    |   500
 supplies | expense  | debit     |   200
(4 rows)

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 backing table. If you insert through an intermediary view with an INSTEAD OF INSERT trigger, each row is routed individually to the backing table. Because INSTEAD OF triggers fire row-by-row, each row is a separate statement from the backing table’s perspective. To benefit from statement-level batching, insert directly into the backing table.

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 pgcraft.factory import PGCraftLedger
from pgcraft.plugins.ledger import LedgerTablePlugin

payments = PGCraftLedger(
    tablename="payments",
    schemaname="finance",
    metadata=metadata,
    schema_items=[
        Column(
            "account_id",
            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 pgcraft.factory import PGCraftLedger
from pgcraft.plugins.pk import UUIDV4PKPlugin

events = PGCraftLedger(
    tablename="events",
    schemaname="analytics",
    metadata=metadata,
    schema_items=[
        Column(
            "event_type",
            String,
            nullable=False,
        ),
    ],
    plugins=[UUIDV4PKPlugin()],
)

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.

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 "primary" (the table) and "__root__".
LedgerTriggerPlugin: Reads "primary", "entry_id_column".
LedgerLatestViewPlugin: Reads "primary" and "created_at_column". Writes "latest_view" (the view name).
LedgerBalanceViewPlugin: Reads "primary". Writes "balance_view" (the view name).
LedgerBalanceCheckPlugin: Reads "primary". 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 "primary", "double_entry_columns", "entry_id_column".

pgcraft

Navigation

Related Topics

Versions

Ledger tables¶

Basic ledger¶

Schema¶

Sample queries¶

Latest view¶

Balance views¶

Balance constraints¶

Double-entry ledger¶

Schema¶

Sample queries¶

How the constraint trigger works¶

Customising the value type¶

Using a UUID primary key¶

Ledger events¶

Plugin reference¶