Securo Database Models: Complete Guide to the Core SQLAlchemy ORM Architecture

Securo uses a single SQLAlchemy declarative base with 25+ core models spanning users, workspaces, financial entities, and automation rules, all defined in backend/app/models/.

The Securo finance platform's persistence layer is built entirely on SQLAlchemy ORM, with a unified declarative base (Base) defined in app.core.database. Every core SQLAlchemy database model in Securo inherits from this base, representing users, workspaces, transactions, and the full spectrum of financial metadata needed for multi-tenant personal and business accounting.

---

User and Identity Management Models

Authentication and authorization in Securo center on two primary models in backend/app/models/user.py and backend/app/models/passkey.py.

User

The User model serves as the central identity for authentication via FastAPI-Users. It stores preferences, two-factor authentication settings, and maintains relationships to all user-owned entities throughout the system.

Key relationships include one-to-many links to Workspace (via ownership), Passkey, and indirect associations to all financial data through workspace membership.

Passkey

The Passkey model stores WebAuthn credentials attached to a user for password-less login. This enables modern, phishing-resistant authentication as implemented in backend/app/models/passkey.py.

---

Workspace and Access Control Models

Multi-tenancy and collaboration are handled through three interconnected models in backend/app/models/workspace.py and backend/app/models/group.py.

Workspace

The Workspace model acts as a container for a set of accounts, categories, and transactions. It supports personal versus business kinds and includes archival and management flags for lifecycle control.

WorkspaceMember

WorkspaceMember functions as a junction table linking User ↔ Workspace with role definitions (owner/editor/viewer) and audit fields. This model enforces granular access control within shared financial environments.

Group

The Group model represents collaborative collections of users and financial entities—such as shared households or business teams. Complementing this is GroupSettlement (backend/app/models/group_settlement.py), which tracks settlement balances between group members.

---

Core Financial Entity Models

The heart of Securo's data model consists of accounts, transactions, and their supporting entities.

Account

In backend/app/models/account.py, the Account model represents financial accounts including bank accounts, cash holdings, and credit cards. It handles currency denominations, balance tracking, and visibility settings within its parent workspace.

Transaction

The Transaction model in backend/app/models/transaction.py is the core ledger entry. It stores amount, dates, description, and maintains foreign keys to accounts, payees, categories, and optional splits.

TransactionSplit

TransactionSplit (backend/app/models/transaction_split.py) allows a single transaction to be divided among multiple categories and amounts—essential for detailed expense tracking where a single purchase spans multiple budget categories.

TransactionAttachment

TransactionAttachment (backend/app/models/transaction_attachment.py) provides optional file or image linking to transactions, typically used for receipt storage and expense documentation.

Payee

The Payee model in backend/app/models/payee.py represents counter-parties for transactions—individuals, merchants, or organizations—with optional tax-ID data for business accounting compliance.

---

Classification and Budgeting Models

Organizational structure for financial data is enforced through hierarchical category systems and budget enforcement.

Category

In backend/app/models/category.py, the Category model provides hierarchical classification for transactions. Each category links to both a user and a workspace, enabling personalized organizational schemes.

CategoryGroup

CategoryGroup (backend/app/models/category_group.py) creates logical groupings of categories, primarily useful for consolidated budgeting and reporting views.

Budget

The Budget model in backend/app/models/budget.py defines monetary caps for a category or category group over a specified time period, enabling spending limit enforcement.

Goal

Goal (backend/app/models/goal.py) implements user-defined financial targets such as savings goals with integrated progress tracking against actual balances.

---

Asset and Investment Models

Non-cash financial holdings are modeled through four related entities.

Asset, AssetGroup, AssetTransaction, AssetValue

Together these models in backend/app/models/asset.py, asset_group.py, asset_transaction.py, and asset_value.py represent:

• Asset: Individual non-cash holdings (stocks, bonds, real estate)
• AssetGroup: Logical collection of related assets
• AssetTransaction: Purchase, sale, and adjustment entries for assets
• AssetValue: Historical valuation snapshots enabling performance tracking and reporting

---

Automation and Integration Models

Securo supports extensive automation through scheduled transactions and rule-based processing.

RecurringTransaction

In backend/app/models/recurring_transaction.py, RecurringTransaction serves as a template for automatically generated transactions on defined schedules—managing subscription tracking and predictable expenses.

Rule

