Two-Database Design

Relevant source files

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

• src/db/connection.ts
• CLAUDE.md
• README.md
• src/db/trees/sources.ts
• src/db/trees/files.ts
• CONTEXT.md

The Vata application employs a specialized architecture that separates application-level metadata from specific research data through a Two-Database Design. This approach ensures that the global state of the application remains isolated from individual genealogical projects, allowing for efficient management of multiple "Trees" while maintaining portable, local-first data storage.

This design utilizes two distinct types of SQLite databases: the System DB and the Tree DB. The System DB acts as a global registry for the application, while a unique Tree DB is created for every family tree project initiated by the user.

Sources: CLAUDE.md:52-60, README.md:75-79

Database Architecture Overview

The separation of concerns is strictly enforced by the database layer. The application handles global orchestration via the System DB and detailed genealogical operations via the active Tree DB.

Database Roles and Scope

Database	File Name	Scope	Key Responsibilities
System DB	system.db	Global (App-level)	Tree registry, app settings, last opened trees.
Tree DB	<tree-name>.db	Local (Project-level)	Individuals, families, events, places, sources, and media.

Sources: CLAUDE.md:52-60, README.md:81-83, src/db/connection.ts:285-296

Architectural Flow Diagram

The following diagram illustrates how the application interacts with both databases based on the user's context.

Connection Management

The logic for managing these two databases resides in src/db/connection.ts. Vata ensures performance and data integrity by applying specific SQLite PRAGMAs to every connection, including WAL (Write-Ahead Logging) mode and foreign key enforcement.

Connection PRAGMAs

Every database connection initialized by the application applies the following settings:

• journal_mode = WAL (Write-Ahead Logging for better concurrency)
• synchronous = NORMAL
• foreign_keys = ON (Ensures relational integrity)
• busy_timeout = 5000
• cache_size = -20000
• temp_store = MEMORY

Sources: src/db/connection.ts:12-20, CLAUDE.md:65-66

Lifecycle and Routing

The lifecycle of the Tree DB connection is tied to the application's routing system. The "Outside Picker" context (at /) only uses the System DB. When a user enters a specific tree (at /tree/$treeId), the src/routes/tree/$treeId.tsx route triggers the opening of the corresponding Tree DB.

The System Database (system.db)

The System DB is the primary entry point for the application. It tracks all created trees and their filesystem locations.

Key Tables

• trees: Stores metadata for every project, including the project name, the unique filesystem path, and counters for individuals/families for high-level summaries.
• app_settings: A key/value store for application-wide configuration.

Data Model: trees table

Field	Type	Description
id	INTEGER (PK)	Unique internal ID.
name	TEXT	User-facing name of the tree.
path	TEXT	Absolute path to the tree's directory.
individual_count	INTEGER	Cached count of persons in the tree.
last_opened_at	TEXT	ISO timestamp for sorting by recent use.

Sources: src/db/connection.ts:285-305

The Tree Database (tree_name.db)

Every family tree project is stored in its own directory containing a .db file and a media folder. The Tree DB schema follows the genealogical domain requirements, closely aligned with GEDCOM 5.5.1 standards.

Core Genealogical Schema

The Tree DB contains several interconnected tables to represent family structures:

Schema Standardization

The Tree DB includes built-in system types for events and places. Upon initialization, the application seeds the event_types table with standard GEDCOM tags such as BIRT (Birth), MARR (Marriage), and DEAT (Death).

Sources: src/db/connection.ts:133-171

Implementation Patterns

The project follows strict conventions for interacting with these databases to maintain type safety and avoid SQL pitfalls.

No SELECT * Policy

The codebase explicitly forbids the use of SELECT *. All queries must define required columns explicitly to prevent brittle code and optimize performance.
Sources: CLAUDE.md:143-157

CRUD Layer Structure

Files within src/db/trees/ (e.g., individuals.ts, sources.ts) follow a multi-step mapping pattern:

1. Raw Types: Interfaces representing snake_case database rows (e.g., RawSource).
2. Domain Types: TypeScript types using camelCase for UI consumption (e.g., Source).
3. Mapping: Dedicated functions (e.g., mapToSource) that convert raw rows to domain types.
4. Exported Functions: CRUD operations that accept and return domain types only.

Sources: CLAUDE.md:78-85, src/db/trees/sources.ts:9-38, src/db/trees/files.ts:17-45

Conclusion

Vata's Two-Database Design provides a robust framework for local-first data management. By isolating project data into individual SQLite files, the application achieves high portability and project isolation, while the System DB ensures a cohesive application experience across multiple family tree projects.