SQLite & Database Standards

Relevant source files

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

• src/db/connection.ts
• src/test/sqlite-memory.ts
• CLAUDE.md
• src/db/trees/sources.ts
• src/db/trees/files.ts
• README.md

Vata employs a local-first, two-database architecture designed for genealogical data management. The system separates application-level metadata from tree-specific genealogical records, utilizing SQLite via the @tauri-apps/plugin-sql driver. This approach ensures data portability, where each family tree is contained within its own portable .db file.

The database layer follows strict implementation patterns, including explicit column selection, snake_case to camelCase mapping, and mandatory PRAGMA configurations for performance and integrity.

Sources: CLAUDE.md:46-54, README.md:14-17

Database Architecture

The application maintains two distinct types of SQLite databases simultaneously:

1. System DB (system.db): Stores global metadata such as the list of known trees, their file paths, and general application settings.
2. Tree DB (<tree-name>.db): Contains all genealogical data for a specific family tree, including individuals, families, events, and citations.

Database Roles and Connectivity

The diagram shows the separation between global application state and individual project data. Sources: CLAUDE.md:50-58, src/db/connection.ts:316-352

Connection Management

Database connections are managed centrally in src/db/connection.ts. Key lifecycle functions include:

Function	Description
getSystemDb()	Opens or initializes the system database on the first call.
openTreeDb(path)	Opens a specific tree database, closing any currently open tree DB first.
getTreeDb()	Returns the active tree database connection; throws an error if none is open.
closeTreeDb()	Gracefully closes the active tree database connection.

Sources: CLAUDE.md:60-65, src/db/connection.ts:335-375

Mandatory Connection Standards

Every database connection must be initialized with specific PRAGMAs to ensure Write-Ahead Logging (WAL) mode, foreign key enforcement, and optimal performance.

async function applyConnectionPragmas(db: Database): Promise<void> {
  await db.execute('PRAGMA journal_mode = WAL');
  await db.execute('PRAGMA synchronous = NORMAL');
  await db.execute('PRAGMA foreign_keys = ON');
  await db.execute('PRAGMA busy_timeout = 5000');
  await db.execute('PRAGMA cache_size = -20000');
  await db.execute('PRAGMA temp_store = MEMORY');
}

Sources: src/db/connection.ts:13-20

Data Modeling & Schema

The Tree DB schema is designed around core genealogical entities. Primary keys are INTEGER AUTOINCREMENT, while the UI uses formatted string IDs (e.g., I-0001).

Core Schema Relationships

Visual representation of the primary genealogical entity relationships. Sources: src/db/connection.ts:25-230

Primary Tables Definitions

Table	Purpose	Key Constraints
individuals	Base person record	gender IN ('M', 'F', 'U')
names	Name variants for individuals	FOREIGN KEY to individuals(id)
families	Union records (not ceremony-dependent)	AUTOINCREMENT PK
family_members	Linkage between persons and families	role (husband, wife, child)
events	Dated occurrences (Birth, Death, etc.)	FOREIGN KEY to event_types
sources	Documentary evidence records	Linked to repositories
tree_meta	Internal metadata (schema version)	Key/Value store

Sources: src/db/connection.ts:25-133, src/test/sqlite-memory.ts:40-150

Implementation Patterns

The database layer follows a strict "No SELECT *" policy and a structured mapping pattern.

1. Explicit Projections

All queries must explicitly list columns. This prevents issues with schema evolution and improves performance.

// Example from src/db/trees/sources.ts
const COLUMNS = 'id, repository_id, title, author, publisher, publication_date, call_number, url, notes, created_at, updated_at';

export async function getAllSources(): Promise<Source[]> {
  const db = await getTreeDb();
  const rows = await db.select<RawSource[]>(`SELECT ${COLUMNS} FROM sources ORDER BY title`);
  return rows.map(mapToSource);
}

Sources: CLAUDE.md:144-159, src/db/trees/sources.ts:41-45

2. Raw to Domain Mapping

Database modules must define Raw* interfaces (snake_case) and public domain types (camelCase). Mapping functions convert database rows to application-friendly objects.

function mapToTreeFile(raw: RawTreeFile): TreeFile {
  return {
    id: String(raw.id),
    originalFilename: raw.original_filename,
    relativePath: raw.relative_path,
    mimeType: raw.mime_type,
    fileSize: raw.file_size,
    // ... further mappings
  };
}

Sources: CLAUDE.md:85-90, src/db/trees/files.ts:33-46

3. Entity ID Conventions

Database INTEGER keys are converted to/from formatted strings for the UI layer:

• I- : Individual
• F- : Family
• S- : Source
• E- : Event

Sources: CLAUDE.md:76-83, src/db/trees/sources.ts:28-30

Testing Infrastructure

Database tests utilize an in-memory SQLite implementation using better-sqlite3.

Unit Testing Flow

The testing flow ensures tests remain isolated and do not touch physical .db files. Sources: src/test/sqlite-memory.ts:265-285, src/db/trees/sources.test.ts:16-25

Conclusion

The database standards in Vata prioritize data integrity through rigorous PRAGMA configuration and explicit coding patterns. By separating system concerns from project data and enforcing strict type mapping, the application ensures that genealogical data remains portable, consistent, and easily accessible across the software's layers.