Database Schema and Migrations

Relevant source files

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

• frontend/lib/db/schema.ts
• backend/app/models.py
• frontend/scripts/migrate.js
• frontend/lib/db/migrations/meta/0000_snapshot.json
• frontend/lib/db/migrations/0003_schema_alignment.sql
• CLAUDE.md
• README.md

The Syllogic personal finance application utilizes a shared PostgreSQL database that serves as the central data store for both the Next.js frontend and the FastAPI backend. The system follows a "two-service" architecture where the Drizzle ORM schema defined in the frontend is treated as the primary source of truth for the database structure.

This system ensures data consistency across the stack by mirroring the Drizzle schema in the backend using SQLAlchemy 2.0. Automated migration scripts handle the deployment of schema changes, ensuring that both fresh installs and upgrades remain aligned with the latest application requirements.

Sources: CLAUDE.md:1-15, README.md:95-115

Architecture and Design

The database architecture is designed for a multi-user, multi-currency environment. It supports core financial entities such as accounts, transactions, and categories, alongside infrastructure for authentication, AI-powered enrichment, and background job tracking.

Entity Relationship Overview

The following diagram illustrates the relationships between the core entities within the system.

The schema emphasizes user-level isolation, where almost every table contains a user_id foreign key referencing the users table.

Sources: frontend/lib/db/migrations/meta/0000_snapshot.json:43-670, frontend/lib/db/migrations/0003_schema_alignment.sql:43-155

Core Data Models

The database is composed of several functional modules, ranging from authentication to financial tracking.

User and Authentication

The system uses BetterAuth for authentication, which requires specific tables for managing users, sessions, and verification tokens.

Table	Description	Key Fields
users	Core user profile data	id, email, functional_currency, onboarding_status
sessions	Active user sessions	id, user_id, token, expires_at
auth_accounts	Linked authentication providers	id, user_id, provider_id, access_token
api_keys	Programmatic access keys	id, user_id, key_hash, key_prefix

Sources: frontend/lib/db/migrations/meta/0000_snapshot.json:115-188, frontend/lib/db/migrations/0003_schema_alignment.sql:12-23

Financial Entities

These tables manage the actual financial data imported from banks or CSV files.

Table	Description	Key Fields
accounts	Bank or investment accounts	id, name, currency, balance_current, provider
transactions	Individual financial entries	id, amount, booked_at, merchant, category_id
categories	Spending/Income classifications	id, name, category_type, color, is_system
account_balances	Historical balance snapshots	account_id, date, balance_in_functional_currency

Sources: frontend/lib/db/migrations/meta/0000_snapshot.json:15-112, frontend/lib/db/migrations/0003_schema_alignment.sql:110-140

Migration System

Migrations are managed primarily through Drizzle Kit on the frontend. A specialized Node.js script, scripts/migrate.js, is used for production-safe deployment of these migrations.

Migration Flow

The application ensures the database is ready before the services start. In containerized environments, the migration job must complete successfully before the backend or frontend services are allowed to initialize.

Migration Components

• scripts/migrate.js: A plain Node.js runner that avoids heavy dependencies like ts-node in production. It handles "Broken Migration Row Types" and manages the search_path for the public schema.
• drizzle Schema: A dedicated internal schema used to track applied migrations in the __drizzle_migrations table.
• 0003_schema_alignment.sql: A critical migration that aligns tables originally created by SQLAlchemy (backend) with the Drizzle schema (frontend) to prevent conflicts.

Sources: frontend/scripts/migrate.js:40-65, frontend/lib/db/migrations/0003_schema_alignment.sql:1-10

Implementation Details

Data Types and Constraints

The schema uses specific PostgreSQL types to ensure financial accuracy:

• numeric(15, 2): Used for all amount and balance fields to prevent floating-point errors.
• uuid: Used for primary keys (generated via gen_random_uuid()) to support distributed systems.
• char(3): Used for ISO currency codes (e.g., 'EUR', 'USD').
• jsonb: Used for flexible data like enrichment_data in transactions and selected_indices in CSV imports.

Sources: frontend/lib/db/migrations/meta/0000_snapshot.json:40-60, frontend/lib/db/migrations/0003_schema_alignment.sql:43-85

Indices

Strategic indices are implemented to maintain performance during filtering and aggregation:

• idx_transactions_booked_at: Optimizes time-based queries for dashboards.
• idx_api_keys_hash: Ensures fast lookup for API authentication.
• idx_company_logos_domain: Facilitates fast logo matching by merchant domain.
• idx_transactions_user_type_date: Optimized for category breakdown reports.

Sources: frontend/lib/db/migrations/meta/0000_snapshot.json:450-510, frontend/lib/db/migrations/0003_schema_alignment.sql:145-155

Conclusion

The Syllogic database schema is a robust, shared foundation that enables cross-service compatibility. By utilizing Drizzle as the source of truth and a dedicated migration runner, the project maintains a strict, type-safe structure that supports complex financial operations like multi-currency tracking, AI categorization, and transaction linking while ensuring deployment reliability across different environments.