Database & Migrations

Relevant source files

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

• internal/db/db.go
• internal/db/db_test.go
• internal/app/app.go
• internal/app/backup.go
• internal/db/migrations/0004_local_calendar.sql
• internal/db/migrations/0006_integrations.sql
• README.md

GoCasa stores all application data in a single SQLite database file named app.db. The system manages the database lifecycle through automated migrations embedded in the binary and a robust backup strategy that protects data before schema changes and during daily operations.

Sources: README.md:5-7, internal/app/app.go:50-52

Database Architecture

GoCasa uses SQLite with Write-Ahead Logging (WAL) enabled to support concurrent operations. The application opens the database with specific pragmas to ensure data integrity and performance, including foreign key enforcement and synchronous normal mode.

Sources: internal/db/db.go:23-24, internal/db/db_test.go:42-53

Connection Configuration

The Open function initializes the SQLite connection using the following parameters:

• Journal Mode: WAL (Write-Ahead Logging)
• Busy Timeout: 5000ms
• Foreign Keys: ON
• Synchronous: NORMAL

Sources: internal/db/db.go:24-25

Migration System

The migration system applies SQL scripts embedded in the binary to the database. Each migration runs in its own transaction. The system tracks the current schema version using the SQLite PRAGMA user_version.

Sources: internal/db/db.go:37-40, README.md:43-45

Migration Execution Flow

When the application starts, it performs the following steps to update the schema:

1. Scan: It identifies all *.sql files in the embedded migrations/ directory.
2. Validate: It ensures migration filenames start with sequential positive numbers (e.g., 0001_baseline.sql) without gaps or duplicates.
3. Compare: It reads the current user_version from the database.
4. Backup: If pending migrations exist, it executes a "pre-migration" backup.
5. Apply: It executes each new migration script in numeric order.
6. Update Version: It increments the user_version within the same transaction as the migration script.

Sources: internal/db/db.go:41-76, internal/db/db_test.go:55-73

The diagram shows the sequential application of migrations and the safety rollback mechanism. Sources: internal/db/db.go:59-93

Migration Constraints

Constraint	Description	Source
Numeric Order	Migrations must be named NNNN_*.sql and start from 1.	db.go:49-55
No Gaps	The system rejects migrations if a number in the sequence is missing.	db.go:57-61
Binary Version	Rejects databases with a user_version higher than the binary's latest migration.	db.go:68-70
Transactional	Each migration file and its version update succeed or fail together.	db.go:88-93

Backup Strategy

GoCasa implements two types of backups using the SQLite VACUUM INTO command to ensure data can be restored without corruption.

Sources: internal/app/backup.go:18-28, README.md:31-41

Backup Types

1. Daily Backups: Executed every day at 03:00 (in the configured TZ). The system keeps the 14 newest daily backups.
2. Pre-migration Backups: Created immediately before applying schema updates. These are named app-YYYYMMDD-pre-vN.db and are never automatically pruned.
3. Startup Backups: If the application starts and the newest backup is more than 24 hours old, it takes a backup immediately.

Sources: internal/app/backup.go:53-73, internal/app/app.go:56-59, README.md:31-34

Backup Management

Backups are stored in the /data/backups/ directory. The application manages pruning by sorting files by date (based on filename) and removing those exceeding the retention limit.

Sources: internal/app/backup.go:13-16, 42-45

Data Schema Overview

The database schema is defined across several migration files. Key entities include integrations, calendars, and events.

Sources: internal/db/migrations/0004_local_calendar.sql, internal/db/migrations/0006_integrations.sql

Core Tables

Table	Purpose	Key Fields
integrations	Sync sources (e.g., iCal, Google).	id, type, name, enabled, next_sync_at
source_calendars	Specific calendars within an integration.	id, integration_id, name, color, read_only
events	Individual or recurring calendar entries.	id, calendar_id, title, all_day, start_utc, rrule
members	Household members who own events.	id, name, role, archived

Sources: internal/db/migrations/0004_local_calendar.sql:4-44, internal/db/migrations/0006_integrations.sql:3-13

Entity Relationships

The ER diagram illustrates the hierarchy from integration sources down to individual event participants. Sources: internal/db/migrations/0004_local_calendar.sql:4-53

Health Monitoring

The application provides a health check endpoint at GET /healthz. This endpoint performs a simple database ping (SELECT 1) to ensure the database is reachable and responsive. If the query fails, it returns a 503 Service Unavailable status.

Sources: internal/app/app.go:121-131, internal/app/app_test.go:27-35

Summary

GoCasa's database system prioritizes safety and simplicity. By using an embedded migration system and mandatory pre-migration backups, it ensures that schema upgrades are predictable and reversible. The use of SQLite in WAL mode combined with automated daily backups provides a reliable data layer for a self-hosted family dashboard.

Sources: README.md:5-7, 43-48