Transaction Sync and Import Pipeline

Relevant source files

The following files were used as context for generating this wiki page:

• frontend/lib/actions/csv-import.ts
• backend/app/routes/transaction_import.py
• BANK_INTEGRATION_GUIDE.md
• frontend/app/(dashboard)/transactions/import/mapping/page.tsx/transactions/import/mapping/page.tsx)
• frontend/app/(dashboard)/transactions/import/preview/page.tsx/transactions/import/preview/page.tsx)
• backend/tests/test_transaction_import.py
• frontend/scripts/import-revolut-csv.ts

The Transaction Sync and Import Pipeline is a core component of the Syllogic personal finance app, responsible for ingesting financial data from various sources into the shared PostgreSQL database. It supports a multi-modal ingestion strategy, including manual CSV uploads, programmatic bank API integrations, and scripted legacy imports.

The pipeline ensures data integrity through sophisticated duplicate detection, AI-powered categorization, and automated balance reconciliation. It serves as the bridge between raw external data and the enriched financial insights presented in the dashboard.

Sources: README.md, frontend/lib/actions/csv-import.ts:1-20, backend/app/routes/transaction_import.py:1-15

Pipeline Architecture

The pipeline follows a two-service architecture where the Next.js frontend handles file parsing and user mapping, while the FastAPI backend executes the complex data processing, enrichment, and persistence logic.

Data Flow Overview

This diagram illustrates the progression from raw data ingestion through frontend validation to final backend persistence.
Sources: frontend/lib/actions/csv-import.ts:80-150, backend/app/routes/transaction_import.py:150-250, CLAUDE.md

Ingestion Methods

The system provides three primary ways to import transactions, accommodating both casual users and automated workflows.

Method	Description	Source
Manual CSV Import	A step-by-step wizard in the frontend for uploading, mapping, and previewing CSV files.	frontend/lib/actions/csv-import.ts
Bank API (Adapters)	Ready-to-implement interfaces for Plaid and TrueLayer using an adapter pattern.	BANK_INTEGRATION_GUIDE.md
Programmatic CLI	Scripts for direct database insertion, used for legacy Revolut CSV formats.	frontend/scripts/import-revolut-csv.ts

Bank Adapter Pattern

The system uses a BankAdapter interface to standardize inputs from different financial service providers. This allows the core sync logic to remain agnostic of the specific provider.
Sources: BANK_INTEGRATION_GUIDE.md:5-20

CSV Import Workflow

The CSV import workflow is a highly interactive multi-stage process located in the frontend (dashboard)/transactions/import directory.

1. Initialization and Header Parsing

The pipeline begins by uploading the CSV to storage and parsing the raw content to identify headers and sample rows.
Sources: frontend/lib/actions/csv-import.ts:88-105

2. Intelligent Field Mapping

Mapping connects CSV columns (e.g., "Narrative", "Value") to internal schema fields. This can be done manually or via an AI-assisted process using OpenAI.

• AI Mapping: The getAiColumnMapping function sends headers and sample rows to GPT-4o-mini to predict the correct mapping and date formats (DD-MM-YYYY vs MM-DD-YYYY).
• Mapping Interface: Users verify the mapping in the MappingPage before proceeding.

Sources: frontend/lib/actions/csv-import.ts:182-260, frontend/app/(dashboard)/transactions/import/mapping/page.tsx:30-60

3. Preview and Duplicate Detection

Before finalization, the system generates a preview of the transactions.

• Deterministic ID Generation: Transactions are assigned a hash-based external_id (format: csv-import-{hash1}{hash2}{hash3}) derived from the account ID, date, amount, description, and merchant.
• Conflict Resolution: Users can choose to skip detected duplicates or transactions with ambiguous states (e.g., PENDING or REVERTED).

Sources: frontend/lib/actions/csv-import.ts:514-550, frontend/app/(dashboard)/transactions/import/preview/page.tsx:50-80

Backend Processing Pipeline

When an import is finalized, the frontend calls the /api/transactions/import endpoint. The backend then executes a series of sequential tasks.

Sequence diagram showing the backend's internal execution steps during a transaction import.
Sources: backend/app/routes/transaction_import.py:110-300

Normalization Logic

Transaction amounts are normalized based on their type:

• Debit: Forced to a negative value.
• Credit: Forced to a positive value.
• Aliases: Terms like "expense" or "revenue" are mapped to "debit" and "credit" respectively.

Sources: backend/app/routes/transaction_import.py:126-160

Smart Categorization

The pipeline uses a hybrid approach for categorization:

1. User Overrides: Matches transactions against previous manual edits by the user.
2. Deterministic Rules: Matches against defined patterns in CategorizationRule.
3. LLM Categorization: As a fallback, OpenAI analyzes the description and merchant to suggest a category.

Sources: backend/app/routes/transaction_import.py:200-240, frontend/lib/actions/csv-import.ts:835-850

Balance Reconciliation and Timeseries

The final stage of the pipeline updates the financial state of the relevant accounts.

1. Starting Balance Update: If the CSV contains an explicit starting balance, the account's starting_balance field is updated.
2. Daily Snapshots: The AccountBalanceService imports daily balances from the CSV to ensure historical accuracy.
3. Functional Currency Conversion: Transaction amounts are converted into the user's functional_currency (e.g., EUR) using synced exchange rates.
4. Timeseries Calculation: A full daily balance history is re-calculated for the affected accounts to support dashboard charts.

Sources: backend/app/routes/transaction_import.py:530-610, backend/app/services/account_balance_service.py

Key Data Structures

Transaction Import Request (Pydantic)

class TransactionImportRequest(BaseModel):
    transactions: List[TransactionImportItem]
    user_id: Optional[str] = None
    sync_exchange_rates: bool = True
    update_functional_amounts: bool = True
    calculate_balances: bool = True
    detect_subscriptions: bool = True
    daily_balances: Optional[List[DailyBalanceImport]] = None
    starting_balance: Optional[Decimal] = None

Sources: backend/app/routes/transaction_import.py:56-65

Summary

The Transaction Sync and Import Pipeline is designed for both accuracy and user convenience. By combining deterministic hashing for deduplication with LLM-based intelligent mapping and categorization, the pipeline minimizes manual data entry while maintaining a high-fidelity financial record. Its modular backend pipeline ensures that every import triggers necessary side effects like subscription detection and balance reconciliation automatically.