[BUG] PIT with Backdated Transactions: Is Balance Filtering Supported in list_accounts API?

[imtiazmangerah]

Hi team,

I'm encountering unexpected behaviour when listing accounts using the pit parameter and when backdated transactions are posted.

My main question:

Is balance filtering supported in the list_accounts API when backdated transactions are posted and a pit is used?

---

Context

• Ledger version: v2.3.0

---

Reproduction Script (Python)

from datetime import datetime, timedelta, timezone
from formance_sdk_python import SDK
from formance_sdk_python.models import operations, shared

current_datetime = datetime.now().strftime("%Y%m%d_%H%M%S")

LEDGER_NAME = f"example_{current_datetime}"
SERVER_URL = "http://localhost:3068"

SEND_EXAMPLE = """
vars {
    account $source
    monetary $amount
}
send $amount (
    source = $source allowing unbounded overdraft
    destination = @world
)
"""

TRANSACTION_DATE = datetime(2025, 10, 1, 0, 0, second=0, tzinfo=timezone(timedelta(hours=0)))
ONE_SECOND_AFTER = datetime(2025, 10, 1, 0, 0, second=1, tzinfo=timezone(timedelta(hours=0)))

def run():
    client = SDK(server_url=SERVER_URL)

    # Create a ledger
    client.ledger.v2.create_ledger(
        request=operations.V2CreateLedgerRequest(
            ledger=LEDGER_NAME,
            v2_create_ledger_request=shared.V2CreateLedgerRequest(
                features={}, metadata={}, bucket="_default"
            ),
        )
    )

    # Post a backdated transaction
    client.ledger.v2.create_transaction(
        request=operations.V2CreateTransactionRequest(
            ledger=LEDGER_NAME,
            v2_post_transaction=shared.V2PostTransaction(
                metadata={},
                timestamp=TRANSACTION_DATE,
                script=shared.V2PostTransactionScript(
                    plain=SEND_EXAMPLE,
                    vars={"source": "user:1234", "amount": "USD/2 50"},
                ),
            ),
        )
    )

    # 1️⃣ List all accounts
    print("✅ List all accounts")
    accounts = client.ledger.v2.list_accounts(
        request=operations.V2ListAccountsRequest(
            ledger=LEDGER_NAME, request_body={}, expand="effectiveVolumes"
        )
    )
    print(accounts.v2_accounts_cursor_response.cursor.data)

    # 2️⃣ List all accounts with negative balance
    print("\n✅ List all accounts with negative balance")
    accounts = client.ledger.v2.list_accounts(
        request=operations.V2ListAccountsRequest(
            ledger=LEDGER_NAME,
            request_body={"$lt": {"balance[USD/2]": 0}},
            expand="effectiveVolumes",
        )
    )
    print(accounts.v2_accounts_cursor_response.cursor.data)

    # 3️⃣ List all accounts at PIT
    print("\n✅ List all accounts at PIT")
    accounts = client.ledger.v2.list_accounts(
        request=operations.V2ListAccountsRequest(
            ledger=LEDGER_NAME, request_body={}, expand="effectiveVolumes", pit=ONE_SECOND_AFTER
        )
    )
    print(accounts.v2_accounts_cursor_response.cursor.data)

    # 4️⃣ List all accounts at PIT with balance filter
    print("\n❌ List all accounts at PIT and with balance filter")
    accounts = client.ledger.v2.list_accounts(
        request=operations.V2ListAccountsRequest(
            ledger=LEDGER_NAME,
            request_body={"$lt": {"balance[USD/2]": 0}},
            expand="effectiveVolumes",
            pit=ONE_SECOND_AFTER,
        )
    )
    print(accounts.v2_accounts_cursor_response.cursor.data)

if __name__ == "__main__":
    run()

Expected vs Actual Results

Case	Description	Expected	Actual
✅ No PIT + no filter	Lists all accounts correctly	✔️ Works	✔️ Works
✅ No PIT + balance filter	Filters correctly by negative balance	✔️ Works	✔️ Works
✅ PIT only	Shows state as of PIT time	✔️ Works	✔️ Works
❌ PIT + balance filter	Should return accounts that had negative balance at PIT	❌ Correct accounts listed	❌ No results / uses insertion_date instead of effective_date

Observed SQL Query

