Database Schema & Entities

Relevant source files

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

• CLAUDE.md
• src/db/connection.ts
• CONTEXT.md
• src/db/trees/sources.ts
• src/db/trees/places.ts
• src/db/trees/citations.ts
• src/test/sqlite-memory.ts

Vata employs a local-first, two-database architecture designed for managing genealogical family trees. The system separates application-level metadata from specific family tree data to ensure portability and privacy. Data is persisted using SQLite via the @tauri-apps/plugin-sql plugin.

The application distinguishes between two primary database contexts:

1. System Database (system.db): Manages app-level settings and the registry of available family trees.
2. Tree Database (<tree-name>.db): Contains all genealogical data for a single family tree, including individuals, families, events, and citations.

Sources: CLAUDE.md:46-56, src/db/connection.ts:257-300

Two-Database Architecture

Vata maintains two distinct SQLite databases simultaneously to separate global application state from user-generated genealogical content.

System Database

The System DB initializes on the first app launch. It primarily tracks the list of family trees (names, file paths, and metadata like individual counts) and general application settings.

Tree Database

The Tree DB is a self-contained SQLite file for a specific project. This architecture allows users to share or move individual tree files (e.g., via cloud storage or USB) without affecting the overall application registry.

The diagram above illustrates the relationship between the global registry and individual tree files.
Sources: CLAUDE.md:46-56, src/db/connection.ts:311-342

Core Genealogical Entities

The Tree DB schema is built around standard genealogical entities defined in the GEDCOM 5.5.1 specification.

Individuals and Names

An Individual represents a person. While the internal database and code refer to "individuals," the UI uses the label "Person." An individual can have multiple Names (birth, married, etc.), with exactly one flagged as is_primary.

Table	Purpose	Key Constraints
individuals	Core person record.	gender in ('M', 'F', 'U')
names	Naming records for an individual.	individual_id (FK), is_primary

Sources: src/db/connection.ts:30-74, CONTEXT.md:12-25

Families and Relationships

A Family record represents a union. It links individuals in specific roles: husband, wife, or child. The nature of the parent-child relationship is defined by a Pedigree type (birth, adopted, foster, sealing, or step).

The ER diagram shows how the family_members junction table connects individuals to a family unit.
Sources: src/db/connection.ts:77-108, CONTEXT.md:27-31

Events and Places

Events (birth, marriage, death, etc.) are dated occurrences. They are linked to a Place, which is hierarchical (e.g., City -> County -> State).

• Places: Hierarchical via a parent_id reference to itself.
• Event Participants: Individuals or Families participating in an event with a specific role (principal, witness, officiant).

Sources: src/db/connection.ts:149-218, src/db/trees/places.ts:248-278

Evidence and Citations

Vata implements a strict separation between Sources (the physical/digital document) and Citations (specific references within a source).

The Source-Citation-Entity Chain

Data is never linked directly to a Source. Instead, an entity (Individual, Event, etc.) is linked to a Citation, which in turn points to a Source.

• Source: A parish register, census, or certificate.
• Citation: Page number, quality assessment, and transcription within a source.
• Repository: The archive or library holding the Source.

The flow shows how evidence is attributed to genealogical facts through citations.
Sources: CONTEXT.md:40-66, src/db/connection.ts:241-285

Entity ID Conventions

Vata uses a specific ID convention to bridge the gap between SQLite's INTEGER primary keys and the UI's formatted strings.

Entity	Prefix	Example String ID
Individual	I	I-0001
Family	F	F-0002
Event	E	E-0003
Place	P	P-0004
Source	S	S-0005
Repository	R	R-0006

The src/lib/entityId.ts utility handles conversion using formatEntityId and parseEntityId.
Sources: CLAUDE.md:65-75, src/db/trees/sources.ts:32-47

Implementation Patterns

DB Layer Structure

Files within src/db/trees/ follow a consistent pattern to ensure type safety and explicit data access:

1. Raw Type: Interfaces defining snake_case fields matching the SQLite columns.
2. Public Type: CamelCase interfaces used by the React frontend.
3. Mapping Function: Functions (e.g., mapRawToSource) that convert database rows to public objects, including ID formatting.
4. No SELECT *: All queries must explicitly list columns to prevent accidental data leakage or performance issues.

// 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:77-84, src/db/trees/sources.ts:51-67

Conclusion

The Vata database schema provides a robust foundation for local-first genealogy. By utilizing a dual-database design and strictly adhering to GEDCOM-compliant entity relationships—particularly the distinction between sources and citations—the system ensures both data integrity and high interoperability. The use of formatted string IDs and explicit mapping layers facilitates a clean separation between the SQLite storage layer and the React user interface.

Sources: CLAUDE.md:46-63, CONTEXT.md:75-84