github-starred/actual
2
0
Fork 0
mirror of https://github.com/actualbudget/actual.git synced 2026-05-06 07:01:45 -05:00
Code Issues 1.5k Packages Projects Releases 54 Wiki Activity

[GH-ISSUE #505] RFC: running queries in actual-server #49298

New Issue
Closed
opened 2026-04-30 10:35:03 -05:00 by GiteaMirror · 1 comment
GiteaMirror commented 2026-04-30 10:35:03 -05:00
Owner
Copy Link

Originally created by @rich-howell on GitHub (Jan 22, 2023).
Original GitHub issue: https://github.com/actualbudget/actual/issues/505

Discussed in https://github.com/actualbudget/actual/discussions/388

Originally posted by MatissJanis November 11, 2022

TLDR

Reading actual-server data is a pain. This RFC proposes adding a new endpoint (/query) where customers can send AQL queries to retrieve results from the actual-server database.

This will allow 3rd parties to more easily integrate with Actual.

Background

Actual is a local-first budget management tool. The "local-first" part is achieved by having a base database file downloaded from the server. The base file then is updated with transactions (using the sync operation). The local database file is used as a primary source of truth, but a backup is also available on the server. This backup is necessary so that other devices could get access to the budget at a later date.

Problem

Because of the architecture we use in Actual it is very hard to implement integrations with other services.

For example: what if you wanted to sync a bank account to Actual? It would need to know the current state inside Actual or check last imported transaction to know which new transactions are yet to be synced to Actual. However, there is no central API the integration could call to get the latest state of transactions (or anything else). The integration needs to download the entire database and then manually run the Actual sync operations on top of it to get the active state.

This means each integration needs to duplicate the database syncing logic. It makes all the integrations fragile to future changes, but also it is a very complex implementation. For every. single. integration...

Proposed solution

In Actual-web we already utilize AQL (think of it as SQL for Actual) to run queries against the local database. We could export the AQL library to a npm module which can be reused between actual-web and actual-server.

In actual-server we can then create a new endpoint: /query. This endpoint would accept AQL as a parameter. It would then run the given AQL query against the server database (+subsequent sync updates) and return the response of the query.

This would allow 3rd parties to build queries with AQL. Run them against a remote actual-server. And in response retrieve the expected data.

Things to consider

Access restriction

What if we need to restrict access to a specific table in the future? The current proposal does not allow creating RBAC (role-based access control) for specific tables. However, RBAC can be added later on if we deem it necessary.

Performance

Bootstrapping a server database + sync changes latency will increase linearly. The more syncs there have been - the longer the operation will take.
Given we have the same problem in actual-web too, I won't focus on it in this RFC even though there are things we could improve here.

Alternatives

RESTful server

The most simple and straight-forward solution: to expose the actual-server data via RESTful endpoints. They offer full flexibility, however will cost in maintenance. Hence why I would not recommend this approach.

Database download

Another alternative is to create a new endpoint where the entire database could be downloaded in one go. Then the consumer would have the full database and it would be up to them to query data in it. The consumer in this case would not need to know how to apply all the sync messages, so it's a slightly better solution than can be achieved now.

Originally created by @rich-howell on GitHub (Jan 22, 2023). Original GitHub issue: https://github.com/actualbudget/actual/issues/505 ### Discussed in https://github.com/actualbudget/actual/discussions/388 <div type='discussions-op-text'> <sup>Originally posted by **MatissJanis** November 11, 2022</sup> # TLDR Reading actual-server data is a pain. This RFC proposes adding a new endpoint (`/query`) where customers can send [AQL] queries to retrieve results from the actual-server database. This will allow 3rd parties to more easily integrate with Actual. --- # Background Actual is a local-first budget management tool. The "local-first" part is achieved by having a base database file downloaded from the server. The base file then is updated with transactions (using the sync operation). The local database file is used as a primary source of truth, but a backup is also available on the server. This backup is necessary so that other devices could get access to the budget at a later date. # Problem Because of the architecture we use in Actual it is very hard to implement integrations with other services. For example: what if you wanted to sync a bank account to Actual? It would need to know the current state inside Actual or check last imported transaction to know which new transactions are yet to be synced to Actual. **However, there is no central API the integration could call to get the latest state of transactions (or anything else).** The integration needs to download the entire database and then manually run the Actual sync operations on top of it to get the _active state_. This means **each integration needs to duplicate the database syncing logic**. It makes all the integrations fragile to future changes, but also it is a very complex implementation. For every. single. integration... # Proposed solution In Actual-web we already utilize [AQL] (think of it as SQL for Actual) to run queries against the local database. We could export the [AQL] library to a npm module which can be reused between actual-web and actual-server. In actual-server we can then create a new endpoint: `/query`. This endpoint would accept [AQL] as a parameter. It would then run the given [AQL] query against the server database (+subsequent sync updates) and return the response of the query. This would allow 3rd parties to build queries with [AQL]. Run them against a remote actual-server. And in response retrieve the expected data. # Things to consider ## Access restriction What if we need to restrict access to a specific table in the future? The current proposal does not allow creating RBAC (role-based access control) for specific tables. However, RBAC can be added later on if we deem it necessary. ## Performance Bootstrapping a server database + sync changes latency will increase linearly. The more syncs there have been - the longer the operation will take. Given we have the same problem in actual-web too, I won't focus on it in this RFC even though there are things we could improve here. # Alternatives ## RESTful server The most simple and straight-forward solution: to expose the actual-server data via RESTful endpoints. They offer full flexibility, however will cost in maintenance. Hence why I would not recommend this approach. ## Database download Another alternative is to create a new endpoint where the entire database could be downloaded in one go. Then the consumer would have the full database and it would be up to them to query data in it. The consumer in this case would not need to know how to apply all the sync messages, so it's a _slightly better_ solution than can be achieved now. [AQL]: https://actualbudget.com/docs/developers/ActualQL/</div>
GiteaMirror commented 2026-04-30 10:35:04 -05:00
Author
Owner
Copy Link

@MatissJanis commented on GitHub (Jan 29, 2023):

I created this RFC a while ago :)

This won't be done anytime soon, so I'll close it off to have a cleaner backlog of issues.

<!-- gh-comment-id:1407752439 --> @MatissJanis commented on GitHub (Jan 29, 2023): I created this RFC a while ago :) This won't be done anytime soon, so I'll close it off to have a cleaner backlog of issues.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
AI generated
API
bank sync
budgeting
bug
can’t replicate
dependencies
docker
documentation
electron
experimental feature
feature
feedback
goal templates
good first issue
help wanted
importers
maintenance
needs info
needs testing
needs triage
needs votes
openid
payees
pull-request
Mirrored from GitHub Pull Request
 regression
reports
responsive
rules
schedules
server
✨ merged
split transactions
tech debt
theme
transaction import
transaction reconciliation
transactions
translations
upstream
user interface
✅ approved
wontfix
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
GiteaMirror ninjasurge
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: github-starred/actual#49298
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?