[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