How Transactions Relate to Accounts in Securo's Database: Foreign-Key Relationships Explained

Transactions in Securo are linked to accounts through explicit foreign-key fields (account_id, from_account_id, to_account_id) that enforce referential integrity across the TypeScript frontend, Pydantic schemas, and SQLAlchemy database models.

In Securo's open-source personal finance platform, every financial transaction maintains a clear relationship to account records. This article examines the complete data flow—from UI components to database tables—based on the securo-finance/securo source code.

The Core Relationship: account_id Foreign Keys

Securo implements a consistent foreign-key pattern across all layers of its stack. The primary mechanism is the account_id field (and its variants) that creates a direct reference to the account table.

Frontend TypeScript Types

In frontend/src/types/index.ts, the Transaction interface defines the schema used by the UI:

interface Transaction {
  id: string;
  date: string;
  amount: number;
  description: string | null;
  account_id: string | null;  // Links to single account for income/expense
  // ... additional fields
}

The account_id is nullable (string | null) to accommodate transfer transactions, which instead use from_account_id and to_account_id in API payloads.

Backend Pydantic Schemas

The server-side validation in backend/app/schemas/transaction.py mirrors the frontend structure:

class TransactionBase(BaseModel):
    date: date
    amount: int
    description: Optional[str] = None
    account_id: Optional[str] = None  # FK to Account.id

This TransactionBase class serves as the foundation for TransactionCreate, TransactionUpdate, and response schemas, ensuring consistent account linking across all API operations.

Database SQLAlchemy Model

The actual relational constraint lives in backend/app/models/transaction.py:

class Transaction(Base):
    __tablename__ = "transaction"
    
    id = Column(UUID, primary_key=True)
    account_id = Column(String, ForeignKey("account.id"), nullable=True)
    from_account_id = Column(String, ForeignKey("account.id"), nullable=True)
    to_account_id = Column(String, ForeignKey("account.id"), nullable=True)

Three separate foreign keys handle three transaction types:

• Income/expense: account_id populated, transfer fields null
• Transfer between accounts: account_id null, from_account_id and to_account_id populated
• Unclassified transactions: all three fields null (temporary state during import)

How Transactions Are Created with Account Links

Standard Income or Expense Transactions

When creating a simple grocery expense, the TransactionDialog component constructs a payload including account_id:

import { transactions } from '@/lib/api';
import { TransactionEditPayload } from '@/types';

const newTx: TransactionEditPayload = {
  date: '2024-08-28',
  amount: 42_00,            // Amount in cents (integer)
  description: 'Coffee',
  account_id: 'acct_12345', // Explicit link to checking account
};

await transactions.create(newTx);

The backend validates against TransactionCreate, and SQLAlchemy inserts the row with the foreign key set to 'acct_12345'.

Transfer Transactions Between Accounts

For account-to-account transfers, the relationship model changes:

const transferTx: TransactionEditPayload = {
  date: '2024-08-28',
  amount: 500_00,
  description: 'Monthly savings',
  from_account_id: 'acct_checking_001',  // Source account
  to_account_id: 'acct_savings_002',     // Destination account
  account_id: null,                      // No single primary account
};

The database stores both foreign keys, while account_id remains null to indicate this is a transfer rather than single-account activity.

Querying Transactions by Account

API endpoints that filter transactions use a multi-column approach to capture all related records. In backend/app/services/transaction_service.py (implied by query patterns), the service layer constructs ORM queries like:

from sqlalchemy import or_

def get_transactions_for_accounts(account_ids: List[str]):
    return (
        db.session.query(Transaction)
        .filter(
            or_(
                Transaction.account_id.in_(account_ids),
                Transaction.from_account_id.in_(account_ids),
                Transaction.to_account_id.in_(account_ids),
            )
        )
        .all()
    )

The account_ids query parameter on GET /transactions drives this filter, ensuring users see complete transaction histories regardless of whether an account was the source, destination, or primary account.

Displaying Account Information in the UI

Helper utilities in frontend/src/lib/account-utils.ts resolve raw account_id values to human-readable names:

export function getAccountName(
  accountId: string | null,
  accounts: Account[]
): string {
  if (!accountId) return 'Unassigned';
  const account = accounts.find(a => a.id === accountId);
  return account?.display_name ?? 'Unknown Account';
}

This enables the interface to render "Coffee – My Checking" instead of exposing internal UUIDs to users.

Aggregating Transactions for Dashboard Analytics

Workspace statistics endpoints (/workspace/stats) perform SQL joins between transaction and account tables to calculate per-account and per-workspace totals:

SELECT 
    a.id as account_id,
    a.display_name,
    COUNT(t.id) as transaction_count,
    SUM(CASE WHEN t.amount > 0 THEN t.amount ELSE 0 END) as total_income,
    SUM(CASE WHEN t.amount < 0 THEN t.amount ELSE 0 END) as total_expense
FROM account a
LEFT JOIN transaction t ON t.account_id = a.id
WHERE a.workspace_id = :workspace_id
GROUP BY a.id, a.display_name;

These aggregations depend entirely on the account_id foreign-key relationship established in the SQLAlchemy model.

Summary

• Foreign-key architecture: Transactions relate to accounts through account_id, from_account_id, and to_account_id columns in backend/app/models/transaction.py
• Layer consistency: The same account_id field appears in TypeScript interfaces (frontend/src/types/index.ts), Pydantic schemas (backend/app/schemas/transaction.py), and SQLAlchemy models
• Transfer handling: Dual-account transactions use from_account_id/to_account_id while leaving account_id null
• Query completeness: API filters check all three foreign-key columns to return complete account histories
• Display abstraction: Utility functions in frontend/src/lib/account-utils.ts convert IDs to readable names without exposing database internals

Frequently Asked Questions

What happens if a transaction doesn't have an account assigned?

Transactions with null account_id values typically represent transfers between accounts or unclassified imports. The UI displays these as "Unassigned" or derives context from from_account_id/to_account_id fields. During CSV import workflows, users can bulk-assign accounts before finalizing transactions.

Can a transaction belong to multiple accounts simultaneously?

No single transaction row links to multiple accounts through account_id, but transfer transactions achieve this through the separate from_account_id and to_account_id columns. The query logic in get_transactions_for_accounts uses OR conditions to find transactions where an account appears in any of the three possible positions.

How does Securo maintain referential integrity between transactions and accounts?

The SQLAlchemy model defines explicit ForeignKey("account.id") constraints on all account-related columns. At the database level, this prevents inserting transactions with invalid account references. Application-level validation in Pydantic schemas provides additional checks before database commits occur.

Why is account_id nullable in the transaction schema?

The field is optional to support legitimate business cases: transfers (which use dual account references), pending imports (awaiting user categorization), and split transactions (where sub-items carry individual account assignments). This design prioritizes flexibility while the foreign-key constraint still validates actual relationships when present.

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