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

> Understand how Securo's database links transactions to accounts using foreign-key fields for referential integrity across frontend, schemas, and models.

- Repository: [securo-finance/securo](https://github.com/securo-finance/securo)
- Tags: internals
- Published: 2026-08-28

---

**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`](https://github.com/securo-finance/securo/blob/main/frontend/src/types/index.ts), the `Transaction` interface defines the schema used by the UI:

```typescript
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`](https://github.com/securo-finance/securo/blob/main/backend/app/schemas/transaction.py) mirrors the frontend structure:

```python
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`](https://github.com/securo-finance/securo/blob/main/backend/app/models/transaction.py):

```python
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`:

```tsx
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:

```tsx
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`](https://github.com/securo-finance/securo/blob/main/backend/app/services/transaction_service.py) (implied by query patterns), the service layer constructs ORM queries like:

```python
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`](https://github.com/securo-finance/securo/blob/main/frontend/src/lib/account-utils.ts) resolve raw `account_id` values to human-readable names:

```typescript
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:

```sql
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`](https://github.com/securo-finance/securo/blob/main/backend/app/models/transaction.py)
- **Layer consistency**: The same `account_id` field appears in TypeScript interfaces ([`frontend/src/types/index.ts`](https://github.com/securo-finance/securo/blob/main/frontend/src/types/index.ts)), Pydantic schemas ([`backend/app/schemas/transaction.py`](https://github.com/securo-finance/securo/blob/main/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`](https://github.com/securo-finance/securo/blob/main/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.