WITH "dataset" AS (
  SELECT *, row_number() OVER (ORDER BY address ASC)
  FROM (
    SELECT
      "address",
      "address_array",
      "first_usage",
      "insertion_date",
      "updated_at",
      COALESCE(accounts_metadata.metadata, '{}'::jsonb) AS metadata
    FROM "_default".accounts
    LEFT JOIN (
      SELECT DISTINCT ON (accounts_address)
        "accounts_address",
        FIRST_VALUE(metadata)
        OVER (PARTITION BY accounts_address ORDER BY revision DESC) AS metadata
      FROM "_default".accounts_metadata
      WHERE (ledger = 'example_20251011_212737')
        AND (date <= '2025-10-01T00:00:01Z')
    ) accounts_metadata
    ON accounts_metadata.accounts_address = accounts.address
    WHERE (ledger = 'example_20251011_212737')
      AND (accounts.first_usage <= '2025-10-01T00:00:01Z')
  ) dataset
  WHERE (
    SELECT balance < '0'
    FROM (
      SELECT DISTINCT ON (asset)
        FIRST_VALUE((post_commit_volumes).inputs - (post_commit_volumes).outputs)
        OVER (PARTITION BY (accounts_address, asset) ORDER BY seq DESC) AS balance
      FROM "_default".moves
      WHERE (accounts_address = dataset.address)
        AND (ledger = 'example_20251011_212737')
        AND (insertion_date <= '2025-10-01T00:00:01Z')
        AND (asset = 'USD/2')
    ) balance
  )
  LIMIT 101
), "expand0" AS (
  WITH "rows" AS (
    SELECT DISTINCT ON (accounts_address, asset)
      "accounts_address",
      "asset",
      FIRST_VALUE(post_commit_effective_volumes)
      OVER (PARTITION BY (accounts_address, asset)
      ORDER BY effective_date DESC, seq DESC) AS volumes
    FROM "_default".moves
    WHERE (accounts_address IN (SELECT address FROM dataset))
      AND (ledger = 'example_20251011_212737')
      AND (effective_date <= '2025-10-01T00:00:01Z')
  )
  SELECT
    "accounts_address",
    public.aggregate_objects(
      json_build_object(asset, json_build_object('input', (volumes).inputs, 'output', (volumes).outputs))::jsonb
    ) AS effective_volumes
  FROM rows
  GROUP BY "accounts_address"
)
SELECT *
FROM dataset
LEFT JOIN expand0 ON expand0.accounts_address = dataset.address
ORDER BY "row_number";

Observation

In the SQL, the balance subquery uses insertion_date instead of effective_date:

AND (insertion_date <= '2025-10-01T00:00:01Z')

This may explain why the results for PIT with balance filtering are not as expected.

Question

Should the balance computation for PIT queries use effective_date instead of insertion_date when applying balance filters (e.g., $lt on balance[...])?

Potential Code reference for issue from ledger source.

[aalloiteau]

Thanks for the analysis @imtiazmangerah , we'll have a look and get back to you shortly

[aalloiteau]

We are checking. In the meantime, you can also retrieve balances using /volumes endpoint along w/ pit url parameter + filter {"$lt": {"balance[USD/2]": 0}}
cf How to fetch balances with /volumes endpoint

[aalloiteau]

This is going to be fixed in order to have the same behavior as /volumes endpoint.
By default, it would take effetive_date, and a param useInsertionDate will be added, same as /volumes to be using insertion_date

[aalloiteau]

Fixed in v2.3.1 to take into account effective_date
cc @imtiazmangerah

[imtiazmangerah]

Thank you. I will take a look at the volumes endpoint as a short-term workaround, although my goal was to fetch account metadata directly via the API call.

Regarding account metadata, I’ve observed another behavior: when posting backdated transactions, the metadata is added with the insertion timestamp. This means that queries at a point-in-time (PIT) before the insertion time do not return the metadata as expected.

Here’s a minimal script to reproduce the issue:

from datetime import datetime, timedelta, timezone

from formance_sdk_python import SDK
from formance_sdk_python.models import operations, shared

current_datetime = datetime.now().strftime("%Y%m%d_%H%M%S")

LEDGER_NAME = f"example_{current_datetime}"
SERVER_URL = "http://localhost:3068"

SEND_EXAMPLE = """
vars {
    account $source
    monetary $amount
}
send $amount (
    source = $source allowing unbounded overdraft
    destination = @world
)
set_account_meta($source, "foo", "bar")
"""

TRANSACTION_DATE = datetime(2025, 10, 1, 0, 0, second=0, tzinfo=timezone(timedelta(hours=0)))
ONE_SECOND_AFTER = datetime(2025, 10, 1, 0, 0, second=1, tzinfo=timezone(timedelta(hours=0)))


def run():
    client = SDK(server_url=SERVER_URL)

    # Create a ledger
    client.ledger.v2.create_ledger(
        request=operations.V2CreateLedgerRequest(
            ledger=LEDGER_NAME,
            v2_create_ledger_request=shared.V2CreateLedgerRequest(
                features={}, metadata={}, bucket="_default"
            ),
        )
    )

    # Post a backdated transaction
    client.ledger.v2.create_transaction(
        request=operations.V2CreateTransactionRequest(
            ledger=LEDGER_NAME,
            v2_post_transaction=shared.V2PostTransaction(
                metadata={},
                timestamp=TRANSACTION_DATE,
                script=shared.V2PostTransactionScript(
                    plain=SEND_EXAMPLE,
                    vars={
                        "source": "user:1234",
                        "amount": "USD/2 50",
                    },
                ),
            ),
        )
    )

    # List accounts without PIT
    print("List all accounts")
    accounts = client.ledger.v2.list_accounts(
        request=operations.V2ListAccountsRequest(
            ledger=LEDGER_NAME,
            request_body={},
        )
    )
    print(accounts.v2_accounts_cursor_response.cursor.data)

    print("\n\n✅  List all accounts at PIT")
    accounts = client.ledger.v2.list_accounts(
        request=operations.V2ListAccountsRequest(
            ledger=LEDGER_NAME,
            request_body={},
            pit=ONE_SECOND_AFTER,
        )
    )
    print(accounts.v2_accounts_cursor_response.cursor.data)


if __name__ == "__main__":
    run()

Could you confirm whether this is the expected behavior for metadata historization?

[aalloiteau]

Yes I confirm metadata are always stored w/ insertion_date timestamp