The Rule model in backend/app/models/rule.py implements an automation rule engine with condition → action logic. Rules can automatically tag, categorize, or move transactions based on pattern matching.

Collection

Collection (backend/app/models/collection.py) provides user-defined groupings of transactions for bulk actions or specialized reporting, independent of categorization.

---

Banking and External Integration Models

Financial institution connectivity is abstracted through dedicated credential and metadata models.

BankConnection

BankConnection in backend/app/models/bank_connection.py stores OAuth and API credentials for linked financial institutions, managing secure access to external account data.

Institution

The Institution model (backend/app/models/institution.py) maintains metadata about financial institutions including name, type, and supported feature flags for connection management.

FxRate

FxRate (backend/app/models/fx_rate.py) stores foreign-exchange rate snapshots used for currency conversion calculations across multi-currency workspaces.

CreditCardBill

CreditCardBill in backend/app/models/credit_card_bill.py represents monthly credit-card statements linked to specific accounts, enabling statement reconciliation workflows.

---

System and Audit Models

Operational support is provided through logging and configuration models.

ImportLog

ImportLog (backend/app/models/import_log.py) records data import operations from formats like CSV and OFX, supporting audit trails and troubleshooting workflows.

AppSettings

The AppSettings model in backend/app/models/app_settings.py stores global configuration in the database—including feature flags and deployment-specific parameters.

---

Practical Model Usage Example

The following service-layer function demonstrates canonical usage of Securo's core SQLAlchemy database models for creating a transaction:

from sqlalchemy.ext.asyncio import AsyncSession
from app.models.transaction import Transaction
from app.models.account import Account
from app.models.payee import Payee
from uuid import uuid4
import datetime

async def create_transaction(
    db: AsyncSession,
    *, 
    workspace_id: uuid.UUID,
    account_id: uuid.UUID,
    payee_id: uuid.UUID,
    amount: int,
    description: str,
) -> Transaction:
    # Fetch related entities (simplified)

    account = await db.get(Account, account_id)
    payee = await db.get(Payee, payee_id)

    tx = Transaction(
        id=uuid4(),
        workspace_id=workspace_id,
        account_id=account.id,
        payee_id=payee.id,
        amount=amount,
        description=description,
        date=datetime.date.today(),
        currency=account.currency,
    )
    db.add(tx)
    await db.commit()
    await db.refresh(tx)
    return tx

This pattern illustrates direct use of declarative models with automatic handling of column defaults and relationships as defined in the source files.

---

Summary

• All Securo database models inherit from a single SQLAlchemy declarative base in app.core.database
• User-centric models (User, Passkey, Workspace, WorkspaceMember) handle authentication and multi-tenant access control
• Financial core models (Account, Transaction, TransactionSplit, Payee) implement double-entry ledger functionality
• Classification models (Category, CategoryGroup, Budget, Goal) enable organizational structure and spending controls
• Asset models (Asset, AssetGroup, AssetTransaction, AssetValue) support investment tracking with historical valuations
• Automation models (RecurringTransaction, Rule, Collection) provide scheduled and conditional transaction processing
• Integration models (BankConnection, Institution, FxRate, CreditCardBill) abstract external financial data sources

---

Frequently Asked Questions

How does Securo handle multi-tenant data isolation?

Securo implements multi-tenancy through the Workspace model as the primary isolation boundary. All financial entities—including Account, Transaction, and Category—carry a workspace_id foreign key. The WorkspaceMember junction table enforces role-based access control (owner/editor/viewer) within each workspace, ensuring users only access data from workspaces where they have explicit membership.

Where is the SQLAlchemy Base class defined in Securo?

The declarative Base class is defined in backend/app/core/database.py alongside the engine configuration and session management utilities. All ORM models across the backend/app/models/ package import and inherit from this single base, ensuring consistent metadata and table naming conventions throughout the application.

What model handles transaction categorization and splits?

Transaction categorization uses the Category model with optional hierarchical relationships. For transactions spanning multiple purposes, TransactionSplit (backend/app/models/transaction_split.py) enables division of a single transaction amount across multiple categories with independent amounts, while preserving a single source transaction record for reconciliation and audit purposes.

Does Securo support automated transaction processing?

Yes, through two complementary models: RecurringTransaction generates transactions on defined schedules for predictable items like subscriptions, while Rule (backend/app/models/rule.py) implements conditional automation that can tag, categorize, or move existing transactions based on pattern-matching criteria evaluated at import time or on demand.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}