Repository: bvanelli/actualpy
Pull request #104 (2025-01-15 21:20:18)

feat: Add support for multiple budget types, introduced in version 25.
Dynamically resolves the type of budget based on the file configuration. There are now two types of budget to choose from, retrieved from the `preferences` table:

- Envelope budgeting (default, recommended): `budgetType` is `rollover`, table is ZeroBudgets
- Tracking budgeting: `budgetType` is `report`, table is `ReflectBudgets`

All budget functions will now respect the globally defined preference when creating/updating budgets.

Closes https://github.com/bvanelli/actualpy/issues/103

codecov[bot]: ## [Codecov](https://app.codecov.io/gh/bvanelli/actualpy/pull/104?dropdown=coverage&src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Brunno+Vanelli) Report
Attention: Patch coverage is `95.65217%` with `2 lines` in your changes missing coverage. Please review.
> Project coverage is 97.80%. Comparing base [(`9ee273b`)](https://app.codecov.io/gh/bvanelli/actualpy/commit/9ee273bf5bce3af4dd7bdd356444579528f3ae30?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Brunno+Vanelli) to head [(`fb9b1b2`)](https://app.codecov.io/gh/bvanelli/actualpy/commit/fb9b1b2735be7d5ccb4696990b2b7a33563892bf?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Brunno+Vanelli).

| [Files with missing lines](https://app.codecov.io/gh/bvanelli/actualpy/pull/104?dropdown=coverage&src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Brunno+Vanelli) | Patch % | Lines |
|---|---|---|
| [actual/queries.py](https://app.codecov.io/gh/bvanelli/actualpy/pull/104?src=pr&el=tree&filepath=actual%2Fqueries.py&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Brunno+Vanelli#diff-YWN0dWFsL3F1ZXJpZXMucHk=) | 91.30% | [2 Missing :warning: ](https://app.codecov.io/gh/bvanelli/actualpy/pull/104?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Brunno+Vanelli) |

<details><summary>Additional details and impacted files</summary>


```diff
@@            Coverage Diff             @@
##             main     #104      +/-   ##
==========================================
- Coverage   97.87%   97.80%   -0.07%     
==========================================
  Files          16       16              
  Lines        2258     2283      +25     
==========================================
+ Hits         2210     2233      +23     
- Misses         48       50       +2     
```

</details>

[:umbrella: View full report in Codecov by Sentry](https://app.codecov.io/gh/bvanelli/actualpy/pull/104?dropdown=coverage&src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Brunno+Vanelli).   
:loudspeaker: Have feedback on the report? [Share it here](https://about.codecov.io/codecov-pr-comment-feedback/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Brunno+Vanelli).


Patch:
diff --git a/README.md b/README.md
index 653d10b..9c119b5 100644
--- a/README.md
+++ b/README.md
@@ -87,9 +87,9 @@ The Actual budget is stored in a sqlite database hosted on the user's browser. T
 and can be encrypted with a local key, so that not even the server can read your statements.
 
 The Actual Server is a way of only hosting files and changes. Since re-uploading the full database on every single
-change is too heavy, Actual only stores one state of the database and everything added by the user via frontend
-or via the APIs are individual changes on top of the "base database" stored on the server. This means that on every
-change, done locally, a SYNC request is sent to the server with a list of the following string parameters:
+change is too heavy, Actual only stores one state of the "base database" and everything added by the user via frontend
+or via the APIs are individual changes applied on top. This means that on every change, done locally, the frontend
+does a SYNC request with a list of the following string parameters:
 
 - `dataset`: the name of the table where the change happened.
 - `row`: the row identifier for the entry that was added/update. This would be the primary key of the row (a uuid value)
@@ -97,21 +97,20 @@ change, done locally, a SYNC request is sent to the server with a list of the fo
 - `value`: the new value. Since it's a string, the values are either prefixed by `S:` to denote a string, `N:` to denote
   a numeric value and `0:` to denote a null value.
 
-All individual column changes are computed on an insert, serialized with protobuf and sent to the server to be stored.
-Null values and server defaults are not required to be present in the SYNC message, unless a column is changed to null.
-If the file is encrypted, the protobuf content will also be encrypted, so that the server does not know what was
-changed.
+All individual column changes are computed for an insert or update, serialized with protobuf and sent to the server to
+be stored. Null values and server defaults are not required to be present in the SYNC message, unless a column is
+changed to null. If the file is encrypted, the protobuf content will also be encrypted, so that the server does not know
+what was changed.
 
-New clients can use this individual changes to then sync their local copies and add the changes executed on other users.
-Whenever a SYNC request is done, the response will also contain changes that might have been done in other browsers, so
-that the user the retrieve the information and update its local copy.
+New clients can use this individual changes to then update their local copies. Whenever a SYNC request is done, the
+response will also contain changes that might have been done in other browsers, so that the user is informated about
+the latest information.
 
 But this also means that new users need to download a long list of changes, possibly making the initialization slow.
-Thankfully, user is also allowed to reset the sync. When doing a reset of the file via frontend, the browser is then
+Thankfully, the user is also allowed to reset the sync. When doing a reset of the file via frontend, the browser is then
 resetting the file completely and clearing the list of changes. This would make sure all changes are actually stored in
-the
-database. This is done on the frontend under *Settings > Reset sync*, and causes the current file to be reset (removed
-from the server) and re-uploaded again, with all changes already in place.
+the "base database". This is done on the frontend under *Settings > Reset sync*, and causes the current file to be
+reset (removed from the server) and re-uploaded again, with all changes already in place.
 
 This means that, when using this library to operate changes on the database, you have to make sure that either:
 
diff --git a/actual/database.py b/actual/database.py
index b3a2fc3..9ab2489 100644
--- a/actual/database.py
+++ b/actual/database.py
@@ -293,6 +293,12 @@ class Categories(BaseModel, table=True):
             "primaryjoin": "and_(ZeroBudgets.category_id == Categories.id)",
         },
     )
+    reflect_budgets: "ReflectBudgets" = Relationship(
+        back_populates="category",
+        sa_relationship_kwargs={
+            "primaryjoin": "and_(ReflectBudgets.category_id == Categories.id)",
+        },
+    )
     transactions: List["Transactions"] = Relationship(
         back_populates="category",
         sa_relationship_kwargs={
@@ -500,18 +506,6 @@ class Preferences(BaseModel, table=True):
     value: Optional[str] = Field(default=None, sa_column=Column("value", Text))
 
 
-class ReflectBudgets(SQLModel, table=True):
-    __tablename__ = "reflect_budgets"
-
-    id: Optional[str] = Field(default=None, sa_column=Column("id", Text, primary_key=True))
-    month: Optional[int] = Field(default=None, sa_column=Column("month", Integer))
-    category: Optional[str] = Field(default=None, sa_column=Column("category", Text))
-    amount: Optional[int] = Field(default=None, sa_column=Column("amount", Integer, server_default=text("0")))
-    carryover: Optional[int] = Field(default=None, sa_column=Column("carryover", Integer, server_default=text("0")))
-    goal: Optional[int] = Field(default=None, sa_column=Column("goal", Integer, server_default=text("null")))
-    long_goal: Optional[int] = Field(default=None, sa_column=Column("long_goal", Integer, server_default=text("null")))
-
-
 class Rules(BaseModel, table=True):
     id: Optional[str] = Field(default=None, sa_column=Column("id", Text, primary_key=True))
     stage: Optional[str] = Field(default=None, sa_column=Column("stage", Text))
@@ -659,24 +653,11 @@ class ZeroBudgetMonths(SQLModel, table=True):
     buffered: Optional[int] = Field(default=None, sa_column=Column("buffered", Integer, server_default=text("0")))
 
 
-class ZeroBudgets(BaseModel, table=True):
-    __tablename__ = "zero_budgets"
-
+class BaseBudgets(BaseModel):
     id: Optional[str] = Field(default=None, sa_column=Column("id", Text, primary_key=True))
     month: Optional[int] = Field(default=None, sa_column=Column("month", Integer))
-    category_id: Optional[str] = Field(default=None, sa_column=Column("category", ForeignKey("categories.id")))
+    category_id: Optional[str] = Field(default=None, sa_column=Column("category", Text))
     amount: Optional[int] = Field(default=None, sa_column=Column("amount", Integer, server_default=text("0")))
-    carryover: Optional[int] = Field(default=None, sa_column=Column("carryover", Integer, server_default=text("0")))
-    goal: Optional[int] = Field(default=None, sa_column=Column("goal", Integer, server_default=text("null")))
-    long_goal: Optional[int] = Field(default=None, sa_column=Column("long_goal", Integer, server_default=text("null")))
-
-    category: "Categories" = Relationship(
-        back_populates="zero_budgets",
-        sa_relationship_kwargs={
-            "uselist": False,
-            "primaryjoin": "and_(ZeroBudgets.category_id == Categories.id, Categories.tombstone == 0)",
-        },
-    )
 
     def get_date(self) -> datetime.date:
         return datetime.datetime.strptime(str(self.month), "%Y%m").date()
@@ -716,6 +697,46 @@ class ZeroBudgets(BaseModel, table=True):
         return decimal.Decimal(value) / 100
 
 
+class ReflectBudgets(BaseBudgets, table=True):
+    __tablename__ = "reflect_budgets"
+
+    id: Optional[str] = Field(default=None, sa_column=Column("id", Text, primary_key=True))
+    month: Optional[int] = Field(default=None, sa_column=Column("month", Integer))
+    category_id: Optional[str] = Field(default=None, sa_column=Column("category", ForeignKey("categories.id")))
+    amount: Optional[int] = Field(default=None, sa_column=Column("amount", Integer, server_default=text("0")))
+    carryover: Optional[int] = Field(default=None, sa_column=Column("carryover", Integer, server_default=text("0")))
+    goal: Optional[int] = Field(default=None, sa_column=Column("goal", Integer, server_default=text("null")))
+    long_goal: Optional[int] = Field(default=None, sa_column=Column("long_goal", Integer, server_default=text("null")))
+
+    category: "Categories" = Relationship(
+        back_populates="reflect_budgets",
+        sa_relationship_kwargs={
+            "uselist": False,
+            "primaryjoin": "and_(ReflectBudgets.category_id == Categories.id, Categories.tombstone == 0)",
+        },
+    )
+
+
+class ZeroBudgets(BaseBudgets, table=True):
+    __tablename__ = "zero_budgets"
+
+    id: Optional[str] = Field(default=None, sa_column=Column("id", Text, primary_key=True))
+    month: Optional[int] = Field(default=None, sa_column=Column("month", Integer))
+    category_id: Optional[str] = Field(default=None, sa_column=Column("category", ForeignKey("categories.id")))
+    amount: Optional[int] = Field(default=None, sa_column=Column("amount", Integer, server_default=text("0")))
+    carryover: Optional[int] = Field(default=None, sa_column=Column("carryover", Integer, server_default=text("0")))
+    goal: Optional[int] = Field(default=None, sa_column=Column("goal", Integer, server_default=text("null")))
+    long_goal: Optional[int] = Field(default=None, sa_column=Column("long_goal", Integer, server_default=text("null")))
+
+    category: "Categories" = Relationship(
+        back_populates="zero_budgets",
+        sa_relationship_kwargs={
+            "uselist": False,
+            "primaryjoin": "and_(ZeroBudgets.category_id == Categories.id, Categories.tombstone == 0)",
+        },
+    )
+
+
 class PendingTransactions(SQLModel, table=True):
     __tablename__ = "pending_transactions"
 
diff --git a/actual/queries.py b/actual/queries.py
index 2938cfa..7d95459 100644
--- a/actual/queries.py
+++ b/actual/queries.py
@@ -22,6 +22,8 @@ from actual.database import (
     MessagesClock,
     PayeeMapping,
     Payees,
+    Preferences,
+    ReflectBudgets,
     Rules,
     Schedules,
     Transactions,
@@ -264,9 +266,8 @@ def create_transaction(
 
 def normalize_payee(payee_name: str | None, raw_payee_name: bool = False) -> str:
     """
-    Normalizes the payees according to the source code found at:
-
-    https://github.com/actualbudget/actual/blob/f02ca4e3d26f5b91f4234317e024022fcae2c13c/packages/loot-core/src/server/accounts/sync.ts#L206-L214
+    Normalizes the payees according to the source code found at the [official source code](
+    https://github.com/actualbudget/actual/blob/f02ca4e3d26f5b91f4234317e024022fcae2c13c/packages/loot-core/src/server/accounts/sync.ts#L206-L214)
 
     This make sures that the payees are consistent across the imports, i.e. 'MY PAYEE ' turns into 'My Payee', but so
     does 'My PaYeE'.
@@ -573,11 +574,32 @@ def get_or_create_account(s: Session, name: str | Accounts) -> Accounts:
     return account
 
 
+def _get_budget_table(s: Session) -> typing.Type[typing.Union[ReflectBudgets, ZeroBudgets]]:
+    """
+    Finds out which type of budget the user uses. The types are:
+
+    - Envelope budgeting (default, recommended): `budgetType` is `rollover`, table is ZeroBudgets
+    - Tracking budgeting: `budgetType` is `report`, table is `ReflectBudgets`
+
+    :param s: session from Actual local database.
+    :return: table object for the budget type, based on the preferences.
+    """
+    budget_type = get_preference(s, "budgetType")
+    if budget_type and budget_type.value == "report":
+        return ReflectBudgets
+    else:  # budgetType is rollover
+        return ZeroBudgets
+
+
 def get_budgets(
     s: Session, month: datetime.date = None, category: str | Categories = None
-) -> typing.Sequence[ZeroBudgets]:
+) -> typing.Sequence[typing.Union[ZeroBudgets, ReflectBudgets]]:
     """
-    Returns a list of all available budgets.
+    Returns a list of all available budgets. The object type returned will be either
+    ZeroBudgets or ReflectBudgets, depending on the type of budget selected globally. The budget options are:
+
+    - Envelope budgeting (default): ZeroBudgets
+    - Tracking budgeting: ReflectBudgets
 
     :param s: session from Actual local database.
     :param month: month to get budgets for, as a date for that month. Use `datetime.date.today()` if you want the budget
@@ -585,19 +607,22 @@ def get_budgets(
     :param category: category to filter for the budget. By default, the query looks for all budgets.
     :return: list of budgets
     """
-    query = select(ZeroBudgets).options(joinedload(ZeroBudgets.category))
+    table = _get_budget_table(s)
+    query = select(table).options(joinedload(table.category))
     if month:
         month_filter = int(datetime.date.strftime(month, "%Y%m"))
-        query = query.filter(ZeroBudgets.month == month_filter)
+        query = query.filter(table.month == month_filter)
     if category:
         category = get_category(s, category)
         if not category:
             raise ActualError("Category is provided but does not exist.")
-        query = query.filter(ZeroBudgets.category_id == category.id)
+        query = query.filter(table.category_id == category.id)
     return s.exec(query).unique().all()
 
 
-def get_budget(s: Session, month: datetime.date, category: str | Categories) -> typing.Optional[ZeroBudgets]:
+def get_budget(
+    s: Session, month: datetime.date, category: str | Categories
+) -> typing.Optional[typing.Union[ZeroBudgets, ReflectBudgets]]:
     """
     Gets an existing budget by category name, returns `None` if not found.
 
@@ -613,7 +638,7 @@ def get_budget(s: Session, month: datetime.date, category: str | Categories) ->
 
 def create_budget(
     s: Session, month: datetime.date, category: str | Categories, amount: decimal.Decimal | float | int = 0.0
-) -> ZeroBudgets:
+) -> typing.Union[ZeroBudgets, ReflectBudgets]:
     """
     Gets an existing budget based on the month and category. If it already exists, the amount will be replaced by
     the new amount.
@@ -626,12 +651,13 @@ def create_budget(
     :return: return budget matching the month and category, and assigns the amount to the budget. If not found, creates
              a new budget.
     """
+    table = _get_budget_table(s)
     budget = get_budget(s, month, category)
     if budget:
         budget.set_amount(amount)
         return budget
     category = get_category(s, category)
-    budget = ZeroBudgets(id=str(uuid.uuid4()), category_id=category.id)
+    budget = table(id=str(uuid.uuid4()), category_id=category.id)
     budget.set_date(month)
     budget.set_amount(amount)
     s.add(budget)
@@ -767,3 +793,42 @@ def get_or_create_clock(s: Session, client: HULC_Client = None) -> MessagesClock
         if client:
             clock.set_timestamp(client)
     return clock
+
+
+def get_preferences(s: Session) -> typing.Sequence[Preferences]:
+    """
+    Loads the preference list from the database.
+
+    :param s: session from Actual local database.
+    :return: List of preferences.
+    """
+    return s.exec(select(Preferences)).all()
+
+
+def get_or_create_preference(s: Session, key: str, value: str) -> Preferences:
+    """
+    Loads the preference list from the database. If the key is missing, a new one is created, otherwise it's updated.
+
+    :param s: session from Actual local database.
+    :param key: key of the preference.
+    :param value: value of the preference.
+    :return: the preference object.
+    """
+    preference = get_preference(s, key)
+    if preference is None:
+        preference = Preferences(id=key, value=value)
+        s.add(preference)
+    else:
+        preference.value = value
+    return preference
+
+
+def get_preference(s: Session, key: str, default: str = None) -> typing.Optional[Preferences]:
+    """
+    Gets an existing preference by key name, returns `None` if not found.
+
+    :param s: session from Actual local database.
+    :param key: preference name.
+    :param default: default value to be returned if key is not found.
+    :return: preference matching the key provided. If not found, returns `None`."""
+    return s.exec(select(Preferences).where(Preferences.id == key)).one_or_none() or default
diff --git a/docker/docker-compose.yml b/docker/docker-compose.yml
index cc4cf18..e353254 100644
--- a/docker/docker-compose.yml
+++ b/docker/docker-compose.yml
@@ -2,7 +2,7 @@
 services:
   actual:
     container_name: actual
-    image: docker.io/actualbudget/actual-server:24.12.0
+    image: docker.io/actualbudget/actual-server:25.1.0
     ports:
       - '5006:5006'
     volumes:
diff --git a/mkdocs.yml b/mkdocs.yml
index d0ed4f2..00af619 100644
--- a/mkdocs.yml
+++ b/mkdocs.yml
@@ -34,6 +34,7 @@ markdown_extensions:
 
 plugins:
 - search
+- autorefs
 - mkdocstrings:
     handlers:
           python:


Test patch:
diff --git a/tests/test_database.py b/tests/test_database.py
index 4158fdd..0ce8405 100644
--- a/tests/test_database.py
+++ b/tests/test_database.py
@@ -6,7 +6,7 @@ from datetime import date, timedelta
 import pytest
 
 from actual import Actual, ActualError, reflect_model
-from actual.database import Notes
+from actual.database import Notes, ReflectBudgets, ZeroBudgets
 from actual.queries import (
     create_account,
     create_budget,
@@ -19,6 +19,8 @@ from actual.queries import (
     get_or_create_category,
     get_or_create_clock,
     get_or_create_payee,
+    get_or_create_preference,
+    get_preferences,
     get_ruleset,
     get_transactions,
     normalize_payee,
@@ -191,7 +193,13 @@ def test_rule_insertion_method(session):
     assert str(rs) == "If all of these conditions match 'date' isapprox '2024-01-02' then set 'cleared' to 'True'"
 
 
-def test_budgets(session):
+@pytest.mark.parametrize(
+    "budget_type,budget_table",
+    [("rollover", ZeroBudgets), ("report", ReflectBudgets)],
+)
+def test_budgets(session, budget_type, budget_table):
+    # set the config
+    get_or_create_preference(session, "budgetType", budget_type)
     # insert a budget
     category = get_or_create_category(session, "Expenses")
     unrelated_category = get_or_create_category(session, "Unrelated")
@@ -202,6 +210,7 @@ def test_budgets(session):
     assert len(get_budgets(session, date(2024, 10, 1), category)) == 1
     assert len(get_budgets(session, date(2024, 9, 1))) == 0
     budget = get_budgets(session)[0]
+    assert isinstance(budget, budget_table)
     assert budget.get_amount() == 10.0
     assert budget.get_date() == date(2024, 10, 1)
     # get a budget that already exists, but re-set it
@@ -300,3 +309,17 @@ def test_get_or_create_clock(session):
     clock = get_or_create_clock(session)
     assert clock.get_timestamp().ts == datetime.datetime(1970, 1, 1, 0, 0, 0)
     assert clock.get_timestamp().initial_count == 0
+
+
+def test_get_preferences(session):
+    assert len(get_preferences(session)) == 0
+    preference = get_or_create_preference(session, "foo", "bar")
+    assert preference.value == "bar"
+    preferences = get_preferences(session)
+    assert len(preferences) == 1
+    assert preferences[0] == preference
+    # update preference
+    get_or_create_preference(session, "foo", "foobar")
+    new_preferences = get_preferences(session)
+    assert len(new_preferences) == 1
+    assert new_preferences[0].value == "foobar"
diff --git a/tests/test_integration.py b/tests/test_integration.py
index 7e9b6ca..074de99 100644
--- a/tests/test_integration.py
+++ b/tests/test_integration.py
@@ -22,7 +22,7 @@ from actual.queries import (
     get_transactions,
 )
 
-VERSIONS = ["24.10.0", "24.10.1", "24.11.0", "24.12.0"]
+VERSIONS = ["24.10.1", "24.11.0", "24.12.0", "25.1.0"]
 
 
 @pytest.fixture(params=VERSIONS)  # todo: support multiple versions at once